ermetadb.c

Go to the documentation of this file.

/*
 * File Name: ermetadb.c
 */

/*
 * This file is part of libermetadb.
 *
 * libermetadb is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.
 *
 * libermetadb is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

/**
 * Copyright (C) 2008 iRex Technologies B.V.
 * All rights reserved.
 */

//----------------------------------------------------------------------------
// Include Files
//----------------------------------------------------------------------------

#include "config.h"

#include <errno.h>
#include <glib.h>
#include <limits.h>
#include <libgen.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <sys/wait.h>
#include <unistd.h>

#include "ermetadb_log.h"
#include "ermetadb.h"
#include "ermetadb_error.h"
#include "ermetadb_private.h"
#include "sqlite3_wrapper.h"

//----------------------------------------------------------------------------
// Global Constants
//----------------------------------------------------------------------------

#define DATABASE_VERSION_MAJOR  0
#define DATABASE_VERSION_MINOR  6

#define MAX_COLUMNS  17
static const struct
             {
                 const char     *table_name;                    // table name
                 const char     *column_names[MAX_COLUMNS+1];   // array with column names
             } global_database_tables[]
             =
             {    // Table name
                  //     Column names
                {
                    "database_properties",
                    {
                        "version_major",
                        "version_minor",
                        NULL
                    }
                },
                { 
                    "file_metadata",
                    {
                        "file_id",
                        "filename",
                        "directory_path",
                        "sort_priority",
                        "is_directory",
                        "is_template",
                        "file_type",
                        "file_size",
                        "file_time_modified",
                        "file_time_lastread",
                        "file_time_added",
                        "title",
                        "author",
                        "number_of_pages",
                        "current_page",
                        "tag",
                        NULL
                    }
                },
                {
                    "thumbnails",
                    {
                        "file_id",
                        "thumb_data_mini",
                        "thumb_data_small",
                        "thumb_data_medium",
                        "thumb_data_large",
                        NULL
                    }
                },
                {
                    NULL,
                    {}
                }
             };


//============================================================================
// Local Function Definitions
//============================================================================

static gboolean update_version_number(sqlite3 *db, int major, int minor)
{
    char query[255];
    sprintf(query, "UPDATE database_properties SET version_major=%d, version_minor=%d;",
                major, minor);
    int rc = sql3_execute_query(db, query, NULL, NULL);
    if (rc != ER_OK) {
        ERRORPRINTF("cannot update version number to %d.%d", major, minor);
    }
    return (rc == ER_OK);
}


