Analyzing iMessage with SQL
arctype.com
arctype.com
For me, when I scroll up to load historical data, it takes always multiple _seconds_. This is on the newest, highest specced m1 macbook. What the hell is it doing in that time? Multiple seconds to do read-only queries a <100MB sqlite DB (and sqlite can be incredibly fast), on an SSD with >5GB/s read speeds, with 64GB of ram (where the whole sqlite can/should be loaded into memory), to render a blue bubble with some text inside.
What is Messages doing, and why is it so bad and inefficient. I'm certain the local sqlite db has the data I need because the above bothered me so much i wrote some scripts to read my historical chats from the cli. Quality of Apple software is certainly not increasing but going downhill, you see that across many of their apps.
With iMessage it loads for seconds every 20 messages or so. I also really don't get it.
Incredibly frustrating and slow, but not new.
The US phased out their SMS fees about the same time smart phones started to become popular. I think it was only due to regulation.
In many countries carriers did offer unlimited sms plans, but they would cost a significant amount more over base plans which would say include 100 free texts a month. Also until EU regulations hit if you had any family or friends in other countries or ever went on holiday the cost of SMS was astronomical.
It took many carriers awhile to realize that "free" messaging apps were a significant threat, I assume because they were also charging heavily for data and just assumed because these used data they would make just as much money. Ignoring things like WiFi and how in the long run these apps would use such a small % of the data a user demanded they would be effectively free.
Here's a contemporary article (2008) about the "Rising Cost of Texting" (voice+data+200 SMS/month for $75): https://www.cnet.com/culture/the-rising-cost-of-texting/
I know a lot of people that cringe when one non-iPhone user is added to a group chat and it turns everything you send green instead of blue.
Signal doesn't keep logs, and is end-to-end encrypted without a key escrow or plaintext escrow backdoor for the transiting middle service. You can review their actual response documents to law enforcement.
Encryption, like deadbolts, helps society function.
so now i have to drop $700 on an iphone for a text messaging app. fucking ridiculous, but i want better communication with my friends without telling them what to do
For the bigger groups, or groups with older people, we use WhatsApp since there might be an Android user or less risk of having to exclude someone because they switched to Android.
Edit: it seems like the way to ensure sharing photos/video at original quality in iOS/macOS is to select the media in photos app, and then share, and then select “copy iCloud link”.
> When shared, photos are reduced to 2048 pixels on the long edge, except panoramic photos, which can be up to 5400 pixels wide. You can share GIFs that are 100MB or smaller.
I do not see a material advantage of iMessage over WhatsApp now.
1. iMessage interacts with SMS just like normal
2. It gives you the "blue bubble" which is a sign of higher class, because you have the more expensive iphone platform.
But if someone messages me from iMessage, I can only reply via old school SMS/MMS because Apple won't allow any non-Apple users access to their IM platform. This is incredibly annoying and well established as an intentional selling point.
All I hear is "if you want me to really listen to you, then you need to buy a specific brand of device."
A "green bubble person" is just someone forced to use crappy fallback measures because your preferred messaging platform wants to be exclusive. I'd gladly install iMessage (as I've installed many other IM apps) if it was an option, if only so I could stop the endless complaints about "green bubble people" ruining everyone's day.
All my friends on Telegram have iPhones and Android. Oh, and Telegram is objectively a better experience from a UI and UX standpoint than iMessage. And it's more reliable and much faster when sending images or videos, and does not randomly convert my links into obnoxious previews. I don't mind spending ten seconds installing the app _and_ making an account (since that's literally how long it takes) to get that experience.
If only it was fully e2e encrypted, but maybe that's why it's so frictionless. OTOH, e2e encryption is weak when you have groups, as anyone can enter public groups, and a compromised participant in a private group can read everyone's messages.
iMessage doesn't interact with SMS just like normal — the Messages app does. It just switches between SMS and iMessage based on the recipient.
(surprising facts about the people) you text
Vs
surprising facts about (the people you text)
While i don't use iMessage and therefore have no intention of doing this myself, I find exploring on-device sqlite pretty interesting and enjoyed this read. Doesn't seem at all like click bait
Mine gets bloated with images and videos to multi gigabytes in days, and it's unlikely that it's used only for text for 13 years. (I mean, it's either never used at all, or used for all sorts of media not just text, and since it has been used, it should be the latter)
Even if the actual media isn't stored in the database, all the metadata for all those messages should exceed 1GB IMO.
https://www.macrumors.com/how-to/auto-delete-old-messages-ip...
Anecdatum: My `chat.db` on this laptop is 44MB for 30863 messages going back just under 4 years (basically just a handful of family.) I can see that being 1GB even for triple the length of time and many more interactions, sure.
The `Attachments` folder next to it, on the other hand, is 6.4GB.
This seems to be where you went wrong.
Thanks for the clarification.
The writeup makes me wonder what other Apple software uses SQLite under the hood. In particular if the iTunes library is backed by a SQLite file, I could actually use that for some future projects. Cursory inspection shows that it's hdf-compressed so I'll need to get past that first... anyway, nice task for after-work hours.
If you have `person` and `address`:
create table person (
id serial primary key,
first_name text not null,
last_name text not null,
date_of_birth date not null
);
create table address (
id serial primary key,
person_id integer not null references person (id),
street_number text not null,
apartment_number text,
city text not null,
state text,
postal_code text not null
);
If we've got:
insert into person (first_name, last_name, date_of_birth)
values ('Brian', 'Kernighan', '1950-01-01'), -- gets id 1
('Dennis', 'Ritchie', '1948-01-01'); -- gets id 2
insert into address (person_id, street_number, city, postal_code)
values (1, '123 Main St.', 'Berkeley', '94701');
-- an inner join, only returns `person` rows with a corresponding
-- row in the `address` table
select person.first_name,
person.last_name,
address.street_number,
address.city,
address.postal_code
from person
inner join address on person.id = address.person_id
-- output:
-- first_name | last_name | street_number | city | postal_code
-- Brian | Kernighan | 123 Main St. | Berkeley | 94701
-- a left outer join, returns all `person` rows, regardless of
-- the existence of a corresponding row in the `address` table
select person.first_name,
person.last_name,
address.street_number,
address.city,
address.postal_code
from person
left outer join address on person.id = address.person_id
-- output:
-- first_name | last_name | street_number | city | postal_code
-- Brian | Kernighan | 123 Main St. | Berkeley | 94701
-- Dennis | Ritchie | | |
There are other types of joins, but these two are the usual suspects in most queries.
What you experienced sounds worrying though, I wouldn't want to lose that db on a potential computer upgrade. Do you have any more details on it or what you think happened?
I want an app that will prowl my pool of SMS texts on my phone every couple of minutes and delete every SMS text that matches a regexp I give it, without sending all of my texts off-device.
I made the mistake of giving my real phone number to an organization giving a relative an award 8 years ago. (Had to buy tickets) I still get SMS spam as a result.
Voter registration lists are public, your phone number is not.
I get texts from Trumpsters because I worked at a place with a public sector union and some ass gave the GAL to a anti-union group, which subsequently sold it to some other groups.
Unfortunately there doesn’t seem to be any solution for MMS image spam from politicians that follows the same pattern of fake numbers and not honoring a “STOP” response.
select
h.id as sender_name,
m.text as message_body
from
message m
join handle h on h.rowid = m.handle_id
order by
m.date;[0]: https://www.nowsecure.com/blog/2021/01/27/reverse-engineerin...
The only reasonable usecase I can identify are people writing iMessage bots/ spammers but those groups most likely wouldn't want to open-source their stuff.