Re: [sqlite] Performing multiple actions on a query

[Ian Petts]

> > I know I can run the query again with a DELETE command, but what if
> > the data has changed in between queries?
>
> Surround both statements with a BEGIN/COMMIT pair.

Ahh, okay. That seems to do the trick.

Thank you very much, Drake.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] autoindex1 test fail on linux debian

[Yoni]

Hi,

I get the following fail when running sqlite3 tests, under linux debian, 
with TCL 8.4.
Code is latest from fossil.
Any help appreciated.

Yoni.

autoindex1-400... Ok
autoindex1-401... Ok
./testfixture: extra characters after close-brace
 while compiling
"uplevel do_test $testname [list "execsql {$sql}"] [list [list {*}$result]]
"
 (compiling body of proc "do_execsql_test", line 3)
 invoked from within
"do_execsql_test autoindex1-500 {
   CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
   CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
   EXPLAIN QUERY PLAN
   ..."
 (file "../test/autoindex1.test" line 143)
 invoked from within
"source ../test/autoindex1.test"
 invoked from within
"interp eval tinterp $script"
 (procedure "slave_test_script" line 24)
 invoked from within
"slave_test_script [list source $zFile] "
 invoked from within
"time { slave_test_script [list source $zFile] }"
 (procedure "slave_test_file" line 14)
 invoked from within
"slave_test_file $file"
 (procedure "run_tests" line 13)
 invoked from within
"run_tests veryquick -presql {} -files {bigfile.test shared3.test 
tkt3419.test where9.test fts1o.test fts2f.test sync.test misc2.test 
e_select2.test se..."
 ("uplevel" body line 1)
 invoked from within
"uplevel run_tests $name $::testspec($name)"
 (procedure "run_test_suite" line 5)
 invoked from within
"run_test_suite veryquick"
 (file "..//test/veryquick.test" line 16)
make: *** [test] Error 1

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

[Niklas Bäckman]

There's a problem aligning columns containing UTF-8 (non-ASCII) characters
correctly when output is done in column mode.

The .width was set as ".width 5 30 15 15 10 30 10" and here's an example of
the incorrect output:

(Maybe paste into editor with fixed width font to better see the error)

===

BookI  Title   Last NameFirst Name
Date Read   Original Title  Story Titl
-  --  ---  ---
--  --  --
20 Bakom fasaden   Sandemo  Margit   0
20 Bakom fasaden   Sandemo  Margit
2010-07-02
13 Blodshämnd Sandemo  Margit   0
13 Blodshämnd Sandemo  Margit
2010-06-19
10 Bödelns dotter Sandemo  Margit   0
10 Bödelns dotter Sandemo  Margit
2010-06-11
24 Demonen och jungfrunSandemo  Margit   0
24 Demonen och jungfrunSandemo  Margit
2010-07-08
43 Demonernas fjäll   Sandemo  Margit   0
43 Demonernas fjäll   Sandemo  Margit
2010-07-25

===

Columns with special characters like ("å" "ä" "å") get too short widths when
output.

I guess this is due to the shell not counting actual UTF8 *characters/code
points* when calculating the widths, but instead only
counting the plain bytes in the strings, so they will seem longer until they
are actually printed to the console.

The output was done to a Windows console with code page set to 65001.

-- 
Niklas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL file size

[Ben Harper]

A cyclical WAL file has this problem:

You can never be sure when is the right time to wrap around.
If you wrap around too early, you run the risk of hitting the wall
put up by the earliest reader, who is still using the end of the
WAL file. In which case the writer would have to block on the reader,
which is something that the current system doesn't suffer from.
My guess is that a very high proportion of use cases would never
suffer this issue, provided they set an appropriate wrap around size
but this does place that burden on the implementer - of picking
the right heuristics.

It is a pathological case, but imagine an implementer never tests
for this wraparound issue, and builds a deadlock into his app
that arises when a writer blocks on a reader. This is the kind of
unexpected behaviour that could really bite you.

The only solution around this would be to have more than 1 WAL file.
To avoid hitting this same problem with multiple WAL files, you'd
need to support an arbitrary N number of WAL files. And that,
undoubtedly, is a more complex implementation than what currently
exists. I imagine it's quite a task for the SQLite developers to
get 100% branch test coverage.

On one system of mine, where blocking is an issue, I
buffer up the write messages, and flush them on a background thread.
Of course this may not be practical for you...

Ben

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Yoni
Sent: 24 November 2010 04:46 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] WAL file size

Hi,

I am currently developing a system, which have the following requirements:
1. should be very fast and responsive
2. run forever (or at least a very long time) without restart.
3. have steady stream of writes to the DB.

Currently I am using sqlite with one sql connection, and I run sql 
checkpoint every once in a while (e.g. after 5 minutes, or after 1 
inserts to the DB) - I don't use auto checkpoint.
Since I run the checkpoint in the main thread, the system is 
unresponsive for a long time (can take from 400ms to 2ms!)

What I would like to do, is to run the checkpoint in another thread, so 
the main thread can keep working (and using sql), and do the long 
checkpoint work in the background.

The problem is that when I try to do it, the WAL file grows without limit.
I wrote a little program that do exactly this (insert rows as fast as C 
allows, and in the background run checkpoints).
The result (after 30 secs and 400K records) was: DB size 19MB and WAL 
size 451MB! (tried on linux Debian with sqlite 3.7.2, and on winXP with 
same sqlite version).

