[sqlite] Suggestion about check-in 1fa74930ab

[sqlite]

This check-in is done so that trigger programs can use table-valued-functions. 
But it seems to me that the correct way should be to check if it is a eponymous 
virtual table; whether it uses table-valued-function syntax or not is 
irrelevant. Since, eponymous virtual tables do not belong to any particular 
database.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion: Pre-release Snapshots versioning

[jose isaias cabrera]

Richard Hipp wrote...
On 8/4/17, jose isaias cabrera  wrote:


Right now, when I went to a machine that I had upgraded with a
snapshot, I saw that the version was 3.20.0.  But when I compared the DLL
file size and date, they were different.  It would be nice for 
pre-releases

to have something to distinguish them with the new one.


That "something" is the "source-id".  You can access the source-id
from C-code using

   SQLITE_SOURCE_ID   (https://sqlite.org/c3ref/c_source_id.html)

or

   sqlite3_sourceid()  (https://sqlite.org/c3ref/libversion.html)

Or you can access the information from SQL using

   sqlite_source_id() 
(https://sqlite.org/lang_corefunc.html#sqlite_source_id)


The 3.20.0 release version has a source-id of

   "2017-08-01 13:24:15
9501e22dfeebdcefa783575e47c60b514d7c2e0cad73b2a496c0bc4b680900a8"

The snapshots have an earlier date.  You can trace the version of
SQLite you are running back to a particular source-code repository
check-in using the hash.  Let $HASH be some prefix of the hash shown
at the end of the source-id.  (8 characters is usually plenty.)  Then
you can find the check-in, in context, by visiting
"sqlite3.org/src/timeline?c=$HASM".  For example:

   https://sqlite.org/src/timeline?c=9501e22d

Thanks.

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

Re: [sqlite] Suggestion: Pre-release Snapshots versioning

[Richard Hipp]

On 8/4/17, jose isaias cabrera  wrote:
>
> Right now, when I went to a machine that I had upgraded with a
> snapshot, I saw that the version was 3.20.0.  But when I compared the DLL
> file size and date, they were different.  It would be nice for pre-releases
> to have something to distinguish them with the new one.

That "something" is the "source-id".  You can access the source-id
from C-code using

SQLITE_SOURCE_ID   (https://sqlite.org/c3ref/c_source_id.html)

or

sqlite3_sourceid()  (https://sqlite.org/c3ref/libversion.html)

Or you can access the information from SQL using

sqlite_source_id()  (https://sqlite.org/lang_corefunc.html#sqlite_source_id)

The 3.20.0 release version has a source-id of

"2017-08-01 13:24:15
9501e22dfeebdcefa783575e47c60b514d7c2e0cad73b2a496c0bc4b680900a8"

The snapshots have an earlier date.  You can trace the version of
SQLite you are running back to a particular source-code repository
check-in using the hash.  Let $HASH be some prefix of the hash shown
at the end of the source-id.  (8 characters is usually plenty.)  Then
you can find the check-in, in context, by visiting
"sqlite3.org/src/timeline?c=$HASM".  For example:

https://sqlite.org/src/timeline?c=9501e22d

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Suggestion: Pre-release Snapshots versioning

[jose isaias cabrera]

Greetings!

Thanks for this wonderful tool! Dr. Hipp and team, thanks.

A suggestion I have is that the pre-release snapshots have some type of 
versioning.  Right now, when I went to a machine that I had upgraded with a 
snapshot, I saw that the version was 3.20.0.  But when I compared the DLL file 
size and date, they were different.  It would be nice for pre-releases to have 
something to distinguish them with the new one.  I suggest “3.20.0 beta release 
0” or “3.20.0 beta 201707251512”.  I know that this may damage some folks 
programming, but it will save time for some of us that use multiple machines 
for testing and also to distinguish between the regular releases and the 
pre-releases.  Thanks.

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

Re: [sqlite] Suggestion for PRAGMA SYNCHRONOUS=0 documentation

[Clemens Ladisch]

Rowan Worth wrote:
> I do think it's worth a mention in the PRAGMA SYNCHRONOUS=0 documentation
> that sqlite3_close() doesn't propagate I/O errors so you might never hear
> about write failures in this mode.

When writing asynchronously, errors can be detected _after_ the close, so
this is not even a problem with sqlite3_close() itself.


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

[sqlite] Suggestion for PRAGMA SYNCHRONOUS=0 documentation

[Rowan Worth]

Hi guys,

Ran into an interesting case today where one of our programs was writing an
sqlite DB to a network file system. Just as it was finishing up there was a
brief connectivity issue, and sqlite's call to close() ran into an EIO
error.

Sqlite3_close() doesn't return an error in this case so it wasn't
immediately obvious that anything had gone wrong - the program claimed to
finish successfully but the result was a corrupt DB. Later we found
sqlite's warning in a log file explaining what happened.

Personally I think it would be courteous for sqlite3_close() to return
SQLITE_IOERR in this case, but I acknowledge that any sane/honest VFS layer
would have reported any error at the last sync() call.


... so the final arc of the story, as you may have guessed from the subject
line, is that this program uses PRAGMA SYNCHRONOUS=0 for performance
reasons and therefore there is no sync() call.


If you think everything through carefully there's no real surprises here,
and I don't really have an issue with sqlite's behaviour. But I do think
it's worth a mention in the PRAGMA SYNCHRONOUS=0 documentation that
sqlite3_close() doesn't propagate I/O errors so you might never hear about
write failures in this mode.


And yes I appreciate that writing to a network file system without sync()
is about as unsupported as possible :)

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

[sqlite] Suggestion: Regularize output of setting pragmas.

[Scott Hess]

Often, PRAGMA are documented like mmap_size, like:

> Query or change the maximum number of bytes that are set aside
> for memory-mapped I/O on a single database. The first
> form (without an argument) queries the current limit. The
> second form (with a numeric argument) sets the limit for the
> specified database, or for all databases if the optional
> database name is omitted.

I notice that mmap_size _also_ returns the set value, which is useful to
query since it could be constrained by compile-time options or
sqlite3_config settings.  Like:

   sqlite> pragma mmap_size = 3221225472;  -- 3GB
   2147418112  -- 2GB - 64KB

Sometimes pragma don't do this:

   sqlite> pragma auto_vacuum = 1;
   sqlite> pragma auto_vacuum;
   1

Might it be useful to have a general policy that all PRAGMA calls return a
result consistent with the first form, even if an argument is passed?  As
things currently stand, you really shouldn't depend on behavior like
mmap_size's current implementation to do set-and-query, but you also can't
write code as if setting pragma do not return results.

-scott

[sqlite] Suggestion for .import improvement

[James K. Lowden]

On Mon, 11 May 2015 22:24:53 +0300
 wrote:

> Current .import behavior is that when the imported table does not
> exist, it will be created using the first row from the data file as
> column names for the new table.

Huh, somehow I'd forgotten that!  

> Once the initial table is created by the first .import, every
> subsequent time .import will add the header row again and again as
> data.

I think your suggested solution is a bit arcane, though.  A single
change to the header row, perhaps capitalization or adding an
underscore, would cause the header row to be treated as data.  And the
whole create-table-from-headers feature, while admittedly sometimes
convenient, is also a lion trap.  Most of the time, I'd rather be told
the tablename I typed doesn't exist than to automatically create a new
one  with the wrong name or in the wrong database.  

It would be nice to be able to control that feature instead of adding to
its automagic.  In addition, it would be nice to control which lines of
the data file are imported.  

My first suggestion would be to add a setting, perhaps 

.import_mode create|require|skip_header|check_header
or
.autocreate on|off

The first option's values are:

create: .import *always* creates a table (else error)
require: .import *never* creates a table
skip_header: like require, and skip the first line of a file
check_header: like require, and verify that strings in the first
line of the file match the column names

That's pretty comprehensive, but also a little rigid.  Sometimes
there's more to choose about the file than how to treat the first
line.  That's more than a humble .import_mode setting can bear.  

Deselecting the header line isn't so hard, after all.  One way is to
replace 

sqlite3 db ".import '${FILE}' tablename"
with
sed 1d ${FILE} | sqlite3 db ".import '/dev/stdin' tablename"

But what if it's not a one-liner?  How to choose lines through
the .import syntax?  

.import FILE TABLE

I would take a page out of Perl's syntax, and let FILE be a command if
the first character is '|', 

sqlite3 db ".import '| sed 1d ${FILE}' tablename"

Drop the leading pipe character of the FILE string, hand the rest to
exec(2), and read its output from a pipe.  That give you the full
capabilty of the command line to manipulate the file as it's imported.  

--jkl

[sqlite] Suggestion for .import improvement

[to...@acm.org]

I would like to request this improvement to the shell .import command.  (Wish 
list, I guess.)

Current .import behavior is that when the imported table does not exist, it 
will be created using the first row from the data file as column names for the 
new table.

This is great but it has one problem:

Example use case: Loading phone call logs.  Each month a new file with just 
that month?s activity needs to be inserted.  So, old data has to remain as is, 
and new data added to the table.  The format of the file is always the same: 
header row followed by data rows.

Once the initial table is created by the first .import, every subsequent time 
.import will add the header row again and again as data.

My suggestion to avoid this is to behave like so, instead:

1. If the table does not exist, the same as current behavior: Create table 
using first row for column names.
2. If the table exists, and the first row matches the column names, skip the 
first row, and continue inserting the rest of the rows.
3. If the table exists, and the first row does not match the column names, 
treat as data, and insert into table.  (This takes care if the file has no 
longer header row, or manually edited out.)

Is this possible?

It?s a real nuisance to have to edit out the headers every time, especially 
when the files are very large and they do not load fast (or at all) into a text 
editor.  Plus, it?s semi-automatic.
(I know I could write a Lua/Python script to do this filtering but I think it 
would be simpler and more immediate if the SQLite shell could take care of it 
directly.)

Thank you.

[sqlite] Suggestion for .import improvement

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/11/2015 12:24 PM, tonyp at acm.org wrote:
> It?s a real nuisance to have to edit out the headers every time,
> especially when the files are very large and they do not load fast
> (or at all) into a text editor.  Plus, it?s semi-automatic.

I suggest you use a simpler way.  Create a temporary table and do the
.import into that.  You can then copy that table into your desired one:

  BEGIN
  CREATE TEMPORARY TABLE temptable .;
  .import log.csv temptable
  INSERT INTO desired SELECT * from temptable;
  COMMIT

You can explicitly delete the temp table, or it will automatically go
away when you close the connection.

The advantage of this approach is you can do processing on the
temporary table (eg convert nulls to empty string or vice versa, drop
header row), add a column (eg import date), have different columns
and/or names between the two (adjust the INSERT/SELECT statement) etc.
 This will all work from the shell, requires no programming, and will
import just as fast.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlVRVTYACgkQmOOfHg372QREYwCePcN1Z5RauOs7lo4jvtLNyx+t
018AnRJpES+JhBR5kM3+7m4Fa2p1I9t6
=oss6
-END PGP SIGNATURE-

Re: [sqlite] Suggestion for syntax enhancement for virtual tables

[Peter Aronson]

If only the xDisconnect method is called on a virtual table create in the temp 
database at disconnect time, is that the only time xDisconnect will be called?  
The documentation at sqlite.org doesn't seem to say.  Jay Krebich's Using 
SQLite says xDisconnect is "Called when a database containing a virtual table 
instance is detached or closed.  Called once for each table instance."  But 
looking at the SQLite code and comments, I'm not sure this is true.  Is it?  If 
so, it would be easy enough when writing a Virtual Table Module to note that it 
is being created in the temp database, and do any required cleanup in 
xDisconnect instead of xDestroy for that instance.  But if xDisconnect can be 
called at other times, cleanup could be premature.

Best,

Peter


On Friday, January 2, 2015 12:56 AM, Dan Kennedy <danielk1...@gmail.com> wrote:
 

>
>
>On 01/02/2015 01:58 PM, Hick Gunter wrote:
>> Temporary virtual tables sounds like an interesting concept. Does the 
>> xDestroy() function get called on such a beast (as opposed to xDisconnect() 
>> when the connection is closed)?
>
>Just xDisconnect().
>
>Dan.
>
>
>
>>   Should that function delete the backing store (even if a non-temporary 
>> virtual table is still connected)?
>>
>> -Ursprüngliche Nachricht-
>> Von: Baruch Burstein [mailto:bmburst...@gmail.com]
>> Gesendet: Donnerstag, 01. Jänner 2015 08:38
>> An: General Discussion of SQLite Database
>> Betreff: [sqlite] Suggestion for syntax enhancement for virtual tables
>>
>> For creating temporary virtual tables, currently you need to do:
>>
>> CREATE VIRTUAL TABLE temp.t ...
>>
>> Can this syntax be made to work too (similar to creating regular tables)?
>>
>> CREATE VIRTUAL TEMP TABLE t ...
>> or
>> CREATE TEMP VIRTUAL TABLE t ...
>>
>> --
>> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı 
>> ___
>> 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] Suggestion for syntax enhancement for virtual tables

[Dan Kennedy]

On 01/02/2015 01:58 PM, Hick Gunter wrote:

Temporary virtual tables sounds like an interesting concept. Does the 
xDestroy() function get called on such a beast (as opposed to xDisconnect() 
when the connection is closed)?


Just xDisconnect().

Dan.




  Should that function delete the backing store (even if a non-temporary 
virtual table is still connected)?

-Ursprüngliche Nachricht-
Von: Baruch Burstein [mailto:bmburst...@gmail.com]
Gesendet: Donnerstag, 01. Jänner 2015 08:38
An: General Discussion of SQLite Database
Betreff: [sqlite] Suggestion for syntax enhancement for virtual tables

For creating temporary virtual tables, currently you need to do:

CREATE VIRTUAL TABLE temp.t ...

Can this syntax be made to work too (similar to creating regular tables)?

CREATE VIRTUAL TEMP TABLE t ...
or
CREATE TEMP VIRTUAL TABLE t ...

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı 
___
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] Suggestion for syntax enhancement for virtual tables

[Hick Gunter]

Temporary virtual tables sounds like an interesting concept. Does the 
xDestroy() function get called on such a beast (as opposed to xDisconnect() 
when the connection is closed)? Should that function delete the backing store 
(even if a non-temporary virtual table is still connected)?

-Ursprüngliche Nachricht-
Von: Baruch Burstein [mailto:bmburst...@gmail.com] 
Gesendet: Donnerstag, 01. Jänner 2015 08:38
An: General Discussion of SQLite Database
Betreff: [sqlite] Suggestion for syntax enhancement for virtual tables

For creating temporary virtual tables, currently you need to do:

CREATE VIRTUAL TABLE temp.t ...

Can this syntax be made to work too (similar to creating regular tables)?

CREATE VIRTUAL TEMP TABLE t ...
or
CREATE TEMP VIRTUAL TABLE t ...

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı 
___
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] Suggestion for syntax enhancement for virtual tables

[Baruch Burstein]

For creating temporary virtual tables, currently you need to do:

CREATE VIRTUAL TABLE temp.t ...

Can this syntax be made to work too (similar to creating regular tables)?

CREATE VIRTUAL TEMP TABLE t ...
or
CREATE TEMP VIRTUAL TABLE t ...

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)

[tonyp]

Stored procedure would be a great addition.  But they would be not in the 
shell, they belong in the library core.

Scripts are already a part of the current functionality.  Only they have to be 
external.  There no IFs, loops, or whatever other constructs.  Those who say 
soon we would want this and that, it is irrelevant.  Because we could want this 
and that even if scripts remain external to the database.

The idea is to simply move scripts inside the db and add parameters (like 
simple in macro processors.)  That's all.

Talking about anything more is a much more involved proposal, and when 
something adds too many complications it will inevitably take forever (if ever) 
to implement.

-Original Message- 
From: J Decker 
Sent: Thursday, June 19, 2014 3:45 AM 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Suggestion for SHELL impovement (built-in scripting) 

Stored procedures

variables, a goto(jump/branch) and a conditional so loops can be made;

On the topic of shell results; isn't there a result value of like number of
records inserted ? So something like var a = insert... select $a; and then
test the output sort of?

went searching cause I really ended up avoiding stored procedures because
of the lack of consistency I don't think SQL standard defines such
things... what I saw was very linear  top-down sort of things, which makes
querying a recursive heirarchical table kinda hard to do in a stored
procedure but I guess I was wrong; but they are all different.

http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html  (mysql
does have loop constructs)
http://technet.microsoft.com/en-us/library/ms180796(v=sql.105).aspx ( flow
control in M$ SQL  [tsql])

