Re: [sqlite] T-SQL to retrieve needed records

[James K. Lowden]

On Sat, 31 Aug 2013 22:04:54 +0200
Petite Abeille  wrote:

> > I changed the data (see below) because *meaning* of recordings.ends
> > should be an "exclusive end", what's knows as a half-open interval.
> > That makes the question of "does b follow a" one of equality.  It is
> > likely not the case that exactly one second transpires between two
> > files.  Rather, there is *no* time between them, implying that the
> > end time is at the "end of that second".  Much easier to think of
> > both as being the beginning of the second.  
> 
> Meant to ask you about that? is it really worthwhile the overall
> complication? That half-open interval business that is?
> 
> After all, the idiomatic way to query point in time data would be
> using 'between', no?

Yes and yes.  :-)

I grant that using an exclusive end date runs afoul of BETWEEN.  

Nevertheless, exclusive end dates are your friend, because they give
you JOIN joy in return.  

As a practical matter, exclusive end dates make self-joins easier,
because otherwise to find a successor you have to know what the
"missing" time is between the end of one and the start of the next. 

They're also more stable.  In the OP's example, that missing time is 1
second or three minutes, or something. A minor upstream change in how
the files are created could make that something else, changing the
application's artifact but *not* changing the notion of two files being
contiguous in time. Better to record the fact that they are contiguous
(by making the end and start times the same) and relying on simple
equality to express the idea. (The file's runtime can be recorded
separately, if it matters.) 

Lots of time-series have that property.  Take month-end dates, or stock
prices, for instance. Here in the US, Monday is a holiday.  For some
purposes, the price of stocks traded on the exchanges won't change
again until Tuesday morning.  If you record them as ending on Friday,
you have to know the exchange calendar to determine whether Tuesday is
the successor or there's a gap.  If you record them as ending on
Tuesday, not only is the exchange calendar irrelevant, but you have the
correct ends to compute the return.  

I need hardly point out that exclusive end dates are efficient, too.
Using an exclusive end date, we join two dates on simple equality. No
datetime arithmetic, just a bitwise comparison. Someone is bound to
point out that SQLite will seize upon the index, too. 

The downside is confusion, especially if your database has both kinds.
Naming conventions can help, but education matters more.  

Half-open intervals are unnatural at first.  Like counting from zero,
it's one of those things that gets easier with time and yields
benefits.  

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

Re: [sqlite] to encrypt sqlite db

[j . merrill]

If Windows RT supports the "encrypted files and folders" option of the NTFS 
file system -- I could not find anything that said explicitly that it either 
was or was not supported, and I don't have a Windows RT device to test with -- 
that would definitely be the way to go. It would require only the effort to 
turn on the encryption feature for the database file (or the directory holding 
multiple database files).

It's appropriate to always ask the question "what is the motivation for 
encrypting the database?"

If the reason is to prevent someone who steals the device from easily reading 
the data file (for example after removing or copying the hard drive), consider 
that someone who steals the device could run whatever installed software that's 
designed to be able to read/write the database. You would need to prevent them 
from being able to do that, otherwise the encryption would not be very useful.

[quoted message from Paolo Bolzoni]
Date: Sat, 31 Aug 2013 14:40:19 +0200
From: Paolo Bolzoni 
Subject: Re: [sqlite] to encrypt sqlite db
Message-ID:  


There is a non-free version of sqlite that
encrypt the db. If it is that you want then
you have to contact them directly.

Otherwise just use sqlite on a EncFs mounted
disk?

On Sat, Aug 31, 2013 at 2:25 PM, dd  wrote:
> Hi All,
>
>   I have to encrypt sqlite database on winrt.
>
>   What are all the necessary steps to do to encrypt sqlite database?
>
>   Thanks in advance.
>
> Regards,
[end of quoted message from Paolo Bolzoni]

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

Re: [sqlite] T-SQL to retrieve needed records

[Petite Abeille]

On Aug 31, 2013, at 6:42 PM, James K. Lowden  wrote:

> I changed the data (see below) because *meaning* of recordings.ends
> should be an "exclusive end", what's knows as a half-open interval.
> That makes the question of "does b follow a" one of equality.  It is
> likely not the case that exactly one second transpires between two
> files.  Rather, there is *no* time between them, implying that the end
> time is at the "end of that second".  Much easier to think of both as
> being the beginning of the second.  

Meant to ask you about that… is it really worthwhile the overall complication? 
That half-open interval business that is?

After all, the idiomatic way to query point in time data would be using 
'between', no?

For example:

where point_in_time between valid_from and valid_to

As oppose to:

where point_in_time >= valid_from and point_in_time < valid_to

Thoughts?

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

Re: [sqlite] to encrypt sqlite db

[Mikael]

Wait, can you clarify what you mean by SEE here, and also which library you
mean for BW?

Thanks


2013/8/31 Etienne 

