[vtk-developers] vtkSQLDatabase

[Shead, Timothy]

I'd argue that SetURL()/GetURL() don't make much sense on a concrete
database instance, and you don't really need them since your
CreateFromURL() factory method can dispatch the correct parameters to
the correct concrete database type.

Cheers,
Tim

Timothy M. Shead
Sandia National Laboratories
Data Analysis & Visualization
505-284-0139
 

> -----Original Message-----
> From: vtk-developers-bounces+tshead=sandia.gov at vtk.org 
> [mailto:vtk-developers-bounces+tshead=sandia.gov at vtk.org] On 
> Behalf Of David C Thompson
> Sent: Tuesday, November 27, 2007 5:23 PM
> To: Wilson, Andrew T; VTK Developer List
> Subject: [vtk-developers] vtkSQLDatabase
> 
> 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
> 
> _______________________________________________
> vtk-developers mailing list
> vtk-developers at vtk.org
> http://www.vtk.org/mailman/listinfo/vtk-developers
> 
>