geting database metadata for mssql and mysql

March 6, 2007 2:56 PM
Related Categories: Web Dev

For the database I am updating it must run on either mssql or mysql. That makes things a bit more difficult since my upgrade scripts must work for either db.

Today I am doing more research on how to get the metadata about the existing database. Basically get the tables, columns, and indexes for the database, compare to what I need for the upgrade and do the create or alter table commands needed.

For MS SQL you run different sql commands to get that metadata.

something like:

SELECT *
FROM information_schema.tables

You can do that for tables and columns to get what you need. But you can't do that for indexes. Why? No idea. I found this little gem, however.

SELECT "table" = object_name(i.id), i.name,
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
"column" = col_name(i.id, ik.colid), ik.keyno
FROM sysindexes i
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(i.id, name, 'IsHypothetical') = 0
AND indexproperty(i.id, name, 'IsStatistics') = 0
AND indexproperty(i.id, name, 'IsAutoStatistics') = 0
AND objectproperty(i.id, 'IsMsShipped') = 0
ORDER BY "table", "isclustered" DESC, i.name, ik.keyno

I just tested it out and even on my SQL 2000 machine it works exactly as I need.

For MySQL you need to run things a bit different. Well, no you can use information_schema but from what I read only with ver 5 or above. Since I have users on 3 and 4, I need to do something more MySQL specific.

show columns
from ecommerceTBL

show index
from ecommerceTBL

So I figure I write up an XML document describing exactly how the DB should be in final state, then loop through it to make sure all tables exist, all columns exist and all indexes exist. If they don't run the appropriate create or alter command. Then in the future when I need to upgrade a site I can just run this script to build the database to the latest and greatest.


Like this entry? Subscribe to my blog.

  • Comments (8)
  • 986 views

Comments (moderation on)

[Add Comment]
mysql can beat up yoursql.
# Posted By Book Kitten | 3/6/07 3:11 PM
Just a quick update. I used the MS SQL code above to very easily/quickly get the full DB schema (tables, columns and indexes with defaults) and then save as xml. So slick. I know you can use CFMX 7 to do it as well, but I needed 6.1 compatibility.
# Posted By Joshua | 3/8/07 8:07 PM
Have you had a look at DataMgr? Don't know if it does exactly what you want, but what you are describing sounds very much like what it does out of the box.

Just a thought . . .
# Posted By Peter Bell | 4/6/07 12:28 AM
Well dang, that would have been handy. I don't see that it supports indexes (checking and creation). But that is easy enough to add. I went ahead and built my own, but not as slick and clean. Turns out that migrating over mssql to mysql default values, especial for date / datetime can be a pain!

On the flip side my code now takes an xml doc (which it also makes) and creates any tbls, columns and indexes that don't already exist. Then runs maintenance code on them that may be needed for an upgrade.
# Posted By Joshua Cyr | 4/6/07 8:44 AM
Joshua

One other option - did you ever look at using JDBC metadata? The JDBC API has some useful features. If you've never looked at it before this article in CFDJ should help:

http://coldfusion.sys-con.com/read/45569.htm
# Posted By Kola | 4/8/07 4:01 AM
I did take a look at that actually. I opted against it because it wasn't officially supported by Adobe, and it required database name and other information. The odds of the installer having that is pretty good, but not 100%. If I had no other options I would have used it but I was trying to limit the steps needed. The code I ended up using was just two querries... well 3 (for ms sql) and was pretty easy to do. The real bummer about the solution I came up with was that it was different for mssql than mysql. Both in getting the data and in creating tables and columns.

The solution I came up with works for now, but I may have to revisit it again with dataMGR or similar tools. Adding new databases to support will be a pain. Then again that could be tricky even with datamgr since some columns need to change based on the DB. datetime for mssql and datetime for mysql are not the similar enough for me for example.
# Posted By Joshua | 4/8/07 10:06 AM
Joshua,

I just happened onto this article. I like your approach. Certainly your thinking is similar to what mine was when I started working on that feature of DataMgr.

What problem are you having with datetime between MSSQL and MySQL?

In DataMgr, I define all of the data types using the data types from cfqueryparam and then map those to database data types (allows me to use cfqueryparam for all the CRUD work as well).

I like the idea of tracking indexes as well. I can certainly see where that would be helpful.
# Posted By Steve Bryant | 4/14/07 8:03 AM
Well, first thing I did is run the script to gather the information from my MSSQL db, which is the master DB. That built the xml doc. When have another script for building the DB from it. I had trouble with things like datetime for mssql and having a default value of NOW(). (for fields like DateCreated and the sort). With MySQL you can't have a deafault value that is a function for datetime, the default has to be a default static value. Sor you need to make them TimeStamp. However you can't have two fields in the same table that ar TimeStamp (I am prety sure). So if you have something where you have datecreated and datemodified, then I had to detect the first one and make into timestamp and the next one datetime.

In addition I have issue when a table already exists and my script detects a field not existing. Idealy the field would be Null, but not have a default value. However the db's dont' like to have an alter table script to make a column that allows NNull without also a default value. Or was it Not Null?

Honestly I am away from my main computer on vacation and my memory is a bit fuzzy. :-)

The best way to do this is to alter my master DB to clean up some inconsistancies so that the issue doesn't come up.

Oh, and I have reports that with mysql 5 my scripts have issues with default values and nulls that they don't have with mysql 4, though I haven't debugged it yet.

The index detection and creation is pretty easy and works well. However it doesn't do anything more than a very simple index.
# Posted By Joshua | 4/14/07 9:03 AM
[Add Comment]

Sponsors


Savvy Content Manager