http://en.wikipedia.org/wiki/SQL#Procedural_extensions (standard?) ya ...
almost as many flavors of this as there are databases.  sad.
___
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] Suggestion for SHELL impovement (built-in scripting)

[J Decker]

Stored procedures

variables, a goto(jump/branch) and a conditional so loops can be made;

On the topic of shell results; isn't there a result value of like number of
records inserted ? So something like var a = insert... select $a; and then
test the output sort of?

went searching cause I really ended up avoiding stored procedures because
of the lack of consistency I don't think SQL standard defines such
things... what I saw was very linear  top-down sort of things, which makes
querying a recursive heirarchical table kinda hard to do in a stored
procedure but I guess I was wrong; but they are all different.

http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html  (mysql
does have loop constructs)
http://technet.microsoft.com/en-us/library/ms180796(v=sql.105).aspx ( flow
control in M$ SQL  [tsql])

http://en.wikipedia.org/wiki/SQL#Procedural_extensions (standard?) ya ...
almost as many flavors of this as there are databases.  sad.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)

[Nico Williams]

My suggestion is to have a sqlite_... table in which to start
statements to run at DB open time, so as to:

 - automatically CREATE temp tables, indexes, views
 - automatically ATTACH related DBs
 - automatically load extensions (this should require explicit
acquiescence from the API caller though)

This would allow the use of the sqlite3 shell with apps fully
contained in the DB.

This is almost what you want, but not quite.  This is better because
all it does is initial setup.  Application logic would still be
restricted to being in the schema, but now that could include temp
schema that "persists" -- not entirely an oxymoron.

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

Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)

[Roger Binns]

On 06/18/2014 02:47 PM, to...@acm.org wrote:

But you need bash, or TCL, or Perl, or Python, or whatever other than
sqlite3.exe
So, you're suggesting that an innocent SQLite user should install any of
those programming packages just to run SQLite.  Hmm... no, thanks!


Yes.  Quite simply you'll start wanting if statements and variables, and 
loops, and stronger matching primitives etc.  That is why I said "add 
puny inadequate incomplete scripting into the C based shell".  Unless 
you can show that what you asked for is the limit of functionality 
needed for all users of your proposed feature for the foreseeable 
future, extra scripting stuff would be added.


It is far more sensible to use an existing one than invent an arbitrary 
new one.


Roger


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

Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)

[jose isaias cabrera]

to...@acm.org wrote...


Hi all,

...
So here’s my suggestion for what (I feel) is a significant improvement for 
the SHELL version of SQLite without being too much of a programming 
complication in my view.  (Those who usually attack any new concept, 
please pause a moment and give it some thought, then attack as usual!)

Funny...
...
...
This capability would make the shell capable of supporting very complex 
script-based command-line applications all stored within the same single 
database file!


I happen to think that this is a great idea.  I can see many ways where I 
can use it. 


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

[sqlite] Suggestion for SHELL impovement (built-in scripting)

[tonyp]

Hi all,

First of all, this is not about using SQLite as an embedded library from C, 
or whatever other language.  It does not affect the sqlite3.c file at all. 
It only applies to the shell (so logically, it only affects shell.c)


So here’s my suggestion for what (I feel) is a significant improvement for 
the SHELL version of SQLite without being too much of a programming 
complication in my view.  (Those who usually attack any new concept, please 
pause a moment and give it some thought, then attack as usual!)


Because I primarily use SQLite as a tool (from the shell), and given that 
SQLite does not (yet, if ever) support stored procedures, I often find 
myself (as I believe most people on this list) writing scripts that will do 
a certain task, and then run those using the shell with something like 
“sqlite3 my.db < script.sql”


I think we can all agree that the single most important advantage of SQLite 
is the one-file-holds-everything deal (both for the database file, and for 
the application or library file).  On that principle,...


The problem with having all those scripts separate from the database file 
somehow violate the previous assertion.  Plus, there is the problem that 
scripts cannot be made to have parameters (AFAIK).

So, two birds with one stone, ...

Wouldn’t it be nice if we could have those scripts somehow saved in the 
sqlite_master table (or some other new system table, if this one would cause 
compatibility issues), and then be able to call them very easily from the 
shell with some special prefix (e.g., :SCRIPTNAME parm1 parm2 parm3 ... – or 
some similar simple syntax).  The : character could be some other special 
character (except for . used for built in commands.)


Then the shell, using the simplest of macro expansion techniques of plain 
text replacement, would read each line from the saved script, convert 
occurrence of the each parameter to the text appearing in the invocation and 
run it as if it was just typed on the keyboard.


For example, if my script was:

SELECT ~1~ from ~2~ where name like (‘%~3~%’);

giving the shell command:

:SCRIPTNAME * my_table some_name

would be executed as:

SELECT * from my_table where name like (‘%some_name%’);

I have used ~number~ as a parameter placeholder, but anything that works 
without ambiguities in the grammar would work.


And, then the next line of the script would be executing in a similar manner 
until the whole script is exhausted.


This capability would make the shell capable of supporting very complex 
script-based command-line applications all stored within the same single 
database file!


Thanks for listening (hopefully).

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

Re: [sqlite] Suggestion for shell .IMPORT improvement

[Stephan Beal]

On Thu, May 22, 2014 at 4:55 PM, Dominique Devienne wrote:

> On Thu, May 22, 2014 at 3:26 PM, Noel Frankinet
>  wrote:
> > I propose Musqlar, the Mighty universal sql Archiver :-)
>
> Sounds like Musk-lar, so you loose the S.Q.L. of sqlar which sounds to
> me like C-quel-ar ;)
>


Maybe this is stretching it a bit, but how about...

darh?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion for shell .IMPORT improvement

[Dominique Devienne]

On Thu, May 22, 2014 at 3:26 PM, Noel Frankinet
 wrote:
> I propose Musqlar, the Mighty universal sql Archiver :-)

Sounds like Musk-lar, so you loose the S.Q.L. of sqlar which sounds to
me like C-quel-ar ;)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion for shell .IMPORT improvement

[Noel Frankinet]

I propose Musqlar, the Mighty universal sql Archiver :-)


On 22 May 2014 15:22, Dominique Devienne  wrote:

> On Thu, May 22, 2014 at 1:51 PM, Richard Hipp  wrote:
> > How about "sqlar"  for "SQL Archive"?  http://www.sqlite.org/sqlar
>
> I like it! Sounds ominous when pronounced out loud :).
>
> Much better name IMHO, and no negative connotation in French that I
> know of. --DD
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion for shell .IMPORT improvement

[Dominique Devienne]

On Thu, May 22, 2014 at 1:51 PM, Richard Hipp  wrote:
> How about "sqlar"  for "SQL Archive"?  http://www.sqlite.org/sqlar

I like it! Sounds ominous when pronounced out loud :).

Much better name IMHO, and no negative connotation in French that I
know of. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion for shell .IMPORT improvement

[Richard Hipp]

On Thu, May 22, 2014 at 7:46 AM, Richard Hipp  wrote:

>
>
>
> On Thu, May 22, 2014 at 7:36 AM, Dominique Devienne 
> wrote:
>
>> On Thu, May 22, 2014 at 12:39 PM, Richard Hipp  wrote:
>> > Hyperlink has changed.  It is now called "sfa" instead of "sar":
>>
>> I can't read "sfa" w/o remembering tv ads [1] for the toilet
>> equivalent to a sink garbage disposal :)
>>
>
> Good to know.  Suggestions for a better name?
>

How about "sqlar"  for "SQL Archive"?  http://www.sqlite.org/sqlar



>
>
>>
>> Some acronyms don't cross language barriers w/o harm, like Audi's
>> "e-tron" car [2]. --DD
>>
>> [1] https://www.google.com/search?q=pub+sanibroyeur+sfa
>> [2]
>> http://www.autoblog.com/2010/09/13/oh-crap-audi-mucks-up-e-tron-name-in-french/
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>



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

Re: [sqlite] Suggestion for shell .IMPORT improvement

[Richard Hipp]

On Thu, May 22, 2014 at 7:36 AM, Dominique Devienne wrote:

> On Thu, May 22, 2014 at 12:39 PM, Richard Hipp  wrote:
> > Hyperlink has changed.  It is now called "sfa" instead of "sar":
>
> I can't read "sfa" w/o remembering tv ads [1] for the toilet
> equivalent to a sink garbage disposal :)
>

Good to know.  Suggestions for a better name?


>
> Some acronyms don't cross language barriers w/o harm, like Audi's
> "e-tron" car [2]. --DD
>
> [1] https://www.google.com/search?q=pub+sanibroyeur+sfa
> [2]
> http://www.autoblog.com/2010/09/13/oh-crap-audi-mucks-up-e-tron-name-in-french/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Suggestion for shell .IMPORT improvement

