0

There are three SQLite3 tables (Location, Music and History) that need to be vacuumed.

For the first two tables, the Primary Key cannot change, and for the last table (History), the Primary key should change.

The SQLite VACUUM command states something about "unaliased", which is confusing.

Can someone state the syntax to be used?

TL;DR

Location Table (leave Primary key intact)

con.execute(
    "CREATE TABLE IF NOT EXISTS Location(Id INTEGER PRIMARY KEY, " +
    "Code TEXT, Name TEXT, ModifyTime FLOAT, ImagePath TEXT, " +
    "MountPoint TEXT, TopDir TEXT, HostName TEXT, " +
    "HostWakeupCmd TEXT, HostTestCmd TEXT, HostTestRepeat INT, " +
    "HostMountCmd TEXT, HostTouchCmd TEXT, HostTouchMinutes INT, " +
    "Comments TEXT)")

con.execute("CREATE UNIQUE INDEX IF NOT EXISTS LocationCodeIndex ON " +
            "Location(Code)")

Music Table (leave Primary key intact)

con.execute(
    "create table IF NOT EXISTS Music(Id INTEGER PRIMARY KEY, " +
    "OsFileName TEXT, OsAccessTime FLOAT, OsModifyTime FLOAT, " +
    "OsChangeTime FLOAT, OsFileSize INT, " +
    "ffMajor TEXT, ffMinor TEXT, ffCompatible TEXT, " +
    "Title TEXT, Artist TEXT, Album TEXT, Compilation TEXT, " +
    "AlbumArtist TEXT, AlbumDate TEXT, FirstDate TEXT, " +
    "CreationTime TEXT, DiscNumber TEXT, TrackNumber TEXT, " +
    "Rating TEXT, Genre TEXT, Composer TEXT, Comment TEXT, " +
    "Hyperlink TEXT, Duration TEXT, Seconds FLOAT, " +
    "GaplessPlayback TEXT, PlayCount INT, LastPlayTime FLOAT, " +
    "LyricsScore BLOB, LyricsTimeIndex TEXT)")

con.execute("CREATE UNIQUE INDEX IF NOT EXISTS OsFileNameIndex ON " +
            "Music(OsFileName)")

History Table (primary key needs to be reset)

con.execute(
    "create table IF NOT EXISTS History(Id INTEGER PRIMARY KEY, " +
    "Time FLOAT, MusicId INTEGER, User TEXT, Type TEXT, " +
    "Action TEXT, SourceMaster TEXT, SourceDetail TEXT, " +
    "Target TEXT, Size INT, Count INT, Seconds FLOAT, " +
    "Comments TEXT, Timestamp FLOAT)")

con.execute("CREATE INDEX IF NOT EXISTS MusicIdIndex ON " +
            "History(MusicId)")

con.execute("CREATE UNIQUE INDEX IF NOT EXISTS TimeIndex ON " +
            "History(Timestamp)")

con.execute("CREATE INDEX IF NOT EXISTS TypeActionIndex ON " +
            "History(Type, Action)")
WinEunuuchs2Unix
  • 1,801
  • 1
  • 17
  • 34
  • If your primary key is not AUTOINCREMENT, then VACUUM isn't going to touch it. The integer might be a social security number. MySQL has no idea about that. – Tim Roberts Aug 20 '23 at 18:46
  • @TimRoberts So instead of deleting history records I need to create a new database with the history records to keep. – WinEunuuchs2Unix Aug 20 '23 at 18:52
  • Yes, or just DELETE the ones you don't want. – Tim Roberts Aug 20 '23 at 22:40
  • If you have an `INTEGER PRIMARY KEY` column, `VACUUM` will not change rowids. If you don't, it can. `AUTOINCREMENT` is irrelevant here (Since it requires an IPK column). – Shawn Aug 21 '23 at 01:05
  • In general, changing a row's primary key from one value to another is a sign you have a flawed design or are making some fundamental misunderstandings about how databases, in particular SQLite, work. What's your intent in having it change on a vacuum? – Shawn Aug 21 '23 at 01:18
  • @Shawn There are 20,000 history records now and 15,000 will be deleted. After when the next history record is added it makes sense it is number 5,001 and not number 20,001. The other two tables have keys cross-referenced within history so those keys cannot change. – WinEunuuchs2Unix Aug 21 '23 at 03:41
  • Ah, I wondered if it was something like that. Sounds like you're confusing an arbitrary unique row identifier with a sequential row number. If you want the latter, use the `row_number()` window function with an appropriate ordering. Don't try to force a rowid to be something it isn't. – Shawn Aug 21 '23 at 12:55

0 Answers0