[vtk-developers] vtkSQLDatabase

[David Thompson]

On Nov 28, 2007, at 6:26 AM, Jeff Baumes wrote:
>> 4. Adding a uniform interface for creating and dropping databases
>>   and tables?
>
> Is the goal here to create database-independent schemas, or to store
> database schemas in a way that is easy to reference later on (perhaps
> to recreate the same schema at different times, populate GUI drop-down
> menus, or perform some type of analysis / visualization of the
> database)?
Yes. For the former, the goal is not to represent all possible  
database schemas but to cover the subset of them which may be easily  
moved "across" databases.

> If it is the first, this will probably not happen often due to the
> database-dependent features that you mention.  Unless the
> "implementation-specific stuff" fields that you describe are empty, it
> will not work across database types.
I was pointing out that some implementation-specific stuff *could* be  
stored there, not that those fields existed solely for implementation  
specifics. For example, the "NOT NULL" and "DEFAULT xxx" phrases are  
generic SQL used to describe columns in a CREATE TABLE statement and  
would be put in the vtkSQLDatabase::Column.Attribute string. The goal  
would be for VTK to never put implementation-specific stuff in that  
string if filling it in programatically but to allow users to put  
implementation-specifics there with the knowledge that they could only  
use backends that support it.

> I can see the second as very useful.  It would be even more useful if
> a database schema structure could be automatically populated given a
> database URL.  In MySQL you could possibly parse a "SHOW CREATE TABLE"
> query to do this.
That is something I had in mind but don't want to promise implementing  
at the moment. It is also implementation specific.

> I would suggest also adding some way to specify foreign keys, as they
> are often important in understanding the connections between tables.
Thanks, that is a good idea. I'll take a stab at adding it to the  
vtkSQLDatabase::Index structure.

	David


>> Tim Shead wrote:
>>> 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.
>>
>> I agree that SetURL shouldn't be public, but it can be useful to keep
>> the URL of the database around... if a reconnect is required or if  
>> you
>> want to present information about the database to the user, it  
>> could be
>> handy. How about a protected SetURL() method used by CreateFromURL()?
>>
>> That would change the usage from the current classes, where an
>> implementation-specific instance is created and then its filename,  
>> URL,
>> etc. are set. Are there any cases not handled by CreateFromURL() that
>> are accommodated by the current code?
>>
>>        David
>>
>>>
>>>> -----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);
>>>> ...
>>
>>
>> _______________________________________________
>> vtk-developers mailing list
>> vtk-developers at vtk.org
>> http://www.vtk.org/mailman/listinfo/vtk-developers
>>
>
>
> -- 
> Jeff Baumes, Ph.D.
> R&D Engineer, Kitware Inc.
> (518) 371-3971 x132
> jeff.baumes at kitware.com
>