[cvsnt] Re: Repository auditing with Oracle

[Olaf Groeger]

Sorry Bo, my internet provider had some problems yesterday.

The way oracle works is not really different from other databases. the key
point is that a so-called identity column is just a convenient to take over
some responsibility from the client to the db server.

If you insert a row into a column with a PK it is in your responsibility to
ensure that the primary key is unique, otherwise the insert will fail. So
you must have a strategy to generate unique values and you must keep in
mind that several clients can perform an insert in the same table at the
same time, meaning the uniqueness must be over all clients. Obviously, this
is best done by a central point for all clients: the database server. So
the database holds some kind of static counter with an atomic access, from
which each client can receive one unique value, a sequence. because the
database increments the sequence upon each access, each client receives a
unique value. The sequence is not bound to a table and no one forces you to
use. It is just a tool that the database provides to you to generate unique
values. You can use a different strategy to ensure that the PK are unique,
but  it is a good practise to create a sequence for each table with a PK
and make the contract that each client must use the sequence seq_tabx for
tabx.

The client workflow is then:
1) Open connection
2) Start transaction
3) Get a sequence value
4) insert with the PK ID = sequence value
5) insert in dependant tables with FK = sequence value 
6) commit transaction
7) Close connection

this means, that if you don't use stored procedure, the number 3, getting
the sequence value is an additional server roundtrip.

The idea of the autoincrement/identiy stuff is to combine number 3 and
number 4 on the database side. The database server has a kind of hidden
sequence, which it automatically uses for the PK value. To perform the step
5 the database server then must give you the possibility to receive the
value of the PK it used.

You see, the difference between the Oracle/PostGreSQL and MSSQL is not on
the table layer, but only in the workflow. The table definition contains in
both cases something like PK unique NOT NULL INT. You can check this by
setting @@identity_insert off in MSSQL. Now it doesn't manage the PK colum
for you and your insert must contain a (legal) value for the PK. If you
replace the get-a-value-from-a-sequence by generate-an-id, then you are on
the sql standard level: There is a table with a PK, so if you want to
insert something take care to offer a unique value for it.

Hope that helps
Olaf