Index: /trunk/FACT++/src/fits2sql.cc =================================================================== --- /trunk/FACT++/src/fits2sql.cc (revision 19149) +++ /trunk/FACT++/src/fits2sql.cc (revision 19150) @@ -67,4 +67,5 @@ ("ignore-errors", po_switch(), "Adds the IGNORE keyword to the INSERT query (turns errors into warnings, ignores rows with errors)") ("const.*", var(), "Insert a constant number into the given column (--const.mycolumn=5). A special case is `/..../N/`") + ("delete", po_switch(), "Delete all entries first which fit all constant columns defined by --const") ; @@ -78,4 +79,5 @@ ("print-insert", po_switch(), "Print the INSERT query (note that it contains all data)") ("print-create", po_switch(), "Print the CREATE query") + ("print-delete", po_switch(), "Print the DELETE query") ("verbose,v", var(1), "Verbosity (0: quiet, 1: default, 2: more, 3, ...)") ; @@ -183,4 +185,11 @@ "regular expression is applied to the filename and N specifies the N-th " "sub-sequence which matches. To debug what matches, verbosity can be set to 3.\n" + "\n" + "Usually the previously defined constant values are helpful to create an index " + "which relates unambiguously the inserted data to the file. It might be useful " + "to delete all data which belongs to this particular file before new data is " + "entered. This can be achieved with the `--delete` directive. It deletes all " + "data from the table before inserting new data which fulfills the condition " + "defined by the `--const` directives.\n" "\n" "If a query failed, the query is printed to stderr together with the error message. " @@ -309,4 +318,5 @@ const bool noinsert = conf.Get("no-insert"); const bool dry_run = conf.Get("dry-run"); + const bool run_delete = conf.Get("delete"); const string engine = conf.Get("engine"); @@ -320,4 +330,5 @@ const bool print_create = conf.Get("print-create"); const bool print_insert = conf.Get("print-insert"); + const bool print_delete = conf.Get("print-delete"); const vector mymap = conf.Vec("map"); @@ -394,4 +405,5 @@ const auto fixed = conf.GetWildcardOptions("const.*"); + string rmquery = "DELETE FROM `"+table+"` WHERE 1"; for (auto it=fixed.cbegin(); it!=fixed.cend(); it++) { @@ -431,4 +443,5 @@ vec.emplace_back(name, val); + rmquery += " AND `"+name+"`="+val; } @@ -492,8 +505,8 @@ query += " `"+name; if (N>1) - query += "["+to_string(i)+"]"; + query += "["+to_string((long long int)i)+"]"; query += "` "+sqltype; if (col.type=='A') - query += '('+to_string(col.num)+')'; + query += '('+to_string((long long int)col.num)+')'; query += " NOT NULL COMMENT '"+ic->first; if (!col.unit.empty()) @@ -619,4 +632,26 @@ cout << "Table `" << table << "` created." << endl; + + try + { + if (run_delete && !fixed.empty() && !drop && !dry_run) + { + const mysqlpp::SimpleResult res = + connection.query(rmquery).execute(); + + if (verbose>0) + cout << res.rows() << " row(s) deleted.\n" << endl; + } + } + catch (const exception &e) + { + cerr << rmquery << "\n\n"; + cerr << "SQL query failed: " << e.what() << endl; + return 7; + } + + if (print_delete) + cout << rmquery << endl; + // ------------------------------------------------------------------------- @@ -642,5 +677,5 @@ query += " `"+c->column+"`"; else - query += " `"+c->column+"["+to_string(i)+"]`"; + query += " `"+c->column+"["+to_string((long long int)i)+"]`"; if (N>1 && i!=N-1) Index: /trunk/FACT++/src/root2sql.cc =================================================================== --- /trunk/FACT++/src/root2sql.cc (revision 19149) +++ /trunk/FACT++/src/root2sql.cc (revision 19150) @@ -70,4 +70,5 @@ ("ignore-errors", po_switch(), "Adds the IGNORE keyword to the INSERT query (turns errors into warnings, ignores rows with errors)") ("const.*", var(), "Insert a constant number into the given column (--const.mycolumn=5). A special case is `/..../N/`") + ("delete", po_switch(), "Delete all entries first which fit all constant columns defined by --const") ; @@ -81,4 +82,5 @@ ("print-insert", po_switch(), "Print the INSERT query (note that it contains all data)") ("print-create", po_switch(), "Print the CREATE query") + ("print-delete", po_switch(), "Print the DELETE query") ("verbose,v", var(1), "Verbosity (0: quiet, 1: default, 2: more, 3, ...)") ; @@ -190,4 +192,11 @@ "regular expression is applied to the filename and N specifies the N-th " "sub-sequence which matches. To debug what matches, verbosity can be set to 3.\n" + "\n" + "Usually the previously defined constant values are helpful to create an index " + "which relates unambiguously the inserted data to the file. It might be useful " + "to delete all data which belongs to this particular file before new data is " + "entered. This can be achieved with the `--delete` directive. It deletes all " + "data from the table before inserting new data which fulfills the condition " + "defined by the `--const` directives.\n" "\n" "If a query failed, the query is printed to stderr together with the error message. " @@ -338,4 +347,5 @@ const bool noinsert = conf.Get("no-insert"); const bool dry_run = conf.Get("dry-run"); + const bool run_delete = conf.Get("delete"); const string engine = conf.Get("engine"); @@ -351,4 +361,5 @@ const bool print_create = conf.Get("print-create"); const bool print_insert = conf.Get("print-insert"); + const bool print_delete = conf.Get("print-delete"); const vector mymap = conf.Vec("map"); @@ -417,4 +428,5 @@ const auto fixed = conf.GetWildcardOptions("const.*"); + string rmquery = "DELETE FROM `"+table+"` WHERE 1"; for (auto it=fixed.cbegin(); it!=fixed.cend(); it++) { @@ -454,4 +466,5 @@ vec.emplace_back(name, val); + rmquery += " AND `"+name+"`="+val; } @@ -473,4 +486,5 @@ continue; } + string name = o->GetName(); @@ -523,5 +537,5 @@ query += " `"+name; if (N>1) - query += "["+to_string(i)+"]"; + query += "["+to_string((long long int)i)+"]"; query += "` "+sqltype+" NOT NULL COMMENT '"+o->GetTitle()+"'"; if (N>1 && i!=N-1) @@ -654,4 +668,27 @@ cout << "Table `" << table << "` created." << endl; + + try + { + if (run_delete && !fixed.empty() && !drop && !dry_run) + { + const mysqlpp::SimpleResult res = + connection.query(rmquery).execute(); + + if (verbose>0) + cout << res.rows() << " row(s) deleted.\n" << endl; + } + } + catch (const exception &e) + { + cerr << rmquery << "\n\n"; + cerr << "SQL query failed: " << e.what() << endl; + return 7; + } + + if (print_delete) + cout << rmquery << endl; + + // ------------------------------------------------------------------------- @@ -677,5 +714,5 @@ query += " `"+c->column+"`"; else - query += " `"+c->column+"["+to_string(i)+"]`"; + query += " `"+c->column+"["+to_string((long long int)i)+"]`"; if (N>1 && i!=N-1)