Re: [sqlite] Bug in SQLite FLOAT values

[RSmith]

On 2015/02/07 15:47, Abdul Aziz wrote:

Thanks for replybut I am now using VARCHARS, then how this is working?
not generating any errors?

Please elaborate, my query to create DB is:
mSQLiteDatabase.execSQL("CREATE TABLE " + tableName
+ " ( "
+ EVENT_TIME + " INTEGER, " + SYSTEM_TIME + " INTEGER PRIMARY KEY, "
+ ACCURACY + " INTEGER," + X + " VARCHAR, " + Y + " VARCHAR, " + Z
+ " VARCHAR );");


It translates Varchar to Text, that's why no errors are generated, it understands what you mean by Varchar, which is really just 
some text, so it translates it to the internal type TEXT.


As for your question about decimal points and floats, no float in any language stores values up to a certain length... lengths are 
the domain of Strings and text, not Floating point numbers. any floating point number is an approximate number with a representation 
as close as is possible to the actual number. That representation includes many significant digits in the significand and an 
exponent. You may need to read up on floats some more to see how it works - my point is just that it doesn't  store numbers up to a 
certain length, for that you need a formatter.


Many DB engines offer formatted types, such as Decimal (in PG, Oracle, MSSQL, etc) where you can say you need the number with so 
many decimals after the point.  In SQLite you can format the output (much like your C solution) by doing  SELECT 
printf('%.6f',somevalue); etc.


Read the pages offered by the other posters and maye check out the Wikipedia pages on floating point storage and representation to 
understand WHY all the above happens, but to solve your immediate problem, use the output formatting or store as strings - there is 
no way to tell a true floating number to keep itself short.


Other interesting things you can see about this floating point problem (it's a mathematical problem too), look on youtube for "Why 
is 0.9... equal to 1?" or "How do we know two numbers are distinct?" - The Numberphile videos in general do a good job of 
explaining it.


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

Re: [sqlite] why does the recursive example sort alphabetically

[RSmith]

On 2015/02/04 15:45, Mark Summerfield wrote:

Hi,

In the documentation on page http://www.sqlite.org/lang_with.html
there is an example of a recursive query if you scroll down to the heading
"Controlling Depth-First Versus Breadth-First Search Of a Tree Using ORDER
BY".

The second example under that heading shows how to get a depth-first
search. Here's the query:

WITH RECURSIVE
   under_alice(name,level) AS (
 VALUES('Alice',0)
 UNION ALL
 SELECT org.name, under_alice.level+1
   FROM org JOIN under_alice ON org.boss=under_alice.name
  ORDER BY 2 *DESC*
   )
SELECT substr('..',1,level*3) || name FROM under_alice;


It turns out that this query not only provides a correctly indented output
of the tree, but it also sorts every branch alphabetically by name.

What I don't understand is *why* it sorts alphabetically by name.

I would have expected to need to change the query to have ORDER BY 2 DESC,
org.name
for it to work, but it works anyway.


It doesn't actually "work anyway", the result is not ordered at all - it just happens to be the order in which the rows are fetched 
which only happens to be in that order because of the default sort direction of the primary key (which is ascending).  If you do 
PRAGMA reverse_unordered_selects=1;  and then run the query again, it should pop out the opposite order.


(I haven't actually tested this, but trust it to the point of not feeling the need to test it, hoever, let us know if you test it 
and get a different result).




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

Re: [sqlite] Possible to get table size (in bytes)?

[RSmith]

On 2015/02/04 20:26, Rael Bauer wrote:

Hi,

Is it possible to get the information of how many bytes a table is taking up in 
the database?


Not with API calls, but it is possible by running the SQLiteAnalyzer utility 
afvailable from the same download pages as SQLite3 CLI etc.
http://www.sqlite.org/download.html

In the output which the analyzer produces, you can find the space taken up by any table or index, also the total pages ofr tables 
and the bytes payload (which is the actual amount of data, I believe what you are looking for?) among a myriad of other data 
properties and measurements.


 Also, you can load the output as a script which would add a table to your database storing all this info - but it is of course 
only updated once the analyzer is run again.





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

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[RSmith]

On 2015/02/03 17:34, Gerald Bauer wrote:

Hello,

I'm looking for a little tool that reads in an SQLite schema (e.g.
beer.db, football.db, etc.)  and outputs (generates)  documentation
for tables, fields etc. as a single HTML page or as HTML pages.Any
insight appreciated?


Actually I was just in the process of making such a tool - maybe we could help each other. Could you send me an example schema and 
resulting document that you would like it to produce?

No need to be complex, but it needs to contain every plausible kind of result 
needed. Triggers and Indices too. Thanks!



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

Re: [sqlite] Encoding question

[RSmith]

On 2015/02/02 19:37, Peter Haworth wrote:

On Mon, Feb 2, 2015 at 9:00 AM,  wrote:


From: RSmith 
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Encoding question
Message-ID: <54cebb71.8060...@rsweb.co.za>
Content-Type: text/plain; charset=windows-1252; format=flowed

In short, the UTF-8 Pragma settings /allows/ your data to be interpreted
as such. It doesn't /force/ it, nor magically /converts/
the data into UTF-8, and it most certainly does not under any
circumstances *guarantee* the UTF-8-ness of data. (Though it does
guarantee that /IF/ you put valid UTF-8 data in there, it will be handled
and returned correctly).


Thanks for this and the other responses, makes sense.  I suppose it's
similar to putting non-integer data into an INTEGER column.

This is in the context of an SQLite utility I sell which I'm trying to make
unicode compatible so I have no control over the data in the database, just
have to interpret it the best I can.  I've seen that there are algorithms
out there that will detect different encodings but it seems that the
algorithms are not 100% reliable.

I should also have mentioned that the question also included table names,
column names, constraint names, etc, but I'll assume the same applies to
them as for the data.


Good news here is that if you do set the DB to be UTF-8 compatible (with the discussed Pragma) all your table names, column names 
and database objects in general are very much UTF-8 enabled. You can name a table in Hebrew or Chinese without any issues and fill 
them with UTF8 data (as long as your program takes care of adding the data in correct UTF8, it willl get it back in correct UTF8).


Proof of concept - here is a script I made quickly adding some poetry from different nations to a table called Des Garçons (Boys) 
with two columns having names in Braille and Russian with an Index in Chinese, and querying it using other UTF-8 SQL statements. You 
should be able to copy-paste this and run it through another SQLite engine on any UTF8 enabled DB:


(I hope the mail forum reproduces this right)


  -- Processing Script for File: E:\Documents\SQLiteScripts\UTF8_Test.sql
  -- Script Items: 7  Parameter Count: 0
  -- 2015-02-02 20:48:22.804  |  [Success]Script Started...
  -- 


DROP TABLE IF EXISTS "Garçons";
CREATE TABLE "Garçons" (
  "ID" INTEGER PRIMARY KEY,
  "⠝⠙⠊⠞" TEXT,
  "пустынных" TEXT
);

CREATE INDEX "我能吞下" ON "Garçons" ("ID","⠝⠙⠊⠞");

