Re: [sqlite] Developing a SQLite3 DB remotely

[Stephen Chrzanowski]

Roman, I've never seen or heard of a file taking 20 seconds to show up,
except MAYBE in the case of a caching system that hasn't had the chance to
physically write the file out to the real destination, or a situation where
temp files are being written to somewhere else, then being dumped to
whatever you were monitoring when complete.  Either way, that kind of
action you're reporting about would make me queasy.

As for GFS or any flavor, yeah, that isn't going to happen for such a small
one-off project.  Dev, my manager, my bosses boss... they'd want my head on
a plate for introducing a completely unsupported FS. :]

On Thu, Mar 23, 2017 at 6:12 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> I do not have big experience in the area, but have some.
>
> I think that light weight use is not the right thing to ask. I have seen
> NFS delays of 20 seconds: file was created on one machine and showed up on
> another after 20 seconds. This depends on how heavy OTHER things are, not
> how heavy SQLite access is.
>
> GFS2 and GPFS supposedly solve file synchronization issue (by sharing disk
> inodes rather than files ). I never tested this (we have GPFS) and do not
> know about other file systems.
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on
> behalf of Simon Slavin [slav...@bigfraud.org]
> Sent: Thursday, March 23, 2017 2:14 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Developing a SQLite3 DB remotely
>
> On 23 Mar 2017, at 5:45pm, Stephen Chrzanowski 
> wrote:
>
> > The remote
> > system is a Linux based OS. […]
>
> How 'remote' is this ?  What protocol is used to do the remote access ?
>
> > What would be a recommended way to setup the
> > connections for a DEV-only arena where the below paragraph describes?
>
> … or is that what you’re asking for advice on here ?
>
> > By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I
> have
> > one permanent open file handle to the database via SEP, and that Linux OS
> > will only open a handle  periodically while I'm writing the script,
> > multiple accesses of reading or writing to the DB at the exact same time
> > just will not happen.
>
> Set a timeout of at least 10 seconds on all connections to the database.
> Apart from that I can’t think of anything you haven’t mentioned.  I do more
> complicated things by using SQLite as a back end to a web-facing system
> without problems.
>
> Simon.
> ___
> 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] Developing a SQLite3 DB remotely

[Stephen Chrzanowski]

For development, until I get the machine going, could be as far as a local
VM, or, a VM I build on one of our ESX boxes in the office.  The connection
would probably be via SMB while developing.

I'll start with the 10 seconds timeout on the remote.  I've stumbled across
a couple of examples Mr. Google has provided for me, but I didn't find much
in the lines of remote access settings for lightweight use.


On Thu, Mar 23, 2017 at 2:14 PM, Simon Slavin  wrote:

>
> On 23 Mar 2017, at 5:45pm, Stephen Chrzanowski 
> wrote:
>
> > The remote
> > system is a Linux based OS. […]
>
> How 'remote' is this ?  What protocol is used to do the remote access ?
>
> > What would be a recommended way to setup the
> > connections for a DEV-only arena where the below paragraph describes?
>
> … or is that what you’re asking for advice on here ?
>
> > By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I
> have
> > one permanent open file handle to the database via SEP, and that Linux OS
> > will only open a handle  periodically while I'm writing the script,
> > multiple accesses of reading or writing to the DB at the exact same time
> > just will not happen.
>
> Set a timeout of at least 10 seconds on all connections to the database.
> Apart from that I can’t think of anything you haven’t mentioned.  I do more
> complicated things by using SQLite as a back end to a web-facing system
> without problems.
>
> Simon.
> ___
> 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] Drop view “automatically” when its associated table is dropped?

[Daniel Anderson]

Yep Sybase preparse it. When you create a view/sproc/triggers/function the
objects referenced must exist or your DDL will fail.

this create some complexity when we want to recreate all the objects, order
of creation is very important.



2017-03-23 21:15 GMT-04:00 Simon Slavin :

>
> On 24 Mar 2017, at 12:45am, Domingo Alvarez Duarte 
> wrote:
>
> > It's not that complicated, sqlite already do all work right now but it
> doesn't store that information in a structured way "data dictionary".
>
> SQLite does not seem to parse views or triggers for dependencies.  If it
> did it wouldn’t allow this view or these triggers.
>
> SQLite version 3.16.0 2016-11-04 19:09:39
> Enter ".help" for usage hints.
> sqlite> CREATE TABLE ta (c1 TEXT);
> sqlite> CREATE TABLE tb (c2 TEXT, c3 TEXT);
>
> sqlite> CREATE VIEW v1 AS SELECT i1 FROM ia;
> sqlite> PRAGMA table_info(v1);
> Error: no such table: main.ia
>
> sqlite> CREATE TRIGGER t22 BEFORE INSERT ON tb
>...> FOR EACH ROW BEGIN
>...> INSERT INTO ta (fred) VALUES (new.c2);
>...> END;
> sqlite> CREATE TRIGGER t23 BEFORE INSERT ON tb
>...> FOR EACH ROW BEGIN
>...> INSERT INTO gradsad VALUES (ajsdhasd);
>...> END;
> sqlite>
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Daniel
*L'action accède à la perfection quand, bien que vivant, vous êtes déjà
mort*
*Bunan*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Simon Slavin]

On 24 Mar 2017, at 12:45am, Domingo Alvarez Duarte  wrote:

> It's not that complicated, sqlite already do all work right now but it 
> doesn't store that information in a structured way "data dictionary".

SQLite does not seem to parse views or triggers for dependencies.  If it did it 
wouldn’t allow this view or these triggers.

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> CREATE TABLE ta (c1 TEXT);
sqlite> CREATE TABLE tb (c2 TEXT, c3 TEXT);

sqlite> CREATE VIEW v1 AS SELECT i1 FROM ia;
sqlite> PRAGMA table_info(v1);
Error: no such table: main.ia

sqlite> CREATE TRIGGER t22 BEFORE INSERT ON tb
   ...> FOR EACH ROW BEGIN
   ...> INSERT INTO ta (fred) VALUES (new.c2);
   ...> END;
sqlite> CREATE TRIGGER t23 BEFORE INSERT ON tb
   ...> FOR EACH ROW BEGIN
   ...> INSERT INTO gradsad VALUES (ajsdhasd);
   ...> END;
sqlite> 

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

Re: [sqlite] Memoization in sqlite json1 functions

[Jens Alfke]

> On Mar 23, 2017, at 3:17 PM, Deon Brewis  wrote:
> 
> If you however can use a forward-only push or pull parser like a SAX or StAX 
> parse, it's a different story. I'm using a StAX-like pull parser for a binary 
> json-ish internal format we have, and reading & parsing through it is on par 
> with the performance of reading equivalent SQLITE columns directly

I agree that’s a lot faster, but you’re still looking at O(n) lookup time in an 
array or dictionary. And the proportion constant gets worse the bigger the 
document is, since jumping to the next item involves parsing through all of the 
nested items in that collection.

