Re: [sqlite] Intended use case for 'without rowid'?

[RSmith]

On Fri, Nov 15, 2013 at 7:33 AM, RSmith mailto:rsm...@rsweb.co.za>> wrote:


Yes there would be a space-saving, but it is rather minimal. The real 
advantage is removing one complete lookup reference
cycle from a Query...


That was my original theory too.  But experimental evidence inverts this.

There is a program called "wordcount.c" (see http://www.sqlite.org/src/artifact/2c2cc111?ln) that tests the performance of WITHOUT 
ROWID.  It constructs a table like this:



etc.

Why yes of course there is no more need for an entire index and all the baggage accompanying it which makes for a ~50% reduction in 
a table with just those columns, but surely any normally useful data table (as opposed to this experimental type) consisting of many 
columns would receive a rapidly diminishing benefit in terms of size reduction?


 I am also convinced after some testing that the speed benefit might be somewhat bigger for these larger tables where more bytes 
distance exist for pages etc, but I might be wrong - will do some more testing later.

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

Re: [sqlite] Intended use case for 'without rowid'?

[RSmith]

Oh and of course the space saving for simple reference tables (basic 
Value-for-ref-lookups)  would be great.

To be sure, this does not just affect Text Keys, but all non-INTEGER primary 
keys, right?


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

Re: [sqlite] Intended use case for 'without rowid'?

[RSmith]

I'm ALWAYS looking for a faster query (Who isn't? -- Except those edge
cases where management thinks the software is broken because the query is
TOO fast and doesn't trust the results) but the loss of some common use
functionality kind of has me wondering "Why?"


Well yes but...

Firstly, using the last_insert or equivalent function is not the SQL way to do things, but a rather clever optimization provided by 
most (if not all) SQL implementations that supports a very specific method of adding things to tables.  You could for instance 
rather remember (or find) the last added primary key through SQL, and then simply add data where YOU assign the primary key values 
(in stead of letting the autoinc do its thing) and then you have the key to insert in as many tables as needed - as it should work. 
The autoinc is just an added help for humans to make it easier since "everybody uses integer primary keys anyway", and it ensures 
unique values from a database-engin side removing that responsibility from your code. As far as SQL is concerned though, if you use 
a lot of matching keys to identify data in multiple tables, you should really be specifying those keys yourself and not rely on the 
various shortcuts.


It's probably faster even to specify Key values than wait for the DB engine to run its own Autoinc code for every insert (though 
this is very fast too).


Now disregarding all the above - The very only reason you would use the WITHOUT ROWID optimization on any table is precisely because 
you are NOT using an integer primary key but because you are adding proper text values as the Primary key, so even if you could use 
the last_insert function to get a valid rowid then it won't help you because the table is referenced via text primary key and there 
is nothing useful the linked table can do woth the last_insert value... unless you are linking tables to each other using the actual 
rowid, which would be the very worst DB decision ever, so I'm sure that is not the case.


Even in an after-insert Trigger (as another poster remarked) you would really need to know the Primary Key by direct reference 
already since the DB isnt making up it's own on an insert, I'm not sure how it would be of any value - but I might be 
misunderstanding the suggestion.


As to your question of: WHY?  I'm sure other posts describe the benefits well so I won't repeat it except to say that it is a very 
case-specific benefit and there is no need to use it for the normal tables you described.




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

Re: [sqlite] Intended use case for 'without rowid'?

[RSmith]

Pepijn & Peter - I'm not sure how this will be an issue for the sort of existing systems you describe?  You will need to actually 
physically change your current schemas to produce the mentioned problems, which if you don't, you have nothing to worry about.  The 
only people I think should plan some changes are those making DB admininstrator type systems where they cannot control what things 
users open, in which case - yes, a parse error is on the books, but this should be an easy addition for said devs.  (Never relying 
on rowid turned out a pedanticism that paid off for me - it might be a lot of changes for some though, so I understand the notion).


You could also introduce a unique Application ID (see: 
http://www.sqlite.org/pragma.html#pragma_application_id)

or do a check:
IF EXISTS (SELECT 1 FROM sqlite_master WHERE type="table" AND like('%WITHOUT 
ROWID%',sql);

- to simply know whether it's an incompatible file being opened and notify the 
user as such.

Being more pedantic - Schema X would still be Schema X and work exactly like a Schema X worked before, the possibility that a Schema 
Y might also be formed now does not mean Schema X works any different than before, ergo this is not a true case for Schema versioning.


- BUT -

I would still like to see some kind of function, even a totally new one that 
does not affect any backward compatibility, such as:

*BOOL sqlite3_table_has_rowid(*tbl);

where maybe if the 'tbl' parameter is empty it checks all tables and lets us know whether any tables in the Schema does not contain 
a rowid (FALSE) etc.



The only reason I would want this is for speed (the query above might not be very efficient, or, I might be wrong - an indication 
would be appreciated).


I hope this makes some sense - thanks.



On 2013/11/15 21:17, Pepijn Van Eeckhoudt wrote:

Will without rowid introduce a new schema version number?

If so, we’ll be ok since GeoPackage requires schema version 4.

Pepijn

On 15 Nov 2013, at 16:33, Peter Aronson  wrote:


One  additional thing not listed in this document -- use of a internal rowid 
alias (OID, ROWID or _ROWID_) will produce a parse error on a query against a 
WITHOUT ROWID table (unless, of course, it has an actual column with the 
specified name),  which makes sense, of course, but could be an issue for 
generic table handling code that currently uses any of those.

The fact that the presence of such a table makes a database containing one 
unreadable at releases before 3.8.2 is a bit of an issue for those using SQLite 
as a data exchange format (like GeoPackages), but then that's true with partial 
indexes too.

Peter



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

Re: [sqlite] Intended use case for 'without rowid'?

[RSmith]

Here's a thought:  What does your hypothetical function return for a table 
defined as follows:

 CREATE TABLE strange(rowid TEXT, _rowid_ TEXT, oid TEXT);

That table has a rowid, but it is completely inaccessible to the application.  
Does your function return TRUE or FALSE?

My point:  I think any application that depends on there being a column named "rowid" that is the key to the table is already 
broken.  WITHOUT ROWID does not add any new brokenness.


Yeah, that Schema would break most systems depending on a rowid today even, regardless of rowid-less tables, so the danger is 
ever-present.  Point taken (and view shared) - but it won't be the first brokenness-habit to permeate SQL-programs the world over. 
However, even if so broken, with a fix as easy as a single SQL query upon opening a table, I doubt the issue merrits any more time.


For Peter & Pepijn - I think the issue is essentially a forward-compatibility problem moreso than a backward-compatibility one. So I 
think your idea on introducing some version control would be the least painful.


Thank you kindly.
*goes off to import TABLE strange() into some systems for fun*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?

[RSmith]

Perhaps we should make the allowed DDL subset a part of the spec. That way we make explicit what is allowed and anything outside 
of that is forbidden. Pepijn 


Perhaps.
It would involve a rather large document though, one which an average user is sure to skip over but at least it provides indemnity, 
plus I don't see an enormous or sudden uptake of WITHOUT ROWID and/or partial Index tables in the general populous. By the time this 
is a generality, you should be a couple of versions of your system further down the product line.


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

Re: [sqlite] help writing DELETE with JOIN

[RSmith]

On 2013/11/16 20:02, David M. Cotter wrote:

okay i realize my requirements were wrong, here's a better summary:

the plID (playlist ID) in the song table is different (the OLD id 33), the plID 
in the playlist table is the new ID 35, so i have to test them separately.  the 
song ID's must match
the playlist table's index is the plID, so i guess that's what i have to select 
to delete?  is that what's wrong below?

and here's the statement i came up with, it executes without error but does 
nothing to my tables:

DELETE FROM playlist WHERE EXISTS ( SELECT 1 FROM song WHERE playlist.soID = 
song.soID AND song.plID = 33 AND song.Stal ) AND playlist.plID = 35


That statement when translated to English reads as follows (give or take some 
artistic license):

Go through every record in the playlist, and whenever it is found that this record's plID is 35 and at the same time you can find a 
record in the song table which matches this playlist-record's soID, but also has a song.plID value which is exactly 33 and a boolean 
value in the song.Stal field which is 1, then delete this record from the playlist.


Are you sure this is what you wish to happen? It seems unlikely to me that a song would have a plID that refers to an old playlist 
when it has already been added to a new playlist. When do you set the song's plID then?


It seems to me this query is removing or preventing songs from belonging to a new playlist (35) when they already exist in an older 
playlist (33).


Also, are you sure the .Stal field has boolean values?

Does the Select query work as expected when joined with the playlist but 
without the delete statement?





hmmm, oh SQLite, you so opaque!  stop it!

--
kJams:  Mac and Windows Karaoke: CD+G Rip, Mix & Burn!
Main:   http://kjams.com/wiki/
Downloads:  http://kjams.com/downloads/
What's New: http://kjams.com/history/
To Unsubscribe: Simply reply with "kJams: unsubscribe"

___
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] SQLite server/file-locking scenario

[RSmith]

Thanks so much for the reply. Sorry for the ignorance, but wouldn't only the sectors (page cache) that are being written need to 
be cached? And I was trying to read up on how sqlite does atomic writes, but doesn't the way sqlite handles atomic writes 
guarentee that the file is *always* in a valid state (even from the perspective of other programs that try to read it while being 
written)?




Do not confuse the atomicity of the "Database" with the state of the "File"... they are in no way the same thing. The database is of 
course quite atomic and quite safe to access whenever and expect to be in a good state if not locked etc. etc. This is achieved 
through one or more files containing data and journals that may very well contain very different things at different times. The 
atomicity of the "file" IO (if that is even a valid thought) is in no way guaranteed or implied or claimed at any point and as Simon 
correctly pointed out, the only way to ensure you are getting a fully up-to-date file is to use the Back-up API.


The Backup API will copy the current complete database ensuring all committed and atomic states and data goes into the new file and 
even provides nice callbacks for progress, cancellation and the like. It is even clever enough to reset and resume the copying 
process if a change occured once it already started backing up, ensuring your copied (target) database file is always fully 
up-to-date, uncorrupted and copy-able (if no other handles to it exist) at the time the backup concluded. This is the only way to 
ensure the *file* is atomic in the way that I think you mean above - and it works rather well. (It's also supported by most wrappers 
in case you are not using the API directly).


Details and examples here:
http://www.sqlite.org/backup.html

Cheers,
Ryan

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

Re: [sqlite] query optimization

[RSmith]

I might be missing something extraordinarily obvious... but I cannot understand 
the use case for this logic you have.

My first response was to just use "delete from emp where key=123" and be done 
with it, who cares what the name is, right?

But then it dawned on me that you may for some reason have that key NOT as a unique key, which means you can have many keys that are 
123 in which case delete where key = 123 will remove all of them, but adding a name as an optional second parameter/check now makes 
sense.


Some old-school boolean logic to the rescue then:
If this last case is true:

 delete from emp where (key = '123') AND ((name IS NULL) OR (name = 'abc'));


will simply delete all keys with 123 values, but only if the name is either not 
specified, or the name is both specified and specific.



Be careful that you might not be binding null values, but maybe empty strings 
in stead of values, so another solution might be:

 delete from emp where (key = '123') AND ((name = '') OR (name = 'abc'));


You get the idea.

Cheers,
Ryan


On 2013/11/18 09:45, d b wrote:

Hi,


   I am trying to make single query instead of below two queries. Can
somebody help?

  1. delete from emp where key = '123';
  2. delete from emp where key = '123' and name = 'abc';

if Key available, execute 1st query. if key and name available, execute 2nd
query.

  Is it possible to write in single query?

Regards,
va
___
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] query optimization

[RSmith]

Oops, misprint...

name won't be null of course, the parameter needs to be null, kindly replace 
the queries offered like this:

 delete from emp where ( key = ?1 )  AND  (( ?2 IS NULL )  OR  ( name = ?2 ));

or in the second form:

 delete from emp where ( key = ?1 )  AND  (( ?2 = '' )  OR  ( name = ?2 ));

I think this is closer to the intended - thanks,
Ryan


On 2013/11/18 12:56, RSmith wrote:

I might be missing something extraordinarily obvious... but I cannot understand 
the use case for this logic you have.

My first response was to just use "delete from emp where key=123" and be done 
with it, who cares what the name is, right?

But then it dawned on me that you may for some reason have that key NOT as a unique key, which means you can have many keys that 
are 123 in which case delete where key = 123 will remove all of them, but adding a name as an optional second parameter/check now 
makes sense.


Some old-school boolean logic to the rescue then:
If this last case is true:

 delete from emp where (key = '123') AND ((name IS NULL) OR (name = 'abc'));


will simply delete all keys with 123 values, but only if the name is either not specified, or the name is both specified and 
specific.




Be careful that you might not be binding null values, but maybe empty strings 
in stead of values, so another solution might be:

 delete from emp where (key = '123') AND ((name = '') OR (name = 'abc'));


You get the idea.

Cheers,
Ryan


On 2013/11/18 09:45, d b wrote:

Hi,


   I am trying to make single query instead of below two queries. Can
somebody help?

  1. delete from emp where key = '123';
  2. delete from emp where key = '123' and name = 'abc';

if Key available, execute 1st query. if key and name available, execute 2nd
query.

  Is it possible to write in single query?

Regards,
va
___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization

[RSmith]

Well this is the reason for my initial misunderstanding - which I then thought I had wrong, but either you have it wrong too... or I 
had it right in the first place. Ok, less cryptically now:


It all depends on whether he has a Column called "name" that might be Null, or whether he has a parameter which checks column "name" 
and which might be null... in one case (your's) we check for null values in the column and in another case (my later case) we check 
if the parameter is null, not the column, and then from that decide whether to use it as a check or not - not sure which but between 
your and my solutions both are covered though, so I hope the OP gets sorted out - if not, let us know...



On 2013/11/18 13:55, Kees Nuyt wrote:

On Mon, 18 Nov 2013 13:04:31 +0200, RSmith  wrote:


Oops, misprint...

name won't be null of course, the parameter needs to be null, kindly replace 
the queries offered like this:

  delete from emp where ( key = ?1 )  AND  (( ?2 IS NULL )  OR  ( name = ?2 ));

or in the second form:

  delete from emp where ( key = ?1 )  AND  (( ?2 = '' )  OR  ( name = ?2 ));

I think this is closer to the intended - thanks,
Ryan

Uhm, I think you mean:

delete from emp
where ( key = ?1 ) AND (( name IS NULL ) OR ( name = ?2 ));

delete from emp
where ( key = ?1 ) AND (( name = '' ) OR ( name = ?2 ));

To cover both NULL and empty:

delete from emp
where key = ?1 AND (name IS NULL OR name = '' OR name = ?2);



On 2013/11/18 12:56, RSmith wrote:

I might be missing something extraordinarily obvious... but I cannot understand 
the use case for this logic you have.

My first response was to just use "delete from emp where key=123" and be done 
with it, who cares what the name is, right?

But then it dawned on me that you may for some reason have that key NOT as a 
unique key, which means you can have many keys that
are 123 in which case delete where key = 123 will remove all of them, but 
adding a name as an optional second parameter/check now
makes sense.

Some old-school boolean logic to the rescue then:
If this last case is true:

  delete from emp where (key = '123') AND ((name IS NULL) OR (name = 'abc'));


will simply delete all keys with 123 values, but only if the name is either not 
specified, or the name is both specified and
specific.



Be careful that you might not be binding null values, but maybe empty strings 
in stead of values, so another solution might be:

  delete from emp where (key = '123') AND ((name = '') OR (name = 'abc'));


You get the idea.

Cheers,
Ryan


On 2013/11/18 09:45, d b wrote:

Hi,


I am trying to make single query instead of below two queries. Can
somebody help?

   1. delete from emp where key = '123';
   2. delete from emp where key = '123' and name = 'abc';

if Key available, execute 1st query. if key and name available, execute 2nd
query.

   Is it possible to write in single query?

Regards,
va
___
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-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] query optimization

[RSmith]

Thanks, this explanation makes it easier to understand what you are tryingto 
achieve.

I do not see any binding in your code, so let us assume you are not binding anything and just executing the query, this rework of 
your code should be the easiest:


bool delete_emp(int key, string name = "")
{
string query = "DELETE FROM `emp` WHERE (`key`="+intToStr(key)+")";

if(name.length() > 0)
{
//needs to delete specific row. by unique key.
query = query + " AND (`name`="+quotedStr(name)+")";
}

query = query + ";";  // Just add ending SQL delimeter - (pedanticness)

return sqlite3_execute(fDBHandle, query);
}


The above assumes your version of whatever language this is (looks like a C or JAVA / FLEX version of sorts) has a function that 
makes integers into strings (replace intToStr() with the correct one) and a function which can encode and double quotations for SQL 
(replace quotedStr() with whatever does that for you) and use whatever you normally use to execute the queries if it isn't 
sqlite3_execute.


Hoping this all makes sense.

Pseudo-code for quotedStr() // just incase you dont have such a thing yet - It needs to double up on quotes found and enclose it all 
in a set of quotes.


string quotedStr(sOriginStr) {
for ( n = sOriginStr.length()-1;  n > 0;  n-- ) {
if ( sOriginStr[n] = "'" )  insert ( sOriginStr(n, "'");
}
return "'" + sOriginStr + "'";
}

where "'" is a single quote enclosed in double-quotes (incase that was not 
obvious)




On 2013/11/18 14:24, d b wrote:

Hi RSmith,

   Thanks. Still, I could not delete with single query.


create table if not exists emp(key integer not null, name text not null ,
personaldata text not null, unique(key, name));
insert into emp (key, name, personaldata) values(1, 'a', 'z');
insert into emp (key, name, personaldata) values(1, 'b', 'zz');
insert into emp (key, name, personaldata) values(2, 'c', 'y');
insert into emp (key, name, personaldata) values(3, 'd', 'yy');
insert into emp (key, name, personaldata) values(1, 'e', 'yyy');

bool delete_emp(int key, string name = "")
{
 string query = ???;

 if(name.length() > 0)
 {
 //needs to delete specific row. by unique key.
 }
 else
 {
 //needs to delete rows belongs to key
 }
}


On Mon, Nov 18, 2013 at 2:13 PM, d b  wrote:


Hi Luis,

Those are parameters.

This is the query after replacing with ?1 and ?2.

delete from emp where key = '123' and (case when name = 'abc' is null THEN
1 else name = 'abc' end);

It covered  "delete from emp where key = '123' and name = 'abc';" but not
other query.

I tried with "select  (case when name = 'abc' is null THEN 1 else name =
'abc' end) from emp;"  query. It's always going to else portion when 'abc'
doesn't exist in table. Any suggestions?


___
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] query optimization

[RSmith]

Thanks RSmith.

It works.

But, I am looking for single query for prepared statements. That's the
actual struggle for me.


Ok, but you give code examples that has nothing to do with prepared statements.

Giving this one last push, I iwll try to ignore all you have said and simply show the best way to do it with prepared statements 
(much like Igor noted) in some pseudo-code trying to avoid any ambiguity - Hope this is easily understood.



function - bool deleteEmps( key_param, name_param ) {

string sQuery = "DELETE FROM `Emp` WHERE (key = ?1) AND (( ?2 = '' ) OR ( ?2 = 
name )); "

pointer stmt;
int sqlResult = sqlite3_prepareV2(dbHandle, sQuery, sQuery.length, stmt, 
null );

while ( sqlResult == SQLITE_OK || sqlResult == SQLITE_ROW )
{
if ( sqlite3_bind_int(stmt, 1, key_param) == SQLITE_OK  )
if ( sqlite3_bind_text(stmt, 2, name_param == SQLITE_OK )
{
sqlResult = sqlite3_step(stmt);
}
}

return (sqlResult == SQLITE_DONE);
}


Notes:
No need to check if the second parameter (?2) is NULL in this case, because I forcibly bind it every iteration, it can only be == 
name_param - which in turn can be empty, but cannot be regarded as NULL.


Most of the bracketing and spaces are superfluous but harmless, and only added 
for clarity.

In the bindings, I simply KNOW that I am dealing with parameters 1 and 2 because I specified them just so in the Query, but this 
should really be automated or using named parameters (:key, :name etc) with binding on names or index-of-name kind of resolves. If 
this procedure only ever does this one thing, it matters none - but that would be rather inefficient.


I assumed the code is obvious, but in case you are not familiar:
&& means logical AND
|| means logical OR.
== means testing equality
= means assignment of a value.

There are no fault reporting, typically if the return statement is reached and it is NOT SQLITE_DONE, it means something went wrong. 
The prepare and binds should all return SQLILTE_OK and the step may return SQLITE_ROW or SQLITE_DONE depending on whether it is 
finished or there are more work to be done. Of course, ANY of them can return a different value which would mean an immediate error 
occured.


This must definitely do what you require - if it doesn't, please post your 
actual code.




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

Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

[RSmith]

On 2013/11/19 00:45, Nico Williams wrote:

Thinking about how SQLite3 would implement WITHOUT ROWID, it seems
logical that a set of columns to be used as the primary indexed is
required, but that they be unique shouldn't be.

..and...  The implied UNIQUE constraint is just an unfortunate side-effect.

..and...
I can live with this for now, but I can imagine that this will become
a real problem for someone eventually.  I can envision SQLite3
requiring an INDEX be declared before any INSERTs on a WITHOUT ROWID
table,  etc.