INSERT INTO "Garçons" VALUES
 (1,'From the Anglo-Saxon Rune Poem (Rune version):',
'ᚠᛇᚻ᛫ᛒᛦᚦ᛫ᚠᚱᚩᚠᚢᚱ᛫ᚠᛁᚱᚪ᛫ᚷᛖᚻᚹᛦᛚᚳᚢᛗ
ᛋᚳᛖᚪᛚ᛫ᚦᛖᚪᚻ᛫ᛗᚪᚾᚾᚪ᛫ᚷᛖᚻᚹᛦᛚᚳ᛫ᛗᛁᚳᛚᚢᚾ᛫ᚻᛦᛏ᛫ᛞᚫᛚᚪᚾ
ᚷᛁᚠ᛫ᚻᛖ᛫ᚹᛁᛚᛖ᛫ᚠᚩᚱ᛫ᛞᚱᛁᚻᛏᚾᛖ᛫ᛞᚩᛗᛖᛋ᛫ᚻᛚᛇᛏᚪᚾ᛬')

,(2,'From Laȝamon''s Brut (The Chronicles of England, Middle English, West 
Midlands): ',
'An preost wes on leoden, Laȝamon was ihoten
He wes Leovenaðes sone -- liðe him be Drihten.
He wonede at Ernleȝe at æðelen are chirechen,
Uppen Sevarne staþe, sel þar him þuhte,
Onfest Radestone, þer he bock radde.')

,(3,'From the Tagelied of Wolfram von Eschenbach (Middle High German): ',
'Sîne klâwen durh die wolken sint geslagen,
er stîget ûf mit grôzer kraft,
ich sih in grâwen tägelîch als er wil tagen,
den tac, der im geselleschaft
erwenden wil, dem werden man,
den ich mit sorgen în verliez.
ich bringe in hinnen, ob ich kan.
sîn vil manegiu tugent michz leisten hiez.');


SELECT ID,"⠝⠙⠊⠞",Char(13)||"пустынных"
FROM "Garçons" WHERE "⠝⠙⠊⠞"<>'';

  -- ID⠝⠙⠊⠞Char(13)||"пустынных"
  -- ---
  -- 1From the Anglo-Saxon Rune Poem (Rune version):
ᚠᛇᚻ᛫ᛒᛦᚦ᛫ᚠᚱᚩᚠᚢᚱ᛫ᚠᛁᚱᚪ᛫ᚷᛖᚻᚹᛦᛚᚳᚢᛗ
ᛋᚳᛖᚪᛚ᛫ᚦᛖᚪᚻ᛫ᛗᚪᚾᚾᚪ᛫ᚷᛖᚻᚹᛦᛚᚳ᛫ᛗᛁᚳᛚᚢᚾ᛫ᚻᛦᛏ᛫ᛞᚫᛚᚪᚾ
ᚷᛁᚠ᛫ᚻᛖ᛫ᚹᛁᛚᛖ᛫ᚠᚩᚱ᛫ᛞᚱᛁᚻᛏᚾᛖ᛫ᛞᚩᛗᛖᛋ᛫ᚻᛚᛇᛏᚪᚾ᛬

  -- 2From Laȝamon's Brut (The Chronicles of England, Middle English, West 
Midlands):
An preost wes on leoden, Laȝamon was ihoten
He wes Leovenaðes sone -- liðe him be Drihten.
He wonede at Ernleȝe at æðelen are chirechen,
Uppen Sevarne staþe, sel þar him þuhte,
Onfest Radestone, þer he bock radde.

  -- 3From the Tagelied of Wolfram von Eschenbach (Middle High German):
Sîne klâwen durh die wolken sint geslagen,
er stîget ûf mit grôzer kraft,
ich sih in grâwen tägelîch als er wil tagen,
den tac, der im geselleschaft
erwenden wil, dem werden man,
den ich mit sorgen în verliez.
ich bringe in hinnen, ob ich kan.
sîn vil manegiu tugent michz leisten hiez.

  --Item Stats:  Item No:   5 Query Size (Chars):  74
  -- Result Columns:3 Result Rows: 3
  -- VM Work Steps: 35Rows Modified:   0
  -- Full Query Time:   0d 00h 00m and 00.001s
  -- Query 

Re: [sqlite] Encoding question

[RSmith]

On 2015/02/02 01:12, Peter Haworth wrote:

I'm new to the unicode world so this question may not make sense.

The "PRAGMA encoding" statement tells me the encoding of a database. Can I
rely on all data in the database having that encoding? For example, if the
encoding is UTF8 and a row is inserted containing UTF16 encoded data, will
it still end up as UTF8 data in the database?


Not exactly sure if I understand what you are asking, so this answer may make 
even less sense!

If you are asking whether or not you can rely on the data which you do not control, inside a DB set to UTF-8, to always be UTF-8, 
then the simple answer is: Obviously not, it holds whatever the user (or program using the DB) stores in there however he/she/it 
stores it. What the encoding means simply is that the database engine (SQLite in this case) will treat the data in the database as 
if it is UTF-8 data. It will store, compare, collate, order, extract and do all the other things with the expectation that the data 
will conform to the UTF-8 standard and as such return to you (the user) valid UTF-8 based answers.


None of this prevents you from sticking a BLOB or some UTF-16 or indeed any other Unicode text in there but you might find the 
storing and returning of the values and query answers are not exactly what you expected with the format being somewhat off.


In short, the UTF-8 Pragma settings /allows/ your data to be interpreted as such. It doesn't /force/ it, nor magically /converts/ 
the data into UTF-8, and it most certainly does not under any circumstances *guarantee* the UTF-8-ness of data. (Though it does 
guarantee that /IF/ you put valid UTF-8 data in there, it will be handled and returned correctly).



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

Re: [sqlite] "database disk image is malformed" error occurs more

[RSmith]

On 2015/01/30 14:45, Mario M. Westphal wrote:

- The databases in question are stored on a location hard disk or SSD.

- If a user stores his database on a NAS box or Windows server, it is accessed 
directly, via standard Windows file system routines.

- From what I can tell, network-based databases are not more likely to corrupt 
than databases stored on built-in disks or SSDs or databases kept on disks or 
USB sticks connected via USB.


That is simply not true. The report-back on locking success via a local resource (albeit for a removable drive) is under normal 
circumstances absolute and correct. For a network file (remote) source, that is just not true in near all network cases.  If you can 
be sure only one instance of your program access it over the network and nothing else, then it should not be harmed, but this is 
difficult.


Users kill their processes and re-start programs and SQLite connections (unwittingly) that finds hot roll-back journals and all 
kinds of things that might fall into a long "busy" cycle which may again prompt a process-kill, etc.


It's easy to tell though, when you get reports of corruption, require the file location information. A pattern should quickly emerge 
if this is a networking problem.



- My software is updated every 2 to 4 weeks, and I always include and ship with 
the latest SQLite version.

- There is a big variance in when users update so some users may work with 
versions several months old, but not older than 2 months, typically.

- A user may access a database from multiple computers, but then only in 
read-only mode. Write access is only permitted when the database is opened in 
exclusively.

- I use SQLite since about 2008, but the code base is changed frequently. I 
maintain old databases (up to maybe one year old and use them in regression 
tests before shipping).



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

Re: [sqlite] Truncation of floating point numbers in SQLite?

[RSmith]

On 2015/01/30 05:49, Donald Shepherd wrote:

Trying to retrieve a stored qNaN or sNaN returns a column type of NULL and a 
value of 0.


Thank you for letting us know.

Well I suppose that's SQLite's method to answer with errors of the sort, returning NULL (as is the case with div0 for instance). 
Which makes some sense and is probably documented (though I did not check).  It seems to get really unhandled bitwise round-trips 
you will need to store/receive  blobs or strings in the 0xNNN format.


At first I thought SQLite (or any RDBMS) should really strore whatever you give and return it untouched, but that cannot be true for 
an RDBMS because it has to interpret the data, it isn't just a binary store. It has to answer questions like SELECT ("colA"+3), 
(7/"colB");  or sort by colA or use a collation on strings etc. etc. - all of which means it must care about what the value relates 
to and cannot simply ignore it unless stored as an ignorant type (i.e Blob).



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

Re: [sqlite] Truncation of floating point numbers in SQLite?

[RSmith]

On 2015/01/29 05:05, James K. Lowden wrote:
There's no reason to think, if the data are provided in binary form, that they won't be returned in the identical form absent an 
explicit conversion. If that's not so, I'd sure like to know why. I'm faintly surprised NaNs can't be stored, too. Why should 
SQLlite interpret them if they're bound to a double?


Indeed, which is what all the posts have been saying more or less in terms of 
round-tripping all but NaNs.

In the case of NaN though, there are two defined NaNs, namely qNaN and sNan which both means the same but the sNaN will cause an 
exception even at hardware level by merely passing through any register, which I believe is its intended purpose. This means that in 
order for software to work correctly, it should never let an sNaN pass through untouched, it should produce an error so all parties 
are savvy to the wrongness that just occured (Even if the software in use is not specifically checking for NaN, an sNaN should still 
cause an exception from lower down).


qNaN should pass through and round-trip same as any other float. I'm not sure how SQLite handles either of these NaNs, but am now 
quite interested to know.


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

Re: [sqlite] Truncation of floating point numbers in SQLite?

[RSmith]

On 2015/01/29 01:00, Donald Shepherd wrote:

I can say there's no string round-trips with fairly high confidence and I
am using _bind_double and _column_double.

I can also confirm NaNs are a special case, as I've had to write code to
store those as a BLOB.


So you have a case where you have a 64-bit IEEE754 Float (C - double) which isn't a NaN and add it to a query by 
sqlite3_bind_double() and then retrieve it back using column_double() and the bit pattern across the 8 bytes is different to the 
original?


Please  do share!



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

Re: [sqlite] "database disk image is malformed" error occurs more

[RSmith]

On 2015/01/28 20:06, Mario M. Westphal wrote:

1. I don’t have the damaged databases here so I cannot run the diagnosis 
myself. The databases are usually too large to upload or transfer.

2. The SQLite version I currently use is 3.8.8.1 (complied using the Amalgation 
and Visual Studio 2012).
But since not every user always keeps up to day, older versions of SQLite are 
also in use, some maybe 3 to 4 four months old.


Hi Mario,

Thank you for being specific. I have to ask, what were the changes you implemented in your application a few months ago?  SQLite 
seems to not be in general worse for wear, but almost weekly you are getting serious problems (in DB terms) cropping up - and this 
only started some months ago. The common denominator seems to be "some months ago", so your system may have changed in a way that 
somehow facilitates the error.


Of course you already pointed this out, so it's understood, but my aim is that: if you could list the changes you have made recently 
in general  and maybe specific to SQLite usage, we might better guess at which things to check or recognise similarities with 
problems we've faced.  That said, there are not many design choices that might cause "Database malformed" errors and since you are 
already familiar with all the documentation, we could assume you would have noticed anything obvious.


All this makes it very hard to guess. Getting specific logs with the improved error reporting would be helpful as Richard suggested, 
or making your app "phone home".  Are the come-backs all random? Do you have your own server running a user version or test version 
under full load at your own offices perhaps?


Also, getting one Malformed Database a week out of how many? 5, 500, 500 000?  (Not that it changes anything, there should be no 
incidents, but it might tell us something about the prevalence). Does your system  have a back-up mechanism? (The DB sizes you 
describe seem to suggest you would shy away from an auto-multi-backup scenario).



I know all of the above do not help directly, but this error seems strange so I 
am simply prompting for more information.

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

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

[RSmith]

On 2015/01/26 14:00, Tim Streater wrote:

On 26 Jan 2015 at 07:33, Hick Gunter  wrote:


It is never a good idea to rely on automatically assigned column names. If you
want reproducible, predictable, release independant column names then please
assign them with the AS clause.

So you're saying that if I do:

   create table wiggy (a,b,c,d,e,f,g,h);

and later do:

   select a, b, c, d, e, f from wiggy;

then I should really be doing:

   select a as a, b as b, c as c, d as d, e as e, f as f from wiggy;

That'll be a bit tedious changing the more than 300 select statements in my app.


Actually yes and no...  You are mixing two different problems here, namely the REQUEST and the RESULT. The first is referencing a 
table name and column name inside a query - this is the entire point of the SQL language and must always be understood correctly by 
the Engine to produce the correct result. For this, you can request: SELECT a,b FROM wiggy; and it MUST in all circumstances return 
exactly values found in those specific columns of that table, every time, without fail... and, the good news is: it does.


The problem as posted by the OP defines the second scenario where you use that same query (SELECT a,b FROM wiggy;) and then examine 
the RESULT, for example:


For every returned result, if itsResultColName=="a" then put the value in x and if 
itsResultColName=="b" then put the value*2 in y...
This is dangerous.

The correct thing would be:
For every returned result row, put the first value in x and second value*2 in 
y...

If you request first a, then b, you can and must expect it to be returned exactly like that, but expecting the name of the /result/ 
column headers to absolutely be "a" and "b" you may do if, and only if, you explicitly asked for it to be so (to borrow your 
example: SELECT a AS a, b AS b FROM wiggy; ).


Yes I know ALL SQL engines will return the correct column names for the above example queries because they are simple and it is the 
most obvious name to return, but please do not allow that to lure you into expecting that you can always trust the returned column 
name in more complex queries.



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

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

[RSmith]

On 2015/01/26 04:04, James K. Lowden wrote:

On Sun, 25 Jan 2015 23:18:05 +0200
RSmith  wrote:


There is no documentation in either SQLite or the SQL standard
that would lead anyone to believe that behavior is expected - in fact
it is very clear about the returned column names being
non-deterministic if not explicitly requested by aliasing.

I no longer think that is entirely true.  In SQLite's documentation,
it's not in the description of the SELECT statement that I can find
(http://www.sqlite.org/lang_select.html).  It is mentioned in the
sqlite3_column_name description, so, OK, it's not a bug.

But I'm going to say that a function that "returns the name" of a
column really should return the column's *name*.  Especially if that
name would suffice to address the column syntactically if the form
statement were wrapped one more level, i.e.:

select * from () as T

Otherwise there's weird mismatch between the names SQL itself uses and
the ones presented to the user.


You are of course correct in feeling this way from a practical point of view, and I daresay SQLite (and the other engines) tries to 
return useful colmn naming where the cost of deduction does not impact performance. In fact, I believe it is this specific thing 
that lulls users or programmers into believing that the column naming returned by the engine for non-aliased columns is somehow 
governed by special rules or format and can therefore be trusted to be always conformant - where they really shouldn't.


Implementing a specific methodology for returning useful column naming based maybe on column references (as per your suggestion), I 
am not quite comfortable with.
Forgetting for the moment any calculated columns and other obvious column-reference/naming problems, even in straight-forward 
reference schemes I think there will be difficulties - I can think of tables joined to themselves (maybe even more than once, a 
useful trick as you know), multiple tables joined where some columns appear in both but others not and other possibilities that 
might come to mind if more time is spent on it.


Understand, I do not think these are insurmountable problems, but two questions 
arise:
   - Who decides the rules for handling it so that it may become "trusted" by DB users/admins/programmers, if not the SQL standard? 
- and,
   - What cost to performance will be acceptable? (i.e. how many cpu cycles might be spent to finding suitable column names when 
they are not specifically requested?)


Add to this the fact that for most queries where result column headers are not aliased to specifically required items, the actual 
naming just doesn't matter - and if it does,

   A - you are doing it wrong (which isn't a new rule), and
   B - It is so easy to add an alias request if you need an exact name.


I do understand that there is a small but important category of systems where this is paramount, like people making DB tools or 
admin tools which try to interpret data from user-specified queries, where random returned column naming is detrimental (I'm myself 
troubled by this), but I do not think the engines should cater for this if it comes at any sort of cost.



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

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

[RSmith]

On 2015/01/25 15:16, Marcus Bergner wrote:

Hi,
Using the latest amalgamation build sqlite-autoconf-3080801 I'm seeing the
following inconsistent behaviour:

$ ./sqlite3
sqlite> .headers on
sqlite> pragma short_column_names;
1
sqlite> pragma full_column_names;
0
sqlite> create table tbl1 (id1 integer, s1 text);
sqlite> create table tbl2 (id2 integer, s2 text);
sqlite> insert into tbl1 values (1, 'v1');
sqlite> insert into tbl2 values (1, 'v2');
sqlite> select x.id1, x.s1, y.s2 from tbl1 x inner join tbl2 y on
x.id1=y.id2;
id1|s1|s2
1|v1|v2

So far so good, everything as expected. If I rewrite the above select
statement to do a join with a subquery instead the resulting output changes
in an unexpected way.

sqlite> select x.id1, x.s1, subq.s2 from tbl1 x inner join (select * from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;
x.id1|x.s1|subq.s2
1|v1|v2

Here we get unexpected column prefixes on all fetched columns. If I rewrite
the query again to a subq.* query the behaviour is different again where
only the first two columns have prefixes.

sqlite> select x.id1, x.s1, subq.* from tbl1 x inner join (select * from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;
x.id1|x.s1|id2|s2
1|v1|1|v2

Expected behaviour: returned columns should not contain prefixes in any of
the above scenarios.


May I ask what exactly makes you "expect" your expected behavior? There is no documentation in either SQLite or the SQL standard 
that would lead anyone to believe that behavior is expected - in fact it is very clear about the returned column names being 
non-deterministic if not explicitly requested by aliasing.


In short, if you need the column names to be something specific, you need to ask for it exactly so, else the SQL engine (any of 
them, not just SQLite) may return whatever they like by virtue of what might seem the most unconvoluted but also non-ambiguous (this 
latter simply being a minor paradigm,  not a rule of any kind).


My guess is you have come to expect the behavior by simply observing it in the past and not through consulting the standards 
(because that's how I made the mistake initially).


Always use aliasing when you need the results to conform.


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

Re: [sqlite] Having problems with Entity Framework code first db creation

[RSmith]

On 2015/01/23 16:51, Walter Williams wrote:

I'm trying to use a code first model ///... (snipped)

then in the signature...


"Do, or do not.  There is no try."


Thank you for the chuckle.  As to the actual question, when you say "when I try to create a new database file", do you mean that you 
are using the open command (or whatever in the wrapper tries to call the sqlite3_open* commands) with a valid filename (which need 
not exist, but must be a valid name) and then it fails with the mentioned message?


The list unfortunately (or fortunately) does not permit attachments, could you use a file upload/sharing service kindly so we can 
access the file and (hopefully) better understand the question? - thanks.


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

Re: [sqlite] ORDER BY and LIMIT regression

[RSmith]

Might this not be a "reverse_unordered_selects" pragma or compile option  going wrong, or at least the code making it work getting 
somehow hooked in the new versions for this query?


I have seen similar things when using that pragma (but of course that was 
intended).
Just a thought...


On 2015/01/19 16:27, Richard Hipp wrote:

Ignore my previous email on this subject.  We are able to get
different results from 3.8.6 and 3.8.8.  Unclear yet if the one or the
other is incorrect.

On 1/19/15, Richard Hipp  wrote:

On 1/19/15, Angelo Mottola  wrote:

Hello,

I have a regression to report, that seems to have been introduced between
SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6
and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in
3.8.8 but eventually it wasn’t).

...

The query worked correctly with SQLite 3.8.6, returning for our test-case
database 5 records with the same EB_DocumentiFiscali__00.NumeroInterno,
ordered by EB_RigheDocFiscali.NumeroRiga in ascending order.
With 3.8.7 and 3.8.8 however, the very same query returns the same 5
records
but in the wrong order, as if it was ordered by NumeroRiga DESC (instead
of
ASC). What’s even more strange is the fact that if you remove the LIMIT
clause, the records are returned in the correct order even with 3.8.7 and
3.8.8.


I downloaded your test database and ran your query on 3.8.6, 3.8.7.4,
and 3.8.8.  All three give the same answer for me.  Dan did likewise
with the same results, and in addition ran the test under valgrind
with no warnings issued.

Unable to recreate the problem.

--
D. Richard Hipp
d...@sqlite.org





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

Re: [sqlite] Malformatted output by sqlite3

[RSmith]

On 2015/01/19 12:52, Stephan Buchert wrote:

(Prompt erased for easier paste and copy):

CREATE TABLE satpos(msec INTEGER, latitude REAL, longitude REAL);
INSERT INTO satpos VALUES (86386217,-0.0318895369716216,-167.689719869132);
INSERT INTO satpos VALUES
(86386716,-2.93238037697483e-06,-167.690497310632);
INSERT INTO satpos VALUES (86387217,0.0319616241531195,-167.69127664905);
SELECT * FROM satpos;
86386217|-0.0318895369716216|-167.689719869132
86386716|-2.93238037697483e-06|-167.690497310632
86387217|0.0319616241531195|-167.69127664905

So far, so good, but:

.mode column
.width -8 -7 -8
SELECT * FROM satpos;
86386217  -0.0318  -167.689
86386716  -2.9323  -167.690
86387217  0.03196  -167.691

In the 2nd row, 2nd column "-0." or "-2.9e-6" would make me happy. But
"-2.9323" definitely messes up my stuff.


The width specifier is simply a cut-off style formatter. Two solutions to get the correct anwer listed below, pic the one that suits 
you -



CREATE TABLE satpos(msec INTEGER, latitude REAL, longitude REAL);
INSERT INTO satpos VALUES (86386217,-0.0318895369716216,-167.689719869132);
INSERT INTO satpos VALUES (86386716,-2.93238037697483e-06,-167.690497310632);
INSERT INTO satpos VALUES (86387217,0.0319616241531195,-167.69127664905);

SELECT msec, round(latitude,6), round(longitude,6) FROM satpos;

msecround(latitude,6)round(longitude,6)
86386217-0.03189-167.68972
86386716-3.0e-06-167.690497
863872170.031962-167.691277

 SELECT msec, printf('%12.3f',latitude), printf('%12.3f',longitude) FROM satpos;

msecprintf('%12.3f',latitude)printf('%12.3f',longitude)
86386217  -0.032-167.690
86386716  -0.000-167.690
86387217   0.032-167.691




  Script Stats: Total Script Execution Time: 0d 00h 00m and 00.049s
Total Script Query Time: 0d 00h 00m and 00.005s
Total Database Rows Changed: 3
Total Virtual-Machine Steps: 141
Last executed Item Index:6
Last Script Error:

2015-01-19 14:22:02.197  |  [Success]Script Success.
2015-01-19 14:22:04.959  |  [Success]Transaction Rolled back.

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

Re: [sqlite] Shell tool improvement request

[RSmith]

On 2015/01/16 18:33, Simon Slavin wrote:

(following description simplified)

I have a text file I wanted to .import into a table.  The text file has two 
columns separated by a tab: a word and a number.  It starts off like this:

!   32874624
"  239874242
#   98235252
$   438743824
%   324872489
&   39854724
a   23498725
i   1298371287
as  23194874
at  3598725

and continues for another 13588392 (sic.) rows.

I use the SQLite shell tool.  I create an appropriate table in my database, set 
.separator to "\t" and use .import.

The .import failed because the second line of the file contains a quote 
character.  I'm okay with the fact that it fails: it's documented, or nearly 
documented.

However, the failure message is this:

Error: mytextfile.txt line 13588392: expected 2 columns of data but found 1

Naturally I spent some time looking near the end of the file to figure out what was wrong 
where the actual error was in line 2, and should have read something like "still 
inside quote when file ends".


Firstly - thanks, this made me chuckle.  Secondly - I think the parser does not realize there is an error until it is at the end of 
the file nor counts any imports as successful or indeed imported at all, since that first line will very much not be imported after 
the transaction was rolled back (I hope), so to report the successful imports number is probably not feasible. Stating that the 
error was specifically due to this or that might also be a stretch if one examines the number of possible things that can go wrong.


I do however believe it would be easy to note the line at which the import failed as being the line where parsing started for the 
current record (i.e line 2 in Simon's case), but then it may well be the error actually occurs on line 13588392 (such as an invalid 
UNICODE character), reporting line 2 in this case will be diabolical.


Last I would offer an idea to simply specifically check for "Unclosed Quote" error. I mean I can't imagine another import like the 
one above existing in real life, but certainly an actual unclosed quote might be a common mistake in some new export system (which 
maybe one is designing for oneself or as an application extension) or such.



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

Re: [sqlite] Huge WAL log

[RSmith]

On 2015/01/16 11:33, Jan Slodicka wrote:


The code schema is as follows:

foreach table
{
 BEGIN
 INSERT INTO table VALUES()
 INSERT INTO table VALUES()
 ...
 COMMIT
}

Large column values are supplied as parameters, the rest (vast majority) is
passed through SQL command.


When you say "passed through SQL command", you mean using sqlite3_execute()?

If so, why not bind them all? That will be significantly faster...

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

Re: [sqlite] Inserting/removing large number of rows with index

[RSmith]

On 2015/01/15 23:18, Baruch Burstein wrote:

Hi,

If I have a table with an index, and INSERT or DELETE a large number of
rows in one statement, does sqlite stop to update the index for each
record, or is it smart enough to update the index just once for all the
changed records?

 In a B-Tree Index this hardly matters much in terms of total time (it just sometimes feels a lot faster when you first insert then 
index, but in reality the total time should not differ  hugely unless you are inserting an insane amount of rows), but it is not a 
question of being "smart" enough or not, it's a question of functionality - if the index is unique or primary (for instance), it 
must fail immediately when a duplication occurs, not at the end. How would it accomplish this if it deferred indexing to after the 
insertions took place?


The SQL's first and foremost responsibility is producing correct answers and strict enforcement of the DB designer's rules - only 
after these are satisfied can an engine attempt optimization. This is why it is suggested sometimes that if the checking does not 
matter to you during insertion, then build the index afterwards.


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

Re: [sqlite] SQLite 3.8.8 expected in January

[RSmith]

On 2015/01/10 15:50, Richard Hipp wrote:
Yes, it was a compile-time omission. I have uploaded a new DLL that includes the loadable extension interface. 


Thank you - it works perfectly for all entries.

All other tests worked well too, so no new problems to report from this side.

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

Re: [sqlite] SQLite 3.8.8 expected in January

[RSmith]

The pre-compiled and supplied DLL (sqlite3.dll) seem to be missing an entry point for "sqlite3_enable_load_extension" - I do not see 
any mention in the update text about altering or removing this feature so I am assuming this might be a compile-time omission?



On 2015/01/09 19:23, Richard Hipp wrote:

We hope to release SQLite version 3.8.8 sometime later this month
(January).  A change-log is available at
https://www.sqlite.org/draft/releaselog/current.html

Please stress the code in every way you can between now and then and
report any problems to this list, or directly to me.

Source code snapshots and precompiled Windows DLLs can be found at
https://www.sqlite.org/download.html



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

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

[RSmith]

On 2015/01/09 01:12, MikeSnow wrote:

I was wondering if anyone could let me know where I am going wrong. I am
getting the error...
"Error while executing query: no such column: t1.*B.Switch-Tower-Sector"
but, the column,  t1.[*B.Switch-Tower-Sector], does exist.  I get results
when I do
select[*B.Switch-Tower-Sector] from t1;
but an error when I do the UPDATE statement...

UPDATE t2
SET [*B.ANT_ORIENTATION] =
(SELECT t2.ANT_ORIENTATION
FROM t2
WHERE
t2.[*SSID-CELLID-SECTOR] = t1.[*B.Switch-Tower-Sector]);


Hi Mike,

There are many things that might be improved here, and one thing that is 
definitely wrong.

In the Update Query, you are asking the DB to set a column named "*B.ANT_ORIENTATION" in a Table called "t2" to another value in a 
column called "ANT_ORIENTATION" selected from the same table "t2" which can be found in some row it has to look up by seeing where 
the "*SSID-CELLID-SECTOR" column equals a value that can be found in a column called "*D.Sqitch-Tower-Sector" in a Table called "t1" 
- But where is this table "t1" referenced in the query?  Nowhere - not in the UPDATE clause, not in the FROM clause, how must it 
know what t1 refers to?


Even if the table exists in the database, you cannot just reference some column from it without telling the query to scan that table 
in a FROM clause.


Even then, if you add that table to the from clause by means of a join or such, you still need to tell it explicitly which row to 
look up... The value for that column must be compared for which row?


Then, how must it distinguish between table t2 in the Update clause and t2 in 
the SELECT clause? You need a bit of an Alias I think.

I will try to re-write the query here to what I /THINK/ might be a pseudo-query 
of what you intended, but this is just a guess:

UPDATE t2 SET [*B.ANT_ORIENTATION] = (
  SELECT SS.ANT_ORIENTATION
FROM t2 AS S2
LEFT JOIN t1 AS S1 ON S2.[*SSID-CELLID-SECTOR] = S1.[*B.Switch-Tower-Sector]
  WHERE S2.[SOME_ID]=t2.[SOME_ID]  (...or perhaps 
S1.[SOME_COLUMN]=t2.[SOME_COLUMN]?)
);

If you give us the table layouts (schemata) and explain in simple terms what answer you need from the query, we'd be able to make a 
more accurate suggestion on best query to use.


PS: While The MS SQL SERVER type brackets [ and ] are allowed in SQLite because of niceness, it isn't best practice or correct SQL. 
The SQL standard calls for enclosing columns and table  object names in double quotes:  "column_name";  and values in single quotes: 
'value'.  Another less than optimal practice is using SQL-specific control characters in object names, such as the asterisk - or 
having columns or aliases that are reserved words for the specific engine. It should mostly work though.



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

Re: [sqlite] Sqlite problem possible bug

[RSmith]

On 2015/01/07 12:13, The Responsa Project wrote:

To Whom it amy concern


I am trying to use SQLITE and the like statement with wildcards and hebrew

when I put in an english string it works correctly, such as

Select  * from dbname where colname like '%123%'

I will get all the entries from that column that contain 123 anywhere in the 
column.

However if I substitute 123 with hebrew letters - it matches all the entries, 
not just the ones containing what I asked for.

If I do not use the wilcards in the like it matches the exact word properly. In 
version 3.2.2 of sqlite this worked fine (with wildcards), later versions it does 
not. So for example "SELECT * from dbname where colname like '%אב%' will give 
me all the entries not only the ones matching only אב.

I tried GLOB, which also did not work.?


I would like to (and need to) upgrade to the latest version of Sqlite but I 
cannot because of this issue.


Is this a bug? Am I doing something wrong?


Not a bug in the latest version - works fine for me, but I am not sure which other versions you have tested. Are you using the C api 
directly or going through some wrapper? (It might mess with the UTF8 or whatever encoding you start off with). Is  your DB in UTF-8 
mode?


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

Re: [sqlite] Can I user sqlite on wp8 native project?

[RSmith]

On 2015/01/06 11:24, Bite Forest wrote:

I’m developing game with cocos2d-x. But compile sqlite.c in vs, there’r lots of 
error. Which version of sqlite can I compile through vs in c++ code?


SQLite source is in C so it won't compile in C++, but you can just statically link the .obj for it (in c++) or dynamically link 
against the DLL which can be downloaded or compiled if you have special requirements and c++ link objects for the sqlite3.dll are 
freely available, some people on this list might even have made some.


Other C++ wrappers for SQLite can be  found here:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

(Scroll down a bit to where it says "C++ Wrappers" - some of those links might 
be a bit outdated)



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

Re: [sqlite] VACUUM requires 6.7 times space ?

[RSmith]

On 2015/01/05 13:32, Dan Kennedy wrote:

On 01/05/2015 06:22 PM, Simon Slavin wrote:

I have a database file which is 120GB in size.  It consists of two huge tables 
and an index. //...

Probably running out of space wherever temp files are created.


I haven't done this, but I seem to remember there was a way to tell SQLite where to make temp files, or override the system default 
at any rate - which may help.


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

Re: [sqlite] COMMIT nested in SELECT returns unexpected results

[RSmith]

On 2014/12/24 12:50, Jim Carroll wrote:

I understand that performing a SELECT and nested COMMIT on the same table is
not supported in sqlite, but I would have expected a COMMIT on a separate
table would not be a problem.  Some test code in python however reveals that
performing the COMMIT disrupts the SELECT statement, and causes duplicate
data to be returned.

  


If this is not a supported operation, would you mind pointing me to the docs
so I can understand it better?


The example code works fine if executed in another language (in C first, then I tried PHP since it's also scripting, but I don't 
have Python on Linux to try with).  That said, I use explicit Transactions, your interface seems to do some form of automatic 
transaction (I only see commits, no begins...) which might be a clue.


i.e. this is not an SQLite problem per se, unless your version has a problem. I am expecting it might be a peculiarity with the 
connector you are using. Would that be PySQLite? If so, the devs for that also see this list so maybe they could comment.  I can't 
imagine a scenario in which your posted results would be the expected result though, so you are correct to question it.


In the meantime, could you kindly post the actual connector or SQLite interface used and which version of it (both the 
connector/interface and the SQLite versions) so that we can test fully.


You can get the version of SQLite returned with this query:
*select sqlite_version();*



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

Re: [sqlite] Parentheses in column name

[RSmith]

On 2014/12/22 22:19, Federico Jurio wrote:

Hi guys, i'm trying to make a simple query using sqlite through gdal
library ( http://www.gdal.org/ogr_sql.html )

I have a simple table with two columns: ID and (asd*) (both columns have
integer values)

I want the minimun value of this columns

My first attempt was successful

E:\Pruebas>ogrinfo -sql "select min(id) MINID from Geo1" Geo1.shp
INFO: Open of `Geo1.shp'
   using driver `ESRI Shapefile' successful.

Layer name: Geo1
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
MINID: Integer (10.0)
OGRFeature(Geo1):0
   MINID (Integer) = 2

My second attempt was unsuccessful

E:\Pruebas>ogrinfo -sql "select min((asd*)) MINASD from Geo1" Geo1.shp
INFO: Open of `Geo1.shp'
   using driver `ESRI Shapefile' successful.
ERROR 1: SQL Expression Parsing Error: syntax error, unexpected ')'.
Occured around :
select min((asd*)) MINASD from Geo1


E:\Pruebas>ogrinfo -sql "select min(""(asd*)"") MINASD from Geo1" Geo1.shp

Should fix it.


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

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

[RSmith]

On 2014/12/18 12:03, Dan Kennedy wrote:

On 12/18/2014 04:16 PM, Paul wrote:

I understand. I guess, I'll have to stick to UPDATE <-> INSERT.
Thank you for taking your time.

Just out of curiosity, I want to ask one more question.
How can FK constraint fail if I am removing (replacing) row from the 'child' 
table?


The FK constraint can fail because a new row is being inserted into the child table. The reason statement rollback may be required 
is because any replaced rows will be removed before SQLite has a chance to figure out if the INSERT actually does violate the PK 
constraint.




Also - I think it depends on more factors, like that may not be the only child table linking to that item in the parent, nor is the 
child table excluded from also being a parent to another table. Constraints may fail all over and knowing all this before-hand would 
require a much longer query prep I would imagine, but the journal does the job.


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

Re: [sqlite] Feature Request - RowCount

[RSmith]

On 2014/12/13 21:46, James K. Lowden wrote:

So the number of tools with feature X is no measure of the value of X. (Notable example: the tool should keep every query and 
result in a time-sequenced transcript log, so that prior results can be re-examined and prior queries modified. Most tools 
disassociate query from output and invite the user to modify the query in-place, destroying the prior.) 


This is hardly a function of the DB admin tools though, it's a research function or application function (depending on whether the 
inquest is theoretical or physical). That said, many of the tools I know do allow saving queries as scripts with the possibility to 
examine output at every step, but not all of them, so this might be somewhat valid.  More importantly, I was not touting the idea 
that because all the tools use feature X, it is therefore valid... I more conceded the fact that the request for feature X is 
valuable (mostly) only to those tools, which probably renders it less urgent - quite in agreement with your point.


My first question, then, is whether or not the rowcount is so interesting that it must be known before a table can be operated on. 
I suggest the answer is No. The relative & approximate sizes of the tables is known to the admin in most cases and, when it is 
not, the information is readily discovered on a case-by-case basis. Would a proxy figure do? Is it enough to know the number of 
pages or bytes allocated to a table? I don't know if such is available, but if it is perhaps that would serve your purpose. 


Yes, this would actually do, but it is not available as you rightly pondered. To the point of necessity, I have to disagree.  It is 
nearly always the first thing I want to know. When someone here is troubled by a query running time... first question is: how many 
rows are in which joined tables? To state the problem a bit simplistic - It is hard to fathom the meaning of O log N without a clear 
understanding of what both O and N might be.  And that's just from a DB admin perspective, in companies where the business analysis 
data matter, lots of queries are usually stored as tables for further analysis, and the first thing asked is: How many?  Other times 
that figure serves probably only as bemusement to big data fans.  It's usually (I'd say vast majority of cases) an easy and fast 
step to ascertain though (as this discussion pointed out) hence me resting the case - but I do stand by the point that the need 
isn't invalid.


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

Re: [sqlite] Feature Request - RowCount

[RSmith]

On 2014/12/13 14:38, Richard Hipp wrote:
The "SELECT count(*) FROM table" query already has a special optimization in the b-tree layer to make it go faster.  You can see 
this by comparing the times of these queries:


 SELECT count(*) FROM table;
 SELECT count(*) FROM table WHERE 1;

The WHERE clause on the second query disables the optimization and so the second query should run slower.  The second query visits 
and partially decodes every row in the b-tree.  The first visits every leaf page of the b-tree, but it does nothing more than read 
the "number-of-entries" from the header of the page, add that value to the accumulating count, and then move on.


Thank you very much for the clear explanation and taking the time Richard. The proposed methods above are fast indeed, but not 
instant and I guess you will never satisfy everyone until everything works instantaneously. I take the point that the only possible 
improvements seem to need alteration to the file structure or added maintenance which may use up cycles for something that just 
isn't that important to DB use in general - and I have to agree, I too have zero want for seeing more cycles used. I was hoping 
there might be a way, but am satisfied there isn't and would like to hope out loud with Simon that this might be seen in SQLite4.


Thanks,
Ryan

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

[sqlite] Feature Request - RowCount

[RSmith]

To the SQLite devs:

After recent discussion about the row-count issue w.r.t. Nulls in primary keys etc. I have been somewhat wrestling with how to 
improve this from a user perspective.


To explain: Most DB Admin tools out there displays the number of rows in a table when you select it or open it, so too the one I am 
working on and after testing stuff on Simon's question about the row counting, I realised that selecting a large table always pauses 
somewhat (with suitable progress bar) to simply open and report the usual statistics. The culprit of course being row-counting.  The 
problem escalates with bigger tables and most users detest sluggishness and all of us try to make things less so.


I thought of keeping the count cached, which works while the connection is open, but becomes useless if re-opened (another app may 
have changed that in the meantime - actually this may even have happened while the connection is open). I've also tried cheating by 
inspecting the file size and upon big enough files, defer row-counting with some form of [This is a large DB - Click here to check 
the row count, this may take some time.] user message where the row-count is supposed to appear - but as you must be aware I have 
run across DBs several GBs in size with only a few hundred-K rows in the large tables, and one DB I have weighs in at only 250MB but 
have about 11mil rows in the main table. Not to mention the fact that one table might have all the rows and the others may all be small.


To address the table-walk for Indices containing NULLs: Most DB admins and system engineers are savvy to this problem, I am sure in 
over 90% of the cases they do not keep NULLs in primary keys even if SQLite allows this. (I think the figure is over 99% but I am 
weary of exaggeration) - but even if they do have NULLs, sometimes you just need to know the amount of rows, not the amount of 
non-NULL value rows. I realise this cannot fit in an algebraically verifyable SQL result such as count() because of those few cases, 
but a possible pragma can fix it for the rest of us.


I realise this problem is rather specific to the DB admin programs as opposed to user systems, but a Pragma "rowcount(TableName);" 
would be spectacular where the count is simply a fast reference to the total rows regardless of content with documentation pointing 
out the difference.


I am very willing to submit a documentation draft if this feature gets added (to save someone some work) but I am not versed well 
enough in the SQLite internals to attempt a patch. Also, the solution needn't fall upon my suggestion, any other suitable means of 
making row count fast-determinable would be welcome.



Thank you kindly,
Ryan


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

Re: [sqlite] How to speed up database open

[RSmith]

On 2014/12/11 17:58, Paul wrote:



On 2014/12/11 13:51, Paul wrote:
I have yet to try and test if dropping stat tables worth the effort. Some databases in fact can grow pretty big, up to few 
hundred of megabytes// 


In that case maybe keep the Stat1 tables and there is also the option of using stuff like "USING" clauses and "LIKELY" or "UNLIKELY" 
planner directives in your queries to force a tried and tested query plan on the QP in lieu of using the stat tables - but now you 
are getting very hands-on with your data and no longer leaving it up to the internal wisdom of SQLite - something I don't usually 
advocate, but as you rightly observed - your case is quite special.


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

Re: [sqlite] Counting rows

[RSmith]

On 2014/12/11 17:19, Simon Slavin wrote:

In my table which had about 300 million (sic.) rows I did this

SELECT count(*) FROM myTable;

to count the number of rows.  After half an hour it was still processing and I 
had to kill it.

I know that the internal structure of a table means that this number isn't 
simple to produce.  But is there really no faster way ?  This table is going to 
have about six times that amount soon.  I really can't count the rows in less 
than a few hours ?



Not so strange I think...

The highest number of rows I have tried to do maintenance of that sort on was only about 50 million though DB size was around 150GB, 
and the row-count on that took some time to establish, but in the order of minutes, not hours and certainly not days. I have here 
and now only a 10-million row DB to run some quick tests on a machine without SSD or anything good - seems to take around 1 min 20s 
on the first attempt to do a count() and around 33s on the next attempts (I'm assuming caching doing its bit here).


Scaling that up - it becomes apparent that a ~300-mil row DB with 30x the rows than what I am testing should take around 30x the 
time, which is 1m20 x 30 which is around 40 minutes assuming similar hardware.


You probably stopped it just shy of the goal. Either way, 30 minutes and 45 minutes are /exactly/ the same amounts of time when 
measured in impatience base units.


I have no idea how to make it faster or in any way how to improve the speed on 
a query that simple.

Knowing you (a bit) I already know you have thought about all of this a lot and you won't be asking if solutions were easy to come 
by, so my usual advice probably won't help much, other than to ask - do you really need to know the row-count? Is knowing it is 
around 300mil not enough? Any other query you might envision on this data-set will probably take in the order of hours for simple 
ones and days for anything joined. Best is to have a lone-standing machine churn through it over time and getting some results from 
time to time as a long-term project. (I know you are already well-aware of this).


Cheers and best of luck!
Ryan

(PS: I know the above isn't really helpful or qualified as an "answer", sorry 
about that - you are dealing with a special beast indeed).



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

Re: [sqlite] How to speed up database open

[RSmith]

On 2014/12/11 13:51, Paul wrote:

In my specific case I need to open database as fast as possible.
Usual working cycle: open -> select small data set -> close.
It is irrelevant how much time it takes to open database when
data is being added or updated, since it happens not too often.


/Snipped for brevity/

Hi Paul,

You seem to know your way around systems so I will not waste time on details. The core of the problem is that it takes time to open 
a database and file and extra cpu cycles because upon opening an SQLite database much cpu and I/O time is spent reading and 
interpreting the enitre schema (which sound complex even though the actual data might be small) and then checking for hot-journals, 
opening accompanying file objects (possibly WAL journals etc.) and some basic maintenance. It then loads the stat tables and not 
only read the data but use it to set up certain Query planner adjustments which eats a few more cycles (This is something I think 
happen on startup, but I might be wrong).  Also, I think start-up routines is surely not an area of waste but probably not a great 
focus of fine-tuning performance and optimizations (Richard or Dan might shed more light if I am wrong about this).  Ether way, once 
it is open, the speed is lightning quick, as you have noticed. The point being: It is the opening that eats the cpu time.


Next point is that you cannot do much about that in terms of your explanation of how you access data, and I won't try to dissuade 
you from the way you use it. One thing that I notice as a definitive possibility is simply dropping all stat tables from your system 
and at least saving those reading and adjusting steps. The reason why I suggest this is that you have already done the research and 
noticed the time degradation due to it, but more importantly, there is no need.


The stat tables help the Query planner (NGQP as it is officially known these days) to make decisions on making queries on large 
datasets a bit faster (sometimes a LOT faster). You however do not have any large datasets or performance-bending queries, you have 
no need for this, your need is more to save those cycles at file-open time. You need something we do not often see in database 
designs: Quantity over quality - and dropping the stat tables (and the maintenance routines causing them to exist) should do you a 
favour.


Best of luck with the implementation!
Ryan




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

Re: [sqlite] replace many rows with one

[RSmith]

On 2014/12/10 13:39, Simon Slavin wrote:

Dear folks,

A little SQL question for you.  The database file concerned is purely for data 
manipulation at the moment.  I can do anything I like to it, even at the schema 
level, without inconveniencing anyone.

I have a TABLE with about 300 million (sic.) entries in it, as follows:

CREATE TABLE s2 (a TEXT, b TEXT, theCount INTEGER)

There are numerous cases where two or more rows (up to a few thousand in some 
cases) have the same values for a and b.  I would like to merge those rows into 
one row with a 'theCount' which is the total of all the merged rows.  
Presumably I do something like

CREATE TABLE s2merged (a TEXT, b TEXT, theCount INTEGER)

INSERT INTO s2merged SELECT DISTINCT ... FROM s2


I think the one you are looking for is:

INSERT INTO s2merged SELECT a, b, sum(theCount) FROM s2 GROUP BY a,b;

Not sure if your theCount field already contains totals or if it just has 1's...  how did duplication happen? Should this be the 
case you might also be able to use simply:


INSERT INTO s2merged SELECT a, b, count() FROM s2 GROUP BY a,b;

Either way, the last query will obviously show the duplication counts (if 
needed as an exercise).

For 300 mil rows this will be rather quick if it's going to be a once-off thing and not something running often. I'd say it will 
take under an hour depending on hardware and how much duplication happened in s2.  Making an index will take a lot longer, you are 
better off just running the merge as above - unless of course the eventual use of s2merged includes being a look-up attached DB or 
such, in which case making an index from the start will be worthwhile.



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

Re: [sqlite] seeking advice

[RSmith]

On 2014/12/09 22:41, Rene Zaumseil wrote:

Hi there,

I have to store and retrieve up to 2000 parameters.
The parameters can have real and integer values.
The max. change rate is 100ms and the max. duration is up to some hours.

The simple solution would be to use plain binary files. It's fast but not
flexible.

So I came to sqlite. Before starting I would like to ask if someone could give
me some advice which way to go. Here are my current versions.

Version 1: One big table with time stamp and one column for each parameter
   - I can have up to SQLITE_MAX_COLUMNS=2000 but how can I insert all values
with only SQLITE_MAX_VARIABLE_NUMBER=999?
   - What about space consumption if NULL values are given?

Version 2: One table for each parameter with time stamp and value
   - Does this scale for up to 2000 parameters?
   - Can sqlite handle so much tables?

Version 3: One table with time stamp, parameter id and parameter value
   - Is it working when all values change?
   - Is retrieving values for one parameter fast?


Definitely No. 3 always. SQLite will retrieve a value from among 2000 items on an indexed column in under 1ms (as will any other 
localized DB). Even I/O lag will be masked by the cache at that size.


Assuming the parameter names do not change all the time and the time-stamp column is not indexed, writing will be similarly fast 
using a suitable journal mode. On this point, you might simply use an in-memory DB since you do not care about data loss on power 
failure (use ":memory:" as the file-name) which will be even faster but with an on-disk DB you will have a snapshot remain of the 
current parameter set if your application fails - which might assist debugging.


Also, making that DB, be sure to use "WITHOUT ROWID" and specifying the parameter-name as the primary key. Use Numeric affinity for 
time-stamp and value columns.


Cheers,
Ryan




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

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

[RSmith]

On 2014/12/09 03:36, David Barrett wrote:

Hi all, great questions:

*Re: Why VACUUM.*  We vacuum weekly.  This particular database is a
"rolling journal" -- we are constantly adding new rows to the end of the
table, and every week we truncate off the head of the journal to only keep
3M rows at the "tail".  Given that we're truncating the "head", without
vacuuming we'd be inserting the new rows at the "front" of the database
with the old rows at the "end" -- and then each truncation would leave the
database more and more fragmented.  Granted, this is on SSDs so the
fragmentation doesn't matter a *ton*, but it just adds up and gets worse
over time.  Anyway, agreed it's not the most important thing to do, but all
things being equal I'd like to do it if I can to keep things clean and
snappy.


Hi David - this is a completely unneeded step. SQLite will maintain it's internal structure, it doesn't do 'rolling' data usage and 
it will do so using the minimum IO. SQLite knows about people deleting and inserting, it will re-use and not waste, so no need to 
try and accomplish that. Only use Vacuum maybe after some months or such when you are doing all your proper DB maintenance - 
otherwise the performance gain will be negligible (and if you do experience some or other significant performance gain after a 
vacuum in your use-case, please let us know because something might be broken then).


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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[RSmith]

On 2014/12/08 15:58, Gwendal Roué wrote:
I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed 
without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a 
compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint 
checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, 
until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, 
Gwendal Roué 


Your new-ness is irrelevant, if you have a worthy argument it deserves being heard. To that end, let me just clarify that nobody was 
saying the idea of deferring the constraint checking is invalid or ludicrous (at least I had no such intention) and you make a valid 
point, especially since most other DB engines do work as you suggest - and this will be fixed in SQLite4 I believe, where 
backward-compatibility is not an issue.


The reason I (and others) will say it isn't a bug is because it isn't working different than is intended, or more specifically, than 
is documented. It works exactly like described - whether you or I agree with that paradigm or not is up to discussion but does not 
make it a "bug" as long as it works as described.


I hope the work-around you found works great!



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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[RSmith]

On 2014/12/08 11:55, Gwendal Roué wrote:

Hi,

Unique indexes make some valid update queries fail.

Please find below the SQL queries that lead to the unexpected error:

-- The `books` and `pages` tables implement a book with several pages.
-- Page ordering is implemented via the `position` column in the pages table.
-- A unique index makes sure two pages do not share the same position.
CREATE TABLE books (
 id INT PRIMARY KEY
 )
CREATE TABLE pages (
 book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE 
CASCADE,
 position INT
)
CREATE UNIQUE INDEX pagination ON pages(book_id, position)

-- Let's populate the tables with a single book and three pages:
INSERT INTO books VALUES (0);
INSERT INTO pages VALUES (0,0);
INSERT INTO pages VALUES (0,1);
INSERT INTO pages VALUES (0,2);

-- We want to insert a page between the pages at positions 0 and 1. So we have
-- to increment the positions of all pages after page 1.
-- Unfortunately, this query yields an error: "columns book_id, position are not 
unique"/

UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1;


NOT a bug...  the moment you SET position to position +1 for the first iteration of the query, it tries to make that entry look like 
(0,2) and there is of course at this point in time already an entry like (0,2).


Some engines allow you to defer the constraint checking until the end of the transaction (and you can do this for References, though 
you are cascading which is fine). In SQLite the check is immediate and will fail for the duplication attempted on the first 
iteration. The fact that the other record will eventually be changed to no longer cause a fail is irrelevant to the engine in a 
non-deferred checking.


Now that we have established it isn't a bug, some methods of working round this exist, like Updating in the reverse order (though 
this has to be done in code as the UPDATE function cannot be ordered). Also creating a temp table then substituting it after an 
update (but then you have to recreate the index anyway, so dropping the index and re-making it is better though this can take a long 
time on really large tables).


My favourite is simply running the query twice, once making the values 
negative, and once more fixing them, like this:

UPDATE pages SET position = ((position + 1) * -1) WHERE book_id = 0 AND position 
>= 1;
UPDATE pages SET position = abs(position) WHERE book_id = 0 AND position < 0;

No mess, no fuss, no Unique constraint problem.

Cheers,
Ryan


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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[RSmith]

On 2014/12/03 13:00, Jonathan Moules wrote:

Hi,
Just a quick request/suggestion.

Currently SQLITE_BUSY events return an error of "Database is locked". Is it possible to 
change this to "Database is busy" or something similar?
I ask because when someone then goes googling for "SQLite database locked", 
they'll end up thinking they're hitting the SQLITE_LOCKED event which as best I can tell 
is very different to the SQLITE_BUSY event.
I'm sure I can't be the only person who has been tripped up by that one.


I see your point, but is it not in principle the exact same thing? How is it "very different"? i.e. - Why else would a DB be 
"Locked" other than being explicitly busy? Or, why else would it be "Busy" other than being specifically locked?


Is there an event for which the DB can be "Locked" but not necessarily because it is busy?  If so I would like to second this 
request, but I am unaware currently that the case exists.


(Would the case where a DB was opened exclusively by another connection return 
a different error?  I have not checked this yet).

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

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

[RSmith]

On 2014/11/26 15:58, Darko Volaric wrote:
I'm not looking for confirmation of ideas, on the contrary, people seem to want to push their own ideas about a database should be 
used and how I'm not using it correctly, when that is irrlevent to the issue I'm discussing. Maybe more focus on the technical 
facts and less on divining what I think at night in bed and try framing your arguments based on those technical facts rather than 
ad-hominem attacks.


I am sincerely sorry if you construed my reply as anything remotely ad hominem - it surely wasn't, and it's not pushing ideas on 
you, it's giving advice or alternates because your ideas are short-sighted and hard to implement. We are however very nice on here 
since sharing knowledge is a passion, and that's why we say nice things like we understand how it feels to have ideas and then offer 
some advice... not because we spend our days divining about your life.  You throw everyone's advice back in their faces and are 
arrogant about it - well, even to that we are still nice and willing to answer the questions - kindly accept it in that spirit.




But I don't intend to argue this point any further since I'm merely looking for advice about how the database engine is 
implemented, not about how it's used or how I'm using it.


Ok, sticking to the facts, the database engine is implemented in a way that 
makes your original suggested options pan out like this:
   1 - very easy for the engine, very work intensive for you.
   2 - still easy for the engine, although it will lose most RDBMS querying value, and still cumbersome for you (Maybe best to use 
Virtual tables to implement this), and

   3 - impossible without a dedicated fork, and even then very difficult.

I wouldn't personally pick any of those, but if those were the only options in life and I had to pick one, knowing SQLite, I'd 
probably lean more towards option 2 than the others.


SQLite is "loosely typed", but it is still typed, and the typing mechanism is not open to the API and every one of the hundreds of 
core functions in SQLite are specifically coded to dance with those few primary types. Adding/Altering it must always be the very 
last option on any list.



Good luck,
Ryan

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

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

[RSmith]

Hi Darko,

Firstly, kindly keep this to the sqlite-users forum and not on the dev forum (the devs read this too, the difference being simply 
that this one exists to help you, the other one is to discuss development stuff, not to help anyone).


Secondly, you are confusing two things. You are arguing about what /CAN/ be done while James tried to explain what /Should/ be done. 
Nobody can argue with the possibility, but from experience we know that you are going down a road that will bite you later.


That said, the best way to achieve what you wish to do is by adding columns that explain the domain of a type on a per-row basis. 
The fact that inside of SQLite an Integer can be stored in different ways is simply a code/data/space optimisation for SQLite, it is 
transparent to the user and transparent to the SQL - it is in no way intended as a data-feature or extension of the SQL Language. If 
you try to build on top of that (or maybe not on top of it, but in the same way) more arbitrary sub-types, yes of course it is 
possible, but it may leave you in a World of hurt in terms of using the DB as a relational database system for other normal activities.


In contrast, if this is intended more as an application file-format than RDBMS, sure, make it better but, as others pointed out, 
still try to do so in a way that does not require maintaining a software fork. This is good advice and in no way forbidding or 
prescribing, just letting you know what will make life easier for you in the long run.


From the tone of your last post (if I am reading correctly) I understand that you have your mind set on finding a way that you have 
thought about a lot lying in your bed late at night, you have rolled it around in your head and you just "feel" this should be 
doable and will be so elegant a solution. We all go through that. You probably came here looking for confirmation of your ideas 
rather than advice, but many of the people here have already done what you are trying now, this is why they know and this is why 
this list is useful.


Y'know, use it, don't use it, etc. :)


On 2014/11/26 13:22, Darko Volaric wrote:

That's not true. A 64 bit floating point number and an 64 bit integer can
be represented by the exact same 64 bit pattern, and no-one would suggest
they're the same value. You can have those two differently typed although
identical bit values in the same SQLite column. The data identifying the
representation of those datums is integral to that value and doesn't belong
in a different column as you say. Other SQL based systems also allow
differently represented ("typed") values to appear in the same column.

The domain of a column can logically incorporate these different kinds of
values by introducing the concept of subtype. For instance in SQLite there
are something like 6 different representations of integer of between 1 and
64 bits. Each one of those is a different "type" in the sense of having a
different representation due to the number of bits they use and being
limited to a different set of numbers. A 1 bit integer is a subtype of a 64
bit integer since the set of numbers allowed by the 1 bit integer is {0, 1}
are contained with the set of numbers allowed by 64 bit integers, ie
{0..2^64-1}. If the column has a domain of integer then all these values
are valid since they're all integers. There is no logical or formal reason
why this can't be extended further to allow arbitrary subtypes according to
the user's wishes.

You can have the same 64 bits of data represent 4 different values in
SQLite: integer, double, string and blob (I'm assuming SQLite can store a
blob in 8 bytes). They are not treated as equal by SQLite because they have
different types. There is no reason why we should be limited to those 4
types in SQLite. Many SQL based system allow users to define their own
types. What I'm proposing is just implementing the same thing in SQLite.

There is nothing in the relational model that disallows this. You're
assuming that because columns have a domain or type, then that domain must
have a fixed representation in the database implementation. The relational
model says nothing about how the data is represented or what kind of data
can be stored.

On Tue, Nov 25, 2014 at 5:20 PM, James K. Lowden 
wrote:


On Tue, 25 Nov 2014 04:41:51 -0800
Darko Volaric  wrote:


I have a need to implement per-value custom typing in SQLite. In my
case I may have the integer value 1234 appear in two rows in the same
column, but they are different in that they have completely different
meanings because they have different types although they have the
same representation in the database.

A column is a type.  Two values of the same type *must* represent the
same thing.  That's how SQL works.

When you say 1234 may represent two different things in the same
column, what you really mean is that 1234 doesn't stand for the whole
thing, that the column isn't the whole type (as you conceive it).
That's fine; you need anot

Re: [sqlite] Serializing an object's vector or array using sqlite3 in c++

[RSmith]

On 2014/11/21 08:09, Thane Michael wrote:

Many of the answers I came across online implied that it wouldn't be as
straightforward as serializing ints and strings.


It would be equally difficult in any RDBMS, the difficulty does not lie with the Database, it lies with your objects. There are two 
general approaches for doing this with different advantages.


First and foremost is the method described by Clemens in another mail (so I won't dwell on the detail here), which is perfect for 
use in uncomplicated objects and which allows you to search the database and even build representations of entire objects just using 
some clever SQL (See: WITH RECURSIVE in the documentation). This is the method I would typically choose if my DB is intended to be a 
helpful search tool for objects as opposed to just storage.


Alternatively you can serialize anything object-like to XML or JSON and simply store the resulting string in the database. This 
allows you to not spend a lot on DB design and to use the same DB layout for any future changes to the base objects and save more 
since object-to-JSON etc. serializers are usually freely available and easy to code - but it takes away the ability to use fast SQL 
searches for specific properties etc.  I say "fast" because it is still possible to search using SQL wildcards and the LIKE 
statement etc, but in general that is slower and much more cumbersome than being able to access/check a field directly.


Of course a byte-stream serializer is most speed-and-size happy and can easily be saved as a BLOB in any RDBMS (including SQLite) 
but the coding is usually painstaking and the DB is useless as a search-tool unless you employ some virtual tables to present data, 
and even then it will be slow-ish because of the VT decode step. This method should only ever be used if speed and/or space is 
absolutely paramount, otherwise go with the others.



All of this said - I can find no reason why any of the above methods should be any more or less difficult on any specific platform 
or RDBMS... I am not sure what those "online" help you found refers to or maybe we are not understanding the question well.


Good luck!

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

Re: [sqlite] Insert Hindi Language

[RSmith]

On 2014/11/18 15:12, Richard Hipp wrote:

CREATE TABLE example(x TEXT);
INSERT INTO example(x) VALUES('हैलो, विश्व');
SELECT * FROM example;


The point being that Hindi isn't special, the presented characters are Unicode just like all the others - as long as your DB text 
encoding is set to UTF-8 or better (which is almost guaranteed), you can add any language characters. Fixing sorting collations for 
those languages is a different matter, but solutions do exist and are regularly seen here (if that becomes needed).


If you are using the sqlite_bind*16 type API in C or some high-level language, you might need to convert from the internal Unicode 
representations to the SQLite standard code-points as per the selected encoding (which is not an SQLite problem but because some 
languages use different internal representations or code points or translations than the standard). Most use Unicode16le MBCS 
internal memory and translate on the fly (because it is easier and fast) to UTF-8 etc, so if you simply move the bytes from memory, 
you might end up with every second byte being a Zero or similar which is of course incorrect for how SQLite expects the value. 
Linux, Windows and MacOS all have internal APIs for converting between most Unicode formats. I'm sure the same is true for mobile 
and other platforms, but have no direct experience to confirm it.


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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[RSmith]

On 2014/11/17 14:48, RP McMurphy wrote:
Upon further analysis it appears that the data "shape" is different in different periods within the table. That is, some sections 
have the inverse shape to other sections. So it looked like query times would change over time but actually they are changing 
because different portions of the table are being accessed. Is it possible to tell sqlite to analyze different sections and keep 
separate a record for each? 


Ok - this one is tricky - but I bet folks on here would love a go at cracking that. Can you post a moderate size section of the DB 
which includes the problem areas on a file-share somewhere (feel free to obscure the data if it is sensitive but try to keep the 
shape in tact - to be sure, whatever you produce should still cause the problem you described). Then post the queries that produce 
the bad timings (multiple to show the relevant discrepancies) and maybe some description of what it is you really want the intended 
result to look like (in case the queries produce somewhat different results to what you need).


Sounds like an interesting problem.

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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[RSmith]

On 2014/11/17 14:48, RP McMurphy wrote:
PS: Some administravia; Does anyone know of a way to reduce the posting delay for this list? Is it always like this? Or is it some 
problem with the gmane site in general?


Join the mailing list directly (you can unsubscribe once you got what you were 
looking for) at:

http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Not sure why gmane is slow or /if/ indeed gmane is the real culprit, but the list usually distributes posts within a minute (for 
non-digest users of course).



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

Re: [sqlite] Verifying a file is valid SQLite3

[RSmith]

Nvm - found it - thanks.

It seems the SQlite search engine is better than the Google one - Page ref (in 
case anyone else is interested):
https://www.sqlite.org/fileformat2.html#database_header
Specifically point 1.2.1


On 2014/11/16 15:03, RSmith wrote:
Could someone kindly tell me the byte-pattern, offset and length into an SQLite3 file that might suffice to verify that it is 
indeed an SQLite3 file.


I am not very concerned with false positives (as nobody can control all the files in the World), but rather interested in absolute 
negatives, i.e. if you do not find these exact bytes as this exact offset, then it definitely isn't an SQLite3 file.


If this is documented somewhere, a link would suffice - thanks!




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


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

[sqlite] Verifying a file is valid SQLite3

[RSmith]

Could someone kindly tell me the byte-pattern, offset and length into an SQLite3 file that might suffice to verify that it is indeed 
an SQLite3 file.


I am not very concerned with false positives (as nobody can control all the files in the World), but rather interested in absolute 
negatives, i.e. if you do not find these exact bytes as this exact offset, then it definitely isn't an SQLite3 file.


If this is documented somewhere, a link would suffice - thanks!




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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[RSmith]

On 2014/11/14 16:32, RP McMurphy wrote:


On Tue, 11/11/14, RP McMurphy  wrote:

  > If you can provide
  any examples where ANALYZE makes a query slower, I
  suspect the developer team would like
  > to see them.
  >
  
  After we run analyze and then

  let the process run for a while the DB
  contents change - and it can change quite
  considerably depending
  upon what is
  happening. I suspect that the analyze data gets stale, but
  I
  don't know how to track such things in
  sqlite. Anyhow we can't keep running
  analyze every few minutes because it takes a
  long time to run with our DB
  and it appears
  to block all other actions until it is done.


Firstly, that is a considerably different problem to your initial claim that ANALYZE makes queries slower. That said, it is an 
understandable concern.  When you say the data changes, does the shape actually change? Because all that matters to analyze is the 
shape of the data, not the data itself.


As an example, consider the following three small tables:

   A:
   ID, No, Name,   Value, XT,  AX
   1,  1,  John,   34,Panthera Leo,2014-11-12 09:44:26
   2,  2,  Chris,  88,Panthera Pardis, 2014-11-13 04:17:43
   3,  3,  Martin, 20, etc...

   B:
   ID, No, Name,  Value, XT,   AX
   1,  1,  John,  34,,
   2,  X1, John,  ,  Panthera Leo,
   3,  A1, John,  ,  , 2014-11-12 09:44:26
   4,  2,  Chris, 88,,
   5,  X2, Chris, ,  Panthera Pardis,
   6,  A2, Chris, ,  , 2014-11-13 04:17:43
   etc.

   C:
   ID,  No, Name, Value, XT,  AX
   106, 62, Andrew,   18,Loxodonta Africana,  -00-00 00:00:00
   107, 677,James,12,Podocarpus Falcatus, 2014-11-09 14:27:12
   108, 176612, Flemming, 40,Tectonia Grandis,2014-11-14 03:31:55
   etc.



From table A to B the data did not change significantly, but the shape of it changed significantly in respect of cardinality, value 
types, fill rate etc.  From A to C the data is completely different, but the shape is exactly the same.


Running Analyze for table A which shape-shifts into B over time (which is highly unlikely) will certainly see the values become 
"stale". Running Analyze for A which eventually changes into C (very likely) will have minimal effect on the outcome of the Analyze 
data and hence on the outcome of the Query Planner decisions.


To re-iterate the question posed earlier - If you can show that the QP makes a worse decision after analyzing a table - OR - that 
the decision degrades over time for a table of which the shape does not change effectively - I am sure the case would be considered 
as undesirable and the devs would probably be interested in doing something about it.


Other options include forcing the use of an Index or Indices (see "USING" in 
the SQLite SQL docs) or using cross-joins and the like.



  A this point we are
  considering writing VDBE code directly and bypassing the
  parser. Has anyone else done this? Is it going
  to be a huge ugly
  can-of-worms if we do
  that?


Not just a can of worms - an insane one, and what is worse, you will immediately lose any benefit of periodic updates and all the 
other advances in technology so frequently visited upon SQLite because your version is no longer interchangeable with the new ones. 
Further to that, this speaks of the willingness to take 3 steps in the wrong direction to avoid one step in the right direction - 
maybe some past experience influence or some other unknowns which are not immediately obvious now is influencing this thought? (I 
only ask because this is not uncommon).


If you do have a table of which the shape keeps changing to the point it needs re-analyzing every few minutes, then you are working 
with the worst DB in history and should really make your own native storage solution - but I am 90% certain this is not the case and 
you are simply overlooking something or not doing something efficiently - which we will be glad to help with if you post schemata 
and maybe link an example DB or such.


Unlike me, some other people on here are extremely knowledgeable on SQL and specifically the inner workings of SQLite - Tell us the 
full story, we might save you hundreds of hours.


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

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

[RSmith]

On 2014/11/13 19:06, Simon Slavin wrote:



On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin  wrote:


100,000,000,000,000,000,000

But he's using the field to store an amount of money in.  So why ask for 
anything with ten places after the decimal point ?  No genuine currency 
requires more than three places.


He mightn't be storing the actual money, but the working factors and figures used in the formulas seeding the money calculations, 
conversions, etc. which may have a bit higher accuracy required. Though if this is the case, I would suggest getting back to your 
original suggestion of saving the money as integer cents (or milli-dollars if you like) and storing the factors and figures as good 
old 64 bit IEEE floats which will give you up to 15 digits after the decimal point accurately, twice the required accuracy - not to 
mention the luxury of being able to access both types natively in the api /and/ represent it easily in human-readable format in data 
dumps.


By the way, my Oracle friends should intersect here if need be, but I believe the oracle method of /decimal(n,m)/ is simply a 
representation directive and constraint, there is no native datatype that actually stores or communicates such a value. Oracle 
stores it internally in a very specific arrangement of bytes and you need to still interpret it in your software.


Nor do I think the Oracle SQL engine would be able to do (without any add-on 
modules):
SELECT (B.Money*B.Factor) FROM BigMoney B;
from said table with Money and Factor both as decimal(38,6) each containing >30 
decimals - or would it?

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

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

[RSmith]

On 2014/11/13 15:01, Dinesh Navsupe wrote:

Hi,

My need is 23 decimal digits of precision. We work on complex payout
calculation engine where in formula outputs are quite large numbers and
clients do not want to round off.


I do not think that re-stating your need suffices as a good enough argument. We heard you the first time and understand the need and 
understand exactly that you are working with numbers in that precision so you would like to be able to store them efficiently. 
SQLite does not do this in binary terms (it can produce 64 bit Integers/Reals at best in accordance with IEEE as some others already 
explained). No other Engine does this natively either (meaning that in your code there is no way to exchange that size number with 
the prepared statement/api/object within a standard C data type other than a string or blob). Using strings is preferable because 
they will be human readable in DB dumps, but you will need to use a BigInt type library or create your own conversion routines to 
actually translate those numbers to and from strings for the purposes of communicating it to/from any DB engine, SQLite included.




We want to use SQLite for local disk data store and calculations.


That's a good decision, but it comes with a bit of work, same as any other 
chosen RDBMS.

As an aside, do you really need that precision? You can accurately state the American National Debt in dollars and cents within 16 
digits of precision (That's 18 trillion dollars btw, or 18^12). I can't imagine a payout ever needing to be much higher than that, 
or by another 8 or so digits more precise? (well, that would allow you to express the entire World's collective national debts in 
Zimbabwe dollars). 16 digits can very well be represented by a Float in SQLite and transferred to your program in standard 64-bit 
floats via the API. Above that you will need to make the routines.



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

Re: [sqlite] Locking errors on network

[RSmith]

On 2014/11/10 20:22, Mike McWhinney wrote:

So SQLite shouldn't be used at all on a network?  Aren't there any other 
provisions to handled the locking errors if/when
they occur?


It is not about SQLite, it is about the Networking systems lying about whether a file is locked or not. No RDBMS can trust the 
network, but the client-server types do not care since they control the locking and do not depend on the OS / file status. SQLite 
however depends on it and as such cannot accurately (or timeously I should say) verify such status via a Network. On a local drive 
this is never a problem.


If you need Networking or User-control, please use a client-server type 
database.

There is one Client-Server implementation of SQLite (SQLightening I think) but it is neither free nor easy to convert to. You can 
write your own server too, but the best bet is using MySQL or PostGres in these cases.



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

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

[RSmith]

On 2014/11/09 14:11, Tristan Van Berkom wrote:
A good SQL rule of thumb: if you can think of a way, so can the DBMS. "... no opportunity to make a good guess" is not true. In 
some sense, SQLite has had 10 years to make a good guess, and often does. A nested select need not be materialized as a "table", 
opaque or otherwise. It can be converted, or "flattened" under some circumstances. SQLite's query planner isn't the most 
sophisticated; no one suggests otherwise. It does not follow that every JOIN will outperform every EXISTS or vice versa. 

Indeed this is a large misconception on my part, on the queries which
I *have* profiled, it did turn out that JOINs were more effective than
solutions which involve nesting select statements.


This seems like the appropriate place to interject with a Statistics / 
Sample-size comment, but I think the point is obvious.


Anyway, I appreciate the input and will try to accept that I should not
be in control of how the query is run - I was under the impression that
SQL engines can perform better when given more context about how the
query should run (i.e. being more explicit with JOINs), but I do agree
that, at least ideally, the planner should be able to make a better
guess as to how to plot a query with a more relaxed/vague statement,
than with a more explicit one.


Woah... this is not at all what James tried to say and it might just be that your choice of words is unfortunate and you did mean it 
correctly, but just to be sure, allow me to elaborate somewhat:


The query should in no way be "Vague" or "Relaxed", it should be precise, succint, exact and fully correct making the intended 
result impossible to misinterpret. What it should not be is convoluted with additional hot air that attempts to better explain to 
the QP how to do the query... that is where we trust the QP to do it's job as long as the question is not Vague and indeed very 
precise and concise.


Sometimes the Query planner is faced with situations where an algebraically correct result can be achieved via different ways 
(picking/using an Index (if any) is a good case-in-point) and sometimes the best one is not obvious from the query or table size. In 
these cases, providing hints to it - such as running Analyze (Stat3 or 4 tables) and providing direct hints (such as 'Likely' and 
'Likelihood') - is a good way for a programmer (or "Query Author" I should say) to /help/ the QP decide on what is best - but these 
should be used sparingly and only where the improvement is substantial and the optimisation not premature.


Another consideration is that the QP gets updated almost every other month... you have no way of knowing whether your convoluted 
query (which may shave off a few milliseconds now) will still be the fastest next month when the QP is a bit smarter. The long-term 
best is to formulate the most precise and concise query possible and let the Engine do the work.



Cheers,
Ryan.


(PS: I don't mean to second-guess James, and the only reason I feel any comfort saying what he meant is that we just had an 
interesting debate about this elsewhere and I have come to appreciate the view above somewhat more).





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

Re: [sqlite] bug: "no such column" with UNION + subquery + GROUP BY + ORDER BY + COLLATE

[RSmith]

On 2014/11/08 14:21, Clemens Ladisch wrote:

Hi,

the following query fails to parse, although it should be valid:

   sqlite> select 1 union select 1 from (select 1 as x) group by x order by 1 
collate binary;
   Error: no such column: x

Will call the above  Version A.

I don't see how that can ever be valid - the "as x" pertains to a sub-query not referenced by that column in the higher query and 
without any alias to the derived table which makes for serious lack of inferred referencing.  It doesn't parse in MSSQL and doesn't 
parse in MySQL either with errors that pertain to aliasing the derived table (as expected) [1].  I believe a valid way to put that 
would be:


Version B:  select 1 union select 1 from (select 1 as x) as t group by x order 
by 1 collate binary;
since that added "as t" now qualifies the derived table removing the ambiguity 
and make x referencable (if there is such a word) outside in the precise higher query 
that is trying to do the grouping.

or perhaps something like:

Version C:   select 1 union select 1 from (select 1 as x group by x) as t order 
by 1 collate binary;
since the alias and the reference to it is again contained in a single 
query-level.

Both versions B and C work in all of SQLite, MSSQL and MySQL, as it should. Of course SQL is not defined by "what other Engines do" 
but I cannot seem to find a reference in the SQL-92 standard that mandates version A either (though, to be fair, I was speed-reading 
to the seemingly relevant parts and might have missed it).



Not a bug I think.


PS: Did not have a PostGres DB handy to check, but I believe it will behave similarly - would love to know actually, if someone 
would be so kind...



[1] Have replaced the "collate binary" with appropriate clauses for the other 
engines.

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

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

[RSmith]

On 2014/11/05 15:26, David Woodhouse wrote:

On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote:
I don't think it's anything to do with the table data being special, is it? Isn't it generically true that for any LEFT JOIN of 
a,b WHERE b.anything IS NOT NULL, the results are going to be equal with an INNER JOIN?


Yes, I was simply pointing to the fact that if indeed you had NULL values and omit the specific WHERE clause the results will depend 
on your table. You also need to look through the entire A table in a Left join because NULLs in the B table does not disqualify 
them, unless of course there is specifically either a NOT NULL or ColVal='Someting Specific'. I am now not sure if these would be 
the only set of cases for the optimisation, but they are the only ones coming to mind currently.



Or we could hope that the SQL database has a query planner of its own which can 
do such optimisations... :)


Quite a reasonable request if the optimisation can be boiled down to a specific always working set of rules. As I mention above, the 
ones coming to mind is that the WHERE clause is simple and specifically excludes NULLs. Even if NULLs are not specifically excluded, 
the column schema might include "NOT NULL" for that column which may also incur the optimisation (if it doesn't already), or even 
have the sqlite_statx analyze point out column that do not contain any nulls, or rather not as that may change. I know there is an 
optimisation step in SQLite NGQP that promotes ON clauses to WHERE clause statements (except for Left Joins for this reason), though 
more complex queries might have some caveats which eludes me currently. Also if several left-joins exist in the same query, a light 
implementation of this optimisation might influence the truth of the result (and of course I am not implying that the implementation 
would be light, just that the optimisation might need a lot of work). It also doesn't affect people who know when to not use a left 
join, but that is never a reason not to implement an obvious optimisation (as your use case illustrates).


In fact this may allow much wider use of Left-joins which is always the preferred join method system-side because it doesn't hide 
missing/unlinked items. Ok, I convinced myself, +1 to the request. :)


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

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

[RSmith]

On 2014/11/05 14:13, vita...@yourcmc.ru wrote:

Hi!

After playing a little with SQLite as a DBMS for Bugzilla, I've discovered that LEFT/INNER join affects query plan in a bad way 
even for semantically equal queries:


SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to WHERE 
p.login_name='vita...@yourcmc.ru'

Query plan:
SEARCH TABLE profiles AS p USING INDEX profiles_login_name_idx (login_name=?)
  SEARCH TABLE bugs AS b USING INDEX bugs_assigned_to_idx (assigned_to=?)

But

SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to WHERE 
p.login_name='vita...@yourcmc.ru'

Query plan:
SCAN TABLE bugs AS b
  SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?)

Which is of course very slow.


These queries are very different, not equal in any way (semantically or otherwise), the fact that they produce the exact same answer 
is simply by virtue of your WHERE clause being specifically that and your table data being being special. Drop the where clause and 
they produce very different results for different table data.


To clarify - Producing the same results does not imply semantic equality, if I asked you to find me some people of a specific kind 
in a specific bus, and someone else asks you to find the same type of people in the New-York downtown traffic... it "may" end up 
that those just happen to be the exact same people at the end, but the methods which you employ to look for those people will be 
significantly different, the result coinciding does not make the syntax semantically equal.


Telling SQL (of any flavour) to "Left join" is asking for a very different method of computation than when asking it to "inner join" 
and it should use the best tools for the request, it cannot see into the future to know the result will be fine if it stealthily 
just does the other method. (Though Analyze might help it to see a bit clearer and take that decision differently).


Why would you use left join for that? Maybe this is just a really small extract from the real query you are doing and the real one 
would shed more light?


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

Re: [sqlite] x64 vs x32 DLL

[RSmith]

On 2014/11/04 21:34, jose isaias cabrera wrote:

Greetings!

I have an application that is written for x32 machines.  However, we now have a 
few machines that are x64 and all is well when we are using the precompiled x32 
DLLs provided by http://www.sqlite.org/download.html.  Changing the x32 DLLs to 
x64 DLLs, will it show any difference in speed on the x64 machines?  Thanks.


Yeah, might even be slower, but so negligibly that you won't notice.

Important to note (which many people seem to miss) is that the step up from 32 to 64 bit computing is an increase in computing 
accuracy and capacity, not in speed, though computations on historic 64 bit variables (which used to be split up into two or more 
values to work) might be faster, but they generally make up less than 1% of the computations in any sane software system (though 
exceptions do exist) and so the speed increase is usually miniscule.  On the downside, a 64-bit system uses 64 bit values for all 
registers even where 32-bit values could have sufficed, and so all internal looping through bits or register shifts take longer, but 
generally by negligible amounts. (All modern processors are 64 bit anyway - 32 bit OSes still exist for legacy reasons only).


It isn't a magic boost - The best way to think of the 64 bit upgrade in normal programming is: "Able to do larger accuracy 
calculations at more or less the same speed".



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

Re: [sqlite] Search query alternatives.

[RSmith]

On 2014/10/16 00:05, Michael Falconer wrote:

Hi all,

first off I must start with an apology. I know I'm sort of doing the wrong//...


No need to apologise, this flies quite close to the central theme. Whether you are using SQLite or any other SQL RDBMS, this is 
horrible DB design and it is so for precisely the reasons you are asking the question.  Tables should not be dynamic: The simple 
rule of thumb being - if you cannot tell (or at least accurately predict) before-hand exactly how many tables will be in the final 
system, then you are doing it wrong.  Why not have one set of tables and in stead of preceding each of them with a , simply 
add a column that can host the  as a simple value, which will immediately make your life very much easier and get the SQL 
engine to do the work you are now trying to compensate for in a lop-sided manual kind of way.


I can only imagine the amount of code you invested into your system to track and deal with these dynamic sets of tables, so I know 
changing it will seem like a rather large undertaking, but really it will mostly involve removing loads of code to end up with a 
small set of simple queries that does all the work for you.


The only reason I can imagine this sort of breakdown useful is if your tree tables are all really really huge, like Gigabytes, and 
so there might be some efficiency to be gained from splitting it up, but I doubt this is the case.


Just imagine the ease of those search queries you demonstrated... one query, no UNIONs, a single set of joins and an extra 
where-clause check... Add to that increased efficiency at finding results (run one query in stead of 50).  Easy-mode. As a 
proof-of-concept, just make an SQLite DB, one set of tables as per your list, add column(s) for prefixing as needed, import all the 
tables from some large set of them (specifying additionally the column for the prefix) and ten run search queries on them. I am 
willing to bet on the fact it will be much quicker in addition to being much simpler.


If you cannot change the schemata to be more suitable, then your demonstrated queries are very near as good as it gets. I can think 
of other ways to achieve the same, but nothing that would be more efficient to a tangible level. Maybe someone else here can come up 
with something a bit more tight.



thing here as this question is NOT related to sqlite. It is a general SQL
question but I ask it here because I have great respect for the answers and
discussions I have seen on this forum over many years. I rarely post myself
as there are always several contributors who beat me to the answer and
often their response is far better than mine would have been. I'm not a
code leper, I don't need the actual SQL just the method really, though a
short code example would be well received for illustration.

It's about a search performed on multiple tables. However the structure of
this database is somewhat unorthodox. It contains genealogical data and
this is clustered into trees. Each tree has 8 tables, there can be as many
as 100 trees but most installations of the associated application software
contain between 5 - 50 trees. These 8 tables contain a family trees data
and are named:
addresses
connections
events
family
person
repositories
sources
texts

The  changes and is unique for each tree.

There are extensive search options offered to users as well as simple name
searching and it is one of the best features of the app. It works pretty
well, so it ain't broke in any sense, we just wonder if there is a better
way to perform this search in SQL. Is there a general technique which is
superior either in speed, efficiency or load bearing contexts?

I am sure you can see one of the pitfalls here is the exponential growth of
such a search query as both total number of trees and indeed user search
criteria increase. For each criteria component, and there are quite a few,
the appropriate tables must be queried and results joined.

Searches return records of individuals meeting the entered search criteria
so the query focuses on the person table as it's anchor, performs searches
on required other tables in the tree and joins the results to the person
data. The results from each tree search are then UNION'ed to provide the
result set. Here is a contrived example of the SQL query code which should
make things clearer.

The user enters simple search criteria - any person with a last name
containing 'mac' and a first name containing the character 'a'. This is the
resulting query (generated by php code), which searches a small 4 family
tree installation.

(SELECT humo1_person.*, event_kind, event_event, address_place, address_zip
 FROM humo1_person
 LEFT JOIN humo1_events ON pers_gedcomnumber=event_person_id
 LEFT JOIN humo1_addresses ON pers_gedcomnumber=address_person_id
 WHERE pers_lastname LIKE '%mac%'
   AND (pers_firstname LIKE '%a%'
   OR (event_kind='name' AND event_event LIKE '%a%')

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

[RSmith]

On 2014/10/14 16:19, Clemens Ladisch wrote:

RSmith wrote:

On 2014/10/14 13:09, Clemens Ladisch wrote:

SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, *
FROM (SELECT ...);

This solution from Clemens will work perfectly, and depending on the
kind of OS you use and output method it might even work to add
something like '-\n' or '\r\n' as the Inserted item if
char(10) causes headaches.

   sqlite> select '-\n';
   -\n

That's not very useful.  ;-)

and...

On output produced for Windows systems that should also be
char(13) || char(10).

The sqlite3 shell uses the C runtime conventions, where stdout is in
text mode, so char(10) is correct even on Windows.


Indeed on both counts, but you assume he is using the sqlite3 shell and stdout...  I said "might" be needed - if he produces, say 
for argument's sake, a csv-like file that will be opened by some text editor on Windows, the chr(13) is needed, and if he produces 
anything like a XML file or via script in Python or PHP or such the \r and \n (or even  for HTML) will work as intended.


Not all of us are so pro as to only use queries in the Linux command-line ;)


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

Re: [sqlite] Make a database read-only?

[RSmith]

On 2014/10/14 13:48, Ross Altman wrote:

Hi Martin,

Thank you, I'll definitely look into that. It's unfortunate that there
isn't a simpler way to do this... oh well.


Let me bud in here since I encounter this question a lot in other matters. There typically are three reasons one would like to 
protect the data in a file from end-users' meddling:

  - You need to protect idiot users against themselves,
  - You need the data to remain clean and untarnished to make some other system 
depending on it function correctly, or
  - The data itself is important for legal reasons or you have some kind of 
liability towards data accuracy.

If it is the first case, then you are stuffed and Richard's byte-change is the 
closest to a solution you can come.

If the second case, then make the other system check the file, add table with encrypted values that has meaning only to the other 
system, or even use file encryption for the entire database - this is common and can be had commercially from 
http://www.hwaci.com/sw/sqlite/see.html


For the latter I suggest recording the file hash (sha512+) whenever you update it and store that in a data list marking release 
dates. That way if someone claims that they have data gotten from you that says x while you claim it says y...  then simply whip out 
the hash list and compare to their file, any changes will be evident immediately.


You probably need to then also keep a register history of DBs that correspond to those hashes, else you cannot prove the data from 
that file to correspond to any specific hash. Also it is safer to upload such hashes to a blog or something that is not under your 
control, where any edits will be marked and timestamped, then it is impossible for yourself to meddle with the files after release 
and a public record exists of the file version hashes. Pretty solid in legal terms.


Whichever way, good luck!
Ryan


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

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

[RSmith]

On 2014/10/14 13:09, Clemens Ladisch wrote:

Paul Sanderson wrote:

SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, *
FROM (SELECT ...);


This solution from Clemens will work perfectly, and depending on the kind of OS you use and output method it might even work to add 
something like '-\n' or '\r\n' as the Inserted item if char(10) causes headaches. On output produced for Windows systems 
that should also be  char(13) || char(10).


Have a great day!
Ryan

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

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

[RSmith]

And of course there must be a LIMIT 10 added to every Union'd select (which I 
forgot):
SELECT * FROM ReportJobLengths R WHERE GroupName like 
'GRP01%' LIMIT 10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP04%' LIMIT 
10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP12%' LIMIT 
10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP15%' LIMIT 
10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP20%' LIMIT 
10;

etc.

On 2014/10/14 09:22, RSmith wrote:


On 2014/10/13 23:21, pihu...@free.fr wrote:

Hello!

I'm trying to find a way to reduce the length of the following query using 
SQLite:

select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from 
ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP01%' ORDER BY Length DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from 
ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP04%' ORDER BY Length DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from 
ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP12%' ORDER BY Length DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from 
ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP15%' ORDER BY Length DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from 
ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP20%' ORDER BY Length DESC LIMIT 10);


I want to select the ten longest jobs for five different groups (GRP01%, GRP04%, GRP12%, GRP15% and GRP20%). I can't find a 
solution without using this "UNION ALL" trick.


There is nothing wrong with this query, and the UNION ALL "trick" exists exactly for this purpose. I know it seems a bit 
convoluted but it achieves the goal and it should be real fast. To get rid of the convolution I would implement a view, either 
temporary or permanent, something like this:


CREATE TEMPORARY VIEW ReportJobLengths AS
  SELECT GroupName, JobName, Start, End, Status,
 (strftime('%s', End) - strftime('%s', Start)) as Length DESC
  FROM ReportJobs where PlanDate = '2014-02-13'
  ORDER BY Length;

  SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP01%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP04%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP12%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP15%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP20%';

You may also create a persistent view and then always use it (without the 
"temporary" keyword).

To get even more compact, I would go with Igor's SQL which is quite succint, but if those tables are big, that query will run 
quite a bit slower - which is only a problem if the speed really matters.



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


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

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

[RSmith]

On 2014/10/13 23:21, pihu...@free.fr wrote:

Hello!

I'm trying to find a way to reduce the length of the following query using 
SQLite:

select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP01%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP04%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP12%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP15%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP20%' ORDER BY Length DESC LIMIT 10);

I want to select the ten longest jobs for five different groups (GRP01%, GRP04%, GRP12%, 
GRP15% and GRP20%). I can't find a solution without using this "UNION ALL" 
trick.


There is nothing wrong with this query, and the UNION ALL "trick" exists exactly for this purpose. I know it seems a bit convoluted 
but it achieves the goal and it should be real fast. To get rid of the convolution I would implement a view, either temporary or 
permanent, something like this:


CREATE TEMPORARY VIEW ReportJobLengths AS
  SELECT GroupName, JobName, Start, End, Status,
 (strftime('%s', End) - strftime('%s', Start)) as Length DESC
  FROM ReportJobs where PlanDate = '2014-02-13'
  ORDER BY Length;

  SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP01%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP04%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP12%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP15%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP20%';

You may also create a persistent view and then always use it (without the 
"temporary" keyword).

To get even more compact, I would go with Igor's SQL which is quite succint, but if those tables are big, that query will run quite 
a bit slower - which is only a problem if the speed really matters.



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

Re: [sqlite] decoding a bitmask

[RSmith]

On 2014/10/13 15:39, Paul Sanderson wrote:

Thanks all

Clemens - I went initially for your solution as it fitsbetter with some
other work i have done

My actual code is as folows

   (CASE visits.transition & 0xFF00  WHEN 0x0080 THEN 'Blocked'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0100 THEN
'Forward_Back'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0200 THEN
'From_Address_Bar' ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0400 THEN 'Home_Page'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x0800 THEN 'From_API'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x1000 THEN 'Chain_Start'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x2000 THEN 'Chain_end'
 ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x4000 THEN
'Client_Redirect'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0x8000 THEN
'Server_Redirect'  ELSE '' END ||
CASE visits.transition & 0xFF00  WHEN 0xC000 THEN
'Is_Redirect_Mask' ELSE '' END )
   AS Qualifiers

The query is on a visits table from a google chrome history database. The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?


Rookie mistake :)

You are basically bitwise AND-ing 2 values together, such as (visits.transition & 0xFF00) and then checking the result against a 
single bit... this will never work unless the transition value has only a single bit set, I think you meant to AND the mask with the 
transition value and then check if the result is non-zero?


This works perfectly for me:

  (CASE WHEN (visits.transition & 0x0080)>0 THEN 'Blocked'
ELSE '' END ||... etc. etc.
 



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

Re: [sqlite] decoding a bitmask

[RSmith]

On 2014/10/13 16:51, Mark Lawrence wrote:

On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote:

The query is on a visits table from a google chrome history database. The
query seems to work OK if a single bit is set, but fails (a blank string is
returned) when multiple bits are set. Any ideas why?

I suspect it is a bug with multiple bitwise ORs. Demonstration:

 WITH x
 AS (
 SELECT
 0x0080 | 0x0800 AS a
 )
 SELECT
 a & 0x0080,
 a & 0x0800,
 a & 0x0080 & 0x0800
 FROM
 x
 ;

Result:

 a & 0x0080  a & 0x0800  a & 0x0080 & 0x0800
 --  --  ---
 8388608 134217728   0


Those look like exactly the correct results to me, how did you expect the 
result to be?



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

Re: [sqlite] decoding a bitmask

[RSmith]

On 2014/10/13 13:52, Paul Sanderson wrote:

I have a table with an integer value which is a bitmask. one or more of the
bits can be set and each bit has a corresponding meaning.

so using the windows file attribute as an example we have

0c01 readonly
0x02 hidden
0x04 system
0x10 directory
0x20 archive

none, any or all could be set

I'd like to create a query which would take an attribute, say 0x07 and spit
out "system, hidden, readonly"


Or you could make a table like this:

CREATE TABLE "fileFlags" (
"ID" INTEGER PRIMARY KEY,
 "Descr" TEXT
);

Populate it with the Mask descriptions like so:

INSERT INTO "fileFlags" ("ID","Descr") VALUES
(1,'readonly'),
(2,'hidden'),
(4,'system'),
(8,'unknown'),
(16,'directory'),
(32,'archive');

And then any query of the form:

SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & [BitValue])>0;

Will work, as the following tests shown:

SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & 11)>0
readonly
hidden
unknown


SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & 7)>0
readonly
hidden
system

