Re: [sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

[Peter Aronson]

On 6/14/2017 5:42 AM, R Smith wrote:


On 2017/06/14 7:08 AM, Wout Mertens wrote:

Is there a way to specify the starting rowid when using autoincrement?

Or should I insert and then remove a row with the id set to one less than
the desired id?


This is quite easy, but first it is helpful to understand the mechanism 
by which SQLite keeps track of the Primary Key Auto-Inc value.


If you define a primary key that is of type INT, and omit the 
AUTOINCREMENT directive, then you will still have a primary key that 
increments if you don't specify the value directly by virtue of primary 
keys being UNIQUE and requires a value, so it's safe to bet if you 
adding a key without specifying the value for it, you intend for it to 
be automatic.
BUT, the next increment value depends on the DB engine guessing what it 
should be based on existing key values (which can cause re-used keys 
that used to exist for now-deleted items).


If you do define the AUTOINCREMENT directive, then SQLite promises to 
always increment the value by one from the last time a value was 
inserted - whether that value has been deleted or changed etc. - i.e. it 
promises to never re-use a key. It achieves this by keeping a table, 
namely the "sqlite_sequence" system-generated table, with references to 
each table using AUTOINCREMENT and its Key based on the last value used 
for the referred table.


You can simply change the values in this reference table to inform the 
next AUTOINCREMENTed value you would like for the specific table-name.


I'm not sure now if references inside this sqlite_sequence table exists 
the moment you create a table with an AUTOINCREMENT key, or only once 
you insert for the first time, but it is easy to check and handle both 
ways.


Taking all this into account, that is why (as another post suggested) 
the equivalent in SQLite for other SQL DB's:

ALTER TABLE myTable AUTOINCREMENT = 5;

would be something like:
UPDATE sqlite_sequence SET seq = 5 WHERE name = 'myTable';


Good luck!
Ryan

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


It is worth noting that there is no entry for a table with an 
autoincrement column in sqlite_sequence until that table has at least 
one row, so you need to perform an INSERT instead of an UPDATE (I don't 
know if sqlite_sequence has a unique constraint on name, so I don't know 
if REPLACE would work for both cases).


sqlite> create table t1 (c1 integer primary key autoincrement,c2 text);

sqlite> select * from sqlite_sequence;


sqlite> insert into t1 (c2) values ('stuff');

sqlite> select * from sqlite_sequence;

t1|1

sqlite>

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

Re: [sqlite] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

[Peter Aronson]

If you're creating GeoPackages with the F.3 RTREE Spatial Indexes 
extension, you do not "wrap" a bounding box. You need to define 5 
functions from SQL/MM -- ST_MinX, ST_MaxX, ST_MinY, ST_MaxY and 
ST_IsEmpty -- that take a geometry blob as input and return (for the 
first four) a floating point number or an integer value of 0 or 1 
(ST_IsEmpty). Now it is possible you might want to create some sort of 
side cache so you don't have to parse the geometry blob four times, but 
that is an implementation issue. The exact text of the triggers is 
specified by the standard and is not optional if you are using the 
extension. And you certainly could not use standard WKB values in the 
geometry columns without completely violating the GeoPackage standard.


Peter

On 4/3/2017 9:26 AM, Stadin, Benjamin wrote:

Hi,

Is there a hook which allows to get notified as soon as it’s save to modify a 
db connection after (or as alternative to) sqlite3_update_hook was triggered?

The background to this question is that I’m trying to prepare a proposal for 
Geopackage. And one of the questions is if it’s possible to avoid Geopackage 
using a wrapper object [1] which is used in combination with user defined 
functions to update the rtree index after a change to the geometry table 
occurred. This object is currently necessary because it wraps another object 
(an envelope / bbox) to be used by the user defined function when the SQL 
update trigger is called.

So my idea is to register an update hook and do the update to the rtree index 
directly after the step(). So this could execute an arbitrary statement, or 
more concrete a statement which would read an envelope column from the geometry 
table where the update occurred and update the rtree table accordingly. This 
would allow those columns to be plain and widely adopted WKB fields instead of 
this Geopackage binary wrapper object.

Regards
Ben

[1] http://www.geopackage.org/spec/#gpb_spec


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



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

Re: [sqlite] Unable to create table, default value of column [ID] is not constant

[Peter Aronson]

But you could use an INSERT  trigger instead.

Also "does contains no sub-queries" ought to "doesn't contain any sub-queries", 
shouldn't it?

Peter 

On Monday, June 20, 2016 5:55 PM, Peter Aronson <pbaron...@att.net> wrote:
 
 

 According to https://www.sqlite.org/lang_createtable.html:

"An explicit DEFAULT clause may specify that the default value is NULL, a 
string constant, a blob constant, a signed-number, or any constant expression 
enclosed in parentheses. A default value may also be one of the special 
case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. For 
the purposes of the DEFAULT clause, an expression is considered constant if it 
does contains no sub-queries, column or table references, bound parameters, or 
string literals enclosed in double-quotes instead of single-quotes."

So that explains why random() works but your hash() does not.

Peter 

    On Monday, June 20, 2016 5:47 PM, Jörgen Hägglund 
<jor...@sirlucifer.mine.nu> wrote:
 
 

 Alright, trying to set the scenario:
* I open/create a database
* I register a custom function called HASH (set to be deterministic), 
tested and working
* I create a bunch of tables (if not exists)
So far, everything works fine.

Then, the troublesome create:
CREATE TABLE IF NOT EXISTS Programmes (
    ID NVARCHAR(64) UNIQUE NOT NULL PRIMARY KEY DEFAULT (HASH(Title, 
Year, EpNo2)),
    Title NVARCHAR(100) NOT NULL,
    Description NVARCHAR(1000),
    Year NVARCHAR(10),
    EpNo1 NVARCHAR(50),
    EpNo2 NVARCHAR(100))

This query raises the following exception:
Unable to create table default value of column [ID] is not constant

Is it not possible to use custom functions this way?
I tried with random() on another table and that works.

Any ideas and explanations would be appreciated.
/Jörgen

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

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


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

Re: [sqlite] Unable to create table, default value of column [ID] is not constant

[Peter Aronson]

According to https://www.sqlite.org/lang_createtable.html:

"An explicit DEFAULT clause may specify that the default value is NULL, a 
string constant, a blob constant, a signed-number, or any constant expression 
enclosed in parentheses. A default value may also be one of the special 
case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. For 
the purposes of the DEFAULT clause, an expression is considered constant if it 
does contains no sub-queries, column or table references, bound parameters, or 
string literals enclosed in double-quotes instead of single-quotes."

So that explains why random() works but your hash() does not.

Peter 

On Monday, June 20, 2016 5:47 PM, Jörgen Hägglund 
 wrote:
 
 

 Alright, trying to set the scenario:
* I open/create a database
* I register a custom function called HASH (set to be deterministic), 
tested and working
* I create a bunch of tables (if not exists)
So far, everything works fine.

Then, the troublesome create:
CREATE TABLE IF NOT EXISTS Programmes (
    ID NVARCHAR(64) UNIQUE NOT NULL PRIMARY KEY DEFAULT (HASH(Title, 
Year, EpNo2)),
    Title NVARCHAR(100) NOT NULL,
    Description NVARCHAR(1000),
    Year NVARCHAR(10),
    EpNo1 NVARCHAR(50),
    EpNo2 NVARCHAR(100))

This query raises the following exception:
Unable to create table default value of column [ID] is not constant

Is it not possible to use custom functions this way?
I tried with random() on another table and that works.

Any ideas and explanations would be appreciated.
/Jörgen

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

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

Re: [sqlite] Trouble coding conditional UNIQUE

[Peter Aronson]

CREATE UNIQUE INDEX bcuif0e ON (b,c) WHERE e = 0;

Assuming you're not using a pre-WHERE clause on Indexes version of SQLite.  
Since unique constraints and unique indexes are functionally identical.


Peter 

On Monday, June 13, 2016 12:11 PM, "Drago, William @ CSG - NARDA-MITEQ" 
 wrote:
 
 

 All,

I am having trouble figuring out how to implement a conditional UNIQUE 
constraint.

Assume the following table:

CREATE TABLE myTable (
A INTEGER PRIMARY KEY,
B TEXT NOT NULL COLLATE NOCASE,
C TEXT NOT NULL COLLATE NOCASE,
D TEXT NOT NULL COLLATE NOCASE,
E INTEGER,
F TEXT COLLATE NOCASE,
G TEXT COLLATE NOCASE,
H TEXT COLLATE NOCASE
);

I need UNIQUE(B, C) only when E=0. I've searched all the usual places, but I 
can't find anything close enough to what I'm doing to be useful.

Thanks,
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

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


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

[sqlite] autoincrement

[Peter Aronson]

The documentation for sqlite3_table_column_metadata C function can be 
found here; https://www.sqlite.org/c3ref/table_column_metadata.html.   
You just call it in turn on each column in a table (you can get the 
column names for a table by using Pragma table_info) and check the value 
of the 9th argument.

Peter

On 4/13/2016 7:58 PM, Igor Korot wrote:
> Hi,
>
> On Wed, Apr 13, 2016 at 1:54 PM, Igor Korot  wrote:
>> Hi, Peter,
>>
>> On Wed, Apr 13, 2016 at 1:16 PM, Peter Aronson  wrote:
>>> There is one limitation to this approach, however.  The entry for an 
>>> autoincrement column in the sqlite_sequence table isn't made until the 
>>> first row is inserted into the table.  If you are also using the C 
>>> interface, you can identify autoincrement columns using 
>>> sqlite3_table_column_metadata.
>> I will test that when I come home from work.
> Yes, that didn't work.
>
> Can you tell me how to use sqlite3_table_column_metadata?
>
> Thank you.
>
>> Thank you.
>>
>>> Peter
>>>
>>>  On Wednesday, April 13, 2016 9:40 AM, Igor Korot >> gmail.com> wrote:
>>>
>>>
>>>
>>>   Hi, Kees,
>>>
>>> On Wed, Apr 13, 2016 at 12:15 PM, Kees Nuyt  wrote:
>>>> On Wed, 13 Apr 2016 10:58:54 -0400, Igor Korot
>>>>  wrote:
>>>>
>>>>> Hi,,
>>>>>
>>>>> On Wed, Apr 13, 2016 at 10:54 AM, J Decker  wrote:
>>>>>> Yes, you can get the create statement from sqlite_master table
>>>>> I was kind of hoping for a simpler solution so that not to
>>>>> parse "CREATE TABLE" statement...
>>>>>
>>>>> Well, I guess I will have to.
>>>> With AUTOINCREMENT, the last autoincremented primary key value
>>>> is tracked in a table called sqlite_sequence.
>>>>
>>>> $ sqlite3 test2.db
>>>> SQLite version 3.8.12 2015-10-07 00:35:18
>>>> Enter ".help" for usage hints.
>>>> sqlite> create table t1 (id INTEGER PRIMARY KEY, tx TEXT);
>>>> sqlite> create table t2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
>>>> tx TEXT);
>>>> sqlite> INSERT INTO t1 (tx) VALUES ('one'),('two');
>>>> sqlite> INSERT INTO t2 (tx) VALUES ('one'),('two');
>>>> sqlite> SELECT name FROM sqlite_master WHERE type='table';
>>>> t1
>>>> t2
>>>> sqlite_sequence
>>>> sqlite> SELECT * FROM sqlite_sequence;
>>>> t2|2
>>>> sqlite>
>>> So I can actually query sqlite_sequence table with the table name in WHERE
>>> clause and it should give me the field that is set to be autoincrement.
>>>
>>> For the reference: https://www.sqlite.org/fileformat2.html (paragraph 
>>> 2.6.2).
>>>
>>> Thank you.
>>>
>>> P.S.: There is a small typo on the page/paragraph I referenced:
>>>
>>> [quote]
>>> The schema for the sqlite_sequence table is:
>>>
>>> CREATE TABLE sqlite_sequence(name,seq);
>>>
>>> There is a single row in the sqlite_sequence table for each ordinary
>>> table that uses AUTOINCREMENT. The name of the table (as it appears in
>>> sqlite_master.name) is in the sqlite_sequence.main field and the
>>> largest INTEGER PRIMARY KEY ever used by that table is in the
>>> sqlite_sequence.seq field.
>>> [/quote]
>>>
>>> That should be changed to:
>>>
>>> [changes}
>>> The name of the table (as it appears in sqlite_master.name) is in the
>>> sqlite_sequence._name_ field
>>> [/changes]
>>>
>>> Could someone please correct that? The changes are between the underscores.
>>>
>>> Thank you.
>>>
>>>> Hope this helps
>>>>
>>>>
>>>>>> On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  
>>>>>> wrote:
>>>>>>> Hi,
>>>>>>> Is it possible to get whether the column is set to autoincrement or not?
>>>>>>>
>>>>>>> PRAGMA table_info() does not give such info...
>>>>>>>
>>>>>>> Thank you.
>>>> --
>>>> Regards,
>>>> Kees Nuyt
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users at mailinglists.sqlite.org
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] autoincrement

[Peter Aronson]

There is one limitation to this approach, however. ?The entry for an 
autoincrement column in the sqlite_sequence table isn't made until the first 
row is inserted into the table. ?If you are also using the C interface, you can 
identify autoincrement columns using?sqlite3_table_column_metadata.

Peter 

On Wednesday, April 13, 2016 9:40 AM, Igor Korot  
wrote:



 Hi, Kees,

On Wed, Apr 13, 2016 at 12:15 PM, Kees Nuyt  wrote:
> On Wed, 13 Apr 2016 10:58:54 -0400, Igor Korot
>  wrote:
>
>> Hi,,
>>
>>On Wed, Apr 13, 2016 at 10:54 AM, J Decker  wrote:
>>> Yes, you can get the create statement from sqlite_master table
>>
>> I was kind of hoping for a simpler solution so that not to
>> parse "CREATE TABLE" statement...
>>
>> Well, I guess I will have to.
>
> With AUTOINCREMENT, the last autoincremented primary key value
> is tracked in a table called sqlite_sequence.
>
> $ sqlite3 test2.db
> SQLite version 3.8.12 2015-10-07 00:35:18
> Enter ".help" for usage hints.
> sqlite> create table t1 (id INTEGER PRIMARY KEY, tx TEXT);
> sqlite> create table t2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
> tx TEXT);
> sqlite> INSERT INTO t1 (tx) VALUES ('one'),('two');
> sqlite> INSERT INTO t2 (tx) VALUES ('one'),('two');
> sqlite> SELECT name FROM sqlite_master WHERE type='table';
> t1
> t2
> sqlite_sequence
> sqlite> SELECT * FROM sqlite_sequence;
> t2|2
> sqlite>

So I can actually query sqlite_sequence table with the table name in WHERE
clause and it should give me the field that is set to be autoincrement.

For the reference: https://www.sqlite.org/fileformat2.html (paragraph 2.6.2).

Thank you.

P.S.: There is a small typo on the page/paragraph I referenced:

[quote]
The schema for the sqlite_sequence table is:

CREATE TABLE sqlite_sequence(name,seq);

There is a single row in the sqlite_sequence table for each ordinary
table that uses AUTOINCREMENT. The name of the table (as it appears in
sqlite_master.name) is in the sqlite_sequence.main field and the
largest INTEGER PRIMARY KEY ever used by that table is in the
sqlite_sequence.seq field.
[/quote]

That should be changed to:

[changes}
The name of the table (as it appears in sqlite_master.name) is in the
sqlite_sequence._name_ field
[/changes]

Could someone please correct that? The changes are between the underscores.

Thank you.

>
> Hope this helps
>
>
>>>
>>> On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  wrote:
 Hi,
 Is it possible to get whether the column is set to autoincrement or not?

 PRAGMA table_info() does not give such info...

 Thank you.
>
> --
> Regards,
> Kees Nuyt
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing

[Peter Aronson]

If this change also applies to?sqlite3_table_column_metadata()'s behavior with 
views, then I would consider it a real negative. ?Currently I can get my 
declaration types using that call, which are hints on how to interpret the 
values in the databases I create, from simple views which is convenient. 
?Otherwise I have to use a number of less exact fallback methods to interpret 
the values (such as reading the first row, or running queries against the 
column, etc.).
Peter 

On Tuesday, April 5, 2016 10:19 AM, Richard Hipp  wrote:



 On 4/4/16, Mike Bayer  wrote:
> The "type" column in PRAGMA table_info() is now a blank string when the
> target object is a view in 3.12.0. In 3.11.0 and prior versions, the
> typing information is returned,
>

This could easily be considered a bug fix rather than a regression.
Please explain why you think it is important to know the "type" of a
column in a view?

There are further inconsistencies here.? Example:

? ? CREATE TABLE t1(x INTEGER);
? ? CREATE VIEW v1 AS SELECT x FROM t1;
? ? CREATE VIEW v2(x) AS SELECT x FROM t1;
? ? PRAGMA table_info('v1');
? ? PRAGMA table_info('v2');

As of version 3.12.0, the two pragma's give the same answer. but in
version 3.11.0, they were different.? Which of the two answers
returned by 3.11.0 is correct?

Or, consider this situation:

? ? CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL);
? ? CREATE VIEW v3 AS SELECT w+x+y+z FROM t2;

What should "PRAGMA table_info('v3')" report as the column type?

It seems to me that the most consistent answer is that the "type" of
columns in a VIEW should always be an empty string.

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

[sqlite] A question about sqlite3_get_auxdata()/sqlite3_set_auxdata().

[Peter Aronson]