> That obviously implies if you do random-access into a structure you have to 
> keep reparsing it (which is where Memoization would be nice). However, CPU 
> caches are better at reading continues data streams in forward-only fashion 
> than they are with pointers, so forward-only pull parsers, even when you have 
> to repeat the entire parse, are often faster than the math behind it suggests.

It could be; my knowledge of optimization gets tenuous when it comes to 
down-to-the-metal areas like CPU caching. But for large data, you run the risk 
of blowing out the cache traversing it. And if the data is memory-mapped, it 
becomes hugely faster to skip right to the relevant page instead of faulting in 
every page ahead of it.

> Besides, in 99% of cases my users take the outcome from a json parse and just 
> store the results into a C++ data structure anyway. In that case the 
> intermediary object tree is just a throwaway and you may as well have built 
> the C++ structure up using a pull or push parser.

In Fleece I put a lot of effort into making the C++ API nice to use, so that I 
don’t have to have any other data structure. That's worked well so far.

—Jens

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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Domingo Alvarez Duarte]

Hello !

It's not that complicated, sqlite already do all work right now but it 
doesn't store that information in a structured way "data dictionary".


Cheers !


On 23/03/17 21:17, Simon Slavin wrote:

On 23 Mar 2017, at 10:52pm, Daniel Anderson  wrote:


Sybase has one.

there is even a way to get objects dependencies

Okay, thanks for the answers.  The only way to do this involves quite a 
complicated set of dependencies.  Consider, for example, having to parse a 
TRIGGER to figure out all the TABLEs and VIEWs mentioned inside the stored 
procedure, and also all the columns of those tables and views mentioned in the 
TRIGGER.

The only way I can see it happening involved a significant modification of 
SQLite so that as it parses the schema stored in the database it constructs 
this table of dependencies.  Nothing simple.

Simon.
___
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] Drop view “automatically” when its associated table is dropped?

[Simon Slavin]

On 23 Mar 2017, at 10:52pm, Daniel Anderson  wrote:

> Sybase has one.
> 
> there is even a way to get objects dependencies

Okay, thanks for the answers.  The only way to do this involves quite a 
complicated set of dependencies.  Consider, for example, having to parse a 
TRIGGER to figure out all the TABLEs and VIEWs mentioned inside the stored 
procedure, and also all the columns of those tables and views mentioned in the 
TRIGGER.

The only way I can see it happening involved a significant modification of 
SQLite so that as it parses the schema stored in the database it constructs 
this table of dependencies.  Nothing simple.

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

[sqlite] Second beta for SQLite 3.18.0.

[Richard Hipp]

The second beta release for SQLite 3.18.0 is now available on the website:

   https://www.sqlite.org/download.html

This second beta adds a new SQL function: json_patch().  See
https://www.sqlite.org/draft/json1.html#jpatch for details.

Final testing will begin soon, so please do not delay in reporting
issues or concerns.  Thanks.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Daniel Anderson]

Sybase has one.

there is even a way to get objects dependencies, I have been working on a
project for the last 5 years using sybase (ASE). very huge project and
whenever we must alter the schema we have tools to tell us which store
proc, triggers, views will be impacted. it allow us to reach the different
stake holders impacted so they can assess how the change will impact them.

also, because the way ASE works, they must provide some way to find
dependencies, because all objects which depends on table must be recreated
when that table is changed (according to doc) if not the
view/triggers/sproc might not do what you expect or fail.

ex:
create proc ert as select * from user_table where country = 'canada'

if a column is added at the end to user_table and the proc ert is not
recreated, it will not see the new column.
if a column is drop the proc will fail to execute
if a column is added in the middle then undefined behavior.

so with that kind of behavior it is good that sybase provide a way to find
the dependencies.



2017-03-23 7:56 GMT-04:00 Simon Slavin :

>
> On 23 Mar 2017, at 11:25am, Domingo Alvarez Duarte 
> wrote:
>
> > This problem and other related with the lack of a "data dictionary" in
> sqlite,
>
> Not sure what you expect here.  Can you point me at a "data dictionary"
> for some other implementation of SQL ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Daniel
*L'action accède à la perfection quand, bien que vivant, vous êtes déjà
mort*
*Bunan*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Developing a SQLite3 DB remotely

[Roman Fleysher]

No. I was not aware of these tools. Are any of them good? Maintained?

I am mostly using sqlite3 shell from bash scripts. Do you know if some of them 
are suitable replacements?

Is this off the topic of the  original  question?

Thank you,

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Joshua J. Kugler [jos...@azariah.com]
Sent: Thursday, March 23, 2017 6:16 PM
To: SQLite mailing list
Subject: Re: [sqlite] Developing a SQLite3 DB remotely

Are you aware options for true network access?

https://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

Just thought I'd throw that out there.

j

--
Joshua J. Kugler - Fairbanks, Alaska
Azariah Enterprises - Programming and Website Design
jos...@azariah.com - Jabber: pedah...@gmail.com
PGP Key: http://pgp.mit.edu/  ID 0x73B13B6A
___
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] Memoization in sqlite json1 functions

[Deon Brewis]

It has more to do with how you parse JSON - if you want to build it into an 
object tree, sure, then you're obviously dead in the water with any kind of 
json.

If you however can use a forward-only push or pull parser like a SAX or StAX 
parse, it's a different story. I'm using a StAX-like pull parser for a binary 
json-ish internal format we have, and reading & parsing through it is on par 
with the performance of reading equivalent SQLITE columns directly (apart from 
the expression indexed covered value lookup scenario... grumble... grumble...).

So binary-json like formats can perform well - you just can't use an object 
tree to parse them.

That obviously implies if you do random-access into a structure you have to 
keep reparsing it (which is where Memoization would be nice). However, CPU 
caches are better at reading continues data streams in forward-only fashion 
than they are with pointers, so forward-only pull parsers, even when you have 
to repeat the entire parse, are often faster than the math behind it suggests. 
(In the way that scanning an unsorted vector in O(n) is often times faster than 
searching O(log n) through a map).

Besides, in 99% of cases my users take the outcome from a json parse and just 
store the results into a C++ data structure anyway. In that case the 
intermediary object tree is just a throwaway and you may as well have built the 
C++ structure up using a pull or push parser. It's very like extra work, and 
it's way... way... faster. 

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Thursday, March 23, 2017 11:05 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Memoization in sqlite json1 functions


> On Mar 23, 2017, at 4:30 AM, Richard Hipp  wrote:
> 
> BLOBs are reserved for a future enhancement in which BLOBs will store the 
> binary encoding for JSON.

I’ve been down this road. There are a number of JSON-compatible binary encoding 
formats, but most of them don’t save much time, because (a) they’re not _that_ 
much faster to parse, (b) reading JSON tends to be dominated by allocating an 
object tree, not by the actual parsing, and (c) usually you have to parse the 
entire data even if you only want to use one piece of it [as in a query].