SELECT F.Descr FROM fileFlags AS F WHERE (F.ID & 4)>0
system

etc.

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

Re: [sqlite] Stored Procedures

[RSmith]

On 2014/10/09 19:04, Omprakash Kolluri wrote:

Hi,

I am new to SQLite. I am working on an app that I am developing and plan to
use SQLite as an embedded database. My Question - Does SQLite support
stored procedures similar to those in MS SQL Server etc. Any suggestions OR
pointers to information links woill be greatly appreciated. Thank you


Hi Om,

Not directly since it is at the API level very integrated and integratable with any C or other common compiler languages, so doing 
anything procedural is usually an easy task, and as the name implies it is "Lite" which means some 
higher-CPU-Cycle-and-Memory-consumption additions are foregone specifically so it could work well on embedded systems, as you seem 
to be implementing.


A good read in this regard would be here:
http://www.sqlite.org/whentouse.html

Check out the recent CTE additions which does allow a level of procedural 
querying here:
http://www.sqlite.org/lang_with.html#rcex2

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

Re: [sqlite] Single large table vs. several smaller tables

[RSmith]

There is no point to grouping similar fields in different tables, though I would advise grouping them together in the main table as 
a simple case for clarity, but it has no other benefit. As long as the data is in 1NF, one table is fine. The main reason (and I 
think only motivation) to have data in seperate tables is if some column or set of columns repeats the very same information over 
and over, or if you have multiple-linking of sub data items to a main index - none of which seems to be the case in your table, all 
the data is needed and possibly quite different for every test/entry.


