Re: [sqlite] Someone knows about an ETL tool in foreign languages?

[John Machin]

On 24/09/2009 12:02 AM, hfdabler wrote:
> 
> Hello to all, 
> 
> Being in a pretty much international company, I have come here to ask a few
> things about ETL tools and their different languages. 

Why? The principal focus of this mailing list is SQLite and its C APIs, 
not ETL.

> 
> We have offices in the US, in Europe (Italy, France) and in China. We think
> English is fine but our European team and Chinese team especially would like
> to get software in their language. 
> 
> What we are trying to find is an ETL program to perform data integration,
> data synchronization and deduplication on our database in French, Italian
> and especially in Chinese. 

I presume that you mean that the /user interface/ should work in those 
languages. To handle Chinese /data/, you may need to source a separate 
program in China ... a Western-origin program would need a 
Chinese-specific deduplication module, developed with the aid of Chinese 
experts -- soundex, metaphone, Jaro-Winkler, Levenshtein and such-like 
don't grok non-alphabetic languages.

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

Re: [sqlite] Tedious CSV import question

[John Machin]

On 26/09/2009 5:38 AM, Petite Abeille wrote:
> On Sep 25, 2009, at 9:21 PM, C. Mundi wrote:
> 
>> Your post neatly articulates virtually every facet of this issue.
>> Thank you.  I wish we could get everyone to stop using csv.  I hate to
>> look at xml but I often wish everyone would use it instead of csv.
> 
> In fact, in Switzerland, there is a federal law mandating the use of  
> XML 5NF for all data transmission, irrespectively of their purpose.  
> Namespaces have to be properly encoded in the 4 officially recognized  
> languages.

"encoded in a language" is a novel concept. In how many? All 4? Or one 
of the sender's choice? Sending all of your data out with namespaces 
"encoded" only in Romansch seems like a brilliant idea :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[John Machin]

On 26/09/2009 5:03 AM, Wilson, Ronald wrote:
>
> 
> Yeah.  The clearest thing in the RFC is the ABNF grammar.  However, even
> that leaves out common cases like white space outside of quoted fields,
> which most people would expect to be trimmed.  Also, I think most people
> would expect leading/trailing white space to be trimmed, even in
> unquoted fields.

No, thanks.

(1) Whitespace (space? CR? TAB? LF? NBSP??) outside of a quoted string 
is a violation of the writing rules for de-facto standard CSV. If the 
writer has used a different set of rules, then the reader should use 
that set of rules.

(2) Trimming leading/trailing whitespace from unquoted fields? Excuse 
me, if CSV is the only format I can get data in, I don't want any 
wiseguy messing with it -- I want to see what exactly is in the data. Do 
XML parsers strip whitespace? Stripping leading/trailing whitespace (and 
compressing whitespace runs into a single space) should be /optional/ 
functionality to be applied (if desired) /after/ input, irrespective of 
the input format.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up a query between two tables?

[John Machin]

On 15/09/2009 7:25 PM, Kermit Mei wrote:
> On Tue, 2009-09-15 at 17:37 +1000, John Machin wrote:
>> On 15/09/2009 4:47 PM, Kermit Mei wrote:
>>>
>>> sqlite> SELECT HomeDev.text, ZPhDev.id
>>>...> FROM ZPhDev
>>>...> INNER JOIN HomeDev ON (HomeDev.id = ZPhDev.id)
>> Are you sure that you mean ZPhDev.id in the ON clause?? 
> 
> Oh,I'm sorry, that should be :
> CREATE VIEW ZHView AS SELECT HomeDev.text, ZPhDev.id FROM ZPhDev INNER
> JOIN HomeDev ON (HomeDev.id = ZPhDev.HomeDevId) ORDER By HomeDev.text;
> 
> 
>> If so, what's 
>> the point of having the ZPhDev.HomeDevId which isn't used and whose name 
>> suggests that it should be used? 


You didn't answer this question:

 >> If not, what's the point of having
 >> ZphDev.id? Could the second table be collapsed into an isaZPhDev
 >> (true/false) column in the first table?

>>> Then, How can I speed up this kind of operation?
>> Standard advice: ensure that you have indexes on whatever the join 
>> columns should be. You might get more help if you showed the CREATE 
>> TABLE (including pk and any other constraints) and CREATE INDEX 
>> statements, and added a note about the relationship between the two tables.

And the relationship between the tables is ..??

> The homeDev table:
> CREATE TABLE HomeDev (color TEXT, secId NUMERIC, background TEXT,
> pageNum int, icon_y int, icon_x int, size_y int, size_x int, pos_y int,
> pos_x int, id INTEGER PRIMARY KEY, text varchar(15), icon varchar(50),
> type int);
> 
> The ZPhDev table:
> CREATE TABLE ZPhDev (HomeDevId NUMERIC, id INTEGER PRIMARY KEY);

No relationship comments, no foreign key clause, ... no useful info.

> How can I create index for them?

With a CREATE INDEX statement. Please consider reading an SQL text ... 
anyone have any suggestions for Kermit?

HomeDev.id already has an index, automatically created because it is a 
primary key and in this case it's not an extra index, it's the rowid index.

>>>  Use view like this:
>>>
>>> sqlite> CREATE VIEW ZHview AS
>>>...> SELECT HomeDev.text, ZPhDev.id
>>>...> FROM ZPhDev
>>>...> INNER JOIN HomeDev ON (HomeDev.id = ZPhDev.id)
>>>...> ORDER By HomeDev.text;
>>>
>>> And can it speed up my operation?
>> Read this: http://en.wikipedia.org/wiki/View_%28database%29 and see if 
>> you can find any indication that a (non-materialised) view speeds 
>> anything up at runtime.
> 
> Thanks,but I can't find anything about how can I speed it up on wiki. 

Three possibilities, any one or more or all of which could be true: the 
information about how a non-materialised view can speed up a query is:
(1) there but you can't find it.
(2) not there because the wiki authors left it out for some reason.
(3) not there because such a view can't speed up a query.

Here's a hint: re-read the section on Equivalence, which says in essence 
that selecting from a view makes the programmer's job easier, but at 
runtime it's just a text substitution exercise. There's a very close 
parallel to using functions in procedural programming languages -- 
replacing say 3 copies of the same code with one copy plus 3 calls to it 
  has little effect on the runtime speed.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] trouble with precompiled binaries for MacOS X

[John Machin]

On 26/08/2009 5:47 AM, Matt Stiles wrote:
> Am I losing my mind, or is there something wrong with the bin.gz file on the
> download page? I've downloaded it several times, but I can't get it to open
> completely using Stuffit or the Mac archive utility. It appears to open, but
> it just spits out another compressed file, over and over -- like a cruel
> Matryoshka doll on my desktop.
> 
> The file in question: http://www.sqlite.org/sqlite3-3.6.17-osx-x86.bin.gz

I can open this on Windows XP with the "7-Zip" 3rd-party utility. As 
expected of a foo.bin.gz file, it decompresses to a foo.bin file. 7zip 
can even open that; it says there are a bunch of __TEXT__ things, some 
__DATA__ things, and 2 __IMPORT__ things ... looks superficially not 
unlike an executable to me.

Doesn't the Mac OS have a "gunzip" program? I have one from the mingw32 
project or similar collection of Windows ports of *x programs:

C:\installers>gunzip -l sqlite3-3.6.17-osx-x86.bin.gz
compressed  uncompr. ratio uncompressed_name
258707510012  49.2% sqlite3-3.6.17-osx-x86.bin

C:\installers>gunzip -tv sqlite3-3.6.17-osx-x86.bin.gz
sqlite3-3.6.17-osx-x86.bin.gz:   OK

C:\installers>gunzip -V
gunzip 1.2.4 (18 Aug 93)
Compilation options:
DIRENT SYS_UTIME STDC_HEADERS HAVE_UNISTD_H NO_CHOWN PROTO ASMV

So that's two independent witnesses to the file on the website being an 
OK gzip file, and one that the inner file appears to be an uncompressed 
executable.  How did you obtain it -- http or ftp? Have you checked the 
compressed file size (I have 258707 (see above))?

What version of OS X are you running?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[John Machin]

On 23/08/2009 3:08 PM, Itzchak Raiskin wrote:
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?

Specify with some precision what tables of data you expect to have:

create table terrain_data (id, x, y, height) -- ??
create table line (?)

How is the terrain data expressed? I.e. are "cordinates" (lon, lat) or 
something else? Height above what in what units?

What is a "line"? 2D or 3D?

Note you say you have hundreds of lines but don't say how they are 
related to the terrain data ...

Explain "get a vector with all heights point along this line" in 
mathematical terms

Explain "for each point along the line"

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

Re: [sqlite] suggestion on the database design

[John Machin]

On 21/08/2009 1:29 PM, pierr wrote:
> 
> Simon Slavin-2 wrote:
>>
>> On 21 Aug 2009, at 3:26am, pierr wrote:
>>
>>> I did not know the sequence in defining the field matters. This is
>>> what I should have done.
>> Sorry, I should have explained better.  You were right: there is no  
>> difference.  I was just rearranging the fields in the classic way:  
>> with the primary key column as the first column.  It helps me think  
>> about how the database works.  You did nothing wrong.
>>
> Hi Simon,
>   It do make a difference. 
>   With this schema, 
>   CREATE TABLE IF NOT EXISTS tblIndex(
>  frame_type INTEGER,
>  pts VARCHAR(5)
>  ts_start INTEGER PRIMARY KEY,
>  ts_end INTEGER,
>  ) 
>  There will be a rowid field in the database ; and there is a
> sqlite_autoindex_tblIndex_1 build on ts_start automatically ; so 1,800,000
> records (16bytes each) takes 62M . 

Please don't type from memory -- what you have above has TWO syntax 
errors, and doesn't (with those random comma placements fixed) produce 
the result that you say -- and do read my previous message.

Here is an (annotated) copy/paste of an actual session:

sqlite>   CREATE TABLE IF NOT EXISTS tblIndex(
...>  frame_type INTEGER,
...>  pts VARCHAR(5)<<<=== missing comma
...>  ts_start INTEGER PRIMARY KEY,
...>  ts_end INTEGER,
...>  )
...> ;
SQL error: near "ts_start": syntax error
sqlite>   CREATE TABLE IF NOT EXISTS tblIndex(
...>  frame_type INTEGER,
...>  pts VARCHAR(5),
...>  ts_start INTEGER PRIMARY KEY,
...>  ts_end INTEGER, <<<=== superflous comma
...>  );
SQL error: near ")": syntax error
sqlite>   CREATE TABLE IF NOT EXISTS tblIndex(
...>  frame_type INTEGER,
...>  pts VARCHAR(5),
...>  ts_start INTEGER PRIMARY KEY,
...>  ts_end INTEGER
...>  );
sqlite> select * from sqlite_master;
table|tblIndex|tblIndex|2|CREATE TABLE tblIndex(
  frame_type INTEGER,
  pts VARCHAR(5),
  ts_start INTEGER PRIMARY KEY,
  ts_end INTEGER
  ) <<<=== no index !!
sqlite> drop table tblIndex;
sqlite>   CREATE TABLE IF NOT EXISTS tblIndex(
...>  frame_type INTEGER,
...>  pts VARCHAR(5),
...>  ts_start INT PRIMARY KEY,  <<<=== using your original INT 
instead of Simon's INTEGER
...>  ts_end INTEGER
...>  );
sqlite> select * from sqlite_master;
table|tblIndex|tblIndex|2|CREATE TABLE tblIndex(
  frame_type INTEGER,
  pts VARCHAR(5),
  ts_start INT PRIMARY KEY,
  ts_end INTEGER
  )
index|sqlite_autoindex_tblIndex_1|tblIndex|3| <<<=== index!!
sqlite>


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

Re: [sqlite] suggestion on the database design

[John Machin]

On 21/08/2009 12:59 PM, Simon Slavin wrote:
> On 21 Aug 2009, at 3:26am, pierr wrote:
> 
>> I did not know the sequence in defining the field matters. This is
>> what I should have done.
> 
> Sorry, I should have explained better.  You were right: there is no  
> difference.  I was just rearranging the fields in the classic way:  
> with the primary key column as the first column.  It helps me think  
> about how the database works.  You did nothing wrong.

Pierr had "INT primary key". Simon suggested "INTEGER primary key" which 
is *better* -- it means that the PK is also the rowid (saves space in 
the table) and you don't need a separate index for the PK. See 
http://sqlite.org/lang_createtable.html#rowid

Perhaps Pierr's problem is related to the "usb interface" on the hard drive?


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

Re: [sqlite] How to select data from 2 lines in one line?

[John Machin]

On 20/08/2009 12:57 AM, Kit wrote:
> Right form (tested):
> 
> SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS
> previous_data,temp.preult AS previous_preult
>FROM petr4,petr4 AS temp
>WHERE petr4.rowid=temp.rowid+1;

Don't you think that relying on (a) rowid being consecutive (b) rowid 
order being identical to date order is just a little bit dodgy? What if 
the table has been created by a bulk load, not necessarily in ascending 
date order? What if some rows have been deleted?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select data from 2 lines in one line?

[John Machin]

On 20/08/2009 12:10 AM, Mário Anselmo Scandelari Bussmann wrote:
> I have a table like this:
> 
> petr4
> ---
> rowid|data|preabe|premax|premin|preult|voltot
> 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0
> 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0
[snip]
> 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0
> 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0
> 
> I need a select that returns data,preult,previous data and previous preult:
> 
> 2007-01-03|48.7|2007-01-02|50.45
> 2007-01-04|47.65|2007-01-03|48.7
[snip]
> 2007-01-12|45.15|2007-01-11|45.21
> 2007-01-15|44.89|2007-01-12|45.15
> 
> How can I do that using only sql (no python, c or perl, no cursor)?

No Python? How cruel :-)

This works but you'd better have an index on 'data', and it looks like 
at least O(N**2) OTTOMH:

sqlite> create table x (data,preabe,premax,premin,preult,voltot);
sqlite> insert into x values 
('2007-01-02',50.0,50.45,49.76,50.45,256115409.0);
/* etc etc*/
sqlite> select a.data, a.preult, b.data, b.preult from x a, x b
...> where b.data = (select max(c.data) from x c where c.data < a.data)
...> order by a.data;
2007-01-03|48.7|2007-01-02|50.45
2007-01-04|47.65|2007-01-03|48.7
[snip]
2007-01-12|45.15|2007-01-11|45.21
2007-01-15|44.89|2007-01-12|45.15
sqlite>

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

Re: [sqlite] use of parentheses when not arithmetically or logically required

[John Machin]

On 19/08/2009 11:26 AM, Simon Slavin wrote:

> DRH's post trumps mine, of course.  I'm surprised to find that  
> brackets are optimised out of WHERE evaluations. 

Why? In the OP's example (all AND operators) the parentheses are 
redundant. In SQL, AND and OR are not guaranteed to be short-circuited; 
both operands may be evaluated. In a case like (a+b)*(c+d) the 
parentheses are necessary otherwise a+b*c+d would be interpreted as 
a+(b*c)+d. In parsing any expression in any language, one would expect 
unless documented otherwise that there was no other guarantee of order 
of evaluation -- if your expression has side-effects you are on your 
own. Generally parentheses are ignored immediately their purpose 
(overriding operator precedence) has been fulfilled.


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

Re: [sqlite] INSERT with multiple VALUES clause?

[John Machin]

On 18/08/2009 11:28 PM, Beau Wilkinson wrote:
> That said, if you're in posession of the source code,
 > you can certainly hack something up to support that.
 > A better option might be to pre-process the MySQL file
 > using C, Perl, XSLT (just kidding - don't use XSLT)
 > or whatever else you prefer for this kind of rote file manipulation

"rote" is relative; it's certainly just a flick of the wrist if you're 
willing to bet on there being no ')' characters in the text literals, 
otherwise it gets a bit hairy...

Here's an attempt at something fairly general using Python regular 
expressions; just point this at the remainder of the statement after the 
  VALUES keyword:

import re
value_literal = r"""
 (?:
 ' (?: [^'] | '' ) * ' # text literal
 |
 [^,)\s] + # any other literal
 )
 """
value_list_re = r"\(\s*LIT\s*(?:,\s*LIT\s*)*\)".replace("LIT", 
value_literal)
data = """
 (1,'PENELOPE','GUINESS','2006-02-15 04:34:33'), (2,'NICK','WAHLBERG',
 '2006-02-15 04:34:33'),(3,'ED','CHASE','2006-02-15 04:34:33'),
 (4,'Seamus', 'O''Reilly'),(4.1,x'f00baa'),(5678),
 (6, 'William','Pitt (the Elder)'),(6.1, 'Willie', 'Pitt (the 
Younger)'),
 (  7  , 'spaced'  ,  'out'  )
 """
rx = re.compile(value_list_re, re.VERBOSE)
for vlist in rx.findall(data):
 print vlist

and here's the output:
(1,'PENELOPE','GUINESS','2006-02-15 04:34:33')
(2,'NICK','WAHLBERG',
 '2006-02-15 04:34:33')
(3,'ED','CHASE','2006-02-15 04:34:33')
(4,'Seamus', 'O''Reilly')
(4.1,x'f00baa')
(5678)
(6, 'William','Pitt (the Elder)')
(6.1, 'Willie', 'Pitt (the Younger)')
(  7  , 'spaced'  ,  'out'  )

Cheers,
John


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

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[John Machin]

On 17/08/2009 2:37 PM, Dan Kennedy wrote:
> On Aug 17, 2009, at 11:05 AM, John Machin wrote:
> 
>> On 17/08/2009 11:41 AM, Shane Harrelson wrote:
>>> INDEXED BY doesn't allow you to specify which index to use.  It  
>>> just causes
>>> the query to fail if SQLite thinks it should use an index different  
>>> then the
>>> one specified by the INDEXED BY clause.
>> Oh. The docs say "If index-name does not exist or cannot be used for  
>> the
>> query, then the preparation of the SQL statement fails." Please  
>> consider
>>  submitting a docs bug report (with evidence).
> 
> The two statements are not incompatible.

AFAICT, NONE of the scenarios you outline below fit Shane's statement 
"It just causes the query to fail if SQLite thinks it should use an 
index different then the one specified by the INDEXED BY clause."

For a scenario to match that statement, there must be at least TWO 
indexes on the table.

Only one scenario has two indexes. In that case, whether SQLite "thinks" 
(looks at the analyze results) or not, you say that the result is that 
it goes with the index in the INDEXED BY clause ... so your description 
of that scenario doesn't match Shane's statement at all.

On the other hand, all your scenarios match the above doc excerpt -- 
provided of course that the "use" is interpreted as "use 
beneficially/advantageously".

> 
> What happens in the code is that for a table with an "INDEXED BY" clause
> attached, SQLite does not consider any other indexes or a linear scan  
> when
> planning a query.

"SQLite does not consider any other indexes" is NOT compatible with 
Shane's "SQLite thinks it should use an index different ...".

> SQLite will not do a full scan of an index (unless  
> this
> helps with an ORDER BY clause). If this means no valid plan is found,  
> query
> compilation fails.
> 
> So if you try this:
> 
>CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a);
>SELECT * FROM t1 INDEXED BY i1;
> 
> Then the SELECT statement fails (to prepare) as SQLite cannot find a  
> plan
> where it can use index i1 (advantageously).

Yes, indeed, that select statement constitutes a user bug; it is a 
nonsense, quite irrespective of the presence/absence of i1 or any other 
index, creating/deleting ANALYZE results, or SQLite version changes.

  But if you do this:
> 
>SELECT * FROM t1 INDEXED BY i1 ORDER BY a;
> 
> Then this will prepare and run fine. The following will also work:
> 
>CREATE INDEX i2 ON t1(b);
>SELECT * FROM t1 INDEXED BY t1 WHERE b=10 ORDER BY a;
> 
> In this case, depending on the stats collected by any ANALYZE command,  
> SQLite
> will normally use index i2 to optimize the b=10 constraint. But with the
> INDEXED BY, it uses index i1 to optimize the ORDER BY instead.  
> Presumably the
> user knows something about the contents of table t1 that has allowed  
> her to
> conclude that using index i1 will be more efficient in this case.
> 
> The INDEXED BY feature was introduced to address concerns that SQLite  
> might
> suddenly start using a different plan for a query in the field than it  
> did
> in the office during testing. Either because somebody ran ANALYZE, or  
> because
> the SQLite version was upgraded. In this situation, some users  
> consider it
> better to throw an exception than to run the query with a different,  
> possibly
> slower, plan.

Confusion reigns supreme. Your second last paragraph says (about your 
last scenario) that it uses index i1 instead of the apparently better 
index i2 -- no exception throwing. Your last paragraph indicates that in 
this case an exception would be thrown.

The docs are likewise confused -- after the early piece that I quoted 
(which supports using the INDEXED BY index unless it has vanished or 
would result in a full scan), we find this "The intent of the INDEXED BY 
clause is to raise a run-time error if a schema change, such as dropping 
or creating an index, causes the query plan for a time-sensitive query 
to change. The INDEXED BY clause is designed to help detect undesirable 
query plan changes during regression testing."

SUMMARY:
Three worries with "INDEXED BY i1":

(1) i1 doesn't exist => no argument, raise an exception.

(2) i1 cannot be used advantageously e.g. there is nothing in a WHERE 
clause or ORDER BY clause that fits i1 => no argument, raise an exception.

(3) i1 exists and could be used (as in better than no index at all) but 
there is another index i2 that looks even better => option (a) use i1; 
(b) raise an exception

IMHO whichever of (a) and (b) actually happens, some clarification in 
the docs might be useful.

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

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[John Machin]

On 17/08/2009 11:41 AM, Shane Harrelson wrote:
> INDEXED BY doesn't allow you to specify which index to use.  It just causes
> the query to fail if SQLite thinks it should use an index different then the
> one specified by the INDEXED BY clause.

Oh. The docs say "If index-name does not exist or cannot be used for the 
query, then the preparation of the SQL statement fails." Please consider 
  submitting a docs bug report (with evidence).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question regarding SQLite btree structure

[John Machin]

On 16/08/2009 6:34 PM, deddy wahyudi wrote:

> I am currently on a research project about SQLite btree data structure and I
> have a simple question here.
> 
> I need to retrieve in which offset SQLite keeps my record, for example :
> 
> lets say I have 100 records kept in a table named "customer", with three
> fields, namely "id", "name" and "address"
> I need to know in which offset SQLite keeps all my records

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

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[John Machin]

On 15/08/2009 4:48 PM, Jim Showalter wrote:
> It doesn't collect those statistics automatically, as part of query 
> plan optimization?

You may like to consider looking at
"6.0 Choosing between multiple indices" in
http://www.sqlite.org/optoverview.html

HTH,

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

Re: [sqlite] translating CSV file into sqlite3 database for iPhone?

[John Machin]

On 8/08/2009 2:02 AM, Simon Slavin wrote:
> On 7 Aug 2009, at 4:21am, aerende wrote:
> 
>>sqlite> .import myfile.csv mydatabasetable
>>sqlite> .output mydatabasetable.sql
> 
> When you look at the .sql file in a text editor, does it make sense ?   
> Does it look like legal SQL ?  Does it have all the INSERT commands in ?
> 
> I would probably try it differently: open the .csv file in a  
> spreadsheet program, and use calculations to convert each line into an  
> INSERT command.  Then save that column of commands as a text file and  
> add the CREATE TABLE and other commands to it.

Good idea, but not a novel one; creating INSERT statements using Excel 
is rather prevalent in rapid-response "support" environments and 
provides many work opportunities for data remediaters.

Example: a database where many rows were thrown up by this query:

select account_num, price, qty, amount
from a_table
where price * qty != amount;

Further investigation showed that a high proportion met one of the 
following criteria:
(1) price = account_num
(2) qty = account_num
(3) amount = account_num
(4) price * qty = account_num -- after allowing for rounding.


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

Re: [sqlite] translating CSV file into sqlite3 database for iPhone?

[John Machin]

On 7/08/2009 1:21 PM, aerende wrote:
> I'm trying to take a CSV file and create a sqlite3 database for the iPhone. 
> The CSV file has 33K entries and is 2 MB.  The problem I am having is that
> only about 1/10 of the database file gets written into the sqlite3 database.
> 
> I first translated the CSV file into SQL commands using the terminal-based
> verison of sqlite3:
> 
> % sqlite3
> sqlite> .mode csv
> sqlite> create table mydatabasetable (ITEM_ID INTEGER PRIMARY KEY,
> FIELDA TEXT, FIELDB TEXT);
> sqlite> .import myfile.csv mydatabasetable

Were there any error messages from that step?

If at this stage you do

select count(*) from mydatabasetable;

what is the result? If it's not the full 33K, which records are being 
left out?

> sqlite> .output mydatabasetable.sql

The .output command specifies what file any output will be sent to. It 
doesn't actually generate any output itself. Perhaps you are missing a 
.dump command and a quit command -- it's always a good idea to 
copy/paste actual output into your mail client, rather than re-typing it 
from memory.

> Then I tried to create a sqlite3 database from the sql file:
> 
> % sqlite3 mydatabasetable.sqlite < mydatabasetable.sql

Any errors from this step? What does select count(*) give you?

> 
> When I read in mydatabasetable.sqlite into a sqlite3 database,

What does that mean? A third step? If mydatabasetable.sqlite is not 
already a sqlite3 database, the previous steps have run amok somehow.

> only the
> first 3400 entries out of 33,000 are in the database even though
> mydatabasetable.sql has 33,000 unique insert commands.

In which database?

> Am I following the correct approach to write out an sqlite database? 

Dunno why you are doing it in two (three?) steps; the CSV import should 
be all you need.

> Is
> there some default database filesize limit that I need to set?  Does anyone
> know why only the first 3400 entries show up in the database?

It would help very much if you said what version of SQLite you are 
running and what platform you are running it on.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite database "signature" ?

[John Machin]

On 7/08/2009 2:36 AM, luc.moulinier wrote:

> I'd like to know what is the best way to know if a file
 > is a sqlite DB or not (without launching sqlite of course) ?
 > For example, is the first line of the file unambiguously
 > a signature of sqlite ? If so, what is its structure ?

http://www.sqlite.org/fileformat.html#database_header

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

Re: [sqlite] FTS and postfix search

[John Machin]

On 6/08/2009 12:07 PM, Jim Showalter wrote:
> Sorry--I read my emails arrival order, not reverse chronological--so I 
> didn't see that John had already solved it.

Not me ... this is ancient lore e.g. Knuth vol 3 of TAOCP 1973 edition 
page 391 "If we make two copies of the file, one in which the keys are 
in normal alphabetic order and another in which they are ordered from 
right to left (as if the words were spelled backwards), a misspelled 
word will probably agree up to half or more of its length with an entry 
in one of those two files."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS and postfix search

[John Machin]

On 6/08/2009 11:16 AM, Lukas Haase wrote:
> Wes Freeman schrieb:

> 
>> Strange that it's implemented for prefix and not postfix?
> 
> Well, an explanation is easy: Same as with LIKE, LIKE 'xxx' or LIKE 
> 'xxx%' can be performed easy because only the beginning of words need to 
> be compared.
> 
> However, there /is/ a way to also do postfix searches. I have the *same* 
> database in *.hlp format and with WinHelp it's possible to search 
> '*otor' (and others) with almost zero CPU and time consumption. I'd be 
> curious how they did this.

In memory: maybe a suffix tree.

In a database: have a column with the words stored backwards. SELECT ... 
WHERE back_word LIKE "roto%"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Cutoff Statement

[John Machin]

On 4/08/2009 8:52 AM, Rick Ratchford wrote:
>  
> What I want to do is modify this SELECT statement so that the rows returned
> do not go past a certain date. Let's call it dStopDate.
>  
> If I have dStopDate = '2009-28-07'

Did you mean '2009-07-28' ?

> for example, then the last row I want to
> return is 07/24/2009, which is the last week prior to my dStopDate.
>  
> However, by adding WHERE Date < '" dStopDate "' prior to GROUP BY... (and
> yes, assume Date and dStopDate are same format), my last record returned is
> actually 07/27/2009 (the day before my dStopDate) rather than my 'weekly'
> record of 07/24/2009.

Did you mean "<=" instead of "<" ?



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

Re: [sqlite] Subtotal SQL

[John Machin]

On 29/07/2009 11:34 PM, Adler, Eliedaat wrote:
> SQL/sqlite challenge  for all:

It would be helpful if you made it plain whether you are asking a trick
question, or are a novice with a perceived problem (and whether the
management is insisting that you absolutely must have an SQL-only
solution irrespective of overall efficiency and understandability).


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

Re: [sqlite] Little Help on SQL

[John Machin]

On 27/07/2009 12:16 PM, Rick Ratchford wrote:
> It's a seasonal map, so every year must overlay onto a 366 day grid.
> 
> The table that contains the data has assigned each day a day number from 1
> to 366. If the year isn't a leap year, then day 60 will simply not be
> registered for that year.

Fair enough ... it's just that that's not what first comes to mind upon 
reading "Day Numbers (1 to 366)"

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

Re: [sqlite] Little Help on SQL

[John Machin]

On 27/07/2009 7:40 AM, Rick Ratchford wrote:

> I have a TABLE with a column of Day Numbers (1 to 366) called DayNum.

> Let's say that you want get a count of each DayNum.

> How do I word my statement so that it gives me a count of each DayNum, which
> is from 1 to 366?

Consider leap years ... day number 60 means February 29 in a leap year 
and March 01 in other years. The last day of the year is day 366 in a 
leap year and day 365 in other years. Comparing day numbers that relate 
to different years seems not very meaningful. Grouping by day numbers 
that relate to different years seems likewise not very meaningful (for 
day numbers greater than 59).

What are you trying to achieve?


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

Re: [sqlite] Reporting on summary data

[John Machin]

On 25/07/2009 11:59 AM, David Bicking wrote:
> On Sat, 2009-07-25 at 10:33 +1000, John Machin wrote:

>> An accounting system where the sign of the amount is detached and has to 
>> be obtained from another column is tedious and error-prone; obtaining it 
>> from TWO columns is "interesting"; 

> You must have never dealt with SAP.

What makes you say that?

> The values are all positive, and in
> another column they have H or S to say if it is positive or negative.

Yes, SAP is an example of the type of system to which I referred.

>> a system where the nature of the 
>> transaction or event is not recorded and has to be reverse-engineered 
>> from circumstantial evidence is also "interesting" (especially where the 
>> evidence spans more than one row) -- is this an existing system, or is 
>> it a prototype that you could reconsider?
>>
> 
> The values are always what they are in the amt column, but this report
> is tracking changes for a given date.
> 
> d1 and d2 are starting and ending dates. If you have an id that starts
> on a given date, but has no prior record ending on that date, then it is
> brand new. If one ends on a given date but there is no newer record
> starting, then the item was dropped, and represents a decrease in the
> total. If an item end son one date, and picks up with a new value, then
> it changed. 

Thanks for the explanation; I presumed without any evidence that the 
amounts were transactional rather than current totals or balances ... a 
bit of prejudice carrying over from some systems that I've had to 
struggle with :-)

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

Re: [sqlite] Reporting on summary data

[John Machin]

On 25/07/2009 6:17 AM, David Bicking wrote:
> That works. Thanks!

It struck me that Pavel's revised query didn't mention the d2 column at 
all, only d1:

>> sum(case when d1='X' then 1 else -1 end) as act_sum,
>> sum(case when d1='X' then amt else -amt end) as net

... backtracking, it seems that you haven't stated anything about the 
possibility of having 'X' in neither d1 nor d2. You may like to program 
this along more defensive lines:

sqlite> create table source (id integer, d1 text, d2 text, amt int);
sqlite> insert into source values(1, 'X', 'Y', 15);
sqlite> insert into source values(2, 'X', 'Z',  6);
sqlite> insert into source values(2, 'A', 'X',  7);
sqlite> insert into source values(3, 'B', 'X', 12);
sqlite> insert into source values(4, 'C', 'W', 99);
sqlite> .headers on
sqlite> select id,
...> case kind
...> when 1 then 'NEW'
...> when 2 then 'DROP'
...> when 3 then 'CHANGE'
...> else'WHOOPS'
...> end as Action,
...> net
...> from (
...> select id,
...> sum((d1='X') + (d2='X') * 2) as kind,
...> sum(((d1 = 'X') - (d2 = 'X')) * amt) as net
...> from source
...> group by id
...> )
...> order by 2 desc
...> ;
id|Action|net
4|WHOOPS|0
1|NEW|15
3|DROP|-12
2|CHANGE|-1
sqlite> select id,
...> case when act_sum = 1 then 'NEW'
...> when act_sum = 0 then 'CHANGE'
...> else 'DROP'
...> end as Action,
...> net
...> from
...> (
...> select id,
...> sum(case when d1='X' then 1 else -1 end) as act_sum,
...> sum(case when d1='X' then amt else -amt end) as net
...> from Source
...> group by id
...> )
...> order by 2 desc;
id|Action|net
1|NEW|15
3|DROP|-12
4|DROP|-99
2|CHANGE|-1
sqlite>

