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