Another reason people might use different tables is when you have large text or blobs that might make querying a bit faster if the 
main table has a simple smaller fieldset and then references another table 1-to-1 when retrieving the very large data - again, 
seemingly not the case for you.


Any other breaking up of a table is unneeded complication - if the data belongs together, keep it together - though if someone else 
has a thought on why it is reasonable to split up such data, I'd be interested to hear it too.


Have a great day!
Ryan


On 2014/10/09 16:25, Drago, William @ MWG - NARDAEAST wrote:

All,

An automatic test system that I designed generates 25 data elements for each 
unit tested. We test about 50 units/day. This data is currently being stored in 
a csv file. I'd like to move this over to an SQLite database.

The question I have is, should I lump everything together in one table just 
like the .csv file or should I create several smaller tables that group similar 
parameters? I'm not sure what would normally be done. I think the database is 
normalized properly in either case.

Existing records will never be modified once they are inserted. We will 
occasionally read the database to gather statistical information.

I'd like to get your input before I commit to anything that I might regret 
later.

Thanks,
-Bill


** Single table **

CREATE TABLE UUT(
 DatasetID INTEGER PRIMARY KEY,
 Location TEXT,
 ModelNumber TEXT,
 SerialNumber TEXT,
 TestDate TEXT,
 ATE TEXT,
 CalStatus TEXT,
 Par0 TEXT,
 LFvOffset REAL,
 LFv10 REAL,
 LFv20 REAL,
 LFv50 REAL,
 LFv100 REAL,
 LFv200 REAL,
 HFvOffset REAL,
 HFv10 REAL,
 HFv20 REAL,
 HFv50 REAL,
 HFv100 REAL,
 HFv200 REAL,
 LFTarget REAL,
 LFSource REAL,
 LFPind REAL,
 HFTarget REAL,
 HFSource REAL,
 HFPind REAL
);