I ended up designing and implementing a new format, called Fleece*. Its 
advantage is that it doesn’t require parsing or even memory allocation. The 
internal structure is already an object tree, except that it uses compressed 
relative offsets instead of pointers. This means that internal pointers into 
Fleece data can be used directly as the data objects.

In my current project** we’re storing Fleece in SQLite instead of JSON, with a 
modified version of the json1 extension to make it accessible in queries. It 
works very well. The Fleece equivalent of json_extract( ) just does some 
pointer manipulation to find the root object in the blob, then further lookups 
to jump to each nested object in the path.

—Jens

* https://github.com/couchbaselabs/fleece 

** https://github.com/couchbase/couchbase-lite-core
___
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] Developing a SQLite3 DB remotely

[Joshua J. Kugler]

Are you aware options for true network access? 

https://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

Just thought I'd throw that out there.

j

-- 
Joshua J. Kugler - Fairbanks, Alaska
Azariah Enterprises - Programming and Website Design
jos...@azariah.com - Jabber: pedah...@gmail.com
PGP Key: http://pgp.mit.edu/  ID 0x73B13B6A
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Developing a SQLite3 DB remotely

[Roman Fleysher]

I do not have big experience in the area, but have some.

I think that light weight use is not the right thing to ask. I have seen NFS 
delays of 20 seconds: file was created on one machine and showed up on another 
after 20 seconds. This depends on how heavy OTHER things are, not how heavy 
SQLite access is.

GFS2 and GPFS supposedly solve file synchronization issue (by sharing disk 
inodes rather than files ). I never tested this (we have GPFS) and do not know 
about other file systems.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, March 23, 2017 2:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] Developing a SQLite3 DB remotely

On 23 Mar 2017, at 5:45pm, Stephen Chrzanowski  wrote:

> The remote
> system is a Linux based OS. […]

How 'remote' is this ?  What protocol is used to do the remote access ?

> What would be a recommended way to setup the
> connections for a DEV-only arena where the below paragraph describes?

… or is that what you’re asking for advice on here ?

> By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I have
> one permanent open file handle to the database via SEP, and that Linux OS
> will only open a handle  periodically while I'm writing the script,
> multiple accesses of reading or writing to the DB at the exact same time
> just will not happen.

Set a timeout of at least 10 seconds on all connections to the database.  Apart 
from that I can’t think of anything you haven’t mentioned.  I do more 
complicated things by using SQLite as a back end to a web-facing system without 
problems.

Simon.
___
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] Memoization in sqlite json1 functions

[Domingo Alvarez Duarte]

Hello Jens !

Nice to know this project, I'll look at it.

Cheers !


On 23/03/17 15:05, Jens Alfke wrote:

On Mar 23, 2017, at 4:30 AM, Richard Hipp  wrote:

BLOBs are reserved for a future enhancement in which BLOBs will store the 
binary encoding for JSON.

I’ve been down this road. There are a number of JSON-compatible binary encoding 
formats, but most of them don’t save much time, because (a) they’re not _that_ 
much faster to parse, (b) reading JSON tends to be dominated by allocating an 
object tree, not by the actual parsing, and (c) usually you have to parse the 
entire data even if you only want to use one piece of it [as in a query].

I ended up designing and implementing a new format, called Fleece*. Its 
advantage is that it doesn’t require parsing or even memory allocation. The 
internal structure is already an object tree, except that it uses compressed 
relative offsets instead of pointers. This means that internal pointers into 
Fleece data can be used directly as the data objects.

In my current project** we’re storing Fleece in SQLite instead of JSON, with a 
modified version of the json1 extension to make it accessible in queries. It 
works very well. The Fleece equivalent of json_extract( ) just does some 
pointer manipulation to find the root object in the blob, then further lookups 
to jump to each nested object in the path.

—Jens

* https://github.com/couchbaselabs/fleece 

** https://github.com/couchbase/couchbase-lite-core
___
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] Drop view ?automatically? when its associated table is dropped?

[James K. Lowden]

On Thu, 23 Mar 2017 11:56:00 +
Simon Slavin  wrote:

> > This problem and other related with the lack of a "data dictionary"
> > in sqlite, 
> 
> Not sure what you expect here.  Can you point me at a "data
> dictionary" for some other implementation of SQL ?

https://www.postgresql.org/docs/9.1/static/catalogs-overview.html

Every commonly used DBMS has some support for system tables.  ANSI
standardized them with INFORMATION_SCHEMA.  SQLite's support for them
is notably sparse.  

A data dictionary commonly means something about data semantics: some
description about where the data came from and what they mean.
Unfortunately most system table implementations cannot be extended in
that way by the user.  (Of course, it is possible to write queries that
refer to them.  It is not possible to create constraints on system
tables that e.g. require a semantic description or even just a primary
key).  

--jkl



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

Re: [sqlite] RIGHT JOIN! still not supported?

[James K. Lowden]

On Wed, 22 Mar 2017 10:53:09 -0500
Jeffrey Mattox  wrote:

> Isn't it possible to get the same results of a RIGHT JOIN by using
> two selects with a UNION or UNION ALL between them.

Yes.  By definition, an outer join is the union of

the rows that meet the matching criteria
and
the rows that do not meet the matching criteria

The outer join construct exists for convenience: the convenience of the
user, who expresses the idea with fewer words, and the convenience of
the implementer, whose query optimization problem is simplified by the
direct expression of the desired result.  

An outer join says explicitly it wants all rows in the outer table.  On
the other hand, the query planner has to be quite sophisticated to
recognize that 

A = (A where exists B)  union (A where not exists B)

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

Re: [sqlite] Drop view ?automatically? when its associated table is dropped?

[James K. Lowden]

On Wed, 22 Mar 2017 15:07:41 +
Simon Slavin  wrote:

> On 22 Mar 2017, at 11:52am, R Smith  wrote:
> > 
> > May I add further that views are tricky things. They can refer to
> > multiple tables, or other views which in turn refer to multiple
> > tables or yet other views (and have obscured the names of the
> > original tables via "AS" aliasing). There is no way to easily
> > implement an automatic view-of-table dropper. (I've tried to do
> > something like this for an sqlite tool long ago).
> 
> I think I?ve tried to do the equivalent thing for TRIGGERs and also
> failed.  And there?s also FOREIGN KEYs, though you can figure those
> out using PRAGMA calls.

It's only "tricky" because SQL is tricky to parse.  Given a parser,
collecting terminal nodes of a particuar type is simple.  

An interesting -- and innovative -- SQLite function would expose the
parser's result as ordinary data: 

sqlite3_parse_sql( sqlite3*, const char sql[] )

returning two tables representing the parse tree, along the lines of

Expressions:
expression_id
parent_expression_id
table_name
column_number
column_name
column_type
column_size 

and

Operations:
operation_id
left_expression_id
left_expression_column_number
right_expression_id
right_expression_column_number