Forgetting for a moment that WITHOUT ROWID is a very optional optimisation - Can you honestly use tables without a single unique 
reference to them?


Consider your suggestion in carnate here in the following Table on which some 
indices may exist but none are Unique:
---|  id  | attr_name   | attr_val  |
---|--|-|-|
1 |  4| ape  | Gray   |
2 |  7| ape  | Silver  |
3 |  12  | Lemur | Black  |
4 |  9| monkey   | Brown|
5 |  9| monkey   | Brown|
6 |  4| ape  | Gray   |
7 |  7| Lemur | White |


The column to the far left exists so that I can point out rows to you, but it does not exist in the table, only the ones with 
headings do. There are no unique indices nor any primary key.  How would you construct a query to fix the second monkey reference in 
row 5 (say you want it to be another animal) without affecting the other one in row 4?  Or just delete the second one and not the 
other? Or say make the first monkey white and the second black? Or detect a human error in the work of the researchers who collected 
the data?


Unless a Table is just a list of values for which you never want to adjust them, I cannot imagine how you would ever manage a 
unique-key-less table.  Especially when you make admin software that is supposed to edit tables made by other people and you run 
into a gem like the above. What do you tell the user? "Sorry mate, I have no clue how to change the 5th line for you.. either change 
both or none... sorry."  These rows are still proximitely close and a bit of skullduggery with limits etc. might get you changing 
only one of them (albeit indeterminate which one), but can you imagine editing a large table and some row somewhere else changes out 
of the view of the user?


It will be a mess.


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

Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

[RSmith]

On 2013/11/19 08:37, Nico Williams wrote:
More generally however, it's clear that a unique constraint is not necessary to make rowid-less tables work (see the MySQL 
example), SQL doesn't require unique constraints, and it's not clear that just because you (or I) lack imagination that unique 
constraints ought to be required. On the contrary, SQLite3 has never required a unique constraint -- the rowid is an 
implementation detail, one that turns out to not be necessary. Nico -- 


I think you misunderstand me - I am definitely sure that unique-key-less datasets can be useful and minimising the cpu cycles to 
deal with them is a good idea. I do think these are fringe-cases though. You however argued that SQLite should drop the unique 
constraint requirement - which led me to lament the virtues of unique constraints - as opposed to declaring them essential for all 
datasets.


 I also do not think (or imagine) that unique constraints "ought" to be required - I am saying that in the case of SQLite or any 
mainstream RDBMS it becomes necessary to have some unique reference to single out a row should you wish to adjust it somehow (often, 
adjustment are not required, such as for logs), and that (because of that) it is good that SQLite decided to have rowids, if a 
little archaic.  It's even better with the new optimisation being possible, and certainly to keep all admin things working as it 
should, I do not think it should drop the requirement.


This is very different to your suggestion that you (or I) may lack the imagination to fathom a unique-constraint-less arena - I'm 
sure anyone can name a couple use cases off-hand - but I would like SQLite to not drop the requirement altogether.


This is a statement of considered outcomes though, not a lecture of dogma.  If the efficiency gains can be shown to be significant, 
then I will change my mind very quickly.


(Let me add: ... and very much appreciate the efforts of whomever took the time 
to study and show such.)

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

Re: [sqlite] SQlite3 Query Format for Export

[RSmith]

What will be the query format to export database table data into different
export format (CSV,HTML,XML,SQL) ?



This is not an SQL function.

Do you need it exported to some format once, or do you need this often (such as 
providing it as a standard functionality to users?)

If you need it once, simply download one of the SQLite DB Managers that offers exporting. If you need it often, I'm afraid you will 
need to make the converters yourself or use some third-party plug-ins that can do it for the platform you are designing on.



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

Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

[RSmith]

Hi Nico, thanks for the discussion - although this thread is dangerously close to becoming a stale debate rather than a helpful 
forum discussion.

In this light, I will try to be brief and then fight the urge to reply again.

You still seem to try and sell the usability of non-unique tables to me, I'm with you on that, no need to keep pushing it. What I'm 
saying is even though you can conjur up valid use-cases, I am not convinced it merrits the changeover for SQLite.


A B-Tree quite clearly imposes no need for uniqueness; the uniqueness requirement in SQLite3's WITHOUT ROWID almost certainly 
derives from PRIMARY KEY being a convenient syntactic source of columns for the primary index. There are alternatives to 
overloading existing syntax; MySQL has a suitable syntax, and even without it there are alternatives. Overloading syntax is not 
always free... Nico -- 


MySQL use internal row indexers for both INNO and ISAM table types (there are others of which I do not know the workings, though), 
the only difference is that A - they are not BTrees, which SQLite chose to use, and B - there is no way to access the internal row 
pointers in MySQL through SQL statements and the only reason it's accessible in SQLite is because it mimics a normal PKey - for the 
sole reason that it essentially is one.


Again, I might be wrong about this and rowids may have been designed in lieu of internal pointers specifically to be used by people 
for some reason or the other.  A Dev with more insight weighing in on this might be a more trustworthy source.


Either way, for whatever reason(s) rowids were used, now the PKey is chosen to replace rowid since for the most part, tables that 
can perform well sans said rowid are typically those that already have PKeys and if we drop the rowid, SQLite can STILL directly 
dereference any row inside a table because it still has a unique Key to go on. I very much doubt your contention that it was merely 
a "convenient source of columns" - in fact, I would like to postulate that it would be impossible, as it stands now, for SQLite to 
directly access table rows missing both a Primary key and rowid - not merely inconvenient but I could be wrong.


Cheers!
Ryan

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

Re: [sqlite] Proper handling of SQLITE_BUSY

[RSmith]

Hi Tristan,

Do you honestly have a use-case where you do not know whether a transaction is 
going to be writing to the DB or not?

I would imagine the only way this is possible is that you are doing some form of select query, and then based on the outcome, decide 
whether or not to start writing values or not.


If this is the case, the solution is simple - Do the select query before starting the transaction. Only when you KNOW you are going 
to need to write to the DB, THEN start a transaction - and start it explicitely Immediate - if this fails, feel free to retry as 
many times as ppossible.


If you insist on starting a transaction without immediate, and later maybe upgrade it to a writing transaction (i.e. obtain a 
lock)... then it may fail (if the db is Busy) and if it does you can end and retry the entire transaction because no writes would 
have been effected yet, although it will also rerun the selects which is not efficient, but might be needed if the other thread 
writing to the DB (which made your current thread fail with SQLITE_BUSY) changed some of the data you depend on for the write 
decisions. This is easy in DB terms, but might complicate your App code - I would go with the first suggestion.


As an aside - you mention "Nested Transaction"... there is no such thing, unless you are using Savepoints. You can only ever begin 1 
single transaction, the next "BEGIN TRANSACTION" in the same DB connection will fail hard.

http://www.sqlite.org/lang_transaction.html
http://www.sqlite.org/lang_savepoint.html

To get back to Simon's advice about failing loud and you suggesting you wish to 
suppress this
if you set a sufficient time-out, you should never get Busy replies - unless you have something that updates the database for, say, 
an hour or so - some long-running update preventing your thread from doing its thing - In which case you have to ask yourself, do I 
really want to keep my user hanging-on for an hour waiting while I retry? - just so I gracefully suppress the message? If you think 
that is a good idea, you have not had many users. Let them KNOW what is going on, it's always safer. A user that doesn't know what 
is going on will use the OS managers to simply stop your task/app from running and then blame you for the broken database 
(rightfully so in IMHO) and various other nefarious things.

http://www.sqlite.org/pragma.html#pragma_busy_timeout


Cheers,
Ryan

On 2013/11/20 07:03, Tristan Van Berkom wrote:
Thanks for the slightly more definitive answer than I could get on IRC. It doesn't exactly answer my question though. More 
precisely then, in the case that a read transaction is upgraded to a write transaction, either by issuing an INSERT/DELETE command 
or by issuing a nested BEGIN IMMEDIATE command, is it safe to retry sqlite3_exec() until SQLITE_BUSY is not returned anymore ? Or, 
must the entire transaction be retried from the beginning, in the case that SQLITE_BUSY is returned at the point of this upgrade 
from read to write transaction ?

What the documentation is saying however is a word to the wise:  Always check 
your return codes, and always deal with every situation that you can foresee.  
If a situation arises for which you are not prepared then die -- as 
expeditiously and as noisily as possible.

Yes, good general advice of course.

My particular interest here is to handle SQLITE_BUSY gracefully
so that callers of my library don't have to deal with it.

Similar to how one normally would check (errno == EINTR) in
a read()/write() loop and keep writing until finished, or until
an error from which one cannot recover, occurs (instead
of propagating the cumbersome error up the call chain).

You seem to suggest that I should fail loudly in the case
of SQLITE_BUSY, this is exactly what I want to avoid.

Cheers,
 -Tristan



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

Re: [sqlite] Using multiple connections from multiple threads - SQLITE_LOCKED

[RSmith]

Hi SQLiteuser, is that really your name? - If so, bless your parents :)

Seriously though, it is quite legal (and also done mostly) to have several connections to a database. What you can't do is read data 
WHILE another thread is writing to it in serializable mode as you are using. The table has to be locked for writing else you have no 
clue in which state of update the data you are reading is.  You can change a connection's serializable mode to read uncommitted data 
with the appropriate setting, see:

http://www.sqlite.org/pragma.html#pragma_read_uncommitted

Point is, you WILL get SQLITE_BUSY errors when it is busy writing, for which you can set a time-out to accommodate fast transactions 
still committing - see:

http://www.sqlite.org/pragma.html#pragma_busy_timeout
The OS or another system may also lock the file to return those errors, or a 
thread setting this:
PRAGMA locking_mode = 'Exclusive';

But if any of those locks last too long, it will still fail with SQLITE_BUSY.

SQLITE_LOCKED, on the other hand, should only be returned if the specific Table you are attempting to access is locked by a thread 
specifically, which is only possible to get in non-serialized mode - or so I thought. (I must be wrong about this one, anyone who 
knows this specifically would like to confirm or correct me please?)

Check the extended result code too, See:
http://www.sqlite.org/c3ref/c_abort_rollback.html
After enabling the extended API, as shown here: (Needing 3.3.8 or later)
http://www.sqlite.org/c3ref/extended_result_codes.html
- which will all tell you much more specific what went wrong.


I hope this shed some light on your problem.

Cheers,
Ryan


On 2013/11/21 23:10, sqliteuser wrote:

Hi.
Is it legal to use multiple connections to a database when the db is in
serialized mode and wal is enabled. Additionally i would like to open the
connections and use them from several threads. I keep getting SQLITE_LOCKED
errors and cannot read from the db when a write transaction from another
connection is ongoing.

Regards



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Using-multiple-connections-from-multiple-threads-SQLITE-LOCKED-tp72532.html
Sent from the SQLite mailing list archive at Nabble.com.
___
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] SQLite version 3.8.2 running 2.5x faster for some queries.

[RSmith]

Agreed - also some functions might not be intrinsically deterministic, but it may well be so for the duration of a query. There may 
need to be some thinking on this.


I refer back to a discussion earlier (and subsequent SQLite adaption) which made a date-time reference deterministic within a single 
query for transactional integrity.  Similarly if one was to add a function which returns a date-dependant value, such as 
'dayOfWeek(x)' and mark it as deterministic for inside a single query, that would make sense, even though it would be very 
indeterministic (or referentially opaque, if you will) between queries.  This is all dandy unless you have queries (as seen on this 
forum) that run for three days or more... would it matter then?


I for one would very much like the ability to specify added functions as deterministic or not (or whatever term would indicate 
'cacheability of the result') as this might be an enormous efficiency improvement in itself, and also allow you to now add things to 
improve speed in some standard queries.  Think of the people who have queries running for ages and can add a function to replace an 
SQL function to improve it many times for the specific purpose.*


Actually, thinking about it, this was always achievable with some clever programming and managing the result cache yourself - but I 
still think this addition would be an improvement.


That's my 2 cents.
Have a great day all!
Ryan


*This is not to say the SQL way is not good, but it often has to cater for a wide variety of things where a user-added function 
might be very tuned to a specific purpose - ridding a lot of CPU-time fat.



On 2013/11/24 16:30, Petite Abeille wrote:

On Nov 24, 2013, at 3:17 PM, Doug Currie  wrote:


There is value in compatibility, but those adjectives are awful.

FWIW, DETERMINISTIC is what Oracle uses:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/function.htm#LNPLS1183

I would personally stick to that if such functionality was ever introduced in 
SQLite.
___
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] SQLite version 3.8.2 running 2.5x faster for some queries.

[RSmith]

Ugh, my last thought was not well-formed - apologies.

When I said:
"...can add a function to replace an SQL function to improve it many times for the 
specific purpose".

This would of course hardly matter if the SQL (or SQLite specifically) function was already deterministic (read: cached). My 
thinking was more towards enforcing determinism on something that isn't usually, if it does not affect your query's outcome (i.e 
specific purpose).


Hope that is more clear - thanks.
Ryan


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

Re: [sqlite] Your thoughts on these observations

[RSmith]

Hi L,

You seem to be after a theory rather than an actual helpful criterion - and Richard answered questions 1, 2 and 3 all in a single 
statement as far as the criterion matters.  To illuminate the theory is not really possible and presupposes a wealth of preceding 
information that must be known - something I doubt anyone will want to re-type here.


To try my hand at summarising what I think you are asking and what I think really happens without too much detail (hoping others 
will fill in where I may err):


SQLite has no direct control to a file, it operates at the other end of a VFS, which is much like a telephone conversation to 
somebody you cannot see. It asks the VFS for a handle to a file, it suppplies that handle every time it requests a file operation. 
The operations are mostly Open, Read, Write, Close and a few less important ones.  It also stores a path to an opened DB file and 
use this same mechanism for the journal files.


Specific to your question 1 : Even if it was set in stone (which it ain't) how SQLite starts a file connection, it still does not 
mean the VFS will do it in the same way. This is not just theoretical differences, there are very existing differences on how OSX or 
Windows or Linux or Android systems do this, nevermind all the custom VFSes used in the wild. So the answer becomes: "Who knows?" - 
AND, as if that's not unspecific enough, - "It can change at a whim".


Question 2: SQLite requires the data this-side-of-the-vfs to be consistent and atomic, therefore it locks files as needed and you 
cannot read a file in the middle of a write operation (I mean, what data will you be getting back? and will it be 
half-old-half-new?). When moving/renaming files - some OS systems like Linux are very happy to keep VFS comms going to a file-handle 
because the actual bytes on disk does not move, just a file-table reference to it, which you only really need as a lookup for 
acquiring a new handle. On other systems notably Android and Windows, actual bytes might be moved in part, but regardless of whether 
it is, just for the possibility thereof (and in the name of consistency with cross-volume moves), the VFS kills access to the file 
once it is moved. SQLite has no control over this and is at the mercy of the operating system on the other side of the VFS. (but it 
contains many checks and balances to ensure data stability regardless of VFS whims).


Question 3: Answered above really, but to recap - SQLite has no control over files directly, it communicates only with the VFS, 
therefore ANYTHING that goes wrong will kill the access, whether it be a move on non-movable OS, or SQLite asking for a file in a 
path (access or create) that no longer exists, or a number of other related possible issues, it's essentially unknown to SQLite and 
cannot be known comprehensively, unless you single out an OS, and even then, it may change on a whim and can never be used as a 
programmatic measure (but I don't think you were imagining it in this way anyway).



I hope this is slightly more illumiating than before. Sorry it can't be 
definitive answers.


On 2013/11/29 17:59, L. Wood wrote:

Of course, Richard has already answered question 1 and that answer definitely 
doesn't change.

Questions 2 and 3 now remain.



From: lwoo...@live.com
To: sqlite-users@sqlite.org
Date: Fri, 29 Nov 2013 15:21:51 +
Subject: Re: [sqlite] Your thoughts on these observations

Before answering my questions:

Please get rid of "rename" entirely in my post. Replace "rename/move" with just 
"move".

Also, assume that whenever I move my main database file, the -journal file 
always moves with it too.

I apologize for the confusion. My mistake. Sorry Richard.


I have made a few experimental observations and I would appreciate your 
thoughts.

1.

_open() does indeed actually open the main database file. It is left open all 
the way until _close(), when it is actually closed. Correct?

I ask because someone mentioned before that _open() doesn't really open the 
file - that it only prepares the file for opening at a later time when open is 
actually needed.


2.

If my SQL statements do nothing but read from the database (no writes), I can 
rename/move the main database file any time I wish after _open() without any 
problems whatsoever. Correct?


3.

If my SQL statements write to the database and I rename/move the main database 
file just after _open(), the next call to _step() gives me the error 
SQLITE_IOERR.

This happens NOT because the main database file has been renamed/moved. Correct?

Rather, this happens because the old path has been saved by SQLite for use by 
the -journal files. The error happens because SQLite cannot match the -journal 
file name and the main database file name (which has now changed). But here I'm 
just guessing. Is this correct? If not, then why exactly does the error occur?

___
sqlite-users maili

Re: [sqlite] What this function returns?

[RSmith]

On 2013/11/30 05:28, Igor Korot wrote:

As you can see from http://sqlite.org/c3ref/last_insert_rowid.html, it
returns sqlite3_int64, a signed 64-bit integer type.  The C99 name and
I think the C++11 name for this is int64_t, which is probably what
you want, but I vaguely recall the Microsoft compiler requires jumping
through some kind of hoop to get it.  You could just use sqlite3_int64
directly if you don't mind taking the header dependency.
Well, than I don't want the extra dependancy.
I have a nice application with dependancies set up correctly as it is
compiled on Windows.
And I wouldn't even know about it if it's not about Mac compilation. ;-)

My biggest problem is: why MSVC compiled this code just fine?

Also I am compiling 32-bit app on both platforms.


The fact that the app compiles is no great precursor to its success in the wild.  The thing you are avoiding is not a dependancy - 
64 bit types should be easy in any platform and I know Windows (anything after XP anyway) uses only 64-bit internals, much like any 
other OS since the dawn of the 21st century.  The fact that you can still compile 32 bit programs and run it is pure backward 
compatibility on any system that still allow it, so if your compiler does not support it natively, you need to upgrade.


But, that is not the case... your compiler supports it just fine - it isn't a dependancy, it's a native part of the compiler, it 
must be.  Even in older 32-bit compiling systems there's always a 64 bit LongLong or Int64 or whatever flavour it went by. The 
reason you might not be getting the same 32-vs-64 bit warning might be that your compiler is sneakily using 64 bit integers in the 
background anyway, while the other doesn't or at least, doesn't warn you about it. (This is a hypothesis, I'm not using MSVC so 
cannot say for sure).


Either way, you NEED to cast that return value to a proper data-type hat can hold all of 64 bits in it... else you will get errors, 
especially when some user of your program runs into the first >32bit number in his/her database and it turns negative, or overwrite 
previous keys, etc. etc.


You cannot ignore it, but it should be really easy to fix. Any MSVC buff here 
knows the exact Type for a 64b-int in MSVC?



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

Re: [sqlite] Percentile function in SQLite 3.8.0 (for the .NET version)

[RSmith]

Hi Hayden,

The most usual form of percentile function (I believe) is where you have record values in a selection set (or "sample" from a 
"population" for the astute statisticians), and you wish to know if you sorted them all by value, and then grouped them in clusters 
of 1% so that you have a 100 such clusters in a row all numbered from 1 to 100, then in which n-th of those little clusters would 
entry x be?


Of course in real maths the "clusters" are really exact values and non-integer and in 
a set [ c | 0 < c < 100 ].

One of the short-cut methods of getting a percentile is simple indexing, but it won't give us an accurate x.xxx-th percentile, only 
an integer n-th percentile - which might suffice for your application and is a lot quicker to do.


Indexing simply requires ordering the data-values from small to large, then finding "the first value bigger than your value" in the 
list, subtract 1, divide by the total number of values in the list ( Count() ) and round it to find value that will be very very 
close to the actual percentile (but not exact).


Similarly, finding which value represents the n-th percentile where you specify the percentile and need the value for it (as is your 
question's case), you could simply find the n-th percentile index or indices and see which value(s) are in there, the last value of 
which (in case of multiple indices) usually represents it the best, especially if you reverse-engineer it back to a percentile.


This shortcut in SQL terms then:
SELECT `latency` FROM `latencytable` WHERE 1 ORDER BY `latency` ASC LIMIT (SELECT (Count() / ( 100 / ?1 ) ) FROM `latencytable` 
WHERE 1), 1;


The value ?1 in this case needs to be the required percentile, so if you are 
looking for the 25th percentile, then ?1 = 25.
(Note: ?1 = 0 may throw exceptions )

Essentially the query counts the values, finds the x-th percentile index (call it n), then lists the table ordered ascending 
starting from offset n and listing only 1 value, et voila.


Now this will be accurate enough on large samples. On really small samples where values are widely differing it will be more 
accurate to find a set of every index that falls within a percentile and then averaging it - which can easily be added to the above, 
but should not be needed and will take much more processing.


And if you have sets that are very very small (<50 items) then you should not 
have a need to know the percentile.


Hope this helps!
Ryan

(PS. in the query, I use "WHERE 1" and lots of quotes and spaces which all help to see the query structure better, but they are very 
superfluous in SQLite terms and may well be omitted.)




On 2013/11/30 01:38, Hayden Livingston wrote:

Is there any documentation or use case for percentile queries? I've only
seen the source code checked in by "drh".

Is it like the EXCEL 2013 function?

I have a table {operationId, latency) with a set of rows that have floating
values for latencies of a given operation.

I want to find the 25th %ile latency for this rowset.

(A) How do I create a Percentile Query?
(B) How do I make it work for the SQLite System.Data.Sqlite adapter in .NET?

Many 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

Re: [sqlite] Bug in sqlite.exe? NOT !

[RSmith]

The virtualization and UAC caused many a headache for unsuspecting programmers not used to the Linux way. It's a brilliant new way 
they do it but they had to move from an old way to a the new way in a way that wouldn't break old Windows programs (too much). 
Virtualization provided just the trick at the cost of messing with a lot of people's ideas of how things work.


If I may suggest that you don't try to find a way to circumvent the UAC - though placing the DB files in "my documents" will do fine 
at that, rather embrace the UAC and get yourself a manifest file set up and then use the App-data folders in the User's folder for 
settings and a specified other folder that is not in a system-tree for shared data. (Any will do, such as C:\mydata\ - it doesn't 
care that you want to write data to the root, as long as it isn't into a protected area). [Just Google "Creating a Windows manifest" 
- the results are myriad]


With the manifest in place you are basically telling Windows "I know what Im doing and will abide by the rules of not messing with 
the protected folders, so please stop treating my program like a child and virtualizing my paths"... and Windows will happily 
oblige. You can also use the manifest to specifically require escalation to admin privileges from the current user should you need 
it (the famous pop-up that goes "Program xxx wants to write to your system folders, dangers are x y z" to which the user may then 
decide to allow or deny, and once allowed, you have almost carte blanche on where to write to. (almost...)


You could also just kill the UAC on your own machine if this is not a commercial product, Windows let's you do it... but this is a 
silly thing and it means your program will not be compatible with other user-computers on which it isn't disabled.


Cheers,
Ryan


On 2013/12/01 02:17, Eric Teutsch wrote:

Yep, that's the reason.  Thanks to Marcus and Clemens (with an extra high-5
for the link) for figuring it out, and everybody for suggestions.  Now to
figure out where to put the database so that non-admins can see the same
file as admins...
Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: November-30-13 16:15
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Bug in sqlite.exe?

Marcus Grimm wrote:

You have the DB file in ProgramData, maybe you are a victim of the
windows file virtualization ?

Given the symptoms, this is very likely.
See .


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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concrete example of corruption

[RSmith]

Hi L,

This seems to be a somewhat classic case of "If your only tool is a hammer, every 
job resembles a nail...".

Not only is Meta-data only a Mac thing, the ideal is non-reachable.

What I mean is: There is an infinite number of things that will kill a system, (any system), we single out the most pressing, and 
then the less pressing but probable, and then the not-so-probable but still obviously possible ones, and make systems prepared for 
those. Thereafter, the rest of the infinity of black holes you can step in are unlikely and there is no need to add weight to a 
system to avoid what is negligible. Add checking for 1 negligible error, and another will have merrit, fix that and another becomes 
reasonable - rinse and repeat ad infinitum.


I know that a negligible error becomes enormously a-negligible when you are the one encountering it and I sympathise - I would 
indeed encourage you to employ such checking when coding your system, and all of us might even benefit from it if you succeed and is 
generous enough to share.


Of course if the error becomes common-place, a fix whould definitely need to be considered. So far though, your case seems to be the 
only one of its kind that happened ever, or at least, got reported ever (at least that is what a google-search suggests, I took the 
lazy way out and did not search this list).



To get to an actual point:  Did this just happen once or is it a regular problem for you or a specific system you use or specific 
way you use it?




On 2013/12/02 01:35, L. Wood wrote:

Not possible. Doing any writes to the database file (such as to set a
flag) requires a journal file, otherwise a power loss in the middle of the
write can cause database corruption.

It doesn't matter at all whether the flag is a regular write to the actual file 
or merely metadata on the file (as I suggested)?

___
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] SQLite ver: 1.0.89 issue

[RSmith]

This list does not allow attachments - Could you upload them to a file-host 
site somewhere and paste the links kindly?

Thanks,
Ryan


On 2013/12/02 13:56, Nikola Boyadjiev wrote:
  
   Hello,

   I'm very sorry, the files did not attach to the previous e-mail i sent,  I 
will attach them to this one.
   Hope this doesn't cause any issues..
   Thanks,
   Nikola


___
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] Percentile function in SQLite 3.8.0 (for the .NET version)