I had written code that I thought should cache the repeated user function 
arguments used in a SQL statement generally like so:
SELECT a.id FROM a,b WHERE MyFunc(a.data,b.data);
But to my surprise, even though I was saving my (parsed and processed) 
arguments using at the end of my function implementation sqlite3_set_auxdata(), 
and checking for them at the top of my function implementation with 
sqlite3_get_auxdata(), sqlite3_get_auxdata() was always returning NULL for both 
arguments. ?This was a surprise as I expected one of them (but not both of 
course) would be successfully cached. ?So, I looked into the documentation, and 
I found this line on the page for sqlite3_get_auxdata()/sqlite3_set_auxdata() 
page (https://www.sqlite.org/c3ref/get_auxdata.html):
"In practice, metadata is preserved between function calls for function 
parameters that are compile-time constants, including literal values and 
parameters and expressions composed from the same."
So, why *isn't* metadata preserved between function calls for data values? 
?There's a lot more possible savings in pre-processing a blob containing an 
image or a geometry or a large text string containing XML than pre-processing a 
constant. ?Presumably, if someone goes through the effort of calling 
sqlite3_set_auxdata() that meant it to be kept.
Peter

[sqlite] SQLite crashing

[Peter Aronson]

How are you building the DLL and the executable?  Are they both 
including a copy of sqlite3.obj?  Or are you using a sqlite3.dll?

Peter

On 1/25/2016 9:16 AM, Igor Korot wrote:
> Hi, Peter,
>
> On Mon, Jan 25, 2016 at 10:50 AM, Peter Aronson  wrote:
>> Igor,
>>
>> You can't safely pass a SQLite handle between different SQL DLLs that way if
>> they're both built with their own copy of the amalgamation (or link to
>> things built with different copies). SQLite uses a handful of global
>> variables, but each DLL has its own copy of each of these global variables
>> and they can and will have different values, which can mess things up.  I
>> ran into a version of this problem when I tried to load a 2nd DLL built with
>> its own copy of the sqlite3.c amalgamation.  I fixed that by exposing the
>> SQLite3 entrypoints in the first DLL and linking the second DLL against it
>> so there was only one copy of the amalgamation used for that SQLite3 handle.
> The SQLite is built only once and with just one version of the code.
>
> Consider following pseudo-code:
>
> In DLL:
>
> BOOL APIENTRY DLLMain()
> {
> }
>
> extern "C" __declspec(dllexport) Database *CreateObject(Database *db)
> {
>  db = new SQLiteDatabase();
>  db->Connect();
>  return db;
> }
>
> In the main application:
>
> mainframe.h:
>
> class MainFrame
> {
> public:
>   MainFrame();
>   ~MainFrame();
>   void ConnectToDb();
> private:
>   Database *m_db;
> };
>
> mainframe.cpp:
>
> void MainFrame::ConnectToDb()
> {
>  Database *db = NULL;
>  LoadLibrary();
>  func = GetProcAddress();
>  m_db = func( db );
> }
>
> MainFrame::~MainFrame()
> {
>  delete m_db;  // this is where the crash happens
> }
>
> The pointer address are the same in DLL and main application MainFrame class.
> And as I said the crash occurs when it tries to acquire the mutex lock.
>
> Thank you.
>
>> Peter
>>
>>
>>
>>
>> On 1/24/2016 10:18 PM, Igor Korot wrote:
>>> Hi, ALL,
>>> I have a strange problem.
>>>
>>> I am trying to use sqlite in my program. It has a main application and
>>> couplef DLLs.
>>>
>>> I am getting the connection in one of the DLL, then the pointer is passed
>>> up
>>> to the main application.
>>>
>>> Upon exiting from the application I'm trying to close the connection and
>>> delete all the memory.
>>>
>>> Unfortunately upon exiting the application it crashes inside
>>> sqlite3_mutex_enter().
>>> The comment above the function says:
>>>
>>> [quote]
>>> /*
>>> ** Obtain the mutex p. If some other thread already has the mutex, block
>>> ** until it can be obtained.
>>> */
>>> [/quote]
>>>
>>> The DLL does not start any threads, in fact the application will be 1
>>> thread only.
>>> So is there some compile-time switch I should use to mitigate the issue?
>>>
>>> Moreover I don't understand why am I getting the assertion - there is no
>>> MT
>>> involved.
>>>
>>> Can someone shed some lights?
>>>
>>> Thank you.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQLite crashing

[Peter Aronson]

Igor,

You can't safely pass a SQLite handle between different SQL DLLs that 
way if they're both built with their own copy of the amalgamation (or 
link to things built with different copies). SQLite uses a handful of 
global variables, but each DLL has its own copy of each of these global 
variables and they can and will have different values, which can mess 
things up.  I ran into a version of this problem when I tried to load a 
2nd DLL built with its own copy of the sqlite3.c amalgamation.  I fixed 
that by exposing the SQLite3 entrypoints in the first DLL and linking 
the second DLL against it so there was only one copy of the amalgamation 
used for that SQLite3 handle.

Peter



On 1/24/2016 10:18 PM, Igor Korot wrote:
> Hi, ALL,
> I have a strange problem.
>
> I am trying to use sqlite in my program. It has a main application and
> couplef DLLs.
>
> I am getting the connection in one of the DLL, then the pointer is passed up
> to the main application.
>
> Upon exiting from the application I'm trying to close the connection and
> delete all the memory.
>
> Unfortunately upon exiting the application it crashes inside
> sqlite3_mutex_enter().
> The comment above the function says:
>
> [quote]
> /*
> ** Obtain the mutex p. If some other thread already has the mutex, block
> ** until it can be obtained.
> */
> [/quote]
>
> The DLL does not start any threads, in fact the application will be 1
> thread only.
> So is there some compile-time switch I should use to mitigate the issue?
>
> Moreover I don't understand why am I getting the assertion - there is no MT
> involved.
>
> Can someone shed some lights?
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQLite version 3.8.12 enters testing

[Peter Aronson]

Currently if you have a check constraint with a user-defined function that is 
not defined in the current environment, you can not execute any SQL statements 
in that database -- you get the same error you get with features not supported 
in the current release. ?I suspect the same thing would happen here.

Peter 


 On Wednesday, October 7, 2015 1:27 PM, Simon Slavin  wrote:




On 7 Oct 2015, at 8:42pm, Richard Hipp  wrote:

> New documentation covering indexes on expressions has been added.
> Please let me know if you think more is needed.

I tentatively suggest that modifications be made to the text describing



and



.? However it may be that such changes would just lengthen the explanation 
without adding anything useful, and that any programmer who tries out those 
PRAGMAs with the new indexes won't have any trouble understanding what they see.

It also might be useful to produce a -2 in the second output column for 
index_xinfo, for columns which are calculations.

I wonder what happens if an index is defined which uses an application-defined 
functions, and a later attempt is made to use the database without that 
function defined.? I can see an argument for returning an error when any 
relevant SELECT is attempted.? But I can also see an argument for returning an 
error only when an attempt is made to INSERT or UPDATE into the indexed table.

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

[sqlite] SQLite version 3.8.12 enters testing

[Peter Aronson]

I assume, like with similar enhancements, a database created at SQLite 3.8.12 
with an expression index will be unreadable with earlier versions of SQLite? 
?As, for instance, a database created at 3.8.11 with an index with a where 
clauses causes SQLite 3.715.2 to fail when trying execute any SQL statements 
with "Error: malformed database schema (index name) - near "where": syntax 
error?

Peter 


 On Wednesday, October 7, 2015 12:42 PM, Richard Hipp  
wrote:



 On 10/7/15, Zsb?n Ambrus  wrote:
>
> I have concerns with the [indexes on expressions] documentation.
>

New documentation covering indexes on expressions has been added.
Please let me know if you think more is needed.
-- 
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] bug in PRAGMA ignore_check_constraints?

[Peter Aronson]

Meh. ?Formatting:
sqlite> create table cc (c1 integer not null,c2 integer 
check(typeof(c2)<>'null'));
sqlite> insert into cc values (null,null);
Error: NOT NULL constraint failed: cc.c1
sqlite> insert into cc values (1,null);
Error: CHECK constraint failed: cc
sqlite> insert into cc values (1,1);
sqlite> pragma ignore_check_constraints = yes;
sqlite> insert into cc values (1,null);
sqlite>



 On Thursday, September 10, 2015 6:11 PM, Peter Aronson  wrote:



 That would be my assumption. ?And experimentation seems to back it up (at 
least for NOT NULL):
sqlite> create table cc (c1 integer not null,c2 integer 
check(typeof(c2)<>'null'));sqlite> insert into cc values (null,null);Error: NOT 
NULL constraint failed: cc.c1sqlite> insert into cc values (1,null);Error: 
CHECK constraint failed: ccsqlite> insert into cc values (1,1);
sqlite> pragma ignore_check_constraints = yes;sqlite> insert into cc values 
(1,null);sqlite>
Peter 


? ? On Thursday, September 10, 2015 6:02 PM, Simon Slavin  wrote:
? 



On 11 Sep 2015, at 1:17am, Peter Aronson  wrote:

> I do not believe NOT NULL is a CHECK constraint, though you could use gender 
> TEXT CHECK(typeof(gender) <> 'null') is and would work much the same way, 
> though possibly with less efficiency.

Looking at

<https://www.sqlite.org/syntax/column-constraint.html>

maybe the 'constraints' that the documentation refers to are the ones 
specifically declared using CHECK in the table definition.? Perhaps NOT NULL 
and UNIQUE don't count.

Simon.


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

[sqlite] bug in PRAGMA ignore_check_constraints?

[Peter Aronson]

That would be my assumption. ?And experimentation seems to back it up (at least 
for NOT NULL):
sqlite> create table cc (c1 integer not null,c2 integer 
check(typeof(c2)<>'null'));sqlite> insert into cc values (null,null);Error: NOT 
NULL constraint failed: cc.c1sqlite> insert into cc values (1,null);Error: 
CHECK constraint failed: ccsqlite> insert into cc values (1,1);
sqlite> pragma ignore_check_constraints = yes;sqlite> insert into cc values 
(1,null);sqlite>
Peter 


 On Thursday, September 10, 2015 6:02 PM, Simon Slavin  wrote:




On 11 Sep 2015, at 1:17am, Peter Aronson  wrote:

> I do not believe NOT NULL is a CHECK constraint, though you could use gender 
> TEXT CHECK(typeof(gender) <> 'null') is and would work much the same way, 
> though possibly with less efficiency.

Looking at

<https://www.sqlite.org/syntax/column-constraint.html>

maybe the 'constraints' that the documentation refers to are the ones 
specifically declared using CHECK in the table definition.? Perhaps NOT NULL 
and UNIQUE don't count.

Simon.

[sqlite] bug in PRAGMA ignore_check_constraints?

[Peter Aronson]

I do not believe NOT NULL is a CHECK constraint, though you could use gender 
TEXT CHECK(typeof(gender) <> 'null') is and would work much the same way, 
though possibly with less?efficiency.

Peter? 


 On Thursday, September 10, 2015 4:48 PM, Roman Fleysher  wrote:



 Dear SQLiters,

I am trying to temporarily disable CHECK constraint given in columns of table 
definition. As far as I understand, 

PRAGMA ignore_check_constraints='yes';

should do it. However this example demonstrates that it is not:

CREATE TABLE subject(
? subjectID? INT,
? gender? ? TEXT NOT NULL
);


CREATE TABLE input(
? subjectID? INT,
? gender? ? TEXT
);

INSERT INTO input(subjectID, gender) VALUES (1, 'female');
INSERT INTO input(subjectID, gender) VALUES (2, 'male');
INSERT INTO input(subjectID) VALUES (3);

PRAGMA ignore_check_constraints='yes';
INSERT INTO subject (rowid) SELECT rowid FROM input;

Error: NOT NULL constraint failed: subject.gender

I am using SQLite 3.8.11.1. Am I misusing the PRAGMA?

Thank you,

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

[sqlite] order by not working in combination with random()

[Peter Aronson]

If you're talking about Database Browser for SQLite (formally named SQLite 
Database Browser), at least at one time (version 3.5) it executed each query 
twice, apparently the first time to figure out the return types, and the second 
time to display the results (this caused me a certain amount of trouble until I 
realized the initialization function for my extension was being run twice by 
SELECT load_extension, and I fixed the function to be OK with being run 
multiple times). ?It wouldn't explain a 3x+ slowdown, but would explain a 2x 
anyway.
Peter 


 On Friday, August 28, 2015 7:45 AM, "Rousselot, Richard A" 
 wrote:



 I have noticed that SQLite Query Browser is running slower than other IDEs, 
including SQLitespeed, for some reason.? Even when each IDE is set to using 
similar versions of the SQLite3.dll.? We had a recursive query in SQB take 6 
min, on other IDEs it would be less than 2 min.

My $0.02

[sqlite] CSV excel import

[Peter Aronson]

Actually there exists an open source tool that convert Excel data into SQLite 
tables -- the ogr2ogr command line tool of OSGeo's GDAL library 
(http://www.gdal.org/). ?You do need a version of GDAL built with the SQLite 
and XLSX and/or XLS drivers. ?Actually, if you don't mind adding SpatiaLite 
into the mix, you can make your spreadsheets show up a virtual tables using the 
VirtualOGR module.
Peter 


 On Thursday, July 30, 2015 11:37 AM, Sylvain Pointeau  wrote:



 On Thu, Jul 30, 2015 at 8:32 PM, Bernardo Sulzbach <
mafagafogigante at gmail.com> wrote:

> > My point is that I have seen so many emails regarding this incorrect csv
> import, that it would be so easy for us if it just simply works in the CLI
> and delivered in standard in the sqlite3 executable.
>
> I don't think I understand what you mean by this. Also, most of the
> problems seems to arise from the fact that CSV is just too weakly
> specified. See how better defined JSON is and how it solves a lot of
> problems (not suggesting JSON here).
>

JSON is not an option when we are working with business people. Excel is
their only? tool to review and modify data so we need to import and export
CSV. Honestly direct excel import/export would be even better but CSV is
fine too and largely simpler.

for instance, H2 worked wonderfully well for all excel import. why is it
unreasonable to ask for the same in sqlite?
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] create temporary virtual table

[Peter Aronson]

Most types of virtual table can be created as temporary tables with no trouble 
by specifying the temp database when you create them. ?IE:
CREATE VIRTUAL TABLE temp.myrtree USING RTREE (id,minx,maxx);
However, the virtual table method xDestroy does not get called for a virtual 
table in the temp database unless an explicit DROP TABLE statement is executed 
on it; simply exiting and allowing SQLite to clean up the temp database won't 
do it. ?However, for many Virtual Tables, the xDestroy method and the 
xDisconnect methods are the same, and SQLite will call the xDisconnect method 
on a virtual table in the temp database before exiting. ?So, unless the virtual 
table has to clean up additional metadata or if it deletes or releases some 
external resources, it can probably be safely created in the temp database and 
cleaned up by SQLite on exit. ?Of course, if you explicitly DROP it when you 
are done with it, the xDestroy methods will be called and all should be as 
normal.
Peter 


 On Monday, July 20, 2015 3:34 PM, Andy Rahn  wrote:



 I see there is no way to create a temporary virtual table.

One idea I had was to create a second, in-memory db and attach that, then
create the virtual table over there.

Currently I have it working where as soon as I open the db, I use
sqlite_master to find any left over virtual tables from before and drop
them.

Any other ideas?

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

[sqlite] Error: no such column: When column exists!

[Peter Aronson]

When I look at your queries table in sqlite3 in a windows command prompt, the 
failing SQL has a non-ASCII character instead of a semicolon.
Peter 


 On Wednesday, July 15, 2015 12:53 PM, Jason H  wrote:



 So yes, I was missing 'S's, but that is only because I've been hacking at the 
tables to get something to change.

So the query you gave me works, however, when I put it all on the same line, it 
is identical to my query which does not. To prove this, I updated the file on 
github with a query table.
Just select from it, and paste it into the shell:

sqlite> select * from queries;
1|select * from PatientTreatmentNotes ptn join PatientTreatmentNotesSteps pts 
ON pts.TreatmentNoteID = ptn.TreatmentNoteID ;|works
2|select * from PatientTreatmentNotes ptn join PatientTreatmentNotesSteps pts 
ON pts.TreatmentNoteID = ptn.TreatmentNoteID?;|error
sqlite> select * from PatientTreatmentNotes ptn join PatientTreatmentNotesSteps 
pts ON pts.TreatmentNoteID = ptn.TreatmentNoteID ;
sqlite> select * from PatientTreatmentNotes ptn join PatientTreatmentNotesSteps 
pts ON pts.TreatmentNoteID = ptn.TreatmentNoteID?;
Error: no such column: ptn.TreatmentNoteID

I think this is a gremlin. I'm interested in knowing your results.

And thanks to everyone helping with this!

> Sent: Wednesday, July 15, 2015 at 2:21 PM
> From: "Richard Hipp" 
> To: "General Discussion of SQLite Database"  mailinglists.sqlite.org>
> Subject: Re: [sqlite] Error: no such column: When column exists!
>
> On 7/15/15, Jason H  wrote:
> > Since attachments are not supported,
> > https://github.com/jhihn/files/blob/master/no_such_column.sqlite3
> >
> 
> The table names were both misspelled in your original query.? After I
> fixed that, everything seems to work.? I tested with 3.7.8, 3.7.17,
> 3.8.0, 3.8.7, and trunk.
> 
> select *
>? from PatientTreatmentNotes ptn
>? ? ? ? ? ? join PatientTreatmentNotesSteps pts
>? ? ? ? ? ? ? ON pts.TreatmentNoteID = ptn.TreatmentNoteID ;
> -- 
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] AUTOINC vs. UUIDs

[Peter Aronson]

Never mind about the 122 vs 124 bit thing. ?I could have swore I read that... 


 On Wednesday, May 20, 2015 4:52 PM, Peter Aronson  
wrote:



 Now you're just getting silly. ?What if the application sets all rowids, 
