Create database creation script from database?

[Knuth Konrad]

Does anybody know of a tool which takes a database (MS Access in this case, but more formats/databases welcomed) and creates from that a SQL script (re-)create the database and preferably it's content?

My Google seach turned up lots of tools, but most of them dealt with flavors of SQL servers (Oracle, MS, MySQL) or were meant as upsizing tools from a desktop DB (Access, dBASE) to a SQL server.

Basically, I'm trying to accomplish some kind of "Diff" between two databases: one out in production, with data in it, one in development with new and/or altered tables/indexes and/or updated data. My idea was to create a script with such a tool and my application would detect such an "update script" and simply execute the statements in it.

I'd also welcome other ideas on how to approach such an database update to a live environment.

[Michael Mattias]

WinSQL has a 'diff' function built-in; i also has a "CREATE TABLE" statement builder.

The Pro version is about $250.00. The "Lite" version is free for the download but it doesn't have these goodies. http://www.synametrics.com

Very nice product, I have used for several years.

MCM

[Chris Holbrook]

see post #35 here

[Brian Chirgwin]

Basically, I'm trying to accomplish some kind of "Diff" between two databases: one out in production, with data in it, one in development with new and/or altered tables/indexes and/or updated data. My idea was to create a script with such a tool and my application would detect such an "update script" and simply execute the statements in it.

I'd also welcome other ideas on how to approach such an database update to a live environment.

In a commercial product a table was created that contained a version #. This can be created during the first upgrade (if the file doesn't exist, assume version 1.0). Then an upgrade application (exe) was executed during installation. It checked the existing version and performed the upgrade(s) necessary. A database would be upgraded through the versions necessary (i.e. 1.0 would be upgraded first to 1.1 then 1.2, etc...)

If version=1.0 then upgrade1.1
If version=1.1 then upgrade1.2
if version=1.2 then upgrade1.3

The reason for the upgrade application is adding a table field not only required database changes, but functionality to fill that field with data. This functionality could not always be written as a SQL commands. In many cases it required more than SQL functions. The database was a desktop database.

We did discuss changing the functionality to do a diff and create what was necessary, but we never did make this change.

If I remember correctly we also verified the table structures of the database was identical to the version specified.

[Erich Schulman]

If you want to give it a shot in Python, you can use ODBC and ADO, and the difflib might be helpful.

For difflib, see difflib -- Helpers for computing deltas.

For Python and Access, see http://www.ecp.cc/pyado.html and http://bytes.com/forum/thread637384.html for some information and sample code.

Also see

http://adodbapi.sourceforge.net/

http://ceodbc.sourceforge.net/

http://pdo.neurokode.com/index.php?page=About

http://www.mayukhbose.com/python/ado/index.php

[Mark Strickland]

SQLmaestro

Here is a link to something similar to WinSQL. It is free (if you can use an ODBC connection). It is a pretty "full service" SQL tool. They also have "non-free" versions for various DB's using "native" connections.

http://www.sqlmaestro.com/

[Christopher Carroll]

Knuth

Do you have SQLTools? If yes, I can post code which does much of what you want. If not, I can always send you the executables. I do not run a 'diff' on data, relying instead on manually recording which records I update during an upgrade or similar process.

The tools I have do the following (all use ODBC):
- create INSERT SQL statements for data in a table based on one or more record keys, a WHERE clause, or for all records
- create CREATE TABLE SQL statements for one or more tables in a database
- create an Access database
- dump table structure and related meta-data to a text file for one or more tables
- a utility which I include with systems delivered to clients which reads text files containing SQL statements and runs these statements. I use this to update their databases, both structurally and with data. I can send these files to the client, and they can run the update program themselves, or I can do it myself if necessary.

Any ALTER TABLE or UPDATE SQL statements are manually written for this update program, but I use the above utilities for the INSERT and CREATE TABLE statements.

The program which generates the CREATE TABLE statements needs to be modified to produce CREATE INDEX statements as well, though it does build the primary key.

I will be away for a few days, however.

Chris

[Knuth Konrad]

Thanks for all the suggestions so far.

Michael,
while WinSQL indeed looks like a nice DBA tool, the application I need such a tool for, is a freeware tool that I share with others. Therefore $250 is not in my price range this time.

Chris,
if I'm not mistaken, the delta approach doesn't work for me. I don't have a destination database to compare against and create the delta from. The user has added records to their DB, therefore it doesn't match the one I have here.

