database - Mysql custom sequence generator ( like oracle) -


I have two auto_increment columns per table, but mysql only allows for an auto_increment column. . So, I tried to copy the Oracle sequence using my own table

Here is the schema

  create the table logical_id_seq (logical_id integer auto_increment, primary key (logical_id) ). Table mytable (physical_id integer auto_increment, logical_id integer no parent reference (logical_id), data varchar (20), version_start_date datetime do not empty, version_end_date datetime not null, primary key (physical_id), foreign key (logical_id) reference logical_id_seq Logical_id), unique key (logical_id, version_start_date, version_and_date));  

Therefore, the sequence of the logical_id_seek table is used as the generator.

Create new entity:

  1. Enter new entry in logical_id_sec
  2. read last_insert_id () from logical_id_seq.
  3. Use the above value to insert a new line in the table. Let me tell you a little more context on logical_id and physical_id. I want to create a time travel database, which means that I want the database state to be any timestamp (now or past) Given. Therefore, I'm getting version_start_data and version_and_data.

    Can you please tell me that the sequence generator has side effects of my method. Based on your table type there is a clever trick you can use with autoincrement (this IIRC MyISAM)

  create table component_core (component_id int auto_inderrate, primary key (component_id)); Create table component_history (component_id integer not tap, version_id integer auto_increment, data varchar (20), version_start_date datetime not empty, version_end_date datetime not null, primary key (component_id, version_id));  

When inserting a new component into component_core, the autoincremented id that will be used as component_id when inserted in the component_history and version_id autoincrement area, there will be numbers for 1 for each separate component_id above . When to make a change in component_history, use the original component_id, but allow this to be automatically converted to version_id. In this situation, the generated value for the auto_increment column is maximized (auto_increment_column) + 1 WHERE component_id = given-component_id Insert a new component_core from component_core (1) insert components_core from component_id, insert a new component_history, insert a new component_history using the same component_id itself component_id insert

  get previous autoincremented value from component_core Insert a new component_history using a new component_core component_core to retrieve the last auto-indexed value (2) Insert a new component_history using component_id from component_id Insert a new component_history using the same element_id    component_core  

  component_ id 1 2  

Strong> component_history

  component_id version_id 1 1 2 2 3 3 1 2 2  

This technique is not certain that any help Especially because it is limited to specific table types (I believe it works, but you lose with transaction control intelligence H MyISAM)

Edit

Reference:


Comments

Popular posts from this blog

c# - sqlDecimal to decimal clr stored procedure Unable to cast object of type 'System.Data.SqlTypes.SqlDecimal' to type 'System.IConvertible' -

Calling GetGUIThreadInfo from Outlook VBA -

Obfuscating Python code? -