Re: [sqlite] Hexadecimal integer literals

[Scott Robison]

On Wed, Jul 23, 2014 at 9:46 PM, J Decker  wrote:

> Seems like adding hex interpreting is just adding code for the sake of
> adding code.
> Unless the data is coming from some pre written text file, isn't just just
> as easy to format an into into decimal as it is for hex without having to
> add extra characters for the prefix?
>

One desirable aspect of code is that it is easily understood, that future
modifications / changes be as easy as possible. It is usually much easier
to see that 0x4000 is a single bit set in a 32 bit integer vs seeing
the number 1073741824 and wondering exactly what it means. At least it is
to me.

For that reason, I think adding hex constants to the parser is a good idea.
If I were expressing wishes, I'd suggest adding binary constants (with a 0b
prefix) but that's probably pushing luck. :)

But no octal! At least not using the C syntax. I like being able to prefix
a decimal integer with 0 and not having it change the meaning (which can't
be done in C).
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Addressing columns in an attached db

[jose isaias cabrera]

"James K. Lowden" wrote...


On Wed, 23 Jul 2014 20:00:52 -0600
"Keith Medcalf"  wrote:


BEGIN;
  INSERT OR REPLACE INTO LSOpenProjects
SELECT * FROM client.LSOpenProjects as A
  WHERE A.ProjID <= 133560 AND
Date != A.Date AND
A.login = 'user1';
END;

I presume that LSOpenProjects in both databases has a unique
constraint on ProjID, Date, and login ...


That BEGIN/END should be superfluous because INSERT...SELECT is a
single atomic statement.  Is there something about SQLite that makes it
necessary, or was it just for illustrative purposes?


I learned in this list that BEGIN/END speeds up the inserts by blocking the 
write mode to the DB.  I was getting a lot of SQLITE_BUSY and these 
BEGIN/END got rid of 99% of them.


jic 


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

Re: [sqlite] Addressing columns in an attached db

[jose isaias cabrera]

"Keith Medcalf" wrote...


BEGIN;
 INSERT OR REPLACE INTO LSOpenProjects
   SELECT * FROM client.LSOpenProjects as A
 WHERE A.ProjID <= 133560 AND
   Date != A.Date AND
   A.login = 'user1';
END;

I presume that LSOpenProjects in both databases has a unique constraint on 
ProjID, Date, and login ...


Indeed.  Thanks so much.

josé 


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

Re: [sqlite] Hexadecimal integer literals

[J Decker]

Seems like adding hex interpreting is just adding code for the sake of
adding code.
Unless the data is coming from some pre written text file, isn't just just
as easy to format an into into decimal as it is for hex without having to
add extra characters for the prefix?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Addressing columns in an attached db

[James K. Lowden]

On Wed, 23 Jul 2014 20:00:52 -0600
"Keith Medcalf"  wrote:

> BEGIN;
>   INSERT OR REPLACE INTO LSOpenProjects
> SELECT * FROM client.LSOpenProjects as A
>   WHERE A.ProjID <= 133560 AND
> Date != A.Date AND
> A.login = 'user1';
> END;
> 
> I presume that LSOpenProjects in both databases has a unique
> constraint on ProjID, Date, and login ...

That BEGIN/END should be superfluous because INSERT...SELECT is a
single atomic statement.  Is there something about SQLite that makes it
necessary, or was it just for illustrative purposes?  

--jkl

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

Re: [sqlite] Hexadecimal integer literals

[Eric Rubin-Smith]

So far no one has raised the idea of using a "big int" layer to implement
proper integer arithmetic past 64 bits.  The fact that it hasn't been
mentioned makes me worry that it's a blatantly silly idea for SQLite for
some reason -- but I'm tossing it out there on the off chance that it's
useful.

Personally, I would have loved to be able to use 128-bit integer math a few
weeks ago when I was investigating the idea of implementing a
longest-prefix search capability for IPv6 networks using SQLite's R*Tree.
 I had to implement all the shifting and such in C, which is fine, but it
would have been pretty sexy to have gotten it all done in SQL.

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

Re: [sqlite] Addressing columns in an attached db

[Keith Medcalf]

BEGIN;
  INSERT OR REPLACE INTO LSOpenProjects
SELECT * FROM client.LSOpenProjects as A
  WHERE A.ProjID <= 133560 AND
Date != A.Date AND
A.login = 'user1';
END;

I presume that LSOpenProjects in both databases has a unique constraint on 
ProjID, Date, and login ...

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of jose isaias cabrera
>Sent: Wednesday, 23 July, 2014 10:32
>To: General Discussion of SQLite Database
>Subject: [sqlite] Addressing columns in an attached db
>
>
>Greetings.
>
>I have two DB identical and I am copying data from one to another.
>
>When I attach a DB, i.e..
>
>ATTACH 'c:\db\mydb.sqlite' as client;
>
>how do I address the names of the column?  for example, both DBs
>connected
>and disconnected have the columns: id,ProjID, Date, code, login.  When I
>do
>this command,
>
>BEGIN;
>  INSERT OR REPLACE INTO LSOpenProjects
>SELECT * FROM client.LSOpenProjects
>  WHERE client.LSOpenProjects.ProjID <= 133560 AND
>Date != client.LSOpenProjects.Date AND
>client.LSOpenProjects.login = 'user1';
>END;
>
>So, is there a different way of addressing the attached DB table columns?
>Any help would be greatly appreciated.  thanks.
>
>josé
>
>___
>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] Hexadecimal integer literals

[James K. Lowden]

On Wed, 23 Jul 2014 07:07:25 -0400
Richard Hipp  wrote:

> Hex literals are useful in conjunction with the bit-wise AND and OR
> operators (& and |) and in applications that make use of bit fields.
...
> The current SQLite implementation (on the "hex-literal" branch) works
> by converting hex literals of 64 bits or less into a signed 64-bit
> integer. Hex literals of 65 bits or more are approximated by a nearby
> floating-point number.  

Hex literals are used in an integer context.  Converting them to
floating point is counter-productive.  If someone gives you a
17-digit hexadecimal literal, he doesn't want a floating point
approximation.  He wants an error message.  

I would raise an error for anything over 64 bits, or just ignore it,
until you support a processor with a wider integral type.  I don't see
a need for it except as input to the parser.  I guess that puts me in
the #6 camp, too.  

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

Re: [sqlite] [Spellfix] Searching for short words is very slow

[Philip Bennefall]

Hi Richard,

My application is basically just to take a text file as a command line 
argument and run the spellchecker on it, showing an alert for each word 
that is not found in the dictionary and giving the user some options.


After a bit of experimentation I concluded that one way to speed things 
up is to store the entire dictionary in memory as a hash map and look 
for exact matches. Only when an exact match isn't found do I fall back 
to the spellfix table. This allowed me to scan a document with just over 
86000 words in less than 500 milliseconds, which is more than acceptable 
for my needs. Certainly not ideal if you aren't on a workstation, but 
it's a reasonable tradeoff if memory is not an issue.


Perhaps something similar could be done in the spellfix table itself? 
Have an indexed integer column containing a crc32 or similar for each 
word in the dictionary so that we can look for exact matches very 
quickly. We only fall back to the fuzzy search if no match is found. Can 
you see any obvious drawbacks with this? If not, I'd like to put this 
optimization forth as an initial suggestion. I'll write again if I can 
think of anything else after reading the code more thoroughly.