> > Those first few bytes are just as well known after they have been run
> > through zlib or libbz2 or whatever compression library you are using.
> Your
> > encryption algorithm, whatever it is, needs to be resistant to a
> > known-plaintext attack.
>
> LZ is a sequential algorithm, while BW works with large blocks of data.
>
> > SEE uses a random IV or nonce on each page.
>
> Exactly the reason why I would recommend SEE over wxSQLite for paranoiac
> usage.
>
> I did mention WXSQLite because the OP is looking for a free solution.
>
> Regards,
> Etienne
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Etienne]

> >  On Sat, 31 Aug 2013 17:17:23 +0200
> >  Etienne  wrote:
> > 
> >  > > On the other hand removing patterns definitely cannot hurt.
> >  >
> >  > Precisely.
> >  >
> >  > The very first bytes of SQLite files are, AFAIK, well known.
> > 
> >  That's what salt is for, no?
> > 
> "nonce", "IV", "salt" - call it whatever you want.  Yes.

I simply wanted to warn the OP that wxSQLite, while free, does NOT use
salts: 2 SQLite DBs built exactly the same way at different times are
byte-to-byte identical.

Regards,
Etienne

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

Re: [sqlite] T-SQL to retrieve needed records

[James K. Lowden]

On Wed, 28 Aug 2013 14:58:35 -0700 (PDT)
jdp12383  wrote:

> Each record is a recording up to 3 min. I am trying to retrieve one
> record per continuous recording. 
...
> CREATE TABLE recordings ( 
> [key]  INTEGERPRIMARY KEY ASC AUTOINCREMENT,
> filename   VARCHAR(50),
> start_datetime DATETIME,
> end_datetime   DATETIME,
> deletedBOOLEAN 
> );

This is a simplified version of finding overlapping time series, which
I discuss at http://www.schemamania.org/sql/#overlapping.dates.

To write your query I changed your table:

CREATE TABLE recordings ( 
filename   VARCHAR(50) unique,
starts DATETIME,
ends   DATETIME,
primary key (starts)
);

because

1.  Both start time and filename are unique
2.  start time is the key to the time sequence
3.  "_datetime" is wordy; don't name things after their type
4.  Deleted rows belong in another table that will probably grow to
carry when it was deleted, by whom, and why. Restricting recordings to
the active set will minimize the work to find contiguous series. 

I also recommend a constraint enforcing "starts <= ends".

I changed the data (see below) because *meaning* of recordings.ends
should be an "exclusive end", what's knows as a half-open interval.
That makes the question of "does b follow a" one of equality.  It is
likely not the case that exactly one second transpires between two
files.  Rather, there is *no* time between them, implying that the end
time is at the "end of that second".  Much easier to think of both as
being the beginning of the second.  

With that behind us, here's your self-join:

create view vrecordings 
as
select a.starts, max(b.ends) as ends
from recordings a join recordings b 
on a.ends <= b.starts -- includes same row
where not exists (
  -- no row between a and b starts a new sequence
  select 1 from recordings as s
  where starts between a.ends and b.starts
  and not exists ( -- i.e. not a starter
select 1 from recordings
where ends = s.starts
  )
)
and not exists ( -- "a" is a starter
  select 1 from recordings
  where ends = a.starts
)
and not exists ( -- "b" is an ender
  select 1 from recordings
  where starts = b.ends
)
group by a.starts
;

> If there is gap more than 3 min then it would be considered as a
> separate recording.

If that's the true requirement, you'll have to replace the equality
tests with datetime arithmetic.  For simplicity, I'd keep the query
above,  but instead of reading recordings directly I'd have it read a
view, say "vtrecordings", that replaces "ends" with the value of the
"starts" column of the succeeding row, if extant, else the row's
own "ends" value.  Do your date magic in that view, and use equality to
find the sequences. 

For your convenience, the insert statements follow.  

--jkl

INSERT INTO recordings (filename, starts, ends)
 VALUES ('f1', '2013-08-26 00:00:00', '2013-08-26 00:03:00');
INSERT INTO recordings (filename, starts, ends)
 VALUES ('f2', '2013-08-26 00:03:00', '2013-08-26 00:06:00');
INSERT INTO recordings (filename, starts, ends)
 VALUES ('f3', '2013-08-26 00:06:00', '2013-08-26 00:09:00');

INSERT INTO recordings (filename, starts, ends)
 VALUES ('f4', '2013-08-26 00:14:00', '2013-08-26 00:17:00');
INSERT INTO recordings (filename, starts, ends)
 VALUES ('f5', '2013-08-26 00:17:00', '2013-08-26 00:20:00');
INSERT INTO recordings (filename, starts, ends)
 VALUES ('f6', '2013-08-26 00:20:00', '2013-08-26 00:23:00');

INSERT INTO recordings (filename, starts, ends)
 VALUES ('f7', '2013-08-26 00:30:00', '2013-08-26 00:33:00');
INSERT INTO recordings (filename, starts, ends)
 VALUES ('f8', '2013-08-26 00:33:00', '2013-08-26 00:36:00');
INSERT INTO recordings (filename, starts, ends)
 VALUES ('f9', '2013-08-26 00:36:00', '2013-08-26 00:39:00');

INSERT INTO recordings (filename, starts, ends)
 VALUES ('f10', '2013-08-26 00:44:00', '2013-08-26 00:47:00');