[Dominique Devienne]

On Thu, May 22, 2014 at 12:39 PM, Richard Hipp  wrote:
> Hyperlink has changed.  It is now called "sfa" instead of "sar":

I can't read "sfa" w/o remembering tv ads [1] for the toilet
equivalent to a sink garbage disposal :)

Some acronyms don't cross language barriers w/o harm, like Audi's
"e-tron" car [2]. --DD

[1] https://www.google.com/search?q=pub+sanibroyeur+sfa
[2] 
http://www.autoblog.com/2010/09/13/oh-crap-audi-mucks-up-e-tron-name-in-french/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion for shell .IMPORT improvement

[Richard Hipp]

On Thu, May 22, 2014 at 4:22 AM, Kees Nuyt  wrote:

> On Sun, 18 May 2014 01:06:42 +0300,  wrote:
>
> > PS. By the way, any progress on the shell enhancement to load/save blobs?
> > This will make the shell capable of handling any database without the
> need
> > for external programs.
>
> I think SQLite File Archiver comes very close:
> http://www.sqlite.org/sar/doc/trunk/README.md
>


Hyperlink has changed.  It is now called "sfa" instead of "sar":

http://www.sqlite.org/sfa

The old link ("sar") still works, but will eventually be removed.


>
> --
> Groet, Cordialement, Pozdrawiam, Regards,
>
> Kees Nuyt
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Suggestion for shell .IMPORT improvement

[Kees Nuyt]

On Sun, 18 May 2014 01:06:42 +0300,  wrote:

> PS. By the way, any progress on the shell enhancement to load/save blobs? 
> This will make the shell capable of handling any database without the need 
> for external programs.

I think SQLite File Archiver comes very close:
http://www.sqlite.org/sar/doc/trunk/README.md

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

[sqlite] Suggestion for shell .IMPORT improvement

[tonyp]

Hello,

When .importing from a csv file (for example) with a command like ".import 
data.csv data" if the table does not exist, the table is created using the 
first row of data.csv as the field names.
But if the same file is imported a second time, the first line is treated as 
data.


My suggestion is to recognize if the first line matches the current table 
field names, and only if not, treat it as data.  This is because one often 
gets the same CSV (for example) file format, and while the first run 
correctly creates the table, consequent updates insert dummy records (the 
first line which is the header.)


Thanks.

PS. By the way, any progress on the shell enhancement to load/save blobs? 
This will make the shell capable of handling any database without the need 
for external programs.


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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

Hello,

Thanks to Keith's help I succeeded to set up a comparison a "native" sqrt()
versus a "python" mysqrt() function .

The speed-up in a best case non-realistic scenario is only 40%.

create_function() looks very performant.

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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[Keith Medcalf]

I built-in an extension which defines all the standard math functions as SQL 
functions.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of big stone
>Sent: Monday, 10 March, 2014 13:22
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Suggestion to add "locate" as a broader version of
>"instr"
>
>Hello Keith,
>
>Thanks for the ".timer on" tip.
>
>I only succeed to get a "Error: not such function : sqrt" from default
>Sqlite.exe
>How did you get that "native" sqrt working ?
>
>
>regards,
>___
>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] Suggestion to add "locate" as a broader version of "instr"

[big stone]

Hello Keith,

Thanks for the ".timer on" tip.

I only succeed to get a "Error: not such function : sqrt" from default
Sqlite.exe
How did you get that "native" sqrt working ?


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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[Simon Slavin]

On 10 Mar 2014, at 1:38am, Stephen Chrzanowski  wrote:

> Apologies for the interruption and sort of off topic, but, is .timer part
> of the CLI only or is it part of the SQL language?  Can I get the result of
> a timer from a call, or do I have to put a wrapper on my wrapper?

Commands which start with a dot are part of the Shell Tool, not built into the 
SQLite API.

Furthermore there are no rules about which order SQLite would execute something 
like

SELECT timerBefore(), somethingComplicated(), timerAfter() FROM myTable

in.  Sorry.

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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[Keith Medcalf]

On Sunday, 9 March, 2014 19:38, Stephen Chrzanowski <pontia...@gmail.com> 
inquired:

>Apologies for the interruption and sort of off topic, but, is .timer part
>of the CLI only or is it part of the SQL language?  Can I get the result
>of a timer from a call, or do I have to put a wrapper on my wrapper?

.timer is a shell command specific to the sqlite shell and not part of the SQL 
language.  

You would have to put your own wrapper to collect timing data.

>On Sun, Mar 9, 2014 at 8:17 PM, Keith Medcalf <kmedc...@dessus.com>
>wrote:
>
>>
>> sqlite> create virtual table n using wholenumber;
>> sqlite> .timer on
>> sqlite> select sum(sqrt(value)) from n where value between 1 and 1000;
>> 21097.4558874807
>> Run Time: real 0.001 user 0.00 sys 0.00
>> sqlite> select sum(sqrt(value)) from n where value between 1 and
>100;
>> 67166.458841
>> Run Time: real 0.160 user 0.156250 sys 0.00
>> sqlite> select sum(sqrt(value)) from n where value between 1 and
>> 10;
>> 21081851083598.4
>> Run Time: real 151.021 user 151.031250 sys 0.00
>> sqlite> select sum(value) from n where value between 1 and 10;
>> 55
>> Run Time: real 89.341 user 89.343750 sys 0.00
>>
>> A native sqrt takes about 60 ns per operation.
>>
>> >-Original Message-
>> >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> >boun...@sqlite.org] On Behalf Of big stone
>> >Sent: Sunday, 9 March, 2014 03:35
>> >To: sqlite-users@sqlite.org
>> >Subject: Re: [sqlite] Suggestion to add "locate" as a broader version
>of
>> >"instr"
>> >
>> >Hello Max,
>> >
>> >Your link is pretty interesting. It looks that :
>> >- method1 should be easily implemented with SQLite "floating point"
>> >representation,
>> >- and with a very very small code size.
>> >
>> >Here is the benchmarking of the two available methods :
>> >
>>
>>https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_ben
>c
>> >hmark.GIF
>> >
>> >
>> >So :
>> >- your method is only 3 times slower than the python sqrt(),
>> >- if SQLite team accepts to sacrifice a few bytes to implement sqrt(),
>we
>> >may benefit a  389% speed-up (300/27*.652/1.86) at least.
>> >(300/27*.652/1.86)
>> >
>> >sqrt() is very interesting for statistics on-the-go over sql datas.
>> >
>> >Regards,
>> >___
>> >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] Suggestion to add "locate" as a broader version of "instr"

[Stephen Chrzanowski]

Apologies for the interruption and sort of off topic, but, is .timer part
of the CLI only or is it part of the SQL language?  Can I get the result of
a timer from a call, or do I have to put a wrapper on my wrapper?


On Sun, Mar 9, 2014 at 8:17 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> sqlite> create virtual table n using wholenumber;
> sqlite> .timer on
> sqlite> select sum(sqrt(value)) from n where value between 1 and 1000;
> 21097.4558874807
> Run Time: real 0.001 user 0.00 sys 0.00
> sqlite> select sum(sqrt(value)) from n where value between 1 and 100;
> 67166.458841
> Run Time: real 0.160 user 0.156250 sys 0.00
> sqlite> select sum(sqrt(value)) from n where value between 1 and
> 10;
> 21081851083598.4
> Run Time: real 151.021 user 151.031250 sys 0.00
> sqlite> select sum(value) from n where value between 1 and 10;
> 55
> Run Time: real 89.341 user 89.343750 sys 0.00
>
> A native sqrt takes about 60 ns per operation.
>
> >-Original Message-
> >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> >boun...@sqlite.org] On Behalf Of big stone
> >Sent: Sunday, 9 March, 2014 03:35
> >To: sqlite-users@sqlite.org
> >Subject: Re: [sqlite] Suggestion to add "locate" as a broader version of
> >"instr"
> >
> >Hello Max,
> >
> >Your link is pretty interesting. It looks that :
> >- method1 should be easily implemented with SQLite "floating point"
> >representation,
> >- and with a very very small code size.
> >
> >Here is the benchmarking of the two available methods :
> >
> >https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_benc
> >hmark.GIF
> >
> >
> >So :
> >- your method is only 3 times slower than the python sqrt(),
> >- if SQLite team accepts to sacrifice a few bytes to implement sqrt(), we
> >may benefit a  389% speed-up (300/27*.652/1.86) at least.
> >(300/27*.652/1.86)
> >
> >sqrt() is very interesting for statistics on-the-go over sql datas.
> >
> >Regards,
> >___
> >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] Suggestion to add "locate" as a broader version of "instr"

[Keith Medcalf]

sqlite> create virtual table n using wholenumber;
sqlite> .timer on
sqlite> select sum(sqrt(value)) from n where value between 1 and 1000;
21097.4558874807
Run Time: real 0.001 user 0.00 sys 0.00
sqlite> select sum(sqrt(value)) from n where value between 1 and 100;
67166.458841
Run Time: real 0.160 user 0.156250 sys 0.00
sqlite> select sum(sqrt(value)) from n where value between 1 and 10;
21081851083598.4
Run Time: real 151.021 user 151.031250 sys 0.00
sqlite> select sum(value) from n where value between 1 and 10;
55
Run Time: real 89.341 user 89.343750 sys 0.00

A native sqrt takes about 60 ns per operation.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of big stone
>Sent: Sunday, 9 March, 2014 03:35
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Suggestion to add "locate" as a broader version of
>"instr"
>
>Hello Max,
>
>Your link is pretty interesting. It looks that :
>- method1 should be easily implemented with SQLite "floating point"
>representation,
>- and with a very very small code size.
>
>Here is the benchmarking of the two available methods :
>
>https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_benc
>hmark.GIF
>
>
>So :
>- your method is only 3 times slower than the python sqrt(),
>- if SQLite team accepts to sacrifice a few bytes to implement sqrt(), we
>may benefit a  389% speed-up (300/27*.652/1.86) at least.
>(300/27*.652/1.86)
>
>sqrt() is very interesting for statistics on-the-go over sql datas.
>
>Regards,
>___
>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] Suggestion to add "locate" as a broader version of "instr"

[big stone]

Hello Max,

Your link is pretty interesting. It looks that :
- method1 should be easily implemented with SQLite "floating point"
representation,
- and with a very very small code size.

Here is the benchmarking of the two available methods :

https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_benchmark.GIF


So :
- your method is only 3 times slower than the python sqrt(),
- if SQLite team accepts to sacrifice a few bytes to implement sqrt(), we
may benefit a  389% speed-up (300/27*.652/1.86) at least.
(300/27*.652/1.86)

sqrt() is very interesting for statistics on-the-go over sql datas.

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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[Max Vlasov]

On Sat, Mar 8, 2014 at 2:24 AM, big stone  wrote:
> Ooups !
>
> Thanks to the awesome  posts about "RPAD/LPAD", I understood that I could
> already create a "sqrt()" function for SQLite3  in interpreted python.
>


Yes, that discussion was inspiring :)

Looking at your task I also played with cte version of sqrt.
Based on the "guessing" approach from one of the answers from
  
http://stackoverflow.com/questions/3581528/how-is-the-square-root-function-implemented
the following query finally worked.

/*
  :value=12345
*/
with recursive
  sqrt(depth, val, guess) as
   (
  select 1, :value, Cast(:value as Float)/2
  UNION ALL
  select depth + 1, val as newval, ((guess + val/guess)/2) as
newguess from sqrt where abs(newguess - guess) > 1e-308 and depth <
100
   )
select guess from sqrt order by depth desc limit 1

but I could not overcome some pecularities of float numbers so depth <
100 here is for cases when comparison fails to stop.
Also for CTE queries in general I wonder whether there is another
faster way to get the last row of the query (in natural executing
order), so order by depth can be replaced by something else. I suspect
ordering here triggers temporary storage.

I tested this function as "expression function" implemented based on
that thread and an average speed of this one is about 4000 sqrt
operations / second on a mobile Intel i3. Not so fast, but if one
desperately needs one, then it would be ok.

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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

Ooups !

Thanks to the awesome  posts about "RPAD/LPAD", I understood that I could
already create a "sqrt()" function for SQLite3  in interpreted python.

 (example) ***
import sqlite3
db_filename = ':memory:'

def mysqrt(s):
""" returns sqrt(s)"""
#must return a string, apparently
return ("%s" %sqrt(s))

with sqlite3.connect(db_filename) as conn:

conn.create_function('mysqrt', 1, mysqrt)
cursor = conn.cursor()
query = "select 'hello, sqrt' , mysqrt(3), 'of ', mysqrt(3)*mysqrt(3) "
cursor.execute(query)
for row in cursor.fetchall():
print (row)
cursor.close
cursor = None
conn.close
conn = None


 (The link that showed nicely how to play with that) ***
http://pymotw.com/2/sqlite3/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[Gabor Grothendieck]

On Fri, Feb 14, 2014 at 2:33 PM, Max Vlasov  wrote:
> Hi,
>
> Some time ago when there was no "instr" functions, I looked at Mysql help
> pages and implemented a user function "locate" as the one that allows
> searching starting a particular position in the string. With two parameters
> form it was just identical to "instr" only the order of parameters was
> reversed. As I see, the latest sqlite has only "instr".
>
> It's not a big deal, but I noticed that "locate" with three parameters
> becomes convenient for CTE recursive queries since it allows search
> sequentially in the string. For example, a little bulky at last, but I
> managed to do "comma-list to dataset" query
>
> I suppose implementing "locate" and doing "instr" as a call to "locate"
> would cost the developers probably no more than a hundred of bytes for the
> final binary


Parsing fields is also done with substring_index in MySQL and having both locate
and substring_index would be useful for MySQL compatibility.

Parsing fields created using group_concat is one particular example.
One related
item is that in MySQL group_concat can specify the order of rows to be
concatenated
as well as a number of other aspects not currently available in SQLite.

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

+1 .

A few more 'classic/simple' sql instructions would not be a bad thing :
sqrt(), locate(substring, string, start), ...
They are not in a sql official "normalisation", but :
-  '%' is not either,
- avg() looks a little bit incomplete without sqrt().
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Suggestion to add "locate" as a broader version of "instr"

[Max Vlasov]

Hi,

Some time ago when there was no "instr" functions, I looked at Mysql help
pages and implemented a user function "locate" as the one that allows
searching starting a particular position in the string. With two parameters
form it was just identical to "instr" only the order of parameters was
reversed. As I see, the latest sqlite has only "instr".

It's not a big deal, but I noticed that "locate" with three parameters
becomes convenient for CTE recursive queries since it allows search
sequentially in the string. For example, a little bulky at last, but I
managed to do "comma-list to dataset" query

I suppose implementing "locate" and doing "instr" as a call to "locate"
would cost the developers probably no more than a hundred of bytes for the
final binary

Thanks

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

[sqlite] Suggestion for cache_size pragma documentation

[Richard Allen]

Regarding the 'cache_size' pragma
documentation@http://www.sqlite.org/pragma.html -

Could a note be added stating the version number(3.7.10) where a negative
cache_size began to mean a number of kByte instead of a number of
pages? In earlier versions, a negative cache_size was interpreted as
number of pages, and if your page size was large sqlite would use
more memory than expected.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Black, Michael (IS)]

If speed and storage are a concern then as somebody else notedjulianday is 
the way to go.
Just don't confuse CURRENT_TIMESTAMP with CURRENT_TIME -- you can still extract 
just date from the field if you need it.

sqlite> create table t(id,time);
sqlite> insert into t values(1,julianday(CURRENT_TIMESTAMP));
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t(id,time);
INSERT INTO "t" VALUES(1,2456236.05462963); -- you can see storage mode is 
double
COMMIT;
sqlite> select id,date(time) from t;
1|2012-11-04
sqlite> select id,datetime(time) from t;
1|2012-11-04 13:18:40


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Sunday, November 04, 2012 1:34 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"

Fri, 2 Nov 2012 14:11:26 + от "Black, Michael (IS)" 
<michael.bla...@ngc.com>:
>CREATE TABLE t(id,time);
>
INSERT INTO t VALUES(1,CURRENT_DATE);
>
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
>
INSERT INTO t VALUES(3,datetime('now'));
>
INSERT INTO t VALUES(4,date('now'));
>
SELECT * FROM t;
>
1|2012-11-02
>
2|2012-11-02 14:10:15
>
3|2012-11-02 14:10:15
>
4|2012-11-02
>
>
Perhaps the documentation needs to be better?  Apparently you couldn't find 
this info...
Indeed, I was never aware of CURRENT_*.

Anyway, all these functions return current moment as _string_ and this is not a 
great way to store datetime in db, isn't it?
- more memory occupied;
- slower compare;
- cannot add & substract;
etc.




>
>
Michael D. Black
>
Senior Scientist
>
Advanced Analytics Directorate
>
Advanced GEOINT Solutions Operating Unit
>
Northrop Grumman Information Systems
>
>

>
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
>
Sent: Friday, November 02, 2012 8:08 AM
>
To: General Discussion of SQLite Database
>
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"
>
>
Thu, 1 Nov 2012 19:57:42 + от Simon Slavin <slav...@bigfraud.org>:
>
>
>
>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко <grigore...@mail.ru> wrote:
>
>
>
>
>
> it is a common practice to store datetime values as UNIX time UTC.
>
>
>
> Maybe, Sqlite should have some shortcut for evaluating current moment?
>
>
>
>
>
Please read
>
>
>
>
>
<http://www.sqlite.org/lang_datefunc.html>
>
>
>
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
functions that modify or format date values.
>
>
It's about having useful shortcut for getting current moment that doesn't have 
(string) parameters and so can be easily remembered and typed.
>
>
Compare:
>
MS SQL: CURRENT_TIMESTAMP
>
PostgreSQL: now()
>
Oracle: sysdate
>
>
To:
>
Sqlite: strftime('%s','now')
>
>
>
>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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Roger Andersson]

-Ursprungligt meddelande- 
From: Baruch Burstein

Sent: Sunday, November 04, 2012 10:43 AM
To: Григорий Григоренко ; General Discussion of SQLite Database
Subject: Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

CURRENT_TIMESTAMP returns "-MM-DD HH:MM:SS", not a unix timestamp. I
think other DB systems all use this function to return a unix timestamp.

- Reply -
There might be some that does, but
Oracle11g
SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
---
2012-11-04 11:05:23,537000 +01:00

Oracle9i
SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
---
2012-11-04 11:08:48,942211 EUROPE/STOCKHOLM

MySQL 5
mysql> select CURRENT_TIMESTAMP;
+-+
| CURRENT_TIMESTAMP   |
+-+
| 2012-11-04 11:06:31 |
+-+

Regards
Roger 


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

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Baruch Burstein]

CURRENT_TIMESTAMP returns "-MM-DD HH:MM:SS", not a unix timestamp. I
think other DB systems all use this function to return a unix timestamp.

On Sun, Nov 4, 2012 at 9:34 AM, Григорий Григоренко <grigore...@mail.ru>wrote:

>
>
>
> Fri, 2 Nov 2012 14:11:26 + от "Black, Michael (IS)" <
> michael.bla...@ngc.com>:
> >CREATE TABLE t(id,time);
> >
> INSERT INTO t VALUES(1,CURRENT_DATE);
> >
> INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
> >
> INSERT INTO t VALUES(3,datetime('now'));
> >
> INSERT INTO t VALUES(4,date('now'));
> >
> SELECT * FROM t;
> >
> 1|2012-11-02
> >
> 2|2012-11-02 14:10:15
> >
> 3|2012-11-02 14:10:15
> >
> 4|2012-11-02
> >
> >
> Perhaps the documentation needs to be better?  Apparently you couldn't
> find this info...
> Indeed, I was never aware of CURRENT_*.
>
> Anyway, all these functions return current moment as _string_ and this is
> not a great way to store datetime in db, isn't it?
> - more memory occupied;
> - slower compare;
> - cannot add & substract;
> etc.
>
>
>
>
> >
> >
> Michael D. Black
> >
> Senior Scientist
> >
> Advanced Analytics Directorate
> >
> Advanced GEOINT Solutions Operating Unit
> >
> Northrop Grumman Information Systems
> >
> >
> 
> >
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Григорий Григоренко [grigore...@mail.ru]
> >
> Sent: Friday, November 02, 2012 8:08 AM
> >
> To: General Discussion of SQLite Database
> >
> Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for
> "strftime('%s','now')"
> >
> >
> Thu, 1 Nov 2012 19:57:42 + от Simon Slavin <slav...@bigfraud.org>:
> >
> >
> >
> >
> On 1 Nov 2012, at 7:55pm, Григорий Григоренко <grigore...@mail.ru> wrote:
> >
> >
> >
> >
> >
> > it is a common practice to store datetime values as UNIX time UTC.
> >
> >
> >
> > Maybe, Sqlite should have some shortcut for evaluating current moment?
> >
> >
> >
> >
> >
> Please read
> >
> >
> >
> >
> >
> <http://www.sqlite.org/lang_datefunc.html>
> >
> >
> >
> Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking
> functions that modify or format date values.
> >
> >
> It's about having useful shortcut for getting current moment that doesn't
> have (string) parameters and so can be easily remembered and typed.
> >
> >
> Compare:
> >
> MS SQL: CURRENT_TIMESTAMP
> >
> PostgreSQL: now()
> >
> Oracle: sysdate
> >
> >
> To:
> >
> Sqlite: strftime('%s','now')
> >
> >
> >
> >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
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Fri, 2 Nov 2012 14:11:26 + от "Black, Michael (IS)" 
<michael.bla...@ngc.com>:
>CREATE TABLE t(id,time);
>
INSERT INTO t VALUES(1,CURRENT_DATE);
>
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
>
INSERT INTO t VALUES(3,datetime('now'));
>
INSERT INTO t VALUES(4,date('now'));
>
SELECT * FROM t;
>
1|2012-11-02
>
2|2012-11-02 14:10:15
>
3|2012-11-02 14:10:15
>
4|2012-11-02
>
>
Perhaps the documentation needs to be better?  Apparently you couldn't find 
this info...
Indeed, I was never aware of CURRENT_*.

Anyway, all these functions return current moment as _string_ and this is not a 
great way to store datetime in db, isn't it?
- more memory occupied;
- slower compare;
- cannot add & substract;
etc.




>
>
Michael D. Black
>
Senior Scientist
>
Advanced Analytics Directorate
>
Advanced GEOINT Solutions Operating Unit
>
Northrop Grumman Information Systems
>
>

>
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
>
Sent: Friday, November 02, 2012 8:08 AM
>
To: General Discussion of SQLite Database
>
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"
>
>
Thu, 1 Nov 2012 19:57:42 + от Simon Slavin <slav...@bigfraud.org>:
>
>
>
>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко <grigore...@mail.ru> wrote:
>
>
>
>
>
> it is a common practice to store datetime values as UNIX time UTC.
>
>
>
> Maybe, Sqlite should have some shortcut for evaluating current moment?
>
>
>
>
>
Please read
>
>
>
>
>
<http://www.sqlite.org/lang_datefunc.html>
>
>
>
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
functions that modify or format date values.
>
>
It's about having useful shortcut for getting current moment that doesn't have 
(string) parameters and so can be easily remembered and typed.
>
>
Compare:
>
MS SQL: CURRENT_TIMESTAMP
>
PostgreSQL: now()
>
Oracle: sysdate
>
>
To:
>
Sqlite: strftime('%s','now')
>
>
>
>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] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Fri, 2 Nov 2012 10:25:18 -0400 от Richard Hipp :

>
>
>On Fri, Nov 2, 2012 at 10:18 AM, Simon Davies  
>wrote:
>

datetime() will give current date and time
>>
likewise date(), time() etc
>>
>If I read the original post correctly, I think the OP is requesting a new 
>function, "now()", that returns the number of seconds since 1970.  This would 
>be the same as "CAST(strftime('%s','now') AS INTEGER)", just easier to 
>remember.
Correct!


>
>By coincidence, such a function has already been added to the SQLite 
>implementation inside of Fossil.  See:
>
>http://www.fossil-scm.org/fossil/artifact/b743628236c?ln=669-679
>http://www.fossil-scm.org/fossil/artifact/b743628236c?ln=718
>
>Perhaps the OP can simply copy the code above into his own application?
>
I'm using Delphi with sqlite3.dll, maybe I should introduce my own function.






>
>-- 
>D. Richard Hipp
>d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Fri, 02 Nov 2012 15:32:44 +0100 от Clemens Ladisch :
>Igor Tandetnik wrote:
>
> SQLite does in fact accept CURRENT_TIMESTAMP in DEFAULT clause.
>
>
>
> http://sqlite.org/lang_createtable.html#tablecoldef
>
>
SQLite does in fact accept CURRENT_TIMESTAMP anywhere:
>http://www.sqlite.org/lang_expr.html
>
>
Yes, indeed. Perhaps, this should be 
on http://www.sqlite.org/lang_datefunc.html page.





>
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

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Clemens Ladisch]