static void convert_database_01_06(erMetadb thiz, int *minor)
{
    WARNPRINTF("Trying to convert db version 0.1 to 0.6");
    // create temp table for file_metadata
    // copy file_metadata to temp table
    // drop old file_metadata table
    // rename temp to file_metadata
    // DROP thumbnails
    const char* sql = "CREATE TABLE temp (\n"
                      "  file_id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n"
                      "  filename VARCHAR(250) NOT NULL\n"
                      ");";
    if (sql3_execute_query(thiz->database, sql, NULL, NULL) != ER_OK)
    {
        ERRORPRINTF("cannot create temp table to convert version 0.1->0.6, dir %s", thiz->directory);
        return;
    }

    sql = "INSERT INTO temp SELECT file_id, filename FROM file_metadata;\n"
          "DROP TABLE file_metadata;";
    if (sql3_execute_query(thiz->database, sql, NULL, NULL) != ER_OK)
    {
        ERRORPRINTF("cannot fill temp to convert version 0.1->0.6, dir %s", thiz->directory);
        return;
    }

    sql = "ALTER TABLE temp RENAME TO file_metadata;\n"
          "DROP TABLE thumbnails;";
    if (sql3_execute_query(thiz->database, sql, NULL, NULL) != ER_OK)
    {
        ERRORPRINTF("cannot convert version 0.1->0.6, dir %s", thiz->directory);
        return;
    }

    // get list of anchors
    sql = "SELECT annotation_id, start_anchor from annotations;";
    metadata_table *result = NULL;
    if (sql3_execute_query(thiz->database, sql, NULL, &result) != ER_OK)
    {
        ERRORPRINTF("cannot get annotations to convert version 0.1->0.6, dir %s", thiz->directory);
        return;
    }

    static const char* begin = "pdf:/page:";
    unsigned int begin_len = strlen(begin);
    static const char* new_anchor = "adobe:|type:0|pf_page";
    // convert anchors:  pdf:/page:1   ->   adobe:|type:0|pf_page:0.000000
    if (result) {
        const metadata_cell *cell = (const metadata_cell*) result->cell_data->data;
        unsigned int row;
        for (row=0; row<result->n_rows; row++) {
            int id = cell->value.v_int64;
            cell++;
            const char* anchor = cell->value.v_text->str;
            cell++;
            if (strncmp(begin, anchor, begin_len) == 0 && strlen(anchor) > begin_len)
            {
                int page = atoi(anchor + begin_len);
                if (page > 0) page--;   // new system starts at 0 instead of 1
                char query[256];
                sprintf(query, "UPDATE annotations set start_anchor='%s:%d.0000' WHERE annotation_id=%d;",
                    new_anchor, page, id);
                if (sql3_execute_query(thiz->database, query, NULL, NULL) != ER_OK) {
                    ERRORPRINTF("cannot update annotations");
                }
            }

        }
        metadata_table_free(result);
    }

    // convert last_read locations in application_data
    sql = "SELECT file_id, key, value FROM application_data;";
    result = NULL;
    if (sql3_execute_query(thiz->database, sql, NULL, &result) != ER_OK)
    {
        ERRORPRINTF("cannot get application data to convert version 0.1->0.6, dir %s", thiz->directory);
        return;
    }

    // convert uds_last_read_location anchor:  pdf:/page:1   ->   adobe:|type:0|pf_page:0.000000
    if (result) {
        const metadata_cell *cell = (const metadata_cell*) result->cell_data->data;
        unsigned int row;
        for (row=0; row<result->n_rows; row++) {
            int id = cell->value.v_int64;
            cell++;
            const char* key = cell->value.v_text->str;
            cell++;
            const char* value = cell->value.v_text->str;
            cell++;
            static const char* lastread_key = "uds_last_read_location";
            if (strcmp(lastread_key, key) == 0) {
                if (strncmp(begin, value, begin_len) == 0 && strlen(value) > begin_len)
                {
                    int page = atoi(value + begin_len);
                    if (page > 0) page--;   // new system starts at 0 instead of 1
                    char query[256];
                    sprintf(query, "UPDATE application_data set value='%s:%d.0000' WHERE file_id=%d AND key='%s';",
                        new_anchor, page, id, lastread_key);
                    if (sql3_execute_query(thiz->database, query, NULL, NULL) != ER_OK) {
                        ERRORPRINTF("cannot update application_data");
                    }
                }
            }
        }
        metadata_table_free(result);
    }

    if (update_version_number(thiz->database, 0, 6)) {
        LOGPRINTF("version updated to 0.6");
        *minor = 6;
    }
}


static int add_file_ids(sqlite3* db, GSList **list, const char* table_name)
{
    char sql[255];
    sprintf(sql, "SELECT DISTINCT file_id FROM %s;", table_name);
    metadata_table *result = NULL;
    int rc = sql3_execute_query(db, sql, NULL, &result);
    if (rc != ER_OK) {
        ERRORPRINTF("cannot read file_id's for %s", table_name);
        return rc;
    }
    int n_rows = metadata_table_n_rows(result);
    int row;
    for (row=0; row<n_rows; row++) {
        const metadata_cell *cell = metadata_table_get_cell(result, row);
        if (cell->type != METADATA_INT64) {
            ERRORPRINTF("invalid data type for file_id [%d]", cell->type);
        } else {
            int id = cell->value.v_int64;
            GSList *found = g_slist_find(*list, (gpointer)id);
            if (found == NULL) {
                *list = g_slist_append(*list, (gpointer)id);
            }
        }
    }
    metadata_table_free(result);
    return ER_OK;
}


// results should be a table with <fileid, filename>
static const char* get_filename_from_results(const metadata_table *table, int id)
{
    g_assert(table->n_columns == 2);
    if (table->n_rows == 0) return NULL;

    const metadata_cell *cell = (const metadata_cell*) table->cell_data->data;
    unsigned int row;
    for (row=0; row<table->n_rows; row++) {
        g_assert_cmpint(cell->type, ==, METADATA_INT64);

        int id2 = cell->value.v_int64;
        if (id == id2) {
            cell++;
            g_assert_cmpint(cell->type, ==, METADATA_TEXT);
            return cell->value.v_text->str;
        }
        cell += table->n_columns;
    }
    return NULL;
}


