[sqlite] How can xBestIndex discern 'a' = col COLLATE NOCASE vs 'a' = col

[Ben Harper]

I have a virtual table, but I cannot figure out how to discover the
collating sequence of a query expression passed to xBestIndex.

As far as I can tell, these three statements ...

SELECT * FROM tab WHERE col = 'val';
SELECT * FROM tab WHERE col = 'val' COLLATE BINARY;
SELECT * FROM tab WHERE col = 'val' COLLATE NOCASE;

... send identical data to xBestIndex.

Am I right if I conclude that the xBestIndex interface would need to be extended
in order to make it possible to distinguish between these three different 
queries?

Thanks,
Ben


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

Re: [sqlite] Bug or Problem in Sqlite3.exe when importing UTF8

[Igor Tandetnik]

Jens Hantschel  wrote:
> I am facing problems when importing a file to a database-table with 
> sqlite3.exe and ".import"-parameter.
> 
> The file is encoded in UTF8 an every time i import the file in the first 
> column of the first row there is a special character
> occuring in 
> the field.

That would be UTF-8 BOM, I imagine: 
http://en.wikipedia.org/wiki/Byte_order_mark . I suspect you'll have to strip 
it from the file (or use a tool that doesn't generate it in the first place) 
before feeding the file to sqlite3
-- 
Igor Tandetnik

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

Re: [sqlite] How to make this calculation in VIEW

[Jeff Archer]

Thank you, Simon and Owen.

I have tried and both solutions work equally well.  I have actually used C code 
to make the calculation as I pull out the data.
But to help further my understanding of SQL/SQLite, can anyone comment on 
potential performance differences of the 2 SQL solutions.

CREATE VIEW IF NOT EXISTS [vw_patterns0] AS 
SELECT Patterns.Name AS Pattern, 
   Patterns.Origin_X, 
   Patterns.Origin_Y, 
   Patterns.wMicrons, 
   Patterns.hMicrons, 
   COUNT(DISTINCT Offset_X) AS nTilesX, 
   COUNT(DISTINCT Offset_Y) AS nTilesY, 
   Patterns.wPixels,
   Patterns.hPixels,   
   Patterns.wMicrons * COUNT(DISTINCT Offset_X) AS wTotalMicrons,
   Patterns.hMicrons * COUNT(DISTINCT Offset_Y) AS hTotalMicrons,
   Patterns.wPixels * COUNT(DISTINCT Offset_X) AS wTotalPixels,
   Patterns.hPixels * COUNT(DISTINCT Offset_Y) AS hTotalPixels,
   Patterns.Description 
FROM [Tiles] INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID 
GROUP BY Tiles.PatternID;

CREATE VIEW IF NOT EXISTS [vw_patterns1] AS 
SELECT Patterns.Name AS Pattern, 
   Patterns.Origin_X, 
   Patterns.Origin_Y, 
   Patterns.wMicrons, 
   Patterns.hMicrons, 
   COUNT(DISTINCT Offset_X) AS nTilesX, 
   COUNT(DISTINCT Offset_Y) AS nTilesY, 
   Patterns.wPixels,
   Patterns.hPixels,
   Patterns.Description 
FROM [Tiles] INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID 
GROUP BY Tiles.PatternID;

CREATE VIEW IF NOT EXISTS [vw_patterns2] AS 
SELECT Pattern, 
   Origin_X, 
   Origin_Y, 
   wMicrons, 
   hMicrons, 
   nTilesX, 
   nTilesY, 
   wMicrons * nTilesX AS wTotalMicrons,
   hMicrons * nTilesY AS hTotalMicrons,
   wPixels * nTilesX AS wTotalPixels,
   hPixels * nTilesY AS hTotalPixels,
   wPixels,
   hPixels,
   Description 
FROM [vw_patterns1];
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] creating a sqlite db on raw disk ?

[Simon Slavin]

On 19 Nov 2010, at 7:04am, Yang wrote:

> when I create a db on a file system, I guess a query process

Wait ... are you talking here about creating a new database file or querying 
one what already exists ?

> has to go
> through 2 levels of seeks ?
> first sqlite finds the B-tree node that stores the index to the file
> offset of my desired record, then sqlite uses that offset to make
> syscall seek(offset),
> then Kernel consults the FS implementation to find from its  OWN
> B-tree (for example in ext3 fs )  the block location of that offset.

Assuming that you're using a FS that uses B-trees, nodes, and indices (many 
don't), that's a fair summary of what happens.  But SQLite runs fine on many 
embedded systems that use linked lists instead of B-trees, or don't use nodes, 
or have databases stored in non-writable sequential memory.

