Index: trunk/ippTools/src/pxadminConfig.c =================================================================== --- trunk/ippTools/src/pxadminConfig.c (revision 16557) +++ trunk/ippTools/src/pxadminConfig.c (revision 16588) @@ -31,5 +31,5 @@ bool status; - fprintf (stderr, "\nPan-STARRS Phase 2 Admin Tool\n\n"); + fprintf (stderr, "\nPan-STARRS DataBase Admin Tool\n\n"); fprintf (stderr, "Usage: %s [mode]\n", program); fprintf (stderr, " [mode] : -create | -delete\n\n"); @@ -90,9 +90,9 @@ // paul's argument parsing convention requires: -key value psMetadataAddStr(arguments, PS_LIST_TAIL, "-create", 0, - "create the P2 tables", ""); + "create all IPP tables", ""); psMetadataAddStr(arguments, PS_LIST_TAIL, "-delete", 0, - "delete the P2 tables", ""); + "delete all IPP tables", ""); psMetadataAddStr(arguments, PS_LIST_TAIL, "-recreate", 0, - "delete and recreate the P2 tables", ""); + "delete and recreate all tables", ""); if (config->mode == PXADMIN_MODE_NONE) { Index: trunk/ippTools/src/pxtables.c =================================================================== --- trunk/ippTools/src/pxtables.c (revision 16557) +++ trunk/ippTools/src/pxtables.c (revision 16588) @@ -54,8 +54,10 @@ CREATE_TABLE(summitExpCreateTable); CREATE_TABLE(summitImfileCreateTable); - CREATE_TABLE(pzPendingExpCreateTable); - CREATE_TABLE(pzPendingImfileCreateTable); - CREATE_TABLE(pzDoneExpCreateTable); - CREATE_TABLE(pzDoneImfileCreateTable); +// CREATE_TABLE(pzPendingExpCreateTable); +// CREATE_TABLE(pzPendingImfileCreateTable); +// CREATE_TABLE(pzDoneExpCreateTable); +// CREATE_TABLE(pzDoneImfileCreateTable); + CREATE_TABLE(pzDownloadExpCreateTable); + CREATE_TABLE(pzDownloadImfileCreateTable); CREATE_TABLE(newExpCreateTable); CREATE_TABLE(newImfileCreateTable); @@ -151,8 +153,10 @@ DROP_TABLE(summitExpDropTable); DROP_TABLE(summitImfileDropTable); - DROP_TABLE(pzPendingExpDropTable); - DROP_TABLE(pzPendingImfileDropTable); - DROP_TABLE(pzDoneExpDropTable); - DROP_TABLE(pzDoneImfileDropTable); +// DROP_TABLE(pzPendingExpDropTable); +// DROP_TABLE(pzPendingImfileDropTable); +// DROP_TABLE(pzDoneExpDropTable); +// DROP_TABLE(pzDoneImfileDropTable); + DROP_TABLE(pzDownloadExpDropTable); + DROP_TABLE(pzDownloadImfileDropTable); DROP_TABLE(newExpDropTable); DROP_TABLE(newImfileDropTable); Index: trunk/ippTools/src/pzgetexp.c =================================================================== --- trunk/ippTools/src/pzgetexp.c (revision 16557) +++ trunk/ippTools/src/pzgetexp.c (revision 16588) @@ -2,5 +2,5 @@ * pzgetexp.c * - * Copyright (C) 2006 Joshua Hoblitt + * Copyright (C) 2006-2008 Joshua Hoblitt * * This program is free software; you can redistribute it and/or modify it @@ -186,31 +186,4 @@ psFree(newSummitExps); - // add new exps to pzPendingExp -- must be done before the new exps are - // added to summitExp because of the SQL logic - { - char *query = - "INSERT INTO pzPendingExp" - " SElECT" - " incoming.exp_name," - " incoming.camera," - " incoming.telescope" - " FROM incoming" - " LEFT JOIN summitExp" - " USING(exp_name, camera, telescope)" - " WHERE" - " summitExp.exp_name is NULL" - " AND summitExp.camera is NULL" - " AND summitExp.telescope is NULL"; - - if (!p_psDBRunQuery(config->dbh, query)) { - // rollback - if (!psDBRollback(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - } - psError(PS_ERR_UNKNOWN, false, "database error"); - return false; - } - } - // add new exps to summitExp { Index: trunk/ippTools/src/pzgetimfiles.c =================================================================== --- trunk/ippTools/src/pzgetimfiles.c (revision 16557) +++ trunk/ippTools/src/pzgetimfiles.c (revision 16588) @@ -226,35 +226,9 @@ psFree(newImfiles); - // queue the imfiles (files) dest. for pzPendingImfile in pending -- must - // be done before the imfile is insterted into summitImfile (after that - // happens when don't know which imfiles are new anymore (well thats not - // exactly true but it may be someday if we have to beable to resync to - // modified datastore entries) - { - char *query = - "INSERT IGNORE INTO pzPendingImfile" - " SElECT" - " incoming.exp_name," - " incoming.camera," - " incoming.telescope," - " incoming.class," - " incoming.class_id" - " FROM incoming"; - - if (!p_psDBRunQuery(config->dbh, query)) { - // rollback - if (!psDBRollback(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - } - psError(PS_ERR_UNKNOWN, false, "database error"); - return false; - } - } - // copy imfiles (files) from the temp table into summitImfiles { char *query = "INSERT IGNORE INTO summitImfile" - " SElECT" + " SELECT" " incoming.exp_name," " incoming.camera," @@ -313,61 +287,32 @@ } - // cp the pzPendingExp entry to pzDoneExp + // add new exps to pzDownloadExp -- must be done before the new exps are + // added to summitExp because of the SQL logic { char *query = - "INSERT INTO pzDoneExp" + "INSERT INTO pzDownloadExp" " SELECT" - " pzPendingExp.*" - " FROM pzPendingExp" + " incoming.exp_name," + " incoming.camera," + " incoming.telescope," + " \"run\"" + " FROM incoming" + " LEFT JOIN pzDownloadExp" + " USING(exp_name, camera, telescope)" " WHERE" - " pzPendingExp.exp_name = '%s'" - " AND pzPendingExp.camera = '%s'" - " AND pzPendingExp.telescope = '%s'"; - if (!p_psDBRunQuery(config->dbh, query, filesetid, camera, telescope)) { - // rollback - if (!psDBRollback(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - } - psError(PS_ERR_UNKNOWN, false, "database error"); - return false; - } - - // sanity check: we should have inserted only one row - psU64 affected = psDBAffectedRows(config->dbh); - if (psDBAffectedRows(config->dbh) != 1) { - // rollback - if (!psDBRollback(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - } - psError(PS_ERR_UNKNOWN, false, "should have affected 1 row but %" PRIu64 " rows were modified", affected); - return false; - } - } - - // remove the pzPendingExp entry for this exp (fileset) - { - char *query = - "DELETE FROM pzPendingExp" - " WHERE" - " exp_name = '%s'" - " AND camera = '%s'" - " AND telescope = '%s'"; - if (!p_psDBRunQuery(config->dbh, query, filesetid, camera, telescope)) { - // rollback - if (!psDBRollback(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - } - psError(PS_ERR_UNKNOWN, false, "database error"); - return false; - } - - // sanity check: we should have removed only one row - psU64 affected = psDBAffectedRows(config->dbh); - if (psDBAffectedRows(config->dbh) != 1) { - // rollback - if (!psDBRollback(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - } - psError(PS_ERR_UNKNOWN, false, "should have affected 1 row but %" PRIu64 " rows were modified", affected); + " pzDownloadExp.exp_name is NULL" + " AND pzDownloadExp.camera is NULL" + " AND pzDownloadExp.telescope is NULL" + " GROUP BY" + " incoming.exp_name," + " incoming.camera," + " incoming.telescope"; + + if (!p_psDBRunQuery(config->dbh, query)) { + // rollback + if (!psDBRollback(config->dbh)) { + psError(PS_ERR_UNKNOWN, false, "database error"); + } + psError(PS_ERR_UNKNOWN, false, "database error"); return false; } Index: trunk/ippTools/src/pztool.c =================================================================== --- trunk/ippTools/src/pztool.c (revision 16557) +++ trunk/ippTools/src/pztool.c (revision 16588) @@ -45,4 +45,5 @@ static psArray *pzGetPendingCameras(pxConfig *config); static psArray *pzArrayZip(psArray *arraySet, psS64 limit); +static bool pzDownloadExpSetState(pxConfig *config, const char *exp_name, const char *camera, const char *telescope, const char *state); # define MODECASE(caseName, func) \ @@ -204,11 +205,15 @@ "SELECT" " summitExp.*" - " FROM pzPendingExp" - " JOIN summitExp" + " FROM summitExp" + " LEFT JOIN pzDownloadExp" " USING(exp_name, camera, telescope)" + " WHERE" + " pzDownloadExp.exp_name IS NULL" + " AND pzDownloadExp.camera IS NULL" + " AND pzDownloadExp.telescope IS NULL" ); if (config->where) { - psString whereClause = psDBGenerateWhereConditionSQL(config->where, "pzPendingExp"); + psString whereClause = psDBGenerateWhereConditionSQL(config->where, "pzDownloadExp"); psStringAppend(&query, " AND %s", whereClause); psFree(whereClause); @@ -281,5 +286,5 @@ if (config->where) { - psString whereClause = psDBGenerateWhereConditionSQL(config->where, "pzPendingImfile"); + psString whereClause = psDBGenerateWhereConditionSQL(config->where, "pzDownloadImfile"); psStringAppend(&query, " AND %s", whereClause); psFree(whereClause); @@ -355,5 +360,5 @@ // need to know exp_name, camera, telescope, class, class_id (to find the - // pzPendingImfile entry and the URI for the newImfile entry. + // pzDownloadImfile entry and the URI for the newImfile entry. // start a transaction so it's all rows or nothing @@ -363,19 +368,23 @@ } - // cp the imfile into pzDoneImfile + // cp the imfile into pzDownloadImfile { char *query = - "INSERT INTO pzDoneImfile" + "INSERT INTO pzDownloadImfile" " SELECT" - " pzPendingImfile.*," + " exp_name," + " camera," + " telescope," + " class," + " class_id," " '%s'," // uri of downloaded file " '%d'" // fault code - " FROM pzPendingImfile" + " FROM summitImfile" " WHERE" - " pzPendingImfile.exp_name = '%s'" - " AND pzPendingImfile.camera = '%s'" - " AND pzPendingImfile.telescope = '%s'" - " AND pzPendingImfile.class = '%s'" - " AND pzPendingImfile.class_id = '%s'"; + " summitImfile.exp_name = '%s'" + " AND summitImfile.camera = '%s'" + " AND summitImfile.telescope = '%s'" + " AND summitImfile.class = '%s'" + " AND summitImfile.class_id = '%s'"; if (!p_psDBRunQuery(config->dbh, query, uri, code, exp_name, camera, telescope, class, class_id)) { @@ -400,34 +409,11 @@ } - // removve the entry from pzPendingImfile - { - char *query = - "DELETE FROM pzPendingImfile" - " WHERE" - " pzPendingImfile.exp_name = '%s'" - " AND pzPendingImfile.camera = '%s'" - " AND pzPendingImfile.telescope = '%s'" - " AND pzPendingImfile.class = '%s'" - " AND pzPendingImfile.class_id = '%s'"; - - if (!p_psDBRunQuery(config->dbh, query, exp_name, camera, telescope, class, class_id)) { - // rollback - if (!psDBRollback(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - } + if (!copydoneCompleteExp(config)) { + // rollback + if (!psDBRollback(config->dbh)) { psError(PS_ERR_UNKNOWN, false, "database error"); - return false; - } - - // sanity check: we should have removed only one row - psU64 affected = psDBAffectedRows(config->dbh); - if (psDBAffectedRows(config->dbh) != 1) { - // rollback - if (!psDBRollback(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - } - psError(PS_ERR_UNKNOWN, false, "should have affected 1 row but %" PRIu64 " rows were modified", affected); - return false; - } + } + psError(PS_ERR_UNKNOWN, false, "copydoneCompleteExp() failed"); + return false; } @@ -439,9 +425,4 @@ } psError(PS_ERR_UNKNOWN, false, "database error"); - return false; - } - - if (!copydoneCompleteExp(config)) { - psError(PS_ERR_UNKNOWN, false, "copydoneCompleteExp() failed"); return false; } @@ -493,16 +474,8 @@ } - // start a transaction so all newExp's can start off with a state of run - if (!psDBTransaction(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - psFree(output); - return false; - } - - for (long i = 0; i < psArrayLength(output); i++) { psMetadata *row = output->data[i]; - pzDoneExpRow *doneExp = pzDoneExpObjectFromMetadata(row); + pzDownloadExpRow *doneExp = pzDownloadExpObjectFromMetadata(row); if (!newExpInsert(config->dbh, @@ -521,8 +494,4 @@ ) ) { - // rollback - if (!psDBRollback(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - } psError(PS_ERR_UNKNOWN, false, "database error"); psFree(doneExp); @@ -533,5 +502,5 @@ psS64 exp_id = psDBLastInsertID(config->dbh); - // insert new all pzDoneImfile for the exp into newImfile + // insert newImfiles { char *query = @@ -539,17 +508,13 @@ " SElECT" " %" PRId64 "," // exp_id - " pzDoneImfile.class_id," // tmp_class_id - " pzDoneImfile.uri" // uri - " FROM pzDoneImfile" + " pzDownloadImfile.class_id," // tmp_class_id + " pzDownloadImfile.uri" // uri + " FROM pzDownloadImfile" " WHERE" - " pzDoneImfile.exp_name = '%s'" - " AND pzDoneImfile.camera = '%s'" - " AND pzDoneImfile.telescope = '%s'"; + " pzDownloadImfile.exp_name = '%s'" + " AND pzDownloadImfile.camera = '%s'" + " AND pzDownloadImfile.telescope = '%s'"; if (!p_psDBRunQuery(config->dbh, query, exp_id, doneExp->exp_name, doneExp->camera, doneExp->telescope)) { - // rollback - if (!psDBRollback(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - } psError(PS_ERR_UNKNOWN, false, "database error"); psFree(doneExp); @@ -561,8 +526,4 @@ psU64 affected = psDBAffectedRows(config->dbh); if (psDBAffectedRows(config->dbh) < 1) { - // rollback - if (!psDBRollback(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - } psError(PS_ERR_UNKNOWN, false, "should have affected at least 1 row but %" PRIu64 " rows were modified", affected); psFree(doneExp); @@ -572,4 +533,12 @@ } + // set pzDownloadExp.state to 'stop' + if (!pzDownloadExpSetState(config, doneExp->exp_name, doneExp->camera, doneExp->telescope, "stop")) { + psError(PS_ERR_UNKNOWN, false, "failed to change pzDownloadExp.state for %s:%s:%s", doneExp->exp_name, doneExp->camera, doneExp->telescope); + psFree(doneExp); + psFree(output); + return false; + } + psFree(doneExp); } @@ -577,9 +546,4 @@ psFree(output); - if (!psDBCommit(config->dbh)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - return false; - } - return true; } @@ -588,5 +552,5 @@ { // get a list of cameras we've seen exps for - if (!p_psDBRunQuery(config->dbh, "SELECT DISTINCT camera FROM pzPendingImfile")) { + if (!p_psDBRunQuery(config->dbh, "SELECT DISTINCT camera FROM pzDownloadExp")) { psError(PS_ERR_UNKNOWN, false, "database error"); return false; @@ -666,16 +630,16 @@ PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false); - psString query = psStringCopy("SELECT * FROM pzDoneImfile"); + psString query = psStringCopy("SELECT * FROM pzDownloadImfile"); if (faulted) { // list only faulted rows - psStringAppend(&query, " %s", "WHERE pzDoneImfile.fault != 0"); + psStringAppend(&query, " %s", "WHERE pzDownloadImfile.fault != 0"); } else { // don't list faulted rows - psStringAppend(&query, " %s", "WHERE pzDoneImfile.fault = 0"); + psStringAppend(&query, " %s", "WHERE pzDownloadImfile.fault = 0"); } if (psListLength(where->list)) { - psString whereClause = psDBGenerateWhereConditionSQL(where, "pzDoneImfile"); + psString whereClause = psDBGenerateWhereConditionSQL(where, "pzDownloadImfile"); psStringAppend(&query, " AND %s", whereClause); psFree(whereClause); @@ -727,5 +691,5 @@ PXOPT_LOOKUP_S16(code, config->args, "-code", true, false); - if (!pxSetFaultCode(config->dbh, "pzDoneImfile", config->where, code)) { + if (!pxSetFaultCode(config->dbh, "pzDownloadImfile", config->where, code)) { psError(PS_ERR_UNKNOWN, false, "failed to set set fault flag"); return false; @@ -747,5 +711,5 @@ if (config->where) { - psString whereClause = psDBGenerateWhereConditionSQL(config->where, "pzDoneImfile"); + psString whereClause = psDBGenerateWhereConditionSQL(config->where, "pzDownloadImfile"); psStringAppend(&query, " AND %s", whereClause); psFree(whereClause); @@ -768,4 +732,30 @@ +static bool pzDownloadExpSetState(pxConfig *config, const char *exp_name, const char *camera, const char *telescope, const char *state) +{ + PS_ASSERT_PTR_NON_NULL(state, false); + + // check that state is a valid string value + if (!( + (strncmp(state, "run", 4) == 0) + || (strncmp(state, "stop", 5) == 0) + || (strncmp(state, "reg", 4) == 0) + ) + ) { + psError(PS_ERR_UNKNOWN, false, + "invalid pzDownloadExp state: %s", state); + return false; + } + + char *query = "UPDATE pzDownloadExp SET state = '%s' WHERE exp_name = '%s' and camera = '%s' and telescope = '%s'"; + if (!p_psDBRunQuery(config->dbh, query, state, exp_name, camera, telescope)) { + psError(PS_ERR_UNKNOWN, false, "failed to change state for %s:%s:%s", exp_name, camera, telescope); + return false; + } + + return true; +} + + #if 0 static psArray *pzArrayAddArray(psArray *array, psArray *input)