WilsonORMapper works well with MySQL

I spent a couple of hours yesterday testing my WilsonORMapper with MySQL (note the My, not MS). I designed my O/R mapper to work with any ANSI compliant database, so I had high hopes. Note that I do of course have a few optimizations for MS SQL, Access, and Oracle, so I don't mean everything is or should be generic. I'm very experienced with Oracle, although its been a few years back, but I had never touched MySQL until now. I started by downloading MySQL v4.0.17, which was the recommended version, MySQL Control Center, and MySQL Odbc Driver v3.51. I installed everything, then I figured out how to start the server and create a database and a table pretty easily. The next thing was configuring Odbc and figuring out my connection string, and do a few small regular ADO.NET tests. So far so good -- by the way, MySQL seems surprisingly comparable to MS SQL in ease of use.

So next I tried my first test with my WilsonORMapper, a simple GetObject, and it failed. My debug version spits out all the SQL so I copied it into the query tool to test it directly since it looked good. It turns out that MySQL is not ANSI compliant by default -- it uses ` instead of " for its table and field delimiters. By the way, neither is MS SQL, which uses [ and ] by default, so my Commands class allows this to be easily overridden. I tried to get the Option clause in my connection string to turn on ANSI support, but finally gave up and just started MySQL in ANSI mode (mysqld --ansi). That was easy enough so now all my GetXXX methods work great, just as expected when you support generic ANSI. Note that I will still need to write a custom Commands provider to get paging to work, but that will be a simple method override.

Next, I tried to do an Insert and it failed at first, but this time the error message in the debugger helped right away. I use parameterized dynamic sql for all my inserts, updates, and deletes, which stops sql injection attacks and allows the database to cache the execution plan. The Access OleDb driver supports named parameters, not just ?, although you still have to assign your values in the same order as the parameters, so that's what I have coded. Well the MySQL Odbc driver requires ? for parameters -- but guess what, I have an optional parameter mapping for all my fields since I support stored procedures. A simple change to the mapping xml file and now all my inserts, updates, and deletes work with MySQL too. Note that auto-generated identities are not supported by ANSI SQL, so I have a hack using MAX in place that isn't scalable, but a custom Commands provider can easily fix this with again a simple method override.

So I would like to say that my WilsonORMapper supports MySQL, but I really should probably write that custom provider to make it perfect. That brings me to a few questions though, which maybe some of my readers can help me better answer. There are multiple MySQL .NET native drivers out there, as well as OleDb and Odbc drivers -- what should I expect of my users? Should I force a specific driver on them or should I just write a custom Commands provider and expect them to do something more to take advantage of it? And if the latter, should they have to recompile my source with their few driver specifics, or should I somehow make it a configuration option? The same questions will apply to PostgreSQL also, which I have someone asking me about already, so whatever I decide will apply to other databases also.

By the way, to me this experiment validates a couple of my design decisions that I blogged about previously. First, generic ANSI SQL is not a silly pipe dream -- it really is possible in most simple cases, although certainly you should provide provider specific optimizations too. Yes, I know I'm just talking about the simple CRUD operations, but apparently I've guessed correctly that this really is all many people care about, judging by the responses I have received. Finally, having my mappings in an external xml file was crucial here, since I needed to change my parameter names to ? for just MySQL, not all the other cases. This means that I did not have to recompile my code -- all I needed was a MySQL specific mapping file and everything worked perfectly. If O/R mappers are to support multiple database platforms, then how can we honestly ever expect the mappings to only ever be specified once in the code as attributes?

5 Comments

  • ::If O/R mappers are to support multiple

    ::database platforms, then how can we honestly

    ::ever expect the mappings to only ever be

    ::specified once in the code as attributes?



    Paul, this is ignorant AND arrogant AND wrong.



    It just works.



    In the EntityBroker we specify mapping information in code.



    But this is not database dependant.



    Basically, a string field is annotated as "string, variable length, max 50 chars". Whatever SQL is generated out of this is NOT part of the attribute at all.



    On top of this the mapping contains table name and field name - which, again, are NOT database specific - using "'" instead of "[" for mapping is not what the mapping file does - this is what the database adapter should do.



    We DO use attributes, and we DO have customers running one assembly with the attibutes against Oracle and MS SQL, and it just works - so, you CAN do it, unless you are totally idiotic with the data you put into the database.



    That said - I should really sit down half a day and get MySQL supported :-)



    What native interface library should one use for this?

  • As soon as you start to use joins you'll fail with ANSI-sql.



    If I would just use CRUD stuff, I could generate my sql with 60 lines of code, maybe less. However the various aspects of non-CRUD sql demand serious customization.



    For example this Sqlserver query (pseudocode)

    UPDATE Table

    Set field = value

    FROM othertable

    WHERE Othertable.field2 = table.field2



    No can do on oracle, you need a subquery construct for that.



    Delete FROM table FRom Table2 where Table1.foo = Table2.foo



    Same thing.



    These situations are the hard part.



    With the release of my Adapter template set yesterday, it's now possible to fetch an entity from oracle and save it into sqlserver, including data contained in the object, and vice versa, or develop 1 system and target oracle and sqlserver, depending on what the customer wants.



    This however ALWAYS database specific details, how else would a boolean field be stored in for example oracle? Varchar(1)? NUMBER (1)? You tell me.



    You do a good job developing your tool, but you do a bad job getting the O/R mapping message accros. You tell the people it's so simple and so easy, but it isn't. Not by far.

  • To be honest, it gets annoying.



    You try to sell a story that writing an O/R mapper is so easy, that supporting a truckload of databases is like flipping a switch, but your mapper is severily crippled and because of that can 'support' multiple databases without a lot of effort.



    This obviously raises questions like: "Why can paul wilson write a mapper that supports a lot of databases while others have a hard time supporting multiple databases?". The answer to that is: because if you just want simple selects and simple CRUD operations on a table, you don't need sophisticated code. However reality isn't served with simple crud and simple selects on a table.



    Still, you blog continuously about how easy it was/is to support all these databases. You don't tell the people that you needed custom code for Oracle's sequences for example, or how you would work with a REF CURSOR stored proc output on oracle vs the stored proc output in sqlserver.



    I appreciate your work but I'm really starting to get annoyed by the way you try to tell the world how easy it was to write your tool, which indirectly says that others apparently are doing something wrong. You even say it in your blog here:

    "Yes, I know I'm just talking about the simple CRUD operations, but apparently I've guessed correctly that this really is all many people care about, judging by the responses I have received. "

    How wrong can you possibly be.



    Writing a good, useful O/R mapper targeting a lot of databases is hard, not easy. Supporting a database type in FULL is very hard, it requires a lot of custom code per database, because every database type has very specific SQL syntaxis for a lot of constructs you run into with an SQL generation engine, or even with the mapping of types to .NET types, to name a few silly things.



    Telling your readers it's a walk in the park as you tend to do is misleading. Sure, you don't compete with 'the big guys' as you said, that however isn't what I read in this blog as it seems according to you that a large group of people is only looking for simple CRUD.

  • Have you ever tested with SQLite? I don't believe it has an odbc driver, but it does have an ado.net provider. SQLite is a great offline type database like access.



    thanks

    Paul

  • Another Paul W.? :)



    Sorry, but I've barely even heard of SQLite. The architecture should make it very easy to extend to other providers, even without OleDb or Odbc drivers, once you have the source code.

Comments have been disabled for this content.