Mysql custom sequence generator (e.g. oracle)

Question

I want to have two auto_increment columns for each table, but mysql allows only one auto_increment column. So, I tried to reproduce the oracle sequence using my own table.

Here is a diagram.

create table logical_id_seq (
    logical_id int auto_increment,
    primary key(logical_id)
);

create table mytable (
    physical_id int auto_increment,
    logical_id int not null references parent(logical_id),
    data varchar(20),
    version_start_date datetime not null,
    version_end_date datetime not null,
    primary key(physical_id),
    foreign key (logical_id) references logical_id_seq(logical_id),
    unique key (logical_id,version_start_date,version_end_date)
);

So, the logical_id_seq table is used as a sequence generator.

creating a new object:

• Insert a new entry into logical_id_seq.
• Read last_insert_id () from logical_id_seq.
• Use the above value to insert a new row into the table.

logical_id physical_id. , , ( ). , version_start_date version_end_date.

, .

Answer 1

(IIRC it MyISAM BDB), , .

create table component_core ( 
    component_id int auto_increment, 
    primary key(component_id) 
); 

create table component_history ( 
    component_id int not null, 
    version_id int auto_increment, 
    data varchar(20), 
    version_start_date datetime not null, 
    version_end_date datetime not null, 
    primary key(component_id,version_id) 
); 

component_core , _ component_history version_id 1 _. _ __, version_id . auto_increment MAX (auto_increment_column) + 1 WHERE component_id = given-component_id.

Insert a new component_core
Retrieve the last autoincremented value from component_core (1)
Insert a new component_history using the component_id from component_core
Insert a new component_history using the same component_id
Insert a new component_history using the same component_id
Insert a new component_core
Retrieve the last autoincremented value from component_core (2)
Insert a new component_history using the component_id from component_core
Insert a new component_history using the same component_id

component_core

component_id
1
2

component_history

component_id version_id
1            1
1            2
1            3
2            1
2            2

, - , ( , MyISAM, MyISAM)

: http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

Answer 2

, (...)

Hibernate Envers , :

Envers / . , , , , @Audited. , , . .