Pong

June 21th 2014, SQLite Statements

← June 20th 2014 SQLite CLI | ● | June 21th 2014 Qt SQLite Database Manipulations →

A transmitted file consists of the following properties that need to be stored in a SQLite table:

  • unique id
  • file name id (tid)
  • file user id (uid)
  • last modified time stamp
  • file content

Once the db has been opened, the file table containing files with the above specifications can be created with the following sql statement: 

CREATE TABLE files(
             id INTEGER PRIMARY KEY AUTOINCREMENT,
             tid TEXT NOT NULL,
             uid TEXT NOT NULL,
             isotime VARCHAR(19),
             content BLOB)

Note that there is an unique id for each file, which is auto-incremented. This is the primary key, which is indexable automatically. The AUTOINCREMENT key word ensures that the ids are monotone. The primary index allows for efficiently searching the table by id.

Since we would like to search the table for file names of a particular user name, too, we need to add an additional table index for uid and tid: 

CREATE UNIQUE INDEX search_index ON files(uid, tid)

The two fields “uid” and “tid” make up a so called composite key, which identifies unique files of a particular unique user. Therefore, the two fields need to be specified as “NOT NULL”.

A composite index implies that any prefix of the composite index is also an index. So the above composite index (uid, tid) implies that the table is also indexable by (uid). This holds only for prefixes not suffixes, meaning that the table is not efficiently indexable by (tid).

To insert a file into the created table, we use another sql statement: 

INSERT OR REPLACE INTO files
   VALUES(NULL, 'file name', 'user name', '2014-06-21T12:00:00', ?)

The NULL argument auto-increments the file identifier. The “?” argument is a place holder for the blob of the file content.

To retrieve a particular file identified by its file name and user name, we use this sql statement: 

SELECT content, isotime FROM files
       WHERE (uid = 'user name') AND (tid = 'file name')


← June 20th 2014 SQLite CLI | ● | June 21th 2014 Qt SQLite Database Manipulations →

Options: