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

[dmp]

Randall wrote:
~
~
> 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?

Hello,

I should suppose pretense with I'm wholly ignorant to such a process, but
even
has such may have some wisdom. The only reason I'm replying because I have
been
working on I guess an ORM, data converter it seems.

One, it seems data is best preserved in a standard raw format such as CSV.
SQL
statements from one database do not translate to another databases well and
perhaps of the same database v1 to v2.

Two, with changes to schema it seems the simple way to go would be export
data
in a query fashion, to match the new schema. Leave out columns not needed and
a default for new.

Three, this in hindsight, but data should be always be in it simplest form as
possible, Integer, Char, etc. The most intolerant data is temporal
information.
For these ALWAYS use a SQL or a common appropriate standard.

danap.

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

[Ling, Andy]

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

Several have given examples of holding a schema version number in the database 
and
having your application know how to upgrade from one to the next. I have used
similar mechanisms, but one thing I would say. If you have to maintain older 
versions
of your app, make a jump in the schema version when you switch major version of 
you app.

For example, if you have version 1.5 of your app and you move to version 2.0, 
then you
need to make a version 1.6 for some reason and it needs a schema change. If you 
jumped
to say, schema version 100 for app 2.0, it is much easier to add a new schema 
version for 1.6
that can then be handled by 2.x

Also, one system I worked on used SQL script files to make the database 
changes. e.g. There would be
a script that updated from schema 2 to 3. To go with this there would also be a 
script that
downgraded from schema 3 to 2. These scripts where left by the installer, so if 
you needed
to install an earlier version of code, it could find the "downgrade" scripts 
and run them
without the earlier version of code having to know how to downgrade.

HTH

Andy Ling



**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
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?

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

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] Best practices for huge batch inserts with WAL?

[Clemens Ladisch]

Scott Hess wrote:
> WRT #3, you could also consider journal_mode off (or memory, if your code
> requires transactions to work right).  In that case, the database state is
> indeterminate if you have an app-level crash, but you should be fine if you
> make it to the end.

It would be a better idea to change to the rollback journal mode (DELETE/
TRUNCATE/PERSIST) - it's guaranteed to work correctly in all situations,
and with a small database to be filled, the journal cannot become large
because it stores only _old_ data.


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

Re: [sqlite] Best practices for huge batch inserts with WAL?

[Scott Hess]

What I'd do:

1) Look at the indices, and make sure the input is sorted to insert in
index order.  Also drop any unnecessary indices and add them back at the
end.  [Read the code for vacuum to see what kinds of things make sense to
defer.]

2) Bump up the cache a lot.  Inserting in sorted order makes this less
essential, but for a one-off like this it can't hurt.

3) Turn synchronous off while loading, and commit to starting over if you
get an OS-level crash.  Even with synchronous off, things should be
correctly ordered for app-level crashes.

4) Maybe use exclusive locking?  That wouldn't have a huge impact if you're
batching inserts, I expect.

---

WRT #3, you could also consider journal_mode off (or memory, if your code
requires transactions to work right).  In that case, the database state is
indeterminate if you have an app-level crash, but you should be fine if you
make it to the end.

WRT #1, I would consider fts as an index for these purposes, but it may
require schema changes to make is possible to selectively disable/enable
the indexing.  See https://sqlite.org/fts5.html#external_content_tables .
I'm not sure there would be much gain from disabling fts when loading,
though, as long as your bulk transactions are large.

-scott


On Mon, Apr 24, 2017 at 11:00 AM, Jens Alfke  wrote:

> I’m importing a large data set with a lot of rows — an entire Wikipedia
> dump, about 60GB, one article per row — into a brand new SQLite database in
> WAL mode. What’s the fastest way to import it?
>
> I started with one big transaction, but noted that (of course) the WAL
> file was growing rapidly while the main database file stayed tiny. I
> figured this would become inefficient, so I stopped the run and adjusted my
> code to commit and re-open a transaction every 10,000 rows.
>
> With that, the import started quickly, but as time went on the commits
> were taking longer and longer, so the process was spending most of its time
> committing. (I wasn’t able to finish the job, as it ran into an unrelated
> fatal error in my code about ⅔ of the way through.)
>
> Would it have been faster to use a single transaction? Even if the commit
> at the end is epic-length, it wouldn’t be rewriting the b-tree nodes over
> and over again. If so, would periodic WAL checkpoints help?
>
> —Jens
> ___
> 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 huge batch inserts with WAL?

[John Found]

Do you have some indices on the table? Drop them before the huge insert and 
then recreate again. It is faster.

On Mon, 24 Apr 2017 11:00:00 -0700
Jens Alfke  wrote:

> I’m importing a large data set with a lot of rows — an entire Wikipedia dump, 
> about 60GB, one article per row — into a brand new SQLite database in WAL 
> mode. What’s the fastest way to import it?
> 
> I started with one big transaction, but noted that (of course) the WAL file 
> was growing rapidly while the main database file stayed tiny. I figured this 
> would become inefficient, so I stopped the run and adjusted my code to commit 
> and re-open a transaction every 10,000 rows.
> 
> With that, the import started quickly, but as time went on the commits were 
> taking longer and longer, so the process was spending most of its time 
> committing. (I wasn’t able to finish the job, as it ran into an unrelated 
> fatal error in my code about ⅔ of the way through.)
> 
> Would it have been faster to use a single transaction? Even if the commit at 
> the end is epic-length, it wouldn’t be rewriting the b-tree nodes over and 
> over again. If so, would periodic WAL checkpoints help?
> 
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best practices for huge batch inserts with WAL?

[Jens Alfke]

I’m importing a large data set with a lot of rows — an entire Wikipedia dump, 
about 60GB, one article per row — into a brand new SQLite database in WAL mode. 
What’s the fastest way to import it?

I started with one big transaction, but noted that (of course) the WAL file was 
growing rapidly while the main database file stayed tiny. I figured this would 
become inefficient, so I stopped the run and adjusted my code to commit and 
re-open a transaction every 10,000 rows.

With that, the import started quickly, but as time went on the commits were 
taking longer and longer, so the process was spending most of its time 
committing. (I wasn’t able to finish the job, as it ran into an unrelated fatal 
error in my code about ⅔ of the way through.)

Would it have been faster to use a single transaction? Even if the commit at 
the end is epic-length, it wouldn’t be rewriting the b-tree nodes over and over 
again. If so, would periodic WAL checkpoints help?

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

[sqlite] Best Practices

[R.Smith]

On 2015-10-01 01:08 AM, eluken at pentarch.org wrote:
> Well, when you put it like that :)
>
> I might allow the user to change the name based on the game name. But 
> there again, special characters.  Ideally, the functionality would 
> allow for multiple databases.
>
> I also need to figure out how to upgrade the database when I have a 
> database upgrade, and maintain current data.  That is probably just a 
> series of SQL commands creating temp tables and copying the new 
> structure over.

Yep. Should be that easy. Just make, debug and test your scripts well 
before releasing them - and be sure the DB's against which they run are 
version-checked so that script for upgrading version 3 to version 4 
doesn't get run on a version 1, 2 or 5 DB file.
In my systems mostly I add SQL for upgrading every version to the next, 
so whatever version DB I encounter (and as long as I can deduce which 
version DB it is), I can simply walk it through the upgrades until the 
version is current.

You can use PRAGMA user_version, see:
https://www.sqlite.org/pragma.html#pragma_schema_version

>
> On a slight tangent, is there a way to verify database integrity?

https://www.sqlite.org/faq.html#q21
https://www.sqlite.org/pragma.html#pragma_quick_check
https://www.sqlite.org/pragma.html#pragma_integrity_check

[sqlite] Best Practices

[Simon Slavin]

On 1 Oct 2015, at 12:08am, eluken at pentarch.org wrote:

> Well, when you put it like that :)
> 
> I might allow the user to change the name based on the game name. But there 
> again, special characters.  Ideally, the functionality would allow for 
> multiple databases.

You could make a rule that only letters and digits are allowed.

Or you could do what lots of games do for their savepoints: keep the filenames 
as numbers and create another database which contains a table matching the 
numbers with whatever the user thinks the database is called.

> I also need to figure out how to upgrade the database when I have a database 
> upgrade, and maintain current data.  That is probably just a series of SQL 
> commands creating temp tables and copying the new structure over.

That's one way to do it.  Helped by you keeping the database-format-version 
number somewhere in the database, probably in a 'configuration' table.

> On a slight tangent, is there a way to verify database integrity?



Simon.

[sqlite] Best Practices

[elu...@pentarch.org]

Well, when you put it like that :)

