CODEWITHSUNDEEP
pythonsqlsqlite
G. Chan
G. Chan

Reputation: 43

SQLite inner join 2 tables into a main table

I have a main table that looks like 

    CREATE TABLE IF NOT EXISTS filesystem (
    inode           INTEGER PRIMARY KEY, 
    uid             TEXT, 
    gid             TEXT, 
    type            TEXT,
    pathname        TEXT,
    parentinode     INTEGER,
    FileCounter     INTEGER DEFAULT 0,
    DirCounter      INTEGER DEFAULT 0,
    TotalByteSum    INTEGER DEFAULT 0 )

and 2 sub tables that look like: 

    CREATE TABLE IF NOT EXISTS groups (
    gid         TEXT PRIMARY KEY, 
    groupname   TEXT

    CREATE TABLE IF NOT EXISTS users (
    uid         TEXT PRIMARY KEY,
    username    TEXT

Currently I am using:

CREATE TABLE completeFileSystem AS SELECT filesystem.inode, users.username, groups.groupname, filesystem.type,  filesystem.pathname,filesystem.parentinode, filesystem.FileCounter, filesystem.DirCounter, filesystem.TotalByteSum
FROM filesystem 
INNER JOIN users ON filesystem.uid = users.uid 
INNER JOIN groups ON groups.gid = filesystem.gid

that creates a new table which results in my db file being larger than desired.

I would like to inner join on filesystem.uid = users.uid and on filesystem.gid = groups.gid into the filesystem table directly. Is this possible in SQLite? I am using Python to script these sql statements if that changes anything.

Upvotes: 4

Views: 773

Answers (1)

CL.
CL.

Reputation: 180070

Consider using a view for this:

CREATE VIEW completeFileSystem AS
SELECT ...
FROM filesystem 
JOIN users USING (uid)
JOIN groups USING (gid);

This is usually fast enough. (You can speed it up a little bit by making the two lookup tables WITHOUT ROWID tables.) If the main table has very many rows, the space saved by storing only the user/group IDs will, and thus reduced I/O, can compensate for the effort of doing the joins.

Whether this is actually fast enough for your particular application is something you have to measure yourself.

Upvotes: 1

Related Questions