An accounting system where the sign of the amount is detached and has to 
be obtained from another column is tedious and error-prone; obtaining it 
from TWO columns is "interesting"; a system where the nature of the 
transaction or event is not recorded and has to be reverse-engineered 
from circumstantial evidence is also "interesting" (especially where the 
evidence spans more than one row) -- is this an existing system, or is 
it a prototype that you could reconsider?

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

Re: [sqlite] An index question

[John Machin]

On 25/07/2009 2:14 AM, Jon Dixon wrote:
> In the description of the "Create Index" statement, it says:
> 
> "Every time the database is opened,
> all CREATE INDEX statements
> are read from the sqlite_master table and used to regenerate
> SQLite's internal representation of the index layout."
> 
> Does this mean that all of the indices are regenerated when the database is 
> opened
[snip]

It doesn't say "regenerate the index". It says "regenerate
SQLite's internal representation of the index layout". It does what it 
says. Consider that building an index can take some considerable time 
... it's not the sort of thing that the average customer wants to wait 
for at the start of the working day or when they turn the phone on to 
dial 112 or whatever -- especially when it's a rather pointless activity.

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

Re: [sqlite] Defining a table that starts rowid as 0

[John Machin]

On 24/07/2009 3:10 PM, Simon Slavin wrote:
> On 24 Jul 2009, at 5:49am, John Machin wrote:
> 
>> On 24/07/2009 3:22 AM, Simon Slavin wrote:
>>
>>> And note that if you have a column which is an integer that
>>> has doesn't allow duplicates, SQLite will automatically use that
>>> column as the one it uses for _rowid_, etc..  So define your own
>>> integer column, feed it whatever integers you want, and you won't
>>> waste any space.
>> Documentation reference, please.
> 
> I'm sorry.  I think that instead of 'integer unique' what I meant was  
> 'autoincrement'.  It's in the section of the documentation about row  
> ids.  I can't get at the web right now.

Perhaps not:

sqlite> create table bar(x text, y integer autoincrement);
SQL error: near "autoincrement": syntax error
sqlite> create table bar(x text, y autoincrement);
SQL error: near "autoincrement": syntax error

In any case 'autoincrement' doesn't correlate with "feed it whatever 
integers you want".

Perhaps you meant 'integer primary key':

sqlite> create table bar(x text, y integer primary key);
sqlite> insert into bar values('aaa', 1);
sqlite> insert into bar values('bbb', 42);
sqlite> insert into bar values('ccc', 666);
sqlite> select rowid, * from bar;
1|aaa|1
42|bbb|42
666|ccc|666
sqlite>


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

Re: [sqlite] Defining a table that starts rowid as 0

[John Machin]

On 24/07/2009 3:22 AM, Simon Slavin wrote:

> And note that if you have a column which is an integer that  
> has doesn't allow duplicates, SQLite will automatically use that  
> column as the one it uses for _rowid_, etc..  So define your own  
> integer column, feed it whatever integers you want, and you won't  
> waste any space.

Documentation reference, please.

How do you account for this:

SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table bar(x text, y integer unique);
sqlite> insert into bar values('aaa', 1);
sqlite> insert into bar values('bbb', 42);
sqlite> insert into bar values('ccc', 666);
sqlite> select rowid, * from bar;
1|aaa|1
2|bbb|42
3|ccc|666
sqlite> insert into bar values('ddd', 42);
SQL error: column y is not unique
sqlite>

?

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

Re: [sqlite] Installing SQLite

[John Machin]

On 23/07/2009 6:48 AM, Rich Shepard wrote:
> On Wed, 22 Jul 2009, scabral wrote:
> 
>> When i download the sqlite-amalgamation-3_6_16.zip i get 3 text files:
>>
>> sqlite3   C File
>> sqlite3   H File
>> sqlite3ext H File
> 
>> what am i supposed to do with those?
> 
>  Well, based on what others wrote about your initial comments, I suggest
> that you replace XP with a linux distribution. Then you can compile that
> source code all by yourself.

No need. One can compile sqlite on Windows all by ones's own self using 
a variety of $-free C/C++ compilers (gcc/mingw32 (FOSS), Borland, and 
there's a command-line compiler somewhere inside the 100Mb VS2009 
Express download from the dark tower of Redmond).

> On the other hand, if you insist on sticking
> with Microsoft, download one of the pre-built Winduhs .zip files as I
> indicated in my previous message.

The pre-built Windows .zip files are paralleled one-to-one by pre-built 
linux .gz files ... someone must consider that inability to compile 
one's way out of a wet paper bag is platform-independent :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do bitwise operators work?

[John Machin]

On 20/07/2009 11:05 PM, Le Hyaric Bruno wrote:
> Hi,
> 
> I'm making some testing with sqlite3.
> I need to know how bitwise operator work? with which type?
> Is that possible to use these operators on blob of thousands of bits?
> 
> To give an idea of the context, I need to store a lot of data (issued 
> from code coverage analysis).
> I'm trying to store this data as bit arrays (array of 20 bits :-x).
> And I want to know if bitwise operators work with such arrays?

sqlite> select 1 | 2;
3
sqlite> select X'01' | X'02';
0

Doesn't look like it.

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

Re: [sqlite] The SQL Guide to SQLite

[John Machin]

On 20/07/2009 12:08 AM, P Kishor wrote:
> 
> unfortunately, we get either advertisements nowadays
> 

> or a signature twice the length of the message warning us that the
> contents of the particular email are confidential and meant only for
> the recipient, and if I am not the recipient then I should promptly
> destroy the message.

The screamingly funny bit is that they should say "*intended* recipient" 
but often they don't -- if I receive it, whether intended or not, I am 
the recipient :-)

> In my view, those who attach such warnings and disclaimers to their
> emails should be fired from their jobs for breach of security and
> protocol

Out there in enterprise land, it is not the individuals who are 
attaching the warning/disclaimer -- admins are directed by the HigherUps 
to configure the mail software to attach the e-bumf automatically


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

Re: [sqlite] SQLite Delete doesn't work

[John Machin]

On 19/07/2009 8:20 PM, Diana Chinces wrote:
> Hi.
> I am having some kind of issues with the delete command when my WHERE expr
> is formed from several expression.

On the surface, what you say you did should have worked. Hence a whole 
bag of questions:

What version of SQLite? Running on what platform? How are you executing 
the SELECT and DELETE statements -- the command-line executable?

> I have something like this:

Please copy/paste *exactly* what you ran, not "something like". We would 
like to see the output from the select -- make it select rowid instead 
of * (we don't need to see your data, just some actual output as part of 
the evidence chain). Include another statement (the same select) that 
shows that the row is still there.

> SELECT * FROM Ture WHERE idT = 1 AND data = '19/07/2009'
> returns one row.
> DELETE FROM Ture WHERE idT = 1 AND data = '19/07/2009' 
> does not delete the row.

Also please show us what the CREATE TABLE statement looks like.

Aside: should be nothing to do with your problem, but if you were to 
store your dates in YMD order (e.g. '2009-07-19') indexes and ORDER BY 
would be much more useful.

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

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[John Machin]

On 17/07/2009 1:30 AM, Simon Slavin wrote:
> On 16 Jul 2009, at 2:35pm, Uijtdewilligen, Freek wrote:
> 
>> Okay, way too much time after discovering the problem, I found the
>> cause: a simple typo :)
>>
>> In the String where it was storing the column-names, it said (x, y, x,
>> etc..), and somewhere this created the null..
> 
> Congratulations and well spotted.  We've all done it.
> 
> 
> On 16 Jul 2009, at 3:36pm, John Machin wrote:
> 
>> This sounds like a bug somewhere -- having a column name twice  
>> should be
>> met with an error message, not with setting the integer column to  
>> NULL.
> 
> It's doing The Right Thing.  The SQL standard states that things like
> 
> INSERT INTO favouriteColour (person,person) VALUES ('Fred', 'Joan')
> 
> must work.  And if a default value for the other field is not declared  
> it naturally gets NULL.  It's silly, but it's right.
> 

Thanks for that, Simon; that's news to me, and rather mind-boggling:

sqlite> create table foo (x, y, z);
sqlite> insert into foo (x, y, x) values ('first', 'second', 'third');
sqlite> insert into foo values ('first', 'second', 'third');
sqlite> select * from foo;
first|second|
first|second|third
sqlite>

I always though the first variety of INSERT was preferred because it did 
some error checking that was not possible with the second variety of 
INSERT :-(

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

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[John Machin]

On 17/07/2009 12:54 AM, Uijtdewilligen, Freek wrote:
>  
>> This sounds like a bug somewhere -- having a column name twice should
>> be
>> met with an error message, not with setting the integer column to
> NULL.
>> So please give us some more information:
>>
>> In the String where it [what is "it"?] was storing the column names
>> [storing for what purpose?] it said (x, y, x, etc) [who said/typed?]
>>
> 
> Sorry, I might have a been a little bit too vague. I use static strings
> containing the table names and columns names, so when I change something
> to the database, I won't have to go through all my code. In one of these
> Strings, I myself made a typing error. When executing an INSERT, one
> column was skipped because of the error in the column names string and
> therefore the default value null was left there. 
> 
> Anyway, it works fine now, so thanks again for the help! :)

Please bear with me -- I'm trying to ascertain where the the second 
problem (bad response to the duplicate-column-name problem) exists:

So the column was skipped (resulting in a NULL being left there by 
default) because (a) SQLite (or whatever layer you were using to execute 
the inserts) skipped the column without giving your code an error 
indication or (b) your code got an error indication but ignored it and 
kept going -- which?

What was the layer that you were using to execute the inserts?

Cheers,

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

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[John Machin]

On 16/07/2009 11:35 PM, Uijtdewilligen, Freek wrote:
> Okay, way too much time after discovering the problem, I found the
> cause: a simple typo :)
> 
> In the String where it was storing the column-names, it said (x, y, x,
> etc..), and somewhere this created the null..

This sounds like a bug somewhere -- having a column name twice should be 
met with an error message, not with setting the integer column to NULL.

So please give us some more information:

In the String where it [what is "it"?] was storing the column names 
[storing for what purpose?] it said (x, y, x, etc) [who said/typed?]

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

Re: [sqlite] storing and retrieving integers with value 0 (not null)

[John Machin]

On 16/07/2009 7:24 PM, Uijtdewilligen, Freek wrote:


> INSERT INTO t_rp (x, y, z)

> VALUES (1, 1, 0);
> it gets stored as (1,1,null).

What evidence do you have to support your assertion that it is stored as 
  NULL?

As Simon has pointed out, 0 != '0'. If after considering that, you feel 
you still have a problem, try selecting the offending rows using some 
criteria that are NOT dependant on column z e.g.

select rowid, z, quoted(z), typeof(z) from t_rp where row is offending;

BTW, if your assertion is true, this query should give some output:

select rowid, z, quoted(z), typeof(z) from t_rp where z is null;

HTH,
John

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

Re: [sqlite] range enclosing a number

[John Machin]

On 14/07/2009 11:44 AM, Jay A. Kreibich wrote:
> On Mon, Jul 13, 2009 at 07:40:48PM -0400, Wes Freeman scratched on the wall:
>> Yeah, sorry about that. In two statements:
>>
>> select max(number) from table where number < ?
>> select min(number) from table where number > ?
> 
>   I'm pretty sure you don't want to do it this way.  What this does is
>   gather every row that meets the WHERE condition and then runs a max()
>   or min() aggregation function across all of those rows.  That means
>   that even if the column "number" has an index on it, between these
>   two statements you're going to end up scanning the whole table.

Let's assume right from the start that there's going to be an index on 
the subject column. too_slow * 2 == too_slow in boss arithmetic :-)

Are you sure? I'm no expert on decoding the EXPLAIN output but the two 
look essentially the same to me: set up a cursor on the index, do a 
SeekLt(the_input_parameter) then test the limit in the first case, do 
exactly ONE AggStep operation in the other case

> 
>   You also have the problem that you can't return the rest of the row.

(1) RowS plural. The limit 1 is arbitrary; there may be more than one 
row with such a value of number.

(2) I would have thought it possible to return the rest of the rows 
using something like this:

select * from table t1 where t1.number = (select max(t2.number) from 
table t2 where t2.number <= ?);

with optional LIMIT if desired.

My rules of thumb: (1) a sub-select like that can be used just about 
everywhere (2) whenever I see "limit 1" I get nervous and want to make 
absolutely sure that the query isn't going to generate a zillion rows 
and throw all but one away, or generate 5 and throw 4 away when somebody 
has presumed incorrectly that there would be only one row not 5.

BTW, has the OP thought about the end conditions (no such lower value, 
no such higher value)?

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

Re: [sqlite] string is converted into a number

[John Machin]

On 14/07/2009 3:04 AM, Simon Slavin wrote:
> On 13 Jul 2009, at 4:35pm, Wilfried Mestdagh wrote:
> 
>> But the circumstances are not really described (possible I cannot read
>> between the lines as my English is not perfect). So as far as I  
>> understand
>> the page if I want to store / retrieve a string (which can be a  
>> numeric
>> string) I have to create my field as "char", "text" or as "none". Is  
>> this
>> correct?

@Wilfried: in CREATE TABLE, you should use a data type that contains 
"char", "clob", or "text" (uppercase or lowercase doesn't matter) -- 
that way the column has TEXT affinity which biases SQLite towards 
storing data as TEXT instead of as numbers. See example below.


> 
> The reference you were pointed to explains what happens:
> 
> http://www.sqlite.org/datatype3.html#affinity
> 
> So you want 'TEXT' ... 'char' doesn't mean anything to SQLite.  

@Simon: I'm not sure what you mean by that; see below:

 From the quoted URL:
"""
If the datatype of the column contains any of the strings "CHAR", 
"CLOB", or "TEXT" then that column has TEXT affinity. Notice that the 
type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
"""

Perhaps it's case sensitive? A weird definition of "contains"? Doesn't 
seem so:

SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table x (a text, b varchar, c char, d string);
sqlite> insert into x values('1', '2', '3', '4');
sqlite> select quote(a), quote(b), quote(c), quote(d) from x;
'1'|'2'|'3'|4
sqlite> select typeof(a), typeof(b), typeof(c), typeof(d) from x;
text|text|text|integer
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Number truncation problem in SQLite 3

[John Machin]

On 13/07/2009 8:40 AM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Jim Showalter wrote:
>> create table words (_id integer primary key autoincrement, wordtext 
>> text not null unique, timestamp integer not null);
>>
>> public class Word
>> {
>> long _id;
>> String wordtext;
>> long timestamp;
>> }
>>
>> timestamp:
>> before save: 1247435151517
>> after save : 1247435160847

The "after" number is greater than the "before" number. That's 
truncation? Looks like some more information is required e.g. a small 
piece of code (preferably runnable) that shows what exactly was done, 
with debug prints in appropriate places.

>>
>> 64-bit max is: 9223372036854775807, so it should fit.
> 
> http://catb.org/esr/faqs/smart-questions.html
> 
> Your mailer headers show that you are using Windows. On Windows
> (including a 64 bit environment) long is 32 bits.

Ummm, each of the quoted numbers takes up 41 bits (unsigned).

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

Re: [sqlite] How-to change column limit?

[John Machin]

On 12/07/2009 10:23 PM, Stephan Lindner wrote:
> 
> I'm importing large survey files into sqlite, and I run into the
> problem of creating a table with too many columns, i.e. 

How many columns do you have?


> bash$ sqlite3 < tables.sql
> 
> produces 
> 
> bash$ SQL error near line 3: too many columns on t3
> (where tables.sql  looks like this: create table t1(var1,var2,...); create 
> t2(var1, var2, ...); )
> 
> Now I figured that I have to change the limit on columns -- see
> 
>   http://www.sqlite.org/c3ref/c_limit_attached.html 
> 
> but I don't know how! I tred all kinds of variations of 
> 
> int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000)
> 
> 
> as for instance 
> 
> sqlite> int sqlite3_limit(sqlite3*, SQLITE_LIMIT_COLUMN, 5000);

Huh? "sqlite>" indicates the command-line executable program ... but int 
sqlite3_limit() is C code
> 
> all without success. 

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

Summary: Default is 2000. You can change the default max at COMPILE 
time, up to 32767. You can REDUCE the maximum at RUN time using 
sqlite3_limit().

If, as it appears, you have more than 2000 columns, you might like to 
consider a bit of normalisation of your schema. Please note carefully 
the remarks about O(N**2) in the docs.

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