> innodb allows creating a db on a raw disk partition, can we do the
> same on  sqlite?

No.  You need a file system of some kind.  Or, at least, your operating system 
needs to be able to address your storage using file-system calls, not 
storage-structure calls.

> I tried directly creating a db on ramdisk, failed:
> 
> javasqlite-20100727# sqlite3 /dev/ram0

You didn't tell it what to call the database, just where you wanted it.  I 
think you want something like

# sqlite3 /dev/ram0/myDatabase.sqlite

There's no reason this shouldn't work if your drivers and your version of *n*x 
/fully/ support the use of /deb/ram0 for file storage, including support for 
locking calls.  Works fine under Mac OS X, by the way.

But SQLite does provide its own way of creating a database in memory.  See

http://www.sqlite.org/inmemorydb.html

So you'd use something like

# sqlite3 :memory:

Of course, that database will be visible only to the process that creates it.  
Which means that if you use the command-line tool to create it it will 
effectively disappear as you quit the command-line tool.

If you're using Linux or a Unix that supports it, you can also look into the 
shared memory filesystem 'tmpfs'.  I haven't tried it, but this should also 
support SQLite without any problems.

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

Re: [sqlite] Corrupted database with duplicated primary keys

[Israel Lins Albuquerque]

Due to attach limits this is the part 001 of the file! 

- "Israel Lins Albuquerque"  escreveu: 
> Attached has a database corrupted. 
> We use the version 3.6.23.1 in wince. 
> 
> the command: 
> pragma integrity_check; 
> 
> show many errors and 
> 
> Duplicate pk was founded using: 
> SELECT u_pkey, count(*) 
> FROM tp_gpsdata 
> GROUP BY u_pkey 
> HAVING count(*) > 1 
> ; 
> 
> this returns only 1 record 
> SELECT * 
> FROM tp_gpsdata 
> WHERE u_pkey IN (4684, 4879) 
> ORDER BY u_pkey 
> DESC LIMIT 10; 
> 
> deletes one record only 
> DELETE FROM tp_gpsdata WHERE u_pkey IN (4684, 4879); 
> 
> vacuum; 
> doesn't works because of pk constraints. 
> 
> 
> 
> 
> 
> I'm seeding because that can be a bug in OS or in sqlite and maybe someone 
> can see that, 
> Thanks for your time! 
> 
> -- 
> 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 
> 

-- 






-- 
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] Corrupted database with duplicated primary keys

[Israel Lins Albuquerque]

Due to attach limits this is the part 002 of the file! 

- "Israel Lins Albuquerque"  escreveu: 
> Attached has a database corrupted. 
> We use the version 3.6.23.1 in wince. 
> 
> the command: 
> pragma integrity_check; 
> 
> show many errors and 
> 
> Duplicate pk was founded using: 
> SELECT u_pkey, count(*) 
> FROM tp_gpsdata 
> GROUP BY u_pkey 
> HAVING count(*) > 1 
> ; 
> 
> this returns only 1 record 
> SELECT * 
> FROM tp_gpsdata 
> WHERE u_pkey IN (4684, 4879) 
> ORDER BY u_pkey 
> DESC LIMIT 10; 
> 
> deletes one record only 
> DELETE FROM tp_gpsdata WHERE u_pkey IN (4684, 4879); 
> 
> vacuum; 
> doesn't works because of pk constraints. 
> 
> 
> 
> 
> 
> I'm seeding because that can be a bug in OS or in sqlite and maybe someone 
> can see that, 
> Thanks for your time! 
> 
> -- 
> 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 
> 

-- 






-- 
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] UPDATE during SELECT

[Nikolaus Rath]

"Igor Tandetnik"  writes:
> Nikolaus Rath  wrote:
>> I understand that running INSERT or DELETE during an active SELECT query
>> can get me into trouble. But is it safe to run (in pseudocode):
>> 
>> for value in "SELECT main_column IN mytable":
>> 
>>   UPDATE mytable SET other_column='foobar' WHERE main_column=value
>
> It should be safe. Though I don't see how this would be different than
> just updating all rows in a single query:
>
> UPDATE mytable SET other_column='foobar';

Well, in the actual code 'foobar' is of course a non-trivial function of
value. I assume that this does not change the validity of the
construction...?


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE during SELECT

[Pavel Ivanov]

> for value in "SELECT main_column IN mytable":
>   UPDATE mytable SET other_column='foobar' WHERE main_column=value

