[OT] Table Creation Syntax + Visual SQL Query Builder // A GREAT Mac Cocoa MYSQL tool - Donationware

[Peter Haworth]

Sounds interesting Bob.  I'm not doing anything close to that but I am getting a list of tables in the db and then a list of columns in each table.  I use the revDatabaseTable Names to get a list of tables and then revDataFromQuery(,,DBID,"PRAGMA table_info(tablename)") to get the column info for all the columns in the table.  The column info includes the column name, data type, whether or not the column can be NULL, and the default value for the column, plus an indication of whether the column is the primary key or not.  

Unfortunately it doesn't give you everything you need to recreate that column elsewhere so may not be useful for you.  For example, there's no foreign key information there although there is another PRAGMA that will get you that info given a column name.  If you can get the info you need, it could save you some pretty tricky parsing of the CREATE statement.

Pete Haworth

On Jan 6, 2011, at 1:20 PM, Bob Sneidar wrote:

> Good to know Peter. I have been working on a Table Merge function for some time, and one of the features is to be able to isolate the SQL for creation of a table, allow the user to pick which columns to merge between the tables, ant then if necessary, create the table in the destination database with the columns that the user selected. So a method for producing the exact syntax for table and column was essential to this. 
> 
> It's still buggy, but functional. I keep going back through and reworking things, mainly interface issues. But the code to actually create new tables and insert columns into existing ones identical to the source columns is all intact. It's been a fun project and has taught me a lot about SQL, specifically the subtle differences in syntax, which SQL was supposedly going to eliminate when it was first conceived. 
> 
> Bob
> 
> 
> On Jan 6, 2011, at 11:47 AM, Peter Haworth wrote:
> 
>> SQLite does have an ALTER TABLE command.  You can use it to rename a table or add a column but that's all.  I've used the same technique as Bob to get the CREATE command for a table and I believe that the sql syntax is updated by an ALTER command, not a second entry created.  I use the SQLite admin plugin for Firefox do other types of table structure changes and it does indeed unload the table, delete it, then load it again and it seem to recreate any indexes/triggers associated with the table.  The CREATE syntax in th mastr table is recreated automatically since the table is re-created.
>> 
>> Pete Haworth
> 
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>