Kind regards,

Philip Bennefall
On 7/24/2014 12:25 AM, Richard Hipp wrote:




On Wed, Jul 23, 2014 at 6:18 PM, Philip Bennefall > wrote:


I have to amend my last message. The timings I just gave was for
looking up that word 10 times, not 1. So the longest time I've
seen would be about 150 ms. However, if you have a document with a
few thousand words we would still be looking at a significant
total searching time. Is this to be expected?


There is no expectation.

Spellfix is an experiment in doing fuzzy matching.  It was designed 
for a specific customer who is doing spell-checking in real-time, as 
the text is being entered.  Spellfix works way faster than the end 
user can enter text, so performance is not an issue in its original 
purpose.


Perhaps you are using spellfix in a different way?  You are welcomed 
to do so.  If you want to contribute ideas on how to improve spellfix 
for use in different scenarios, we will welcome your input.


There are comments in the code explaining how spellfix works.  Please 
review the principles of operation and then perhaps run a performance 
analysis using gprof or cachegrind.  Then describe exactly what you 
are doing and why it isn't working out for you and perhaps we can help.




--
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] [Spellfix] Searching for short words is very slow

[Richard Hipp]

On Wed, Jul 23, 2014 at 6:18 PM, Philip Bennefall 
wrote:

> I have to amend my last message. The timings I just gave was for looking
> up that word 10 times, not 1. So the longest time I've seen would be about
> 150 ms. However, if you have a document with a few thousand words we would
> still be looking at a significant total searching time. Is this to be
> expected?
>

There is no expectation.

Spellfix is an experiment in doing fuzzy matching.  It was designed for a
specific customer who is doing spell-checking in real-time, as the text is
being entered.  Spellfix works way faster than the end user can enter text,
so performance is not an issue in its original purpose.

Perhaps you are using spellfix in a different way?  You are welcomed to do
so.  If you want to contribute ideas on how to improve spellfix for use in
different scenarios, we will welcome your input.

There are comments in the code explaining how spellfix works.  Please
review the principles of operation and then perhaps run a performance
analysis using gprof or cachegrind.  Then describe exactly what you are
doing and why it isn't working out for you and perhaps we can help.



-- 
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] [Spellfix] Searching for short words is very slow

[Philip Bennefall]

I have to amend my last message. The timings I just gave was for looking 
up that word 10 times, not 1. So the longest time I've seen would be 
about 150 ms. However, if you have a document with a few thousand words 
we would still be looking at a significant total searching time. Is this 
to be expected?


Kind regards,

Philip Bennefall
On 7/23/2014 11:57 PM, Philip Bennefall wrote:

Hi all,

I have been running some tests with spellfix using a table containing
about 30 words, extracted from the Moby project's single word list
as well as names and places. Moby can be found at:
http://icon.shef.ac.uk/Moby/

I have noticed that searching for medium length to very long words is
very fast, but when I start searching for short words like "hi" and
"bye", the search time skyrockets. I think the longest search time was
about 1500 milliseconds (the average is somewhere around the 500 ms
mark). My table is set up as follows:

create virtual table if not exists dictionary using
spellfix1(edit_cost_table=editcosts);

When searching, I specify top=5 to get these timings.

Is there anything I can tweak to speed up the search for short words, or
is there anything that can be done by the developers to optimize this
further?

Kind regards,

Philip Bennefall
___
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] Hexadecimal integer literals

[Zsbán Ambrus]

Hello!


Just like the others in this conversation, I also believe that you
must not change the rules how strings are converted to integers by
type affinity, or by type conversions of arithmetic operators.  Thus,
you must not add hexadecimal representation to conversions (nor hex
floats or 'inf' or any other new numeric representation).

Let me explain why.  I create the following table in a database:

CREATE TABLE tb(cl NUMERIC PRIMARY KEY);
INSERT INTO tb VALUES('3.50');
INSERT INTO tb VALUES('0x20');

This inserts the real 3.5 and the string '0x20' into the table.  Then,
any of the following queries finds a row from that table.

SELECT cl FROM tb WHERE cl = 3.5;
SELECT cl FROM tb WHERE cl = '3.5';
SELECT cl FROM tb WHERE cl = '3.50';
SELECT cl FROM tb WHERE cl = '3.500';
SELECT cl FROM tb WHERE cl = '0x20';

Now suppose hypothetically that you changed a future version of sqlite
to convert the string '0x20' to the number 32.  Load the existing
database into this new version.  This table will then have a strange
state: it will have a row with the string ('0x20') which is something
you couldn't insert with the new version of sqlite, because if you
tried to insert it, the new version would convert it to a number.
Further, the queries

SELECT cl FROM tb WHERE cl = '0x20';
SELECT cl FROM tb WHERE cl = 32;

would now either not find that row, or would have to search in
multiple places in the index, because '0x20' is still indexed as a
string, not as a number.

Similarly, a table could have a check constraint that the previous
version of sqlite had validated at insertion, but is no longer true in
the hypothetical new version; or a partial index that used to be
correct in a previous version of sqlite but is now suddenly missing
rows that should be in there in the hypothetical new version.  (I
don't know whether there could be a problem with foreign keys.)


Now of course adding hexadecimal literals in the parser doesn't cause
any of these problems, as long as you make sure any statement or
schema with such a hexadecimal literal definitely gives an error in
previous versions of sqlite.  Similarly, if you believe that parsing
numbers from a hexadecimal representation is useful at runtime, you
could add an ordinary SQL function that does this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [Spellfix] Searching for short words is very slow

[Philip Bennefall]

Hi all,

I have been running some tests with spellfix using a table containing 
about 30 words, extracted from the Moby project's single word list 
as well as names and places. Moby can be found at:

http://icon.shef.ac.uk/Moby/

I have noticed that searching for medium length to very long words is 
very fast, but when I start searching for short words like "hi" and 
"bye", the search time skyrockets. I think the longest search time was 
about 1500 milliseconds (the average is somewhere around the 500 ms 
mark). My table is set up as follows:


create virtual table if not exists dictionary using 
spellfix1(edit_cost_table=editcosts);


When searching, I specify top=5 to get these timings.

Is there anything I can tweak to speed up the search for short words, or 
is there anything that can be done by the developers to optimize this 
further?


Kind regards,

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

Re: [sqlite] Sometimes when my process restarts, it returns error "database is locked"

[Simon Slavin]

On 23 Jul 2014, at 8:56pm, Mayank Kumar (mayankum)  wrote:

> I have already enabled extended error codes but I am not seeing any extended 
> error codes being returned. I also enabled the error logging callback which 
> just prints the errorcode and the error message which I was already getting 
> when the sqlite3 api was failing.

If you have enabled extended error codes you should be getting extended error 
codes returned by the API calls themselves.  I'm afraid that is all you can get.

> I know this error logging callback can report misuse of api's etc, but can 
> this facility or any other logging facility tell me more about why an api is 
> failing like for .e.g. in some customer scenarios I get sqlite_busy or 
> database is locked and I am trying to add the error logging callback to see 
> if I can get more details on why the database is locked or which process has 
> locked it, etc.

Sorry, SQLite does not know what process has the database locked.  The 
processed do not have codes, and no code is stored then the database is locked, 
just the fact that a process wants it to be locked.

> I have only one process which opens a connection to the database and 
> sometimes  when it restarts and then it detects the database is locked 
> although there is no other process which accesses it.

Do you call have a timeout value set before you make the API call which opens 
the database ?

Your description suggests that your process is not unlocking the database 
correctly when it quits to restart.  Is that process quitting under its own 
control, or is it crashing ?  If it is quitting under its own control is should 
be calling sqlite3_close_v2() and then sqlite3_shutdown() and checking the 
responses to make sure it was correctly able to release all resources.

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

Re: [sqlite] Sometimes when my process restarts, it returns error "database is locked"

[Mayank Kumar (mayankum)]

Thanks Simon, I have already enabled extended error codes but I am not seeing 
any extended error codes being returned. I also enabled the error logging 
callback which just prints the errorcode and the error message which I was 
already getting when the sqlite3 api was failing.  I know this error logging 
callback can report misuse of api's etc, but can this facility or any other 
logging facility tell me more about why an api is failing like for .e.g. in 
some customer scenarios I get sqlite_busy or database is locked and I am trying 
to add the error logging callback to see if I can get more details on why the 
database is locked or which process has locked it, etc. I have only one process 
which opens a connection to the database and sometimes  when it restarts and 
then it detects the database is locked although there is no other process which 
accesses it.


-Mayank





-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Wednesday, July 23, 2014 11:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sometimes when my process restarts, it returns error 
"database is locked"


On 23 Jul 2014, at 7:27pm, Mayank Kumar (mayankum)  wrote:

> SQLITE_LOG: sqlite errcode=26, sqlite errmsg=file is encrypted or is not a 
> database
> 
> The same error code I get in the return value of sqlite3_step. Just wondering 
> if there is a way  I can get this  error logging call back to  print more 
> details than this ?
> I have enabled extended error codes, is there a way I can get some more error 
> details like :- -what part of the database is corrupted

There is no way to tell.  Sometimes this error is caused by a simple obvious 
part of the file (e.g. the text right at the beginning) but other times it 
means that two parts of the file disagree with each other.  There's no way to 
tell which one got corrupted.  Perhaps they both got corrupted.  Perhaps the 
entire file is corrupt and has been replaced with a text file.

> -what line number or when was this issue first detected

SQLite does not know how your application is organised.  One particular API 
call returns the error, so you know where the error was found: it was by that 
call.

> -what is the extended error code in this scenarios ?

If you have set the flag using



then you will get extended error codes.

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] Sometimes when my process restarts, it returns error "database is locked"

