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
Index: connection.cpp
===================================================================
--- connection.cpp (revision 750995)
+++ connection.cpp (working copy)
@@ -1045,9 +1045,13 @@
#define C_INS_REC(args, vals) \
bool Connection::insertRecord(KexiDB::TableSchema &tableSchema args) {\
- return executeSQL( \
- QString("INSERT INTO ") + escapeIdentifier(tableSchema.name()) + " VALUES (" + vals + ")" \
- ); \
+ drv_beforeInsert( tableSchema.name(), tableSchema ); \
+ bool res = executeSQL( \
+ QString("INSERT INTO ") + escapeIdentifier(tableSchema.name()) \
+ + " (" + tableSchema.sqlFieldsList(m_driver) + ") VALUES (" + vals + ")" \
+ ); \
+ drv_afterInsert( tableSchema.name(),tableSchema ); \
+ return res; \
}
#define C_INS_REC_ALL \
@@ -1078,10 +1082,13 @@
vals \
it.toFront(); \
QString tableName( (it.hasNext() && it.peekNext()->table()) ? it.next()->table()->name() : "??" ); \
- return executeSQL( \
+ drv_beforeInsert( tableName, fields ); \
+ bool res = executeSQL( \
QString("INSERT INTO ") + escapeIdentifier(tableName) \
+ "(" + fields.sqlFieldsList(m_driver) + ") VALUES (" + value + ")" \
); \
+ drv_afterInsert( tableName, fields ); \
+ return res; \
}
C_INS_REC_ALL
@@ -1119,7 +1126,11 @@
m_sql += ")";
// KexiDBDbg<<"******** "<< m_sql << endl;
- return executeSQL(m_sql);
+ drv_beforeInsert( tableSchema.name(), tableSchema );
+ bool res = executeSQL(m_sql);
+ drv_afterInsert( tableSchema.name(), tableSchema );
+
+ return res;
}
bool Connection::insertRecord(FieldList& fields, const QList<QVariant>& values)
@@ -1135,10 +1146,11 @@
m_sql.clear();
QList<QVariant>::ConstIterator it = values.constBegin();
// int i=0;
+ QString tableName = escapeIdentifier( flist->first()->table()->name() );
while (f && (it!=values.constEnd())) {
if (m_sql.isEmpty())
m_sql = QString("INSERT INTO ") +
- escapeIdentifier(flist->first()->table()->name()) + "(" +
+ tableName + "(" +
fields.sqlFieldsList(m_driver) + ") VALUES (";
else
m_sql += ",";
@@ -1150,7 +1162,11 @@
}
m_sql += ")";
- return executeSQL(m_sql);
+ drv_beforeInsert( tableName, fields );
+ bool res = executeSQL(m_sql);
+ drv_afterInsert( tableName, fields );
+
+ return res;
}
bool Connection::executeSQL( const QString& statement )
@@ -2277,6 +2293,30 @@
return executeSQL( "ROLLBACK" );
}
+bool KexiDB::Connection::drv_beforeInsert(const QString& table, FieldList& fields)
+{
+ // do nothing
+ return true;
+}
+
+bool KexiDB::Connection::drv_afterInsert(const QString& table, FieldList& fields)
+{
+ // do nothing
+ return true;
+}
+
+bool KexiDB::Connection::drv_beforeUpdate(const QString& table, FieldList& fields)
+{
+ // do nothing
+ return true;
+}
+
+bool KexiDB::Connection::drv_afterUpdate(const QString& table, FieldList& fields)
+{
+ // do nothing
+ return true;
+}
+
bool Connection::drv_setAutoCommit(bool /*on*/)
{
return true;
@@ -3306,7 +3346,15 @@
m_sql += (sqlset + " WHERE " + sqlwhere);
KexiDBDbg << " -- SQL == " << ((m_sql.length() > 400) ? (m_sql.left(400)+"[.....]") : m_sql) << endl;
- if (!executeSQL(m_sql)) {
+ // preprocessing before update
+ drv_beforeUpdate( mt->name(), query );
+
+ bool res = executeSQL( m_sql );
+
+ // postprocessing after update
+ drv_afterUpdate( mt->name(), query );
+
+ if (!res) {
setError(ERR_UPDATE_SERVER_ERROR, i18n("Row updating on the server failed."));
return false;
}
@@ -3409,8 +3457,14 @@
m_sql += (sqlcols + ") VALUES (" + sqlvals + ")");
// KexiDBDbg << " -- SQL == " << m_sql << endl;
- bool res = executeSQL(m_sql);
+ // do driver specific pre-processing
+ drv_beforeInsert( mt->name(), query);
+ bool res = executeSQL(m_sql);
+
+ // do driver specific post-processing
+ drv_afterInsert( mt->name(), query);
+
if (!res) {
setError(ERR_INSERT_SERVER_ERROR, i18n("Row inserting on the server failed."));
return false;
Index: cursor.cpp
===================================================================
--- cursor.cpp (revision 750642)
+++ cursor.cpp (working copy)
@@ -229,15 +229,13 @@
m_afterLast = !getNextRecord();
return !m_afterLast;
}
- }
-#if 0 // reverted; todo: fix for sybase only - reopen() does the job below for other drivers...
- else {
+ } else if ( !( m_conn->driver()->beh->_1ST_ROW_READ_AHEAD_REQUIRED_TO_KNOW_IF_THE_RESULT_IS_EMPTY ) ) {
// not buffered
m_at = 0;
m_afterLast = !getNextRecord();
return !m_afterLast;
}
-#endif
+
if (m_afterLast && m_at==0) //failure if already no records
return false;
if (!reopen()) //try reopen
Index: connection.h
===================================================================
--- connection.h (revision 750642)
+++ connection.h (working copy)
@@ -1001,7 +1001,37 @@
*/
virtual bool drv_rollbackTransaction(TransactionData* trans);
- /*! Changes autocommiting option for established connection.
+
+ /*! Preprocessing (if any) required by drivers before execution of an
+ Insert statement
+
+ \sa drv_afterInsert()
+ */
+ virtual bool drv_beforeInsert(const QString& table, FieldList& fields);
+
+ /*! Postprocessing (if any) required by drivers before execution of an
+ Insert statement.
+
+ \sa drv_beforeInsert()
+ */
+ virtual bool drv_afterInsert(const QString& table, FieldList& fields);
+
+ /*! Preprocessing required by drivers before execution of an
+ Update statement
+
+ \sa drv_afterUpdate()
+ */
+ virtual bool drv_beforeUpdate(const QString& table, FieldList& fields);
+
+ /*! Postprocessing required by drivers before execution of an
+ Insert statement
+
+ \sa drv_beforeUpdate()
+ */
+ virtual bool drv_afterUpdate(const QString& table, FieldList& fields);
+
+
+ /*! Changes autocommiting option for established connection.
\return true on success.
Note for driver developers: reimplement this only if your engine
Index: drivers/sybase/sybasedriver.cpp
===================================================================
--- drivers/sybase/sybasedriver.cpp (revision 750992)
+++ drivers/sybase/sybasedriver.cpp (working copy)
@@ -57,7 +57,9 @@
beh->_1ST_ROW_READ_AHEAD_REQUIRED_TO_KNOW_IF_THE_RESULT_IS_EMPTY=false;
beh->USING_DATABASE_REQUIRED_TO_CONNECT=false;
- beh->AUTO_INCREMENT_FIELD_OPTION="DEFAULT AUTOINCREMENT";
+ // for Sybase ASA this field is "DEFAULT AUTOINCREMENT"
+ // for MSSQL and Sybase ASE it's IDENTITY
+ beh->AUTO_INCREMENT_FIELD_OPTION="IDENTITY";
beh->AUTO_INCREMENT_PK_FIELD_OPTION = beh->AUTO_INCREMENT_FIELD_OPTION + " PRIMARY KEY ";
// confirm
@@ -169,12 +171,13 @@
+ QByteArray( "\"" ) ;
}
-QString SybaseDriver::addLimitTo1(const QString& sql, bool add)
+QString SybaseDriver::addLimitTo1(const QString& sql, bool add )
{
// length of "select" is 6
// eg: before: select foo from foobar
// after: select TOP 1 foo from foobar
- return add ? sql.trimmed().insert( 6 , " TOP 1 " ) : sql;
+ QString returnString = sql.trimmed().insert( 6, " TOP 1 " );
+ return add ? returnString : sql;
}
#include "sybasedriver.moc"
Index: drivers/sybase/sybaseconnection_p.cpp
===================================================================
--- drivers/sybase/sybaseconnection_p.cpp (revision 750992)
+++ drivers/sybase/sybaseconnection_p.cpp (working copy)
@@ -175,7 +175,7 @@
out<<" host = "<<hostName<<"\n";
if ( data.port == 0 )
- out<<" port = "<<2638<<"\n"; // default port to be used
+ out<<" port = "<<5000<<"\n"; // default port to be used
else
out<<" port = "<<data.port<<"\n";
@@ -241,8 +241,8 @@
if ( dbuse( dbProcess, dbName.toLatin1().data() ) == SUCCEED ) {
return true;
}
-// return false;
- return true; // for testing
+
+ return false;
}
/*! Executes the given SQL statement on the server.
Index: drivers/sybase/sybaseconnection.cpp
===================================================================
--- drivers/sybase/sybaseconnection.cpp (revision 750992)
+++ drivers/sybase/sybaseconnection.cpp (working copy)
@@ -58,11 +58,11 @@
QString serverVersionString;
- if ( !querySingleString( "Select @@servername" , version.string, 0 , false ) ) {
+ if ( !querySingleString( "Select @@servername" , version.string ) ) {
KexiDBDrvDbg << "Couldn't fetch server name" << endl;
}
- if ( !querySingleString( "Select @@version", serverVersionString , 0 , false ) ) {
+ if ( !querySingleString( "Select @@version", serverVersionString ) ) {
KexiDBDrvDbg << "Couldn't fetch server version" << endl;
}
@@ -97,16 +97,21 @@
if ( queryStringList( "Select name from master..sysdatabases", list ) )
return true;
- d->storeResult();
+// d->storeResult();
// setError(ERR_DB_SPECIFIC,mysql_error(d->mysql));
return false;
}
-bool SybaseConnection::drv_createDatabase( const QString &dbName) {
+bool SybaseConnection::drv_createDatabase( const QString &dbName)
+{
KexiDBDrvDbg << "SybaseConnection::drv_createDatabase: " << dbName << endl;
// mysql_create_db deprecated, use SQL here.
- if (drv_executeSQL("CREATE DATABASE " + driver()->escapeString(dbName)))
- return true;
+ if (drv_executeSQL("CREATE DATABASE " + dbName)) {
+ // set allow_nulls_by_default option to true
+ QString allowNullsQuery = QString( "sp_dboption %1, allow_nulls_by_default, true" ).arg( dbName );
+ if ( drv_executeSQL( allowNullsQuery.toLatin1().data() ) )
+ return true;
+ }
d->storeResult();
return false;
}
@@ -115,8 +120,9 @@
{
Q_UNUSED(cancelled);
Q_UNUSED(msgHandler);
+
//TODO is here escaping needed?
- return d->useDatabase( driver()->escapeString( dbName ) );
+ return d->useDatabase( dbName ) ;
}
bool SybaseConnection::drv_closeDatabase() {
@@ -137,7 +143,7 @@
{
int rowId;
- querySingleNumber( "Select @@IDENTITY", rowId );
+ querySingleNumber( "Select @@IDENTITY", rowId );
return ( qint64 )rowId;
}
@@ -174,7 +180,7 @@
bool SybaseConnection::drv_getTablesList( QStringList &list )
{
- return queryStringList( "Select name from sysobjects where type='U'", list );
+ return queryStringList( "Select name from sysobjects where type='U'", list );
}
PreparedStatement::Ptr SybaseConnection::prepareStatement(PreparedStatement::StatementType type,
@@ -183,4 +189,59 @@
return KSharedPtr<PreparedStatement>( new SybasePreparedStatement(type, *d, fields) );
}
+bool KexiDB::SybaseConnection::drv_beforeInsert( const QString& table, FieldList& fields )
+{
+
+ int count = fields.autoIncrementFields()->count();
+
+ if ( count > 0 ) {
+ // explicit insertion into IDENTITY fields !!
+ drv_executeSQL( QString( "SET IDENTITY_INSERT %1 ON" ).arg( table ) );
+ }
+
+ return true;
+}
+
+bool KexiDB::SybaseConnection::drv_afterInsert( const QString& table, FieldList& fields )
+{
+ // should we instead just set a flag when an identity_insert has taken place and only check for that
+ // flag here ?
+
+ int count = fields.autoIncrementFields()->count();
+
+ if ( count > 0 ) {
+ // explicit insertion into IDENTITY fields has taken place. Turn off IDENTITY_INSERT
+ drv_executeSQL( QString( "SET IDENTITY_INSERT %1 OFF" ).arg( table ) );
+ }
+
+ return true;
+}
+
+bool KexiDB::SybaseConnection::drv_beforeUpdate( const QString& table, FieldList& fields )
+{
+ Field::List* identityFieldList = fields.autoIncrementFields();
+
+ if ( identityFieldList->count() > 0 ) {
+ // explicit update of IDENTITY fields has taken place.
+ drv_executeSQL( QString( "SET IDENTITY_UPDATE %1 ON" ).arg( table ) );
+ }
+
+ return true;
+}
+
+bool KexiDB::SybaseConnection::drv_afterUpdate( const QString& table, FieldList& fields )
+{
+ // should we instead just set a flag when an identity_update has taken place and only check for that
+ // flag here ?
+
+ Field::List* identityFieldList = fields.autoIncrementFields();
+
+ if ( identityFieldList->count() > 0 ) {
+ // explicit insertion into IDENTITY fields has taken place. Turn off IDENTITY_INSERT
+ drv_executeSQL( QString( "SET IDENTITY_UPDATE %1 OFF" ).arg( table ) );
+ }
+
+ return true;
+}
+
#include "sybaseconnection.moc"
Index: drivers/sybase/sybasedriver.h
===================================================================
--- drivers/sybase/sybasedriver.h (revision 750992)
+++ drivers/sybase/sybasedriver.h (working copy)
@@ -48,7 +48,7 @@
virtual QByteArray drv_escapeIdentifier(const QByteArray& str) const;
virtual Connection *drv_createConnection( ConnectionData &conn_data );
virtual bool drv_isSystemFieldName( const QString& n ) const;
- virtual QString addLimitTo1(const QString& sql, bool add);
+ virtual QString addLimitTo1(const QString& sql, bool add );
private:
static const char *keywords[];
Index: drivers/sybase/sybaseconnection.h
===================================================================
--- drivers/sybase/sybaseconnection.h (revision 750992)
+++ drivers/sybase/sybaseconnection.h (working copy)
@@ -73,10 +73,17 @@
//TODO: move this somewhere to low level class (MIGRATION?)
virtual bool drv_containsTable( const QString &tableName );
+ virtual bool drv_beforeInsert( const QString& table, FieldList& fields );
+ virtual bool drv_afterInsert( const QString& table, FieldList& fields );
+
+ virtual bool drv_beforeUpdate( const QString& table, FieldList& fields );
+ virtual bool drv_afterUpdate( const QString& table, FieldList& fields );
+
SybaseConnectionInternal* d;
friend class SybaseDriver;
friend class SybaseCursor;
+
};
}
_______________________________________________
Kexi mailing list
[email protected]
https://mail.kde.org/mailman/listinfo/kexi
