Re: [sqlite] Best practices for forward conversion of database formats?

[Keith Medcalf]

Or you can store the version in a database table itself.  Though I suppose the 
user_version pragma is kinda like a table:  create table version(user_version); 
and then putting a single integer in there.

All the "good" applications ship with an upgrader from *any* version ever to 
the current version, as a bunch of stepwise modifications.  Each subsequent 
version merely adds any needed changes from the version available.  This allows 
any version to upgrade to the current.

Crappier applications only include the upgrader from the previous interim beta 
patch release format/version to the current format/version, and you have to 
hunt all over the place to find every single version of the application to run 
one after each to upgrade stepwise to the current format/version.  Needless to 
say, such vendors have their "user/administrator experience" tested once, then 
they are relegated forever to the corner full of steaming excrement and their 
product and company is never spoken to (or of, except with dire warnings to 
avoid at all costs) ever again.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Roger Binns
>Sent: Thursday, 30 August, 2018 11:10
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Best practices for forward conversion of
>database formats?
>
>On 30/08/18 09:51, Randall Smith wrote:
>> is how to convert existing data from one DB format to another,
>given some arbitrary set of changes in the database schema in the
>interim.
>
>I use SQLite's user pragma.  It starts at zero.
>
>https://sqlite.org/pragma.html#pragma_user_version
>
>My code ends up looking like this:
>
>if user_version==0:
>CREATE TABLE IF NOT EXISTS events(key, time, message);
>PRAGMA user_version=1;
>
>if user_version==1:
>CREATE INDEX IF NOT EXISTS [events:message] ON events(message);
>PRAGMA user_version=2;
>
>if user_version==2:
>ALTER TABLE events ADD COLUMN severity;
>PRAGMA user_version=3;
>
>This ensures that the currently running code will upgrade the schema
>as
>needed.  Ensure the commands are wrapped in a transaction so they
>either
>completely happen or not.
>
>I am helped by having low complexity schemas.  If yours are large you
>could probably generate something like the above.  Some ORM style
>engines also have schema and data upgrade functionality.
>
>Roger




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

Re: [sqlite] sqlidiff --schema question

[Richard Hipp]

On 8/7/18, Mark Wagner  wrote:
> I was surprised to see sqldiff --schema not report column constraints as
> schema differences.  Or am I missing something?
>
> $ echo .schema | sqlite3 /tmp/f1.db
> CREATE TABLE t (foo text unique);
>
> $ echo .schema | sqlite3 /tmp/f2.db
> CREATE TABLE t (foo text);
>
> $ sqldiff --schema /tmp/f1.db /tmp/f2.db
> $

Correct.  sqldiff appears to only look at the column names, not constraints.

I think it will pick up if you add an extra UNIQUE index.

-- 
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] sqlidiff --schema question

[Mark Wagner]

Just pining on this in case anyone knows more... ?

On Tue, Aug 7, 2018 at 2:00 PM Mark Wagner  wrote:

>
> I was surprised to see sqldiff --schema not report column constraints as
> schema differences.  Or am I missing something?
>
> $ echo .schema | sqlite3 /tmp/f1.db
> CREATE TABLE t (foo text unique);
>
> $ echo .schema | sqlite3 /tmp/f2.db
> CREATE TABLE t (foo text);
>
> $ sqldiff --schema /tmp/f1.db /tmp/f2.db
> $
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite on IBM z/OS Unix

[Don V Nielsen]

This is what I was trying to remember. It is for calling LE Cobol, but it
demonstrates the use of the CEEENTRY and CEETERM macro that are used to
establish the environment without having to use a LE C stub program. I used
to use the Cobol stub method in the past.

https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.1.0/com.ibm.zos.v2r1.ceea400/priasm.htm



On Thu, Aug 30, 2018 at 4:13 PM David Jackson 
wrote:

> Thanks for the various replies to this question.
> I have my link working now. It starts off wit a basic 3 line c program (to
> establish the LE enclave) that then calls an Asm program that passes the
> SQL that then loads and branches to a c program to do the grunt work. There
> is an example IBM program that does something similar.
> That called c program  then returns and is re-invoked any number of times
> by the asm caller.  I now only open and close the db on the first and final
> call.
> So all is good as far as that goes .
>
> The challenge is to now determining a means of getting the query result set
> back to the calling asm program in a usable format.
> ___
> 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] sqlite on IBM z/OS Unix