[Tim Streater]

On 23 Jul 2014 at 19:43, Simon Slavin  wrote: 

>> -what line number or when was this issue first detected
>
> SQLite does not know how your application is organised.  One particular API
> call returns the error, so you know where the error was found: it was by that
> call.

I needed to know which SQLite had an error. So I made a shim around the actual 
PHP calls to SQLite with extra arguments, one of which is a unique code for the 
call. In addition, the shim does all error handling, packaging the error 
message so it can be suitably returned as results of an AJAX request in a way 
that the calling side can readily interpret.



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

Re: [sqlite] Sometimes when my process restarts, it returns error "database is locked"

[Simon Slavin]

On 23 Jul 2014, at 7:27pm, Mayank Kumar (mayankum)  wrote:

> SQLITE_LOG: sqlite errcode=26, sqlite errmsg=file is encrypted or is not a 
> database
> 
> The same error code I get in the return value of sqlite3_step. Just wondering 
> if there is a way  I can get this  error logging call back to  print more 
> details than this ?
> I have enabled extended error codes, is there a way I can get some more error 
> details like :- -what part of the database is corrupted

There is no way to tell.  Sometimes this error is caused by a simple obvious 
part of the file (e.g. the text right at the beginning) but other times it 
means that two parts of the file disagree with each other.  There's no way to 
tell which one got corrupted.  Perhaps they both got corrupted.  Perhaps the 
entire file is corrupt and has been replaced with a text file.

> -what line number or when was this issue first detected

SQLite does not know how your application is organised.  One particular API 
call returns the error, so you know where the error was found: it was by that 
call.

> -what is the extended error code in this scenarios ?

If you have set the flag using



then you will get extended error codes.

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

Re: [sqlite] Sometimes when my process restarts, it returns error "database is locked"

[Mayank Kumar (mayankum)]

[Resending to see if someone has insight here ...]
I was able to get the error logging callback working. But I am curious as I am 
just seeing the same error details as the sqlite3 api itself returns. For e.g.

SQLITE_LOG: sqlite errcode=26, sqlite errmsg=file is encrypted or is not a 
database

The same error code I get in the return value of sqlite3_step. Just wondering 
if there is a way  I can get this  error logging call back to  print more 
details than this ?
I have enabled extended error codes, is there a way I can get some more error 
details like :- -what part of the database is corrupted -what line number or 
when was this issue first detected -what is the extended error code in this 
scenarios ?


-mayank


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Mayank Kumar (mayankum)
Sent: Friday, July 18, 2014 12:26 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sometimes when my process restarts, it returns error 
"database is locked"

Thanks Simon. Now sqlite3_config is the first call we are making followed by 
sqlite3_open. I am checking the return code for all calls.

In one of the sqlite3_step calls later, I am getting an error "file is 
encrypted or is not a database" but I don't see this callback getting called. 
Immediately after I get this error , the process kills itself purposely.  I am 
only interested in knowing why my callback is not getting called in this 
scenario ?  Is it not getting called because the process is not alive by that 
time or some other issue. The " file is encrypted or is not a database" error 
is being simulated by writing bad data at the  header of the sqlite db while my 
application is running.

Ret =   Sqlite3_step()
Ret = 26
Die()---kills itself

Does the callback gets called before sqlite3_step returns with an error code or 
after ? Since if it gets called after, it may not happen. I want to enable this 
callback to get more fine grained information about  whats going on with out 
system.

Thanks for your help 
Mayank


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Tuesday, July 15, 2014 11:13 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sometimes when my process restarts, it returns error 
"database is locked"


On 15 Jul 2014, at 6:58pm, Mayank Kumar (mayankum)  wrote:

> I was calling it after sqlite3_open,I will try calling before sqlite3_open 
> and update the thread.

When you call sqlite3_config() after sqlite3_open() it returns SQLITE_MISUSE to 
tell you you're doing it wrong.  The fact that you didn't notice this error 
shows us you are not checking the result codes returned by (almost ?) all 
sqlite3 API calls.

Checking these results codes for /every/ call helps programmers spot and 
correct almost all errors.  It's important to check them all because often the 
error is returned by a different call to the one which, logically, should be 
generating the error.  It's simple, you just replace



with something like

if ( != SQLITE_OK) {

}

When people report problems to this list, having them make this change to 
/every/ sqlite3 call makes a lot of them go away.

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

[sqlite] Announcement: sqlite3 vfs layer for RAMCloud

[Jakob Blomer]

Dear all,

I’d like to announce an sqlite3 vfs layer [1,2] that uses the RAMCloud 
[3] distributed key-value store as backing storage.  RAMCloud provides 
distributed, durable (replicated) storage with low-latency access.  The 
VFS driver is still under development but the basic operations, 
including locks, work.  We were able to extract benchmark numbers on our 
test cluster with Infiniband interconnect.  The performance largely 
depends on the latency of the remote procedure calls to the distributed 
key-value store, which we tried to reduce as much as possible.  For a 
TPCC benchmark, we found that sqlite on our RAMCloud test cluster 
provides around 2/3 the performance of a local ram disk.