everywhere to 1? ?The fact is, the chance of collision on a UUID is pretty 
astronomically low as long as a decent source of entropy is used 
(see?http://en.wikipedia.org/wiki/Universally_unique_identifier#Random_UUID_probability_of_duplicates).
 ?Yes, some application might not generate proper UUIDs, but that's true with 
any scheme that needs to coordinate disconnected data editing or generation on 
multiple machines. ?There are lots of applications out there that use UUIDs 
pretty successfully. ?Are they a perfect solution? ?Of course not, but then, 
what is? ?But for that particular problem domain they have proved viable.
Mind you, the original article linked was guilty of gross over-generalization, 
and got the amount of randomess in a UUID wrong (it's 122 bits, not 124).
Peter? ?


? ? On Wednesday, May 20, 2015 4:38 PM, Keith Medcalf  
wrote:
? 



On the other hand, perhaps both tablets implement the same PRNG with the same 
seed.? You will then have the same HUID's generated on both and have the exact 
same problem.? Addressing the problem using Hope and Pray is not a very robust 
solution.? It would be much better to solve the problem using a deterministic 
solution than a prayer based one.? Even though God hears all prayers, the 
answer might be no ...

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Steven M. McNeese
> Sent: Wednesday, 20 May, 2015 16:09
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] AUTOINC vs. UUIDs
> 
> You misunderstood. If an application running on a tablet for 2 users add
> rows to a local database with an auto increment key, each would get a key
> based on their database an let's say for grins they both start with a new
> clean database. User 1 gets key 1 and user 2 gets key 1. Now they push
> their data to a server each with key 1 but different data. That doesn't
> work. Both records needs to be added to the server database - a collection
> of all the data collected on the client tablets.
> 
> Sent from my iPhone
> 
> > On May 20, 2015, at 4:38 PM, Jean-Christophe Deschamps
>  wrote:
> >
> > At 23:24 20/05/2015, you wrote:
> >
> >> On Wed, May 20, 2015 at 2:20 PM, Steven M. McNeese
> >>  wrote:
> >> > Often times people will use GUIDs as primary keys when different
> systems need to generate
> >> > rows and then merge together. Like an application that works with
> offline clients that push the
> >> > data to a server when the connect. However there are other ways of
> accomplishing the same thing.
> >>
> >> For curiosity - Is there a site/blog post somewhere
> >> enumerating/listing these other ways ?
> >
> > I don't know, but let's say your rowids range from -9223372036854775807
> to 9223372036854775807, that's 18446744073709551614 possible rowids.
> >
> > Imagine that in 50 years, the total population on Earth will grow to
> (say) 50 billion people (I hope it won't!).
> >
> > 18 446 744 073 709 551 614 / 50 000 000 000 = 368 934 881.474 191 032 28
> >
> > That leaves you the possibility to assign a unique identifier to every
> potential client on the planet (and a big one) in nearly 369 million
> servers concurrently without the faintest risk of collision.
> >
> > At this rate, you may limit rowids to only positive integers ... and
> hire a large army of telemarketers.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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



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

[sqlite] AUTOINC vs. UUIDs

[Peter Aronson]

Now you're just getting silly. ?What if the application sets all rowids, 
everywhere to 1? ?The fact is, the chance of collision on a UUID is pretty 
astronomically low as long as a decent source of entropy is used 
(see?http://en.wikipedia.org/wiki/Universally_unique_identifier#Random_UUID_probability_of_duplicates).
 ?Yes, some application might not generate proper UUIDs, but that's true with 
any scheme that needs to coordinate disconnected data editing or generation on 
multiple machines. ?There are lots of applications out there that use UUIDs 
pretty successfully. ?Are they a perfect solution? ?Of course not, but then, 
what is? ?But for that particular problem domain they have proved viable.
Mind you, the original article linked was guilty of gross over-generalization, 
and got the amount of randomess in a UUID wrong (it's 122 bits, not 124).
Peter? ?


 On Wednesday, May 20, 2015 4:38 PM, Keith Medcalf  
wrote:




On the other hand, perhaps both tablets implement the same PRNG with the same 
seed.? You will then have the same HUID's generated on both and have the exact 
same problem.? Addressing the problem using Hope and Pray is not a very robust 
solution.? It would be much better to solve the problem using a deterministic 
solution than a prayer based one.? Even though God hears all prayers, the 
answer might be no ...

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Steven M. McNeese
> Sent: Wednesday, 20 May, 2015 16:09
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] AUTOINC vs. UUIDs
> 
> You misunderstood. If an application running on a tablet for 2 users add
> rows to a local database with an auto increment key, each would get a key
> based on their database an let's say for grins they both start with a new
> clean database. User 1 gets key 1 and user 2 gets key 1. Now they push
> their data to a server each with key 1 but different data. That doesn't
> work. Both records needs to be added to the server database - a collection
> of all the data collected on the client tablets.
> 
> Sent from my iPhone
> 
> > On May 20, 2015, at 4:38 PM, Jean-Christophe Deschamps
>  wrote:
> >
> > At 23:24 20/05/2015, you wrote:
> >
> >> On Wed, May 20, 2015 at 2:20 PM, Steven M. McNeese
> >>  wrote:
> >> > Often times people will use GUIDs as primary keys when different
> systems need to generate
> >> > rows and then merge together. Like an application that works with
> offline clients that push the
> >> > data to a server when the connect. However there are other ways of
> accomplishing the same thing.
> >>
> >> For curiosity - Is there a site/blog post somewhere
> >> enumerating/listing these other ways ?
> >
> > I don't know, but let's say your rowids range from -9223372036854775807
> to 9223372036854775807, that's 18446744073709551614 possible rowids.
> >
> > Imagine that in 50 years, the total population on Earth will grow to
> (say) 50 billion people (I hope it won't!).
> >
> > 18 446 744 073 709 551 614 / 50 000 000 000 = 368 934 881.474 191 032 28
> >
> > That leaves you the possibility to assign a unique identifier to every
> potential client on the planet (and a big one) in nearly 369 million
> servers concurrently without the faintest risk of collision.
> >
> > At this rate, you may limit rowids to only positive integers ... and
> hire a large army of telemarketers.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] Fwd: bulk upload from Excel

[Peter Aronson]

I don't know what happened to my links in the previous email. ?Again:
http://www.gdal.org/ogr_sql_sqlite.html
http://www.gdal.org/drv_sqlite.html
?


 On Monday, May 11, 2015 12:01 PM, Peter Aronson  
wrote:



 It's a little indirect, but starting with GDAL 1.10, if you have a version oft 
he GDAL dynamic library built with both SQLite support and XLS and/or XLSX 
support (depending on which you need), you can load the GDAL library as a 
SQLite extension using the load_extension SQL function, and then create a 
virtual table of an Excel spread-sheet using the VirtualOGR module (see 
http://www.gdal.org/drv_sqlite.html?and?http://www.gdal.org/ogr_sql_sqlite.html).
 ?There might be a simpler approach, of course.
Peter?


? ? On Monday, May 11, 2015 11:08 AM, maksood alam <786maksood at gmail.com> 
wrote:
? 


 -- Forwarded message --
From: "maksood alam" <786maks...@gmail.com>
Date: May 11, 2015 11:21 AM
Subject: bulk upload from Excel
To: 
Cc:

Hi Team,

Do we have any option like below for the bulk upload.

INSERT INTO tbl_test1 SELECT * FROM [asd$] IN '" &
ThisWorkbook.FullName & "' 'Excel 8.0;'"
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] Fwd: bulk upload from Excel

[Peter Aronson]

It's a little indirect, but starting with GDAL 1.10, if you have a version oft 
he GDAL dynamic library built with both SQLite support and XLS and/or XLSX 
support (depending on which you need), you can load the GDAL library as a 
SQLite extension using the load_extension SQL function, and then create a 
virtual table of an Excel spread-sheet using the VirtualOGR module (see 
http://www.gdal.org/drv_sqlite.html?and?http://www.gdal.org/ogr_sql_sqlite.html).
 ?There might be a simpler approach, of course.
Peter?


 On Monday, May 11, 2015 11:08 AM, maksood alam <786maksood at gmail.com> 
wrote:



 -- Forwarded message --
From: "maksood alam" <786maks...@gmail.com>
Date: May 11, 2015 11:21 AM
Subject: bulk upload from Excel
To: 
Cc:

Hi Team,

Do we have any option like below for the bulk upload.

INSERT INTO tbl_test1 SELECT * FROM [asd$] IN '" &
ThisWorkbook.FullName & "' 'Excel 8.0;'"
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Two Threads Share In-Memory Table

[Peter Aronson]

See https://www.sqlite.org/sharedcache.html and in particular, section 6 
at the bottom.

Peter

On 5/10/2015 10:42 AM, Joe Pasquariello wrote:
> Hello,
>
> One thread of our Windows application is an HTTP server reading data 
> from an SQLite database for display by web clients. Some tables are 
> updated infrequently (minutes), but one contains current status of 
> numerous machines, and it is written/read relatively frequently. Data 
> is written to the DB by a dedicated writer thread. The 
> frequently-written table is temporary  in the sense that it is 
> dropped/created at run-time, and the data doesn't need to persist 
> between executions.
>
> My question is whether there is a way for this table to be in memory. 
> Each thread has its own connection, which I understand to be 
> necessary. Am I correct that this means they can't share an in-memory 
> DB or an in-memory TEMP table, because those are restricted to a 
> single connection? It's very convenient to use SQLite for this purpose 
> because each client may be interested in a different row or set of 
> rows from the "real-time" table, and it seems a shame for the shared 
> data to have to be on disk and not simply in memory.
>
> Thanks,
>
> Joe
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Determine type of prepared statement via C Interface?

[Peter Aronson]

Well, there's sqlite3_stmt_readonly which appears to do pretty much what 
you're asking for: https://www.sqlite.org/c3ref/stmt_readonly.html.  Or, 
if you want more detailed control, there's the whole authorizer 
interface: https://www.sqlite.org/c3ref/set_authorizer.html.

Peter

On 5/8/2015 10:01 AM, Stephen Broberg wrote:
> Using versions 3.8.5 and 3.7.7.1:
>
> Is there something in the SQLite C API that will tell what type of statement 
> a prepared sqlite3_stmt is (e.g., insert, update, delete, select, etc.).  
> Specifically, I?d like to know whether a statement that either has run or is 
> about to be run (via sqlite3_step) is a read-only (select) or write (pretty 
> much everything else) operation.  I?m implementing a reader/writer lock 
> mechanism around our access to the sqlite database, and as the consumer of 
> the interface will need to specify the lock type when they construct a query 
> object (save point objects would always be write, of course), I?d want to 
> runtime sanity-check their choice after preparing the statement, and log or 
> throw an error if they flagged a non-select statement as a reader.  Perhaps 
> it?s possible to interrogate the connection object after a statement runs to 
> determine if the database was changed?
>
> A possible approach might be to use sqlite3_update_hook or 
> sqlite3_commit_hook to identify the statements that modify the db.  Does 
> sqlite3_commit_hook fire when a statement is implicitly committed (e.g., an 
> update executed outside the context of a savepoint when in autocommit mode)?  
> Another option might be to use two database connections, one read-only, the 
> other normal, but the design of our current system makes this option a major 
> redesign (this is legacy code I?ve inherited.  If I had my druthers, I?d be 
> using a dedicated sqlite connection per thread and running in multi-thread 
> mode, but right now that?s too great a refactoring task).
>
> Or am I doing this wrong?  Our multithreaded app produces a lot of 
> SQLITE_BUSY results when concurrency increases; serializing our transactions, 
> both read & write, globally solved this problem and increased throughput, but 
> I?m interested in moving beyond a simple mutex to a read/write one.  We?re 
> running Serialized mode, but is there some other sqlite configuration that 
> automatically does what I?m trying to accomplish: serialize all write 
> operations and save points for all threads, allow selects to run concurrently?
>
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug with locking/retying

[Peter Aronson]

If you look here: http://beets.radbox.org/blog/, you can see the blog 
entry is dated August 24th, 2012.

Peter

On 5/2/2015 5:18 PM, Simon Slavin wrote:
> In searching for something else I came across this:
>
> 
>
> I don't like the fact that it's undated.  For all I know this is about a 
> three year old bug which has long since been patched.
>
> I understand the description, but not what SQLite does internally, and I 
> don't know whether it was ever reported to the SQLite dev team.  Would 
> someone like to take a look ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] List duplication

[Peter Aronson]

I've seen it too.  All of the duplicate messages appear to have been sent to 
both sqlite-dev at mailinglists.sqlite.org and sqlite-dev at sqlite.org or to 
both sqlite-usersmailinglists.sqlite.org and sqlite-users at sqlite.org.

Peter


On Fri, 2/27/15, R.Smith  wrote:

 Subject: [sqlite] List duplication
 To: sqlite-users at mailinglists.sqlite.org
 Date: Friday, February 27, 2015, 12:38 PM

 Hi all,

 Just a quick question, I've been getting duplicated mails
 via the forum. 
 It seems very random, roughly 1 in 4 mails I receive in
 duplicate. Has 
 anyone else been getting duplicate mails?

 It's not a biggie, I just delete the duplicates. Also, it
 might be my 
 email settings or setup being the culprit - if anyone has an
 idea what I 
 might check, it will be most helpful - thanks.

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

Re: [sqlite] binding multiple values in a query

[Peter Aronson]

You can't do that in standard SQLite -- you can only bind single values.  
However, if you download the full source package (as opposed to the 
amalgamation) of SQLite, there are a pair of files under src, test_intarray.c 
and test_intarray.h, which implement a virtual table that would let you do 
that, as long as the array you wanted to bind was made up of integers.  
Although it wouldn't be hard to make a version that worked on floats or strings.

Peter


On Wednesday, February 11, 2015 3:46 PM, Jono Poff 
 wrote:
 

>
>
>Hi,
>
>I wonder if anybody could give me a simple example in C to bind an array 
>of values to a prepared statement?
>
>The effect I'm looking for is 
>
>sqlite3_stmt* stmt = Compile(db, "select * in Things where thing1 in 
>(  );");
>
>sqlite3_bind_???(stmt,0 ,);
>
>
>Cheers,
>Jono
>
>
>
>
>-- 
>
>--
>This email, including any attachments, is only for the intended recipient. 
>It is subject to copyright, is confidential and may be the subject of legal 
>or other privilege, none of which is waived or lost by reason of this 
>transmission.
>If you are not an intended recipient, you may not use, disseminate, 
>distribute or reproduce such email, any attachments, or any part thereof. 
>If you have received a message in error, please notify the sender 
>immediately and erase all copies of the message and any attachments.
>Unfortunately, we cannot warrant that the email has not been altered or 
>corrupted during transmission nor can we guarantee that any email or any 
>attachments are free from computer viruses or other conditions which may 
>damage or interfere with recipient data, hardware or software. The 
>recipient relies upon its own procedures and assumes all risk of use and of 
>opening any attachments.
>--
>___
>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] Porting SQLite to another operating system (not supported out of the box)

[Peter Aronson]

You could add VFS creation if you ever do a revised edition (along with a 
virtual table example that actually used xBestIndex and xFilter...).


On Tuesday, February 10, 2015 9:58 AM, Jay Kreibich  wrote:
 

>
>
>
>
>No, it does not.  Using SQLite covers Virtual Tables in great detail, but not 
>VFS systems.  They’re somewhat unusual, after all.
>
>I assume you’ve read the VFS docs: http://www.sqlite.org/vfs.html
>
>From there, my suggestion is to look closely at the code for the VFS structure 
>(http://www.sqlite.org/c3ref/vfs.html) and the IO Methods structure 
>(http://www.sqlite.org/c3ref/io_methods.html).  Both those structs contain a 
>bunch of function pointers for which you need to provide code.
>
>-j
>
>
>
>On Feb 10, 2015, at 10:31 AM, Stephan Beal  wrote:
>
>> On Tue, Feb 10, 2015 at 5:27 PM, Janke, Julian 
>> wrote:
>> 
>>> So my question is, if there is any additional information,  how to write a
>>> VFS?
>>> Or does anyone have a clue how to start best?
>>> 
>> 
>> This book:
>> 
>> http://www.amazon.de/Using-SQLite-Jay-Kreibich-ebook/dp/B008IGK5QM/
>> 
>> resp.
>> 
>> http://shop.oreilly.com/product/9780596521196.do
>> 
>> covers VFS creation in detail with a step-by-step example.
>> 
>> -- 
>> - 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
>
>--  
>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
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using a hex integer as input to DateTime

[Peter Aronson]

If you're accessing SQLite from your own program, adding a hextoint function 
would be pretty easy.  Even if you were using sqlite3, defining your own 
extension isn't that hard (and is documented on the SQLite website to some 
extent).  That would allow you to do most of the things you've been asking 
about.  Here's a rough cut at one (untested, and somewhat platform dependent):

/***
*
*  S_hextoint_function -- Convert a hex number string starting with 0x to int.
*
***/
static void S_hextoint_function (sqlite3_context  *ctx,
 int  num_values,
 sqlite3_value**values)
{
  const char *input_string;
  char   *error_string;
  sqlite3_int64  value;
  long long  llvalue;
  intconverted;
  size_t string_length;
  
  /* The integer value of a NULL is NULL. */

  if (sqlite3_value_type (values[0]) == SQLITE_NULL) {
sqlite3_result_null (ctx);
return;
  }

  /* Get the hex string and make sure it starts with 0x. */

  input_string = (const char *)sqlite3_value_text (values[0]);
  if ((const SE_WCHAR *)NULL == input_string) {
sqlite3_result_error_nomem (ctx);
return;
  }

  string_length = strlen (input_string);
  if (strlen < 3 || '0' != input_string[0] ||
  ('x' != input_string[1] && 'X' != input_string[1])) {
error_string = sqlite3_mprintf ("'%s' is not a legal hex constant.",
input_string);
sqlite3_result_error (ctx,error_string,-1);
sqlite3_free (error_string);
return;
  }

  /* Convert the hex string. */

  converted = sscanf (input_string,"%llx",);
  if (1 != converted)
error_string = sqlite3_mprintf ("'%s' is not a legal hex constant.",
input_string);
sqlite3_result_error (ctx,error_string,-1);
sqlite3_free (error_string);
return;
  }
  value = (sqlite3_int64)llvalue;

  /* Return the integer value. */

  sqlite3_result_int64 (ctx,value);
}

And, if in your own program, you'd just execute 

  sqlite3_create_function (hdbc,
   "hextoint",
   -1,
   SQLITE_ANY,
   globalfunc_info,
   S_hextoint_function,
   NULL,
   NULL);

After connecting, but before executing your SQL.

Peter


On Wednesday, January 14, 2015 4:32 PM, Paul Sanderson 
 wrote:
 

>
>
>Thanks all
>
>I am running the latest version :)
>
>I am just getting back to this and have a related problem
>
>I have a table
>
>create table (base int, hex text)
>
>and I want to create a trigger such that if hex is updated (or a new
>row inserted) with a text string in the form 0x12345abcd this value is
>converted into an integer and copied to base.
>
>I have tried various methods such as
>
>CREATE TRIGGER hex_trig after insert on dates
>when (select hex from dates where hex is not null)
>begin
>update dates set base = cast(new.hex as int);
>end
>
>but so far have drawn a blank
>
>Can this be done?
>
>
>Paul
>www.sandersonforensics.com
>skype: r3scue193
>twitter: @sandersonforens
>Tel +44 (0)1326 572786
>http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>-Forensic Toolkit for SQLite
>http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>processing made easy
>
>
>
>On 8 January 2015 at 00:33, Richard Hipp  wrote:
>> On 1/7/15, Paul Sanderson  wrote:
>>> Evening all
>>>
>>> I dont think this can be done, but would love to be corrected. I have
>>> a column with integer dates stored in hex format so 1234567890 is
>>> stored as 49962d2
>>>
>>> Obviously
>>> DateTime(1234567890, 'unixepoch')
>>>
>>> work OK, but what I would like to do is something like
>>>
>>> DateTime(0x49962d2, 'unixepoch')
>>
>> This should work fine, provided you are using SQLite 3.8.6 or later.
>> Support for hexadecimal literals was added in version 3.8.6.  Release
>> date for 3.8.6 was 2014-08-15, so this is not the version running on
>> your phone.  :-\
>>
>>
>>>
>>> or
>>>
>>> DateTime(HexToInt(0x49962d2), 'unixepoch')
>>>
>>> Is this possible? Not a problem if not, but would be nice.
>>>
>>> Thanks
>>>
>>>
>>> Paul
>>> www.sandersonforensics.com
>>> skype: r3scue193
>>> twitter: @sandersonforens
>>> Tel +44 (0)1326 572786
>>> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>>> -Forensic Toolkit for SQLite
>>> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>>> processing made easy
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> D. Richard Hipp

[sqlite] How often is xDisconnect called? (Was: Suggestion for syntax enhancement for virtual tables)

[Peter Aronson]

It's this comment that makes me worry that xDisconnect can be called at 
other times than detach or close:


** When an in-memory Table object is deleted (for example when the
** schema is being reloaded for some reason), the VTable objects are not
** deleted and the sqlite3_vtab* handles are not xDisconnect()ed
** immediately. Instead, they are moved from the Table.pVTable list to
** another linked list headed by the sqlite3.pDisconnect member of the
** corresponding sqlite3 structure. They are then deleted/xDisconnected
** next time a statement is prepared using said sqlite3*. This is done
** to avoid deadlock issues involving multiple sqlite3.mutex mutexes.

I'm not sure exactly what this means, but it implies that xDisconnect 
can be called in the middle of a session.


Peter

On 1/2/2015 3:00 PM, Peter Aronson wrote:

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


___
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

[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  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] JSON indexing

[Peter Aronson]

I've never used it myself, but there is http://www.unqlite.org/, which is an 
embedded document store database library.  I believe it uses JSON as native 
storage format, and it supports an embedded scripting language to access the 
contents.  The Unqlite forums seem active, so it seems to be being used and 
supported.

Peter


On Tuesday, October 21, 2014 11:10 AM, Mike Jarmy  wrote:
 

>
>
>Hmm, UnQL looks like just what I was looking for.  Seems like the project
>never really got rolling though.
>
>DocumentDB looks very interesting.  There is a lot of activity in this
>space I think -- the Postgres 'jsonb' stuff is really great.
>
>An embedded solution is what I need though :-)
>
>On Tue, Oct 21, 2014 at 11:35 AM, Cory Nelson  wrote:
>
>> On Tue, Oct 21, 2014 at 10:14 AM, Dominique Devienne 
>> wrote:
>>
>> > On Tue, Oct 21, 2014 at 3:38 PM, Mike Jarmy  wrote:
>> >
>> > > Has there been any discussion of adding JSON indexing to sqlite,
>> similar
>> > to
>> > > the way Full Text Search and R-Tree are available?
>> > >
>> > > Postgres 9.4 beta has a very nice facility for creating indexes on json
>> > > column types:
>> > >
>> > > http://www.postgresql.org/docs/9.4/static/datatype-json.html
>> > >
>> > > It would be extremely useful for me if a similar facility were
>> available
>> > in
>> > > sqlite.  I can sort of fake some of what I need via FTS, but true JSON
>> > > support would be wonderful.
>> > >
>> >
>> > Would be nice, true. But then you need some kind of XPath or XQuery for
>> > JSON on the query side.
>> >
>>
>> Microsoft's DocumentDB modifies SQL to work pretty well with unstructured
>> JSON. This might provide some inspiration.
>>
>>
>> http://azure.microsoft.com/en-us/documentation/articles/documentdb-sql-query/
>>
>>
>> --
>> Cory Nelson
>> http://int64.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
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-announce] SQLite version 3.8.7