Re: [sqlite] SQL Syntax

[John Machin]

On 9/07/2009 2:21 PM, Rick Ratchford wrote:
>  
> Okay, this worked, but I have NO IDEA why.
> 
> SQLString = "SELECT min(Year) FROM TmpTable " & _
> "WHERE Month=1 UNION " & _
> "SELECT max(Year) FROM TmpTable " & _
> "WHERE Month = 12 LIMIT 2"
> 
> 
> While this returned the correct answers:
> 
> 1988
> 2008
> 
> What I don't understand is why it didn't simply return:
> 
> 1988
> 1988
> 
> Since there is at least 15 or more days in Month=1 (Jan).
> 
> Anyone?
> 
> Thanks.
> Rick
> 
> 
> 
> Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
> Sent: Wednesday, July 08, 2009 11:17 PM
> To: 'General Discussion of SQLite Database'
> Subject: [sqlite] SQL Syntax
> 
> I've been trying all kinds of different ways to accomplish the following,
> and my head is spinning.
>  
> Problem: How do you return from the DB just the YEAR of the first and last
> YEAR that had dates from 1st week  of January to last week of December?
>  
> Clarification: Suppose you had a database that contained stock price data.
> You are only interested in the first and last year that was a complete year.
>  
> A "complete year" is a year where you have price data from the very first
> weekday (not weekend or holiday) of the year to the very last weekday of
> that year. Usually, a complete year is from Jan 2, 3 or 4 to Dec 29, 30, 31.
>  
>  
> Result Desired: To simply return the YEAR of the first complete year, and
> the YEAR of the last complete year of the dataset. This should return only
> two years in a single column. If 1988 is the first complete year and 2008 is
> the last complete year, then it should only return:
>  
> 1988
> 2008
>  
> Available columns are:
>  
> DATE (complete date)
> YEAR
> MONTH
> DAY
> ...
>  
>  
> I'm trying to do something like this, but it won't work because it says you
> can only have one LIMIT clause.
>  
> SQLString = "SELECT min(Year) FROM TmpTable " & _
> "WHERE Month=1 LIMIT 1 UNION " & _
> "SELECT max(Year) FROM TmpTable " & _
> "WHERE Month = 12 LIMIT 1"
> 
> Help would be appreciated.
>  
Each SELECT will return only 1 result. Split that up into 2 queries 
(omit the LIMIT clause; it's redundant) and see for yourself.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Complete Years Only

[John Machin]

On 9/07/2009 9:33 AM, Rick Ratchford wrote:
> Lucky nothing. You're just brilliant. Such humility. :-)
> 
> Yes, it worked wonderfully. The dataset only contains data that does not
> fall on weekends. Stock data to be exact.
> 
> It is likely to start sometime during the year of the first year available
> and end sometime during the year of the last year. I want to only return
> 'complete years' from January to December.
> 
> Now I will admit that I made a minor adjustment once Igor pointed me in the
> right direction. Since stock data does not start on Jan 01, as that is New
> Year's and no trading occurs then, and that not all years will see trading
> on Dec 31. I simply adjusted the statement to allow a few days following Jan
> 01 and a few before Dec 31. 
> 
> Thanks again. :-) 
> 
> Rick
> 
>  
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Wednesday, July 08, 2009 6:17 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Getting Complete Years Only
> 
> John Machin <sjmac...@lexicon.net> wrote:
>> On 9/07/2009 3:39 AM, Igor Tandetnik wrote:
>>> Rick Ratchford
>>> <r...@amazingaccuracy.com> wrote:
>>>> Can someone help me with a SQL request?
>>>>
>>>> The Table contains Date, as well as Year, Month and Day columns.
>>>>
>>>> I would like to return a recordset that is made up of only COMPLETE 
>>>> YEARS, from January to December.
>>>>
>>>> Say my data starts on August 14, 1975 and ends with May 4, 2009.
>>>>
>>>> How do I get just those records that start from January 1, 1976 to 
>>>> December 31, 2008, as those would be the only COMPLETE YEARS from 
>>>> January to December in my table?
>>> Perhaps something like this:
>>>
>>> select * from myTable
>>> where Year between
>>> (select min(Year) from myTable where Month=1 and Day=1) and
>>> (select max(Year) from myTable where Month=12 and Day=31);
>>>
>> This assumes something that wasn't explicitly stated: there is data 
>> for each and every day from the start date to the end date.
> 
> Hence "perhaps". In the face of incomplete information, I made an educated
> guess and designed the simplest query that would produce correct results
> under certain reasonable assumptions. Occam's razor and all that. Had I
> guessed wrong, the OP would have come back with the explanation as to why
> the query didn't work for him, necessarily supplying the missing information
> in the process. Then I could come up with a more complex query taking new
> facts into account.
> 
> It seems I got lucky on the first attempt, and the OP is happy with the
> results.

It seems you weren't lucky, but the OP has worked out how to adjust the 
query and happiness after all prevails :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Complete Years Only

[John Machin]

On 9/07/2009 3:39 AM, Igor Tandetnik wrote:
> Rick Ratchford 
> wrote:
>> Can someone help me with a SQL request?
>>
>> The Table contains Date, as well as Year, Month and Day columns.
>>
>> I would like to return a recordset that is made up of only COMPLETE
>> YEARS, from January to December.
>>
>> Say my data starts on August 14, 1975 and ends with May 4, 2009.
>>
>> How do I get just those records that start from January 1, 1976 to
>> December 31, 2008, as those would be the only COMPLETE YEARS from
>> January to December in my table?
> 
> Perhaps something like this:
> 
> select * from myTable
> where Year between
> (select min(Year) from myTable where Month=1 and Day=1)
> and
> (select max(Year) from myTable where Month=12 and Day=31);
> 

This assumes something that wasn't explicitly stated: there is data for 
each and every day from the start date to the end date. For example, if 
there were no records for January 1977, Igor's query would still use 
1976 as the first complete year.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue in using 'LIKE' with Indexes.

[John Machin]

On 8/07/2009 7:11 PM, aalap shah wrote:
> Hi,
> 
> I am a new user to sqlite3, I have a program that searches through a
> database. I have a table with 1 column as varchar and I want to
> perform a search on it.
> I have created an index over that column. And I use a select query
> with "column_name LIKE 'a%' ".
> So my first question is will this query use the index created or not?

If that is the only index on the table, it should be used. If there is 
another index on some other column in the table and that other column is 
mentioned in the query, then SQLite may decide to use that other index 
instead.

You can find out what indexes are being used by using "explain query 
plan select ..." instead of "select ..."; instead of returning results 
it will return a row of info for each table involved in the query.

Note the above answer is conditional on the expression having a trailing 
  '%'. Anything other than a "startswith" condition can make no use of 
the  index.

Reading material: http://www.sqlite.org/optoverview.html

> 
> And if it does then , according to my understanding select query like
> above will directly locate records starting with 'a' and results will
> be returned.

Not "directly" in the sense that a hash index would in a non-LIKE case. 
All SQLite indexes use a BTree structure. It will locate all rows such 
that 'a' <= your_column < 'b', typically by descending the tree to 
locate the smallest value that is >= 'a' then proceding in key sequence 
until it finds a value that doesn't start with 'a'.

> And if I change my query to have "column_name LIKE 'ab%'
> " will take more time then previous because sqlite3 will have to
> perform strcmp of some sort to find results.

No, it will use the same procedure as the LIKE 'a%' query.

> But the results that I have observed , it seems that 2nd query takes
> less time than first one.

Ummm, that could be because logic guarantees that (# rows retrieved by 
LIKE 'a%') <= (#rows retrieved by LIKE 'ab%') ... more character 
comparisons, but their cost is trivial compared to the cost per row 
retrieved.
> 
> And if it doesn't then, how do I make use of index with LIKE queries.

See section 4.0 of the link I quoted above.

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

Re: [sqlite] Query by Day

[John Machin]

On 8/07/2009 2:14 AM, Rick Ratchford wrote:
[snip]

> To John Machin: To save from answering multiple messages (and save space for
> all), I'll address John's reply here.
> 
> --
> "Consider getting answers faster by (a) trying things out yourself, e.g. 
> type this at the sqlite3 command-line program:
> 
> select CAST(strftime('%d', '2009-06-30'), INTEGER);
> 
> and (b) looking at the docs; in this case
> http://www.sqlite.org/lang_expr.html;
> --
> 
> I'll consider myself admonished. But let me say that I go through a lot of
> searching and testing before I ask questions. Also, I'm a novice programmer
> and really new to SQLite. So some things may be more obvious to others than
> to me. I'm not yet familiar with a "sqlite3 command-line program".

It's a very handy utility ... trying syntax out to see if it works; 
prototyping queries before you embed them in VB6 or whatever; lots more 
uses. In future when you have a question like "why is my query producing 
output X instead of output Y" you will be helping yourself more if you 
publish the query and say "I've run this using the sqlite3 program and 
it gives the same unexpected results" [you now have a larger audience; 
people who don't know any VB6 can help you] or "it gives the expected 
results" [you've isolated the problem to be related to usage of VB6; 
you'll get more focussed responses]

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

download: http://www.sqlite.org/download.html
See "Precompiled Binaries For Windows ... sqlite-3_6_16.zip (246.32 
KiB) ... A command-line program for accessing and modifying SQLite 
databases."

> I'm
> programming in VB6. My question on CAST wasn't so much whether I can do it
> (as testing would bear out), but was more towards whether I should do it.

If you mean "should", say "should", not "can".

> Experts here could steer me away towards a better way that I'm not aware of.

> Anyway, thank you John.

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

Re: [sqlite] Query by Day

[John Machin]

On 7/07/2009 10:13 AM, Rick Ratchford wrote:
> Hi Simon. 
> 
> Ah. So what I need to do then is to make the return of strftime of type INT.
> 
> Since I'm creating a recordset from an existing table (rather than creating
> a table itself), then I don't have the option to set the affinity of my
> newly created column Day to INT.
> 
> Can CAST(strftime('%d', Date), INTEGER) be used in this context, or is there
> another way?

It can be used, but not with a very productive outcome.

Consider getting answers faster by (a) trying things out yourself, e.g. 
type this at the sqlite3 command-line program:

select CAST(strftime('%d', '2009-06-30'), INTEGER);

and (b) looking at the docs; in this case 
http://www.sqlite.org/lang_expr.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to refer to a multiple-column primary key (PK) as 1 column (field) name

[John Machin]

On 5/07/2009 5:49 AM, James Scott wrote:
> I have the following:
> 
> CREATE TABLE [Sections] (
>   [Department] varchar NOT NULL COLLATE NOCASE,
>   [Course] varchar NOT NULL COLLATE NOCASE,
>   [Section] varchar NOT NULL COLLATE NOCASE,
>   [Class_Time] timestamp,
>   [I_Id] varchar COLLATE NOCASE,
>   [Room] varchar COLLATE NOCASE,
>   CONSTRAINT [sqlite_autoindex_Sections_1] PRIMARY KEY ([Department],
> [Course], [Section]));
> 
> CREATE INDEX [PK_Sections] ON [Sections] ([Department], [Course], [Section]);

Ummm, after those two statements, you have TWO indexes on your 3 fields.

sqlite> .header on
sqlite> select * from sqlite_master where type = 'index';
type|name|tbl_name|rootpage|sql
index|sqlite_autoindex_Sections_1|Sections|3|
index|PK_Sections|Sections|4|CREATE INDEX [PK_Sections] ON [Sections] 
([Department], [Course], [Section])
sqlite>

What are you trying to achieve?

> In the programming language, I need to refer to the primary key as 1 field.

And "the programming language" (why the mystery? which language?) 
doesn't support concatenation of strings? Or better, e.g. Python's tuple 
  pk = (department, course, section) which can be used as a dictionary 
key or a set element or a sort key or ... and can be easily picked apart 
to recover the parts: department, course, section = pk

> Does Sqlite allow a 'calculated field', such as concatenation of the 3
> columns in the PK? 

Of course. SQL has allowed it since the year dot.
http://www.sqlite.org/syntaxdiagrams.html#result-column
"expr" => expression ... do what you want.

HTH,
John

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

Re: [sqlite] SQL Query Question

[John Machin]

On 4/07/2009 9:01 AM, Simon Slavin wrote:
> On 3 Jul 2009, at 10:03pm, Rick Ratchford wrote:
> 
>> Suppose my 15 Dates are:
>>
>> 2009-03-03
>> 2008-11-05
>> 2008-07-10
>> ...
>> ...
>> 2007-07-23
>>
>>
>> Assuming this is a SORTED dataset in ascending order by Date, I  
>> would need
>> to extract 40 records that start with the record at 2009-03-03, then  
>> 40
>> records starting with the record at 2008-11-05, and so-forth.
>>
>> Can I do this in one SQL statement and have it produce one Recordset  
>> of this
>> result? Or would I have to run 15 different queries?
> 
> If you have exactly 40 records for each day then you can do it with a  
> JOIN.  Or put all the dates into a long string like  
> 'x2009-03-03x2008-11-05x ...' and use 'LIKE'.  However, if you have  
> different numbers of entries for each day then I can't think of a way  
> to do it in one SELECT.
> 
> Oh, hold on, you mean you have one record for each day, and you want  
> the records for those days and the 39 days after each of those days.   
> You could make an extremely long SELECT with lots of 'AND' clauses.   
> But I think you're going to have to do it in software.

There's a strong presumption that there are missing days i.e. there is 
not a row for each possible day, so you can't just do "where date 
between x and x-plus-40-days" ...

How many years of data? 10? That's max 3653 dates. Using Python, you'd 
run a query to fetch *all* rows in date order. Build a dict mapping date 
to row index. Then for each of your interesting dates, use the dict to 
get rowindex, and your up-to-40 required rows are 
result_set[rowindex:rowindex+40]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[John Machin]

On 3/07/2009 7:08 AM, Ed Hawke wrote:
> 
> Out of interest, would I be able to use binding on the run-time defined 
> fields?
> 
> If I wanted to use:
> 
> select * from A
> join B b1 on (A.Column3 = b1.ID)
> join C c1 on (b1.Column1 = c1.ID)
> join D d1 on (b1.Column2 = d1.ID)
> 
> join B b2 on (A.Column4 = b2.ID)
> join C c2 on (b2.Column1 = c2.ID)
> join D d2 on (b2.Column2 = d2.ID);
> where d2.ID = ?
> 
> Would that work?

If we had an explicit literal, e.g. "where d2.ID = 1234", would that 
work? Of course it would work.

Here's the scoop: you can have a binding parameter anywhere you can have 
a literal. It's that simple.

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

Re: [sqlite] 3 million rows, query speeds, and returning zero for rows that don't exist

[John Machin]

On 2/07/2009 11:00 AM, yaconsult wrote:
> 
> Most of the queries I've done so far have been pretty straightforward
> and it's worked very well.  But, now I need to do one that's taking
> too long.  There's probably a better way than the one I'm using.
> 
> The problem is that I need to produce information for the timeslots
> that are missing as well as those that are there.
> 
> For example, I need to be able to evaluate the effect of different
> load balancers, so I want to compare the numbers of transactions on
> all the ports.   I need to do so on a second by second basis so I can
> see the most detail and not an average.
> 
> I thought one way do do this would be with a self-join because
> there are so many transactions that I'm pretty sure that all seconds
> will be present in the table.  I also considered creating a second
> table derived from the first whenever it's updated that would have a
> unix epoch entry for each second within the log file. 

Consider filling in zeroes for missing timeslots in the script that's 
processing the results of a simple only-non-missing-timeslots query:

Assuming the results come out in time order: I dunno about perl, but in 
Python I'd read the results in a generator which maintained a one-line 
history and yielded missing or non-missing slot data as appropriate. You 
can get the same effect without generators, it's just a bit awkward/ugly.

If results not in order, either USE ORDER BY or for a daily report 
prepopulate an array with 24*60*60 slots containing zero, fill in with 
your query results, process the array.

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

Re: [sqlite] first few characters of varchar() corrupted when SELECTing from a C++ program?

[John Machin]

On 30/06/2009 2:56 PM, freshie2004-sql...@yahoo.com.au wrote:

> printf("testValue=(%s)\n");

I've always been afraid to use those new-fangled mind-reading C 
compilers lest they were easily shocked ;-)

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

Re: [sqlite] Column headers of result

[John Machin]

