Comparing xklb and SQLAlchemy generated databases

[deldesir]

I downloaded the playlist Think like a coder twice.

In the first try, I used SQLAlchemy ORM to generate the database before the download

• Database generated using SQLAlchemy: https://dbhub.io/deldesir/sqlalchemy-generated.db

In the second try, I let xklb generated it:

• Database generated by xklb's pure SQL: https://dbhub.io/deldesir/xklb-generated.db

[deldesir]

Both schemas for references (click to expand):

sqlalchemy-generated.db schema

CREATE TABLE playlists (
        id INTEGER NOT NULL,
        time_modified INTEGER,
        time_deleted INTEGER,
        time_created INTEGER,
        hours_update_delay INTEGER,
        path TEXT,
        extractor_key TEXT,
        profile TEXT,
        extractor_config TEXT,
        extractor_playlist_id TEXT,
        title TEXT,
        uploader TEXT,
        PRIMARY KEY (id)
);
CREATE TABLE captions (
        rowid INTEGER NOT NULL,
        media_id INTEGER,
        time INTEGER,
        text TEXT,
        PRIMARY KEY (rowid),
        FOREIGN KEY(media_id) REFERENCES media (id)
);
CREATE TABLE IF NOT EXISTS 'playlists_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'playlists_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'playlists_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'playlists_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS "media" (
   [id] INTEGER PRIMARY KEY NOT NULL,
   [time_deleted] INTEGER,
   [playlists_id] INTEGER,
   [size] INTEGER,
   [duration] INTEGER,
   [time_created] INTEGER,
   [time_modified] INTEGER,
   [time_downloaded] INTEGER,
   [fps] INTEGER,
   [view_count] INTEGER,
   [time_uploaded] INTEGER,
   [width] INTEGER,
   [height] INTEGER,
   [video_count] INTEGER,
   [audio_count] INTEGER,
   [chapter_count] INTEGER,
   [other_count] INTEGER,
   [subtitle_count] INTEGER,
   [download_attempts] INTEGER,
   [path] TEXT,
   [webpath] TEXT,
   [extractor_id] TEXT,
   [title] TEXT,
   [uploader] TEXT,
   [live_status] TEXT,
   [type] TEXT,
   [video_codecs] TEXT,
   [audio_codecs] TEXT,
   [subtitle_codecs] TEXT,
   [other_codecs] TEXT,
   [language] TEXT,
   [error] TEXT
);
CREATE TABLE IF NOT EXISTS 'media_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'media_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'media_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'media_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'captions_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'captions_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'captions_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'captions_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE UNIQUE INDEX idx_playlists_path ON playlists (path);
CREATE INDEX [idx_playlists_id]
    ON [playlists] ([id]);
CREATE INDEX [idx_playlists_time_modified]
    ON [playlists] ([time_modified]);
CREATE INDEX [idx_playlists_time_deleted]
    ON [playlists] ([time_deleted]);
CREATE INDEX [idx_playlists_time_created]
    ON [playlists] ([time_created]);
CREATE INDEX [idx_playlists_hours_update_delay]
    ON [playlists] ([hours_update_delay]);
CREATE INDEX [idx_playlists_extractor_key]
    ON [playlists] ([extractor_key]);
CREATE INDEX [idx_playlists_profile]
    ON [playlists] ([profile]);
CREATE INDEX [idx_playlists_uploader]
    ON [playlists] ([uploader]);
CREATE INDEX [idx_playlists_extractor_config]
    ON [playlists] ([extractor_config]);
CREATE INDEX [idx_media_id]
    ON [media] ([id]);
CREATE INDEX [idx_media_time_deleted]
    ON [media] ([time_deleted]);
CREATE INDEX [idx_media_playlists_id]
    ON [media] ([playlists_id]);
CREATE INDEX [idx_media_size]
    ON [media] ([size]);
CREATE INDEX [idx_media_duration]
    ON [media] ([duration]);
CREATE INDEX [idx_media_time_created]
    ON [media] ([time_created]);