[Peter Aronson]

No big deal, but on line 885 of shell.c, did you really mean to test if azArg 
(of type char**) was greater than 0 rather than not equal to 0?  It throws a 
warning on Solaris 9 with the SUNPro compiler.


On Friday, October 17, 2014 10:00 AM, D. Richard Hipp  wrote:
 

>
>
>SQLite version 3.8.7 is now available on the SQLite website:
>
>  http://www.sqlite.org/
>  http://www.sqlite.org/download.html
>  http://www.sqlite.org/releaselog/3_8_7.html
>
>SQLite version 3.8.7 is a regularly scheduled maintenance release. Upgrading 
>from all prior versions is recommended.
>
>Most of the changes from the previous release have been micro-optimizations 
>designed to help SQLite run a little faster. Each individual optimization has 
>an unmeasurably small performance impact. But the improvements add up. 
>Measured on a well-defined workload (which the SQLite developers use as a 
>proxy for a typical application workload) using cachegrind on Linux and 
>compiled with gcc 4.8.1 and -Os on x64 linux, the current release does over 
>20% more work for the same number of CPU cycles compared to the previous 
>release. Cachegrind is not a real CPU, and the workload used for measurement 
>is only a proxy. So your performance may vary. We expect to see about half the 
>measured and reported improvement in real-world applications. 10% is less than 
>20% but it is still pretty good, we think.
>
>This release includes a new set of C-language interfaces that have unsigned 
>64-bit instead of signed 32-bit length parameters. The new APIs do not provide 
>any new capabilities. But they do make it easier to write applications that 
>are more resistant to integer overflow vulnerabilities.
>
>This release also includes a new sorter that is able to use multiple threads 
>to help with large sort operations. (Sort operations are sometimes required to 
>implement ORDER BY and/or GROUP BY clauses and are almost always required for 
>CREATE INDEX.) The multi-threads sorter is turned off by default and must be 
>enabled using the "PRAGMA threads" SQL command. Note that the multi-threaded 
>sorter provides faster real-time performance for large sorts, but it also uses 
>more CPU cycles and more energy.
>
>As always, please report any problems to the sqlite-users@sqlite.org mailing 
>list or directly to me.  Thanks
>--
>D. Richard Hipp
>d...@sqlite.org
>
>
>
>___
>sqlite-announce mailing list
>sqlite-annou...@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite RTree nearest neighbour

[Peter Aronson]

According to R-Trees: Theory and Applications by Yannis Manolopoulos, 
Alexandros Nanopoulos, Apostolos N. Papadopoulos and Yannis Theodoridis, there 
are a number of algorithms for efficiently determining the nearest neighbor(s) 
using an R-Tree (an internet search on the two terms will pull up several).  
There are two things to keep in mind about this:
1. You would need to access SQLite's R-Tree "shadow" tables (xx_node, 
xx_parent, xx_rowid) directly in to perform the traversals required by all of 
the algorithms -- I don't know if this is officially supported by SQLite's 
developers, or if these tables are guaranteed not to change;
2. If your dimension is > 1, the R-Tree alone can't give you a reliable 
answer about who is closer, you would also need a method to calculate the 
minimum distance between two indexed objects.
Peter


On Thursday, August 21, 2014 2:32 PM, skywind mailing lists 
 wrote:
 

>
>
>Hello,
>
>does anybody have any experience with implementing a nearest neighbor search 
>using SQLite's RTree functionality? Is a nearest neighbor search possible?
>
>Regards,
>Hartwig
>
>
>___
>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] Prevent non-Integers going into int fields.

[Peter Aronson]

If you don't mind requiring that the value being inserted be typed 
integer, you can use:


create table t1 (c1 integer check (typeof(c1) = 'integer'));

But this means that values that could be converted to integers by the 
integer affinity of the column, like 4.0 or '1' will be cause a check 
constraint violation.


If this is a problem, a better approach might be to use cast as like this:

create table t2 (c1 integer check (cast (c1 as integer) = c1));

Since I *think* cast ought to work pretty much the same a conversion via 
affinity, producing a consistent set of results. And this has the 
advantage of round that text integers will still be allowed.


Peter

On 8/2/2014 9:52 PM, Richard Warburton wrote:

Whilst any type going into a field is often seen as a big plus, there are
occasions where this may be undesirable.

For such occasions, I'm currently thinking that:
NOT NULL CHECK( ROUND(fieldname)=fieldname )

looks ok, but I'm wondering if there's a case that still gets through or if
there's a better way.

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



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

[sqlite] Entry Points missing from sqlite3ext.h

[Peter Aronson]

Found in 3.8.4.3.  Missing from sqlite3ext.h are sqlite3_auto_extension() and 
sqlite3_cancel_auto_extension(). 

Now, you might ask, why would I need these functions in an extension?  Well, it 
turns out I'm writing a virtual table that accesses someone else's library that 
also uses SQLite, at least some of the time, and I don't want my extension 
being run on that library's SQLite connection.  At best it's a waste of time, 
and at worst it could confuse the other library.  So I call 
sqlite3_cancel_auto_extension() to cancel my extension before calling the other 
library's open function (which might open SQLite), and afterwards I set it 
again using sqlite3_auto_extension().

Thanks,

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

Re: [sqlite] Issue with sqlite3_uri_parameter

[Peter Aronson]

Actually, SQLite does use sqlite3_uri_parameter indirectly via 
sqlite3_uri_boolean, but I haven't been able to blow up sqlite3 by supplying 
malformed URIs.  I thing sqlite3ParseUri throws bad options away.


On Thursday, July 3, 2014 3:20 PM, Peter Aronson <pbaron...@att.net> wrote:
 

>
>
>I believe the function expects URIs of the general form (before separators are 
>converted to Nuls) of file:filename?param1=value1=v2 coverted into file 
>nul param1 nul value1 ... which means that the argument to this function is 
>always expected to have an odd number of strings.  Your input has an even 
>number.  However, it does seems wrong that a malformed URI should cause an 
>access violation.  It does not appear that SQLite uses this function itself.
>
>Peter
>
>
>On Thursday, July 3, 2014 1:51 PM, Ronan Meneu <ronan.me...@gmail.com> wrote:
>
>
>>
>>
>>Hello,
>>
>>Using VFS and sqlite3_uri_parameter, it appears that it ends with an Access
>>Violation.
>>
>>Looking a bit more closely to the code of this function:
>>
>>SQLITE_API const char *sqlite3_uri_parameter(const char *zFilename, const
>>char *zParam){
>>  if( zFilename==0 ) return 0;
>>  zFilename += sqlite3Strlen30(zFilename) + 1;
>>  while( zFilename[0] ){
>>    int x = strcmp(zFilename, zParam);
>>    zFilename += sqlite3Strlen30(zFilename) + 1;
>>    if( x==0 ) return zFilename;
>>    zFilename += sqlite3Strlen30(zFilename) + 1;
>>  }
>>  return 0;
>>}
>>
>>It appears that, if i understand well, it is expecting a series a
>>null-terminated strings. I don't know if our issue comes from the zFilename
>>not properly formatted: we have filename0filename00
>>
>>The first   "zFilename += sqlite3Strlen30(zFilename) + 1;" consumes the
>>first filename.
>>Then, it expects pairs of arg and values (?).
>>
>>In our case, algorithm goes one char too far.
>>
>>Except if you add a line just before the third "zFilename +=
>>sqlite3Strlen30(zFilename) + 1;"
>>if (zFilename[0] == 0) break;
>>
>>Is it an issue in the function, or is it coming from the zFilename input?
>>
>>Thanks,
>>
>>  Ronan
>>___
>>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] Issue with sqlite3_uri_parameter

[Peter Aronson]

I believe the function expects URIs of the general form (before separators are 
converted to Nuls) of file:filename?param1=value1=v2 coverted into file 
nul param1 nul value1 ... which means that the argument to this function is 
always expected to have an odd number of strings.  Your input has an even 
number.  However, it does seems wrong that a malformed URI should cause an 
access violation.  It does not appear that SQLite uses this function itself.

Peter


On Thursday, July 3, 2014 1:51 PM, Ronan Meneu  wrote:
 

>
>
>Hello,
>
>Using VFS and sqlite3_uri_parameter, it appears that it ends with an Access
>Violation.
>
>Looking a bit more closely to the code of this function:
>
>SQLITE_API const char *sqlite3_uri_parameter(const char *zFilename, const
>char *zParam){
>  if( zFilename==0 ) return 0;
>  zFilename += sqlite3Strlen30(zFilename) + 1;
>  while( zFilename[0] ){
>    int x = strcmp(zFilename, zParam);
>    zFilename += sqlite3Strlen30(zFilename) + 1;
>    if( x==0 ) return zFilename;
>    zFilename += sqlite3Strlen30(zFilename) + 1;
>  }
>  return 0;
>}
>
>It appears that, if i understand well, it is expecting a series a
>null-terminated strings. I don't know if our issue comes from the zFilename
>not properly formatted: we have filename0filename00
>
>The first   "zFilename += sqlite3Strlen30(zFilename) + 1;" consumes the
>first filename.
>Then, it expects pairs of arg and values (?).
>
>In our case, algorithm goes one char too far.
>
>Except if you add a line just before the third "zFilename +=
>sqlite3Strlen30(zFilename) + 1;"
>if (zFilename[0] == 0) break;
>
>Is it an issue in the function, or is it coming from the zFilename input?
>
>Thanks,
>
>  Ronan
>___
>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] xCommit is called without a prior xBegin when creating a virtual table.

[Peter Aronson]

According to the documentation for the xCommit virtual table method "A call to 
this method always follows a prior call to xBegin and xSync."  However, this 
does not seem to be the case when actually creating a virtual table.  The 
post-create xCommit call is made without any prior xBegin call.  Stepping 
through sqlite3VtabBegin(), it looks like pVTab is always NULL, so no call is 
made to xBegin.

SQLite 3.8.4.3
Solaris 9

Is there anyone out there using the xBegin, xCommit,. xRollback methods in 
their virtual table?  If so, do you pay attention to errors, or only return 
success?

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

Re: [sqlite] PRAGMA table_info(second.table)

[Peter Aronson]

You should look more closely at the syntax for the Pragma statement.  
What you need is:


PRAGMA test.table_info(tab1);

This makes sense when you consider not all pragmas have arguments.

Peter

On 6/4/2014 11:19 PM, LacaK wrote:

Hi,
when I attach database using f.e ATTACH DATABASE 'test.db' AS tets;
and in attached database is f.e. table "tab1".
Then when I try PRAGMA table_info(test.tab1) , I get error: near ".": 
syntax error.

Is it expected ?
(of course SELECT * FROM test.tab1  works as expected ...)
Thanks
-Laco.


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

Re: [sqlite] Understanding Sqlite

[Peter Aronson]

What worked for me was, before starting to code with SQLite,  I read the first 
two books on this page http://www.sqlite.org/books.html cover-to-cover, and 
spent a lot of time reading various articles on the SQLite website.  I also 
find it useful to read the SQLite code itself.

Peter




On Tuesday, June 3, 2014 11:36 PM, dd  wrote:
 

>     What is/are the best practice(s) to become master in sqlite in short
>period of time for new developers (i mean, new to sqlite not for
>programming)?
>
>Regards,
>dd
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Peter Aronson]

SQLite seemed to provide good randomness in my (admittedly informal) tests.
 
Peter

From: jose isaias cabrera <cabr...@wrc.xerox.com>
>To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Database 
><sqlite-users@sqlite.org> 
>Sent: Tuesday, April 22, 2014 1:06 PM
>Subject: Re: [sqlite] BLOBs and NULLs
>
>
>
>"Peter Aronson" wrote...
>
>
>> If you want to use sqlite3_randomness to generate a Version 4 UUID 
>> according to RFC4122, the following code will can be used:
>>
>> unsigned char uuid_data[16];
>>
>> /* We'll generate a version 4 UUID as per RFC4122. Start by generating
>> 128 bits of randomness (we will use 122 of them). */
>> sqlite3_randomness (16,uuid_data);
>>
>> /* Set the two most significant bits (bits 6 and 7) of the
>> clock_seq_hi_and_reserved field to zero and one, respectively. */
>> uuid_data[8] &= 0x3f;
>> uuid_data[8] |= 0x80;
>> /* Set the four most significant bits (bits 12 through 15) of the
>> time_hi_and_version field to the 4-bit version number from
>> Section 4.1.3 (which is 4). */
>> uuid_data[6] &= 0x0f;
>> uuid_data[6] |= 0x40;
>>
>> This assumes that sqlite3_randomness generates sufficiently good random 
>> numbers, but it appears to in my tests.
>
>Are you saying that sqlite3 does not appear to provide "good random numbers" 
>in your tests, or that it appears to, in your tests?
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Peter Aronson]

If you want to use sqlite3_randomness to generate a Version 4 UUID according to 
RFC4122, the following code will can be used:
 
  unsigned char  uuid_data[16];

  /* We'll generate a version 4 UUID as per RFC4122.  Start by generating
 128 bits of randomness (we will use 122 of them). */
  sqlite3_randomness (16,uuid_data);
  
  /* Set the two most significant bits (bits 6 and 7) of the 
 clock_seq_hi_and_reserved field to zero and one, respectively. */
  uuid_data[8] &= 0x3f;
  uuid_data[8] |= 0x80;
  /* Set the four most significant bits (bits 12 through 15) of the
 time_hi_and_version field to the 4-bit version number from
 Section 4.1.3 (which is 4). */
  uuid_data[6] &= 0x0f;
  uuid_data[6] |= 0x40;

This assumes that sqlite3_randomness generates sufficiently good random 
numbers, but it appears to in my tests.
 
Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE index not working as expected - what am I overlooking?

[Peter Aronson]

On 3/27/2014 4:21 AM, Richard Hipp wrote:

On Thu, Mar 27, 2014 at 12:49 AM, SongbookDB
wrote:


Gidday guys

I'm working with SQLite in Flash.

I have this unique index:

CREATE UNIQUE INDEX songsIndex ON songs ( DiscID, Artist, Title )

I have a parametised recursive functionset up to insert any new rows
(single or multiple).

It works fine if I try to insert a row with the same DiscID, Artist and
Title as an existing row - ie it ignores inserting the existing row, and
tells me that 0 out of 1 records were updated - GOOD.

However, if, for example the DiscId is blank, but the artist and title are
not, a new record is created when there is already one with a blank DiscId
and the same artist and title - BAD.


NULL values are distinct for the purpose of computing uniqueness.  This is
how PostgreSQL, Oracle, and MySQL work.  MS-SQL works differently.  The SQL
standards are ambiguous on this point.  See
http://www.sqlite.org/nulls.html for additional information.
This isn't exactly how Oracle works.  It's true for when there is a mix 
of NULL and not NULL values for a unique constraint, but it is not true 
if all fields in the unique constraint are null -- you can have any 
number of rows like that.  See the discussion here: 
https://community.oracle.com/message/1255458 or Oracle documentation 
here 
http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm#i1034458. 



Peter




I traced out the disc id prior to the insert, and Flash is telling me it's
undefined. So I've coded it to set anything undefined to "" (an empty
string) to make sure it's truly an empty string being inserted - but
subsequent inserts still ignore the unique index and add a brand new row
even though the same row exists.



Empty strings are not distinct for the distinct, so setting DiscID to ''
instead of NULL ought to work.  What is the declared datatype of DiscID?
Perhaps DiscID is a numeric type, and Flash is trying to be helpful and
convert the empty string into a NULL for you automatically due to the type
mismatch?



What am I misunderstanding?

Thanks for your time and help.

--
Shaun Thomson
Owner - SongbookDB
___
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] Command line shell not flushing stderr when interactive

[Peter Aronson]

Microsoft seems to only make the stderr stream unbuffered when writing to a 
character device: "The stdout and stderr functions are flushed whenever they 
are full or, if you are writing to a character device, after each library 
call."  It doesn't seem to consider pipe that emacs is reading from a character 
device.  This seems to violate the ISO C standard, which I believe requires 
stderr to be unbuffered or line-buffered at start-up, but I doubt that 
Microsoft is losing any sleep over that.
 
My office mate, who also uses Emacs on Windows, modified our local copy of 
shell.c to deal with this.  First, he made a similar addition of a fflush that 
you did (our changes are bracketed by ifdef ESRI):
 