Brian,
that's pretty much what I'm trying to do. Create a bunch of CREATE/ALTER statements, along with INSERT INTO/UPDATE for records. The updater will just execute the statements create by the tool I'm looking for here right now.

Erich,
thanks, but no. I'll stay with BASIC.

Mark,
thanks, I'll give it a try. That might be it.

Christopher,
yes, I own SQLTools. But I vaguely seem to remember that SQLTools has a restriction in regards to distribution and freeware. Need to look that up again. But I'll look at the other options first. I might come back to you, if I'm allowed to.

Again, thank you for your suggestions.

[Michael Mattias]

Using either ODBC or ADO, there are functions and/or properties/methods to generate a list of columns for a table along with their characteristics such as data type and size. (ODBC: SqlCOlumns).

There are also functions to list all indices against a table. (ODBC: SqlStatistics)


Given the lists, you can compare using Win32: Find Differences in WIN32API.INC and other text files April 21, 2000. (Ignore the title... it compares two string arrays providing a list of "in A() but not in B(); in B() but not in A(); and "in both A() and B())

[Graham McPhee]

Database differences

Certainly for SQL server I would recommend either DBGhost or RedGate SQL Compare, both of which can be used to compare two databases and generate script/s to turn one into the other for both data and schema.

I've never needed to do that for Access databases so I'm not aware of any available tools for that database format.

[Chris Holbrook]

...the delta approach doesn't work for me. I don't have a destination database to compare against and create the delta from. The user has added records to their DB, therefore it doesn't match the one I have here.

So in "diffback world", your DB is "the base file" and the user's is the one to backup, the delta is the differences between them. They would both have to be local or on network mapped drives for the application to work. Diffback.exe reads them both into memory, which will have performance implications, both in relation to their size and the speed of the media/network.

[Knuth Konrad]

Chris,

if I'm not mistaken, I still think the Diff approach wouldn't work. The situation is as follows:

- My application is delivered with a database that holds "base data" (think: "inventory of available items"). This data is never changed by the user. It also comes with an empty table, which will be filled by the user (think: "sales he made and saved in the DB"). This data is never supplied to me.

- I now need to supply new "inventory items", perhaps along with additional informational fields in the inventory table structure and or completely new tables (and data) because of added, new functionality in the application.

Hmm, but now that I think of it ... the solution might be easier than I thought the whole time. The way I had in mind allt he time was: DB at the user's side needs to be updated with new data. But I do know what's in his DB for the part that I provided him with initially. And I do know which static data needs to be updated. Only thing not known to me: the data the user gathered meanwhile. But I do know the structure of those tables.

So, instead of updating the existing user's DB with the new static data, I could provided the complete static data and update *that* one with the contents of the user's DB.

Haha, sometimes you just need to explain your problem to someone in order to come up with a solution yourself.

Thanks for listening, guys!

[Chris Holbrook]

Thanks for listening, guys!

You're welcome, the usual fee will be waived in your case.

[Michael Mattias]

Something you can do in this case if it becomes necessary...

When you ALTER TABLE by adding columns, only "select *" or insert into ... (select *) will be affected by the addition of columns.

If you've coded everthing with column names ...

select col1, col2.... 
or 
INSERT INTO table (col, col...) VALUES (value, value)

.. existing code will work. (assuming you can live with 'null' for the new fields on select, or 'null or default' on insert)

But if you have any "*" entries which will fail, you can set up a VIEW to match the old table reference ... and refer to that in statements which would otherwise fail because the new "*" is not the same as the old "*".

There are some caveats, and if the table names are hard-coded it probably would be just as simple to change the entire affected statement(s), but it *is* an option.

MCM

[Knuth Konrad]

Good suggestion/reminder, Michael.

Although unnecessary for me, as I stopped coding "SELECT * " a long time ago, mostly because of performance reasons, a reminder like yours might be interesting to read for others.

And no, the table names are not hard coded either.

And I strongly recommend following that advice, although it sometimes really is an annoying nightmare to code. But it pays back. I promise!

[Werner Bleckwendt]

I am using DataPro http://www.vive.net/. I don't remember how much the lizence was but I believe ~$90. On their website was also an older trial version.

rgds
Werner

[Knuth Konrad]

Thanks, Werner.

I meanwhile implemented the approach I mentioned in my last posting (copy existing user data over to new supplied DB with updated basic data).

Works good so far.