[vtk-developers] vtkSQLDatabase

[David C Thompson]

Andy (et al.),

We're mulling over the use of vtkSQLDatabase for a project but it would
require a few additions to the existing class. Do you (or anyone) have
objections to

1. Adding a URL parser to the vtkSQLDatabase class?

   This way, sqlite, mysql, and postgres could share a common
   SetURL() method instead of the current SetFileName,
   Set{HostName,UserName,Password,Port}, SetURL trichotomy.
   A vtksys::RegularExpression would match

     file:///path     or maybe     sqlite:///path       - for SQLITE
     mysql://[user[:password]@]hostname[:port]/database - for MySQL
     pgsql://[user[:password]@]hostname[:port]/database - for Postgres

   It seems easily extensible to other backends and could conceivably be
   used to have vtkSQLDatabase create the proper subclass given the url.
   There would be a static method to examine a character string and
   return username, password, hostname, port, and database(or file
   path) as appropriate, with NULLs returned for portions not present:

   static void ParseURL( const char* URL, char*& protocol,
      char*& user, char*& passwd, char*& host, char*& port,
      char*& path );
   static vtkSQLDatabase* CreateFromURL( const char* URL );
   vtkSetStringMacro(URL);
   vtkGetStringMacro(URL);

2. Adding enums to represent the various basic column storage types?
   This is an area where SQL implementations vary significantly.
   The enums we're interested in are show in #4 below.

3. Adding #define VTK_SQL_FEATURE_TRIGGERS 1009?

4. Adding a uniform interface for creating and dropping databases
   and tables? This is a place where various SQL backends diverge
   significantly in syntax/keywords. For instance, Postgres uses a
   special sequence domain for automatically incremented default column
   values while MySQL uses "int" but with the "AUTO_INCREMENT" keyword
   in the list of options for the column. Also, some backends don't
   have "IF EXISTS" while others do. Here's the implementation we
   would prefer (I'll mention why just below):

   class vtkSQLDatabase
   {
   public:
     virtual bool CreateDatabase( const char* URL, bool dropIfExists );
     virtual bool DropDatabase( const char* URL );

     //BTX
     enum ColumnType
       { SERIAL, SMALLINT, INT, BIGINT,
         VARCHAR, TEXT, REAL, DOUBLE, BLOB,
         TIME, DATE, TIMESTAMP };
     enum IndexType
       { INDEX, UNIQUE, PRIMARY_KEY };
     enum TriggerType
       { BEFORE_INSERT, AFTER_INSERT,
         BEFORE_UPDATE, AFTER_UPDATE,
         BEFORE_DELETE, AFTER_DELETE };
     struct Column
       {
       int Type; // one of the ColumnType enums
       int Size; // used when required, ignored otherwise (e.g. varchar)
       const char* Name;
       const char* Attributes; // may have implementation-specific stuff
       };
     struct Index
       {
       int Type; // one of the IndexType enums
       vtkstd::vector<vtkstd::string> ColumnNames;
       };
     struct Trigger
       {
       int Type; // one of the TriggerType enums
       const char* Name;
       const char* Action; // may have implementation-specific stuff
       };
     struct Table
       {
       const char* Name;
       vtkstd::vector<vtkSQLDatabase::Column> Columns;
       vtkstd::vector<vtkSQLDatabase::Index> Indices;
       vtkstd::vector<vtkSQLDatabase::Trigger> Triggers;
       };

     virtual bool CreateTable( vtkSQLDatabase::Table* table, bool dropIfExists );
     //ETX
     virtual bool DropTable( const char* TableName );
   };

   With these public structs, it is simple to store a database schema in
   a compiled form. A few helper methods would let you declare a schema
   like this:

   vtkstd::vector<vtkSQLDatabase::Table> tables = NewTables(
     NewTable( "TestTable1",
       NewColumns(
         NewColumn( vtkSQLDatabase::SERIAL,  -1, "Key", "" ),
         NewColumn( vtkSQLDatabase::VARCHAR, 80, "Text", "NOT NULL" ),
         NewColumn( vtkSQLDatabase::INT,     -1, "Num", "NOT NULL DEFAULT -1" ),
         0 ),
       NewIndices(
         NewIndex( vtkSQLDatabase::PRIMARY_KEY, "Key", 0 ),
         NewIndex( vtkSQLDatabase::INDEX, "Num", "Text", 0 ),
         0 ),
       NewTriggers(
         NewTrigger( "SumTrigger", vtkSQLDatabase::AFTER_INSERT,
           "FOR EACH ROW BEGIN "
           " SELECT SUM(Num) INTO @s FROM TestTable1 WHERE Key = NEW.Key;"
           " REPLACE INTO TestTable2(Key,Sum) VALUES (NEW.Key, at s);"
           "END"
         0 )
     ),
     NewTable( "TestTable2",
       // ...
     ),
     0 );

   and quickly create a database from the schema.

Does anyone have objections / comments / nitpicks?

	Thanks,
	David