ermetadb_file.c

Go to the documentation of this file.

/*
 * File Name  : ermetadb_file.c
 *
 * Description: Functions to access tables 'file_metadata' and 'thunbnails'
 */

/*
 * 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
//----------------------------------------------------------------------------

// system include files, between < >
#include <glib.h>
#include <limits.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>

// ereader include files, between < >
#include <liberutils/er_error.h>

// local  files, between " "
#include "ermetadb_log.h"
#include "ermetadb.h"
#include "ermetadb_error.h"
#include "ermetadb_private.h"
#include "sqlite3_wrapper.h"


#define MDB_SORT_PRIORITY_FILE              10
#define MDB_SORT_PRIORITY_FOLDER            20

#define CHECK_GLOBAL \
    g_assert(thiz); \
    g_assert(thiz->database); \
    g_assert(thiz->is_global);

//----------------------------------------------------------------------------
// Type Declarations
//----------------------------------------------------------------------------

typedef enum
        {
            ISDIR_DOCUMENT = 0,
            ISDIR_FOLDER,
            ISDIR_DONT_CARE,
        } is_directory_value;


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

static const gchar *FILE_METADATA_DIRPATH_DEFAULT = ".";


//============================================================================
// Functions Implementation
//============================================================================

// copied from libgnome-2.6.1.1 gnome-util.c:
/**
 * g_extension_pointer:
 * @path: A filename or file path.
 *
 * Extracts the extension from the end of a filename (the part after the final
 * '.' in the filename).
 *
 * Returns: A pointer to the extension part of the filename, or a
 * pointer to the end of the string if the filename does not
 * have an extension.
 */
const char *g_extension_pointer (const char *path)
{
   char * s, * t;

   g_return_val_if_fail(path != NULL, NULL);

   /* get the dot in the last element of the path */
   t = strrchr(path, G_DIR_SEPARATOR);
   if (t != NULL)
      s = strrchr(t, '.');
   else
      s = strrchr(path, '.');

   if (s == NULL)
      return path + strlen(path); /* There is no extension. */
   else {
      ++s;      /* pass the . */
      return s;
   }
}


static gint64 get_global_id ( erMetadb thiz,
                            const gchar *filename,
                            const gchar *filepath,
                            const is_directory_value is_dir)
{
    TRACE( "entry: filename [%s] filepath [%s] is_dir [%d]",
               filename, filepath, is_dir );

    // build query
    GString *sql = g_string_new("");
    char *sql3_filename = sqlite3_mprintf("%Q", filename);
    char *sql3_dirpath  = sqlite3_mprintf("%Q", filepath);
    g_string_printf( sql, "SELECT file_id FROM file_metadata"
                          " WHERE filename = %s"
                            " AND directory_path = %s",
                     sql3_filename,
                     sql3_dirpath  );
    sqlite3_free( sql3_dirpath  );
    sqlite3_free( sql3_filename );

    switch (is_dir)
    {
        case ISDIR_DOCUMENT:
            g_string_append(sql, " AND is_directory = 0");
            break;
        case ISDIR_FOLDER:
            g_string_append(sql, " AND is_directory = 1");
            break;
        case ISDIR_DONT_CARE:
            // ignore
            break;
    }
    g_string_append(sql, " ORDER BY file_id LIMIT 1;");

    // execute query
    metadata_table *result = NULL;
    int rc = sql3_execute_query(thiz->database, sql->str, NULL, &result);
    gint64 file_id = -1;
    if (rc == ER_OK  &&  result)
    {
        // filename present: get file_id
        const metadata_cell *cell = metadata_table_get_cell(result, 0);
        if (cell->type != METADATA_INT64)
        {
            ERRORPRINTF( "filename [%s] invalid file_id type [%d] from database",
                         filename, cell->type );
        }
        else
        {
            file_id = cell->value.v_int64;
        }
    }

    metadata_table_free(result);
    g_string_free(sql, TRUE);
    return file_id;
}