I might allow the user to change the name based on the game name. But there 
again, special characters.  Ideally, the functionality would allow for 
multiple databases.

I also need to figure out how to upgrade the database when I have a database 
upgrade, and maintain current data.  That is probably just a series of SQL 
commands creating temp tables and copying the new structure over.

On a slight tangent, is there a way to verify database integrity?

-Original Message- 
From: Simon Slavin
Sent: Tuesday, September 29, 2015 6:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Best Practices


On 30 Sep 2015, at 12:05am, eluken at pentarch.org wrote:

> I am using SQLite as the backend for a table-top wargame aid. One of the 
> features I am including in the aid is allowing the user to change the name 
> of the database. What would be the best way to do so? Inside the 
> filesystem? Or some feature in SQLite to copy the database to a new name? 
> Ideally, all of the data within the database will be transferred as well.

Are you absolutely sure you want your users to choose filenames ?  Do they 
know all the filename rules for the platform they're using ?  Will they try 
to include question marks, series of dots, backslashes, or other illegal 
characters ?  Will you do the testing for checking to see if there's already 
a folder or file with the name they select ?

If you're sure you want to do it, make sure the database is not open while 
you rename it, and rename it using normal file commands, not SQLite.

There is a SQLite Backup API which can be used to take a copy of the 
database under a new name.  But this is perhaps not what you want if you 
just want to rename it.

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

[sqlite] Best Practices

[Simon Slavin]

On 30 Sep 2015, at 12:05am, eluken at pentarch.org wrote:

> I am using SQLite as the backend for a table-top wargame aid. One of the 
> features I am including in the aid is allowing the user to change the name of 
> the database. What would be the best way to do so? Inside the filesystem? Or 
> some feature in SQLite to copy the database to a new name? Ideally, all of 
> the data within the database will be transferred as well.

Are you absolutely sure you want your users to choose filenames ?  Do they know 
all the filename rules for the platform they're using ?  Will they try to 
include question marks, series of dots, backslashes, or other illegal 
characters ?  Will you do the testing for checking to see if there's already a 
folder or file with the name they select ?

If you're sure you want to do it, make sure the database is not open while you 
rename it, and rename it using normal file commands, not SQLite.

There is a SQLite Backup API which can be used to take a copy of the database 
under a new name.  But this is perhaps not what you want if you just want to 
rename it.

Simon.

[sqlite] Best Practices

[Richard Hipp]

On 9/29/15, eluken at pentarch.org  wrote:
> I am using SQLite as the backend for a table-top wargame aid. One of the
> features I am including in the aid is allowing the user to change the name
> of the database. What would be the best way to do so? Inside the filesystem?
> Or some feature in SQLite to copy the database to a new name? Ideally, all
> of the data within the database will be transferred as well.

https://www.sqlite.org/backup.html
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Best Practices

[elu...@pentarch.org]

I am using SQLite as the backend for a table-top wargame aid. One of the 
features I am including in the aid is allowing the user to change the name of 
the database. What would be the best way to do so? Inside the filesystem? Or 
some feature in SQLite to copy the database to a new name? Ideally, all of the 
data within the database will be transferred as well.

Re: [sqlite] Best practices for create derived SQLite tables

[Simon Slavin]

On 2 Jun 2013, at 8:26pm, James Mahon  wrote:

> I'm trying to find the best method to create new tables in SQLite derived
> from a select statement on an existing SQLite table. I've currently tried
> the two methods, one in Python and one in the SQLite command shell (both
> illustrated below). Although I generally prefer to code in Python, I find
> it much slower. I also find the SQLite command line much slower when I use
> the INSERT INTO ... SELECT query. What are best coding practices in
> building derived SQLite tables?

The delay is partly the effort of moving all the data about and partly the task 
of searching through your panels table for rows where sales >= 3.  All of 
your methods would be faster if you already had an index on panel.panel(sales) 
.  If you're going to do this more than once and speed is an issue it might be 
an idea to make that index.

>sales decimal(30,4)

SQLite doesn't have a decimal type.  It is interpreting these numbers as REAL 
numbers and it will not truncate them to four digits.  If you are storing 
numbers which are inherently integer define the columns as INTEGER and multiply 
the numbers by (in your case) 1 before storing them.  This will speed up 
everything including searching, sorting and maths, but only a bit.

Lastly, the question of why you need to make this second table anyway.  If 
you're just to be reading from it (rather than modifying it) then it will be 
more efficient to make a VIEW.  Add the index I wrote above then do something 
like

