NewsForumsCodeLogsGallerySpecsSearch

bah.database feature request

BlitzMax Forums/Brucey's Modules/bah.database feature request

Htbaa(Posted 2008) [#1]
Brucey,

Could you add a GetTableInfo(tableName:String) method to bah.database?

It should return a data structure containing the TQueryField's of the given table.

Every driver could overload the method and perform the proper query to retrieve the table information.

MySQL
DESCRIBE `tablename`

SQLite
pragma table_info(`tablename`)


I don't know what the correct queries would be for the other databases.

Would this be possible?


Brucey(Posted 2008) [#2]
Sounds like a plan :-)


Htbaa(Posted 2008) [#3]
It would be really useful for the TQueryBuilder I'm working on. That way I can easily quote table- and column-names. The best place a table info retriever is of course in the database driver itself :-).


Htbaa(Posted 2008) [#4]
I don't want to be pushy :-) But do you have any idea when you might add this feature? It's not that it's a absolute requirement for my TQueryBuilder type (which is shaping nicely now) but it would be nice if the column names were available. Even more for the ORM/ActiveRecord stuff.


Brucey(Posted 2008) [#5]
Well, I'm away from home at the moment (Chargers vs Saints this weekend!!), so it won't be until the week.
I wonder if we should maybe have a TDBSchema type to represent a table and its fields? Just a thought...


Htbaa(Posted 2008) [#6]
As long as we're able to retrieve information about a given table it would be fine I guess. A TDBSchema should contain all tables, since a schema is your database layout. Instead of always requesting the table info it could make use of some form of lazy loading. For example, a TDBSchema will cache info for all tables from a given database schema, but will only execute a query to get the table info when the user asks for it. It can then put it in a cache so the next time the data will be there.

Generally a database layout is fixed within an application and is unlikely to change during runtime. Nonetheless it would be useful to have a Reset() method to reset the complete cache and a ResetTable(tableName:String) method.

.... Just now I was writing a simple prototype to demonstrate it but as my MaxIDE under Linux crashed I lost the source. But basically, it could contain these methods.

Type TDBSchema Abstract
	Field tableInfo:TMap
	
	'Returns a TMap containing TQueryField
	'Method should check if the key tableName is already
	'inside the tableInfo TMap. If it isn't, it should call
	'a SetTableInfo(tableName:String)
	Method GetTableInfo:TMap(tableName:String) Abstract
	
	'Set table info
	'It should execute a query to retrieve table info
	'about the given table. It should then add a entry into
	'tableInfo with the key tableName
	Method SetTableInfo(tableName:String) Abstract
	
	'Reset given tablename
	'Since there's a cache you could just remove the entry
	'from the tableInfo TMap
	Method ResetTable(tableName:String) Abstract
	
	'Reset all tablenames
	'Just clear the TMap
	Method Reset() Abstract
End Type


I'm suggesting using TQueryField types because they look like they can contain the data. Or perhaps some other type. The data this Type should store about a column could be: data type, NULL allowed, column name, primary key?

Just some thoughts :-)


Brucey(Posted 2008) [#7]
A TDBSchema should contain all tables...

Good point. That's what happens when I'm half-asleep ;-)


Htbaa(Posted 2008) [#8]
My prototype name should be something like TDBSchemaTable or something like that.


Brucey(Posted 2008) [#9]
Okay, I've knocked together something for starters...

connection now has a getTableInfo(tablename, withDDL) method.

It returns a TDBTable object, which currently contains name:String, columns:TDBColumn[] and ddl fields.
A TDBColumn has, name, dbType, nullable and defaultValue fields. Probably wants the primary-key flag added (if I can get that from most of the databases).

The second (optional) param of getTableInfo(), if set to True, will populate the ddl field of TDBTable with the SQL you would use to create that table. Not sure if ODBC will have that available, but most of the rest appear to have some way of getting that.

Not thoroughly tested as yet - only SQLite and MySQL so far, which seem to work.. or at least, don't crash...

The other drivers in SVN, have been updated to support the revised API, but have not had that particular functionality implemented yet.


Htbaa(Posted 2008) [#10]
Very nice! I'll update tomorrow! Need to go to bed now :-). For the time being the other drivers could just throw an exception when that method is called?


byo(Posted 2008) [#11]
Thanks a lot, Brucey.
I'll update right now.

Do you think you could support SQL Server in the future? :)
My company user it a lot and it really has many professional uses.

Anyway your modules are awesome.


Brucey(Posted 2008) [#12]
Do you think you could support SQL Server in the future?

SQL Server should be supported indirectly via the ODBC driver.
One of the ODBC examples shows how to connect to an Access database via ODBC, so it should be just as easy to do so to a "proper" database ;-)


Htbaa(Posted 2008) [#13]
It's working really nice! Thanks!


Brucey(Posted 2008) [#14]
the other drivers could just throw an exception when that method is called?

They should return Null at the moment... which is easy enough to test for.
Same would happen if the table was "not found".

Could possibly set the error state too, I suppose.


byo(Posted 2008) [#15]
Really great. I'll try that, thanks.