Exactly this sequence is safe. Things can go nuts in case if you have
index on other_column and you do something like this:

for value in "SELECT main_column IN mytable WHERE other_column='something'":
  UPDATE mytable SET other_column='foobar' WHERE main_column=value


Pavel

On Thu, Nov 18, 2010 at 7:41 PM, Nikolaus Rath  wrote:
> Hello,
>
> I understand that running INSERT or DELETE during an active SELECT query
> can get me into trouble. But is it safe to run (in pseudocode):
>
> for value in "SELECT main_column IN mytable":
>
>   UPDATE mytable SET other_column='foobar' WHERE main_column=value
>
> ?
>
>
> Thanks,
>
>   -Nikolaus
>
> --
>  »Time flies like an arrow, fruit flies like a Banana.«
>
>  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
> ___
> 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] UPDATE during SELECT

[Igor Tandetnik]

Nikolaus Rath  wrote:
> "Igor Tandetnik"  writes:
>> Nikolaus Rath  wrote:
>>> I understand that running INSERT or DELETE during an active SELECT query
>>> can get me into trouble. But is it safe to run (in pseudocode):
>>> 
>>> for value in "SELECT main_column IN mytable":
>>> 
>>>   UPDATE mytable SET other_column='foobar' WHERE main_column=value
>> 
>> It should be safe. Though I don't see how this would be different than
>> just updating all rows in a single query:
>> 
>> UPDATE mytable SET other_column='foobar';
> 
> Well, in the actual code 'foobar' is of course a non-trivial function of
> value.

See if you can implement it as a custom function (see sqlite3_create_function), 
then do

UPDATE mytable SET other_column=foobar(main_column);

-- 
Igor Tandetnik


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

Re: [sqlite] Custom collating sequences and performance

[Duquette, William H (316H)]

On 11/18/10 8:57 PM, "Dan Kennedy"  wrote:

On 11/19/2010 05:22 AM, Duquette, William H (316H) wrote:
> On 11/18/10 2:16 PM, "Drake Wilson"  wrote:
>
> Quoth "Duquette, William H (316H)", on 
> 2010-11-18 14:08:10 -0800:
>> It seems to me that it shouldn't be necessary for SQLite to evaluate
>> FOO's comparison function when doing queries on mytable; the
>> collation order should be implicit in the mykey column's index.  Is
>> this in fact the case?
>
> When doing which queries?
>
> How do you propose to look up a key value in the index without using
> the collation function?
>
> I was thinking of queries like
>
>  SELECT mykey FROM mytab ORDER BY mykey

The collation sequence callback will not be invoked in that
case. SQLite will just iterate from start to finish of the
index b-tree. You do need to have the collation sequence
registered when compiling the query though.

Dan.

Thanks, Dan.  That's what I was expecting. :-)

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

--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] creating a sqlite db on raw disk ?

[Yang]

Thanks for your detailed explanation.

for your first question: I mean creating a new database file.

since you asserted "
>> innodb allows creating a db on a raw disk partition, can we do the
>> same on  sqlite?
>
> No.  You need a file system of some kind.  Or, at least, your operating 
> system needs to be able to address your storage using file-system calls, not 
> storage-structure calls.
",  that answers my question.



On Fri, Nov 19, 2010 at 5:38 AM, Simon Slavin  wrote:
>
> On 19 Nov 2010, at 7:04am, Yang wrote:
>
>> when I create a db on a file system, I guess a query process
>
> Wait ... are you talking here about creating a new database file or querying 
> one what already exists ?
>
>> has to go
>> through 2 levels of seeks ?
>> first sqlite finds the B-tree node that stores the index to the file
>> offset of my desired record, then sqlite uses that offset to make
>> syscall seek(offset),
>> then Kernel consults the FS implementation to find from its  OWN
>> B-tree (for example in ext3 fs )  the block location of that offset.
>
> Assuming that you're using a FS that uses B-trees, nodes, and indices (many 
> don't), that's a fair summary of what happens.  But SQLite runs fine on many 
> embedded systems that use linked lists instead of B-trees, or don't use 
> nodes, or have databases stored in non-writable sequential memory.
>
>> innodb allows creating a db on a raw disk partition, can we do the
>> same on  sqlite?
>
> No.  You need a file system of some kind.  Or, at least, your operating 
> system needs to be able to address your storage using file-system calls, not 
> storage-structure calls.
>
>> I tried directly creating a db on ramdisk, failed:
>>
>> javasqlite-20100727# sqlite3 /dev/ram0
>
> You didn't tell it what to call the database, just where you wanted it.  I 
> think you want something like
>
> # sqlite3 /dev/ram0/myDatabase.sqlite
>
> There's no reason this shouldn't work if your drivers and your version of 
> *n*x /fully/ support the use of /deb/ram0 for file storage, including support 
> for locking calls.  Works fine under Mac OS X, by the way.
>
> But SQLite does provide its own way of creating a database in memory.  See
>
> http://www.sqlite.org/inmemorydb.html
>
> So you'd use something like
>
> # sqlite3 :memory:
>
> Of course, that database will be visible only to the process that creates it. 
>  Which means that if you use the command-line tool to create it it will 
> effectively disappear as you quit the command-line tool.
>
> If you're using Linux or a Unix that supports it, you can also look into the 
> shared memory filesystem 'tmpfs'.  I haven't tried it, but this should also 
> support SQLite without any problems.
>
> 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