// changes from version 0.5 -> 0.6
// - added file_metadata table to local db's to get rid of global db dependency
//   this table has file_id, filename as fields
static void convert_database_05_06(erMetadb thiz, int *minor)
{
    WARNPRINTF("Trying to convert %s db version 0.5 to 0.6", (thiz->is_global ? "global" : "local"));
    GSList *id_list = NULL;
    
    if (thiz->is_global)
    {
        // Nothing needs to be done; only update version number
        goto ok;
    }

    // create new file_metadata table
    const char *sql = "CREATE TABLE file_metadata (file_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, filename VARCHAR(250) NOT NULL);";
    if (sql3_execute_query(thiz->database, sql, NULL, NULL) != ER_OK)
    {
        ERRORPRINTF("Failed to create table 'file_metadata'");
        return;
    }

    // get all used local id's
    int rc1 = add_file_ids(thiz->database, &id_list, "application_data");
    int rc2 = add_file_ids(thiz->database, &id_list, "annotations");
    if (rc1 != ER_OK || rc2 != ER_OK) goto err;

    if (id_list == NULL) goto ok; // empty database, just update version

    // open global db for lookups
    // TODO Q: how to get path? (seach upwards?)
    const char* global_path = "/media/mmcblk0p1";
    erMetadb global_db = ermetadb_global_open(global_path, TRUE);
    if (global_db == NULL) {
        ERRORPRINTF("cannot open global db for lookups");
        goto err;
    }

    // get all filename, id's from global for path
    char query[512];
    sprintf(query, "SELECT file_id, filename FROM file_metadata WHERE directory_path = '%s';", thiz->directory);
    metadata_table *global_results = NULL;
    int rc = sql3_execute_query(global_db->database, query, NULL, &global_results);
    ermetadb_close(global_db);
    if (rc != ER_OK || global_results == NULL) {
        ERRORPRINTF("cannot get file_id's from global");
        goto err;
    }

    // for all id's, find matching filename and insert into local file_metadata
    GSList *iter = id_list;
    while (iter) {
        int id = (int)iter->data;
        const char* filename = get_filename_from_results(global_results, id);

        if (filename == NULL) {
            WARNPRINTF("cannot find filename for id %d, removing data", id);
            local_delete_all_data_for_id(thiz, id); // dont care about result
            goto next;
        }

        // find matching filename from global_resultss
        char *sql2 = sqlite3_mprintf("INSERT INTO file_metadata (filename, file_id) VALUES (%Q,%d);", filename, id);
        rc = sql3_execute_query(thiz->database, sql2, NULL, NULL);
        sqlite3_free(sql2);
        if (rc != ER_OK) {
            ERRORPRINTF("cannot insert <'%s', %d> into database", filename, id);
        }
next:
        iter = g_slist_next(iter); 
    }
    metadata_table_free(global_results);
ok:
    // update version number
    if (update_version_number(thiz->database, 0, 6)) {
        LOGPRINTF("version updated to 0.6");
        *minor = 6;
    }
err:
    if (id_list) g_slist_free(id_list);
}


static int convert_database(erMetadb thiz, const int v_major, const int v_minor)
{
    g_assert(thiz->database);

    int ret = ER_OK;
    int major = v_major;
    int minor = v_minor;

    TRACE("entry: db [%p] version [%d].[%d]  is_global=%d", db, v_major, v_minor, is_global);

    if (major == 0 && minor == 1)
    {
        convert_database_01_06(thiz, &minor);
    }

    if (major == 0 && minor == 5)
    {
        convert_database_05_06(thiz, &minor);
    }

    // check conversion succeeded
    if (   major != DATABASE_VERSION_MAJOR
        || minor != DATABASE_VERSION_MINOR )
    {
        ERRORPRINTF( "cannot convert database version [%d.%d] to %d.%d",
                     v_major,
                     v_minor,
                     DATABASE_VERSION_MAJOR,
                     DATABASE_VERSION_MINOR );
        ret = ERMETADB_DATABASE_STRUCTURE;
    }

    return ret;
}