static int process_input(struct callback_data *p, FILE *in){
  char *zLine = 0;
  char *zSql = 0;
  int nSql = 0;
  int nSqlPrior = 0;
  char *zErrMsg;
  int rc;
  int errCnt = 0;
  int lineno = 0;
  int startline = 0;
  while( errCnt==0 || !bail_on_error || (in==0 && stdin_is_interactive) ){
#ifdef ESRI
    fflush(stderr);
#endif
    fflush(p->out);
    free(zLine);
 
And made this addition to main to get interactive behavior when running is an 
Emacs *shell* window:
 
int main(int argc, char **argv){
  char *zErrMsg = 0;
  struct callback_data data;
  const char *zInitFile = 0;
  char *zFirstCmd = 0;
  int i;
  int rc = 0;
  if( strcmp(sqlite3_sourceid(),SQLITE_SOURCE_ID)!=0 ){
    fprintf(stderr, "SQLite header and source version mismatch\n%s\n%s\n",
    sqlite3_sourceid(), SQLITE_SOURCE_ID);
    exit(1);
  }
  Argv0 = argv[0];
  main_init();
  stdin_is_interactive = isatty(0);
#ifdef ESRI
  if (!stdin_is_interactive) {
    /* If Emacs shell window's TERM is set to "emacs". 
    ** Then set interactive mode on to redirect STDIN to Emacs shell window.
    */
    char *env_var = getenv("TERM");
    if (env_var) {
  if (!strcmp (env_var, "emacs"))
    stdin_is_interactive = 1;
    }
  }
#endif

Peter

From: Christopher Wellons 
>To: sqlite-users@sqlite.org 
>Sent: Sunday, January 19, 2014 9:10 AM
>Subject: [sqlite] Command line shell not flushing stderr when interactive
>
>
>
>When the shell is set to interactive (i.e. "-interactive"), the output
>(stdout) is flushed with every prompt (shell.c:422) but stderr is not.
>In some situations this leads to no error messages being displayed until
>the stderr buffer fills.
>
>This happens when running the official sqlite3 binary as subprocess of
>Emacs under Windows 7. The error messages do not appear in a timely
>fashion. I was unable to trigger the misbehavior in a plain shell so my
>only demo is a bit of Emacs Lisp. When this Elisp code below is run, a
>buffer will pop up that *should* contain the output of .help. Under
>Windows it does not. The same occurs even when it's launched via a shell
>subprocess using "2>&1", so it's not simply an issue with Emacs not
>reading from the subprocess's stderr output fast enough.
>
>    (let* ((buffer (generate-new-buffer "sqlite"))
>          (proc (start-process "sqlite" buffer "sqlite3" "-interactive")))
>      (process-send-string proc ".help\n")
>      (pop-to-buffer buffer))
>
>I suspect it has to do with being compiled without readline, which is
>why it behaves better elsewhere. I couldn't figure out how to link with
>libreadline on Windows, though, so I couldn't test this.
>
>With the following change to the amalgamation release I got the behavior
>I was looking for: timely error messages from the SQLite command line
>shell. I understand this is probably not the Right Way to do this, but
>it's just a demonstation of a possible fix.
>
>--- a/shell.c
>+++ b/shell.c
>@@ -418,6 +418,7 @@ static char *one_input_line(FILE *in, char *zPrior, int 
>isCont
>    zResult = readline(zPrompt);
>    if( zResult && *zResult ) add_history(zResult);
>#else
>+    fflush(stderr);
>    printf("%s", zPrompt);
>    fflush(stdout);
>    zResult = local_getline(zPrior, stdin);
>___
>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] Transaction involving multiple attached databases

[Peter Aronson]

And you can't execute it at all if any of the attached databases are read-only.
 
Peter


>On 11/26/2013 2:24 PM, Joshua Grauman wrote:
>> If I have multiple databases attached and then do a:
>> BEGIN EXCLUSIVE
>> 
>> I assume all the sqlite3 tables involved get locked?
>
>All the database files are locked.
>
>> Is there a way to lock only one of the attached tables?
>
>I'm pretty sure there isn't, short of creating a connection directly to a 
>single database and not attaching any others to it.
>-- Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter Aronson]

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

Indeed. The lack of rowid itself is not an issue. It's that someone could 
create a database using SQLite 3.8.x features, pass it on to someone with a 
SQLite 3.7 based app and run into runtime problems that they weren't expecting. 
We're looking for some way to reliably detect these kinds of cases or 
alternatively language to put in the spec to prevent this situation in the 
first place.

AFAICT there's no simple value in the database we can use to check for this. I 
mistakenly thought we could use the schema format number for this. Queries on 
sqlite_master could work but we would need to come up with a way to use this 
reliably. The suggested queries check for known problems. I'm worried that this 
will be an inherently brittle check since the next sqlite revision could 
introduce a new feature for which the spec has no checks in place.

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

Pepijn


Thinking about this further, it occurs to me that a brute force way of 
testing this would be to keep around a separate executable with a 
statically linked version of a minimum release of SQLite you want to 
support, and have it try to open and access a database to make sure it 
didn't have any unsupported database objects.  (It would, BTW, also fail 
in the same way if there are any unrecognized functions in any check 
constraints, but that's probably OK.)  Parsing the sqlite_master table 
for DDL SQL unsupported at a specified release would probably require 
extracting SQLite's SQL parser to perform correctly (but then, if you 
did that, you could extract the parser for your minimum release 
supported), which isn't exactly making things easy to use.


Peter

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

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

[Peter Aronson]

On 11/16/2013 10:46 AM, RSmith wrote:


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


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


Ryan


It might be simpler to simply specify a minimum release of SQLite that 
must be supported, and forbid any DDL statements that could not be 
parsed at that release, giving partial indexes (and maybe without rowid 
tables) as an example.


(Pepijn, when a you first access any object in database containing an 
object created using an unsupported DDL command, the prepare will fail 
with:


SQLITE_CORRUPT 11   /* The database disk image is malformed */


As that will trigger a parse of the contents of the sqlite_master 
table.  Note that this does not actually happen when you first open the 
database, unless you have a statically loaded extension that accesses a 
database object.)


Peter

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

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

[Peter Aronson]

Actually, we were talking about the schema format number at offset 44.  
However, neither that nor any of the other you point out will let you know if a 
without rowid table is present.  That's discovered when parsing the contents of 
the sqlite_master table.
 
Peter

From: Simon Slavin <slav...@bigfraud.org>
>To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Database 
><sqlite-users@sqlite.org> 
>Sent: Friday, November 15, 2013 3:29 PM
>Subject: Re: [sqlite] Intended use case for 'without rowid'?
>
>
>I'm confused.  By 'Schema Version Number' are people meaning this:
>
><http://www.sqlite.org/pragma.html#pragma_schema_version>
>
>Or the header string at offset 0 in this:
>
><http://www.sqlite.org/fileformat.html>
>
>Or the value written at offset 92 in this:
>
><http://www.sqlite.org/fileformat.html>
>
>?
>
>The first one, which has a name nearest to 'Schema Version Number', should 
>have nothing to do with databases popping up with 'without row'.  On the other 
>hand, an application which is testing to see whether it understands the file 
>format can usefully check the value at 92 and make sure it's less than or 
>equal to such-and-such value.  Beginning to allow 'without rowid' must 
>increase the value used.
>
>Simon.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter Aronson]

The GeoPackage is both a use format, and an interchange specification.  Both 
Pepijen and I are involved with writing general purpose software that should, 
in theory, be able read any properly formed GeoPackage SQLite database.  If one 
of these databases contains a partial index or a without rowid (or some new, 
future feature that is also not backwards compatible), and our software is not 
running a late enough release of SQLite, we won't be able to read it (or 
someone using one of our spatial type libraries on an older version of SQLite). 
 I think the way to handle this is probably for any particular release of the 
GeoPackage spec to specify the oldest version of SQLite that it is required to 
be readable at, and thus require that features that would prevent that not be 
allowed in conforming GeoPackage databases.  So, maybe, GeoPackage 1.0 will 
require working with any version of SQLite from say, 3.7.0 onward, which would 
forbid partial indexes and
 without rowid tables in conforming databases.  And later releases could kick 
the minimum up.
 
Peter

From: RSmith <rsm...@rsweb.co.za>
>To: sqlite-users@sqlite.org 
>Sent: Friday, November 15, 2013 2:11 PM
>Subject: Re: [sqlite] Intended use case for 'without rowid'?
>
>
>Pepijn & Peter - I'm not sure how this will be an issue for the sort of 
>existing systems you describe?  You will need to actually 
>physically change your current schemas to produce the mentioned problems, 
>which if you don't, you have nothing to worry about.  The 
>only people I think should plan some changes are those making DB 
>admininstrator type systems where they cannot control what things 
>users open, in which case - yes, a parse error is on the books, but this 
>should be an easy addition for said devs.  (Never relying 
>on rowid turned out a pedanticism that paid off for me - it might be a lot of 
>changes for some though, so I understand the notion).
>
>You could also introduce a unique Application ID (see: 
>http://www.sqlite.org/pragma.html#pragma_application_id)
>
>or do a check:
>IF EXISTS (SELECT 1 FROM sqlite_master WHERE type="table" AND like('%WITHOUT 
>ROWID%',sql);
>
>- to simply know whether it's an incompatible file being opened and notify the 
>user as such.
>
>Being more pedantic - Schema X would still be Schema X and work exactly like a 
>Schema X worked before, the possibility that a Schema 
>Y might also be formed now does not mean Schema X works any different than 
>before, ergo this is not a true case for Schema versioning.
>
>- BUT -
>
>I would still like to see some kind of function, even a totally new one that 
>does not affect any backward compatibility, such as:
>
>*BOOL sqlite3_table_has_rowid(*tbl);
>
>where maybe if the 'tbl' parameter is empty it checks all tables and lets us 
>know whether any tables in the Schema does not contain 
>a rowid (FALSE) etc.
>
>
>The only reason I would want this is for speed (the query above might not be 
>very efficient, or, I might be wrong - an indication 
>would be appreciated).
>
>I hope this makes some sense - thanks.
>
>
>
>On 2013/11/15 21:17, Pepijn Van Eeckhoudt wrote:
>> Will without rowid introduce a new schema version number?
>>
>> If so, we’ll be ok since GeoPackage requires schema version 4.
>>
>> Pepijn
>>
>> On 15 Nov 2013, at 16:33, Peter Aronson <pbaron...@att.net> wrote:
>>
>>> One  additional thing not listed in this document -- use of a internal 
>>> rowid alias (OID, ROWID or _ROWID_) will produce a parse error on a query 
>>> against a WITHOUT ROWID table (unless, of course, it has an actual column 
>>> with the specified name),  which makes sense, of course, but could be an 
>>> issue for generic table handling code that currently uses any of those.
>>>
>>> The fact that the presence of such a table makes a database containing one 
>>> unreadable at releases before 3.8.2 is a bit of an issue for those using 
>>> SQLite as a data exchange format (like GeoPackages), but then that's true 
>>> with partial indexes too.
>>>
>>> Peter
>>>
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter Aronson]

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


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


Peter

On 11/15/2013 4:47 AM, Luís Simão wrote:

SQLite answers those question in:

http://www.sqlite.org/draft/withoutrowid.html

BR
___
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] How to use aux_data effectively?

[Peter Aronson]

Ah, I see.  Yeah, that would be trickier.  You could save off the geometry blob 
and the GEOSPreparedGeometry object in a structure passed in to 
sqlite3_create_function and accessed via sqlite3_user_data and memcmp each 
geometry blob with the previous one to see if you need to regenerate the 
prepared geometry, but then you'd have to clean it up yourself somehow 
afterwards, and the cost of alloc, memcpy calls and memcmp calls would have to 
be cheaper than generating the prepared geometry.  

It does seem like SQLite would benefit from somewhat to declare a function as 
invarient, so if it had constant inputs, its output would be treated as a 
constant input itself when fed into another function.

Peter
- Original Message -
> From: Pepijn Van Eeckhoudt <pep...@vaneeckhoudt.net>
> To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Database 
> <sqlite-users@sqlite.org>
> Cc: 
> Sent: Friday, September 6, 2013 4:47 PM
> Subject: Re: [sqlite] How to use aux_data effectively?
> 
> Peter,
> 
> Thanks for the suggestion but that's not the part I'm trying to optimise 
> at the moment. It could be useful to use auxdata there as well to avoid 
> reparsing the text of course. What I would really like to achieve is that the 
> GEOSPreparedGeometry can be cached to speed up the geometry calculations.
> 
> Pepijn
> 
> On 07 Sep 2013, at 00:58, Peter Aronson <pbaron...@att.net> wrote:
> 
>> Actually, as it turns out, you can get the result you want by having the 
> GeomFromText function use auxdata to store the geometry blob generated from 
> the 
> WKT string, since it's a constant.  Then all the GeomFromText has to do is 
> to return the Geometry blob when sqlite3_get_auxdata returns non-NULL.
>> 
>> Peter
>> 
>> - Original Message -
>>> From: Pepijn Van Eeckhoudt <pep...@vaneeckhoudt.net>
>>> To: sqlite-users@sqlite.org
>>> Cc: 
>>> Sent: Friday, September 6, 2013 8:38 AM
>>> Subject: [sqlite] How to use aux_data effectively?
>>> 
>>> Hi,
>>> 
>>> In the extension I'm developing 
> (https://bitbucket.org/luciad/libgpkg)
>>> I'm currently adding support for queries like:
>>> select Distance(
>>>   GeomFromText('Point(13.457 3)'),
>>>   geometry
>>> ) from table;
>>> 
>>> GeomFromText takes a string and outputs a geometry blob
>>> Distance takes two geometry blobs and returns a double
>>> 
>>> In order to speed up the distance function I was wondering if I could
>>> use aux_data to cache the parsed version of the first parameter since
>>> this remains the same for every call. So far I haven't been able to 
> get
>>> this to work though, aux_data is always NULL, no matter what I try.
>>> 
>>> My hunch is that this is because the first parameter is the result of a
>>> function call which could in theory return different values for each 
> row
>>> even if the input parameters are constant. Is that correct?
>>> 
>>> Are there any other ways to kind of memoize the GeomFromText function
>>> (or the parameters to distance) besides aux_data?
>>> 
>>> Thanks,
>>> 
>>> Pepijn
>>> 
>>> ___
>>> 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] How to use aux_data effectively?

[Peter Aronson]

Actually, as it turns out, you can get the result you want by having the 
GeomFromText function use auxdata to store the geometry blob generated from the 
WKT string, since it's a constant.  Then all the GeomFromText has to do is to 
return the Geometry blob when sqlite3_get_auxdata returns non-NULL.

Peter

- Original Message -
> From: Pepijn Van Eeckhoudt 
> To: sqlite-users@sqlite.org
> Cc: 
> Sent: Friday, September 6, 2013 8:38 AM
> Subject: [sqlite] How to use aux_data effectively?
> 
> Hi,
> 
> In the extension I'm developing (https://bitbucket.org/luciad/libgpkg)
> I'm currently adding support for queries like:
> select Distance(
>   GeomFromText('Point(13.457 3)'),
>   geometry
> ) from table;
> 
> GeomFromText takes a string and outputs a geometry blob
> Distance takes two geometry blobs and returns a double
> 
> In order to speed up the distance function I was wondering if I could
> use aux_data to cache the parsed version of the first parameter since
> this remains the same for every call. So far I haven't been able to get
> this to work though, aux_data is always NULL, no matter what I try.
> 
> My hunch is that this is because the first parameter is the result of a
> function call which could in theory return different values for each row
> even if the input parameters are constant. Is that correct?
> 
> Are there any other ways to kind of memoize the GeomFromText function
> (or the parameters to distance) besides aux_data?
> 
> Thanks,
> 
> Pepijn
> 
> ___
> 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] Changed behavior from 3.7.17 to 3.8.0.

[Peter Aronson]

I don't know if this is serious, but a SAVEPOINT command fails with an open 
statement handle used to execute PRAGMA journal_mode = off at SQLite 3.8.0, but 
appears to succeed at SQLite 3.7.17.  This does not appear to be documented.  
Note that a BEGIN TRANSACTION will appear to succeed where the SAVEPOINT 
appears to fail.
 
The program:
 
#include 
#include 
#include 
 
#include "sqlite3.h"
 
/* The following program produces an error on a savepoint command at
   SQLite 3.8.0, but not at SQLite 3.7.17. */
 
int main (int  argc,
  char *argv[])
{
  sqlite3   *hdbc;
  int s3err,row;
  sqlite3_stmt   *hpragma;
 
  /* Print SQLite library release. */
 
  printf ("\nSavepoint creation test, run at SQLite %s\n\n",sqlite3_version);
  fflush (stdout);
 
  /* Open our test database (assumed to be named sp_test.db). */
 
  s3err = sqlite3_open_v2 ("sp_test.db",
  ,
  SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,
  NULL);
  if (SQLITE_OK != s3err) {
    fprintf (stderr,"Unable to open sp_test.db, err = %d\n",s3err);
    return EXIT_FAILURE;
  }
 
  /* Start an pragma on sptest. */
 
  s3err = sqlite3_prepare_v2 (hdbc,
  "PRAGMA journal_mode = off",
  -1,
 ,
 NULL);
  if (SQLITE_OK != s3err) {
    fprintf (stderr,"Unable to prepare pragma on base1, err = %d\n",s3err);
    return EXIT_FAILURE;
  }
 
  /* Perform a step to execute the pragma (assuming prepare didn't). */
  s3err = sqlite3_step (hpragma);
  if (SQLITE_DONE != s3err && SQLITE_ROW != s3err) {
    fprintf (stderr,"Error on sqlite3_step for pragma, err = %d\n",s3err);
    return EXIT_FAILURE;
  }
 
  /* Try to perform a savepoint. */
 
  s3err = sqlite3_exec (hdbc,
  "SAVEPOINT doesthiswork",
  NULL,
  NULL,
  NULL);
  if (SQLITE_OK != s3err) {
    const char  *errmsg = sqlite3_errmsg (hdbc);
    fprintf (stderr,
 "Unable create savepoint, err = %d %s\n",
 s3err,
 errmsg);
    return EXIT_FAILURE;
  }
  printf ("Savepoint successfully created.\n");
 
  /* Done. */
 
  sqlite3_finalize (hpragma);
  (void) sqlite3_close (hdbc);
  
  return EXIT_SUCCESS;
}

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

Re: [sqlite] name resolutionn in GROUP BY

[Peter Aronson]

While I can certainly see the value of going with what PostgreSQL and SQL 
Server do on the ORDER BY issue, I have to say that I suspect that Oracle's 
behavior here seem more in line Principle of Least Astonishment. First, because 
ORDER BY generally works on the resultant relation, and second, the same 
expression (say lower(m)) in GROUP BY and ORDER BY can have different values in 
the same SELECT statement with that logic.  But I admit it isn't a big deal as 
long as it is properly documented.  Really, using a column alias that is the 
same as an actual base column isn't a particularly clever thing to do in the 
first place, even if legal.
 
Peter

From: Richard Hipp <d...@sqlite.org>
>To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Database 
><sqlite-users@sqlite.org> 
>Sent: Wednesday, August 14, 2013 2:05 PM
>Subject: Re: [sqlite] name resolutionn in GROUP BY
>
>
>
>On Wed, Aug 14, 2013 at 5:02 PM, Peter Aronson <pbaron...@att.net> wrote:
>
>If I understand Dominique's post, Oracle works like SQLite 3.7.15 as well.  
>Things only got confusing when we moved from discussing GROUP BY to discussing 
>ORDER BY for some reason.
>>
>
>There are two separate (though related) issues:
>
> http://www.sqlite.org/src/info/1c69be2daf
> http://www.sqlite.org/src/info/f617ea3125
>
>
>Oracle is the outlier on the second of the two.
>
>
>-- 
>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] name resolutionn in GROUP BY

[Peter Aronson]

If I understand Dominique's post, Oracle works like SQLite 3.7.15 as well.  
Things only got confusing when we moved from discussing GROUP BY to discussing 
ORDER BY for some reason.


From: Richard Hipp 
>To: General Discussion of SQLite Database  
>Sent: Wednesday, August 14, 2013 12:40 PM
>Subject: Re: [sqlite] name resolutionn in GROUP BY
>
>
>On Wed, Aug 14, 2013 at 3:08 PM, Igor Tandetnik  wrote:
>
>>
>> Most DBMS allow sorting (and grouping) by arbitrary expressions, which
>> means that the standard is not directly applicable. One has to extrapolate.
>>
>
>PostgreSQL, MS-SQL, and SQLite 3.7.15 work one way.  Oracle and SQLite
>3.7.17 work the other.  I think I'm going to revert SQLite to working as
>does PostgreSQL.
>
>-- 
>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] name resolution in GROUP BY

