Changeset 22043

Show
Ignore:
Timestamp:
06.05.2010 12:49:54 (9 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 modified

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