I think that the problem relies on the fact the during the checkpoint, I 
keep writing to the WAL file, thus the checkpoint can never get to the 
end of the file. And since WAL file is not cyclic, it will grow forever, 
leaving the most of the file unused (the beginning), and using only the 
end of the file for real data.

I found this thread 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg56074.html, 
discussing a similar problem, and also saw dan's commit 
(http://www.sqlite.org/src/info/72787c010c) approaching the same 
problem, but not solving it, since it requires to block all DB activity.

I think this can be fixed, if sqlite use a cyclic WAL file.
I found that PostgreSQL also bumped into this, and used multiple WAL 
files, in order to avoid this kind of problem 
(http://www.sql.org/sql-database/postgresql/manual/wal-implementation.html).
Now, we do not need multiple files to do it, we just need to write to 
the beginning of the WAL file, when we have enough space (e.g. at least 
16MB of unused space - configurable).
So the WAL file might look like this:

 logical end   logical start
  ||
  VV
--
header | committed data  | unused space   | committed data   |==>
--  |
^|
 |

when the data at the head of the file is logically at the end of it.

When the writing is too fast, eventually they will get to logical start 
of the file. in this point we can force a blocking checkpoint, since 
this situation is very rare.

This will allow limiting WAL file size, while avoiding blocking 
checkpoint, and will increase responsiveness significantly!

Yoni.
___
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] WAL for single user database on nfs

[Richard Hipp]

On Wed, Nov 24, 2010 at 6:01 PM, p r  wrote:

> I read the following statement on
> http://www.sqlite.org/wal.html#concurrency
>
> ..This is why the write-ahead log implementation will not work on a network
> filesystem...
>
> I can how nfs would be an issue (due to stale handles) if processes from
> different machines were to access the sqlite db but I am thinking of a
> single
> process at a time (of n such processes on 1 machine)  accessing a single
> Sqlite
> database at a time over nfs - is WAL safe/feasible or not supported in that
> mode?
>

I guess it depends on your NFS implementation.  SQLite uses mmap() to get
the shared memory it needs.  Is mmap() supported on NFS files?

Beginning with SQLite version 3.7.4 (due out in about 2 weeks) you can use
WAL mode without shared memory by using "PRAGMA locking_mode=EXCLUSIVE".
That will be a safer approach.



>
> Thanks.
>
> Pete.
>
>
>
> ___
> 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] Relationship class in sqlite?

[Dekay Kim]

Hi,

What I am trying to do is to basically define the relationship between
various ArcGIS shapefiles and tables that are contained in one
sqlite(spatialite extension) database.

After defining the relationship, I want to be able to navigate through the
related values in a GIS application (e.g. QGIS)

In ArcGIS-FGDB(Feature Geodatabase) set-up, I can define something called
"relationship class" which enables such capability.

So far, I figured out that I can relate two tables using the syntax
"REFERENCES" in sqlite, but it does not seem to have something similar to
relationship class explicitly.

Assuming that I completed defining the relationship between the shapefiles
using REFERENCES keyword in sqlite, is there any visualization tool that
enables me the navigation between the related table attribute values?

I would appreciate your answer.

Thanks!

DK

-- 
Dekay Kim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WAL for single user database on nfs

[p r]

I read the following statement on http://www.sqlite.org/wal.html#concurrency

..This is why the write-ahead log implementation will not work on a network 
filesystem...

I can how nfs would be an issue (due to stale handles) if processes from 
different machines were to access the sqlite db but I am thinking of a single 
process at a time (of n such processes on 1 machine)  accessing a single Sqlite 
database at a time over nfs - is WAL safe/feasible or not supported in that 
mode?

Thanks.

Pete.


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pointers wanted for debugging an SQLITE_CORRUPT error

[Dick.Detweiler]

Hello Richard,

Thanks for the swift reply!

-Original Message-


>If you can construct a script of SQL statements that, fed into the sqlite3
>command-line shell, will generate a corrupt database, that would be a big
>help.
>If you can send me a copy of the (non-corrupt) database before your
>experiment, then the exact sequence of SQL operations you perform in order
>to get to corruption, that might be helpful to.

Will do - I sent this before heading out the door for the holiday so it might 
be next week.

>Have you tried recompiling with SQLite 3.7.3, just for grins?  I'll bet it
>will work out better for you.

I bet it will!  Don't know if it meets our testing requirements and deadlines 
this time however.


Have a happy Thanksgiving,

Dick
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pointers wanted for debugging an SQLITE_CORRUPT error

[Richard Hipp]

On Wed, Nov 24, 2010 at 4:57 PM,  wrote:

> Hello all,
>
> I have been supporting an application that uses SQLite version 3.3.7.  I
> knows this is literally YEARS out of date so I am not looking for detailed
> help or workarounds, just for pointers on how to approach debugging a
> corruption issue.
>
> Our environment is OpenEmbedded Linux on a PowerPC platform.  As far as I
> know, no changes have been made to the SQLite source.
>
> In our latest release testing, we encountered a very repeatable instance of
> SQLite data base corruption.  When running integrity check on the corrupt DB
> it returns:
>
> sqlite3 /tmp/caesar.db 'PRAGMA integrity_check'
> *** in database main ***
> Page 84: initPage() returns error code 11
> SQL error: database disk image is malformed
>
> It doesn't take much data to cause the problem.  Basically we have a table
> called oobdev_v2.  In the repeatable instance we add 6 rows to this table.
>  Before adding a row to this table, the code sets auto commit to false,
> performs an update and a  query on another table to get a unique serial
> number, and sets auto commit back to true.   The serial number is used as a
> column in the oobdev_v2 row we enter.
>
> On the 7th add we get the serial number but then determine we won't be
> adding the oobdev row.  The next DB access results in a DB exception and the
> above problem.  A .dump of the data base reveals that the only row left in
> the oobdev_v2 table is the last row and all tables after that in prior dumps
> no longer exist.
>
> We are going to try things like moving the serial number update and access
> until after we are sure we are adding a row and leaving some time between
> this failure and subsequent DB accesses.
>
> Is there anything to be recommended for getting more details of what or how
> the corruption took place in SQLite?
>

If you can construct a script of SQL statements that, fed into the sqlite3
command-line shell, will generate a corrupt database, that would be a big
help.

If you can send me a copy of the (non-corrupt) database before your
experiment, then the exact sequence of SQL operations you perform in order
to get to corruption, that might be helpful to.

Have you tried recompiling with SQLite 3.7.3, just for grins?  I'll bet it
will work out better for you.


>
> Thanks for any help,
>
>
> Dick Detweiler |Senior Software Engineer, MSD | Avocent | USA
> Emerson Network Power | 4991 Corporate Drive | Huntsville, AL 35805-6201
> T 256.261.6550 | F 256.430.4027
> www.avocent.com
> www.emersonnetworkpower.com
>
> ___
> 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] Pointers wanted for debugging an SQLITE_CORRUPT error

[Dick.Detweiler]

Hello all,

I have been supporting an application that uses SQLite version 3.3.7.  I knows 
this is literally YEARS out of date so I am not looking for detailed help or 
workarounds, just for pointers on how to approach debugging a corruption issue.

Our environment is OpenEmbedded Linux on a PowerPC platform.  As far as I know, 
no changes have been made to the SQLite source.

In our latest release testing, we encountered a very repeatable instance of 
SQLite data base corruption.  When running integrity check on the corrupt DB it 
returns:

sqlite3 /tmp/caesar.db 'PRAGMA integrity_check'
*** in database main ***
Page 84: initPage() returns error code 11
SQL error: database disk image is malformed

It doesn't take much data to cause the problem.  Basically we have a table 
called oobdev_v2.  In the repeatable instance we add 6 rows to this table.  
Before adding a row to this table, the code sets auto commit to false, performs 
an update and a  query on another table to get a unique serial number, and sets 
auto commit back to true.   The serial number is used as a column in the 
oobdev_v2 row we enter.

On the 7th add we get the serial number but then determine we won't be adding 
the oobdev row.  The next DB access results in a DB exception and the above 
problem.  A .dump of the data base reveals that the only row left in the 
oobdev_v2 table is the last row and all tables after that in prior dumps no 
longer exist.

We are going to try things like moving the serial number update and access 
until after we are sure we are adding a row and leaving some time between this 
failure and subsequent DB accesses.

Is there anything to be recommended for getting more details of what or how the 
corruption took place in SQLite?

Thanks for any help,


Dick Detweiler |Senior Software Engineer, MSD | Avocent | USA
Emerson Network Power | 4991 Corporate Drive | Huntsville, AL 35805-6201
T 256.261.6550 | F 256.430.4027
www.avocent.com
www.emersonnetworkpower.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attaching databases with different encoding schemes

[Kevin Turner]

Thanks, your suggestion worked perfect!

:)

On Wed, Nov 24, 2010 at 10:40 AM, Pierpaolo Bernardi wrote:

> On Wed, Nov 24, 2010 at 15:55, Kevin Turner 
> wrote:
> > I'm trying to attach one database to another but they have different
> > encoding schemas. One is UTF-16le and the other is UTF-8. I get this
> error
> > message when I try to attach:
> >
> > Error: attached databases must use the same text encoding as main
> database
> >
> > According to the sqlite FAQ, once an encoding has been set for a
> database,
> > it cannot be changed. Fair enough. But is it possible to *recreate* an
> > existing database and set the encoding type before the create table
> > statements are run?
> >
> > I tried doing this to the table that uses UTF-16le:
> >
> >  BEGIN TRANSACTION;
> >  CREATE TEMPORARY TABLE tmp(...);
> >  INSERT INTO tmp(...) SELECT ... FROM questions;
> >  DROP TABLE mytable;
> >  PRAGMA encoding = "UTF-8";
> >  CREATE TABLE mytable(...);
> >  INSERT INTO mytable(...) SELECT ... FROM tmp;
> >  COMMIT;
> >
> > But it didn't work, the newly rebuilt database was still UTF-16le. Any
> > suggestions on this? I'm a sqlite n00b and not a database specialist, so
> > verbosity in your responses would be much appreciated. ;)
>
> probably not the best solution, but the following should work:
>
> - sqlite3 your-utf16-db .dump > qqq
> - edit qqq appropriately, with a text editor
> - sqlite3 -init qqq the-converted-db-name
>
> P.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attaching databases with different encoding schemes

