First we want to make MySQL tables retrieve PKs values from one global sequence. The second needle is to make this sequence values not consistent, it is to disallow visitors track your ids order.
h2. Theory of operation
Lets combine ideas together. One sequence for all tables + Pseudo random sequence values. My friend, the developer and mathematician, had suggested me the following formula:
@(`currval` * `factor`) % `module`@
_`currval`_ — The current sequence values, which will be incremented by 1 on each nextval operation.
_`factor`_ — Multiplier which should be equals to 282 475 249.
_`module`_ — Divisor which should be equals to 1 000 000 000 (one billion).
The algorithm guarantees than with the following formula we will always get the unique value starting from `currval` = 1 and so on.
@(`currval` * 282475249) % 1000000000@
The sequence configuration will be stored into the database table `_sequence`. The `name` field is for sequence name, it should be unique. Columns `currval`, `factor` and `module` are from the formula described above. It is important to make this table not transaction aware, because transactions will damage our sequence on rollback. We use MyISAM engine for this purpose.
pre. create table `_sequence` (
`name` varchar(32) not null primary key,
`currval` bigint(20) unsigned not null,
`factor` bigint(20) unsigned not null,
`module` bigint(20) unsigned not null
) engine=MyISAM default charset=utf8;
insert into `_sequence` values (‘id’, 1, 282475249, 1000000000);
Next, we need _`nextval`_ and _`currval`_ function to deal with sequence. See their definition in my “MySQL Sequence Generator”:http://webcraft-dev.blogspot.com/2010/09/mysql-sequence-generator.html blog post.
“Zend Framework supports sequences”:http://framework.zend.com/manual/en/zend.db.table.html#zend.db.table.insert.key-sequence (for PostgreSQL, Oracle, etc.), and we have to make it support our MySQL sequences. It is easy. First we should extend MySQL adapter and override _lastSequenceId()_ and _nextSequenceId()_ to call _`currval`_ and _`nextval`_. Get the “Db_Adapter_Mysql”:http://github.com/satyrius/Webcraft/blob/e73f3125594cb9e22a0373510c3ebfdb5e930b6a/classes/Db/Adapter/Mysql.php from my repository.
That’s all. Our application is ready. To make your table use this sequence do the following:
pre. create table `account` (
`id` int unsigned not null primary key comment ‘pk will be retrieved from sequence’,
`email` varchar(64) not null unique key comment ‘primary user identificator’,
`password` varchar(32) not null comment ‘user password md5 hash’
) engine=innodb default charset=utf8;
// Use MySQL adapter with sequence support
$db = new Db_Adapter_MySQL($connectionOptions);
// Create account table instance, pass adapter and sequence name
$account = new Account(array('db' => $db, ‘sequence’ => ‘id’));
// Create new account and save it. PK will be generated automatically by sequence
->setFromArray(array(‘email’ = ‘firstname.lastname@example.org’, ‘qwerty’))
In spite of MySQL does not support sequences we can emulate this id generation method by creating _`nextval`_ and _`currval`_ mysql function. We use MyISAM engine for `_sequence` to use it outside of transactions. To itegrate this solution to our Zend Framework application we should extend _Zend_Db_Adapter_Pdo_Mysql_ by overriding _lastSequenceId()_ and _nextSequenceId()_ methods.
* Original “MySQL Sequence Generator”:http://webcraft-dev.blogspot.com/2010/09/mysql-sequence-generator.html article;
* This technique in action in “Webcraft CMF”:http://github.com/satyrius/Webcraft
* SQL script to create “`_sequence` table and `currval`, `nextval` functions”:http://github.com/satyrius/Webcraft/blob/4996c971fb500520b73fe68a88ea0d680b9d7ab5/scripts/schema/02_routines.sql
* Extended “MySQL adapter with sequence support”:http://github.com/satyrius/Webcraft/blob/e73f3125594cb9e22a0373510c3ebfdb5e930b6a/classes/Db/Adapter/Mysql.php