You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Paul Linehan <li...@tcd.ie> on 2014/01/21 15:09:39 UTC
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 '0000-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...
--
linehanp@tcd.ie
Mob: 00 353 86 864 5772
Re: Creating tables - migrating from MySQL.
Posted by Rick Hillegas <ri...@oracle.com>.
On 1/21/14 11:39 AM, Paul Linehan wrote:
> 2014/1/21 Rick Hillegas<ri...@oracle.com>:
>
>
> 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
>
Re: Creating tables - migrating from MySQL.
Posted by Paul Linehan <li...@tcd.ie>.
2014/1/21 Rick Hillegas <ri...@oracle.com>:
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
--
linehanp@tcd.ie
Mob: 00 353 86 864 5772
Re: Creating tables - migrating from MySQL.
Posted by Rick Hillegas <ri...@oracle.com>.
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.
Posted by Paul Linehan <li...@tcd.ie>.
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
--
linehanp@tcd.ie
Mob: 00 353 86 864 5772
Re: Creating tables - migrating from MySQL.
Posted by Bryan Pendleton <bp...@gmail.com>.
> 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