[Koha-devel] Suggestion on DB portability

[Alan Millar]

On Sat, Jun 29, 2002 at 08:33:26PM -0700, Tonnesen Steve wrote:
> On Sat, 29 Jun 2002, Alan Millar wrote:
> > The topic of PostgresQL came up on the main Koha list.  One of the 
> > suggestions was that the database should not be created via a MySQL dump 
> > because the index and unique-id commands are different.
> > 
> > I agree.  I would suggest that "updatedatabase" should be enhanced, and 
> > all database creation/adjustment should be done through it.  
 
> I think this is a great idea.  On the plus side, there are only four
> tables that use auto_increment in Koha, four of which are mine.  In the
> four tables where I use it, it just increments a unique identifier that I
> use for identifying the rows in the table, and should be replaceable in
> every case by the row OID in postgresql.  That makes for database specific
> code, though.  Is there some other workaround for the auto_increment
> issue?

Postgresql has an auto-incrementing identifier feature.  I think it's
called a "sequence" and I seem to recall that it is not an attribute of
the column per se, but a separate entity that can be tied to the
column, resulting in the same thing.  It seems to be the closest match.  

Do row OIDs ever change, such as with DB maintenance ("vacuuming", perhaps)?  
We'd want to be wary of that.   Using the contents of a column sounds 
most portable.

On a related topic:

The auto-increment done by the database is good, because it
guarantees uniqueness.  Koha has a looming problem in the current
increment method, used for example with numeric barcodes, biblio
numbers, and item numbers.  Right now a query is done to retrieve
the last value, a line of perl increments it, and then the new value
is written to the database.  The problem is that in between the
first step (read) and the third step (write) there is a race condition.
If anyone else does the same action at the same time, you
will get a conflict.  

How Koha handles it varies from one script to the next.  Because 
the nature of card catalog usage, it probably won't happen often, but 
we'll see it especially as we try to scale up.  If we have more batch 
automation of any sorts, we will definitely be more likely to see it then.

At some point I imagine we will need a function that guarantees
a unique value for a given identifier, including any appropriate 
locking or similar requirements.

- Alan

----
Alan Millar     --==> am12 at bolis.com <==--