// add document or folder
static int add_filename ( erMetadb       thiz,
                          const gchar    *filepath,
                          const gchar    *filename,
                          const gboolean is_directory,
                          const gint64   size,
                          const gint64   time_modified,
                          const gchar    *title,
                          const gchar    *author,
                          const gchar    *tag)
{
    TRACE("entry: path [%s] file [%s]", filepath, filename);

    CHECK_GLOBAL
    g_assert(filename && filename[0]);
    g_assert(filepath && filepath[0]);

    int ret = sql3_begin_transaction(thiz);
    if (ret != ER_OK) return ret;

    // add entry
    if (ret == ER_OK)
    {
        char *sql3_filename  = sqlite3_mprintf( "%Q", filename );
        char* extension;
        if (is_directory) extension = g_strdup("");
        else extension = g_ascii_strdown( g_extension_pointer(filename), -1 );
        char *sql3_extension = sqlite3_mprintf( "%Q", extension     );
        char *sql3_dirpath   = sqlite3_mprintf( "%Q", filepath ? filepath
                                                         : FILE_METADATA_DIRPATH_DEFAULT );
        char *sql3_title     = sqlite3_mprintf( "%Q", title ? title : filename);
        char *sql3_author    = sqlite3_mprintf( "%Q", author ? author : "");
        char *sql3_tag       = sqlite3_mprintf( "%Q", tag ? tag : "");

        GString *sql = g_string_new("");
        gint64 local_time = time(NULL);
        g_string_printf( sql, "INSERT INTO file_metadata"
                              " (filename, directory_path, sort_priority, is_directory,"
                               " file_type, file_size, file_time_modified, file_time_added,"
                               " title, author, tag)"
                              " VALUES (%s, %s, '%d', '%d',"
                                      " %s, '%lld', '%lld', '%lld',"
                                      " %s, %s, %s);",
                         sql3_filename,
                         sql3_dirpath,
                         is_directory ? MDB_SORT_PRIORITY_FOLDER : MDB_SORT_PRIORITY_FILE,
                         is_directory ? 1 : 0,
                         sql3_extension,
                         size,
                         time_modified,
                         local_time,
                         sql3_title,
                         sql3_author,
                         sql3_tag ); 

        sqlite3_free( sql3_dirpath   );
        sqlite3_free( sql3_extension );
        sqlite3_free( sql3_filename  );
        sqlite3_free( sql3_title     );
        sqlite3_free( sql3_tag       );
        g_free(extension);

        int rc = sql3_execute_query(thiz->database, sql->str, NULL, NULL);
        g_string_free(sql, TRUE);
        if (rc != ER_OK)
        {
            ERRORPRINTF("cannot insert filename [%s]", filename);
            ret = ERMETADB_FAIL;
        }
    }

    ret = sql3_commit_or_rollback(thiz, ret);
    return ret;
}


int ermetadb_global_add_folder (erMetadb     thiz,
                               const gchar  *filepath,
                               const gchar  *foldername,
                               const gint64 time_modified )
{
    LOGPRINTF("folder [%s]  file [%s]", filepath, foldername);

    return add_filename(thiz, filepath, foldername, TRUE, 0, time_modified, NULL, NULL, NULL);
}


int ermetadb_global_add_file (erMetadb thiz,
                              const gchar  *filepath,
                              const gchar  *filename,
                              const gint64  size,
                              const gint64  time_modified,
                              const gchar  *title,
                              const gchar  *author,
                              const gchar  *tag)
{
    LOGPRINTF("folder [%s]  file [%s]", filepath, filename);

    return add_filename(thiz, filepath, filename, FALSE, size, time_modified, title, author, tag);
}