That's just off the top of my head.  Doubtless the experience of
representing JSON trees as tables would provide insight, too.  

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

Re: [sqlite] Developing a SQLite3 DB remotely

[Simon Slavin]

On 23 Mar 2017, at 5:45pm, Stephen Chrzanowski  wrote:

> The remote
> system is a Linux based OS. […]

How 'remote' is this ?  What protocol is used to do the remote access ?

> What would be a recommended way to setup the
> connections for a DEV-only arena where the below paragraph describes?

… or is that what you’re asking for advice on here ?

> By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I have
> one permanent open file handle to the database via SEP, and that Linux OS
> will only open a handle  periodically while I'm writing the script,
> multiple accesses of reading or writing to the DB at the exact same time
> just will not happen.

Set a timeout of at least 10 seconds on all connections to the database.  Apart 
from that I can’t think of anything you haven’t mentioned.  I do more 
complicated things by using SQLite as a back end to a web-facing system without 
problems.

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

Re: [sqlite] Memoization in sqlite json1 functions

[Jens Alfke]

> On Mar 23, 2017, at 4:30 AM, Richard Hipp  wrote:
> 
> BLOBs are reserved for a future enhancement in which BLOBs will store the 
> binary encoding for JSON.

I’ve been down this road. There are a number of JSON-compatible binary encoding 
formats, but most of them don’t save much time, because (a) they’re not _that_ 
much faster to parse, (b) reading JSON tends to be dominated by allocating an 
object tree, not by the actual parsing, and (c) usually you have to parse the 
entire data even if you only want to use one piece of it [as in a query].

I ended up designing and implementing a new format, called Fleece*. Its 
advantage is that it doesn’t require parsing or even memory allocation. The 
internal structure is already an object tree, except that it uses compressed 
relative offsets instead of pointers. This means that internal pointers into 
Fleece data can be used directly as the data objects.

In my current project** we’re storing Fleece in SQLite instead of JSON, with a 
modified version of the json1 extension to make it accessible in queries. It 
works very well. The Fleece equivalent of json_extract( ) just does some 
pointer manipulation to find the root object in the blob, then further lookups 
to jump to each nested object in the path.

—Jens

* https://github.com/couchbaselabs/fleece 

** https://github.com/couchbase/couchbase-lite-core
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Developing a SQLite3 DB remotely

[Stephen Chrzanowski]

Right up front, I'm fully aware about the How To Corrupt document (Believe
me, I've preached about network access in this forum), and with the
development tools I have in Windows vs the destination OS and the purpose
of the DB, I'm asking for other peoples experience on remote developing a
database.

The SQLite editor of choice for me is SQLite Expert Pro (SEP).  The remote
system is a Linux based OS.  The databases job is to keep track of jobs,
hosts, last completed, priorities of the jobs, etc.  The Linux machine is
going to be running a BASH script that runs in an infinite loop,
periodically poking the database to decide what to run next based on a
schedule.  There will be frequent sleep periods between SQL calls.

While I'm developing the database, the infinite looping in the bash script
isn't going to exist.  The script runs, does its thing (To start, just ECHO
what I want it to do), update the database on successful completion, then
check for the next job if any are available.  When the scripts are done
running, I want to re-run a query in the SEP to confirm what I've done in
the BASH script did what it was supposed to do.

The question for the experienced multi-machine & multi-OS DB designers, has
anyone ever run into a problem where EXTREMELY LIGHT WEIGHT use of the
database causes corruption?  What would be a recommended way to setup the
connections for a DEV-only arena where the below paragraph describes?

By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I have
one permanent open file handle to the database via SEP, and that Linux OS
will only open a handle  periodically while I'm writing the script,
multiple accesses of reading or writing to the DB at the exact same time
just will not happen.  Once development stops, it'll be just this one BASH
script that will ever touch the database.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memoization in sqlite json1 functions

[Scott Hess]

What is the goal, though?  Your app knows your data and performance needs,
so if you find yourself running the same query to read off the same result
set over and over, change your app to do the right thing.

If it's somehow more convenient to have SQLite do it, populate a temporary
table and pull the data from that, which doesn't require any new API at all.

-scott


On Thu, Mar 23, 2017 at 6:23 AM, Domingo Alvarez Duarte 
wrote:

> Hello Richard !
>
> Yes I do see "because the implementation parsing the JSON anew each time"
> and this is a recurring pattern in some sqlite functions, would be nice if
> we could have a "session/query/row" storage space to store query
> information that can be  reused, for example on the json functions we could
> reuse an already parsed json field several times, another example we could
> have session/query/row variables.
>
> We could have something like "sqlite3_set_auxdata" but with granularity
> for row/query/session and as a bonus would be nice to have session
> variables like https://dev.mysql.com/doc/refman/5.7/en/user-variables.html
> .
>
> Cheers !
>
> On 23/03/17 08:30, Richard Hipp wrote:
>
>> On 3/22/17, Domingo Alvarez Duarte  wrote:
>>
>>> Hello Richard !
>>>
>>> I noticed that sqlite do not use any memoization in json1 functions.
>>>
>>> For example jsonExtractFunc and others parse the json string every time
>>> it's called even when the json string is the same.
>>>
>>> minimal example : "select json_extract(json, '$.name') name,
>>> json_extract(json, '$.address') name from some_table;"
>>>
>>> Could be possible to have some kind of memoization as a general option
>>> for any sqlite functions ?'
>>>
>> In paragraph 3.0 of https://www.sqlite.org/json1.html:  "All json1
>> functions currently throw an error if any of their arguments are BLOBs
>> because BLOBs are reserved for a future enhancement in which BLOBs
>> will store the binary encoding for JSON."
>>
>> But let me ask this:  Have you actually measured a performance
>> problem?  Or are you just assuming that because the implementation
>> parses the JSON anew each time it see it that it must therefore be
>> inefficient?
>>
>>
> ___
> 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] Possibly pointless assert

[Scott Robison]

On Thu, Mar 23, 2017 at 11:05 AM, Dan Kennedy  wrote:
> On 03/23/2017 11:46 PM, Scott Robison wrote:
>>
>> Note: I'm on Windows 10 and reproduced this with the amalgamation
>> downloaded today from
>> http://sqlite.com/2017/sqlite-amalgamation-317.zip
>>
>> Step 1: Using sqlite3 shell, created a database test.db with the
>> following schema:
>>
>>  CREATE TABLE a(b text collate binary, c text collate nocase);
>>  CREATE INDEX ab on a(b);
>>  CREATE INDEX ac on a(c);
>
>
> Thanks for reporting this. Removed the assert() here:
>
>   http://www.sqlite.org/src/info/9f2e04d3c3526b5f

Glad to do it. Complete fluke that I came across it. Still, nice to
"contribute" something, even if this trivial. :)

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

Re: [sqlite] Possibly pointless assert

[Dan Kennedy]

On 03/23/2017 11:46 PM, Scott Robison wrote:

On Thu, Mar 23, 2017 at 10:17 AM, Scott Robison  wrote:

On Thu, Mar 23, 2017 at 9:21 AM, Dan Kennedy  wrote:

How did you trip the assert()? i.e. what is the database schema and query
that cause it to fail?

In trying to track down issues recently, a team member defined
SQLITE_DEBUG. My "fix" was to simply undefine SQLITE_DEBUG, thus
compiling out the assertions anyway. Since we won't have it in
production code, I wouldn't call it a bug, just an over enthusiastic
bit of error prevention.

The query was apparently a vacuum. I'll synthesize a test case and
submit it later.

Note: I'm on Windows 10 and reproduced this with the amalgamation
downloaded today from
http://sqlite.com/2017/sqlite-amalgamation-317.zip

Step 1: Using sqlite3 shell, created a database test.db with the
following schema:

 CREATE TABLE a(b text collate binary, c text collate nocase);
 CREATE INDEX ab on a(b);
 CREATE INDEX ac on a(c);


Thanks for reporting this. Removed the assert() here:

  http://www.sqlite.org/src/info/9f2e04d3c3526b5f

Dan.

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

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

[J Decker]

On Thu, Mar 23, 2017 at 9:01 AM, Dan Kennedy  wrote:

> On 03/23/2017 10:00 PM, Subramaniyan, Ganesan (G.) wrote:
>
>> Hi,
>> We are facing dynamic memory pool corruption issue while using SQLite.
>> Based on our investigation we suspect SQLite freeing memory bypassing the
>> memory management API'S registered as below.
>>
>> We have registered the memory allocation routines as below.
>> static const sqlite3_mem_methods mqxmem = {
>>  mqxMalloc,
>>  mqxFree,
>>  mqxRealloc,
>>  mqxSize,
>>  mqxRoundup,
>>  mqxInit,
>>  mqxShutdown,
>>  NULL
>>  };
>> sqlite3_config(SQLITE_CONFIG_MALLOC, );
>>
>> Despite this, is there any sort of configurations by which SQLite might
>> still use its own or standard library APIs to allocate/reallocate or free
>> the memory, bypassing the memory management APIs registered above?
>>
>
>
> No. Following a successful call to sqlite3_config() SQLite allocates and
> frees memory using the configured routines exclusively[1]. There are no
> direct calls to malloc()/free() or similar in the library.
>
> Are you checking the return value of sqlite3_config()? If
> sqlite3_initialize() or any other sqlite3_*() function has already been
> called when sqlite3_config() is invoked it will fail and the memory
> allocation routines will not be configured.
>
>   https://sqlite.org/c3ref/config.html
>
> What symptoms are you seeing that suggest SQLite is bypassing your memory
> allocation functions?
>

There is an internal check to see if sqlite has alerady 'initialized' the
config needs to be set before it's initialized.  There's a error thrown

void errorLogCallback(void *pArg, int iErrCode, const char *zMsg){
printf( "Sqlite3 Err: (%d) %s", iErrCode, zMsg);
}

sqlite3_config( SQLITE_CONFIG_LOG, errorLogCallback, 0);

that is sqlite3_config is called late it logs an 'invalid operation' sort
of message.

>
> Dan.
>
> [1] It may also make use of static buffers supplied by calls to
> sqlite3_config(SQLITE_CONFIG_HEAP), CONFIG_PAGECACHE or CONFIG_SCRATCH,
> but I'm guessing you have made no such calls. Most apps do not.
>
>
>
>
>
> We are using SQLite version 3.7.10 running on Freescale MQX hosted on ARM
>> A5 Cortex processor.
>> We have enabled SQLITE_ENABLE_MEMORY_MANAGEMENT configuration and using
>> sqlite3_release_memory()
>> very often, after every fetch/write operations.
>>
>> Thanks & Regards,
>> Ganesan.S
>> ___
>> 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] Possibly pointless assert

[Scott Robison]

On Thu, Mar 23, 2017 at 10:17 AM, Scott Robison  wrote:
> On Thu, Mar 23, 2017 at 9:21 AM, Dan Kennedy  wrote:
>> How did you trip the assert()? i.e. what is the database schema and query
>> that cause it to fail?
>
> In trying to track down issues recently, a team member defined
> SQLITE_DEBUG. My "fix" was to simply undefine SQLITE_DEBUG, thus
> compiling out the assertions anyway. Since we won't have it in
> production code, I wouldn't call it a bug, just an over enthusiastic
> bit of error prevention.
>
> The query was apparently a vacuum. I'll synthesize a test case and
> submit it later.

Note: I'm on Windows 10 and reproduced this with the amalgamation
downloaded today from
http://sqlite.com/2017/sqlite-amalgamation-317.zip

Step 1: Using sqlite3 shell, created a database test.db with the
following schema:

CREATE TABLE a(b text collate binary, c text collate nocase);
CREATE INDEX ab on a(b);
CREATE INDEX ac on a(c);

Note: I did not insert any data. It is not necessary.

Step 2: Copied the 35 line sample C code from
http://sqlite.com/quickstart.html into sqlite-assert-test.c. My only
change was to change the sqlite3.h include from  to
"sqlite3.h"

Step 3: Extracted the amalgamation files into the directory with
sqlite-assert-test.c.

Step 4: Opened a 64 bit native build command prompt from Visual C++ 2015.

Step 5: Build the test program as follows:

cl /Zi -DSQLITE_DEBUG sqlite-assert-test.c sqlite3.c

Step 6: Ran the resulting executable as:

sqlite-assert-test.exe test.db vacuum

It throws an assertion, presumably trying to vacuum the ab index.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possibly pointless assert

[Scott Robison]

On Thu, Mar 23, 2017 at 9:21 AM, Dan Kennedy  wrote:
> On 03/23/2017 04:45 AM, Scott Robison wrote:
>>
>> Take a look at
>> http://www.sqlite.org/cgi/src/artifact/3ed64afc49c0a222?ln=2214,2233
>> (especially the assert within).
>>
>> I may not be understanding something, but that assert seems pointless
>> to me.
>
>
>
> The assert() says that if the buffer pointed to by Column.zColl contains the
> string "BINARY", then it must point actually point to global buffer
> sqlite3StrBINARY, not to some other buffer that contains the same bytes.
> i.e. the assert() means that the next line could be written as:
>
>   if( sqlite3StrBINARY==zColl ) break;
>
> instead of:
>
>   if( sqlite3_stricmp(sqlite3StrBINARY, zColl) ) break;
>
> I think it's likely an oversight that that line was not rewritten. Or
> perhaps just a choice to take the safer option in case the assert() is not
> actually true. There is another part of the code where that assumption is
> made and the (sqlite3StrBINARY==zColl) comparison is used, although I think
> it's just an optimization - SQLite should not return the wrong answer even
> if the assert() can be false.

