[vtk-developers] vtkSQLDatabase
David C Thompson
dcthomp at sandia.gov
Tue Nov 27 19:22:49 EST 2007
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
More information about the vtk-developers
mailing list