// check database version, convert when needed
static int check_database_version(erMetadb thiz)
{
    TRACE("entry: db [%p]  global=%d", thiz->database, thiz->is_global);
    g_assert(thiz->database);

    int ret = ER_OK;
    // get current database version
    const char* sql = "SELECT version_major, version_minor FROM database_properties;";
    metadata_table *result = NULL;
    int rc = sql3_execute_query(thiz->database, sql, NULL, &result);
    if (rc != ER_OK)
    {
        ERRORPRINTF("cannot get version from database");
        ret = rc;
        goto out;
    }

    // extract version numbers
    const metadata_cell *cell = metadata_table_get_cell(result, 0);
    if (cell->type != METADATA_INT64)
    {
        ERRORPRINTF("invalid data type database version major [%d]", cell->type);
        ret = ERMETADB_DATABASE_STRUCTURE;
        goto out;
    }
    int v_major = cell->value.v_int64;

    cell = metadata_table_get_cell(result, 1);
    if (cell->type != METADATA_INT64)
    {
        ERRORPRINTF("invalid data type database version minor [%d]", cell->type);
        ret = ERMETADB_DATABASE_STRUCTURE;
        goto out;
    }
    int v_minor = cell->value.v_int64;

    // check database version
    if (v_major > DATABASE_VERSION_MAJOR)
    {
        ERRORPRINTF("incompatible database version [%d.%d]", v_major, v_minor);
        ret = ERMETADB_DATABASE_STRUCTURE;
        goto out;
    }
    if (v_major != DATABASE_VERSION_MAJOR || v_minor != DATABASE_VERSION_MINOR )
    {
        ret = convert_database(thiz, v_major, v_minor);
    }

out:
    metadata_table_free(result);
    return ret;
}


// check consistency of database tables:
//   all tables present
//   no additional tables
static int check_global_database_tables(erMetadb thiz)
{
    TRACE("entry: thiz [%p]", thiz);
    g_assert(thiz->database);
    g_assert(thiz->is_global);

    // get tablenames from database
    metadata_table *result   = NULL;    // result from query
    const char *sql = "SELECT name FROM sqlite_master WHERE type='table';";
    int ret = sql3_execute_query(thiz->database, sql, NULL, &result);
    if (ret != ER_OK) goto out;

    int n_tables = metadata_table_n_rows(result);
    int col = metadata_table_find_column(result, "name");
    if (col < 0)
    {
        ERRORPRINTF("No 'names' column in result");
        ret = ERMETADB_FAIL;
        goto out;
    }

    // check table names
    int i;
    for ( i = 0 ; global_database_tables[i].table_name ; i++)
    {
        const char *name = global_database_tables[i].table_name;

        gboolean found = FALSE;
        int row;
        for (row = 0 ; row < n_tables && !found ; row++)
        {
            GString *string = metadata_table_get_string(result, metadata_table_cell_index(result, row, col));
            if (string)
            {
                if ( string->str  &&  strcmp(string->str, name) == 0 )
                {
                    found = TRUE;
                }
                g_string_free(string, TRUE);
            }
        }
        if (!found)
        {
            ERRORPRINTF("table [%s] not present", name);
            ret = ERMETADB_DATABASE_STRUCTURE;
        }
    }
out:
    metadata_table_free(result);
    return ret;
}


// check consistency of database columns:
//   per table all columns present
//   no additional columns
static int check_global_database_columns(erMetadb thiz)
{
    TRACE("entry: thiz [%p]", thiz);
    g_assert(thiz->database);
    g_assert(thiz->is_global);

    // for each table expected
    int ret = ER_OK;
    int tbl;
    for ( tbl = 0 ; global_database_tables[tbl].table_name  &&  ret == ER_OK ; tbl++ )
    {
        const char* table_name = global_database_tables[tbl].table_name;
        int n_columns = 0;
        int col;
        metadata_table *result = NULL;

        // get column names from database
        GString *sql = g_string_new("");
        g_string_printf( sql, "PRAGMA table_info(%s);", table_name );
        ret = sql3_execute_query(thiz->database, sql->str, NULL, &result);
        g_string_free(sql, TRUE);
        if (ret == ER_OK)
        {
            n_columns = metadata_table_n_rows(result);
            col = metadata_table_find_column(result, "name");
            if (col < 0)
            {
                ERRORPRINTF("No 'names' column in result");
                ret = ERMETADB_FAIL;
            }
        }

        // check column names
        if (ret == ER_OK)
        {
            // find column name
            int i;
            for ( i = 0 ; global_database_tables[tbl].column_names[i] ; i++ )
            {
                const char* name = global_database_tables[tbl].column_names[i];

                gboolean found = FALSE;
                int row;
                for ( row = 0 ; row < n_columns  &&  !found ; row++)
                {
                    GString *string = metadata_table_get_string(result, metadata_table_cell_index(result, row, col));
                    if (string)
                    {
                        if ( string->str  &&  strcmp(string->str, name) == 0 )
                        {
                            found = TRUE;
                        }
                        g_string_free(string, TRUE);
                    }
                }
                if (!found)
                {
                    ERRORPRINTF("column [%s] not present in table [%s]", name, global_database_tables[tbl].table_name);
                    ret = ERMETADB_DATABASE_STRUCTURE;
                }
            }
        }
        metadata_table_free(result);
    }

    return ret;
}


