2. SQLite Table Schemas

The following section describes the SQLite schemas for all the tables needed by Worgle.

2.1. Segment Schema

The segment schema contains the following items:

- the UUID - the type (stored as integer) - the line number - the text itself (stored as text) - the file (filename, not UUID) that it belongs to - the UUID of the next segment

The types for segments are defined in an ENUM.

enum {
    SEGMENT_STRING,
    SEGMENT_REFERENCE
};

sqlite3_exec(db,
             "CREATE TABLE segments(\n"
             "program INTEGER,\n"
             "id INTEGER,\n"
             "type INTEGER,\n"
             "str TEXT,\n"
             "linum INTEGER,\n"
             "filename TEXT,\n"
             "next_segment INTEGER\n"
             ");\n",
             NULL,
             NULL,
             NULL);

2.2. Block Schema

The block schema contains the following items:

- the UUID - the UUID of the segment head - the name of the block

sqlite3_exec(db,
             "CREATE TABLE blocks("
             "program INTEGER,\n"
             "id INTEGER,\n"
             "head_segment INTEGER,\n"
             "name TEXT,\n"
             "nblocks INTEGER,\n"
             "nsegs INTEGER\n"
             ");\n",
             NULL,
             NULL,
             NULL);

2.3. File Schema

A file name table contains the following:

- UUID - filename - the top-level block - the UUID for the next file (0 if there is no next file)

sqlite3_exec(db,
             "CREATE TABLE files(\n"
             "program INTEGER,\n"
             "id INTEGER,\n"
             "filename TEXT,\n"
             "top INTEGER,\n"
             "next_file INTEGER\n"
             ");\n",
             NULL,
             NULL,
             NULL);

2.4. Resource Schema

Every single UUID is logged into a single table. This table has every single UUID and their type.

- UUID - type (as an integer)

The integer-based types are defined as enums below:

enum {
    RES_FILE,
    RES_BLOCK,
    RES_SEGMENT,
    RES_HEADER,
    RES_CONTENT,
    RES_BLKREF
};

sqlite3_exec(db,
             "CREATE TABLE resources(\n"
             "program INTEGER,\n"
             "id INTEGER,\n"
             "type INTEGER\n"
             ");\n",
             NULL,
             NULL,
             NULL);

This operation happens so often that there is a function for it called insert_resource.

static void insert_resource(sqlite3 *db,
                            int prog,
                            worgle_long id,
                            int type)
{
    sqlite3_stmt *stmt;
    int rc;

    sqlite3_prepare_v2(db,
                       "INSERT INTO resources"
                       "(id, type, program)\n"
                       "VALUES(?1,?2,?3);",
                       -1,
                       &stmt,
                       NULL);

    sqlite3_bind_int(stmt, 1, id);
    sqlite3_bind_int(stmt, 2, type);
    sqlite3_bind_int(stmt, 3, prog);

    rc = sqlite3_step(stmt);

    if(rc != SQLITE_DONE) {
        fprintf(stderr,
                "SQLite error: %s\n",
                sqlite3_errmsg(db));
    }

    sqlite3_finalize(stmt);
}

2.5. Header Schema

The header schema contains the following: - UUID(?) - Section Number (as a string) - Section Name - Level - Org File Name - Line Number

sqlite3_exec(db,
             "CREATE TABLE headers(\n"
             "program INTEGER,\n"
             "id INTEGER,\n"
             "section TEXT,\n"
             "level INTEGER,\n"
             "top_level INTEGER,\n"
             "name TEXT,\n"
             "filename TEXT,\n"
             "linum INTEGER,\n"
             "next INTEGER\n"
             ");\n",
             NULL,
             NULL,
             NULL);

2.6. Content Schema

sqlite3_exec(db,
             "CREATE TABLE content(\n"
             "program INTEGER,\n"
             "id INTEGER,\n"
             "section TEXT,\n"
             "filename TEXT,\n"
             "linum INTEGER,\n"
             "content TEXT,\n"
             "next INTEGER\n"
             ");\n",
             NULL,
             NULL,
             NULL);

2.7. Block Reference

This provides information about a particular Block position, but not the content itself.

This includes: - UUID - Section Number

This is a separate section from the blocks section, for two reasons. One, because the section number is only derived while it is being parsed. Two, to more cleanly separate the woven content from the tangled content.

sqlite3_exec(db,
             "CREATE TABLE blkref(\n"
             "program INTEGER,\n"
             "id INTEGER,\n"
             "ref INTEGER,\n"
             "pos INTEGER,\n"
             "segoff INTEGER,\n"
             "prev_lastseg INTEGER,\n"
             "section TEXT,\n"
             "filename TEXT,\n"
             "linum INTEGER,\n"
             "next INTEGER\n"
             ");\n",
             NULL,
             NULL,
             NULL);

2.8. Type View

The resource table has a redundant column which provides a text version of the human readable type id. This view, called reslist, creates a more human readable version of the resources table.

sqlite3_exec(db,
             "CREATE VIEW reslist AS "
             "SELECT "
             "program, id, "
             "CASE type "
             "WHEN 0 THEN 'File' "
             "WHEN 1 THEN 'Block' "
             "WHEN 2 THEN 'Segment' "
             "WHEN 3 THEN 'Header' "
             "WHEN 4 THEN 'Content' "
             "WHEN 5 THEN 'Block Reference' "
             "ELSE 'Unknown' END as type "
             "FROM resources;"
             ");\n",
             NULL,
             NULL,
             NULL);

2.9. orglist view

The orglist is a resource list that only selects org components: headers, content, and block references. For now, hardcoded as 3, 4, 5. This orglist is primarily used by worgmap to figure out the last significant to render to.

sqlite3_exec(db,
             "CREATE VIEW orglist AS "
             "SELECT * "
             "FROM resources WHERE "
             "type == 3 or "
             "type == 4 or "
             "type == 5;",
             NULL,
             NULL,
             NULL);

2.10. pglist view

The pglist view lists all the weewiki pages that would be generated via worgmap. This is a helpful view that can be used to dynamically generate page reference links.

sqlite3_exec(db,
             "CREATE VIEW pglist AS "
             "SELECT * "
             "FROM headers WHERE "
             "level == 1;",
             NULL,
             NULL,
             NULL);

---

prev | home | next