CREATE INDEX [idx_media_time_modified]
    ON [media] ([time_modified]);
CREATE INDEX [idx_media_time_downloaded]
    ON [media] ([time_downloaded]);
CREATE INDEX [idx_media_fps]
    ON [media] ([fps]);
CREATE INDEX [idx_media_view_count]
    ON [media] ([view_count]);
CREATE INDEX [idx_media_time_uploaded]
    ON [media] ([time_uploaded]);
CREATE INDEX [idx_media_width]
    ON [media] ([width]);
CREATE INDEX [idx_media_height]
    ON [media] ([height]);
CREATE INDEX [idx_media_video_count]
    ON [media] ([video_count]);
CREATE INDEX [idx_media_audio_count]
    ON [media] ([audio_count]);
CREATE INDEX [idx_media_chapter_count]
    ON [media] ([chapter_count]);
CREATE INDEX [idx_media_other_count]
    ON [media] ([other_count]);
CREATE INDEX [idx_media_subtitle_count]
    ON [media] ([subtitle_count]);
CREATE INDEX [idx_media_download_attempts]
    ON [media] ([download_attempts]);
CREATE UNIQUE INDEX [idx_media_path]
    ON [media] ([path]);
CREATE INDEX [idx_media_live_status]
    ON [media] ([live_status]);
CREATE INDEX [idx_media_language]
    ON [media] ([language]);
CREATE INDEX [idx_media_type]
    ON [media] ([type]);
CREATE INDEX [idx_media_error]
    ON [media] ([error]);
CREATE INDEX [idx_media_uploader]
    ON [media] ([uploader]);
CREATE INDEX [idx_media_subtitle_codecs]
    ON [media] ([subtitle_codecs]);
CREATE INDEX [idx_media_other_codecs]
    ON [media] ([other_codecs]);
CREATE INDEX [idx_media_video_codecs]
    ON [media] ([video_codecs]);
CREATE INDEX [idx_media_audio_codecs]
    ON [media] ([audio_codecs]);
CREATE INDEX [idx_captions_rowid]
    ON [captions] ([rowid]);
CREATE INDEX [idx_captions_media_id]
    ON [captions] ([media_id]);
CREATE INDEX [idx_captions_time]
    ON [captions] ([time]);
CREATE VIRTUAL TABLE [playlists_fts] USING FTS5 (
    [path], [title],
    tokenize='trigram',
    content=[playlists]
);
CREATE TRIGGER [playlists_ai] AFTER INSERT ON [playlists] BEGIN
  INSERT INTO [playlists_fts] (rowid, [path], [title]) VALUES (new.rowid, new.[path], new.[title]);
END;
CREATE TRIGGER [playlists_ad] AFTER DELETE ON [playlists] BEGIN
  INSERT INTO [playlists_fts] ([playlists_fts], rowid, [path], [title]) VALUES('delete', old.rowid, old.[path], old.[title]);
END;
CREATE TRIGGER [playlists_au] AFTER UPDATE ON [playlists] BEGIN
  INSERT INTO [playlists_fts] ([playlists_fts], rowid, [path], [title]) VALUES('delete', old.rowid, old.[path], old.[title]);
  INSERT INTO [playlists_fts] (rowid, [path], [title]) VALUES (new.rowid, new.[path], new.[title]);
END;
CREATE VIRTUAL TABLE [media_fts] USING FTS5 (
    [path], [webpath], [title],
    tokenize='trigram',
    content=[media]
);
CREATE TRIGGER [media_ai] AFTER INSERT ON [media] BEGIN
  INSERT INTO [media_fts] (rowid, [path], [webpath], [title]) VALUES (new.rowid, new.[path], new.[webpath], new.[title]);
END;
CREATE TRIGGER [media_ad] AFTER DELETE ON [media] BEGIN
  INSERT INTO [media_fts] ([media_fts], rowid, [path], [webpath], [title]) VALUES('delete', old.rowid, old.[path], old.[webpath], old.[title]);