[David Jackson]

Thanks for the various replies to this question.
I have my link working now. It starts off wit a basic 3 line c program (to
establish the LE enclave) that then calls an Asm program that passes the
SQL that then loads and branches to a c program to do the grunt work. There
is an example IBM program that does something similar.
That called c program  then returns and is re-invoked any number of times
by the asm caller.  I now only open and close the db on the first and final
call.
So all is good as far as that goes .

The challenge is to now determining a means of getting the query result set
back to the calling asm program in a usable format.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] beginners question

[Jürgen Palm]

Jens Alfke wrote:



On Aug 30, 2018, at 12:10 PM, Richard Hipp  wrote:

So, perhaps the solution is for Klaus to compile it himself from sources?

What about `sudo apt-get install sqlite3` ?

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

It would install an older version (3.22.0).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] beginners question

[Jens Alfke]

> On Aug 30, 2018, at 12:10 PM, Richard Hipp  wrote:
> 
> So, perhaps the solution is for Klaus to compile it himself from sources?

What about `sudo apt-get install sqlite3` ?

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

Re: [sqlite] beginners question

[Jürgen Palm]

Richard Hipp wrote:

On 8/30/18, Jürgen Palm  wrote:


Richard Hipp wrote:

On 8/30/18, Klaus Maas  wrote:

Same issues on Xubuntu 16.04 and 18.04

US international keyboard layout

Actually 2 questions:

1) The precompiled binary for Linux of sqlite3  does not run, but
sqldiff and sqlite3_analyzer do.
./sqlite3: error while loading shared libraries: libz.so.1: cannot open
shared object file: No such file or directory
What steps can I take to provide the missing library?

You have a desktop linux machine on which libz is not installed?  I
didn't even know that was possible.

What flavor of linux are you running?



He mentioned it in the first sentence: Xubuntu 16.04 and 18.04. Of
course, libz.so.1 is installed. I've currently running a virtual machine
with Xubuntu 18.04 and tried also to execute the precompiled binary and
it didn't work, but I didn't actually expect it as I'm running a 64bit
system. Most likely the problem from Klaus is caused by the same reason.

So, perhaps the solution is for Klaus to compile it himself from sources?

If he is indeed running a 64bit system he could also just install the 
package zlib1g:i386, which contains the 32bit version of libz.so.1. With 
this package installed, the precompiled sqlite binary is running 
properly on my 64bit system.

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

Re: [sqlite] beginners question

[Richard Hipp]

On 8/30/18, Jürgen Palm  wrote:
>
>
> Richard Hipp wrote:
>> On 8/30/18, Klaus Maas  wrote:
>>> Same issues on Xubuntu 16.04 and 18.04
>>>
>>> US international keyboard layout
>>>
>>> Actually 2 questions:
>>>
>>> 1) The precompiled binary for Linux of sqlite3  does not run, but
>>> sqldiff and sqlite3_analyzer do.
>>> ./sqlite3: error while loading shared libraries: libz.so.1: cannot open
>>> shared object file: No such file or directory
>>> What steps can I take to provide the missing library?
>> You have a desktop linux machine on which libz is not installed?  I
>> didn't even know that was possible.
>>
>> What flavor of linux are you running?
>>
>>
> He mentioned it in the first sentence: Xubuntu 16.04 and 18.04. Of
> course, libz.so.1 is installed. I've currently running a virtual machine
> with Xubuntu 18.04 and tried also to execute the precompiled binary and
> it didn't work, but I didn't actually expect it as I'm running a 64bit
> system. Most likely the problem from Klaus is caused by the same reason.

So, perhaps the solution is for Klaus to compile it himself from sources?

-- 
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] beginners question

[Jürgen Palm]

Richard Hipp wrote:

On 8/30/18, Klaus Maas  wrote:

Same issues on Xubuntu 16.04 and 18.04

US international keyboard layout

Actually 2 questions:

1) The precompiled binary for Linux of sqlite3  does not run, but
sqldiff and sqlite3_analyzer do.
./sqlite3: error while loading shared libraries: libz.so.1: cannot open
shared object file: No such file or directory
What steps can I take to provide the missing library?

You have a desktop linux machine on which libz is not installed?  I
didn't even know that was possible.

What flavor of linux are you running?


He mentioned it in the first sentence: Xubuntu 16.04 and 18.04. Of 
course, libz.so.1 is installed. I've currently running a virtual machine 
with Xubuntu 18.04 and tried also to execute the precompiled binary and 
it didn't work, but I didn't actually expect it as I'm running a 64bit 
system. Most likely the problem from Klaus is caused by the same reason.

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

Re: [sqlite] beginners question

[Richard Hipp]

On 8/30/18, Klaus Maas  wrote:
> Same issues on Xubuntu 16.04 and 18.04
>
> US international keyboard layout
>
> Actually 2 questions:
>
> 1) The precompiled binary for Linux of sqlite3  does not run, but
> sqldiff and sqlite3_analyzer do.
> ./sqlite3: error while loading shared libraries: libz.so.1: cannot open
> shared object file: No such file or directory
> What steps can I take to provide the missing library?

You have a desktop linux machine on which libz is not installed?  I
didn't even know that was possible.

What flavor of linux are you running?