[sqlite] Bug or Problem in Sqlite3.exe when importing UTF8

[Jens Hantschel]

Hello, 
 
I am facing problems when importing a file to a database-table with sqlite3.exe 
and ".import"-parameter.
 
The file is encoded in UTF8 an every time i import the file in the first column 
of the first row there is a special character occuring in 
the field.
 
I can't use encoding ASCII or ANSI because I want to display special characters 
like ä,ö,ü,ß
If I import the file with encoding ASCII or ANSI the special character is not 
occuring but then I can't display special characters.
 
Have you got any hint for me?
 
Kind Regards, 
Jens Hantschel

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

[sqlite] When is corruption acceptable?

[Dustin Sallings]

I have an application that was writing to a sqlite db until we ran out 
of disk space.  The app failed to store data, commit, etc... and then was 
killed.

I've got the data moved off to the side after it broke.  integrity 
check says this:

*** in database main ***
Main freelist: 231854296 of 5 pages missing from overflow list starting at 
7184967
On tree page 187114 cell 0: 4 of 5 pages missing from overflow list starting at 
5920373
Page 5510841 is never used
Page 5985431 is never used
Page 6127423 is never used
Page 6324952 is never used
Page 7156369 is never used
Page 7179495 is never used
Page 7184966 is never used
Page 7184968 is never used


-- clearly, overflow broke a bit.  I can do a count(*) on the table, 
but I can't grab all of the rows due to overflow.

It seems a bit... delicate at this point, which leads to two questions:

1. When is it acceptable for sqlite to leave a corrupt database that 
can't be used?
2. Is there any way to recover the data that didn't get corrupt (which 
should be lots)?

-- 
Dustin Sallings

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

Re: [sqlite] When is corruption acceptable?

[Black, Michael (IS)]

Did you try and do an .export of your database?
That would be my first thing to try.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Dustin Sallings
Sent: Fri 11/19/2010 2:12 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL:[sqlite] When is corruption acceptable?




I have an application that was writing to a sqlite db until we ran out 
of disk space.  The app failed to store data, commit, etc... and then was 
killed.

I've got the data moved off to the side after it broke.  integrity 
check says this:

*** in database main ***
Main freelist: 231854296 of 5 pages missing from overflow list starting at 
7184967
On tree page 187114 cell 0: 4 of 5 pages missing from overflow list starting at 
5920373
Page 5510841 is never used
Page 5985431 is never used
Page 6127423 is never used
Page 6324952 is never used
Page 7156369 is never used
Page 7179495 is never used
Page 7184966 is never used
Page 7184968 is never used


-- clearly, overflow broke a bit.  I can do a count(*) on the table, 
but I can't grab all of the rows due to overflow.

It seems a bit... delicate at this point, which leads to two questions:

1. When is it acceptable for sqlite to leave a corrupt database that 
can't be used?
2. Is there any way to recover the data that didn't get corrupt (which 
should be lots)?

--
Dustin Sallings

___
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] When is corruption acceptable?

[Pavel Ivanov]

>        1. When is it acceptable for sqlite to leave a corrupt database that 
> can't be used?

It's never acceptable. SQLite specifically written and tested to
manager "out of disk space" errors appropriately without database
corruption.

>        I've got the data moved off to the side after it broke.

Did you move journal along with database? If not you made a big
mistake. If it's not deleted yet and you didn't change anything in the
database you can close all connections to it, put journal nearby and
run command line utility again. After that integrity check should be
okay.

>        2. Is there any way to recover the data that didn't get corrupt (which 
> should be lots)?

If there's no way to restore journal then you can only try .dump
command from command line utility.


