Join contacts to all core tables

[left-on-read]

Join contacts to all core tables

[alexdanilowicz]

Instead of having the user export a .vcf, we are likely going to go with reading their populated *.abcddb file instead.

[alexdanilowicz]

Going to work on this next, starting tomorrow.

[alexdanilowicz]

Posting this here because I have nowhere else to put it.

Implementation flow:

• 1) copy the AddressBook.db file to the .leftonread folder
• 2) loop over all possible the AddressBook.db files and find the one that is populated
• 3) if none are populated, you don't have to skip (6) because of the coalesce usage in 8... so just continue onward

4. with the populated one, open it, and ATTACH it to the existing chat.db database connection

• If successfully attached, you should be able to do the following:

• -- UPDATE 4.5) Create the contacts table. See query below.

-- 5) NOTE: add this to a try/catch. If it fails, then that means the column exists so just skip to 6
ALTER TABLE handle ADD contact_name VARCHAR(255);

-- 6)
UPDATE handle 
SET contact_name = (SELECT contacts_table.contact_name FROM contacts_table WHERE handle.id = contacts_table.contact_phone);

-- 7) Close the addressbook DB connection now

-- 8)
-- downstream usage in core table
SELECT coalesce(contact_name, handle.id) as contact FROM handle

The whole idea is to alter the existing chat.db handle table directly by adding a contact_name column to it directly.

PROS:

• We worry about this one on initializing and then never again
• We don't have to worry about JOINs down the road
• We simply reference the handle table every time and use a coalesce. You could probs get around the coalesce if I gave this more thought.
• We can close the addressbook.db file immediately

CONS:

• contacts isn't its own table... which would make more sense logically?

[alexdanilowicz]

This coming along nicely. For posterity's sake, the query for this will be:

CREATE TABLE CONTACTS_TABLE AS
WITH CONTACTS_CLEAN_TABLE AS (
SELECT 
	ZABCDRECORD.Z_PK, 
	ZABCDRECORD.ZFIRSTNAME,
	ZABCDRECORD.ZLASTNAME, 
	-- TODO: I understand this is ugly... use regex.
	replace(replace(replace(replace(replace(replace(ZABCDPHONENUMBER.ZFULLNUMBER, "(", ""), ")", ""), "_", ""), "-", ""), " ", ""), "+", "") as ZFULLNUMBER
FROM ZABCDRECORD
LEFT JOIN ZABCDPHONENUMBER ON ZABCDPHONENUMBER.ZOWNER = ZABCDRECORD.Z_PK
WHERE ZABCDPHONENUMBER.ZFULLNUMBER IS NOT NULL
)
--- NOTE: in the chat.db the format is +1619... in other words
--- chat.db lists the country code always. The addressbook.db, however, does not always include the country code.
--- Therefore, we remove the + and the country code, and FILTER on the 10-digit length.
--- All phone numbers in the US/Canada are 10-digit... this assumption may not work as well for users in other countries
SELECT 
ZFIRSTNAME || " " || ZLASTNAME  as contact_name,
CASE WHEN LENGTH(ZFULLNUMBER) > 10
	THEN SUBSTR(ZFULLNUMBER, -10, 10)
	ELSE ZFULLNUMBER
END AS contact_phone
FROM CONTACTS_CLEAN_TABLE