Menu
▾
▴
Re: [Firebird-devel] Simplify MySQL migration: LAST_INSERT_ID()
|
From: Tony W. <ton...@mc...> - 2010-12-26 15:06:49
|
Roman,
I had to convert a MySQL database to Firebird last year and created the
awk script attached below to convert a MySQL SQL schema to the Firebird
dialect. You may find this useful, as it is intended to take most of the
grunt work out of the conversion.
It is invoked with "gawk -f convert.awk schema.sql" assuming that the
script is saved in "convert.awk" and you are working on "schema.sql".
I found two main problems with the conversion - "auto-increment" and
"enumerated types" of which the latter is the more difficult to resolve,
with several alternative strategies.
In the script, I dealt with auto-increment by creating a generator and
an "insert before trigger". I did not have to deal with retrieving the
last insert id. However, in the approach below, this should be easy
enough to deal with using the Firebird USER_SESSION namespace. If you
amend the Create Trigger part of the awk script to
print "CREATE TRIGGER AI_" tablename " FOR " tablename
print "ACTIVE BEFORE INSERT"
print "AS"
print "BEGIN"
print " IF (new." auto_col " is NULL) THEN"
print " BEGIN"
print " Select GEN_ID(" generator ",1) FROM RDB$Database Into
new." auto_col ";"
print " rdb$set_context('USER_SESSION', 'lastinsertid', new."
auto_col ");"
print " END"
print "END^"
then the last generated value will the saved in the user session. This
can then be retrieved by a stored procedure such as
Create Procedure GET_LAST_INSERT_ID
RETURNS
(lastInsertID Integer)
As
Begin
lastInsertID = rdb$get_context('USER_SESSION','lastinsertid');
SUSPEND;
END
This has the correct semantic as MySQL as, quoting the MySQL Manual
,"The value of mysql_insert_id() is affected only by statements issued
within the current client connection. It is not affected by statements
issued by other clients. "
Enumerated types are more of an issue. In this script, I replaced them
with a VarChar and a Check constraint. Alternatively, you could create a
lookup table.
Regards
Tony Whyman
MWA
# Convert MySQL Schema to Firebird
BEGIN {FS = ","; RS=";"; IGNORECASE=1;
gen = 0;
foreignkeys = 0;
ix = 0;
}
/insert[ \t\n]+into/ {
body = $0
gsub(/\r\n/," ",body);
gsub(/\n/," ",body);
gsub(/\r/," ",body);
gsub(/^[ \t]*/,"",body);
gsub(/`/,"",body)
gsub(/\\0/,"!",body)
gsub(/\\\\/,"!",body)
gsub(/\\r/,"!",body)
gsub(/\\n/,"!",body)
gsub(/\\'/,"''",body)
gsub(/'0000-00-00 00:00:00'/,"NULL",body)
gsub(/0x[A-F0-9]+/,"NULL",body)
insertclause = body
sub(/values.*/,"",insertclause)
count = split(body,parts,/\),/)
if (count == 1)
print body ";"
else
{
print parts[1] ");"
for (i = 2; i < count; i++)
print insertclause "values " parts[i] ");"
print insertclause "values " parts[count] ";"
}
print "Commit;"
print ""
}
/create[ \t\n].*view.*/ {
body = $0
gsub(/\r\n/," ",body);
gsub(/\n/," ",body);
gsub(/^[ \t]*/,"",body);
gsub(/straight_join/,"",body);
body = gensub(/UNIX_TIMESTAMP\(([A-Za-z0-9_\. ]+)\)/,"\\1","g",body)
viewname = body
c = match(viewname,/\(.*/)
if (c != 0 && c < match(viewname,/[ \t\)]as /))
{
#View has column list
sub(/\(.*/,"",viewname)
sub(/^[ \tA-Za-z0-9_]*\(/,"(",body)
}
else
{
sub(/[ \t]as .*/,"",viewname)
sub("^[ \tA-Za-z0-9_]*" viewname "[ \t]as"," as ",body)
}
sub(/[ \t]+$/,"",viewname)
count = split(viewname,parts,/[ \t]+/)
viewname = parts[count]
if (length(viewname) > 31)
viewname = substr(viewname,1,31)
print "RECREATE VIEW " viewname body ";"
print ""
}
/create[ \t\n]+index.*/ {
body = $0
gsub(/\r\n/," ",body);
gsub(/\n/," ",body);
gsub(/^[ \t]*/,"",body);
count = split(body,parts,/[ \t(]+/)
indexname = parts[3]
if (length(indexname) > 31)
indexname = "INDEX_" ++ix
tablename = parts[5]
if (length(tablename) > 31)
tablename = substr(tablename,1,31)
line = "CREATE INDEX " indexname " ON " tablename "("
for (i = 6; i <= count; i++)
line = line " " parts[i]
print line ";"
}
/create[ \t\n]+table.*/ { //Convert Table Definitions
linecount = 0;
delete lines;
gsub(/mediumtext/,"BLOB sub_type 0")
#Reverse Foreign Key Dependences
gsub(/ delete /,"__U__");
gsub(/ update /," Delete ");
gsub(/__U__/," Update ");
#other simple textual changes
gsub(/type = innodb/,"");
gsub(/datetime/,"TIMESTAMP");
gsub(/binary\([0-9]+)/,"& character set none");
gsub(/binary\(/,"CHAR(");
#Find Auto_increment and replace by trigger
body = $0;
auto_col = "";
gsub(/\r\n/," ",body);
gsub(/\n/," ",body);
split(body,parts,/\(/);
tablename=parts[1];
sub(/[ \t]*create[ \t]+table[ \t]+/,"",tablename);
if (length(tablename) > 31)
tablename = substr(tablename,1,31)
sub(/create[ \t]+table[ \t]+[A-Za-z0-9_]+[ \t]*\(/,"",body);
sub(/)[ \t]*$/,"",body);
cols = split(body,col_defs,",");
i = 1
while (i <= cols)
{
sub(/^[ \t]+/,"",col_defs[i]); #Remove leading space
if (match(col_defs[i],/^.*foreign.*/)) #Move Foreign Key Constraints
to end
{
tconstraints[++foreignkeys] = "Alter Table " tablename " Add "
col_defs[i]
while (!match(col_defs[i],/references/))
{
i++;
tconstraints[foreignkeys] = tconstraints[foreignkeys] ","
col_defs[i]
}
sub(/).*references/,"",col_defs[i]) #remove leading ) to avoid
confusion
while (!match(col_defs[i],/)/))
{
i++;
tconstraints[foreignkeys] = tconstraints[foreignkeys] ","
col_defs[i]
}
tconstraints[foreignkeys] = tconstraints[foreignkeys] ";"
}
else
if (match(col_defs[i],/.*auto_increment.*/)) #Replace Auto Increment
with Trigger
{
if (split(col_defs[i],words,/ /))
auto_col = words[1]
sub(/[ \t\n]+auto_increment/,"",col_defs[i])
lines[++linecount] = " " col_defs[i]
}
else
if (match(col_defs[i],/[ \t]+references[ \t]*/))
{
idx = match(col_defs[i],/[ \t]+references[ \t]*/)
if (split(col_defs[i],words,/ /))
tconstraints[++foreignkeys] = "Alter Table " tablename " Add
Foreign key (" words[1] ") " substr(col_defs[i],idx) ";"
lines[++linecount] = " " substr(col_defs[i],1,idx-1)
}
else
{
if (match(col_defs[i],/ENUM[ \t]*\(/)) #Enumerated Type
{
#recast enumerated types as VAR CHAR with Check Constraint
if (split(col_defs[i],words,/ /))
enum_col = words[1]
else
enum_col = "!Unknown!"
j = 1;
do
{
c = match(col_defs[i],/.*['.*']*.*).*/)
elts = split(col_defs[i],words,/'/);
if (elts == 3)
identifiers[j++] = words[2];
i++
} while (c == 0)
elts = split(col_defs[i-1],words,/)/);
if (elts)
constraint = words[elts]
else
constraint = ""
#Determine max id length
maxlen = 0
for (k = 1; k < j; k++)
if (length(identifiers[k]) > maxlen)
maxlen = length(identifiers[k])
line = " " enum_col " VARCHAR(" maxlen ") " constraint " CHECK("
if (match(constraint,/not[ \t]+null/) == 0)
line = line enum_col " is null or " enum_col " = '' or "
for (k = 1; k < j-1; k++)
line = line enum_col " = '" identifiers[k] "' or "
line = line enum_col " = '" identifiers[k] "')"
lines[++linecount] = line
continue
}
else
if (match(col_defs[i],/^[ \t]*check/)) #wrap check constraints in
extra parenthesises
{
sub(/^[ \t]*check/,"CHECK (",col_defs[i])
lines[++linecount] = " " col_defs[i] ")"
}
else
lines[++linecount] = " " col_defs[i]
}
i++
}
print ""
print "CREATE TABLE " tablename "("
for (i = 1; i < linecount; i++)
print lines[i] ","
print lines[linecount]
print ");"
print ""
if (auto_col != "")
{
generator = "G" gen++;
print "CREATE GENERATOR " generator ";
print "set term ^;"
print "CREATE TRIGGER AI_" tablename " FOR " tablename
print "ACTIVE BEFORE INSERT"
print "AS"
print "BEGIN"
print " IF (new." auto_col " is NULL) THEN"
print " Select GEN_ID(" generator ",1) FROM RDB$Database Into
new." auto_col ";"
print "END^"
print "set term ;^"
}
}
END {
print ""
for (i = 1; i <= foreignkeys; i++)
print tconstraints[i]
}
Roman Rokytskyy wrote:
> Hi,
>
> I am trying to port Joomla CMS to Firebird (just started, so no progress
> here), but I found that it uses one feature that MySQL has in regard to
> auto-increment columns.
>
> Excert from the docs:
>
> "When a new AUTO_INCREMENT value has been generated, you can also obtain
> it by executing a SELECT LAST_INSERT_ID() statement with mysql_query()
> and retrieving the value from the result set returned by the statement.
>
> For LAST_INSERT_ID(), the most recently generated ID is maintained in
> the server on a per-connection basis. It is not changed by another
> client. It is not even changed if you update another AUTO_INCREMENT
> column with a nonmagic value (that is, a value that is not NULL and not
> 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously
> from multiple clients is perfectly valid. Each client will receive the
> last inserted ID for the last statement that client executed."
>
> We do not have the AUTO_INCREMENT data type, but I was thinking that for
> the most use cases (80/20 principle), storing the last value used in the
> gen_id(..) function in the context would do the trick.
>
>
>
> Therefore, I suggest to add to the next possible releases feature that
> automatically stores value of the gen_id() function call (as well as
> NEXT VALUE FOR ... clause) into the SYSTEM context (or some new context
> to avoid conflicts).
>
> Comments, please!
>
> Roman
>
> ------------------------------------------------------------------------------
> Learn how Oracle Real Application Clusters (RAC) One Node allows customers
> to consolidate database storage, standardize their database environment, and,
> should the need arise, upgrade to a full multi-node Oracle RAC database
> without downtime or disruption
> http://p.sf.net/sfu/oracle-sfdevnl
> Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
>
|