If you have any comments or questions, please let us know.

Best regards,
Jakob


[1] https://github.com/jblomer/ramcloud-sqlite3
[2] https://github.com/jblomer/ramcloud-sqlite3/blob/master/README.md
[3] https://ramcloud.stanford.edu
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory ownership for sqlite3_temp_directory

[Richard Hipp]

On Wed, Jul 23, 2014 at 1:29 PM, Nicolás Brailovsky  wrote:

> > What platform are you running on that you feel like you need to specify a
> > temporary directory other than the system default?
>
> Android. If I don't specify the temp dir, I end up triggering a bunch of
> bugs in fuse :)
>

On Android, set either the TMPDIR or the SQLITE_TMPDIR environment variable.


>
> Cheers
>
> Nicolás Brailovsky
> - Tech blog http://monoinfinito.wordpress.com
>
>
>
> On 23 July 2014 19:24, Richard Hipp  wrote:
>
> > On Wed, Jul 23, 2014 at 1:20 PM, Nicolás Brailovsky <
> > nicolasbra...@gmail.com
> > > wrote:
> >
> > > > Set sqlite3_temp_directory once when the process starts (and then
> > > > only if you really need it) then leave it alone for the remainder of
> > the
> > > > live of the process.  Do not try to reclaim memory.
> > >
> > > Fair enough. Would you recommend any alternatives to replace
> > temp_directory
> > > altogether?
> > >
> >
> > What platform are you running on that you feel like you need to specify a
> > temporary directory other than the system default?
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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
>



-- 
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] Addressing columns in an attached db

[jose isaias cabrera]

Richard Hipp wrote...

On Wed, Jul 23, 2014 at 12:32 PM, jose isaias cabrera 

wrote:




Greetings.

I have two DB identical and I am copying data from one to another.

When I attach a DB, i.e..

ATTACH 'c:\db\mydb.sqlite' as client;

how do I address the names of the column?  for example, both DBs 
connected
and disconnected have the columns: id,ProjID, Date, code, login.  When I 
do

this command,

BEGIN;
 INSERT OR REPLACE INTO LSOpenProjects
   SELECT * FROM client.LSOpenProjects
 WHERE client.LSOpenProjects.ProjID <= 133560 AND
   Date != client.LSOpenProjects.Date AND
   client.LSOpenProjects.login = 'user1';
END;


The main.LsOpenProjects is not in scope for the SELECT statement so you 
can

omit the "client.LSOpenProjects." prefix if you like.  On the other hand,
what you wrote works, so what's the problem?


No problem. I just wanted to see if there was another way of writing it. 
So, what you are saying is that this,


INSERT OR REPLACE INTO LSOpenProjects
   SELECT * FROM client.LSOpenProjects
 WHERE ProjID <= 133560 AND
   Date != Date AND
   login = 'user1';

is the same as above?  Ok, I think I wrote this one wrong.  This statement,

   Date != client.LSOpenProjects.Date

is it really,

main.LSOpenProjects.Date != client.LSOpenProjects.Date

or am I just writing Junk SQL?  Sorry for the newbie question.  What I would 
like is to INSERT anything that from the client.LSOpenProjects.Date != 
main.LSOpenProjects.Date.  Thanks for the help.


josé


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

Re: [sqlite] Memory ownership for sqlite3_temp_directory

[Nicolás Brailovsky]

> What platform are you running on that you feel like you need to specify a
> temporary directory other than the system default?

Android. If I don't specify the temp dir, I end up triggering a bunch of
bugs in fuse :)

Cheers

Nicolás Brailovsky
- Tech blog http://monoinfinito.wordpress.com



On 23 July 2014 19:24, Richard Hipp  wrote:

> On Wed, Jul 23, 2014 at 1:20 PM, Nicolás Brailovsky <
> nicolasbra...@gmail.com
> > wrote:
>
> > > Set sqlite3_temp_directory once when the process starts (and then
> > > only if you really need it) then leave it alone for the remainder of
> the
> > > live of the process.  Do not try to reclaim memory.
> >
> > Fair enough. Would you recommend any alternatives to replace
> temp_directory
> > altogether?
> >
>
> What platform are you running on that you feel like you need to specify a
> temporary directory other than the system default?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Memory ownership for sqlite3_temp_directory

[Richard Hipp]

On Wed, Jul 23, 2014 at 1:20 PM, Nicolás Brailovsky  wrote:

> > Set sqlite3_temp_directory once when the process starts (and then
> > only if you really need it) then leave it alone for the remainder of the
> > live of the process.  Do not try to reclaim memory.
>
> Fair enough. Would you recommend any alternatives to replace temp_directory
> altogether?
>

What platform are you running on that you feel like you need to specify a
temporary directory other than the system default?


-- 
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] Memory ownership for sqlite3_temp_directory

[Nicolás Brailovsky]

> Set sqlite3_temp_directory once when the process starts (and then
> only if you really need it) then leave it alone for the remainder of the
> live of the process.  Do not try to reclaim memory.

Fair enough. Would you recommend any alternatives to replace temp_directory
altogether?

Cheers

Nicolás Brailovsky
- Tech blog http://monoinfinito.wordpress.com



On 23 July 2014 18:40, Richard Hipp  wrote:

> On Wed, Jul 23, 2014 at 12:10 PM, Nicolás Brailovsky <
> nicolasbra...@gmail.com> wrote:
>
> >
> > How would you suggest to implement the cleanup for
> sqlite3_temp_directory?
> >
>
> Don't.  Set sqlite3_temp_directory once when the process starts (and then
> only if you really need it) then leave it alone for the remainder of the
> live of the process.  Do not try to reclaim memory.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] [Spellfix] Avoiding exact duplicates

[Klaas V]

Philip wrote:

>Is it possible to tell the spellfix extension not to accept duplicate 
>words if the rank and langid are exactly the same? I am collecting words 
>from many different sources and after inserting them I very often get 
>the same word back several times.

Add a 'unique' constraint in your create table statement for the column word so 
you can't have double words unless the same word has more meanings.
In that case you need to add e.g. (verb), (noun), [1], [2] or whatever you find 
applicable storing words in a table. 

I don't know if the extension allows you to make changes. 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Addressing columns in an attached db

[Richard Hipp]

On Wed, Jul 23, 2014 at 12:32 PM, jose isaias cabrera  wrote:

>
> Greetings.
>
> I have two DB identical and I am copying data from one to another.
>
> When I attach a DB, i.e..
>
> ATTACH 'c:\db\mydb.sqlite' as client;
>
> how do I address the names of the column?  for example, both DBs connected
> and disconnected have the columns: id,ProjID, Date, code, login.  When I do
> this command,
>
> BEGIN;
>  INSERT OR REPLACE INTO LSOpenProjects
>SELECT * FROM client.LSOpenProjects
>  WHERE client.LSOpenProjects.ProjID <= 133560 AND
>Date != client.LSOpenProjects.Date AND
>client.LSOpenProjects.login = 'user1';
> END;
>
>
The main.LsOpenProjects is not in scope for the SELECT statement so you can
omit the "client.LSOpenProjects." prefix if you like.  On the other hand,
what you wrote works, so what's the problem?