int ermetadb_global_rename_file (erMetadb    thiz,
                                 const gchar *filepath,
                                 const gchar *filename,
                                 const gchar *new_filename,
                                 const gchar *new_title)
{
    LOGPRINTF("folder [%s]  file [%s->%s]", filepath, filename, new_filename);

    CHECK_GLOBAL;
    g_assert(filepath && filepath[0]);
    g_assert(filename && filename[0]);
    g_assert(new_filename && new_filename[0]);
    g_assert(new_title && new_title[0]);

    char *sql_path    = sqlite3_mprintf("%Q", filepath);
    char *sql_srcfile = sqlite3_mprintf("%Q", filename);
    char *sql_dstfile = sqlite3_mprintf("%Q", new_filename);
    char *sql_title   = sqlite3_mprintf("%Q", new_title);

    // in case of overwrite: first delete old occurence, case-insensitive!
    GString *delq = g_string_new("");
    g_string_printf(delq , "DELETE FROM file_metadata "
                           " WHERE filename LIKE %s" 
                           " AND directory_path = %s",
                     sql_dstfile,
                     sql_path);
    (void) sql3_execute_query(thiz->database, delq->str, NULL, NULL);
    g_string_free(delq, TRUE);

    GString *sql = g_string_new("");
    g_string_printf( sql, "UPDATE file_metadata SET"
                          " filename = %s"
                          " , title = %s"
                          " WHERE filename = %s"
                          " AND directory_path = %s",
                     sql_dstfile,
                     sql_title,
                     sql_srcfile,
                     sql_path);
    int ret = sql3_execute_query(thiz->database, sql->str, NULL, NULL);

    g_string_free(sql, TRUE);
    sqlite3_free( sql_path );
    sqlite3_free( sql_srcfile );
    sqlite3_free( sql_dstfile );
    sqlite3_free( sql_title );

    ret = sql3_commit_or_rollback(thiz, ret);
    return ret;
}


// no transaction, can be global or local
static int delete_entry(erMetadb thiz, gint64 file_id)
{
    // NOTE: thumbnails are deleted by trigger of delete
    char query[255];
    snprintf(query, sizeof(query), "DELETE FROM file_metadata WHERE file_id = %lld;", file_id);
    return sql3_execute_query(thiz->database, query, NULL, NULL);
}


int ermetadb_global_remove_folder (erMetadb thiz, const gchar *filepath, const gchar *filename)
{
    LOGPRINTF("folder [%s]  file [%s]", filepath, filename);

    CHECK_GLOBAL
    g_assert(filepath && filepath[0]);
    g_assert(filename && filename[0]);

    gint64 file_id = get_global_id (thiz, filename, filepath, ISDIR_FOLDER);
    if (file_id == -1) return ER_NOT_FOUND;

    // delete all underlying entries with trumbnails (and metadata)
    int ret = sql3_begin_transaction(thiz);
    if (ret != ER_OK) return ER_FAIL;

    delete_entry(thiz, file_id);

    // Also delete items that are in the directory.
    // ASSUMES that directory is really deleted from FS, so local metadb info
    // will be deleted automatically
    // NOTE: thumbnail data is deleted by database trigger
    GString *sql = g_string_new("");
    g_string_printf(sql, "DELETE FROM file_metadata WHERE directory_path LIKE '%s/%s%%';", filepath, filename);
    ret = sql3_execute_query(thiz->database, sql->str, NULL, NULL);
    g_string_free(sql, TRUE);

    ret = sql3_commit_or_rollback(thiz, ret);
    return ret;
}


int ermetadb_global_remove_file (erMetadb thiz, const gchar *filepath, const gchar *filename)
{
    LOGPRINTF("folder [%s]  file [%s]", filepath, filename);

    CHECK_GLOBAL
    g_assert(filepath && filepath[0]);
    g_assert(filename && filename[0]);

    gint64 file_id = get_global_id (thiz, filename, filepath, ISDIR_DOCUMENT);
    if (file_id == -1) return ER_NOT_FOUND;

    return ermetadb_global_remove_entry(thiz, file_id, filepath, filename, FALSE);
}


