Re: [libdbi-users] Problem getting SQLite last sequence value
On 2016-08-02 00:44, Markus Hoenicka wrote:
> 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
Hi Markus,
thanks for your reply. I've wrote a small fragment to do some tests.
I've put it on Gist:
https://gist.github.com/StrumentiResistenti/58943f64c7e702dc4f77c15a120619ea
This is the output (I think it's quite self explanatory):
---> QUERY: [create table if not exists objects (
inode integer primary key autoincrement not null,
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 '')]
no tables in statement !
---> QUERY: [insert into objects (objectname) values ('testname1'),
('testname2'), ('testname3')]
no tables in statement !
---> QUERY: [select last_insert_rowid()]
no tables in statement !
<--- callback got a ___STRING___ field: "3"
no tables in statement !
---> QUERY: [select cast(last_insert_rowid() as int)]
no tables in statement !
<--- callback got a ___STRING___ field: "3"
Last inserted sequence number is 3
Clearly there are two problems to solve. The first one is why, inside
Tagsistant, I'm not able to fetch the right number, provided that the
Gist fragment succeeds. The second one is (I suppose) internal to the
DBI driver: why the sequence number is returned as a string instead
of a integer?
I'll address the first one. I hope DBD developers could do something
about the second one.
Btw. at line 1198 of dbd_sqlite3.c there's a printf() call that should
be commented out:
printf("no tables in statement !\n");
It prints this message every time the dbi_conn_ping() function is called
on SQLite.
Thanks again.
Ciao!
--
Tx0 - GNU/Linux hacker
Author of http://www.tagsistant.net/
--
___
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] Problem getting SQLite last sequence value
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,
> );
> break;
>
> case TAGSISTANT_DBI_MYSQL_BACKEND:
> tagsistant_query(
> "SELECT last_insert_id()",
> conn,
> tagsistant_return_integer,
> );
> 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
[libdbi-users] Problem getting SQLite last sequence value
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,
);
break;
case TAGSISTANT_DBI_MYSQL_BACKEND:
tagsistant_query(
"SELECT last_insert_id()",
conn,
tagsistant_return_integer,
);
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!
--
Tx0
Author of http://www.tagsistant.net/
--
___
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users
