Changeset 22043 for main


Ignore:
Timestamp:
2010-05-06T12:49:54+12:00 (14 years ago)
Author:
davidb
Message:

Upgrading of database backends to allow support for sql-query support. Opportunity also taken to make calls to sql related classes (such as mssql and sqlite) to be more unified.

Location:
main/trunk/greenstone2/common-src/src/lib
Files:
7 edited

Legend:

Unmodified
Added
Removed

  • main/trunk/greenstone2/common-src/src/lib/Makefile.in

    r21447 r22043  
    8888    text_t.cpp \
    8989    unitool.cpp \
     90    sqldbclass.cpp \
    9091    $(SQLITE_SOURCES)
    9192
    … …  
    109110    text_t.o \
    110111    unitool.o \
     112    sqldbclass.o \
    111113    $(SQLITE_OBJECTS)
    112114

  • main/trunk/greenstone2/common-src/src/lib/jdbmnaiveclass.cpp

    r21438 r22043  
    184184      fgets(buf,256,PIN);
    185185
    186       cerr << "**## buf = " << buf << endl;
    187 
    188       cerr << "**## buf: ";
     186      /*
     187       * debuging code
     188
     189      cerr << "****## buf = " << buf << endl;
     190
     191      cerr << "****## buf: ";
    189192
    190193      int blen = strlen(buf);
    … …  
    192195    cerr << "(" << buf[i] << "," << (int)buf[i] << ") ";
    193196      }
     197
    194198      cerr << endl;
     199      */ // *****
    195200
    196201
    … …  
    202207  delete [] key_cstr;
    203208 
    204   cerr << "**** data before to_unicode = " << data << endl;
     209  // cerr << "**** data before to_unicode = " << data << endl;
    205210
    206211  data = to_uni(data);  // convert to unicode

  • main/trunk/greenstone2/common-src/src/lib/mssqldbclass.cpp

    r17476 r22043  
    143143void mssqldbclass::deletekey (const text_t &key)
    144144{
    145   text_t sql_cmd = "DELETE FROM data_" + tableid + " WHERE one_key=N'" + mssql_safe(key) + "'";
    146   dbquery(sql_cmd);
     145  text_t sql_cmd = "DELETE FROM data_" + tableid + " WHERE one_key=N'" + sql_safe(key) + "'";
     146  sqlexec(sql_cmd);
    147147}
    148148
    … …  
    151151bool mssqldbclass::getkeydata (const text_t& key, text_t &data)
    152152{
    153   text_t sql_cmd = "SELECT one_value FROM data_" + tableid + " WHERE one_key=N'" + mssql_safe(key) + "'";
     153  text_t sql_cmd = "SELECT one_value FROM data_" + tableid + " WHERE one_key=N'" + sql_safe(key) + "'";
    154154  vector<text_tmap> sql_results;
    155155  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
    … …  
    196196  if (!exists(key))
    197197  {
    198     text_t sql_cmd = "INSERT INTO data_" + tableid + " (one_key, one_value) VALUES (N'" + mssql_safe(key) + "', N'" + mssql_safe(data) + "')";
    199     return dbquery(sql_cmd);
     198    text_t sql_cmd = "INSERT INTO data_" + tableid + " (one_key, one_value) VALUES (N'" + sql_safe(key) + "', N'" + sql_safe(data) + "')";
     199    return sqlexec(sql_cmd);
    200200  }
    201201  else
    202202  {
    203     text_t sql_cmd = "UPDATE data_" + tableid + " SET one_value='" + mssql_safe(data) + "' WHERE one_key=N'" + mssql_safe(key) + "'";
    204     return dbquery(sql_cmd);
     203    text_t sql_cmd = "UPDATE data_" + tableid + " SET one_value='" + sql_safe(data) + "' WHERE one_key=N'" + sql_safe(key) + "'";
     204    return sqlexec(sql_cmd);
    205205  }
    206206}
    … …  
    221221 
    222222  // Get the entries in the "document_metadata" table where the element and value matches those specified
    223   text_t sql_cmd = "SELECT DISTINCT docOID FROM document_metadata_" + tableid + " WHERE element IN (N'" + mssql_safe(metadata_element_names[0]) + "'";
     223  text_t sql_cmd = "SELECT DISTINCT docOID FROM document_metadata_" + tableid + " WHERE element IN (N'" + sql_safe(metadata_element_names[0]) + "'";
    224224  for (int i = 1; i < metadata_element_names.size(); i++)
    225225  {
    226     sql_cmd += ",'" + mssql_safe(metadata_element_names[i]) + "'";
    227   }
    228   sql_cmd += ") AND value='" + mssql_safe(metadata_value) + "'";
     226    sql_cmd += ",'" + sql_safe(metadata_element_names[i]) + "'";
     227  }
     228  sql_cmd += ") AND value='" + sql_safe(metadata_value) + "'";
    229229 
    230230  // If we're sorting the documents by a certain metadata element, extend the SQL command to do this
    231231  if (sort_by_metadata_element_name != "")
    232232  {
    233     sql_cmd = "SELECT docOID FROM (" + sql_cmd + ") LEFT JOIN (SELECT docOID,value from document_metadata_" + tableid + " WHERE element=N'" + mssql_safe(sort_by_metadata_element_name) + "') USING (docOID) ORDER by value";
     233    sql_cmd = "SELECT docOID FROM (" + sql_cmd + ") LEFT JOIN (SELECT docOID,value from document_metadata_" + tableid + " WHERE element=N'" + sql_safe(sort_by_metadata_element_name) + "') USING (docOID) ORDER by value";
    234234  }
    235235 
    … …  
    275275 
    276276  // Get the entries in the "document_metadata" table where the element matches that specified
    277   text_t sql_cmd = "SELECT DISTINCT docOID," + value_select_expression + " FROM document_metadata_" + tableid + " WHERE element IN (N'" + mssql_safe(metadata_element_names[0]) + "'";
     277  text_t sql_cmd = "SELECT DISTINCT docOID," + value_select_expression + " FROM document_metadata_" + tableid + " WHERE element IN (N'" + sql_safe(metadata_element_names[0]) + "'";
    278278  for (int i = 1; i < metadata_element_names.size(); i++)
    279279  {
    280     sql_cmd += ",N'" + mssql_safe(metadata_element_names[i]) + "'";
     280    sql_cmd += ",N'" + sql_safe(metadata_element_names[i]) + "'";
    281281  }
    282282  sql_cmd += ")";
    … …  
    285285  if (metadata_value_filter != "")
    286286  {
    287     sql_cmd += " AND value GLOB N'" + mssql_safe(metadata_value_filter) + "'";
     287    sql_cmd += " AND value GLOB N'" + sql_safe(metadata_value_filter) + "'";
    288288  }
    289289 
    … …  
    308308
    309309
    310 //-------------------------------------------------------------------------------------//
    311 // MS-SQL Private Utilities [START]
    312 //-------------------------------------------------------------------------------------//
    313 
    314 // dbquery(const text_t &sql)
     310// sqlexec(const text_t &sql)
    315311// Takes a sql statement and executes it
    316312// Returns false if failed, otherwise true
    317 bool mssqldbclass::dbquery (const text_t &sql)
     313bool mssqldbclass::sqlexec (const text_t &sql)
    318314{
    319315  char *sql_c = sql.getcstr();
    … …  
    324320  if (FAILED(cmd.CreateInstance(__uuidof(Command))))
    325321  {
    326     cerr << "mssqldbclass::dbquery: CreateInstance failed" << endl;
     322    cerr << "mssqldbclass::sqlexec: CreateInstance failed" << endl;
    327323    return false;
    328324  }
    … …  
    339335    text_t error;
    340336    error.setcstr(e.ErrorMessage());
    341     cerr << "mssqldbclass::dbquery: _com_error: (" << sql << ") : (" << error << ")" << endl;
     337    cerr << "mssqldbclass::sqlexec: _com_error: (" << sql << ") : (" << error << ")" << endl;
    342338    rv = false;
    343339  }
    344340  catch (...)
    345341  {
    346     cerr << "mssqldbclass::dbquery: unknown error: (" + sql + ")" << endl;
     342    cerr << "mssqldbclass::sqlexec: unknown error: (" + sql + ")" << endl;
    347343    rv = false;
    348344  }
    … …  
    426422
    427423
    428 text_t mssqldbclass::mssql_safe (const text_t &value_arg)
     424// returns true if exists
     425bool sqlitedbclass::sqltableexists(const text_t &table_name)
     426{
     427  cerr << "**** mssqldbclass::sqltableexists: This implementation is untested!" << endl;
     428  cerr << "**** Remove warning message once confirmed/fixed to works as required" << endl;
     429
     430  text_t sql_cmd = "SELECT name FROM sysobjects WHERE name='" + sql_safe(table_name)
     431    + "' AND OBJECTPROPERTY(id,'IsUserTable')=1";
     432
     433  vector<text_tmap> sql_results;
     434  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
     435  {
     436    return false;
     437  }
     438
     439  return true;
     440}
     441
     442
     443text_t mssqldbclass::sql_safe (const text_t &value_arg)
    429444{
    430445  text_t value = value_arg;
    … …  
    434449
    435450
     451//---------------------------------------------------------------------------//
     452// MS-SQL Private Utilities [START]
     453//---------------------------------------------------------------------------//
     454
     455
    436456// read_infodb_file(text_t filename)
    437 // Unlike the others (sqlite and gdbm), this is a server-client based database.
    438 // Therefore, instead of store the actual database, we store the infoserver-client based database.
     457// Unlike the others (sqlite and gdbm), this is a server-client based
     458// database.  Therefore, instead of store the actual database, we store the
     459// infoserver-client based database.
    439460// What we want to do here is to have the
    440461text_tmap mssqldbclass::read_infodb_file(text_t filename)
    … …  
    494515}
    495516
    496 //-------------------------------------------------------------------------------------//
     517//---------------------------------------------------------------------------//
    497518// MS-SQL Private Utilities [END]
    498 //-------------------------------------------------------------------------------------//
    499 
     519//---------------------------------------------------------------------------//
     520

  • main/trunk/greenstone2/common-src/src/lib/mssqldbclass.h

    r17476 r22043  
    33 * mssqldbclass.h --
    44 * Copyright (C) 2008  DL Consulting Ltd
     5 * Copyright (C) 2010  New Zealand Digital Library Project
    56 *
    67 * A component of the Greenstone digital library software
    … …  
    3536class mssqldbclass : public sqldbclass
    3637{
    37  private:
    38 
    39   _ConnectionPtr dbptr;
    40 
    41   text_t tableid;
    42  
    43   // To enable the debug string to be output to cerr.
    44   bool debug;
    45  
    46   text_tmap read_infodb_file (text_t filename);
    47 
    48   void debug_output (text_t output_string);
    49 
    50   text_t convert_bstr_to_textt (_variant_t in);
    51 
    52   bool dbquery (const text_t &sql);
    53 
    54   bool sqlgetarray (const text_t &sql_cmd, vector<text_tmap> &sql_results);
    55 
    56   text_t mssql_safe (const text_t &value_arg);
    57  
    5838 public:
    5939
    6040  mssqldbclass ();
    61 
    62   ~mssqldbclass ();
     41  virtual ~mssqldbclass ();
    6342 
    6443  // returns true if opened
    … …  
    8160 
    8261  // returns array of document OIDs
    83   text_tarray get_documents_with_metadata_value (const text_tarray &metadata_element_names,
    84                                                 const text_t &metadata_value,
    85                                                 const text_t &sort_by_metadata_element_name);
     62  virutal text_tarray get_documents_with_metadata_value (const text_tarray &metadata_element_names,
     63                            const text_t &metadata_value,
     64                            const text_t &sort_by_metadata_element_name);
    8665 
    8766  // returns array of values
    88   text_tarray get_metadata_values (const text_tarray &metadata_element_names,
    89                                    const text_t &metadata_value_filter,
    90                                    const text_t &metadata_value_grouping_expression);
     67  virtual text_tarray get_metadata_values (const text_tarray &metadata_element_names,
     68                       const text_t &metadata_value_filter,
     69                       const text_t &metadata_value_grouping_expression);
     70
     71
     72 protected:
     73  _ConnectionPtr dbptr;
     74  text_t tableid;
     75  bool debug; // To enable the debug string to be output to cerr.
     76
     77  virutal text_t sql_safe (const text_t &value_arg);
     78
     79  virtual bool sqlexec (const text_t &sql);
     80  virtual bool sqlgetarray (const text_t &sql_cmd, vector<text_tmap> &sql_results);
     81  virtual bool sqltableexists (const text_t &table_name);
     82
     83 
     84  text_tmap read_infodb_file (text_t filename);
     85  void debug_output (text_t output_string);
     86  text_t convert_bstr_to_textt (_variant_t in);
     87
    9188};
    9289

  • main/trunk/greenstone2/common-src/src/lib/sqldbclass.h

    r16180 r22043  
    33 * sqldbclass.h --
    44 * Copyright (C) 2008  DL Consulting Ltd
     5 * Copyright (C) 2010  New Zealand Digital Library Project
    56 *
    67 * A component of the Greenstone digital library software
    … …  
    3536{
    3637public:
    37   sqldbclass() {}
    38   virtual ~sqldbclass() {}
     38  sqldbclass();
     39  virtual ~sqldbclass();
    3940
    40 
    41   // -----------------------------------------------------------------------------------------------
    42   // These functions MUST be implemented by subclasses, as they are database-specific
     41  // returns array of document OIDs
     42  virtual text_tarray get_documents_where (const text_t& sql_initial_cmd,
     43                       const text_t& sort_by_metadata_element_name);
    4344
    4445  // returns array of document OIDs
    … …  
    5152                       const text_t &metadata_value_filter,
    5253                       const text_t &metadata_value_grouping_expression) = 0;
     54
     55protected:
     56  virtual text_t sql_safe (const text_t &value_arg) = 0;
     57
     58  virtual bool sqlexec(const text_t &sql_cmd) = 0;
     59  virtual bool sqlgetarray(const text_t& sql_cmd,
     60               vector<text_tmap>& sql_results) = 0;
     61  virtual bool sqltableexists(const text_t &table_name) = 0;
     62
    5363};
    5464

  • main/trunk/greenstone2/common-src/src/lib/sqlitedbclass.cpp

    r21865 r22043  
    3838
    3939#define SQLITE_MAX_RETRIES 8
     40
     41sqlitedbclass::sqlitedbclass()
     42{
     43  sqlitefile = NULL;
     44}
    4045
    4146
    … …  
    95100void sqlitedbclass::deletekey (const text_t &key)
    96101{
    97   text_t sql_cmd = "DELETE FROM data WHERE key='" + sqlite_safe(key) + "'";
     102  text_t sql_cmd = "DELETE FROM data WHERE key='" + sql_safe(key) + "'";
    98103  sqlexec(sql_cmd);
    99104}
    … …  
    114119
    115120  // Get the entries in the "document_metadata" table where the element and value matches those specified
    116   text_t sql_cmd = "SELECT DISTINCT docOID FROM document_metadata WHERE element IN ('" + sqlite_safe(metadata_element_names[0]) + "'";
     121  text_t sql_cmd = "SELECT DISTINCT docOID FROM document_metadata WHERE element IN ('" + sql_safe(metadata_element_names[0]) + "'";
    117122  for (int i = 1; i < metadata_element_names.size(); i++)
    118123  {
    119     sql_cmd += ",'" + sqlite_safe(metadata_element_names[i]) + "'";
    120   }
    121   sql_cmd += ") AND value='" + sqlite_safe(metadata_value) + "'";
     124    sql_cmd += ",'" + sql_safe(metadata_element_names[i]) + "'";
     125  }
     126  sql_cmd += ") AND value='" + sql_safe(metadata_value) + "'";
    122127
    123128  // If we're sorting the documents by a certain metadata element, extend the SQL command to do this
    124129  if (sort_by_metadata_element_name != "")
    125130  {
    126     sql_cmd = "SELECT docOID FROM (" + sql_cmd + ") LEFT JOIN (SELECT docOID,value from document_metadata WHERE element='" + sqlite_safe(sort_by_metadata_element_name) + "') USING (docOID) ORDER by value";
     131    sql_cmd = "SELECT docOID FROM (" + sql_cmd + ") LEFT JOIN (SELECT docOID,value from document_metadata WHERE element='" + sql_safe(sort_by_metadata_element_name) + "') USING (docOID) ORDER by value";
    127132  }
    128133
    … …  
    157162bool sqlitedbclass::getkeydata (const text_t& key, text_t &data)
    158163{
    159   text_t sql_cmd = "SELECT value FROM data WHERE key='" + sqlite_safe(key) + "'";
     164  text_t sql_cmd = "SELECT value FROM data WHERE key='" + sql_safe(key) + "'";
    160165  vector<text_tmap> sql_results;
    161166  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
    … …  
    217222
    218223  // Get the entries in the "document_metadata" table where the element matches that specified
    219   text_t sql_cmd = "SELECT DISTINCT docOID," + value_select_expression + " FROM document_metadata WHERE element IN ('" + sqlite_safe(metadata_element_names[0]) + "'";
     224  text_t sql_cmd = "SELECT DISTINCT docOID," + value_select_expression + " FROM document_metadata WHERE element IN ('" + sql_safe(metadata_element_names[0]) + "'";
    220225  for (int i = 1; i < metadata_element_names.size(); i++)
    221226  {
    222     sql_cmd += ",'" + sqlite_safe(metadata_element_names[i]) + "'";
     227    sql_cmd += ",'" + sql_safe(metadata_element_names[i]) + "'";
    223228  }
    224229  sql_cmd += ")";
    … …  
    227232  if (metadata_value_filter != "")
    228233  {
    229     sql_cmd += " AND value GLOB '" + sqlite_safe(metadata_value_filter) + "'";
     234    sql_cmd += " AND value GLOB '" + sql_safe(metadata_value_filter) + "'";
    230235  }
    231236
    … …  
    256261  if (!exists(key))
    257262  {
    258     text_t sql_cmd = "INSERT INTO data (key, value) VALUES ('" + sqlite_safe(key) + "', '" + sqlite_safe(data) + "')";
     263    text_t sql_cmd = "INSERT INTO data (key, value) VALUES ('" + sql_safe(key) + "', '" + sql_safe(data) + "')";
    259264    return sqlexec(sql_cmd);
    260265  }
    261266  else
    262267  {
    263     text_t sql_cmd = "UPDATE data SET value='" + sqlite_safe(data) + "' WHERE key='" + sqlite_safe(key) + "'";
     268    text_t sql_cmd = "UPDATE data SET value='" + sql_safe(data) + "' WHERE key='" + sql_safe(key) + "'";
    264269    return sqlexec(sql_cmd);
    265270  }
    … …  
    282287
    283288
    284 text_t sqlitedbclass::sqlite_safe (const text_t &value_arg)
     289text_t sqlitedbclass::sql_safe (const text_t &value_arg)
    285290{
    286291  text_t value = value_arg;
    … …  
    380385bool sqlitedbclass::sqltableexists(const text_t &table_name)
    381386{
    382   text_t sql_cmd = "SELECT * FROM sqlite_master WHERE tbl_name='" + sqlite_safe(table_name) + "'";
     387  text_t sql_cmd = "SELECT * FROM sqlite_master WHERE tbl_name='" + sql_safe(table_name) + "'";
    383388  vector<text_tmap> sql_results;
    384389  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)

  • main/trunk/greenstone2/common-src/src/lib/sqlitedbclass.h

    r16180 r22043  
    33 * sqlitedbclass.h --
    44 * Copyright (C) 2008  DL Consulting Ltd
     5 * Copyright (C) 2010  New Zealand Digital Library Project
    56 *
    67 * A component of the Greenstone digital library software
    … …  
    3536{
    3637public:
    37   sqlitedbclass() { sqlitefile = NULL; }
    38   ~sqlitedbclass();
     38  sqlitedbclass();
     39  virtual ~sqlitedbclass();
    3940
    4041  // returns true if opened
    41   bool opendatabase (const text_t &filename, int mode, int num_retrys, bool need_filelock);
     42  bool opendatabase (const text_t &filename, int mode, int num_retrys,
     43             bool need_filelock);
    4244
    4345  void closedatabase ();
    4446
    4547  void deletekey (const text_t &key);
    46 
    47   // returns array of document OIDs
    48   text_tarray get_documents_with_metadata_value (const text_tarray &metadata_element_names,
    49                          const text_t &metadata_value,
    50                          const text_t &sort_by_metadata_element_name);
    5148
    5249  // returns file extension string
    … …  
    5956  text_tarray getkeys ();
    6057
     58
     59  // returns array of document OIDs
     60  virtual text_tarray get_documents_with_metadata_value (const text_tarray &metadata_element_names,
     61                             const text_t &metadata_value,
     62                             const text_t &sort_by_metadata_element_name);
     63
    6164  // returns array of values
    62   text_tarray get_metadata_values (const text_tarray &metadata_element_names,
    63                    const text_t &metadata_value_filter,
    64                    const text_t &metadata_value_grouping_expression);
     65  virtual text_tarray get_metadata_values (const text_tarray &metadata_element_names,
     66                       const text_t &metadata_value_filter,
     67                       const text_t &metadata_value_grouping_expression);
    6568
    6669  // returns true on success
    … …  
    7174  sqlite3* sqlitefile;
    7275
    73   text_t sqlite_safe (const text_t &value_arg);
     76  virtual text_t sql_safe (const text_t &value_arg);
    7477
    75   bool sqlexec (const text_t &sql_cmd);
    76   bool sqlgetarray (const text_t &sql_cmd, vector<text_tmap> &sql_results);
    77   bool sqltableexists (const text_t &table_name);
     78  virtual bool sqlexec (const text_t &sql_cmd);
     79  virtual bool sqlgetarray (const text_t &sql_cmd, vector<text_tmap> &sql_results);
     80  virtual bool sqltableexists (const text_t &table_name);
     81
    7882};
    7983
Note: See TracChangeset for help on using the changeset viewer.