Igor Tandetnik wrote:
> SQLite does in fact accept CURRENT_TIMESTAMP in DEFAULT clause.
>
> http://sqlite.org/lang_createtable.html#tablecoldef

SQLite does in fact accept CURRENT_TIMESTAMP anywhere:
http://www.sqlite.org/lang_expr.html


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

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Simon Davies]

On 2 November 2012 13:08, Григорий Григоренко  wrote:
>
> Thu, 1 Nov 2012 19:57:42 + от Simon Slavin :
>
>>
> Please read
>>
>>
> 
>>
> Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
> functions that modify or format date values.
>
> It's about having useful shortcut for getting current moment that doesn't 
> have (string) parameters and so can be easily remembered and typed.
>
> Compare:
> MS SQL: CURRENT_TIMESTAMP
> PostgreSQL: now()
> Oracle: sysdate
>
> To:
> Sqlite: strftime('%s','now')

datetime() will give current date and time
likewise date(), time() etc

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

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Black, Michael (IS)]

CREATE TABLE t(id,time);
INSERT INTO t VALUES(1,CURRENT_DATE);
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
INSERT INTO t VALUES(3,datetime('now'));
INSERT INTO t VALUES(4,date('now'));
SELECT * FROM t;
1|2012-11-02
2|2012-11-02 14:10:15
3|2012-11-02 14:10:15
4|2012-11-02

Perhaps the documentation needs to be better?  Apparently you couldn't find 
this info...

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Friday, November 02, 2012 8:08 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"

Thu, 1 Nov 2012 19:57:42 + от Simon Slavin <slav...@bigfraud.org>:

>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко <grigore...@mail.ru> wrote:
>
>
> it is a common practice to store datetime values as UNIX time UTC.
>
> Maybe, Sqlite should have some shortcut for evaluating current moment?
>
>
Please read
>
>
<http://www.sqlite.org/lang_datefunc.html>
>
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
functions that modify or format date values.

It's about having useful shortcut for getting current moment that doesn't have 
(string) parameters and so can be easily remembered and typed.

Compare:
MS SQL: CURRENT_TIMESTAMP
PostgreSQL: now()
Oracle: sysdate

To:
Sqlite: strftime('%s','now')


>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] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Igor Tandetnik]

Григорий Григоренко  wrote:
> Compare:
> MS SQL: CURRENT_TIMESTAMP

SQLite does in fact accept CURRENT_TIMESTAMP in DEFAULT clause. Does this 
satisfy your requirements?

http://sqlite.org/lang_createtable.html#tablecoldef

-- 
Igor Tandetnik

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

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Thu, 1 Nov 2012 19:57:42 + от Simon Slavin :

>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко  wrote:
>
>
> it is a common practice to store datetime values as UNIX time UTC. 
>
> Maybe, Sqlite should have some shortcut for evaluating current moment?
>
>
Please read
>
>

>
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
functions that modify or format date values.  

It's about having useful shortcut for getting current moment that doesn't have 
(string) parameters and so can be easily remembered and typed.

Compare:
MS SQL: CURRENT_TIMESTAMP
PostgreSQL: now()
Oracle: sysdate

To:
Sqlite: strftime('%s','now')


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

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Simon Slavin]

On 1 Nov 2012, at 7:55pm, Григорий Григоренко  wrote:

> it is a common practice to store datetime values as UNIX time UTC. 
> Maybe, Sqlite should have some shortcut for evaluating current moment?

Please read



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

[sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Hello,

it is a common practice to store datetime values as UNIX time UTC. 
Maybe, Sqlite should have some shortcut for evaluating current moment?
Some alias for strftime('%s','now') ? Like, "now" or "unixnow":

  created_at   DEFAULT  (now())     vs     created_at    DEFAULT  
(strftime('%s','now'))

It is shorter and more important - it can be written without consulting docs 
for strfime() , I cannot remember parameters  (


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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Igor Tandetnik]

On 4/23/2012 2:34 PM, Pete wrote:

Folks,
I'm a bit late to this discussion but what are the new PRAGMAs referred to
here?


They were discussed hypothetically. As in, wouldn't it be nice if there 
were pragmas to control date and time formats.

--
Igor Tandetnik

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

[sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Pete]

Folks,
I'm a bit late to this discussion but what are the new PRAGMAs referred to
here?  I don't see them listed in the documentation - do they exist or are
they a suggestion for future implementation?

On Tue, Apr 3, 2012 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:

> Message: 2
> Date: Mon, 2 Apr 2012 20:38:37 +0400
> From: Alexey Pechnikov <pechni...@mobigroup.ru>
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Suggestion about hard-coded time string format
>-MM-DD
> Message-ID:
>

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Yves Goergen]

On 02.04.2012 18:38 CE(S)T, Alexey Pechnikov wrote:
> Why we can't control this? As example, in Russia the date format is
> DD.MM. and is needed the patch
> http://sqlite.mobigroup.ru/fdiff?v1=288ad2e1e017565c=720cb1015e95af7a
> 
> I think the new pragmas DATEFORMAT and TIMEFORMAT will be helpful for
> internationalization. These may be used for parsing and formatting dates.

So is there an SQLite feature to accept floating point numbers with a
decimal comma instead of the English (and programming language standard)
point? After all, why doesn't SQLite accept a Russian translation of all
those SQL commands? Not even considering the code page issues (see
Unicode comments above)...

I also agree that a database should just stick to standard
representation of data, not to user-specific or local.

The time-to-string function is useful for selecting and grouping. You
can group by a day of all months, for example. But the best knowledge
about interpreting a local date representation is surely still in your
application, not in any database system.

-- 
Yves Goergen "LonelyPixel" 
Visit my web laboratory at http://beta.unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

2012/4/2 Jay A. Kreibich 

>  It would make more sense to just implement a strptime() SQL function
>  to compliment the existing strftime() function.  That would allow
>  SQLite to understand and convert any incoming date-time format
>  without depending on specific build parameters.
>
>
But may strptime() function returns datetime in format '%Y-%m-%d
%H:%M:%S'?.. It's produce some overhead by expensive (string parsing)
operations.

select strptime('%d.%m.%Y %H:%M:%S', '12.11.2001 18:31:01');
2001-11-12 18:31:01
select strftime('%s', strptime('%d.%m.%Y %H:%M:%S', '12.11.2001 18:31:01'));
1005589861


-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/04/12 14:34, Simon Slavin wrote:
> I wonder whether it respects languages.

A German user living in France has some Swedish names in their list to be
sorted.  Do you use Swedish sort order, German sort order, German name
order or French order?

There is a Unicode Collation Algorithm that tries to help:

  http://unicode.org/reports/tr10/

Something you may want to consider is to transliterate the text into the
closest ASCII sounds and then sort on that.  It will help when you have
multiple languages represented, some text missing accents etc.

If you search for "unidecode" you'll find a toolkit for doing just that in
various languages.  The original was written by Sean Burke and the
thinking is documented here:

  http://interglacial.com/~sburke/tpj/as_html/tpj22.html

That site appears dead so the wayback machine helps:


http://web.archive.org/web/20091014161106/http://interglacial.com/~sburke/tpj/as_html/tpj22.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk96ODwACgkQmOOfHg372QTOOgCZAQ95J0sYZZYU5LkmxA2+WtoV
8VQAoMy1FH/wiuaekSIdyspXIGt1pc+1
=ehka
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Nico Williams]

On Mon, Apr 2, 2012 at 5:46 PM, Simon Slavin  wrote:
> Replace part of that routine with something which specifies the locale rather 
> than fetching it from OS settings.  And store the locale used with the index, 
> as a COLLATE setting.  Thus leaving it up to whoever writes the CREATE 
> command to decide which locale was used.  I find that acceptable.  This does 
> still give you the problem Jean-Christophe noted of sorting multilanguage 
> lists of names, but that's inherent in Unicode.  Encountering the problem 
> just means you're implementing Unicode properly.

If only it were that easy.  A plain C locale (i.e., byte-wise)
collation will result in "encountering the problem", but you won't be
"implementing Unicode properly", you won't be implementing it at all!

Even if you use some Unicode collation, if you don't handle
normalization insensitivity then you're not really doing it right
either.

Consider that HFS+ on MacOS X always normalizes to NFD on
file/directory create.  But all user input methods I've seen to date
produce NFC for all Latin-* characters!  This means that if someone
does a cut-n-paste of filenames from an HFS+ filesystem then there
will be a very difficult-to-detect conflict.

Unicode is hard.  I want a doll that says that.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 10:44pm, Igor Tandetnik  wrote:

> On 4/2/2012 5:34 PM, Simon Slavin wrote:
>> Hmm.  It's there:
>> 
>> 
>> 
>> "The following function compares two Unicode strings of possibly different 
>> lengths.
>> 
>> — Function: int u8_cmp2 (const uint8_t *s1, size_t n1, const uint8_t *s2, 
>> size_t n2)
>> — Function: int u16_cmp2 (const uint16_t *s1, size_t n1, const uint16_t *s2, 
>> size_t n2)
>> — Function: int u32_cmp2 (const uint32_t *s1, size_t n1, const uint32_t *s2, 
>> size_t n2)
>> Compares s1 and s2, lexicographically. Returns a negative value if s1 
>> compares smaller than s2, a positive value if s1 compares larger than s2, or 
>> 0 if they compare equal."
>> 
>> I wonder whether it respects languages.
> 
> These don't,

You know, I don't care that much.  Unicode sorting even without languages would 
be a nice plugin for SQLite3, if that makes things so much simpler.

> but u8_strcoll et al supposedly do, based on LC_COLLATE locale category. 
> Herein lies the problem: if you build an index using these functions while 
> running under locale A, then try to run queries against this database in an 
> application running with locale B, bad things happen. From the point of view 
> of the second application, the index is corrupted.

Replace part of that routine with something which specifies the locale rather 
than fetching it from OS settings.  And store the locale used with the index, 
as a COLLATE setting.  Thus leaving it up to whoever writes the CREATE command 
to decide which locale was used.  I find that acceptable.  This does still give 
you the problem Jean-Christophe noted of sorting multilanguage lists of names, 
but that's inherent in Unicode.  Encountering the problem just means you're 
implementing Unicode properly.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Nico Williams]

On Mon, Apr 2, 2012 at 1:25 PM, Richard Hipp  wrote:
> On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin  wrote:
>> I think ... a higher priority than that would be handling Unicode
>> correctly.  And having Unicode support would be useful in writing the code
>> which handles dates.
>>
> size of SQLite library:  approx 500 KB
> size of ICU library: approx 21,919 KB
>
> The ICU library (needed to handle Unicode "correctly") is over 40x larger
> than SQLite.  Can you understand then why we don't want to make SQLite
> dependent upon ICU?
>
> If you really need correct ICU support, SQLite will optionally link with
> ICU and use it.  But *requiring* SQLite to link against ICU is a
> deal-breaker for many users.

Also, Unicode collation is typically orders of magnitude slower than
US-ASCII collation.  This comes up a lot in other contexts,
particularly as the various OSes have begun defaulting to Unicode
locales.  I've seen ls(1) of directories with millions of files run as
fast as the output device permits when run in the C locale (in less
than 1 second when tmpfs), but take many minutes when in a UTF-8
locale, and that's without any use of normalization.  But mostly this
is a result of Unicode collation in libc being awful.

The OpenSolaris u8_textprep code is designed to make u8_str*cmp()
really fast, though not quite as fast as the C locale strcmp(), when
strings are mostly ASCII and even when they are not because
u8_textprep does no memory allocation for normalization-insensitive
comparison and has a fast-path for comparing substrings of two or more
ASCII codepoints.  This is the main reason that I'd recommend
u8_textprep...

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Nico Williams]

On Mon, Apr 2, 2012 at 4:24 PM, Simon Slavin  wrote:
> On 2 Apr 2012, at 9:58pm, Alexey Pechnikov  wrote:
>> Description: Unicode string library for C
>> The 'libunistring' library implements Unicode strings (in the UTF-8,
>> UTF-16, and UTF-32 encodings), together with functions for
>> Unicode characters (character names, classifications, properties) and
>> functions for string processing (formatted output, width, word
>> breaks, line breaks, normalization, case folding, regular expressions).
>
> Trying to figure out what SQLite would want from Unicode characters I don't 
> end up with any of those.  I think all it wants is sorting, so SQLite can 
> make an index properly.  And I don't really care whether it's case-sensitive 
> or not since my software can do case conversion on input.  Because they're in 
> standard functions, string length and substring substitution would be nice 
> but I can live without them working properly.

