Re: [sqlite] SQLite.NET.chm pages are empty
Le 29.04.2013 11:35, Joe Mistachkin a écrit : Mathieu Schroeter wrote: I downloaded the SQLite.NET.chm from the trunk in order to access the .NET documentation but the pages are empty. Actually, this is the result of a Windows security feature that disables downloaded content. To enable it, navigate to the file in Windows Explorer, right-click the file, go to "Properties", and then click the "Unblock" button. Good to know, thank you Regards, Mathieu Schroeter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite.NET.chm pages are empty
Hi, I downloaded the SQLite.NET.chmfrom the trunk [1]in order to access the .NET documentation but the pages are empty. Regards, Mathieu Schroeter [1]: http://system.data.sqlite.org/index.html/doc/trunk/Doc/SQLite.NET.chm?mimetype=application/x-chm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Very specific use of sqlite3_backup_* with main and temp tables between a real and a :memory: database
Hello, I would like more details about the functions sqlite3_backup_*. I am trying to use them in a very specific use case. I have a database with real and temporary tables. For a very specific work, I must load (for a while) the real tables only in memory because it must not affect the other users. For that, I use two sqlite3 handles. The first is on the file, the second is on a ":memory:" database. Then I copy all tables (main + temp) in the ":memory:" database. I switch the handle then the software continues as usual. After the work, I must switch on the real sqlite3 handle. But I must copy the temporary tables from :memory: in the real handle. I use sqlite3_backup_* because there are many data and it must be fast. db1 is the file "base.db" db2 is ":memory:" db 1. base.db has the real tables (main) and the temporary tables (temp) 2. I create a new database with the name ":memory:" 3. I use sqlite3_backup_* in this way : sqlite3_backup_init (db2, "main", db1, "main"); /* blabla */ sqlite3_backup_init (db2, "temp", db1, "temp"); /* blabla */ 4. I create my triggers, busy handler, etc.. on db2 (like for db1 at the beginning). 4. I work with the db2 handle instead of db1 because when the main tables are changed here it must not affect the other users. 5. When the work is done, I must return to the db1 database. But the changes in the temp tables must be kept. The I use: sqlite3_backup_init (db1, "temp", db2, "temp"); And here.. I've many very strange things.. memory corruptions but not always at the same place and in the code which is not related to sqlite... I think that the problem is only sqlite3_backup_init (db1, "temp", db2, "temp"); Because (maybe) the temporary triggers on db1 are now broken... ? I must drop all temporary things in db1 before sqlite3_backup_init() in the case where the tables already exist? And what about my queries? I must finalized them before, and re-prepare.. ? Thank you for your help and sorry for my bad english. Regards, Mathieu Schroeter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lock problem opening a Sqlite db on a Samba/CIFS shared disk
Le 12. 12. 10 20:09, Marco Turco a écrit : > Hi all, > > I am having a problem running my Window Sqlite app on Mac OS X with Wine > emulator. > > It runs well in local but when I try to access to a network disk hosted on > Windows XP > > then a lock error appears. > > I checked on internet about this and as I know Sqlite at this moment doesn't > support the Samba/CIFS disk with reference to the locking system. > > I am really in trouble because I have more than 50 customers with mixed > network (Windows/Mac OS X) having this problem in the next future. Hi, It works fine for me with a *Linux* Samba Server (SMB1). it is very bugged with the SMB1, SMB2 and SMB2.1 provided by Microsoft. Even if the Oplocks are disabled with SMB1. -- Mathieu SCHROETER ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN
Le 20.10.2010 16:44, Pavel Ivanov a écrit :
>> It seems legitimate to use the idx_foobar because it is already
>> sorted.. no?
>
> Yes, it is sorted. So for example you have 2 values and you need to
> put them in order. You know that these values exist somewhere in the
> index in exact order you need. How would you find the order? You'll
> need to scan the whole index to find values you have (you don't know
> where exactly they are in the index) and then you'll know what order
> they have. And you have to repeat this index scan for each value you
> have. Do you think it would be faster than just calling your
> comparison function?
Yes, okay... I was imagining an index like this:
/* for one data in a table */
struct data {
void *value;
...
const struct idx *idx; /* ptr on the entry in 'idx_foobar' */
} *my_data_in_the_table
/* for an index */
struct idx {
struct idx *next;
struct idx *prev;
...
const void *data; /* ptr on 'my_data_in_the_table' */
} *idx_foobar
To fix my problem then I must create my own high level index ...
Thanks everyone!
Mathieu
>
>
> Pavel
>
> On Wed, Oct 20, 2010 at 10:30 AM, Mathieu Schroeter
> <schroe...@epsitec.ch> wrote:
>> Le 20.10.2010 15:51, Drake Wilson a écrit :
>>> Quoth Mathieu Schroeter<schroe...@epsitec.ch>, on 2010-10-20 15:41:51 +0200:
>>>> Thanks for your interesting answer.
>>>>
>>>> The first plan looks the best but it does not explain the ORDER BY.
>>>> If I could done this job with pencil and paper, my way will be:
>>>>
>>>> a) enumerate all entries in lol
>>>> b) for each entry look up a corresponding row in tmp using its
>>>> primary key
>>>> c) the loop over lol is complete, I see that tmp.data uses
>>>> `COLLATE foobar` then I look if an index exists.
>>>> d) Yes then I use idx_foobar instead of many calls on the
>>>> comparison function.
>>>
>>> In step (d), you're doing what? Sorting the resulting rows? How
>>> exactly would you use the index for that?
>>>
>>
>> Well, I don't know how are implemented the indexes. I know only
>> that the index (idx_foobar) is ordered and this one is populated
>> at the same time that the INSERT.
>>
>> CREATE INDEX idx_foobar ON tmp (data COLLATE foobar);
>>
>> INSERT INTO tmp (data) values (CAST(x'0100' AS TEXT));
>> INSERT INTO tmp (data) values (CAST(x'0400' AS TEXT));
>> ...
>>
>> and CAST() is used because I have a BLOB in my table...
>>
>>
>> I would like to save time for this query (no index for lol.id):
>>
>> SELECT data
>> FROM lol INNER JOIN tmp
>>ON lol.id = tmp.id
>> ORDER BY tmp.data COLLATE foobar;
>>
>>
>> Now each row in the result are using the comparison function provided
>> by foobar. But the comparisons were already done when the data were
>> inserted in the table?!?
>>
>> It seems legitimate to use the idx_foobar because it is already
>> sorted.. no?
>>
>>
>> Maybe I've not understood what is an index.
>>
>>
>> Mathieu
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN
Le 20.10.2010 15:51, Drake Wilson a écrit : > Quoth Mathieu Schroeter<schroe...@epsitec.ch>, on 2010-10-20 15:41:51 +0200: >> Thanks for your interesting answer. >> >> The first plan looks the best but it does not explain the ORDER BY. >> If I could done this job with pencil and paper, my way will be: >> >> a) enumerate all entries in lol >> b) for each entry look up a corresponding row in tmp using its >> primary key >> c) the loop over lol is complete, I see that tmp.data uses >> `COLLATE foobar` then I look if an index exists. >> d) Yes then I use idx_foobar instead of many calls on the >> comparison function. > > In step (d), you're doing what? Sorting the resulting rows? How > exactly would you use the index for that? > Well, I don't know how are implemented the indexes. I know only that the index (idx_foobar) is ordered and this one is populated at the same time that the INSERT. CREATE INDEX idx_foobar ON tmp (data COLLATE foobar); INSERT INTO tmp (data) values (CAST(x'0100' AS TEXT)); INSERT INTO tmp (data) values (CAST(x'0400' AS TEXT)); ... and CAST() is used because I have a BLOB in my table... I would like to save time for this query (no index for lol.id): SELECT data FROM lol INNER JOIN tmp ON lol.id = tmp.id ORDER BY tmp.data COLLATE foobar; Now each row in the result are using the comparison function provided by foobar. But the comparisons were already done when the data were inserted in the table?!? It seems legitimate to use the idx_foobar because it is already sorted.. no? Maybe I've not understood what is an index. Mathieu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN
Le 20.10.2010 14:14, Igor Tandetnik a écrit : > Mathieu Schroeter<schroe...@epsitec.ch> wrote: >> Le 20.10.2010 12:32, Mathieu Schroeter a écrit : >>> I've made a small program with this output (please, look at the >>> attached C code _first_): >> >> Mmmh.. sorry but the previous attached code has at least one error >> and some parts commented. And now I see that without EXPLAIN QUERY >> PLAN, the comparison callback is called with the second query. >> >> There is way in order to have the second query using the index >> with ORDER BY? It is strange that it is not the case.. > > Think about how you, a human, would execute such a query with pencil > and paper. There is no index on lol. So you could a) enumerate all > entries in lol, b) for each entry look up a corresponding row in tmp > using its primary key, and c) at the end, after the loop over lol is > complete, sort all records obtained in step b. > > Or, you could a) enumerate all records in tmp in the correct order > using the index, and b) for each tmp record, scan through all entries > in lol to see if the ID appears there (remember, there are no indexes > on lol, full scan is the only option). > > Which plan would you choose? Thanks for your interesting answer. The first plan looks the best but it does not explain the ORDER BY. If I could done this job with pencil and paper, my way will be: a) enumerate all entries in lol b) for each entry look up a corresponding row in tmp using its primary key c) the loop over lol is complete, I see that tmp.data uses `COLLATE foobar` then I look if an index exists. d) Yes then I use idx_foobar instead of many calls on the comparison function. Why not? The way used by SQLite looks like a) enumerate all entries in lol b) for each entry look up a corresponding row in tmp using its primary key c) the loop over lol is complete, there is `COLLATE foobar` then I call the cmp function... Mathieu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN
Le 20.10.2010 12:32, Mathieu Schroeter a écrit :
I've made a small program with this output (please, look at
the attached C code _first_):
Mmmh.. sorry but the previous attached code has at least one error
and some parts commented. And now I see that without EXPLAIN QUERY PLAN,
the comparison callback is called with the second query.
There is way in order to have the second query using the index with
ORDER BY? It is strange that it is not the case..
Thanks,
Mathieu SCHROETER
#include
#include
#include "sqlite3.h"
static int
cmp (void *data,
int len1, const void *d1,
int len2, const void *d2)
{
printf (".");
return -memcmp (d1, d2, 2);
}
int
main (void)
{
sqlite3 *db;
sqlite3_stmt *stmt;
char *err = NULL;
unlink ("./tmp.db");
sqlite3_open ("./tmp.db", );
sqlite3_create_collation (db, "foobar", SQLITE_UTF8, NULL, cmp);
sqlite3_exec (db, "CREATE TABLE tmp (id INTEGER PRIMARY KEY AUTOINCREMENT,
data BLOB);"
"CREATE TABLE lol (id INTEGER);"
"CREATE INDEX idx_foobar ON tmp (data COLLATE foobar);",
NULL, NULL, );
printf ("insert into tmp\n");
sqlite3_exec (db, "INSERT INTO tmp (data) values (CAST(x'0100' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0400' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0050' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'1800' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0090' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0105' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0001' AS TEXT));",
NULL, NULL, );
printf ("\ninsert into lol\n");
sqlite3_exec (db, "INSERT INTO lol (id) values (1);"
"INSERT INTO lol (id) values (2);"
"INSERT INTO lol (id) values (3);"
"INSERT INTO lol (id) values (4);"
"INSERT INTO lol (id) values (5);"
"INSERT INTO lol (id) values (6);"
"INSERT INTO lol (id) values (7);"
"INSERT INTO lol (id) values (8);",
NULL, NULL, );
if (err)
{
fprintf (stderr, "%s\n", err);
sqlite3_free (err);
goto out;
}
sqlite3_prepare_v2 (db, "EXPLAIN QUERY PLAN "
"SELECT data "
"FROM ("
"SELECT id AS id0 "
"FROM tmp "
") INNER JOIN tmp "
"ON id0 = tmp.id "
"ORDER BY data COLLATE foobar;",
-1, , NULL);
printf ("step for first query\n");
while (sqlite3_step (stmt) == SQLITE_ROW)
printf ("%10s | %10s : %s\n",
sqlite3_column_text (stmt, 0),
sqlite3_column_text (stmt, 1),
sqlite3_column_text (stmt, 2));
sqlite3_finalize (stmt);
printf ("\nstep for second query\n");
sqlite3_prepare_v2 (db, "EXPLAIN QUERY PLAN "
"SELECT data "
"FROM ("
"SELECT id AS id0 "
"FROM lol "
") INNER JOIN tmp "
"ON id0 = tmp.id "
"ORDER BY data COLLATE foobar;",
-1, , NULL);
while (sqlite3_step (stmt) == SQLITE_ROW)
printf ("%10s | %10s : %s\n",
sqlite3_column_text (stmt, 0),
sqlite3_column_text (stmt, 1),
sqlite3_column_text (stmt, 2));
sqlite3_finalize (stmt);
sqlite3_close (db);
out:
unlink ("./tmp.db");
return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN
tested with sqlite-3.7.3
Hello,
I'm trying to use the collations with BLOB because I am sorting
something else that text. For example I have some types which
can not be used as-is with SQLite because these types can be
more complex. Then I have a collation which provides the right
order. But my tables have millions of BLOB to sort and without
indexes it is very very slow.
I'm trying to use an index + collation with my BLOBs but there
is something strange. It seems that my index on the collation
is not used accordingly to EXPLAIN QUERY PLAN. But it seems that
the callback for the comparison is not called then is suggests
that the index is used.
I've made a small program with this output (please, look at
the attached C code _first_):
insert into tmp
.
insert into lol
step for first query
0 | 1 : TABLE tmp WITH INDEX idx_foobar ORDER BY
1 | 0 : TABLE tmp USING PRIMARY KEY
step for second query
0 | 0 : TABLE lol
1 | 1 : TABLE tmp USING PRIMARY KEY
There are two queries. Both provide the same output but the second
uses ID from the table 'lol' for an INNER JOIN with the table tmp.
The first query is stupid (it uses the tmp table with the JOIN on
itself). It is just an example.
A dot [.] is printed every time that the collation is computing a
comparison. In the case, the comparisons are done with the INSERTs.
My question:
Why EXPLAIN QUERY PLAN does not show idx_foobar in the second query?
Is the index used? It seems that this index is used, because otherwise
more dot [.] should be printed between "step for first " and
"step for second ". No?
Thanks
Regards,
Mathieu SCHROETER
#include
#include
#include "sqlite3.h"
static int
cmp (void *data,
int len1, const void *d1,
int len2, const void *d2)
{
printf (".");
return -memcmp (d1, d2, 4);
}
int
main (void)
{
sqlite3 *db;
sqlite3_stmt *stmt;
char *err = NULL;
unlink ("./tmp.db");
sqlite3_open ("./tmp.db", );
sqlite3_create_collation (db, "foobar", SQLITE_UTF8, NULL, cmp);
sqlite3_exec (db, "CREATE TABLE tmp (id INTEGER PRIMARY KEY AUTOINCREMENT,
data BLOB);"
"CREATE TABLE lol (id INTEGER);"
"CREATE INDEX idx_foobar ON tmp (data COLLATE foobar);",
NULL, NULL, );
printf ("insert into tmp\n");
sqlite3_exec (db, "INSERT INTO tmp (data) values (CAST(x'0100' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0400' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0050' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'1800' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0090' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0105' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0001' AS TEXT));",
NULL, NULL, );
printf ("\ninsert into lol\n");
sqlite3_exec (db, "INSERT INTO lol (id) values (1);"
"INSERT INTO lol (id) values (2);"
"INSERT INTO lol (id) values (3);"
"INSERT INTO lol (id) values (4);"
"INSERT INTO lol (id) values (5);"
"INSERT INTO lol (id) values (6);"
"INSERT INTO lol (id) values (7);"
"INSERT INTO lol (id) values (8);",
NULL, NULL, );
if (err)
{
fprintf (stderr, "%s\n", err);
sqlite3_free (err);
goto out;
}
sqlite3_prepare_v2 (db, "EXPLAIN QUERY PLAN "
"SELECT data "
"FROM ("
"SELECT id AS id0 "
"FROM tmp "
") INNER JOIN tmp "
"ON id0 = tmp.id "
"ORDER BY data COLLATE foobar;",
-1, , NULL);
printf ("step for first query\n");
/*while (sqlite3_step (stmt) == SQLITE_ROW)
printf ("%10s | %10s : %s\n",
sqlite3_column_text (stmt, 0),
sqlite3_column_text (stmt, 1),
sqlite3_column_text (stmt, 2));*/
//sqlite3_finalize (stmt);
printf ("\nstep for second query\n");
sqlite3_prepare_v2 (db, "EXPLAIN QUERY PLAN "
"SELECT data "
"FROM ("
"SELECT id AS id0 "
"FRO
Re: [sqlite] bind for sub-queries
Jay A. Kreibich a écrit : > On Sun, Jan 03, 2010 at 10:47:01AM +0100, Mathieu SCHROETER scratched on the > wall: >> Hello, >> >> I've a simple question about the bind functions. If it can >> be realistic to have in the future, a way to bind a sub-query >> in a query? > > Doubtful. Sub-queries are a syntactical thing and exist because of > limitations in the SQL language. From the perspective of the > database engine, sub-queries are merged into the main query as one big > operation. > > Depending on your situation, you might be able to use views, temporary > views, or temporary tables. Well.. thanks for the answer! Mathieu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] bind for sub-queries
Hello, I've a simple question about the bind functions. If it can be realistic to have in the future, a way to bind a sub-query in a query? I create queries with a number of sub-queries which depends of the user. Then, if all sub-queries can be prepared independently of the main query, it suggests that a significant time would be saved. Regards, Mathieu SCHROETER ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