END;
CREATE TRIGGER [media_au] AFTER UPDATE ON [media] BEGIN
  INSERT INTO [media_fts] ([media_fts], rowid, [path], [webpath], [title]) VALUES('delete', old.rowid, old.[path], old.[webpath], old.[title]);
  INSERT INTO [media_fts] (rowid, [path], [webpath], [title]) VALUES (new.rowid, new.[path], new.[webpath], new.[title]);
END;
CREATE VIRTUAL TABLE [captions_fts] USING FTS5 (
    [text],
    tokenize='trigram',
    content=[captions]
);
CREATE TRIGGER [captions_ai] AFTER INSERT ON [captions] BEGIN
  INSERT INTO [captions_fts] (rowid, [text]) VALUES (new.rowid, new.[text]);
END;
CREATE TRIGGER [captions_ad] AFTER DELETE ON [captions] BEGIN
  INSERT INTO [captions_fts] ([captions_fts], rowid, [text]) VALUES('delete', old.rowid, old.[text]);
END;
CREATE TRIGGER [captions_au] AFTER UPDATE ON [captions] BEGIN
  INSERT INTO [captions_fts] ([captions_fts], rowid, [text]) VALUES('delete', old.rowid, old.[text]);
  INSERT INTO [captions_fts] (rowid, [text]) VALUES (new.rowid, new.[text]);
END;
ANALYZE sqlite_schema;
INSERT INTO sqlite_stat1 VALUES('captions_fts_data',NULL,'2');
INSERT INTO sqlite_stat1 VALUES('media_fts_docsize',NULL,'11');
INSERT INTO sqlite_stat1 VALUES('media_fts_idx','media_fts_idx','2 2 1');
INSERT INTO sqlite_stat1 VALUES('media_fts_config','media_fts_config','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_extractor_config','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_uploader','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_profile','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_extractor_key','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_hours_update_delay','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_time_created','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_time_deleted','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_time_modified','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_id','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_path','1 1');
INSERT INTO sqlite_stat1 VALUES('captions_fts_config','captions_fts_config','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists_fts_data',NULL,'3');
INSERT INTO sqlite_stat1 VALUES('playlists_fts_idx','playlists_fts_idx','1 1 1');
INSERT INTO sqlite_stat1 VALUES('media_fts_data',NULL,'4');
INSERT INTO sqlite_stat1 VALUES('playlists_fts_docsize',NULL,'1');
INSERT INTO sqlite_stat1 VALUES('playlists_fts_config','playlists_fts_config','1 1');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_audio_codecs','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_video_codecs','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_other_codecs','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_subtitle_codecs','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_uploader','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_error','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_type','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_language','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_live_status','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_path','11 1');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_download_attempts','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_subtitle_count','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_other_count','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_chapter_count','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_audio_count','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_video_count','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_height','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_width','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_time_uploaded','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_view_count','11 1');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_fps','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_time_downloaded','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_time_modified','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_time_created','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_duration','11 1');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_size','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_playlists_id','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_time_deleted','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_id','11 1');
ANALYZE sqlite_schema;

xklb-generated.db schema