SQLite3 needs:

 - string comparison with normalization-insensitivity, unless
   SQLite3 were to normalize TEXT values on INSERT/UPDATE,
   but I don't recommend that, except that for indexes it's
   required; see below

 - string comparison with case-insensitivity as an option (for LIKE)

 - string normalization and case-folding functions, which are
   needed for computing index key prefixes for LIKE and GLOB
   patterns that use globbing, so that the index cursors can be
   positioned correctly

 - preferably a way to specify a collation for Unicode (i.e., a
   language, since collation rules may vary by language)

 - preferably a way to specify not to use locale environment
   variables (see Igor's comments)

 - functionality needed to implement SQLite3's built-in string functions
- i.e., trim(), ltrim(), rtrim(), replace(), substr(), lower(),
upper(), min(), max(), and length()

Incidentally, length() claims to return a count of characters, but it
actually counts *codepoints*.  Counting characters is a lot harder
than counting codepoints...  Codepoint counting in UTF-* is trivial;
character counting requires tables of combining codepoint ranges and
code to skip combining codepoints.  Counting graphemes is harder
still.  Getting these things right is non-trivial.  Ideally there
would be an option to the length() function to request counts of
different possible things: UTF-8 units (bytes), UTF-16 units,
codepoints, characters, glyphs, and graphemes, though just stopping at
characters would do.

Similar comments apply to string indices in functions like substr()!

In practice one should want to count characters when dealing with
sub-string operations, but storage units when dealing with
transmission.  Using codepoint counts in substr() risks breaking
combining codepoint sequences and thus producing garbage.

I think the OpenSolaris u8_textprep code is good enough for the
collation requirements, but it probably isn't sufficient for the
SQLite3 string functions, but I'd have to look carefully.  I suspect
that ICU and libunistring meet all the requirements.

> One problem is that, as someone explained to me last year, sorting of unicode 
> characters depends on which language you're using (and other things if you're 
> fussy).  So for every index you make you'd have to declare the language, and 
> SQLite would have to store it.

SQLite3 allows you to specify collations though, so that's not that
big a deal.  For a web application, say, it's very difficult to
implement sorting that satisfies all possible users because indexes
can't provide a globally sufficient collation, not unless you were
willing to have a multitude of indexes.  Sorting, then, has to be done
on result sets -- that is, without the benefit of indexes in most
cases, which means it will be slow for any queries that return large
row sets.

In practice though this is not that big a deal.  And there will be a
tendency to simplify collations.  For example the Royal Spanish
Academy no longer requires that 'ch' sort after 'c', nor that 'll'
sort after 'l' [*].  I suspect most users won't really care, but
whether they do will depend on the application and the user.

> I was trying to figure out whether SQLite could make use of the OS's unicode 
> library (using different compilation directives for each platform which 
> supports unicode) but I'm only really familiar with the Mac operating system 
> and I don't know how Windows or Linux does these things.

There's no standard C libraries that deal with Unicode in sufficient
detail.  In particular the wchar_t functions are useless for the
purposes of SQLite3 because they try to hide too much detail, and
because in some cases they attempt to hide even the codeset.

[*] http://servicios.larioja.com/romanpaladino/g02.htm claims that the
Academy changed this in 1994, and that people started noticing this in
phone books in 1996, and that they complained.

http://es.wikipedia.org/wiki/Ortograf%C3%ADa_del_espa%C3%B1ol goes
into 

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Jean-Christophe Deschamps]

At 23:44 02/04/2012, you wrote:


I wonder whether it respects languages.


These don't, but u8_strcoll et al supposedly do, based on LC_COLLATE 
locale category. Herein lies the problem: if you build an index using 
these functions while running under locale A, then try to run queries 
against this database in an application running with locale B, bad 
things happen. From the point of view of the second application, the 
index is corrupted.


That is: the collation used for this exact purpose becomes a strong 
metadata of the table but there's no easy way to deal with that.


Not only, but say you have a table of worldwide customers (I do have 
clients in 39 countries today), which exact locale are you going to 
use?  I know this question has no answer (and that's the main grief I 
have with Unicode).


A workable perspective is to come up with a "decent if not perfect" way 
to deal with unaccenting and getting rid of the locale concept.


For those ready to cut some corners and to give a rough idea of what 
can be done easily if you're ready to live with some compromises (since 
*-no-* perfect solution exists), my C shared library implementing a 
large number of string and misc. functions (with both UTF-8 & UTF-16 
interfaces) dealing with a weak form of "unaccented Unicode v5.1" is 
currently a 143Kb Win x86 DLL and runs reasonably fast.


JcD 


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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Igor Tandetnik]

On 4/2/2012 5:34 PM, Simon Slavin wrote:

Hmm.  It's there:



"The following function compares two Unicode strings of possibly different 
lengths.

— Function: int u8_cmp2 (const uint8_t *s1, size_t n1, const uint8_t *s2, 
size_t n2)
— Function: int u16_cmp2 (const uint16_t *s1, size_t n1, const uint16_t *s2, 
size_t n2)
— Function: int u32_cmp2 (const uint32_t *s1, size_t n1, const uint32_t *s2, 
size_t n2)
Compares s1 and s2, lexicographically. Returns a negative value if s1 compares 
smaller than s2, a positive value if s1 compares larger than s2, or 0 if they 
compare equal."

I wonder whether it respects languages.


These don't, but u8_strcoll et al supposedly do, based on LC_COLLATE 
locale category. Herein lies the problem: if you build an index using 
these functions while running under locale A, then try to run queries 
against this database in an application running with locale B, bad 
things happen. From the point of view of the second application, the 
index is corrupted.

--
Igor Tandetnik

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 10:24pm, Simon Slavin  wrote:

> On 2 Apr 2012, at 9:58pm, Alexey Pechnikov  wrote:
> 
>> Description: Unicode string library for C
>> The 'libunistring' library implements Unicode strings (in the UTF-8,
>> UTF-16, and UTF-32 encodings), together with functions for
>> Unicode characters (character names, classifications, properties) and
>> functions for string processing (formatted output, width, word
>> breaks, line breaks, normalization, case folding, regular expressions).
>> 
>> This package contains the shared library.
>> Homepage: http://www.gnu.org/software/libunistring/
> 
> Trying to figure out what SQLite would want from Unicode characters I don't 
> end up with any of those.  I think all it wants is sorting, so SQLite can 
> make an index properly.

Hmm.  It's there:



"The following function compares two Unicode strings of possibly different 
lengths.

— Function: int u8_cmp2 (const uint8_t *s1, size_t n1, const uint8_t *s2, 
size_t n2)
— Function: int u16_cmp2 (const uint16_t *s1, size_t n1, const uint16_t *s2, 
size_t n2)
— Function: int u32_cmp2 (const uint32_t *s1, size_t n1, const uint32_t *s2, 
size_t n2)
Compares s1 and s2, lexicographically. Returns a negative value if s1 compares 
smaller than s2, a positive value if s1 compares larger than s2, or 0 if they 
compare equal."

I wonder whether it respects languages.  I don't think so, but I can live 
without it.  Time to hack up an external function for SQLite and see how little 
of the library I need to make it work.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 9:58pm, Alexey Pechnikov  wrote:

> Description: Unicode string library for C
> The 'libunistring' library implements Unicode strings (in the UTF-8,
> UTF-16, and UTF-32 encodings), together with functions for
> Unicode characters (character names, classifications, properties) and
> functions for string processing (formatted output, width, word
> breaks, line breaks, normalization, case folding, regular expressions).

Trying to figure out what SQLite would want from Unicode characters I don't end 
up with any of those.  I think all it wants is sorting, so SQLite can make an 
index properly.  And I don't really care whether it's case-sensitive or not 
since my software can do case conversion on input.  Because they're in standard 
functions, string length and substring substitution would be nice but I can 
live without them working properly.

One problem is that, as someone explained to me last year, sorting of unicode 
characters depends on which language you're using (and other things if you're 
fussy).  So for every index you make you'd have to declare the language, and 
SQLite would have to store it.

I was trying to figure out whether SQLite could make use of the OS's unicode 
library (using different compilation directives for each platform which 
supports unicode) but I'm only really familiar with the Mac operating system 
and I don't know how Windows or Linux does these things.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Nico Williams]

On Mon, Apr 2, 2012 at 3:58 PM, Alexey Pechnikov  wrote:
> And see too:
>
> Homepage: http://www.gnu.org/software/libunistring/

Thanks!  That looks like a dream come true (even if LGPL).  It's much
more complete than the OpenSolaris u8_textprep stuff, which would be
particularly useful for SQLite3 string functions.  But for just
collations the u8_textprep stuff is good enough, and probably much
smaller (though I've not yet examined the size of libunistring).

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

And see too:

# LANG=C aptitude show libunistring0
Package: libunistring0
New: yes
State: not installed
Version: 0.9.3-3
Priority: optional
Section: libs
Maintainer: Andreas Rottmann 
Uncompressed Size: 1221 k
Depends: libc6 (>= 2.3)
Description: Unicode string library for C
 The 'libunistring' library implements Unicode strings (in the UTF-8,
UTF-16, and UTF-32 encodings), together with functions for
 Unicode characters (character names, classifications, properties) and
functions for string processing (formatted output, width, word
 breaks, line breaks, normalization, case folding, regular expressions).

 This package contains the shared library.
Homepage: http://www.gnu.org/software/libunistring/


2012/4/2 Nico Williams 

> On Mon, Apr 2, 2012 at 1:25 PM, Richard Hipp  wrote:
> > On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin 
> wrote:
> >> I think ... a higher priority than that would be handling Unicode
> >> correctly.  And having Unicode support would be useful in writing the
> code
> >> which handles dates.
> >>
> >>
> > size of SQLite library:  approx 500 KB
> > size of ICU library: approx 21,919 KB
> >
> > The ICU library (needed to handle Unicode "correctly") is over 40x larger
> > than SQLite.  Can you understand then why we don't want to make SQLite
> > dependent upon ICU?
>
> I completely agree.  It'd be nice if SQLite3 could have an option for
> a weak dependency on ICU.  I.e., if it can be found with dlopen(),
> then use it, else not; a pragma could be used by applications to check
> whether SQLite3 found ICU, or to require that it always be found for
> any given DB file.  Along these lines it'd be nice if a DB file could
> record required loadable extensions and provide a single pragma to
> load them all, with errors returned when compiling or running
> statements other than pragmas until the required extensions are
> loaded.
>
> > If you really need correct ICU support, SQLite will optionally link with
> > ICU and use it.  But *requiring* SQLite to link against ICU is a
> > deal-breaker for many users.
>
> FYI, the OpenSolaris Unicode library is significantly smaller, most
> likely because it deals only with normalization, case conversion, and
> codepoint prohibitions -- everything needed for "stringprep" and
> normalization- and case-insensitive string comparison.  The data
> tables reside in a header file and are 1.8 MB.  The code is 55KB, +
> 3KB of headers.  The license for this is CDDL (a per-file copyleft, as
> opposed to project-wide copyleft), which is not quite as free as ICU's
> license (which is essentially a two-clause BSD license), but probably
> good enough for most projects that use SQLite3.  FreeBSD, for example,
> includes u8_textprep, probably because it also includes ZFS (which
> depends on u8_textprep).  This is still many times larger than
> SQLite3, but still more than ten times smaller than ICU.
>
>
> http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/common/unicode/
>
> http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/uts/common/sys/
> (scroll down to u8_textprep.h and u8_textprep_data.h)
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Nico Williams]

On Mon, Apr 2, 2012 at 1:25 PM, Richard Hipp  wrote:
> On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin  wrote:
>> I think ... a higher priority than that would be handling Unicode
>> correctly.  And having Unicode support would be useful in writing the code
>> which handles dates.
>>
>>
> size of SQLite library:  approx 500 KB
> size of ICU library: approx 21,919 KB
>
> The ICU library (needed to handle Unicode "correctly") is over 40x larger
> than SQLite.  Can you understand then why we don't want to make SQLite
> dependent upon ICU?

I completely agree.  It'd be nice if SQLite3 could have an option for
a weak dependency on ICU.  I.e., if it can be found with dlopen(),
then use it, else not; a pragma could be used by applications to check
whether SQLite3 found ICU, or to require that it always be found for
any given DB file.  Along these lines it'd be nice if a DB file could
record required loadable extensions and provide a single pragma to
load them all, with errors returned when compiling or running
statements other than pragmas until the required extensions are
loaded.

> If you really need correct ICU support, SQLite will optionally link with
> ICU and use it.  But *requiring* SQLite to link against ICU is a
> deal-breaker for many users.