// deletes all metadata for a file from a local database
// NOTE: local metadata.db (or directory) doesn't have to exist anymore!!
static int delete_local_metadata(const gchar* filepath, const gchar* filename)
{
    // try to open local database
    erMetadb thiz = ermetadb_local_open(filepath, FALSE);
    if (thiz) {
        int ret = local_delete_all_data_for_file(thiz, filename);
        ermetadb_close(thiz);
        return ret;
    }

    return ER_OK;   // no database, ok
}


int ermetadb_global_remove_entry(erMetadb thiz,
                              gint64 file_id,
                              const gchar* filepath,
                              const gchar* filename,
                              gboolean is_dir)
{
    LOGPRINTF("id [%lld]  folder [%s]  file [%s]", file_id, filepath, filename);

    CHECK_GLOBAL
    g_assert(file_id > 0);
    g_assert(filepath && filepath[0]);
    g_assert(filename && filename[0]);

    int res = delete_entry(thiz, file_id);

    if (!is_dir) { // for a dir, there is no metadata
        delete_local_metadata(filepath, filename);
    }

    return res;
}


static int set_file_metadata_impl ( erMetadb             thiz,
                                    const gchar          *filepath,
                                    const gchar          *filename,
                                    const metadata_table *values_tbl )
{
    TRACE("entry");

    CHECK_GLOBAL
    g_assert(filepath && filepath[0]);
    g_assert(filename && filename[0]);
    g_return_val_if_fail(  values_tbl                             , ER_INVALID_PARAMETER );
    g_return_val_if_fail( (metadata_table_n_rows(values_tbl) == 1), ER_INVALID_PARAMETER );

    gint64 file_id = get_global_id(thiz, filename, filepath, ISDIR_DONT_CARE);
    if (file_id < 0) return ER_NOT_FOUND;

    int ret = sql3_begin_transaction(thiz);
    if (ret != ER_OK) return ret;

    // build sql query per table
    GString *sql_m = g_string_new("");  // SQL query to update file_metadata
    GString *sql_t = g_string_new("");  // SQL query to update thumbnails

    const char* const *names_m = ermetadb_private_get_global_column_names("file_metadata");
    g_assert(names_m);
    const char* const *names_t = ermetadb_private_get_global_column_names("thumbnails"   );
    g_assert(names_t);

    int n_cols = metadata_table_n_columns(values_tbl);
    int col;
    for (col = 0 ; col < n_cols ; col++)
    {
        const metadata_cell *cell = metadata_table_get_cell(values_tbl, col);
        if (cell && cell->name && cell->name->str)
        {
            const gchar *cell_name = cell->name->str;

            // find column in file_metadata
            gboolean found = FALSE;
            const char* const *name;
            for (name = names_m ; *name && !found ; name++)
            {
                if (strcmp(cell_name, *name) == 0)
                {
                    found = TRUE;
                }
            }
            if (found)
            {
                if (sql_m->len == 0)
                {
                    g_string_assign(sql_m, "UPDATE file_metadata SET ");
                }
                else
                {
                    g_string_append(sql_m, ", ");
                }
                g_string_append_printf(sql_m, "%s = ?%d", cell_name, col + 1);
            }

            // find column in thumbnails
            found = FALSE;
            for (name = names_t ; *name && !found ; name++)
            {
                if (strcmp(cell_name, *name) == 0)
                {
                    found = TRUE;
                }
            }
            if (found)
            {
                if (sql_t->len == 0)
                {
                    g_string_printf( sql_t, "INSERT OR IGNORE INTO thumbnails (file_id) VALUES (%lld);"
                                            "UPDATE thumbnails SET ",
                                     file_id );
                }
                else
                {
                    g_string_append(sql_t, ", ");
                }
                g_string_append_printf(sql_t, "%s = ?%d", cell_name, col + 1);
            }
        }
    }
    if (sql_m->len > 0)
    {
        g_string_append_printf(sql_m, " WHERE file_id = %lld;", file_id);
    }
    if (sql_t->len > 0)
    {
        g_string_append_printf(sql_t, " WHERE file_id = %lld;", file_id);
    }

    // execute query on file_metadata
    if (ret == ER_OK  &&  sql_m->len > 0)
    {
        int rc = sql3_execute_query(thiz->database, sql_m->str, values_tbl, NULL);
        if (rc != ER_OK)
        {
            ERRORPRINTF("cannot set file_metadata, sql [%s]", sql_m->str);
            ret = ER_NOT_FOUND;
        }
    }

    // execute query on thumbnails
    if (ret == ER_OK  &&  sql_t->len > 0)
    {
        int rc = sql3_execute_query(thiz->database, sql_t->str, values_tbl, NULL);
        if (rc != ER_OK)
        {
            ERRORPRINTF("cannot set thumbnails, sql [%s]", sql_t->str);
            ret = ER_NOT_FOUND;
        }
    }

    // commit changes to database
    ret = sql3_commit_or_rollback(thiz, ret);

    // clean up
    g_string_free(sql_t, TRUE);
    g_string_free(sql_m, TRUE);

    return ret;
}


