MySqlDatastore.cpp

Go to the documentation of this file.
00001 /* ****************************************************************** **
00002 **    OpenSees - Open System for Earthquake Engineering Simulation    **
00003 **          Pacific Earthquake Engineering Research Center            **
00004 **                                                                    **
00005 **                                                                    **
00006 ** (C) Copyright 2002, The Regents of the University of California    **
00007 ** All Rights Reserved.                                               **
00008 **                                                                    **
00009 ** Commercial use of this program without express permission of the   **
00010 ** University of California, Berkeley, is strictly prohibited.  See   **
00011 ** file 'COPYRIGHT'  in main directory for information on usage and   **
00012 ** redistribution,  and for a DISCLAIMER OF ALL WARRANTIES.           **
00013 **                                                                    **
00014 ** Developed by:                                                      **
00015 **   Frank McKenna (fmckenna@ce.berkeley.edu)                         **
00016 **   Gregory L. Fenves (fenves@ce.berkeley.edu)                       **
00017 **   Filip C. Filippou (filippou@ce.berkeley.edu)                     **
00018 **                                                                    **
00019 ** ****************************************************************** */
00020                                                                         
00021 // $Revision: 1.7 $
00022 // $Date: 2005/11/07 21:34:25 $
00023 // $Source: /usr/local/cvs/OpenSees/SRC/database/MySqlDatastore.cpp,v $
00024 
00025 #include <MySqlDatastore.h>
00026 #include <Vector.h>
00027 #include <Matrix.h>
00028 #include <ID.h>
00029 
00030 // #include <mysqld_error.h> .. use the following #define instead
00031 #define ER_TABLE_EXISTS_ERROR 1050
00032 
00033 
00034 #define MAX_BLOB_SIZE 16777215
00035 
00036 MySqlDatastore::MySqlDatastore(const char *projectName,
00037                                Domain &theDomain,
00038                                FEM_ObjectBroker &theObjectBroker,
00039                                int run)
00040   :FE_Datastore(theDomain, theObjectBroker), dbTag(0), dbRun(run), 
00041    connection(true), query(0), sizeQuery(0), sizeColumnString(0)
00042 {
00043   // initialise the mysql structure
00044   mysql_init(&mysql);
00045 
00046   // connect to the server
00047   if (mysql_real_connect(&mysql, NULL, NULL, NULL, NULL, 0, "/tmp/mysql.sock", 0) == NULL) {
00048 
00049       opserr << "MySqlDatastore::MySqlDatastore() - could not connect to server\n";
00050       opserr << mysql_error(&mysql) << endln;
00051       connection = false;
00052 
00053   } else {
00054 
00055     // link to the database, 
00056     if (mysql_select_db(&mysql, projectName) != 0) {
00057 
00058       // if no database exists, try creating one
00059       if (this->createOpenSeesDatabase(projectName) != 0) {
00060         connection = false;
00061         mysql_close(&mysql);
00062         opserr << "MySqlDatastore::MySqlDatastore() - could not open the database\n";
00063         opserr << mysql_error(&mysql) << endln;      
00064       }
00065     }
00066   }
00067 
00068 }
00069 
00070 
00071 
00072 MySqlDatastore::MySqlDatastore(const char *databaseName,
00073                                const char *host,
00074                                const char *user,
00075                                const char *passwd,
00076                                unsigned int port,
00077                                const char *socket,
00078                                unsigned int clientFlag,
00079                                Domain &theDomain, 
00080                                FEM_ObjectBroker &theObjectBroker,
00081                                int run)
00082   :FE_Datastore(theDomain, theObjectBroker), dbTag(0), dbRun(run), 
00083    connection(true), query(0), sizeQuery(0), sizeColumnString(0)
00084 {
00085   // initialise the mysql structure
00086   mysql_init(&mysql);
00087 
00088   // connect to the server & database
00089   if (mysql_real_connect(&mysql, host, user, passwd, databaseName, port, socket, clientFlag) == NULL) {
00090 
00091   // connect to the server & see if can link to database, or create a new one if one does not exist
00092     if (mysql_real_connect(&mysql, host, user, passwd, NULL, port, socket, clientFlag) == NULL) {
00093       opserr << "MySqlDatastore::MySqlDatastore() - could not connect to server\n";
00094       opserr << mysql_error(&mysql) << endln;
00095       connection = false;
00096 
00097     } else {
00098 
00099       // link to the database, 
00100       if (mysql_select_db(&mysql, databaseName) != 0) {
00101         
00102         // if no database exists, try creating one
00103         if (this->createOpenSeesDatabase(databaseName) != 0) {
00104           connection = false;
00105           mysql_close(&mysql);
00106           opserr << "MySqlDatastore::MySqlDatastore() - could not open the database\n";
00107           opserr << mysql_error(&mysql) << endln;      
00108         }
00109       }
00110     }
00111   }
00112 }
00113 
00114 
00115 MySqlDatastore::~MySqlDatastore()
00116 {
00117   if (connection == true) 
00118     mysql_close(&mysql);
00119 
00120   if (query != 0)
00121     delete [] query;
00122 }
00123 
00124 
00125 int 
00126 MySqlDatastore::sendMsg(int dataTag, int commitTag, 
00127                        const Message &, 
00128                        ChannelAddress *theAddress)
00129 {
00130   opserr << "MySqlDatastore::sendMsg() - not yet implemented\n";
00131   return -1;
00132 }                      
00133 
00134 int 
00135 MySqlDatastore::recvMsg(int dataTag, int commitTag, 
00136                        Message &, 
00137                        ChannelAddress *theAddress)
00138 {
00139   opserr << "MySqlDatastore::recvMsg() - not yet implemented\n";
00140   return -1;
00141 }                      
00142 
00143 
00144 
00145 int 
00146 MySqlDatastore::sendMatrix(int dbTag, int commitTag, 
00147                            const Matrix &theMatrix, 
00148                            ChannelAddress *theAddress)
00149 {
00150   // check that we have a connection
00151   if (connection == false)
00152     return -1;
00153 
00154   // check that the data will fit, MySQL has a limit on blob sizes
00155   int sizeData = theMatrix.dataSize * sizeof(double);
00156   if (sizeData > MAX_BLOB_SIZE) {
00157       opserr << "MySqlDatastore::sendMatrix - vector too big to send to MySQL databse, enlarge BLOBS!";
00158       return  -2;
00159   }
00160 
00161   // check that query string is big enough to hold the data, if not enlarge
00162   if ((query == 0) || ((sizeData*2 + 254) > sizeQuery)) { // *2 for extra space MySQL may need
00163     if (query != 0)
00164       delete [] query;
00165     sizeQuery = 2 * sizeData + 254; // 254 for the INSERT INTO Vec... blah blah
00166     query = new char [sizeQuery];
00167 
00168     if (query == 0) {
00169       opserr << "MySqlDatastore::sendMatrix - out of memory creating query of size";
00170       opserr << sizeQuery << endln;
00171       return -2;
00172     }
00173   }
00174 
00175   //
00176   // to send the data the database we do the following:
00177   // 1. try to INSERT the data into the database
00178   // 2. if INSERT fails we have to reformulate the query to UPDATE the existing data, 
00179   //    as the row given by dbTag, commitTag and vectorSize may already already exist.
00180   //
00181 
00182   // form the INSERT query
00183   sprintf(query, "INSERT INTO Matrices VALUES (%d, %d, %d,'", dbTag, commitTag, theMatrix.dataSize);  
00184   char *p = query + strlen(query);
00185   p += mysql_escape_string(p, (char *)theMatrix.data, sizeData); // add vector values in binary form
00186   strcpy(p, "')");
00187 
00188   // execute the INSERT query
00189   if (mysql_query(&mysql, query) != 0) {
00190     
00191     //
00192     // if INSERT fails we reformulate query and try an UPDATE
00193     //
00194 
00195     // formulate the query
00196     sprintf(query, "UPDATE Matrices SET data='");
00197     char *p = query + strlen(query);
00198     p += mysql_escape_string(p, (char *)theMatrix.data, sizeData);
00199     sprintf(p, "' WHERE dbTag=%d AND commitTag=%d AND size=%d", dbTag, commitTag, theMatrix.dataSize);  
00200 
00201     // invoke the query on the database
00202     if (mysql_query(&mysql, query) != 0) {    
00203       opserr << "MySqlDatastore::sendMatrix() - failed to send the Matrix to MySQL database";
00204       opserr << endln << mysql_error(&mysql) << endln;          
00205       return -3;      
00206     }
00207   }
00208 
00209   return 0;
00210 }
00211  
00212 int 
00213 MySqlDatastore::recvMatrix(int dbTag, int commitTag, 
00214                            Matrix &theMatrix,
00215                            ChannelAddress *theAddress)
00216 {
00217   // check that we have a connection
00218   if (connection == false)
00219     return -1;
00220 
00221   // check that query string is big enough to hold the data, if not enlarge
00222   int sizeData = theMatrix.dataSize * sizeof(double);
00223   if ((query == 0) || ((sizeData*2 + 256) > sizeQuery)) { // *2 for extra space mysql needs
00224     if (query != 0)
00225       delete [] query;
00226     sizeQuery = 2 * sizeData + 256; // 256 for the SLECECT data FROM ... blah blah
00227     query = new char [sizeQuery];
00228     if (query == 0) {
00229       opserr << "MySqlDatastore::recvMatrix - out of memory creating query of size";
00230       opserr << sizeQuery << endln;
00231       return -2;
00232     }
00233   }
00234 
00235   //
00236   // to receive the data the database we do the following:
00237   // 1. use SELECT to receive the data from the database
00238   // 2. fetch the results from the server and copy to the Matrices data area
00239   // 3. clean up the MYSQL_RES datastructure 
00240   //
00241   // NOTE: probably using a malloc() and free() under the covers for this, could be 
00242   // very expensive .. IS THERE ANOTHER WAY TO DO THIS, i.e. SAVE A MYSQL_RES FOR
00243   // NUMEROUS USES??
00244 
00245   // form the SELECT query
00246   sprintf(query, "Select data FROM Matrices WHERE dbTag=%d AND commitTag=%d AND size=%d", 
00247           dbTag, commitTag, theMatrix.dataSize);  
00248 
00249   // execute the SELECT query
00250   if (mysql_query(&mysql, query) != 0) {
00251     opserr << "MySqlDatastore::recvMatrix() - failed to receive vector from MySQL database";
00252     opserr << endln << mysql_error(&mysql) << endln;          
00253     return -3;
00254   } 
00255 
00256   // fetch the results from the database
00257   MYSQL_RES *results;
00258   MYSQL_ROW row;
00259   results = mysql_store_result(&mysql);
00260   if (results == NULL) {
00261     // no vector stored in db with these keys
00262     opserr << "MySqlDatastore::recvMatrix - no data in database for Matrix with dbTag, cTag: ";
00263     opserr << dbTag << ", " << commitTag << endln;
00264     return -4;
00265   }
00266   row = mysql_fetch_row(results);
00267   if (row == NULL) {
00268     // no vector stored in db with these keys
00269     opserr << "MySqlDatastore::recvMatrix - no data in database for Matrix with dbTag, cTag: ";
00270     opserr << dbTag << ", " << commitTag << endln;
00271     mysql_free_result(results);
00272     return -5;
00273   }
00274 
00275   // place the results into the vectors double array
00276   double *data = (double *)row[0];
00277   for (int i=0; i<theMatrix.dataSize; i++)
00278     theMatrix.data[i] = data[i];
00279 
00280 
00281   // free the MYSQL_RES structure
00282   mysql_free_result(results);
00283 
00284   return 0;
00285 }
00286 
00287 
00288 
00289 int 
00290 MySqlDatastore::sendVector(int dbTag, int commitTag, 
00291                            const Vector &theVector, 
00292                            ChannelAddress *theAddress)
00293 {
00294   // check that we have a connection
00295   if (connection == false)
00296     return -1;
00297 
00298   // check that the data will fit, MySQL has a limit on blob sizes
00299   int sizeData = theVector.sz * sizeof(double);
00300   if (sizeData > MAX_BLOB_SIZE) {
00301       opserr << "MySqlDatastore::sendVector - vector too big to send to MySQL databse, enlarge BLOBS!";
00302       return  -2;
00303   }
00304 
00305   // check that query string is big enough to hold the data, if not enlarge
00306   if ((query == 0) || ((sizeData*2 + 254) > sizeQuery)) { // *2 for extra space MySQL may need
00307     if (query != 0)
00308       delete [] query;
00309     sizeQuery = 2 * sizeData + 254; // 254 for the INSERT INTO Vec... blah blah
00310     query = new char [sizeQuery];
00311 
00312     if (query == 0) {
00313       opserr << "MySqlDatastore::sendVector - out of memory creating query of size";
00314       opserr << sizeQuery << endln;
00315       return -2;
00316     }
00317   }
00318 
00319   //
00320   // to send the data the database we do the following:
00321   // 1. try to INSERT the data into the database
00322   // 2. if INSERT fails we have to reformulate the query to UPDATE the existing data, 
00323   //    as the row given by dbTag, commitTag and vectorSize may already already exist.
00324   //
00325 
00326   // form the INSERT query
00327   sprintf(query, "INSERT INTO Vectors VALUES (%d, %d, %d,'", dbTag, commitTag, theVector.sz);  
00328   char *p = query + strlen(query);
00329   p += mysql_escape_string(p, (char *)theVector.theData, sizeData); // add vector values in binary form
00330   strcpy(p, "')");
00331 
00332   // execute the INSERT query
00333   if (mysql_query(&mysql, query) != 0) {
00334     
00335     //
00336     // if INSERT fails we reformulate query and try an UPDATE
00337     //
00338 
00339     // formulate the query
00340     sprintf(query, "UPDATE Vectors SET data='");
00341     char *p = query + strlen(query);
00342     p += mysql_escape_string(p, (char *)theVector.theData, sizeData);
00343     sprintf(p, "' WHERE dbTag=%d AND commitTag=%d AND size=%d", dbTag, commitTag, theVector.sz);  
00344 
00345     // invoke the query on the database
00346     if (mysql_query(&mysql, query) != 0) {    
00347       opserr << "MySqlDatastore::sendVector() - failed to send the Vector to MySQL database";
00348       opserr << endln << mysql_error(&mysql) << endln;          
00349       return -3;      
00350     }
00351   }
00352 
00353   return 0;
00354 }
00355  
00356 int 
00357 MySqlDatastore::recvVector(int dbTag, int commitTag, 
00358                            Vector &theVector,
00359                            ChannelAddress *theAddress)
00360 {
00361   // check that we have a connection
00362   if (connection == false)
00363     return -1;
00364 
00365   // check that query string is big enough to hold the data, if not enlarge
00366   int sizeData = theVector.sz * sizeof(double);
00367   if ((query == 0) || ((sizeData*2 + 256) > sizeQuery)) { // *2 for extra space mysql needs
00368     if (query != 0)
00369       delete [] query;
00370     sizeQuery = 2 * sizeData + 256; // 256 for the SLECECT data FROM ... blah blah
00371     query = new char [sizeQuery];
00372     if (query == 0) {
00373       opserr << "MySqlDatastore::recvVector - out of memory creating query of size";
00374       opserr << sizeQuery << endln;
00375       return -2;
00376     }
00377   }
00378 
00379   //
00380   // to receive the data the database we do the following:
00381   // 1. use SELECT to receive the data from the database
00382   // 2. fetch the results from the server and copy to the Vectors data area
00383   // 3. clean up the MYSQL_RES datastructure 
00384   //
00385   // NOTE: probably using a malloc() and free() under the covers for this, could be 
00386   // very expensive .. IS THERE ANOTHER WAY TO DO THIS, i.e. SAVE A MYSQL_RES FOR
00387   // NUMEROUS USES??
00388 
00389   // form the SELECT query
00390   sprintf(query, "Select data FROM Vectors WHERE dbTag=%d AND commitTag=%d AND size=%d", 
00391           dbTag, commitTag, theVector.sz);  
00392 
00393   // execute the SELECT query
00394   if (mysql_query(&mysql, query) != 0) {
00395     opserr << "MySqlDatastore::recvVector() - failed to receive vector from MySQL database";
00396     opserr << endln << mysql_error(&mysql) << endln;          
00397     return -3;
00398   } 
00399 
00400   // fetch the results from the database
00401   MYSQL_RES *results;
00402   MYSQL_ROW row;
00403   results = mysql_store_result(&mysql);
00404   if (results == NULL) {
00405     // no vector stored in db with these keys
00406     opserr << "MySqlDatastore::recvVector - no data in database for Vector with dbTag, cTag: ";
00407     opserr << dbTag << ", " << commitTag << endln;
00408     return -4;
00409   }
00410   row = mysql_fetch_row(results);
00411   if (row == NULL) {
00412     // no vector stored in db with these keys
00413     opserr << "MySqlDatastore::recvVector - no data in database for Vector with dbTag, cTag: ";
00414     opserr << dbTag << ", " << commitTag << endln;
00415     mysql_free_result(results);
00416     return -5;
00417   }
00418 
00419   // place the results into the vectors double array
00420   double *data = (double *)row[0];
00421   for (int i=0; i<theVector.sz; i++)
00422     theVector.theData[i] = data[i];
00423 
00424 
00425   // free the MYSQL_RES structure
00426   mysql_free_result(results);
00427 
00428   return 0;
00429 }
00430 
00431 
00432 int 
00433 MySqlDatastore::sendID(int dbTag, int commitTag, 
00434                            const ID &theID, 
00435                            ChannelAddress *theAddress)
00436 {
00437   // check that we have a connection
00438   if (connection == false)
00439     return -1;
00440 
00441   // check that the data will fit, MySQL has a limit on blob sizes
00442   int sizeData = theID.sz * sizeof(int);
00443   if (sizeData > MAX_BLOB_SIZE) {
00444       opserr << "MySqlDatastore::sendID - vector too big to send to MySQL databse, enlarge BLOBS!";
00445       return  -2;
00446   }
00447 
00448   // check that query string is big enough to hold the data, if not enlarge
00449   if ((query == 0) || ((sizeData*2 + 254) > sizeQuery)) { // *2 for extra space MySQL may need
00450     if (query != 0)
00451       delete [] query;
00452     sizeQuery = 2 * sizeData + 254; // 254 for the INSERT INTO Vec... blah blah
00453     query = new char [sizeQuery];
00454 
00455     if (query == 0) {
00456       opserr << "MySqlDatastore::sendID - out of memory creating query of size";
00457       opserr << sizeQuery << endln;
00458       return -2;
00459     }
00460   }
00461 
00462   //
00463   // to send the data the database we do the following:
00464   // 1. try to INSERT the data into the database
00465   // 2. if INSERT fails we have to reformulate the query to UPDATE the existing data, 
00466   //    as the row given by dbTag, commitTag and vectorSize may already already exist.
00467   //
00468 
00469   // form the INSERT query
00470   sprintf(query, "INSERT INTO IDs VALUES (%d, %d, %d,'", dbTag, commitTag, theID.sz);  
00471   char *p = query + strlen(query);
00472   p += mysql_escape_string(p, (char *)theID.data, sizeData); // add vector values in binary form
00473   strcpy(p, "')");
00474 
00475   // execute the INSERT query
00476   if (mysql_query(&mysql, query) != 0) {
00477     
00478     //
00479     // if INSERT fails we reformulate query and try an UPDATE
00480     //
00481 
00482     // formulate the query
00483     sprintf(query, "UPDATE IDs SET data='");
00484     char *p = query + strlen(query);
00485     p += mysql_escape_string(p, (char *)theID.data, sizeData);
00486     sprintf(p, "' WHERE dbTag=%d AND commitTag=%d AND size=%d", dbTag, commitTag, theID.sz);  
00487 
00488     // invoke the query on the database
00489     if (mysql_query(&mysql, query) != 0) {    
00490       opserr << "MySqlDatastore::sendID() - failed to send the ID to MySQL database";
00491       opserr << endln << mysql_error(&mysql) << endln;          
00492       return -3;      
00493     }
00494   }
00495 
00496   return 0;
00497 }
00498  
00499 int 
00500 MySqlDatastore::recvID(int dbTag, int commitTag, 
00501                            ID &theID,
00502                            ChannelAddress *theAddress)
00503 {
00504   // check that we have a connection
00505   if (connection == false)
00506     return -1;
00507 
00508   // check that query string is big enough to hold the data, if not enlarge
00509   int sizeData = theID.sz * sizeof(int);
00510   if ((query == 0) || ((sizeData*2 + 256) > sizeQuery)) { // *2 for extra space mysql needs
00511     if (query != 0)
00512       delete [] query;
00513     sizeQuery = 2 * sizeData + 256; // 256 for the SLECECT data FROM ... blah blah
00514     query = new char [sizeQuery];
00515     if (query == 0) {
00516       opserr << "MySqlDatastore::recvID - out of memory creating query of size";
00517       opserr << sizeQuery << endln;
00518       return -2;
00519     }
00520   }
00521 
00522   //
00523   // to receive the data the database we do the following:
00524   // 1. use SELECT to receive the data from the database
00525   // 2. fetch the results from the server and copy to the IDs data area
00526   // 3. clean up the MYSQL_RES datastructure 
00527   //
00528   // NOTE: probably using a malloc() and free() under the covers for this, could be 
00529   // very expensive .. IS THERE ANOTHER WAY TO DO THIS, i.e. SAVE A MYSQL_RES FOR
00530   // NUMEROUS USES??
00531 
00532   // form the SELECT query
00533   sprintf(query, "Select data FROM IDs WHERE dbTag=%d AND commitTag=%d AND size=%d", 
00534           dbTag, commitTag, theID.sz);  
00535 
00536   // execute the SELECT query
00537   if (mysql_query(&mysql, query) != 0) {
00538     opserr << "MySqlDatastore::recvID() - failed to receive vector from MySQL database";
00539     opserr << endln << mysql_error(&mysql) << endln;          
00540     return -3;
00541   } 
00542 
00543   // fetch the results from the database
00544   MYSQL_RES *results;
00545   MYSQL_ROW row;
00546   results = mysql_store_result(&mysql);
00547   if (results == NULL) {
00548     // no vector stored in db with these keys
00549     opserr << "MySqlDatastore::recvID - no data in database for ID with dbTag, cTag: ";
00550     opserr << dbTag << ", " << commitTag << endln;
00551     return -4;
00552   }
00553   row = mysql_fetch_row(results);
00554   if (row == NULL) {
00555     // no vector stored in db with these keys
00556     opserr << "MySqlDatastore::recvID - no data in database for ID with dbTag, cTag: ";
00557     opserr << dbTag << ", " << commitTag << endln;
00558     mysql_free_result(results);
00559     return -5;
00560   }
00561 
00562   // place the results into the vectors double array
00563   int *data = (int *)row[0];
00564   for (int i=0; i<theID.sz; i++)
00565     theID.data[i] = data[i];
00566 
00567 
00568   // free the MYSQL_RES structure
00569   mysql_free_result(results);
00570 
00571   return 0;
00572 }
00573 
00574 
00575 
00576 
00577 int 
00578 MySqlDatastore::createTable(const char *tableName, int numColumns, char *columns[])
00579 {
00580   // check that we have a connection
00581   if (connection == false)
00582     return -1;
00583 
00584   // check that the query string can old the actual query, if not enlarge the string
00585   int requiredSize = 100 + strlen(tableName); // create table blah blah blah
00586   int sizeColumn = 0;
00587   for (int i=0; i<numColumns; i++) {
00588     sizeColumn += strlen(columns[i]) + 2;
00589   }
00590   
00591   if (sizeColumnString < sizeColumn)
00592     sizeColumnString = sizeColumn;
00593 
00594   requiredSize += sizeColumn + 18*numColumns;
00595 
00596   if (query == 0 || sizeQuery < requiredSize) {
00597     if (query != 0)
00598       delete [] query;
00599     query = new char[requiredSize];
00600     sizeQuery = requiredSize;
00601   }
00602 
00603   // create the sql query
00604   char *p = query;
00605   int numChar = sprintf(query, "CREATE TABLE %s (dbRun INT NOT NULL, commitTag INT NOT NULL, ", tableName);
00606   p += numChar;
00607   for (int j=0; j<numColumns; j++) {
00608     numChar = sprintf(p, "%s DOUBLE NOT NULL, ", columns[j]);
00609     p += numChar;
00610   }
00611   
00612   sprintf(p, "PRIMARY KEY (dbRun, commitTag) )");
00613 
00614   // execute the query
00615   if (mysql_query(&mysql, query) != 0) {
00616     if (mysql_errno(&mysql) != ER_TABLE_EXISTS_ERROR) {
00617       opserr << "MySqlDatastore::createTable() - failed to create the table in the database";
00618       opserr << endln << mysql_error(&mysql) << endln;          
00619       opserr << "SQL query: " << query << endln;          
00620       return -3;
00621     }
00622   } 
00623 
00624   return 0;
00625 }
00626 
00627 int 
00628 MySqlDatastore::insertData(const char *tableName, char *columns[], int commitTag, const Vector &data)
00629 {
00630   // check that we have a connection
00631   if (connection == false)
00632     return -1;
00633 
00634   // check that query string is big enough to hold the data, if not enlarge
00635   int sizeData = 128 + strlen(tableName);
00636   if ((query == 0) || (sizeData > sizeQuery)) { // *2 for extra space mysql needs
00637     if (query != 0)
00638       delete [] query;
00639     sizeQuery = sizeData; // 256 for the SLECECT data FROM ... blah blah
00640     query = new char [sizeQuery];
00641     if (query == 0) {
00642       opserr << "MySqlDatastore::getData - out of memory creating query of size";
00643       opserr << sizeQuery << endln;
00644       return -2;
00645     }
00646   }
00647 
00648   // form the insert query
00649   sprintf(query, "INSERT INTO %s VALUES (%d, %d ", tableName, dbRun, commitTag);  
00650   char *p = query + strlen(query);
00651   for (int i=0; i<data.Size(); i++)
00652     p += sprintf(p, ", %f ", data(i));  
00653   strcpy(p, ")");
00654 
00655   // execute the query
00656   if (mysql_query(&mysql, query) != 0) {
00657 
00658 
00659     //
00660     // if INSERT fails we reformulate query and try an UPDATE
00661     //
00662 
00663     // formulate the query
00664     sprintf(query, "UPDATE %s SET %s=%f", tableName, columns[0], data(0));
00665     char *p = query + strlen(query);
00666     for (int i=1; i<data.Size(); i++)
00667       p += sprintf(p, ", %s=%f ", columns[i], data(i));  
00668     sprintf(p, " WHERE dbRun=%d AND commitTag=%d", dbTag, commitTag);  
00669 
00670     // invoke the query on the database
00671     if (mysql_query(&mysql, query) != 0) {    
00672       opserr << "MySqlDatastore::insertData() - failed to send the data to MySQL database";
00673       opserr << p;
00674       opserr << endln << mysql_error(&mysql) << endln;          
00675       return -3;      
00676     }
00677   }
00678 
00679   return 0;
00680 }
00681 
00682 int 
00683 MySqlDatastore::getData(const char *tableName, char *columns[], int commitTag, Vector &data)
00684 {
00685   // check that we have a connection
00686   if (connection == false)
00687     return -1;
00688 
00689   // check that query string is big enough to hold the data, if not enlarge
00690   int sizeData = 128 + strlen(tableName);
00691   if ((query == 0) || (sizeData > sizeQuery)) { // *2 for extra space mysql needs
00692     if (query != 0)
00693       delete [] query;
00694     sizeQuery = sizeData; // 256 for the SLECECT data FROM ... blah blah
00695     query = new char [sizeQuery];
00696     if (query == 0) {
00697       opserr << "MySqlDatastore::getData - out of memory creating query of size";
00698       opserr << sizeQuery << endln;
00699       return -2;
00700     }
00701   }
00702 
00703   // to receive the data the database we do the following:
00704   // 1. use SELECT to receive the data from the database
00705   // 2. fetch the results from the server and copy to the Vectors data area
00706   // 3. clean up the MYSQL_RES datastructure 
00707   //
00708   // NOTE: probably using a malloc() and free() under the covers for this, could be 
00709   // very expensive .. IS THERE ANOTHER WAY TO DO THIS, i.e. SAVE A MYSQL_RES FOR
00710   // NUMEROUS USES??
00711 
00712   // form the SELECT query
00713   sprintf(query, "Select * FROM %s WHERE dbRun=%d AND commitTag=%d", 
00714           tableName, dbRun, commitTag);  
00715 
00716   // execute the SELECT query
00717   if (mysql_query(&mysql, query) != 0) {
00718     opserr << "MySqlDatastore::getData() - failed to receive vector from MySQL database";
00719     opserr << endln << mysql_error(&mysql) << endln;          
00720     return -3;
00721   } 
00722 
00723   // fetch the results from the database
00724   MYSQL_RES *results;
00725   MYSQL_ROW row;
00726   results = mysql_store_result(&mysql);
00727   if (results == NULL) {
00728     // no vector stored in db with these keys
00729     opserr << "MySqlDatastore::getData - no data in database for Vector with dbTag, cTag: ";
00730     opserr << dbTag << ", " << commitTag << endln;
00731     return -4;
00732   }
00733   row = mysql_fetch_row(results);
00734   if (row == NULL) {
00735     // no vector stored in db with these keys
00736     opserr << "MySqlDatastore::getData - no data in database for Vector with dbTag, cTag: ";
00737     opserr << dbTag << ", " << commitTag << endln;
00738     mysql_free_result(results);
00739     return -5;
00740   }
00741 
00742   // place the results into the vectors double array
00743   char *dataRes;
00744   for (int i=0; i<data.sz; i++) {
00745     dataRes = row[i+2];
00746     data.theData[i] = atof(dataRes);
00747   }
00748 
00749   // free the MYSQL_RES structure
00750   mysql_free_result(results);
00751 
00752   return 0;
00753 }
00754 
00755 
00756 int 
00757 MySqlDatastore::setDbRun(int run)
00758 {
00759   dbRun = run;
00760   return dbRun;
00761 }
00762 
00763 int 
00764 MySqlDatastore::getDbRun(void)
00765 {
00766   return dbRun;
00767 }
00768 
00769 
00770 int
00771 MySqlDatastore::createOpenSeesDatabase(const char *projectName) 
00772 {
00773   if (query == 0) {
00774     query = new char[512];
00775     sizeQuery = 512;
00776   }
00777 
00778   // create the database
00779   sprintf(query, "CREATE DATABASE %s", projectName);
00780   if (this->execute(query) != 0) {
00781     opserr << "MySqlDatastore::createOpenSeesDatabase() - could not create the database\n";
00782     return -1;
00783   }
00784 
00785   // link to the database, 
00786   if (mysql_select_db(&mysql, projectName) != 0) {
00787     opserr << "MySqlDatastore::createOpenSeesDatabase() - could not set the database\n";
00788     return -2;
00789   }
00790 
00791   // now create the tables in the database
00792   sprintf(query, "CREATE TABLE Messages ( dbTag INT NOT NULL, commitTag INT NOT NULL, size INT NOT NULL,\
00793                                           data MEDIUMBLOB, PRIMARY KEY (dbTag, commitTag, size) )");
00794 
00795   if (this->execute(query) != 0) {
00796     opserr << "MySqlDatastore::createOpenSeesDatabase() - could not create the Messagess table\n";
00797     return -3;
00798   }
00799 
00800   sprintf(query, "CREATE TABLE Matrices ( dbTag INT NOT NULL, commitTag INT NOT NULL, size INT NOT NULL,\
00801                                          data MEDIUMBLOB, PRIMARY KEY (dbTag, commitTag, size) )");
00802 
00803   if (this->execute(query) != 0) {
00804     opserr << "MySqlDatastore::createOpenSeesDatabase() - could not create the Matricess table\n";
00805     return -3;
00806   }
00807 
00808   sprintf(query, "CREATE TABLE Vectors ( dbTag INT NOT NULL, commitTag INT NOT NULL, size INT NOT NULL,\
00809                                          data MEDIUMBLOB, PRIMARY KEY (dbTag, commitTag, size) )");
00810 
00811   if (this->execute(query) != 0) {
00812     opserr << "MySqlDatastore::createOpenSeesDatabase() - could not create the Vectors table\n";
00813     return -3;
00814   }
00815 
00816   sprintf(query, "CREATE TABLE IDs ( dbTag INT NOT NULL, commitTag INT NOT NULL, size INT NOT NULL,\
00817                                      data MEDIUMBLOB, PRIMARY KEY (dbTag, commitTag, size) )");
00818 
00819   if (this->execute(query) != 0) {
00820     opserr << "MySqlDatastore::createOpenSeesDatabase() - could not create the ID's table\n";
00821     return -3;
00822   }
00823 
00824 
00825   // done
00826   return 0;
00827 }
00828 
00829 int
00830 MySqlDatastore::execute(const char *query) 
00831 {
00832 
00833   if (mysql_query(&mysql, query) != 0) {
00834     opserr << "MySqlDatastore::execute() - could not execute command: " << query;
00835     opserr << endln << mysql_error(&mysql) << endln;          
00836     return -1;
00837   } else 
00838     return 0;
00839 }
00840 

Generated on Mon Oct 23 15:05:00 2006 for OpenSees by doxygen 1.5.0