In .odb with an ODBC connection to a SQLite3 database, creating a table in Design View, I wanted to choose a primary key, so I right-clicked on the grey rectangle at the left end of the row defining the first field: Program action actual: The pop menu offers { Cut, Copy, Delete} . Program action expected: The pop-up menu offers { Cut, Copy, Delete, separator, "Primary Key"}. This observation is based on master commit 692878e, fetched 2014-08-13, configured: --enable-option-checking=fatal --enable-dbgutil --enable-crashdump --without-system-postgresql --without-myspell-dicts --with-extra-buildid --without-doxygen --with-external-tar=/home/terry/lo_hacking/git/src built on debian-wheezy 64-bit and running chroot'd to debian-sid. The database driver is from package libsqliteodbc version 0.992-2.
git bisect tells me: 423a84c4f7068853974887d98442bc2a2d0cc91b is the first bad commit ... source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e ... and git bisect log: # bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e # good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932 git bisect start 'latest' 'oldest' # good: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb git bisect good e02439a3d6297a1f5334fa558ddec5ef4212c574 # good: [4850941efe43ae800be5c76e1102ab80ac2c085d] source-hash-980a6e552502f02f12c15bfb1c9f8e6269499f4b git bisect good 4850941efe43ae800be5c76e1102ab80ac2c085d # good: [a900e72b6357882284c5955bdf939bf14269f5fb] source-hash-dd1050b182260a26a1d0ba6d0ef3a6fecc3f4e07 git bisect good a900e72b6357882284c5955bdf939bf14269f5fb # skip: [e80660c5a1d812cd04586dae1f22767fc3778c4a] source-hash-07c60c8ee2d1465544a6a39e57bc06b3690b8dfb git bisect skip e80660c5a1d812cd04586dae1f22767fc3778c4a # good: [df9bcaed2faa2a8d11b19f877cdff3a12a887278] source-hash-6ba9692d8bbe3e3c245aca9a7c928e81178d05f1 git bisect good df9bcaed2faa2a8d11b19f877cdff3a12a887278 # good: [741197a13a361480f59eeb3bd1401f984f49f1c0] source-hash-9a61470eb1fa161cba70f2e9c4ea8817dc7f617e git bisect good 741197a13a361480f59eeb3bd1401f984f49f1c0 # good: [882db5e268e28962bdf805c820a5e031b0df9936] source-hash-383dccc094f8c8c07b4298ce0b7406d18cd61cee git bisect good 882db5e268e28962bdf805c820a5e031b0df9936 # good: [69e91b0c890097cfb1781733f260644bb1ec9ee8] source-hash-66fce1f61e7e088bd92e19ecb0dd94553de6f824 git bisect good 69e91b0c890097cfb1781733f260644bb1ec9ee8 # good: [426fdcf0b13134ffbc7f92900d5862fad3723192] source-hash-c9f3c508bb1a1d94fd6172b9cdac30278559f31c git bisect good 426fdcf0b13134ffbc7f92900d5862fad3723192 # good: [9f0fd4c327fa8765bef8a0e2a0e85a620ab6f835] source-hash-fe1e1d1292973211f0edfa64c05279ca59634652 git bisect good 9f0fd4c327fa8765bef8a0e2a0e85a620ab6f835 # first bad commit: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e
Confirming - I had noticed this too.
Nothing in the range fe1e1d1292973211f0edfa64c05279ca59634652..c15927f20d4727c3b8de68497b6949e72f9e6e9e looks relevant...
On pc Debian x86-64 with 4.4 sources updated today, I could reproduce this. A bt retrieved: #0 connectivity::odbc::ORealObdcDriver::getOdbcFunction (this=0x2bdf350, _nIndex=7) at /home/julien/compile-libreoffice/libo_4_4/connectivity/source/drivers/odbc/ORealDriver.cxx:106 #1 0x00002aaad4b9c41c in connectivity::odbc::OConnection::getOdbcFunction (this=0x2be8fd0, _nIndex=7) at /home/julien/compile-libreoffice/libo_4_4/connectivity/source/drivers/odbc/OConnection.cxx:96 #2 0x00002aaad4b7d615 in connectivity::odbc::OTools::GetInfo (_pConnection=0x2be8fd0, _aConnectionHandle=0x2bed4a0, _nInfo=15, _rValue=@0x7fffffff3adc: 0, _xInterface=uno::Reference to (connectivity::odbc::ODatabaseMetaData *) 0x2bf6dd0) at /home/julien/compile-libreoffice/libo_4_4/connectivity/source/drivers/odbc/OTools.cxx:551 #3 0x00002aaad4b962e2 in connectivity::odbc::ODatabaseMetaData::supportsCoreSQLGrammar (this=0x2bf6dd0) at /home/julien/compile-libreoffice/libo_4_4/connectivity/source/drivers/odbc/ODatabaseMetaData.cxx:1518 #4 0x00002aaac8df975b in dbtools::DatabaseMetaData::supportsPrimaryKeys (this=0x2c6f708) at /home/julien/compile-libreoffice/libo_4_4/connectivity/source/commontools/dbmetadata.cxx:258 #5 0x00002aaacd6f8c7d in dbaui::OTableEditorCtrl::IsPrimaryKeyAllowed (this=0x2c72550) at /home/julien/compile-libreoffice/libo_4_4/dbaccess/source/ui/tabledesign/TEditControl.cxx:1318 The problem is I don't understand how this part works: 550 OTools::ThrowException(_pConnection, 551 (*(T3SQLGetInfo)_pConnection->getOdbcFunction(ODBC3SQLGetInfo))(_aConnectionHandle,_nInfo,&_rValue,sizeof _rValue,&nValueLen), 552 _aConnectionHandle,SQL_HANDLE_DBC,_xInterface); (see http://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/odbc/OTools.cxx#549) Indeed, after having typed several times "s"/"n" in gdb I don't find the function where "_rValue" might be changed. So "_rvalue" stays to 0 and we only have got "SQL_OSC_MINIMUM""
I think I understood how it works. 131 if( ( pODBC3SQLGetInfo = (T3SQLGetInfo)osl_getFunctionSymbol(pODBCso, OUString("SQLGetInfo").pData )) == NULL ) 132 return false; http://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/odbc/OFunctions.cxx#131 which calls sqlliteodbc (on Debian testing, "apt-cache show libsqliteodbc" gives: Package: libsqliteodbc Source: sqliteodbc Version: 0.992-2 ) Retrieving the sources of sqliteodbc (from http://www.ch-werner.de/sqliteodbc/), I got in sqliteodbc.c: 8368 #if (defined(HAVE_UNIXODBC) && (HAVE_UNIXODBC)) || !defined(WINTERFACE) 8369 /** 8370 * Return information about what this ODBC driver supports. 8371 * @param dbc database connection handle 8372 * @param type type of information to be retrieved 8373 * @param val output buffer 8374 * @param valMax length of output buffer 8375 * @param valLen output length 8376 * @result ODBC error code 8377 */ 8378 8379 SQLRETURN SQL_API 8380 SQLGetInfo(SQLHDBC dbc, SQLUSMALLINT type, SQLPOINTER val, SQLSMALLINT valMax, 8381 SQLSMALLINT *valLen) 8382 { 8383 SQLRETURN ret; 8384 8385 HDBC_LOCK(dbc); 8386 ret = drvgetinfo(dbc, type, val, valMax, valLen); 8387 HDBC_UNLOCK(dbc); 8388 return ret; 8389 } 8390 #endif Then 7875 /** 7876 * Internal return information about what this ODBC driver supports. 7877 * @param dbc database connection handle 7878 * @param type type of information to be retrieved 7879 * @param val output buffer 7880 * @param valMax length of output buffer 7881 * @param valLen output length 7882 * @result ODBC error code 7883 */ 7884 7885 static SQLRETURN 7886 drvgetinfo(SQLHDBC dbc, SQLUSMALLINT type, SQLPOINTER val, SQLSMALLINT valMax, 7887 SQLSMALLINT *valLen) 7888 { ... 8026 case SQL_ODBC_SQL_CONFORMANCE: 8027 *((SQLSMALLINT *) val) = SQL_OSC_MINIMUM; 8028 *valLen = sizeof (SQLSMALLINT); 8029 break; Idem in sqlite3odbc.c and sqlite4odbc.c So we retrieved SQL_OSC_MINIMUM whereas we expect SQL_OSC_CORE or SQL_OSC_EXTENDED. (sidenote: OSC = "ODBC SQL CONFORMANCE") Now perhaps the sqliteodbc package fills wrongly SQL_ODBC_SQL_CONFORMANCE part (unless it really doesn't implement primary key whereas there are functions for this) or perhaps we shoudn't call "supportsCoreSQLGrammar" but "supportsMinimumSQLGrammar". Lionel: any thoughts?
Adding self to CC if not already on
I'll quote Christian Werner, maintainer of sqllite odbc: " SQL_OSC_MINIMUM is what SQLite and the driver can provide, higher values would be a cheat. There are other means in the ODBC spec to find out if a primary key is available on a table, think e.g. of SQLPrimaryKeys() "
(In reply to Julien Nabet from comment #7) > I'll quote Christian Werner, maintainer of sqllite odbc: > " > SQL_OSC_MINIMUM is what > SQLite and the driver can provide, higher values would be a cheat. There are > other means in the ODBC spec to find out if a primary key is available on a > table, think e.g. of SQLPrimaryKeys() > " The question is not whether a particular table has a primary key (which is the question SQLPrimaryKeys() answers), but whether the database supports the concept of primary key in general, and (I guess) whether in its DDL SQL (Data Definition Language) it supports the SQL-standard way of adding a primary key. From a pure ODBC point of view, we might be able to get this information from SQLGetInfo(SQL_CREATE_TABLE) or something like that (might be what is meant by SQL_CT_TABLE_CONSTRAINT)... Need to check. Also need to check whether this can map "cleanly" to some JDBC/SDBC interface to go through the layers of abstraction in LibreOffice :) That is probably, one of the "supportsFoo" in https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html References for later usage: ODBC Minimum grammar (no mention of primary key from a cursory glance): http://msdn.microsoft.com/en-us/library/ms711725%28v=vs.85%29.aspx ODBC SQLGetInfo reference: http://msdn.microsoft.com/en-us/library/ms711681%28v=vs.85%29.aspx Haven't found the exact definition of SQL Core / Extended grammar (should be somewhere...) but here's a third-party high-level description I've found: https://www.progress.com/products/datadirect-connect/odbc-drivers/odbc-developer-center/odbc-definitions
For reference the behaviour seems to have changed as of the below commit. I see Lionel is already Cc'd on the bug so I guess he knew that already :) commit 94a07be5d726de71315f47d24ef9003991d201ad Author: Lionel Elie Mamane <lionel@mamane.lu> Date: Thu May 15 12:55:06 2014 +0200 odbc: clean up SQL conformance level tests Change-Id: I9c96ee389a086c76489f99750c2c86b7504cd393
When/if LibreOffice cannot guess (or guesses wrong) about whether a database supports Primary Keys, go to menu "Edit/Database/Advanced Settings" and in tab "Special Settings", check or uncheck "Supports Primary Keys" as appropriate.
In the particular case of SQLite, it seems we are in a bind. According to my understanding, primary key support is in the ODBC core grammar, not the minimal grammar. So LibreOffice is "right" to check for Core grammar support to check for primary key support. On the other hand, (I understand from Christian Werner) SQLite does not support parts of the Core grammar, so it is right to not report Core grammar support. The SQL92 Entry level grammar includes primary key support, so I'm changing LibreOffice to assume primary key support if the ODBC Core _OR_ the SQL92 Entry grammar is supported. It won't help in the case of SQLite because: - the ODBC driver does not support GetInfo(..., SQL_SQL_CONFORMANCE, ...) - I expect (but don't know for sure) that SQLite doesn't support all of the SQL92 entry level grammar anyway, so even if the driver supported the above, it would answer "nope, entry level not supported". It might be relevant to ask Christian Werner confirmation of this point. Julien, you want to do that? As to why it worked before, it was a "happy accident" from a bug in LibreOffice, which the commit 94a07be5d726de71315f47d24ef9003991d201ad fixed: when asked whether the database supported the Core SQL grammar, the ODBC-SDBC driver checked the level of ODBC API compliance (something unrelated!) and really answered whether the driver supports the core *API* conformance, which the SQLite ODBC driver does. So it replied "yes, database supports Core SQL grammar" although IT DID NOT.
Just to be clear, the suggestion to users is to go the "Edit/Database/Advanced Settings" / "Special Settings" / "Supports Primary Keys" route
Migrating Whiteboard tags to Keywords: (bibisected) [NinjaEdit]