On 29/06/2009 2:57 PM, BareFeet wrote:
> Hi,
> 
> Is there any way in the command line to get the columns in a query  
> result?
> 
> For example, given an ad-hoc SQL command, such as:
> 
> begin;
> insert into MyTableOrView select * from SomeSource;
> select * from MyTableOrView join SomeOtherTableOrView where condition;
> end;
> 
> how can I get the column headers in the result?
> 
> I know I can get the column info of a table using pragma table_info,  
> but I don't think that works for an ad-hoc query.
> 

SQLite version 3.6.14
Enter ".help" for instructions<<<=== ever noticed this before?
Enter SQL statements terminated with a ";"
sqlite> .help
[snip]
.header(s) ON|OFF  Turn display of headers on or off
[snip]
sqlite> select 1 as one, 2 as two;
1|2
sqlite> .header on
sqlite> select 1 as one, 2 as two;
one|two
1|2
sqlite> select 1 as one, 2 as two, 3;
one|two|3
1|2|3
sqlite> create table foo (bar int);
sqlite> insert into foo values(42);
sqlite> select * from foo;
bar
42
sqlite> select bar as rab from foo;
rab
42
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Near misses

[John Machin]

On 27/06/2009 7:00 AM, Jean-Christophe Deschamps wrote:
> At 13:25 26/06/2009, you wrote:
> ´¯¯¯
>> I am trying to find words in a dictionary stored in sqlite, and trying
>> a near miss approach.
>> For that I tried an algorithm to create patterns corresponding to
>> Levenshtein distance of 1 (edit distance of 1).
>> That means, one adition, one remotion or one substitution.
>>
>> Any hint on how to speed up this thing?
> `---
> 
> Hi,
> 
> I'm currently finishing an C extension offering, among other functions, 
> a "TYPOS" scalar operator which is meant to perform just that, and a 
> bit more.

There's a strong presumption that it doesn't handle CJK text, but what 
about alphabets other than Latin-based e.g. Arabic, Cyrillic, Greek, 
Hebrew, ...?

> Internally, it applies a Unicode fold() function,

What does fold() do? Strip off accents/umlauts/etc?

> a Unicode lower() 

upper() might be more suitable; consider the German eszett (U+00DF).

> function and then computes the Damerau-Levenshtein distance between the 
> strings.  It returns the number of insertions, omissions, change and 
> transposition (of adjacent letters only).

Consider an additional API which returns a scaled similarity score e.g 
1.0 - float(distance) / max(length(string1), length(string2))

> If the reference string is 'abcdef', it will return 1 (one typo) for
> 'abdef' missing c
> 'abcudef'   u inserted
> 'abzef' c changed into z
> 'abdcef'c & d exchanged
> 
> It will also accept a trailing '%' in string2 acting as in LIKE.
> 
> You can use it this way:
> 
>select * from t where typos(col, 'levencht%') <= 2;
> 
> or this way
> 
>select typos(str1, str2)
> 
> The code currently makes use of a couple of Win32 functions, which 
> should have Un*x equivalent.  It runs at really decent speed even if I 
> didn't fight for optimization.  It will obviously outperform any SQL 
> solution by a large factor.

Does it use the icu library? What is the memory footprint?

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

Re: [sqlite] How to find the version of the database.

[John Machin]

On 27/06/2009 3:36 AM, Kalyani Phadke wrote:
> Is there any way to find the version of SQlite3 database. eg. I have
> test.DB file . I want to know which SQLite3 version its using ..eg 3.5.4
> or 3.6.15? 

Short answer: You can't know. What problem do you face that makes you 
want to know? If the problem is "I need to know the earliest version of 
SQLite3 that can be used to access test.db"

Long answer:

Assuming the database has been created/updated by version X of the 
library and you attempt to open the database with version Y of the library:

if Y < X
and X used some new feature that Y doesn't know about:
Y won't access the database

Q: How does Y know what X did?
A: X records the most recent feature-set that it used in bytes 44..47 of 
the database header; see 
http://www.sqlite.org/fileformat.html#database_header

 From the number you find there (should be one of 1, 2, 3, 4) you can 
infer some but not much information about what versions have been 
updating the database. Note: either of those two versions that you 
mention could have been updating the database, but if neither used any 
new features, the magic number would be 1 on both cases. The magic 
number ("Schema layer file-format") is in effect a file-format version 
number. This may well be what you need -- there seems to be no way of 
getting this number short of opening the database as a file however you 
can and digging it out.

HTH,
John


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

Re: [sqlite] search in archive

[John Machin]

On 20/06/2009 3:56 AM, Rizzuto, Raymond wrote:
> Is it possible to have a search feature for the archive?  I.e. rather than 
> having to do a linear search through 18 archives for an answer to a question, 
> have a google-like search across all of the archives?

http://search.gmane.org/

In the box called "group", type in: comp.db.sqlite.general

Based on a reverse-date search for "Richard Hipp", it appears to go back 
to 2002 at least.

HTH,
John

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

Re: [sqlite] Literal replacements in prepared statements

[John Machin]

On 20/06/2009 12:06 AM, Shaun Seckman (Firaxis) wrote:
> Not sure I fully understand what you mean.

> Is it not possible to replace the table name in the prepared statement?

It is not possible.

>  What sort of things can I replace then?

You can do replacement at any place where a "literal" (i.e. a constant) 
is legal.

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

Re: [sqlite] Sqlite-3.5.9: getting sqlite_autoindex error

[John Machin]

On 18/06/2009 10:40 PM, hiral wrote:
> Hi Simon,
> 
> Thank you for your quick reply.
> 
> I am sorry for more general questions.
> 
> As I mentioned I was getting corrupted db error with sqlite-3.5.9, 

"was getting error often" or "did get error ONCE"??

> but when
> I tried with sqlite-3.6.4 it is no more corrupting the db.

What is the "it" that was corrupting the db when you ran (what?) with 
3.5.9 but is no longer corrupting the db with 3.6.4?? Did you run enough 
tests with 3.6.4 for long enough ?

> -- so was it a bug with sqlite-3.5.9 ? and got fixed in sqlite-3.6.4

Consider that the cause may be closer to home ... in an earlier message 
you wrote "I am using sqlite-3.5.9 and observing a 'disk image 
malformed' error nfs, on doing 'PRAGMA integrity_check' I got following 
messages...". Is that "nfs" what I think it is? What exactly is the 
environment: simultaneous users? db file on a network share? threads? 
unsafe pragma usage?

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

Re: [sqlite] Database inserts gradually slowing down

[John Machin]

On 17/06/2009 11:52 AM, Dennis Cote wrote:
> Jens Páll Hafsteinsson wrote:
>> Closing and opening again did not speed up steps 1-4, it actually slowed 
>> things down even more. The curve from the beginning is a bit similar to a 
>> slightly flattened log curve. When I closed the database and started the 
>> test again, a similar curve appeared again, but now starting from where the 
>> first run left off.
>>
>> I've been running the same 3.6.15 since this afternoon (the previous test 
>> was using 3.6.14) and it seems to flatten out fairly quickly but it is 
>> significantly slower (2.3 to 1.3 times slower, depending on where you 
>> measure it using the data I have). I'm not that worried about that for the 
>> time being; I'm just hoping it will stay flat.
>>
>> JP
>> On 16 Jun 2009, at 1:06pm, Jens Páll Hafsteinsson wrote:
>>> 1.   start a transaction
>>> 2.   insert 1000 records
>>> 3.   commit
>>> 4.   repeat steps 1-3 100 times
>>> 5.   delete everything from the table
>>> 6.   Start again at step 1
> 
> I suspect that you may be using a autoincrement id field and then 
> running into the extra work (both CPU load and increased disk space) 
> needed to handle the variable sized integer storage method used by 
> SQLite. This would lead to the type of logarithmic growth you are 
> seeing. The first few iterations used short single byte integer values, 
> the next bunch use 2 byte integer values, etc. The autoincrement field 
> would cause SQLite to continue at the same speed after restarting the 
> application as you have described, since the next field values used 
> would continue from where it left off at the end of the previous run.

Even after "delete everything from the table"; see below.

> 
> I would have expected the time to stabilize on 3 byte values fairly 
> qucikly, and then only change again when switching to values that 
> required 4 bytes.
> 
> This may be a part of the answer even if it is not the complete answer.

 From Jens's description, he is writing only 1000 * (1 + 100) = 101,000 
records before "delete everything from the table". A 3-byte variable 
integer will hold a number 16K to 2M approx. 4-byte: 2M to 268M approx.

On the surface, should be OK. UNDER the surface:

sqlite> create table t (p integer primary key autoincrement, x text);
sqlite> select * from sqlite_master;
table|t|t|2|CREATE TABLE t (p integer primary key autoincrement, x text)
table|sqlite_sequence|sqlite_sequence|3|CREATE TABLE 
sqlite_sequence(name,seq)
sqlite> insert into t(x) values('blah');
sqlite> insert into t(x) values('yadda');
sqlite> select * from sqlite_sequence;
t|2
sqlite> delete from t;
sqlite> select * from sqlite_sequence;
t|2 == whoops

Documented (and good behaviour) -- never re-issue a key while the table 
exists.

Possible solution (apart from DROP TABLE):

sqlite> delete from sqlite_sequence where name = 't';
sqlite> select * from sqlite_sequence;
sqlite>


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

Re: [sqlite] Question about searches

[John Machin]

On 17/06/2009 1:19 AM, Christophe Leske wrote:

>>> So far ,  so good, but my client also expects ANY simplification of a
>>> character to be recognized:
>>> Cote d'azur for instance  should return "Côte d'azur"
>>> or the Sao Paulo issue - how can a search for "Sao Paulo" return "Sào
>>> Paulo" in the result set?
>>> 
>> How are these examples different from previous ones?
>>   
> I am sorry, but I find this to be quite obvious?
> Here, the problematic char is to be found in the *result set*, not in 
> the query itself.

It's NOT different. You need to map BOTH your database values and your 
query values into the same space and then compare them. Don't fall into 
the trap of assuming that your database is correctly accented.

> 
> How do you educate SQlite to return me "Sào Paulo" if only "Sao Paulo" 
> is being entered?
> How do I know which character to substitute with a placeholder?
> 
> Is it
> S%o Paulo to look for?
> Or Sa% Paulo?
> Or Sao P%ulo?
> 
> I can't know this beforehand. These are just examples, i need a generic 
> solution if possivble.
> 
> All i can see so far is to build a table of all special characters ever 
> used in the 24000 names of cities which make problems and remap them 
> accordingly.

That's exactly what you need. And you're not the first person with this 
problem. See for example 
http://mail.python.org/pipermail/python-list/2008-July/669592.html

The technique discussed there starts off with using the unicodedata 
database and finding dynamically (and caching) Unicode characters that 
can be decomposed into a basic latin letter plus one or more accents, 
backed up by a table of cases not found by that technique. Great for 
likers of clever code who have lots of CPU and disk space (unicodeddata 
is huge!) to spare.

I have developed a table which maps most latin-decorated Unicode 
characters into the non-decorated basic form. Sometimes 2 ASCII 
characters will be produced (e.g. latin capital letter thorn -> "Th") 
but latin small letter u with diaeresis -> "u" -- not "ue" which is 
German-specific.

I can let you have a copy if you are interested. What is your 
implementation language? C/C++?

BTW someone mentioned smashing everything into lowercase for comparison 
purposes at some stage -- I'd suggest uppercase especially if you have a 
few of the good old eszett in your data :-)

BTW2: The only sane usage of soundex IMHO is as a strawman when 
proposing phonetic matching algorithms like NYSIIS and [Double 
]Metaphone :-)

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

Re: [sqlite] Database inserts gradually slowing down

[John Machin]

On 17/06/2009 6:17 AM, Hoover, Jeffrey wrote:

> One other note, if you have a primary key whose value is continually
> increasing your pk index can become imbalanced and therefore
> inefficient.

A B-tree becomes imbalanced? How so?

http://www.sqlite.org/fileformat.html#btree_structures says: "The tree 
is always of uniform height, meaning the number of intermediate levels 
between each leaf node page and the root page is the same."

Do you have any evidence to the contrary?

> You may be able to get around this by using INTEGER
> PRIMARY KEY (or INTEGER PRIMARY KEY AUTOINCREMENT).

So with autoincrement SQLite3 generates the "continually increasing" pk 
values instead of the app doing it, and that solves the alleged problem?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatypes

[John Machin]

On 16/06/2009 10:47 PM, A Drent wrote:
> Sorry, something went wrong on the previous post.

*AND* on this one; you are starting a new topic but you included about 
900 lines from today's digest!!

>>From the docs I read that for the new version:
> 
> a.. When new tables are created using CREATE TABLE ... AS SELECT ... the 
> datatype of the columns is the simplified SQLite datatype (TEXT, INT, REAL, 
> NUMERIC, or BLOB) instead of a copy of the original datatype from the source 
> table.

What source table? There may be 0, 1, or many tables involved. E.g.

create table foo as select 1, 2.34, 'hello', x'f00baa', null;

> I don't know why this has been done, as far as I can tell this will cause 
> trouble in several wrappers. I.e. the Delphi wrappers depend on regular 
> datatypes like varchar(xx), number etc. If SQLite will 'translate' this into 
> other datatypes this will cause inconsistancies. If this is necessary why 
> not then just allow the primitive datatypes within the 'create'? Then the 
> reported datatype will be the same as the datatypes uses within the create. 
> What will happen on a create table when the datatype is other then the 
> primitives? How does SQLite translate these? Or am I completely 
> misundertanding things?

Possibly. To save us from misunderstanding you, show us a concrete 
example of something going wrong (in your opinion).

> 
> albert

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

Re: [sqlite] Shortest time interval? [Was: Re: repeating events?]

[John Machin]

On 13/06/2009 9:05 AM, Allen Fowler wrote:

> Indeed, I am aware that SQL is not a "traditional"
 > programming language per-se and have will now be writing
 > the calendar logic at the application level.  (Looking at Python...)

Don't look any further :-)
Check out the dateutil module...
http://labix.org/python-dateutil
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] repeating events?

[John Machin]

On 13/06/2009 1:08 AM, Allen Fowler wrote:
>> What are you doing about timezones and DST? Are "start" and "end" UTC? 
> 
> For v1, all local times.  UTC is not a requirement yet, but if can be added 
> with out hassle, then why not.   
> 
>> Is a location (and by extension a timezone) associated with events like 
>> face-to-face meetings?
> 
> No TZ affected location data is stored.  (All local areas.)

I'll ask again: What are you doing about DST?

> If you have any suggestions for how start planning for multiple TZ and UTC / 
> DST support, I would be very interested to hear.

UTC is neither an (external) "requirement" nor something your app 
"supports". Recording all times in a way that is independent of TZs and 
DST is necessary to avoid total confusion if your people and resources 
can be in more than one TZ or DST is used in the single TZ. UTC happens 
to be the obvious candidate for that way.

Plan to throw v1 away. v2 ("support UTC/DST/multiple TZs") will require 
starting from scratch. Plan for v3 ("support DD/MM/, MM/DD/, 
-MM-DD, etc etc date formats in the UI") and v4 ("support Unicode 
and multiple languages in the UI").

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

Re: [sqlite] repeating events?

[John Machin]

On 12/06/2009 7:48 PM, Allen Fowler wrote:
> idname  kind  start   
>   end   length
>       
>     --
> 3 joe   hour  2009-06-13 
> 09:00:00   2009-06-13 10:00:00   3600  
> 4 tom   day   2009-06-13 
> 00:00:00   2009-06-14 00:00:00   3600  

What are you doing about timezones and DST? Are "start" and "end" UTC? 
Is a location (and by extension a timezone) associated with events like 
face-to-face meetings?



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

Re: [sqlite] Issue with Distinct and Large numbers

[John Machin]

On 12/06/2009 11:14 AM, dbcor...@rockwellcollins.com wrote:
> I receive erroneous data when I try to populate a table using data from 
> another table: Here is how!
> 
> I have TABLE A (that has IDs of INTEGER, Seats as INTEGER, and so forth)
> 
> I want to take this master table and in essence transfer the data I only 
> need into 
> another table called TABLE B (say it only has IDs of INTEGER)
> 
> To do this I do the following 
> INSERT INTO TABLE B SELECT DISTINCT Content_ID from TABLE A
> 
> unfortunately numbers like 
> 
> 854459, 854477, 
> 900499, 900517, 
> 905209, 905227, 
> 
> will produce float results of 854459.1, 854477.1, etc. 
> but the neighboring numbers stay intact and do not produce a float value.

[snip]

You are likely to get better responses faster if you post a small script 
(preferably language agnostic e.g. for the command-line sqlite3 program) 
that reproduces what you describe. Also what version of sqlite3? binary 
download or home-made (if so, how?)?, what platform are you running this 
on? what mechanism are you using that inspects table_B and finds values 
like 854459.1? is this finding corroborated by any other mechanism?

But before you start that, try this simple query; it might give us a 
clue as to what the problem is:

select typeof(Content_ID), count(*)
from table_A
group by typeof(Content_ID);

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

Re: [sqlite] Problem with sqlite3_interrupt on Windows

[John Machin]

On 10/06/2009 9:02 AM, Igor Tandetnik wrote:
> Jeremy Smith wrote:
>> John Machin wrote:
>>> On 10/06/2009 4:40 AM, Jeremy Smith wrote:
>>>
>>>> When I run sqlite3_interrupt, it doesn't close existing file
>>>> handles, making further searches tricky.
>>>>
>>> Which handles? How do you know? What does "tricky" mean -- "difficult
>>> but I can cope with it" or "causes an error" (if so, which?) or
>>> something else?
>>>
>> I mean that it's impossible to use the same data files or SQL database
>> file without running a new session (which means closing my GUI). So
>> not 'tricky', but 'impossible'.
> 
> There is a bug in your application. After a call to sqlite3_interrupt, 
> you leave behind an open transaction. Naturally, any other connections 
> are then blocked by that transaction.
> 
> The only thing sqlite3_interrupt does is force a failure of sqlite3_step 
> call currently in progress, if any. It doesn't finalize any statements, 
> commit or roll back any transactions, or close any connections. It's 
> your responsibility to do that, if that's your intent.

However the documentation (http://www.sqlite.org/c3ref/interrupt.html) 
does say:

"""An SQL operation that is interrupted will return SQLITE_INTERRUPT. If 
the interrupted SQL operation is an INSERT, UPDATE, or DELETE that is 
inside an explicit transaction, then the entire transaction will be 
rolled back automatically."""

In any case, as you say the interrupted thread needs to do some recovery 
work that should not require back-door nobbling of file handles.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with sqlite3_interrupt on Windows

[John Machin]

On 10/06/2009 4:40 AM, Jeremy Smith wrote:
> When I run sqlite3_interrupt, it doesn't close existing file handles, 
> making further searches tricky.

Which handles? How do you know? What does "tricky" mean -- "difficult 
but I can cope with it" or "causes an error" (if so, which?) or 
something else?

> So I wrote code which clears all normal 
> file handles (fopen in shell.c), but...

Have you tried calling sqlite3_finalize() on each prepared statement and 
sqlite3_close() on each open connection?


> How do I close the database file too? It's not opened using fopen, but 
> with CreateFileA (in winOpen in os_win.c) so I don't know what to call 
> after calling sqlite3_interrupt. CloseHandle(global_h) just throws up an 
> error. And I can't do further searches until the database file is free.

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

Re: [sqlite] SQLite Exception: SQLite BUSY

[John Machin]

On 8/06/2009 8:22 PM, Manasi Save wrote:
> Hi All,
> 
> I have one query regarding SQlite Busy error.
> 
> Can anyone explain me in what cases this error occurs?

Yes. You should be able to explain it to yourself after reading relevant 
parts of:

http://www.sqlite.org/faq.html
http://www.sqlite.org/lockingv3.html
http://www.sqlite.org/lang_transaction.html


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

Re: [sqlite] Importing text file of numbers into INTEGER PRIMARY KEY aka rowid

[John Machin]

On 7/06/2009 11:38 AM, P Kishor wrote:
> On Sat, Jun 6, 2009 at 8:28 PM, Kelly Jones 
> wrote:
>> On 6/6/09, P Kishor  wrote:
>>> On Sat, Jun 6, 2009 at 1:43 PM, Kelly Jones
>>> wrote:
 I have a text file onenum.txt with just "1234\n" in it, and a db w/
 this schema:

 sqlite> .schema
 CREATE TABLE test (foo INTEGER PRIMARY KEY);

 When I import, it fails as follows:

 sqlite> .import onenum.txt test
 Error: datatype mismatch

 Is sqlite3 treating "1234" as a string or something? Short of doing
 "INSERT INTO test VALUES (1234);", how do I import numbers into
 sqlite3's rowid column? [1]
>>> Remove the "\n"
>> Er, by "\n", I just meant that the file ended in a newline. I didn't
>> literally type a backslash and an 'n' into the file.
> 
> 
> Yes, I understand what you meant. If there is a newline, sqlite tries
> to import it, and that doesn't fit into the INTEGER PRIMARY KEY
> categorization, hence the datatype mismatch error.

If there is a newline?? There should be a newline at the end of each 
line of the file; with luck the reader will not complain if the final 
newline is missing.

Expected behaviour for various raw file contents:

1234 => one row, ok (with luck)
1234\n => one row, ok
1234\n\n => first row ok, 2nd is empty, expect error message
1234\n5678 => two rows, ok (with luck)
1234\n5678\n => two rows, ok

> So, the following
> fails (I am typing bogus lines to indicate the newline
> --
> 1234
> 
> --
> 
> while the following imports just fine
> --
> 1234
> --

Simply: avoid having empty or blank lines in the .import file, 
especially at the end, where they're not obvious.

IMHO that error message is carrying "Lite" a little too far; some prefix of:
 data mismatch in line 2, column 1 (foo): expected integer, found ''
might save some wear and tear on the help desk :-)

Cheers,
John


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

Re: [sqlite] Importing text file of numbers into INTEGER PRIMARY KEY aka rowid

[John Machin]

On 7/06/2009 11:28 AM, Kelly Jones wrote:
> On 6/6/09, P Kishor  wrote:
>> On Sat, Jun 6, 2009 at 1:43 PM, Kelly Jones
>> wrote:
>>> I have a text file onenum.txt with just "1234\n" in it, and a db w/
>>> this schema:
>>>
>>> sqlite> .schema
>>> CREATE TABLE test (foo INTEGER PRIMARY KEY);
>>>
>>> When I import, it fails as follows:
>>>
>>> sqlite> .import onenum.txt test
>>> Error: datatype mismatch
>>>
>>> Is sqlite3 treating "1234" as a string or something? Short of doing
>>> "INSERT INTO test VALUES (1234);", how do I import numbers into
>>> sqlite3's rowid column? [1]
>> Remove the "\n"
> 
> Er, by "\n", I just meant that the file ended in a newline. I didn't
> literally type a backslash and an 'n' into the file.
> 
> Note there's only line in the entire file, so there are no duplicates
> or misformatted entries.

I can't reproduce your problem. If you can reproduce it, show us a 
verbatim transcript of your session, preferably obtained by copy/paste 
and including an *unambiguous* dump of the file contents, together with 
platform details.

E.g.
[Windows XP SP2, SQLite version 3.6.14]

| C:\junk>copy con onenum.txt
| 1234
| ^Z
| 1 file(s) copied.
|
| C:\junk>type onenum.txt
| 1234
|
| C:\junk>python -c "print repr(open('onenum.txt', 'rb').read())"
| '1234\r\n'
|
| C:\junk>sqlite3 test.db
| SQLite version 3.6.14
| Enter ".help" for instructions
| Enter SQL statements terminated with a ";"
| sqlite> CREATE TABLE test (foo INTEGER PRIMARY KEY);
| sqlite> .schema
| CREATE TABLE test (foo INTEGER PRIMARY KEY);
| sqlite> .import onenum.txt test
| sqlite> select rowid, foo, typeof(foo) from test;
| 1234|1234|integer
| sqlite> ^Z
|
| C:\junk>

Did I hear a mutter about the '\r' above?

| C:\junk>python -c "open('onenum2.txt', 'wb').write('1234\n')"
|
| C:\junk>python -c "print repr(open('onenum2.txt', 'rb').read())"
| '1234\n'
|
| C:\junk>sqlite3 test2.db
| SQLite version 3.6.14
| Enter ".help" for instructions
| Enter SQL statements terminated with a ";"
| sqlite> CREATE TABLE test (foo INTEGER PRIMARY KEY);
| sqlite> .schema
| CREATE TABLE test (foo INTEGER PRIMARY KEY);
| sqlite> .import onenum2.txt test
| sqlite> select rowid, foo, typeof(foo) from test;
| 1234|1234|integer
| sqlite> ^Z
|
| C:\junk>

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

Re: [sqlite] Getting last inserted rowid?

[John Machin]

On 6/06/2009 8:19 AM, Nikolaus Rath wrote:
> John Machin <sjmac...@lexicon.net> writes:
>>> Now I'm confused. I want to know if it will be sufficient to wrap my
>>> last_insert_rowid() call between BEGIN .. and END in order to make it
>>> return the rowid that was last inserted by the same thread even if
>>> multiple threads are using the same connection (but different cursors).
>>>
>>> As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
>>> saying that it's not sufficient, I need to use additional mechanism.
>> As Igor pointed out, if you have multiple threads using the same 
>> connection, you ALREADY need mutexes or whatever to maintain atomicity. 
>> If you don't have that, yes you need to "use additional mechanism" BUT 
>> this constitutes an EXISTING bug in your code. Perhaps Nuno should have 
>> added a rider like "(presuming your existing code is not stuffed)".
> 
> Are you saying that I need to use mutexes or whatever in the following
> program?
> 
> def thread1():
> cur = conn.cursor()
> for i in range(500):
> cur.execute("INSERT INTO tab1 (no) VALUES(?)", i)
> 
> def thread2():
> cur = conn.cursor()
> for i in range(500):
> cur.execute("INSERT INTO tab2 (no) VALUES(?)", i)
> 
> threading.Thread(target=thread1).start()
> threading.Thread(target=thread2).start()

Somebody needs to use mutexes somewhere. You have obscured the question 
by introducing two unknowns: (1) Which Python wrapper are you using 
(sqlite3 module or the apsw module)? (2) What does it do under the 
covers? Try asking the relevant guru for whatever you are using.

>>> Where am I wrong?
>> In wasting time on semantic confusion instead of implementing it and 
>> testing the bejaysus out of it.
> 
> When you are working with a multithreaded program, you can't even hope
> to test a fraction of the possible state trajectories. Finding the
> proper implementation by trial & error is therefore even more hopeless
> than in a single threaded program.

If you can't test it, then how will you know whether *any* 
implementation is "proper", let alone *the* "proper" one?

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

Re: [sqlite] Db design question (so. like a tree)

[John Machin]

On 5/06/2009 5:27 PM, Francis GAYREL wrote:
> To build a consistent oriented tree we need to associate to the nodes a 
> ranking property such as the birthdate (or any precedence criterion).
> Therefore the ancestor of  someone  is to be selected among  older ones.

"Ancestor" is a *derived* relationship, not something you'd wish to 
store in the database, and is quite irrelevant at data-entry time. Don't 
you mean "parent"?

Let me get this straight: the user is entering the details of animal X 
who was born yesterday and has to input somehow the identity of the 
mother and of the father.

> To make the ancestor allocation more easy  the ancestor's list may be 
> filtered on birthdate credibility.

So your plot is, (e.g. for the father) to show a list of all male 
animals who are in some credible-parenthood age range on (say) a 
drop-down list, and the user selects one, hopefully not at random. Is 
that right?

I would imagine in a planned targeted organised animal breeding program 
that the mother-to-be and father-to-be are recorded at the time of 
impregnation, and the identities are established from ear-tags, embedded 
chips, photographs, etc and the credible-parenthood test is applied then 
[note: test, NOT input selection method] and all of the above is 
confirmed at birth.

> The ranking property eliminates the circular link concern.

Indeed, but you have to use it properly to eliminate other data 
integrity concerns :-)

HTH,

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

Re: [sqlite] Concat two fields for LIKE query?

[John Machin]

On 5/06/2009 7:46 AM, Andrés G. Aragoneses wrote:
> Igor Tandetnik wrote:
>> "Andrés G. Aragoneses" 
>> wrote:
>>> My query, which I want to make it return the first row:
>>>
>>> SELECT * FROM SomeTable WHERE Path+FileName LIKE '%user/File%'
>> SELECT * FROM SomeTable WHERE Path || FileName LIKE '%user/File%';
>>
>> In SQL, string concatenation operator is ||, not +.
>>
> 
> Cool! And can I do this as well?:
> 
> SELECT Path||Filename FROM SomeTable WHERE Path || FileName LIKE
> '%user/File%'

Well, you should be able to do that. If you can type it into an e-mail 
client, you can type it into an SQL processor. Try it, and let us know 
what the result is.

Consider becoming familiar with the fantastic documentation:

http://www.sqlite.org/syntaxdiagrams.html#result-column

That "expr" in the box means "expression" which gives you a lot of scope ...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Db design question (so. like a tree)

[John Machin]

On 5/06/2009 12:59 AM, Griggs, Donald wrote:
> Regarding:
>I could start the id initially with 10 to allocate 
> 
> That WOULD allow for a bunch of bull.;-) 

Don't horse about with IDs with attached meaning; it's a cow of a 
concept whose outworking could well be catastrophic and dog you for the 
rest of your life.

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

Re: [sqlite] Getting last inserted rowid?

[John Machin]

On 4/06/2009 12:57 PM, Nikolaus Rath wrote:
> John Machin <sjmac...@lexicon.net> writes:
>> On 4/06/2009 8:22 AM, Nikolaus Rath wrote:
>>> Nuno Lucas <ntlu...@gmail.com> writes:
>>>> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath <nikol...@rath.org> wrote:
>>>>> Nuno Lucas <ntlu...@gmail.com> writes:
>>>>>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath <nikol...@rath.org> wrote:
>>>>>>> Hello,
>>>>>>>
>>>>>>> How can I determine the rowid of the last insert if I am accessing the
>>>>>>> db from different threads? If I understand correctly,
>>>>>>> last_insert_rowid() won't work reliably in this case.
>>>>>> It should work if you do:
>>>>>>
>>>>>> BEGIN
>>>>>> INSERT ...
>>>>>> last_insert_rowid()
>>>>>> END
>>>>> That would be very nice. But does "it should work" mean that you know
>>>>> that it works (and it is documented and guaranteed)? The above sounds a
>>>>> bit uncertain to me...
>> Guaranteed? You're expecting a lot. Most software that you pay large 
>> sums of money for guarantee not much more than that the version numbers 
>> will be monotonically increasing.
> 
> I trust you know what I mean. Guaranteed in the sense that the
> developers try to make sqlite behave in this way and in contrast to "it
> just happens to work right now, but it might change anytime without
> warning".

"guarantee X" means "try to ensure X"??
You trust that I know that you mean that

> 
>>>> It just means I'm too old to assume anything is certain. The Universe
>>>> is always conspiring against you ;-)
>>>>
>>>> What I mean is that if it doesn't work, then you found a bug, most
>>>> probably in your own code.
>>> Well, now you are in direct contradiction to Igor who says that it does
>>> not work:
>>>
>>> ,
>>> | >> If all threads share the same connection, it is your responsibility
>>> | >> to make "insert then retrieve last rowid" an atomic operation, using
>>> | >> thread synchronization mechanism of your choice. Just as with any
>>> | >> access to shared data.
>>> | >
>>> | > Is BEGIN ... COMMIT sufficient for that?
>>> | 
>>> | No. Transaction is also maintained per connection. Starting a 
>>> | transaction would prevent other connections from making concurrent 
>>> | changes, but wouldn't block other threads using the same connection.
>>> `
>>>
>>>
>>> Any third opinions or references to documentation? 
>> 
>> I don't see Igor saying it doesn't work. He says that it is not 
>> sufficient; *YOU* must maintain atomicity, using mutexes or whatever -- 
>> just as you need to "with any access to shared data" if you have 
>> multiple threads per connection.
>>
>> "if it doesn't work, then you found a bug, most probably in your own 
>> code" is not "in direct contradiction to Igor" ... I'd call it 
>> corroborative of Igor.
>> 
> 
> Now I'm confused. I want to know if it will be sufficient to wrap my
> last_insert_rowid() call between BEGIN .. and END in order to make it
> return the rowid that was last inserted by the same thread even if
> multiple threads are using the same connection (but different cursors).
> 
> As I understand Nuno, he is saying that this is sufficient. Igor OTOH is
> saying that it's not sufficient, I need to use additional mechanism.

