[sqlite] cnt(x) what do the brackets signify

I am looking at recursive cte and saw this example I have googled but can't see what cnt(x) actually signifies - cnt is not a function - could someone point me to a resource so I can understand this construct Thanks WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000

Re: [sqlite] Index without backing table

> On Mon, 03 Nov 2014 11:50:17 +0200 > Paul wrote: > > > > > Would be nice to have ability to store both key and payload in the > > > > index. (Let's call it index-only table) > > > > This could be a feature that sets some limitations on a tabl

Re: [sqlite] Index without backing table

3 November 2014, 13:56:36, by "Richard Hipp" : > On Mon, Nov 3, 2014 at 6:48 AM, Clemens Ladisch wrote: > > > Paul wrote: > > > Are additional indices, created for WITHOUT ROWID, potentially less > > > efficient and more cumbersome? > > >

Re: [sqlite] Index without backing table

3 November 2014, 13:48:30, by "Clemens Ladisch" : > Paul wrote: > > Are additional indices, created for WITHOUT ROWID, potentially less > > efficient and more cumbersome? > > For tables with a rowid, the index stores the indexed columns and the > rowid. F

Re: [sqlite] Index without backing table

> > On 3 Nov 2014, at 9:50am, Paul wrote: > > > So, to be clear, WITHOUT ROWID table will have it's PRIMARY KEY > > as a replacement for ROWID and table itself is an index? > > It would appear that the answer is "yes". I'm not going to go bey

Re: [sqlite] Index without backing table

> > > Would be nice to have ability to store both key and payload in the > > index. (Let's call it index-only table) > > This could be a feature that sets some limitations on a table, like > > being unable to have more than one index or inefficient table scans, > > but it will also give some adva

Re: [sqlite] Index without backing table

eing unable to have more than one index or inefficient table scans, but it will also give some advantage in special cases like mine. - About your case, Edward. I believe you also need mapping where Key is Big (text) but Value is small (offset in a file?)? >

Re: [sqlite] Index without backing table

31 October 2014, 14:19:56, by "Richard Hipp" : > I don't really understand what you are asking, but I suspect that > http://www.sqlite.org/withoutrowid.html is probably the answer you are > looking for. I have just realized, how stupid my question is. Actually I don't even have to use w/o rowi

[sqlite] Index without backing table

Is there a way to have index without table? Is it possible to achieve this with virtual talbe tricks but without implementing B-tree myself? I need this both for space effciency and to minimize disk I/O (by avoiding table updates). ___ sqlite-users ma

Re: [sqlite] [regression] SQLite 3.8.7 causes Evolution to crash

Dear Richard, Am Mittwoch, den 22.10.2014, 21:53 -0400 schrieb Richard Hipp: > On Wed, Oct 22, 2014 at 5:14 PM, Paul Menzel wrote: > > after the upgrade of libsqlite3 from 3.8.6 to 3.8.7 Evolution crashes > > with a segmentation fault. > > > > pool[6371]: s

[sqlite] [regression] SQLite 3.8.7 causes Evolution to crash

n > you'll use "the affected" sqlite version. It’d be great if you could open a bug report in the SQLite bug tracker and help analyzing the bug. Please find the backtrace pasted at the end of the message. Thanks, Paul [1] https://bugzilla.gnome.org/show_bug.cgi?id=738965 [2]

[sqlite] [regression] SQLite 3.8.7 causes Evolution to crash

n > you'll use "the affected" sqlite version. It’d be great if you could open a bug report in the SQLite bug tracker and help analyzing the bug. Please find the backtrace pasted at the end of the message. Thanks, Paul [1] https://bugzilla.gnome.org/show_bug.cgi?id=738965 [2]

[sqlite] what is wrong with this query

I have two versions of the same table with minor differences. I open one database (test1) and attach the second (test2) SELECT test2.table1.* FROM test2.table1 LEFT JOIN table1 table11 ON test2.table1.id = table11.id Gives an error condition near "*": syntax error. provide an alias for the

Re: [sqlite] Full outer joins

Really!! I can accept that it would not be needed as often as other joins but I can imagine that anyone who wrtes software that populates databases and who subsequently changes their software or needs to benchmark against some other datasource would find this useful. Cheers Paul On 22 October

[sqlite] Full outer joins

I have two tables from two versions of the same database each table has an integer id primary key I want to find the rows that dont appear in both tables. So I think I need a full outer join which I understand is not supported by SQLite I have seen this which gives an alternative, but it seems v

Re: [sqlite] line break, or similar, in query results

Thanks Clemens Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC

[sqlite] line break, or similar, in query results

on (it doesn't have to be a newline - just soemthng to make it more readable) * I know texas isn't a city :) Cheers Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit

Re: [sqlite] decoding a bitmask

Thanks all - agree rookie mistake with xor, had this been a C++ exercise I would have have been OK - SQL seems to make my mind go blank... Thanks for the case explanation Mark - v helpful. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http

Re: [sqlite] decoding a bitmask

CASE visits.transition & 0xFF00 WHEN 0xC000 THEN 'Is_Redirect_Mask' ELSE '' END ) AS Qualifiers The query is on a visits table from a google chrome history database. The query seems to work OK if a single bit is set, but fails (a blank string is re

[sqlite] decoding a bitmask

I have a table with an integer value which is a bitmask. one or more of the bits can be set and each bit has a corresponding meaning. so using the windows file attribute as an example we have 0c01 readonly 0x02 hidden 0x04 system 0x10 directory 0x20 archive none, any or all could be set I'd lik

Re: [sqlite] following a trail of references

Thanks both - modified the code for my example but it failed :( I'll keep at it and see if I can get my head around it. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-To

[sqlite] following a trail of references

I think this might be beyond the ability of SQL - but there are cleverer people on here than me, so I might be wrong :) Lets say we have a table Create table (id integer primary key, previousid integer, location text) previousid contains a pointer to ID or 0 for no previous id, so for instance w

Re: [sqlite] Behavior change: INTEGER PRIMARY KEY and PRAGMA index_list

You were totally correct, read data after done. Thanks for your reply. -Paul -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch Sent: Sunday, October 05, 2014 3:05 AM To: sqlite-users@sqlite.org Subject: Re

[sqlite] Best page size and cache size in high memory environment

, does it make sense to change the page size only for the temp database? "pragma temp.page_size=65536" ? Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Behavior change: INTEGER PRIMARY KEY and PRAGMA index_list

ger primary key index is "" (empty string). Is this behavior change by design? We are working around it by checking for the "" (empty string) named index. Is there a better way to identify this index returned by the Pragma is the automatically create one? Thanks -Paul Quinn

[sqlite] Interrupt PRAGMA integrity_check

It appears that the long-running task of 'PRAGMA integrity_check' does not respond to being cancelled during operation. We've experimented with "sqlite3_interrupt" and "sqlite3_progress_handler" both which say they can interrupt long running tasks. Looking at SQLite's implementation it appears t

Re: [sqlite] 2 joins on one table

Lovely - thanks On 30 September 2014 00:14, David Empson wrote: > > On 30/09/2014, at 12:04 pm, Paul Sanderson > wrote: > > > I two tables of the form > > > > create table1 (person1 text, person2 text) > > create table2 (person text, picture blob) > >

[sqlite] 2 joins on one table

I two tables of the form create table1 (person1 text, person2 text) create table2 (person text, picture blob) Is it possible to create a join so I can get a resultant dataset of the form person1, person1picture, person2, person2picture Thanks ___ sqli

Re: [sqlite] Atomic database structure initialization

Thank you for help, guys! I knew that sqlite is a great piece of software, now I have even more proofs :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

> > > Paul wrote: > > > I can check whether user_version matches magic number without transaction. > > > > No. Executing "PRAGMA user_version" will start an automatic transaction > > if you didn't already start an explicit one. > > &

Re: [sqlite] Atomic database structure initialization

> > On 22 Sep 2014, at 1:13pm, Paul wrote: > > > The only thing I am worried about is whether > > > > pragma user_version=n; > > > > respects transactions and will be rolled back automatically in case > > if something happens between that sta

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > > I can check whether user_version matches magic number without transaction. > > No. Executing "PRAGMA user_version" will start an automatic transaction > if you didn't already start an explicit one. > > > Only when user_version does

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > >> pragma user_version; > >> > >> returns a single row with a single value which is the version, and the > >> command, > >> > >> pragma user_version=n; > >> > >> lets you change it to n. Perhaps you can

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > >> pragma user_version; > >> > >> returns a single row with a > single value which is the version, and the command, > >> > >> pragma > user_version=n; > >> > >> lets you change it to n. Perhaps you can

Re: [sqlite] Atomic database structure initialization

> > There is also a PRAGMA user_version (see > http://www.sqlite.org/pragma.html#pragma_schema_version) which will let you > store a number in the database header so you can keep track of what version > of the "user schema" you have implemented in the database. Initially, when > the database

Re: [sqlite] Atomic database structure initialization

> > On 19 Sep 2014, at 8:34am, Paul wrote: > > > if database file is missing it must be created and initialized. > > For that purpose I need to provide a guarantee that *on_create* callback > > will be called strictly once. > > Can you check to see wheth

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > >> Paul wrote: > >>> My goal is to make structure initialization of an *abstract* database > >>> atomic. > >>> [...] if database file is missing it must be created and initialized. > >> > >> <http://www.sqlite

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > > My goal is to make structure initialization of an *abstract* database > > atomic. > > [...] if database file is missing it must be created and initialized. > > <http://www.sqlite.org/transactional.html> > > Just do the check for the da

[sqlite] Atomic database structure initialization

this, I think, common problem has standard ways of solving. Regards, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

It all helped me thanks - just not in solving this particular problem (which I have now manage to do) Thanks all. On 16 September 2014 19:33, jose isaias cabrera wrote: > > "RSmith" wrote... > > >> On 2014/09/16 15:32, Paul Sanderson wrote: >> >>>

Re: [sqlite] Create join and add unique column

ginal type definition :( On 16 September 2014 18:18, Paul Sanderson wrote: > Thanks Ryan. That doesn't work for me though as I am looking for a generic > solution that will work on multiple tables - so no hard coding of column > definitions :( > > I think I am getting there > >

Re: [sqlite] Create join and add unique column

Thanks Ryan. That doesn't work for me though as I am looking for a generic solution that will work on multiple tables - so no hard coding of column definitions :( I think I am getting there On 16 September 2014 15:38, RSmith wrote: > > On 2014/09/16 15:32, Paul Sanderson wrote:

Re: [sqlite] Create join and add unique column

select _rowid_, * from tab3 does the trick - thanks all On 16 September 2014 13:13, Paul Sanderson wrote: > _rowid_ is probably the answer with a temporary table > > On 16 September 2014 13:00, Paul Sanderson > wrote: > >> Thanks - I like the temporary table idea, but

Re: [sqlite] Create join and add unique column

_rowid_ is probably the answer with a temporary table On 16 September 2014 13:00, Paul Sanderson wrote: > Thanks - I like the temporary table idea, but now sure how it would work. > > say for instance I have two tables > > create tab1 (id int, name text) > 1, 'paul'

Re: [sqlite] Create join and add unique column

Thanks - I like the temporary table idea, but now sure how it would work. say for instance I have two tables create tab1 (id int, name text) 1, 'paul' 2, 'helen' 3, 'melanie' create tab2 (id int, country text) 1, 'uk' 2, 'scotland' I can cr

[sqlite] Create join and add unique column

I want to create a join on two tables and add a unique number to each returned row. Can this be done with a SQL query? Thanks -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786 http

Re: [sqlite] PRAGMA cache_size;

> On Fri, Sep 12, 2014 at 5:00 AM, Paul wrote: > > > > > I have a few questions regarding 'cache_size' PRAGMA: > > > > (Q1) Does this PRAGMA affects all attached databases or should I set it > > for each > > attached database ind

[sqlite] PRAGMA cache_size;

t it to 100, will it mean every database (attached or main) have its own separate cache of size 100 * page_size or is it single, shared cache? Thank you, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-

Re: [sqlite] fts5

> Fts5 is still in the experimental stage at the moment. > > If anybody has any ideas for useful features, or knows of problems with > FTS4 that could be fixed in FTS5, don't keep them to yourself! > It would be awesome to be able to select document ids in the content-less FTS4 table.

[sqlite] Reinstalling My Build Environment

d and they are hijacking your support email group, it's obvious that the developers on System.Data.SQLite are not supporting their own products, so I will not pursue this any further. Please forgive the intrusion, Paul ___ sqlite-users mailing l

[sqlite] Reinstalling My Build Environment

SQLite is a great database, but I've had nothing but problems deploying it to a clean machine. There is supposed to be nothing extra to do when moving the files from the bin directory to the target machine, but then I hear rumblings about an unknown missing C++ redistribution package that may need

Re: [sqlite] how best to determine # of rows in a table

What about canonical SELECT COUNT(*) FROM table; ? 27 August 2014, 16:51:23, Author "Mark Halegua" > this may seem like a small issue, but I'm not sure if the solutions I've > found > on the web will do what I want in a low memory situation.I'd like to iterate > through a table one row

Re: [sqlite] Improve query performance using a join

cords (primary keys) that's millions of rows (in > the main 150 column table) where the precompiled SQL query doesn't have to > be executed. Cheers for that, I'll do some testing and see how I go! Thanks, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Improve query performance using a join

e, and implement it when the cost of doing so is not much more than not doing it. I have some evidence that the approach has worked for me in the past. Thanks, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Improve query performance using a join

to insert into two tables instead of one? I use "INSERT OR REPLACE" for my loading, with a single index. Many Thanks, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

break any existing logic. Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_BUSY/SQLITE_LOCKED

ror Code SQLITE_LOCKED (6): Database Is Locked I was getting a "database is locked" message in my log file, and spent some time trying to understand how I could be getting SQLITE_LOCKED error, when in fact I was just getting a SQLITE_BUSY! ? Regards, Paul www.we-do-it.com<http:/

[sqlite] Query help

I suspect this is easy i have three tables create table t1 (recno int, a int, b int) create table t2 (a int, name text) create table t3 (b int, name text) I want to create a query that lists all rows in t1 but rather than the integers a and b I want to display the associated names from t2 and

Re: [sqlite] Building multiple indexes in one scan over a table

e no clue, how much bigger will your database become and how will it affect the performance. I'm not even sure if 'index creation' be any faster... Make your own research if you like. Just wanted to share what came to my crazy mind :) Good luck! Paul

Re: [sqlite] how to write this commands?

> UPDATE adla1 > SET pflopf = ( > SELECT pflopf > FROM adl > WHERE adl.ref = adla1.ref) > WHERE ( > SELECT COUNT(*) > FROM ( > SELECT 1 FROM adl > WHERE adl.ref = adla1.ref > LIMIT 2 > ) > ) = 1; > > Not all sure what LIMIT 2 does there. I think a SQL-92 version > would be > Limit, limit

Re: [sqlite] how to write this commands?

> update adla1 set PFLOPF=(SELECT pflopf from adl where adla1.ref=adl.ref) > where select count(adl.ref) from adl=1; A bit optimized version... UPDATE adla1 SET pflopf = (SELECT pflopf FROM adl WHERE adl.ref = adla1.ref) WHERE (SELECT COUNT(*) FROM (SELECT 1 FROM adl WHERE adl.ref = adla1.ref

Re: [sqlite] Primary Key without DataAnnotation for Windows Runtime

If I understood you right... 1) Create teable without primary key. 2) Separately create unique index on the column you want to be primary key. > Hi, > > > > we are building an App for Windows 8 and want to use sqlite as local DB. We > want to separate our Models from our DB Engine. So we need

Re: [sqlite] Conditional JOIN

dx DESC; Anyway, thank you for help! Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple data conversion in SQLite - please help

Hi everyone, I hope this is an easy one for you! I've got an SQLite 3 database from an iphone app. I've pulled a table out and the "date" column is in double binary - How do I see it as the actual "date"?? Thank in a

[sqlite] A read right after a write does not read changes

etting the new values. Any help on this issue would be greatly appreciated. Sincerely, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite doesn't work for me

Hi ..I recently started to program in java and tried to work with an SQLite example...and everytime I ran an example it would “shutdown unexpectedly” and it has told me that my Toast class wasn’t in the source code library, or it indicated source code missing. I chalked it up to conflicting vers

Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files

when trying to recover data from corrupted SQLite databases. At the moment you still have to run Undark in two passes if you want to extract both the normal payload data and the freespace data, hoping to merge this in 0.5. http://pldaniels.com/undark Regards,

[sqlite] Mystery why SQLite will not work until System.Data.SQLite has been installed

s per the instructions from the installation. Whatever help you can give me on this would be appreciated. Sincerely, Paul Bainter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 70, Issue 21

This is also not a unique occurance as I have done this now on other machines that did not before have SQLite on them. The best test, however I believe, was to create a new virtual machine as clean physical machines may be difficult to find. Best Regards, Paul Bainter -Original Message- Fro

[sqlite] System.Data.SQLite Deployment Mystery

Data.SQLite" installation. I know that's a mute-point because I don't believe that is what you guys intended either. It's a pretty simple scenario, so hopefully I've given enough information to help diagnose what is going on. If there is any other informat

[sqlite] FW: sqlite-users Digest, Vol 70, Issue 16

to do with SQLite. You are awesome. Sincerely, Paul Bainter -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of sqlite-users-requ...@sqlite.org Sent: Wednesday, October 16, 2013 10:00 AM To: sqlite-users@sqlite.org Subject: sqlite-

Re: [sqlite] Feature Request: Binding Arrays

> Since version 3.6.21, circa 2009-12-07. Note however that this capability > is not built in. It is an extension that you need to compile and link > separately. > > OK... Herewith my vote to make it standard then, like SQLITE_ENABLE_COLUMN_METADATA was enabled for the precompiled binary at some

Re: [sqlite] Feature Request: Binding Arrays

Fantastic! I've been wanting this for a long time. Since which version do we have sqlite3_intarray_x? On Wed, Oct 16, 2013 at 1:28 PM, Richard Hipp wrote: > Please see http://www.sqlite.org/src/artifact/2ece66438?ln=13-75 > > > -- > D. Richard Hipp > d...@sqlite.org > _

[sqlite] System.Data.SQLite Deployment Problem

but this has got me pulling my hair out, (not that I have much anyway. J) Any help would be tremendously appreciated. Sincerely, Paul Bainter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Differences in row removal methods between iOS SQLite and PC?

Richard, > The use of these 2-byte values is why the maximum page size in SQLite > is 65536, btw. Nice to know about the technical reasons behind these things. One last question for now, if that's okay; regarding the freelist pages, does one include the trunk pages in th

Re: [sqlite] Differences in row removal methods between iOS SQLite and PC?

On Sat, 12 Oct 2013 22:15:21 -0400 Richard Hipp wrote: > On Sat, Oct 12, 2013 at 10:06 PM, Paul L Daniels > wrote: > > > > > Intact record, before removal; > > 81 16 86 30 05 1D 47 81 65 52 45 4D 4F 56 45 4D ...0..G.eREMOVEM > > > > Removed using sqlite3

Re: [sqlite] Differences in row removal methods between iOS SQLite and PC?

fect. > I tried with the journal mode set to off/none, no difference in this test instance ( see other reply to Richard Hip showing the data dump ). Regards, Paul. -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___

Re: [sqlite] Differences in row removal methods between iOS SQLite and PC?

d, I'm not sure what the significance of the 9A is. The 05 byte is the payload header size varint (5 bytes). Paul. -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.or

[sqlite] Differences in row removal methods between iOS SQLite and PC?

e know what the *default* method should be ( I'm assuming the zero-byteing is the correct method, as it genuinely marks the payload in a way that it cannot be casually recovered by normal methods that I'm using ). For me to recover the data deleted in this manner I'm going to have to add

[sqlite] SQLite Application Deployment

st be something that I'm missing because I get exactly the same result on any machine that I use outside of my own development machine. Best Regards, Paul Bainter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite

Re: [sqlite] Can't seem to create DB with unvacuumed data [ for testing ]

On Fri, 11 Oct 2013 23:15:24 -0500 Drake Wilson wrote: > Quoth Paul L Daniels , on 2013-10-12 > 13:57:00 +1000: > > I've tried the pragma autovacuum=0 and journalling set to none to > > no avail. Every time I exit from the command line SQlite3 tool I > > find that

[sqlite] Can't seem to create DB with unvacuumed data [ for testing ]

tent deleted rows in it. I've tried the pragma autovacuum=0 and journalling set to none to no avail. Every time I exit from the command line SQlite3 tool I find that the data I explicitly deleted has genuinely been removed. Any suggestions? Paul. -- Computer Repairs for Charters towers -

[sqlite] Consistent reads

could make a change to a db here ** sqlite3_blob_open( rowid ) ** blob may now be for the "wrong row" cheers, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

Thanks guys. Perhaps docs could be updated in the _prepare_v2 section, to mention the 'best practices' lifecycle of a statement in regards to commit On 9 October 2013 04:08, Stephan Beal wrote: > On Tue, Oct 8, 2013 at 9:58 PM, Petite Abeille >wrote: > > > > > On Oct 8, 2013, at 8:10 PM, Ste

Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files

) Regards, Paul -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COMMIT in SQLite

more data left, but that may not be guaranteed in the future. Do I have to reset before I commit? And where is the requirement written in the sqlite documentation? thanks, Paul - OLD EMAIL - Dec 15, 2008; 9:39pm Re: COMMIT in SQLite "hussainfarzana" <[hidden email]>

Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files

arise due to the limited size of the probability space ( the fewer columns in a table the higher the chance of a false hit ). Regards, Paul -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing

Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files

uring my short searches around the net, so again, another very good bit of info. After my original post, I realised that Undark could also be used to pull data from corrupted db's ( missing tables or various B-tree indexes ), so I'll probably need some willin

[sqlite] Tool for extracting deleted data from unvacuumed SQLite files

Hello everyone, Sincerely hoping this is the appropriate place to post this announcement. A while ago a client needed some important SMSs retrieved from their iPhone that had been deleted, anyhow, long story short, I've since created a tool which I now call

Re: [sqlite] sqlite on ESXI hypervisor

ude this: DMI: VMware, Inc. VMware Virtual Platform/440BX Desktop Reference Platform, BIOS 6.00 10/13/2009 We have also run test installs under Virtualbox with no known issues. Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.

Re: [sqlite] table format for most efficient query

, Igor Tandetnik wrote: > On 7/5/2013 9:13 AM, Paul Sanderson wrote: > >> my primary key would not be unique :( >> > > That's an oxymoron - primary key is unique, by definition. > > -- > Igor Tandetnik > > __**

Re: [sqlite] table format for most efficient query

Having read up on RTrees I may have a problem - my primary key would not be unique :( On 5 July 2013 12:05, Paul Sanderson wrote: > Thanks for that Igor - I had RTree in the back of my mine but couldn't > remember what or where I had read about it. > > before I compile

Re: [sqlite] table format for most efficient query

t not always, be successful - i.e. about 90% of the time a search will result in about 4 rows being returned the other 10% of the time nothing will be found Just sitting down to look at Simons suggestions. On 5 July 2013 01:47, Igor Tandetnik wrote: > On 7/4/2013 5:29 PM, Paul Sanders

Re: [sqlite] table format for most efficient query

Thanks Simon - i'll have a play. tomorrow On 4 July 2013 22:52, Simon Slavin wrote: > > On 4 Jul 2013, at 10:29pm, Paul Sanderson > wrote: > > > create table lookup (index int, start int end int) > > I assume you missed a comma: > > create table lo

[sqlite] table format for most efficient query

I need to craete a lookup table which has the form create table lookup (index int, start int end int) The takle will be joined on a second table via the index column the table is likely to have a few million rows and I will be doing many thousands of lookups consequtively. My current lookups are

Re: [sqlite] Large Database Windows vs Linux

sor lies about having done an fsync. Performace is the same (as in transactions per second) regardless of what PRAGMA synchronous is set to. On real hardware, there's a big difference. Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://

[sqlite] MMIO and VFS Obfuscation

a valid concern.   Anyway, any ideas on this? Thanks! Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding table scans with complex queries

stop working on the FTS lookups once the LIMIT conditions are met. Seems UNIONing repetitions of whole query for each OR section is offering the best option so far.  I'll keep trying things.  Cheers, Paul From: Clemens Ladisch To: sqlite-users@sqlite.org Sent

[sqlite] Avoiding table scans with complex queries

ns on their own.  (I could do a UNION of multiple versions of the whole query with each OR section separated out, but that's not terribly satisfying.)    Many thanks! -Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query help

I have a table of the form create table tab (num int1 unique, num2, int) for each row for num2 there is usually a matching num1. But not always. I want to identify each row where num2 does not have a matching num1 example data might be num1 num2 1 3 2 3 3 2 4

<    1   2   3   4   5   6   7   8   >