> So, is there a different way of addressing the attached DB table columns?
> Any help would be greatly appreciated.  thanks.
>
> josé
> ___
> 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] Hexadecimal integer literals

[Klaas V]

Like some others I vote for solution 6.
In general accepting hexadecimal notation for floating point values exceeding 
64 bits is too developer/scientist friendly. Who needs so much precision use 
Fortran or another specialized language rather than SQL of any flavor. My € 
2E-2 [±$0,026930006220831 ;-)]
 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory ownership for sqlite3_temp_directory

[Richard Hipp]

On Wed, Jul 23, 2014 at 12:10 PM, Nicolás Brailovsky <
nicolasbra...@gmail.com> wrote:

>
> How would you suggest to implement the cleanup for sqlite3_temp_directory?
>

Don't.  Set sqlite3_temp_directory once when the process starts (and then
only if you really need it) then leave it alone for the remainder of the
live of the process.  Do not try to reclaim memory.

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

[sqlite] Addressing columns in an attached db

[jose isaias cabrera]

Greetings.

I have two DB identical and I am copying data from one to another.

When I attach a DB, i.e..

ATTACH 'c:\db\mydb.sqlite' as client;

how do I address the names of the column?  for example, both DBs connected 
and disconnected have the columns: id,ProjID, Date, code, login.  When I do 
this command,


BEGIN;
 INSERT OR REPLACE INTO LSOpenProjects
   SELECT * FROM client.LSOpenProjects
 WHERE client.LSOpenProjects.ProjID <= 133560 AND
   Date != client.LSOpenProjects.Date AND
   client.LSOpenProjects.login = 'user1';
END;

So, is there a different way of addressing the attached DB table columns? 
Any help would be greatly appreciated.  thanks.


josé 


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

Re: [sqlite] Memory ownership for sqlite3_temp_directory

[Nicolás Brailovsky]

Thanks for your reply Richard


> >* 1. Is the memory for sqlite3_temp_directory ever cleaned up?
*> >>
> Not by SQLite.  That memory is the responsibility of the application that
> allocated it.

Given the memory may be cleaned up by sqlite itself in some cases:
1. Freeing it after calling shutdown (ie when we're certain sqlite is not
going to use it anymore) using sqlite3_temp_directory itself wouldn't work,
since sqlite3_temp_directory seems to be set to null after calling shutdown
2. Keeping a copy of the pointer to free it later could result in a double
free [quote: the pragma may attempt to free that memory...]
3. Freeing it just before calling shutdown seems to me like a race
condition (I haven't done any tests in this regard, though)

How would you suggest to implement the cleanup for sqlite3_temp_directory?

> I will add text encouraging people to NOT use this interface.

Is there a better alternative to dynamically set a temp path?

Cheers

--

>* Is the memory used by sqlite3_temp_directory freed by sqlite or should it
*>* be freed by the user?
*>>* From the documentation for sqlite3_temp_directory:
*>>* ** the [temp_store_directory pragma] always assumes that any string
*>* ** that this variable points to is held in memory obtained from
*>* ** [sqlite3_malloc] and the pragma may attempt to free that memory
*>* ** using [sqlite3_free].
*>>* From this I understand that the memory ownership gets transferred to
*>* sqlite, so the responsibility for clean up should be sqlite's (presumably
*>* in the sqlite3_shutdown function). This doesn't seem to be the case,
*>* though.
*>>* 1. Is the memory for sqlite3_temp_directory ever cleaned up?
*>
Not by SQLite.  That memory is the responsibility of the application that
allocated it.



>* 2. If not, should it be?
*>
No.  To do so would break legacy applications that clean it up themselves.


>* 3. Could the information about the memory ownership be added to the
*>* documentation? I believe this would also apply to other extern-definitions
*>* as well.
*>
I will add text encouraging people to NOT use this interface.  yes.


>>* Cheers
*>>* Nicolás Brailovsky
*>* - Tech blog http://monoinfinito.wordpress.com

*>* ___
*>* sqlite-users mailing list
*>* sqlite-users at sqlite.org

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

*>


-- 
D. Richard Hippdrh at sqlite.org

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

Re: [sqlite] Hexadecimal integer literals

[Jean-Christophe Deschamps]

Conversion of oversized hex into FP would break easily and reveal 
hardly reproductible across many platforms. Being a support for some 
languages fora I observe daily how FP inaccuracies is a real-world 
problem in simple-looking code.


The only reasonable thing I can foresee is treat hex as signed int64 
inside limits and as blobs outside int64 limits, all without a warning 
or error raised.


This way no new behavior is needed: what happens today when a hex blob 
appears somewhere? Just like when a column typed as INT holds few 
blobs: does the index build/search bomb: no. Oversize hex literals will 
simply behave the same.


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

Re: [sqlite] Memory ownership for sqlite3_temp_directory

[Richard Hipp]

Document updated here: http://www.sqlite.org/draft/c3ref/temp_directory.html


On Wed, Jul 23, 2014 at 11:36 AM, Richard Hipp  wrote:

>
>
>
> On Wed, Jul 23, 2014 at 11:31 AM, Nicolás Brailovsky <
> nicolasbra...@gmail.com> wrote:
>
>> Is the memory used by sqlite3_temp_directory freed by sqlite or should it
>> be freed by the user?
>>
>> From the documentation for sqlite3_temp_directory:
>>
>> ** the [temp_store_directory pragma] always assumes that any string
>> ** that this variable points to is held in memory obtained from
>> ** [sqlite3_malloc] and the pragma may attempt to free that memory
>> ** using [sqlite3_free].
>>
>> From this I understand that the memory ownership gets transferred to
>> sqlite, so the responsibility for clean up should be sqlite's (presumably
>> in the sqlite3_shutdown function). This doesn't seem to be the case,
>> though.
>>
>> 1. Is the memory for sqlite3_temp_directory ever cleaned up?
>>
>
> Not by SQLite.  That memory is the responsibility of the application that
> allocated it.
>
>
>
>> 2. If not, should it be?
>>
>
> No.  To do so would break legacy applications that clean it up themselves.
>
>
>> 3. Could the information about the memory ownership be added to the
>> documentation? I believe this would also apply to other extern-definitions
>> as well.
>>
>
> I will add text encouraging people to NOT use this interface.  yes.
>
>
>>
>> Cheers
>>
>> Nicolás Brailovsky
>> - Tech blog http://monoinfinito.wordpress.com
>> ___
>> 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] Memory ownership for sqlite3_temp_directory

[Richard Hipp]

On Wed, Jul 23, 2014 at 11:31 AM, Nicolás Brailovsky <
nicolasbra...@gmail.com> wrote:

> Is the memory used by sqlite3_temp_directory freed by sqlite or should it
> be freed by the user?
>
> From the documentation for sqlite3_temp_directory:
>
> ** the [temp_store_directory pragma] always assumes that any string
> ** that this variable points to is held in memory obtained from
> ** [sqlite3_malloc] and the pragma may attempt to free that memory
> ** using [sqlite3_free].
>
> From this I understand that the memory ownership gets transferred to
> sqlite, so the responsibility for clean up should be sqlite's (presumably
> in the sqlite3_shutdown function). This doesn't seem to be the case,
> though.
>
> 1. Is the memory for sqlite3_temp_directory ever cleaned up?
>

Not by SQLite.  That memory is the responsibility of the application that
allocated it.