** Multiple Tables **

CREATE TABLE UUT(
 DatasetID INTEGER PRIMARY KEY,
 Location TEXT,
 ModelNumber TEXT,
 SerialNumber TEXT,
 TestDate TEXT,
 ATE TEXT,
 CalStatus,
 Par0,
);

CREATE TABLE LFCal(
 DatasetID INTEGER REFERENCES UUT,
 vOffset REAL,
 v10 REAL,
 v20 REAL,
 v50 REAL,
 v100 REAL,
 v200 REAL
);

CREATE TABLE HFCal(
 DatasetID INTEGER REFERENCES UUT,
 vOffset REAL,
 v10 REAL,
 v20 REAL,
 v50 REAL,
 v100 REAL,
 v200 REAL
);

CREATE TABLE LFPowerLevels(
 DatasetID INTEGER REFERENCES UUT,
 Target REAL,
 Source REAL,
 Pind
);

CREATE TABLE HFPowerLevels(
 DatasetID INTEGER REFERENCES UUT,
 Target REAL,
 Source REAL,
 Pind
);

--
Bill Drago
Senior Engineer
L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any at

Re: [sqlite] Is there equivalent to MySQL IF() function?

[RSmith]

On 2014/10/07 13:20, Tony Papadimitriou wrote:
Well, it is exactly because I understand the difference between a boolean expression and a non-boolean expression, along with a 
bit misleading documentation, that I got confused.