[RSmith]

There have been a few responses to your question, I count at least 3 from 
different people all with working suggestions.

Are you sure you are getting the list emails? Maybe check spam folders etc.

Or are you unsatisfied with the responses?


On 2013/12/02 19:30, Hayden Livingston wrote:

Is there any documentation or use case for percentile queries? I've only
seen the source code checked in by "drh".

Is it like the EXCEL 2013 function?

I have a table {operationId, latency) with a set of rows that have floating
values for latencies of a given operation.

I want to find the 25th %ile latency for this rowset.

(A) How do I create a Percentile Query?
(B) How do I make it work for the SQLite System.Data.Sqlite adapter in .NET?

Many 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

Re: [sqlite] Concrete example of corruption

[RSmith]

On 2013/12/05 16:40, L. Wood wrote:

Could you be clear on what issue it is that you want
solved, and how your proposal solves it any better than
what is currently being done ?

L. Wood:
We are trying to find ways to avoid the corruption problem that D. Richard Hipp 
outlined. See his steps (1)-(5) in a previous post.


Richard did not "outline a corruption problem"... he merely stated a possible way to corrupt the database. In the same way as when I 
explain that driving your car over a cliff is a possible way to destroy it - I am being very truthful and it is a very real-world 
possibility should your driving sensibilities allow - BUT, I am in no way outlining an "over-the-cliff-driving" problem which 
warrants special adaptions to cars or should merit attention by the governments of the world in order to stifle the phenomenon.


Mainly because it isn't a phenomenon - (as others and myself have suggested in previous posts), unless I am mistaken and you have 
found it to be a phenomenon - in which case kindly supply the case study / corruption figures, which I am very sure will be taken 
seriously.


Your previous post simply asked for changes without showing any cause to do so (which I believe is what Simon suggested above), but 
the new post has done nothing different - it's still a request without providing a cause.


(May I add that changing the way in which SQLite access files has a huge impact on backward-compatibility and testing for millions 
of systems, which is not to say that it can't or won't be done, but simply that your reason for requiring the change needs to be 
specific and non-trivial).



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

Re: [sqlite] Selecting rows as "groups" -- "pivots"?

[RSmith]

One PIVOT-ing approach is per-item selects when you don't know the subject 
value - this is an exact version of your question:

CREATE TABLE `temptest` (
`ID` INTEGER PRIMARY KEY,
 `Col1` TEXT,
 `Col2` TEXT,
 `Col3` TEXT,
 `Value` TEXT
);

INSERT INTO `temptest` (`Col1`, `Col2`, `Col3`, `Value`) VALUES
('a', NULL, NULL, 'X'),
(NULL, 'a', NULL, 'Y'),
(NULL, NULL, 'a', 'Z'),
('b', NULL, NULL, 'A'),
(NULL, 'b', NULL, 'B'),
(NULL, NULL, 'b', 'C');

So the full table is now this:

IDCol1Col2Col3Value
--------- ---
1   a  X
2a Y
3a Z
4   b  A
5b B
6b C


And using this query:

SELECT (coalesce(T.Col1,T.Col2,T.Col3)) AS A,
(SELECT Value FROM temptest AS X1 WHERE X1.Col1=coalesce(T.Col1,T.Col2,T.Col3)) AS 
"Value",
(SELECT Value FROM temptest AS X1 WHERE X1.Col2=coalesce(T.Col1,T.Col2,T.Col3)) AS 
"Value",
(SELECT Value FROM temptest AS X1 WHERE X1.Col3=coalesce(T.Col1,T.Col2,T.Col3)) AS 
"Value"
FROM temptest AS T GROUP BY A;

Yields these results:

AValueValueValue
a X  Y   Z
b A  B  C


As requested.

You can shorten the SQL by using some "AS" statements etc, and not sure if it will work for whatever big query you have in mind - 
The question seems very unspecific though and the table is weird in any SQL handbook's terms, but this gets the required results.



On 2013/12/05 20:15, Hayden Livingston wrote:

I have a table schema such like

ID | Col1 | Col2 | Col3 | Value
1  anull nullX
2  null  a   nullY
3 null   null  a Z
4  b  nullnull   A
5  null  b   nullB
6 null   null  b C

Right now these are in the same table (they may be different tables in the
near future, but I don't think that impacts this discussion)

I want to "PIVOT" this data such that:

A | Value | Value | Value
a X Y  Z
b A B  C

Searching on the internet says this can be done in SQLite using CASE
statements? But what happens I don't know the row id values (i.e. a, b,
etc)?
___
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] Selecting rows as "groups" -- "pivots"?

[RSmith]

Apologies, my mail is slow today, did not notice this thread had progressed 
significantly before I posted - please ignore previous.

I'm with Igor though, the multi-table layout you now have is even less convenient than the matrix - It's equally dispersed data only 
now you have to join 3 tables for the result. Any chance you could share with us the exact thing you are trying to store and the 
exact resulting knowledge you wish to deduce from the stored data?  Maybe we can come up with more helpful suggestions (as opposed 
to just looking puzzled!).


Cheers,
Ryan

On 2013/12/05 20:54, Igor Tandetnik wrote:

On 12/5/2013 1:43 PM, Hayden Livingston wrote:

Yes, are moving our sparse matrix to different tables:

Id | RelationalIdentifier | ColA
1aX
2bA

Id | RelationalIdentifier | ColB
1aY
2bB

Id | RelationalIdentifier | ColC
1aZ
2bC


Why? Personally, I'd have one table: either

Id, RelationalIdentifier, ColA, ColB, ColC
1, a, X, Y, Z
2, b, A, B, C

or

Id, RelationalIdentifier, Col, Value
1, a, ColA, X
1, a, ColB, Y
1, a, ColC, Z
2, b, ColA, A
2, b, ColB, B
2, b, ColC, C

depending on how open-ended the list of columns is. It's also not clear what the purpose of Id and RelationalIdentifier is; they 
appear duplicative of each other.



How can I adapt your query to this new table schema?


select RelationalIdentifier, ColA, ColB, ColC
from TableA join TableB using (RelationalIdentifier) join TableC using 
(RelationalIdentifier);



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

Re: [sqlite] sqlite download for 64 bit

[RSmith]

I think the OP might mean the DLL downloaded from the site - which is W32, I don't think this can be linked into a 64-bit 
application... can it?  (I haven't attempted it).


Would the powers that be terribly mind adding a 64-bit DLL to the download list?


On 2013/12/09 16:24, Kees Nuyt wrote:

On Mon, 9 Dec 2013 17:48:34 +0530, Krishna Chaitanya Konduru 
 wrote:


hi
at the sqlite download page there is download for win 32 x86 what abut
64bit os.. would the same appllication runon both 32 and 64 bit os?? I am a
total newbie

SQLite compiled for 32 bit works fine on 32-bit and 64-bit Windows OS.

Only for very large databases you may need SQLite compiled for 64 bit.
Typically, that is not something a newbie would do.



Regards
Krishna


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

Re: [sqlite] SELECT statement failure

[RSmith]

On 2013/12/09 15:32, Simon Slavin wrote:

First, never do this:

CREATE TABLE IF NOT EXISTS T1(F1,F2,F3,F4,SUBTOTAL);

Always define your column types.  In your case you'd be using either INTEGER or 
REAL.


Agreed, those Columns have TEXT affinity by default which is wholly unsuitable for numeric comparisons such as those that prompted 
this thread.



Second, multiplying by 100 and defining columns as INTEGER to store money to 
two places /is/ the correct solution.  This means that arithmetic will 
automatically be done to two places.  You will have to test whether results are 
rounded or truncated when you do tax calculations but better still would be to 
explicitly use round() when any dividing is done.


I cannot agree with this - maybe if you are storing a simple personal budget on your home PC, but in any real-world financial 
institution or corporate finance system and even the corner-shop up your street, those milli-dollars (if you will) behind the cents 
can amount to substantial amounts over some transactions and/or time and should not ever be lost to rounding.  (In most large 
bank-systems, the accumulated transactional amounts less than 1 cent amounts to several million per month)


What I mean is - It is quite OK to represent the bottom-line profit of a multi-billion-dollar corporate to the nearest 10 million 
dollars in the report - but it is NOT OK to disregard the remainder which may be +/-5 million dollars in the calculations. It is 
similarly OK to represent or format the readout of a savings account to the nearest cent - but it is NOT OK to discard the remainder 
through rounded calculations. Rounding should only ever occur during "presentation". For all internal calculations the full value 
should be used.  Round( $ 2.3749 * TAX , 2)  !=  Round( $ 2.37 * TAX , 2) where TAX > 0.0%


Of course there is computer methodology-imposed problem with this, in that the floating-point value result of (5-2) might be 
2.8 as might be the closest possible 64-bit float binary that we can make to 3 [1]...  so rounding to *some* degree is 
advised as [(5.0 - 2) = 3] may return TRUE for some systems and FALSE for others [2] depending on type used and ALU and other system 
designs which should all be irrelevant to the actual calculation, but [Round(5.0 - 2, 6) = Round(3, 6)] will always return TRUE 
regardless of any system design.


What you need is a rounding that prevents FP errors but still small enough to not cause any financial deficits - A common finance 
system acceptable storage is 6 decimals - that is 0.01 dollar units rounding for -Calculations-, and 2 decimal places for 
-Presentations- such as on your statement. (This is by no way a generally accepted standard, just a common practice - most banks are 
even more pedantic - obviously, but it suffices for most corporates).


All that said, finding an order or invoice or any other financial system entry based on comparison of its calculated sum (or 
calculated ANYTHING) is a really really bad idea... is there a reason you are not using a Key of some sort? In general you should 
only look-up per value or calculation if you are reporting within ranges <=, >=, BETWEEN, etc.


Cheers,
Ryan


[1] - Quoted ex-memory as an example, actual figures may differ

[2] - Luckily most modern systems will Floatify both values of the = operand so that 5.0-2 and 3 both evaluate to 2.9998 and 
thus still end up being equal but this may be evaluated differently if you use <, >, <= or >= etc.  Still, we should always use a 
way that produces the correct result regardless of the cleverness/deficits of the underlying system.


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

Re: [sqlite] SELECT statement failure

[RSmith]

Thank you - I stand corrected - I should really stop quoting from memory!

On this note, a technical question: The data surely is coerced when comparing to another column that has an affinity of not "NONE" 
or assigned collation, right?


If so, what about a calculation, i.e the SQL phrase "WHERE F1 > (5 * 0.1)" assuming the F1 column has "NONE" affinity, would the 
calculation on the right have any automatic affinity, such as Numeric or Real, and would that inform the comparison?



On 2013/12/09 19:08, Richard Hipp wrote:


On Mon, Dec 9, 2013 at 11:12 AM, RSmith mailto:rsm...@rsweb.co.za>> wrote:


On 2013/12/09 15:32, Simon Slavin wrote:

First, never do this:

CREATE TABLE IF NOT EXISTS T1(F1,F2,F3,F4,SUBTOTAL);

Always define your column types.  In your case you'd be using either 
INTEGER or REAL.


Agreed, those Columns have TEXT affinity by default which is wholly 
unsuitable for numeric comparisons such as those that
prompted this thread.


The default affinity is "NONE".  Which basically means the SQLite leaves your data alone and does not try to coerce it into one 
datatype or another.


--
D. Richard Hipp
d...@sqlite.org <mailto:d...@sqlite.org>


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

Re: [sqlite] Vacuum command in a transaction?

[RSmith]

On 2013/12/11 01:41, veeresh kumar wrote:

Thanks Igor and Simon for your inputs. I was under the impression that VACUUM 
would also help performance since it does defragmentation.



Hi Veeresh,

Vacuum does de-fragment the database, it also amalgamates any transaction files and so on - so you are not wrong about that. The 
idea that it speeds up the database or improves performance if you will, is not wrong, but mostly negligible.


Consider what is meant by "defragmentation" for a database - the pointer position of a "next page" holding data would be a different 
numerical value and/or some transactional data may still reside in a different file altogether, that's all. The things that actually 
require processing time (especially within SQLite) are not really affected by this in a meaningful way.  This may well have a slight 
seek-penalty for the physical hardware even though an un-fragmented DB file might still be very much fragmented on the physical 
layer, but the modern drive caches are adequate in size to make the penalty disappear - and if you are using SSD, the point becomes 
completely moot.


Further to this, as Simon noted, 50GB is easily handled for SQLite - are your queries taking excessive amounts of time to run (as 
opposed to on PostGres/MySQL or such) or is there some other reason you believe it would improve performance? (For instance, does it 
improve performance for you currently? - If so, something else might be wrong).


On the other hand, the process of Vacuuming a database file has to do a lot of data moving and consequently takes quite a while - 
but unless you have an app running 24/7 (such as Web-Page back-end) this should not matter much.


Cheers,
Ryan

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

Re: [sqlite] REINDEX - Performance increase?

[RSmith]

We already discussed VACUUM, and REINDEX does pretty much what it says on the box. While there might be an arguably present 
performance increase, it should be negligible - unless you are using ascending or descending indices to which I am sure reindex will 
recreate the index in the proper order as with other SQL engines (but am not 100% sure in SQLite's case) or help redistribute how 
the index is spaced inside the pages. Either way, the raw performance gain should be quite small.


What I think you are looking for is ANALYZE which looks at the shape of your data more than the schema and provides meaningful 
statistics in an additional table which assists the query planner in making better query plan decisions and hopefully more efficient 
ways of stepping through a query. Note that this is an "optimisation", as in it improves how the query is executed, as opposed to 
providing a raw performance increase. Also note that there is no off-the-shelf standard on how often to analyze - It all depends on 
how often and by what degree the shape of your data might change. I'd say a change of 20GB in a 50GB DB as you mentioned, is more 
than sufficient cause to re-run analyze.


Other optimisations exist for various things and SQLite itself is always improved where possible - All this said, other than using 
the latest SQLite engine, checking your queries are not structured silly and running "analyze" once in a while - SQLite is about as 
fast as is physically possible and there is no real magic performance improvement command (if there was, it would already be part of 
the normal way SQLite does things).


Some final checks would be to ensure hardware that is as fast as possible, using enough cache and so on. If the speed is still too 
slow, you can start to think of partitioning the DB (splitting it into different smaller DBs) and then querying each in a separate 
process and amalgamating the results (effectively multi-threading the app) and so on. Another way is to have static "queried output" 
and only update the bits which have changed with some clever programming, meaning you have to firstly keep track of what changed, 
and so on.


At this point you have to ask yourself exactly how important the speed is?  While the extra programming will be quite a job, it 
might be well worth the effort if the speed is important enough.


On another note, advising the clients on which things to run is probably less safe than building in some automatic scheduled 
maintenance procedure which could include any/all of the above and be run in some off-line or off-peak period.



On 2013/12/14 02:37, veeresh kumar wrote:

We are looking for database maintenance commands for the Sqlite database and 
came across 2 commands which we felt that it would improve the performance if 
used. The commands are VACUUM and REINDEX. I came to know that VACUUM just 
helps us to reclaim the space and does not give any performance increase. Just 
wanted to check of REINDEX or any other Sqlite command which you suggest users 
to run it every month?
Our database size varies from 30GB to 50 GB,with various operations like 
insert/update/delete would be performed on day to day basis.Just wanted to know 
if there is a need to maintain the sqlite database so that the performance does 
not degrade or just leave as it is.
___
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] Unexpected SELECT results

[RSmith]

The mistake is not obvious at first (took me a few takes to figure it out) and gets obscured by the use of views.  I dismantled the 
views ruling out interplay by designing a query that should do the same sans the views, like this:


SELECT _key FROM
(
  SELECT m.project, m.date, m.time, MIN(m._key) AS _key
  FROM (
SELECT w.project, w.date, w.time, MAX(w._key) AS _key
FROM stamp AS w GROUP BY w.project
  ) AS m
);

It works perfectly well with correct results. Now if I change the primary selector to anything that does not include "_key", it 
breaks - such as:

SELECT project, time FROM
(
  SELECT m.project, m.date, m.time, MIN(m._key) AS _key
  FROM (
SELECT w.project, w.date, w.time, MAX(w._key) AS _key
FROM stamp AS w GROUP BY w.project
  ) AS m
);

The reason for this is easily understood in that MIN(m._key) does not work as intended because that query level (m) has no Group-by 
clause and as such it displays whatever m.project it finds last (which is "ws") alongside whichever m._key is the minimum where the 
m._key is not implicit in the requested results.


This does not violate the SQL standard to my best knowledge, however Interestingly, in MySQL the opposite happens in that it uses 
whichever project is FIRST on the list, being "ca" alongside the minimum key for this exact same query.


To fix this issue there are several options, my favourite for all-round clarity 
is:

SELECT * FROM
(
  SELECT m.project, m.date, m.time, m._key
  FROM (
SELECT w.project, w.date, w.time, MAX(w._key) AS _key
FROM stamp AS w GROUP BY w.project
  ) AS m ORDER BY m._key LIMIT 1
) AS v;

- which works for any requested columns in the primary selector. (The standard actually requires every sub-query to be uniquely 
identifiable, hence the "AS v" at the end, even though SQLite is forgiving about this, it won't work in PostGres or MySQL etc.)

You can easily decompose this into views to achieve the same.

There are of course other ways to do it and if the query ever gets very big - into many thousands of lines in the implied "w" table, 
some aggregation may be better.


Hope this helps!
Ryan


On 2013/12/19 17:54, Louis Jean-Richard wrote:

Hello,

I run into an unexpected result from a SELECT on a view
in one of my schema for which I have distilled out the
following test script:

-- test.script
DROP TABLE IF EXISTS stamp
;
CREATE TABLE stamp
( dateCHAR(10)NOT NULL
, timeCHAR(8) NOT NULL
, project CHAR(2)
, _keyCHAR(19)
)
;
DROP TRIGGER IF EXISTS stamp_added_key
;
CREATE TRIGGER stamp_added_key AFTER INSERT ON stamp
FOR EACH ROW WHEN new._key IS NULL
BEGIN
UPDATE stamp SET _key = date || 'T' || time;
END
;
DROP VIEW IF EXISTS most_recent_stamp
;
CREATE VIEW most_recent_stamp AS
SELECT w.project, w.date, w.time
 , MAX(w._key) AS _key
FROM stamp AS w
GROUP BY w.project
;
DROP VIEW IF EXISTS oldest_among_recent_stamp
;
CREATE VIEW oldest_among_recent_stamp AS
SELECT m.project
 , m.date
 , m.time
 , MIN(m._key) AS _key
FROM most_recent_stamp AS m
;
DELETE FROM stamp
;
INSERT INTO stamp (date, time, project)
VALUES ('2013-11-14','07:38:48','ra')
 , ('2013-11-14','07:43:45','sp')
 , ('2013-11-14','07:52:19','ws')
 , ('2013-11-14','07:53:46','ca')
 , ('2013-11-14','07:58:43','lj')
 , ('2013-11-14','08:07:32','ma')
 , ('2013-11-15','08:08:50','ra')
 , ('2013-11-15','08:14:21','sp')
 , ('2013-11-15','08:22:01','ws')
 , ('2013-11-15','08:23:50','ca')
 , ('2013-11-15','09:03:42','ma')
 , ('2013-11-15','09:08:42','ra')
 , ('2013-11-15','09:13:42','sp')
 , ('2013-11-15','09:18:42','ws')
;
-- test sequence
select 'most recent stamps:';
select * from most_recent_stamp;
select '  - OK';
select 'oldest among recent stamps:';
select * from oldest_among_recent_stamp;
select '  - OK';
select 'oldest among recent stamps: (just show the project code)';
select project from oldest_among_recent_stamp;
select '  - WRONG';
select project, _key from oldest_among_recent_stamp;
select '  - OK';
select project, time from oldest_among_recent_stamp;
select '  - WRONG';
-- EOF

Here what my Linux with Ubuntu displays upon running it:

...$> ./sqlite3
SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read bug.script
most recent stamps:
ca|2013-11-15|08:23:50|2013-11-15T08:23:50
lj|2013-11-14|07:58:43|2013-11-14T07:58:43
ma|2013-11-15|09:03:42|2013-11-15T09:03:42
ra|2013-11-15|09:08:42|2013-11-15T09:08:42
sp|2013-11-15|09:13:42|2013-11-15T09:13:42
ws|2013-11-15|09:18:42|2013-11-15T09:18:42
  - OK
oldest among recent stamps:
lj|2013-11-14|07:58:43|2013-11-14T07:58:43
  - OK
oldest among recent stamps: (just show its project code)
ws
  - WRONG
lj|2013-11-14T07:58:43
  - OK
ws|09:18:42
  - WRONG

sqlite>

For me the view 'oldest_among_recent_st

Re: [sqlite] Does not detect invalid column name when subexpression optimized away

[RSmith]

With this query you essentially ask the RDBMS to evaluate and supply you with the result of (X and 0) - my guess is the optimiser 
pounces directly on the fact that (X and 0) will always be 0 no matter what X is so that it does not bother trying to evaluate X 
which means it never has the need to resolve the name and so never needs to raise the error.


It's like if I ask you to deliver me a zamooki... you may need to report back 
that you haven't the faintest clue what it is...

But if I ask you to NOT deliver me a zamooki, you could just say "DONE!" and go 
on with your life without a bother.

That said, the standard requires every specified value to be evaluatable, whether or not it needs to be... but it takes extra CPU 
cycles to determine this and if it is not needed, SQLite does not bother to waste the resource simply to check whether you are 
asking sane questions.


Feature or bug?  a bit of both maybe. To adequately describe this sort of 
happenstance, I propose the word: Fug.

Cheers!
Ryan

On 2013/12/20 01:11, Zsbán Ambrus wrote:

Why does the following select statement succeed, instead of giving a
"no column error"?  Is this a feature or a bug?

sqlite> select nonsensename and 0;
0
sqlite> .version
SQLite 3.8.1 2013-10-17 12:57:35 c78be6d786c19073b3a6730dfe3fb1be54f5657a

The select statement is not a subquery and has no from clause, so
nonsensename is a column that should not exist and so should give an
error in my opinion.

Ambrus
___
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] Does not detect invalid column name when subexpression optimized away

[RSmith]

On 2013/12/20 06:11, David Bicking wrote:

But isn't NULL and 0 a NULL? So wouldn't it need to evaluate X to determine if 
it was null, and thus discover it wasn't a valid column name and return an 
error?

David


It's hard to make a case for it though. I could argue both sides from first 
principles -

If I have it right, the effective statement (when using AND) becomes a Boolean type evaluate, and while it might be represented by 
0, it really means "false", so that (X and 0) = (X and false) = false for any X, even if it is Null, as opposed to X + 0 which 
evaluates to X for any X and NULL if X is NULL - ok, that seems superfluous to even mention but it is significant, in that X + 5 
evaluates to X + 5 for any X and NULL if X is NULL because NULL + 5 has no footing in arithmetic reality since the sets are not of 
the same kind and is equivalent to trying to evaluate Apple + Wednesday. In contrast, (X AND 0) is a logic statement and not an 
arithmetic one, meaning that (Apple AND false) is still false, regardless of the base of the sets.


On the other hand, you are not wrong and a case can be made for your suggestion and one could argue it would be closer following of 
the standard to check operands for validity prior to any evaluation.


My point being that it's really an edge case and probably fair to go for "whatever is most efficient", and I for one would prefer 
saving the clock cycles, few as it may be.  Is this causing your systems to not work correctly? I imagine at the very least you 
spent a lot of debugging time trying to hunt down this peculiarity for whatever dissonance it caused, but now that you've 
successfully caught it and know the system's logic, is it still a big problem?



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

Re: [sqlite] Does not detect invalid column name when subexpression optimized away

[RSmith]

On 2013/12/20 14:09, Simon Slavin wrote:

On 20 Dec 2013, at 12:05pm, Dan Kennedy  wrote:


"1 OR unknown" is not unknown, it is 1. And so on. To summarize:

  sqlite> SELECT (0 AND NULL), (1 AND NULL), (0 OR NULL), (1 OR NULL);
  0|null|null|1

Well if you're so smart,

(A) Anything divided by itself is 1.
(B) Anything divided by zero is infinity.
(C) What is 0 / 0 ?

There.  That should keep you busy through the new year break.


Heh - Sqlite fun threads for christmas!

Of course A and B above are not the only mathematical rules in existence. 0 / 0 is called indeterminate since there is no possible 
value x where ( x * 0  ) =/= 0. Of course the axiomatic equation:

  x * 0 = 0
can be re-shuffled to show that:
  0 = 0 / x
when dividing by x both sides, but when dividing by 0 both sides it yields:
  0 / 0 = x / 0
which means x has to be zero for any value of x, which is itself contradictory and cannot ever be the case  - and it also 
illuminates the reason why 0 / 0 = 1 can never be assumed.


What phased me more was the day my school teacher (eons ago) tried to explain 
why:
x ^ 0 = 1 for any x...
Personally I found this much harder to swallow than the indeterminate form of 
0/0.  :)