[Peter Aronson]

Except the quote I provided said nothing about ORDER BY, just WHERE, GROUP BY 
or HAVING clauses.  So I'm not sure what all tests with ORDER BY are 
demonstrating, since the original question was about GROUP BY, which is a 
different thing, since ORDER BY operates strictly on the derived table.
 
Peter

From: Marc L. Allen 
>To: General Discussion of SQLite Database  
>Sent: Wednesday, August 14, 2013 11:28 AM
>Subject: Re: [sqlite] name resolution in GROUP BY
>
>
>I understand.  My previous email had the values of your original request. This 
>email was in response to Peter who found a reference that you could not use 
>derived names in a ORDER BY clause.
>
>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
>On Behalf Of Richard Hipp
>Sent: Wednesday, August 14, 2013 2:26 PM
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] name resolution in GROUP BY
>
>On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen
>wrote:
>
>> Heh... I forgot.. both selects below are identical, as 'lower(m1)' is 
>> incorrect.  MS SQL does not permit further operations on the derived value.
>>
>
>I think you also missed the name ambiguity issue.  The queries are these:
>
>SELECT '1', substr(m,2) AS m
>  FROM t1
>ORDER BY m;
>
>SELECT '2', substr(m,2) AS m
>  FROM t1
>ORDER BY lower(m);
>
>Notice that the "m" in the ORDER BY clause might refer to column t1.m or it 
>might refer to the result set column labeled "AS m".  The question is which 
>one.  PostgreSQL answers t1.m for the first case and "AS m" for the second.  
>SQLite used to do that, but now it answers "t1.m" in both cases, which seems 
>to be a better fit to the SQL standard that Peter reports.
>
>Marc, if you can also try the query below on SQL Server, that would be most
>helpful:
>
>SELECT '3', substr(m,2) AS m
>  FROM t1
>ORDER BY m COLLATE Latin1_General_CS_AS;
>
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>This email and any attachments are only for use by the intended recipient(s) 
>and may contain legally privileged, confidential, proprietary or otherwise 
>private information. Any unauthorized use, reproduction, dissemination, 
>distribution or other disclosure of the contents of this e-mail or its 
>attachments is strictly prohibited. If you have received this email in error, 
>please notify the sender immediately and delete the original.
>___
>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] name resolution in GROUP BY

[Peter Aronson]

I dug out my copy of THE GUIDE TO THE SQL STANDARD, 4th Edition, by Date and 
Darwen, and it states (in a footnote on page 151) that name specified for a 
scalar-expression in a SELECT clause can not be used in a WHERE, GROUP BY or 
HAVING clause as it is a column in the derived table, not the base table.
 
Peter

From: Richard Hipp 
>To: General Discussion of SQLite Database  
>Sent: Wednesday, August 14, 2013 9:59 AM
>Subject: Re: [sqlite] name resolutionn in GROUP BY
>
>
>On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn
>wrote:
>
>> create table test(name);
>> insert into test values (NULL);
>> insert into test values ('abc');
>>
>> select count(),
>>        NULLIF(name,'abc') AS name
>> from test
>> group by lower(name);
>>
>
>So the question is, should the "name" symbol in the GROUP BY clause refer
>to the original column name in the TEST table, or should it refer to the
>result column called "name".  SQLite version 3.7.15 picks the TEST table
>column.  Version 3.7.17 picks the result column.
>
>Anybody know which is correct?
>
>-- 
>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] Trigger Logic!!!

[Peter Aronson]

If you write a function in C, and register it with 
sqlite3_create_function (or one of its variants), you can then have a 
trigger like so:


CREATE TRIGGER Event_test1 AFTER INSERT ON test
BEGIN
  SELECT my_notifier_function();
END;

And since it's a C function, you can do pretty much anything you want at 
that point.


Peter

On 7/21/2013 10:32 PM, techi eth wrote:

I want to create a trigger on INSERT & in trigger logic I want to notify
other running executable in system.

Can I do this using trigger operation?

  Example:

Create table ();

CREATE TABLE test (

ID INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL,

);



CREATE TRIGGER Event_test1 AFTER INSERT

ON test

BEGIN



END;


]1
C1heers

Techi
___
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] sqlite3_column_count vs sqlite_data_count

[Peter Aronson]

It seems like a very subtle difference, but I think sqlite3_column_count will 
return the number of columns returned by a prepared statement regardless of 
whether there is data available to get with sqlite3_column_* functions, whereas 
sqlite3_data_count requires that there be a current result set row available 
due to sqlite3_step most recently returning SQLITE_ROW.
 
Peter

From: Simon Slavin 
>To: General Discussion of SQLite Database  
>Sent: Tuesday, July 9, 2013 5:11 PM
>Subject: Re: [sqlite] Reference to an undefined field
>
>
>By the way, does anyone understand the difference between 
>sqlite3_column_count() and sqlite3_data_count() ?  I mean, I can read the 
>definitions, but why are they both provided ?  Is one more useful than another 
>sometimes ?
>
>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] Problem compiling 3.8.0 (New Query Planner version) on Solaris 9

[Peter Aronson]

At least in my setup (Solaris 9 with SUNPro C compiler), the amalgamation 
downloaded from http://www.sqlite.org/draft/download.html fails to compile with 
the following errors (line numbers may vary slightly from standard as I am 
using a slightly modified pragma index_info):
 "/usr/include/sched.h", line 52: warning: dubious tag declaration: struct 
timespec
"/usr/include/pthread.h", line 223: warning: dubious tag declaration: struct 
timespec
"/usr/include/pthread.h", line 225: warning: dubious tag declaration: struct 
timespec
"sqlite3.c", line 21563: warning: conversion to double is out of range
"sqlite3.c", line 23365: warning: implicit function declaration: fchown
"sqlite3.c", line 23453: undefined symbol: fchmod
"sqlite3.c", line 23453: non-constant initializer involving a cast
"sqlite3.c", line 25112: warning: implicit function declaration: utimes
"sqlite3.c", line 29060: warning: implicit function declaration: usleep
"sqlite3.c", line 29102: incomplete struct/union/enum timeval: sNow
"sqlite3.c", line 29103: warning: implicit function declaration: gettimeofday
"sqlite3.c", line 29104: undefined struct/union member: tv_sec
"sqlite3.c", line 29104: undefined struct/union member: tv_usec
"sqlite3.c", line 41053: warning: statement not reached
"sqlite3.c", line 69547: warning: statement not reached
"sqlite3.c", line 70905: warning: statement not reached
"sqlite3.c", line 71013: warning: statement not reached
cc: acomp failed for sqlite3.c
make: *** [sqlite3.o] Error 2
 
However, changing _XOPEN_SOURCE from 600 back to 500 (what it was in 3.7.17) 
fixes the problem.
 
Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to interrupt a long running update without rollback?

[Peter Aronson]

Good point.  However, it looks to me that if you replace ignore with fail (and 
a message) you may get the semantics you want.  Here's a little test script:
 
drop table if exists iupdate;
create table if not exists iupdate (c1 integer primary key,tag text);
 
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');

 
select * from iupdate;

 
create trigger if not exists iupdate_up before update on iupdate
begin
  select raise(fail,'halt in processing requested') where old.c1 = 7;
end;

 
begin;
update iupdate set tag = 'updated';
select * from iupdate;
end;
 
 
Peter

From: jhnlmn <jhn...@yahoo.com>
>To: sqlite-users@sqlite.org 
>Sent: Monday, June 24, 2013 11:02 AM
>Subject: Re: [sqlite] How to interrupt a long running update without roll back?
>
>
>Peter Aronson <pbaronson@...> writes:
>> create trigger inter_update before update on my_table
>> begin
>>    select raise(ignore) where my_function() = 1;
>> end;
>
>No, this is the worst approach so far.
>This "raise(ignore)" does abort that single update 
>of that particular row, but the loop continues.
>So, if I called
>UPDATE T set C1 = calculation(C2) where C1 is NULL
>on 100,000 rows and my_function will begin returning 1 after 10,000 calls,
>then my_function will be called 100,000 times and calculation will be called
>100,000 times, but only 10,000 rows will be actually updated.
>And the time of such semi-aborted update is almost the same 
>as time of a single update without any triggers 
>(almost 5 seconds, which is close to the timeout time).
>But then I will have to call update again, this time it will make 90,000
>calls, etc, etc, etc.
>The total time is 6 times worse than time of running single update query
>without any limits. 
>
>
>
>___
>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] How to interrupt a long running update without roll back?

[Peter Aronson]

From: jhnlmn 
To: sqlite-users@sqlite.org 
Sent: Friday, June 21, 2013 3:20 PM
Subject: Re: [sqlite] How to interrupt a long running update without roll back?

Alas, none of the above answered my original question "How to interrupt a
long running update without roll back". But, guess, I will not get an answer.

I don't know if it'll work for your application (there being a cost), but a 
before trigger that performs an raise(ignore) seems to interrupt an update 
without a rollback.  So a trigger of the form:

create trigger inter_update before update on my_table
begin
   select raise(ignore) where my_function() = 1;
end;

Seems like it should interrupt an update when you take some action to make 
my_function() return 1 without causing a rollback, according to my quick test.

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

[sqlite] Are RTREE virtual tables supposed to treat NULL values as 0.0?

[Peter Aronson]

So, I was looking at some triggers to update an RTREE virtual table that 
someone 
else wrote.  I noticed that the trigger didn't handle NULLs.  I was curious, 
and 
decided to see what happened if you tried to insert NULL values into an RTREE.  
Actually, I rather expected it to throw an error.  Instead, the values (aside 
from the id which is a separate issue) became 0.0 like so:

CREATE VIRTUAL TABLE nulltest USING RTREE (pkid,v1,v2);
INSERT INTO nulltest DEFAULT VALUES;
SELECT * FROM nulltest;
pkid   v1 v2 
-- -- --
1  0.0    0.0 
 
This is not actually an ideal result, since 0.0 could either be a legitimate 
value, which means a search of the rtree table could produce a false positive; 
or it could be a completely unexpected value and cause who know what sort of 
problems.
 
Mind you, while I use RTREEs in SQLite myself, this isn't a problem for me, as 
I 
always supply legitimate values.
 
When I looked at the SQLite source, you can see that there is no check for NULL 
in the rtree code except for ids, so the calls to sqlite3_value_double in 
rtreeValueDown and rtreeValueUp are just going to return 0.0 when NULLs are 
supplied.  It seems to me that logically, they should probably throw constraint 
violations instead, but at this point that would be an issue for backwards 
compatibility.
 
Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table with check

[Peter Aronson]

The "OR NULL" doesn't work the way you think -- it's going to make the whole 
expression null, which apparently check constraints treat the same as not 
false.  What you want there is "OR typeof(handedness)='null'".

Peter

- Original Message 
> From: Roman Fleysher 
> To: General Discussion of SQLite Database 
> Sent: Fri, May 17, 2013 12:19:21 PM
> Subject: [sqlite] table with check
> 
> Dear SQLiters,
> 
> I am using sqlite shell, I believe version 3.7.16.2. I created a table with 
>CHECK condition as:
> 
> CREATE TABLE subject(
>   subjectID  INTEGER PRIMARY KEY,
>   handedness TEXT CHECK (handedness='Left' OR handedness='Right' OR NULL) 
> );
> 
> in hopes to be able to insert only "Right", "Left" or nothing "", i.e. fail 
>otherwise. But:
> 
> INSERT INTO subject (subjectID,"qqq");
> 
> actually inserts qqq. Am I doing something wrong? I read manual that newer 
>versions of sqlite should enforce CHECKs.
> 
> Thank you,
> 
> Roman
> ___
> 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] Update statement

[Peter Aronson]

At least for SQLite it appears to be.  From the Update doc page 
(http://www.sqlite.org/lang_update.html):

"The modifications made to each row affected by an UPDATE statement are 
determined by the list of assignments following the SET keyword. Each 
assignment 
specifies a column name to the left of the equals sign and a scalar expression 
to the right. For each affected row, the named columns are set to the values 
found by evaluating the corresponding scalar expressions. If a single 
column-name appears more than once in the list of assignment expressions, all 
but the rightmost occurrence is ignored. Columns that do not appear in the list 
of assignments are left unmodified. The scalar expressions may refer to columns 
of the row being updated. In this case all scalar expressions are evaluated 
before any assignments are made."

And in fact I rely on it behaving this way for in one place in my SQLite code 
and it seems to be working correctly.

Peter

- Original Message 
> From: skywind mailing lists 
> To: General Discussion of SQLite Database 
> Sent: Tue, May 7, 2013 10:46:42 AM
> Subject: Re: [sqlite] Update statement
> 
> Hi,
> 
> my question is: is it guaranteed that it works?
> 
> Regards,
> Hartwig
> 
> Am 07.05.2013 um 03:24 schrieb James K. Lowden:
> 
> > On Mon, 6 May 2013 23:53:40 +0100
> > Simon Slavin  wrote:
> > 
> >>> How do I create this kind of update statement?
> >>> 
> >>> UPDATE T SET a=0.5*(a+b), b=0.5*(b-a);
> >>> 
> >>> The RHS should always be used with the values of a and b before the
> >>> assignment.
> >>> 
> >>> I think that the result of this kind of statement is undefined, or?
> >> 
> >> No need to worry, it will work the way you want it to work:
> >> 
> >> The row is read.
> >> The new values are calculated.
> >> The new values are written to the database.
> > ...
> >> That was a terrible description. 
> > 
> > Actually that's not a bad approximation of what happens.  Here's a
> > simpler example:
> > 
> > sqlite> create table t(a int, b int);
> > sqlite> insert into t values (1,2);
> > sqlite> select * from t;
> > a          b        
> > --  --
> > 1          2        
> > sqlite> update t set a=b, b=a;  -- Et Voila! 
> > sqlite> select * from t;
> > a          b        
> > --  --
> > 2          1        
> > 
> > There is no "RHS".  The syntax and semantics of SQL are its own; they
> > cannot be extrapolated from other languages.  
> 
> I know but everybody knows what I meant, or? And its a quite brief 
description.
> 
> > 
> > --jkl
> > ___
> > 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] Changed behavior in SQLite 3.7.16 beta

[Peter Aronson]

OK, downloaded and built from this and the problem went away, including the 
more 
complex case the submitted case was cut down from.  We'll run our autotests 
again and see if anything else pops up.  Thanks for all the hard work!

Peter

- Original Message 
> From: Richard Hipp <d...@sqlite.org>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Sat, March 9, 2013 7:58:27 AM
> Subject: Re: [sqlite] Changed behavior in SQLite 3.7.16 beta
> 
> Please try again with the latest 3.7.16 beta.  You can download an
> amalgamation snapshot from http://www.sqlite.org/test/download.html
> 
> The testing checklist (http://www.sqlite.org/checklists/3071600) has been
> restarted once again on account of this issue.
> 
> On Thu, Mar 7, 2013 at 7:07 PM, Peter Aronson <pbaron...@att.net> wrote:
> 
> > I've encountered the following changed behavior (which I believe is a
> > bug).  The
> > following script works at 3.7.15.2 but fails at 3.6.16 beta:
> >
> > create table qa_data_edit (str_col text,int_col integer,rowidcol integer);
> > insert into qa_data_edit values ('this',1000,1);
> > insert into qa_data_edit values ('that',3000,2);
> > create view qa_data_edit_evw as select str_col,int_col,rowidcol from
> > qa_data_edit;
> >
> > create trigger qa_data_edit_view_update instead of update on
> > qa_data_edit_evw
> > BEGIN
> >  update or replace qa_data_edit
> >  set str_col = new.str_col,
> >      int_col = new.int_col
> >  where rowidcol = old.rowidcol;
> > END;
> > select * from qa_data_edit;
> > update main.qa_data_edit_evw
> > set str_col = 'fred',
> >    int_col = 5000
> > where main.qa_data_edit_evw.rowidcol = 1;
> > select * from qa_data_edit;
> >
> > Output:
> >
> > SQLite version 3.7.16 2013-03-06 01:55:27
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> .read test.sql
> > this|1000|1
> > that|3000|2
> > Error: near line 17: no such column: main.qa_data_edit_evw.rowidcol
> > this|1000|1
> > that|3000|2
> >
> > SQLite version 3.7.15.2 2013-01-09 11:53:05
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> .read test.sql
> > this|1000|1
> > that|3000|2
> > fred|5000|1
> > that|3000|2
> >
> > Using an unqualified ROWIDCOL in the update causes the error to go away.
> > However, database.table.column is a legal expression element according the
> > the
> > syntax diagrams.
> >
> > Tested on Windows XP and Solaris 9.
> >
> > Peter
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Changed behavior in SQLite 3.7.16 beta

[Peter Aronson]

I've encountered the following changed behavior (which I believe is a bug).  
The 
following script works at 3.7.15.2 but fails at 3.6.16 beta:

create table qa_data_edit (str_col text,int_col integer,rowidcol integer);
insert into qa_data_edit values ('this',1000,1); 
insert into qa_data_edit values ('that',3000,2); 
create view qa_data_edit_evw as select str_col,int_col,rowidcol from 
qa_data_edit; 

create trigger qa_data_edit_view_update instead of update on qa_data_edit_evw 
BEGIN
  update or replace qa_data_edit
  set str_col = new.str_col,
  int_col = new.int_col
  where rowidcol = old.rowidcol;
END;
select * from qa_data_edit;
update main.qa_data_edit_evw
set str_col = 'fred', 
    int_col = 5000
where main.qa_data_edit_evw.rowidcol = 1; 
select * from qa_data_edit;

Output:

SQLite version 3.7.16 2013-03-06 01:55:27 
Enter ".help" for instructions 
Enter SQL statements terminated with a ";"
sqlite> .read test.sql
this|1000|1
that|3000|2
Error: near line 17: no such column: main.qa_data_edit_evw.rowidcol
this|1000|1
that|3000|2

SQLite version 3.7.15.2 2013-01-09 11:53:05 
Enter ".help" for instructions 
Enter SQL statements terminated with a ";"
sqlite> .read test.sql
this|1000|1
that|3000|2
fred|5000|1
that|3000|2

Using an unqualified ROWIDCOL in the update causes the error to go away.  
However, database.table.column is a legal expression element according the the 
syntax diagrams.

Tested on Windows XP and Solaris 9.

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

[sqlite] Minor documentation correction for CREATE INDEX

[Peter Aronson]

No big deal, but I had to look at this recently, so I though I'd point it out  
before I forget about it.

First, according to the SQLite documentation for CREATE INDEX:
 
"If the UNIQUE keyword appears between CREATE and INDEX then duplicate index 
entries are not allowed. Any attempt to insert a duplicate entry will result in 
an error. For the purposes of unique indices, all NULL values are considered to 
different from all other NULL values and are thus unique. This is one of the 
two 
possible interpretations of the SQL-92 standard (the language in the standard 
is 
ambiguous) and is the interpretation followed by PostgreSQL, MySQL, Firebird, 
and Oracle. Informix and Microsoft SQL Server follow the other interpretation 
of 
the standard."
 
But Oracle says this:
 
"To satisfy a composite unique key, no two rows in the table or view can have 
the same combination of values in the key columns. Any row that contains nulls 
in all key columns automatically satisfies the constraint. However, two rows 
that contain nulls for one or more key columns and the same combination of 
values for the other key columns violate the constraint."
 