int ermetadb_global_change_file (erMetadb              thiz,
                                 const gchar           *filepath,
                                 const gchar           *filename,
                                 const metadata_table  *values_tbl)
{
    LOGPRINTF("folder [%s]  file [%s]", filepath, filename);

    return set_file_metadata_impl( thiz, filepath, filename, values_tbl );
}


int ermetadb_global_update_size(erMetadb thiz,
                                gint64 file_id,
                                gint64 last_modified,
                                gint64 filesize)
{
    LOGPRINTF("id [%lld]", file_id);

    CHECK_GLOBAL
    g_assert(file_id > 0);

    char query[255];
    snprintf(query, sizeof(query),
        "Update file_metadata SET file_size=%lld, file_time_modified=%lld WHERE file_id = %lld;",
        filesize, last_modified, file_id);
    if (!sql3_execute_query(thiz->database, query, NULL, NULL)) {
        return ER_NOT_FOUND;
    }
    return ER_OK;
}


int ermetadb_global_update_title(erMetadb thiz,
                                 gint64 file_id,
                                 const gchar *title,
                                 const gchar *author)
{
    LOGPRINTF("id [%lld]", file_id);

    CHECK_GLOBAL
    g_assert(file_id > 0);
    g_assert(title);
    g_assert(author);

    char *sql3_title = sqlite3_mprintf("%Q", title);
    char *sql3_author = sqlite3_mprintf("%Q", author);
    char query[255];
    snprintf(query, sizeof(query),
        "Update file_metadata SET title=%s, author=%s WHERE file_id = %lld;",
        sql3_title, sql3_author, file_id);
    sqlite3_free(sql3_title);
    sqlite3_free(sql3_author);

    if (!sql3_execute_query(thiz->database, query, NULL, NULL)) {
        return ER_NOT_FOUND;
    }
    return ER_OK;
}


static GString* get_column_names(const metadata_table *names_tbl)
{
    GString *columns = g_string_new("");
    int n_cols = metadata_table_n_columns(names_tbl);
    int col;
    for (col = 0 ; col < n_cols ; col++)
    {
        const metadata_cell *cell = metadata_table_get_cell(names_tbl, col);
        if (cell && cell->name && cell->name->str)
        {
            if (columns->len > 0)
            {
                g_string_append(columns, ", ");
            }
            g_string_append(columns, cell->name->str);
        }
    }
    return columns;
}


