[PATCH] MySQL 3.23/4.0/4.1 compatibility
Reported by Sebastian Isaac Velasco | December 2nd, 2008 @ 01:45 PM
The MySQL_adapter functions use the MySQL's information_schema. MySQL 5 and later includes an information_schema database, but not the previous versions 3.23/4.0/4.1. So if you have an old versions of MySQL the datamapper migration doesn't work. I don't know if you prefer not be compatible with older version, but in case you want increase the available databases for datamapper, I wrote a few changes that will let be compatible. I changed the information_schema's selects by the old SHOW command. Thanks a lot for your great work !!!!
Sebastian
Changes:
gem: dm-core-0.9.7 file: dm-core-0.9.7/lib/dm-core/adapters/mysql_adapter.rb
Function: storage_exists?(storage_name) Replace:
SELECT COUNT(*)
FROM `information_schema`.`tables`
WHERE `table_type` = 'BASE TABLE'
AND `table_schema` = ?
AND `table_name` = ?
By:
SHOW TABLES FROM db_name LIKE "storage_name"
Code:
query('SHOW TABLES FROM `'+db_name+'` LIKE "'+storage_name+'"').to_s == storage_name
Function: field_exists?(storage_name, field_name) Replace:
SELECT COUNT(*)
FROM `information_schema`.`columns`
WHERE `table_schema` = ?
AND `table_name` = ?
AND `column_name` = ?
By:
SHOW COLUMNS FROM `storage_name` FROM `db_name` LIKE "field_name"
Code:
if query('SHOW TABLES FROM `'+db_name+'` LIKE "'+storage_name+'"').to_s == storage_name
result = query('SHOW COLUMNS FROM `'+storage_name+'` FROM `'+db_name+'` LIKE "'+field_name+'"').first
result.respond_to?('field') && result.field.to_s == field_name
else
false
end
Function: show_variable(name) Replace:
SHOW VARIABLES WHERE `variable_name` = ?
By:
SHOW VARIABLES LIKE ?
Code:
query('SHOW VARIABLES LIKE ?', name).first.value rescue nil
uname -a
Linux linux 2.4.31 [#21](/projects/20609/tickets/21 "Ticket #21") Sun Jun 5 19:19:51 PDT 2005 i686 unknown unknown GNU/Linux
Slackware Linux
ruby -v
ruby 1.8.7 (2008-08-11 patchlevel 72) [i686-linux]
gem -v
1.3.1
merb --version
merb 1.0.1
SHOW VARIABLES LIKE 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 4.1.14 | +---------------+--------+
Comments and changes to this ticket
-
Dan Kubb (dkubb) December 2nd, 2008 @ 03:12 PM
- Tag changed from “adapter, compatibility, dm-core, migrations, mysql” to “adapter, compatibility, dm-core, migrations, mysql, patch”
- State changed from “new” to “open”
- Title changed from “MySQL 3.23/4.0/4.1 compatibility” to “[PATCH] MySQL 3.23/4.0/4.1 compatibility”
I have no problem with these fixes, provided they also work on MySQL 5.x and 6.x. Sebastian, have you tested these commands on newer versions of MySQL too?
-
Dan Kubb (dkubb) December 2nd, 2008 @ 03:25 PM
Sebastian, would you also mind making a few minor changes to the patch and resubmitting?
Ideally the patch should have:
- the commented-out code removed
- the same style of syntax for the SQL queries, using EOS and compress_lines, and putting the SHOW TABLES/COLUMNS line on a separate line from the conditions clause. We like to construct and format the SQL in a consistent manner.
- bind values for the conditions to ensure they are always properly quoted by the DO driver rather that in the adapter.
- the field_exists? method use storage_exists? instead of cut/pasting the same query code.
I'm not sure if the to_s is necessary within the code. If not, would you mind removing it (we try to be as brief as possible within the code base).
Also the "rescue nil" in show_variable is something we're trying to fix anytime we see it. it would be better for it to assign the first result to a variable, and only if that variable is defined, call value on it. I'd probably do the same thing within field_exists? too and not use respond_to, because if the fields value was not provided we'd want to throw an exception.. the current code would silently return false if the SHOW COLUMNS syntax ever changed.
(In case you're wondering why I didn't just do it myself, I figure it's always better to show people our coding standards and style when submitting patches so that in the future they can work on the code in the repo using the same style)
-
Sebastian Isaac Velasco December 2nd, 2008 @ 04:11 PM
Thanks Dan for your response. I'll change the code to follows your coding standards. Also I'll test it on MySQL 6.
Sebastian
-
Sebastian Isaac Velasco December 3rd, 2008 @ 03:01 PM
- Assigned user set to “Dan Kubb (dkubb)”
- State cleared.
I made the changes so the code follows the standards and style. I tested it with MySQL 4 and 6 (6.0.7-alpha). Still, there is one thing that is a bit ugly:
SHOW TABLES FROM `#{db_name}` LIKE ?I can't include "db_name" like a variable '?', just because is a database name. If you know a better way, please tell me. There are functions call quote_table_name(table_name) & quote_column_name(column_name). I'm not sure if would be best create some thing like quote_database_name(database_name) and use it. Using the functions would be more secure, i guess.
-
Dan Kubb (dkubb) December 3rd, 2008 @ 04:08 PM
- State set to “unconfirmed”
Sebastian,
Since DM and do_mysql work together by connecting to a specific database, you can always use the syntax:
SHOW TABLES LIKE ?I would actually like to avoid using db_name whenever possible.
Given that these changes are really small, I'll make them to the patch and apply it against dkubb/dm-core.
-
Dan Kubb (dkubb) December 3rd, 2008 @ 06:02 PM
- State changed from “unconfirmed” to “resolved”
This is now resolved in dkubb/dm-core with the following commit:
-
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create new ticket
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
Dan Kubb (dkubb)
Sebastian Isaac Velasco