Which contradicts what SQLite says, at least for multi-column unique 
constraints.  So I'd just drop Oracle from that list, since Oracle only treats 
NULLs as unique when all columns in the unique constraint are NULL.  (Which, if 
anyone should happen to ask,I thin  is a bit weird and counter-intuitive, but 
that's just me.)

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

Re: [sqlite] Ongoing SELECT sees INSERTed rows.

[Peter Aronson]

- Original Message 
> From: Richard Hipp <d...@sqlite.org>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Thu, February 21, 2013 11:39:44 AM
> Subject: Re: [sqlite] Ongoing SELECT sees INSERTed rows.
> 
> On Thu, Feb 21, 2013 at 1:32 PM, Peter Aronson <pbaron...@att.net> wrote:
> 
> > SQLite 3.7.15.2, Solaris and Windows (and presumably others).  Journal
> > mode is
> > delete, but WAL appears to behave in the same manner.
> >
> > One of my coworkers ran into an infinite loop when accessing SQLite.
> > Essentially
> > an outer select loop was reading records, and an inner select loop was
> > inserting
> > records based on the records found into the same table.  The loop never
> > ended as
> > the records inserted showed up in the select.  This is not necessarily an
> > error,
> > of course.  But if you take the script and the program below which
> > reproduces
> > the problem, add "order by c3" to the select statement, it doesn't happen
> > (presumably because we're reading the materialized temporary table, not
> > the base
> > table at that point).  This sort of inconsistency can be an unpleasant
> > surprise.
> >
> > If you look at SQL 92, it has a concept of INSENSITIVE cursors, which
> > causes the
> > cursor to logically (at least) work on a copy of the data, so that changes
> > to
> > the underlying table are not visible.  It would be nice if SQLite provided
> > a
> > method to make a insensitive select ("order by rowid" appears to get
> > silently
> > removed by the optimizer as you'd expect) less expensive than performing an
> > otherwise unnecessary order by.
> >
> 
> (1) CREATE TEMP TABLE xyz AS SELECT...;
> 
> (2) Query the xyz table while doing whatever updates are needed.
> 
> (3) DROP TABLE xyz;
> 
> The other thing you can do is open two database connections on the same
> database (in WAL mode) and query from one while updating on the other.
> 
> 

Thanks!  The possibility we were wondering about was having an integer primary 
key autoincrement column and just exiting the select loop when encountering a 
id 
greater than the max id at start time (I assume this approach would not be safe 
if the integer primary key column was not autoincrementing).


> >
> > Peter
> >
> > create table base1 (c1 integer,c2 text,c3 integer);
> > insert into base1 (c1,c2,c3) values (1,'andrew',10);
> > insert into base1 (c1,c2,c3) values (1,'jeffrey',15);
> > insert into base1 (c1,c2,c3) values (1,'david',20);
> > insert into base1 (c1,c2,c3) values (1,'Jonathan',25);
> >
> > #include 
> > #include 
> > #include 
> > #include "sqlite3.h"
> > int main (int  argc,
> >          char *argv[])
> > {
> >  sqlite3      *hdbc;
> >  int          s3err,rid,c3;
> >  sqlite3_stmt  *hupdate,*hselect;
> >  char          sql[256];
> >
> >  /* Open our test database (assumed to be named test.db). */
> >
> >  s3err = sqlite3_open_v2 ("test.db",
> >                            ,
> >                            SQLITE_OPEN_READWRITE,
> >                            NULL);
> >  if (SQLITE_OK != s3err) {
> >    fprintf (stderr,"Unable to open test.db, err = %d\n",s3err);
> >    return EXIT_FAILURE;
> >  }
> >
> >  /* Start a select on base1. */
> >
> >  s3err = sqlite3_prepare_v2 (hdbc,
> >                              "select rowid,c3 "
> >                              "from base1 "
> >                              "where c3 < 15 and c1 = 1",
> >                              -1,
> >                              ,
> >                              NULL);
> >  if (SQLITE_OK != s3err) {
> >    fprintf (stderr,"Unable to prepare select on base1, err = %d\n",s3err);
> >    return EXIT_FAILURE;
> >  }
> >
> >  while ((s3err = sqlite3_step (hselect)) == SQLITE_ROW) {
> >    rid = sqlite3_column_int (hselect,0);
> >    c3 = sqlite3_column_int (hselect,1);
> >    sprintf (sql,"insert into base1 (c1,c2,c3) values
> > (1,'inactive',%d)",c3);
> >
> >    /* Insert into base1. */
> >
> >    s3err = sqlite3_prepare_v2 (hdbc,
> >                                sql,
> >                                -1,
> >                                ,
> >                                NULL);
> >    if (SQLITE_OK != s3err) {
> >      fprintf (stderr,"Unable to prepare insert on base1, err =
> > %

[sqlite] Ongoing SELECT sees INSERTed rows.

[Peter Aronson]

SQLite 3.7.15.2, Solaris and Windows (and presumably others).  Journal mode is 
delete, but WAL appears to behave in the same manner.

One of my coworkers ran into an infinite loop when accessing SQLite. 
Essentially 
an outer select loop was reading records, and an inner select loop was 
inserting 
records based on the records found into the same table.  The loop never ended 
as 
the records inserted showed up in the select.  This is not necessarily an 
error, 
of course.  But if you take the script and the program below which reproduces 
the problem, add "order by c3" to the select statement, it doesn't happen 
(presumably because we're reading the materialized temporary table, not the 
base 
table at that point).  This sort of inconsistency can be an unpleasant surprise.

If you look at SQL 92, it has a concept of INSENSITIVE cursors, which causes 
the 
cursor to logically (at least) work on a copy of the data, so that changes to 
the underlying table are not visible.  It would be nice if SQLite provided a 
method to make a insensitive select ("order by rowid" appears to get silently 
removed by the optimizer as you'd expect) less expensive than performing an 
otherwise unnecessary order by.

Peter

create table base1 (c1 integer,c2 text,c3 integer); 
insert into base1 (c1,c2,c3) values (1,'andrew',10); 
insert into base1 (c1,c2,c3) values (1,'jeffrey',15); 
insert into base1 (c1,c2,c3) values (1,'david',20); 
insert into base1 (c1,c2,c3) values (1,'Jonathan',25);

#include 
#include 
#include 
#include "sqlite3.h"
int main (int  argc,
  char *argv[])
{
  sqlite3   *hdbc;
  int   s3err,rid,c3;
  sqlite3_stmt  *hupdate,*hselect;
  char  sql[256];

  /* Open our test database (assumed to be named test.db). */

  s3err = sqlite3_open_v2 ("test.db",
   ,
   SQLITE_OPEN_READWRITE,
   NULL);
  if (SQLITE_OK != s3err) {
    fprintf (stderr,"Unable to open test.db, err = %d\n",s3err);
    return EXIT_FAILURE;
  }

  /* Start a select on base1. */

  s3err = sqlite3_prepare_v2 (hdbc,
  "select rowid,c3 "
  "from base1 "
  "where c3 < 15 and c1 = 1",
  -1,
  ,
  NULL);
  if (SQLITE_OK != s3err) {
    fprintf (stderr,"Unable to prepare select on base1, err = %d\n",s3err);
    return EXIT_FAILURE;
  }

  while ((s3err = sqlite3_step (hselect)) == SQLITE_ROW) {
    rid = sqlite3_column_int (hselect,0);
    c3 = sqlite3_column_int (hselect,1);
    sprintf (sql,"insert into base1 (c1,c2,c3) values (1,'inactive',%d)",c3);
    
    /* Insert into base1. */

    s3err = sqlite3_prepare_v2 (hdbc,
    sql,
    -1,
    ,
    NULL);
    if (SQLITE_OK != s3err) {
  fprintf (stderr,"Unable to prepare insert on base1, err = %d\n",s3err);
  return EXIT_FAILURE;
    }

    switch (sqlite3_step (hupdate)) {
    case SQLITE_DONE:
    case SQLITE_ROW:
  printf ("%d:",rid);
  fflush (stdout);
  sqlite3_finalize (hupdate);
  break;
    default:
  fprintf (stderr,"Error on sqlite3_step for insert, err = %d\n",s3err);
  return EXIT_FAILURE;
    }
  }
  sqlite3_finalize (hselect);
  if (SQLITE_DONE != s3err) {
    fprintf (stderr,"Error on sqlite3_step for select, err = %d\n",s3err);
    return EXIT_FAILURE;
  }
  
  (void) sqlite3_close (hdbc);
  
  return EXIT_SUCCESS;
}

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

Re: [sqlite] How to get the RecNo ???

[Peter Aronson]

You can add it to the select list as OID, ROWID or _ROWID_ or, if the 
table has a column defined INTEGER PRIMARY KEY (but not INTEGER PRIMARY 
KEY DESC) it'll also be this value.  See: 
http://www.sqlite.org/lang_createtable.html#rowid


Peter
On 2/10/2013 5:23 PM, roystonja...@comcast.net wrote:

After you do a retrieve from the database, how would to access the RecNo for 
each record?  I can get to all the fields but I don't know how to access the 
record number that sqlite creates when it creates your record.  I am not 
looking for the last record number created.

I will be populating a ListView and I want to add the RecNo in the caption.

"SELECT * FROM TestTable"

~while(sqlite3_step(stmt) == SQLITE_ROW)
~{
~~sName = (char*)sqlite3_column_text(stmt, 0);
   sLName = (char*)sqlite3_column_text(stmt, 1);



Thanks



___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

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



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

[sqlite] Peter Aronson

[Peter Aronson]

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

[sqlite] An undefined function in a check constraint will cause selecting from sqlite_master to fail.

[Peter Aronson]

I've thought about this a bit more, and concluded this is probably a bug.

If you define your own function, and reference it in a check constraint, and 
then try to select from sqlite_master when that function is not currently 
defined, the select fails with Error: malformed database schema.  I've noticed 
this since 3.7.2 and it still happens at 3.7.15.1.  It will occur even if there 
is a where clause that would avoid selecting the table with the check constaint.

Example (hopefully this will format properly):

/rococo3/peter > sqlite3 -header -column b.sqlite
sqlite3 -header -column b.sqlite
SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter ".help" for instructions 
Enter 

SQL statements terminated with a ";"
sqlite> .read add_check.sql
select load_extension ('libspatialite.so'); 
load_extension ('libspatialite.so')
---

create table if not exists boom (c1 float check(c1 <> PI())); 
select * from sqlite_master;
type   name   tbl_name   rootpage   sql 
-- -- -- -- 
--
table  boom   boom   2  CREATE TABLE boom (c1 float 
check(c1 
<> PI()))
.quit
sqlite> .q
.q

/rococo3/peter > sqlite3 -header -column b.sqlite SQLite version 3.7.15.1 
2012-12-19 20:39:10 Enter ".help" for instructions Enter SQL statements 
terminated with a ";"
sqlite> select * from sqlite_master;
Error: malformed database schema (boom) - no such function: PI
sqlite> .q
/rococo3/peter > 

Oddly enough, this doesn't appear to happen with triggers -- you only get an 
error message when actually firing the trigger.
 
Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why does an undefined function call in a check constraint cause selecting from sqlite_master fail?

[Peter Aronson]

I'm a bit puzzled by this behavior.  If you define your own function, and 
reference it in a check constraint, and then try to select from sqlite_master, 
the select fails with Error: malformed database schema.  I've noticed this 
since 
3.7.2 and it still happens at 3.7.15.1.

Example (hopefully this will format properly):

/rococo3/peter > sqlite3 -header -column b.sqlite
sqlite3 -header -column b.sqlite
SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter ".help" for instructions 
Enter 
SQL statements terminated with a ";"
sqlite> .read add_check.sql
select load_extension ('libspatialite.so'); load_extension ('libspatialite.so')
---
create table if not exists boom (c1 float check(c1 <> PI())); select * from 
sqlite_master;
type name tbl_name rootpage sql 
-- -- -- -- 
--
table boom boom 2 CREATE TABLE boom (c1 float check(c1 <> PI()))
.quit
sqlite> .q
.q
/rococo3/peter > sqlite3 -header -column b.sqlite SQLite version 3.7.15.1 
2012-12-19 20:39:10 Enter ".help" for instructions Enter SQL statements 
terminated with a ";"
sqlite> select * from sqlite_master;
Error: malformed database schema (boom) - no such function: PI
sqlite> .q
/rococo3/peter > 
 
Oddly enough, this doesn't happen with triggers -- you only get an error 
message 
when actually firing the trigger.
 
Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] A minor bug in the SQLite shell program.

[Peter Aronson]

I have found a minor bug in the sqlite3 shell program. If you use .read to run 
a 
file with exactly two errors in it, it will exit from sqlite3 to the operating 
system.
To demonstrate, I first create a file with two lines each of which causes an 
(called e2.sql)
 
select nonexistentfunction();
select nonexistentfunction();
 
Then I use .read on this file:
 
sqlite3 -header -column label.esrigdb
SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter ".help" for instructions 
Enter 
SQL statements terminated with a ";"
sqlite> .q
/rococo3/peter/sqliteexe101 > sqlite3 x.db SQLite version 3.7.14.1 2012-10-04 
19:37:12 Enter ".help" for instructions Enter SQL statements terminated with a 
";"
sqlite> .read e2.sql
Error: near line 1: no such function: nonexistentfunction
Error: near line 2: no such function: nonexistentfunction
/rococo3/peter/sqliteexe101 > 
 
And I'm back at the command prompt! This works this way on both Solaris and 
Windows. 

A quick examination of shell.c shows me that in do_meta_command(), quit sets rc 
to 2, and that read sets rc to the output from process_input (which is errCnt) :
.
.
.
  if( c=='q' && strncmp(azArg[0], "quit", n)==0 && nArg==1 ){
    rc = 2;
  }else
.
.
.
  if( c=='r' && n>=3 && strncmp(azArg[0], "read", n)==0 && nArg==2 ){
    FILE *alt = fopen(azArg[1], "rb");
    if( alt==0 ){
  fprintf(stderr,"Error: cannot open \"%s\"\n", azArg[1]);
  rc = 1;
    }else{
  rc = process_input(p, alt);
  fclose(alt);
    }
  }else
.
.
.
  return rc;
}
 
And this in process_input() (note the return of errCnt):
 
.
.
.
  while( errCnt==0 || !bail_on_error || (in==0 && stdin_is_interactive) ){ .
.
.
.
  if( zLine && zLine[0]=='.' && nSql==0 ){
    if( p->echoOn ) printf("%s\n", zLine);
    rc = do_meta_command(zLine, p);
    if( rc==2 ){ /* exit requested */
  break;
  }else if( rc ){
    errCnt++;
  }
  continue;
    }
.
.
.
  }
  free(zLine);
  return errCnt;
}
 
And when process_input() returns after being called from main(),sqlite3 exits.
 
Adding the following line (the one with the comment) to do_meta_command() seems 
to fix the problem:
 
.
.
.
    if( c=='r' && n>=3 && strncmp(azArg[0], "read", n)==0 && nArg==2 ){
  FILE *alt = fopen(azArg[1], "rb");
  if( alt==0 ){
    fprintf(stderr,"Error: cannot open \"%s\"\n", azArg[1]);
    rc = 1;
    }else{
  rc = process_input(p, alt);
  fclose(alt);
  if (rc>1) rc = 1; /* rc == errCnt, but when rc == 2, shell exits. */
    }
  }else
.
.
.
 
Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Alter table constraint question

[Peter Aronson]

As pointed out, there are products out there that will add or drop 
constraints (by doing all the tedious table creation/rename/drop under 
the covers) for SQLite.  The other approach is to do what SpatiaLite 
does in general -- use triggers instead of check constraints but for the 
same purpose (see the triggers created by AddGreometryColumn()).  SQLite 
can drop and add triggers, and unlike check constraints, they can't be 
turned off by a pragma.  They may be less efficient, though (it would be 
an interesting thing to test).


Peter

On 9/9/2012 2:19 AM, Andrea Peri wrote:

Hi,

I'm an user of sqlite with the spatial extension "spatialite".

I see the sqlite db allow to define a constraints when are in the creating
phase of the table.
But is not possible to add or drop a constraint after the table is created.

In the GIS data the availability of the constraints is a really useful
think,
but if not so smart to have they locked to the creation phase.

So it is a continuous drop and recreate the table.

This is a really time consuming work.

So I like to understand if the not availability of the
both

Alter table add constraint ... foreign key (..)
Alter table drop constraint ...

is a technology choose.
and how much complex could be to add both to the sqlite sql.

Thx,

Andrea




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

Re: [sqlite] Could anyone recommend books for SQLite

[Peter Aronson]

There's a page on the SQLite website about SQLite books at 
http://www.sqlite.org/books.html. I own the first two on the page, and 
like them both. If I was only to get one SQLite book, it'd be the second 
one on the list: Using SQLite by JayKreibich.


Best,

Peter

On 7/28/2012 6:44 PM, 黃楨民 wrote:

Dear all
  I am looking for a book ,which include all instructures and functions
of SQLite. It is like reference guide.
Could any help?
your sincerely
tom
___
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] auto-incrementing integer in composite primary key

[Peter Aronson]

You might want to look at the book Temporal Data and the Relational Model by 
Date, Darwin and Lorentzos, which goes into the subject in fairly great 
detail.  
There are subtleties.
 
Best regards,
 
Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUMing large DBs

[Peter Aronson]

Actually, it can't be in a transaction.  To quote: "A VACUUM will fail if there 
is an open transaction, or if there are one or more active SQL statements when 
it is run."  (See http://www.sqlite.org/lang_vacuum.html).

Best regards,

Peter



From: Pete 
To: sqlite-users@sqlite.org
Sent: Mon, March 26, 2012 10:14:32 AM
Subject: Re: [sqlite] VACUUMing large DBs

SHould a VACUUM command be wrapped in a transaction, or is that done
automatically?


-- 
Pete
___
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] 3.7.11 Shouldn't the new API calls also be added to sqlite3ext.h?

[Peter Aronson]

Just a question about the latest release: shouldn't the new API calls be added 
to sqlite3ext.h?  For that matter, the new API calls from 3.7.10 haven't been 
added, either.   Is this one of those things that only gets updated on larger 
releases?

Best regards,

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

Re: [sqlite] PRAGMA user_version of attached database

[Peter Aronson]

"A pragma may have an optional database name before the pragma name. The 
database name is the name of an ATTACH-ed database or it can be "main" or 
"temp" 
for the main and the TEMP databases. If the optional database name is omitted, 
"main" is assumed. In some pragmas, the database name is meaningless and is 
simply ignored."

Thus, PRAGMA .user_version;

Peter




From: Marc L. Allen 
To: "sqlite-users@sqlite.org" 
Sent: Mon, March 12, 2012 10:27:54 AM
Subject: [sqlite] PRAGMA user_version of attached database

When multiple databases are attached to the main, is there a way to get the 
user_version of any of the attached DBs?

Thanks,

Marc

--
**
*                            *                                  *
* Marc L. Allen              *  "... so many things are        *
*                            *  possible just as long as you    *
* Outsite Networks, Inc.      *  don't know they're impossible." *
* (757) 853-3000 #215        *                                  *
*                            *                                  *
* mlal...@outsitenetworks.com *      -- The Phantom Tollbooth  *
*                            *                                  *
**
___
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] Building an SQLite Extension: How to check if a table exists in the database?

[Peter Aronson]

Er, what do you mean by C Syntax -- SQL isn't C?  If you meant ANSI SQL syntax, 
you could use:

select count(*) from sqlite_master where type='table' and 
lower(name)=lower('tablename');

Instead.  But since you're accessing a metadata table that only exists in 
SQLite, this isn't particularly more standard.

Peter




From: Wei Song 2 <wei.s...@senecacollege.ca>
To: sqlite-users@sqlite.org
Sent: Fri, March 9, 2012 12:36:58 PM
Subject: Re: [sqlite] Building an SQLite Extension: How to check if a table 
exists in the database?


I'd like get the result in C Syntax. How can I do it?


Peter Aronson-3 wrote:
> 
> You got to be a bit careful there, SQLite isn't case-sensitive about table 
> names, but sqlite_master will preserve the case from the CREATE TABLE 
> statement.  Instead of 
> 
> 
> select count(*) from sqlite_master where type='table' and
> name='tablename';
> 
> You need something like 
> 
> select count(*) from sqlite_master where type='table' and name='tablename' 
> collate nocase;
> 
> Peter
> 
> 
> 
> 
> From: Roger Andersson <r...@telia.com>
> To: sqlite-users@sqlite.org
> Sent: Fri, March 9, 2012 11:52:31 AM
> Subject: Re: [sqlite] Building an SQLite Extension: How to check if a
> table 
> exists in the database?
> 
> On 03/09/12 19:39, Wei Song wrote:
>> Hello,
>>
>> I'm developing an SQLite extension which uses a function to set data into
>> a 
>>table. I'd like to know how to check if a table exists in a database?
>>
> It's hard to say what you need but maybe
> select count(*) from sqlite_master where type='table' and
> name='tablename';
> /Roger
> ___
> 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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Building-an-SQLite-Extension%3A-How-to-check-if-a-table-exists-in-the-database--tp33473784p33474119.html

Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Building an SQLite Extension: How to check if a table exists in the database?

[Peter Aronson]

You got to be a bit careful there, SQLite isn't case-sensitive about table 
names, but sqlite_master will preserve the case from the CREATE TABLE 
statement.  Instead of 


select count(*) from sqlite_master where type='table' and name='tablename';

You need something like 

select count(*) from sqlite_master where type='table' and name='tablename' 
collate nocase;

Peter




From: Roger Andersson 
To: sqlite-users@sqlite.org
Sent: Fri, March 9, 2012 11:52:31 AM
Subject: Re: [sqlite] Building an SQLite Extension: How to check if a table 
exists in the database?

On 03/09/12 19:39, Wei Song wrote:
> Hello,
>
> I'm developing an SQLite extension which uses a function to set data into a 
>table. I'd like to know how to check if a table exists in a database?
>
It's hard to say what you need but maybe
select count(*) from sqlite_master where type='table' and name='tablename';
/Roger
___
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] Building an SQLite Extension: How to Persist Configurations/Variables for a database

[Peter Aronson]

I didn't, but I could have, by using authorizer function (relatively easy -- 
see 
sqlite3_set_authorizer), and setting it in the extension initialization 
function.  Mind you, I generally feel if people are going to muck with the 
metadata, it's their own problem.

Peter

 


From: Grace Simon Batumbya <grace.batum...@senecacollege.ca>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Cc: Peter Aronson <pbaron...@att.net>
Sent: Tue, March 6, 2012 10:57:48 AM
Subject: Re: [sqlite] Building an SQLite Extension: How to Persist 
Configurations/Variables for a database

On 3/6/2012 12:04, Peter Aronson wrote: 
It's just basic SQLite reading and writing logic, like in here: 
http://www.sqlite.org/cintro.html 

>
>Okay, I thought I needed to use some other functions since I was creating an 
>extension.

Do you implement any logic prevent users from modifying the value in the table 
directly after it has been set?


Grace Batumbya 
Research Assistant | Seneca CDOT 
Phone: 416-491-5050 x3548 
cdot.senecac.on.ca 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Building an SQLite Extension: How to Persist Configurations/Variables for a database

[Peter Aronson]

It's just basic SQLite reading and writing logic, like in here: 
http://www.sqlite.org/cintro.html


Best,

Peter

On 3/6/2012 9:44 AM, Grace Simon Batumbya wrote:

On 3/6/2012 11:22, Peter Aronson wrote:
The simplest approach would be to create a table to hold this 
information.  I have an extension that works way -- you have to run 
an initialization function (SELECT function();) to create metadata 
tables before creating any tables using the extension.


If you do not mind, could you please share the code that reads and 
writes to the table.


*Grace Batumbya*
Research Assistant | Seneca CDOT
Phone: 416-491-5050 x3548
cdot.senecac.on.ca <http://cdot.senecac.on.ca/>



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

Re: [sqlite] Building an SQLite Extension: How to Persist Configurations/Variables for a database

[Peter Aronson]

The simplest approach would be to create a table to hold this 
information.  I have an extension that works way -- you have to run an 
initialization function (SELECT function();) to create metadata tables 
before creating any tables using the extension.


Best,

Peter

On 3/6/2012 6:35 AM, Grace Simon Batumbya wrote:

Hello there,
I am developing an SQLite extension in which I would like the user to 
be able to specify some configurations before the creation of any 
tables and then have those settings persist for that database.( I got 
this idea from the way pragma encoding work 
s)


My question, is how do I go about doing this? is there a best 
practises on how to implement configurations for user extensions?


Thanks.


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

Re: [sqlite] Function context

[Peter Aronson]

I believe It's private to that query as it is stored in sqlite3_context 
structure at the moment (the context being stored in a stack variable during 
SQL 
execution as far as I can tell).  I'd assume this is stable behavior, but you'd 
have to ask the SQLite Dev team for an official answer.  I aggree that the 
documentation isn't very clear.

Best regards,

Peter


From: Steinar Midtskogen <stei...@latinitas.org>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, February 13, 2012 10:24:56 AM
Subject: Re: [sqlite] Function context

[Peter Aronson]

> (2) You can associate data with an argument to a regular user-defined
> function using sqlite3_set_auxdata() and sqlite3_get_auxdata() as long
> as the value of the argument is static.  If you don't normally have a
> static argument to your function, you can add one (say a string
> MAVG').  I actually used this approach with some application generated
> SQL in my current project at one point.

Thanks.  I'm intending to write a function so I can do:

SELECT unix_time, mavg(value, unix_time, ) FROM tab;

assuming:

CREATE TABLE tab (value REAL, unix_time INTEGER, PRIMARY KEY (unix_time));

So I assume that your second approach could work, since the third
argument to mavg() (the period, window size in seconds) is static,
e.g. mavg(value, unix_time, 86400) will give me the moving daily
average.

But will the data be private to only one query?  That is, if two
queries using the same period happen to run simultaniously, will it
still work?  The documentation wasn't clear.  In its example of using
this data for storing a compiled regexp, it would rather be useful if
it was not strictly private.

-- 
Steinar
___
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] Function context

[Peter Aronson]

You can't call sqlite3_aggregate_context() in a non-aggregate function.  
However, there *are* a couple of other things you can do.


(1) All forms of sqlite3_create_function() take as 5th argument a void 
pointer to an arbitrary user data structure.  This data structure can be 
accessed in a regular user-defined function by calling 
sqlite3_user_data().  The difficultly with this approach is initializing 
this information for a scan.  There are a number of approaches you could 
take there.  One possibility is to pass the same argument in as user 
data into sqlite3_set_authorizer(), and have your authorizer function 
clear it whenever a SELECT is checked.


(2) You can associate data with an argument to a regular user-defined 
function using sqlite3_set_auxdata() and sqlite3_get_auxdata() as long 
as the value of the argument is static.  If you don't normally have a 
static argument to your function, you can add one (say a string 
'MAVG').  I actually used this approach with some application generated 
SQL in my current project at one point.


Best regards,

Peter

On 2/13/2012 7:48 AM, Steinar Midtskogen wrote:

Hello

Is it possible to have a context for a custom SQL function that is NOT
an aggregate function?  It might sound silly, but if a SELECT
statement with this function causes the function to be called in a
defined order (as with the step function of an aggregate function),
this can be useful to calculate the moving average.  That is, in its
simplest form, to return the average of the N last values.

sqlite spiral to a crash if I call sqlite3_aggregate_context() when I
don't have a finalise function.



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

[sqlite] Why does an RTREE leave its statement handles open after access?

[Peter Aronson]

I added some code before my call to sqlite3_close recently, like so:


hstmt = NULL;
while (hstmt = sqlite3_next_stmt (db,hstmt)) {
  sql = sqlite3_sql (hstmt);
  if (!sql) sql = "-"
  fprintf (log,"Unfinalized statement handle found on database close for 
[%s]\n",sql);
  sqlite3_finalize (hstmt);
}

This was really just in case code.  And to my surprise, I found that the 9 
statements that rtreeSqlInit (called by rtreeInit called by rtree's xConnect) 
prepares hadn't been finailized.  On the other hand, the statement handle used 
by my own virtual table module (used in the same query) was finalized.  Looking 
at the code, it seems that rtree uses a busy count (set to 1 by xConnect or 
xCreate, incremented by xUpdate and xFilter and decremented by xDisconnect or 
xDestroy), and until it hits 0, the statement handles are not finalized.  When 
I 
traced the behavior in the debugger, I saw the statements get finalized swhen 
qlite3_close was called.  Is this the expected behavior?  I could see it as an 
optimization, although I don't see equivalent logic in the FTS code.

Best regards,

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

Re: [sqlite] Accessing temporary tables

[Peter Aronson]

Each database has  a SQLITE_TEMP_MASTER table for temp tables and temp 
triggers.  See http://www.sqlite.org/faq.html#q7

Best regards,

Peter





From: Pete 
To: sqlite-users@sqlite.org
Sent: Sat, February 4, 2012 5:26:21 PM
Subject: [sqlite] Accessing temporary tables

I'd like to get some guidance on accessing and using temporary tables, i.e.
those created using the TEMPORARY keyword.

How do I get a list of any temporary tables in a database? They don't
appear in the sqlite_master table.

Once I have a list, can I use some form of PRAGMA table_info to get a list
of the columns in a temp table?

Thanks,

-- 
Pete
___
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] SQLite not processing triggers on ALTER TABLE RENAME when table name case mismatches

[Peter Aronson]

This had me scratching my head for a while.  Normally, when you rename a table 
with triggers on it using ALTER TABLE old_name RENAME TO new_name, it will fix 
the table association in sqlite_master to indicate the relationship with the 
new 
table. However, it turns out, if the table name is specified with different 
case 
in the create table statement and the create trigger statement, the triggers 
are 
not actually fixed.

I ran the following script in SQLite 3.7.10 on Solaris 9 in the shell program, 
using a brand-new UTF-8 database:

.echo 1
.width 36
 
create table Old_table (c1 integer);
 
create trigger old_table_insert_trigger after insert on old_table for each row 
begin select 1; end;
 
select name,tbl_name from sqlite_master;
 
alter table old_table rename to new_table;
 
select name,tbl_name from sqlite_master;
 
And the second select showed old_table_insert_trigger as belonging to 
old_table.  Given there wasn't an old table at that point, this is a bit of a 
problem.  Also, once in this state, the .tables command fails.  Also, drop 
trigger doesn't work on a trigger in this state.  However, if you change the 
create trigger statement slightly to:
 
create trigger old_table_insert_trigger after insert on Old_table for each row 
begin select 1; end;
 
And then everything works fine.  Now, I can see what's going wrong in 
sqlite3AlterRenameTable -- the where clauses in the SQL used to detect triggers 
to fix are case sensitive.  But what strikes me as the most obvious fix -- 
using 
lower() or upper() to make the comparison case-insensitive -- might be 
problematic on non-ASCII table names if ICU isn't being used.  I'm not sure how 
much of an issue that is.
 
Best regards,
 
Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] R*Tree insert error ...

[Peter Aronson]

The order for RTREEs is 
min_dimension1,max_dimension1,min_dimension2,max_dimension2...

51.51340259 is definitely greater than -1.86352760

Best regards,

Peter




From: "g...@novadsp.com" 
To: sqlite-users@sqlite.org
Sent: Wed, January 18, 2012 11:51:41 AM
Subject: Re: [sqlite] R*Tree insert error ...

On 18/01/2012 18:04, Dan Kennedy wrote:

> One possibility is that you're hitting one of the r-tree tables
> built-in constraints by specifying a record with (minval > maxval)
> for one or more of the dimensions.

Rearranging the bind parameters for match minx,miny,maxx,maxy layout I now get 
this (doubles dumped with 8 decimal places)

Inserting ID: 01, 51.51340259, -1.86352760, 51.51340259, -1.86352760
failure at read() error code 19 (constraint failed)

This is repeatable using Sqlite3

sqlite> INSERT INTO gps_index VALUES(01, 51.51340259, -1.86352760, 51.51340259,
-1.86352760);
Error: constraint failed
sqlite>

> (minval > maxval)

So how does one insert a point? Is there a magic fudge factor involved? Surely 
minval >= maxval? I cannot see anything at http://www.sqlite.org/rtree.html

Help much appreciated. Thanks.

Jerry


___
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] How to get column datatype

