[Kexi] IDENTITY issues in Sybase

[Jarosław Staniek]

Sharan Rao said the following, On 2007-12-23 23:07:

>  I've somehow managed to get Sybase ASE running, but I think Sybase tried real 
> hard to introduce incompatibilities (hopefully not :P ) between Sybase ASE 
> and Sybase ASA ( in which I tested much of my kexi code :( ) 
> 
> Auto-increment column is available in Sybase ASA as both `AUTOINCREMENT` and 
> `IDENTITY`. But in Sybase ASE there's only `IDENTITY` ( as afar as I could 
> find ). Now, the interesting thing about this column is explained in the 
> following (tinied) link:
> 
> http://tinyurl.com/25ltr9 
> 
> There is a similar option, identity_update which needs to be set/reset before 
> updating an IDENTITY column.
> 
> Now, the issues :
> 1. It needs the field list to be specified in every INSERT statement which 
> inserts an explicit value into the IDENTITY column. This is currently not 
> done in Kexi for some `INSERT INTO` queries. Trivial to add,I assume. Shall 
> I ?

Sharan,
thanks for continuing this research.

Yes, definitely. This makes the statements easier to debug when a column is 
added/remoevd/changed in the future.

> 2. I need to set/unset the options, `identity_insert` and `identity_update`, 
> before Inserting / Updating respectively, and turn them off immediately after 
> the respective query. ( these options need to be set per table).

Related question: can we analyze where do we have to perform explicit 
inserts/updates touching identity columns?
I hope we should not have them almost at all for kexi__* system tables. Of 
course in case of user tables wee need the hack you have mentioned.

 >   Given the fact, that I only have the SQL query string, when I get one of
 > these `execute` queries in the driver code, I guess the right way to proceed
 > is to use the KexiDB::Parser class for getting the operation, tableschema
 > etc. . (or is there a better method ? ).

My idea is that we could handle this at higher level, in a delayed - while 
building SQL statement that's then transferred to the backend. Note that as we 
have no parser for updates/inserts, all such queries are built anyway by 
kexidb insert/update helper routines, what are in turn executed from places 
like KexiDataAwareObjectInterface.
We have these functions used:

bool Connection::updateRow()
bool Connection::insertRow()

We may want to add empty virtual methods
Connection::drv_beforeUpdateRow(), Connection::drv_afterUpdateRow()
Connection::drv_beforeInsertRow(), Connection::drv_afterInsertRow()
and call them directly before/after executeSQL(m_sql) line.

These methods would have similar parameters as updateRow()/insertRow(), so you 
can implement the forementioned drv_* methods for the Sybase driver and check 
in 'QuerySchema &query' and 'RowEditBuffer& buf' whether your identity 
column(s) have to be altered.

(I guess bool Connection::deleteRow() does not require this kind of approach?)

Merry Christmas, Sharan and everyone!

-- 
regards / pozdrawiam, Jaroslaw Staniek
  Sponsored by OpenOffice Polska (http://www.openoffice.com.pl/en) to work on
  Kexi & KOffice (http://www.kexi.pl/en, http://www.koffice.org/kexi)
  KDE Libraries for MS Windows (http://windows.kde.org)