Welcome Guest, Not a member yet? Register   Sign In
Convert Postgresql to MySql?
#1

[eluser]zimco[/eluser]
I need to convert a database schema originally written for postgresql to mysql, anybody ever done anything like this, and how?

I do not need to convert any data, just the database structure. Some things are easily translated, but i do not understand what something like author_id int references thing means when "thing" is another table.

I have already given www.lightbox.ca/pg2mysql.php a try without success.

Any suggestions? Can the following even be translated into a working mysql database structure?

Code:
$def with (tables, sequences, multisite=False)

BEGIN;

-- changelog:
-- 10: added active and bot columns to account and created meta table to track the schema version.

create table meta (
    version int
);
insert into meta (version) values (10);

$if multisite:
    create table site (
        id serial primary key,
        name text UNIQUE,
        created timestamp default(current_timestamp at time zone 'utc')
    );

create table thing (
    id serial primary key,
    $if multisite:
        site_id int references site,
    key text,
    type int references thing,  
    latest_revision int default 1,
    created timestamp default(current_timestamp at time zone 'utc'),
    last_modified timestamp default(current_timestamp at time zone 'utc')
);
$for name in ['key', 'type', 'latest_revision', 'last_modified', 'created']:
    create index thing_${name}_idx ON thing($name);

$if multisite:
    create index thing_site_id_idx ON thing(site_id);

create table transaction (
    id serial primary key,
    action varchar(256),
    author_id int references thing,
    ip inet,
    comment text,
    bot boolean default 'f', -- true if the change is made by a bot
    created timestamp default (current_timestamp at time zone 'utc')    
);

$for name in ['author_id', 'ip', 'created']:
    create index transaction_${name}_idx ON transaction($name);

create table version (
    id serial primary key,
    thing_id int references thing,
    revision int,
    transaction_id int references transaction,
    UNIQUE (thing_id, revision)
);

create table property (
    id serial primary key,
    type int references thing,
    name text,
    UNIQUE (type, name)
);

CREATE FUNCTION get_property_name(integer, integer)
RETURNS text AS
'select property.name FROM property, thing WHERE thing.type = property.type AND thing.id=$$1 AND property.id=$$2;'
LANGUAGE SQL;

create table account (
    $if multisite:
        site_id int references site,
    thing_id int references thing,
    email text,
    password text,
    active boolean default 't',
    bot  boolean default 'f',
    verified boolean default 'f',
    
    $if multisite:
        UNIQUE(site_id, email)
    $else:
        UNIQUE(email)
);

create index account_thing_id_idx ON account(thing_id);
create index account_thing_email_idx ON account(active);
create index account_thing_active_idx ON account(active);
create index account_thing_bot_idx ON account(bot);

create table data (
    thing_id int references thing,
    revision int,
    data text
);
create unique index data_thing_id_revision_idx ON data(thing_id, revision);

$ sqltypes = dict(int="int", float="float", boolean="boolean", str="varchar(2048)", datetime="timestamp", ref="int references thing")

$for table, datatype in tables:
    create table $table (
        thing_id int references thing,
        key_id int references property,
        value $sqltypes[datatype],
        ordering int default NULL
    );
    create index ${table}_idx ON ${table}(key_id, value);
    create index ${table}_thing_id_idx ON ${table}(thing_id);
    
-- sequences --
$for seq in sequences:
    CREATE SEQUENCE $seq;

COMMIT;
#2

[eluser]Sinclair[/eluser]
Hi,

Why not to use MySQL WorkBench to draw the model? You have the CREATE's, should be quick to draw the tables in WorkBench.


Best Regards
#3

[eluser]kea13[/eluser]
Hi zimco,

I haven't done any PostgreSQL to something else migration, but maybe I can help a little:

you have a table called "thing" which has a primary key of type serial (MySQL's notion of this is AFAIK 'unique') named "id".
In table "transaction" for example you find "author_id int references thing" which tells you that "author_id" is a foreign key to table "thing", referencing "thing"'s column "id".

Any clearer now?

Regards,
Roman


20100122 13:56: weeded out typo Smile
#4

[eluser]zimco[/eluser]
@kea13, yes that is much clearer, thank-you!




Theme © iAndrew 2016 - Forum software by © MyBB