Correct. If the line following was just using an equality comparison
of two pointers, the assert would make a bit more sense. Given that
the following comparison uses the "safer" stricmp, the assertion seems
pointless. It winds up asserting something is wrong when in fact the
code works exactly as advertised.

>
> How did you trip the assert()? i.e. what is the database schema and query
> that cause it to fail?

In trying to track down issues recently, a team member defined
SQLITE_DEBUG. My "fix" was to simply undefine SQLITE_DEBUG, thus
compiling out the assertions anyway. Since we won't have it in
production code, I wouldn't call it a bug, just an over enthusiastic
bit of error prevention.

The query was apparently a vacuum. I'll synthesize a test case and
submit it later.

>
> Dan.
>
>
>
>
>
>
>
>> The point of the loop is to check all the columns in an index
>> to see if they are all binary collated. If any column is not binary
>> collated, then exit early, which will skip the following if statement
>> at 2234.
>>
>> It feels to me like that assert was added as a mid-development sanity
>> check when it was being developed against a known database. I had it
>> trip on me today unexpectedly.
>>
>> If I am incorrect and that is a useful assertion, I'd like to
>> understand the reason why. Otherwise, the if statement at 2232 does
>> everything the assert at 2230 does, making the assert fire when the
>> code is working correctly.
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

[Dan Kennedy]

On 03/23/2017 10:00 PM, Subramaniyan, Ganesan (G.) wrote:

Hi,
We are facing dynamic memory pool corruption issue while using SQLite. Based on 
our investigation we suspect SQLite freeing memory bypassing the memory 
management API'S registered as below.

We have registered the memory allocation routines as below.
static const sqlite3_mem_methods mqxmem = {
 mqxMalloc,
 mqxFree,
 mqxRealloc,
 mqxSize,
 mqxRoundup,
 mqxInit,
 mqxShutdown,
 NULL
 };
sqlite3_config(SQLITE_CONFIG_MALLOC, );

Despite this, is there any sort of configurations by which SQLite might still 
use its own or standard library APIs to allocate/reallocate or free the memory, 
bypassing the memory management APIs registered above?



No. Following a successful call to sqlite3_config() SQLite allocates and 
frees memory using the configured routines exclusively[1]. There are no 
direct calls to malloc()/free() or similar in the library.


Are you checking the return value of sqlite3_config()? If 
sqlite3_initialize() or any other sqlite3_*() function has already been 
called when sqlite3_config() is invoked it will fail and the memory 
allocation routines will not be configured.


  https://sqlite.org/c3ref/config.html

What symptoms are you seeing that suggest SQLite is bypassing your 
memory allocation functions?


Dan.

[1] It may also make use of static buffers supplied by calls to 
sqlite3_config(SQLITE_CONFIG_HEAP), CONFIG_PAGECACHE or CONFIG_SCRATCH, 
but I'm guessing you have made no such calls. Most apps do not.






We are using SQLite version 3.7.10 running on Freescale MQX hosted on ARM A5 
Cortex processor.
We have enabled SQLITE_ENABLE_MEMORY_MANAGEMENT configuration and using 
sqlite3_release_memory() very 
often, after every fetch/write operations.

Thanks & Regards,
Ganesan.S
___
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] QUERY: Usage of malloc and free in SQLite

[Subramaniyan, Ganesan (G.)]

Hi,
We are facing dynamic memory pool corruption issue while using SQLite. Based on 
our investigation we suspect SQLite freeing memory bypassing the memory 
management API'S registered as below.

We have registered the memory allocation routines as below.
static const sqlite3_mem_methods mqxmem = {
mqxMalloc,
mqxFree,
mqxRealloc,
mqxSize,
mqxRoundup,
mqxInit,
mqxShutdown,
NULL
};
sqlite3_config(SQLITE_CONFIG_MALLOC, );

Despite this, is there any sort of configurations by which SQLite might still 
use its own or standard library APIs to allocate/reallocate or free the memory, 
bypassing the memory management APIs registered above?
We are using SQLite version 3.7.10 running on Freescale MQX hosted on ARM A5 
Cortex processor.
We have enabled SQLITE_ENABLE_MEMORY_MANAGEMENT configuration and using 
sqlite3_release_memory() very 
often, after every fetch/write operations.

Thanks & Regards,
Ganesan.S
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possibly pointless assert

[Dan Kennedy]

On 03/23/2017 04:45 AM, Scott Robison wrote:

Take a look at 
http://www.sqlite.org/cgi/src/artifact/3ed64afc49c0a222?ln=2214,2233
(especially the assert within).

I may not be understanding something, but that assert seems pointless
to me.



The assert() says that if the buffer pointed to by Column.zColl contains 
the string "BINARY", then it must point actually point to global buffer 
sqlite3StrBINARY, not to some other buffer that contains the same bytes. 
i.e. the assert() means that the next line could be written as:


  if( sqlite3StrBINARY==zColl ) break;

instead of:

  if( sqlite3_stricmp(sqlite3StrBINARY, zColl) ) break;

I think it's likely an oversight that that line was not rewritten. Or 
perhaps just a choice to take the safer option in case the assert() is 
not actually true. There is another part of the code where that 
assumption is made and the (sqlite3StrBINARY==zColl) comparison is used, 
although I think it's just an optimization - SQLite should not return 
the wrong answer even if the assert() can be false.


How did you trip the assert()? i.e. what is the database schema and 
query that cause it to fail?


Dan.








The point of the loop is to check all the columns in an index
to see if they are all binary collated. If any column is not binary
collated, then exit early, which will skip the following if statement
at 2234.

It feels to me like that assert was added as a mid-development sanity
check when it was being developed against a known database. I had it
trip on me today unexpectedly.

If I am incorrect and that is a useful assertion, I'd like to
understand the reason why. Otherwise, the if statement at 2232 does
everything the assert at 2230 does, making the assert fire when the
code is working correctly.



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

Re: [sqlite] about sqlite db.delete the file

[Clemens Ladisch]

z...@tofocus.cn wrote:
>recently, I have a prolbem, that my android(4.3) application use sqlite db 
> to store the data.
> my had two thread visit the db, and offten happen  delete  db table, so my 
> data lose.
> [...]
> 03-18 14:25:04.194 2345-2384/? E/SQLiteLog: (26) statement aborts at 0: 
> [PRAGMA user_version;] file is encrypted or is not a database
> 03-18 14:25:04.194 2345-2384/? E/DefaultDatabaseErrorHandler: Corruption 
> reported by sqlite on database: 
> /data/data/com.mxcg.abcmarket/databases/citycardconfig.db
> 03-18 14:25:04.194 2345-2384/? E/DefaultDatabaseErrorHandler: deleting the 
> database file: /data/data/com.mxcg.abcmarket/databases/citycardconfig.db

The file got corrupted.  This is either a hardware problem, or wrong
(insufficient) locking in your threaded code.

