Changeset 22043 for main/trunk/greenstone2/common-src/src/lib/mssqldbclass.cpp

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.

File:

• main/trunk/greenstone2/common-src/src/lib/mssqldbclass.cpp (modified) (12 diffs)

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

@@ -143 +143 @@
 void mssqldbclass::deletekey (const text_t &key)
 {
-  text_t sql_cmd = "DELETE FROM data_" + tableid + " WHERE one_key=N'" + mssql_safe(key) + "'";
-  dbquery(sql_cmd);
+  text_t sql_cmd = "DELETE FROM data_" + tableid + " WHERE one_key=N'" + sql_safe(key) + "'";
+  sqlexec(sql_cmd);
 }
 
@@ -151 +151 @@
 bool mssqldbclass::getkeydata (const text_t& key, text_t &data)
 {
-  text_t sql_cmd = "SELECT one_value FROM data_" + tableid + " WHERE one_key=N'" + mssql_safe(key) + "'";
+  text_t sql_cmd = "SELECT one_value FROM data_" + tableid + " WHERE one_key=N'" + sql_safe(key) + "'";
   vector<text_tmap> sql_results;
   if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
@@ -196 +196 @@
   if (!exists(key))
   {
-    text_t sql_cmd = "INSERT INTO data_" + tableid + " (one_key, one_value) VALUES (N'" + mssql_safe(key) + "', N'" + mssql_safe(data) + "')";
-    return dbquery(sql_cmd);
+    text_t sql_cmd = "INSERT INTO data_" + tableid + " (one_key, one_value) VALUES (N'" + sql_safe(key) + "', N'" + sql_safe(data) + "')";
+    return sqlexec(sql_cmd);
   }
   else
   {
-    text_t sql_cmd = "UPDATE data_" + tableid + " SET one_value='" + mssql_safe(data) + "' WHERE one_key=N'" + mssql_safe(key) + "'";
-    return dbquery(sql_cmd);
+    text_t sql_cmd = "UPDATE data_" + tableid + " SET one_value='" + sql_safe(data) + "' WHERE one_key=N'" + sql_safe(key) + "'";
+    return sqlexec(sql_cmd);
   }
 }
@@ -221 +221 @@
   
   // Get the entries in the "document_metadata" table where the element and value matches those specified
-  text_t sql_cmd = "SELECT DISTINCT docOID FROM document_metadata_" + tableid + " WHERE element IN (N'" + mssql_safe(metadata_element_names[0]) + "'";
+  text_t sql_cmd = "SELECT DISTINCT docOID FROM document_metadata_" + tableid + " WHERE element IN (N'" + sql_safe(metadata_element_names[0]) + "'";
   for (int i = 1; i < metadata_element_names.size(); i++)
   {
-    sql_cmd += ",'" + mssql_safe(metadata_element_names[i]) + "'";
-  }
-  sql_cmd += ") AND value='" + mssql_safe(metadata_value) + "'";
+    sql_cmd += ",'" + sql_safe(metadata_element_names[i]) + "'";
+  }
+  sql_cmd += ") AND value='" + sql_safe(metadata_value) + "'";
   
   // If we're sorting the documents by a certain metadata element, extend the SQL command to do this
   if (sort_by_metadata_element_name != "")
   {
-    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";
+    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";
   }
   
@@ -275 +275 @@
   
   // Get the entries in the "document_metadata" table where the element matches that specified
-  text_t sql_cmd = "SELECT DISTINCT docOID," + value_select_expression + " FROM document_metadata_" + tableid + " WHERE element IN (N'" + mssql_safe(metadata_element_names[0]) + "'";
+  text_t sql_cmd = "SELECT DISTINCT docOID," + value_select_expression + " FROM document_metadata_" + tableid + " WHERE element IN (N'" + sql_safe(metadata_element_names[0]) + "'";
   for (int i = 1; i < metadata_element_names.size(); i++)
   {
-    sql_cmd += ",N'" + mssql_safe(metadata_element_names[i]) + "'";
+    sql_cmd += ",N'" + sql_safe(metadata_element_names[i]) + "'";
   }
   sql_cmd += ")";
@@ -285 +285 @@
   if (metadata_value_filter != "")
   {
-    sql_cmd += " AND value GLOB N'" + mssql_safe(metadata_value_filter) + "'";
+    sql_cmd += " AND value GLOB N'" + sql_safe(metadata_value_filter) + "'";
   }
   
@@ -308 +308 @@
 
 
-//-------------------------------------------------------------------------------------//
-// MS-SQL Private Utilities [START]
-//-------------------------------------------------------------------------------------//
-
-// dbquery(const text_t &sql)
+// sqlexec(const text_t &sql)
 // Takes a sql statement and executes it
 // Returns false if failed, otherwise true
-bool mssqldbclass::dbquery (const text_t &sql)
+bool mssqldbclass::sqlexec (const text_t &sql)
 {
   char *sql_c = sql.getcstr();
@@ -324 +320 @@
   if (FAILED(cmd.CreateInstance(__uuidof(Command))))
   {
-    cerr << "mssqldbclass::dbquery: CreateInstance failed" << endl;
+    cerr << "mssqldbclass::sqlexec: CreateInstance failed" << endl;
     return false;
   }
@@ -339 +335 @@
     text_t error;
     error.setcstr(e.ErrorMessage());
-    cerr << "mssqldbclass::dbquery: _com_error: (" << sql << ") : (" << error << ")" << endl;
+    cerr << "mssqldbclass::sqlexec: _com_error: (" << sql << ") : (" << error << ")" << endl;
     rv = false;
   }
   catch (...)
   {
-    cerr << "mssqldbclass::dbquery: unknown error: (" + sql + ")" << endl;
+    cerr << "mssqldbclass::sqlexec: unknown error: (" + sql + ")" << endl;
     rv = false;
   }
@@ -426 +422 @@
 
 
-text_t mssqldbclass::mssql_safe (const text_t &value_arg)
+// returns true if exists
+bool sqlitedbclass::sqltableexists(const text_t &table_name)
+{
+  cerr << "**** mssqldbclass::sqltableexists: This implementation is untested!" << endl;
+  cerr << "**** Remove warning message once confirmed/fixed to works as required" << endl;
+
+  text_t sql_cmd = "SELECT name FROM sysobjects WHERE name='" + sql_safe(table_name) 
+    + "' AND OBJECTPROPERTY(id,'IsUserTable')=1";
+
+  vector<text_tmap> sql_results;
+  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
+  {
+    return false;
+  }
+
+  return true;
+}
+
+
+text_t mssqldbclass::sql_safe (const text_t &value_arg)
 {
   text_t value = value_arg;
@@ -434 +449 @@
 
 
+//---------------------------------------------------------------------------//
+// MS-SQL Private Utilities [START]
+//---------------------------------------------------------------------------//
+
+
 // read_infodb_file(text_t filename)
-// Unlike the others (sqlite and gdbm), this is a server-client based database.
-// Therefore, instead of store the actual database, we store the infoserver-client based database.
+// Unlike the others (sqlite and gdbm), this is a server-client based
+// database.  Therefore, instead of store the actual database, we store the
+// infoserver-client based database.
 // What we want to do here is to have the 
 text_tmap mssqldbclass::read_infodb_file(text_t filename)
@@ -494 +515 @@
 }
 
-//-------------------------------------------------------------------------------------//
+//---------------------------------------------------------------------------//
 // MS-SQL Private Utilities [END]
-//-------------------------------------------------------------------------------------//
-
+//---------------------------------------------------------------------------//
+