Menu
▾
▴
firebird-devel
|
From: Roman R. <ro...@ro...> - 2010-12-25 22:15:45
|
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 |
|
From: Paul V. <pa...@vi...> - 2010-12-25 23:11:55
|
Roman wrote: > 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). I don't know if this is a good or bad idea. Personally, I don't like the fact that LAST_INSERT_ID() returns the last value generated by *any* generator. If I need to know a generated ID, it's usually from a specific generator. If I want it in a variable, I can have the trigger that calls the generator store it in a user context variable. Paul Vinkenoog |
|
From: Adriano d. S. F. <adr...@gm...> - 2010-12-25 23:22:47
|
On 25-12-2010 19:58, Roman Rokytskyy wrote: > > 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. > I'd much prefer something like LAST_GEN_ID(<name>). Oracle has <name>.curval, so it's like something a database-portable application would be capable to use. Adriano |
|
From: Leyne, S. <Se...@br...> - 2010-12-26 03:04:29
|
Adriano, > > 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. > > > I'd much prefer something like LAST_GEN_ID(<name>). Oracle has > <name>.curval, so it's like something a database-portable application would > be capable to use. Does this represent the next value, the last value for any transaction or the last value for the current transaction? Sean |
|
From: Roman R. <ro...@ro...> - 2010-12-26 08:29:35
|
> Does this represent the next value, the last value for any transaction or the last value for the current transaction? Last value for current transaction, I think. I need to check MySQL docs for this. Roman |
|
From: Norman D. <No...@du...> - 2010-12-26 17:16:25
|
Hi Sean, >> I'd much prefer something like LAST_GEN_ID(<name>). Oracle has >> <name>.curval, so it's like something a database-portable application would >> be capable to use. > > Does this represent the next value, the last value for any transaction or the last value for the current transaction? Assuming you are asking about Oracle..... You can select <sequence_name>.curval ONLY whan that sequence has been used to generate a new value within the current transaction using nextval. So, this fails: <start a new transaction> ... Select sequence_name.curval [into some_variable] from dual; ... commit; Whereas this works: <start a new transaction> ... Select sequence_name.nextval [into some_variable] from dual; Select sequence_name.curval [into another_variable] from dual; ... commit; It doesn't have to be a 'select', it can be an 'insert' or 'insert ... returning' etc. But once you have the "new" value using nextval, you can call curval as often as you like within this transaction. Cheers, Norman. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767 |
|
From: Helen B. <he...@tp...> - 2010-12-26 04:48:23
|
At 12:22 PM 26/12/2010, Adriano dos Santos Fernandes wrote: >On 25-12-2010 19:58, Roman Rokytskyy wrote: >> >> 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. >> >I'd much prefer something like LAST_GEN_ID(<name>). Oracle has ><name>.curval, so it's like something a database-portable application >would be capable to use. What's wrong with SELECT GEN_ID (<name>,0) from rdb$database ? Does it need to be stored somehow (otherwise)? Surely poking it into a context variable is just lipstick. All transactions should get the "right" result, since it is independent of any other transactions. Helen |
|
From: Roman R. <ro...@ro...> - 2010-12-26 08:56:02
|
> What's wrong with > SELECT GEN_ID (<name>,0) from rdb$database Nothing, except that I do not know <name>... > ? Does it need to be stored somehow (otherwise)? Surely poking it into a context variable is just lipstick. No, that is not strictly requried. > All transactions should get the "right" result, since it is independent of any other transactions. Correct. Roman |
|
From: Adriano d. S. F. <adr...@gm...> - 2010-12-26 10:20:30
|
On 26-12-2010 06:55, Roman Rokytskyy wrote: >> What's wrong with >> SELECT GEN_ID (<name>,0) from rdb$database > > Nothing, except that I do not know <name>... > >> ? Does it need to be stored somehow (otherwise)? Surely poking it into a context variable is just lipstick. > > No, that is not strictly requried. > >> All transactions should get the "right" result, since it is independent of any other transactions. > > Correct. > No, that's all wrong! You'll get values allocated in another connections. Adriano |
|
From: Roman R. <ro...@ro...> - 2010-12-26 11:08:35
|
>>> All transactions should get the "right" result, since it is independent of any other transactions. >> >> Correct. >> > No, that's all wrong! > > You'll get values allocated in another connections. You mean the SELECT gen_id(<name>, 0) case? Yes, that won't work in a concurrent environment. I thought that you're talking about the general requirements. Roman |
|
From: Roman R. <ro...@ro...> - 2010-12-26 08:55:03
|
> I'd much prefer something like LAST_GEN_ID(<name>). Oracle has > <name>.curval, so it's like something a database-portable application > would be capable to use. Issue is that if you port PHP code that uses AUTO_INCREMENT feature, there is no name for the generator available in the context. If I knew which column was incremented, I would use RETURNING clause. But that's not the case. Joomla has a function to insert an object, which generates an INSERT statement, and then if PK column is specified, generated value is filled in the object. Then there is another function that takes the INSERT statement and executes it, and then another function to get the generated values. Similar approach construct is used in Java O/R mappers, however there is an API call that tells the JDBC driver to return the generated values. I parse the SQL statement and generate a correct one with RETURNING clause. So, at the moment we have the only possibility to use the same generator for all tables and use gen_id(<name>, 0) as Helen suggests. And I am not sure that this will not require changing too much code in Joomla (which most likely won't be accepted by Joomla project). Considering the big picture I was thinking about having table declarations like this: CREATE SEQUENCE someTableSeq; CREATE TABLE someTable( ID INTEGER NOT NULL DEFAULT NEXT VALUE FOR someTableSeq, .... ) I remember that there were issues with backup/restore here (functions are restored after tables, so this construct is disabled, AFAIK), so one should use triggers at the moment. But I think that DEFAULT clause with gen_id function or NEXT VALUE FOR clause should be enabled anyway (generators can be restored first, they do not depend on any other objects). Roman |
|
From: Dimitry S. <sd...@ib...> - 2010-12-26 09:06:20
|
26.12.2010 9:54, Roman Rokytskyy wrote: > Joomla has a function to insert an object, which generates an INSERT > statement, and then if PK column is specified, generated value is filled > in the object. Then there is another function that takes the INSERT > statement and executes it, and then another function to get the > generated values. And what is preventing you from putting PK value, generated in the first procedure, into any half-global variable and retrieve the value in the third procedure?.. -- SY, SD. |
|
From: Roman R. <ro...@ro...> - 2010-12-26 09:20:16
|
26.12.2010 10:05, Dimitry Sibiryakov написав(ла): > 26.12.2010 9:54, Roman Rokytskyy wrote: >> Joomla has a function to insert an object, which generates an INSERT >> statement, and then if PK column is specified, generated value is filled >> in the object. Then there is another function that takes the INSERT >> statement and executes it, and then another function to get the >> generated values. > > And what is preventing you from putting PK value, generated in the first procedure, > into any half-global variable and retrieve the value in the third procedure?.. I do not know the name of the PK column that was generated. That would require parsing the INSERT statement, extract table name and then check the metadata to discover the name of that PK column. Roman |
|
From: Dimitry S. <sd...@ib...> - 2010-12-26 10:36:42
|
26.12.2010 10:20, Roman Rokytskyy wrote: > 26.12.2010 10:05, Dimitry Sibiryakov написав(ла): >> 26.12.2010 9:54, Roman Rokytskyy wrote: >>> Joomla has a function to insert an object, which generates an INSERT >>> statement, and then if PK column is specified, generated value is filled >>> in the object. Then there is another function that takes the INSERT >>> statement and executes it, and then another function to get the >>> generated values. >> >> And what is preventing you from putting PK value, generated in the first procedure, >> into any half-global variable and retrieve the value in the third procedure?.. > > I do not know the name of the PK column that was generated. But how in this case Joomla manages to cope with "generated value is filled in the object"? -- SY, SD. |
|
From: Roman R. <ro...@ro...> - 2010-12-26 11:05:46
|
>> I do not know the name of the PK column that was generated. > > But how in this case Joomla manages to cope with "generated value is filled in the object"? Yes, you are right. In that case I have the name of the column. Considering the case that INSERT is generated inside that function, it will be easy to use RETURNING clause. More problematic is the second case where arbitrary SQL is set from outside and then this function is called. That is the main use case I am worried about, since the function to execute SQL and the function to get last generated ID are public. They are used in base Joomla in two places, those can be fixed, but they can be used in thousands of extensions, that would be an issue. Roman |
|
From: Martin S. <mse...@gm...> - 2010-12-26 08:49:15
|
On Saturday, 25. December 2010 22.58:53 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. > [...] In MSEgui we use either a tsequencelink which queries a generator to provide the ID value for an inserted record or we use the RETURNING clause of the INSERT or UPDATE statement in order to update the datatset field value of the inserted or modified record automatically. Martin |
|
From: Roman R. <ro...@ro...> - 2010-12-26 09:08:39
|
> In MSEgui we use either a tsequencelink which queries a generator to provide > the ID value for an inserted record or we use the RETURNING clause of the > INSERT or UPDATE statement in order to update the datatset field value of the > inserted or modified record automatically. Right, but that requires either parsing of the passed INSERT statement (and I would need to check metadata to discover what are the PK columns and rewrite the query) or rewriting the applications. I would like to avoid latter, and it would require a lot of coding for the former. Roman |
|
From: Adriano d. S. F. <adr...@gm...> - 2010-12-26 10:21:35
|
On 26-12-2010 06:54, Roman Rokytskyy wrote: > > Considering the big picture I was thinking about having table > declarations like this: > > CREATE SEQUENCE someTableSeq; > > CREATE TABLE someTable( > ID INTEGER NOT NULL DEFAULT NEXT VALUE FOR someTableSeq, > .... > ) > > I remember that there were issues with backup/restore here (functions > are restored after tables, so this construct is disabled, AFAIK), so one > should use triggers at the moment. But I think that DEFAULT clause with > gen_id function or NEXT VALUE FOR clause should be enabled anyway > (generators can be restored first, they do not depend on any other objects). > Provided we're talking about new features anyway, Firebird 3 already has identity columns. Adriano |
|
From: Roman R. <ro...@ro...> - 2010-12-26 11:06:43
|
> Provided we're talking about new features anyway, Firebird 3 already has > identity columns. Fine, but I suspect that we still won't have similar function to get last generated ID. Roman |
|
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
>
|
|
From: Roman R. <ro...@ro...> - 2010-12-26 21:53:33
|
Tony,
> 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.
Thanks! I will give it a try!
> 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.
So far I did not see the enumerated types. Hopefully, they don't use them :)
> 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. "
Hey, that's very nice solution to the issue! Thanks!
Roman
|
|
From: Ann W. H. <aha...@ib...> - 2010-12-29 19:38:03
|
On 12/26/2010 9:44 AM, Tony Whyman wrote:
> Roman,
>
>
> 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
>
The MySQL semantics are that after an insert that creates more than one
row (insert ... select) last_insert_id returns the first autonumber
value. Probably not significant, and probably returning the last one
generated is more useful, but a difference I think. And do remember to
log changes in BEFORE action triggers, or you'll only get log ids...
Best regards,
Ann
|
|
From: Alex P. <pes...@ma...> - 2010-12-27 09:59:26
|
On 12/26/10 11:54, Roman Rokytskyy wrote: >> I'd much prefer something like LAST_GEN_ID(<name>). Oracle has >> <name>.curval, so it's like something a database-portable application >> would be capable to use. > Issue is that if you port PHP code that uses AUTO_INCREMENT feature, > there is no name for the generator available in the context. If I knew > which column was incremented, I would use RETURNING clause. But that's > not the case. > > Joomla has a function to insert an object, which generates an INSERT > statement, and then if PK column is specified, generated value is filled > in the object. Then there is another function that takes the INSERT > statement and executes it, and then another function to get the > generated values. > Roman, what if table in that insert has after insert trigger which also inserts records in more tables? I suppose that last_gen_id() with no name parameter will return you that value. Is it what people expect from that Joomla's function? If we anyway talk about SQL extension - what if we add RETURNING PRIMARY KEY? Suppose this can be useful in many cases. |
|
From: Roman R. <ro...@ro...> - 2010-12-27 12:41:28
|
> Roman, what if table in that insert has after insert trigger which also > inserts records in more tables? I suppose that last_gen_id() with no > name parameter will return you that value. Is it what people expect from > that Joomla's function? Correct. But that is the design behind the MySQL AUTO_INCREMENT column. I still do not have a question re. how MySQL behaves when two connections use the same user name and perform the insert into the same table... > If we anyway talk about SQL extension - what if we add RETURNING PRIMARY > KEY? Suppose this can be useful in many cases. That would work for Joomla, but I am not sure whether it is useful in general case. On the other hand, it would be a nice addition to identity columns in 3.0. Roman |
|
From: Milan B. <mi...@pa...> - 2010-12-27 11:17:40
|
Roman Rokytskyy wrote:
> For LAST_INSERT_ID(), the most recently generated ID is maintained in
> the server on a per-connection basis.
Wouldn't adding a simple line to auto-increment triggers do the same?
create trigger
...
new.autoinc = gen_id(some_generator, 1);
RDB$SET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID', new.autoinc);
...
--
Milan Babuskov
==================================
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==================================
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X