Index: /branches/eam_branches/ipp-pstamp-20260421/psLib/src/db/psDB.c =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/psLib/src/db/psDB.c (revision 43003) +++ /branches/eam_branches/ipp-pstamp-20260421/psLib/src/db/psDB.c (revision 43004) @@ -713,5 +713,5 @@ // Create SQL statement string - psString query = psDBGenerateDeleteRowSQL(tableName, where,limit); + psString query = psDBGenerateDeleteRowSQL(tableName, where, limit); if (!query) { psError(PS_ERR_UNEXPECTED_NULL, false, "Query generation failed."); @@ -871,4 +871,24 @@ } +bool psDBFreeQueryPrepared (void) { + + // initalize the prepared query cache + if (!preparedQuery) { + return true; + } + + // start lock on query cache + if (psMemGetThreadSafety()) { + pthread_mutex_lock(&preparedQueryMutex); + } + + psFree (preparedQuery); + preparedQuery = NULL; + + pthread_mutex_unlock(&preparedQueryMutex); + + return true; +} + long p_psDBRunQueryPrepared(psDB *dbh, const psArray *rowSet, @@ -918,4 +938,5 @@ // add this statement to the cache psHashAdd(preparedQuery, query, stmt); + psFree (stmt); } else { psTrace("psLib.db", PS_LOG_INFO, "found statment in query cache"); @@ -1608,4 +1629,6 @@ // have to be free'd MYSQL_TIME *myTime = psAlloc(sizeof(MYSQL_TIME)); + memset(myTime, 0, sizeof(MYSQL_TIME)); + myTime->year = (unsigned int)tmTime->tm_year + 1900; myTime->month = (unsigned int)tmTime->tm_mon + 1; @@ -1619,11 +1642,17 @@ // in 5.0 and 5.6, my_bool is char myTime->neg = (bool)false; - // currently unused by mysql - myTime->second_part = (unsigned long)time->nsec; + + // MySQL expects fractional seconds in microseconds, not nanoseconds + myTime->second_part = (unsigned long)(time->nsec / 1000); + + // XXX remove: currently unused by mysql + // myTime->second_part = (unsigned long)time->nsec; + psFree(tmTime); bind[i].buffer = myTime; bind[i].buffer_length = sizeof(MYSQL_TIME); - bind[i].length = &bind[i].buffer_length; + // XXX bind[i].length = &bind[i].buffer_length; + bind[i].length = 0; bind[i].is_null = NULL; } else { @@ -2129,18 +2158,22 @@ char *limitString; - // delete all rows if where is NULL - if (!where) { + // delete all rows if WHERE is NULL and no limit is given (limit == 0) + if (!where && !limit) { psStringAppend(&query, "TRUNCATE TABLE %s", tableName); return query; } - // Generate where SQL substring - whereSQL = psDBGenerateWhereSQL(where, tableName); - if (!whereSQL) { + psStringAppend(&query, "DELETE FROM %s", tableName); + + if (where) { + // Generate where SQL substring + whereSQL = psDBGenerateWhereSQL(where, tableName); + if (!whereSQL) { psError(PS_ERR_UNEXPECTED_NULL, false, _("SQL substring generation failed.")); return NULL; - } - - psStringAppend(&query, "DELETE FROM %s %s", tableName, whereSQL); + } + psStringAppend(&query, " %s", whereSQL); + psFree(whereSQL); + } // Complete delete SQL command string @@ -2151,5 +2184,4 @@ psFree(limitString); } - psFree(whereSQL); return query; @@ -2945,2 +2977,3 @@ #endif // HAVE_PSDB + Index: /branches/eam_branches/ipp-pstamp-20260421/psLib/src/db/psDB.h =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/psLib/src/db/psDB.h (revision 43003) +++ /branches/eam_branches/ipp-pstamp-20260421/psLib/src/db/psDB.h (revision 43004) @@ -174,4 +174,11 @@ const char *query ///< SQL string to execute ); + +/** Free all prepared queries + * dialect is provided. Caveat emptor. + * + * @return long: the number of database rows affected + */ +bool psDBFreeQueryPrepared(void); /** Fetches the result of a SQL query Index: /branches/eam_branches/ipp-pstamp-20260421/psLib/test/db/tap_psDB.c =================================================================== --- /branches/eam_branches/ipp-pstamp-20260421/psLib/test/db/tap_psDB.c (revision 43003) +++ /branches/eam_branches/ipp-pstamp-20260421/psLib/test/db/tap_psDB.c (revision 43004) @@ -21,8 +21,11 @@ #define HOST "localhost" -#define USER "test" -#define PASSWD "" -#define DBNAME "test" #define PORT 0 + +static char *USER = ""; +static char *PASSWD = ""; +static char *DBNAME = ""; + +# define DEBUG 0 int main(int argc, char* argv[]) @@ -33,25 +36,28 @@ #endif - plan_tests(34 + 1); + plan_tests(53); + + ok(argc == 4, "USAGE: %s USER PASSWD DBNAME", argv[0]); + if (argc != 4) { done(); } + + USER = argv[1]; + PASSWD = argv[2]; + DBNAME = argv[3]; // see if we can open a db connection at all - { + if (1) { psDB *dbh = psDBInit(HOST, USER, PASSWD, DBNAME, PORT); ok(dbh, "open a database handle"); - if (!dbh) { - done(); - } + if (!dbh) { done(); } psFree(dbh); } - // test new database creation - // the next 3 tests will fail as the MySQL test account has insufficent - // permissions - { - psDB *dbh = psDBInit(HOST, USER, PASSWD, DBNAME, PORT); - - skip_start(!psDBCreate(dbh, "foobar"), 4, - "you probably don't have proper database permissions"); - ok(psDBCreate(dbh, "foobar"), "psDBCreate()"); + // Test new database creation. The next 3 tests will fail if the MySQL test account + // has insufficent permissions + if (1) { + psDB *dbh = psDBInit(HOST, USER, PASSWD, DBNAME, PORT); + ok(dbh, "re-open a database handle"); + + skip_start(!psDBCreate(dbh, "foobar"), 4, "you probably don't have proper database permissions"); ok(psDBChange(dbh, "foobar"), "psDBChange()"); ok(psDBDrop(dbh, "foobar"), "psDBDrop()"); @@ -64,6 +70,8 @@ // psDBCreateTable() & psDBDropTable() - { - psDB *dbh = psDBInit(HOST, USER, PASSWD, DBNAME, PORT); + if (1) { + psDB *dbh = psDBInit(HOST, USER, PASSWD, DBNAME, PORT); + ok(dbh, "re-open a database handle"); + psMetadata *md = psMetadataAlloc(); psMetadataAdd(md, PS_LIST_TAIL, "foo", PS_DATA_S32, "Primary Key", 0); @@ -80,9 +88,11 @@ } - { - psDB *dbh = psDBInit(HOST, USER, PASSWD, DBNAME, PORT); + if (1) { + psDB *dbh = psDBInit(HOST, USER, PASSWD, DBNAME, PORT); + ok(dbh, "re-open a database handle"); + psMetadata *md = psMetadataAlloc(); psMetadataAdd(md, PS_LIST_TAIL, "foo", PS_TYPE_S32, "Primary Key", 0); - psDBCreateTable(dbh, "bar", md); + ok(psDBCreateTable(dbh, "bar", md), "psDBCreateTable()"); ok(!psDBCreateTable(dbh, "bar", md), "psDBCreateTable() - table already exists"); @@ -90,5 +100,5 @@ psFree(md); - psDBDropTable(dbh, "bar"); + ok(psDBDropTable(dbh, "bar"), "psDBDropTable()"); ok(!psDBDropTable(dbh, "fubar"), "psDBDropTable() - non-existant table"); @@ -96,6 +106,7 @@ } - { - psDB *dbh = psDBInit(HOST, USER, PASSWD, DBNAME, PORT); + if (1) { + psDB *dbh = psDBInit(HOST, USER, PASSWD, DBNAME, PORT); + ok(dbh, "re-open a database handle"); // setup yak table for later tests @@ -103,5 +114,5 @@ psMetadata *md = psMetadataAlloc(); psMetadataAdd(md, PS_LIST_TAIL, "hair", PS_TYPE_F32, NULL, 0.0); - psDBCreateTable(dbh, "yak", md); + ok(psDBCreateTable(dbh, "yak", md), "psDBCreateTable"); psFree(md); } @@ -111,10 +122,10 @@ psMetadata *md = psMetadataAlloc(); psMetadataAdd(md, PS_LIST_TAIL, "color", PS_DATA_STRING, NULL, "100"); - psDBCreateTable(dbh, "horse", md); + ok(psDBCreateTable(dbh, "horse", md), "psDBCreateTable"); psFree(md); } // psDBInsertOneRow() - { + if (1) { psMetadata *md = psMetadataAlloc(); psMetadataAdd(md, PS_LIST_TAIL, "hair", PS_TYPE_F32, NULL, 10e3); @@ -125,5 +136,5 @@ } - { + if (1) { psMetadata *md = psMetadataAlloc(); psMetadataAdd(md, PS_LIST_TAIL, "hair", PS_TYPE_F32, NULL, NAN); @@ -156,4 +167,6 @@ } + // XXX bad + // psDBSelectColumn() { @@ -161,4 +174,7 @@ ok(column, "psDBSelectColumn() - select"); is_long(psArrayLength(column), 3, "psDBSelectColumn() - number of elements"); + + // XXX + // exit (2); // XXX this test is depending on the order the rows come out it... @@ -253,13 +269,77 @@ // psDBDeleteRows() - ok(psDBDeleteRows(dbh, "yak", NULL, 0), "psDBDeleteRows()"); + // Note: psDBDeleteRows without a WHERE clause is converted to TRUNCATE TABLE + // name. This query deletes the table rows but does not return a count of the + // deleted rows. Thus this returns a 0, not a positive integer. On failure, + // psDBDeleteRows returns -1 (0 rows can be a valid result) + + ok(psDBDeleteRows(dbh, "yak", NULL, 1) == 1, "psDBDeleteRows()"); + ok(psDBDeleteRows(dbh, "yak", NULL, 0) == 0, "psDBDeleteRows()"); // cleanup other tests - psDBDropTable(dbh, "horse"); - psDBDropTable(dbh, "yak"); - - psFree(dbh); - } - + ok(psDBDropTable(dbh, "horse"), "psDBDropTable"); + ok(psDBDropTable(dbh, "yak"), "psDBDropTable"); + + ok(psDBFreeQueryPrepared(), "psDBFreeQueryPrepared()"); + psFree(dbh); + } + + // Tests of TIME-related types + { + psDB *dbh = psDBInit(HOST, USER, PASSWD, DBNAME, PORT); + ok(dbh, "re-open a database handle"); + + // create t1 table for time tets + if (1) { + psMetadata *md = psMetadataAlloc(); + psMetadataAdd(md, PS_LIST_TAIL, "speed", PS_DATA_F32, NULL, 0.0); // dummy column speed + psMetadataAdd(md, PS_LIST_TAIL, "epoch", PS_DATA_TIME, NULL, NULL); // time-type column + ok(psDBCreateTable(dbh, "t1", md), "psDBCreateTable"); + psFree(md); + } + + { + psMetadata *md = psMetadataAlloc(); + + psTime *T1 = psTimeGetNow(PS_TIME_TAI); + psMetadataAdd(md, PS_LIST_TAIL, "epoch", PS_DATA_TIME, NULL, T1); + psMetadataAdd(md, PS_LIST_TAIL, "speed", PS_DATA_F32, NULL, 1.0); + psFree(T1); + + ok(psDBInsertOneRow(dbh, "t1", md),"psDBInsertOneRow()"); + psFree(md); + } + + // psDBInsertRows() + { + psArray *rowSet = psArrayAllocEmpty(3); + + psMetadata *R1 = psMetadataAlloc(); + psTime *T1 = psTimeGetNow(PS_TIME_TAI); + psMetadataAdd(R1, PS_LIST_TAIL, "speed", PS_DATA_F32, NULL, 2.0); + psMetadataAdd(R1, PS_LIST_TAIL, "epoch", PS_DATA_TIME, NULL, T1); + psArrayAdd(rowSet, 0, R1); + psFree(T1); + psFree(R1); + + psMetadata *R2 = psMetadataAlloc(); + psTime *T2 = psTimeFromISO("2026-04-15T00:00:00.0", PS_TIME_TAI); + psMetadataAdd(R2, PS_LIST_TAIL, "speed", PS_DATA_F32, NULL, 3.0); + psMetadataAdd(R2, PS_LIST_TAIL, "epoch", PS_DATA_TIME, NULL, T2); + psArrayAdd(rowSet, 0, R2); + psFree(T2); + psFree(R2); + + ok(psDBInsertRows(dbh, "t1", rowSet), "psDBInsertRows() - multi-row insert"); + + psFree(rowSet); + } + + // empty the database after tests are done + ok(psDBDropTable(dbh, "t1"), "psDBDropTable() - drop table"); + + ok(psDBFreeQueryPrepared(), "psDBFreeQueryPrepared()"); + psFree(dbh); + } done();