>
> 2) When following the compile instructions on the website I get an
> executable sqlite3, however:
>
> Letters, numbers work fine.
>
> When pressing arrow keys, pgup, pgdwn there key scan codes appear in the
> terminal.
>
> ^[[A^[[B for up-arrow and down-arrow.
>
> How do I fix that?

Run configure using either --enable-readline or --enable-editline,
depending on which library you have installed.  Or if you are
compiling manually, add -DHAVE_READLINE or -DHAVE_EDITLINE to the
compiler command-line, and then add the appropriate libraries to the
end.

But if you don't have libz on your system, probably you don't have
readline or editline either.

Again, what Linux distro is this?

-- 
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] Best practices for forward conversion of database formats?

[Scott Robison]

On Thu, Aug 30, 2018 at 11:10 AM Roger Binns  wrote:
>
> On 30/08/18 09:51, Randall Smith wrote:
> > is how to convert existing data from one DB format to another, given some 
> > arbitrary set of changes in the database schema in the interim.
>
> I use SQLite's user pragma.  It starts at zero.
>
> https://sqlite.org/pragma.html#pragma_user_version
>
> My code ends up looking like this:
>
> if user_version==0:
> CREATE TABLE IF NOT EXISTS events(key, time, message);
> PRAGMA user_version=1;
>
> if user_version==1:
> CREATE INDEX IF NOT EXISTS [events:message] ON events(message);
> PRAGMA user_version=2;
>
> if user_version==2:
> ALTER TABLE events ADD COLUMN severity;
> PRAGMA user_version=3;
>
> This ensures that the currently running code will upgrade the schema as
> needed.  Ensure the commands are wrapped in a transaction so they either
> completely happen or not.
>
> I am helped by having low complexity schemas.  If yours are large you
> could probably generate something like the above.  Some ORM style
> engines also have schema and data upgrade functionality.

One place I worked for had a proprietary ORM style interface that was
intended to bridge the gap between an older legacy database and
SQLite. It's upgrade process involved an in memory data structure that
listed each upgrade step (add a column, move data, drop a column,
create a table, etc) then for each step, read each row of data, write
a custom insert statement, prepare, execute, finalize, discard, lather
rinse repeat. It was very slow. Very very slow. I don't think it is
possible to qualify it with sufficient verys to convey just how slow
it was.

I replaced it with a straight forward implementation that did a direct
SQL statement migration from the old schema to the new schema. Begin a
transaction, one statement per table, commit the transaction. We wound
up going from as much as 48 hours to migrate a 10 GB or so DB to about
5 to 15 minutes (it's been a while, I don't remember exactly).

If you have a simple schema and/or a small data set, the ORM migration
approach might work well for you. If you have a complex schema and
especially if you have a large data set, I'd encourage you to look
into alternatives to an ORM approach. I don't think all ORMs would
necessarily be as slow as the one I worked with was, but it was just
the wrong tool for the job in that particular case.

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

Re: [sqlite] Best practices for forward conversion of database formats?

[Simon Slavin]

On 30 Aug 2018, at 5:51pm, Randall Smith  wrote:

> Are there accepted or best practices in the industry for handling this in 
> general, or with SQLite in particular?  Can anyone who has implemented this 
> make useful suggestions?  Or are there published resources I am missing?

Roger has provided an example of upgrading databases within SQLite, where your 
existing database is in SQLite, and you intend to continue working with SQLite. 
 However, a truly paranoid organisation might think SQLite might fail entirely. 
 It would institute a requirement that it be easy to export your data from 
SQLite in a form readable by other systems.

SQLite allows this using its precompiled binary tool.  This allows you to turn 
any SQLite database into a text file containing the SQL commands required to 
reconstruct that database.  The text can be immediately processed by any other 
program which understands SQL, or it can be processed by any text-processing 
program to massage the format into an alternative format some other SQL system 
might prefer.

Unless you feel that nothing that understands SQL will survive, this should 
satisfy such a requirement.

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

[sqlite] beginners question

[Klaus Maas]

Same issues on Xubuntu 16.04 and 18.04

US international keyboard layout

Actually 2 questions:

1) The precompiled binary for Linux of sqlite3  does not run, but 
sqldiff and sqlite3_analyzer do.
./sqlite3: error while loading shared libraries: libz.so.1: cannot open 
shared object file: No such file or directory

What steps can I take to provide the missing library?

2) When following the compile instructions on the website I get an 
executable sqlite3, however:


Letters, numbers work fine.

When pressing arrow keys, pgup, pgdwn there key scan codes appear in the 
terminal.


^[[A^[[B for up-arrow and down-arrow.

How do I fix that?


Klaus


email signature Klaus Maas

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

Re: [sqlite] Strategies to reduce page-loads?

[David Raymond]

I know the CLI has the .stats option and the .scanstats option. I don't exactly 
what they implement, but here's an example output from a very quick query on 
something after turning both of those on. I think the page cache numbers are 
probably what you're most interested in. (I vaguely recall that things like the 
Virtual Machine steps will quietly overflow, so may show negative or way lower 
than you might expect)

More interesting queries will probably give more interesting numbers to look at.


sqlite> select recordtype, count(*) from foo where bar = 'something' group by 
recordtype order by recordtype;
QUERY PLAN
`--SEARCH TABLE foo USING COVERING INDEX idx_foo (bar=?)
recordtype|count(*)
F|48
G|1
H|540
P|68
S|2133
Memory Used: 885360 (max 888328) bytes
Number of Outstanding Allocations:   1142 (max 1166)
Number of Pcache Overflow Bytes: 710416 (max 710416) bytes
Largest Allocation:  12 bytes
Largest Pcache Allocation:   4360 bytes
Lookaside Slots Used:45 (max 100)
Successful lookaside attempts:   906
Lookaside failures due to size:  20
Lookaside failures due to OOM:   35
Pager Heap Usage:708616 bytes
Page cache hits: 46
Page cache misses:   162
Page cache writes:   0
Page cache spills:   0
Schema Heap Usage:   46624 bytes
Statement Heap/Lookaside Usage:  37168 bytes
Fullscan Steps:  0
Sort Operations: 0
Autoindex Inserts:   0
Virtual Machine Steps:   22395
Reprepare operations:0
Number of times run: 1
Memory used by prepared stmt:37168
 scanstats 
 subquery 8 ---
Loop  1: SEARCH TABLE foo USING COVERING INDEX idx_foo (bar=?)
 nLoop=1nRow=2790 estRow=1024 estRow/Loop=1024
---
Run Time: real 0.032 user 0.00 sys 0.00

sqlite>



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of t...@qvgps.com
Sent: Thursday, August 30, 2018 12:28 PM
To: SQLite mailing list
Subject: Re: [sqlite] Strategies to reduce page-loads?

It would be interesting to "measure" the effect of these ideas during 
the process of optimizing.
I can profile and measure the execution times, but also interesting 
would be to know, how much pages are involved in a specific query.

Is there maybe a way to get the count of pages currently used?



-- Originalnachricht --
Von: "Richard Hipp" 
An: "SQLite mailing list" 
Gesendet: 30.08.2018 13:48:30
Betreff: Re: [sqlite] Strategies to reduce page-loads?

>On 8/30/18, t...@qvgps.com  wrote:
>>
>>Structure is  simple:
>>CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates
>>BLOB, Flags INT, StyleId INT);
>>And an rtree-index:
>>CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0
>>FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);
>
>Three points that might help, either separately or in combination:
>
>(1) Use exactly "INTEGER PRIMARY KEY".  "LONG PRIMARY KEY" and "INT
>PRIMARY KEY" are not the same thing and do not work as well.
>
>(2) In the very latest versions of SQLite, 3.24,0 and the beta for
>3.25.0, you can put the "Lines" information directly in the RTree:
>
>   CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0,
>z1, +Label, +Coordinates, +Flags, +StyleId);
>
>The extra columns in r-tree are prefaced by a "+" character so that
>the r-tree module knows that they are auxiliary columns and not extra
>coordinates.
>
>(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on
>a "Morton code" or "Z-Order curve" of the coordinates.
>(https://en.wikipedia.org/wiki/Z-order_curve)  That will cause
>features that are close together geographically to tend to be close
>together within the file.  There is are two extension functions in the
>https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the
>SQLite source tree that might help you with this.  Or you can do the
>same using your own functions.
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-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] Best practices for forward conversion of database formats?

[Roger Binns]

On 30/08/18 09:51, Randall Smith wrote:
> is how to convert existing data from one DB format to another, given some 
> arbitrary set of changes in the database schema in the interim.

I use SQLite's user pragma.  It starts at zero.

https://sqlite.org/pragma.html#pragma_user_version

My code ends up looking like this:

if user_version==0:
CREATE TABLE IF NOT EXISTS events(key, time, message);
PRAGMA user_version=1;

if user_version==1:
CREATE INDEX IF NOT EXISTS [events:message] ON events(message);
PRAGMA user_version=2;

if user_version==2:
ALTER TABLE events ADD COLUMN severity;
PRAGMA user_version=3;

This ensures that the currently running code will upgrade the schema as
needed.  Ensure the commands are wrapped in a transaction so they either
completely happen or not.

I am helped by having low complexity schemas.  If yours are large you
could probably generate something like the above.  Some ORM style
engines also have schema and data upgrade functionality.

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best practices for forward conversion of database formats?

[Randall Smith]

This is not strictly a SQLite question, though in my case it is.

Any application that involves a "persistent" database, i.e., one where the data 
is long-lived and expected to exist and evolve over time, sooner or later has 
the issue of moving customers from a V1 database to a V2 database.  Obviously 
at least one technical issue (there are probably others) is how to convert 
existing data from one DB format to another, given some arbitrary set of 
changes in the database schema in the interim.

Are there accepted or best practices in the industry for handling this in 
general, or with SQLite in particular?  Can anyone who has implemented this 
make useful suggestions?  Or are there published resources I am missing?

Thanks in advance for whatever you can suggest.

Randall.

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

Re: [sqlite] Strategies to reduce page-loads?

[t...@qvgps.com]

It would be interesting to "measure" the effect of these ideas during 
the process of optimizing.
I can profile and measure the execution times, but also interesting 
would be to know, how much pages are involved in a specific query.

Is there maybe a way to get the count of pages currently used?



-- Originalnachricht --
Von: "Richard Hipp" 
An: "SQLite mailing list" 
Gesendet: 30.08.2018 13:48:30
Betreff: Re: [sqlite] Strategies to reduce page-loads?

>On 8/30/18, t...@qvgps.com  wrote:
>>
>>Structure is  simple:
>>CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates
>>BLOB, Flags INT, StyleId INT);
>>And an rtree-index:
>>CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0
>>FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);
>
>Three points that might help, either separately or in combination:
>
>(1) Use exactly "INTEGER PRIMARY KEY".  "LONG PRIMARY KEY" and "INT
>PRIMARY KEY" are not the same thing and do not work as well.
>
>(2) In the very latest versions of SQLite, 3.24,0 and the beta for
>3.25.0, you can put the "Lines" information directly in the RTree:
>
>   CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0,
>z1, +Label, +Coordinates, +Flags, +StyleId);
>
>The extra columns in r-tree are prefaced by a "+" character so that
>the r-tree module knows that they are auxiliary columns and not extra
>coordinates.
>
>(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on
>a "Morton code" or "Z-Order curve" of the coordinates.
>(https://en.wikipedia.org/wiki/Z-order_curve)  That will cause
>features that are close together geographically to tend to be close
>together within the file.  There is are two extension functions in the
>https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the
>SQLite source tree that might help you with this.  Or you can do the
>same using your own functions.
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite on IBM z/OS Unix

[John McKown]

On Thu, Aug 30, 2018 at 11:12 AM Don V Nielsen 
wrote:

> "Having successfully ported sqlite to z/OS Unix as a 32 bit app"
>
> Totally Awesome! Do I have a solution? No. But I'll bet John McKown will. I
> believe he is a guru with the mainframe.
>

I don't know about being a "guru". Personally, all my HLASM for UNIX
program is LE based (starts up via a CEEENTRY macro) just so that I can use
C language subroutines. This doesn't seem to have any real drawbacks, other
than some learning and recoding the startup/return stuff. The parameter
passing is the same and an HLASM LE routine can do anything that a non-LE
can do, as best as I know.

If anyone is curious about a z/OS UNIX program written in LE HLASM, here:
https://github.com/JohnArchieMckown/utilities-1/blob/master/lsenq.s

This program also shows how to get to the UNIX arguments from the shell
command line. It is not as simple as in z/OS batch. More akin to a TSO
command processor, albeit different.



>
> It is not SqlLite. It is that communication mechanism between the non-LE
> program calling into the LE environment. Are you saying the LE is loading
> and not unloading. I would guess that it is, and it is that which is
> closing the connection. I'm sorry, but I'm 10+ years past working with
> mainframes. But I recall having to do something special when calling
> LE-Cobol from assembler. There was something that needed to be communicated
> to say "Get up and stay up until I tell you to close", otherwise, you are
> continually loading and unloaded LE with every call.
>
> Wish I could be more helpful,
> dvn
>
> On Wed, Aug 29, 2018 at 5:26 PM David Jackson 
> wrote:
>
> > Having successfully ported sqlite to z/OS Unix as a 32 bit app, I am now
> > looking at a c program to make SQL calls to this.
> > Starting with an Assembler routine that runs within z/OS (not Unix),
> which
> > is not LE (Language Environment)enabled, we then call a c routine
> (numerous
> > times) that is LE enabled. That is all working fine and making good SQL
> > calls to sqlite. the c program then returns back to the upper assembler
> > calling program. The problem is that the c routines is opening, issuing
> the
> > SQL and closing on each invocation.
> >
> > Now this may be a dumb question, so apologies up front.
> > Is there any way that the c program can open the sqlite db initially on
> the
> > first call and keep it open after it returns back to the calling program
> > until a final call at which point it will issue the sqlite3_close.
> >
> > Again - sorry if this was a dumb question.
> >
> > Thanks
> > ___
> > 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
>


-- 
People who frustrate us will be around for as long as we need them.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite on IBM z/OS Unix

[Don V Nielsen]

"Having successfully ported sqlite to z/OS Unix as a 32 bit app"

Totally Awesome! Do I have a solution? No. But I'll bet John McKown will. I
believe he is a guru with the mainframe.

It is not SqlLite. It is that communication mechanism between the non-LE
program calling into the LE environment. Are you saying the LE is loading
and not unloading. I would guess that it is, and it is that which is
closing the connection. I'm sorry, but I'm 10+ years past working with
mainframes. But I recall having to do something special when calling
LE-Cobol from assembler. There was something that needed to be communicated
to say "Get up and stay up until I tell you to close", otherwise, you are
continually loading and unloaded LE with every call.

Wish I could be more helpful,
dvn

On Wed, Aug 29, 2018 at 5:26 PM David Jackson 
wrote:

> Having successfully ported sqlite to z/OS Unix as a 32 bit app, I am now
> looking at a c program to make SQL calls to this.
> Starting with an Assembler routine that runs within z/OS (not Unix), which
> is not LE (Language Environment)enabled, we then call a c routine (numerous
> times) that is LE enabled. That is all working fine and making good SQL
> calls to sqlite. the c program then returns back to the upper assembler
> calling program. The problem is that the c routines is opening, issuing the
> SQL and closing on each invocation.
>
> Now this may be a dumb question, so apologies up front.
> Is there any way that the c program can open the sqlite db initially on the
> first call and keep it open after it returns back to the calling program
> until a final call at which point it will issue the sqlite3_close.
>
> Again - sorry if this was a dumb question.
>
> Thanks
> ___
> 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] Strategies to reduce page-loads?

[David Raymond]

People being very specific about "integer primary key" is about another 
optimization by the way, and not because using "long" or "int" as a your data 
type is wrong in any way. Simply that "integer" primary key is needed for the 
optimization.

See: https://www.sqlite.org/lang_createtable.html#rowid



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of t...@qvgps.com
Sent: Thursday, August 30, 2018 8:18 AM
To: SQLite mailing list
Subject: Re: [sqlite] Strategies to reduce page-loads?

Thanks guys for quick and competent answers!

After first sight, this "Z-Order curve" looks very promising, will give 
it a try.

The LONG PRIMARY KEY is because  I need a 64-bit integer (osm-id).
I only learned now, that sqlite-int is also 64 bit long.
Will change to INT PRIMARY KEY now.

Tom


-- Originalnachricht --
Von: "Richard Hipp" 
An: "SQLite mailing list" 
Gesendet: 30.08.2018 13:48:30
Betreff: Re: [sqlite] Strategies to reduce page-loads?

>On 8/30/18, t...@qvgps.com  wrote:
>>
>>Structure is  simple:
>>CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates
>>BLOB, Flags INT, StyleId INT);
>>And an rtree-index:
>>CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0
>>FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);
>
>Three points that might help, either separately or in combination:
>
>(1) Use exactly "INTEGER PRIMARY KEY".  "LONG PRIMARY KEY" and "INT
>PRIMARY KEY" are not the same thing and do not work as well.
>
>(2) In the very latest versions of SQLite, 3.24,0 and the beta for
>3.25.0, you can put the "Lines" information directly in the RTree:
>
>   CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0,
>z1, +Label, +Coordinates, +Flags, +StyleId);
>
>The extra columns in r-tree are prefaced by a "+" character so that
>the r-tree module knows that they are auxiliary columns and not extra
>coordinates.
>
>(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on
>a "Morton code" or "Z-Order curve" of the coordinates.
>(https://en.wikipedia.org/wiki/Z-order_curve)  That will cause
>features that are close together geographically to tend to be close
>together within the file.  There is are two extension functions in the
>https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the
>SQLite source tree that might help you with this.  Or you can do the
>same using your own functions.
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-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] Strategies to reduce page-loads?

[Richard Hipp]

On 8/30/18, t...@qvgps.com  wrote:
> Will change to INT PRIMARY KEY now.

It must be INTEGER PRIMARY KEY - spelled out.  INT PRIMARY KEY won't
work.  This is a quirk of SQLite.
-- 
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] Strategies to reduce page-loads?

[t...@qvgps.com]

Thanks guys for quick and competent answers!

After first sight, this "Z-Order curve" looks very promising, will give 
it a try.

The LONG PRIMARY KEY is because  I need a 64-bit integer (osm-id).
I only learned now, that sqlite-int is also 64 bit long.
Will change to INT PRIMARY KEY now.

Tom


-- Originalnachricht --
Von: "Richard Hipp" 
An: "SQLite mailing list" 
Gesendet: 30.08.2018 13:48:30
Betreff: Re: [sqlite] Strategies to reduce page-loads?

>On 8/30/18, t...@qvgps.com  wrote:
>>
>>Structure is  simple:
>>CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates
>>BLOB, Flags INT, StyleId INT);
>>And an rtree-index:
>>CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0
>>FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);
>
>Three points that might help, either separately or in combination:
>
>(1) Use exactly "INTEGER PRIMARY KEY".  "LONG PRIMARY KEY" and "INT
>PRIMARY KEY" are not the same thing and do not work as well.
>
>(2) In the very latest versions of SQLite, 3.24,0 and the beta for
>3.25.0, you can put the "Lines" information directly in the RTree:
>
>   CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0,
>z1, +Label, +Coordinates, +Flags, +StyleId);
>
>The extra columns in r-tree are prefaced by a "+" character so that
>the r-tree module knows that they are auxiliary columns and not extra
>coordinates.
>
>(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on
>a "Morton code" or "Z-Order curve" of the coordinates.
>(https://en.wikipedia.org/wiki/Z-order_curve)  That will cause
>features that are close together geographically to tend to be close
>together within the file.  There is are two extension functions in the
>https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the
>SQLite source tree that might help you with this.  Or you can do the
>same using your own functions.
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strategies to reduce page-loads?

[Richard Hipp]

On 8/30/18, t...@qvgps.com  wrote:
>
> Structure is  simple:
> CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates
> BLOB, Flags INT, StyleId INT);
> And an rtree-index:
> CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0
> FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);

Three points that might help, either separately or in combination:

(1) Use exactly "INTEGER PRIMARY KEY".  "LONG PRIMARY KEY" and "INT
PRIMARY KEY" are not the same thing and do not work as well.

(2) In the very latest versions of SQLite, 3.24,0 and the beta for
3.25.0, you can put the "Lines" information directly in the RTree:

   CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0,
z1, +Label, +Coordinates, +Flags, +StyleId);

The extra columns in r-tree are prefaced by a "+" character so that
the r-tree module knows that they are auxiliary columns and not extra
coordinates.

(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on
a "Morton code" or "Z-Order curve" of the coordinates.
(https://en.wikipedia.org/wiki/Z-order_curve)  That will cause
features that are close together geographically to tend to be close
together within the file.  There is are two extension functions in the
https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the
SQLite source tree that might help you with this.  Or you can do the
same using your own functions.
-- 
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] Strategies to reduce page-loads?

[Clemens Ladisch]

t...@qvgps.com wrote:
> The larger the db, the slower is the fetching!
>
> My assumption is, that in the big db, these 1000 lines are just spread over a 
> much higher count of pages.
> So more page-loads resulting in more time.

Correct.

> We changed page_size to the maximum value of 64k and it became much better, 
> but still I would lke to improve it.

Store the table entries so that nearby objects are stored nearby.  One
way to do that would be to insert the ways ordered first by Z, then by
the position on a space-filling curve (e.g., Morton order
).


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

[sqlite] Strategies to reduce page-loads?

[t...@qvgps.com]

Hi Guys,

we are using SQlite for storing OpenStreetMap ways (lines).
Once filled its readonly.
Then the lines is queried for specific areas to draw a map.

Structure is  simple:
CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates 
BLOB, Flags INT, StyleId INT);
And an rtree-index:
CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0 
FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);

Queries are always all lines of a specific geographical ares, which is 
very fast because of the rtree.
SELECT Lines.* FROM Lines_bb , Lines WHERE y0 < ? AND y1 > ? AND x0 < ? 
AND x1 > ? AND ? BETWEEN z0 AND z1 AND Lines_bb.Id = Lines.Id ;

Then the rows are fetched and here starts the problems:
var stmt = SQLite3.Prepare2 (handle, select);
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
// fetch row
}

The larger the db, the slower is the fetching!

We compared a small db (50mb) and a big db (500mb), both containing the 
same small area:
Reading the same area of  for example 1000 lines from the small db is 2x 
faster then from the large db.
After doing some profiling, it turned out, that the extra time was spent 
in SQLite3.Step.

My assumption is, that in the big db, these 1000 lines are just spread 
over a much higher count of pages.
So more page-loads resulting in more time.

We changed page_size to the maximum value of 64k and it became much 
better, but still I would lke to improve it.

Thanks,
Tom



/
** Flemming Software Development CC
** Thomas Flemming
** PO Box 81244
** Windhoek, Namibia
** http://www.quovadis-gps.com
** mail t...@qvgps.com
** +264 (0)81 3329923 Nam mobile
** +49 (0)175 7313081 D mobile
** +49 (0)6182 8492599 D office
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users