Pavel

On Fri, Nov 19, 2010 at 3:12 PM, Dustin Sallings  wrote:
>
>        I have an application that was writing to a sqlite db until we ran out 
> of disk space.  The app failed to store data, commit, etc... and then was 
> killed.
>
>        I've got the data moved off to the side after it broke.  integrity 
> check says this:
>
> *** in database main ***
> Main freelist: 231854296 of 5 pages missing from overflow list starting at 
> 7184967
> On tree page 187114 cell 0: 4 of 5 pages missing from overflow list starting 
> at 5920373
> Page 5510841 is never used
> Page 5985431 is never used
> Page 6127423 is never used
> Page 6324952 is never used
> Page 7156369 is never used
> Page 7179495 is never used
> Page 7184966 is never used
> Page 7184968 is never used
>
>
>        -- clearly, overflow broke a bit.  I can do a count(*) on the table, 
> but I can't grab all of the rows due to overflow.
>
>        It seems a bit... delicate at this point, which leads to two questions:
>
>        1. When is it acceptable for sqlite to leave a corrupt database that 
> can't be used?
>        2. Is there any way to recover the data that didn't get corrupt (which 
> should be lots)?
>
> --
> Dustin Sallings
>
> ___
> 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] problem reading a row of data

[Jim Crafton]

I'm trying to use a select statement to read a row of data from a DB.
I have created the db myself, using another program that uses sqlite,
and have verified (using a gui SQlite db browser) that the file does
in fact have data in it (2 tables, both about 1.3 million rows, the
file is around 150Mb in size).

The table schema (for the one I'm interested in) looks like so:

CREATE TABLE IF NOT EXISTS RFLogIndex ( idxID INTEGER PRIMARY KEY,
masterSequence INTEGER,  logFileOffset INTEGER );

After opening the db with sqlite3_open() I attempt to execute a select
statement.

The select statement I'm trying to execute is trivial:

select * from RFLogIndex where idxID = 1;

I do this using sqlite3_prepare() and that returns SQLITE_OK

I call sqlite3_step() and get a return code of SQLITE_DONE (101),
implying there's no data! Which is most definitely NOT correct,
because when I run the same query in the GUI browser, I get exactly 1
row back, as expected.

The code is running in a simple command line program, so no extra
threads. I've built sqlite myself (running on Windows XP SP 3 32bit,
Visual Studio 2008), and I'm using it successfully in other programs
and they all query just fine.

I'm doing something wrong, but I just can't see it yet. Any ideas?

Thanks

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

Re: [sqlite] problem reading a row of data

[Black, Michael (IS)]

Try showing us your code...sounds like you're doing something wrong if the 
query works elsewhere.
 
Here's my simple example that does something similar to what you describe:
 
#include 
#include "sqlite3.h"
main()
{
sqlite3 *db;
sqlite3_stmt *stmt;
char *errmsg=NULL;
char *sql;
remove("update.db");
sqlite3_open("update.db",);
sqlite3_exec(db,"CREATE TABLE t (a int, b int)",NULL,NULL,);
sqlite3_exec(db,"insert into t values(1,1)",NULL,NULL,);
sqlite3_exec(db,"insert into t values(1,2)",NULL,NULL,);
sql = "SELECT * FROM t where a>0";
sqlite3_prepare_v2(db,sql,strlen(sql),,NULL);
while(sqlite3_step(stmt)==SQLITE_ROW) {
char sqlbuf[4096];
int ref;
ref = sqlite3_column_int(stmt,1);
printf("Before %d\n",ref);
sprintf(sqlbuf,"UPDATE t set a=a+1 where a=%d",ref);
puts(sqlbuf);
sqlite3_exec(db,sqlbuf,NULL,NULL,);
}
sqlite3_finalize(stmt);
sqlite3_close(db);
}
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Jim Crafton
Sent: Fri 11/19/2010 2:25 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] problem reading a row of data



I'm trying to use a select statement to read a row of data from a DB.
I have created the db myself, using another program that uses sqlite,
and have verified (using a gui SQlite db browser) that the file does
in fact have data in it (2 tables, both about 1.3 million rows, the
file is around 150Mb in size).

