IDENTITY issues in Sybase

Hi everyone,

 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 ?

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).
  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 ? ). 

Thanks in advance for any suggestions 

Cheers and Merry Christmas to one and all 
 Sharan Rao
_______________________________________________
Kexi mailing list
Kexikde.org
https://ma
il.kde.org/mailman/listinfo/kexi

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/ke
xi)
  KDE Libraries for MS Windows (http://windows.kde.org)
_______________________________________________
Kexi mailing list
Kexikde.org
https://ma
il.kde.org/mailman/listinfo/kexi

On Monday 24 Dec 2007 5:15:12 pm Jarosław Staniek wrote:
> > 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.
>

   hmm, a quick search gave me one in
Connection::storeObjectSchemaData(..)
  My copy of the file is not the same as the svn version,
hence can't give the 
exact line number :(.
 Anyway, my tests failed because of one such query in
tables_test.h 

> 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, 
     
   oh ok  ( anyway
it was more of a hackish idea , so good riddens :P )

>
> 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.
>
 
  yes, obviously a better idea 

> 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?)
>

  Not yet !!! But I can never be sure what killer Sybase
feature I'll find 
next !! :P ;) 
 
> Merry Christmas, Sharan and everyone!

Cheers!
 Sharan Rao


_______________________________________________
Kexi mailing list
Kexikde.org
https://ma
il.kde.org/mailman/listinfo/kexi

On Monday 24 Dec 2007 5:15:12 pm Jarosław Staniek wrote:
>
> 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.
>

Hi,

 I've attached the modification of kexidb API and Sybase
driver adjustments 
for fixing the IDENTITY issues discussed. ( Although I've
attached even the 
sybase specific diffs, the focus ,of course, curently is on
connection.h/cpp 
API )

> 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.
>

The API differs from the above namely because, `Inserting`
into a row takes 
place from more places in KexiDB that
Conenction::insertRow(). There are some 
variants of Connection::insertRecord() to take care of too.
Hence I've changed the signature to   `tablename` and
`fieldlist` ( to which 
any QuerySchema and TableSchema can be passed ,of course,
but is more general 
for Sybase's requirements).  Sybase as such only wants to
know the tablename 
and the AutoIncrement fields that are being modified. Hence
the signature I 
specifed seems to do the job.

Any suggestions as to how I can make the API more general ?


Cheers and Happy New Year to all !!
 Sharan Rao

_______________________________________________
Kexi mailing list
Kexikde.org
https://ma
il.kde.org/mailman/listinfo/kexi

On Jan 1, 2008 1:41 AM, Sharan Rao <sharanraogmail.com> wrote:
> On Monday 24 Dec 2007 5:15:12 pm Jaros³aw Staniek
wrote:
> >
> > 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.
> >
>
> Hi,
>
>  I've attached the modification of kexidb API and
Sybase driver adjustments
> for fixing the IDENTITY issues discussed. ( Although
I've attached even the
> sybase specific diffs, the focus ,of course, curently
is on connection.h/cpp
> API )
>
> > 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.
> >
>
> The API differs from the above namely because,
`Inserting` into a row takes
> place from more places in KexiDB that
Conenction::insertRow(). There are some
> variants of Connection::insertRecord() to take care of
too.
> Hence I've changed the signature to   `tablename` and
`fieldlist` ( to which
> any QuerySchema and TableSchema can be passed ,of
course, but is more general
> for Sybase's requirements).  Sybase as such only wants
to know the tablename
> and the AutoIncrement fields that are being modified.
Hence the signature I
> specifed seems to do the job.
>
> Any suggestions as to how I can make the API more
general ?
>
> Cheers and Happy New Year to all !!
>  Sharan Rao
>


Reviewing the code again, is passing only FieldList object
enough ?
The tablename can be obtained from one of the fields in the
FieldList.
But this would also lead to calculation of the tableName
from the
FieldList object three times in most insertRecord()
functions.
1. Normal calculation in insertRecord()
2. in drv_beforeInsert()
3. in drv_afterInsert()

But I guess the interface will be less redundant ?
Suggestions ?

Cheers!
 Sharan Rao
_______________________________________________
Kexi mailing list
Kexikde.org
https://ma
il.kde.org/mailman/listinfo/kexi

On Tuesday 01 Jan 2008 7:48:11 pm Jarosław Staniek wrote:
> Sharan, thanks for the patch; it's mostly OK.
> First note, could you please change spaces to tabs in
the indentation?
> (even if we plan to switch to spaces soon I hope)
>
> * Move Connection::drv_beforeInsert(), etc. to the
header file for
> efficiency. You'll need to put Q_UNUSED(table);, etc.
there to avoid
> warnings about unused args.
> * Check results of calling these methods, i.e.
>     if (!drv_beforeInsert(...))
> 	return false;
> * commit cursor.cpp change (removal of my commentout)
as a separate
> checkin. * In SybaseConnection::drv_beforeInsert() you
could put instead:
>
> +bool KexiDB::SybaseConnection::drv_beforeInsert( const
QString& table,
> FieldList& fields )
> +{
> +
> +    if ( fields.autoIncrementFields()->isEmpty() )
> +          return true;
>       // explicit insertion into IDENTITY fields !!
>       return drv_executeSQL( QString::fromLatin1(
"SET IDENTITY_INSERT %1
> ON" ).arg( table ) );
> +}
>
> Same for other 3 methods.
> BTW, don't we need escape the table name?

Yes, I think I forgot almost all the error checking stuff
.
As you didn't mention it, I presume that the first interface
signature ( table 
and fieldList ) is to be used ?

Cheers!
 Sharan Rao
_______________________________________________
Kexi mailing list
Kexikde.org
https://ma
il.kde.org/mailman/listinfo/kexi

On Tuesday 01 Jan 2008 7:48:11 pm Jarosław Staniek wrote:
> Sharan Rao said the following, On 2007-12-31 22:07:
> > Reviewing the code again, is passing only
FieldList object enough ?
> > The tablename can be obtained from one of the
fields in the FieldList.
> > But this would also lead to calculation of the
tableName from the
> > FieldList object three times in most
insertRecord() functions.
> > 1. Normal calculation in insertRecord()
> > 2. in drv_beforeInsert()
> > 3. in drv_afterInsert()
>
> Sharan, thanks for the patch; it's mostly OK.
> First note, could you please change spaces to tabs in
the indentation?
> (even if we plan to switch to spaces soon I hope)
>
> * Move Connection::drv_beforeInsert(), etc. to the
header file for
> efficiency. You'll need to put Q_UNUSED(table);, etc.
there to avoid
> warnings about unused args.
> * Check results of calling these methods, i.e.
>     if (!drv_beforeInsert(...))
> 	return false;
> * commit cursor.cpp change (removal of my commentout)
as a separate
> checkin. * In SybaseConnection::drv_beforeInsert() you
could put instead:
>
> +bool KexiDB::SybaseConnection::drv_beforeInsert( const
QString& table,
> FieldList& fields )
> +{
> +
> +    if ( fields.autoIncrementFields()->isEmpty() )
> +          return true;
>       // explicit insertion into IDENTITY fields !!
>       return drv_executeSQL( QString::fromLatin1(
"SET IDENTITY_INSERT %1
> ON" ).arg( table ) );
> +}
>
> Same for other 3 methods.
> BTW, don't we need escape the table name?

Attached new patch with discussed changes.

Cheers!
 Sharan Rao

_______________________________________________
Kexi mailing list
Kexikde.org
https://ma
il.kde.org/mailman/listinfo/kexi