int ermetadb_global_get_file (erMetadb              thiz,
                              const gchar           *filepath,
                              const gchar           *filename,
                              const metadata_table  *names_tbl,
                              metadata_table        **values_tbl)
{
    LOGPRINTF("folder [%s]  file [%s]", filepath, filename);

    CHECK_GLOBAL
    g_assert(filename && filename[0]);
    g_assert(filepath && filepath[0]);
    g_return_val_if_fail( (metadata_table_n_rows(names_tbl) == 0), ER_INVALID_PARAMETER );
    g_return_val_if_fail( (values_tbl && *values_tbl == NULL    ), ER_INVALID_PARAMETER );

    gint64 file_id = get_global_id(thiz, filename, filepath, ISDIR_DONT_CARE);
    if (file_id < 0) return ER_NOT_FOUND;

    int ret = sql3_begin_transaction_readonly(thiz);
    if (ret != ER_OK) return ret;

    GString *columns = NULL;
    // build column names for sql query
    if (names_tbl)
    {
        columns = get_column_names(names_tbl);
    }
    else
    {
        columns = g_string_new("");
        const char* const *name;
        // get all known columns
        for ( name = ermetadb_private_get_global_column_names("file_metadata") ; *name ; name++ )
        {
            if (   strcmp(*name, "file_id"        ) != 0
                && strcmp(*name, "filename"       ) != 0
                && strcmp(*name, "directory_path" ) != 0
                && strcmp(*name, "file_type"      ) != 0 )
            {
                if (columns->len > 0)
                {
                    g_string_append( columns, ", "  );
                }
                g_string_append( columns, *name );
            }
        }
        for ( name = ermetadb_private_get_global_column_names("thumbnails") ; *name ; name++ )
        {
            if ( strcmp(*name, "file_id") != 0 )
            {
                if (columns->len > 0)
                {
                    g_string_append( columns, ", "  );
                }
                g_string_append( columns, *name );
            }
        }
    }

    // build query
    GString *sql = g_string_new("");
    g_string_printf( sql, "SELECT %s FROM file_metadata AS m"
                          " LEFT JOIN thumbnails AS t"
                          " ON m.file_id = t.file_id"
                          " WHERE m.file_id=%lld",
                     columns->str,
                     file_id      );
    g_string_free(columns, TRUE);

    // execute query
    metadata_table *result = NULL;
    if (ret == ER_OK)
    {
        int rc = sql3_execute_query(thiz->database, sql->str, NULL, &result);
        if (rc != ER_OK)
        {
            ERRORPRINTF("cannot get metadata, sql [%s]", sql->str);
            ret = rc;
        }
    }
    g_string_free(sql, TRUE);

    ret = sql3_commit_or_rollback(thiz, ret);

    *values_tbl = result;
    return ret;
}


int ermetadb_global_select_all (erMetadb thiz, metadata_table **values_tbl)
{
    LOGPRINTF("entry");

    CHECK_GLOBAL
    g_return_val_if_fail( (values_tbl && *values_tbl == NULL), ER_INVALID_PARAMETER );

    const char* sql = "SELECT file_id,"
                      " filename,"
                      " directory_path,"
                      " is_directory,"
                      " file_size,"
                      " file_time_modified"
                      " FROM file_metadata";
    metadata_table *result = NULL;
    int ret = sql3_execute_query(thiz->database, sql, NULL, &result);
    if (ret != ER_OK)
    {
        ERRORPRINTF("cannot get filenames");
    }

    *values_tbl = result;
    return ret;
}