[Igor Tandetnik]

Kevin Turner  wrote:
> According to the sqlite FAQ, once an encoding has been set for a database,
> it cannot be changed. Fair enough. But is it possible to *recreate* an
> existing database and set the encoding type before the create table
> statements are run?

I'm not sure what you mean by "recreate the existing database". You can create 
a new database by simply calling sqlite3_open on a file name for which the 
actual file doesn't exist. Then, you can run a PRAGMA statement to set the 
encoding of this database, before any table is created. Once the first table is 
created, the encoding is set in stone and cannot be changed.

> I tried doing this to the table that uses UTF-16le:

Encoding is the property of the database, not of each individual table.
-- 
Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing time to create indexes

[Paul Sanderson]

Stuck with a 32 bit Windows set up as the lowest common so in memory
is not feasible - I have seen some of my DB's in excess of 2GB - users
might object even if I could nick that much memory
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing time to create indexes

[Jean-Christophe Deschamps]

>Simon, I'll try that and see what difference itmakes

That or build a :memory: DB, populate it, build indices and then only 
back it up to disk using the backup API.  That requires you have enough 
memory available, but should be really fast if you have.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does sqlite uses xAccess to check the main db existence?

[Richard Hipp]

On Wed, Nov 24, 2010 at 10:24 AM, Max Vlasov  wrote:

> >
> Thanks, Richard, I thought about the name's checking, but being a little
> paranoid wondered whether the current implementation of sqlite allows for
> main db being named with -journal postfix or it's forbidden? If the latter,
> I indeed can assume that if the name ends with  -journal (maybe also other
> postfixes related to WAL), it is a special request. If former, I easily can
> forbid those postfixes for main db in my xOpen implementation resolving any
> paranoid variant.
>


SQLite does not impose any restrictions on the names of database files.

On the other hand, SQLite doesn't make filenames up.  It only uses the
filenames you pass into sqlite3_open() or give to the ATTACH command.  You
control those interfaces, so you can determine which names are valid and
which are not.

-- 
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] Reducing time to create indexes

[Paul Sanderson]

Thanks All

Simon, I'll try that and see what difference itmakes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing time to create indexes

[Simon Slavin]

On 24 Nov 2010, at 2:18pm, Paul Sanderson wrote:

> Unfortunately all of the columns are represented in a grid that users
> can choose to sort on any column, although they are less likely to
> sort on certain columns, if they do chose to do so then an excessive
> delay is not really acceptable.

Okay, that explains that.

> Currently I create ascending and descending indexes for each column
> individually i.e.
> 
> create index if not exists name_a on table(name asc)
> create index if not exists name_d on table(name desc)
> etc.

Well at least I can save you /some/ time.  You can do just the ascending ones.  
Searches which require descending order should automatically use the ascending 
index if it will be of any use to them.  Descending indexing are usually needed 
only for compound indexes like

create index if not exists name_birthday on table(surname asc, birthdate desc)

where one key needs to be searched ascending and the other descending.

Obviously, try it first.  Create just the ascending indexes and see whether 
descending sorts take longer than ascending ones.

> The data is entered in order for the primary index
> 
> File size varies from case to case but typically prior to indexing it
> is about 300MB and with indexes 600MB.

Mmm.  I think that apart from the above you're doing everything right, and that 
without extreme and complicated measure you could improve your time only by 
some very small percentage.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing time to create indexes

[Israel Lins Albuquerque]

Hey guy, you are doing wrong, you don't need the booth indexes in same field; 


DROP TABLE tb; 
CREATE TEMP TABLE tb ( 
a INTEGER, 
b TEXT, 
CONSTRAINT 'idx_tb00' PRIMARY KEY (a)); 

CREATE INDEX 'idx_tb01' ON tb (b); 

INSERT INTO tb (a, b) VALUES (1, '1'); 
INSERT INTO tb (a, b) VALUES (2, '2'); 
INSERT INTO tb (a, b) VALUES (3, '3'); 
INSERT INTO tb (a, b) VALUES (4, '4'); 
INSERT INTO tb (a, b) VALUES (5, '5'); 
INSERT INTO tb (a, b) VALUES (6, '6'); 

look that example and take a look in the result of "EXPLAIN QUERY PLAN" 
in booth order are the same index used! 

EXPLAIN QUERY PLAN 
SELECT * FROM tb 
ORDER BY a ASC; 

EXPLAIN QUERY PLAN 
SELECT * FROM tb 
ORDER BY a DESC; 

EXPLAIN QUERY PLAN 
SELECT * FROM tb 
ORDER BY b ASC; 

EXPLAIN QUERY PLAN 
SELECT * FROM tb 
ORDER BY b DESC; 

now take a look on EXPLAIN , 
only 2 operations are changed: 
Rewind(First) <=> Last, 
Next <=> Previous 

EXPLAIN 
SELECT * FROM tb 
ORDER BY b ASC; 

EXPLAIN 
SELECT * FROM tb 
ORDER BY b DESC; 

With this your time can be halved ! 

