Changeset 19109

Timestamp:

07/31/18 20:43:31 (6 years ago)

Author:

tbretz

Message:

Implemented unisgned columns, row-format and now using the database default engine if not specified.

File:

• trunk/FACT++/src/fits2sql.cc (modified) (13 diffs)

trunk/FACT++/src/fits2sql.cc

@@ -8 +8 @@
 
 #include "zfits.h"
-
-/*
-#include <TROOT.h>
-#include <TFile.h>
-#include <TTree.h>
-#include <TLeaf.h>
-#include <TError.h>
-*/
 
 using namespace std;
@@ -65 +57 @@
         ("sql-type",       vars<Map>(),               "Allows to overwrite the calculated SQL type for a given column e.g. 'sql-column-name/UNSIGNED IN'")
         ("ignore",         vars<string>(),            "Ignore the given leaf, if the given regular expression matches")
+        ("unsigned",       vars<string>(),            "In fits files per default columns are signed. This interpretss the column as unsigned value. Use the FITS column name.")
         ("primary",        vars<string>(),            "List of columns to be used as primary keys during table creation (in connection with --create)")
         ("first",          var<int64_t>(int64_t(0)),  "First event to start with (default: 0), mainly for test purpose")
         ("max",            var<int64_t>(int64_t(0)),  "Maximum number of events to process (0: all), mainly for test purpose")
-        ("engine",         var<string>("InnoDB"),     "Database engine to be used when a new table is created")
+        ("engine",         var<string>(""),           "Database engine to be used when a new table is created")
+        ("row-format",     var<string>(""),           "Defines the ROW_FORMAT keyword for table creation")
         ("duplicate",      var<string>(""),           "Specifies an assignment_list for an 'ON DUPLICATE KEY UPDATE' expression")
         ("ignore-errors",  po_switch(),               "Adds the IGNORE keyword to the INSERT query (turns errors into warnings, ignores rows with errors)")
@@ -149 +143 @@
         "once. Note that the combination of these columns must be unique.\n"
         "\n"
-        "All columns are created as NOT NULL as default and the table is created as "
-        "InnoDB (default).\n"
+        "All columns are created as NOT NULL as default. To force a database engine "
+        "and/or a storage format, use --engine and --rot-format.\n"
         "\n"
         "Usually, the INSERT query would fail if the PRIMARY key exists already. "
@@ -199 +193 @@
     kDouble,
     kInt8,
+    kUInt8,
     kInt16,
+    kUInt16,
     kInt32,
+    kUInt32,
     kInt64,
+    kUInt64,
 //    kMJD,
 };
@@ -208 +206 @@
 {
     { 'A', { kVarchar, "VARCHAR"  } },
+    { 'a', { kVarchar, "VARCHAR"  } },
     { 'L', { kBool,    "BOOLEAN"  } },
+    { 'l', { kBool,    "BOOLEAN"  } },
     { 'B', { kInt8,    "TINYINT"  } },
+    { 'b', { kUInt8,   "TINYINT UNSIGNED"  } },
     { 'I', { kInt16,   "SMALLINT" } },
+    { 'i', { kUInt16,  "SMALLINT UNSIGNED" } },
     { 'J', { kInt32,   "INT"      } },
+    { 'j', { kUInt32,  "INT UNSIGNED"      } },
     { 'K', { kInt64,   "BIGINT"   } },
+    { 'k', { kUInt64,  "BIGINT UNSIGNED"   } },
     { 'E', { kFloat,   "FLOAT"    } },
+    { 'e', { kFloat,   "FLOAT"    } },
     { 'D', { kDouble,  "DOUBLE"   } },
+    { 'd', { kDouble,  "DOUBLE"   } },
 };
 
@@ -246 +252 @@
         case kBool:
         case kInt8:    str << int32_t(reinterpret_cast<int8_t*>(ptr)[index]); break;
+        case kUInt8:   str << uint32_t(reinterpret_cast<uint8_t*>(ptr)[index]); break;
         case kInt16:   str << reinterpret_cast<int16_t*>(ptr)[index]; break;
+        case kUInt16:  str << reinterpret_cast<uint16_t*>(ptr)[index]; break;
         case kInt32:   str << reinterpret_cast<int32_t*>(ptr)[index]; break;
+        case kUInt32:  str << reinterpret_cast<uint32_t*>(ptr)[index]; break;
         case kInt64:   str << reinterpret_cast<int64_t*>(ptr)[index]; break;
+        case kUInt64:  str << reinterpret_cast<uint64_t*>(ptr)[index]; break;
         case kNone:
             break;
@@ -285 +295 @@
 
     const string engine          = conf.Get<string>("engine");
+    const string row_format      = conf.Get<string>("row-format");
     const string duplicate       = conf.Get<string>("duplicate");
 
@@ -298 +309 @@
     const vector<string> _ignore = conf.Vec<string>("ignore");
     const vector<string> primary = conf.Vec<string>("primary");
+
+    const vector<string> notsigned = conf.Vec<string>("unsigned");
 
     // -------------------------------------------------------------------------
@@ -381 +394 @@
             continue;
 
-        const char tn = col.type;
+        const char tn = find(notsigned.cbegin(), notsigned.cend(), ic.first)!=notsigned.cend() ?
+            tolower(col.type) : toupper(col.type);
 
         auto it = ConvFits.find(tn);
@@ -421 +435 @@
             if (!col.unit.empty())
                 query += "["+col.unit+"]";
-            query += ": "+col.comment+"'";
+            if (!col.comment.empty())
+                query += ": "+col.comment;
+            query += +"'";
+            if (N>1 && i!=N-1)
+                query += ",\n";
         }
 
@@ -463 +481 @@
     query +=
         ")\n"
-        "DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci\n"
-        "ENGINE="+engine+"\n"
-        "COMMENT='created by "+conf.GetName()+"'\n";
+        "DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci\n";
+    if (!engine.empty())
+        query += "ENGINE="+engine+"\n";
+    if (!row_format.empty())
+        query += "ROW_FORMAT="+row_format+"\n";
+    query += "COMMENT='created by "+conf.GetName()+"'\n";
 
 
@@ -530 +551 @@
             else
                 query += "   `"+c->column+"["+to_string(i)+"]`";
+
+            if (N>1 && i!=N-1)
+                query += ",\n";
         }
     }
@@ -564 +588 @@
                 if (print_insert && i==0)
                     query += " /* "+c->column+" -> "+c->branch+" */";
+
+                if (N>1 && i!=N-1)
+                    query += ",\n";
             }
         }