Login
You're viewing the m.mtlynch.io public feed.
  • Michael Lynchmichael
    Apr 30, 2022, 2:01 PM

    I still didn't love the idea of storing the redundant file size, and I considered alternatively creating a virtual SQLite table.

    But we'd still have to populate the virtual table still at app load time, which would take ~10s per GB of data
    sqlite.org/vtab.html

    💬 1🔄 0⭐ 0

Replies

  • Michael Lynchmichael
    Apr 30, 2022, 2:01 PM

    The last tricky part was writing a SQL migration to populate the sizes of files that were uploaded and stored in the DB before this change. I've never written an update that derives from other data in the DB before, but it wasn't too hard.

    ALTER TABLE entries ADD COLUMN file_size INTEGER; -- Populate file sizes for legacy entries UPDATE entries SET file_size = ( SELECT SUM(LENGTH(entries_data.chunk)) AS file_size FROM entries_data WHERE entries.id = entries_data.id );
    💬 1🔄 0⭐ 0
  • 💬 1🔄 0⭐ 0
  • Michael Lynchmichael
    May 1, 2022, 12:36 AM

    Update! @wmertens and @dholth showed me a simpler way to achieve the same performance without storing the file size redundantly. Creating an index achieves the same thing.

    mike@ps:~/picoshare$ time sqlite3 data/store.db "SELECT id, SUM(LENGTH(chunk)) FROM entries_data GROUP BY id" 86cvu4kCt3|1215168512 real 0m5.502s user 0m1.811s sys 0m3.687s mike@ps:~/picoshare$ sqlite3 data/store.db "CREATE INDEX length_index ON entries_data(id, LENGTH(CHUNK));" mike@ps:~/picoshare$ time sqlite3 data/store.db "SELECT id, SUM(LENGTH(chunk)) FROM entries_data GROUP BY id" 86cvu4kCt3|1215168512 real 0m0.007s user 0m0.004s sys 0m0.004s
    💬 1🔄 0⭐ 0
  • 💬 0🔄 0⭐ 0