INSERT INTO recordings (filename, starts, ends)
 VALUES ('f11', '2013-08-26 00:47:00', '2013-08-26 00:50:00');
INSERT INTO recordings (filename, starts, ends)
 VALUES ('f12', '2013-08-26 00:50:00', '2013-08-26 00:53:00');



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

Re: [sqlite] Question about index usage

[James K. Lowden]

On Fri, 30 Aug 2013 16:22:04 +
Doug Nebeker  wrote:

> CREATE TABLE DevProps
> (
> CompID INTEGER NOT NULL,
> PropID INTEGER NOT NULL
> );
> 
> CREATE UNIQUE INDEX Ind_DevProps_CompIDPropID on DevProps (CompID,
> PropID);
...
> SELECT CompID FROM DevProps WHERE PropID=33
> 
> it looks like it will be doing a table scan.  Why is that?

Because the first column of your index is not PropID.  Your index is
ordered by CompIDl.  

PropID 33 might belong to any CompID.  SQLite has to look at every pair
to find them all.  It might as well scan the table.  

I would recomend adding ", primary key (CompID, PropID)" to the table
for semantic reasons, and creating an index with just PropID for
performance if you want to avoid the table scan.  

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

Re: [sqlite] to encrypt sqlite db

[Richard Hipp]

On Sat, Aug 31, 2013 at 1:10 PM, James K. Lowden
wrote:

> On Sat, 31 Aug 2013 17:17:23 +0200
> Etienne  wrote:
>
> > > On the other hand removing patterns definitely cannot hurt.
> >
> > Precisely.
> >
> > The very first bytes of SQLite files are, AFAIK, well known.
>
> That's what salt is for, no?
>
>
"nonce", "IV", "salt" - call it whatever you want.  Yes.

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

Re: [sqlite] to encrypt sqlite db

[James K. Lowden]

On Sat, 31 Aug 2013 17:17:23 +0200
Etienne  wrote:

> > On the other hand removing patterns definitely cannot hurt.
> 
> Precisely.
> 
> The very first bytes of SQLite files are, AFAIK, well known. 

That's what salt is for, no?  

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

Re: [sqlite] Why latest places.sqlite from firefox can't be opened by some older versions of sqlite

[Max Vlasov]

On Sat, Aug 31, 2013 at 10:14 PM, Richard Hipp  wrote:
> On Sat, Aug 31, 2013 at 2:03 PM, Max Vlasov  wrote:
>
>> Hi,
>>
>> I noticed that opening places.sqlite of my installation of Firefox
>> can't be made for example with sqlite 3.6.10, it says that file either
>> encrypted or invalid
>
> Recent versions of Firefox use WAL mode (http://www.sqlite.org/wal.html)
> which is supported by SQLite version 3.7.0 and later.
>

Thanks, Richard, that's it. A small suggestion, if it's possible, the
doc mentioned by Slavin could contain additional section after the
main table with conditional format incompatibilities. WAL and latest
partial index support can be mentioned there.

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

Re: [sqlite] Why latest places.sqlite from firefox can't be opened by some older versions of sqlite

[Richard Hipp]

On Sat, Aug 31, 2013 at 2:03 PM, Max Vlasov  wrote:

> Hi,
>
> I noticed that opening places.sqlite of my installation of Firefox
> can't be made for example with sqlite 3.6.10, it says that file either
> encrypted or invalid (everything ok for example with 3.7.15.2 and sure
> firefox itself, it works :)).
>

Recent versions of Firefox use WAL mode (http://www.sqlite.org/wal.html)
which is supported by SQLite version 3.7.0 and later.



>
> This might be a failure of my particular installation so maybe after
> several years of upgrading firefox made my particular db semi-valid or
> something. It's not big deal, just curious, probably ff uses some
> features making sqlite 3 format incompatible with 3.6.* versions
>
> Max
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] VACUUM silently fails, database size not changed

[Mario M. Westphal]

Unfortunately this does not help :-(
 
It may have still something to do with WAL.
Before I run the VACUUM the WAL file is quite small.
After the VACUUM has completed, it is about 20 MB - about the same size as
the properly compacted database would be.
But when I run a pragma wal_checkpoint; the WAL file does not shrink and
neither the database.
When I close the database, the WAL is deleted but the database remains at
120 MB.
 
Running a vacuum with the sqlite3 command line utility afterwards shrinks
the database to 20 MB. 
 
There must be some kind of condition or lock or whatever in my app which
prevents the vacuum to work properly.
It returns success and the WAL file grows, but the database size is not
reduced. I must be missing a step
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why latest places.sqlite from firefox can't be opened by some older versions of sqlite

[Max Vlasov]

Hi,

I noticed that opening places.sqlite of my installation of Firefox
can't be made for example with sqlite 3.6.10, it says that file either
encrypted or invalid (everything ok for example with 3.7.15.2 and sure
firefox itself, it works :)).

This might be a failure of my particular installation so maybe after
several years of upgrading firefox made my particular db semi-valid or
something. It's not big deal, just curious, probably ff uses some
features making sqlite 3 format incompatible with 3.6.* versions

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