It is usually those who are used to only the C-like treatment of a boolean result as being equivalent to an integer result that 
have no problem with this interpretation. :)


Thank you for clarifying.


There was no malice in the statement, just an honest check.

The fact that it /can/ evaluate to an integer 1 or 0 is coincidental and have nothing to do with the premise I tried to explain, I 
could have used the words ON and OFF, or TRUE and FALSE, or YES and NO... or, as I did, 1 and 0... a Boolean value is dichotomous 
and has no other possible state than the base 2 states - so evaluating it and comparing it to an integer supporting 2^64 states or 
indeed any other type of multi-value field is not useful, and more pertinently, does not work in a CASE statement (or IF() statement 
for that matter) where expressions need to evaluate to a boolean result, that is the only point.


The reason I pointed out that it might work in cases where the evaluated field (a in your example) /actually has a value/ of 0 or 1 
(which can be directly translated to boolean) is only because other people on this forum would quickly launch at the opportunity to 
correct me if I had simply said "it doesn't work like that at all" - as they should.   :)



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

Re: [sqlite] Is there equivalent to MySQL IF() function?

[RSmith]

On 2014/10/07 12:13, Tony Papadimitriou wrote:

Thanks.

It seems quite a bit more verbose than the IF() function, but it works, so I 
can't complain.


As an aside - It's not only a little more verbose, it also happens to be the way prescribed by the SQL standard and to my knowledge 
MySQL, Oracle, PostGres and SQLServer all support it very well... the fact that some of them add other functions (such as the 
IF(eval, when true, when false) construct) for of ease of reading and user preference is quite helpful but then they are not 
constrained by the "Lite" moniker and can rack up those megabytes without a care in the world.




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

Re: [sqlite] Is there equivalent to MySQL IF() function?

[RSmith]

On 2014/10/07 12:42, Tony Papadimitriou wrote:

You're right, ... but in that page it says:

The only difference between the following two CASE expressions is that the x 
expression is evaluated exactly once in the first example but might be 
evaluated multiple times in the second:

 CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
 CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END

So, my understanding is that the second CASE (for my example) should give the 
same result as the first CASE.


No, your example second CASE statement compares a field value (a) to Boolean expressions which each can result to either 0 or 1... 
so unless the value in a is exactly 0 or 1, the ELSE-section will always trigger and so output a '3'.  Do you understand the 
difference between a boolean result and a value result from an expression?


> select a,case a when a<10 then 1 when a < 20 then 2 else 3 end cased from a;
> gives unexpected result 3,3,3

In the document examples the first example compares x to w1 and w2 respectively, if it matches any the resulting output is r1 or r2, 
and if no match is found, r3.
The second example do not have a base field it uses to compare, so in every WHEN section it evaluates the complete BOOLEAN 
expression which returns either 1 or 0 (True or False) and so determines which is shown... If you change that to your examples form 
to read:


CASE x WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END

