Creating tables - migrating from MySQL.
Hi all, I'm trying to migrate a system (72 tables) from MySQL to Apache. I've looked at the docco here http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj27.html#CREATE+TABLE+statement, but it isn't readily apparent to me how to create INDEXES within the CREATE TABLE statement. I'd rather not have to manually perform this procedure - a sample might help. CREATE TABLE analysis ( analysis_id smallint(5) unsigned NOT NULL AUTO_INCREMENT, created datetime NOT NULL DEFAULT '-00-00 00:00:00', logic_name varchar(128) NOT NULL, db varchar(120) DEFAULT NULL, PRIMARY KEY (analysis_id), UNIQUE KEY logic_name_idx (logic_name), KEY db_idx (db) ); Tried this through ij and it failed. What do I have to substitute for "PRIMARY KEY", "UNIQUE KEY" and "KEY" in the CREATE TABLE statement (I'll either use sed/awk/vi/ or Java eventually) - or do I have to create the PRIMARY KEY and other KEYs outside of the main CREATE TABLE statement? I did try doing this automatically using SQuirrel SQL, but there are (MySQL!!!) some fields called "end" I'll try and quote these with double quotes for the time being. Any help, ideas, references, URLs & appreciated. TIA and rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Re: Creating tables - migrating from MySQL.
I'm trying to migrate a system (72 tables) from MySQL to Apache. Have you considered using ddlutils: http://db.apache.org/ddlutils/ thanks, bryan
Re: Creating tables - migrating from MySQL.
Hi Bryan, >> I'm trying to migrate a system (72 tables) from MySQL to Apache. > Have you considered using ddlutils: http://db.apache.org/ddlutils/ Yeah, looks interesting for when I come to settle down and write this sort of thing, but what I'm trying to do for the moment is just get it up and running on an ad-hoc basis. I do know that this isn't ideal - but for the moment (I have to demo something on Thursday morning!) it'll suffice. Also found http://flywaydb.org/ and http://migrate4j.sourceforge.net/ and will be looking at these too. Thanks for your interest. Paul... > bryan -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Re: Creating tables - migrating from MySQL.
On 1/21/14 7:07 AM, Paul Linehan wrote: Hi Bryan, I'm trying to migrate a system (72 tables) from MySQL to Apache. Have you considered using ddlutils: http://db.apache.org/ddlutils/ Yeah, looks interesting for when I come to settle down and write this sort of thing, but what I'm trying to do for the moment is just get it up and running on an ad-hoc basis. I do know that this isn't ideal - but for the moment (I have to demo something on Thursday morning!) it'll suffice. Also found http://flywaydb.org/ and http://migrate4j.sourceforge.net/ and will be looking at these too. Thanks for your interest. Paul... bryan Hi Paul, You might want to take a look at the optional foreignViews tool, which can be useful for data migration projects like yours: http://db.apache.org/derby/docs/10.10/tools/rtoolsoptforeignviews.html. You can create and populate a Derby table from an external table by following the instructions for loading the foreignViews tool and then issuing statements like the following: -- create the local Derby table create table S1.T1 as select * from XYZ_S1.T1 with no data; insert into S1.T1 select * from XYZ_S1.T1; You'll still need to add constraints, indexes, triggers, functions, procedures, etc.. But at least you will have a snapshot of the data and much of the application should run. If you have suggestions about what should go into a data migration script produced by the foreignViews tool, your input is welcome on this issue: https://issues.apache.org/jira/browse/DERBY-6219. Thanks, -Rick
Re: Creating tables - migrating from MySQL.
2014/1/21 Rick Hillegas : Hi Rick, and thanks for your input. > You might want to take a look at the optional foreignViews tool, which can > be useful for data migration projects like yours: > http://db.apache.org/derby/docs/10.10/tools/rtoolsoptforeignviews.html. You > can create and populate a Derby table from an external table by following > the instructions for loading the foreignViews tool and then issuing > statements like the following: > > -- create the local Derby table > create table S1.T1 as select * from XYZ_S1.T1 with no data; > insert into S1.T1 select * from XYZ_S1.T1; I had a quick look at this - launch it from within ij>! I didn't construct my URL properly and got a suitable error message. Just wondering - how will this cope with ENUM types? Tinytext? Mediumtext? In the meantime, I've about 50% of a tool which will get me to where I want - very crude, but it should cover the basics - I'll have a couple of weeks after Thursday and might be able to thrash out something usable. I will experiment with what you've showed me - again after Thursday. I'm Setting Mediumtext and Longtext to BLOB - hope this is OK? Thanks again and rgs, Paul... > -Rick -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Re: Creating tables - migrating from MySQL.
On 1/21/14 11:39 AM, Paul Linehan wrote: 2014/1/21 Rick Hillegas: Hi Rick, and thanks for your input. You might want to take a look at the optional foreignViews tool, which can be useful for data migration projects like yours: http://db.apache.org/derby/docs/10.10/tools/rtoolsoptforeignviews.html. You can create and populate a Derby table from an external table by following the instructions for loading the foreignViews tool and then issuing statements like the following: -- create the local Derby table create table S1.T1 as select * from XYZ_S1.T1 with no data; insert into S1.T1 select * from XYZ_S1.T1; I had a quick look at this - launch it from within ij>! I didn't construct my URL properly and got a suitable error message. Just wondering - how will this cope with ENUM types? Tinytext? Mediumtext? Not sure. The foreign data type is converted to a Derby datatype by org.apache.derby.impl.tools.optional.ForeignDBViews.mapType(). It's all based on column type information which is returned by DatabaseMetaData.getColumns(). But I don't know what MySQL's DatabaseMetaData.getColumns() returns for enum, tinytext, and mediumtext types. If something bad happens, let me know. I may be able to improve the tool. In the meantime, I've about 50% of a tool which will get me to where I want - very crude, but it should cover the basics - I'll have a couple of weeks after Thursday and might be able to thrash out something usable. I will experiment with what you've showed me - again after Thursday. I'm Setting Mediumtext and Longtext to BLOB - hope this is OK? CLOB might be better but BLOB could work. Note that you won't be able to use CLOBs or BLOBs in ordered contexts (like GROUP BY and ORDER BY clauses and in comparisons involving the <, >, and = operators). Hope this helps, -Rick Thanks again and rgs, Paul... -Rick