[Yum-devel] sqlite table creation question

[Gijs Hollestelle]

On Fri, 04 Mar 2005 04:37:03 -0500, seth vidal <skvidal at phy.duke.edu> wrote:
> in sqlitecache.py it does:
>     def createTablesFilelists(self,db):
>         """Create the required tables for filelists metadata in the sqlite
>            database"""
>         cur = db.cursor()
>         self.createDbInfo(cur)
>         # This table is needed to match pkgKeys to pkgIds
>         cur.execute("""CREATE TABLE packages(
>             pkgKey INTEGER PRIMARY KEY,
>             pkgId TEXT)
>         """)
>         cur.execute("""CREATE TABLE filelist(
>             pkgKey INTEGER,
>             dirname TEXT,
>             filenames TEXT,
>             filetypes TEXT)
>         """)
>         cur.execute("CREATE INDEX keyfile ON filelist (pkgKey)")
>         cur.execute("CREATE INDEX pkgId ON packages (pkgId)")
> 
> two questions:
>  1. why do we make the packages table? If that table doesn't exist
> shouldn't we just raise and return b/c the sacks are being inserted out
> of order?

The packages table for filelist information is created so that we
don't have to store the pkgId (40 bytes) for every filelist entry but
we can store the much shorter pkgKey (4-8 bytes) for every filelist
entry and map that back to a pkgId by using the packages table. The
pkgKey's do not have to be the same access different databases
(primary.xml.sqlite and filelists.xml.sqlite) therefore we can't just
use that mapping.

> 
>  2. how much benefit are the indexes? How much does it help on insert if
> we don't make them?

They are handy to make some queries faster (here it makes the list all
files of package X much faster) removing them saves about 5% at import
time.

Using PRAGMA synchronous = OFF doesn't appear to make much difference either.

Greets,
  Gijs