- "Paul Sanderson"  writed: 
> Unfortunately all of the columns are represented in a grid that users 
> can choose to sort on any column, although they are less likely to 
> sort on certain columns, if they do chose to do so then an excessive 
> delay is not really acceptable. 
> 
> Currently I create ascending and descending indexes for each column 
> individually i.e. 
> 
> create index if not exists name_a on table(name asc) 
> create index if not exists name_d on table(name desc) 
> etc. 
> 
> The data is entered in order for the primary index 
> 
> File size varies from case to case but typically prior to indexing it 
> is about 300MB and with indexes 600MB. 
> 
> Ill have a look at FTS as there are other benefits to using that. 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 






-- 
Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does sqlite uses xAccess to check the main db existence?

[Max Vlasov]

On Wed, Nov 24, 2010 at 6:01 PM, Richard Hipp  wrote:

> On Wed, Nov 24, 2010 at 6:15 AM, Max Vlasov  wrote:
>
> > Hi,
> >
> > I'm trying to implement read-only access to sqlite database saved in the
> > Windows RC_DATA resouce (of the same exe file). There are different
> > additional files involved when the db is writable (-journal for example),
> > but it looks like one can have no special knowledge about them since
> there
> > is a flag in xOpen allowing to detect the main db. But notable exception
> is
> > xAccess. I can not see from the parameters whether this checking is about
> > main db or journal file. If I return ok for SQLITE_ACCESS_EXISTS, sqlite
> > goes wrong way with assuming the journal exists (at least different calls
> > lead to "unable to open" error). But also the debugging shows that
> xAccess
> > never checks for the main file existence, only supplemental ones. Can I
> > rely
> > on that and return FALSE unconditionally? Currently it works, but it
> might
> > give a new failure in the future.
> >
>
> We have no plans to change the current behavior.  But who knows what issue
> might come up in the future in which we'll need to call xAccess on the main
> database file.  You would do well to check the filename before returning
> your answer, I think.
>
>
Thanks, Richard, I thought about the name's checking, but being a little
paranoid wondered whether the current implementation of sqlite allows for
main db being named with -journal postfix or it's forbidden? If the latter,
I indeed can assume that if the name ends with  -journal (maybe also other
postfixes related to WAL), it is a special request. If former, I easily can
forbid those postfixes for main db in my xOpen implementation resolving any
paranoid variant.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does sqlite uses xAccess to check the main db existence?

[Richard Hipp]

On Wed, Nov 24, 2010 at 6:15 AM, Max Vlasov  wrote:

> Hi,
>
> I'm trying to implement read-only access to sqlite database saved in the
> Windows RC_DATA resouce (of the same exe file). There are different
> additional files involved when the db is writable (-journal for example),
> but it looks like one can have no special knowledge about them since there
> is a flag in xOpen allowing to detect the main db. But notable exception is
> xAccess. I can not see from the parameters whether this checking is about
> main db or journal file. If I return ok for SQLITE_ACCESS_EXISTS, sqlite
> goes wrong way with assuming the journal exists (at least different calls
> lead to "unable to open" error). But also the debugging shows that xAccess
> never checks for the main file existence, only supplemental ones. Can I
> rely
> on that and return FALSE unconditionally? Currently it works, but it might
> give a new failure in the future.
>

We have no plans to change the current behavior.  But who knows what issue
might come up in the future in which we'll need to call xAccess on the main
database file.  You would do well to check the filename before returning
your answer, I think.


>
> Thanks
>
> Max Vlasov
> ___
> 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] Attaching databases with different encoding schemes

[Kevin Turner]

I'm trying to attach one database to another but they have different
encoding schemas. One is UTF-16le and the other is UTF-8. I get this error
message when I try to attach:

Error: attached databases must use the same text encoding as main database

According to the sqlite FAQ, once an encoding has been set for a database,
it cannot be changed. Fair enough. But is it possible to *recreate* an
existing database and set the encoding type before the create table
statements are run?

I tried doing this to the table that uses UTF-16le:

  BEGIN TRANSACTION;
  CREATE TEMPORARY TABLE tmp(...);
  INSERT INTO tmp(...) SELECT ... FROM questions;
  DROP TABLE mytable;
  PRAGMA encoding = "UTF-8";
  CREATE TABLE mytable(...);
  INSERT INTO mytable(...) SELECT ... FROM tmp;
  COMMIT;

But it didn't work, the newly rebuilt database was still UTF-16le. Any
suggestions on this? I'm a sqlite n00b and not a database specialist, so
verbosity in your responses would be much appreciated. ;)

Thanks in advance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WAL file size

[Yoni]

Hi,

I am currently developing a system, which have the following requirements:
1. should be very fast and responsive
2. run forever (or at least a very long time) without restart.
3. have steady stream of writes to the DB.

Currently I am using sqlite with one sql connection, and I run sql 
checkpoint every once in a while (e.g. after 5 minutes, or after 1 
inserts to the DB) - I don't use auto checkpoint.
Since I run the checkpoint in the main thread, the system is 
unresponsive for a long time (can take from 400ms to 2ms!)

What I would like to do, is to run the checkpoint in another thread, so 
the main thread can keep working (and using sql), and do the long 
checkpoint work in the background.

The problem is that when I try to do it, the WAL file grows without limit.
I wrote a little program that do exactly this (insert rows as fast as C 
allows, and in the background run checkpoints).
The result (after 30 secs and 400K records) was: DB size 19MB and WAL 
size 451MB! (tried on linux Debian with sqlite 3.7.2, and on winXP with 
same sqlite version).