Well... that was the better part of 4 minutes... We'll need a few more 
conundrums to breach the new-years break, let's have it!



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

Re: [sqlite] GROUP BY months and years using integer data

[RSmith]

You are basically trying to group values where the individual values are different but each in itself accumulated in stead of 
accumulated for the grouping.


Just move the scope of the grouping and use Nulls in stead of 0's, like this:

SELECT stats.which_year AS year,
SUM(CASE WHEN stats.which_month =  1  THEN stats.quantity ELSE NULL END) AS
gen,
SUM(CASE WHEN stats.which_month =  2  THEN stats.quantity ELSE NULL END) AS
feb,
SUM(CASE WHEN stats.which_month =  3  THEN stats.quantity ELSE NULL END) AS
mar,
SUM(CASE WHEN stats.which_month =  4  THEN stats.quantity ELSE NULL END) AS
apr,
SUM(CASE WHEN stats.which_month =  5  THEN stats.quantity ELSE NULL END) AS
mag,
SUM(CASE WHEN stats.which_month =  6  THEN stats.quantity ELSE NULL END) AS
giu,
SUM(CASE WHEN stats.which_month =  7  THEN stats.quantity ELSE NULL END) AS
lug,
SUM(CASE WHEN stats.which_month =  8  THEN stats.quantity ELSE NULL END) AS
ago,
SUM(CASE WHEN stats.which_month =  9  THEN stats.quantity ELSE NULL END) AS
sett,
SUM(CASE WHEN stats.which_month =  10  THEN stats.quantity ELSE NULL END) AS
ott,
SUM(CASE WHEN stats.which_month =  11  THEN stats.quantity ELSE NULL END) AS
nov,
SUM(CASE WHEN stats.which_month =  12  THEN stats.quantity ELSE NULL END) AS
dic,
stats.test_id AS test_id
FROM stats
WHERE stats.test_id = 420
GROUP BY which_year


Have a great day!
Ryan

On 2013/12/21 10:42, Giuseppe Costanzi wrote:

HI all,
I've a table such

CREATE TABLE 'stats' (
 'stat_id' INTEGER PRIMARY KEY,
 'test_id' INTEGER,
 'quantity' INTEGER,
 'which_month' INTEGER,
 'which_year' INTEGER,
)

and I need to extract data with somenthing like

SELECT

stats.which_year AS year,

CASE WHEN stats.which_month =  1  THEN SUM(stats.quantity) ELSE 0 END AS
gen,
CASE WHEN stats.which_month =  2  THEN SUM(stats.quantity) ELSE 0 END AS
feb,
CASE WHEN stats.which_month =  3  THEN SUM(stats.quantity) ELSE 0 END AS
mar,
CASE WHEN stats.which_month =  4  THEN SUM(stats.quantity) ELSE 0 END AS
apr,
CASE WHEN stats.which_month =  5  THEN SUM(stats.quantity) ELSE 0 END AS
mag,
CASE WHEN stats.which_month =  6  THEN SUM(stats.quantity) ELSE 0 END AS
giu,
CASE WHEN stats.which_month =  7  THEN SUM(stats.quantity) ELSE 0 END AS
lug,
CASE WHEN stats.which_month =  8  THEN SUM(stats.quantity) ELSE 0 END AS
ago,
CASE WHEN stats.which_month =  9  THEN SUM(stats.quantity) ELSE 0 END AS
sett,
CASE WHEN stats.which_month =  10  THEN SUM(stats.quantity) ELSE 0 END AS
ott,
CASE WHEN stats.which_month =  11  THEN SUM(stats.quantity) ELSE 0 END AS
nov,
CASE WHEN stats.which_month =  12  THEN SUM(stats.quantity) ELSE 0 END AS
dic,

stats.test_id AS test_id

FROM stats

WHERE  stats.test_id = 420
GROUP BY which_year


that return this

"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "71769" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "68972" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "65075" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "50605" "420"

if I add which_month in th e GROUP BY clause I've


"2009" "5994" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "6112" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "7046" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "5947" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "6471" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "6027" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "5841" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "3352" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "6564" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "7075" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6283" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "5057" "420"
"2010" "6112" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "6201" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "6890" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "5907" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "6246" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "5667" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "5185" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "3269" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "5963" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6520" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6162" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "4850" "420"
"2011" "5888" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "6027" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "6756" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "5889" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "6276" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "5985" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "4968" "0"

Re: [sqlite] GROUP BY months and years using integer data

[RSmith]

Boolean Logic 101 - Feel free to skip if this is not your thread!
-

In addition to other replies - Boolean logic is interesting in that it has no real arithmetic value and can have only true or false 
as a value. It doesn't fit too well as a high-level storage value of a system even though it permeates low-level storage in every 
way -  it can't be null for instance, but in an RDBMS it _can_ be Null and it _can_ be arithmetic by taking on the values 0 and 1 
for false and true respectively. This has the added advantage that it can be used in arithmetic statements which is what Igor used 
to save considerable amounts of typing in your query.


Whether or not it is faster in execution than a case statement is probably up to some testing, but I am willing to bet it is 
somewhat faster considering the relative complexity of a CASE statement.


Any statement containing a comparison operator ends up being a Boolean statement because it can be found to either be so, or not be 
so. Month is not Boolean, but (Month = January) is definitely Boolean, because the comparison is either true or false depending on 
the value of "Month".


Similarly any attribute can be Boolean if it is found to be an attribute of an object. Giuseppe is not Boolean, but he is human and 
likely male, so that:

(Giuseppe = Human) is true or 1, and
(Giuseppe = Female) is false or 0.

For RDBMS and indeed most programming languages, any Boolean evaluated result can directly be inferred as the arithmetic values 0 
and 1 so that I could make some programmatic assumptive statements based on it, such as:


HumanX's fertility period recurs every (28 * (HumanX = Female)) days. This will be 0 days for men (28 * false) = (28 * 0) = 0, and 
28 for ladies as (28 * true) = (28 * 1) = 28 in the same logic.


Similarly your odds of balding is roughly 0.04 + (35/Age)*((HumanX = Male) * 10) since men have a roughly 40% chance of balding by 
age 35 which is about 10 times more likely than women and the odds increase with age[1].



Hope this makes clear how Booleans are used mathematically in high-level code. 
Have a great day!
Ryan


[1] - http://www.statisticbrain.com/hair-loss-statistics/


On 2013/12/21 22:24, Giuseppe Costanzi wrote:

...but stats.which_month is not a boolean, it represent  a month...




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

Re: [sqlite] GROUP BY months and years using integer data

[RSmith]

On 2013/12/22 09:55, Giuseppe Costanzi wrote:

I don't know if I have understood well but the statment

SUM(stats.quantity * (stats.which_month = 1))
SUM(stats.quantity * (stats.which_month = 2))
should be interpreted

SUM stats.quantity IF stats.which_month = 1 is TRUE
SUM stats.quantity IF stats.which_month = 2 is TRUE



Kind of yes, although for more clarity I'd propose it stated like this:

For JAN: Results = SUM  ( value )  WHERE (
  value = (stats.quantity * _1_)  WHEN month is JAN
  value = (stats.quantity * _0_)  WHEN month is NOT JAN
)

For FEB: Results = SUM  ( value )  WHERE (
  value = (stats.quantity * _1_)  WHEN month is FEB
  value = (stats.quantity * _0_)  WHEN month is NOT FEB
)

etc.
So that in all cases, "value" contains the value of stats.quantity when the selected month shows up in the iteration loop, and Zero 
when any other month shows up, so Summing the values (or indeed doing any other kind of aggregate function on it) will only affect 
stats.quantity values for the specific months.


Apologies for the roundabout way of explaining - I never quite had to explain this specific kind of thing to anyone, it's usually a 
bit embedded - and I'm sure the same goes for most others on this forum!


Cheers,
Ryan






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

Re: [sqlite] GROUP BY months and years using integer data

[RSmith]

On 2013/12/22 20:53, James K. Lowden wrote:



Similarly any attribute can be Boolean if it is found to be an
attribute of an object. Giuseppe is not Boolean, but he is human and
likely male, so that: (Giuseppe = Human) is true or 1, and
(Giuseppe = Female) is false or 0.

For RDBMS and indeed most programming languages, any Boolean
evaluated result can directly be inferred as the arithmetic values 0
and 1 so that I could make some programmatic assumptive statements
based

Yes, it's a bit of a trick.

Boolean logic operates on true and false values.  Any two distinct
symbols can be used.  It so happens SQLite apparently uses
1 and 0, but e.g. -1 and 0 or 'T' and 'F' would serve as well.


True, when a character interpretation is given, but when converted to a value, as in Integer Byte value, it is always 1 and 0 with 
every compiler in existence - however this may not necessarily be the case in SQL (I will have to look up the Standard to actually 
confirm this), but it _is_ the case with SQLite on any platform because the interpretation is subjective to the SQLite engine's 
interpretation of parsed SQL and not the storage medium or processor of the target platform, as opposed to register bit-quantities 
and the like, as far as I know.


Good point to notice or remind people of, when asking questions here and may 
try to use similar methods on other SQL engines.


stats.quantity * (stats.which_month = 1)
is *logically* nonsensical: the product of a numerical quantity and a
a logical one.  What would, say, "7 times false" mean?

"false false false false false false false"?

Haha, that is really ( false ^ 7 ), which is not true!

I wonder if ( true! ) = true...

( 7 * false ) is false - obviously... but ( 7 * True ) can still be false - if 
you try to argue with my ex wife.

Cheers!
Ryan


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

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

[RSmith]

A field name is an identifier, not just a string, so mostly it can be done in a direct SQL statement since the very idea of a select 
is that you must at least know what you are selecting for...


That said, in MySQL / PostGres (for instance) can query the schema tables where a list of all fields are kept on a per table as a 
standard select sub-query (with glob/like) list and then use that with IN to pick columns. Not in SQLite though, unless you can 
parse the "create table".


There are probably a few approaches that would work, but I can think of none quicker/more efficient than maintaining a list of 
column names ("SELECT * from t WHERE 1 LIMIT 1" will produce it real quick)  then grep that into a list used for building the next 
queries (SELECT ?1, ?2, etc.) according to the list.


So to put that into English: Sorry, don't think you can do that, but good luck!


On 2013/12/26 12:49, dean gwilliam wrote:

...is this possible or should I return the whole lot and subject it to a regexp 
filter to get my cut-down list?
Any advice much appreciated and BTWmerry Christmas to you all!
___
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] select only records with fields that BEGIN with a certain sub-string

[RSmith]

This is actually awesome to know, thanks Stephen, I always thought at least 1 step is needed - I'm going to immediately implement 
this in some functions!


On 2013/12/26 13:30, Stephan Beal wrote:



There are probably a few approaches that would work, but I can think of
none quicker/more efficient than maintaining a list of column names
("SELECT * from t WHERE 1 LIMIT 1" will produce it real quick)

