db = dbFile.sqlite()
data = parseQuery(
await db.query(`SELECT
m.rowid as message_id,
m.text,
datetime(substr(date, 1, 9) + 978307200, 'unixepoch', 'localtime') as date, --MacTime
m.is_from_me, --1 if you sent, 2 if received
c.chat_identifier,
c.room_name, --for group chats
c.display_name, --for group chats
h.id as number
FROM message m
LEFT JOIN chat_message_join j ON j.message_id = m.rowid
LEFT JOIN chat c ON c.rowid = j.chat_id
LEFT JOIN handle h ON h.rowid = m.handle_id`)
)
parseQuery = data =>
data
.map(d => {
const normalizedNumber = normalizePhoneNumber(d.chat_identifier || "");
const chat = d.room_name
? d.display_name || d.chat_identifier
: nameMap.get(normalizedNumber) || d.chat_identifier;
return {
...d,
date: new Date(d.date.replace('T', ' ')),
chat
};
})
.sort((a, b) => d3.ascending(a.date, b.date))
groupChatMembers = d3.rollup(
data.filter(d => d.room_name && !d.is_from_me),
arr =>
Array.from(new Set(arr.map(d => d.number)))
.filter(d => d)
.map(maybeNormalizePhoneNumber)
.map(number => nameMap.get(number) || number),
d => d.chat_identifier
)
chats = d3
.groupSort(
data,
arr => -d3.sum(arr, d => d.text?.length || 0),
d => d.chat
)
.filter(d => d)
dateDomain = [startDate, endDate]
.map(d3.utcParse("%Y-%m-%d"))
.map(d3.utcDay.floor)
contacts = [contactsFile].filter(d => d)
nameMap = new Map(
(
await Promise.all(
contacts.map(async (file) => {
const extension = file.name.toLowerCase().split(".")[1];
if (extension === "vcf")
return getNumbersVCF(parseVCF(await file.text()));
if (extension === "csv") return getNumbersCSV(await file.csv());
})
)
).flat(1)
)
getNumbersVCF = arr =>
arr.flatMap(contact => {
const tel = contact.telephone
? contact.telephone.map(d => [
normalizePhoneNumber(d.value),
contact.displayName
])
: [];
const email = contact.email
? contact.email.map(d => [d.value, contact.displayName])
: [];
return [...tel, ...email];
})
getNumbersCSV = (arr) => {
const fields = [
"Phone 1 - Value",
"Phone 2 - Value",
"Phone 3 - Value",
"Phone 4 - Value"
];
return arr.flatMap((contact) =>
fields
.map((f) => contact[f])
.filter((d) => d)
.map(normalizePhoneNumber)
.map((number) => [number, contact.Name])
);
}
normalizePhoneNumber = (d) => {
d = d.replace(/[().+-\s]/g, "");
return d.length === 10 ? `1${d}` : d;
}
// leaves email addresses alone when you can count on numbers to begin with `+`
maybeNormalizePhoneNumber = d =>
d ? (d[0] === "+" ? normalizePhoneNumber(d) : d) : d
import { parse as parseVCF } from "@tophtucker/vcard-parser"
youGray = "#E5E5EA"
meBlue = "#4E97F1"
msgGroupStyles = ({ borderTop: "1px solid #eee", paddingBottom: "1rem" })
msgContainerStyles = ({
border: "1px solid #ccc",
borderRadius: "1em",
maxHeight: "30rem",
overflow: "scroll",
fontFamily: "var(--sans-serif)",
fontSize: "0.7rem",
padding: "1rem",
color: "gray"
})
filteredMessages = data.filter((d) => d.chat === chat)
chatChunks = {
const chunks = [];
let batch = [];
let lastDate;
for (const m of filteredMessages) {
if (lastDate && d3.timeMinute.count(lastDate, m.date) > maxGap) {
chunks.push(batch);
batch = [];
}
batch.push(m);
lastDate = m.date;
}
if (batch.length) chunks.push(batch);
return chunks;
}
renderMessage = (m, { showName = false } = {}) => htl.html`<div style=${{
fontFamily: "var(--sans-serif)",
fontSize: "0.8rem",
display: "flex",
width: "100%",
justifyContent: "space-between"
}}>
<div style=${{
paddingBottom: "3px",
width: "70%",
textAlign: m.is_from_me ? "right" : "left"
}}>
${
(m.room_name || showName) && !m.is_from_me
? htl.html`<div style=${{
fontSize: "0.7rem",
textIndent: "0.71rem"
}}>${nameMap.get(maybeNormalizePhoneNumber(m.number))}</div>`
: ""
}
<div style=${{
display: "inline-block",
borderRadius: "1rem",
padding: "0.4rem 0.6rem",
background: m.is_from_me ? meBlue : youGray,
color: m.is_from_me ? "white" : "black",
maxWidth: "20em",
overflow: "hidden",
whiteSpace: "pre-wrap",
textAlign: "left"
}}>${m.text}</div>
</div>
<div style=${{ fontSize: "0.7rem" }}>
${m.date.toLocaleDateString("en-US")}
${m.date.toLocaleTimeString("en-US")}
</div>
</div>`
rankingsData = {
const interval = d3.utcWeek
const range = interval.range(...d3.extent(data, d => d.date))
return d3
.rollups(
data.filter(d => chats.slice(0, 6).includes(d.chat)),
arr => d3.sum(arr, d => d.text?.length),
d => d.chat,
d => interval.floor(d.date)
)
.flatMap(
([chat, arr]) => range.map(
(date) => {
const val = arr.find(d => +d[0] === +date);
return ({chat, date, value: val ? val[1] : 0})
}
)
)
}
bookmarksFile = `<!DOCTYPE NETSCAPE-Bookmark-file-1>
<!--This is an automatically generated file.
It will be read and overwritten.
Do Not Edit! -->
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-8">
<Title>Bookmarks</Title>
<H1>Bookmarks</H1>
<DL><p>
${groupedLinks.map(([name, links]) => ` <DT><H3>${name}</H3>
<DL><p>
${links.map(({link}) => ` <DT><a href="${link}">${link.split("://")[1]}</a>`).join("\n")}
</DL>
`)}
</DL><p>`
links = data
.filter((d) => d.text?.match(linkRegex))
.map(d => {
const link = d.text.match(linkRegex)[0];
return {
...d,
link,
text: htl.html`<a href=${link} style="color: inherit;">${link}</a>`
}
})
groupedLinks = d3.groups(links, d => d.chat).sort((a,b) => b[1].length - a[1].length)
// https://stackoverflow.com/questions/6038061/regular-expression-to-find-urls-within-a-string
linkRegex = /(http|ftp|https):\/\/([\w_-]+(?:(?:\.[\w_-]+)+))([\w.,@?^=%&:\/~+#-]*[\w@?^=%&\/~+#-])/
import { key } from "@observablehq/introducing-visual-dataflow"
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
