For reference, here is the Python code used to generate the above file:
import sqlite3
from datetime import datetime, timedelta
def create_diary_file(filename):
# Connect to SQLite database (it will create the file if it doesn't exist)
conn = sqlite3.connect(filename)
cursor = conn.cursor()
# Create the required tables
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS Entries (
DiaryEntryId BIGINT PRIMARY KEY NOT NULL,
Heading VARCHAR NOT NULL,
Text VARCHAR NOT NULL,
Rating INTEGER NOT NULL,
Latitude FLOAT NOT NULL,
Longitude FLOAT NOT NULL
);
"""
)
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS EntryTags (
DiaryEntryId BIGINT NOT NULL,
DiaryTagId INTEGER NOT NULL,
TrackingValue VARCHAR
);
"""
)
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS Events (
DiaryEventId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Type INTEGER NOT NULL,
Content VARCHAR NOT NULL,
Tag VARCHAR,
DiaryEntryId BIGINT NOT NULL
);
"""
)
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS Media (
DiaryMediaId VARCHAR(36) PRIMARY KEY NOT NULL,
Type INTEGER NOT NULL,
Data BLOB,
Name VARCHAR NOT NULL,
FileEnding VARCHAR NOT NULL,
"Index" INTEGER NOT NULL,
DiaryEntryId BIGINT NOT NULL
);
"""
)
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS Tags (
DiaryTagId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Value VARCHAR NOT NULL,
Type INTEGER NOT NULL,
Color VARCHAR,
Icon VARCHAR
);
"""
)
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS Templates (
TemplateId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name VARCHAR NOT NULL,
Heading VARCHAR NOT NULL,
Text VARCHAR NOT NULL,
Tags VARCHAR NOT NULL
);
"""
)
# Create the required indices
cursor.execute(
"CREATE INDEX IF NOT EXISTS Events_DiaryEntryId ON Events(DiaryEntryId);"
)
cursor.execute(
"CREATE INDEX IF NOT EXISTS IX_EntryTags ON EntryTags(DiaryEntryId, DiaryTagId);"
)
cursor.execute(
'CREATE INDEX IF NOT EXISTS IX_Media_2 ON Media(DiaryEntryId, Type, "Index", DiaryMediaId, Name, FileEnding);'
)
# Commit changes and close connection
conn.commit()
conn.close()
def datetime_to_dotnet_ticks(dt):
# .NET ticks are based on 1 Jan 0001, so we need to calculate the difference
# between the given datetime and that epoch.
dotnet_epoch = datetime(1, 1, 1)
delta = dt - dotnet_epoch
# .NET ticks are 100-nanosecond intervals, so multiply seconds by 10^7 and add microseconds
ticks = (
delta.days * 864000000000 + delta.seconds * 10000000 + delta.microseconds * 10
)
return ticks
def insert_entries(filename, entries):
# Connect to the SQLite database
conn = sqlite3.connect(filename)
cursor = conn.cursor()
# Step 1: Extract unique tags from all entries
unique_tags = set()
for entry in entries:
unique_tags.update(entry["tags"]) # Add all tags from this entry to the set
# Step 2: Insert unique tags into the Tags table
tag_to_id = {} # Mapping from tag name to DiaryTagId
for tag in unique_tags:
cursor.execute(
"""
INSERT INTO Tags (Value, Type)
VALUES (?, ?)
""",
(tag, 0),
) # Type is 0 for all tags
tag_id = cursor.lastrowid # Get the generated DiaryTagId
tag_to_id[tag] = tag_id # Map tag to its DiaryTagId
# Step 3: Prepare data for inserting entries into the Entries table
data_to_insert = []
entry_tag_links = [] # To store the links between DiaryEntryId and DiaryTagId
for entry in entries:
timestamp_ticks = datetime_to_dotnet_ticks(entry["timestamp"])
text = entry["text"]
heading = "" # Empty string for Heading
rating = 0 # Default Rating
latitude = 0.0 # Default Latitude
longitude = 0.0 # Default Longitude
# Insert the entry data into the Entries table
data_to_insert.append(
(timestamp_ticks, heading, text, rating, latitude, longitude)
)
# After insertion, we will need to map the DiaryEntryId to the tags
entry_tag_links.append(
(timestamp_ticks, entry["tags"])
) # Store the tags for this entry
# Perform the bulk insert for entries
cursor.executemany(
"""
INSERT INTO Entries (DiaryEntryId, Heading, Text, Rating, Latitude, Longitude)
VALUES (?, ?, ?, ?, ?, ?)
""",
data_to_insert,
)
# Step 4: Insert the links between DiaryEntryId and DiaryTagId into EntryTags
entry_tag_data = []
for diary_entry_id, tags in entry_tag_links:
for tag in tags:
tag_id = tag_to_id[tag] # Get the corresponding DiaryTagId
entry_tag_data.append(
(diary_entry_id, tag_id, None)
) # TrackingValue is not provided
# Insert the links in bulk
cursor.executemany(
"""
INSERT INTO EntryTags (DiaryEntryId, DiaryTagId, TrackingValue)
VALUES (?, ?, ?)
""",
entry_tag_data,
)
# Commit the transaction and close the connection
conn.commit()
conn.close()
# Example usage
entries = [
{
"timestamp": datetime.now(),
"text": "First diary entry",
"tags": ["inspiration", "health", "work"],
},
{
"timestamp": datetime.now() + timedelta(days=1),
"text": "Second diary entry",
"tags": ["motivation", "work"],
},
# Add more entries here
]
# Example usage
create_diary_file("diary.db")
insert_entries("diary.db", entries)