As Igor pointed out, if you have multiple threads using the same 
connection, you ALREADY need mutexes or whatever to maintain atomicity. 
If you don't have that, yes you need to "use additional mechanism" BUT 
this constitutes an EXISTING bug in your code. Perhaps Nuno should have 
added a rider like "(presuming your existing code is not stuffed)".

> Where am I wrong?

In wasting time on semantic confusion instead of implementing it and 
testing the bejaysus out of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[John Machin]

On 4/06/2009 8:22 AM, Nikolaus Rath wrote:
> Nuno Lucas  writes:
>> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath  wrote:
>>> Nuno Lucas  writes:
 On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
> Hello,
>
> How can I determine the rowid of the last insert if I am accessing the
> db from different threads? If I understand correctly,
> last_insert_rowid() won't work reliably in this case.
 It should work if you do:

 BEGIN
 INSERT ...
 last_insert_rowid()
 END
>>> That would be very nice. But does "it should work" mean that you know
>>> that it works (and it is documented and guaranteed)? The above sounds a
>>> bit uncertain to me...

Guaranteed? You're expecting a lot. Most software that you pay large 
sums of money for guarantee not much more than that the version numbers 
will be monotonically increasing.

>> It just means I'm too old to assume anything is certain. The Universe
>> is always conspiring against you ;-)
>>
>> What I mean is that if it doesn't work, then you found a bug, most
>> probably in your own code.
> 
> Well, now you are in direct contradiction to Igor who says that it does
> not work:
> 
> ,
> | >> If all threads share the same connection, it is your responsibility
> | >> to make "insert then retrieve last rowid" an atomic operation, using
> | >> thread synchronization mechanism of your choice. Just as with any
> | >> access to shared data.
> | >
> | > Is BEGIN ... COMMIT sufficient for that?
> | 
> | No. Transaction is also maintained per connection. Starting a 
> | transaction would prevent other connections from making concurrent 
> | changes, but wouldn't block other threads using the same connection.
> `
> 
> 
> Any third opinions or references to documentation? 


I don't see Igor saying it doesn't work. He says that it is not 
sufficient; *YOU* must maintain atomicity, using mutexes or whatever -- 
just as you need to "with any access to shared data" if you have 
multiple threads per connection.

"if it doesn't work, then you found a bug, most probably in your own 
code" is not "in direct contradiction to Igor" ... I'd call it 
corroborative of Igor.



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

Re: [sqlite] how can we solve IF EXIST in SQLite

[John Machin]

On 3/06/2009 5:15 PM, robinsmathew wrote:
> its showing an error near "if": syntax error

"it", my crystal ball tells me, is an SQL processor, behaving much as 
expected when fed what looks like an "if" statement in some other 
language ...

> Kees Nuyt wrote:

>> Pseudocode:

google("pseudocode")

>> BEGIN;
>> UPDATE stock_tab SET . WHERE stock_id = 1;
>> if sqlite_error()
>>  INSERT INTO stock_tab SET (...) VALUES (...);
>> endif
>> COMMIT;

Try this exploded version, written using functions in some arbitrary 
wrapper language, which you need to translate into whatever language you 
are using, supplying missing arguments like a connection and maybe a 
cursor, and adding error-checking where appropriate:

exec_sql("BEGIN")
exec_sql("UPDATE stock_tab SET . WHERE stock_id = 1")
if an_error_happened():
exec_sql("INSERT INTO stock_tab SET (...) VALUES (...)")
exec_sql("COMMIT")

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

Re: [sqlite] Populating dyadic dataset

[John Machin]

On 2/06/2009 10:17 AM, Vincent Arel wrote:

> Your python-like example is also quite helpful.

It is not "python-like". Apart from the "..." in the initial data 
"vectors", it is executable Python code.

> As I understand it, you
> basically implement Igor's suggestion of running loops on the vectors.

More or less :-) BTW, I miscoded; see below.

> I
> should be able to do that quite easily. Most importantly though, it appears
> that I need to revise my understanding of the division of labour between
> sqlite and R.
> 
> Thanks a lot for your help, and have a great week!

You're welcome ... you too.

[big snip]

>>
>> var1 = ["ALB", "CAN", "DZA", ...]
>> var2 = ["ALB", "CAN", "DZA", ...]
>> var3 = ["1961", "1962", "1963",...]
>> # get a list of unique country codes, in sorted order
>> countries = list(set(var1 + var2))
>> countries.sort()
>> # convert years to integer, find range
>> var3int = [int(y) for y in var3]
>> firsty = min(var3int)
>> lasty = max(var3int)
>> year_range = range(firsty, lasty + 1)
>> # do the business
>> id = 0

following works very hard to throw away half of your results :-(

>> ncountries = len(countries)
>> for i in range(ncountries - 1):
>> for j in range(i + 1, ncountries):
>>assert countries[i] != countries[j]
>> id += 1
>> for year in year_range:
>> print id, countries[i], countries[j], year

should be:

for c1 in countries:
for c2 in countries:
   if c2 == c1: continue
   id += 1
   for year in year_range:
  print id, c1, c2, year

and sorting the countries is not essential, but maybe helpful when 
visually checking the output.

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

Re: [sqlite] Populating dyadic dataset

[John Machin]

On 2/06/2009 8:07 AM, Vincent Arel wrote:
> Hi everyone,
> 
> I'm very, very new to SQLite, and would appreciate any help I can get.

Unless I'm very very confused, this has very little to do with SQL at 
all (let alone SQLite) apart from using an INSERT statement to dispose 
of the final product.

You might be better off asking on a forum related to whatever language 
you are using.

Your problem description is a tad ambiguous, so let's see if we can get 
that improved before we send you off elsewhere.

Let's guess that you ultimately want to record two-way trade statistics 
for all possible1 country pairs for all possible2 years, for some 
definition of possible1 and possible2.

> 
> I have 3 long vectors that look like this:
> {"ALB","CAN", "DZA",...}
> {"ALB","CAN", "DZA",...}
> {"1961","1962", "1963",...}
> 
> And I want to create a table that looks like this:
> 
> IDVar1Var2Var3

Using meaningful names is strongly suggested ... e.g. from_country, 
to_country, trade_year


> 1ALBCAN1961
> 1ALBCAN1962
> 1ALBCAN1963
> 2ALBDZA1961
> 2ALBDZA1962
> 2ALBDZA1963
> 3CANALB1961
> 3CANALB1962
> 3CANALB1963
> 4CANDZA1961
> 4CANDZA1962
> 4CANDZA1963
> 5DZAALB1961
> 5DZAALB1962
> 5DZAALB1963
> 6DZACAN1961
> 6DZACAN1962
> 6DZACAN1963
> 
> In short, I need to include every possible pair of Var1/Var2 values (where
> Var1/Var2 != Var2/Var1.

Huh? Var1/Var2 == Var2/Var1 iff var1 == var2 ... if you mean "where var1 
!= var2" why not say so? If you mean something else (possible since you 
later mention /also/ dropping rows where var1 == var2) then please explain.

> I want to keep permutations.)

What does that mean?

> For each of these
> pairs, I need to create separate rows for each different value of Var3.

What if there are gaps in your third vector? Should they be filled in?

> I also need to drop rows where Var1 == Var2.

See above.

 > Finally, I would like to generate
> a unique ID number for each Var1/Var2 pair.

Aha! A vague connection with SQL :-) Why bother? You require the (var1, 
var2) tuple to be unique anyway ... what's the point of having another 
(redundant) column?

> Of course, I do not expect a ready-made answer. However, if some of you
> could tell me where I should start looking for a solution to my problem, or
> if you have any conceptual programming hints that could help me produce the
> needed table, I would be extremely grateful.

Expressing the "hints" in an expressive language like Python, and 
assuming the widest definitions of "possible", but basing it on your data:

var1 = ["ALB", "CAN", "DZA", ...]
var2 = ["ALB", "CAN", "DZA", ...]
var3 = ["1961", "1962", "1963",...]
# get a list of unique country codes, in sorted order
countries = list(set(var1 + var2))
countries.sort()
# convert years to integer, find range
var3int = [int(y) for y in var3]
firsty = min(var3int)
lasty = max(var3int)
year_range = range(firsty, lasty + 1)
# do the business
id = 0
ncountries = len(countries)
for i in range(ncountries - 1):
 for j in range(i + 1, ncountries):
assert countries[i] != countries[j]
 id += 1
 for year in year_range:
 print id, countries[i], countries[j], year

BTW, this is all keys ... where's the data?

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

Re: [sqlite] journey mode TRUNCATE is to append ? not overwrite?

[John Machin]

On 1/06/2009 5:29 PM, pierr wrote:
> Hi all,
> 
> Section 7.9 of http://www.sqlite.org/atomiccommit.html mentioned:
> 
> "On embedded systems with synchronous filesystems, TRUNCATE results in
> slower behavior than PERSIST. The commit operation is the same speed. But
> subsequent transactions are slower following a TRUNCATE because it is faster
> to overwrite existing content than to append to the end of a file. New
> journal file entries will always be appended following a TRUNCATE but will
> usually overwrite with PERSIST."
> 
> why " New journal file entries will always be appended following a TRUNCATE
> but will usually overwrite with PERSIST"?  I think if we trancate the
> journey file to _zero_ , and the following write to that file is going to
> overwrite the old data. Why append?

Truncate means to set the end of the file to a smaller value than 
before; pages so freed are then owned by the filesystem. Writing more 
data then requires appending which requires pages to be obtained from 
the filesystem, which is not a zero-cost operation. It may write over 
old data (you get your old pages back), or it may not (someone else has 
grabbed your freed pages in the meantime).

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

Re: [sqlite] Deleting database content : SQLite3 API

[John Machin]

On 30/05/2009 10:20 PM, souvik.da...@wipro.com wrote:

[top-posting unscrambled]

[first message]
 >> As a result , after finding that the
 >> database already exits at the system startup, I cannot just drop the
 >> tables. ( As the table which are present in the existing data base is
 >> not known. )

They are easily knowable. Otherwise how could the database work? Use 
"select * from sqlite_master;"

[2nd message, in response to suggestion to delete the file and start 
with a new one]
> Yes , I understand that. Infact I was doing that through a script
 > during system startup. I wanted to know whether SQLite provides
 > any API to do the same.

No, why should it? "Lite" means among other things don't reproduce what 
is readily available elsewhere. Consider using the facilities provided 
by your C++ runtime libraries.

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

Re: [sqlite] Corruption of incremental_vacuum databases

[John Machin]

On 17/04/2009 1:39 AM, Filip Navara wrote:
> Hello,
> 
> I have expected at least some reply. Oh well, new the corruption has happened
> again (on another different machine) and I have saved the database files. One
> of the corrupted files is available at 
> http://www.emclient.com/temp/folders.zip.

U ... your first message [scroll down to read] is talking about 
*incremental* vacuuming; however the database file that you made 
available has FULL (not incremental) auto-vacumming set.

dos-prompt>sqlite3 folders.dat
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma auto_vacuum;
1 <<<=== 1 means full, incremental is 2
sqlite> pragma integrity_check;
*** in database main ***
Page 11 is never used
Page 13 is never used
Page 15 is never used
Page 20 is never used
Page 21 is never used
Page 22 is never used
Page 23 is never used
Page 24 is never used
Page 25 is never used
sqlite>

Six orphan pages at the end of the file plus another 3 orphans suggests 
that an auto-vacuum (full or incremental) may have been interrupted -- 
or perhaps later given that you are? were? using synchronous=off.

Did you get any resolution on this?

Cheers,
John

> I'd be glad for any help or at least confirmation that it could be
> related to the
> issues in the tickets listed below.
> 
> Thanks,
> Filip Navara
> 
> On Tue, Mar 31, 2009 at 11:05 AM, Filip Navara  wrote:
>> Hello,
>>
>> after seeing the recent change #6413 and ticket #3761 I finally
>> decided to write about a corruption issue we have.
>>
>> This is the environment of our application:
>> - We are using SQLite 3.6.3 (with SQLite.NET 1.0.60.0).
>> - Several database files. Each file is opened in it's own connection
>> and never shared across them.
>> - Some of these connections have another database attached to it
>> (containing mostly BLOB data).
>> - In all cases the connections are opened on program start and closed
>> on program shutdown.
>> - There's a low-priority thread that executes "pragma
>> incremental_vacuum" when the application is idle and there is enough
>> free pages. Code of the thread is listed below.
>> - "journal_mode=persist" is used on all databases in all connections
>> (to workaround a bug in the journal deletion logic on Windows, search
>> for "TortoiseSVN" in the mailing list archive for details)
>> - "synchronous=off" is used on all databases in all connections. This
>> setting is likely to change in future, but in no case of the
>> corruption a system crash was involved.
>>
>> Since we started using the incremental_vacuum mode we were getting
>> database corruption errors pretty often (sometimes as often as once a
>> day in 3 people). Most, if not all, of these corruptions happened
>> following a ROLLBACK (caused by constraint violation). "pragma
>> integrity_check;" on the already corrupted databases usually reported
>> few "Page XXX is never used" error.
>>
>> Unfortunately I don't have any of the corrupted databases at hand and
>> I have no deterministic way to create them. My question is if these
>> could be related to the just fixed problem (in ticket 3761) or if it
>> could be another issue?
>>
>> Best regards,
>> Filip Navara
>>
>> 
>>
>> WaitHandle[] handles = new WaitHandle[] { this.stopEvent, this.wakeupEvent };
>> System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
>> int timeout = -1;
>> int pagesPerIteration = 32;
>>
>> // Wait for thread shutdown and wakeup event. The shutdown event
>> // is used to stop the thread on application exit. The wakeup event is
>> // fired on startup if there are free pages in the database or if a DELETE
>> // statement was executed.
>> while (WaitHandle.WaitAny(handles, timeout, false) != 0)
>> {
>>long totalFreePages = 0, freePages;
>>lock (this.repositories)
>>{
>>stopWatch.Reset();
>>stopWatch.Start();
>>foreach (IRepositoryBase repository in this.repositories)
>>{
>>   // wrapper around "pragma freelist_count;"
>>freePages = repository.GetFreePageCount();
>>totalFreePages += freePages;
>>if (freePages > 0)
>>   // wrapper around "pragma 
>> incremental_vacuum(x)"
>>repository.Compact(pagesPerIteration);
>>}
>>stopWatch.Stop();
>>}
>>
>>// We start by freeing 32 pages per one iteration of the loop for
>>   // each database. After each iteration the number is recalculated
>>   // based on the time spent on the operation and then it's
>>   // truncated to the <24;4096> range.
>>pagesPerIteration = Math.Min(Math.Max(24, (int)(100.0 *
>> pagesPerIteration / stopWatch.ElapsedMilliseconds)), 4096);
>>
>>// If there are still free pages in the databases then schedule the
>>   // 

Re: [sqlite] 2 columns as primary key?

[John Machin]

On 30/05/2009 12:43 PM, Andrés G. Aragoneses wrote:
> I just tried to create a primary key with 2 columns and got this error:
> 
> "sqlite error" "table X has more than one primary key"
> 
> 
> Doesn't SQLite support this?? :o

It does support multi-column primary keys. It's a bit hard to tell at 
this distance what your problem is. Unfortunately, as the text of your 
CREATE TABLE statement is presumably a state secret, we'll have to play 
guessing games:

dos-prompt>sqlite3
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

Example of supporting multi-column primary keys:

sqlite> create table employment (employer_id text, employee_id text, 
start_date datetime, primary key (employer_id, employee_id));

Example of getting your error message:

sqlite> create table employment2 (employer_id text primary key, 
employee_id text primary key, start_date datetime);
SQL error: table "employment2" has more than one primary key
sqlite>

Are we getting warm?

Suggested reading:

http://www.firstsql.com/tutor6.htm#constraint
http://www.sqlite.org/syntaxdiagrams.html#table-constraint
http://www.catb.org/~esr/faqs/smart-questions.html

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

Re: [sqlite] corrupt database recovery

[John Machin]

On 29/05/2009 10:18 AM, John Machin wrote:
> On 29/05/2009 9:34 AM, Gene Allen wrote:
>> Yeah.  
>>
>> Since my code works in blocks, read/compress/encrypt/write, loop.  Almost
>> all the real data was being written to the compressed file, however any
>> finalization and flushing of the stream wasn't occurring (since the encrypt
>> was failing)
> 
> and the encrypt failure wasn't logged?
> 
>  > so the last bit of any SQLite database wouldn't be written.
> 
> If so, pragma integrity_check should report that some of the pages 
> actually written contain pointers to pages that are past the end of the 
> file, shouldn't it?

Your output from the integrity_check shows complaints about invalid page 
  numbers in the range 462 to 773. At the default page size of 1024, 
those page numbers span (773-462+1)*1024 = 319488 bytes so you are 
missing more than a 262144-byte chunk [unless your page size is 512!].

This doesn't seem to gel with the combination of "almost all the real 
data was being written" and the hypothesis that the database was 
corrupted merely by truncation.

What are the page size, the expected size of the database, and the 
actual (truncated) size of the database?

What evidence do you have that the feof problem actually happened in 
this case?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] corrupt database recovery

[John Machin]

On 29/05/2009 9:34 AM, Gene Allen wrote:
> Yeah.  
> 
> Since my code works in blocks, read/compress/encrypt/write, loop.  Almost
> all the real data was being written to the compressed file, however any
> finalization and flushing of the stream wasn't occurring (since the encrypt
> was failing)

and the encrypt failure wasn't logged?

 > so the last bit of any SQLite database wouldn't be written.

If so, pragma integrity_check should report that some of the pages 
actually written contain pointers to pages that are past the end of the 
file, shouldn't it?

[snip]

>> Well...a more structured test exposed the problem and it was this:
>>
>> The feof() does return true until you attempt to read PAST the end of a
>> file.

If feof doesn't continue to return true, it is broken.

> So the code worked great until the file's length was a multiple of
> the
>> buffer size (in my case 262,144 bytes).  As you can imagine that doesn't
>> happen too often in the real world.
>>
>> Since I assumed that a feof would return true where there wasn't any more
>> data in the file, I would start another pass at reading a chunk of data
>> (which wouldn't find anything) and run thru the compression/encryption
> code.
>> The compression code worked handled it correctly, but the encryption
>> required that a DWORD boundary (blowfish) and since 0 is on such a
> boundary
>> but at the wrong end...it would fail.  

Silently? Unlogged?

In any case, I would have thought using feof() was not needed ...
long time since I've written C in earnest, but isn't something like this 
the standard idiom:

#define BUFSIZ 262144
buff char[BUFSIZ];
size_t nbytes;
FILE *f;
f = fopen("filename", "rb");
while ((nbytes = fread(buff, 1, BUFSIZ, f)) {
do_something(buff, nbytes);
}

??

HTH,

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

Re: [sqlite] add column creating null columns even with default?

[John Machin]

On 29/05/2009 2:53 AM, Simon Slavin wrote:
> On 28 May 2009, at 9:00am, Damien Elmes wrote:
> 
>> alter table cardModels add column allowEmptyAnswer boolean not null  
>> default 1
> 
>> sqlite> update cardModels set allowEmptyAnswer = 0;
> 
> You're obviously used to other implementations of SQL.  'boolean'  
> isn't a legit type name:
> 
> http://www.sqlite.org/datatype3.html

AFAICT that page says nothing about what is a "legit type name". This 
one does: http://www.sqlite.org/syntaxdiagrams.html#type-name

Here are some examples of legitimate type-names:

dos-prompt>sqlite3
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo (c1 jabberwocky, c2 very big inteher whoops 
typo, c3 "3.14159", c4 very variable character (-123456, +666.987), c5 
boolean);
sqlite> pragma table_info(foo);
0|c1|jabberwocky|0||0
1|c2|very big inteher whoops typo|0||0
2|c3|3.14159|0||0
3|c4|very variable character (-123456, +666.987)|0||0
4|c5|boolean|0||0
sqlite>

"boolean" as a type-name will cause the column to have NUMERIC affinity 
according to the rules on the page you quoted, and the OP seems to be 
being careful to restrain values to 0, 1, and NULL, so this all looks 
rather sensible to me.

> 
> This may or may not be the cause of the problem you report, but fix it  
> first.

How would you propose to fix it?

Cheers,

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

Re: [sqlite] SQLite3 question

[John Machin]

On 28/05/2009 10:53 PM, Igor Tandetnik wrote:
> "Oza, Hiral_Dineshbhai"
>  wrote in
> message
> news:24ea477c0c5854409ba742169a5d71c406bd4...@mailhyd2.hyd.deshaw.com
>> Can you please let me know meaning of 'Cell' in Btrees used in
>> sqlite3.
> 
> Can you point to the text where you saw sqlite3, B-trees and the word 
> "Cell" mentioned together?

Possibly here: http://www.sqlite.org/fileformat.html


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

Re: [sqlite] Some index questions

[John Machin]

On 28/05/2009 12:24 AM, Dan wrote:
> 
> If a single column index is like the index found in textbooks,
> a compound index with two fields is like the phone book. Sorted first by
> surname, then by first name. The "rowid", if you like, is the phone  
> number.
> 
> So, it's easy to find the set of phone numbers for everybody with the
> surname "Jones". It's easy to find the set of phone numbers for people
> called "Barry Jones". Quite difficult to find all the people called  
> "Barry"
> though.

And even more difficult to find all the people nicknamed "Bazzer" :-)


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

Re: [sqlite] Fast data duplication

[John Machin]

On 27/05/2009 9:47 PM, Igor Tandetnik wrote:
> "Vasil Boshnyakov" 
> wrote in message news:000c01c9de8b$16510a40$42f31e...@bg
>> The short description is: we need to copy many records of a table in
>> the same table but changing the "Name" value. So we have added a new
>> function which process the names:
>>
>> Insert into users ItemID, Name
>> Select ItemID, newName(Name) from users where itemActive = 1;
>>
>> That works great but we need one more step: how to much the pairs
>> "item comes from the Select <-> new item result of the Insert". We
>> need to track the copy history: itemID -> newItemID.
> 
> What is this newItemID you speak of? As far as I can tell from your 
> (syntactically invalid) statement, new records are inserted with the 
> same ItemId as the old ones.

Vasil, please tell us the schema, otherwise we can't help you. Some 
explanation of what you are really trying to do might also aid us. There 
are other concerns beside Igor's, like "itemActive" would seem from its 
name to belong to an "items" table, not to a "users" table.


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

[sqlite] suggested changes to file format document

[John Machin]

1. In the following, s/less than/less than or equal to/

"""
2.3.3.4 Index B-Tree Cell Format
[snip 2 paragraphs]

If the record is small enough, it is stored verbatim in the cell. A 
record is deemed to be small enough to be completely stored in the cell 
if it consists of less than:
 max-local := (usable-size - 12) * max-embedded-fraction / 255 - 23
bytes.
"""

2. The formula in the following is incorrect.

"""
[H31190] When a table B-Tree cell is stored partially in an overflow 
page chain, the prefix stored on the B-Tree leaf page consists of the 
two variable length integer fields, followed by the first N bytes of the 
database record, where N is determined by the following algorithm:
 min-local := (usable-size - 12) * 255 / 32 - 23
"""

It should be:
 min-local := (usable-size - 12) * 32 / 255 - 23

3. In description of first 100 bytes of file: """The number of unused 
bytes on each page (single byte field, byte offset 20), is always set to 
0x01.""" ... should be 0x00.

4. In section 2.3.2 Database Record Format, in the table describing 
type/size codes:

"""Even values greater than 12 are used to signify a blob of data (type 
SQLITE_BLOB) (n-12)/2 bytes in length, where n is the integer value 
stored in the record header."""

s/greater than/greater than or equal to/

5. In section 2.3.1 Variable Length Integer Format, in the examples
"""
Decimal HexadecimalVariable Length Integer
[snip]
-78056  0xFFFECD56 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFD 0xCD 0x56
"""
s/78056/78506/

6. In description of sqlite_master:

"""[H30300] If the associated database table is a virtual table, the 
fourth field of the schema table record shall contain an SQL NULL value."""

Looks like an integer zero to me:

DOS-prompt>sqlite3
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create virtual table foo using fts3(yadda yadda);
sqlite> select typeof(rootpage),* from sqlite_master where name = 'foo';
integer|table|foo|foo|0|CREATE VIRTUAL TABLE foo using fts3(yadda yadda)

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

Re: [sqlite] database file header: "schema layer file format" anomaly

[John Machin]

On 27/05/2009 3:03 AM, D. Richard Hipp wrote:
> John - what were you doing when you discovered this?
> 
> On May 26, 2009, at 10:57 AM, John Machin wrote:
> 
>> According to the file format document
>> (http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte block
>> starting at byte offset 44 of a well-formed database file, the schema
>> layer file format, contains a big-endian integer value between 1 and  
>> 4,
>> inclusive."
>>
>> However it is possible to end up with this being zero, e.g. by  
>> dropping
>> all tables/etc and then doing a VACUUM:

Eyeballing the following output from my code:

 assert 1 <= self.schema_layer_file_format <= 4
AssertionError

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

Re: [sqlite] Minor errors in CREATE TABLE syntax diagrams

[John Machin]

On 27/05/2009 12:33 AM, Jim Wilcoxson top-posted:
> For my money, I'd prefer to have a smaller, faster parser that worked
> correctly on correct input at the expense of not catching all possible
> syntax errors on silly input.

Firstly, none of the examples that I gave are syntactically incorrect.
Secondly, a compiler that doesn't reject ill-formed syntax should not be
seen after first semester CS101 -- the very idea is a nonsense.
Thirdly, all I'm asking for is a few more lines to make the diagrams 
accord with what the SQL compiler is already doing.

>  There is a definite trade-off here, and
> I could see where a totally complete parser that caught every possible
> error in SQL grammer might be twice the size of the entire SQLite code
> base.
> 
> Of course, you don't want an SQL syntax typo to trash your database
> either, without warning.

Which is why you test your software ... so col1 is not supposed to 
permit NULLs so you need to test it whether you wrote the syntactically 
correct "col1 INTEGER NOTE NULL, ..." or the equally syntactically 
correct "col1 INTEGER, ..." -- both being practically wrong.


>  I'm assuming the SQLite developers have made
> reasonable decisions about which parsing errors are important, and
> which aren't.

I hope they don't have any /parsing/ errors at all.


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

Re: [sqlite] Select on foreign key NULL

[John Machin]

On 27/05/2009 1:09 AM, Leo Freitag wrote:
> Hallo,
> 
> I got some problems with a select on a foreign key with value null.
> I want to filter all male singers.
> 
> CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT, 
> 'fkvoice' INTEGER, 'sex' TEXT);
> INSERT INTO "tblsinger" VALUES(1,'Anna Netrebko',1, 'f');
> INSERT INTO "tblsinger" VALUES(2,'Hanna Schwarz',2, 'f');
> INSERT INTO "tblsinger" VALUES(3,'Luciano Pavarotti', 3, 'm');
> INSERT INTO "tblsinger" VALUES(4,'Robert Lloyd', 4, 'm');
> INSERT INTO "tblsinger" VALUES(5,'Robby Williams', null, 'm');
> 
> CREATE TABLE 'tblvoice' ('id' INTEGER PRIMARY KEY, 'voice' TEXT);
> INSERT INTO "tblvoice" VALUES(1,'sopran');
> INSERT INTO "tblvoice" VALUES(2,'alt');
> INSERT INTO "tblvoice" VALUES(3,'tenor');
> INSERT INTO "tblvoice" VALUES(4,'bass');
> 
> SELECT tblsinger.name, tblsinger.sex, tblvoice.voice FROM tblsinger, 
> tblvoice
> WHERE tblsinger.sex = 'm' AND tblsinger.fkvoice = tblvoice.id;
> 
> -- Result
> 
> Luciano Pavarotti | m | tenor
> Robert Lloyd  | m | bass
> 
> -- How do I have to modify the select statement to get the result below:
> 
> Luciano Pavarotti | m | tenor
> Robert Lloyd  | m | bass
> Robby Williams| m |

sqlite> select s.name, s.sex, v.voice from tblsinger s left outer join 
tblvoice v on s.fkvoice = v.id where s.sex = 'm';
Luciano Pavarotti|m|tenor
Robert Lloyd|m|bass
Robby Williams|m|

With "visible NULL":

sqlite> select s.name, s.sex, ifnull(v.voice, 'UNKNOWN') from tblsinger 
s left outer join tblvoice v on s.fkvoice = v.id where s.sex = 'm';
Luciano Pavarotti|m|tenor
Robert Lloyd|m|bass
Robby Williams|m|UNKNOWN

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

[sqlite] database file header: "schema layer file format" anomaly

[John Machin]

According to the file format document 
(http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte block 
starting at byte offset 44 of a well-formed database file, the schema 
layer file format, contains a big-endian integer value between 1 and 4, 
inclusive."

However it is possible to end up with this being zero, e.g. by dropping 
all tables/etc and then doing a VACUUM:

# Assume vacked.db doesn't exist
DOS-prompt>sqlite3 vacked.db
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo (x, y);
sqlite> insert into foo values(1, 2);
sqlite> drop table foo;
sqlite> vacuum;
sqlite> ^Z

This seems very much a corner case and I don't imagine this is a problem 
in practice; any concern about this number being when it is too high for 
the software opening the file, and as far as I can guess there is no 
"too low" problem -- however in my opinion differences between such 
documents and reality should always be reported, so here it is.

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

[sqlite] Minor errors in CREATE TABLE syntax diagrams

[John Machin]

1. SQLite allows NULL as a column-constraint.

E.g. CREATE TABLE tname (col0 TEXT NOT NULL, col1 TEXT NULL);

The column-constraint diagram doesn't show this possibility.

Aside: The empirical evidence is that NULL is recognised and *ignored*;
consequently there is no warning about sillinesses and typoes like in
these examples of column-def:
col1 INTEGER NOT NULL NULL
col1 INTEGER NOTE NULL -- type="INTEGER NOTE", constraint="NULL"

2. According to the diagram for foreign-key-clause, there is no "express
track" which allows skipping both "ON DELETE|UPDATE|INSERT etc" and
"MATCH name". However SQLite does permit all of that to be skipped.

E.g. CREATE TABLE tname(col0 TEXT PRIMARY KEY, col1 TEXT REFERENCES
ftable(fcol));

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

Re: [sqlite] (no subject)

[John Machin]

On 26/05/2009 7:58 PM, Samuel Baldwin wrote:
> On Tue, May 26, 2009 at 4:45 PM, Martin.Engelschalk
>  wrote:
>> select * from sqlite_master;
> 
> Or:
> .dump tablename

Don't try that with your 100MB database without ensuring that your 
keyboard interrupt mechanism isn't seized up :-)

Perhaps you meant

.schema tablename

Cheers,
John

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

Re: [sqlite] Checking if an "integer" column is set to NULL

[John Machin]

On 25/05/2009 10:15 PM, chandan wrote:
> Hi,
> I have used sqlite3_bind_null() API to bind an integer column with 
> NULL. When I read the value of that integer column I get the value as 0 
> (zero). Is there any way I can check if the column is set to NULL?

You do realise that calling it "that integer column" is more hopeful 
than meaningful, don't you?

How are you reading "the value of that integer column"?

Here are some ways you can display it and test it using SQL:

sqlite> create table t (i integer);
sqlite> insert into t values(1);
sqlite> insert into t values(0);
sqlite> insert into t values(-1);
sqlite> insert into t values(null);
sqlite> insert into t values(123.456);
sqlite> insert into t values('abcdef');
sqlite> insert into t values(x'f000baaa');
sqlite> select rowid, i, quote(i), typeof(i) from t;
1|1|1|integer
2|0|0|integer
3|-1|-1|integer
4||NULL|null
5|123.456|123.456|real
6|abcdef|'abcdef'|text
7|­|X'F000BAAA'|blob
sqlite> select rowid, i, quote(i), typeof(i) from t where i is null;
4||NULL|null

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

Re: [sqlite] SQLite3 support for 64-bit unsigned integers

[John Machin]

On 25/05/2009 4:28 PM, Kelly Jones wrote:
> I tried inserting 2^63-1 and the two integers after it into an SQLite3
> db, but this happened:
> 
> SQLite version 3.6.11
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE test (test INT);
> sqlite> INSERT INTO test VALUES (9223372036854775807);
> sqlite> INSERT INTO test VALUES (9223372036854775808);
> sqlite> INSERT INTO test VALUES (9223372036854775809);
> sqlite> .mode line
> sqlite> SELECT * FROM test;
>  test = 9223372036854775807
>  test = 9.22337203685478e+18
>  test = 9.22337203685478e+18
> 
> sqlite> SELECT * FROM test WHERE test = '9223372036854775808';
>  test = 9.22337203685478e+18
>  test = 9.22337203685478e+18
> 
> Why the sudden switch to scientific notation and loss of precision?

See answer to next question.

> Are 64-bit integers signed (ie -2^63 to 2^63-1)?

SQLite's integers are 64-bit signed two's-complement. Don't bet the 
ranch on -2^63.

> Can I "unsign" them?

No.

> Since sqlite3 uses 64-bit ints for rowid, I figured they'd be unsigned.

Since using rowids at the rate of 1 million per second would bump into 
2^63 after about 292,000 years, and since SQLite has only one integer 
type, to figure that it'd be signed would be a better betting proposition.

> Workarounds?

BLOBs, maybe, depending what you want 64-bit unsigned integers for. 
What's the use case?

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