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
------------------------------------------------------------------------------
_______________________________________________
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users