> 2. If not, should it be?
>

No.  To do so would break legacy applications that clean it up themselves.


> 3. Could the information about the memory ownership be added to the
> documentation? I believe this would also apply to other extern-definitions
> as well.
>

I will add text encouraging people to NOT use this interface.  yes.


>
> Cheers
>
> Nicolás Brailovsky
> - Tech blog http://monoinfinito.wordpress.com
> ___
> 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

[sqlite] Memory ownership for sqlite3_temp_directory

[Nicolás Brailovsky]

Is the memory used by sqlite3_temp_directory freed by sqlite or should it
be freed by the user?

From the documentation for sqlite3_temp_directory:

** the [temp_store_directory pragma] always assumes that any string
** that this variable points to is held in memory obtained from
** [sqlite3_malloc] and the pragma may attempt to free that memory
** using [sqlite3_free].

From this I understand that the memory ownership gets transferred to
sqlite, so the responsibility for clean up should be sqlite's (presumably
in the sqlite3_shutdown function). This doesn't seem to be the case, though.

1. Is the memory for sqlite3_temp_directory ever cleaned up?
2. If not, should it be?
3. Could the information about the memory ownership be added to the
documentation? I believe this would also apply to other extern-definitions
as well.

Cheers

Nicolás Brailovsky
- Tech blog http://monoinfinito.wordpress.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Gerry Snyder]

I can think of situations where I would want the result to be truncated 
to 64 bits.


I can think of situations where I would want SQLite to raise an error.

I cannot imagine wanting a floating point result.

Gerry Snyder
-
On 7/23/2014 4:07 AM, Richard Hipp wrote:

We are looking into adding hexadecimal integer literals to SQLite.  In
other words, we are looking to enhance SQLite to understand 0x1234 as
another way of writing 4660.  Hex literals are useful in conjunction with
the bit-wise AND and OR operators (& and |) and in applications that make
use of bit fields.

The question is what to do with hex literals that are larger than 64 bits.



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

Re: [sqlite] Hexadecimal integer literals

[Tim Streater]

On 23 Jul 2014 at 12:07, Richard Hipp  wrote: 

> We are looking into adding hexadecimal integer literals to SQLite.  In
> other words, we are looking to enhance SQLite to understand 0x1234 as
> another way of writing 4660.  Hex literals are useful in conjunction with
> the bit-wise AND and OR operators (& and |) and in applications that make
> use of bit fields.
>
> The question is what to do with hex literals that are larger than 64 bits.
> In other words, what number is represented by (say):
>
>  0x123456789abcdef01

I would say take up to 64 bits. More than that to be handled as an error in the 
same way that a too-long string of decimal digits would be for an integer, or a 
floating value that won't fit because the exponent is too large.

Equally, if the string contains a character invalid for the type, handle all in 
the same way. E.g.:

  0x1234zz
  1234zz
  1.234zz

should have the same error treatment, if any. I note that "select 1234zz;" in 
the shell gives a good error message but I don't know if that's just the shell 
being clever or the library is picking it up.

Regarding signed/unsigned, what is done for integers? Perhaps if a hex literal 
is prefixed with a minus, that simply forces the sign bit on.

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

Re: [sqlite] Hexadecimal integer literals

[Doug Currie]

>
> > Here's an analogy: a sequence of decimal digits is unsigned; it only
> > becomes negative when you put a "-" in front of it.
> >
> > Why shouldn't hex work the same way? (to eliminate the discombobulating
> > segment)
> >
>
> Because then you would not be able to write (in hex) a 64-bit bitmap that
> had the most significant bit set.
>

Ah, you want convenience! You could write -0x8000, but that
does become a hassle.

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

Re: [sqlite] Hexadecimal integer literals

[Richard Hipp]

On Wed, Jul 23, 2014 at 10:22 AM, Doug Currie  wrote:

> > Why are hex literals interpreted as signed at all? You could simply
> > > consider all hex literals as unsigned values. If you need a negative
> > value,
> > > prefix it with the - operator, e.g., -0x77.
> > >
> > > With this approach (a) there is no discombobulating segment, (b) all 64
> > bit
> > > bit-masks are supported, and (c) the gradual overflow to double makes
> > > sense.
> >
> >
> > Because SQLite only supports signed integers internally.  If hex literals
> > must be unsigned, that limits them to 63 bits.
> >
>
> Here's an analogy: a sequence of decimal digits is unsigned; it only
> becomes negative when you put a "-" in front of it.
>
> Why shouldn't hex work the same way? (to eliminate the discombobulating
> segment)
>

Because then you would not be able to write (in hex) a 64-bit bitmap that
had the most significant bit set.

-- 
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] Hexadecimal integer literals

[Doug Currie]

> Why are hex literals interpreted as signed at all? You could simply
> > consider all hex literals as unsigned values. If you need a negative
> value,
> > prefix it with the - operator, e.g., -0x77.
> >
> > With this approach (a) there is no discombobulating segment, (b) all 64
> bit
> > bit-masks are supported, and (c) the gradual overflow to double makes
> > sense.
>
>
> Because SQLite only supports signed integers internally.  If hex literals
> must be unsigned, that limits them to 63 bits.
>

Here's an analogy: a sequence of decimal digits is unsigned; it only
becomes negative when you put a "-" in front of it.

Why shouldn't hex work the same way? (to eliminate the discombobulating
segment)

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

[sqlite] [Spellfix] Avoiding exact duplicates

[Philip Bennefall]

Hi all,

Is it possible to tell the spellfix extension not to accept duplicate 
words if the rank and langid are exactly the same? I am collecting words 
from many different sources and after inserting them I very often get 
the same word back several times.


Thanks in advance for any tips.

Kind regards,

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

Re: [sqlite] Hexadecimal integer literals

[Richard Hipp]

On Wed, Jul 23, 2014 at 9:59 AM, Doug Currie  wrote:

> > There is this range of negative
> > values smack in the middle of an otherwise uniformly increasing sequence
> of
> > positive numbers.  That negative range seems discombobulating.
>
>
> Why are hex literals interpreted as signed at all? You could simply
> consider all hex literals as unsigned values. If you need a negative value,
> prefix it with the - operator, e.g., -0x77.
>
> With this approach (a) there is no discombobulating segment, (b) all 64 bit
> bit-masks are supported, and (c) the gradual overflow to double makes
> sense.
>


Because SQLite only supports signed integers internally.  If hex literals
must be unsigned, that limits them to 63 bits.

-- 
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] Hexadecimal integer literals

[Doug Currie]

> There is this range of negative
> values smack in the middle of an otherwise uniformly increasing sequence of
> positive numbers.  That negative range seems discombobulating.


Why are hex literals interpreted as signed at all? You could simply
consider all hex literals as unsigned values. If you need a negative value,
prefix it with the - operator, e.g., -0x77.

With this approach (a) there is no discombobulating segment, (b) all 64 bit
bit-masks are supported, and (c) the gradual overflow to double makes sense.

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

Re: [sqlite] Hexadecimal integer literals

[Markus Schaber]

Hi,

Von: Im Auftrag von Dominique Devienne
> On Wed, Jul 23, 2014 at 1:07 PM, Richard Hipp  wrote:
> 
> > (6) Do not support hexadecimal integer literals for casts and affinity
> > coercions.  Only support hex literals in the SQL parser, and throw
> > errors for oversized hex literals in that context.
> >
> 
> +1. --DD