// check database content is consistent:
//   all tables present
//   per table all columns present
//   no additional tables or columns
static int check_global_database_consistency(erMetadb thiz)
{
    TRACE("entry: thiz [%p]", thiz);

    int rc = check_database_version(thiz);
    if (rc != ER_OK)
    {
        ERRORPRINTF("checking database version failed with code [%d]", rc);
        return rc;
    }

    rc = check_global_database_tables(thiz);
    if (rc != ER_OK)
    {
        ERRORPRINTF("checking global tables failed with code [%d]", rc);
        return rc;
    }

    rc = check_global_database_columns(thiz);
    if (rc != ER_OK)
    {
        ERRORPRINTF("checking database columns failed with code [%d]", rc);
        return rc;
    }

    return ER_OK;
}


static int check_local_database_consistency(erMetadb thiz)
{
    TRACE("entry: thiz [%p]", thiz);

    int rc = check_database_version(thiz);
    if (rc != ER_OK)
    {
        ERRORPRINTF("checking database version failed with code [%d]", rc);
        return rc;
    }

    return ER_OK;
}


static int open_sqlite_database(const gchar *filename, gboolean do_open_readonly, sqlite3 **database)
{
    g_assert(filename);
    g_assert(database && *database == NULL);

    // open database read-write
    sqlite3 *db = NULL;
    int rc = sqlite3_open_v2( filename,
                          &db,
                          do_open_readonly ? SQLITE_OPEN_READONLY : SQLITE_OPEN_READWRITE,
                          NULL );
    int ret = ER_OK;
    if (rc != SQLITE_OK)
    {
        ERRORPRINTF("sqlite open error [%d] [%s] on [%s]", rc, sqlite3_errmsg(db), filename);
        ret = ER_OPEN_ERROR;
        sqlite3_close(db);
        db = NULL;
    }

    // install custom collation sequence
    if (ret == ER_OK)
    {
        rc = sqlite3_create_collation( db,                              // database
                                       SQLITE_COLLATION_STRCASECMP,     // collation name
                                       SQLITE_UTF8,                     // text representation
                                       NULL,                            // user data
                                       sql3_strcase_compare );          // compare function
        if (rc != SQLITE_OK)
        {
            ERRORPRINTF("sqlite collation install error [%d] [%s]", rc, sqlite3_errmsg(db));
        }
    }

    if (ret == ER_OK)
    {
        *database = db;
    }
    TRACE("leave: ret [%d]", ret);
    return ret;
}


static int check_database(erMetadb thiz)
{
    if (thiz->is_global)
        return check_global_database_consistency(thiz);
    else
        return check_local_database_consistency(thiz);
}


static int open_sql_database(erMetadb thiz, const char* filename)
{
    // check if directory exists
    struct stat statbuf;
    int rc = stat(thiz->directory, &statbuf);
    if (rc != 0 || !S_ISDIR(statbuf.st_mode))
    {
        // directory not present or not a directory: report error
        LOGPRINTF("folder doesn't exist or is not folder [%s]", thiz->directory);
        return ER_NOT_FOUND;
    }

    // create fullname
    char fullname[PATH_MAX];
    sprintf(fullname, "%s/%s", thiz->directory, filename);

    // check if database exists
    struct stat statbuf2;
    rc = stat(fullname, &statbuf2);
    if (rc != 0 || !S_ISREG(statbuf2.st_mode))
    {
        // database not present: report error
        LOGPRINTF("database not present or is not regular file [%s]", fullname);
        return ER_NOT_FOUND;
    }

    sqlite3* db = NULL;
    rc = open_sqlite_database(fullname, thiz->readonly, &db);
    if (rc != ER_OK)
    {
        ERRORPRINTF("open sqlite failed, rc [%d] filename [%s] readonly [%d]",
                                                        rc, fullname, thiz->readonly);
        return rc;
    }
    thiz->database = db;

    int ret = ER_OK;
    if (thiz->readonly)
    {
        ret = sql3_begin_transaction_readonly(thiz);
    }
    else
    {
        ret = sql3_begin_transaction(thiz);
    }
    ret = check_database(thiz);
    ret = sql3_commit_or_rollback(thiz, ret);
    if (ret != ER_OK) goto error;

    // enable asynchronous mode,
    // i.e. sqlite writes to filesystem but does not wait for flush to disk
    const char *sql = "PRAGMA synchronous = OFF;";
    metadata_table *result = NULL;
    ret = sql3_execute_query(thiz->database, sql, NULL, &result);
    metadata_table_free(result);

    if (ret != ER_OK) goto error;
    return ER_OK;
error:
    sqlite3_close(thiz->database);
    thiz->database = NULL;
    return ret;
}


