6. Database

6.1. Open

A database is opened with weewiki_open.

<<funcdefs>>=

int weewiki_open(weewiki_d *ww, const char *filename);

<<functions>>=

int weewiki_open(weewiki_d *ww, const char *filename)
{
    sqlite3 *db;
    int rc;
    FILE *fp;


    fp = fopen(filename, "r");

    if (fp == NULL) {
        fprintf(stderr,
            "Could not find database %s\n",
            filename);
        return 1;
    }

    fclose(fp);

    ww->db = NULL;
    rc = sqlite3_open(filename, &db);

    if (rc) {
        fprintf(stderr,
                "Could not open database: %s",
                sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

    ww->db = db;
    return 0;
}

6.2. Create

Creates and initializes a weewiki database.

<<funcdefs>>=

int weewiki_create(weewiki_d *ww, const char *filename);

<<functions>>=

int weewiki_create(weewiki_d *ww, const char *filename)
{
    sqlite3 *db;
    int rc;

    ww->db = NULL;
    rc = sqlite3_open(filename, &db);

    if (rc) {
        fprintf(stderr,
                "Could not open database: %s",
                sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

    ww->db = db;
    weewiki_create_tables(ww);
    return 0;
}

6.3. Close

<<funcdefs>>=

void weewiki_close(weewiki_d *ww);

<<functions>>=

void weewiki_close(weewiki_d *ww)
{
    if (ww->db != NULL) sqlite3_close(ww->db);
    ww->db = NULL;
}

6.4. Get

<<funcdefs>>=

int weewiki_getter(weewiki_d *ww, const char *key);

<<functions>>=

int weewiki_getter(weewiki_d *ww, const char *key)
{
    size_t sz;
    sqlite3 *db;
    sqlite3_stmt *stmt;
    int rc;
    FILE *fp;

    fp = stdout;

    db = ww->db;
    sqlite3_prepare_v2(db,
                       "SELECT value FROM wiki WHERE(key==?1);",
                       -1,
                       &stmt,
                       NULL);
    sqlite3_bind_text(stmt, 1, key, -1, NULL);

    rc = sqlite3_step(stmt);

    if (rc != SQLITE_ROW) {
        fprintf(stderr,
                "Could not find key '%s'\n",
                key);
        sqlite3_finalize(stmt);
        return 1;
    }

    sz = sqlite3_column_bytes(stmt, 0);
    fwrite(sqlite3_column_text(stmt, 0), 1, sz, fp);
    sqlite3_finalize(stmt);
    return 0;
}

6.5. Set

<<funcdefs>>=

int weewiki_setter(weewiki_d *ww,
                   const char *key,
                   const char *val);

<<functions>>=

int weewiki_setter(weewiki_d *ww,
                   const char *key,
                   const char *val)
{
    sqlite3 *db;
    sqlite3_stmt *stmt;

    db = ww->db;
    sqlite3_prepare_v2(db,
                       "SELECT value FROM wiki WHERE(key==?1);",
                       -1,
                       &stmt,
                       NULL);

    sqlite3_prepare_v2(db,
                       "INSERT OR REPLACE INTO wiki"
                       "(key, value)\n"
                       "VALUES(?1,?2);",
                       -1,
                       &stmt,
                       NULL);

    sqlite3_bind_text(stmt, 1, key, -1, NULL);
    sqlite3_bind_text(stmt, 2, val, -1, NULL);

    sqlite3_step(stmt);

    sqlite3_finalize(stmt);
    return 0;
}

6.6. Create Tables

The function weewiki_create_tables creates all the needed SQLite tables needed by weewiki. This includes wiki, wikilinks, and wikizet.

The wiki table is most important table by weewiki. It holds all the wiki document content. wikis are stored in a key-value fashion, with unique keys being the page names, and text being the page content, stored in org markup.

The wikilinks table is used to managed all externally linked files. What is given here is the page name (the key), the filename path, and the unix modification time mtime. This logic is used to determine syncing.

The wikizet table is the the weewiki zettelkasten interface. It is implemented using SQLites full-text search capabilities (fts5) as a virtual table. Similar to the wiki, the zet is a key/value database, except that the keys are UUIDs that do not have a unique constraint, and each entry has an optional timestamp for microblogging.

<<funcdefs>>=

void weewiki_create_tables(weewiki_d *ww);

<<functions>>=

void weewiki_create_tables(weewiki_d *ww)
{
    sqlite3_exec(ww->db,
                "CREATE TABLE IF NOT EXISTS wiki(\n"
                "key TEXT UNIQUE,\n"
                "value TEXT\n"
                ");\n",
                NULL,
                NULL,
                NULL);
    sqlite3_exec(ww->db,
                "CREATE TABLE IF NOT EXISTS wikilinks(\n"
                "key TEXT UNIQUE,\n"
                "filename TEXT,\n"
                "mtime INTEGER\n"
                ");\n",
                NULL,
                NULL,
                NULL);

    sqlite3_exec(ww->db,
                 "CREATE VIRTUAL TABLE wikizet using fts5("
                 "time,\n"
                 "UUID,\n"
                 "value);\n",
                 NULL,
                 NULL,
                 NULL);
}

6.7. Push

Low level operation that pushes a file to a key. On error, returns a non-zero value.

<<funcdefs>>=

int weewiki_push(weewiki_d *ww,
                 const char *fname,
                 const char *key);

<<functions>>=

int weewiki_push(weewiki_d *ww,
                 const char *fname,
                 const char *key)
{
    char *buf;
    size_t sz;
    sqlite3 *db;
    sqlite3_stmt *stmt;
    int rc;
    FILE *fp;

    fp = fopen(fname, "r");

    if (fp == NULL) {
        fprintf(stderr,
                "Could not open file %s for reading.\n",
                fname);
        return 1;
    }

    fseek(fp, 0, SEEK_END);
    sz = ftell(fp);
    buf = calloc(1, sz + 1);
    fseek(fp, 0, SEEK_SET);
    fread(buf, 1, sz, fp);

    db = ww->db;

    sqlite3_prepare_v2(db,
                       "INSERT OR REPLACE INTO wiki"
                       "(key, value)\n"
                       "VALUES(?1,?2);",
                       -1,
                       &stmt,
                       NULL);

    sqlite3_bind_text(stmt, 1, key, -1, NULL);
    sqlite3_bind_text(stmt, 2, buf, sz, NULL);

    rc = sqlite3_step(stmt);

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

    free(buf);
    return 0;
}

6.8. Pull

<<funcdefs>>=

int weewiki_pull(weewiki_d *ww,
                 const char *key,
                 const char *fname);

<<functions>>=

int weewiki_pull(weewiki_d *ww,
                 const char *key,
                 const char *fname)
{
    size_t sz;
    sqlite3 *db;
    sqlite3_stmt *stmt;
    int rc;
    FILE *fp;

    fp = fopen(fname, "w");
    if (fp == NULL) {
        fprintf(stderr,
                "Could not open file %s for writing\n",
                fname);
        return 1;
    }

    db = ww->db;
    sqlite3_prepare_v2(db,
                       "SELECT value FROM wiki WHERE(key==?1);",
                       -1,
                       &stmt,
                       NULL);
    sqlite3_bind_text(stmt, 1, key, -1, NULL);

    rc = sqlite3_step(stmt);

    if (rc != SQLITE_ROW) {
        fprintf(stderr,
                "Could not find key '%s'\n",
                key);
        sqlite3_finalize(stmt);
        return 1;
    }

    sz = sqlite3_column_bytes(stmt, 0);
    fwrite(sqlite3_column_text(stmt, 0), 1, sz, fp);
    sqlite3_finalize(stmt);
    fclose(fp);
    return 0;
}

6.9. Exists

The function weewiki_exists is used to check if a page (key) exists in the weewiki database. Returns TRUE (1) or FALSE (0).

<<funcdefs>>=

int weewiki_exists(weewiki_d *ww, const char *key);

<<functions>>=

int weewiki_exists(weewiki_d *ww, const char *key)
{
    sqlite3 *db;
    sqlite3_stmt *stmt;
    int rc;

    db = ww->db;
    sqlite3_prepare_v2(db,
                       "SELECT EXISTS("
                       "SELECT * FROM wiki WHERE(key==?1)"
                       ");",
                       -1,
                       &stmt,
                       NULL);
    sqlite3_bind_text(stmt, 1, key, -1, NULL);

    sqlite3_step(stmt);

    rc = sqlite3_column_int(stmt, 0);

    sqlite3_finalize(stmt);
    return rc;
}

6.10. Name Set/Get

Sets/gets the database name.

A global variable is used outside of the global weewiki_dbecause it needs to be able to be set before that struct is initialized.

<<funcdefs>>=

void weewiki_dbname_set(const char *name);
const char * weewiki_dbname_get(void);

<<functions>>=

static const char *g_dbname = "a.db";
void weewiki_dbname_set(const char *name)
{
    g_dbname = name;
}

const char * weewiki_dbname_get(void)
{
    return g_dbname;
}

prev | home | next