If it happens only with that file, then it is much more likely that your code
is at fault.


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

Re: [sqlite] Memoization in sqlite json1 functions

[Domingo Alvarez Duarte]

Hello Richard !

Yes I do see "because the implementation parsing the JSON anew each 
time" and this is a recurring pattern in some sqlite functions, would be 
nice if we could have a "session/query/row" storage space to store query 
information that can be  reused, for example on the json functions we 
could reuse an already parsed json field several times, another example 
we could have session/query/row variables.


We could have something like "sqlite3_set_auxdata" but with granularity 
for row/query/session and as a bonus would be nice to have session 
variables like https://dev.mysql.com/doc/refman/5.7/en/user-variables.html .


Cheers !

On 23/03/17 08:30, Richard Hipp wrote:

On 3/22/17, Domingo Alvarez Duarte  wrote:

Hello Richard !

I noticed that sqlite do not use any memoization in json1 functions.

For example jsonExtractFunc and others parse the json string every time
it's called even when the json string is the same.

minimal example : "select json_extract(json, '$.name') name,
json_extract(json, '$.address') name from some_table;"

Could be possible to have some kind of memoization as a general option
for any sqlite functions ?'

In paragraph 3.0 of https://www.sqlite.org/json1.html:  "All json1
functions currently throw an error if any of their arguments are BLOBs
because BLOBs are reserved for a future enhancement in which BLOBs
will store the binary encoding for JSON."

But let me ask this:  Have you actually measured a performance
problem?  Or are you just assuming that because the implementation
parses the JSON anew each time it see it that it must therefore be
inefficient?



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

Re: [sqlite] Still running 3.7.x. Was: RIGHT JOIN! still not supported?

[Hick Gunter]

As we build from canonical sources and have some proprietary patches, a new 
release does not just "pop in" but requires merging changes via CVS, upgrading 
is a lengthy process, done when resources are available.

Last try was with release 3.14, and - apart from changes in the handling of 
OMIT flags for "useable but unused" constraints - promptly ran into problems 
with ORDER BY Queries (fixed about 2 weeks later).

Next upgrade may be this summer.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Hipp
Gesendet: Donnerstag, 23. März 2017 12:06
An: SQLite mailing list 
Betreff: [sqlite] Still running 3.7.x. Was: RIGHT JOIN! still not supported?

On 3/23/17, Hick Gunter  wrote:
>  I am still runnning SQLIte
> 3.7.14.1

Why?

3.17.0 is backwards compatible, fixes obscure bugs, has many new features, and 
runs twice as fast.

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] about sqlite db.delete the file

[z...@tofocus.cn]

hi,
   recently, I have a prolbem, that my android(4.3) application use sqlite db 
to store the data.
my had two thread visit the db, and offten happen  delete  db table, so my data 
lose.



03-18 14:25:04.134 2345-2384/? E/SQLiteLog: (26) statement aborts at 78: 
[SELECT locale FROM android_metadata UNION SELECT NULL ORDER BY locale DESC 
LIMIT 1] file is encrypted or is not a database
03-18 14:25:04.144 2345-2384/? E/SQLiteDatabase: Failed to open database 
'/data/data/com.mxcg.abcmarket/databases/citycardconfig.db'.
 
android.database.sqlite.SQLiteException: Failed to change locale for db 
'/data/data/com.mxcg.abcmarket/databases/citycardconfig.db' to 'zh_CN'.
 at 
android.database.sqlite.SQLiteConnection.setLocaleFromConfiguration(SQLiteConnection.java:393)
 at 
android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:218)
 at 
android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
 at 
android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
 at 
android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
 at 
android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
 at 
android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:804)
 at 
android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:789)
 at 
android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
 at 
android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:898)
 at 
android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:235)
 at 
android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:224)
 at 
android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:188)
 at 
com.mxcg.abcmarket.dbdao.BaseDao.queryflag(BaseDao.java:108)
 at 
java.lang.reflect.Method.invokeNative(Native Method)
 at 
java.lang.reflect.Method.invoke(Method.java:525)
 at 
com.mxcg.abcmarket.tools.DbUtils.queryflog(DbUtils.java:332)
 at 
com.mxcg.abcmarket.POS.SMKBackTcpCtl.GoodsWaterSend(SMKBackTcpCtl.java:256)
 at 
com.mxcg.abcmarket.common.BaseApplication$BusinessTask.run(BaseApplication.java:103)
 at 
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:390)
 at 
java.util.concurrent.FutureTask.runAndReset(FutureTask.java:276)
 at 
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:153)
 at 
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:268)
 at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080)
 at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
 at 
java.lang.Thread.run(Thread.java:841)
  Caused by: 
android.database.sqlite.SQLiteDatabaseCorruptException: file is encrypted or is 
not a database (code 26)
 at 
android.database.sqlite.SQLiteConnection.nativeExecuteForString(Native Method)
 at 
android.database.sqlite.SQLiteConnection.executeForString(SQLiteConnection.java:641)
 at 
android.database.sqlite.SQLiteConnection.setLocaleFromConfiguration(SQLiteConnection.java:374)
 at 
android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:218)?
 at 

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Simon Slavin]

On 23 Mar 2017, at 11:25am, Domingo Alvarez Duarte  wrote:

> This problem and other related with the lack of a "data dictionary" in 
> sqlite, 

Not sure what you expect here.  Can you point me at a "data dictionary" for 
some other implementation of SQL ?

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

Re: [sqlite] Memoization in sqlite json1 functions

[Richard Hipp]

On 3/22/17, Domingo Alvarez Duarte  wrote:
> Hello Richard !
>
> I noticed that sqlite do not use any memoization in json1 functions.
>
> For example jsonExtractFunc and others parse the json string every time
> it's called even when the json string is the same.
>
> minimal example : "select json_extract(json, '$.name') name,
> json_extract(json, '$.address') name from some_table;"
>
> Could be possible to have some kind of memoization as a general option
> for any sqlite functions ?'

In paragraph 3.0 of https://www.sqlite.org/json1.html:  "All json1
functions currently throw an error if any of their arguments are BLOBs
because BLOBs are reserved for a future enhancement in which BLOBs
will store the binary encoding for JSON."

But let me ask this:  Have you actually measured a performance
problem?  Or are you just assuming that because the implementation
parses the JSON anew each time it see it that it must therefore be
inefficient?

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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Domingo Alvarez Duarte]

Hello !

This problem and other related with the lack of a "data dictionary" in 
sqlite, would be nice to have the sql parser exposed that way we could 
get the database structure on the fly and solve problems like the one 
discussed on this thread.


Cheers !

On 23/03/17 00:59, 邱朗 wrote:

“Much safer to have a habit to name views like the table names they derive from 
(when they derive from specific tables)”

That is what I am doing right now. So thanks for all the answers I got to 
confirm that there is no easy way to do it.
Qiulang


At 2017-03-22 19:52:59, "R Smith"  wrote:


On 2017/03/22 12:37 PM, Richard Hipp wrote:

On 3/22/17, 邱朗  wrote:

Hi,


Is there any way to drop view “automatically” when its associated table is
dropped?
It seems no way to do. Then if I drop a table, is there any (easy) way to
find views created based on it and I can drop view manually ?


There is no easy way to figure out what tables a view refers to.
Furthermore, some applications might want to drop a table and then
recreate that table (perhaps after adding a new constraint or
reordering the columns) and would like to keep using the VIEW.  Those
applications would not want the VIEW to be dropped automatically.


May I add further that views are tricky things. They can refer to
multiple tables, or other views which in turn refer to multiple tables
or yet other views (and have obscured the names of the original tables
via "AS" aliasing). There is no way to easily implement an automatic
view-of-table dropper. (I've tried to do something like this for an
sqlite tool long ago).

You could even look for the table name referenced in the view sql (by
simply doing something like:
SELECT name FROM sqlite_master WHERE type='view' AND sql LIKE
'%MyTableName%';
and drop all of those views that show up in the result - BUT, then you
have to be absolutely sure no View would ever use a field, refer to a
field, use another table or view, or have inferred / aliased fields or
use tables in a sub-query of any kind that will ever be the same (in
full or in part) as the table name you are trying to drop for.

It's a slippery slope. Much safer to have a habit to name views like the
table names they derive from (when they derive from specific tables),
such as
CREATE VIEW cities_view_someref AS SELECT ... FROM cities WHERE  ;
CREATE VIEW cities_view_someotherref AS SELECT ... FROM cities WHERE  ;

Then later when you drop everything, either in your code do:
DROP TABLE cities;
namelist = (SELECT name FROM sqlite_master WHERE type='view' AND name
LIKE 'cities_view_%';)
Then DROP every view in namelist via code.

--- or by creating an extended function in sqlite3, do this (pseudo coded):

FUNCTION  nmDROPTV(@arg1):
BEGIN
   DROP TABLE @arg1;
   For each name in (SELECT name FROM sqlite_master WHERE type='view'
AND name LIKE '[@arg1]_view_%';)
 do DROP VIEW name;
END;
Maybe also return the failure/success status of those functions, and
perhaps executing them in a transaction, etc.

So then executing:
DROPTV cities;
Should have the same effect as the first example.


___
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

[sqlite] Still running 3.7.x. Was: RIGHT JOIN! still not supported?

[Richard Hipp]

On 3/23/17, Hick Gunter  wrote:
>  I am still runnning SQLIte
> 3.7.14.1

Why?

3.17.0 is backwards compatible, fixes obscure bugs, has many new
features, and runs twice as fast.

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

Re: [sqlite] Memoization in sqlite json1 functions

[Deon Brewis]

That would be nice.

I've resorted to a few horrible hacks like this:

SELECT parse(data), extract("name"), extract("address"), release(data) FROM 
some_table;

It works, but it relies on LTR parsing of arguments (which it does now, but I 
seriously doubt is a guarantee), as well as global/thread-local variables.

Though I can still live with that one - it works.

What I would like to see - is if you have an indexed expression like so:
create index some_index on some_table( json_extract(json, '$.name') );

And you run:
select json_extract(json, '$.name') from some_table indexed by some_index;

That it returns the resultant value that is already stored in the index, rather 
than re-executing the expression.
 
- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Domingo Alvarez Duarte
Sent: Wednesday, March 22, 2017 5:04 PM
To: SQLite mailing list 
Subject: [sqlite] Memoization in sqlite json1 functions

Hello Richard !

I noticed that sqlite do not use any memoization in json1 functions.

For example jsonExtractFunc and others parse the json string every time it's 
called even when the json string is the same.

minimal example : "select json_extract(json, '$.name') name, json_extract(json, 
'$.address') name from some_table;"

Could be possible to have some kind of memoization as a general option for any 
sqlite functions ?

Cheers !


___
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] RIGHT JOIN! still not supported?

[Hick Gunter]

After some thinking I came up with this:

First, set up the example (note: no CTE as I am still runnning SQLIte 3.7.14.1):

CREATE temp TABLE stock(id, cid, sid);
CREATE temp TABLE clients(id,name);
CREATE temp TABLE suppliers(id,name);
insert into stock (id) values (1),(2),(3),(4),(5),(6),(7);
UPDATE stock SET cid = id WHERE id < 4;
UPDATE stock SET sid = id - 1 WHERE id BETWEEN 2 AND 5;
INSERT INTO clients   SELECT cid, 'Hick'||cid   FROM stock WHERE cid IS NOT 
NULL;
INSERT INTO suppliers SELECT sid, 'Smith'||sid FROM stock WHERE sid IS NOT NULL;


The desired output contains rows that have a NULL client, so may as well create 
a view for this (NOTE: renaming the columns to indicate the table they are 
from):

create temp view clients_null as select id as cid,name as cname from clients 
union all select null, null;


The same holds for rows that have a NULL supplier, so create a view for this 
too (NOTE: renaming as above):

create temp view suppliers_null as select id as sid,name as sname from 
suppliers union all select null, null;


Now we are ready to produce all possible rows, may as well do this with a view 
too:

create temp view all_rows as select * from clients_null, suppliers_null;


And for the grand finale, join with stock to retrieve
1) stock connecting client to supplier
2) client with stock not connected to supplier
3) supplier with stock not connected to client
(NOTE: not a single LEFT or RIGHT join required)
(NOTE: if we could indicate that NULL == NULL should be true, just the first 
expressions should suffice):

select cname,sname,id from all_rows a join stock s on ((a.sid = s.sid) and 
(a.cid = s.cid)) or ((a.cid = s.cid) and (a.sid is null) and (s.sid is NULL)) 
or ((a.sid = s.sid) and (a.cid is null) and (s.cid is NULL));

cname|sname|id
Hick1|NULL|1
Hick2|Smith1|2
Hick3|Smith2|3
NULL|Smith3|4
NULL|Smith4|5


Adding a dummy value instead of NULL yields (NOTE: where clause to eliminate 
totally unconnected stock items):

select cname,sname,id from all_rows a join stock s on (ifnull(a.sid,-1) = 
ifnull(s.sid,-1)) and (ifnull(a.cid,-1) = ifnull(s.cid,-1)) where cname not 
null or sname not null;


Pursuing this idea further leads to:

drop view clients_null;
drop view suppliers_null;
create temp view clients_null as select id as cid,name as cname from clients 
union all select -1, null;
create temp view suppliers_null as select id as sid,name as sname from 
suppliers union all select -1, null;
create temp view stock_dummy as select id,ifnull(sid,-1) as sid,ifnull(cid,-1) 
as cid from stock;

and the quite readable statement:

select cname,sname,id from all_rows a join stock_dummy s on (a.sid = s.sid) and 
(a.cid = s.cid) where cname not null or sname not null;



___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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