static int global_select_common(erMetadb             thiz,
                                const gchar          *sort_key,
                                gboolean             sort_is_ascending,
                                long                 num_items,
                                const metadata_table *names_tbl,
                                metadata_table       **values_tbl,
                                const gchar          *extra_sql )
{
    CHECK_GLOBAL
    g_assert(extra_sql);

    // build query
    GString *columns = get_column_names(names_tbl);
    GString *sql = g_string_new("");
    g_string_printf(            sql, "SELECT %s FROM file_metadata AS m"
                                     " LEFT JOIN thumbnails AS t"
                                     " ON m.file_id = t.file_id"
                                     " %s "
                                     " ORDER BY"
                                     "  sort_priority DESC,"
                                     "  (CASE WHEN %s IS NOT NULL THEN 0 ELSE 1 END)",
                                columns->str, extra_sql, sort_key);
    g_string_free(columns, TRUE);

    if (sort_key)
    {
        g_string_append_printf( sql, ", %s", sort_key);

        if (   strcmp(sort_key, "filename") == 0
            || strcmp(sort_key, "title"   ) == 0
            || strcmp(sort_key, "author"  ) == 0 )
        {
            g_string_append(    sql, " COLLATE " SQLITE_COLLATION_STRCASECMP );
        }
        g_string_append(        sql, sort_is_ascending ? " ASC" : " DESC" );

        if ( strcmp(sort_key, "filename") != 0 )
        {
            g_string_append(    sql, ", filename COLLATE " SQLITE_COLLATION_STRCASECMP " ASC" );
        }
        g_string_append(        sql, ", directory_path COLLATE " SQLITE_COLLATION_STRCASECMP " ASC" );
    }

    if (num_items > 0)
    {
        g_string_append_printf( sql, " LIMIT %ld",
                                num_items );
    }

    g_string_append(            sql, ";" );

    // execute query
    metadata_table *result = NULL;
    int rc = sql3_execute_query(thiz->database, sql->str, NULL, &result);
    if (rc != ER_OK)
    {
        ERRORPRINTF("cannot get metadata, sql [%s]", sql->str);
        rc = ER_NOT_FOUND;
    }
    g_string_free(sql, TRUE);

    *values_tbl = result;
    return rc;
}


// get selected metadata for a group of files/folders
int ermetadb_global_select_files (erMetadb             thiz,
                                  const gchar          *sort_key,
                                  gboolean             sort_is_ascending,
                                  const metadata_table *names_tbl,
                                  metadata_table       **values_tbl,
                                  const gchar          *tag_filter)
{
    LOGPRINTF("tag_filter [%s]", tag_filter);

    char extra_sql[128];
    extra_sql[0] = 0;
    if (tag_filter) sprintf(extra_sql, "WHERE tag = '%s'", tag_filter);
    int ret = global_select_common(thiz, 
                                   sort_key, 
                                   sort_is_ascending, 
                                   0,
                                   names_tbl,
                                   values_tbl,
                                   extra_sql);
    return ret;
}


int ermetadb_global_select_recent (erMetadb             thiz,
                                   const gchar          *sort_key,
                                   int                  num_items,
                                   const metadata_table *names_tbl,
                                   metadata_table       **values_tbl)
{
    LOGPRINTF("entry");

    int ret = global_select_common(thiz, 
                                   sort_key, 
                                   FALSE, 
                                   num_items,
                                   names_tbl,
                                   values_tbl,
                                   "WHERE tag != ''");
    return ret;
}


int ermetadb_global_select_subdir (erMetadb              thiz,
                                   const gchar           *sort_key,
                                   gboolean              sort_is_ascending,
                                   const metadata_table  *names_tbl,
                                   metadata_table        **values_tbl,
                                   const gchar           *path_filter)
{
    LOGPRINTF("path_filter [%s]", path_filter);

    g_assert(path_filter && path_filter[0]);

    char sql[256];
    sprintf(sql, " WHERE directory_path = '%s'", path_filter);
    int ret = global_select_common(thiz, 
                                   sort_key, 
                                   sort_is_ascending, 
                                   0,
                                   names_tbl,
                                   values_tbl,
                                   sql);
    return ret;
}


int ermetadb_global_select_search (erMetadb              thiz,
                                   const gchar           *sort_key,
                                   gboolean              sort_is_ascending,
                                   const metadata_table  *names_tbl,
                                   metadata_table        **values_tbl,
                                   const gchar*          search_filter)
{
    LOGPRINTF("search_filter [%s]", search_filter);

    g_assert(search_filter && search_filter[0]);

    char sql[1024];
    sprintf(sql, "WHERE (filename LIKE '%%%s%%' or"
                            " title LIKE '%%%s%%' or"
                            " author LIKE '%%%s%%') AND tag != ''",
                            search_filter, search_filter, search_filter);
    int ret = global_select_common(thiz, 
                                   sort_key, 
                                   sort_is_ascending, 
                                   0,
                                   names_tbl,
                                   values_tbl,
                                   sql);
    return ret;
}