Bug 82688 - ODBC to SQLite3, table Design View does not offer Primary Key
Summary: ODBC to SQLite3, table Design View does not offer Primary Key
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) Master
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
Keywords: bibisected, bisected, regression
Depends on:
Reported: 2014-08-16 00:41 UTC by Terrence Enger
Modified: 2022-03-03 11:29 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Note You need to log in before you can comment on or make changes to this bug.
Description Terrence Enger 2014-08-16 00:41:14 UTC
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

built on debian-wheezy 64-bit and running chroot'd to debian-sid.  The
database driver is from package libsqliteodbc version 0.992-2.
Comment 1 Terrence Enger 2014-08-16 00:43:05 UTC
git bisect tells me:

    423a84c4f7068853974887d98442bc2a2d0cc91b is the first bad commit

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
Comment 2 Alex Thurgood 2014-12-15 14:01:22 UTC
Confirming - I had noticed this too.
Comment 3 Lionel Elie Mamane 2014-12-16 13:30:22 UTC
Nothing in the range fe1e1d1292973211f0edfa64c05279ca59634652..c15927f20d4727c3b8de68497b6949e72f9e6e9e looks relevant...
Comment 4 Julien Nabet 2014-12-20 14:09:18 UTC
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""
Comment 5 Julien Nabet 2014-12-20 16:32:55 UTC
I think I understood how it works.
    131     if( ( pODBC3SQLGetInfo      =   (T3SQLGetInfo)osl_getFunctionSymbol(pODBCso, OUString("SQLGetInfo").pData )) == NULL )
    132         return false;
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  */
   8381            SQLSMALLINT *valLen)
   8382 {
   8383     SQLRETURN ret;
   8385     HDBC_LOCK(dbc);
   8386     ret = drvgetinfo(dbc, type, val, valMax, valLen);
   8387     HDBC_UNLOCK(dbc);
   8388     return ret;
   8389 }
   8390 #endif

   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  */
   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?
Comment 6 Alex Thurgood 2015-01-03 17:40:08 UTC
Adding self to CC if not already on
Comment 7 Julien Nabet 2015-01-05 06:53:31 UTC
I'll quote Christian Werner, maintainer of sqllite odbc:
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()
Comment 8 Lionel Elie Mamane 2015-01-05 07:41:37 UTC
(In reply to Julien Nabet from comment #7)
> I'll quote Christian Werner, maintainer of sqllite odbc:
> "
> 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
Comment 9 Matthew Francis 2015-02-20 02:33:18 UTC
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
Comment 10 Lionel Elie Mamane 2015-02-20 04:03:08 UTC
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.
Comment 11 Lionel Elie Mamane 2015-02-20 04:29:40 UTC
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.
Comment 12 Lionel Elie Mamane 2015-02-20 04:37:21 UTC
Just to be clear, the suggestion to users is to go the
  "Edit/Database/Advanced Settings" / "Special Settings" / "Supports Primary Keys"
Comment 13 Robinson Tryon (qubit) 2015-12-15 11:03:15 UTC
Migrating Whiteboard tags to Keywords: (bibisected)