Re: [sqlite] VACUUM command not working
On Thu, Aug 18, 2011 at 11:19 PM, Tarun wrote:
> Hi All,
>
> I am running program in which I have created SQLite DB file using:
>
> sqlite3async_initialize()
>
The easiest solution to your problem might be simply to not use the
test_async.c module. That module was created long ago to work around issues
with fsync(). These days, using "PRAGMA journal_mode=WAL" does a better job
of overcoming the same issue.
So I suggest that you drop the test_async.c module and instead enable
"PRAGMA journal_mode=WAL". See if that doesn't work out better for you.
And if not, we'll take up the problem from that point
> sqlite3_open_v2()
>
> Then created one table into SQLiteDB by using sqlite3_exec() in which
> I passed SQL command of creating table.
>
> Then I inserted records into table using INSERT sql command in
> sqlite3_exec()
>
> Then I did sqlite3async_run() to commit all write request from
> pending queue to SQLite DB file on disk.
>
> Then I did deletion of records from table using DELETE sql query in
> sqlite3_exec()
>
> Then I ran VACUUM command this way:
>
> sql = "VACUUM;";
> rc = sqlite3_exec(asyncsql.pdb, sql, NULL, 0, &zErrMsg);
>
>
>
> After successful running of above command I checked size of my
> SQLiteDB file using system ("ls -lrt");
>
> NOTE: No compilations issue. async IO code file and sqlite shared
> library linked properly. All sqlite3_exec() ran successfully with
> SQLITE_OK, no error code returned.
>
> Given below is output shown:
>
> [tarun@emu async_prg]$ ./sqlite_async_compKey vacum1.db 1000
>
> 1313661267 1313661269 1313661316
> system1-> Size of file after records insertion
> -rw-r--r--. 1 tarun tarun 559104 Aug 18 15:25 vacum1.db
>
> system2 -> Size of file after records deletion
> -rw-r--r--. 1 tarun tarun 559104 Aug 18 15:25 vacum1.db
>
> VACUUM SQLite API SUCCESS
> system3 -> Size of file after VACUUM command
> -rw-r--r--. 1 tarun tarun 559104 Aug 18 15:25 vacum1.db
>
> My problem is why SQLiteDB file size is not reduced after VACUUM has
> been run. Please help to get VACUUM running in my case.
> It would be really helpful if anyone can share working demo program
> using VACUUM.
> Waiting for your response, I am stuck in my work.
> --
> Thanks and Regards,
> - Tarun Thakur
> Module Lead
> NEC HCL System Technologies, Noida
> www.nechclst.in
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VACUUM command not working
Hi All,
I am running program in which I have created SQLite DB file using:
sqlite3async_initialize()
sqlite3_open_v2()
Then created one table into SQLiteDB by using sqlite3_exec() in which
I passed SQL command of creating table.
Then I inserted records into table using INSERT sql command in sqlite3_exec()
Then I did sqlite3async_run() to commit all write request from
pending queue to SQLite DB file on disk.
Then I did deletion of records from table using DELETE sql query in
sqlite3_exec()
Then I ran VACUUM command this way:
sql = "VACUUM;";
rc = sqlite3_exec(asyncsql.pdb, sql, NULL, 0, &zErrMsg);
After successful running of above command I checked size of my
SQLiteDB file using system ("ls -lrt");
NOTE: No compilations issue. async IO code file and sqlite shared
library linked properly. All sqlite3_exec() ran successfully with
SQLITE_OK, no error code returned.
Given below is output shown:
[tarun@emu async_prg]$ ./sqlite_async_compKey vacum1.db 1000
1313661267 1313661269 1313661316
system1-> Size of file after records insertion
-rw-r--r--. 1 tarun tarun 559104 Aug 18 15:25 vacum1.db
system2 -> Size of file after records deletion
-rw-r--r--. 1 tarun tarun 559104 Aug 18 15:25 vacum1.db
VACUUM SQLite API SUCCESS
system3 -> Size of file after VACUUM command
-rw-r--r--. 1 tarun tarun 559104 Aug 18 15:25 vacum1.db
My problem is why SQLiteDB file size is not reduced after VACUUM has
been run. Please help to get VACUUM running in my case.
It would be really helpful if anyone can share working demo program
using VACUUM.
Waiting for your response, I am stuck in my work.
--
Thanks and Regards,
- Tarun Thakur
Module Lead
NEC HCL System Technologies, Noida
www.nechclst.in
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EINTR and write() in os_unix.c
On Thu, Aug 18, 2011 at 5:27 PM, David Garfield < garfi...@irving.iisd.sra.com> wrote: > I think you are right, that it is too easy, at least on its own. You > also should account for partial writes. > i currently account for partial writes but simply report them as errors (i have no recovery/retry strategy, which is what i'm aiming for here). With Pavel's information i think i now have what i need to (confidently) add this. i was missing the part about EINTR only happening if write() writes 0 bytes. i thought it could also happen on a partial write. So far i've been checking for partial writes but eliding signal checks for platform portability, but now it's reached a point where the code is stable but potential corruption cases which could be largely worked around via a retry-on-interrupt strategy. (i'm also investigating a journaling approach.) > I think the general rule is: if it wrote anything, it tells you how > much it wrote, which can be everything you asked it to write, or less. > If it wrote nothing, it usually returns -1 and sets errno based on why > it failed. You then can keep retrying if the error is EINTR or > EAGAIN, or maybe some others. > That's the key semantic i was missing. > I HOPE that an interrupt in a large interrupted write will NOT return > EINTR, because if it does, the partial write is screwed up. > i hope so, too! :-D Thank you for your insights! -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EINTR and write() in os_unix.c
On Thu, Aug 18, 2011 at 5:12 PM, Pavel Ivanov wrote: > to proceed. BTW, if signal will interrupt write() after it has written > part of the data already you won't get EINTR, you will get successful > result just with amount written less than you asked for. > AHA! That's a little gem i didn't know. That clarifies things greatly. Thank you :). -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EINTR and write() in os_unix.c
I think you are right, that it is too easy, at least on its own. You also should account for partial writes. I think the general rule is: if it wrote anything, it tells you how much it wrote, which can be everything you asked it to write, or less. If it wrote nothing, it usually returns -1 and sets errno based on why it failed. You then can keep retrying if the error is EINTR or EAGAIN, or maybe some others. I HOPE that an interrupt in a large interrupted write will NOT return EINTR, because if it does, the partial write is screwed up. Stephan Beal writes: > On Thu, Aug 18, 2011 at 4:39 PM, Pavel Ivanov wrote: > > > If you don't want your library behavior to be changed when process > > receives some signals then you have to use such or some similar > > approach. > > > > i understand the reason for it, but before i go adding this to my storage > API i just wanted to ask for some opinions as to whether this approach is > generically both technically and philosophically sound, or whether the fact > that it works at all relies on other voodoo deep within os_unix. > > The main thing that troubles me is that this approach just seems "too easy" > given this particular problem, and i'm suspicious because of that. It > _seems_ to be exactly what i'm looking for, but i also know that my > knowledge for the effect of EINTR on system calls is too constrained for me > to have my own educated, confident opinion on it. > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > ___ > 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] EINTR and write() in os_unix.c
> i understand the reason for it, but before i go adding this to my storage > API i just wanted to ask for some opinions as to whether this approach is > generically both technically and philosophically sound, or whether the fact > that it works at all relies on other voodoo deep within os_unix. There's no voodoo in this approach. Look at it from the other point of view. If your library needs to write exactly n bytes but write() returned that it has written m bytes which is less than n what will you do? You will do the same approach - simple retry. Both situations (with signal interruption and with incomplete write) mean the same - there was no error of any kind, just technically write() wasn't able to proceed. BTW, if signal will interrupt write() after it has written part of the data already you won't get EINTR, you will get successful result just with amount written less than you asked for. Pavel On Thu, Aug 18, 2011 at 10:46 AM, Stephan Beal wrote: > On Thu, Aug 18, 2011 at 4:39 PM, Pavel Ivanov wrote: > >> If you don't want your library behavior to be changed when process >> receives some signals then you have to use such or some similar >> approach. >> > > i understand the reason for it, but before i go adding this to my storage > API i just wanted to ask for some opinions as to whether this approach is > generically both technically and philosophically sound, or whether the fact > that it works at all relies on other voodoo deep within os_unix. > > The main thing that troubles me is that this approach just seems "too easy" > given this particular problem, and i'm suspicious because of that. It > _seems_ to be exactly what i'm looking for, but i also know that my > knowledge for the effect of EINTR on system calls is too constrained for me > to have my own educated, confident opinion on it. > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > ___ > 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] PHP, SQLite3 object API, SQLite3::escapeString
I'd guess it was for escaping strings used to build SQL statements by concatenation rather than using prepared statements and binding. On 8/18/2011 7:12 AM, Simon Slavin wrote: > ... > The SQLite3 object API for PHP includes a function SQLite3::escapeString . > The documentation for it doesn't explain what it's of or when to use it. ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EINTR and write() in os_unix.c
On Thu, Aug 18, 2011 at 4:39 PM, Pavel Ivanov wrote: > If you don't want your library behavior to be changed when process > receives some signals then you have to use such or some similar > approach. > i understand the reason for it, but before i go adding this to my storage API i just wanted to ask for some opinions as to whether this approach is generically both technically and philosophically sound, or whether the fact that it works at all relies on other voodoo deep within os_unix. The main thing that troubles me is that this approach just seems "too easy" given this particular problem, and i'm suspicious because of that. It _seems_ to be exactly what i'm looking for, but i also know that my knowledge for the effect of EINTR on system calls is too constrained for me to have my own educated, confident opinion on it. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EINTR and write() in os_unix.c
If you don't want your library behavior to be changed when process
receives some signals then you have to use such or some similar
approach.
Pavel
On Thu, Aug 18, 2011 at 9:38 AM, Stephan Beal wrote:
> Hi, sqlite3 hackers,
>
> i'm taking a look at os_unix.c:
>
> do{ got = osPwrite(id->h, pBuf, cnt, offset); }while( got<0 && errno==EINTR
> );
>
> and i just have to ask: do you Unix signal experts out there (a group to
> which i do not belong, by the way) feel that this is a generically useful
> approach to solving the problem of an interrupted write()? i ask because i
> have a library in which i could really use this, but i don't know enough
> about the semantics of interrupted system calls to know if this is really a
> kosher general-purpose approach or if it's a questionable hack which we kids
> should not try at home.
>
> :-?
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> 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] PHP, SQLite3 object API, SQLite3::escapeString
Recent versions of PHP and the SQLite3 API. No general problems with the system. The SQLite3 object API for PHP includes a function SQLite3::escapeString . The documentation for it doesn't explain what it's of or when to use it. My initial guess was that it should be used for entire SQLite commands, but that was wrong because it doubles quotes. My second guess was that it is for strings which would be bound as string values but there's a particular method used to do that so it seems strange to split the 'escapeString' functionality out to a different method. I know it's not part of the SQLite3 C interface but something that was added by the PHP team. Nevertheless I'm hoping someone on this list understands it and can explain it to me, ideally with a small piece of example code which needs it to work properly. Any help much appreciated. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggest some Tools for Sqlite
Hi, >> I hope there should be some good Free or License Tools for using Sqlite. I recently,( two days back) went through a lot GUIs for Windows. I have it narrowed down to four from the list... http://sqlite.com/cvstrac/wiki?p=ManagementTools 1: SQLite Expert Personal 3 <-- my preferred one 2: The add-in for Firefox <-- convenient if I have the browser open 3: Sqliteman <-- very good, not quite as full-featured as #1 4: SQLiteSpy <-- good, not quite as full-featured as #1 I went for the SQLite Expert Personal 3 as it allows Table name changing and also DataType changes on a loaded and active Table. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] EINTR and write() in os_unix.c
Hi, sqlite3 hackers,
i'm taking a look at os_unix.c:
do{ got = osPwrite(id->h, pBuf, cnt, offset); }while( got<0 && errno==EINTR
);
and i just have to ask: do you Unix signal experts out there (a group to
which i do not belong, by the way) feel that this is a generically useful
approach to solving the problem of an interrupted write()? i ask because i
have a library in which i could really use this, but i don't know enough
about the semantics of interrupted system calls to know if this is really a
kosher general-purpose approach or if it's a questionable hack which we kids
should not try at home.
:-?
--
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANALYZE necessary after database upgrade?
On 18 Aug 2011, at 2:03pm, Kristoffer Danielsson wrote: > Hi, The problem is that the database is around 100 MB large (the error goes > away if I remove unimportant data). Also, it contains data I'd like to keep > private. I do have a specific select query that produces what I believe is a > data error (tested in the latest SQLite version). First run an integrity check on the database: http://www.sqlite.org/pragma.html#pragma_integrity_check Then if that doesn't spot any problems ... Make a copy of the database, then working with the copy, delete data which doesn't matter to your query, leaving just a small database with perhaps one or two rows which demonstrate the problem. If you can do this, you can then run the SQL command 'VACUUM' on the resulting database and this will dramatically reduce the file size. Once you'd done this, test that your demonstration of the problem still works. Then you might think about either anonymising the data so it doesn't contain anything sensitive, or sending the small file to Doctor Hipp with an explanation of the problem. On 18 Aug 2011, at 2:10pm, Kristoffer Danielsson wrote: > http://www.mailinglistarchive.com/html/sqlite-users@sqlite.org/2011-04/msg00315.html Oh. Those two queries could easily return different rows if you have a NULL in the JOINing columns. Any chance of that ? Otherwise, identify which query is returning incorrect data and break it down to see which clause is causing the problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANALYZE necessary after database upgrade?
On Thu, Aug 18, 2011 at 9:03 AM, Kristoffer Danielsson < kristoffer.daniels...@live.se> wrote: > > Hi, The problem is that the database is around 100 MB large (the error goes > away if I remove unimportant data). Also, it contains data I'd like to keep > private. I do have a specific select query that produces what I believe is a > data error (tested in the latest SQLite version). Can I zip the database and > send it to the sqlite team for analysis? > Please send a link were we can download the database, and the original text of the queries you think are incorrect to "supp...@sqlite.org". Tnx. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANALYZE necessary after database upgrade?
I did actually report this before, but nobody answered:http://www.mailinglistarchive.com/html/sqlite-users@sqlite.org/2011-04/msg00315.html As you can see, the query does not contain a LIMIT clause. > From: d...@sqlite.org > Date: Thu, 18 Aug 2011 09:00:23 -0400 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ANALYZE necessary after database upgrade? > > On Thu, Aug 18, 2011 at 8:50 AM, Kristoffer Danielsson < > kristoffer.daniels...@live.se> wrote: > > > > > > > > > > > I have noticed that certain (complex) select queries return unexpected data > > (missing rows) on my upgraded SQLite databases.My guess is that the > > optimizer makes an erroneous decision on some index (I'm using both > > sqlite_stat1 and sqlite_stat2). Is this a reasonable guess? Is it necessary > > to run the ANALYZE command after upgrading (altering table columns etc) a > > database? Thanks! > > > > SQLite should *never* return an incorrect answer because of a failure to > ANALYZE. All ANALYZE should do is make the answer come back faster. > > Note that some non-deterministic queries (such as using a LIMIT without an > ORDER BY) might return different results after ANALYZE because it chooses a > different query plan. But in cases like this, that is not an error - the > use of LIMIT without an ORDER BY gives an undefined result. > > If you find a case where SQLite is giving an incorrect result, please send > us details so that we can track down and fix the problem. > > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] ANALYZE necessary after database upgrade?
Hi, The problem is that the database is around 100 MB large (the error goes away if I remove unimportant data). Also, it contains data I'd like to keep private. I do have a specific select query that produces what I believe is a data error (tested in the latest SQLite version). Can I zip the database and send it to the sqlite team for analysis? > From: slav...@bigfraud.org > Date: Thu, 18 Aug 2011 13:57:31 +0100 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ANALYZE necessary after database upgrade? > > > On 18 Aug 2011, at 1:50pm, Kristoffer Danielsson wrote: > > > I have noticed that certain (complex) select queries return unexpected data > > (missing rows) on my upgraded SQLite databases. > > What do you mean by 'upgraded' ? > > > My guess is that the optimizer makes an erroneous decision on some index > > (I'm using both sqlite_stat1 and sqlite_stat2). Is this a reasonable guess? > > Depends what you mean by 'missing rows'. If you think SQLite is giving you > /wrong/ information, please describe it in more detail, showing both the > output of the query you think is wrong, and some other output from the > database showing why you think it's wrong. > > > Is it necessary to run the ANALYZE command after upgrading (altering table > > columns etc) a database? Thanks! > > ANALYZE just helps SQLite decide what the fastest way to do something is. It > should never change which rows are changed or returned. So it will > definitely not fix a data problem. > > Simon. > ___ > 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] ANALYZE necessary after database upgrade?
On Thu, Aug 18, 2011 at 8:50 AM, Kristoffer Danielsson < kristoffer.daniels...@live.se> wrote: > > > > > I have noticed that certain (complex) select queries return unexpected data > (missing rows) on my upgraded SQLite databases.My guess is that the > optimizer makes an erroneous decision on some index (I'm using both > sqlite_stat1 and sqlite_stat2). Is this a reasonable guess? Is it necessary > to run the ANALYZE command after upgrading (altering table columns etc) a > database? Thanks! > SQLite should *never* return an incorrect answer because of a failure to ANALYZE. All ANALYZE should do is make the answer come back faster. Note that some non-deterministic queries (such as using a LIMIT without an ORDER BY) might return different results after ANALYZE because it chooses a different query plan. But in cases like this, that is not an error - the use of LIMIT without an ORDER BY gives an undefined result. If you find a case where SQLite is giving an incorrect result, please send us details so that we can track down and fix the problem. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANALYZE necessary after database upgrade?
On 18 Aug 2011, at 1:50pm, Kristoffer Danielsson wrote: > I have noticed that certain (complex) select queries return unexpected data > (missing rows) on my upgraded SQLite databases. What do you mean by 'upgraded' ? > My guess is that the optimizer makes an erroneous decision on some index (I'm > using both sqlite_stat1 and sqlite_stat2). Is this a reasonable guess? Depends what you mean by 'missing rows'. If you think SQLite is giving you /wrong/ information, please describe it in more detail, showing both the output of the query you think is wrong, and some other output from the database showing why you think it's wrong. > Is it necessary to run the ANALYZE command after upgrading (altering table > columns etc) a database? Thanks! ANALYZE just helps SQLite decide what the fastest way to do something is. It should never change which rows are changed or returned. So it will definitely not fix a data problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 44, Issue 18
On 18 Aug 2011, at 1:30pm, Lisa Davey wrote: > Can you remove me from this mailing list? > > Lisa > > On Aug 18, 2011, at 8:00 AM, sqlite-users-requ...@sqlite.org wrote: > >> Send sqlite-users mailing list submissions to >> sqlite-users@sqlite.org >> >> To subscribe or unsubscribe via the World Wide Web, visit >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> or, via email, send a message with subject or body 'help' to >> sqlite-users-requ...@sqlite.org Lisa, look at the text you posted. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANALYZE necessary after database upgrade?
I have noticed that certain (complex) select queries return unexpected data (missing rows) on my upgraded SQLite databases.My guess is that the optimizer makes an erroneous decision on some index (I'm using both sqlite_stat1 and sqlite_stat2). Is this a reasonable guess? Is it necessary to run the ANALYZE command after upgrading (altering table columns etc) a database? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggest some Tools for Sqlite
On 18/08/2011, at 5:44 PM, Madhankumar Rajaram wrote: > Kindly suggest the best Free / Licence Tool for using Sqlite I've tabulated a comparison of several SQLite admin and data entry tools here: http://www.barefeetware.com/sqlite/compare/?ml Mainly tools for Mac, but a few are cross platform. Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] type of a value bound by sqlite3_bind_blob ()?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 08/17/2011 09:25 PM, Ivan Shmakov wrote:
> Somehow, I've assumed that sqlite3_bind_blob () will bind a
> parameter to a blob.
It does. There are no affinity rules that will cause otherwise.
There are some operations that cause blobs to be silently promoted to
strings. IMHO these are egregious errors in SQLite since a bucket of
bytes cannot be turned into characters unless you know the encoding
which SQLite doesn't. Here is an example:
sqlite> select X'616263' || 'd';
abcd
sqlite> select typeof(X'616263' || 'd');
text
Consequently if you had a trigger pulling a stunt like this, your code
could try to insert a blob and silently (wrongly) end up with a string.
SQLite won't even complain if the blob isn't a valid text encoding
producing an invalid string.
sqlite> select X'8989' || 'd';
��d
sqlite> select typeof(X'8989' || 'd');
text
sqlite> select hex(X'8989' || 'd');
898964
sqlite> create table x(y);
sqlite> INSERT INTO x values(X'8989' || 'd');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x(y);
INSERT INTO "x" VALUES('��d');
COMMIT;
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk5NBUwACgkQmOOfHg372QQo1gCfXwu15/F97xAqi9VtZq/sjwSb
yyMAnihqCFZpRx1BxwkLF3o6miXiNXWm
=Ky47
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 44, Issue 18
Can you remove me from this mailing list? Lisa On Aug 18, 2011, at 8:00 AM, sqlite-users-requ...@sqlite.org wrote: > Send sqlite-users mailing list submissions to >sqlite-users@sqlite.org > > To subscribe or unsubscribe via the World Wide Web, visit >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > or, via email, send a message with subject or body 'help' to >sqlite-users-requ...@sqlite.org > > You can reach the person managing the list at >sqlite-users-ow...@sqlite.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of sqlite-users digest..." > Today's Topics: > > 1. Re: How to retrieve the Specil characters from the Sql lite > (Igor Tandetnik) > 2. Re: How to retrieve the Specil characters from the Sql lite > (Simon Slavin) > 3. Re: aggregate by break in sequence (Anantha Prasad) > 4. Re: Official replacement for sqlite3_expire (Roger Binns) > 5. Re: forcing X'' literals in sqlite3's .dump? (Roger Binns) > 6. type of a value bound by sqlite3_bind_blob ()? (Ivan Shmakov) > 7. Re: SqLite - Help (Pavel Ivanov) > 8. Re: forcing X'' literals in sqlite3's .dump? (Stephan Beal) > 9. Re: forcing X'' literals in sqlite3's .dump? (Stephan Beal) > 10. Re: null handling import (matthew (matthew.jsoft) white) > 11. Re: null handling import (Stephan Beal) > 12. Re: null handling import (matthew (matthew.jsoft) white) > 13. Re: forcing X'' literals in sqlite3's .dump? (Richard Hipp) > 14. Re: forcing X'' literals in sqlite3's .dump? (Stephan Beal) > 15. Re: How to retrieve the Specil characters from the Sql lite > (Kees Nuyt) > 16. Re: How to retrieve the Specil characters from the Sql lite > (Simon Slavin) > 17. Re: forcing X'' literals in sqlite3's .dump? (Darren Duncan) > 18. Suggest some Tools for Sqlite (Madhankumar Rajaram) > 19. Re: Suggest some Tools for Sqlite (Simon Davies) > 20. Difference between Setups and Precompiled binaries for > windows (Madhankumar Rajaram) > 21. Re: Difference between Setups and Precompiled binaries > forwindows (Joe Mistachkin) > 22. how can i download sqlite 2.x version database for trial > version help me for this.i m waiting for your response . > (Rohit [Maco Infotech]) > 23. fts3 table name cannot be the same as column name (George Brink) > 24. Re: Improving the query optimizer (Akbar Syed) > 25. Re: Improving the query optimizer (Akbar Syed) > 26. Re: fts3 table name cannot be the same as column name > (Dan Kennedy) > 27. Re: Improving the query optimizer (Black, Michael (IS)) > 28. Re: Difference between Setups and Precompiled binaries for > windows (Black, Michael (IS)) > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ___ > 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] Suggest some Tools for Sqlite
I have found the SQL Maestro to be quite helpful. It is a licensed
tool, you have to pay a nominal fee, but I like the tool. You can also
find several good freeware tools, such as SQLiteAdmin. Be warned that
many of these freeware programs are no longer actively being developed
or supported.
-Original Message-
From: Madhankumar Rajaram [mailto:maraja...@technip.com]
Sent: Thursday, August 18, 2011 3:44 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Suggest some Tools for Sqlite
Hi,
I am working on sqlite3 using command prompt.
Its very hard to see the tables and datas.
I hope there should be some good Free or Licence Tools for using Sqlite.
For ex : Toad for Oracle.
so, Kindly suggest the best Free / Licence Tool for using Sqlite Thanks
Madhan Kumar R
This email and any attached files ("Message") may contain confidential
and/or privileged information. It is intended solely for the
addressee(s). If you receive this Message in error, inform the sender by
reply email, delete the Message and destroy any printed copy.
Any unauthorized use, distribution, or copying of this Message or any
part thereof is prohibited. Emails are susceptible to alteration.
Neither Technip nor any of its affiliates shall be liable for the
Message if altered or falsified nor shall they be liable for any damage
caused by any virus that might be transmitted with this Message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can i download sqlite 2.x version database for trial version help me for this.i m waiting for your response .........
On Wed, 17 Aug 2011 15:31:45 +0530, "Rohit [Maco Infotech]" wrote: >hello , > > i want to search on google but i can 't find specific way to download sql > lite database.send me link immediately ... SQLite v2 is obsolete. For a trial, use SQLite v3. http://www.sqlite.org/download.html If you really really need v2, you can check out the source branch from the fossil repositories listed at the bottom of the download page and compile it yourself. http://www.sqlite.org/cgi/src/brlist http://www.sqlite.org/cgi/src/timeline?n=200&b=2005-12-20+14%3A38%3A00 -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Difference between Setups and Precompiled binaries for windows
I think if you check the archives everybody has come to the conclusion that
just putting the amalgamation in your project is your best solution and build
it yourself. Then you never have to worry about somebody blowing away the DLL.
You don't save anything using the DLL unless your planning on putting this on
an iphone or other embedded device where memory is a constraint and there are
other sqlite3 apps.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Madhankumar Rajaram [maraja...@technip.com]
Sent: Thursday, August 18, 2011 3:40 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Difference between Setups and Precompiled binaries for
windows
Hi,
I am developing a windows desktop application (with .Net2010 C# and
sqlite)
I wanted to add sqlite dlls to my application to connect to database.
But i need to know
1.what is difference of Setups and Pre-compiled binaries ?
2. I think 32bit and 64 bit is for speed (RAM), but can i use 64 bit, Is
there any minimum requirement in OS? Which bit windows is best
Setups for 32-bit Windows (.NET Framework 4.0)
Setups for 64-bit Windows (.NET Framework 4.0)
Precompiled Binaries for 32-bit Windows (.NET Framework 4.0)
Precompiled Binaries for 64-bit Windows (.NET Framework 4.0)
In this link,
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
Thanks
Madhan Kumar R
This email and any attached files ("Message") may contain confidential and/or
privileged information. It is intended solely for the addressee(s). If you
receive this Message in error, inform the sender by reply email, delete the
Message and destroy any printed copy.
Any unauthorized use, distribution, or copying of this Message or any part
thereof is prohibited. Emails are susceptible to alteration. Neither Technip
nor any of its affiliates shall be liable for the Message if altered or
falsified nor shall they be liable for any damage caused by any virus that
might be transmitted with this Message.
___
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] Improving the query optimizer
Run "analyze" and your numbers will make more sense...albeit still not perfect.
Those numbers are estimations, ergo the "~" in the answer.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Akbar Syed [syed.akba...@googlemail.com]
Sent: Wednesday, August 17, 2011 2:34 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Improving the query optimizer
Please ignore my previous mail, it was accidentally sent.
To continue with my previous mail,
Offlate I have observed a strange behavior with Query Optimizer. I am not
sure if it is the desired behavior, yet please find my observations below:
Table
employee_table
---
id nameage sex
1 abcd 22m
2 xyz 24m
3 r22 f
4 22 f
5 zzz 23 m
Indexes
-
CREATE INDEX "iName" ON "employee_table" ("name" ASC)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table
0 l 0 l 0 l SCAN TABLE employee_table (~100 rows)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table WHERE id IS NOT NULL
0 l 0 l 0 l SCAN TABLE employee_table (~50 rows)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table WHERE id IS NOT NULL
AND id!=0
0 l 0 l 0 l SCAN TABLE employee_table (~25 rows)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table WHERE id IS NOT NULL
AND id!=0 AND id>0
0 l 0 l 0 l SEARCH TABLE employee_table USING INTEGER PRIMARY KEY
(rowid>?)(~82500 rows)
Everytime I increase the where clause with one more expression, I see the
number of rows get halved.
My emphasis was to reduce the number of rows being traversed to execute this
query.
Is this normal? Or did I interpret it totally wrong? I shall appreciate your
suggestions.
Regards,
Akbar Syed
___
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] fts3 table name cannot be the same as column name
On 08/17/2011 10:10 PM, George Brink wrote: > I think this is a bug... If you trying to create FTS3/FTS4 table, make > sure that column name in FTS table is not the same as the table name. > > C:\Projects>sqlite3 aaa > SQLite version 3.7.7.1 2011-06-28 17:39:05 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create virtual table abc using fts3(abc text); > Error: vtable constructor failed: abc > sqlite> create virtual table abc using fts3(abcd text); > sqlite> .exit It's because of the "hidden column". See the first paragraph under the second example block here: http://www.sqlite.org/fts3.html#section_1_4 Docs should be updated to say that you can't create a column with the same name as the table. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improving the query optimizer
Please ignore my previous mail, it was accidentally sent.
To continue with my previous mail,
Offlate I have observed a strange behavior with Query Optimizer. I am not
sure if it is the desired behavior, yet please find my observations below:
Table
employee_table
---
id nameage sex
1 abcd 22m
2 xyz 24m
3 r22 f
4 22 f
5 zzz 23 m
Indexes
-
CREATE INDEX "iName" ON "employee_table" ("name" ASC)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table
0 l 0 l 0 l SCAN TABLE employee_table (~100 rows)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table WHERE id IS NOT NULL
0 l 0 l 0 l SCAN TABLE employee_table (~50 rows)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table WHERE id IS NOT NULL
AND id!=0
0 l 0 l 0 l SCAN TABLE employee_table (~25 rows)
EXPLAIN QUERY PLAN SELECT count(id) from employee_table WHERE id IS NOT NULL
AND id!=0 AND id>0
0 l 0 l 0 l SEARCH TABLE employee_table USING INTEGER PRIMARY KEY
(rowid>?)(~82500 rows)
Everytime I increase the where clause with one more expression, I see the
number of rows get halved.
My emphasis was to reduce the number of rows being traversed to execute this
query.
Is this normal? Or did I interpret it totally wrong? I shall appreciate your
suggestions.
Regards,
Akbar Syed
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improving the query optimizer
Offlate I have observed a strange behavior with Query Optimizer. I am not sure if it is the desired behavior, yet please find my observations below: Table employee_table --- id nameage sex 1 abcd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fts3 table name cannot be the same as column name
I think this is a bug... If you trying to create FTS3/FTS4 table, make sure that column name in FTS table is not the same as the table name. C:\Projects>sqlite3 aaa SQLite version 3.7.7.1 2011-06-28 17:39:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create virtual table abc using fts3(abc text); Error: vtable constructor failed: abc sqlite> create virtual table abc using fts3(abcd text); sqlite> .exit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how can i download sqlite 2.x version database for trial version help me for this.i m waiting for your response .........
hello , i want to search on google but i can 't find specific way to download sql lite database.send me link immediately ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Difference between Setups and Precompiled binaries forwindows
> > 1. what is difference of Setups and Pre-compiled binaries ? > The setups are designed to create a directory inside the "Program Files" location, create start menu shortcuts, and optionally register the core and LINQ assemblies in the GAC. They also install the necessary VC++ runtime redistributables. The pre-compiled binaries are simply ZIP archives containing the build output for various configurations. > > 2. I think 32bit and 64 bit is for speed (RAM), but can i use 64 bit, Is > there any minimum requirement in OS? > The 64-bit Windows operating systems can access more physical memory at a time. As far as System.Data.SQLite is concerned, the minimum system requirements are Windows XP and the .NET Framework 2.0 for the core assembly and the .NET Framework 3.5 for the LINQ assembly. > > Which bit windows is best > That is difficult to say as it depends on the what your use cases are. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Difference between Setups and Precompiled binaries for windows
Hi,
I am developing a windows desktop application (with .Net2010 C# and
sqlite)
I wanted to add sqlite dlls to my application to connect to database.
But i need to know
1.what is difference of Setups and Pre-compiled binaries ?
2. I think 32bit and 64 bit is for speed (RAM), but can i use 64 bit, Is
there any minimum requirement in OS? Which bit windows is best
Setups for 32-bit Windows (.NET Framework 4.0)
Setups for 64-bit Windows (.NET Framework 4.0)
Precompiled Binaries for 32-bit Windows (.NET Framework 4.0)
Precompiled Binaries for 64-bit Windows (.NET Framework 4.0)
In this link,
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
Thanks
Madhan Kumar R
This email and any attached files ("Message") may contain confidential and/or
privileged information. It is intended solely for the addressee(s). If you
receive this Message in error, inform the sender by reply email, delete the
Message and destroy any printed copy.
Any unauthorized use, distribution, or copying of this Message or any part
thereof is prohibited. Emails are susceptible to alteration. Neither Technip
nor any of its affiliates shall be liable for the Message if altered or
falsified nor shall they be liable for any damage caused by any virus that
might be transmitted with this Message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggest some Tools for Sqlite
On 18 August 2011 08:44, Madhankumar Rajaram wrote: > > Hi, > I am working on sqlite3 using command prompt. > Its very hard to see the tables and datas. > > I hope there should be some good Free or Licence Tools for using Sqlite. > For ex : Toad for Oracle. > > so, Kindly suggest the best Free / Licence Tool for using Sqlite > Thanks http://sqlite.com/cvstrac/wiki?p=ManagementTools (top hit when google "sqlite tools") > > Madhan Kumar R Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Suggest some Tools for Sqlite
Hi,
I am working on sqlite3 using command prompt.
Its very hard to see the tables and datas.
I hope there should be some good Free or Licence Tools for using Sqlite.
For ex : Toad for Oracle.
so, Kindly suggest the best Free / Licence Tool for using Sqlite
Thanks
Madhan Kumar R
This email and any attached files ("Message") may contain confidential and/or
privileged information. It is intended solely for the addressee(s). If you
receive this Message in error, inform the sender by reply email, delete the
Message and destroy any printed copy.
Any unauthorized use, distribution, or copying of this Message or any part
thereof is prohibited. Emails are susceptible to alteration. Neither Technip
nor any of its affiliates shall be liable for the Message if altered or
falsified nor shall they be liable for any damage caused by any virus that
might be transmitted with this Message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
