Re: [sqlite] Unable to create two indexes with the same name but on different tables

Sorry what is "very hard" about SELECT * FROM sqlite_master WHERE type = 'index' AND name = 'new_index_name' to see if the index already exists Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQL

Re: [sqlite] smartest way to exchange a sqlite3 database with another empty

It seems that you just want to keep access to all of your historic logging so rather than copy/backup the entire database you could just create a new archive DB (or open an old one), attach it, copy x records to the archive and then delete the same x records from the master. How big is your log da

[sqlite] Searching for a percent symbol

Hi all I have a column of string values some of which may contain % characters Is it possible to search for just those rows that contain a % eg how could I search for 20%. on a test table containing: I got 20 quid i got 20% of it i got just 20% some money this is an underscore _ ok I tried this

Re: [sqlite] Searching for a percent symbol

/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 5 October 2016 at 10:44, Petite Abeille wrote: > >> On Oct 5, 2016, at 11:38 AM, Paul Sanderson >> wrote: >> >> How casn I find j

Re: [sqlite] Searching for a percent symbol

for a fully functional demo licence On 5 October 2016 at 10:53, Dominique Devienne wrote: > On Wed, Oct 5, 2016 at 11:50 AM, Paul Sanderson < > sandersonforens...@gmail.com> wrote: > >> Thanks Petite - I have already looked at that - but how? >> > > sqlite> creat

Re: [sqlite] Searching for a percent symbol

October 2016 at 11:00, Paul Sanderson wrote: > Brilliant thansks Dominique - I had completely misunderstood it :) > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 > http://sandersonforensics.com/forum/content.php?195-SQ

Re: [sqlite] Order of fields for insert

SQLite does not use any compression when storing data. Occasionally rows have so much data that they overflow to an additonal page(s) so the advice about defining tables so that blobs are at the end of the definition is good - also columns that store long strings might be better at the end of a ta

Re: [sqlite] Order of fields for insert

> Long columns, especially TEXT or BLOBs which may have lots of data in, should > go at the end. Because you don't want SQLite to have to fetch all that data > from storage just to get at the column after it. To be pedantic SQLite does not need to "fetch" all of the data from strorage before a

Re: [sqlite] Data loss during the disk full condition

I haven't seen anything to say what journalling is being used (Rollback, WAL or none). If the latter then SQLite will have nothing to revert to on error. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php

Re: [sqlite] Issue with Malformed table

If you are unsucessful dro me an email - I might be able to help (not a commercial proposition - just may help me doing some testing with my code) Cheers Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.ph

Re: [sqlite] Pragma to flag unknown pragma?

Most pragmas allow you to issue them in a mode such as to query a current state. So to use your example pragma journal_mode = persist; followed by pragma journal_mode; with a subsequent check to see that the returned value is set to what you want it to be set to (actually the new value is return

Re: [sqlite] Read-only access which does not block writers

Steps 2 and 3 can be swapped Also you can convert an existing database from one mode to another (although not, I suspect (I have not tried), in the middle of a transaction and if in the DB is wal mode and you are changing to journal then this would force a checkpoint). Paul www.sandersonforensics.

Re: [sqlite] Read-only access which does not block writers

Could you use PRAGMA data_version before and after each read to see whether there have been any changes to the DB - not surehow this works in WAL mode? Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.p

Re: [sqlite] Date (from REAL) with Negative Year

rather unintuitively excel uses the OLE automation timestamp that records the number of days since 1899/12/30 http://sandersonforensics.com/forum/content.php?131-A-brief-history-of-time-stamps https://msdn.microsoft.com/en-us/library/system.datetime.tooadate(v=vs.110).aspx now no need for the ug

Re: [sqlite] New tool for PUTTY logging [Windows]

You could make the CmdEntered field unique, or create a hash on the uppercase content of the command and make that a unique key. Then use INSERT OR IGNORE... Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/conten

[sqlite] Documentation error

The process for calculating a checksum text on the SQLite file format page contains two errors. Currently reads: The checksum is an unsigned 32-bit integer computed as follows: 1. Initialize the checksum to the checksum nonce value found in the journal header at offset 12. 2. Initialize

Re: [sqlite] Non-unique columns in unique index

As a bit of an off the wall suggestion you could try an MD5 (or even a partial MD5 - half of the bytes) CREATE table hashes ( hash integer primary key; // just the first 64 bits of the hash of uniquecol and extracol ) as an integer primary key the hash would be an alias of the rowid and so st

Re: [sqlite] Non-unique columns in unique index

orage 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 email from a work address for a fully functional demo licence On 1 March 2017 at 22:13,

Re: [sqlite] Why isn't my time formatting working?

The vast majority of dates I see in SQLite databases are unix epoch integer times (seconds since 1/1/1980) with unix milli seconds a close second. Efficient to store, sort and do date arithmetic on but need to be converted to display. I also see unix nano seconds, 100 nano seconds, windows filetim

Re: [sqlite] Why isn't my time formatting working?

72786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 8 March 2017 at 22:57, Tim Streater wrote: > On 08 Mar 2017 at 20:40, Paul Sanderson > wrote: > > >

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

What is the average size of the text in the direction field? and what page size have you set for the database? If the size of a record is such that only a small handful fit into a page, or worse each record overflows (and your select includes the direction field) then this could impact performance.

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

QLite email from a work address for a fully functional demo licence On 19 March 2017 at 12:07, Paul Sanderson wrote: > What is the average size of the text in the direction field? and what page > size have you set for the database? If the size of a record is such that > only a small hand

Re: [sqlite] BLOB sizes beyond 2GB?

I am sure Richard will correct me if I am wrong. But... The format for a record is 1. payload length varint 2. rowid varint (optional) 3. serial type array varint 4. serial types followed by the data for the serial types The issue are as I see them: The payload length varint above, this is the

Re: [sqlite] BLOB sizes beyond 2GB?

On Tue, Mar 28, 2017 at 12:52 PM, Paul Sanderson < > sandersonforens...@gmail.com> wrote: > > > I am sure Richard will correct me if I am wrong. But... > > > > The format for a record is > > > > 1. payload length varint > > 2. rowid varint (optional)

Re: [sqlite] column alignment of views and tables;

Just shooting out so no time to test. But could you try something like select substring('', 1, length(printf("%2.f", price))) || printf("%2.f", price) from prices Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sanderson

Re: [sqlite] column alignment of views and tables;

ns Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 4 April 2017 at 15:07, Paul Sanderson wrote: > Oops would need to subtract the string length from the column wid

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

Hi Ron Your dates are still not 8601 with your dates above in a table called dateplay and column named vi select vi, julianday('now') as now, julianday(substr(replace(vi, '/', '-'), 1, 10)) as jday, julianday('now') - julianday(substr(replace(vi, '/', '-'), 1, 10)) as diff from da

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 12 April 2017 at 13:37, Paul Sanderson wrote: > Hi Ron > > Your dates are still not 8601 > > with your dates above in a table called dateplay and

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

Try something like SELECT dateplay.vi, JulianDay('now') AS now, JulianDay(SubStr(Replace(dateplay.vi, '/', '-'), 1, 10)) AS jday, JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/', '-'), 1, 10)) AS diff, CASE WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

another oops I see Ryan pretty much posted the same as me 5 minutes earlier - I'll go back to bed :) 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 S

[sqlite] NOT NULL integer primary key

Is this a bug? Create table test (id integer not null primary key, data text); insert into test values (null, 'row1'); select * from test; 1, row1 I know that if you provide a NULL value to a column define as integer primary key that SQLite will provide a rowid, but should the not null constraint

Re: [sqlite] NOT NULL integer primary key

licence On 18 May 2017 at 17:26, Gwendal Roué wrote: > > > Le 18 mai 2017 à 18:16, Paul Sanderson a > écrit : > > > > Is this a bug? > > > > Create table test (id integer not null primary key, data text); > > insert into test values (null, 'row1&#

[sqlite] auntondex with unique and integer primary key

Is the autoindex associated when using unique with an integer primary key definition redundant? I have seen a number of DBs/tables created in the following form: Create table test(id integer unique primary key); Insert into test values (1); Insert into test values (2); Insert into test values

Re: [sqlite] auntondex with unique and integer primary key

On 19 May 2017 at 18:49, Simon Slavin wrote: > > On 19 May 2017, at 6:21pm, Paul Sanderson > wrote: > > > Is the autoindex associated when using unique with an integer primary key > > definition redundant? > > > > I have seen a number of DBs/tables created in th

Re: [sqlite] auntondex with unique and integer primary key

demo licence On 19 May 2017 at 19:29, Joseph L. Casale wrote: > > -Original Message- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On > > Behalf Of Paul Sanderson > > Sent: Friday, May 19, 2017 12:08 PM > > To: SQLite maili

Re: [sqlite] auntondex with unique and integer primary key

Ahh - I always let SQLite decide what index to use as I assume that it knows best. I have never used "indexed by" to force the use of a specific index - I see the issue with backward compatibility now. Thanks Richard Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel

[sqlite] imposter tables

I am just taking a look at imposter tables and while the implementation is neat I am just wondering what their use is, or rather what they can achieve that a view can't achieve (and without the risk of DB corruption). For instance an imposter table created on an index such as the following from Sk

Re: [sqlite] imposter tables

SQLite email from a work address for a fully functional demo licence On 14 June 2017 at 13:11, Simon Slavin wrote: > > > On 14 Jun 2017, at 12:52pm, Paul Sanderson > wrote: > > > The only benefit I can see is that you know the imposter table is showing > > you exactly

Re: [sqlite] imposter tables

://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 15 June 2017 at 00:09, Richard Hipp wrote: > On 6/14/17, Paul Sanderson wrote: > > I am just taking a look at imposter t

Re: [sqlite] imposter tables

gt; > On 2017/06/15 10:15 AM, Paul Sanderson wrote: > >> Thanks Richard - the View approach is fine for my needs - just wanted to >> know what the rationale was for the imposter tables given the ability to >> simulate the imposter table with a view. >> > > I'm t

[sqlite] Providing incrementing column to query

I Have a number of queries to which I want to supply an incrementing column, some of these queries involve without rowid tables. I have no control over the design of the tables. So for a table defined as: CREATE TABLE (name text, age integer) with values Steve, 34 Eric, 27 Denis,

Re: [sqlite] Providing incrementing column to query

-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 24 June 2017 at 13:10, Clemens Ladisch wrote: > Paul Sanderson wrote: > > I Have a number of queries to which I want to supply an incrementing > column, > > som

Re: [sqlite] Providing incrementing column to query

e they do > not correlate with anything meaningful? > > -- > ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > > > -Original Message- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > On Behalf Of Paul Sanderson > > Sent:

[sqlite] Document typo?

https://sqlite.org/dbstat.html The DBStat web page defines that schema of the virtual table as follows with path defined as an integer CREATE TABLE dbstat( name STRING, -- Name of table or index path INTEGER,-- Path to page from root pageno INTEGER,-- Page number

Re: [sqlite] FOREING KEY constraint

pragma foreign_key_list(table_name) may help 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 email from a work address for a fully functional

Re: [sqlite] syntax error near AS

The SQLite syntax diagrams are my first point of call when looking at an error in my code like this. https://sqlite.org/lang_update.html "AS" and an alias are clearly not part of the statement. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 htt

Re: [sqlite] Sqlite problem with opening database

Could your 32 bit app be picking up a 64 bit dll. Could you rename the dll's and hard code the location into your library? May not work for your release code but may help you narrow down the issue. Paul On Wed, 5 Jul 2017 at 18:19, Simon Slavin wrote: > > > On 5 Jul 2017, at 1:41pm, Gregor Pa

[sqlite] Summarising (missing) values

I have a table which includes a numeric "ID" column, values in the column generally increment but there are some gaps. I use the following query to get a list of all "missing" values WITH RECURSIVE cte(x) AS (SELECT (SELECT Min(messages._id) FROM messages) UNION ALL SELECT cte.x + 1

Re: [sqlite] Summarising (missing) values

demo licence On 18 July 2017 at 11:19, Clemens Ladisch wrote: > Paul Sanderson wrote: > > What I would like is a single query that summarises the values that are > > present in (or missing from) a table. > > A row is the start of a range if there is no previous row: > &

[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

[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://sanders

Re: [sqlite] Create join and add unique column

Decker > Sent: 16 September 2014 12:02 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Create join and add unique column > > could create a temporary table with a incrementing key and 'insert into > temp_table select join ...' something like that?

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

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

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

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

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: >> >>>

[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] 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] 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] following a trail of references

olkit <http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery> -SQLite Forensic Toolkit http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 12 October 2014 21:18, Igor Tandetnik wrote: > On 10/12/2014 4:03 PM, Paul Sanderson wrote

[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] decoding a bitmask

turned) when multiple bits are set. Any ideas why? Thanks Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit <http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery> -SQLit

Re: [sqlite] decoding a bitmask

wrence wrote: > My apologies for the previous completely wrong mesage. I got mixed up > with operator meaning & precedence... > > On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote: > > > > My actual code is as folows > > > > (CASE visits.transiti

Re: [sqlite] SQL query

Thanks All - duplicated means the content is the same as well as the name, different is the filename is the same but the content is different. I need to refine my query to produce only one copy of any that is not in set 0 file10ABCD file11ABCD file13EF01 file20BCE2

Re: [sqlite] SQL query

ich is great and solves my problem, but I cant see why the first query doesn't work. On 30 January 2013 21:37, Paul Sanderson wrote: > > Thanks All - duplicated means the content is the same as well as the name, > different is the filename is the same but the content is differe

Re: [sqlite] SQL query

jpg',4,'890B-4533-447E-6461-070E-FDB7-799E-1FB8'); > sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE > setid=0); > 1.jpg|4|890B-4533-447E-6461-070E-FDB7-799E-1FB8 > > > -Original Message- > From: sqlite-users-boun...@sqlit

Re: [sqlite] SQL query

242-E734-B125-D02F-A7F0-DC29 > file1|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 > sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE > setid=0) group by hash; > file1|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 > file1|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62 &

Re: [sqlite] (no subject)

Thanks all All columns in the query are indexed. I'll try teh suggestions and see how they perform. On 31 January 2013 19:54, Igor Tandetnik wrote: > On 1/31/2013 2:33 PM, Paul Sanderson wrote: > >> My query is >> >> select fileref from rtable as r where vsc

Re: [sqlite] SQL query

Still playing with this I have the following table and I run the following query - the results of which are what I expect name, num, md5 sqlite> select * from rtable; $RmMetadata|0|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 $RmMetadata|1|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 $RmMetadata|2|8465-CE

Re: [sqlite] (no subject)

etnik wrote: > On 1/31/2013 2:33 PM, Paul Sanderson wrote: > >> My query is >> >> select fileref from rtable as r where vsc > 0 and isgraphic = 1 and not >> exists (select md5 fr >> om rtable as r1 where r.md5 = r1.md5 and isgraphic = 1 and vsc = 0); >>

Re: [sqlite] Determine if an index has been created

I will know the name of the index - I just need to check that it has been created. On 1 February 2013 00:09, Simon Slavin wrote: > > On 31 Jan 2013, at 10:57pm, Igor Tandetnik wrote: > > > On 1/31/2013 5:45 PM, Paul Sanderson wrote: > >> Is it possible to ascertain if

Re: [sqlite] Determine if an index has been created

Thank You On 1 February 2013 10:38, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 01/02/13 02:12, Paul Sanderson wrote: > > I will know the name of the index - I just need to check that it has > > been created. > > Just use pragm

Re: [sqlite] Determine if an index has been created

even easier - thank you. Paul On 1 February 2013 11:46, Simon Slavin wrote: > > On 1 Feb 2013, at 10:12am, Paul Sanderson > wrote: > > > I will know the name of the index - I just need to check that it has been > > created. > > Oh, in that case just submit the &

Re: [sqlite] Sqlite query doesn't run correctly on different databases

Thanks Richard that worked On 1 February 2013 11:23, Richard Hipp wrote: > The expression "x NOT IN (something-that-contains-NULL)" is always false. > I suggest you add an additional term to the WHERE clause of the subquery: > "... AND md5 NOT NULL". > > O

Re: [sqlite] populating a table as quickly as possible

currently using : journal_mode = off page_size=16386 cache_size = 1 synchronous = off I load lots of similar data sets (each into a separate db) and load time is definitely an issue. There are processing delays and loading a db can take 30+ minutes, if I can shave off even a few minutes on eac

Re: [sqlite] populating a table as quickly as possible

Currently ading 5000 at a time, will try increasing to 20 and 50K and see what happens. Just one table - but interesting On 4 February 2013 18:24, Dominique Pellé wrote: > Paul Sanderson wrote: > > > I want to populate a large table (millions of rows) as quickly as > possib

[sqlite] Unlocking locked database and preventing locking to start with

I have a user who has a locked database. I don't know why the db is locked but suspect either he killed the program when it was adding an index or my program crashed (he was using a beta version). Irrespective of wghich he has a db that is locked and I'd like to unlock it. My database would normal

[sqlite] creating a summary table

nc 1a 2a 3a 4b 5b 3b 4b 2b 3a 5b 2b I have a table as above I want to create a summary table that shows in the first column the total number of occurrences of a value in the first column (n) and in the second column for each value in n a count of t

Re: [sqlite] creating a summary table

That did the job - Thank You On 18 February 2013 18:15, James K. Lowden wrote: > On Mon, 18 Feb 2013 17:02:53 + > Paul Sanderson wrote: > > > nc > > 1a > > 2a > > 3a > > 4b > > 5b > > 3b > > 4b >

[sqlite] column totals

I have the following query that produces a summary table SELECT cat, COUNT(*) AS occ, COUNT(DISTINCT tes) AS uni, COUNT(tag) AS tagged FROM rtable WHERE qu > 0 AND qu < 4 GROUP BY qu The table would look something like 1 54 3 2 26 4 3 56 8 I want to modify the above sql

Re: [sqlite] column totals

Thank You Works well summing the columns but I don't get a 'tot' label I, get 1 54 3 2 26 4 3 56 8 0136 15 On 22 February 2013 23:20, Clemens Ladisch wrote: > Paul Sanderson wrote: > > SELECT cat, COUNT(*) AS occ, COUNT(DISTINCT te

Re: [sqlite] column totals

Hmm works OK at an sqlite prompt but not when I pass the query though a data access component On 23 February 2013 11:05, Clemens Ladisch wrote: > Paul Sanderson wrote: > > Works well summing the columns but I don't get a 'tot' label I, get > > > > 1

Re: [sqlite] Joining tow tables with subset of columns from one

Yes thanks Kevin Dull question and I was just coming back here to say I have sorted it. Thanks anyway :) On 13 March 2013 17:59, Kevin Martin wrote: > > On 13 Mar 2013, at 17:44, Paul Sanderson wrote: > > > I want to join two table by doing a select in the form > >

[sqlite] (no subject)

I have two tables of the form create table1 (id1 int, t1 text) create table2 (id2 int unique, t2 text) the data in table 1 is such that some values for t1 are NULL ID1 can contain duplicates ID2 is unique and for every instance of ID1 in table1 there will be a corresponding ID2 entry in table2 t

Re: [sqlite] (no subject)

Rob yes thats correct Igor - thanks I'll give that a go On 16 May 2013 14:51, Rob Richardson wrote: > First idea: include a subject line. > > I'm not 100% clear on your message. You said: > " For those entries in table1 where there is a null in t2..." > I'm guessing you wanted to say: > " Fo

Re: [sqlite] (no subject)

That worked thanks Igor. On 16 May 2013 15:33, Paul Sanderson wrote: > Rob yes thats correct > > Igor - thanks I'll give that a go > > > > On 16 May 2013 14:51, Rob Richardson wrote: > >> First idea: include a subject line. >> >> I'm not 1

[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

[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] 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

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

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

, 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 > > __**

[sqlite] SQL Query - finding rows in a table that are not present in another

I have a couple of table seach of which has one column but millions of rows, the column is a text column. I need to return all of the rows in table B that are not present in table A What is the most efficient way of doing this? ___ sqlite-users mailing

Re: [sqlite] Finding .db file in XCode project

Is it an extension issue - have you given your database an extension that the other tool can't see by default? On 21 November 2012 16:06, Donald Steele wrote: > I am not trying to access it on a iDevice. I am trying to access it on my Mac. > > How do I build the database and then give the iOS app

[sqlite] creating indexes on empty columns

Whilst building a new app I created an index on every column some of which were empty. The database is reasonably large (400K rows) and I notcied that it seems to take as long to create an index on a column in which all the rows are empty as it does on one in which all the rows are unique. I don't

Re: [sqlite] creating indexes on empty columns

turn. This would save on the overhead of reading the entire table for each column. On 24 November 2012 14:40, Clemens Ladisch wrote: > Paul Sanderson wrote: > > Whilst building a new app I created an index on every column some of > which > > were empty. > > And with "

Re: [sqlite] creating indexes on empty columns

Thanks for the replies - I'll try and read through them all thoroughly a bit later. But for now a bit of background. My software creates a large table containing anything between about 250K and Millions of rows when first run, the indexes are created immediately after the table is populated and t

Re: [sqlite] creating indexes on empty columns

Sorry - generally the sorts will be on one column - but they may choose at a later time to sort by another column. They will (but rarely - sort by two or more columns at the same time). On 26 November 2012 14:20, Clemens Ladisch wrote: > Paul Sanderson wrote: > > My software create

[sqlite] Testing for a null string

I am sure tihs is basic but. I have a database with a text column and i want to return all rows where the column has no value I have tried select * from db where f = NULL select * from db where f = "" select * from db where f = '' all return 0 records when I knopw that most fields are empty -

<    1   2   3   >