static int create_db(erMetadb thiz, const char* filename)
{
    WARNPRINTF("(re-)generating database in %s", thiz->directory);
    int res = ER_OK;
    if (thiz->is_global) {
        res = ermetadb_global_create_database(thiz->directory);
    } else {
        res = ermetadb_local_create_database(thiz->directory, filename);
    }
    if (res != ER_OK) {
        ERRORPRINTF("error creating database %s/%s", thiz->directory, filename);
    }
    return res;
}


static erMetadb database_open (const gchar *folder,
                               const gchar *filename,
                               gboolean global,
                               gboolean readonly,
                               gboolean create)
{
    g_assert(folder && folder[0]);
    g_assert(filename && filename[0]);

    erMetadb thiz = g_new0(struct erMetadb_t, 1);

    thiz->database = NULL;
    thiz->directory = g_strdup(folder);
    thiz->is_global = global;
    thiz->readonly = readonly;
    thiz->transaction_nesting = 0;
    thiz->is_transaction_readonly = FALSE;
    thiz->do_force_rollback = FALSE;

    int res = open_sql_database(thiz, filename);
    if (res != ER_OK) {
        // create database on some conditions
        if (global && !readonly) res = create_db(thiz, filename);
        if (!global && create)   res = create_db(thiz, filename);

        if (res == ER_OK) {
            res = open_sql_database(thiz, filename);
        }
    }

    if (res != ER_OK) {
        g_assert(thiz->database == NULL);
        ermetadb_close(thiz);
        return NULL;
    }
    g_assert(thiz->database);
    return thiz;

}


erMetadb ermetadb_global_open (const gchar* folder, gboolean readonly)
{
    LOGPRINTF("folder [%s]", folder);
    return database_open(folder, ERMETADB_GLOBAL_DATABASE_FILE, TRUE, readonly, FALSE);
}


erMetadb ermetadb_local_open (const gchar* folder, gboolean create)
{
    LOGPRINTF("folder [%s]  create [%d]", folder, create);
    return database_open(folder, ERMETADB_LOCAL_DATABASE_FILE, FALSE, FALSE, create);
}


erMetadb ermetadb_local_open_custom (const gchar* folder, const gchar* filename)
{
    LOGPRINTF("folder [%s]  file [%s]", folder, filename);
    return database_open(folder, filename, FALSE, FALSE, FALSE);
}


const gchar* ermetadb_get_dir (erMetadb thiz)
{
    return thiz->directory;
}


void ermetadb_close (erMetadb thiz)
{
    LOGPRINTF("entry");

    if (thiz == NULL) return;

    if (thiz->database) {   // close database if open
        // abort transaction, if any
        if (thiz->transaction_nesting != 0)
        {
            thiz->transaction_nesting = 1;
            sql3_commit_or_rollback(thiz, ER_FAIL);
        }

        // close database
        int rc = sqlite3_close(thiz->database);
        if (rc != SQLITE_OK)
        {
            ERRORPRINTF("sqlite close error [%d] [%s]", rc, sqlite3_errmsg(thiz->database));
        }
        thiz->database = NULL;
    }
    g_free(thiz->directory);
    g_free(thiz);
}