If you want JUST the column names, you can change WHERE 1 to WHERE 0 (and,
optionally, remove the LIMIT). You don't actually need any result rows -
you just need to prepare the statement, and then you can fetch the column
names regardless of whether or not the query would produce any results (you
don't even need to step() it).



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

Re: [sqlite] "Common Table Expression"

[RSmith]

This reminds me of a plan to add RADAR dishes to cars to monitor other traffic and avoid collisions - brilliant idea but the 
detrimental effect on aerodynamics and limiting size-factor of already-built garages all over the world stifled enthusiasm.


Probably "Temporary Views" would be the exact thing that can achieve the same as CTE.  Further simplification might be implemented 
on your code, if in fact you are designing a system and not using some other SQLite-reliant system (in which case CTE might really 
help you).


To emphasize what Simon said: SQLite does not support a full syntactic script engine with variables and the like and isn't likely to 
expand by doing it and/or include CTE for the simple reason that the cost tradeoff in DB-Engine size vs. added functionality is 
non-sensical.  It has to function in many cases as a DB engine on embedded systems where space is a real concern, and those 
designers would dread the idea of devoting more memory in the name of readability - and it is probably fair to extend that sentiment 
to designers with non-embedded systems (I know it is true for me).


One might probably add a compile-time switch enabling or disabling (or including) a CTE component so that the feature and related 
space-consumption might be optional, but if you prefer CTE for your SQL, I am confident that compile-time switching won't be your 
favourite thing either.  Further to this, the effort / pleasure ratio of adding it would probably prove larger than comfortable.


As an aside, proper use of comment-blocks and inline commenting (which both your code and SQLite allows) can make anything as clear 
you'd like.


Have a great day!
Ryan

On 2013/12/26 21:05, Simon Slavin wrote:

On 26 Dec 2013, at 6:57pm, big stone  wrote:


"sub-select" method :
- allows you to do only a part of what you can do in CTE,
- becomes more and more difficult to read as you add tables and treatment
in your sql.

With CTE in SQLite, it would be possible to:
- decompose your SQL treatment in clean intermediate steps,
- make your global algorithm "visible",
- do easily things currently impossible in 1 query with sub-selects.

It looks like you want VIEWs rather than sub-selects.  VIEWs enable all the 
things you listed above.



They're a way of saving a SELECT command so it can be used as if the results 
are a table.

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


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

Re: [sqlite] "Common Table Expression"

[RSmith]

Sorry, this struck a bit of a sore spot with me, so I apologize for the small 
rant...  Feel free to completely ignore it.
You have every right challenging the views of anyone - It is welcome even (I think - cannot speak for everyone else though, but I 
appreciate it). A rant however is probably less effective, but let's see...



CTEs are important for two reasons://.. many valid points to be addressed 
below...//

Non-bug-related posts to this list often take the form of one of the following 
few categories:
1. Underqualified programmers asking for query help to do their jobs that a 
qualified programmer could easily do.  Doesn't belong on the list -- I'm sure 
there's a #sql-newbies list somewhere for things like this, and there should be 
a form-letter answer forwarding people to that.


I'm sorry, are you saying this list is only for bugs and feature requests and helping people use SQLite in normal and mundane ways 
do not belong here?  If this is true, I shall take my leave immediately because I have no bugs to report and the dev-list is ample 
forum for new functionality requests - and kindly point me to where helping people use SQL/SQLite (or gaining such help) is 
acceptable, THAT be the list I meant to sign up with.



2. Feature requests from underqualified programmers that don't realize the 
right way to do something.  Doesn't really belong on the list, though they 
mostly get shot down pretty quick or someone points out the obvious answer.  
Whatever, doesn't take up much mental/email bandwidth.  I can go either way on 
this.


How would you tell that you don't know the right way to do something?  Put another way, how would you know that the only way you 
know how to do something is not the right way or not the best way? You cannot know something until you find it out, so if you are 
unaware as to whether you seek a fix to how SQLite works, an understanding of how it works, or whether the way you do it is wrong, 
how would you know which list to choose to post on?


That said, once you do ask and offer up your way of doing it, if it does get shown to be incorrect or that a better way exists - 
that is a helpful learning experience. I think the words "shot down" is unfair. The only times I have seen direct harsh wording of 
the kind you allude to used on here was in reply to persistant insistance on very obviously incorrect methodology, or rants.



3. True feature requests that are not implemented in SQLite and would be useful 
to a set of users/developers in some way/shape/form and is not directly 
workaroundable.

What I don't like is how often #3 requests gets shot down as being stupid.  
Yes, often a feature request doesn't really fit with the general mantra of 
SQLite, and it can be easily described as such.  However, many things are in a 
pretty grey area.  For example, CTEs would fit fairly nicely with the general 
mantra of SQLite, since it allows for making things smaller/simpler/more 
explicit for the QO, but it's being shot down as a non-useful feature that can 
be worked around.  Well, can it?  Or does sqlite perform the subselect multiple 
times if you mention the same query a couple different times in subtly 
different ways (case sensitive, etc.)?  There are important nuances here before 
completely dismissing something out of hand.


Again I am sorry, I do not think myself, Simon or anyone else "shot down the request as being stupid" or in fact do so "regularly".  
In point of fact, I took great pains to explain that it is a good request and pretty valid too, and even now I admit to you it would 
be a nice addition - what I explained was why it probably won't get added and then continued to offer alternate methods of achieving 
what the OP was hoping to achieve. Is this not precisely what contributors here are for?  Never once did I insinuate stupidity on 
the part of the OP for even daring to utter such a silly request (paraphrasing a bit, I know) - Not sure how this invoked the rant, 
but I sincerely hope you do not maintain this opinion.



Just because there is another way to do something doesn't mean it's not a valid feature 
request to be prioritized with the rest of the feature requests.  Saying "this is a 
potential future feature someday, but due to [some architecture issue] it's actually 
quite complicated to implement, and, as such, is unlikely to ever be actually 
implemented" is a completely valid answer from a project management perspective.  
Simply dismissing something out of hand without a thorough explanation of why, however, 
isn't quite as valid.


Nobody dismissed the idea out of hand nor has the power to do so, though it's a hard sell but if it gains favour universally, or 
even just in significant numbers, I'm sure the push for the feature might become paramount and as such likely to be implemented. As 
of now I count only 2 requests for it and I am not even sure if the above is a real request for it or just a vehicle for delivering 
an opinion. In addi

Re: [sqlite] Web application with SQLite

[RSmith]

To add to other answers:
SQLite is a great DB back-end - I believe the Website at www.sqlite.org and Fossil repositories hosting the code there are all 
running on SQLite (if you fancy browsing an SQLite site to compare).  PHP natively supports SQLite, MySQL, Postgres and MSSQL, which 
means the choice is arbitrary to your web interface. Not exactly sure what DBs are all supported with ASP or other IIS-based systems 
(I stopped using ASP long ago, nothing wrong with it, just that PHP is awesome).


As for comparing SQLite with MySQL or PostGres - the answer is probably as you 
would expect:
SQLite is a lot more portable, a lot easier to contain (by virtue of it's file-usage as already high-lighted by others) and in 
general very easy to back up or quickly download / upload the single file to backup or fix it or whatever else is needed. The Memory 
footprint is always quite small but it does offer some adjustment -  which helps if your server is either low on memory or you host 
a zillion databases.  It has no significant security unless you use the encryption tools offered - if your file gets accessed (which 
the server security should prevent mostly), the Data is open if not encrypted.


MySQL / PostGres / MSSQL are proper client-server based systems and, depending on the install mode, will consume all your available 
server memory and load as much as possible DBs entirely into memory-cache so that it is blisteringly fast, especially with many 
simultaneous user connections. (MSSQL is a bit fat in my opinion with a rather large footprint and loads of added cool functions 
that nobody really uses, but not too bad, the others are smaller and faster though). The user-access model on these makes security 
concerns happy too.


Other random considerations: Talking about PHP specifically, though it supports all the above-mentioned, it seems to have special 
soft-spots for MySQL and PostGres - especially the newer "mysqli" interfacing objects provide real quick and easy integration and 
the phpMyAdmin DB manager is pure magic. Also, the shortest path is usually the one you know, so unless there are specific 
architectural concerns or violations, use the system you know well - this should save development time. On the other hand, if you 
are not hard-pressed for time, learning a new system is always a good idea.



PS to Rob Willet: LOL - yeah I never really understand the flamewars. As a user of probably the widest selection of systems it seems 
to me the only people who get into arguments are the one-trick ponies trying to justify their own subsistence. C is obviously 
awesome and I wouldn't try to write a server in any other way. For rapidly developing user-interfaces though, it is horrible and 
C++/C# and especially Rad studio/Delphi do much better jobs of it. (VB seems to be the only one that is universally bad, but VS 
works well as most game-devs would attest). For adding web interface functionality HTML5, JAVA and FLEX is brilliant and for web 
services PHP/ASP etc. are brilliant, I can go on...Flamewarring about which system is best is rather like arguing about whether 
it is better to drive a car, a motorcycle or a bus. Before you can argue you have to ask, how fast do I need to get there, and how 
many passengers do I need to take with. It is easy to see the answer being meaningless until these are known.  The same goes for the 
*nix, Windows, OSX, Android etc wars I develop for all of them they all are better at something than the others. The fanboyism 
that sometimes creep up on forums are usually down to lack of knowledge or interest. That said, if you can define working specs, 
intended audience, etc. clearly, it is usually easy to pick one over the other based on those specs, since they are all very 
different but not one of them is very bad (else it wouldn't still be around).



On 2013/12/27 23:14, Igor Korot wrote:

Hi ALL,
Does people use SQLite for Web development? Or web apps are written
only with mySQL/MS SQL?

What are pros/cons of usinf SQLite for Web app?

Thank you.
___
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] "Common Table Expression"

[RSmith]

You're right :
*"*CTEs ... add exactly zero to SQLite's capability."

This is also right :
"C Language ... add exactly zero to Intel X86 processor capability".

In both case :
- "adding zero capability" to the underlying tool is a physical constraint,
- CTE (or C Language) bring capabilities to the users, by simplifying the
use of the underlying tool.


I'm trying to no longer weigh in on this subject since previous attempts have been met with some sharp criticism, but I cannot help 
point out the fantastically large difference between the above statements.  James is saying that CTE's do not add any functionality 
(so long as recursive queries are absent of course) to the SQL itself (not some higher-order other language that uses SQL in some way).


At a stretch you might say, when considering loop improvements, that "CTE is to SQL  _as_ a For-loop statement is to C (as opposed 
to a while-loop)", but it's hard to see how "CTE is to SQL _as_ C is to X86" can ever be a valid analogy.


You (Bigstone) have provided great suggestions on how it might be implemented, why it should be implemented and indeed clear 
explanations to the benefit of using CTE SQL.  Responders have pointed out that while this is true, unless recursive query 
capability is also added, the only advantage will be semi-better legibility at the cost of significant added size, overhead and 
code. Also that we'd all like it to work easier, just not at that price.


It is not a suggestion that is shot down or in any way invalid - just not feasible currently, but might well be so in future pending 
developments. I also applaud the "Friends of CTE in SQLite" initiative, but starting on the development before the goal has been 
shown to be appealing to the devs is much like building a sandcastle and then pointing out how easy it was to urge the activity when 
everybody else has brought surf-boards.


Also, the above explains what seems to be the current ruling opinion and is not a law or a set-in-stone working model laid down by 
anyone, least of all me.


That said, it's hard to see how this subject has carried so far.

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

Re: [sqlite] Adobe Air do not include on sqlite database

[RSmith]

If this is on Windows, the UAC might virtualize your file to a different folder so that Adobe Air actually sees a different file. 
Try to not store DB files inside \program-files\ or other system-folders.


The actual file might be typically somewhere inside:
c:\Users\your_username\roaming\your_app-name\

(App-name might be "Adobe" or "Air" or some such)

Cheers,
Ryan

On 2014/01/02 23:29, Digradi Socobaris wrote:

My app Adobe Air try to save some data in a sqlite 3 database, but the app
when insert a data, it does without problem, but i cant see the data when
browse by the lita or sqlite browser. It look like empty table.

select * from table  <- adobe air return ok
insert into table (values...) <- adobe air return ok, but table stil empty !

Despite table empty, the adobe air work how the data was inserted indeed,
but the table stil empty when you browse with sqlite browser.


___
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] "Common Table Expression"

[RSmith]

On 2014/01/05 00:03, Petite Abeille wrote:
Things change. Syntax evolves. Languages matures, even SQL. The ‘with’ clause is a change for the better. As is merge. As are 
windowing functions. SQLite cannot pretend it’s 1986 forever. It has to move with the times or it will become ossified, obsolete, 
a mummy. 


But of course, things evolve and SQLite (like any other system) needs to keep up with times!  This is however much the same as 
telling somebody that they must breathe else they will slowly lose oxygen and suffocate. I think in America the term "Captain 
Obvious" is used for the author of such a statement.


To be clear: the statement is overly obvious and does nothing to aid the point - we are not disagreeing about the need to evolve, we 
both (along with many other distinguished members of this list) feel that it should evolve - AND I am happy to report that it is 
evolving as is evidenced by the regular updates and releases and oft-added functionality. Changes must be planned and reasonable 
though.


The point of contention is _how_ it should evolve, not _whether_ it should evolve - so your statement urging to push forward is a 
moot point - things _are_ moving forward and are changing almost daily, whether you like it or not, even though it may not move in 
the format and time-step you envisioned.
(I am quite sure you already know all this and merely appealed to the general wisdom of "change is good" to try and support your 
unrelated point, so please forgive my dissertation).


Lastly, a small digression:  I put it to you that if SQLite stops evolving completely today, and stays as is (with exception of 
bug-fixes), it will STILL last longer as the DB of choice for 90% of current implementations long after you and I have met our 
demise. If you do not agree on this point you may have no real idea of the actual width of implementation of SQLite3. It is this 
same width of implementation that makes us disagree on what should be added and what not. I will forgo another iteration on my POV 
as I think James K.L. made it very clear already.


May you (and all other list members of course) have an awesome 2014!
Ryan

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

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[RSmith]

On 2014/01/22 23:33, dean gwilliam wrote:

I'm just wondering what my options are here?
Any advice much appreciated.


Firstly, high-five on using D5 - All the High-speed Pascal/C /Assembler coding goodness with none of the fat (as far as 32-bit goes 
anyway) - I use it all the time for critical/server side apps or services, and XE3 or newer for RAD stuff.  I use D5/XE libraries 
from open sources but adapted over time (which you are welcome to have) all working exceptionally well.


Biggest stumbling block for us on D5 is no natively supported UTF8 strings... the type "String" in D5 still aliased to AnsiString 
which is not MBCS/Unicode compatible, or at least, suitable converters did not exist at the time.  In Later versions (2009 onwards) 
"String" started referring to "WideString" or now the newest native type: "UTF8String" - all capable of Unicode seamless conversion, 
so no problem these days. When using D5 I simply don't consider it for systems with Unicode requirements, or run it through explicit 
converters (which adds some cycles but still get the job done pretty fast). Of course it is only one thing solved to be able to 
store a Unicode anything in a DB, but it also lacks the ability to display it correctly in, say, a TLabel or TEdit sans native 
Unicode-string properties. (Again - all fixed since 2009).


As an aside:  for-loops had a really bad implementation in D5 (a fix from earlier versions went slower by an order of magnitude, it 
was fixed again in D7 I think) - best to use while-do or repeat-until loops for speedy needs.  It really is time to lay D5 to rest, 
but it still is my favourite toy.


Fascinating article here on String history in Delphi:
http://www.codexterity.com/delphistrings.htm

On the IDE side, D5 IDE does not keep revision history or integrate SVN in any way and profilers/memory leak checkers exist but are 
not included natively.


These mentioned problems aside, it's still one of the sleekest compilers around producing sub-350KB optimized exe's  for a 
hello-world full windows-forms application and compiles rock-steady huge applications in milliseconds every time, making the 
altering/testing/debugging cycle the biggest pleasure.


Oh yes - plus D5/D7 is free nowdays. (Or rather, officially it does not exist anymore, neither any official legal controls for 
it) which makes it great for start-up devs shying away from the 4000+ dollar XE[n] behemoths.


Hope some of this helps!
Ryan

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

Re: [sqlite] too many SQL variables

[RSmith]

Just for my edification, what is the limit on the number of SQL parameters?
Today I hit "too may SQL variables" with about 1400…
Just for our edification, which kind of statement was that?



The worst kind

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

[sqlite] Database Grammar 101

[RSmith]

I know this is a Database forum (as opposed to a language forum) but kindly allow me a quick interjection here since I have met this 
question many times, as posed by Scott in a forum question:


On 2014/02/01 06:01, Scott Robison wrote:
Exerpt: ...// *and* information that led to creation of ideal indexes (indices?). //... 


The plural of Index is always "Indices", never "Indexes".

An Index (as a noun) means (among other things) a reference to a place, i.e. "Story 
XXX can be found on page Y".
It could also mean a placeholder, such as a card attached to, and protruding from, page Y 
announcing "Story XXX starts here".

In Database terms it mainly refers to a list/column of Key-value references from which the exact placement of the rest of the record 
in the Database data tree/store/list can be deduced, i.e. it always refers to the noun "Index" and as such the plural is always 
"Indices".


Index can however be a verb too: "Mary, please index this book for us..."

Or in present continuous form: "Mary is indexing the book."

And of course simple present transitive verb form: "I will Index this book while Mary Indexes that book..." - which is the only 
valid form of the word "Indexes".


This even true for "American English" which sometimes get the blame for words used differently to "English English" (what a 
tautological oxymoron!). So to be clear:

  "Indexes" = Present tense of the Verb: "Index".
  "Indices" = Plural of the Noun: "Index".

Hope this clears it up some!


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

Re: [sqlite] Database Grammar 101

[RSmith]

Heh, was waiting for someone to point that out :)

For the record, so does Oxford English dictionary - and on reconsideration, I should have been more clear. Indexes are very much 
"allowed" these days a plural of Index when not used in a technical sense, but (and this is a big but), this is due to vernacular 
morphing, not root meaning - i.e. the other form has become acceptable because of a relaxing of a rule since so many people use it 
differently, but the Latin root word is still "Indic" found as the root for other words such as "Indicate" etc.


Other words facing the same sort of scrutiny are Vertex (pl. Vertices vs. Vertexes) or Apex (pl. Apices vs. Apexes) but of these, 
Indexes seem far more used. I'm a pedant though, and just because everyone uses it wrong does not convince me of using it wrong too 
- but one can't really argue with MW or Oxford on this - so, whatever you feel like then!


As an aside, there are other similar Latin origin words that have become acceptable when Englishified, such as "Crematoria" which 
may now be called "Crematoriums" and of course the very widely used "Forums" which used to be "Fora".


All this said, when used in Mathematical/Technical environments (such as SQL), it must still be "Indices" and not "Indexes", even if 
the Wall-street Journal allows the other form!


Some more reading for the astute or interested:
  http://www.oxforddictionaries.com/definition/english/index
  http://www.worldwidewords.org/qa/qa-ind2.htm


On 2014/02/01 13:43, Richard Hipp wrote:




On Sat, Feb 1, 2014 at 5:59 AM, RSmith mailto:rsm...@rsweb.co.za>> wrote:

I know this is a Database forum (as opposed to a language forum) but kindly 
allow me a quick interjection here since I have
met this question many times, as posed by Scott in a forum question:

On 2014/02/01 06:01, Scott Robison wrote:

Exerpt: ...// *and* information that led to creation of ideal indexes 
(indices?). //...


The plural of Index is always "Indices", never "Indexes".


Merriam-Webster disagrees.

--
D. Richard Hipp
d...@sqlite.org <mailto:d...@sqlite.org>


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

Re: [sqlite] Free Page Data usage

[RSmith]

A database that is geared for 32TB size and you are concerned about rather insignificant space wasted by the page size that is 
needed to reach the 32TB max size... does not make any sense unless you are simply paranoid about space.  Removing the gaps in the 
table space when deleting a row (or rows) will render a delete query several magnitudes slower.


If it IS that big of a concern, then maybe use standard files rather than SQLite to save data in?  If the SQL functionality is a 
must, you can use vacuum as often as is needed to clear unused space - but beware, 1 - Vacuum takes some processing to re-pack a DB, 
especially a near 32TB one... in the order of minutes on a computer I would guess, and much much more on anything else.  2 - a 32TB 
DB will need up to 64TB total free disk space to be sure to vacuum correctly - so having issues with it taking up maybe 40TB for 
32TB of data is in itself an irrelevant concern. Even large queries, temporary tables etc will all need additional interim space for 
the sorts of queries that might be requested of a 32TB data-set.


The real point being: if you do not have at least 64TB free on whatever that 32TB DB will sit, you are doing it wrong, and if you do 
have that much free, you can ignore the 25% wasted deletion space problem.


If the problem is simply your own pedanticism (at least I can sympathise with that!) then it's simply a case of "Welcome to 
efficient databasing", but if it is a real space deficit, then I'm afraid you will have to re-plan or reconsider either the max 
allowable DB, or the physical layer's space availability - sorry.



On 2014/02/07 20:35, Raheel Gupta wrote:

Hi,

I use a page size of 64 KB. But my row consists of 2 columns that is :
i - Auto Increment Integer,
b - 4096 Bytes of BLOB data

Now for the sake of calculation, lets say 16 rows fit in a page and my
table has 1 rows when I start.

Now, lets say I delete some data which is not in sequence i.e. it can be
deleted as per data which is not in use. To create such a hypothetical
situation for explaining this to you, here is a simple query :
DELETE from TABLE where i%4 = 0;

As you may see that there is now 25% data deleted in each page.

Now even if I do insert another 2500 rows (25% of original size) my
database size reaches 125% of the original size when I inserted the 1
rows initially.

Hence there is significant space wastage. Anyway i can improve that ?
It would be nice if the database size would be close to the original size
after deleting 25% and adding some new 25% data.

I know you would recommend to use smaller page sizes. Ideally 2KP page size
is good but then, the number of pages is restricted to a max of 2^32 which
will restrict the total database size to 4TB only. I need the max size to
be capable of atleast 32TB.



On Fri, Feb 7, 2014 at 11:14 PM, Donald Griggs  wrote:


Can you write more about how this is causing you a problem? Most users
don't experience this as a problem
On Feb 7, 2014 10:30 AM, "Raheel Gupta"  wrote:


SQLite's tables are B-trees, sorted by the rowid.  Your new data will
probably get an autoincremented rowid, which will be appended at the

end

of the table.

A page gets reorganized only when about 2/3 is free space.


Anyway to make this ratio to lets say 1/3 ?
___
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-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] Free Page Data usage

[RSmith]

Hi Raheel,

It does make sense what you would like to do, but your concern does not make sense. You say you are "trying to optimize the 
utilization of the free space available" but give no indication why, it certainly does not seem that space is a problem.


I do understand the urge to optimize very much, but inside a Database engine you can optimize either for speed or for size, not for 
both. SQLIte as it stands is quite good at not wasting space unnecessarily, BUT, it is first and foremost optimized for speed (Thank 
goodness for that), which means the space-saving you are looking for is not going to happen.  In my previous post I made a passing 
comment / suggestion re using your own data files in stead of sqlite, and if it is a case of not needing the sql ability - which I 
seriously doubt since you are basically saving blocks of information from a blocked device and doing so as byte streams (or BLOB 
fields in SQL terms) - then I seriously suggest creating your own files and custom index mechanism and saving the byte streams in 
there.  It will be a lot faster and with zero space wastage and the size limits can be whatever you like them to be.


Trying to use SQLite (or any other DB engine) for this purpose is akin to using a full-function bakery with ovens, humidifiers, 
provers, rising agents and bake timers when you just want to warm up your pizza (not to mention being restricted by the limitations 
that come with it).


No matter what size you make the pages, a delete function is never going to re-pack the db, though you might get better results at 
re-using the space - but this is a compromise and one that does not sit well with you (if I read you right).


Best of luck!
Ryan