[Peter Aronson]

Well, to begin with, SQLite doesn't exactly have column data types like most
other DBMS, rather, columns have affinities, which might be looked at as
sort of preferences.  See http://www.sqlite.org/different.html#typing and
http://www.sqlite.org/datatype3.html#affinity 

However, this command (http://www.sqlite.org/pragma.html#pragma_table_info)
will list the "type" with which the column was declared:

  Pragma table_info ();

it just may not mean what you think it might.

Best regards,

Peter

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Tarun
> Sent: Tuesday, January 17, 2012 8:51 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] How to get column datatype
> 
> Hi All,
> 
> Does SQLite support any SQL command to get column datatype of table
> like varchar, INTEGER?
> 
> Please share example of such command. It would be helpful for me.
> 
> Thanking you.
> 
> --
> Regards,
> - Tarun Thakur
> ___
> 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] SSD with TRIM

[Peter Aronson]

You know, on some platforms, such as Solaris, /tmp can be configured to use
memory instead of disk (called tmpfs on many unix variants).  Are you sure
your /tmp is actually using disk?  It's the default in a lot of setups.

Best 

Peter

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: Sunday, January 15, 2012 12:35 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SSD with TRIM
> 
> I ran your test on my SSD system with 1M inserts.  I used WAL mode.
> 
> pragma journal_mode=WAL;
> CREATE TABLE [TestTable] (
> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
> [Text] TEXT
> );
> begin;
> insert.
> 
> end;
> 
> 
> 
> 
> 
> On SSD:
> 
> time sqlite3 gen.db  wal
> 
> real3m35.462s
> user2m14.126s
> sys 1m10.718s
> 
> On same machine but on /tmp
> 
> time sqlite3 gen.db  wal
> real3m44.259s
> user2m15.960s
> sys 1m9.437s
> 
> Or did you have some other test in mind?
> 
> 
> 
> 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 Max Vlasov [max.vla...@gmail.com]
> Sent: Saturday, January 14, 2012 1:24 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] SSD with TRIM
> 
> On Sat, Jan 14, 2012 at 6:12 AM, Simon Slavin 
> wrote:
> 
> >
> > Fast.  Fasty fast.  Speed is high.  INSERT, UPDATE, and DELETE all
> > significantly faster.  SELECT is a bit faster, but there's less
> difference.
> >
> >
> Simon, very interesting. Can you make some tests related to internal
> fragmentation? As an advanced user of sqlite,  you probably will easily
> invent your own tests :), but if not  there is a test I used at least once
> 
> INSERT INTO TestTable (id, text) VALUEs (abs(random() % (1 << 48)),
> '12345678901234567...') {the text is about 512 bytes long}
> 
> 1,000,000 inserts should create a 1GB base and the time with general hd
> was
> above 15 minutes, maybe even half an hour. So I'm very interested to see
> was there any progress with overall ssd performance.
> 
> Thanks,
> 
> Max
> ___
> 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] SegV at sqlite3_open() (Was: Re: How to sort within a text field)

[Peter Aronson]

> Or is there some other init function that can be called to maintain it in
> one file?

I don't know about that.  However, I can see two approaches you could use to
keep them in the same file:

(1) Since your main is at the bottom of the file, right before it (just
after #ifdef MAIN, say), add these lines:

#undef sqlite3_open
#undef sqlite3_close
#undef sqlite3_exec
#undef sqlite3_free

(2) Move your main block to the top to just after #include "sqlite3.h" and
just before #include "sqlite3ext.h".

Best regards,

Peter

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: Thursday, January 05, 2012 7:35 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SegV at sqlite3_open() (Was: Re: How to sort within
> a text field)
> 
> Yup...putting the  main in a separate file fixed it.
> 
> Thanks.
> 
> 
> 
> Or is there some other init function that can be called to maintain it in
> one file?
> 
> 
> 
> 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 Peter Aronson [pbaron...@att.net]
> Sent: Wednesday, January 04, 2012 12:02 PM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] SegV at sqlite3_open() (Was: Re: How to sort within
> a text field)
> 
> > But it segfaults on me at sqlite3_open() and I can't figure out
> why...there's
> >no stack info.
> 
> That's because you have it in the same file that sqlite3ext.h is included
> in,
> and the call to sqlite3_open() is really to sqlite3_api->open(), but since
> you
> haven't executed SQLITE_EXTENSION_INIT2 at that point, the sqlite3_api
> variable
> is a NULL pointer.  Thus a SegV without a stack trace.
> 
> Best regards,
> 
> Peter
> ___
> 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] SegV at sqlite3_open() (Was: Re: How to sort within a text field)

[Peter Aronson]

> But it segfaults on me at sqlite3_open() and I can't figure out why...there's 
>no stack info.

That's because you have it in the same file that sqlite3ext.h is included in, 
and the call to sqlite3_open() is really to sqlite3_api->open(), but since you 
haven't executed SQLITE_EXTENSION_INIT2 at that point, the sqlite3_api variable 
is a NULL pointer.  Thus a SegV without a stack trace.

Best regards,

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

Re: [sqlite] Parameters are not allowed in views

[Peter Aronson]

Another possibility might be to create a parameters table, say:

CREATE TABLE tabparams (p1,p2,p2,p4,p5);
INSERT INTO tabparams VALUES (null,null,null,null,null);

And when creating the view, access tabparams.p1, tabparams.p2, etc. instead
of variables (with an appropriate join clause).  Then, before accessing the
view, update the tabparams table with the values you want.

This may be more expensive, depending on the optimizer, since SQLite will no
longer be dealing with a constant but rather a join.

A more elaborate method would be to program a pair of parameter_set and
parameter_get functions in C.

Integer parameter_set (integer parameter_no, value pvalue)
Value parameter_get (integer parameter_no)

The parameter values would actually be stored during the session in the
application data for the sqlite3_create_function_v2 function, and deleted at
the session end by the destroy function you pass to it.

This has the possible advantage that I think functions with constant
arguments are treated themselves as constants.

This has the disadvantage that software that didn't load your functions
would not be able to access the views at all.

Best regards,

Peter

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