The table schema (for the one I'm interested in) looks like so:

CREATE TABLE IF NOT EXISTS RFLogIndex ( idxID INTEGER PRIMARY KEY,
masterSequence INTEGER,  logFileOffset INTEGER );

After opening the db with sqlite3_open() I attempt to execute a select
statement.

The select statement I'm trying to execute is trivial:

select * from RFLogIndex where idxID = 1;

I do this using sqlite3_prepare() and that returns SQLITE_OK

I call sqlite3_step() and get a return code of SQLITE_DONE (101),
implying there's no data! Which is most definitely NOT correct,
because when I run the same query in the GUI browser, I get exactly 1
row back, as expected.

The code is running in a simple command line program, so no extra
threads. I've built sqlite myself (running on Windows XP SP 3 32bit,
Visual Studio 2008), and I'm using it successfully in other programs
and they all query just fine.

I'm doing something wrong, but I just can't see it yet. Any ideas?

Thanks

Jim
___
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] FTS3 snippets() grouping

[Matthew Leffler]

A question:  Is is possible to group the data from the snippets() column in a 
result?  The query would look something like the following (which doesn't work):

>> SELECT snippet(search, '', '', '...') as extract, count() as count FROM 
>> search WHERE content MATCH 'search term' GROUP BY extract ORDER BY count;

That would allow ordering by frequency of a given match snippet.

Thanks!

Matthew Leffler

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

Re: [sqlite] problem reading a row of data

[Jim Crafton]

Thanks God it's Friday. I'm an utter and complete idiot. I was using
the wrong filename. Misspelled it by a single character. I'm going to
pretend this never happened
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] joining two sequences?

[Petite Abeille]

Hello,

Given two tables describing sequences of key value pairs, what would be a 
reasonable way to join them?

For example, assuming two table foo and bar with identical structure:

create temporary table foo 
(
key integer not null,
value   text not null,
constraint  foo_pk primary key( key, value )
);

create temporary table bar
(
key integer not null,
value   text not null,
constraint  bar_pk primary key( key, value )
);

And a set of sequences in each of the table:

insert into foo values( 1, 'a' );

insert into foo values( 2, 'a' );
insert into foo values( 2, 'b' );

insert into foo values( 3, 'a' );
insert into foo values( 3, 'b' );
insert into foo values( 3, 'c' );

insert into bar values( 4, 'a' );
insert into bar values( 4, 'b' );

What would be a good way to join foo( 2, 'a', )( 2, 'b' ) to bar( 4, 'a', )( 4, 
'b' )? In other words, join the sequences with the same values?

Right now, I'm using group_concat to flatten the sequences:

select  *
from(
select  key,
group_concat( value ) as value
fromfoo

group bykey
)
as  foo

join(
select  key,
group_concat( value ) as value
frombar

group bykey
)
as  bar
on  bar.value = foo.value

Which results in:

key|value|key|value
2|a,b|4|a,b

All good, if perhaps clunky.

But the documentation for group_concat mention that the order of the 
concatenated elements is arbitrary [1]. Which perhaps would preclude 
group_concat from being reliably use as a join predicate, no?

Could someone think of a nice alternative to group_concat to join such data 
structure?

Thanks in advance.

Cheers,

PA.

[1] http://www.sqlite.org/lang_aggfunc.html








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

Re: [sqlite] FTS3 snippets() grouping

[Petite Abeille]

On Nov 19, 2010, at 9:46 PM, Matthew Leffler wrote:

> A question:  Is is possible to group the data from the snippets() column in a 
> result?  The query would look something like the following (which doesn't 
> work):
> 
>>> SELECT snippet(search, '', '', '...') as extract, count() as count FROM 
>>> search WHERE content MATCH 'search term' GROUP BY extract ORDER BY count;
> 
> That would allow ordering by frequency of a given match snippet.

Do it in two steps perhaps?

select  result.extract as extract,
count( * ) as count
from(
select  snippet( search, '', '', '...' ) as extract
fromsearch 
where   content match 'search term'
)
as  result

group byresult.extract

order bycount desc

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

Re: [sqlite] FTS3 snippets() grouping

[Matthew Leffler]

I thought of that but I get an error with that query:

>> unable to use function snippet in the requested context

Thanks!

Matthew

On Nov 19, 2010, at 2:08 PM, Petite Abeille wrote:

> 
> On Nov 19, 2010, at 9:46 PM, Matthew Leffler wrote:
> 
>> A question:  Is is possible to group the data from the snippets() column in 
>> a result?  The query would look something like the following (which doesn't 
>> work):
>> 
 SELECT snippet(search, '', '', '...') as extract, count() as count FROM 
 search WHERE content MATCH 'search term' GROUP BY extract ORDER BY count;