I'm also in favour of (6) for now.

This still leaves room for upwards compatible behavior should SQLite 
(version 4, maybe) or an extension add support for larger integers.


Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915

This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient (or have received
this e-mail in error) please notify the sender immediately and destroy this 
e-mail. Any unauthorised copying, disclosure
or distribution of the material in this e-mail is strictly forbidden.

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

Re: [sqlite] Hexadecimal integer literals

[Dominique Devienne]

On Wed, Jul 23, 2014 at 2:15 PM, Simon Slavin  wrote:

> > (3) Convert hex literals of 63-bits or less into integers and convert
> > 64-bit or larger hex literals into a floating-point approximation.
>
> BLOBs.  Anything longer than 64 bits should be BLOBs.  Code which compares
> two values for identity (y == x) will still work correctly, but most other
> things will fail.
>

We already have hex-string literals for blobs, x"ff", so I don't think it's
a good idea.

Someone could accidentally type one too many hex-digit and silently switch
from integer to blob, which might not be immediately apparent, unlike an
error. Which is why I prefer (6). --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Send parameter from batch file to sqlite

[shweta gk]

Operating system : Windows
I am using DOS command shell.
Thanks & Regards,
Shweta.G.K


On Wed, Jul 23, 2014 at 5:48 PM, Simon Slavin  wrote:
> We don't know what operating system or command shell you're using.  So our 
> advice is to write your own editor which takes your export.sql file and the 
> output of your batch file and puts them together to make a new .sql file with 
> the commands you want.
>
> 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] Fwd: Send parameter from batch file to sqlite

[Simon Slavin]

We don't know what operating system or command shell you're using.  So our 
advice is to write your own editor which takes your export.sql file and the 
output of your batch file and puts them together to make a new .sql file with 
the commands you want.

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

Re: [sqlite] Hexadecimal integer literals

[Simon Slavin]

On 23 Jul 2014, at 12:07pm, Richard Hipp  wrote:

> (3) Convert hex literals of 63-bits or less into integers and convert
> 64-bit or larger hex literals into a floating-point approximation.

BLOBs.  Anything longer than 64 bits should be BLOBs.  Code which compares two 
values for identity (y == x) will still work correctly, but most other things 
will fail.

I don't think you need to be considerate to anyone who uses a 65-bit hex 
literal.  Hex is not a natural counting language for humans and anyone who uses 
it needs to be a programmer and think like a programmer.

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

Re: [sqlite] Hexadecimal integer literals

[Paul]

 
> (6) Do not support hexadecimal integer literals for casts and affinity
> coercions. Only support hex literals in the SQL parser, and throw errors
> for oversized hex literals in that context.

I vote for (6) as for being the most predictable behaviour that has no 
possibility to break any existing logic.


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

Re: [sqlite] Hexadecimal integer literals

[Stephan Beal]

On Wed, Jul 23, 2014 at 1:07 PM, Richard Hipp  wrote:

> The current SQLite implementation (on the "hex-literal" branch) works by
> converting hex literals of 64 bits or less into a signed 64-bit integer.
>

Overflow/underflow are unspecified for signed types, and the <> ops
could certainly overflow.


> 1.84467440737096e+19. This approach means that all hexadecimal literals are
> positive numbers, except literals 0x8000 through
> 0x which are negative.  There is this range of negative
> values smack in the middle of an otherwise uniformly increasing sequence of
> positive numbers.  That negative range seems discombobulating.
>

Indeed.

(1) Keep the current prototype behavior.  Hex literals of 64 bits or less
> are converted into twos-complement 64-bit integers, meaning that some
> values are negative.  Hex literals of 65 bits or more are converted into a
> floating-point approximation.
>

Sounds reasonable.


> (2) Declare that the values of hex literals greater than 64 bits are
> "undefined" and convert them into random 64-bit integers.

 Seriously: use
> the random number generator to convert oversized hex literals into
> integers, and thereby discourage programmers from using oversized hex
> literals.
>

i wouln't go quite that far, but converting to NULL might be a middle
ground.


> (4) Silently truncate all hexadecimal literals to 64-bits, like SQL Server
> does.
>

Is reasonable, given that overflow for signed types is undefined (so any
approach is "not wrong").


> (7) Have the parser convert oversized hex literals into NULL, or throw
> errors, and have attempts to coerce oversized hex literals strings into
> numeric values fail, thus preventing affinity conversions.
>

+1 for NULL (sounds simplest and least surprising)


> (8) Work like PostgreSQL and Oracle and legacy SQLite and simply do not
> support hexadecimal integer literals.
>

Also good. Maybe, instead, add functions which take hex strings and return
an int. Then you've got much more freedom in terms of error reporting, as
it all happens via the function:

select hexint('0x10')
16


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

[sqlite] Fwd: Send parameter from batch file to sqlite

[shweta gk]

Thanks & Regards,
Shweta.G.K



-- Forwarded message --
From: shweta gk 
Date: Wed, Jul 23, 2014 at 4:05 PM
Subject: Fwd: Send parameter from batch file to sqlite
To: sqlite-dev 


Hi SQlite Support Team,

I have queries to generate csv file written in a export.sql file. I'm
calling export.sql from a batch file.

One of the queries in export.sql has where clause , to which i have to
send a value from batch file. Which syntax is used for this
functionality.

I need to send the a parameter from batch file to export.sql file. How
can this be done. Kindly send me sample code, as i could not find
anything regarding this issue in the web search.


Kindly reply ASAP.

Thanks & Regards,
Shweta.G.K
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fwd: confirm 6a309b27ca3b749c401faa299d168c6aff685d4b

[shweta gk]

Thanks & Regards,
Shweta.G.K



-- Forwarded message --
From: shweta gk 
Date: Wed, Jul 23, 2014 at 4:09 PM
Subject: Re: confirm 6a309b27ca3b749c401faa299d168c6aff685d4b
To: sqlite-users-requ...@sqlite.org


Thanks & Regards,
Shweta.G.K


