Re: [libdbi-users] Problem getting SQLite last sequence value

[Markus H. <mar...@mh...>]

At 2016-07-31 17:19, Tx0 was heard to say:
> Hi,
> 
> I'm the author of Tagsistant, a semantic filesystem for Linux,
> and this is my first post on this list, so I hope this is the
> right place to ask and to not infringe any rule.
> 
> Tagsistant uses a SQL database as its backend, and libDBI to
> abstract the interaction with different RDBMS. Currently, MySQL
> and SQLite are supported. Before using DBI, the SQLite  API
> was directly used.
> 
> I had no problems with both RDBMS up to DBI version 0.8.x.
> With 0.9.x I'm facing a strange behavior in getting the last
> inserted primary key from a table, but with the SQLite backend
> only.
> 
> This is the schema of the objects table in SQLite:
> 
> CREATE TABLE objects (
>    inode integer not null primary key autoincrement,
>    objectname text(255) not null,
>    last_autotag timestamp not null default current_timestamp,
>    checksum text(40) not null default '',
>    symlink text(1024) not null default ''
> );
> 
> If I insert something from the sqlite3 shell, everything works
> fine:
> 
> sqlite> select max(inode) from objects;
> 3
> sqlite> insert into objects (objectname) values ('testobj');
> sqlite> select last_insert_rowid();
> 4
> sqlite> select max(inode) from objects;
> 4
> sqlite>
> 
> When the insert is done inside Tagsistant, the new record properly
> enters the objects table. After that, Tagsistant uses the code
> below to read the last inserted primary key.
> 
> typedef uint32_t tagsistant_inode
> 
> tagsistant_inode
> tagsistant_last_insert_id(dbi_conn conn)
> {
> #if TAGSISTANT_USE_INTERNAL_SEQUENCES
>      tagsistant_inode inode = 0;
> 
>      switch (tagsistant.sql_database_driver) {
>          case TAGSISTANT_DBI_SQLITE_BACKEND:
>              tagsistant_query(
>                  "SELECT cast(last_insert_rowid() as int)",
>                  conn,
>                  tagsistant_return_integer,
>                  &inode);
>              break;
> 
>          case TAGSISTANT_DBI_MYSQL_BACKEND:
>              tagsistant_query(
>                  "SELECT last_insert_id()",
>                  conn,
>                  tagsistant_return_integer,
>                  &inode);
>              break;
>      }
> 
>      return (inode);
> #else
>      return(dbi_conn_sequence_last(conn, NULL));
> #endif
> }
> 
> If I set TAGSISTANT_USE_INTERNAL_SEQUENCES, no useful value is returned
> by dbi_conn_sequence_last(). It's always 1.
> 
> And here is the callback used to load the value, if my custom code is
> used in place of dbi_conn_sequence_last(). Note that the value is not
> returned as an integer, as expected. It's instead a DBI_TYPE_STRING and
> it's always "1".
> 
> int tagsistant_return_integer(void *return_integer, dbi_result result)
> {
>      uint32_t *buffer = (uint32_t *) return_integer;
>      *buffer = 0;
> 
>      unsigned int type = dbi_result_get_field_type_idx(result, 1);
>      if (type == DBI_TYPE_INTEGER) {
>          unsigned int size = dbi_result_get_field_attribs_idx(result, 
> 1);
>          unsigned int is_unsigned = size & DBI_INTEGER_UNSIGNED;
>          size = size & DBI_INTEGER_SIZEMASK;
>          switch (size) {
>              case DBI_INTEGER_SIZE8:
>                  if (is_unsigned)
>                      *buffer = dbi_result_get_ulonglong_idx(result, 1);
>                  else
>                      *buffer = dbi_result_get_longlong_idx(result, 1);
>                  break;
>              case DBI_INTEGER_SIZE4:
>              case DBI_INTEGER_SIZE3:
>                  if (is_unsigned)
>                      *buffer = dbi_result_get_uint_idx(result, 1);
>                  else
>                      *buffer = dbi_result_get_int_idx(result, 1);
>                  break;
>              case DBI_INTEGER_SIZE2:
>                  if (is_unsigned)
>                      *buffer = dbi_result_get_ushort_idx(result, 1);
>                  else
>                      *buffer = dbi_result_get_short_idx(result, 1);
>                  break;
>              case DBI_INTEGER_SIZE1:
>                  if (is_unsigned)
>                      *buffer = dbi_result_get_uchar_idx(result, 1);
>                  else
>                      *buffer = dbi_result_get_char_idx(result, 1);
>                  break;
>          }
>      } else if (type == DBI_TYPE_STRING) {
>          const gchar *int_string = dbi_result_get_string_idx(result, 
> 1);
>          *buffer = atoi(int_string);
>          dbg('s', LOG_INFO, "tagsistant_return_integer called on string
> field %s", int_string);
>      }
> 
>      dbg('s', LOG_INFO, "Returning integer: %d", *buffer);
> 
>      return (0);
> }
> 
> Could this behavior relate to the dbi_conn reference passed? But then,
> why
> it works with MySQL and not with SQLite?
> 
> Or maybe it's the table schema? Then why everything works fine from the
> sqlite3 shell?
> 
> Thank you very much.
> Ciao!

Hi,

unfortunately I didn't find the time to actually reproduce your problem. 
However, I've found the following conspicuous line in dbd_sqlite3.c 
(line 1236 in my copy):

|| strstr(curr_type, "INTEGER PRIMARY KEY") /* BAD BAD HACK */

It is quite possible that the "bad bad" part of this hack came to life 
in your example. Could you please test your code again with a slightly 
modified table definition like this:

    inode integer primary key not null autoincrement,

Please let me know if this helps.

regards,
Markus

-- 
Markus Hoenicka
http://www.mhoenicka.de
AQ score 38