CREATE TABLE IF NOT EXISTS "playlists" (
   [id] INTEGER PRIMARY KEY,
   [time_modified] INTEGER,
   [time_deleted] INTEGER,
   [time_created] INTEGER,
   [hours_update_delay] INTEGER,
   [path] TEXT,
   [extractor_key] TEXT,
   [profile] TEXT,
   [extractor_config] TEXT,
   [extractor_playlist_id] TEXT,
   [title] TEXT,
   [uploader] TEXT
);
CREATE TABLE IF NOT EXISTS 'playlists_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'playlists_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'playlists_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'playlists_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS "media" (
   [id] INTEGER PRIMARY KEY,
   [playlists_id] INTEGER,
   [size] INTEGER,
   [duration] INTEGER,
   [time_created] INTEGER,
   [time_modified] INTEGER,
   [time_deleted] INTEGER,
   [time_downloaded] INTEGER,
   [fps] INTEGER,
   [view_count] INTEGER,
   [path] TEXT,
   [webpath] TEXT,
   [extractor_id] TEXT,
   [title] TEXT,
   [uploader] TEXT
, [time_uploaded] INTEGER, [width] INTEGER, [height] INTEGER, [live_status] TEXT, [type] TEXT, [video_codecs] TEXT, [audio_codecs] TEXT, [subtitle_codecs] TEXT, [other_codecs] TEXT, [video_count] INTEGER, [audio_count] INTEGER, [chapter_count] INTEGER, [other_count] INTEGER, [language] TEXT, [subtitle_count] INTEGER, [download_attempts] INTEGER, [error] TEXT);
CREATE TABLE IF NOT EXISTS 'media_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'media_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'media_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'media_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE INDEX [idx_playlists_id]
    ON [playlists] ([id]);
CREATE INDEX [idx_playlists_time_modified]
    ON [playlists] ([time_modified]);
CREATE INDEX [idx_playlists_time_deleted]
    ON [playlists] ([time_deleted]);
CREATE INDEX [idx_playlists_time_created]
    ON [playlists] ([time_created]);
CREATE INDEX [idx_playlists_hours_update_delay]
    ON [playlists] ([hours_update_delay]);
CREATE INDEX [idx_playlists_uploader]
    ON [playlists] ([uploader]);
CREATE INDEX [idx_playlists_profile]
    ON [playlists] ([profile]);
CREATE UNIQUE INDEX [idx_playlists_path]
    ON [playlists] ([path]);
CREATE INDEX [idx_playlists_extractor_key]
    ON [playlists] ([extractor_key]);
CREATE INDEX [idx_playlists_extractor_config]
    ON [playlists] ([extractor_config]);
CREATE INDEX [idx_media_id]
    ON [media] ([id]);
CREATE INDEX [idx_media_playlists_id]
    ON [media] ([playlists_id]);
CREATE INDEX [idx_media_size]
    ON [media] ([size]);
CREATE INDEX [idx_media_duration]
    ON [media] ([duration]);
CREATE INDEX [idx_media_time_created]
    ON [media] ([time_created]);
CREATE INDEX [idx_media_time_modified]
    ON [media] ([time_modified]);
CREATE INDEX [idx_media_time_deleted]
    ON [media] ([time_deleted]);
CREATE INDEX [idx_media_time_downloaded]
    ON [media] ([time_downloaded]);
CREATE INDEX [idx_media_fps]
    ON [media] ([fps]);
CREATE INDEX [idx_media_view_count]
    ON [media] ([view_count]);
CREATE INDEX [idx_media_uploader]
    ON [media] ([uploader]);
CREATE UNIQUE INDEX [idx_media_path]
    ON [media] ([path]);
CREATE VIRTUAL TABLE [playlists_fts] USING FTS5 (
    [path], [title],
    tokenize='trigram',
    content=[playlists]
);
CREATE TRIGGER [playlists_ai] AFTER INSERT ON [playlists] BEGIN
  INSERT INTO [playlists_fts] (rowid, [path], [title]) VALUES (new.rowid, new.[path], new.[title]);
END;
CREATE TRIGGER [playlists_ad] AFTER DELETE ON [playlists] BEGIN
  INSERT INTO [playlists_fts] ([playlists_fts], rowid, [path], [title]) VALUES('delete', old.rowid, old.[path], old.[title]);
END;
CREATE TRIGGER [playlists_au] AFTER UPDATE ON [playlists] BEGIN
  INSERT INTO [playlists_fts] ([playlists_fts], rowid, [path], [title]) VALUES('delete', old.rowid, old.[path], old.[title]);
  INSERT INTO [playlists_fts] (rowid, [path], [title]) VALUES (new.rowid, new.[path], new.[title]);