On Wed, Jul 23, 2014 at 4:04 PM,   wrote:
> Mailing list subscription confirmation notice for mailing list
> sqlite-users
>
> We have received a request from 103.24.173.170 for subscription of
> your email address, "shwetag...@gmail.com", to the
> sqlite-users@sqlite.org mailing list.  To confirm that you want to be
> added to this mailing list, simply reply to this message, keeping the
> Subject: header intact.  Or visit this web page:
>
> 
> http://sqlite.org:8080/cgi-bin/mailman/confirm/sqlite-users/6a309b27ca3b749c401faa299d168c6aff685d4b
>
>
> Or include the following line -- and only the following line -- in a
> message to sqlite-users-requ...@sqlite.org:
>
> confirm 6a309b27ca3b749c401faa299d168c6aff685d4b
>
> Note that simply sending a `reply' to this message should work from
> most mail readers, since that usually leaves the Subject: line in the
> right form (additional "Re:" text in the Subject: is okay).
>
> If you do not wish to be subscribed to this list, please simply
> disregard this message.  If you think you are being maliciously
> subscribed to the list, or have any other questions, send them to
> sqlite-users-ow...@sqlite.org.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fwd: Passing parameter to SQlite query

[shweta gk]

Hi SQlite Support Team,

I have queries to generate csv file written in a export.sql file. I'm
calling export.sql from a batch file.

One of the queries in export.sql has where clause , to which i have to
send a value from batch file. Which syntax is used for this
functionality.

I need to send the a parameter from batch file to export.sql file. How
can this be done. Kindly send me sample code, as i could not find
anything regarding this issue in the web search.


Kindly reply ASAP.

Thanks & Regards,
Shweta.G.K
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fwd: Send parameter from batch file to sqlite

[shweta gk]

Hi SQlite Support Team,

I have queries to generate csv file written in a export.sql file. I'm
calling export.sql from a batch file.

One of the queries in export.sql has where clause , to which i have to
send a value from batch file. Which syntax is used for this
functionality.

I need to send the a parameter from batch file to export.sql file. How
can this be done. Kindly send me sample code, as i could not find
anything regarding this issue in the web search.


Kindly reply ASAP.

Thanks & Regards,
Shweta.G.K
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Send parameter from batch file to sqlite

[shweta gk]

Hi SQlite Support Team,

I have queries to generate csv file written in a export.sql file. I'm
calling export.sql from a batch file.

One of the queries in export.sql has where clause , to which i have to
send a value from batch file. Which syntax is used for this
functionality.

I need to send the a parameter from batch file to export.sql file. How
can this be done. Kindly send me sample code, as i could not find
anything regarding this issue in the web search.


Kindly reply ASAP.

Thanks & Regards,
Shweta.G.K
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Passing parameter to SQlite query

[shweta gk]

Hi SQlite Support Team,

I have queries to generate csv file written in a export.sql file. I'm
calling export.sql from a batch file.

One of the queries in export.sql has where clause , to which i have to
send a value from batch file. Which syntax is used for this
functionality.

I need to send the a parameter from batch file to export.sql file. How
can this be done. Kindly send me sample code, as i could not find
anything regarding this issue in the web search.


Kindly reply ASAP.

Thanks & Regards,
Shweta.G.K
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Clemens Ladisch]

Richard Hipp wrote:
> Hex literals are useful in conjunction with
> the bit-wise AND and OR operators (& and |) and in applications that make
> use of bit fields.
>
> The question is what to do with hex literals that are larger than 64 bits.

There are no bit operations on values larger than 64 bits, so such
literals should be rejected.

> (6) Do not support hexadecimal integer literals for casts and affinity
> coercions.

There is a compatibiliy constraint: parsing hexadecimal values out of
strings would change the documented behaviour of casts and coercions:
| When casting a TEXT value to INTEGER, the longest possible prefix of
| the value that can be interpreted as an integer number is extracted
| from the TEXT value and the remainder ignored.

So (6) appears to be the only viable choice.


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

Re: [sqlite] Hexadecimal integer literals

[Dominique Devienne]

On Wed, Jul 23, 2014 at 1:07 PM, Richard Hipp  wrote:

> (6) Do not support hexadecimal integer literals for casts and affinity
> coercions.  Only support hex literals in the SQL parser, and throw errors
> for oversized hex literals in that context.
>

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

[sqlite] Hexadecimal integer literals

[Richard Hipp]

We are looking into adding hexadecimal integer literals to SQLite.  In
other words, we are looking to enhance SQLite to understand 0x1234 as
another way of writing 4660.  Hex literals are useful in conjunction with
the bit-wise AND and OR operators (& and |) and in applications that make
use of bit fields.

The question is what to do with hex literals that are larger than 64 bits.
In other words, what number is represented by (say):

  0x123456789abcdef01

The current SQLite implementation (on the "hex-literal" branch) works by
converting hex literals of 64 bits or less into a signed 64-bit integer.
Hex literals of 65 bits or more are approximated by a nearby floating-point
number.  So, for example, the 65-bit hex literal example above would become
2.09882954794206e+19.  However, this approach creates a discontinuity at
the boundary between 64-bit and 65-bit literals.  The value
0x is equal to -1 but 0x1 is
1.84467440737096e+19. This approach means that all hexadecimal literals are
positive numbers, except literals 0x8000 through
0x which are negative.  There is this range of negative
values smack in the middle of an otherwise uniformly increasing sequence of
positive numbers.  That negative range seems discombobulating.

What Do Other SQL Database Engines Do?

Oracle and PostgreSQL do not support hexadecimal integer literals. MySQL
supports hex literals, but the support appears to be buggy.  In MySQL
5.6.6m9 and 5.5.32 the statement "SELECT 0 + 0x7fff;" yields
9223372036854776000 when the correct answer is 9223372036854775807.  If you
say just "SELECT 0x7fff", without the "0 +" part, then
sqlfiddle gives no answer at all, suggesting that MySQL is crashing.  SQL
Server also supports hex literals, and seems to do so with fewer bugs than
MySQL.  SQL Server appears to silently truncate hex integer to 32 or 64
bits (depending on context), ignoring more significant bits.  Thus in SQL
Server, "SELECT 0x10001" yields just "1".

Possible Solutions:

(1) Keep the current prototype behavior.  Hex literals of 64 bits or less
are converted into twos-complement 64-bit integers, meaning that some
values are negative.  Hex literals of 65 bits or more are converted into a
floating-point approximation.

(2) Declare that the values of hex literals greater than 64 bits are
"undefined" and convert them into random 64-bit integers.  Seriously: use
the random number generator to convert oversized hex literals into
integers, and thereby discourage programmers from using oversized hex
literals.

(3) Convert hex literals of 63-bits or less into integers and convert
64-bit or larger hex literals into a floating-point approximation.  This
means that all hex literals will be non-negative and it eliminates the
discontinuities. But it also means that you can only use the lower 63 bits
of a 64-bit integer as a bitmask.

(4) Silently truncate all hexadecimal literals to 64-bits, like SQL Server
does.

(5) Throw an error when a hex literal larger than 64-bits is seen.  This is
harder than it sounds, since literals can occur in so many different
contexts.  There is the parser, of course.  But also literals can be parsed
when doing string-to-numeric coercion via the CAST operator, or in order to
do arithmetic, or when inserting string values into columns with NUMERIC
affinity.  There are many cases that would need to be coded and tested,
some of which have never before had the capability to throw an error.

(6) Do not support hexadecimal integer literals for casts and affinity
coercions.  Only support hex literals in the SQL parser, and throw errors
for oversized hex literals in that context.

(7) Have the parser convert oversized hex literals into NULL, or throw
errors, and have attempts to coerce oversized hex literals strings into
numeric values fail, thus preventing affinity conversions.

(8) Work like PostgreSQL and Oracle and legacy SQLite and simply do not
support hexadecimal integer literals.

Other ideas?  Comments?  Criticism?
-- 
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] YACC/Bison vs. Lemon vs. standard input

[Gabriel Corneanu]

Hi,

From the old time in school, Bison is a LL parser and I have see that 
Lemon is LR (LALR(1) ).
That's it, Bison reduces as soon as it can while Lemon shifts as much as 
it can.


You have to force Lemon to reduce "prog" rule, i.e. newline should be 
part of another rule:

E.g. modified ladd.y:
-->
start   ::= loop .
loop::= prog NL loop .

prog::= expr(a) prog .   { printf("%d\n", a); }
prog::= .

expr(a) ::= INTEGER(b) .{ a = b; }
expr(a) ::= expr(b) PLUS expr(c) .  { a = b + c; }

<--

Regards,
Gabriel

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