I think that the problem relies on the fact the during the checkpoint, I 
keep writing to the WAL file, thus the checkpoint can never get to the 
end of the file. And since WAL file is not cyclic, it will grow forever, 
leaving the most of the file unused (the beginning), and using only the 
end of the file for real data.

I found this thread 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg56074.html, 
discussing a similar problem, and also saw dan's commit 
(http://www.sqlite.org/src/info/72787c010c) approaching the same 
problem, but not solving it, since it requires to block all DB activity.

I think this can be fixed, if sqlite use a cyclic WAL file.
I found that PostgreSQL also bumped into this, and used multiple WAL 
files, in order to avoid this kind of problem 
(http://www.sql.org/sql-database/postgresql/manual/wal-implementation.html).
Now, we do not need multiple files to do it, we just need to write to 
the beginning of the WAL file, when we have enough space (e.g. at least 
16MB of unused space - configurable).
So the WAL file might look like this:

 logical end   logical start
  ||
  VV
--
header | committed data  | unused space   | committed data   |==>
--  |
^|
 |

when the data at the head of the file is logically at the end of it.

When the writing is too fast, eventually they will get to logical start 
of the file. in this point we can force a blocking checkpoint, since 
this situation is very rare.

This will allow limiting WAL file size, while avoiding blocking 
checkpoint, and will increase responsiveness significantly!

Yoni.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing time to create indexes

[Paul Sanderson]

Unfortunately all of the columns are represented in a grid that users
can choose to sort on any column, although they are less likely to
sort on certain columns, if they do chose to do so then an excessive
delay is not really acceptable.

Currently I create ascending and descending indexes for each column
individually i.e.

create index if not exists name_a on table(name asc)
create index if not exists name_d on table(name desc)
etc.

The data is entered in order for the primary index

File size varies from case to case but typically prior to indexing it
is about 300MB and with indexes 600MB.

Ill have a look at FTS as there are other benefits to using that.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multilingual query

[Black, Michael (IS)]

Depends on what he means by "first".
 
If alphabetically Igor's solution worksbut I'm not sure why you alphabetize 
singled query result like that.
 
If you want the first ones inserted into the table try this:
 

select * from table2 t1where t1.language = 'spanish' or not exists (select 1 
from table2 t2 where t2.id = t1.id and (t2.language = 'spanish' or t2.rowid < 
t1.rowid));


For this data
CREATE TABLE table2 (id integer, language text, title text, primary key (id, 
language));
INSERT INTO "table2" VALUES(1,'spanish','title1');
INSERT INTO "table2" VALUES(2,'english','title2');
INSERT INTO "table2" VALUES(3,'russian','title3');
INSERT INTO "table2" VALUES(3,'english','title3');
INSERT INTO "table2" VALUES(3,'spanish','title3');
INSERT INTO "table2" VALUES(2,'dutch','title2');
INSERT INTO "table2" VALUES(2,'danish','title2');
 
Igor's produces:
1|spanish|title1
3|spanish|title3
2|danish|title2
 
The rowid one produces:
1|spanish|title1
2|english|title2
3|spanish|title3
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Igor Tandetnik
Sent: Tue 11/23/2010 11:28 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] Multilingual query



SQLumee SQLumee  wrote:
> Hi all, I am trying to retrieve multilingual information from a table but I 
> don't know how to do this query.
>
> I have a table defined as:
> CREATE TABLE table2 (id integer, language text, title text, primary key (id, 
> language));
>
> What I want to retrieve are the rows with "spanish" titles or if there is no 
> spanish title available, select the title in the
> first language found.

Try this:

select * from table2 t1
where t1.language = 'spanish' or not exists (
select 1 from table2 t2
where t2.id = t1.id and (t2.language = 'spanish' or t2.language < 
t1.language)
);

Igor Tandetnik

___
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] How do I get expression from column (smart folder orfilter implementation)

[Igor Tandetnik]

