seq generator

[GoogleCodeExporter]

CREATE TABLE `seq` (
  `name` varchar(20) NOT NULL,
  `val` int(10) unsigned NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=MyISAM -- works inside transactions
DEFAULT CHARSET=latin1; 

DROP FUNCTION IF EXISTS I.seq;
CREATE FUNCTION I.`seq`(seq_name char (20)) RETURNS int(11)
begin
 update seq set val=last_insert_id(val+1) where name=seq_name;
 return last_insert_id();
end;

DROP FUNCTION IF EXISTS I.seq_create;
CREATE FUNCTION I.`seq_create`(seq_name char (20), v int) RETURNS int(11)
begin
insert into seq values(seq_name,v);
return 0;
end;

DROP FUNCTION IF EXISTS I.seq_nextval;
CREATE FUNCTION I.`seq_nextval`(seq_name char (20)) RETURNS int(11)
begin
 update seq set val=last_insert_id(val+1) where name=seq_name;
 return last_insert_id();
end;

Original issue reported on code.google.com by lstrash...@gmail.com on 2 Aug 2011 at 10:41

[GoogleCodeExporter]

What's the difference between seq() and seq_nextval() ?

Original comment by shlomi.n...@gmail.com on 10 Aug 2011 at 5:10

[GoogleCodeExporter]

Its a synonym.
In our company we use seq() - and seq_nextval() is an Oracle convention.

Original comment by lstrash...@gmail.com on 10 Aug 2011 at 3:41

[GoogleCodeExporter]

The big problem I have with the notion of sequence generator is that it makes 
common_schema stateful.
So if anyone were to rebuild common_schema, sequences would start going mad.

Also, I have a problem with MyISAM tables, since they are not crash safe; I 
wouldn't want to rely on non-crash safe mechanism for my sequences.

Original comment by shlomi.n...@gmail.com on 6 Sep 2011 at 10:26

[GoogleCodeExporter]

You are correct. Rebuilding is a manual process.

The only reason why I am using myISAM is that the seq() function can be used
inside innodb transactions.
I don't think that in case of crash we worry to much about sequences as long
as they not going backwards :)
I.e. I don't really care if next_val created or not during crash as long it
is not used, and that is insured by innodb.

Original comment by lstrash...@gmail.com on 6 Sep 2011 at 3:40