Re: [sqlite] Question about index usage

[Igor Tandetnik]

On 8/30/2013 12:22 PM, Doug Nebeker wrote:

CREATE UNIQUE INDEX Ind_DevProps_CompIDPropID on DevProps (CompID, PropID);

When I check the query plan for this statement:

SELECT CompID FROM DevProps WHERE PropID=33

it looks like it will be doing a table scan.  Why is that?


Imagine that you have a phone book, sorted by last name then first name. 
Using this book, you can easily find all Smiths, and all John Smiths, 
but it's not helpful in finding all people named John.


The order of columns in the index matters.
--
Igor Tandetnik

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

Re: [sqlite] Question about index usage

[Clemens Ladisch]

Doug Nebeker wrote:
> CREATE UNIQUE INDEX Ind_DevProps_CompIDPropID on DevProps (CompID, PropID);
>
> When I check the query plan for this statement:
>
> SELECT CompID FROM DevProps WHERE PropID=33
>
> it looks like it will be doing a table scan.

My version of SQLite uses a covering index, but is still doing a full scan.

> Why is that?

The index contains the values of the indexed columns, and for each index
entry, a pointer back to the table record.  In your case, it might look
like this:

 CompID PropID rowid
 1  11 *
 1  22 *
 1  33 *
 2  22 *
 3  33 *
 3  44 *
 4  11 *
 4  22 *

Please note that the index is sorted by CompID first, and then by PropID.

How would this index be helpful for looking up entries with PropID=33,
without doing a full scan?

To be useful for lookups, the lookup column(s) must be the leftmost
column(s) in the index.


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

Re: [sqlite] to encrypt sqlite db

[Etienne]

> Those first few bytes are just as well known after they have been run
> through zlib or libbz2 or whatever compression library you are using.  Your
> encryption algorithm, whatever it is, needs to be resistant to a
> known-plaintext attack.

LZ is a sequential algorithm, while BW works with large blocks of data.

> SEE uses a random IV or nonce on each page.

Exactly the reason why I would recommend SEE over wxSQLite for paranoiac usage.

I did mention WXSQLite because the OP is looking for a free solution.

Regards,
Etienne

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

Re: [sqlite] to encrypt sqlite db

[Richard Hipp]

On Sat, Aug 31, 2013 at 11:17 AM, Etienne wrote:

>
> The very first bytes of SQLite files are, AFAIK, well known.
>

Those first few bytes are just as well known after they have been run
through zlib or libbz2 or whatever compression library you are using.  Your
encryption algorithm, whatever it is, needs to be resistant to a
known-plaintext attack.

SEE uses a random IV or nonce on each page.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Etienne]

> That last sentence is quite weird, a good encryption system should
> give a random-like sequence even with very low-entropy input.
> 
> On the other hand removing patterns definitely cannot hurt.

Precisely.

The very first bytes of SQLite files are, AFAIK, well known. 

While "encryption-only" practice might be enough to discourage an average user, 
it would probably not resist to a determined hacker... just my $0.02.

Regards,
Etienne

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

[sqlite] Inefficient covering index used for Subversion with SQLite 3.8.0

[bert]

Hi,

 

Using the Subversion 1.7 / 1.8 wc.db schema I get quite different results
from the query planner for several of our queries, where the difference in
performance is quite huge.

 

For this typical example using 3.8.0.1 only one index component is used:

$ EXPLAIN QUERY PLAN DELETE FROM NODES WHERE wc_id = ?1 AND local_relpath =
?2;

0|0|0|SEARCH TABLE NODES USING COVERING INDEX I_NODES_PARENT (wc_id=?)

 

While with 3.7.17 two components are used:

$ EXPLAIN QUERY PLAN DELETE FROM NODES WHERE wc_id = ?1 AND local_relpath =
?2;

0|0|0|SEARCH TABLE NODES USING COVERING INDEX sqlite_autoindex_NODES_1
(wc_id=? AND local_relpath=?) (~9 rows)

 

As currently wc_id is almost always 0 in our database, 3.8.0 will perform a
full table scan while 3.7.17 (and older) just deletes the right record.

 

If I perform a similar 'SELECT * FROM' instead of 'DELETE FROM', the right
index is used (but not as COVERING of course), while a 'SELECT local_relpath
FROM' shows the same problem as the delete.

 

 

This problem appears specific to cases where multiple covering indexes are
found.

 

Bert

 

 

-- 

Complete schema:

[[

CREATE TABLE REPOSITORY (   id INTEGER PRIMARY KEY AUTOINCREMENT,   root
TEXT UNIQUE NOT NULL,   uuid  TEXT NOT NULL   );

CREATE INDEX I_UUID ON REPOSITORY (uuid);

CREATE INDEX I_ROOT ON REPOSITORY (root);

CREATE TABLE WCROOT (   id  INTEGER PRIMARY KEY AUTOINCREMENT,
local_abspath  TEXT UNIQUE   );

CREATE UNIQUE INDEX I_LOCAL_ABSPATH ON WCROOT (local_abspath);

CREATE TABLE PRISTINE (   checksum  TEXT NOT NULL PRIMARY KEY,   compression
INTEGER,   size  INTEGER NOT NULL,   refcount  INTEGER NOT NULL,
md5_checksum  TEXT NOT NULL   );

CREATE TABLE ACTUAL_NODE (   wc_id  INTEGER NOT NULL REFERENCES WCROOT (id),
local_relpath  TEXT NOT NULL,   parent_relpath  TEXT,   properties  BLOB,
conflict_old  TEXT,   conflict_new  TEXT,   conflict_working  TEXT,
prop_reject  TEXT,   changelist  TEXT,   text_mod  TEXT,
tree_conflict_data  TEXT,   conflict_data  BLOB,   older_checksum  TEXT
REFERENCES PRISTINE (checksum),   left_checksum  TEXT REFERENCES PRISTINE
(checksum),   right_checksum  TEXT REFERENCES PRISTINE (checksum),   PRIMARY
KEY (wc_id, local_relpath)   );

CREATE TABLE LOCK (   repos_id  INTEGER NOT NULL REFERENCES REPOSITORY (id),
repos_relpath  TEXT NOT NULL,   lock_token  TEXT NOT NULL,   lock_owner
TEXT,   lock_comment  TEXT,   lock_date  INTEGER,   PRIMARY KEY (repos_id,
repos_relpath)   );

CREATE TABLE WORK_QUEUE (   id  INTEGER PRIMARY KEY AUTOINCREMENT,   work
BLOB NOT NULL   );

CREATE TABLE WC_LOCK (   wc_id  INTEGER NOT NULL  REFERENCES WCROOT (id),
local_dir_relpath  TEXT NOT NULL,   locked_levels  INTEGER NOT NULL DEFAULT
-1,   PRIMARY KEY (wc_id, local_dir_relpath)  );

CREATE TABLE NODES (   wc_id  INTEGER NOT NULL REFERENCES WCROOT (id),
local_relpath  TEXT NOT NULL,   op_depth INTEGER NOT NULL,   parent_relpath
TEXT,   repos_id  INTEGER REFERENCES REPOSITORY (id),   repos_path  TEXT,
revision  INTEGER,   presence  TEXT NOT NULL,   moved_here  INTEGER,
moved_to  TEXT,   kind  TEXT NOT NULL,   properties  BLOB,   depth  TEXT,
checksum  TEXT REFERENCES PRISTINE (checksum),   symlink_target  TEXT,
changed_revision  INTEGER,   changed_date  INTEGER,   changed_author
TEXT,   translated_size  INTEGER,   last_mod_time  INTEGER,   dav_cache
BLOB,   file_external  TEXT, inherited_props BLOB,   PRIMARY KEY (wc_id,
local_relpath, op_depth)   );

CREATE VIEW NODES_CURRENT AS   SELECT * FROM nodes AS n WHERE op_depth =
(SELECT MAX(op_depth) FROM nodes AS n2   WHERE n2.wc_id
= n.wc_id AND n2.local_relpath = n.local_relpath);

CREATE VIEW NODES_BASE AS   SELECT * FROM nodes   WHERE op_depth = 0;

CREATE TRIGGER nodes_insert_trigger AFTER INSERT ON nodes WHEN NEW.checksum
IS NOT NULL BEGIN   UPDATE pristine SET refcount = refcount + 1   WHERE
checksum = NEW.checksum; END;

CREATE TRIGGER nodes_delete_trigger AFTER DELETE ON nodes WHEN OLD.checksum
IS NOT NULL BEGIN   UPDATE pristine SET refcount = refcount - 1   WHERE
checksum = OLD.checksum; END;

CREATE TRIGGER nodes_update_checksum_trigger AFTER UPDATE OF checksum ON
nodes WHEN NEW.checksum IS NOT OLD.checksum BEGIN   UPDATE pristine SET
refcount = refcount + 1   WHERE checksum = NEW.checksum;   UPDATE pristine
SET refcount = refcount - 1   WHERE checksum = OLD.checksum; END;

CREATE TABLE EXTERNALS (   wc_id  INTEGER NOT NULL REFERENCES WCROOT (id),
local_relpath  TEXT NOT NULL,   parent_relpath  TEXT NOT NULL,   repos_id
INTEGER NOT NULL REFERENCES REPOSITORY (id),   presence  TEXT NOT NULL,
kind  TEXT NOT NULL,   def_local_relpath TEXT NOT NULL,
def_repos_relpath TEXT NOT NULL,   def_operational_revision  TEXT,
def_revision  TEXT,   PRIMARY KEY (wc_id, local_relpath) );

CREATE UNIQUE INDEX I_EXTERNALS_DEFINED ON EXTERNALS (wc_id,
def_local_relpath,
local_relpath);

CREATE UNIQUE INDEX I_NODES_MOVED ON NODES (wc_id, 

Re: [sqlite] Splitting a monolithic table into two related ones.

[Flakheart]

First I must apologise for the dreadful formatting. I didn't realise my 
columns were broken when I copied and pasted the text until the message 
had been saved and I could not edit the post afterwards.

> The ItemData ID can be looked up with a correlated subquery:
>
>   INSERT INTO ItemHistory(
> Boughton, Quantity, Aisle, Price, Discount, Total, ItemDataId)
>   SELECT boughton, quantity, aisle, price, discount, total,
>  (SELECT Record
>   FROM ItemData
>   WHERE Itemname = ShoppingHistory.item
> AND Category = ShoppingHistory.category
> AND Brandname= ShoppingHistory.brand
> AND Manufacturer = ShoppingHistory.manufacturer)
>   FROM ShoppingHistory;

This last bit is amazing Clemens. I would never have figured it out. 
Very much behind the eight ball on more complex queries.

I now have nicely split table with 5,475 grocery items in one table with 
buying history and around 2,120 items with no history associated. Years 
of online grocery shopping allowed me to collect this data. None have 
UPC barcodes though, as I had to get the data from emails (Which didn't 
contain them). (That's the next job.)

Thank you so very much.



>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> [hidden email] 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> 
> If you reply to this email, your message will be added to the 
> discussion below:
> http://sqlite.1065341.n5.nabble.com/Splitting-a-monolithic-table-into-two-related-ones-tp70776p70802.html
>  
>
> To unsubscribe from Splitting a monolithic table into two related 
> ones., click here 
> .
> NAML 
> 
>  
>





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Splitting-a-monolithic-table-into-two-related-ones-tp70776p70807.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Inefficient covering index used for Subversion with SQLite 3.8.0

[Bert Huijben]

Hi,

 

Using the Subversion 1.7 / 1.8 wc.db schema I get quite different results
from the query planner for several of our queries, where the difference in
performance is quite huge.

 

For this typical example using 3.8.0.1 only one index component is used:

$ EXPLAIN QUERY PLAN DELETE FROM NODES WHERE wc_id = ?1 AND local_relpath =
?2;

0|0|0|SEARCH TABLE NODES USING COVERING INDEX I_NODES_PARENT (wc_id=?)

 

While with 3.7.17 two components are used:

$ EXPLAIN QUERY PLAN DELETE FROM NODES WHERE wc_id = ?1 AND local_relpath =
?2;

0|0|0|SEARCH TABLE NODES USING COVERING INDEX sqlite_autoindex_NODES_1
(wc_id=? AND local_relpath=?) (~9 rows)

 

As currently wc_id is almost always 0 in our database, 3.8.0 will perform a
full table scan while 3.7.17 (and older) just deletes the right record.

 

If I perform a similar 'SELECT * FROM' instead of 'DELETE FROM', the right
index is used (but not as COVERING of course), while a 'SELECT local_relpath
FROM' shows the same problem as the delete.

 

 

This problem appears specific to cases where multiple covering indexes are
found.

 

Bert

 

 

-- 

Complete schema:

[[

CREATE TABLE REPOSITORY (   id INTEGER PRIMARY KEY AUTOINCREMENT,   root
TEXT UNIQUE NOT NULL,   uuid  TEXT NOT NULL   );

CREATE INDEX I_UUID ON REPOSITORY (uuid);

CREATE INDEX I_ROOT ON REPOSITORY (root);

CREATE TABLE WCROOT (   id  INTEGER PRIMARY KEY AUTOINCREMENT,
local_abspath  TEXT UNIQUE   );

CREATE UNIQUE INDEX I_LOCAL_ABSPATH ON WCROOT (local_abspath);

CREATE TABLE PRISTINE (   checksum  TEXT NOT NULL PRIMARY KEY,   compression
INTEGER,   size  INTEGER NOT NULL,   refcount  INTEGER NOT NULL,
md5_checksum  TEXT NOT NULL   );

CREATE TABLE ACTUAL_NODE (   wc_id  INTEGER NOT NULL REFERENCES WCROOT (id),
local_relpath  TEXT NOT NULL,   parent_relpath  TEXT,   properties  BLOB,
conflict_old  TEXT,   conflict_new  TEXT,   conflict_working  TEXT,
prop_reject  TEXT,   changelist  TEXT,   text_mod  TEXT,
tree_conflict_data  TEXT,   conflict_data  BLOB,   older_checksum  TEXT
REFERENCES PRISTINE (checksum),   left_checksum  TEXT REFERENCES PRISTINE
(checksum),   right_checksum  TEXT REFERENCES PRISTINE (checksum),   PRIMARY
KEY (wc_id, local_relpath)   );

CREATE TABLE LOCK (   repos_id  INTEGER NOT NULL REFERENCES REPOSITORY (id),
repos_relpath  TEXT NOT NULL,   lock_token  TEXT NOT NULL,   lock_owner
TEXT,   lock_comment  TEXT,   lock_date  INTEGER,   PRIMARY KEY (repos_id,
repos_relpath)   );

CREATE TABLE WORK_QUEUE (   id  INTEGER PRIMARY KEY AUTOINCREMENT,   work
BLOB NOT NULL   );

CREATE TABLE WC_LOCK (   wc_id  INTEGER NOT NULL  REFERENCES WCROOT (id),
local_dir_relpath  TEXT NOT NULL,   locked_levels  INTEGER NOT NULL DEFAULT
-1,   PRIMARY KEY (wc_id, local_dir_relpath)  );

CREATE TABLE NODES (   wc_id  INTEGER NOT NULL REFERENCES WCROOT (id),
local_relpath  TEXT NOT NULL,   op_depth INTEGER NOT NULL,   parent_relpath
TEXT,   repos_id  INTEGER REFERENCES REPOSITORY (id),   repos_path  TEXT,
revision  INTEGER,   presence  TEXT NOT NULL,   moved_here  INTEGER,
moved_to  TEXT,   kind  TEXT NOT NULL,   properties  BLOB,   depth  TEXT,
checksum  TEXT REFERENCES PRISTINE (checksum),   symlink_target  TEXT,
changed_revision  INTEGER,   changed_date  INTEGER,   changed_author
TEXT,   translated_size  INTEGER,   last_mod_time  INTEGER,   dav_cache
BLOB,   file_external  TEXT, inherited_props BLOB,   PRIMARY KEY (wc_id,
local_relpath, op_depth)   );

CREATE VIEW NODES_CURRENT AS   SELECT * FROM nodes AS n WHERE op_depth =
(SELECT MAX(op_depth) FROM nodes AS n2   WHERE n2.wc_id
= n.wc_id AND n2.local_relpath = n.local_relpath);

CREATE VIEW NODES_BASE AS   SELECT * FROM nodes   WHERE op_depth = 0;

CREATE TRIGGER nodes_insert_trigger AFTER INSERT ON nodes WHEN NEW.checksum
IS NOT NULL BEGIN   UPDATE pristine SET refcount = refcount + 1   WHERE
checksum = NEW.checksum; END;

CREATE TRIGGER nodes_delete_trigger AFTER DELETE ON nodes WHEN OLD.checksum
IS NOT NULL BEGIN   UPDATE pristine SET refcount = refcount - 1   WHERE
checksum = OLD.checksum; END;

CREATE TRIGGER nodes_update_checksum_trigger AFTER UPDATE OF checksum ON
nodes WHEN NEW.checksum IS NOT OLD.checksum BEGIN   UPDATE pristine SET
refcount = refcount + 1   WHERE checksum = NEW.checksum;   UPDATE pristine
SET refcount = refcount - 1   WHERE checksum = OLD.checksum; END;

CREATE TABLE EXTERNALS (   wc_id  INTEGER NOT NULL REFERENCES WCROOT (id),
local_relpath  TEXT NOT NULL,   parent_relpath  TEXT NOT NULL,   repos_id
INTEGER NOT NULL REFERENCES REPOSITORY (id),   presence  TEXT NOT NULL,
kind  TEXT NOT NULL,   def_local_relpath TEXT NOT NULL,
def_repos_relpath TEXT NOT NULL,   def_operational_revision  TEXT,
def_revision  TEXT,   PRIMARY KEY (wc_id, local_relpath) );

CREATE UNIQUE INDEX I_EXTERNALS_DEFINED ON EXTERNALS (wc_id,
def_local_relpath,
local_relpath);

CREATE UNIQUE INDEX I_NODES_MOVED ON NODES (wc_id, 

Re: [sqlite] to encrypt sqlite db

[Paolo Bolzoni]

That last sentence is quite weird, a good encryption system should
give a random-like sequence even with very low-entropy input.

On the other hand removing patterns definitely cannot hurt.

On Sat, Aug 31, 2013 at 4:38 PM, Etienne  wrote:
>> Thank you for your quick response.
>>
>> I am looking for freeware. If freeware not available, I have to implement
>> encryption support for sqlite on winrt.
>>
>> What is the procedure to implement encryption support on winrt?
>>
>> Thanks,
>> dd
>
>  might help you.
>
> It does supply free AES128/256 encryption (look in /sqlite3/secure/src/).
>
> However, be aware that there is no compression involved here, making (any) 
> encryption significantly weaker.
>
> Regards,
> Etienne
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Etienne]

> Thank you for your quick response.
> 
> I am looking for freeware. If freeware not available, I have to implement
> encryption support for sqlite on winrt.
> 
> What is the procedure to implement encryption support on winrt?
> 
> Thanks,
> dd

 might help you.

It does supply free AES128/256 encryption (look in /sqlite3/secure/src/).

However, be aware that there is no compression involved here, making (any) 
encryption significantly weaker.

Regards,
Etienne

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

Re: [sqlite] to encrypt sqlite db

[Eric Sink]

SQLCipher is free.

But it's not compatible with WinRT.

I'm pretty sure that right now there is nothing that meets both of your 
requirements.

--
E


On Aug 31, 2013, at 8:52 AM, dd  wrote:

> Thank you for your quick response.
> 
> I am looking for freeware. If freeware not available, I have to implement
> encryption support for sqlite on winrt.
> 
> What is the procedure to implement encryption support on winrt?
> 
> Thanks,
> dd
> 
> 
> On Sat, Aug 31, 2013 at 6:34 PM, Stephan Beal  wrote:
> 
>> On Sat, Aug 31, 2013 at 2:59 PM, Mohit Sindhwani  wrote:
>> 
>>> Adding on to Paolo's answer, see this: http://www.hwaci.com/sw/**
>>> sqlite/prosupport.html 
>>> See SEE and CEROD on that page.
>> 
>> @devs: minor typo on that page:
>> 
>> "The SQLite software free and it works great."
>> 
>> missing "is"
>> 
>> --
>> - stephan beal
>> http://wanderinghorse.net/home/stephan/
>> http://gplus.to/sgbeal
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[dd]

Thank you for your quick response.

I am looking for freeware. If freeware not available, I have to implement
encryption support for sqlite on winrt.

What is the procedure to implement encryption support on winrt?

Thanks,
dd


On Sat, Aug 31, 2013 at 6:34 PM, Stephan Beal  wrote:

> On Sat, Aug 31, 2013 at 2:59 PM, Mohit Sindhwani  wrote:
>
> > Adding on to Paolo's answer, see this: http://www.hwaci.com/sw/**
> > sqlite/prosupport.html 
> > See SEE and CEROD on that page.
> >
>
> @devs: minor typo on that page:
>
> "The SQLite software free and it works great."
>
> missing "is"
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM silently fails, database size not changed

[Dan Kennedy]

On 08/31/2013 08:29 PM, Mario M. Westphal wrote:

I run the VACUUM command at the end of a diagnosis and cleanup operation on
my database.
I use the SQLite 3 API on Windows 7. Latest version of SQLite.
My database uses the WAL mode.
  
The database size is 120 MB when I run the sqlite3_execute("VACUUM",...)

command.
After about 20 seconds of heavy disk activity, sqlite3_execute returns with
SQLITE_OK.
The databate size on disk remains unchanged although I can see the sizeof
the WAL file rise to about 20 MB.
  
I had expected that the database shrinks because a lot of data has been

removed.
I closed my application and used the command line sqlite3.exe to VACUUM it.
The database shrinks from 120 MB to 20 MB!
  
Question:
  
I checked for open transactions: None.

I checked for pending statements (with sqlite3_next_stmt()): None.
  
Are there other reasons why VACUUM runs (it takes maybe 20 seconds for it to

return, and there is high disk activity during the execute) successfully but
apparently does nothing?


Perhaps you have auto-checkpointing turned off, or set to a very large
value:

  http://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint

If so, the changes related to VACUUM are still in the WAL file. To
flush them through to (and truncate) the db file, run an
explicit checkpoint using "PRAGMA wal_checkpoint" after the VACUUM.

Dan.



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

[sqlite] VACUUM silently fails, database size not changed

[Mario M. Westphal]

I run the VACUUM command at the end of a diagnosis and cleanup operation on
my database.
I use the SQLite 3 API on Windows 7. Latest version of SQLite.
My database uses the WAL mode.
 
The database size is 120 MB when I run the sqlite3_execute("VACUUM",...)
command.
After about 20 seconds of heavy disk activity, sqlite3_execute returns with
SQLITE_OK.
The databate size on disk remains unchanged although I can see the sizeof
the WAL file rise to about 20 MB.
 
I had expected that the database shrinks because a lot of data has been
removed.
I closed my application and used the command line sqlite3.exe to VACUUM it.
The database shrinks from 120 MB to 20 MB!
 
Question:
 
I checked for open transactions: None.
I checked for pending statements (with sqlite3_next_stmt()): None.
 
Are there other reasons why VACUUM runs (it takes maybe 20 seconds for it to
return, and there is high disk activity during the execute) successfully but
apparently does nothing?
 
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Stephan Beal]

On Sat, Aug 31, 2013 at 2:59 PM, Mohit Sindhwani  wrote:

> Adding on to Paolo's answer, see this: http://www.hwaci.com/sw/**
> sqlite/prosupport.html 
> See SEE and CEROD on that page.
>

@devs: minor typo on that page:

"The SQLite software free and it works great."

missing "is"

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Mohit Sindhwani]

On 31/8/2013 8:40 PM, Paolo Bolzoni wrote:

There is a non-free version of sqlite that
encrypt the db. If it is that you want then
you have to contact them directly.

Otherwise just use sqlite on a EncFs mounted
disk?



Adding on to Paolo's answer, see this: 
http://www.hwaci.com/sw/sqlite/prosupport.html

See SEE and CEROD on that page.

Best Regards,
Mohit.



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

Re: [sqlite] to encrypt sqlite db

[Paolo Bolzoni]

There is a non-free version of sqlite that
encrypt the db. If it is that you want then
you have to contact them directly.

Otherwise just use sqlite on a EncFs mounted
disk?

On Sat, Aug 31, 2013 at 2:25 PM, dd  wrote:
> Hi All,
>
>   I have to encrypt sqlite database on winrt.
>
>   What are all the necessary steps to do to encrypt sqlite database?
>
>   Thanks in advance.
>
> Regards,
> dd
> ___
> 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] to encrypt sqlite db

[dd]

Hi All,

  I have to encrypt sqlite database on winrt.

  What are all the necessary steps to do to encrypt sqlite database?

  Thanks in advance.

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