FYI, the OpenSolaris Unicode library is significantly smaller, most
likely because it deals only with normalization, case conversion, and
codepoint prohibitions -- everything needed for "stringprep" and
normalization- and case-insensitive string comparison.  The data
tables reside in a header file and are 1.8 MB.  The code is 55KB, +
3KB of headers.  The license for this is CDDL (a per-file copyleft, as
opposed to project-wide copyleft), which is not quite as free as ICU's
license (which is essentially a two-clause BSD license), but probably
good enough for most projects that use SQLite3.  FreeBSD, for example,
includes u8_textprep, probably because it also includes ZFS (which
depends on u8_textprep).  This is still many times larger than
SQLite3, but still more than ten times smaller than ICU.

http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/common/unicode/
http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/uts/common/sys/
(scroll down to u8_textprep.h and u8_textprep_data.h)

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 7:25pm, Richard Hipp  wrote:

> On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin  wrote:
> 
>> I think ... a higher priority than that would be handling Unicode
>> correctly.  And having Unicode support would be useful in writing the code
>> which handles dates.
> 
> size of SQLite library:  approx 500 KB
> size of ICU library: approx 21,919 KB
> 
> The ICU library (needed to handle Unicode "correctly") is over 40x larger
> than SQLite.  Can you understand then why we don't want to make SQLite
> dependent upon ICU?

Yep.  That's why you don't do it.  And handling other things to make data 
human-readable using Unicode characters would be wrong too.

Simon.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

Why you need ICU library to parse datetime strings?!! The my previous patch
to parse Russian dates has only single row without any external libs.

2012/4/2 Richard Hipp 

> On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin  wrote:
>
> >
> > I think ... a higher priority than that would be handling Unicode
> > correctly.  And having Unicode support would be useful in writing the
> code
> > which handles dates.
> >
> >
> size of SQLite library:  approx 500 KB
> size of ICU library: approx 21,919 KB
>
> The ICU library (needed to handle Unicode "correctly") is over 40x larger
> than SQLite.  Can you understand then why we don't want to make SQLite
> dependent upon ICU?
>
> If you really need correct ICU support, SQLite will optionally link with
> ICU and use it.  But *requiring* SQLite to link against ICU is a
> deal-breaker for many users.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Richard Hipp]

On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin  wrote:

>
> I think ... a higher priority than that would be handling Unicode
> correctly.  And having Unicode support would be useful in writing the code
> which handles dates.
>
>
size of SQLite library:  approx 500 KB
size of ICU library: approx 21,919 KB

The ICU library (needed to handle Unicode "correctly") is over 40x larger
than SQLite.  Can you understand then why we don't want to make SQLite
dependent upon ICU?

If you really need correct ICU support, SQLite will optionally link with
ICU and use it.  But *requiring* SQLite to link against ICU is a
deal-breaker for many users.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 6:18pm, Alexey Pechnikov  wrote:

> Please see http://www.sqlite.org/lang_datefunc.html and you will be
> frustrated because SQLite can format date/time to different string formats
> but can't read the produced date/time strings.

I am not frustrated.  That function does not require any understanding of 
locales, timezones, or Summer Time.  It doesn't 'understand' dates at all, it's 
just a version of sprintf() useful for dates.  Easy to write, easy to test.

Turning dates into arbitrary strings is easy.  Adjusting for time zones is 
difficult.  Analyzing a string to turn it into a date is difficult (some 
strings are ambiguous), and may require support for Unicode which is very 
difficult indeed.  This is a complicated thing and putting such functions on 
tiny devices like a GPS Unit would be a big waste.

If you want external functions, SQLite supplies very nice facilities which let 
you plug them in.  No need to patch the source code.  So you can write your 
functions to convert your dates however you want, with whatever understanding 
of locales you want.

I think a future version of SQLite might handle dates.  But a higher priority 
than that would be handling Unicode correctly.  And having Unicode support 
would be useful in writing the code which handles dates.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

> 2012/4/2 Simon Slavin 

Please see http://www.sqlite.org/lang_datefunc.html and you will be
frustrated because SQLite can format date/time to different string formats
but can't read the produced date/time strings.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Jay A. Kreibich]

On Mon, Apr 02, 2012 at 08:38:37PM +0400, Alexey Pechnikov scratched on the 
wall:
> Why we can't control this? As example, in Russia the date format is
> DD.MM. and is needed the patch
> http://sqlite.mobigroup.ru/fdiff?v1=288ad2e1e017565c=720cb1015e95af7a
> 
> I think the new pragmas DATEFORMAT and TIMEFORMAT will be helpful for
> internationalization. These may be used for parsing and formatting dates.

  It would make more sense to just implement a strptime() SQL function
  to compliment the existing strftime() function.  That would allow
  SQLite to understand and convert any incoming date-time format
  without depending on specific build parameters.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 5:38pm, Alexey Pechnikov  wrote:

> Why we can't control this? As example, in Russia the date format is
> DD.MM. and is needed the patch
> http://sqlite.mobigroup.ru/fdiff?v1=288ad2e1e017565c=720cb1015e95af7a
> 
> I think the new pragmas DATEFORMAT and TIMEFORMAT will be helpful for
> internationalization. These may be used for parsing and formatting dates.

I take the view that parsing and formatting data should be done by your 
software.  Your software must be aware of time zones, Summer time adjustments, 
and whether you want your months numbered or spelled.  It needs to deal with 
people entering gibberish as a date.  If needs to know, if a date was entered 
by a user in Russia, and printed by a user in Germany, whether it has to be 
adjusted for local time.

SQLite is a database system.  It's used for /storing/ information.  And that 
has nothing to do with what language you speak.  A date is a date, a time is a 
time, and you can be storing time as UTC or in your local timezone.

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

[sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

Why we can't control this? As example, in Russia the date format is
DD.MM. and is needed the patch
http://sqlite.mobigroup.ru/fdiff?v1=288ad2e1e017565c=720cb1015e95af7a

I think the new pragmas DATEFORMAT and TIMEFORMAT will be helpful for
internationalization. These may be used for parsing and formatting dates.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion for project: Chat

[Alan Chandler]

On 24/08/10 17:21, Artur Reilin wrote:
> I thinking about coding a chat with sqlite. You know the most hosts not
> allow you to use chats or something that way, because they write and read
> to often data from mysql database.
>
> So my thought is to make a chat which use forums software mysql tables for
> user data, but writes and read chat text from sqlite database. As the chat
> content get pruned after some time, the database can't grow big. So the
> insert and selects should be fast.
>


I have already made a chat that uses SQLite.  Its available to clone 
from my git repository (licenced under the GPL).

See http://www.chandlerfamily.org.uk/content/software

To clone the repository you need

git clone git://www.chandlerfamily.org.uk/mbchat.git

This originally started life as an mysql database version, but using it 
on the web site I wrote it for (http://www.melindasbackups.com) it ran 
out of steam when 20 people were using it (as it had to poll the 
database every 2 seconds per user to get a sensible performance).

I went through some intermediate stage of using named pipes and sqlite 
before landing on the current version that forks a chat server for the 
time that anyone is in chat.  I have also added optional security 
controls so that its possible to encrypt chat messages.

It has multiple rooms which are added via adding them to the database, 
and various permissions and colours for users. (users see some rooms as 
public, others are moderated, and others they see only when they are 
members of a specific group - we used them as committee rooms).

There is a useable demo here

http://chat.hartley-consultants.com/

which is stand alone.  The code can also link to an SMF forum and derive 
username from the login identity of the user on the forum.



-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Suggestion for project: Chat

[Artur Reilin]

> On Tue, Aug 24, 2010 at 10:23:55PM +0530, Roger Binns wrote:
>> On 08/24/2010 09:51 PM, Artur Reilin wrote:
>> > I thinking about coding a chat with sqlite.
>>
>> The hard part of implementing chat is not storing the messages, but
>> rather dealing with all the clients at the same time especially with
>> potentially hostile networking (eg reading data slower than you send
>> it).
>>
>> If you want to write one yourself I suggest looking into the Erlang
>> programming language.
>>
>> Instead of reinventing the wheel, I suggest you use the open XMPP chat
>> protocol and open source XMPP servers.  In addition to using any XMPP
>> client, you can also use a web based client.
>
> +1.
>
> Moreover, because often you'll want to log chats hosted on services that
> you do not control, the simplest, most popular, and most interoperable
> (though not most reliable) way to log is to do it via "bots".  "Bots"
> are automated clients.
>
> The most reliable way to log chats is by modifying the service
> implementation or by co-locating logger bots with the service, but this
> only works when you own the service (or otherwise can get the owners to
> log this way).
>
> Don't reinvent the wheel, just find a suitable open source bot and
> modify it to suit your needs.  You might even find some existing ones.
> For example, the "Sqlite Users" wiki page [0] lists one exact match,
> though it appears to be dead.
>
> Just search for "chat bot logger sqlite", "xmpp bot logger sqlite",
> etcetera, and you'll find plenty.  I count at least a handful just on
> the first page of results for each of those searches.
>
> Nico
>
> [0] http://www.sqlite.org/cvstrac/wiki?p=SqliteUsers
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

The thing is, I don't want to log the chat text. It should just get
removed after 30 messages or so. It should just save the chat messages for
some time. I currently using a Ajax based chat in my phpBB3 installation.
Just thought it would reduce the server <-> mysql server data traffic.

I have paid hosting, so I don't think that I'll get many problems. But the
thing is, that I see sometimes that the chat's are more html based and
often really slow. So slow, that even sqlite could take that with 20 users
at once or even more.

with best wishes

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

Re: [sqlite] Suggestion for project: Chat

[Nicolas Williams]

On Tue, Aug 24, 2010 at 10:23:55PM +0530, Roger Binns wrote:
> On 08/24/2010 09:51 PM, Artur Reilin wrote:
> > I thinking about coding a chat with sqlite. 
> 
> The hard part of implementing chat is not storing the messages, but
> rather dealing with all the clients at the same time especially with
> potentially hostile networking (eg reading data slower than you send it).
> 
> If you want to write one yourself I suggest looking into the Erlang
> programming language.
> 
> Instead of reinventing the wheel, I suggest you use the open XMPP chat
> protocol and open source XMPP servers.  In addition to using any XMPP
> client, you can also use a web based client.

+1.

Moreover, because often you'll want to log chats hosted on services that
you do not control, the simplest, most popular, and most interoperable
(though not most reliable) way to log is to do it via "bots".  "Bots"
are automated clients.

The most reliable way to log chats is by modifying the service
implementation or by co-locating logger bots with the service, but this
only works when you own the service (or otherwise can get the owners to
log this way).

Don't reinvent the wheel, just find a suitable open source bot and
modify it to suit your needs.  You might even find some existing ones.
For example, the "Sqlite Users" wiki page [0] lists one exact match,
though it appears to be dead.

Just search for "chat bot logger sqlite", "xmpp bot logger sqlite",
etcetera, and you'll find plenty.  I count at least a handful just on
the first page of results for each of those searches.

Nico

[0] http://www.sqlite.org/cvstrac/wiki?p=SqliteUsers
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion for project: Chat

[Richard Hipp]

FWIW:  The SQLite Core developers all keep in touch using a private,
proprietary chatroom that is backed by an SQLite database.  The chat server
is written in 152 lines of Tcl code and the client (involving a GUI and
therefore being more complex) is 1068 lines of Tcl/Tk.  We have other means
of communication available as a backup (Gmail chat, AOL chat, Skype) but our
private chatroom works so well, that the backups are seldom used.

On Tue, Aug 24, 2010 at 12:21 PM, Artur Reilin  wrote:

> I thinking about coding a chat with sqlite. You know the most hosts not
> allow you to use chats or something that way, because they write and read
> to often data from mysql database.
>
> So my thought is to make a chat which use forums software mysql tables for
> user data, but writes and read chat text from sqlite database. As the chat
> content get pruned after some time, the database can't grow big. So the
> insert and selects should be fast.
>
> But would that work? What do you think?
>
> I'll like to write that for phpBB3 and wbb2 to replace the current chat
> from my forum and also replacings wbb2 stinky chat system xD.
>
> With best wishes
>
> Artur Reilin
> sqlite.yuedream.de
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Suggestion for project: Chat

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/24/2010 09:51 PM, Artur Reilin wrote:
> I thinking about coding a chat with sqlite. 

The hard part of implementing chat is not storing the messages, but
rather dealing with all the clients at the same time especially with
potentially hostile networking (eg reading data slower than you send it).

If you want to write one yourself I suggest looking into the Erlang
programming language.

Instead of reinventing the wheel, I suggest you use the open XMPP chat
protocol and open source XMPP servers.  In addition to using any XMPP
client, you can also use a web based client.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkxz+RgACgkQmOOfHg372QQArwCfSaFfSfzILiZWs6Z0eaAmAzD3
oSoAoL+onPhAEb/FCPDRmIkuuwDt3Sth
=G+i4
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Suggestion for project: Chat

[Artur Reilin]

I thinking about coding a chat with sqlite. You know the most hosts not
allow you to use chats or something that way, because they write and read
to often data from mysql database.

So my thought is to make a chat which use forums software mysql tables for
user data, but writes and read chat text from sqlite database. As the chat
content get pruned after some time, the database can't grow big. So the
insert and selects should be fast.

But would that work? What do you think?

I'll like to write that for phpBB3 and wbb2 to replace the current chat
from my forum and also replacings wbb2 stinky chat system xD.

With best wishes

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

Re: [sqlite] suggestion: bitwise shift right optimization

[Max Vlasov]

>   In the case of SQLite, it is also very unlikely to save space.  In
>  fact, because of the way integer values are stored, it is very
>  likely to use more space.
>

Jay, In most cases yes, but there are ones with several integers that should
be used together in ordering, the space is wasted since sqlite saves the
table b-tree with the rowid + these integers and also index b-tree with the
integers and rowid reference. When such tables contains thousands or
millions records, it starts making sense. Using rowid in this case as a
packed value should help.

Ironically sqlite being db right before you with all the statistics
available encourages experimenting opposite to for example mysql looking
like a mountain far away )

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

Re: [sqlite] suggestion: bitwise shift right optimization

[Jay A. Kreibich]

On Tue, Jul 20, 2010 at 04:43:26PM +0400, Max Vlasov scratched on the wall:
> Hi,
> as long as I see currently bitwise right does not use index

  Doing this requires recognizing when an inverse expression exists,
  and then computing it.  It is extremely difficult in the general
  case.  If you want to use an index, you need to refer to a direct value.

> But the first syntax is more straightforward.

  Having a proper column is even more straightforward.  It also makes
  this whole issue go away and makes your indexes work correctly.

> Btw, actually, sometimes when several small fields exists and they should be
> indexed, one can pack them into id(rowid) to save space and the first syntax
> will allow querying more naturally

  This breaks First Normal Form and is very un-relational.  If you want
  the database to do what it does best, use it properly.  Doing stuff
  like this is thinking like a C programmer, not a database programmer.

  In the case of SQLite, it is also very unlikely to save space.  In
  fact, because of the way integer values are stored, it is very
  likely to use more space.

  It also screams of premature optimization.  In most cases it won't
  save you much, if anything, and will cause all kinds of problems
  (like this!).  You also loose the ability to index anything other
  than the left-most field.  
  
  Finally, bit operations are not part of the SQL standard,
  making this kind of approach very non-portable.  Many databases
  don't even use binary integers to store natural-number values.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] suggestion: bitwise shift right optimization

[Max Vlasov]

On Tue, Jul 20, 2010 at 5:14 PM, Pavel Ivanov  wrote:

> > Are there reasons not to implement optimization in the first case? Except
> > for this is not most requested one :)
>
> I guess because this case is highly specific and it's behavior should
> depend on particular constants used. Put there for example Id >> 54 =
> 1000 and now we should make optimizer guess right away that query will
> never return any rows.
>
>
Pavel, I thought about this a little more and I can see a little problem