On 2014/02/08 07:57, Raheel Gupta wrote:

Hi,
Sir, the 32 TB size is not always going to be reached.
The Database is going to be used to store blocks of a Block Device like 
/dev/sda1
The size can reach 3-4 TB easily and would start from atleast 20-100 GB. 32 TB of data though impractical as of today will be 
possible in 2-3 years.
The issue happens when I delete the rows and new rows are inserted at the end of the database the size of the database exceeds 
that of the actual block device size even though many pages are having free space.

Hence I am simply trying to optimize the utilization of the free space 
available.
I would have loved to use the page size of 2KB which would give me a practical size of 4TB. But that would have this hard limit of 
4TB.

So I have two possible options which I am trying to help me solve this issue :
1) Either make the page size to 2KB and increase the maximum page count to 2^64 
which will be more than sufficient.
2) Improve the free space utilization of each page when the page size is 64KB.
I hope this makes sense.



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

Re: [sqlite] struggling with a query

[RSmith]

One way of doing it:

SELECT IFNULL(V1.name,V2.name) AS VName, CASE WHEN V1.name=V2.name THEN 0 ELSE 
-1 END AS VInd
 FROM v AS V1
 LEFT JOIN v AS V2 ON (V1.vid<>V2.vid) AND (V1.name=V2.name)
 WHERE V1.vid=1
UNION
SELECT IFNULL(V1.name,V2.name) AS VName, CASE WHEN V1.name=V2.name THEN 0 ELSE 
1 END AS VInd
  FROM v AS V1
  LEFT JOIN v AS V2 ON (V1.vid<>V2.vid) AND (V1.name=V2.name)
WHERE V1.vid=2;

Running that on your table yields:
VName"VInd"
bar0
baz1
foo-1

I'm sure someone will have a more succint or optimized version soon :)


On 2014/02/08 12:03, Stephan Beal wrote:

Hi, list,

most of the time i judge my SQL skills as mediocre, but at times like this
i feel like a complete noob...

i have table containing a mapping of logic dataset versions and filenames
contained in that dataset version:

CREATE TABLE v(vid,name);
INSERT INTO "v" VALUES(1,'foo');
INSERT INTO "v" VALUES(1,'bar');
INSERT INTO "v" VALUES(2,'bar');
INSERT INTO "v" VALUES(2,'baz');

i am trying like mad to, but can't seem formulate a query with 2 version
number inputs (1 and 2 in this case) and creates a result set with these
columns:

- name. must include all names across both versions
- status: -1 if in version 1 but not v2, 0 if in both, 1 if in v2 but not
v1.

So the above data set should produce:

foo, -1
bar, 0
baz, 1

My SQL skills fail me miserably, though.

i have no sqlite3 minimum version requirements (am working from the trunk)
and am free to use recursive select if necessary, but my instinct says that
this should be possible with joins and a CASE (for the status).

Any prods in the right direction would be much appreciated,



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

Re: [sqlite] struggling with a query

[RSmith]

Yeah I quite like some of the solutions posted - got to love this list :)

One final optimization, since those values you are looking for essentially maps to Boolean (0 and 1), this query is the smallest and 
probably fastest (I think) that will produce the correct results from your table:


SELECT name, max(vid=2)-max(vid=1) FROM v GROUP BY name;

So much more succint than my original, like I predicted :)


On 2014/02/08 13:11, Stephan Beal wrote:

On Sat, Feb 8, 2014 at 11:58 AM, big stone  wrote:


with sqlite 3.8.3 (for the with) :

with v(vid,name) as (values (1,'foo'),(1,'bar'),(2,'bar'),(2,'baz'))

select  name,
  -max(case when vid=1 then 1 else 0 end ) + max(case when vid=2
then 1 else 0 end)
  from v group by name


i like that one. This slight variation (to allow me to strategically place
the inputs) works for me:


BEGIN TRANSACTION;
DROP TABLE IF EXISTS vf;
CREATE TABLE vf(vid,name);
INSERT INTO "vf" VALUES(1,'foo');
INSERT INTO "vf" VALUES(1,'bar');
INSERT INTO "vf" VALUES(1,'barz');
INSERT INTO "vf" VALUES(2,'bar');
INSERT INTO "vf" VALUES(2,'baz');
INSERT INTO "vf" VALUES(2,'barz');
COMMIT;

with
origin (v1,v2) as (select 1 v1, 2 v2),
v(vid,name) as (select vid,name from vf)
select  name,
  -max(case when vid=origin.v1 then 1 else 0 end )
  + max(case when vid=origin.v2 then 1 else 0 end)
  from v, origin group by name
;

sqlite> .read x.sql
bar|0
barz|0
baz|1
foo|-1

Thank you very much :).



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

Re: [sqlite] struggling with a query

[RSmith]

Just to be clear, it isn't really "mine", just an adaption of the many 
excellent contributions, from which I too have learned.
A huge pleasure and fun exercise no less!


On 2014/02/08 14:35, Stephan Beal wrote:

On Sat, Feb 8, 2014 at 12:39 PM, RSmith  wrote:


SELECT name, max(vid=2)-max(vid=1) FROM v GROUP BY name;

So much more succint than my original, like I predicted :)


Indeed!!! This one wins if i am able to refactor it for use with the much
more complex structure i'm actually working with (the fossil SCM's vfile
table - my example is a simplified form to help me get my head around the
SQL).

FWIW, sqlite3's ".stats" say yours is overall more efficient:

Virtual Machine Steps:   242
vs the WITH variant i posted:
Virtual Machine Steps:   308


Thanks again!



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

Re: [sqlite] Free Page Data usage

[RSmith]

On 2014/02/08 19:30, Raheel Gupta wrote:

@Simon, Sir I dont want to rearrange the data.

I will try to explain more.
All my rows have the exact same size. They will not differ in size.
My problem is due to the fact that I use 64kB page size.
My rows are exactly 8 Bytes + 4096 Bytes.

Now for the purpose of ease in calculation lets assume each row is exactly
4 KB.
So one page stores 16 rows.
Lets say 10 pages are in use and I have a total of 160 rows.


Sir,

We do understand exactly what you mean, no amount  of re-explaining will improve a 100% comprehension, and because we do understand, 
we know SQLite ain't doing it, and we are trying to offer other ways of achieving what you want to achieve, but this is not the road 
you seem to want to go down...  Which is OK.


The basic thing you need to understand is this: SQLite does not work the way you hope, it is not made to do the sort of work within 
the sort of restrictions you prescribe. Please consider using an alternative, or, accept the space vs. usage parameters. Even if you 
could adjust the code of SQLite to allow re-using pages with 1/3 free space (as opposed to 2/3 free space), then you are doomed 
because the code will be untested (unless you can download and run the entire test suite without errors) and even then, you will 
have to manually rebuild and repair and re-test your own version of the DB every time a new release happens and forever in future. 
Is this really feasible?  And even then... there is no guarantee SQLIte will re-use the exact rowids that fit inside a specific 
page, not to mention it will only even consider reusing a key if you did not specify "AUTOINCREMENT" in the schema (which, at least, 
is unlikely and fixable).


If you absolutely have to use SQLite, then maybe you can keep track of deleted rows, and in stead of deleting them, just mark them 
as "not used" while keeping the rowid or whatever primary key is used - add this key to a list of available keys maybe (to be 
faster), and when inserting new rows, first see if you have any items in your list of unused rows, then write them to that primary 
key using REPLACE etc.


A typical Schema could be like this:
CREATE TABLE datablocks (ID INTEGER PRIMARY KEY, Used INT DEFAULT 1, Data BLOB);
CREATE TABLE availrows (ID INTEGER PRIMARY KEY);

Some Pseudo code...
when deleting a row/rows:
UPDATE datablocks SET Used=0 WHERE ID=somerowid;
REPLACE INTO availrows VALUES (somerowid);

when adding a row
availRowID = (SELECT ID FROM availrows LIMIT 1);
if (availRowID!=NULL) then if (DELETE FROM availrows WHERE ID=availRowID) != SQL_OK then availRowID = NULL;  // Needed to ensure you 
can never overwrite a datablock

if (availRowID != NULL) then {
  REPLACE INTO datablocks (availRowID,1,blobValue);
} else {
  INSERT INTO datablocks (Data) VALUES (blobValue);
}

Of course adding BLOBs have some more processing to do, but you get the idea.

This way, no row will ever go unused and inserts wont ever use up any space other than that which already exists, unless no space 
exists, so the DB size will only grow if you have more actual data rows than before. Also, btw, this will have significant 
performance improvements if row-deletion is common.


Queries that need to check through the lists of data can simply reference the "Used" column in the where clause to ensure they list 
only rows that do contain valid data-blocks. (SELECT ... WHERE Used>0, etc.)


Of course, the caveat here is this other index-type table will consume a significant amount of diskspace on a DB the size you 
describe. Maybe have that in another DB file with different page size parameters too.


If it was me though, I would save the blob streams in another bytestreamed file, and only save the other data about it with indexes 
in the SQLite table, since you cannot really use a BLOB in a Where clause or for any other SQL-related function. Do the queries, get 
the index.. read the stream from the other file... SQLIte file size will be negligibly small and the data file will only ever be as 
big as is needed...  easy!


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

Re: [sqlite] Free Page Data usage

[RSmith]

On 2014/02/09 12:06, Raheel Gupta wrote:

Hi,

Sir, I have only one auto increment primary key.
Since the new rows will always have a higher number will the pages
which have some free space by deleting rows with lower numbered keys never
be reused ? e.g. If row with ROWID "1" was deleted and freed, will it not
be used to store the NEW row which will be assigned ROWID 10001 ?


Yes. That is the point of AutoIncrement, every new Key will always be higher than any previous key ever used, and always exactly one 
higher than the highest ever previously used key. As such, it cannot be re-used within pages that are half filled from deletion 
(except maybe the last page), and I believe pages that go completely empty may be re-used without the need to vacuum etc. (need 
someone to confirm this).


I think if you have 2 columns, one with Autoinc Integer and another one with rowid alias as primary key (but not auto-incremented) 
you can get page re-using... but it will still be subject to many factors and never really mimic a full re-using system as you 
really want.


You could of course make the keys yourself, no need to leave it up to the DB to autoincrement them, which might be a cheap solution 
to the problem, but I would still much more favour separate file(s) with byte data alongside an SQLite indexing DB - it would be the 
least work with highest guarantee of working flawlessly.



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

Re: [sqlite] LIKE operator and collations

[RSmith]

On 2014/02/09 13:18, Constantine Yannakopoulos wrote:

Hello all,

I have a case where the user needs to perform a search in a text column of
a table with many rows. Typically the user enters the first n matching
characters as a search string and the application issues a SELECT statement
that uses the LIKE operator with the search string:

SELECT * FROM ATable WHERE AColumn LIKE :SearchString || '%'

According to the LIKE optimization this statement will use an index so it
will be fast.


This can't be true, there is no way a LIKE or GLOB operator can always use an Index (I mean, it can "use" the Indexed column, but it 
cannot always reduce the number of iterations via Indexing).


To explain more what I mean, consider a simple binary search, let's say we have an ordered list of names that we use the alphabet as 
an indexer:


0:Abraham
1:Ben
2:Constantine
3:Igor
4:James
5:John
6:Ryan
7:Simon

A binary search would start by hitting the middle item typically ( i = Length div 2 ) which is 4 in this case, then comparing it to 
the searched item, let's say it is "JOH" in this case.  It sees that Idx 4 is James, which is smaller than "JOH" (no-case collation 
enabled), then looks to divide the remainder of items larger than James (Idx 5, 6 and 7) in two (3 div 2 = 1 ) and adds it to the 
bottom of them (5) so it checks Index 6, which is now Ryan and is higher than "JOH", it then divides into below Ryan and above James 
and obviously gets "John" which is a match.


A binary tree works similar with the difference it does not have to divide anything, the tree node children are already divisive so 
it just follows down the node closest to the target match until a definite match or matches is/are found (depending on search criteria).


The list above does however demonstrate why a LIKE operator cannot always use an Index, let's say I'm using a search for LIKE '%n', 
how on Earth would you be able to look for that by binary jumping through the list? ANY Indexed item might end on an n, indeed 4 of 
those above do, there is no way to tell and a full-table scan is inevitable.


Of course some clever DB systems, of which SQLite is one, can detect when you use LIKE "Jam%" and knows this is index-searchable and 
still use the Index, but it all depends on what you type and where those % signs are - something which is again negated if the 
search collation does not match the column collation, but is rather easy when standard text or binary collations are used.




store two text columns in the table.  The first is the text as entered.
  The second is your text reduced to its simplified searchable form,
probably all LATIN characters, perhaps using some sort of soundex.  Search
on the second column but return the text in the first.

This allows you to write your conversion routine in the language you're
using to do the rest of your programming, instead of having to express it
as a SQLite function.


Thanks for your reply,


Yes. I thought of that as well. I even have the greek soundex() function
from a previous implementation. Problem is it will bloat the database
considerably, keeping in mind that the users will typically demand that 
ALL
searchable text columns in the application work that way, and who can 
blame
them? And the project manager will not be very keen on accepting both 
this
database size increase and the time needed to calculate the extra 
soundex
column for every database row. It will be much easier to convince this
person to accept the time-costly database upgrade needed in both cases
(tables need to be recreated to change collation) but not both upgrade 
and
bloat.


And I am not happy to accept the fact that I cannot fly, but the laws of the 
Universe demands I adhere to the deficit, and when I simply have to fly, employ 
the help of a very large costly winged tube with jet engines attached to it!

It will be a matter of finding the most acceptable deficit... Whether it be 
size, time waste, upgrade cost etc.  By the way, I don't think upgrading the 
table schemata need to be a real hard thing... some scripts can take care of 
that in minimum amount of time. (Test them thoroughly though). Also, another 
poster here had developed a full set of international collations and comparison 
mechanisms as a loadable extension to SQLite - Nunicode by Aleksey Tulinov I 
think... link here:

https://bitbucket.org/alekseyt/nunicode





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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

[RSmith]

On 2014/02/10 18:22, John McKown wrote:

Being a UNIX (Linux) partisan, and somewhat tacky towards Windows users,
why not go the normal Windows route of having a "pop up" dialog box (or at
least a message) similar to what normal Windows applications say about
possible loss of data. Something along the lines of "You are exiting
sqlite3, but there is data in one or more memory resident tables which will
be lost. Proceed (Y or N)?"


Being a Windows partisan and somewhat untacky to downright loving of all other systems and uses, allow me to explain the way Windows 
works quickly - it has a kernel which runs services and programs, just like unix/linux/etc, and then it has a very standardized very 
elaborate graphical user interface system which attaches graphical areas (commonly known as "Forms") to underlying processes - much 
like OSX etc.


Running a process as a command-line or shell process means it can be really lightweight and devoid of any of the mentioned graphical 
user-interfacy stuff, with the added benefit that, a few specific adaptations aside, you can use much the same C codebase to make it 
run on linux or whatever else, much like the discussed slite3 tool. As such, attaching a pop-up anything to the process requires it 
to have evolved into a GUI-supporting system so it has parent windows to have the popped-up handles attached to - a change which is 
simple, but would see the exe auto-double in size.  So to answer your suggestion - no, that's not a good idea.


To further this point, there must be a quadrillion free full graphical-interface SQLite tools on every OS out there... why on earth 
do students not simply use any of those?


FWIW, my vote goes with the current mainstream opinion, leave as is, warn when quitting, possibly provide a .save command. Forcing 
the shell-window closed (Alt-F4, Click close, Alt-SPACE->C, Task manager, whatever) will steal any opportunity for ever having a 
warning of any kind - but then, this is true for any other shell tool, why would it be a special requirement for sqlite3.exe? I 
don't think anybody would expect to have their DB saved if they forcibly shutdown ANY app, only when exiting normally, one should be 
at least warned of a potential loss.


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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

[RSmith]

On 2014/02/10 20:31, Petite Abeille wrote:

On Feb 10, 2014, at 4:23 PM, Richard Hipp  wrote:


Proposed Change To Address The Problem:

What’s the problem exactly? CS101 students distress? That’s way beyond SQLite 
reach.

My 2¢: don’t create a default persistent database. This is not helpful to 
anyone.



I agree 100% on both points, except to say that if a simple change can be found that do not alter the function but can make it work 
better for everyone, then it would be a crime not to implement.


I quite like another poster's idea, Change nothing, but warn when starting sqlite3.exe, not when quitting. An easy function to 
commit an in-memory DB to disk will be great too, though I believe .backup can do this already (though another suggestion to alias 
it to .save have merit). None of this will break current implementations or scripts.



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

Re: [sqlite] help needed for major SQLite problem

[RSmith]

On 2014/02/10 21:18, C M wrote:

Documents\My Dropbox\myapp\gorp.db-journal) - Access is denied. (3338)
SQLITE_IOERR

SQLITE_LOG: statement aborts at 16: [SELECT resumes, start FROM
WHERE start='2014-02-07 14:24:14.064000' AND value='activity'] disk I/O
error (3338) SQLITE_IOERR

Looks like GetFileAttributesEx() might be throwing an ERROR_ACCESS_DENIED
exception. Maybe a virus scanner or some other background process had
temporarily locked the database file.

Dan.

I agree, and I think Dropbox is the culprit here.

May I ask either Dan or Kees, or anyone here, how to go from the error
codes to that diagnosis?

Kees, why do you think Dropbox is the culprit?

I may want to deploy this app to users who would also backup their database
by having it in the Dropbox folder.  What would people suggest I do about
this?


How to go from the error codes to the diagnosis? I think the logic is as 
follows:

We can see an error occurs when trying to access the file, or more specifically, trying to obtain a shared lock on it. This means it 
is locked by another application (as opposed to another SQLite thread). Now the question remains which other application? We would 
usually simply suggest to look in your system, but you already provided a log of the error, and it is clear from the error that a 
file you are trying to access is in "Documents\My Dropbox\myapp\" which, as everyone knows, is a dropbox folder, which means likely 
you have dropbox installed. Secondly, Dropbox is a known culprit in this regard, because it syncs files with the cloud (it is not 
the only one, Skydrive, Google drive etc all do this), which means it will have to lock a file while either uploading or 
download-syncing it for consistency and concurrency reasons.  Put these three pieces of evidence together, and the answer is 
inevitable - you probably have dropbox problems.


The remedy is not easy - same as when dealing with Excel exports or some other system that will lock files of it's own volition if 
it is opened with that system - simply making a byte-copy of the file, changing it and replacing it afterwards, with a possible 
replace-queue facility which will wait till a lock is released. Problem is, what if the other app made changes that you actually 
mean to keep?


To put this into your perspective, what if the file was dropboxed, altered on another machine of the user's, or by another user 
(through a dropbox share), and is now updated in the cloud and due to sync back?  Whatever solution, versioning-control or other 
system you come up with to handle this, it has to be full of user-informative messages and you can never keep an editable file where 
locking might be a problem inside a dropbox (or other locking+syncing) folder.


It is better to have a DB file (meaning a file that gets small incremental changes over time as opposed to a load-once, save-once 
methodology) in a place that is not affected by locks, and sometimes exporting (using the SQLIte backup facility maybe) to the 
dropbox or shared folder so it gets propagated to the cloud...  Using it within that folder is just not feasible.


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

Re: [sqlite] help needed for major SQLite problem

[RSmith]

On 2014/02/10 23:20, C M wrote:

On Mon, Feb 10, 2014 at 2:54 PM, RSmith  wrote:


How to go from the error codes to the diagnosis? I think the logic is as
follows:
[lots of snipping]

Thanks for this insight.

I purposefully put the SQlite database file in the Dropbox folder because
it was my intention, with this app, to allow a user to use the app on more
than one computer and "sync" the database via Dropbox.  E.g., s/he could
make changes to the db at home and then also from his/her office computer,
get home, and the database would be synced.  I tried this out on two
computers at home and it seemed to be sort of working, but I occasionally
got conflicted copies and yet never pursued the right way to do it.

But this must be a fairly commonly sought need.  The solution you propose
where I occasionally export a copy of the db to Dropbox is great *for
backup purposes*  but seems to exclude the possibility of syncing across
multiple computers.  So what would you recommend?


This is something all the systems we make do all the time, and I am sure is a common thing with most people on this list, so we 
definitely understand the need to sync. Problem is Dropbox (or any of the other ones) is a file-syncing solution and not suitable 
for direct DB syncs for reasons we covered already.


With most database syncing we just use actual cloud servers and cloud syncs with an actual database, i.e we have several cloud 
servers running typically Apache, PHP, MySQL, SQLite, etc. to which all systems connect and either upload or download additions, 
changes and the like. Simple versioning within the tables keep track of who updated most recently on a per-record basis, sometimes 
per-field for really critical apps, but this is uncommon.


What you essentially trying to do here is allow another system to take care of the syncing for you with the very real benefit that 
you don't need to support a cloud server. The downside is that you have to play by the rules of whatever service is used.


I will be honest here, for every suggestion I can come up with, I can find a scenario where it would break in a cloud-sync 
environment. We actually have one system that uses cloud syncing through external service (Dropbox, Skydrive, Google drive, whatever 
the user uses), but the we use custom data files that play nice with the services, the SQLite (or any other RDBMS) files simply 
won't work. This means some extra work. Basically we create a computer-based key (hash), write all changes from the DB to a file 
with that key as part of the name in the sync folder (MyDocs/Dropbox/ or whatever the user sets up). Any other system with which it 
is shared sees that file appear in its syncing folder, and as soon as it becomes available (checking every few seconds or so), opens 
it, adds the updates contained within it if those updates are newer than it's own last changes for the specified records/keys.  In 
turn, it will write a file containing its own newest changes etc.