then x is compared to Boolean functions and will have unpredictable results, unless it is itself a boolean value, but if it was a 
boolean value you could just have done this:


CASE x WHEN 1 THEN r1 ELSE r2 END

which is pretty mush the IF-THEN-ELSE form most languages support.


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

Re: [sqlite] Is there equivalent to MySQL IF() function?

[RSmith]

SELECT CASE WHEN (AGE<3) THEN 'Baby' WHEN (AGE BETWEEN 4 AND 18) THEN 'Child' 
ELSE 'Adult' END


On 2014/10/07 11:15, Tony Papadimitriou wrote:

Hi all,

Is there any an equivalent function to the MySQL 
IF(condition,true_expr,false_expr) function?

For example, SELECT AGE,IF(AGE < 3,"BABY",IF(AGE < 18,"CHILD","ADULT"));

If not, please add to wish list :)

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


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

Re: [sqlite] Is there a simple command line data editor for sqlite3?

[RSmith]

Did you have a specific OS in mind?

Linux, MacOS and Windows all have a myriad of SQLite editors. A simple google would no doubt reveal a lot, but if you say which OS, 
I'm sure on here we can point out some good ones.


On 2014/09/30 19:23, c...@isbd.net wrote:

Is there such a thing?

Of course I know and use the sqlite3 command line utility but that's
just a way of running SQL from the command line.

I want some sort of wrapper that runs a SELECT to get the data from a
table, presents it to me in editable format and then does an UPDATE or
INSERT to put the changed data back.



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

Re: [sqlite] Will collate binary supports like query

[RSmith]

On 2014/09/30 09:03, dd wrote:

I just got below pragma:
pragma case_sensitive_like = true

As an aside... The beauty of it being a Pragma is that you are not stuck with a single method - you can switch it on and off at a 
whim... Maybe even provide a GUI interface to switch modes.

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

Re: [sqlite] Division accuracy

[RSmith]

On 2014/09/25 19:32, Clemens Ladisch wrote:

RSmith wrote:

the time of finishing does not determine position alone, there are
bonuses and penalties which doesn't stack up to integer values, but is
measurable [...]  Without going into too much detail about how bonuses
and penalties are calculated

How can anybody help you without these details?


Because this is not the problem I needed help with, this one I can do but merely mentioned it to demonstrate to Mr. Lowden why I 
would need a seemingly unnatural ordering. The help I needed was already provided / added to by various people, for which I am very 
thankful.


And from JKL:

You make valid (if obvious) points, but...


Measurable means "is a number", right?  So we have math, and IEEE
floating point.  Yay!  We can compute the answer!   


No, measurable means that we understand the relative value of an advantage or 
disadvantage even if it does not necessarily compute into a single number 
because it might be subjective or multiplexed. Did you have orange juice or 
grapefruit juice for breakfast? Which scored higher? That depends whether you 
are rating taste or healthiness - and those are by far not the only possible 
ratings.

The exercise to make it map to a number is however a possible one, if not a 
very easy one, and I will (as mentioned before) be considering that solution if 
this order mechanism turns out to be too costly - Thank you kindly.




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

[sqlite] Fwd: Re: sqlite generate strange field name in union...

[RSmith]

On 2014/09/25 05:04, 麦田观望者 wrote:

Hi, RSmith‍:
I can't find a method to reply you message,so i send it to you mailbox 
directly, sorry for disturber.

you say:
>It is just whatever the Query producer feels comfortable writing in the
>header to identify the column‍

maybe you are right on the point of STANDARD‍, but, every serious(normal?) 
programer except the db engine generate the same column name as they defined in 
the database except when the name conflict occured.

index is a number which is inconstant‍  --  table redesign, sql statement 
re-write. but name keep more stable and is more friendly to people.  you can 
find a lot of FieldByName(...) statement in a database related source 
code--even some company require their programer access field only by field name 
-- except a performance issue is occured..


Pardon the next bit of convoluted English, I think the OP works via a 
translator so I will try to be overly verbose to try and
ensure the message go through in its fullest meaning.

Hi Mykore,

FieldByName(...) refers to the fieldname returned by the query or table, if you 
do not tell the query what exact fieldname you mean
for it to show, then it may show some other name. I am not talking about what is 
"Standard" acceptable use, I talk about "THE SQL
Standard", as in what the people who designed SQL language constructs meant and 
require from a DB engine. You only think it is
standard use to get the names in a query when you don't ask for it because most 
DB's do that most of the time, but it is in fact not
required, you HAVE to say specifically what you want as a field name (using the 
AS directive) if you want to be guaranteed that a
certain query will produce certain field names.

This is not my opinion, it's the SQL law - even if it seems silly and you can 
come up with hundred reasons why it is silly, it still
is the law and it won't change. If you expect a name, ask for it specifically.



i whish you report my opinion to the sqlite dev team, or post it to public and 
collect more opinions.


No problem - I have posted this to the forum  :)



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

Re: [sqlite] Division accuracy

[RSmith]

On 2014/09/25 15:43, James K. Lowden wrote:

On Thu, 25 Sep 2014 10:36:31 +0200
Clemens Ladisch  wrote:


Yes, and yes, absolutely.  In that case the order is established by the
user, and can be captured by the application as integers, and stored in
the database.  The problem is trivial because the number is limited
to what a human being is willing to sort "by hand".  And the SQL is
straightforward.

--jkl


I think his example was meant to show the validity of the notion, not be a 
stringent use-case.

To this end, I will explain what I'm trying to do, and I have been thinking of making the system determine ordering up front as to 
avoid the whole issue. Basically we need to record live race results - but not a normal race, imagine a like a bingo game, every now 
and then someone finishes but the time of finishing does not determine position alone, there are bonuses and penalties which doesn't 
stack up to integer values, but is measurable.. so there is an action of finding a best-fit position in the list for the newest 
finisher and "insert" him/her there. The next finisher may well be below or above.  Without going into too much detail about how 
bonuses and penalties are calculated or rather, affected, think of a turn-based strategy card game as is common these days (Pokemon, 
Magic, etc.) with possible penalties such as "skip a turn", how do you put that into number values?  It's much harder to make a 
mathematical positioning result than simply working out if the current is better or worse than any existing position.


Harder, but not impossible, and if this ordering conundrum turns out to have too high an effort/pleasure ratio, then finding a 
mathematical positioning algorithm is next.


Thanks for all the input!


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

Re: [sqlite] sqlite generate strange field name in union

[RSmith]

On 2014/09/24 18:48, 麦田观望者 wrote:

if we have to tables:
create table t1(f1 integer,f2 integer);
create table t2(f1 integer,f2 integer);‍

the fellowing sql generate a result set with a strange field name:

select t1.f1 from t1
union
select t2.f1 from t2 ‍

we expect a column named "f1" but we get "t1.f1"

but "select t1.f1 from t1 ‍" get a "f1"

is this a bug or by designed?


Hi there,

It is not a bug and also not by design. It is just whatever the Query producer feels comfortable writing in the header to identify 
the column. It does not have to write anything there, nor are there any rules of what must be there - the SQL standard says that if 
you need a specific name you must specify it using AS. If you do not say what you want, then the query producer can put whatever it 
likes.


SQLite is rather consistent with what it chooses to put there (much like the other engines) and this sometimes give people the idea 
that whatever is used might be expected in future - but it really is not set in stone and may not be "expected" to conform to 
anything. If you do not use an AS specifier, then you should not count on the header to be any specific name. What you CAN count on 
is the column ordering, i.e. the 3rd column you asked for will always be the 3rd column of values in the output - but the name for 
that column can be anything weird if you did not ask for something specifically.


This next query will always give what you want:

SELECT t1.f1 AS f1 from t1
UNION
SELECT t2.f1 AS f1 from t2


(The second AS is not needed since the first already defines the column, but 
just added here for clarity)

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

Re: [sqlite] Division accuracy

[RSmith]

Thanks Alessandro, this will work, it's just... ugly... and for other reasons I'd prefer the sort order to be numeric. If however it 
doesn't work out, this might just be the best solution, ugly or not. :)


Thanks!
Ryan

On 2014/09/24 21:33, Alessandro Marzocchi wrote:
Which language are you using? Andres ideas could give some nice solutions using blob, even without a specific arbitrary precision 
library. eg:

Splitting data with an encoding like
(In-order id) (Out-of-order id)



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

Re: [sqlite] Division accuracy

[RSmith]

On 2014/09/24 22:24, Clemens Ladisch wrote:

RSmith wrote:

Clemens I'm liking the link list but did not go with it due to an expensive 
insert function

Yes, you have to update two references (with prev/next), but how is that
worse than the update of all SortOrder values?


Well the insert runs 2-3 times a second, the SortOrder update for re-ordering run maybe once a week.. at least, that was what I 
envisioned, but that is not feasible (may need to run several times a day due to the division problem), so hence now looking at the 
more expensive insert as a solution.



how would I get a normal SQL query ORDER BY clause to use that?

This is not possible with ORDER BY.  You would need a recursive common
table expression:

   WITH RECURSIVE ListInOrder(ID, Data, Next) AS (
 SELECT ID, Data, Next
   FROM ListTable
   WHERE Prev IS NULL
 UNION ALL
 SELECT L.ID, L.Data, L.Next
   FROM ListTable AS L
   JOIN ListInOrder ON L.ID = ListInOrder.Next
   )
   SELECT ID, Data FROM ListInOrder;

The recursion stops when a Next value does not have a corresponding ID.
See <http://www.sqlite.org/lang_with.html>.


I'd have to have an Index on "Next"


Thanks again, will try some ideas around this one... Another thought I came up with was a separate table keeping just the sorted 
indices as a list, but I think that will be equally expensive unless I get another thread to do that or maybe a temporary in-memory 
table that gets committed at intervals or such. All of this however detracts from the "live-ness" of the DB.





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

Re: [sqlite] Division accuracy

[RSmith]

Thanks all for the responses.

Thanks Scott for the calcs, I had somehow imagined using a set of values might yield more iterations, but of course that is just 
wishful thinking, the bits are the bits.


The idea from Alessandro is great if I could control or guess where the next inserts will be, but of course I don't know and if they 
happen to be consecutively at the same position, then I will run out of Integer intervals at the the same speed as when using a 
float, save a few bits.


Clemens I'm liking the link list but did not go with it due to an expensive insert function - of course if I knew at the start the 
division thing would bite me this decision may have been different.


But, say I'm changing things to work with the link list... how would I get a 
normal SQL query ORDER BY clause to use that?

I was thinking in stead of maybe having a prev and next column, to just have a next column which points to an ID. Inserting a value 
between rows 1 and 2 in the next example will mean 2 operations only, inserting a new value with it's "next" value pointing to row 2 
and then changing row 1 to have the new ID as it's "Next", repeating that idea twice will work like this:


ID | Next  | Data
1  |   2   | 'First Row'
2  |   3   | 'Eventual Fourth Row'
3  |   1   | 'Last Row'

After Insert we have:

ID | Next  | Data
1  |   4   | 'First Row'
2  |   3   | 'Eventual Fourth Row'
3  |   1   | 'Last Row'
4  |   5   | 'New Second Row'
5  |   2   | 'New Third Row'

Possibly "Last Row" should really have Max_Int as it's Next maybe... or just 
NULL. Needs more thinking.
I'm still confused as to how to get the ORDER BY to follow the trend

I'd have to have an Index on "Next" (which is fine) and then join the table to itself linking where "Next"="ID" kind of thing and 
use that as the ordering - not hard but will have to see how expensive it is.


Either way, thanks for the help!




On 2014/09/24 20:15, Clemens Ladisch wrote:

RSmith wrote:

  I have one program that inserts values to a table and determine sort
order using one standard trick that has a REAL column named
"SortOrder" [...]
reassign SortOrders simply in Integer steps: 1, 2, 3 etc.

ID | SortOrder | Data
1  |   1   | 'First Row'
2  |   4   | 'Eventual Fourth Row'
3  |   5   | 'Last Row'
4  |   2   | 'New Second Row'
5  |   3   | 'New Third Row'

When we think of this not as a relational table but as a data structure
in memory, this is the equivalent of an array, where SortOrder is the
array index.  Inserting or deleting of an entry requires moving all
following entries.

However, an array would not be the most efficient data structure for
this application, because random access to the n-th entry is not needed,
and the order of one entry is only relative to the adjacent entries.
A better data structure would be a linked list.

As a table, a linked list would be modelled as follows:

ID | Prev | Next | Data
1  |  |   4  | 'First Row'
2  |   5  |   3  | 'Eventual Fourth Row'
3  |   2  |  | 'Last Row'
4  |   1  |   5  | 'New Second Row'
5  |   4  |   2  | 'New Third Row'

This makes insertion easier, and should not need more space than
a floating-point SortOrder (because the prev/next pointers are
integers), but now reading the list in order requires a recursive CTE.

Well, I'm not sure if this is more clever than useful ...


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


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

[sqlite] Division accuracy

[RSmith]

I'm trying to find what the limit is for dividing in terms of accuracy.

Basically I have one program that inserts values to a table and determine sort order using one standard trick that has a REAL column 
named "SortOrder" which gets the value Highest_previous_value+1 if an insert happens with something that needs to be sorted at the 
end of the table.


For any other position, the SortOrder gets assigned the value: ((Prev.Sortorder 
+ Next.Sortorder) / 2)

So to be clear (in case anyone is not familiar with this method), let's start 
with a small table with 1 item added like this:

ID | SortOrder | Data
1  |   1 | 'First Row'

Adding two new rows to the end every time will use previous highest SortOrder+1 
so that the result is:

ID | SortOrder | Data
1  |   1 | 'First Row'
2  |   2 | 'Eventual Fourth Row'
3  |   3 | 'Last Row'

Adding a new row that should Sort in between IDs 1 and 2 above will take those SortOrders and find a new Order value by dividing the 
total for IDs 1 and 2 (=3) by 2 (=1.5):


ID | SortOrder | Data
1  |   1 | 'First Row'
2  |   2 | 'Eventual Fourth Row'
3  |   3 | 'Last Row'
4  | 1.5   | 'New Second Row'

Adding another row that should Sort in between IDs 2 and 4 will again total and 
divide by 2 (=(2+1.5)/2):

ID | SortOrder | Data
1  |   1 | 'First Row'
2  |   2 | 'Eventual Fourth Row'
3  |   3 | 'Last Row'
4  | 1.5   | 'New Second Row'
5  | 1.75| 'New Third Row'

So that if the Query 'SELECT Data FROM t ORDER BY SortOrder' executes it goes 
like this:

Data
'First Row'
'New Second Row'
'New Third Row'
'Eventual Fourth Row'
'Last Row'


This seems like a clever method and I've seen it used a few times, but it really can break easily if you keep dividing by two, there 
is a very quick limit in accuracy where one value can no longer be divided by two meanigfully. In 64-bit Floating point Math that 
limit is very far away, quite a few iterations (assuming normal floating point mantissa accuracy - the exponent size does not matter 
since any two such values will be adjacent in the same realm of magnitude and only the available real numbers in between them 
counts), but if inserts happen 2 to 3 times a second, and imagining for a moment that the sort might hit the same spot every time, 
many consecutive divs might be exhausted quick.


The question is - how can I accurately establish how many total-then-divide-by-2's a set of co-values in 64-bit FP guise can 
withstand before the difference is too small to make sense to the sorter in SQLite?


Reason: The fix is easy but costly on a large DB, sort and reassign SortOrders simply in Integer steps: 1, 2, 3 etc., but I want to 
establish how often this should be done, as little as possible preferred, but not so little as to allow the order to break or div0 
errors or such.




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

Re: [sqlite] Non-optimal query plan

[RSmith]

On 2014/09/24 16:52, Rob Golsteijn wrote:

Hi List,

I was looking at the query plan of a rather simple query, but I don't 
understand why sqlite would choose this query plan.

...I was surprised that sqlite came up with the inferior query plan...

Note: After an "analyze aaa" (on a decently populated table) sqlite chooses the 
full table scan instead of creating an automatic index (but our application never uses 
'analyze' to avoid that other (bad performing) query plans are used during operation than 
during testing)
Note 2: Adding "NOT INDEXED" to aa1 gives the desired query plan, but of course 
I prefer that sqlite choses the right query plan.


I think this issue is fixed since it does not do it in my version, but... even if it isn't, let me just note that basically SQLite 
chooses a Query Plan that might in many circumstances work perfectly since it has no information available about the data. Further 
to this SQLite provides not one, but two explicit methods for you to improve the query planning should the default not fit the best 
for your specific query - yet you choose to ignore BOTH of them and expects SQLite to pick a good plan without any knowledge of the 
data, by default. [and ironically you are very happy to circumvent a whole part of SQLite Query planning prowess to hide "other" QP 
issues in stead of fixing it or requesting a fix, but won't add anything legal and valid that would actually improve it. That is 
just bizarre.]


If I was the asker of this question I would concentrate on what you mention in passing in Note1 with regards to avoiding "other" bad 
plans. If you do use Analyze and then at any point find the QP comes up with a bad plan WITH access to analyze data - now THAT would 
be a reason to complain and I have seen requests such as that cause a QP overhaul many times here, you'd actually improve it for 
everyone should you find a use-case with real QP problems based on proper knowledge of data shape.



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

Re: [sqlite] Results of Joins in sqlite

[RSmith]

On 2014/09/24 15:06, Prakash Premkumar wrote:

Thanks a lot , Simon and Hick,

What I am looking for is , instead of iterating through the result which
sqlite provides and then form the respective objects and setting pointers,
is it possible to hack sqlite to fill in the objects this way.
I would like to prevent the extra iteration through the result set.

When the result row is formed in OP_ResultRow, we should be able to form
the structs and their links. Any pointers in this direction?


Hi Prakash,

I understand what and why you are trying to do to achieve this - but please let me suggest some alternatives, and explain why myself 
(and everyone else seemingly) is urging you to not do it the way you are trying. Your code will only work with the current SQLite 
amalgamation, it won't work ever again unless you update it every time a new version appears - which will be very tedious, and core 
functionality might change, which means not only will you need to change your code, but at some point may even need to rethink your 
entire strategy. How many times do you want to reinvent the wheel?