END;
CREATE VIRTUAL TABLE [media_fts] USING FTS5 (
    [path], [webpath], [title],
    tokenize='trigram',
    content=[media]
);
CREATE TRIGGER [media_ai] AFTER INSERT ON [media] BEGIN
  INSERT INTO [media_fts] (rowid, [path], [webpath], [title]) VALUES (new.rowid, new.[path], new.[webpath], new.[title]);
END;
CREATE TRIGGER [media_ad] AFTER DELETE ON [media] BEGIN
  INSERT INTO [media_fts] ([media_fts], rowid, [path], [webpath], [title]) VALUES('delete', old.rowid, old.[path], old.[webpath], old.[title]);
END;
CREATE TRIGGER [media_au] AFTER UPDATE ON [media] BEGIN
  INSERT INTO [media_fts] ([media_fts], rowid, [path], [webpath], [title]) VALUES('delete', old.rowid, old.[path], old.[webpath], old.[title]);
  INSERT INTO [media_fts] (rowid, [path], [webpath], [title]) VALUES (new.rowid, new.[path], new.[webpath], new.[title]);
END;
CREATE TABLE [captions] (
   [media_id] INTEGER,
   [time] INTEGER,
   [text] TEXT
);
ANALYZE sqlite_schema;
INSERT INTO sqlite_stat1 VALUES('media_fts_docsize',NULL,'11');
INSERT INTO sqlite_stat1 VALUES('media_fts_idx','media_fts_idx','2 2 1');
INSERT INTO sqlite_stat1 VALUES('media_fts_config','media_fts_config','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_extractor_config','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_extractor_key','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_path','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_profile','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_uploader','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_hours_update_delay','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_time_created','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_time_deleted','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_time_modified','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists','idx_playlists_id','1 1');
INSERT INTO sqlite_stat1 VALUES('playlists_fts_data',NULL,'3');
INSERT INTO sqlite_stat1 VALUES('playlists_fts_idx','playlists_fts_idx','1 1 1');
INSERT INTO sqlite_stat1 VALUES('playlists_fts_config','playlists_fts_config','1 1');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_path','11 1');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_uploader','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_view_count','11 1');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_fps','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_time_downloaded','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_time_deleted','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_time_modified','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_time_created','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_duration','11 1');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_size','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_playlists_id','11 11');
INSERT INTO sqlite_stat1 VALUES('media','idx_media_id','11 1');
INSERT INTO sqlite_stat1 VALUES('playlists_fts_docsize',NULL,'1');
INSERT INTO sqlite_stat1 VALUES('media_fts_data',NULL,'4');
ANALYZE sqlite_schema;

[deldesir]

xklb-generated.db has indices on:
id, playlists_id, size, duration, time_created, time_modified, time_deleted, time_downloaded, fps, view_count, uploader, and a unique index on path.
No indices on the captions table.

sqlalchemy_generated.db has all the above indices plus additional ones on:
time_uploaded, width, height, video_count, audio_count, chapter_count, other_count, subtitle_count, download_attempts, live_status, language, type, error, subtitle_codecs, other_codecs, video_codecs, and audio_codecs.
Indices on rowid, media_id, and time on the captions table.

[deldesir]

The goal for this ticket is met. Nex steps will be to align the SQLAlchemy generated db with the XKLB generated one, removing unecessary indexing of columns like time_uploaded, width, height, video_count, audio_count, chapter_count, other_count, subtitle_count, download_attempts, live_status, language, type, error, subtitle_codecs, other_codecs, video_codecs, and audio_codecs

[holta]

[Next] steps will be to align the SQLAlchemy generated db with the XKLB generated one, removing unecessary indexing of columns like

What's the most efficient way to make that happen?

[deldesir]

[Next] steps will be to align the SQLAlchemy generated db with the XKLB generated one, removing unecessary indexing of columns like

What's the most efficient way to make that happen?

Edit #259. Doing it right now...

[deldesir]

Having a look at xb.py in #259, there is no explicit indexing for the mentioned columns. That's a discover and this looks odd when all the indices assignments can be seen in the sqlalchemy-generated.db posted above.