MySQL 5.0 Schema Provider for CodeSmith 3.0

I've created a custom schema provider for MySQL 5.0. The provider takes advantage of the latest MySQL features, including INFORMATION_SCHEMA tables, stored procedures, and so forth. Read more about the release and download the code here.

Hope this helps someone!
David

Post Edited (ChristianASP.NET) : 11/28/2005 6:06:04 AM GMT

Unless you recompile the source code, it appears you'll need CodeSmith version 3.1.4 or later.  Also, the connection string to use needs to be in the following format.

Server=localhost;Port=3306;Database=databaseName;Uid=userName;Pwd=userPassword

I'll be adding support for identity/autoincrementing columns to the GetExtendedProperties() soon.

Would you like to take ownership of the file gallery entry for your schema provider and post updates there so that your schema provider will be easier to find?

http://community.codesmithtools.com/files/9/schemaproviders/entry8615.aspx

New version of MySQL schema provider now supports GetExtendedProperties() for CS_IsIdentity (MySQL auto_increment columns).

Great stuff.  Thanks for doing it.

I did discover one problem: The PrimaryKeys property is not getting loaded correctly.  This is because in GetTableKeys you are only returning the foreign keys that exist on the given table, when in fact you also have to return the foreign keys that point to the given table. 
For example, let's say you have something like this:

TABLE: Contacts
COLUMNS: ContactID, Name, Address, Email
TABLE: Phones
COLUMNS: PhoneID, ContactID, Type, Number

where Phones.ContactID references Contacts.ContactID.  Right now, your GetTableKeys(Phones) is returning a single row describing the relationship.  That's correct.  However, GetTableKeys(Contacts) should be returning the exact same row to describe the same relationship.

In my own version, I've taken what you did for GetTableKeys and encapsulated it into a private helper function (GetMyTableKeys).  Then I created another private helper function (GetOthersTableKeys) that does the same, but looks for foreign keys pointing back at my table, and used that to add to your list.

Knowing all this, I'm sure you can whip together your own version of this, but if you'd like my code I'll be happy to post it as well.

Thanks again for doing all the heavy lifting.

Ah... thank you for letting me know about the PrimaryKeys collection.  I understand now.  I'll get that added.  However, if you want to send me your changes, that would be fantastic

Thanks!

I've uploaded it to the files section of this website, but it may not show up right away as I'm told a moderator must first intercede.  Also, I think I accidentally uploaded it to AddIns instead of Providers.  Oops.

Any update on the new provider version? I noticed that the old one w/o the PrimaryKeys was still up there...

I received the updated GetTableKeys code from Dug, and will incorporate his changes.  I will try to post an update tonight or tomorrow.

Latest version is now posted.  Changes include:

• Updated GetTableKeys() to provide not only the foreign keys for the table but also the foreign keys of other tables that point to the given table. Code update graciously provided by Dug Steen.  Thanks!
• Added .NET 2.0 version for CodeSmith 3.2.5 or higher
  
• .NET 1.1 version requires CodeSmith 3.1.6

Keep in mind that the version requirements are for the assemblies that ship with the current download.  If you are using a previous 3.xx version of CodeSmith, you can simply recompile from the source code and it should work.

David,

It would be helpful to post the update information on the file description as well so that users can see it has been updated.

Also, should I go ahead and delete the GetTableKeys patch that is posted in the file gallery since its included in yours now?

Good idea.  Why didn't I think of that?    Let me know if you have any other suggestions.

Yes, you can delete the patch in the file gallery. 

Thanks!
David

Thanks David!

I had a problem with some of my templates that used indexes.  When I inspected the code, I found that you were quoting the tablenames inside the GetTableIndexes function, which was throwing an error.  I found that not quoting these names actually clears up the error.

//string sql = @"SHOW INDEX FROM " + Quote(table.FullName);
string sql = @"SHOW INDEX FROM " + table.FullName;

Excellent job overall!  Thanks for the provider!

 

...Aaron 

 

I am using CodeSmith v4.0 and have .NET Connector 1.0.9 and 5.0.3. I've tried using ChristianASPNet.MySQLSchemaProvider.dll and V2, but I just can't make it show up in the list of providers...

Can you help?

 

P.S. Can it be used with MySQL 4.4+?