>> 
>> That would allow ordering by frequency of a given match snippet.
> 
> Do it in two steps perhaps?
> 
> select  result.extract as extract,
>count( * ) as count
> from(
>select  snippet( search, '', '', '...' ) as extract
>fromsearch 
>where   content match 'search term'
>)
> as  result
> 
> group byresult.extract
> 
> order bycount desc
> 
> ___
> 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] how can I use a larger cache ? "Application Defined Page Cache." ??

[Yang]

I read from the docs that by setting
"PRAGMA cache_size = Number-of-pages;"
(http://www.sqlite.org/pragma.html#pragma_cache_size)
I can use a larger cache, but the same paragraph mentions that how
much is used is actually "at the discretion of the application defined
page cache"

I do not set anything for the "application defined page cache", so
what is the behavior of the default one?
the application page cache page does not describe this.
with the default "application defined page cache", would it utilize
all of the cache I declared ?

I am hoping to use a very large cache, for example 1G

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

Re: [sqlite] FTS3 snippets() grouping

[Petite Abeille]

On Nov 19, 2010, at 10:14 PM, Matthew Leffler wrote:

> I thought of that but I get an error with that query:
> 
>>> unable to use function snippet in the requested context
> 

Pesky function :P

Then, if everything else fails, you could write it down in a temp table, and 
group that sigh...

drop table if exists result;

create temp table if not exists result as

select  snippet( search, '', '', '...' ) as extract
fromsearch 
where   content match 'search term';

select  result.extract as extract,
count( * ) as count
fromresult

group byresult.extract

order bycount desc;

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

Re: [sqlite] When is corruption acceptable?

[Dustin Sallings]

On Nov 19, 2010, at 12:17, Pavel Ivanov wrote:

>>1. When is it acceptable for sqlite to leave a corrupt database that 
>> can't be used?
> 
> It's never acceptable. SQLite specifically written and tested to
> manager "out of disk space" errors appropriately without database
> corruption.

Yes, this is the argument I've been making, and it kind of comes back 
to me when stuff is corrupt.  :/  Then people ask me why I don't just make 
something better.  While I do think I could build something that fit our 
application a little better, my confidence in sqlite is higher than whatever I 
come up with over the weekend.

>>I've got the data moved off to the side after it broke.
> 
> Did you move journal along with database? If not you made a big
> mistake. If it's not deleted yet and you didn't change anything in the
> database you can close all connections to it, put journal nearby and
> run command line utility again. After that integrity check should be
> okay.

I didn't actually do the move, but I've been told all of the files came 
together while the application was shut down.  There is no journal.

I've read the shell history and found that it appears that someone 
copied the files out from under the application while it was still running.  
This copy is what was corrupt.  We later removed the original and replaced them 
with their copy.

So, yay sqlite, confidence restored (now just wait for the data to be).

>>2. Is there any way to recover the data that didn't get corrupt 
>> (which should be lots)?
> 
> If there's no way to restore journal then you can only try .dump
> command from command line utility.


Thank you for the suggestion.  This looks like it's going to be helpful 
to get a lot of the data out.

-- 
Dustin Sallings

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

Re: [sqlite] creating a sqlite db on raw disk ?

[Jay A. Kreibich]

On Thu, Nov 18, 2010 at 11:04:02PM -0800, Yang scratched on the wall:

> innodb allows creating a db on a raw disk partition, can we do the
> same on  sqlite?

  Not out of the box, but it could be done by writing a VFS driver.
  It is an idea I've toyed with, but I don't really have the low-level
  raw device skills, nor enough motivation to learn.  It would be an
  interesting project, however.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] creating a sqlite db on raw disk ?

[Richard Hipp]

On Fri, Nov 19, 2010 at 10:09 AM, Jay A. Kreibich  wrote:

> On Thu, Nov 18, 2010 at 11:04:02PM -0800, Yang scratched on the wall:
>
> > innodb allows creating a db on a raw disk partition, can we do the
> > same on  sqlite?
>
>   Not out of the box, but it could be done by writing a VFS driver.
>  It is an idea I've toyed with, but I don't really have the low-level
>  raw device skills, nor enough motivation to learn.  It would be an
>  interesting project, however.
>

Much of the work has already been done.  See
http://www.sqlite.org/src/artifact?name=40cf9e212a377a6511469384a64b01e6e34b2eec



>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> 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] threads and last_insert_rowid()

[Jay A. Kreibich]

On Tue, Nov 16, 2010 at 09:35:21AM -0500, Pavel Ivanov scratched on the wall:

> But as I see in SQLite sources sqlite3_exec does acquire
> connection's mutex, so nothing can be executed in between statements.
> Thus if you execute this line as one call to sqlite3_exec then it
> won't suffer from threading.

  Humm... I missed that.  I knew that _exec() doesn't actually open a
  proper transaction (since it has no idea what the transaction state
  might be), but I didn't catch that it still grabs the mutex to
  prevent issues with threads.
  
  Clever.



  More teo the point, that means a single _exec() call with the INSERT,
  followed by a call to last_insert_rowid() should always do the right
  thing.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] joining two sequences?

[Jim Morris]

This should return a the equivalent keys in the two maps.  The basic 
idea is to compare the values in each key in foo(left outer join foo) 
with the values for each key in bar where there are any matching 
values(left outer join bar) and only select those with a complete match( 
inner join).  Not sure this is the most efficient way.

select distinct f1 .key as foo_key, b1.key as bar_key
from bar b1
inner join foo f1 on f1 .value = b1.value
where
not exists
(
-- Values for a particular key in foo
select f3.value from foo f3 left outer join bar b3 on b3.value= f3.value 
WHERE f3.key= f1.key
union
-- Values for a particular key in bar
select f3.value from bar b3 left outer join foo f3 on b3.value= f3.value 
WHERE b3.key = b1.key
except
-- Values common to both foo key and bar key
select f2.value from foo f2 inner join bar b2 on b2.value = f2.value 
WHERE b2.key = b1.key AND f2.key= f1.key
);



On 11/19/2010 1:03 PM, Petite Abeille wrote:
> Hello,
>
> Given two tables describing sequences of key value pairs, what would be a 
> reasonable way to join them?
>
> For example, assuming two table foo and bar with identical structure:
>
> create temporary table foo
> (
>  key integer not null,
>  value   text not null,
>  constraint  foo_pk primary key( key, value )
> );
>
> create temporary table bar
> (
>  key integer not null,
>  value   text not null,
>  constraint  bar_pk primary key( key, value )
> );
>
> And a set of sequences in each of the table:
>
> insert into foo values( 1, 'a' );
>
> insert into foo values( 2, 'a' );
> insert into foo values( 2, 'b' );
>
> insert into foo values( 3, 'a' );
> insert into foo values( 3, 'b' );
> insert into foo values( 3, 'c' );
>
> insert into bar values( 4, 'a' );
> insert into bar values( 4, 'b' );
>
> What would be a good way to join foo( 2, 'a', )( 2, 'b' ) to bar( 4, 'a', )( 
> 4, 'b' )? In other words, join the sequences with the same values?
>
> Right now, I'm using group_concat to flatten the sequences:
>
> select  *
> from(
>  select  key,
>  group_concat( value ) as value
>  fromfoo
>
>  group bykey
>  )
> as  foo
>
> join(
>  select  key,
>  group_concat( value ) as value
>  frombar
>
>  group bykey
>  )
> as  bar
> on  bar.value = foo.value
>
> Which results in:
>
> key|value|key|value
> 2|a,b|4|a,b
>
> All good, if perhaps clunky.
>
> But the documentation for group_concat mention that the order of the 
> concatenated elements is arbitrary [1]. Which perhaps would preclude 
> group_concat from being reliably use as a join predicate, no?
>
> Could someone think of a nice alternative to group_concat to join such data 
> structure?
>
> Thanks in advance.
>
> Cheers,
>
> PA.
>
> [1] http://www.sqlite.org/lang_aggfunc.html
>
>
>
>
>
>
>
>
> ___
> 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] page cache vs OS cache

[Yang]

I wonder why we need page cache, since we already have OS file cache.

in other words, if we simply set OS cache to be very large, would it
have the same effect as page cache?

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

Re: [sqlite] EXTERNAL:Re: Strange Corruption

[Black, Michael (IS)]

The idea that integrity_check is 100% foolproof is wishful thinking.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert
Sent: Tue 11/16/2010 8:28 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] Strange Corruption



.dump (3.6.23.1) and afterwards "sqlite3 /tmp/new.db < /tmp/dump.sql"
with 3.7.2 worked and fixed the .backup problem (as expected).

As I've already downgraded sqlite3 in our new firmware and patched the
live-systems that were running with the new firmware I'll only have one
machine to check whether the error comes back or not... So maybe I'll
not be able to give feedback for a few weeks (as I can not enforce errors).

However: if the error will not come back and could have to do with an
already existing error in the database it would be quite interesting to
know why integrity_check doesn't find the error before making a backup.
(What means that a bug exists in any case: either in PRAGMA
integrity_check or in the backup function).

---
Pirmin Walthert



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