For example, if we have an abstract function F, that we can guarantee:
- if a <=b  F(a)<=F(b)
- if a >=b  F(a)>=F(b)
we actually should perform a kind of range search, but less effective than
general range search. General range search knows what are the limits so
search only for them regardless of the rows to be found, but this query
should find any value and after that move left while F() is true and move
right while F() is true.

On the other size this kind of search will have either the same
effectiveness as a full scan (in worst case) or better. I suppose this
limitation is also why the queries with complex left parts (even WHERE id +
1 =) also does not use optimizer (CMIIW)

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

Re: [sqlite] suggestion: bitwise shift right optimization

[Pavel Ivanov]

> Are there reasons not to implement optimization in the first case? Except
> for this is not most requested one :)

I guess because this case is highly specific and it's behavior should
depend on particular constants used. Put there for example Id >> 54 =
1000 and now we should make optimizer guess right away that query will
never return any rows.

> Btw, actually, sometimes when several small fields exists and they should be
> indexed, one can pack them into id(rowid) to save space and the first syntax
> will allow querying more naturally

Only for the field placed in the highest valued bits. Fields in lower
valued bits will have to use '&' operator which can't use index
anyway.


Pavel

On Tue, Jul 20, 2010 at 8:43 AM, Max Vlasov  wrote:
> Hi,
> as long as I see currently bitwise right does not use index
>
> CREATE TABLE [TestTable] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT);
>
> EXPLAIN QUERY PLAN SELECT * FROM TestTable WHERE Id >> 12 = 1000;
> : TABLE TestTable
>
> Sure I can replace it with the following query
>
> EXPLAIN QUERY PLAN SELECT * FROM TestTable WHERE Id BETWEEN 1000 << 12 AND
> (1001 << 12)-1;
> : TABLE TestTable USING PRIMARY KEY
>
> But the first syntax is more straightforward.
> Are there reasons not to implement optimization in the first case? Except
> for this is not most requested one :)
> Btw, actually, sometimes when several small fields exists and they should be
> indexed, one can pack them into id(rowid) to save space and the first syntax
> will allow querying more naturally
>
> Thanks,
>
> Max Vlasov
> maxerist.net
> ___
> 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] suggestion: bitwise shift right optimization

[Max Vlasov]

Hi,
as long as I see currently bitwise right does not use index

CREATE TABLE [TestTable] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT);

EXPLAIN QUERY PLAN SELECT * FROM TestTable WHERE Id >> 12 = 1000;
: TABLE TestTable

Sure I can replace it with the following query

EXPLAIN QUERY PLAN SELECT * FROM TestTable WHERE Id BETWEEN 1000 << 12 AND
(1001 << 12)-1;
: TABLE TestTable USING PRIMARY KEY

But the first syntax is more straightforward.
Are there reasons not to implement optimization in the first case? Except
for this is not most requested one :)
Btw, actually, sometimes when several small fields exists and they should be
indexed, one can pack them into id(rowid) to save space and the first syntax
will allow querying more naturally

Thanks,

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

Re: [sqlite] Suggestion for sqlite shell

[Max Vlasov]

On Mon, Jul 12, 2010 at 5:00 PM, Alexey Pechnikov wrote:

> Maxim, please show example here and the link to your implementation.
>
>
Alexey,

to illustrate I prepared a screen shot

http://www.maxerist.net/downloads/temp/bind_iterate.png

excel and cells to be copied are at the top, at the center the query, the
dialog ask for the user confirmation and the results are below. It's just an
example of complex data manipulation "on the fly" with this feature, the
code itself is very simple as you may see from the input and output. Knowing
that sqlite shell already can parse csv content, I think this feature will
require no more than a couple of hundred lines of code

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

Re: [sqlite] Suggestion for sqlite shell

[Alexey Pechnikov]

Maxim, please show example here and the link to your implementation.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Suggestion for sqlite shell

[Max Vlasov]

Hi,

recently I implemented a feature for an gui admin I use internally, but I
think it would be useful also for sqlite shell. Sorry if it already exists
in some form, I could not find similar.

The idea is to use bind syntax together with csv (or clipboard compatible)
import. So if one enters a query with question marks and other symbols that
allows parameters numbering (but not naming obviously) and also provides csv
file, shell will allow to interate through each row of the file and perform
the query using the row as the parameters set. It will allow not only using
a complex inserts with concatenation and so on but also give the ability to
make mass updates or other conditional operations. There are errors possible
like conflict between expected number of parameters and provided, but this
could be resolved either with returrning an error (% expected, but % found)
or allowing ignoring extra fields with padding.

I don't know whether the clipboard content can be used in sqlite
ideologically, but in my case it saved additional time allowing importing
for example from excel without the intermediate file (csv).

Thanks

Max Vlasov
www.maxerist.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] suggestion - make pragma case_sensitive_like queryable

[Bogdan Ureche]

>
>
> The setting is detectable:
>
>
Simon, thank you for the reply. It's not exactly the answer I was hoping
for, but I suppose fixing this is a low priority if there is a workaround.

Regards,

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

Re: [sqlite] suggestion - make pragma case_sensitive_like queryable

[Simon Davies]

On 22 March 2010 21:53, Bogdan Ureche  wrote:
> The pragma case_sensitive_like is not queryable, unlike other pragmas. Is
> there any reason why it's not?
>
> If not, I would like to suggest to be made queryable, for consistency.
>
> Bogdan

The setting is detectable:

sqlite>
sqlite> select 'a' like 'A';
1
sqlite> pragma case_sensitive_like=1;
sqlite>
sqlite> select 'a' like 'A';
0
sqlite>

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

[sqlite] suggestion - make pragma case_sensitive_like queryable

[Bogdan Ureche]

The pragma case_sensitive_like is not queryable, unlike other pragmas. Is
there any reason why it's not?

If not, I would like to suggest to be made queryable, for consistency.

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

Re: [sqlite] suggestion on the database design

[pierr]

John Machin wrote:
> 
> On 21/08/2009 1:29 PM, pierr wrote:
>> 
>> Simon Slavin-2 wrote:
>>>
>>> On 21 Aug 2009, at 3:26am, pierr wrote:
>>>
 I did not know the sequence in defining the field matters. This is
 what I should have done.
>>> Sorry, I should have explained better.  You were right: there is no  
>>> difference.  I was just rearranging the fields in the classic way:  
>>> with the primary key column as the first column.  It helps me think  
>>> about how the database works.  You did nothing wrong.
>>>
>> Hi Simon,
>>   It do make a difference. 
>>   With this schema, 
>>   CREATE TABLE IF NOT EXISTS tblIndex(
>>  frame_type INTEGER,
>>  pts VARCHAR(5)
>>  ts_start INTEGER PRIMARY KEY,
>>  ts_end INTEGER,
>>  ) 
>>  There will be a rowid field in the database ; and there is a
>> sqlite_autoindex_tblIndex_1 build on ts_start automatically ; so
>> 1,800,000
>> records (16bytes each) takes 62M . 
> 
> Please don't type from memory -- what you have above has TWO syntax 
> errors, and doesn't (with those random comma placements fixed) produce 
> the result that you say -- and do read my previous message.
> 
> Here is an (annotated) copy/paste of an actual session:
> 
> sqlite>   CREATE TABLE IF NOT EXISTS tblIndex(
> ...>  frame_type INTEGER,
> ...>  pts VARCHAR(5)<<<=== missing comma
> ...>  ts_start INTEGER PRIMARY KEY,
> ...>  ts_end INTEGER,
> ...>  )
> ...> ;
> SQL error: near "ts_start": syntax error
> sqlite>   CREATE TABLE IF NOT EXISTS tblIndex(
> ...>  frame_type INTEGER,
> ...>  pts VARCHAR(5),
> ...>  ts_start INTEGER PRIMARY KEY,
> ...>  ts_end INTEGER, <<<=== superflous comma
> ...>  );
> SQL error: near ")": syntax error
> sqlite>   CREATE TABLE IF NOT EXISTS tblIndex(
> ...>  frame_type INTEGER,
> ...>  pts VARCHAR(5),
> ...>  ts_start INTEGER PRIMARY KEY,
> ...>  ts_end INTEGER
> ...>  );
> sqlite> select * from sqlite_master;
> table|tblIndex|tblIndex|2|CREATE TABLE tblIndex(
>   frame_type INTEGER,
>   pts VARCHAR(5),
>   ts_start INTEGER PRIMARY KEY,
>   ts_end INTEGER
>   ) <<<=== no index !!
> sqlite> drop table tblIndex;
> sqlite>   CREATE TABLE IF NOT EXISTS tblIndex(
> ...>  frame_type INTEGER,
> ...>  pts VARCHAR(5),
> ...>  ts_start INT PRIMARY KEY,  <<<=== using your original INT 
> instead of Simon's INTEGER
> ...>  ts_end INTEGER
> ...>  );
> sqlite> select * from sqlite_master;
> table|tblIndex|tblIndex|2|CREATE TABLE tblIndex(
>   frame_type INTEGER,
>   pts VARCHAR(5),
>   ts_start INT PRIMARY KEY,
>   ts_end INTEGER
>   )
> index|sqlite_autoindex_tblIndex_1|tblIndex|3| <<<=== index!!
> sqlite>
> 
> 
After reading your previous message , now I understand the difference
between "INT PRIMARY KEY" and "INTEGER PRIMARY KEY" which I did not notice
before. And sorry for "typing from memory":)
 

-- 
View this message in context: 
http://www.nabble.com/suggestion-on-the-database-design-tp25062134p25074149.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

Re: [sqlite] suggestion on the database design

[John Machin]

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

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

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

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


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