SO any system connected to the cloud sync, sees all files created by all other computers, there may be many, but they are typically 
very small. Every system incorporates updates from every other system's file if it is newer, but only ever creates its own update 
file for its own changes and only really does that on a timely basis when the file is not locked. Every time updates are exported 
only the newest updates are included and most important, what actually makes it all work, is that every such file has only 1 writer, 
nobody else changes it - so you won't ever get the file trying to sync-back to your own folder or other related problems.


A headache we have is that one of the participant systems might go down for several days, user on holiday, whatever, and then it is 
so far out of date that the update files it sees no longer contains some of the stuff it missed, in which case the user is prompted 
to get a full DB backup from another user, and they can email it or dropbox it as another file or whatever.


ALL of these shenanigans simply because of the way the cloud syncs work - a rather cumbersome work-around, but hey, the 
cloud-syncing is not on us, so a win.


I tried to be scant with details and give basic ideas because different approaches might be better depending on the exact nature of 
the data and frequency of changes and importance of updates etc. etc. but feel free to ask off-list if needing more detail lest we 
bore the others.



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

Re: [sqlite] help needed for major SQLite problem

[RSmith]

On 2014/02/10 23:40, Stephen Chrzanowski wrote:

Personally, I don't buy that DropBox is the culprit as I've done this kind
of thing a few times in a few applications of my own, however, I'm the
single user that works on that single account, and any app that uses DB is
usually under development and "closed" on any other geographical site.
However, with the chance that the WAL file makes it down the wire, I could
see that as being a problem if your application spits out small bits of
data periodically and is being run in multiple places.  DropBox, for me at
least, realizes that another application has a handle on the database file
and won't touch it, or overwrite anything.  But the WAL file might make it
through.


Yes, Dropbox realises when YOU are locking the file, the problem comes when you try to open the file while IT is locking it - 
something that will only really happen on an update (upload) or back-sync (download when it was changed elsewhere).


Heh, just try to open the DB on another computer it is sync'ed with while you have it open on your computer, then change stuff in 
both systems and close the apps (to release any SQLite locks)... You will quickly realise what a culprit Dropbox can be. Now imagine 
it for many users...


Further to this, imagine the WAL file (or any other of the temp files) gets synced without the DB file... or vice-versa, or you get 
another user's newer WAL file overwriting your own - a very probable situation.


[Note: This is not drop-box's fault, it has to do what it needs to do to ensure 
sharing tactics that suits most syncing needs]





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

Re: [sqlite] on insert new row, increment date with a month

[RSmith]

On 2014/02/11 20:07, Gert Van Assche wrote:

All,

Does anyone know if it is possible for a date field to be automatically
incremented with a month when a new record is created?

If the last row contains "2013-01-01" in the DateField then the DateField
of the new row should automaticllay be "2013-02-01".


Updating a value is easy, a trigger can do that. Increasing a date by a month 
is very easy.
Finding the last added date, not so easy...
It is of course possible with a query, when making some assumptions, but I would suggest keeping it somewhere else. However, in the 
interest of expedience, I will assume that the date that should be incremented is the very last added date, I will also assume the 
date field is a Key (or Index if you will) and called nDate, and I will assume the the primary Index is called ID and it is 
autoincremented and lastly assume the table is called Table1 - if these assumptions are all true, you could possibly do it like this:


CREATE TRIGGER IF NOT EXISTS Trig_Table1_setLastDate
  AFTER INSERT ON Table1 FOR EACH ROW
BEGIN
  UPDATE Table1 SET nDate = (SELECT date(C.nDate,'+1 month') FROM `Table1` AS C 
 ORDER BY C.ID DESC  LIMIT 1) WHERE ID = NEW.ID;
END;

Note that if the ID is not auto-incremented, it might end up being sorted wrongly and the highest ID might not be the very last 
added ID. You can sort by date too if the last added date is definitely the highest date, etc. The DESC makes sure the highest is 
sorted first, and the limit makes sure we pick only the 1st highest.  The C alias makes sure the SQL engine does not get confused 
with the sub-query table.  The Trigger makes sure that whatever new line is added that the new ID's record get the update.


Pretty straight forward _IF_ all the assumptions hold true - else you may need 
some more tricks to do it.


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

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

[RSmith]

On 2014/02/12 10:09, Stephen Chrzanowski wrote:

The other thing I'd look into is that because of the varying speeds of SD,
the volume of information you could be writing, you may run into an issue
where you call the backup API but due to write speeds, something else
writes to the live in-memory database, and then your data becomes expunged
when the DELETE command is executed while the backup is happening.


Actually, the Backup-API is clever like that, it will automatically void and re-start the backup when data changes happen. Of course 
this might be equally detrimental if you update more frequently than the backup takes to complete, in which case the system would be 
in permanent backup and any gains voided.


Maybe stop the updater till the backup is finished... it should however not take 10s, so it _should_ be safe, but I would put it in 
the precautionary wait state just to be safe.


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

Re: [sqlite] HTML Tokenizer

[RSmith]

On 2014/02/13 22:35, Petite Abeille wrote:
While we are at it, www.sqlite.org exhibits many validation errors: 
http://validator.w3.org/check?uri=http%3A%2F%2Fwww.sqlite.org%2F&charset=%28detect+automatically%29&doctype=Inline&group=0&user-agent=W3C_Validator%2F1.3+http%3A%2F%2Fvalidator.w3.org%2Fservices#result


Yea I actually had some web zealot once point out to me that one of my sites produced 25 errors in the W3C validator. I 
laughed and pointed out that that www.google.com scored 30 errors (I see they are down to 23 errors and 4 warnings now) and that his 
first 3 sites I checked all had over a 100 - (which is quite normal actually - Amazon.com gets over 500 errors, but I did not 
mention this).


Point is - I wouldn't lose any sleep over the 37 SQLite errors, neither should 
any SQLite web admin :)

For ref: Google:
http://validator.w3.org/check?uri=http%3A%2F%2Fwww.google.com%2F&charset=%28detect+automatically%29&doctype=Inline&group=0&user-agent=W3C_Validator%2F1.3+http%3A%2F%2Fvalidator.w3.org%2Fservices

Amazon:
http://validator.w3.org/check?uri=http%3A%2F%2Fwww.amazon.com%2F&charset=%28detect+automatically%29&doctype=Inline&group=0&user-agent=W3C_Validator%2F1.3+http%3A%2F%2Fvalidator.w3.org%2Fservices




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

Re: [sqlite] Once again about random values appearance

[RSmith]

On 2014/02/17 09:59, Max Vlasov wrote:


Ok, I hope I found the topic, the title was
   "racing with date('now') (was: Select with dates):
one of the links to the archive
   https://www.mail-archive.com/sqlite-users@sqlite.org/msg79456.html

CMIIW, but as I see it, the final modification was commented by Richard

  > As a compromise, the current SQLite trunk causes 'now' to be
exactly  the
  > same for all date and time functions within a single sqlite3_step()
call.

