ermetadb_file.c

Go to the documentation of this file.
00001 /*
00002  * File Name  : ermetadb_file.c
00003  *
00004  * Description: Functions to access tables 'file_metadata' and 'thunbnails'
00005  */
00006 
00007 /*
00008  * This file is part of libermetadb.
00009  *
00010  * libermetadb is free software: you can redistribute it and/or modify
00011  * it under the terms of the GNU General Public License as published by
00012  * the Free Software Foundation, either version 2 of the License, or
00013  * (at your option) any later version.
00014  *
00015  * libermetadb is distributed in the hope that it will be useful,
00016  * but WITHOUT ANY WARRANTY; without even the implied warranty of
00017  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
00018  * GNU General Public License for more details.
00019  *
00020  * You should have received a copy of the GNU General Public License
00021  * along with this program. If not, see <http://www.gnu.org/licenses/>.
00022  */
00023 
00024 /**
00025  * Copyright (C) 2008 iRex Technologies B.V.
00026  * All rights reserved.
00027  */
00028 
00029 //----------------------------------------------------------------------------
00030 // Include Files
00031 //----------------------------------------------------------------------------
00032 
00033 // system include files, between < >
00034 #include <glib.h>
00035 #include <limits.h>
00036 #include <stdlib.h>
00037 #include <string.h>
00038 #include <sys/types.h>
00039 #include <sys/stat.h>
00040 #include <unistd.h>
00041 
00042 // ereader include files, between < >
00043 #include <liberutils/er_error.h>
00044 
00045 // local  files, between " "
00046 #include "ermetadb_log.h"
00047 #include "ermetadb.h"
00048 #include "ermetadb_error.h"
00049 #include "ermetadb_private.h"
00050 #include "sqlite3_wrapper.h"
00051 
00052 
00053 #define MDB_SORT_PRIORITY_FILE              10
00054 #define MDB_SORT_PRIORITY_FOLDER            20
00055 
00056 #define CHECK_GLOBAL \
00057     g_assert(thiz); \
00058     g_assert(thiz->database); \
00059     g_assert(thiz->is_global);
00060 
00061 //----------------------------------------------------------------------------
00062 // Type Declarations
00063 //----------------------------------------------------------------------------
00064 
00065 typedef enum
00066         {
00067             ISDIR_DOCUMENT = 0,
00068             ISDIR_FOLDER,
00069             ISDIR_DONT_CARE,
00070         } is_directory_value;
00071 
00072 
00073 //----------------------------------------------------------------------------
00074 // Global Constants
00075 //----------------------------------------------------------------------------
00076 
00077 static const gchar *FILE_METADATA_DIRPATH_DEFAULT = ".";
00078 
00079 
00080 //============================================================================
00081 // Functions Implementation
00082 //============================================================================
00083 
00084 // copied from libgnome-2.6.1.1 gnome-util.c:
00085 /**
00086  * g_extension_pointer:
00087  * @path: A filename or file path.
00088  *
00089  * Extracts the extension from the end of a filename (the part after the final
00090  * '.' in the filename).
00091  *
00092  * Returns: A pointer to the extension part of the filename, or a
00093  * pointer to the end of the string if the filename does not
00094  * have an extension.
00095  */
00096 const char *g_extension_pointer (const char *path)
00097 {
00098    char * s, * t;
00099 
00100    g_return_val_if_fail(path != NULL, NULL);
00101 
00102    /* get the dot in the last element of the path */
00103    t = strrchr(path, G_DIR_SEPARATOR);
00104    if (t != NULL)
00105       s = strrchr(t, '.');
00106    else
00107       s = strrchr(path, '.');
00108 
00109    if (s == NULL)
00110       return path + strlen(path); /* There is no extension. */
00111    else {
00112       ++s;      /* pass the . */
00113       return s;
00114    }
00115 }
00116 
00117 
00118 static gint64 get_global_id ( erMetadb thiz,
00119                             const gchar *filename,
00120                             const gchar *filepath,
00121                             const is_directory_value is_dir)
00122 {
00123     TRACE( "entry: filename [%s] filepath [%s] is_dir [%d]",
00124                filename, filepath, is_dir );
00125 
00126     // build query
00127     GString *sql = g_string_new("");
00128     char *sql3_filename = sqlite3_mprintf("%Q", filename);
00129     char *sql3_dirpath  = sqlite3_mprintf("%Q", filepath);
00130     g_string_printf( sql, "SELECT file_id FROM file_metadata"
00131                           " WHERE filename = %s"
00132                             " AND directory_path = %s",
00133                      sql3_filename,
00134                      sql3_dirpath  );
00135     sqlite3_free( sql3_dirpath  );
00136     sqlite3_free( sql3_filename );
00137 
00138     switch (is_dir)
00139     {
00140         case ISDIR_DOCUMENT:
00141             g_string_append(sql, " AND is_directory = 0");
00142             break;
00143         case ISDIR_FOLDER:
00144             g_string_append(sql, " AND is_directory = 1");
00145             break;
00146         case ISDIR_DONT_CARE:
00147             // ignore
00148             break;
00149     }
00150     g_string_append(sql, " ORDER BY file_id LIMIT 1;");
00151 
00152     // execute query
00153     metadata_table *result = NULL;
00154     int rc = sql3_execute_query(thiz->database, sql->str, NULL, &result);
00155     gint64 file_id = -1;
00156     if (rc == ER_OK  &&  result)
00157     {
00158         // filename present: get file_id
00159         const metadata_cell *cell = metadata_table_get_cell(result, 0);
00160         if (cell->type != METADATA_INT64)
00161         {
00162             ERRORPRINTF( "filename [%s] invalid file_id type [%d] from database",
00163                          filename, cell->type );
00164         }
00165         else
00166         {
00167             file_id = cell->value.v_int64;
00168         }
00169     }
00170 
00171     metadata_table_free(result);
00172     g_string_free(sql, TRUE);
00173     return file_id;
00174 }
00175 
00176 
00177 // add document or folder
00178 static int add_filename ( erMetadb       thiz,
00179                           const gchar    *filepath,
00180                           const gchar    *filename,
00181                           const gboolean is_directory,
00182                           const gint64   size,
00183                           const gint64   time_modified,
00184                           const gchar    *title,
00185                           const gchar    *author,
00186                           const gchar    *tag)
00187 {
00188     TRACE("entry: path [%s] file [%s]", filepath, filename);
00189 
00190     CHECK_GLOBAL
00191     g_assert(filename && filename[0]);
00192     g_assert(filepath && filepath[0]);
00193 
00194     int ret = sql3_begin_transaction(thiz);
00195     if (ret != ER_OK) return ret;
00196 
00197     // add entry
00198     if (ret == ER_OK)
00199     {
00200         char *sql3_filename  = sqlite3_mprintf( "%Q", filename );
00201         char* extension;
00202         if (is_directory) extension = g_strdup("");
00203         else extension = g_ascii_strdown( g_extension_pointer(filename), -1 );
00204         char *sql3_extension = sqlite3_mprintf( "%Q", extension     );
00205         char *sql3_dirpath   = sqlite3_mprintf( "%Q", filepath ? filepath
00206                                                          : FILE_METADATA_DIRPATH_DEFAULT );
00207         char *sql3_title     = sqlite3_mprintf( "%Q", title ? title : filename);
00208         char *sql3_author    = sqlite3_mprintf( "%Q", author ? author : "");
00209         char *sql3_tag       = sqlite3_mprintf( "%Q", tag ? tag : "");
00210 
00211         GString *sql = g_string_new("");
00212         gint64 local_time = time(NULL);
00213         g_string_printf( sql, "INSERT INTO file_metadata"
00214                               " (filename, directory_path, sort_priority, is_directory,"
00215                                " file_type, file_size, file_time_modified, file_time_added,"
00216                                " title, author, tag)"
00217                               " VALUES (%s, %s, '%d', '%d',"
00218                                       " %s, '%lld', '%lld', '%lld',"
00219                                       " %s, %s, %s);",
00220                          sql3_filename,
00221                          sql3_dirpath,
00222                          is_directory ? MDB_SORT_PRIORITY_FOLDER : MDB_SORT_PRIORITY_FILE,
00223                          is_directory ? 1 : 0,
00224                          sql3_extension,
00225                          size,
00226                          time_modified,
00227                          local_time,
00228                          sql3_title,
00229                          sql3_author,
00230                          sql3_tag ); 
00231 
00232         sqlite3_free( sql3_dirpath   );
00233         sqlite3_free( sql3_extension );
00234         sqlite3_free( sql3_filename  );
00235         sqlite3_free( sql3_title     );
00236         sqlite3_free( sql3_tag       );
00237         g_free(extension);
00238 
00239         int rc = sql3_execute_query(thiz->database, sql->str, NULL, NULL);
00240         g_string_free(sql, TRUE);
00241         if (rc != ER_OK)
00242         {
00243             ERRORPRINTF("cannot insert filename [%s]", filename);
00244             ret = ERMETADB_FAIL;
00245         }
00246     }
00247 
00248     ret = sql3_commit_or_rollback(thiz, ret);
00249     return ret;
00250 }
00251 
00252 
00253 int ermetadb_global_add_folder (erMetadb     thiz,
00254                                const gchar  *filepath,
00255                                const gchar  *foldername,
00256                                const gint64 time_modified )
00257 {
00258     LOGPRINTF("folder [%s]  file [%s]", filepath, foldername);
00259 
00260     return add_filename(thiz, filepath, foldername, TRUE, 0, time_modified, NULL, NULL, NULL);
00261 }
00262 
00263 
00264 int ermetadb_global_add_file (erMetadb thiz,
00265                               const gchar  *filepath,
00266                               const gchar  *filename,
00267                               const gint64  size,
00268                               const gint64  time_modified,
00269                               const gchar  *title,
00270                               const gchar  *author,
00271                               const gchar  *tag)
00272 {
00273     LOGPRINTF("folder [%s]  file [%s]", filepath, filename);
00274 
00275     return add_filename(thiz, filepath, filename, FALSE, size, time_modified, title, author, tag);
00276 }
00277 
00278 
00279 int ermetadb_global_rename_file (erMetadb    thiz,
00280                                  const gchar *filepath,
00281                                  const gchar *filename,
00282                                  const gchar *new_filename,
00283                                  const gchar *new_title)
00284 {
00285     LOGPRINTF("folder [%s]  file [%s->%s]", filepath, filename, new_filename);
00286 
00287     CHECK_GLOBAL;
00288     g_assert(filepath && filepath[0]);
00289     g_assert(filename && filename[0]);
00290     g_assert(new_filename && new_filename[0]);
00291     g_assert(new_title && new_title[0]);
00292 
00293     char *sql_path    = sqlite3_mprintf("%Q", filepath);
00294     char *sql_srcfile = sqlite3_mprintf("%Q", filename);
00295     char *sql_dstfile = sqlite3_mprintf("%Q", new_filename);
00296     char *sql_title   = sqlite3_mprintf("%Q", new_title);
00297 
00298     // in case of overwrite: first delete old occurence, case-insensitive!
00299     GString *delq = g_string_new("");
00300     g_string_printf(delq , "DELETE FROM file_metadata "
00301                            " WHERE filename LIKE %s" 
00302                            " AND directory_path = %s",
00303                      sql_dstfile,
00304                      sql_path);
00305     (void) sql3_execute_query(thiz->database, delq->str, NULL, NULL);
00306     g_string_free(delq, TRUE);
00307 
00308     GString *sql = g_string_new("");
00309     g_string_printf( sql, "UPDATE file_metadata SET"
00310                           " filename = %s"
00311                           " , title = %s"
00312                           " WHERE filename = %s"
00313                           " AND directory_path = %s",
00314                      sql_dstfile,
00315                      sql_title,
00316                      sql_srcfile,
00317                      sql_path);
00318     int ret = sql3_execute_query(thiz->database, sql->str, NULL, NULL);
00319 
00320     g_string_free(sql, TRUE);
00321     sqlite3_free( sql_path );
00322     sqlite3_free( sql_srcfile );
00323     sqlite3_free( sql_dstfile );
00324     sqlite3_free( sql_title );
00325 
00326     ret = sql3_commit_or_rollback(thiz, ret);
00327     return ret;
00328 }
00329 
00330 
00331 // no transaction, can be global or local
00332 static int delete_entry(erMetadb thiz, gint64 file_id)
00333 {
00334     // NOTE: thumbnails are deleted by trigger of delete
00335     char query[255];
00336     snprintf(query, sizeof(query), "DELETE FROM file_metadata WHERE file_id = %lld;", file_id);
00337     return sql3_execute_query(thiz->database, query, NULL, NULL);
00338 }
00339 
00340 
00341 int ermetadb_global_remove_folder (erMetadb thiz, const gchar *filepath, const gchar *filename)
00342 {
00343     LOGPRINTF("folder [%s]  file [%s]", filepath, filename);
00344 
00345     CHECK_GLOBAL
00346     g_assert(filepath && filepath[0]);
00347     g_assert(filename && filename[0]);
00348 
00349     gint64 file_id = get_global_id (thiz, filename, filepath, ISDIR_FOLDER);
00350     if (file_id == -1) return ER_NOT_FOUND;
00351 
00352     // delete all underlying entries with trumbnails (and metadata)
00353     int ret = sql3_begin_transaction(thiz);
00354     if (ret != ER_OK) return ER_FAIL;
00355 
00356     delete_entry(thiz, file_id);
00357 
00358     // Also delete items that are in the directory.
00359     // ASSUMES that directory is really deleted from FS, so local metadb info
00360     // will be deleted automatically
00361     // NOTE: thumbnail data is deleted by database trigger
00362     GString *sql = g_string_new("");
00363     g_string_printf(sql, "DELETE FROM file_metadata WHERE directory_path LIKE '%s/%s%%';", filepath, filename);
00364     ret = sql3_execute_query(thiz->database, sql->str, NULL, NULL);
00365     g_string_free(sql, TRUE);
00366 
00367     ret = sql3_commit_or_rollback(thiz, ret);
00368     return ret;
00369 }
00370 
00371 
00372 int ermetadb_global_remove_file (erMetadb thiz, const gchar *filepath, const gchar *filename)
00373 {
00374     LOGPRINTF("folder [%s]  file [%s]", filepath, filename);
00375 
00376     CHECK_GLOBAL
00377     g_assert(filepath && filepath[0]);
00378     g_assert(filename && filename[0]);
00379 
00380     gint64 file_id = get_global_id (thiz, filename, filepath, ISDIR_DOCUMENT);
00381     if (file_id == -1) return ER_NOT_FOUND;
00382 
00383     return ermetadb_global_remove_entry(thiz, file_id, filepath, filename, FALSE);
00384 }
00385 
00386 
00387 // deletes all metadata for a file from a local database
00388 // NOTE: local metadata.db (or directory) doesn't have to exist anymore!!
00389 static int delete_local_metadata(const gchar* filepath, const gchar* filename)
00390 {
00391     // try to open local database
00392     erMetadb thiz = ermetadb_local_open(filepath, FALSE);
00393     if (thiz) {
00394         int ret = local_delete_all_data_for_file(thiz, filename);
00395         ermetadb_close(thiz);
00396         return ret;
00397     }
00398 
00399     return ER_OK;   // no database, ok
00400 }
00401 
00402 
00403 int ermetadb_global_remove_entry(erMetadb thiz,
00404                               gint64 file_id,
00405                               const gchar* filepath,
00406                               const gchar* filename,
00407                               gboolean is_dir)
00408 {
00409     LOGPRINTF("id [%lld]  folder [%s]  file [%s]", file_id, filepath, filename);
00410 
00411     CHECK_GLOBAL
00412     g_assert(file_id > 0);
00413     g_assert(filepath && filepath[0]);
00414     g_assert(filename && filename[0]);
00415 
00416     int res = delete_entry(thiz, file_id);
00417 
00418     if (!is_dir) { // for a dir, there is no metadata
00419         delete_local_metadata(filepath, filename);
00420     }
00421 
00422     return res;
00423 }
00424 
00425 
00426 static int set_file_metadata_impl ( erMetadb             thiz,
00427                                     const gchar          *filepath,
00428                                     const gchar          *filename,
00429                                     const metadata_table *values_tbl )
00430 {
00431     TRACE("entry");
00432 
00433     CHECK_GLOBAL
00434     g_assert(filepath && filepath[0]);
00435     g_assert(filename && filename[0]);
00436     g_return_val_if_fail(  values_tbl                             , ER_INVALID_PARAMETER );
00437     g_return_val_if_fail( (metadata_table_n_rows(values_tbl) == 1), ER_INVALID_PARAMETER );
00438 
00439     gint64 file_id = get_global_id(thiz, filename, filepath, ISDIR_DONT_CARE);
00440     if (file_id < 0) return ER_NOT_FOUND;
00441 
00442     int ret = sql3_begin_transaction(thiz);
00443     if (ret != ER_OK) return ret;
00444 
00445     // build sql query per table
00446     GString *sql_m = g_string_new("");  // SQL query to update file_metadata
00447     GString *sql_t = g_string_new("");  // SQL query to update thumbnails
00448 
00449     const char* const *names_m = ermetadb_private_get_global_column_names("file_metadata");
00450     g_assert(names_m);
00451     const char* const *names_t = ermetadb_private_get_global_column_names("thumbnails"   );
00452     g_assert(names_t);
00453 
00454     int n_cols = metadata_table_n_columns(values_tbl);
00455     int col;
00456     for (col = 0 ; col < n_cols ; col++)
00457     {
00458         const metadata_cell *cell = metadata_table_get_cell(values_tbl, col);
00459         if (cell && cell->name && cell->name->str)
00460         {
00461             const gchar *cell_name = cell->name->str;
00462 
00463             // find column in file_metadata
00464             gboolean found = FALSE;
00465             const char* const *name;
00466             for (name = names_m ; *name && !found ; name++)
00467             {
00468                 if (strcmp(cell_name, *name) == 0)
00469                 {
00470                     found = TRUE;
00471                 }
00472             }
00473             if (found)
00474             {
00475                 if (sql_m->len == 0)
00476                 {
00477                     g_string_assign(sql_m, "UPDATE file_metadata SET ");
00478                 }
00479                 else
00480                 {
00481                     g_string_append(sql_m, ", ");
00482                 }
00483                 g_string_append_printf(sql_m, "%s = ?%d", cell_name, col + 1);
00484             }
00485 
00486             // find column in thumbnails
00487             found = FALSE;
00488             for (name = names_t ; *name && !found ; name++)
00489             {
00490                 if (strcmp(cell_name, *name) == 0)
00491                 {
00492                     found = TRUE;
00493                 }
00494             }
00495             if (found)
00496             {
00497                 if (sql_t->len == 0)
00498                 {
00499                     g_string_printf( sql_t, "INSERT OR IGNORE INTO thumbnails (file_id) VALUES (%lld);"
00500                                             "UPDATE thumbnails SET ",
00501                                      file_id );
00502                 }
00503                 else
00504                 {
00505                     g_string_append(sql_t, ", ");
00506                 }
00507                 g_string_append_printf(sql_t, "%s = ?%d", cell_name, col + 1);
00508             }
00509         }
00510     }
00511     if (sql_m->len > 0)
00512     {
00513         g_string_append_printf(sql_m, " WHERE file_id = %lld;", file_id);
00514     }
00515     if (sql_t->len > 0)
00516     {
00517         g_string_append_printf(sql_t, " WHERE file_id = %lld;", file_id);
00518     }
00519 
00520     // execute query on file_metadata
00521     if (ret == ER_OK  &&  sql_m->len > 0)
00522     {
00523         int rc = sql3_execute_query(thiz->database, sql_m->str, values_tbl, NULL);
00524         if (rc != ER_OK)
00525         {
00526             ERRORPRINTF("cannot set file_metadata, sql [%s]", sql_m->str);
00527             ret = ER_NOT_FOUND;
00528         }
00529     }
00530 
00531     // execute query on thumbnails
00532     if (ret == ER_OK  &&  sql_t->len > 0)
00533     {
00534         int rc = sql3_execute_query(thiz->database, sql_t->str, values_tbl, NULL);
00535         if (rc != ER_OK)
00536         {
00537             ERRORPRINTF("cannot set thumbnails, sql [%s]", sql_t->str);
00538             ret = ER_NOT_FOUND;
00539         }
00540     }
00541 
00542     // commit changes to database
00543     ret = sql3_commit_or_rollback(thiz, ret);
00544 
00545     // clean up
00546     g_string_free(sql_t, TRUE);
00547     g_string_free(sql_m, TRUE);
00548 
00549     return ret;
00550 }
00551 
00552 
00553 int ermetadb_global_change_file (erMetadb              thiz,
00554                                  const gchar           *filepath,
00555                                  const gchar           *filename,
00556                                  const metadata_table  *values_tbl)
00557 {
00558     LOGPRINTF("folder [%s]  file [%s]", filepath, filename);
00559 
00560     return set_file_metadata_impl( thiz, filepath, filename, values_tbl );
00561 }
00562 
00563 
00564 int ermetadb_global_update_size(erMetadb thiz,
00565                                 gint64 file_id,
00566                                 gint64 last_modified,
00567                                 gint64 filesize)
00568 {
00569     LOGPRINTF("id [%lld]", file_id);
00570 
00571     CHECK_GLOBAL
00572     g_assert(file_id > 0);
00573 
00574     char query[255];
00575     snprintf(query, sizeof(query),
00576         "Update file_metadata SET file_size=%lld, file_time_modified=%lld WHERE file_id = %lld;",
00577         filesize, last_modified, file_id);
00578     if (!sql3_execute_query(thiz->database, query, NULL, NULL)) {
00579         return ER_NOT_FOUND;
00580     }
00581     return ER_OK;
00582 }
00583 
00584 
00585 int ermetadb_global_update_title(erMetadb thiz,
00586                                  gint64 file_id,
00587                                  const gchar *title,
00588                                  const gchar *author)
00589 {
00590     LOGPRINTF("id [%lld]", file_id);
00591 
00592     CHECK_GLOBAL
00593     g_assert(file_id > 0);
00594     g_assert(title);
00595     g_assert(author);
00596 
00597     char *sql3_title = sqlite3_mprintf("%Q", title);
00598     char *sql3_author = sqlite3_mprintf("%Q", author);
00599     char query[255];
00600     snprintf(query, sizeof(query),
00601         "Update file_metadata SET title=%s, author=%s WHERE file_id = %lld;",
00602         sql3_title, sql3_author, file_id);
00603     sqlite3_free(sql3_title);
00604     sqlite3_free(sql3_author);
00605 
00606     if (!sql3_execute_query(thiz->database, query, NULL, NULL)) {
00607         return ER_NOT_FOUND;
00608     }
00609     return ER_OK;
00610 }
00611 
00612 
00613 static GString* get_column_names(const metadata_table *names_tbl)
00614 {
00615     GString *columns = g_string_new("");
00616     int n_cols = metadata_table_n_columns(names_tbl);
00617     int col;
00618     for (col = 0 ; col < n_cols ; col++)
00619     {
00620         const metadata_cell *cell = metadata_table_get_cell(names_tbl, col);
00621         if (cell && cell->name && cell->name->str)
00622         {
00623             if (columns->len > 0)
00624             {
00625                 g_string_append(columns, ", ");
00626             }
00627             g_string_append(columns, cell->name->str);
00628         }
00629     }
00630     return columns;
00631 }
00632 
00633 
00634 int ermetadb_global_get_file (erMetadb              thiz,
00635                               const gchar           *filepath,
00636                               const gchar           *filename,
00637                               const metadata_table  *names_tbl,
00638                               metadata_table        **values_tbl)
00639 {
00640     LOGPRINTF("folder [%s]  file [%s]", filepath, filename);
00641 
00642     CHECK_GLOBAL
00643     g_assert(filename && filename[0]);
00644     g_assert(filepath && filepath[0]);
00645     g_return_val_if_fail( (metadata_table_n_rows(names_tbl) == 0), ER_INVALID_PARAMETER );
00646     g_return_val_if_fail( (values_tbl && *values_tbl == NULL    ), ER_INVALID_PARAMETER );
00647 
00648     gint64 file_id = get_global_id(thiz, filename, filepath, ISDIR_DONT_CARE);
00649     if (file_id < 0) return ER_NOT_FOUND;
00650 
00651     int ret = sql3_begin_transaction_readonly(thiz);
00652     if (ret != ER_OK) return ret;
00653 
00654     GString *columns = NULL;
00655     // build column names for sql query
00656     if (names_tbl)
00657     {
00658         columns = get_column_names(names_tbl);
00659     }
00660     else
00661     {
00662         columns = g_string_new("");
00663         const char* const *name;
00664         // get all known columns
00665         for ( name = ermetadb_private_get_global_column_names("file_metadata") ; *name ; name++ )
00666         {
00667             if (   strcmp(*name, "file_id"        ) != 0
00668                 && strcmp(*name, "filename"       ) != 0
00669                 && strcmp(*name, "directory_path" ) != 0
00670                 && strcmp(*name, "file_type"      ) != 0 )
00671             {
00672                 if (columns->len > 0)
00673                 {
00674                     g_string_append( columns, ", "  );
00675                 }
00676                 g_string_append( columns, *name );
00677             }
00678         }
00679         for ( name = ermetadb_private_get_global_column_names("thumbnails") ; *name ; name++ )
00680         {
00681             if ( strcmp(*name, "file_id") != 0 )
00682             {
00683                 if (columns->len > 0)
00684                 {
00685                     g_string_append( columns, ", "  );
00686                 }
00687                 g_string_append( columns, *name );
00688             }
00689         }
00690     }
00691 
00692     // build query
00693     GString *sql = g_string_new("");
00694     g_string_printf( sql, "SELECT %s FROM file_metadata AS m"
00695                           " LEFT JOIN thumbnails AS t"
00696                           " ON m.file_id = t.file_id"
00697                           " WHERE m.file_id=%lld",
00698                      columns->str,
00699                      file_id      );
00700     g_string_free(columns, TRUE);
00701 
00702     // execute query
00703     metadata_table *result = NULL;
00704     if (ret == ER_OK)
00705     {
00706         int rc = sql3_execute_query(thiz->database, sql->str, NULL, &result);
00707         if (rc != ER_OK)
00708         {
00709             ERRORPRINTF("cannot get metadata, sql [%s]", sql->str);
00710             ret = rc;
00711         }
00712     }
00713     g_string_free(sql, TRUE);
00714 
00715     ret = sql3_commit_or_rollback(thiz, ret);
00716 
00717     *values_tbl = result;
00718     return ret;
00719 }
00720 
00721 
00722 int ermetadb_global_select_all (erMetadb thiz, metadata_table **values_tbl)
00723 {
00724     LOGPRINTF("entry");
00725 
00726     CHECK_GLOBAL
00727     g_return_val_if_fail( (values_tbl && *values_tbl == NULL), ER_INVALID_PARAMETER );
00728 
00729     const char* sql = "SELECT file_id,"
00730                       " filename,"
00731                       " directory_path,"
00732                       " is_directory,"
00733                       " file_size,"
00734                       " file_time_modified"
00735                       " FROM file_metadata";
00736     metadata_table *result = NULL;
00737     int ret = sql3_execute_query(thiz->database, sql, NULL, &result);
00738     if (ret != ER_OK)
00739     {
00740         ERRORPRINTF("cannot get filenames");
00741     }
00742 
00743     *values_tbl = result;
00744     return ret;
00745 }
00746 
00747 
00748 static int global_select_common(erMetadb             thiz,
00749                                 const gchar          *sort_key,
00750                                 gboolean             sort_is_ascending,
00751                                 long                 num_items,
00752                                 const metadata_table *names_tbl,
00753                                 metadata_table       **values_tbl,
00754                                 const gchar          *extra_sql )
00755 {
00756     CHECK_GLOBAL
00757     g_assert(extra_sql);
00758 
00759     // build query
00760     GString *columns = get_column_names(names_tbl);
00761     GString *sql = g_string_new("");
00762     g_string_printf(            sql, "SELECT %s FROM file_metadata AS m"
00763                                      " LEFT JOIN thumbnails AS t"
00764                                      " ON m.file_id = t.file_id"
00765                                      " %s "
00766                                      " ORDER BY"
00767                                      "  sort_priority DESC,"
00768                                      "  (CASE WHEN %s IS NOT NULL THEN 0 ELSE 1 END)",
00769                                 columns->str, extra_sql, sort_key);
00770     g_string_free(columns, TRUE);
00771 
00772     if (sort_key)
00773     {
00774         g_string_append_printf( sql, ", %s", sort_key);
00775 
00776         if (   strcmp(sort_key, "filename") == 0
00777             || strcmp(sort_key, "title"   ) == 0
00778             || strcmp(sort_key, "author"  ) == 0 )
00779         {
00780             g_string_append(    sql, " COLLATE " SQLITE_COLLATION_STRCASECMP );
00781         }
00782         g_string_append(        sql, sort_is_ascending ? " ASC" : " DESC" );
00783 
00784         if ( strcmp(sort_key, "filename") != 0 )
00785         {
00786             g_string_append(    sql, ", filename COLLATE " SQLITE_COLLATION_STRCASECMP " ASC" );
00787         }
00788         g_string_append(        sql, ", directory_path COLLATE " SQLITE_COLLATION_STRCASECMP " ASC" );
00789     }
00790 
00791     if (num_items > 0)
00792     {
00793         g_string_append_printf( sql, " LIMIT %ld",
00794                                 num_items );
00795     }
00796 
00797     g_string_append(            sql, ";" );
00798 
00799     // execute query
00800     metadata_table *result = NULL;
00801     int rc = sql3_execute_query(thiz->database, sql->str, NULL, &result);
00802     if (rc != ER_OK)
00803     {
00804         ERRORPRINTF("cannot get metadata, sql [%s]", sql->str);
00805         rc = ER_NOT_FOUND;
00806     }
00807     g_string_free(sql, TRUE);
00808 
00809     *values_tbl = result;
00810     return rc;
00811 }
00812 
00813 
00814 // get selected metadata for a group of files/folders
00815 int ermetadb_global_select_files (erMetadb             thiz,
00816                                   const gchar          *sort_key,
00817                                   gboolean             sort_is_ascending,
00818                                   const metadata_table *names_tbl,
00819                                   metadata_table       **values_tbl,
00820                                   const gchar          *tag_filter)
00821 {
00822     LOGPRINTF("tag_filter [%s]", tag_filter);
00823 
00824     char extra_sql[128];
00825     extra_sql[0] = 0;
00826     if (tag_filter) sprintf(extra_sql, "WHERE tag = '%s'", tag_filter);
00827     int ret = global_select_common(thiz, 
00828                                    sort_key, 
00829                                    sort_is_ascending, 
00830                                    0,
00831                                    names_tbl,
00832                                    values_tbl,
00833                                    extra_sql);
00834     return ret;
00835 }
00836 
00837 
00838 int ermetadb_global_select_recent (erMetadb             thiz,
00839                                    const gchar          *sort_key,
00840                                    int                  num_items,
00841                                    const metadata_table *names_tbl,
00842                                    metadata_table       **values_tbl)
00843 {
00844     LOGPRINTF("entry");
00845 
00846     int ret = global_select_common(thiz, 
00847                                    sort_key, 
00848                                    FALSE, 
00849                                    num_items,
00850                                    names_tbl,
00851                                    values_tbl,
00852                                    "WHERE tag != ''");
00853     return ret;
00854 }
00855 
00856 
00857 int ermetadb_global_select_subdir (erMetadb              thiz,
00858                                    const gchar           *sort_key,
00859                                    gboolean              sort_is_ascending,
00860                                    const metadata_table  *names_tbl,
00861                                    metadata_table        **values_tbl,
00862                                    const gchar           *path_filter)
00863 {
00864     LOGPRINTF("path_filter [%s]", path_filter);
00865 
00866     g_assert(path_filter && path_filter[0]);
00867 
00868     char sql[256];
00869     sprintf(sql, " WHERE directory_path = '%s'", path_filter);
00870     int ret = global_select_common(thiz, 
00871                                    sort_key, 
00872                                    sort_is_ascending, 
00873                                    0,
00874                                    names_tbl,
00875                                    values_tbl,
00876                                    sql);
00877     return ret;
00878 }
00879 
00880 
00881 int ermetadb_global_select_search (erMetadb              thiz,
00882                                    const gchar           *sort_key,
00883                                    gboolean              sort_is_ascending,
00884                                    const metadata_table  *names_tbl,
00885                                    metadata_table        **values_tbl,
00886                                    const gchar*          search_filter)
00887 {
00888     LOGPRINTF("search_filter [%s]", search_filter);
00889 
00890     g_assert(search_filter && search_filter[0]);
00891 
00892     char sql[1024];
00893     sprintf(sql, "WHERE (filename LIKE '%%%s%%' or"
00894                             " title LIKE '%%%s%%' or"
00895                             " author LIKE '%%%s%%') AND tag != ''",
00896                             search_filter, search_filter, search_filter);
00897     int ret = global_select_common(thiz, 
00898                                    sort_key, 
00899                                    sort_is_ascending, 
00900                                    0,
00901                                    names_tbl,
00902                                    values_tbl,
00903                                    sql);
00904     return ret;
00905 }
00906 
00907 
Generated by  doxygen 1.6.2-20100208