Igor Tandetnik  wrote:
> Yuzem  wrote:
>> Suppose I have this tables:
>> movies: movie,name,rating
>> tags: movie,tag
>> filters: filter,expression
>> 
>> In filters I have:
>> rated,rating != ''
>> tagged,movie in (select movie from tags)
>> 
>> But from the "filters" table (doesn't work):
>> SELECT filter,movie FROM filters LEFT JOIN movies WHERE movie IN (SELECT
>> movie FROM movie WHERE expression);
> 
> SQLite doesn't have anything like eval(). It can't take a string from a 
> column, then parse and interpret it as part of a SQL
> statement. 
> 
> You could probably write a custom function that would take (movie, 
> expression) as parameters, build a statement of the form
> 
> select 1 from movies where movie=:movie and ;
> 
> where  is replaced with the actual expression (e.g. using 
> sprintf), run this statement and return 1 if the statement
> produces any rows and 0 otherwise. Then you could write 
> 
> SELECT filter, movie FROM filters LEFT JOIN movies WHERE checkMovie(movie, 
> expression);

Correction: make it

SELECT filter, movie FROM filters LEFT JOIN movies ON checkMovie(movie, 
expression);

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I get expression from column (smart folder or filter implementation)

[Igor Tandetnik]

Yuzem  wrote:
> Suppose I have this tables:
> movies: movie,name,rating
> tags: movie,tag
> filters: filter,expression
> 
> In filters I have:
> rated,rating != ''
> tagged,movie in (select movie from tags)
>
> But from the "filters" table (doesn't work):
> SELECT filter,movie FROM filters LEFT JOIN movies WHERE movie IN (SELECT
> movie FROM movie WHERE expression);

SQLite doesn't have anything like eval(). It can't take a string from a column, 
then parse and interpret it as part of a SQL statement.

You could probably write a custom function that would take (movie, expression) 
as parameters, build a statement of the form

select 1 from movies where movie=:movie and ;

where  is replaced with the actual expression (e.g. using sprintf), 
run this statement and return 1 if the statement produces any rows and 0 
otherwise. Then you could write

SELECT filter, movie FROM filters LEFT JOIN movies WHERE checkMovie(movie, 
expression);

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Auto Reply: sqlite-users Digest, Vol 35, Issue 24

[bogdan . coman]

This is an auto-replied message. I am out of office right now, with limited 
access to e-mail.

I will be returning on Friday, 03 Dec.

If you need Berkeley DB assistance before then, please use My Oracle Support or 
the Oracle Berkeley DB Forums. For urgent issues, please contact Adam Whitter 
at adam.whit...@oracle.com

Bogdan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How do I get expression from column (smart folder or filter implementation)

[Yuzem]

Hello,
Suppose I have this tables:
movies: movie,name,rating
tags: movie,tag
filters: filter,expression

In filters I have:
rated,rating != ''
tagged,movie in (select movie from tags)

Now I want to create a view to relate movies and filters exactly like the
tags table:
movies_filters: movie,filter

Something like this:
SELECT 'rated',movie FROM movies WHERE rating != '';
SELECT 'tagged',movie FROM movies WHERE movie in (SELECT movie FROM tags);

But from the "filters" table (doesn't work):
SELECT filter,movie FROM filters LEFT JOIN movies WHERE movie IN (SELECT
movie FROM movie WHERE expression);
-- 
View this message in context: 
http://old.nabble.com/How-do-I-get-expression-from-column-%28smart-folder-or-filter-implementation%29-tp30295959p30295959.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] union all with limit

[Simon Davies]

On 24 November 2010 11:08, Swithun Crowe  wrote:
> Hello
>
> BS> Thanks, I tried that and it gives no error, but only gives the first
> BS> lot, not the bit after the union all.
>
> Ah. I hadn't tried with data. I don't know why the LIMIT affects the UNION
> ALL, when it is buried in a subquery.

It doesn't in version 3.7.0.1

sqlite> select sqlite_version();
3.7.0.1
sqlite>
sqlite> create table table1 (patientID int, age int);
sqlite> insert into table1 (patientID, age) values (1, 50);
sqlite> insert into table1 (patientID, age) values (2, 50);
sqlite> insert into table1 (patientID, age) values (3, 50);
sqlite> insert into table1 (patientID, age) values (4, 50);
sqlite> insert into table1 (patientID, age) values (5, 50);
sqlite> insert into table1 (patientID, age) values (6, 50);
sqlite> insert into table1 (patientID, age) values (7, 50);
sqlite> insert into table1 (patientID, age) values (8, 60);
sqlite> insert into table1 (patientID, age) values (9, 60);
sqlite> insert into table1 (patientID, age) values (10, 60);
sqlite> insert into table1 (patientID, age) values (11, 60);
sqlite> insert into table1 (patientID, age) values (12, 60);
sqlite>
sqlite> select patientID
   ...> from (
   ...>  select patientID
   ...>  from table1
   ...>  where age = 50
   ...>  limit 6
   ...> )
   ...> union all
   ...> select patientID
   ...> from (
   ...>  select patientID
   ...>  from table1
   ...>  where age = 60
   ...>  limit 4
   ...> );
1
2
3
4
5
6
8
9
10
11
sqlite>

>
> Swithun.

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does sqlite uses xAccess to check the main db existence?

[Max Vlasov]

Hi,

I'm trying to implement read-only access to sqlite database saved in the
Windows RC_DATA resouce (of the same exe file). There are different
additional files involved when the db is writable (-journal for example),
but it looks like one can have no special knowledge about them since there
is a flag in xOpen allowing to detect the main db. But notable exception is
xAccess. I can not see from the parameters whether this checking is about
main db or journal file. If I return ok for SQLITE_ACCESS_EXISTS, sqlite
goes wrong way with assuming the journal exists (at least different calls
lead to "unable to open" error). But also the debugging shows that xAccess
never checks for the main file existence, only supplemental ones. Can I rely
on that and return FALSE unconditionally? Currently it works, but it might
give a new failure in the future.

Thanks

Max Vlasov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] union all with limit

[Bart Smissaert]

Thanks, that one works indeed and will use that.

RBS


On Wed, Nov 24, 2010 at 11:08 AM, Swithun Crowe
 wrote:
> Hello
>
> BS> Thanks, I tried that and it gives no error, but only gives the first
> BS> lot, not the bit after the union all.
>
> Ah. I hadn't tried with data. I don't know why the LIMIT affects the UNION
> ALL, when it is buried in a subquery. It doesn't affect a UNION (no ALL).
> In your real data and query, can a patient have more than one age? If not,
> then UNION and UNION ALL would produce the same results.
>
> sqlite> create table table1 (patientID int, age int);
> sqlite> insert into table1 (patientID, age) values (1, 50);
> sqlite> insert into table1 (patientID, age) values (2, 50);
> sqlite> insert into table1 (patientID, age) values (3, 50);
> sqlite> insert into table1 (patientID, age) values (4, 50);
> sqlite> insert into table1 (patientID, age) values (5, 50);
> sqlite> insert into table1 (patientID, age) values (6, 50);
> sqlite> insert into table1 (patientID, age) values (7, 50);
> sqlite> insert into table1 (patientID, age) values (8, 60);
> sqlite> insert into table1 (patientID, age) values (9, 60);
> sqlite> insert into table1 (patientID, age) values (10, 60);
> sqlite> insert into table1 (patientID, age) values (11, 60);
> sqlite> insert into table1 (patientID, age) values (12, 60);
>
> This seems to work:
>
> select patientID
> from table1
> where patientID in (select patientID from table1 where age=50 limit 6)
> union all
> select patientID
> from table1
> where patientID in(select patientID from table1 where age=60 limit 4);
>
> 1
> 2
> 3
> 4
> 5
> 6
> 8
> 9
> 10
> 11
>
> Swithun.
> ___
> 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] union all with limit

[Swithun Crowe]

Hello

BS> Thanks, I tried that and it gives no error, but only gives the first 
BS> lot, not the bit after the union all.

Ah. I hadn't tried with data. I don't know why the LIMIT affects the UNION 
ALL, when it is buried in a subquery. It doesn't affect a UNION (no ALL). 
In your real data and query, can a patient have more than one age? If not, 
then UNION and UNION ALL would produce the same results.

sqlite> create table table1 (patientID int, age int);
sqlite> insert into table1 (patientID, age) values (1, 50);
sqlite> insert into table1 (patientID, age) values (2, 50);
sqlite> insert into table1 (patientID, age) values (3, 50);
sqlite> insert into table1 (patientID, age) values (4, 50);
sqlite> insert into table1 (patientID, age) values (5, 50);
sqlite> insert into table1 (patientID, age) values (6, 50);
sqlite> insert into table1 (patientID, age) values (7, 50);
sqlite> insert into table1 (patientID, age) values (8, 60);
sqlite> insert into table1 (patientID, age) values (9, 60);
sqlite> insert into table1 (patientID, age) values (10, 60);
sqlite> insert into table1 (patientID, age) values (11, 60);
sqlite> insert into table1 (patientID, age) values (12, 60);

This seems to work:

select patientID 
from table1 
where patientID in (select patientID from table1 where age=50 limit 6) 
union all 
select patientID 
from table1 
where patientID in(select patientID from table1 where age=60 limit 4);

1
2
3
4
5
6
8
9
10
11

Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] union all with limit