But this is just for now and date-related functions. I wanted to be sure so
created a user function NanoSec() that returns  nanoseconds as it is
calculated with QueryPerformanceCounter and QueryPerformanceFrequency on
Windows and clock_gettime(CLOCK_REALTIME... on Linux. Seems like it's not
always real nanoseconds but value that is changed very frequently to be
different for close VDBE instructions of sqlite engine.

So

   Select nanosec() - nanosec() from ...

returns non-zero values for most of the times, so there's no guarantee the
user functions or any other functions will be called once for the step.//... 
etc.


Did you mark your nanosec function as SQLITE_DETERMINISTIC 
?
http://www.sqlite.org/c3ref/create_function.html

Which, if not, it can and will very much return non-zero values.

And if you did, either your function or your version of SQLite is broken.


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

Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC

[RSmith]

On 2014/02/17 18:47, Stephan Beal wrote:

Hi, all,

Regarding SQLITE_DETERMINISTIC:

http://www.sqlite.org/c3ref/create_function.html

does specifying that flag guaranty that sqlite3 will only call my
"deterministic" function one time during any given SQL statement, or must
my function actually guaranty that deterministic behaviour itself?


The flag is telling SQLite that your function will behave determinsitcally, i.e. it won't change the output for the same inputs 
within a single query. SQLite then uses this information to "maybe" cache the output and re-use it, but there is no guarantee the 
optimisation is possible within every query situation, so it is very much possible SQLite can call your function again within the 
same query, you have to make your function behave deterministically if you tell SQLite that it is so - else query results can be 
undefined.


The OP seems to have "tested" SQLite's determinism with adding a very indeterministic function, so what I was trying to point out is 
either his function isn't behaving deterministically and/or he did not specify the flag to let SQLite know - but to your question 
specifically, no the flag does not force determinism (AFAICT), it only allows the optimisation, or more specifically, NOT specifying 
the flag forces SQLIte to call the function every time and not expect determinsitic results (even if your return values are constant).



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

Re: [sqlite] Text column: never used vs. set to empty string

[RSmith]

On 2014/02/17 19:01, Stephan Beal wrote:

On Mon, Feb 17, 2014 at 5:57 PM, Tim Streater  wrote:


complete in a browser window, which data is then gathered up and sent
using ajax to be processed by a PHP script, which writes it to an sqlite
db. The user complains that some of this data doesn't make it, so I want to
pin down where in the chain this might be failing. Hence my Q.


FWIW, i have seen a similar problem in a legacy app which uses latin1
encoding in the DB. Latin1 doesn't always survive round-trip through PHP's
JSON APIs. My case was similar to yours, and we eventually determined that
the fields which got "lost" (set to null or empty values) were those which
came out of the latin1-encoded MySQL db containing invalid UTF8 characters
- the whole values were getting dropped upon transforming to JSON.

TL;DR: double-check all encodings.



Yeah, I too have had real problems with this - It isn't limited to Latin1 either - but in my case I found one invalid character that 
doesn't conform to the specified encoding would drop the entire string in any encoding.  This was hard to trace because 99 
conversions would succeed with all kinds of weird and wonderful encoded characters, and then one suddenly returns an empty string.
I eventually traced this down to the OS's C API for encoding conversions which seems to simply drop the entire value and not even 
cause an error - just returns empty string. Peculiar behaviour if you ask me (though that is probably meant to prompt an error from 
the high-level code's design, but nobody told me...).  This seems the case for both Windows and OSX, I can't speak for Linux, 
Android, etc. maybe someone else knows?


Anyway, adding a conversion check is the key, but the point is moot if you rely on PHP or such to do the conversions for you. 
Scripts don't have GUIs... they cant really do much about it. Maybe an error log somewhere contains some info?


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

Re: [sqlite] Text column: never used vs. set to empty string

[RSmith]

Forgot to add: My headache was essentially UTF-8 encoding, but the same would happen with others, though invalid chars do not really 
exist in UTF7 or ANSI, but in the higher level encodings they are plentiful.



On 2014/02/17 19:35, RSmith wrote:


Yeah, I too have had real problems with this - It isn't limited to Latin1 either - but in my case I found one invalid character 
that doesn't conform to the specified encoding would drop the entire string in any encoding.  This was hard to trace because 99 
conversions would succeed with all kinds of weird and wonderful encoded characters, and then one suddenly returns an empty string.
I eventually traced this down to the OS's C API for encoding conversions which seems to simply drop the entire value and not even 
cause an error - just returns empty string. Peculiar behaviour if you ask me (though that is probably meant to prompt an error 
from the high-level code's design, but nobody told me...).  This seems the case for both Windows and OSX, I can't speak for Linux, 
Android, etc. maybe someone else knows?


Anyway, adding a conversion check is the key, but the point is moot if you rely on PHP or such to do the conversions for you. 
Scripts don't have GUIs... they cant really do much about it. Maybe an error log somewhere contains some info?


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

Re: [sqlite] calculation of a fraction stored in a text column

[RSmith]

Ensure you store the string representation of the reals (floats w/e) of precise 
numerical format and length, such that:
0.3, 12 and 1.456 all look alike and sorts correct ex:

"000.30"   and
"001.456000"   and
"012.00"   etc.

or whatever similar format you may choose as Simon (I think) suggested so that you will have those values correctly 
sorted/grouped/distinct-ed in any string-based SQL function. A further enhancement I like to do in such cases is add some alpha char 
in front, like:

"F01.456000"   and
"F12.00"   etc.
so that any output I do create, which invariably ends up being copied to excel or calc or such, does not get confused with actual 
numerals and stripped of leading zeroes etc. It's much easier to apply a formula to make that into numerals should the need arise, 
than to avoid it being done automatically sans the leading alpha char.




On 2014/02/20 20:50, Patrick Proniewski wrote:

Donald,

On 20 févr. 2014, at 15:16, Donald Griggs wrote:


Am I right that fractional exposures will always have one as the numerator?   I.e., you might 
record an exposure as "1.5" seconds, but never as "3/2" seconds?   If so, then 
that might simplify things.

Yes, no 3/2, only 1/x and regular REALs.



The example below creates a column named "canon" to hold the canonical exposure 
string value for sorting and grouping.
It assumes any non-decimal fractions will begin with the string "1/"

Thank you for this example, the idea of storing the computed number into the database is 
very good and made me rethink the initial database feeding. I'm using exiftool to script 
EXIF reading from my files. exiftool has a very nice option that allows the reading of 
raw data. Exposure Time, displayed in "raw" is the REAL equivalent to my 
strings: 1/200 is read as 0.005. Very handy.
I'm going to redesign my database in order to include raw data aside 
human-readable data when I need it.

thanks,
Patrick
___
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] Proposed enhancement to the sqlite3.exe command-line shell

[RSmith]

On 2014/02/21 11:54, _ph_ wrote:

Suggestion: Warning banner, and a .saveas command that copies the db to a
file.

(I haven't read the entire thread, sorry if this already came up.)


There is usually no call to read an entire thread, unless you decide to 
actually post an opinion, in which case it becomes paramount.



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

Re: [sqlite] Problem with .mode line

[RSmith]

On 2014/02/20 16:58, pelek wrote:

indeed ! I tried to open same file with Programers Notepad and file looked
exacly like I need. But when I was opening file in standard windows notepad
then I got whole CREATE TABLE code in one line!
It is problem for me, because I am trying to open same file with c# code.
Unfortunetly c# is opening the code: CREATE TABLE in one line  - which is
wrong !! :( :( :(



Open it with C# code how?

Load a string or byte-stream from the file and then put it into some component? Probably the component is expecting full compatible 
line truncation which is platform dependent, and you can simply fix it by the following operation on your string (after loaded from 
file, but before using it in any component:


Replace all Linefeed  characters ( h0A ) with full Carriage-Return+Linefeed  characters ( h0D+h0A ) or the inter-OS string 
"\n".


On most Unix Systems a single LF character is taken as a new-line specifier, and the CR is ignored silently, which has the advantage 
that no matter if you use LF or CR + LF, you will see the same lines as a result. using just CR though has no real effect but can be 
used in post-processing to distinguish data items, etc.


Android works a bit different and Windows takes only a full CR+LF set as a formal line-break. The advantage here is that a normal 
line can contain multiple lines of information without being split down, whether you use only CR or only LF for it doesn't matter, 
the line is only broken if a full CR+LF is found. This is why your lines look like 1 line when it contains multiple lines. A bonus 
if you look to store multi-line data in single lines, but rather silly if you hope to display lines as lines and the originator is 
of Unix descent. Most OSes encodes the " \n " character in a string line to whatever the specific OS uses as a valid line-break. 
Still it is useless when the file is created on this OS only to be opened on another target OS.


I think in the case of the sqlite3.exe tool the thought is to use it the way it has always been from the Unix origins and not try to 
re-encode for other OS methods since existing apps may break if that suddenly changes - but it is very easy (as explained above) to 
simply fix the output for inter-OS compatibility. Note that this only applies to the tool's output, and not SQLite itself, which is 
ambivalent to line-breaks.



Take Care: using "\n" might change the actual string or length thereof if your 
app is multi-platform or web-based.



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

Re: [sqlite] partially excluding records

[RSmith]

On 2014/02/21 20:23, David Bicking wrote:

I have a table like

SELECT * FROM T1;
Key  Status
1  O
1  O
2  O
2  C
3  C
3  C
4  O
4  P


Now, I need to consolidate that data.

SELECT Key, COUNT(STATUS) Cnt
, MIN(STATUS) || CASE WHEN COUNT(STATUS)>1 THEN '+' ELSE '' END Statuses
FROM T1
WHERE ...
GROUP BY KEY;

Key  Cnt  Statuses
1  2 O
2  1 C
4  2 O+

The complication is that if a given key has any non-C value, the C values are 
to be excluded. If there are only C values, they are to be included.
How can I state the WHERE clause to do that?


This last statement contradicts your example completely. You say:
"if a given key has any non-C value, the C values are to be excluded"

But looking at the table the Key-value 2 has one non-C value, yet it is 
included and showing the C.

You then say:
"If there are only C values, they are to be included"

But Key "3" clearly contains only C values, yet they are explicitly excluded 
from the result list

If you could kindly fix either the statement or the example so we know which is accurate, then will gladly try to solve the WHERE 
riddle for you!




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

Re: [sqlite] Delete From Not Working Properly

[RSmith]

On 2014/02/22 00:32, Geo Wil wrote:

1. Windows 7 Ultimate 64-bit

2. Here is the path I am using:
void Database::openSave(bool* bErrors)
{
 if (sqlite3_open("*scSave.sqlite*",&dBase) != SQLITE_OK)
 {
 *bErrors = true;
 createBInfo();
 d.createBReport("SQL Code 1",sqlite3_errmsg(dBase),bLocale +
to_string(__LINE__),bTDate,"./SC_Log.txt");
 }

 else
 {
 *bErrors = false;
 }
}

3. I checked that earlier today as well as the permission on the database
itself.  Everything checks out.

4. Here you inspired me to do some extra digging.  I added some couts to
the player data transaction block in the event that error is not null and I
got an error of "Database is Locked".  This was surprising because I have
no programs open that have my save database open and the only other call to
the database before the transaction block starts is the openSave function
listed above.



That path doesn't seem like a path, but just a call to open a filename, which should be in the same folder as the exe. Now, 
depending on where the exe is in Win7, that could be a problem. If your exe is in any of the protected or system folders (such as 
Program_files), Windows won't let you edit files in those places, it might simply shift the file to the virtualized folder and open 
it there, which usually works ok and very much transparent to your app - unless you are trying to force the full path in an open 
statement, such as SQLite should be doing. This may explain why the permission seem ok but the physical file is locked when you try 
to open it.


The way to fix this is to use the correct program data path (typically "c:\users\myUser\AppData\Roaming\MyProgramName\") or the 
user's documents folder (should you want this file to be handled by the user at some point), in stead of the exe path  - and/or to 
let Windows know that you know what you are doing and expect your datafiles to be changed by specifying a manifest to your exe with 
appropriate settings - just MSDN "Manifest file" for examples.


btw: Getting the path to the correct program data path on any PC requires simply a call to the Windows Shell API with the correct 
parameter, again just MSDN it.


Good luck!

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

Re: [sqlite] Delete From Not Working Properly

[RSmith]

On 2014/02/22 01:37, Geo Wil wrote:


As for the fail path issue, it is not an issue or at least it has never been for me.  The way I understand it is that if you just 
put the file name into a function requesting a path in Windows it will look in the folder the process is running from for that 
file. //


Yes of course, but the problem is the typical folder a program will be installed to (which may not be the case in the dev machine) 
is c:\Program Files\ and that path is special and protected, and there is no sub-folder of that path that windows will allow you to 
keep a file and make changes to it unless your manifest specifically requires it (and even then the user will be bothered every time 
for access grants). You need to save the data very far away from the exe, such as the program-data folder I described earlier. You 
may have switched off the UAC on the dev PC, but it will be alive and well on an intended user's PC and it will not allow you (or 
anyone else - which is the beauty of it) to overwrite any file inside your program's exe folder or sub-folder thereof.



Although you are right, I should be a bit more explicit with the path, maybe use ./Databases/[filename] so that it will explicitly 
look in the same folder and not have to use a default behavior that can sometimes be fallible.


Being explicit about the name is of no consequence when the resulting location 
of the file is still inside a protected folder.


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

Re: [sqlite] How to Troubleshoot Disk I/O Error

[RSmith]

On 2014/02/26 16:27, Richard Hipp wrote:

LOG: os_win.c:33842: (33)
winTruncate2(D:\blp\wintrv\smartclient\applications\appinfo.db-shm) -
ãƒ—ãƒ­ã‚»ã‚¹ã ¯ãƒ•ã‚¡ã‚¤ãƒ«ã «ã‚¢ã‚¯ã‚»ã‚¹ã §ã  ã ¾ã ›ã‚“ã€‚åˆ¥ã
®ãƒ—ロセス㠌ファイル㠮一部をロムE‚¯ã —ã,
extended-result-code: 1546
TRUNCATE file=34, rc=SQLITE_IOERR_TRUNCATE
LOG: os_win.c:35002: (33)
winShmMap2(D:\blp\wintrv\smartclient\applications\appinfo.db) -
ãƒ—ãƒ­ã‚»ã‚¹ã ¯ãƒ•ã‚¡ã‚¤ãƒ«ã «ã‚¢ã‚¯ã‚»ã‚¹ã §ã  ã ¾ã ›ã‚“ã€‚åˆ¥ã
®ãƒ—ロセス㠌ファイル㠮一部をロムE‚¯ã —ã ¦ã  E,
extended-result-code: 4874


What happened here is that SQLite tried to invoke SetEndOfFile() on a
shared-memory file in order to change its size from 0 bytes to 32768
bytes.  But it got back an SQLITE_LOCK_VIOLATION error (windows error code
33).

I'm not sure why this is happening. The file had just been created - could
it be that some anti-virus software had the file locked down in order to
scan it somehow?

The unreadable text is the error message that Windows provided via
FormatMessageW().  Clearly it is not in English.  Do you know what the
locale setting is for the machine that is generating this error?


Also possible: File is in a folder that is synced using Windows Briefcase (on older computers typically) or a Dropbox/OneDrive 
folder or such.
Or in a UAC protected place (the create could work fine but the change request may be killed, although the create should set the 
owner correctly which shouldn't really have change request issues then, unless some setting doesn't see this happen correctly).


But from all the above, I think Antivirus or other real-time computer 
protection or some such is most likely.

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

Re: [sqlite] New

[RSmith]

On 2014/02/28 17:13, Ashleigh wrote:

Nothing will load in SQLite just the command box


Not sure if this is a prophecy, a problem, a proposition or a premonition, but I am pretty confident that it isn't an SQLite process 
problem.


Might you give us some more information please?

What command box opens, when you do what? How did you attempt to "load" something, and which something did you try to load on which 
platform using which SQLite tool/version/library, and when the something didn't load, is there an error, an indication or just 
emptiness all around?


Our psychic abilities have not matured, please be explicit.


Live, love & laugh.

Maybe one should add: "pay attention" and "worship curiosity" to those... :)



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

Re: [sqlite] Why SQLITE_FLOAT instead of SQLITE_REAL?

[RSmith]

On 2014/02/28 23:36, L. Wood wrote:

SQLite has the REAL data type:

https://www.sqlite.org/datatype3.html

Then why do we have SQLITE_FLOAT instead of SQLITE_REAL? All the other data 
types (INTEGER, BLOB, TEXT, NULL) match with the SQLITE_ constants.


Quoting Shakespeare's Juliet:
"What's in a name? that which we call a rose by any other name would smell as 
sweet..."

Of course in matters of love one can nod to that, but it can't be more wrong in 
SQL or any code terms!
This may be a quirk, but in the defense, those type names are interchangeable 
(or I should say Aliased) in most modern languages.


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

Re: [sqlite] Why SQLITE_FLOAT instead of SQLITE_REAL?

[RSmith]

On 2014/03/01 10:32, Darren Duncan wrote:


If you're going by semantics though, the meanings are quite different.

A real number represents a point on a line and can be either a rational or irrational number.  (And a complex number is a point on 
a plane.)  An important bit is that a real is a more abstract concept and doesn't imply a single right representation.


In contrast, a float is much more specific, defining also a representation, and as such a float can only be a rational number 
(x*y^z where all 3 are integers, and y is typically 2) and not an irrational.  (Or I suppose if you allow {x,y,z} to be 
non-integers then a float is even more about a representation.)


Speaking in terms of programming language design, "real" is best suited for an abstract type name, that is one that defines an 
interface for using a set of types, same as "numeric".  Whereas, "float" is best suited for the name of a concrete type, like with 
"integer" and "ratio".  (Well strictly speaking all of these could be abstract types, but the latter set are more specific in 
meaning, and in particular "ratio" and "float" imply a representation while the others don't.




I take your point, and thanks for the dissertation.

I would like to point out though that the "implied" meaning of words that were pressed into service in programming languages have 
rarely survived the transition.  A "String" really implies lengthy extrusions of fibrous bundles (but smaller than a "rope") and 
"Long" has no single numerical implication outside of a programming language.  Other less interesting examples abound but suffice to 
say that while true meanings of words can and should influence their use within code, an altered understanding that fits a binary 
universe are often (if not always) the defacto implication. - much as the mathematicians among us abhor the idea.


Hence the copious amount of aliases which attempt to align the meanings between historically different names for essentially the 
same function or functionality in programming terms - all of which can be argued for in implication or semantic terms.


i.e. - You are correct - but I don't see it ever being applicable, or indeed 
apply-able, as a standard.

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

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[RSmith]

On 2014/03/03 23:11, romtek wrote:

Simon, does a real disk have to be a rotating hard disk? Is there  problem
with SSDs as far as SQLite is concerned?


No, what Simon is trying to point out is simply that the write performance experienced by L. Wood might be because journal writes 
might be synced to hard disk (in rotating disk cases anyway) and as such cause delays all throughout the transaction even if they 
are not holding up the final commit.  Not because this is in error or wrong in any way, simply as trying to explain why he sees the 
performance spread he sees.


Other reasons might be excessively large binds causing the cache to spill or simply have memory writes taking so long that it seems 
to cause inter-transaction delays, although if that were the case one would expect the final commit to take a lot longer even.


My money is still on the specific pragmas used which might be forcing syncs or non-ACID operation. We would need to have the DB 
schema and the typical query to really test why it works the way it works in his case.



One note on SSD's, they pose no physical problem to SQLite, and in fact works magically fast, but having a DB which read/write a LOT 
of data on them is not really great since  the repeated read-write cycle of specific data areas tire down the typical NAND flash 
that makes up the SSD, even though modern SSDs may use MLC NAND or have firmware that tries to exercise every bit in memory equally 
so as to spread the write operations to avoid one piece of disk-memory dying quickly. Eventually though, when all bits of memory 
experienced upwards of 500K write operations (which is quite a while), it will fail... but you will have lots of warning. A 
read-only DB on an SSD drive cannot be beaten... even a normal DB where writing does not happen often I would suggest a SSD... but 
anything with a very active read/write cycle is best avoided - or at a minimum backed up by a good old rotating magnetic platter drive.


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

Re: [sqlite] Virtual table API performance

[RSmith]

On 2014/03/05 10:41, Dominique Devienne wrote:

On Tue, Mar 4, 2014 at 9:41 PM, Elefterios Stamatogiannakis

One thing that IMHO long term might improve the situation would be if
SQLite's own "native" tables would use the same Virtual Table API,//...

...//Of course, the above is a "naive" abstract reflection which ignores
the realities of VDBE, so it may sound rubbish to actual SQLite
developers. Apologies for that. --DD


I don't think it is rubbish at all, but maybe idealistic.  The biggest problem I can see from making API's pov is that you can at 
any time alter, update, change the way SQLIte (or any other API) works with the base check that the input values produce the same 
(or maybe more-correct) results.  Once you let the VT use the same API, any change is a potential change to how other people's 
programmed interfaces need to talk to - or get data from - the SQLite engine. This cannot simply change on a whim, so the levels of 
separation remain needed.


That said, I'm all for making a more efficient VT API, but it would probably need to be "new" functionality since I cannot see how 
the existing interface could implement any of the mentioned enhancements without breaking existing behaviour. The OP's xNextRow 
suggestion seems a good idea, but opens up a whole can of what-ifs which other posters have alluded to, but something to that effect 
might be worthwhile if the efficiency bonus is significant.


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

Re: [sqlite] Weird out of memory problem a prepare

[RSmith]

On 2014/03/04 22:05, Eduardo Morras wrote:


The tables have 4 rows each one, that's why I got suprised with the Out of 
Memory error. The biggest row has 12KB and with the join I do, shouldn't use 
more than 200KB.

Changing the ',' with the join you propose, gives Out of Memory too. It happens 
on prepare phase, before binding the ? with my data. The query didn't reach the 
step call.

Trying simple "SELECT r.name FROM resource AS r WHERE r.name = ?" gets "Out of 
memory" too calling preparev2.

Surely something is rotten on my development platform...


Yes, something is very rotten. I would start by looking for possible memory coruptors... an array being adjusted with an index out 
of the declared range, calling methods on an object via a reference that wasn't nulled after the actual object (via another ref) was 
disposed. The problem with these types of errors is the thing that breaks usually have nothing to do with the thing that causes the 
memory to go bad, so it is very hard to trace since we normally try to fix the thing that breaks and stares too long at the code of 
the thing that broke, with which there is rarely a problem.


Do you use any memory profiling tools and checkers? It might give you some 
hints.

If that query fails in an SQLite tool too, then maybe there is a problem, or if you use a custom altered version of the SQLite code. 
Barring that, you need to hunt down the corrupting code - Good luck!




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

Re: [sqlite] How to write a query

[RSmith]

On 2014/03/05 12:04, Igor Korot wrote:

Hi, ALL,
Let's say I have a table with following data:

field1field2field3   field4
12  3 4
5   6   7 8

How do I write a query which will produce the output as:

1 2
5 6
3 4
7 8

Is it possible to write a single query which will produce the output like
this?
And not just in SQLite


SELECT field1, field2 FROM tbl
UNION ALL
SELECT field3, field4 FROM tbl

I can't imagine a use for this... but hey



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

Re: [sqlite] How to write a query

[RSmith]

On 2014/03/05 12:24, Igor Korot wrote:
With UNION I will have 2 DB hits, correct? Meaning I execute the part on the left side of the UNION and then execute the right 
side of the UNION and then add the results together. Do I understand correctly? Thank you. 


Yes. Obviously you need to have the same number of field results for the part before the UNION than the part after the UNION to be 
able to be one query result list at the end without nulls and such.


Also, when you don't want duplicates, just use "UNION", if you want all values to be listed, even if they are duplicates, use "UNION 
ALL"



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

Re: [sqlite] select where field in ($tcl_list) ?

[RSmith]

On 2014/03/05 17:05, Chris wrote:


Ok, fair enough. I thought that in the same way that sqlite looks for
binary vs. string representations of referenced vars and has alternative
ways of specifying variable to bind to ('@', ':'), it might also spot a
list object and internally expand it to "elem_0,elem_1,elem_2". Would
that be a useful feature, or does it introduce opportunities to draw the
wrong conclusion?


Most high-level languages have some sort of list expansion in list objects 
which obviates the need.

You should for instance be bale to say something like this in most languages:

mylist = New(List);
mylist.add("Item1");
mylist.add("Item2");
...
mylist.add("Item6");
mylist.add("Item7");
etc..

mylist.separator=",";   // Comma
mylist.QuoteChar="'";  // Single Quote


So the entire Query bit can become something simple like:

qry = "SELECT a,b FROM t WHERE id IN ("+mylist.asString+");"

or something to that effect
Adding any SQLite function to try and break down your sent variable into lists would probably pay a much higher price in processing 
and added code - moreso than it really being difficult to do or in violation of some method or way of doing.


If those lists get really really long or convoluted, it is best to break them into tables of their own via some query and then use 
the base query from that table as the list specifier.


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

Re: [sqlite] Instead Of Insert Trigger Error

[RSmith]

On 2014/03/06 18:41, Tilsley, Jerry M. wrote:

I would like to create the following INSTEAD OF INSERT trigger:
create trigger insteadInsertPanelTracker instead of insert on
panel_tracker begin set @ov_id = select ov_id from ov_systems where
mt_mnemonic=NEW.ov_id; insert into panel_tracker values (@ov_id,
NEW.mt_acct, NEW.orm_id, NEW.panel_code); end ;
At this point it is probably just easier to do an initial call to the database to lookup the value I want, then send in the 
insert. I thought a trigger would be a nice way to keep my other code cleaner, but I guess not. 


If I may offer some advice - In stead of telling us what you have done and asking what is wrong with it, why don't you simply state 
exactly what you want to achieve (along with all schemata), and ask what the best way to do so is? You will be amazed at the 
solutions some of these geniuses can come up with.



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

Re: [sqlite] RPAD/LPAD

[RSmith]

On 2014/03/07 01:59, Gabor Grothendieck wrote:







A small enhancement request:

It would be great if the RPAD and LPAD functions could be implemented in
sqlite.


The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
See http://www.sqlite.org/lang_corefunc.html#printf for details.

Thanks, but you snipped the relevant part of my post:
"I know I can easily achieve the equivalent ... but if the functions were available 
natively it would avoid the need to hack third party SQL scripts."


I have also found that it was tedious to retarget MySQL scripts to
SQLite because many of the function calls are different.  Its not just
rpad and lpad but other functions too.


Speaking as someone who retargets (nice word btw.) SQL scripts often, yes I agree, it's a bit of a chore to retarget SQL scripts to 
SQLite sometimes, but not really moreso than retargeting a script from MSSQL to PostGres or Oracle, or PostGres to MySQL (which btw. 
doesn't support WITH RECURSIVE at all) etc.[1]   To single out the SQLite differences as anything more than standard incompatibility 
between SQL engines would be... exaggerative. (I just made up that word!) - And still with the bit of incompatibility we need to 
deal with for a world of clever, trusted and fast querying in an engine that is a fraction the size of anything else, so much so 
that you can run it on a smart calculator  hardly a deficit.


Add to this the fact that you can - through SQL's ability to add user-defined functions (an almost unique ability among SQL engines) 
- add your own RPAD and LPAD functions that work exactly how you envision... you have the power to solve your own problem.


[1]: Yes I know MySQL has other ways to deal with recursion, but the point is that revising the scripts will need a LOT of 
adjustment to achieve the same, which is the point of this note.



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

[sqlite] 64bit DLL

[RSmith]

Have any of you kind folks a recent 3.8.3 (or newer) 64-bit DLL for SQLite3 
perhaps?



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

Re: [sqlite] Fwd: Exception when querying a range index

[RSmith]

On 2014/03/11 11:58, St. B. wrote:

I still have a question. Since I have many threads (between 100 and 200)
that do reading on the table that has the R Tree, and 1 thread that will
write to another table once every five minutes, is it normal that I get
database is locked error on a regular basis? I was under the impression
that read only is lock free in when doing multiple access, and that locks
should only occur during the writes.


So to be sure - You have no threads at all writing to this DB? Just 200 that read often and 1 that reads every 5 minutes (and then 
writes to a completely different DB)?


You cannot get locked errors then. Unless maybe some of these reads happen inside a transaction that is exclusive. Or a pragma that 
locks or does some function that requires a lock is called.




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

Re: [sqlite] basic "Window function"

[RSmith]

On 2014/03/13 20:02, Petite Abeille wrote:

On Mar 13, 2014, at 4:17 PM, big stone  wrote:


Is there anyone else, (besides little bee), that would "like" this request?

"Oh! Oh pick me! Pick me! Me! Me! M!” — Donkey, Shrek


Hehe, I live in a Country with 11 official languages. Needless to say the actual dialects used are regularly somewhere in between 
those official lines... the sort of thing would make a purist shudder.  Either way, when lots of people from different linguistic 
origins work together, they sometimes speak a common language that invariably ends up being a bit of a mishmash and often misses 
complete words for which a descriptive might be pressed into service and, on occasion, becomes a mainstream constituent of the 
vernacular.


So it is that in one of these work-languages most widely spoken, my most favourite term is for a a Bee (a rather small English word) 
which ended up being referred to as "Picannini flymasjien yena kona lo jam", which in direct translation amounts to: "Very small 
fly-machine he's got jam".


Some beauty lies in the fact that the whole "machine" bit is completely unneeded "small fly" would have sufficed, but the lovely 
rhyme and metrum surfaces everywhere in the culture.


:)


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

[sqlite] Execute Error Notification

[RSmith]

I use quite a few script-type sql lists to achieve some functions in some systems, by which I basically mean I make up a list of SQL 
statements and then just send them to the very convenient sqlite3_exec() function when I'm pretty sure I cannot do it any faster by 
individual steps or speed is no concern...


A typical script might look like this:
-- --
-- Alter Table masterOrderData after Importing

BEGIN TRANSACTION;
DROP TABLE IF EXISTS `TempAlteringTable`;
ALTER TABLE masterOrderData RENAME TO `TempAlteringTable`;

CREATE TABLE `masterOrderData` (
  `PurchaseOrder` TEXT PRIMARY KEY COLLATE NOCASE,
  `MStockCode` TEXT COLLATE NOCASE,
  `MDescription` TEXT,
... other irrelevant columns omitted 
) WITHOUT ROWID;

INSERT OR ROLLBACK INTO `masterOrderData` (PurchaseOrder, MStockCode, 
MDescription, etc.)
  SELECT PurchaseOrder, MStockCode, MDescription, etc. same as above...)
FROM `TempAlteringTable`;

DROP TABLE `TempAlteringTable`;
COMMIT;
-- --

Basically that master table gets created sometime before this script from an import via another DB and with automatic columns as 
just Text columns, so I run the script to change the columns to have better definitions and Primary Key etc. etc..  It all works 
perfectly.


Sometimes though, the imported table might have duplicate PurchaseOrder No's or such, some or other reason the Insert fails and 
rolls back, which it does perfectly.


However, since I just push the whole script to the sqlite3_exec() function, it replies with SQLITE_OK always... even if a rollback 
occured, which is probably correct since it did what I asked it to do, and it did it correctly... My question is though, is there a 
way to learn somehow whether a rollback or constraint violation happened during the whole execute process which caused a rollback?


Kind of like saying:  "Yes I know the script executed fully, but was there a mess-up 
at some point?"

I'm being lazy here and do not want to check the tables after each script, so 
hoping there is a way...

Thank you kindly,
Ryan


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

Re: [sqlite] Error "Database or disk if full" shown when indexing for 2 billion records.

[RSmith]

That's insane... well done :)

To create the Index you will need at least as much disk space as already used... so you will need another 87GB (approx), not just 
another 50GB.


And it will take no longer to create the table + Index from the start than it will take to create first the table, then the index - 
plus you will need less overall disk-space if you do it from the start, but much more than 87GB... try making the table without the 
Rowid.. that should save you a lot:


CREATE TABLE t (str_md5 VARCHAR(32) PRIMARY KEY COLLATE NOCASE) WITHOUT ROWID;

That should work the best for md5 hashes and might even get to under your GB 
total space (including the key).


What I would do is create that table, then add only 1 million entries indexed and all... see the total size, multiply it with the 
amount of entries you need in total, as a rough guide to how much you can put in the space.


HTH!
Ryan



On 2014/03/17 15:32, Yi Wang wrote:

I inserted 2 billion records with only 1 column with name of "str_md5", the
value is the MD5 encrypted value of "mm"+rowid(such like MD5(mm121212...).

I didn't not create primary key for the only column b/c i am not sure it
would slow the speed of insert command. The whole insert of 2 billion
records took me over 18hours.

But when I hope to index the column with the sql command of

”create index tableMD5_idx on tableMD5(on str_md5);"

The index command ran for around 1 hour and then the error "Database or
disk if full" shown up.

FYI: The sqlite file only contains that only 1 table with 1 column. The
size of the DB file took around 87G disk space.

And the disp space is still 50G more free space to reach full. So I am not
sure whether it's the space problom b/c according to my previous
experience, the index command would increase around 1/3 size of the currnet
DB file (I took a test DB with one hundres million records which took abour
7G space, and after the index command the final size reach to 9G around.)

So any ideas? Thanks in advance.

Yi.



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

Re: [sqlite] Error "Database or disk if full" shown when indexing for 2 billion records.

[RSmith]

Oh, by the way in 2b entries, you may hit duplicate MD5 hashes... which 
will fail the insert when they are primary-keyed

Also, the index does not add 30% as a norm.. it very much depends how wide the data is the index is on, so be careful to take that 
as a rule...


Also, I know there is not much difference between using TEXT or VARCHAR(32) in SQLite, but it matters elsewhere and I believe even 
SQLite will use that varchar value in the query optimiser... [citation needed]



On 2014/03/17 15:39, RSmith wrote:

That's insane... well done :)

To create the Index you will need at least as much disk space as already used... so you will need another 87GB (approx), not just 
another 50GB.


And it will take no longer to create the table + Index from the start than it will take to create first the table, then the index 
- plus you will need less overall disk-space if you do it from the start, but much more than 87GB... try making the table without 
the Rowid.. that should save you a lot:


CREATE TABLE t (str_md5 VARCHAR(32) PRIMARY KEY COLLATE NOCASE) WITHOUT ROWID;

That should work the best for md5 hashes and might even get to under your GB 
total space (including the key).


What I would do is create that table, then add only 1 million entries indexed and all... see the total size, multiply it with the 
amount of entries you need in total, as a rough guide to how much you can put in the space.


HTH!
Ryan



On 2014/03/17 15:32, Yi Wang wrote:

I inserted 2 billion records with only 1 column with name of "str_md5", the
value is the MD5 encrypted value of "mm"+rowid(such like MD5(mm121212...).

I didn't not create primary key for the only column b/c i am not sure it
would slow the speed of insert command. The whole insert of 2 billion
records took me over 18hours.

But when I hope to index the column with the sql command of

”create index tableMD5_idx on tableMD5(on str_md5);"

The index command ran for around 1 hour and then the error "Database or
disk if full" shown up.

FYI: The sqlite file only contains that only 1 table with 1 column. The
size of the DB file took around 87G disk space.

And the disp space is still 50G more free space to reach full. So I am not
sure whether it's the space problom b/c according to my previous
experience, the index command would increase around 1/3 size of the currnet
DB file (I took a test DB with one hundres million records which took abour
7G space, and after the index command the final size reach to 9G around.)

So any ideas? Thanks in advance.

Yi.



___
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