CREATE VIEW clients AS
   SELECT
   cust_id,
   year,
   clerk_id,
   sales
   FROM panel.panel
   WHERE sales >= 3;

Note that creating the VIEW does not duplicate the data in the original table.  
It's a way of saving a SELECT statement for convenient use, not a way of 
copying the data from the table.  Consequently, creating the view takes hardly 
any time.  You might want your view to be temporary.  Read about it here:



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

[sqlite] Best practices for create derived SQLite tables

[James Mahon]

Hello,

I'm trying to find the best method to create new tables in SQLite derived
from a select statement on an existing SQLite table. I've currently tried
the two methods, one in Python and one in the SQLite command shell (both
illustrated below). Although I generally prefer to code in Python, I find
it much slower. I also find the SQLite command line much slower when I use
the INSERT INTO ... SELECT query. What are best coding practices in
building derived SQLite tables?

Python example:
conn = sqlite3.connect("example.db')
c = conn.cursor()
query = '''
CREATE TABLE clients (
cust_id int,
year int,
clerk_id int,
sales decimal(30,4)
PRIMARY KEY (cust_id, year)
);'''
c.execute(query)
query = '''
ATTACH '%s' AS panel;''' % ( 'panel.db' )
c.execute(query)
query = '''
INSERT INTO clients (
cust_id,
year,
clerk_id,
sales)
SELECT
cust_id,
year,
clerk_id,
sales
FROM panel.panel
WHERE sales >= 3;''
c.execute(query)
conn.commit()


SQLite command shell example:
ATTACH 'panel.db' AS panel;
.output ../temp/clients.txt
SELECT
cust_id,
year,
clerk_id,
sales
FROM panel.panel
WHERE sales >= 3;
CREATE TABLE clients (
cust_id int,
year int,
clerk_id int,
sales decimal(30,4)
PRIMARY KEY (cust_id, year)
);
.import ../temp/clients.txt clients
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best practices for Valgrind

[Richard Hipp]

On Wed, Dec 5, 2012 at 6:26 PM, Paul Menzel <
paulepan...@users.sourceforge.net> wrote:

> Dear SQLite folks,
>
>
> using Debian Sid/unstable with SQLite 3.7.14.1-1, running the WebKit
> based browser Midori 0.4.7+ from master branch (7567058e) [1] under
> Valgrind to analyze WebKit 1.8.1 though, I see a lot of the following.
>
>
> I am guessing Valgrind is confused by SQLite’s memory allocator and
> therefore these can be ignored. Do you have some Valgrind suppression
> files to be used with SQLite using programs like PyGObject has [1]?
>

No.  We run SQLite in valgrind all the time, without issues.  The reports
from valgrind indicate that Midori is leaking SQLite resources, by not
finalizing all prepared statements, closing all database connections, or
something like that.


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

[sqlite] Best practices for Valgrind

[Paul Menzel]

Dear SQLite folks,


using Debian Sid/unstable with SQLite 3.7.14.1-1, running the WebKit
based browser Midori 0.4.7+ from master branch (7567058e) [1] under
Valgrind to analyze WebKit 1.8.1 though, I see a lot of the following.

==31797== 64,008 bytes in 1 blocks are possibly lost in loss record 
10,807 of 10,810
==31797==at 0x48288D8: malloc (vg_replace_malloc.c:270)
==31797==by 0x5279F4A: sqlite3MemMalloc (sqlite3.c:15436)
==31797==by 0x525519D: mallocWithAlarm (sqlite3.c:18734)
==31797==by 0x525D336: sqlite3Malloc (sqlite3.c:18767)
==31797==by 0x5263C7D: setupLookaside.part.209 (sqlite3.c:112140)
==31797==by 0x52A68D8: openDatabase (sqlite3.c:112119)
==31797==by 0x52A6EB1: sqlite3_open16 (sqlite3.c:114103)
==31797==by 0x5BA470C: 
WebCore::SQLiteFileSystem::openDatabase(WTF::String const&, sqlite3**, bool) 
(in /usr/lib/libwebkitgtk-1.0.so.0.13.2)
==31797==by 0x5BA2DBC: WebCore::SQLiteDatabase::open(WTF::String 
const&, bool) (in /usr/lib/libwebkitgtk-1.0.so.0.13.2)
==31797==by 0x5A37023: 
WebCore::IconDatabase::iconDatabaseSyncThread() (in 
/usr/lib/libwebkitgtk-1.0.so.0.13.2)
==31797==by 0x5A371DA: 
WebCore::IconDatabase::iconDatabaseSyncThreadStart(void*) (in 
/usr/lib/libwebkitgtk-1.0.so.0.13.2)
==31797==by 0x6CA6A41: WTF::threadEntryPoint(void*) (in 
/usr/lib/libjavascriptcoregtk-1.0.so.0.13.2)
==31797==by 0x6CA6BAD: WTF::wtfThreadEntryPoint(void*) (in 
/usr/lib/libjavascriptcoregtk-1.0.so.0.13.2)
==31797==by 0x710378D: clone (clone.S:130)
==31797== 
==31797== 64,008 bytes in 1 blocks are possibly lost in loss record 
10,808 of 10,810
==31797==at 0x48288D8: malloc (vg_replace_malloc.c:270)
==31797==by 0x5279F4A: sqlite3MemMalloc (sqlite3.c:15436)
==31797==by 0x525519D: mallocWithAlarm (sqlite3.c:18734)
==31797==by 0x525D336: sqlite3Malloc (sqlite3.c:18767)
==31797==by 0x5263C7D: setupLookaside.part.209 (sqlite3.c:112140)
==31797==by 0x52A68D8: openDatabase (sqlite3.c:112119)
==31797==by 0x1484DD: midori_history_new (in /usr/bin/midori)
==31797==by 0x14785A: midori_normal_app_new (in /usr/bin/midori)
==31797==by 0x12C422: main (in /usr/bin/midori)
==31797== 
==31797== 64,008 bytes in 1 blocks are possibly lost in loss record 
10,809 of 10,810
==31797==at 0x48288D8: malloc (vg_replace_malloc.c:270)
==31797==by 0x5279F4A: sqlite3MemMalloc (sqlite3.c:15436)
==31797==by 0x525519D: mallocWithAlarm (sqlite3.c:18734)
==31797==by 0x525D336: sqlite3Malloc (sqlite3.c:18767)
==31797==by 0x5263C7D: setupLookaside.part.209 (sqlite3.c:112140)
==31797==by 0x52A68D8: openDatabase (sqlite3.c:112119)
==31797==by 0x1890D5: katze_http_cookies_sqlite_open_db (in 
/usr/bin/midori)
==31797==by 0x1892D4: katze_http_cookies_sqlite_attach (in 
/usr/bin/midori)
==31797==by 0x4A1F87A: soup_session_feature_attach (in 
/usr/lib/i386-linux-gnu/libsoup-2.4.so.1.5.0)
==31797==by 0x4A1CD33: soup_session_add_feature (in 
/usr/lib/i386-linux-gnu/libsoup-2.4.so.1.5.0)
==31797==by 0x157F04: midori_load_soup_session_full (in 
/usr/bin/midori)
==31797==by 0x48FD18F: g_idle_dispatch (gmain.c:4657)
==31797==by 0x48FF6D2: g_main_context_dispatch (gmain.c:2539)
==31797==by 0x48FFA6F: g_main_context_iterate.isra.21 (gmain.c:3146)
==31797==by 0x48FFECA: g_main_loop_run (gmain.c:3340)
==31797==by 0x7044E45: (below main) (libc-start.c:228)

I am guessing Valgrind is confused by SQLite’s memory allocator and
therefore these can be ignored. Do you have some Valgrind suppression
files to be used with SQLite using programs like PyGObject has [1]?


Thanks and sorry if I wrote nonsense,

Paul


[1] http://www.midori-browser.org
[2] https://mail.gnome.org/archives/commits-list/2010-December/msg11251.html


signature.asc
Description: This is a digitally signed message part
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best practices for dealing with password protected database

[Simon Slavin]

On 31 Oct 2011, at 8:07am, Eugene N wrote:

> For your .NET application to use the, DB it must first decrypt it; That
> means, storing the plain version in ram; A memory dump will pronto show the
> contents of this sqlite database;

Physical possession of the hardware concerned is always an end to encryption 
methods.  Once they've got the computer it's just a question of how much time 
and effort they want to figuring out where you put the password.  That's one 
reason so many systems have just thin clients on your user's hardware and do 
all the secure stuff on a server back in headquarters.

Igor's approach is as good as any: use a password that doesn't look like a text 
string.  You can also introduce an additional step of scrambling the password 
you store in some way, so a cracker doesn't spot a mystery string in a 
configuration file and say "Well, I guess that's the password in plaintext.".

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

Re: [sqlite] Best practices for dealing with password protected database

[Igor Tandetnik]

Bernd  wrote:
> This may not be really SQLite specific, but as it's at least SQLite
> related I thought I asked here.
> Our program ships with an encrypted SQLite database that has to be
> opened by the application to process some other data. As it's a .NET
> application, it's very easy to peek inside the source code (even though
> I'm planning to use an obfuscater tool). Are there any best practices on
> how to store the password to open the database?

Generate a random password when first creating the database. Encrypt it using 
ProtectedData.Protect and store the encrypted version somewhere (e.g in the 
registry, or in a file alongside the database file). Decrypt with 
ProtectedData.Unprotect before using.

This effectively encrypts the password with the user's Windows login 
credentials.
-- 
Igor Tandetnik

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

Re: [sqlite] Best practices for dealing with password protected database

[Eugene N]

Hello Bernd!

I have a very limited knowledge about such matters, but it seems to me
there is a caveat in the whole area of using encrypted data on a end-user
pc;

For your .NET application to use the, DB it must first decrypt it; That
means, storing the plain version in ram; A memory dump will pronto show the
contents of this sqlite database;

There is also handy way of using a debugger to find the function that
decrypts the db (by monitoring all standard .NET disk I/O, obviously the
function will have to read the file first);

So, given that you are aware of those issues, there is a way that has been
known to work in the past (for C programs)  - implement certain
anti-debugger techniques (program detected the presence of a debugger and
behaved differently or erratically);
Storing a password in an obscure manner (not a string! cause they can be
sniffed by 'strings' utility with ease) also is a good option; Using hash
to calculate password at run-time can aslo complicate the job of a will-be
hacker;

Hope that is helpful

Eugene


2011/10/31 Bernd 

> This may not be really SQLite specific, but as it's at least SQLite
> related I thought I asked here.
> Our program ships with an encrypted SQLite database that has to be opened
> by the application to process some other data. As it's a .NET application,
> it's very easy to peek inside the source code (even though I'm planning to
> use an obfuscater tool). Are there any best practices on how to store the
> password to open the database?
> To put things clear - we don't want to make the program absolutely
> hacker-proof, but simply avoid making it too easy for everybody to see how
> the database is structured and what it contains.
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best practices for dealing with password protected database

[Bernd]

This may not be really SQLite specific, but as it's at least SQLite 
related I thought I asked here.
Our program ships with an encrypted SQLite database that has to be 
opened by the application to process some other data. As it's a .NET 
application, it's very easy to peek inside the source code (even though 
I'm planning to use an obfuscater tool). Are there any best practices on 
how to store the password to open the database?
To put things clear - we don't want to make the program absolutely 
hacker-proof, but simply avoid making it too easy for everybody to see 
how the database is structured and what it contains.

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

Re: [sqlite] Best practices fo web interface

[Alexey Pechnikov]

As example, you can build JSON services and use a lot of javascript
widgets. I use tclsqlite on my servers and jQuery on clients (desktop
brawsers, Google Android applications).

Small example:
http://mobigroup.ru/service/jsontest/

2011/2/27 Matt Young :
> I am a regular user of sqite at the office, but I want to progress to sqlite
> web access via the web.  Who has the best widgets for that? Web space not a
> problem.  Widgets that work in blogger pages would be nice, but I can start
> fresh also.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Best practices fo web interface

[Simon Slavin]

On 27 Feb 2011, at 5:51pm, Matt Young wrote:

> I am a regular user of sqite at the office, but I want to progress to sqlite
> web access via the web.  Who has the best widgets for that? Web space not a
> problem.  Widgets that work in blogger pages would be nice, but I can start
> fresh also.

If you use PHP to write your web pages you can have PHP use SQLite to maintain 
its databases.  There are three APIs that can be used to do this but if you're 
interested in SQLite specifically I recommend this one:

http://www.php.net/manual/en/book.sqlite3.php

which gets closest to the spirit of SQLite while doing things in a usefully 
object-oriented way.

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

[sqlite] Best practices fo web interface

[Matt Young]

I am a regular user of sqite at the office, but I want to progress to sqlite
web access via the web.  Who has the best widgets for that? Web space not a
problem.  Widgets that work in blogger pages would be nice, but I can start
fresh also.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practices

[Stephen Oberholtzer]

On Tue, Jun 10, 2008 at 1:29 PM, A. H. Ongun <[EMAIL PROTECTED]> wrote:

> We have  an application requirement for the following:
> 1) Maintain an in memory database of real time data.  The main table mimics
> a Modbus register address scheme and once the table is created and records
> are inserted to create the table at startup, all writes are updates for the
> state machine. (app. 1000 records).  Reads are through multiple Modbus
> TCP/IP protocol based devices which result in selects of up to 100
> contiguous records at a time.
> 2) At periodic intervals based on events and time intervals a disk (SD
> card) database gets updated (inserts).  This contains system parameters, and
> various different tables.
> 3)  Occasionally the whole database on disk is written to an external media
> (USB) for backup purposes.
> 4)  Through a web interface user(s) can export portions of a table by
> specifying type of data (table) and start and end time in csv format. (cgi
> scripts).
> All of the above is implemented currently using a "custom" format, and is
> really a pain to expand and modify.
> Items 1 & 2 are fairly straight forward to implement, they would be in the
> same executable as well.
> My original thought was to use the sqlite3 shell interface to accomplish
> 4.  I am concerned a bit about items 3 and 4 though in regards to
> performance issues in that I can not afford not to service data requests
> that could come several times a second if database could be locked during
> operation of 3 and 4.  Size of the database can grow to 1-2.4GB.
> I am open to any suggestions.
> Thanks in advance.
> andy



Okay, my first question is: What OS is this device running? If you need
realtime semantics, you really need an RTOS.  If the answer contains the
string "Windows" you're in serious trouble, right from the get-go.

You mention two databases (#1 has a modbus database, then #2 has a config
database), then #3 mentions a "whole database".  Which one are you referring
to?

I know nothing about Modbus, but you mention both reading and writing, and
that the reading is being done in bulk by remote devices.  How often does
this happen? How often are new rows inserted into this Modbus database?


My experience with USB memory sticks is: They're SLOW.  A typical desktop
hard drive does about 30MB/sec, which would require 80 seconds to fully
write out a database.  A USB memory stick will be maybe a tenth of that.
That's over ten minutes to write the whole database out to the USB drive.


Based on the limited information I have, the best advice I can give is this:

If the rate of inserts is very low (less than one per second), it may be
best to maintain two sets of databases: the 'live' ones (modbus in memory
and config on SD) and 'backup' ones (on USB stick).

When you need to do an insert/update, take all of the data needed for the
modification, stuff it into a structure, and stick that structure on a
spooling queue.  Then, all you need to do is have a thread that pulls items
off the spooling queue and applies them to the USB stick's version of the
database.

* Inserts to the 'live' database aren't blocked by the slow USB stick
* People doing reporting can report from the USB stick version.   Inserts
that come in while the reporting thread has a shared lock will just pile up
in the spool, then they can all get processed when the report is done.
* Your external backup will probably actually get updated more often.


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



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best Practices

[A. H. Ongun]

We have  an application requirement for the following:
1) Maintain an in memory database of real time data.  The main table mimics a 
Modbus register address scheme and once the table is created and records are 
inserted to create the table at startup, all writes are updates for the state 
machine. (app. 1000 records).  Reads are through multiple Modbus TCP/IP 
protocol based devices which result in selects of up to 100 contiguous records 
at a time.
2) At periodic intervals based on events and time intervals a disk (SD card) 
database gets updated (inserts).  This contains system parameters, and various 
different tables.
3)  Occasionally the whole database on disk is written to an external media 
(USB) for backup purposes.
4)  Through a web interface user(s) can export portions of a table by 
specifying type of data (table) and start and end time in csv format. (cgi 
scripts).
All of the above is implemented currently using a "custom" format, and is 
really a pain to expand and modify.
Items 1 & 2 are fairly straight forward to implement, they would be in the same 
executable as well.
My original thought was to use the sqlite3 shell interface to accomplish 4.  I 
am concerned a bit about items 3 and 4 though in regards to performance issues 
in that I can not afford not to service data requests that could come several 
times a second if database could be locked during operation of 3 and 4.  Size 
of the database can grow to 1-2.4GB.
I am open to any suggestions.
Thanks in advance.
andy


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