To be clear, new versions appear every other month more or less. Further to this, the amount of work you need to do to achieve what 
you are trying is enormous, there is no quick "hack" for it, you have to meticulously adjust and inject code and test it into the 
SQLite main engine - YES this is possible, but at what price?  You are trying to do a job that requires 900% effort for a <1% 
efficiency gain.


Further to this, the kind of tree-graph that you wish to build will only work for very specific kinds of queries, for instance 
requirements such as columns need to be ordered in ascending complexity. If this is the only job this engine does and will not be 
updated, then that does not matter so much.


Why not consider (as suggested by others) using the API as it is supposed to be used, for instance making a virtual table will do 
this job exceedingly well, and really efficient. Just define a new table , write to it via a SELECT query and get from it the 
structure or map or graph which you can very easily ascertain.


if the queries are going to be specific, you can even add SQL functions via the function adder API to systematically build tree 
structure while the query is executing (though specific ordering will be required since the functions do not know at the time of 
being called which specific row is being processed in the output and might be called more than once if it appears in the order-by 
clause etc.).


Then, simply using the sqlite_step() and column value reading APIs will all do exactly what you want WHILE the query executes, no 
need for extra loops... this is why they exist and no injected code could possibly be measurably faster. Why would you insist on 
using another way?


Consider also that using ANY of the above suggested API methods will ensure you can plug-and-play any updated data engine from the 
SQLite site in future (or past if you fancy testing) and capitalize on the improved performance or newer features without any 
problems, without rewriting a single line of code. How is this not a 100 times better?



Please consider the alternates, save yourself a LOT of work.

Cheers!
Ryan





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

Re: [sqlite] Atomic database structure initialization

[RSmith]

On 2014/09/22 15:03, Paul wrote:


I suspect that no, not all accesses to the database file are done using 
transactions.
What about read-only databases? Moreover, what about read-only medium?


A transaction does not necessarily imply a write, only if there is an update of actual data, which depending on the transaction 
model you've chosen may or may not first produce another alternate file recording the transaction before committing it. SQLite is 
quite useful in this regard as a LOT of use cases involve embedded applications using read-only or static memory and SSD or other 
write-averse storage kinds.


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

Re: [sqlite] Performance regression between 3.7.17 and 3.8.0.2?

[RSmith]

On 2014/09/21 15:39, Merike wrote:
Now I could very well be wrong about that as you say in your other reply that "It might simply be that Analyze did not get your QP 
to react on that size DB as it did for us". You seem to be saying that analyze behaves differently depending on database size...


This is very much what I'm saying but maybe not exactly like you think. ANALYZE really interprets the "Shape" of your data (average 
width, cardinality and so on) and from that provides figures to the Query Planner by which to make decisions. For this reason, 
running ANALYZE on any amount of databases, different sizes, different kinds of data, will all provide different figures to the 
Query planner and it is very possible to produce a set of figures running analyze on one DB which changes the QP's mind on what plan 
to follow, whereas the very same Schema DB but with other data in it might make the QP choose a different plan - either of which may 
or may not hide the bug in question.


Glad to hear the trunk fixed your problem though - that will be standard in the 
next release.

Cheers!
Ryan



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

Re: [sqlite] An order by problem, maybe a bug?

[RSmith]

On 2014/09/21 17:18, James K. Lowden wrote:

...to get web payment forms to allow, for the love of God, spaces in credit 
card numbers. --jkl


Now there's a worthy cause. Ditto for phone numbers (though they mostly are more lenient today). Also to allow hashes and dashes in 
the address field. I spent quite a while on the phone to some American online company trying to buy stuff using a credit card for 
which I needed to supply my address for verification purposes... and in some countries we have addresses like 19A 
Hem#Strassen-34'B(N), etc.  So as long as some of those characters are not allowed in the field, I cannot possibly reproduce an 
address that matches my bank's version of it, and needless to say, cannot pay anyone with such input field restrictions.



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

Re: [sqlite] An order by problem, maybe a bug?

[RSmith]

On 2014/09/20 23:23, Simon Slavin wrote:

...calls themself Tarquin 
Fin-tim-lin-bin-whin-bim-lim-bus-stop-F'tang-F'tang-Olé-Biscuitbarrel


Oh you know him? We go way back... old Tim Biscuits we used to call him. It was fun watching the undertakers figure out how to get 
all that on his gravestone after that plan to blow up the empire state went terribly awry in the test phase. People still mistake 
that stone for a karaoke machine.



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

Re: [sqlite] Performance regression between 3.7.17 and 3.8.0.2?

[RSmith]

On 2014/09/21 14:12, RSmith wrote:


On 2014/09/20 23:08, Richard Hipp wrote:

On Sat, Sep 20, 2014 at 12:45 PM, Merike  wrote:


A question: is the query being fast again after analyze call indicative
of the bug being fixed? Because I tried it on my original database too
and there I don't see a speedup after analyze. Should I try to minimize
it to a smaller database again where the bug still occurs, even after
analyze? Or will the change you made fix my original database speed as
well despite the analyze call not helping it?


The change fixes the problem (for us) *without* requiring ANALYZE.



Richard, I think the OP meant to ask whether or not running analyze fixed the bug, maybe a slight misunderstanding and you 
probably assumed correct understanding as a benefit of the doubt scenario - so allow me to just add the following in case the OP 
did think of it the other way...


On second thought, I think the first interpretation was correct. Merike - just so I understand clearly - you DID download and is 
using the new trunk yes? If so, how big is the old DB you tried it on? Could you try the minimize and send the file via a 
file-upload service?
It might simply be that Analyze did not get your QP to react on that size DB as it did for us - but if the bug is not fixed it needs 
to be seen.


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

Re: [sqlite] Performance regression between 3.7.17 and 3.8.0.2?

[RSmith]

On 2014/09/20 23:08, Richard Hipp wrote:

On Sat, Sep 20, 2014 at 12:45 PM, Merike  wrote:


A question: is the query being fast again after analyze call indicative
of the bug being fixed? Because I tried it on my original database too
and there I don't see a speedup after analyze. Should I try to minimize
it to a smaller database again where the bug still occurs, even after
analyze? Or will the change you made fix my original database speed as
well despite the analyze call not helping it?


The change fixes the problem (for us) *without* requiring ANALYZE.



Richard, I think the OP meant to ask whether or not running analyze fixed the bug, maybe a slight misunderstanding and you probably 
assumed correct understanding as a benefit of the doubt scenario - so allow me to just add the following in case the OP did think of 
it the other way:


Merike: Running Analyze did not fix the bug, it simply changed some internal-use values that allowed the bug to be circumvented. If 
you re-make a table as you had without running analyze, the problem will surely remain using the same codebase. Updating your SQLite 
code from the Trunk specified by Richard will fix the bug for real, meaning that analyze would make no real difference (for the 
specifed use case). Naturally the trunk is also included in the next release which is probably due within the next month at which 
time the bug fix will be standard across all released versions.



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

Re: [sqlite] I'm trying to figure out how to ...

[RSmith]

On 2014/09/18 05:38, Mark Halegua wrote:

I'm racking my brain trying to figure out how to get directly to the last item 
in a (potentially)
sorted or ordered table.  At least oe of the tables will be ordered by a name 
and a date,
so uising the rtowid won't work.

Also, how to traverse a table or cursor in a reverse direction.


Hi Mark, Keith's reply is quite complete and you should design systems with 
those tenets in mind.


That said - to elaborate more on the quick and dirty methods (in case you are 
hacking together a quicky in stead of a proper system):


Getting only the last item in table t which has fields fDate and fName among 
others:

SELECT * FROM t WHERE 1 ORDER BY fDate DESC, fName DESC LIMIT 1;

(The DESC does the trick of making the sort order go the other way round, the LIMIT is to get just the 1st item [which is really the 
last item had the sort-order been normal] and the WHERE clause is superfluous here, but shows where it might be. Also note that this 
method will be real fast if fDate and fName are contained in Indices, else it may well be quite slow for large tables)



Traversing the results in reverse is achieved much the same way, just remove the limit clause. If however you meant that you need to 
move "back" through the list after having gone forward or such, then you are in scrolling cursor territory and I respectfully refer 
you back to Keith's article and urge to take heed of the advice.



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

Re: [sqlite] FW: I'm trying to figure out how to ...

[RSmith]

On 2014/09/18 08:18, Keith Medcalf wrote:

...long article...


Thanks Keith for taking the time, I imagine this is a topic you are passionate about and I think it should be published somewhere as 
an article because it will save lives of beginner DBA/Programmers for millenia to come - I have certainly born witness to many of 
these pitfalls at various clients, and in the past stepped in some myself.


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

Re: [sqlite] Create join and add unique column

[RSmith]

On 2014/09/16 20:00, James K. Lowden wrote:


Or not use a temporary table.

http://www.schemamania.org/sql/#rank.rows

Now easier with CTE.  In theory it could be *faster* than a temporary
table, because the insertion I/O is avoided.  But only testing will
tell.

--jkl


Hi James,

I'm well familiar with the ranking SQL (thanks to your site btw), but I fail to see how that can be applied to solve the OP's 
problem which I will list again hereunder.  I know the OP in the meantime realized he had other problems which renders any of these 
solutions unhelpful, but allow me the learning experience please, could you show how to achieve this in SQL via the ranking method 
you linked (or any other method you might come up with - I can do it in CTE, but am hoping to learn how to do without):


say for instance I have two tables

create tab1 (id int, name text)
1, 'paul'
2, 'helen'
3, 'melanie'

create tab2 (id int, country text)
1, 'uk'
2, 'scotland'


what I want is

1 | 1 | paul  | 1 | uk
2 | 1 | paul  | 2 | scotland
3 | 2 | helen  | 1 | uk
4 | 2 | helen  | 2 | scotland
5 | 3 | melanie | 1 | uk
6 | 3 | melanie | 2 | scotland
 



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

Re: [sqlite] Create join and add unique column

[RSmith]

On 2014/09/16 15:32, Paul Sanderson wrote:

select _rowid_, * from tab3 does the trick - thanks all


Indeed, and if you are pedantic or do not work in a table with rowids, the solution is to explicitly give the table definition then 
fill it, some variation on this:


CREATE TEMPORARY TABLE tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT, name 
TEXT, country TEXT);
INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N, tab2 C;

tab 3 should now look like this (according to your sample tables):
rowNo|   name   |   country
-
1 |  paul   | uk
2 |  paul   | scotland
3 |  helen | uk
4 |  helen | scotland
5 |  melanie | uk
6 |  melanie | scotland


NOTE:
In-case you are not familiar with it - That insert omits the rowNo and can be thought of as a variation of this query which achieves 
the same:

INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country FROM 
tab1 N, tab2 C;


Hope that widens your SQL arsenal another micron, Cheers!
Ryan


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

Re: [sqlite] Divide by 0 not giving error

[RSmith]

On 2014/09/15 22:13, Simon Slavin wrote:

On 15 Sep 2014, at 8:33pm, Dave Wellman  wrote:


Simon,
I'm really surprised at that. Effectively what this means is that the answer
that Sqlite returns may or may not be the correct result.

What ?  No.  It's correct.  The answer is not known, and NULL means "I don't 
know".  Given that anything divided by 0 is infinity, and anything divided by itself 
is 1 what is 0/0 ?


x/0 is of course unknown or, more technically, "undefined" and not simply "infinity" as Simon points out later, and mathematicians 
have reasons for this. If anyone has an interest in understanding the real reasons - one of the most succint descriptions of 
problems concerning division by zero, 0/0, 0^0, etc. can be found in this youtube video where Dr. James Grime and kie explain some 
of the oddities:


http://www.youtube.com/watch?v=BRRolKTlF6Q

And some more interesting information on the History of the number Zero and 
whether it is even or not - for those interested:

http://www.youtube.com/watch?v=8t1TC-5OLdM



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

Re: [sqlite] Divide by 0 not giving error

[RSmith]

On 2014/09/15 20:50, Dave Wellman wrote:

Hi all,


I've found that an sql request that I expected to fail, but it didn't. On
the face of it that is good news but there is a potential downside. I wonder
if my expectation is wrong or if this is a bug which so far hasn't been
caught.
  


The problem deals with dividing by 0. As far as I can remember, in every
programming language that I have ever used and in all databases that I've
used, if you try and divide by 0 the process will fail with a 'divide by
zero' error. Sqlite doesn't seem to do that, it instead returns NULL.


This is not the first time this issue has come up and the answers are usually the same - NULL is a very good indicator that the math 
did not result in a successful calculation, and SQLite has been like this forever, cannot change it now - both valid points btw. but 
not really conformist.


I'd like to propose the NULL return as a superior solution though - If I may 
illustrate why I think so:

A lot of times I use division in queries, in one example system I need to measure the ratio of cost of material issued vs. bill of 
materials cost in some report, a simple (100*A/B) AS 'CostFactor' makes my query work like a charm.  Every now and again some system 
editor or costing clerk might register a bill of Materials wrongly so that the total cost is 0 or such, an easy fix, but until it is 
fixed my queries simply show a NULL in the 'CostFact' Column and in fact, this indicates that there is a problem to whomever is 
reading the report, but most importantly, the entire report doesn't fail, and much less forces a rollback or something horrible 
until someone sorts out the glitch.


Having said that, the other side of the coin needs consideration too... Sometimes an INSERT query populates new data to a transacted 
job registry or such, in here I need the fields to be explicitly correct  and fail very hard on a DIV/0 error and force the rollback 
with appropriate error message, because financials will be affected the previously harmless error becomes a serious error when 
channeled into a monetary value journal. I am however well-aware of this problem, as I imagine any system designer should be, so 
when making these queries, I add fail-safes.


The reason I prefer this method is that I have the choice of adding fail-safe code for important queries/functions (which is anyway 
only 10% or less of the codebase) and no need to add ludicrous amounts of fail-safes to protect the other 90% quick data views or 
reports from not falling over/rolling back every time a zero value appears.


I understand that this assumes I know about the DIV/0 thing and that it does not work the same as the other DBs, but I vote for 
documenting it well and keeping it like this, even in upcoming SQLite4.



Regards,
Ryan

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

Re: [sqlite] Can't figure out how to report a bug

[RSmith]

This is the right way to report a bug, and as soon as you encounter a bug, you 
should report it here.


As for the current query, this is not a bug, it's a VS13 compiler peculiarity which they feel pertinent to report on, but which does 
not affect the ability of SQLite to produce the correct result or function perfectly in any way - i.e. NOT a bug.


Feel free to fix it in your source to just hush the compiler (which introduces the risk for real bugs), or just ignore it and SQLite 
will work perfectly.



Have a great day,
Ryan


On 2014/09/07 14:26, Joe Mucchiello wrote:

So I'm posting it here. The 3.8.6 Amalgam file generates an error in VS13 on 
Windows:
sqlite3.c(77874): error C4703: potentially uninitialized local pointer variable 
'pReadr' used


This is from the source file src/vbesort.c in a function called 
vdbeSorterSetupMerge:

   PmaReader *pReadr;
   SortSubtask *pLast = &pSorter->aTask[pSorter->nTask-1];
   rc = vdbeSortAllocUnpacked(pLast);
   if( rc==SQLITE_OK ){
 pReadr = (PmaReader*)sqlite3DbMallocZero(db, sizeof(PmaReader));
 pSorter->pReader = pReadr;
 if (pReadr == 0) rc = SQLITE_NOMEM;
   }
   if (rc == SQLITE_OK){
 rc = vdbeIncrMergerNew(pLast, pMain, &pReadr->pIncr); -- << Error line

Here is my fix:

   PmaReader *pReadr = 0;
   SortSubtask *pLast = &pSorter->aTask[pSorter->nTask-1];
   rc = vdbeSortAllocUnpacked(pLast);
   if( rc==SQLITE_OK ){
 pReadr = (PmaReader*)sqlite3DbMallocZero(db, sizeof(PmaReader));
 pSorter->pReader = pReadr;
   }
   if (pReadr == 0) rc = SQLITE_NOMEM;
   if (rc == SQLITE_OK){
 rc = vdbeIncrMergerNew(pLast, pMain, &pReadr->pIncr);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Problems uploading CSV into sqlite3 DB

[RSmith]

Humour us and paste the first 4 lines or so of your CSV text here


On 2014/09/05 04:17, Carlos A. Gorricho (HGSAS) wrote:

So, any ideas on how to solve this issue will be more than welcome. I have
tried several shortcuts...none works.

Latest was to install an Ubuntu 14.04 Virtual Machine on my Mac, via
Parallels software.

Downloaded sqlite de Linux way:

$ sudo apt-get install sqlite3


Ran the CSV .import protocol and didn't work Result was eve weirder, since
it create a table using ALL the records as columns headers...



Saludos/Cheers,



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

Re: [sqlite] Not SQLite related, but, SQLite Mailing List related

[RSmith]

On 2014/08/11 19:19, Stephen Chrzanowski wrote:

Ok, this IS NOT about SQLite itself in ANY regard, but specifically about
this particular mailing list and how GMail is handling itself.

When I joined this mailing list years ago, I put anything that goes through
here into its own label via the same means that I've got for other email I
regularly receive.  When I go through my PHP Classes emails, my regular in
box, etc, those "Newer" and "Older" buttons are enabled.  However, in THIS
mailing list, when I click on any email, I don't get the option to go to
OLDER messages, but I do get to go to NEWER messages.  That OLDER button
doesn't activate when I go to a NEWER message when hitting the NEWER
button, but this functionality seemingly works everywhere else (I admit I
didn't try EVERY label)

Anyone have an idea why?


Goverment Conspiracy.

THEY Know



Might be a G-mail setting, mine works fine, but it always have, so no idea what to look for other than to say it should work under 
the right circumstances. I'm hoping someone else here already battled with the same issue and won.


Actually, mine gets into gmail via an IMAP server (as per my email address) and isn't a native gmail address, so might not be 
comparable...



(Disclaimer: I realize now that this response contains not one single bit of helpful information, but hopefully eliminates one of 
the myriad questions)


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