// create a database file from template dir
static int create_database(const char *source_name, const char *target_dir, const char *target_name)
{
    // determine path for database files
    gchar *file_source = g_strdup_printf("%s/%s",  SYSCONFDIR, source_name);
    gchar *file_target = g_strdup_printf("%s/%s",  target_dir, target_name);
    gchar *file_target_old = g_strdup_printf("%s.old", file_target);

    // remove existing .old file, if present
    unlink(file_target_old);
    // rename current file (if any) to .old
    rename(file_target, file_target_old);

    // copy source database to specified location
    unsigned int argc = 0;
    const char *argv[10];
    argv[argc++] = "cp";
    argv[argc++] = "-f";
    argv[argc++] = file_source;
    argv[argc++] = file_target;
    argv[argc]   = NULL;
    g_assert(argc < sizeof(argv)/sizeof(argv[0]));
    gint stat;
    GError *err = NULL;
    gboolean ok = g_spawn_sync( NULL,                   // working directory: inherit
                       (char**)argv,           // child's argument vector
                       NULL,                   // environment: inherit
                       G_SPAWN_SEARCH_PATH,    // flags
                       NULL,                   // child_setup: none
                       NULL,                   // child setup data: none
                       NULL,                   // stdout: not interested
                       NULL,                   // stderr: not interested
                       &stat,                  // exit status
                       &err                );  // error return
    int ret = ER_OK;
    if ( !ok )
    {
        // error: command not executed
        ERRORPRINTF("g_spawn_sync error [%s] on copy [%s] [%s]", err->message, file_source, file_target);
        g_clear_error(&err);
    }
    else
    {
        // command executed: check its exit status
        if ( !WIFEXITED(stat) )
        {
            // error: something weird happened
            ERRORPRINTF("g_spawn_sync ok but child not exited, stat [0x%04X]", stat);
            ret = ER_FAIL;
        }
        else
        {
            // ok: check exit status
            int rc = WEXITSTATUS(stat);
            if (rc != 0)
            {
                // command failed
                ERRORPRINTF( "Failed to copy [%s] [%s], error [%d] [%s]",
                             file_source,
                             file_target,
                             rc,
                             strerror(rc) );
                switch (rc)
                {
                    case EROFS:
                    case EPERM:
                        ret = ER_READONLY;
                        break;
                    default:
                        ret = ER_FAIL;
                }
            }
        }
    }
    g_free(file_target_old);
    g_free(file_target    );
    g_free(file_source    );
    return ret;
}


int ermetadb_global_create_database(const gchar *folder)
{
    LOGPRINTF("folder [%s]", folder);

    g_assert(folder && folder[0]);

    return create_database(ERMETADB_GLOBAL_DATABASE_FILE, folder, ERMETADB_GLOBAL_DATABASE_FILE);
}


int ermetadb_local_create_database(const gchar *folder, const gchar* filename)
{
    LOGPRINTF("folder [%s]  file [%s]", folder, filename);

    g_assert(folder && folder[0]);
    g_assert(filename && filename[0]);

    return create_database(ERMETADB_LOCAL_DATABASE_FILE, folder, filename);
}


const char* const * ermetadb_private_get_global_column_names(const char* table)
{
    LOGPRINTF("entry: table [%s]", table);
    g_assert(table);

    int i;
    for (i = 0 ; global_database_tables[i].table_name; i++)
    {
        if ( strcmp(global_database_tables[i].table_name, table) == 0 )
        {
            return global_database_tables[i].column_names;
        }
    }

    return NULL;
}


int ermetadb_begin_transaction (erMetadb thiz)
{
    LOGPRINTF("entry nesting [%d]", thiz->transaction_nesting);

    g_assert(thiz);
    g_assert(thiz->database);
    g_return_val_if_fail( thiz->transaction_nesting == 0, ER_FORBIDDEN );

    return sql3_begin_transaction(thiz);
}


int ermetadb_begin_transaction_readonly (erMetadb thiz)
{
    LOGPRINTF("entry nesting [%d]", thiz->transaction_nesting);

    g_assert(thiz);
    g_assert(thiz->database);
    g_return_val_if_fail( thiz->transaction_nesting == 0, ER_FORBIDDEN );

    return sql3_begin_transaction_readonly(thiz);
}


int ermetadb_end_transaction (erMetadb thiz)
{
    LOGPRINTF("entry nesting [%d]", thiz->transaction_nesting);

    g_assert(thiz);
    g_assert(thiz->database);

    if (thiz->transaction_nesting != 1)
    {
        // nesting level unbalanced: force rollback
        WARNPRINTF("entry with transaction_nesting [%d]", thiz->transaction_nesting);
        thiz->transaction_nesting = 1;
        thiz->do_force_rollback   = TRUE;
    }

    return sql3_commit_or_rollback(thiz, ER_OK);
}