[Bart Smissaert]

Thanks, I tried that and it gives no error, but only gives the first
lot, not the bit after the union all.

RBS


On Wed, Nov 24, 2010 at 10:27 AM, Swithun Crowe
 wrote:
> Hello
>
> BS> select
> BS> patient_id
> BS> from
> BS> table1
> BS> where
> BS> age = 50
> BS> limit 6
> BS> union all
> BS> select
> BS> patient_id
> BS> from
> BS> table1
> BS> where
> BS> age = 60
> BS> limit 4
>
> You might want to wrap the two selects with limits inside subqueries:
>
> select patientID
> from (
>  select patientID
>  from table1
>  where age = 50
>  limit 6
> )
> union all
> select patientID
> from (
>  select patientID
>  from table1
>  where age = 60
>  limit 4
> );
>
> Swithun.
> ___
> 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] union all with limit

[Swithun Crowe]

Hello

BS> select
BS> patient_id
BS> from
BS> table1
BS> where
BS> age = 50
BS> limit 6
BS> union all
BS> select
BS> patient_id
BS> from
BS> table1
BS> where
BS> age = 60
BS> limit 4

You might want to wrap the two selects with limits inside subqueries:

select patientID 
from (
  select patientID 
  from table1 
  where age = 50 
  limit 6
) 
union all 
select patientID 
from (
  select patientID 
  from table1 
  where age = 60 
  limit 4
);

Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] union all with limit

[Bart Smissaert]

Trying to run this SQL:

select
patient_id
from
table1
where
age = 50
limit 6
union all
select
patient_id
from
table1
where
age = 60
limit 4

But it fails due to the limit clause before the union.
Would there be a way round this?


RBS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] __GNUC__ __x86_64__ hwtime

[Rickey Bowers]

Instruction RDTSC works the same in long mode (64-bit) as it does in IA32
mode: the upper dword is in EDX and the lower dword is in EAX. Currently,
hwtime.h assumes that RAX contains the 64-bit value. However logical the
assumption, it is still incorrect.

Correct code:

#elif (defined(__GNUC__) && defined(__x86_64__))

  __inline__ sqlite_uint64 sqlite3Hwtime(void){
 unsigned int lo, hi;
 __asm__ __volatile__ ("rdtsc" : "=a" (lo), "=d" (hi));
 return (sqlite_uint64)hi << 32 | lo;
  }

#elif (defined(__GNUC__) && defined(__ppc__))
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users