[sqlite] Interrupt SQL query in SHELL

[R.Smith]

On 2015-04-01 09:09 PM, Gert Van Assche wrote:
> Dr. Hipp, thanks for the tip. I put
> .bail on
> in the script.
>
>
> Ryan, I think I don't know how to trigger the bail out from within a SELECT
> statement.
> I tried
>   SELECT CASE (select count(*) from T1) WHEN (select count(*) from T2)
> then 'OK' else RAISE(FAIL) END;
>
> But this is definitely not the right way to do it. If I understand the doc
> correctly, it should be an expression, but I don't see how I can do this...

On closer inspection, it seems the raise function is really only allowed 
in triggers. So you can use a trigger.

AS a proof of concept I've made a table like this:

CREATE TABLE "vChk" ("CheckTS" NUMERIC DEFAULT (CURRENT_TIMESTAMP));

CREATE TRIGGER Trig_vChk_T1_T2 AFTER INSERT  ON vChk FOR EACH ROW
BEGIN
   SELECT (CASE (select count(*) from T1) WHEN (select count(*) from T2) 
THEN 'OK' ELSE RAISE(FAIL,'The number of Inserts are mismatched...') END);
END;

So then at the end of any insert bits of a script, you would simply add 
a line like this:

INSERT INTO vChk DEFAULT VALUES;

and that would cause the script to stop execution and fail if the 
statement requires it.

You can add any amount of triggers to that one vChk table to check all 
sorts of things.

I've tested this time, it works.

[sqlite] Interrupt SQL query in SHELL

[Simon Slavin]

On 1 Apr 2015, at 8:09pm, Gert Van Assche  wrote:

> But this is definitely not the right way to do it. If I understand the doc
> correctly, it should be an expression, but I don't see how I can do this...

You can trigger a bail by causing any SQL error.  For example, inserting a 
duplicate value in a column which is declared UNIQUE, maybe a primary key.  Or 
inserting a NULL value in a column defined as NOT NULL.  Or failing any other 
constraint check.  So you may be able to use CASE or a calculation to arrange 
that under the condition you're watching, one of these happens.

You may be able to check the value returned by sqlite3 as it exits to see 
whether it bailed or exited normally.

Simon.

[sqlite] Did A Recent Windows & Update Affect SQLite???

[R.Smith]

On 2015-04-01 09:17 PM, Mark Romero wrote:
> Hello everyone and thanks for your replies (I am new to using a mailing
> list so I hope that everyone gets this response).

Everyone did. As to the question - as Simon mentioned, we are not aware 
- but I am not a Lightroom or Win7 user, so I phoned a friend who is a 
photographer and I happen to know uses Win7 64 Pro always up to date and 
for whom Lightroom is a daily staple, hoping that any regression might 
be visible or known to him (and possibly how to solve it).

He was quite surprised at my suggestion and confirmed doing more than a 
bit of lightroom editing and backups (whatever that means in a Lightroom 
context) even this week without any issues.  This is of course nothing 
more than incidental evidence but does suggest that the problem, if the 
cause is in the update, might be due to some other subset of hardware or 
software combinations you use.

If I pretend to wear a sleuth hat for a moment, I would say the fact 
that it affects both you and your son's machines seems non-coincidental 
and would suggest that you list all things you have in common, 
especially other software, and start eliminating those. I'd start with 
Lightroom add-ons (if those exist).

Best of luck!
Ryan

[sqlite] Interrupt SQL query in SHELL

[Gert Van Assche]

Dr. Hipp, thanks for the tip. I put
   .bail on
in the script.


Ryan, I think I don't know how to trigger the bail out from within a SELECT
statement.
I tried
 SELECT CASE (select count(*) from T1) WHEN (select count(*) from T2)
then 'OK' else RAISE(FAIL) END;

But this is definitely not the right way to do it. If I understand the doc
correctly, it should be an expression, but I don't see how I can do this...

thanks


gert

2015-04-01 20:44 GMT+02:00 Richard Hipp :

> On 4/1/15, Gert Van Assche  wrote:
> >
> > My question is: when the import fails (detected in the script.sql file),
> I
> > would like to stop executing the batch file (runscript.cmd).
> >
> > Is there a way to do so?
> >
>
> Have you tried the -bail command-line option?
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Interrupt SQL query in SHELL

[R.Smith]

On 2015-04-01 08:29 PM, Gert Van Assche wrote:
> Hi all,
>
> When running SQLite3 from command line, is there a way to interrupt the CMD
> file when a condition is true?
>
> I'm importing a lot of plain text files that should all count the same
> number of lines, so I want to compare the record count of all imported data
> with the first file that was imported.
>
> I can detect this easily with a query like this:
>
> .import 'x.txt' T1
> .import 'y.txt' T2
> .mode list
> .output importerror.txt
> SELECT CASE (select count(*) from T1) WHEN (select count(*) from T2)
> then 'OK' else 'Not all files contain the same number of lines' END;
>
> This is in an "script.sql" file and I run it by executing a batch file
> (runscript.cmd).
>
> type script.sql | sqlite3.exe test.db
>
>
> My question is: when the import fails (detected in the script.sql file), I
> would like to stop executing the batch file (runscript.cmd).
>
> Is there a way to do so?

Hi Gert, I have not tested this but there is an SQLite expression called 
RAISE(conflict-clause, message) which will stop transaction execution - 
not too sure if it stops a file import in its tracks, but the test will 
be easy.

https://www.sqlite.org/syntax/raise-function.html

It is usually used inside triggers for this purpose, but I assume it 
will work wherever.

[sqlite] Interrupt SQL query in SHELL

[Gert Van Assche]

Hi all,

When running SQLite3 from command line, is there a way to interrupt the CMD
file when a condition is true?

I'm importing a lot of plain text files that should all count the same
number of lines, so I want to compare the record count of all imported data
with the first file that was imported.

I can detect this easily with a query like this:

   .import 'x.txt' T1
   .import 'y.txt' T2
   .mode list
   .output importerror.txt
   SELECT CASE (select count(*) from T1) WHEN (select count(*) from T2)
then 'OK' else 'Not all files contain the same number of lines' END;

This is in an "script.sql" file and I run it by executing a batch file
(runscript.cmd).

   type script.sql | sqlite3.exe test.db


My question is: when the import fails (detected in the script.sql file), I
would like to stop executing the batch file (runscript.cmd).

Is there a way to do so?

thanks for your thoughts,


Gert

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Simon Slavin]

We can only answer your original question with a 'No'.  We're not aware of any 
dramatic SQLite slow-down with any recent Windows update, let alone W7 
specifically.  As Alex wrote, it's probably better if you ask questions on a 
forum about Lightroom.

Simon.

[sqlite] insert in sqlite (returning value for inserted row)

[R.Smith]

On 2015-04-01 07:10 PM, Kumar Suraj wrote:
> Hi Richard.. this wont work for me due to following reason.
>
> If a separate thread performs a new INSERT
>  on the same database connection
> while the sqlite3_last_insert_rowid()
>  function is running
> and thus changes the last insert rowid
> , then the value
> returned by sqlite3_last_insert_rowid()
>  is unpredictable and
> might not equal either the old or the new last insert rowid
> .

Yes... this is why transactions exist and why multiple connections used 
by multiple threads is safe - there is no such thing as unpredictable 
inside an ACID engine transaction.

The last row id will  remain valid for the duration of the transaction.

Outside of a transaction any writer can update any row/table at any time 
and produce very many different row id's due to inserts. How did you 
intend to control the last insert concurrency in your application? If 
you explain the rules you have set for your application, we can tell you 
how to get sqlite to play ball.

Also - if you do actually use AUTOINCREMENT as opposed to simply an 
integer primary key, you can look up the next AI number in the sequence 
from the sqlite_sequence table you will find auto-created in your DB 
(which is where sqlite gets it too) and yet another way is to query the 
MAX(rowid) of the table (this will be slower) - All of this however must 
also happen inside of the same transaction (as the insert) to be meaningful.

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Jim Callahan]

You may or may not find this Adobe Lightroom thread helpful
(it winds up discussing critical sections and thread proliferation
-- which are not a SQLite issues):
https://forums.adobe.com/thread/1229203?tstart=0

Jim Callahan

On Wed, Apr 1, 2015 at 3:21 PM, Simon Slavin  wrote:

> We can only answer your original question with a 'No'.  We're not aware of
> any dramatic SQLite slow-down with any recent Windows update, let alone W7
> specifically.  As Alex wrote, it's probably better if you ask questions on
> a forum about Lightroom.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Interrupt SQL query in SHELL

[Richard Hipp]

On 4/1/15, Gert Van Assche  wrote:
>
> My question is: when the import fails (detected in the script.sql file), I
> would like to stop executing the batch file (runscript.cmd).
>
> Is there a way to do so?
>

Have you tried the -bail command-line option?
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Mark Romero]

Thank you all for all your help.

I have been soliciting advice from all sorts of Lightroom forums / adobe
forums / Dell computer forums and other forums more specific to hardware
and Lightroom, and they all ended up in a dead end.

But seeing as though it DOESN'T look like a recent Win 7 update has
affected SQLite performance, I guess I have reached a dead end, too. (You
were my last remaining hope, guys!!!)

Anyway, thanks all for all your help and suggestions. Looks like I might
just have to stick with Adobe Camera Raw and Photoshop for now until I can
get a new computer.

On Wed, Apr 1, 2015 at 12:51 PM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> You may or may not find this Adobe Lightroom thread helpful
> (it winds up discussing critical sections and thread proliferation
> -- which are not a SQLite issues):
> https://forums.adobe.com/thread/1229203?tstart=0
>
> Jim Callahan
>
> On Wed, Apr 1, 2015 at 3:21 PM, Simon Slavin  wrote:
>
> > We can only answer your original question with a 'No'.  We're not aware
> of
> > any dramatic SQLite slow-down with any recent Windows update, let alone
> W7
> > specifically.  As Alex wrote, it's probably better if you ask questions
> on
> > a forum about Lightroom.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Behaviour changed from 3.7 to 3.8

[Marcel Keller]

Hi,

Consider the following database:

sqlite> .schema
CREATE TABLE counter (i integer);
CREATE TABLE expenses (date float, month text, amount numeric);
sqlite> select * from counter;
0
1
2
sqlite> select * from expenses;
2457113.5163101||100

In the latest binary from the website, I get the following:

sqlite> .version
SQLite 3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d
sqlite> select strftime("%Y-%m",date,'start of month',i || ' months') as 
month, sum(amount) from expenses join counter where i < 12 group by month;
2015-06|300

However, with an earlier version, I get:

sqlite> .version
SQLite 3.7.15.2 2013-01-09 11:53:05 c0e09560d26f0a6456be9dd3447f5311eb4f238f
sqlite> select strftime("%Y-%m",date,'start of month',i || ' months') as 
month, sum(amount) from expenses join counter where i < 12 group by month;
2015-04|100
2015-05|100
2015-06|100

It seems that the meaning of "group by month" changed from referring to 
"as mount" to expenses.month. Is this intentional?

Best regards,
Marcel

[sqlite] insert in sqlite (returning value for inserted row)

[Doug Currie]

Suraj,

Don't use the same database connection in multiple threads. Each thread
should use its own connection. Then last insert rowid is predictable.

e


On Wed, Apr 1, 2015 at 1:10 PM, Kumar Suraj  wrote:

> Hi Richard.. this wont work for me due to following reason.
>
> If a separate thread performs a new INSERT
>  on the same database connection
> while the sqlite3_last_insert_rowid()
>  function is running
> and thus changes the last insert rowid
> , then the value
> returned by sqlite3_last_insert_rowid()
>  is unpredictable and
> might not equal either the old or the new last insert rowid
> .
>
> On Tue, Mar 31, 2015 at 6:23 PM, Richard Hipp  wrote:
>
> > https://www.sqlite.org/c3ref/last_insert_rowid.html
> >
> > On Tue, Mar 31, 2015 at 9:19 PM, Kumar Suraj 
> wrote:
> >
> > > Hi
> > >
> > > I am using sqlite C interface for inserting data in the table. The
> > primary
> > > key is a 64 bit integer which i need to auto-increment and get
> populated
> > > automatically as we do not provide that value in insert statement. Is
> > there
> > > a way i can get the autoincremented value for each row inserted when
> > ever i
> > > execute my insert.
> > >
> > > -Suraj
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > drh at sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] WAL with linux and win

[Simon Slavin]

On 1 Apr 2015, at 12:16am, MM  wrote:

> Is all I need to do: PRAGMA journal_mode=WAL; once from sqlite3 command
> line?

Yes.  Just do it once, from any connection or any program.  The mode is stored 
inside the database file and will then be used by any program which opens a 
connection.

> Any further arguments when opening the db from sqlite3.connect in python,
> or from C++/odb ? How about win/linux dbfile travel through git? I
> understand 2 more files appear in WAL, the wal-index and the shmem file. I
> can't just share across the 2 boxes server and pc?

You need to share the folder that the dbfile is in, rather than trying to share 
specific files.  SQLite can make and destroy ancillary files (journal, 
temp-index, etc.) any time it wants, and if you're using WAL mode, they should 
all be deleted by the time you close your last connection to the database.

There's no way to ensure concurrency through git.  Just ensure that you take 
your git 'copy' when the database isn't open.  That should give it a 'clean' 
database file and no ancillary files.

Simon.

[sqlite] insert in sqlite (returning value for inserted row)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/01/2015 10:10 AM, Kumar Suraj wrote:
> Hi Richard.. this wont work for me due to following reason.

Yes it will.  This is how I do it:

  BEGIN TRANSACTION;
INSERT INTO table .;
SELECT last_insert_rowid();
  COMMIT;

That will always give the right answer no matter how concurrent your
usage is.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUcTPwACgkQmOOfHg372QRmcwCgzSMaabaWvdQGIIa2cSH2UywX
kHIAoN/ovfhvl54Qc/SbJ5NCDc/xb9CT
=4iBF
-END PGP SIGNATURE-

[sqlite] Performance issue

[GB]

In case of SELECTing "all available" I recommend invoking a different 
statement without the timestamp-part instead of providing some min and 
max values for timestamp. This avoids tricking the query planner into 
some wrong decisions (if you have an index with protocol as the first 
column).

And how about WAL mode? If concurrency is of any concern for you, this 
definitely is something worth a try.

-- GB

Jeff Roux schrieb am 31.03.2015 um 12:48:
> Thanks everyone for the answers.
>
> I won't be able to make some tests today, I will come back to you soon with
> additional information. Just to say that, in the worst case, the WHERE
> clause selects the entire data, i.e 100 entries. The user can select a
> time range of 1 hour, 3 hours, 1 day, and ? all available ?. Note: before
> being added in the database, the time stamps are aggregated on a 180 second
> period and a lot of rows has the same time stamp (~ one row per TCP
> session).
>
> All the columns are defined as INTEGER. There are 41 columns in total in
> the flow table. If I remember well, there is no primary key defined for
> this table.
>
> 2015-03-31 8:32 GMT+02:00 GB :
>
>>  From what I see, I assume that timestamp gives the highest selectivity.
>> Taking into account that protocol is SELECTed for and portLan is GROUPed
>> BY, I'd try an index (timestamp, protocol, portLan) (not sure if portLan
>> helps here, but it's worth a try, I think). Don't forget to ANALYZE, of
>> course. Are your colums of INTEGER affinity? If the are of TEXT, they will
>> store anything as TEXT. May make a difference in both space consumption and
>> speed. Is your SQLite lib built with SQLITE_ENABLE_STAT4 enabled? If not,
>> give it a try. It sometimes makes a big difference.
>>
>> Is it possible that data collection and retrieval happen at the same time?
>> If so, try running the database in WAL mode, it should help with
>> concurrency issues.
>>
>> -- GB
>>
>>
>> Jeff Roux schrieb am 30.03.2015 um 11:46:
>>
>>> Hi everyone,
>>>
>>> I have a daemon that collects information and stores it in a SQLite
>>> database. The table has 1 million rows.
>>>
>>> This daemon is running on a HP server with 12 cores, 32 GB of RAM,
>>> and a SSD drive. I have performance issues with some requests. For
>>> instance, the following request takes more than 5 seconds to
>>> accomplish with SQlite3 (in that particular case, the WHERE clause
>>> selects all the data in the database, i.e. 100 rows):
>>>
>>> SELECT SUM(nbBytesDecompOut + nbBytesCompIn) as vol, portLan as item
>>> FROM  flows
>>> WHERE timestamp>=1383770600 AND timestamp<=1384770600 AND protocol IN
>>> (17, 6)
>>> GROUP BY portLan ORDER BY vol DESC LIMIT 6;
>>>
>>> I have done some tests with or without "INDEXED BY" clauses and got
>>> nearly the same results.
>>>
>>> I compared the performance with a mySQL and the same request takes
>>> less than 1 second to accomplish.
>>>
>>> Could you give me some directions to optimize this kind of request
>>> with SQlite3 when there is a big amount of data in the table ? I
>>> need to increase 3 times the number of rows and the performance will
>>> become unacceptable for my application.
>>>
>>> Thanks in advance.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Mark Romero]

Hello everyone and thanks for your replies (I am new to using a mailing
list so I hope that everyone gets this response).

- Viruses and Antivirus software:

I use Avast in real time and I have scanned my system several times and it
came up clean.

I installed malwarebytes shortly after Lightroom began having problems, and
the scans came up clean, too. (I have since uninstalled malware bytes.)

Have not tried any of the native Microsoft virus tools.

- Software Updates:

I am using the latest version of Lightroom (5.7.1)

I am using Win 7 Pro 64-bit Service Pack 1 and I am pretty sure it is up to
date since I do automatic updates with it.

- CPU Usage:

In resource monitor and task manager, it is ONLY lightroom that is using up
all the CPU and is "spiking" the cpu (it repeatedly and rhythmically goes
from 50% use to 100% use over and over again. It spikes approximately every
5 seconds or so.

No other program or process is using an abnormally large percentage of CPU
(or memory)

Please also note that when Lightroom IS spiking the CPU, I am still able to
open other programs (like Photoshop, which is something of a resource hog),
and work in those programs, WHILE Lightroom is spiking the CPU. To rephrase
this, when lightroom stops responding I can go ahead and open photoshop and
work on a different photo, with only minimal lag compared to when Lightroom
is not spiking the CPU.

- Hard Drive (and other hardware):

I have used the Dell diagnostic tests and the Western Digital tests and
they have all been fine for the hard drive as well as ALL other hardware.

- Disk Performance / File corruption

Yes, I have seen NTFS errors in the event manager. But they only seem to
happen when Lightroom has stopped responding and I chose to do a
ctrl-alt-del instead of doing a ctrl-q and waiting for the program to
eventually close. (I know that ctrl-alt-delete is bad... will try to avoid
again!)

I have done another chkdsk and it came out fine, and I did a chkntfs and it
came out saying C: is not dirty.

- Tech Support from Adobe:

I have had THREE separate Adobe technicians login remotely to my computer
and make changes to different settings, and unfortunately it has not made a
difference.

Because the program stops responding sporadically, they will make a few
changes and then ask me to try it. If the problem doesn't happen within
five minutes, they end the chat and say, "It's probably ok, let  us know if
you have problems in the future."

Then five minutes after they end their support chat and log out of my
system, it will stop responding again.

The changes they have made include increasing the cache, giving adobe
programs "resource priority" . they also tried to update my drivers but I
don't know if they were able to update or not.

- Reinstallation:

I have deleted and reinstalled Lightroom 5.7.1 once already after the
problem started a few months back, and unfortunately it did not make a
difference.

- Lightroom versus Adobe Camera Raw (ACR)

Someone pointed out that Lightroom is different than ACR, and that is true.
However, I spend most of my time in the Development module of Lightroom,
which is (as far as I understand) extremely similar to ACR.

Just so you know, I can use ACR ALL DAY LONG without any problems.

My understanding is that Lightroom saves info to its SQlite database, while
ACR saves info to a separate file.

One Other Fun Fact:

A lot of people are dismissive of my problem because I have an older
computer (core 2 duo E8400 with 8 gigs DDR3 RAM).

"Just buy an i7" they like to chant.

However, Lightroom worked fine for over 8 months until just about two
months ago, when the problems started, and...

My sons computer has the same problem, which developed at the same time.
Again, worked fine for 8 months, then BAM! It started having the problem
too.

However, it happens A LOT LESS on my son's computer, and his computer is
even LOWER SPEC'D than my computer (He has a C2D E4500 with only 4 Gigs
DDR2 Ram)

The main thing we have in common is that we both have Win 7 64 bit (his
computer has ultimate, I have Pro).

Thanks in advance for any other thoughts or suggestions.


On Wed, Apr 1, 2015 at 6:05 AM, Donald Griggs  wrote:

> Regarding: "... would love to see the SQLite schema Lightroom uses"
>
> Hi, Rob
>
> I am not a user of Lightroom, but there's a start at this link:
>
>
> http://www.earthboundlight.com/phototips/querying-sqlite-lightroom-database.html
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Performance issue

[R.Smith]

On 2015-04-01 10:20 AM, Jeff Roux wrote:
> Here is the complete schema:
>
> sqlite> .schema
> CREATE TABLE application (id INTEGER CONSTRAINT applpk PRIMARY KEY,
> shortname VARCHAR(64), name VARCHAR(256));
>
> CREATE TABLE flows (idx INTEGER PRIMARY KEY, ipLan INTEGER, ipWan INTEGER,
> flags INTEGER, portLan INTEGER, portWan INTEGER, tos INTEGER, protocol
> INTEGER, wanTcpFlags INTEGER, nbBytesDecompOut INTEGER, nbBytesCompIn
> INTEGER, duration INTEGER, applId INTEGER, CST INTEGER, SRT INTEGER, RTT
> INTEGER, lan_retransmission INTEGER, wan_retransmission INTEGER,
> nbPktDecompIn INTEGER, nbPktCompOut INTEGER, nbBytesDecompIn INTEGER,
> nbBytesCompOut INTEGER, timestamp INTEGER, rtpTypeLAN INTEGER,
> rtpPktsLossLAN INTEGER, rtpJitterLAN INTEGER, rtpFactorRLAN INTEGER,
> rtpTypeWAN INTEGER, rtpPktsLossWAN INTEGER, rtpJitterWAN INTEGER,
> rtpFactorWLAN INTEGER, nbBytesDbcDecompOut INTEGER, nbBytesDbcCompIn
> INTEGER, nbBytesDefDecompOut INTEGER, nbBytesDefCompIn INTEGER,
> nbPktDecompOut INTEGER, nbPktCompIn INTEGER, nbBytesDbcDecompIn INTEGER,
> nbBytesDbcCompOut INTEGER, nbBytesDefDecompIn INTEGER, nbBytesDefCompOut
> INTEGER);
>
> CREATE INDEX idxApp ON flows(applId);
> CREATE INDEX idxLanWan ON flows(ipLan, ipWan, portWan, portLan);
> CREATE INDEX idxProto ON flows(protocol);
> CREATE INDEX idxTos ON flows(tos);
> CREATE INDEX idxTsLanWan ON flows(timestamp, ipLan, ipWan, portWan,
> portLan);
> CREATE INDEX tsindex ON flows(timestamp);

You may remove all those Indices (unless you need them for something 
else) and jfor the specific query ust use:

CREATE INDEX tsIdxProt ON flows(timestamp,protocol);

Which, as done in the test scripts for 3 million rows I've sent earlier, 
will produce very fast results for your query (Sub 50 milliseconds for 
12K rows).
The actual time will greatly depend on the amount of hits qualified by 
the WHERE clause, but the times will be comparable to MySQL or any other 
SQL service (uncached).

[sqlite] Two different Order By in one statement

[R.Smith]

On 2015-04-01 10:29 AM, Bart Smissaert wrote:
> OK, let me give the simplest example possible.
>
> Table with 3 integer fields, A, B and C
>
> AB C
> 
> 1 1 2
> 2 1 2
> 1 2 1
> 2 2 1
>
> This needs to be sorted on column A asc
> Then when the value in A is 1 the second sort needs to be asc on column B,
> but when the value in A is 2 then the second sort  needs to be asc on
> column C.
> So, the result should be this:
>
> AB C
> 
> 1 1 2
> 1 2 1
> 2 2 1
> 2 1 2
>
> I could think of various ways to achieve this, but not with one statement.

SELECT A, B, C, (CASE A WHEN 1 THEN B ELSE C) AS Srt
FROM t WHERE 1
ORDER BY A, Srt DESC;

This might also work according to Darren (thanks for pointing it out) 
and produce one column less, but I did not test it:

SELECT A, B, C
FROM t WHERE 1
ORDER BY A, (CASE A WHEN 1 THEN B ELSE C) DESC;

[sqlite] Two different Order By in one statement

[Bart Smissaert]

Also, performance is very good with a compound index on emis_number,
status, entry_date, significance.

RBS

On Wed, Apr 1, 2015 at 11:06 AM, Bart Smissaert 
wrote:

> Thanks, that does work indeed. My actual real order is now this:
>
> ORDER BY emis_number asc, status desc,
> (case status when 2 then entry_date when 1 then significance end) desc,
> entry_date desc
>
> All working perfect.
> Never realised you could do this.
>
> RBS
>
>
> On Wed, Apr 1, 2015 at 10:03 AM, Simon Slavin 
> wrote:
>
>>
>> On 1 Apr 2015, at 9:29am, Bart Smissaert 
>> wrote:
>>
>> > This needs to be sorted on column A asc
>> > Then when the value in A is 1 the second sort needs to be asc on column
>> B,
>> > but when the value in A is 2 then the second sort  needs to be asc on
>> > column C.
>>
>> You just put it all in your ORDER BY clause.
>>
>> SELECT * FROM myTable ORDER BY a, (CASE a WHEN 1 THEN b WHEN 2 THEN c
>> END) ASC
>>
>> What you can't do is create an index which perfects suit this clause.
>> You'll get the right results but not as quickly as a clause with an ideal
>> index.  I can think of a few indexes which the planner might take advantage
>> of, but not any which are ideal.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

[sqlite] Two different Order By in one statement

[Bart Smissaert]

Thanks, that does work indeed. My actual real order is now this:

ORDER BY emis_number asc, status desc,
(case status when 2 then entry_date when 1 then significance end) desc,
entry_date desc

All working perfect.
Never realised you could do this.

RBS


On Wed, Apr 1, 2015 at 10:03 AM, Simon Slavin  wrote:

>
> On 1 Apr 2015, at 9:29am, Bart Smissaert  wrote:
>
> > This needs to be sorted on column A asc
> > Then when the value in A is 1 the second sort needs to be asc on column
> B,
> > but when the value in A is 2 then the second sort  needs to be asc on
> > column C.
>
> You just put it all in your ORDER BY clause.
>
> SELECT * FROM myTable ORDER BY a, (CASE a WHEN 1 THEN b WHEN 2 THEN c END)
> ASC
>
> What you can't do is create an index which perfects suit this clause.
> You'll get the right results but not as quickly as a clause with an ideal
> index.  I can think of a few indexes which the planner might take advantage
> of, but not any which are ideal.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Performance issue

[Jeff Roux]

Here is the complete schema:

sqlite> .schema
CREATE TABLE application (id INTEGER CONSTRAINT applpk PRIMARY KEY,
shortname VARCHAR(64), name VARCHAR(256));

CREATE TABLE flows (idx INTEGER PRIMARY KEY, ipLan INTEGER, ipWan INTEGER,
flags INTEGER, portLan INTEGER, portWan INTEGER, tos INTEGER, protocol
INTEGER, wanTcpFlags INTEGER, nbBytesDecompOut INTEGER, nbBytesCompIn
INTEGER, duration INTEGER, applId INTEGER, CST INTEGER, SRT INTEGER, RTT
INTEGER, lan_retransmission INTEGER, wan_retransmission INTEGER,
nbPktDecompIn INTEGER, nbPktCompOut INTEGER, nbBytesDecompIn INTEGER,
nbBytesCompOut INTEGER, timestamp INTEGER, rtpTypeLAN INTEGER,
rtpPktsLossLAN INTEGER, rtpJitterLAN INTEGER, rtpFactorRLAN INTEGER,
rtpTypeWAN INTEGER, rtpPktsLossWAN INTEGER, rtpJitterWAN INTEGER,
rtpFactorWLAN INTEGER, nbBytesDbcDecompOut INTEGER, nbBytesDbcCompIn
INTEGER, nbBytesDefDecompOut INTEGER, nbBytesDefCompIn INTEGER,
nbPktDecompOut INTEGER, nbPktCompIn INTEGER, nbBytesDbcDecompIn INTEGER,
nbBytesDbcCompOut INTEGER, nbBytesDefDecompIn INTEGER, nbBytesDefCompOut
INTEGER);

CREATE INDEX idxApp ON flows(applId);
CREATE INDEX idxLanWan ON flows(ipLan, ipWan, portWan, portLan);
CREATE INDEX idxProto ON flows(protocol);
CREATE INDEX idxTos ON flows(tos);
CREATE INDEX idxTsLanWan ON flows(timestamp, ipLan, ipWan, portWan,
portLan);
CREATE INDEX tsindex ON flows(timestamp);


2015-03-30 12:44 GMT+02:00 Jeff Roux :

> Simon,
>
> here is the list of the indexes that were already defined on the table:
> CREATE INDEX idxLanWan ON flows(ipLan, ipWan, portWan, portLan);
> CREATE INDEX idxProto ON flows(protocol);
> CREATE INDEX idxTos ON flows(tos);
> CREATE INDEX idxTsLanWan ON flows(timestamp, ipLan, ipWan, portWan,
> portLan);
> CREATE INDEX tsindex ON flows(timestamp);
>
> I added the ones you gave me:
> CREATE INDEX ts1 ON flows (portLan, protocol, timestamp);
> CREATE INDEX ts2 ON flows (portLan, timestamp);
> CREATE INDEX ts3 ON flows (protocol, portLan, timestamp);
> CREATE INDEX ts4 ON flows (protocol, timestamp);
>
>  and it appears it now uses the ts4 index, but the time spent is still ~ 5s
>
>
> sqlite> EXPLAIN QUERY PLAN SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as
> vol, portLan as item FROM  flows  WHERE timestamp>=1383770600 AND
> timestamp<=1384770600 AND protocol IN (17, 6) GROUP BY portLan ORDER BY vol
> DESC LIMIT 6;
> 0|0|0|SEARCH TABLE flows USING INDEX ts4 (protocol=? AND timestamp>? AND
> timestamp 0|0|0|EXECUTE LIST SUBQUERY 1
> 0|0|0|USE TEMP B-TREE FOR GROUP BY
> 0|0|0|USE TEMP B-TREE FOR ORDER BY
>
>
>
> 2015-03-30 12:20 GMT+02:00 Simon Slavin :
>
>>
>> On 30 Mar 2015, at 10:46am, Jeff Roux  wrote:
>>
>> > This daemon is running on a HP server with 12 cores, 32 GB of RAM,
>> > and a SSD drive. I have performance issues with some requests. For
>> > instance, the following request takes more than 5 seconds to
>> > accomplish with SQlite3 (in that particular case, the WHERE clause
>> > selects all the data in the database, i.e. 100 rows):
>> >
>> > SELECT SUM(nbBytesDecompOut + nbBytesCompIn) as vol, portLan as item
>> > FROM  flows
>> > WHERE timestamp>=1383770600 AND timestamp<=1384770600 AND protocol IN
>> (17, 6)
>> > GROUP BY portLan ORDER BY vol DESC LIMIT 6;
>>
>> What indexes do you have on that table ?  I would expect to get best
>> results from one of the following indexes
>>
>> (portLan, protocol, timestamp)
>> (portLan, timestamp)
>> (protocol, portLan, timestamp)
>> (protocol, timestamp)
>>
>> For best results, CREATE all of those indexes and any others that strike
>> your fancy, run ANALYZE, then execute your SELECT.  If this gives
>> satisfactory results, use
>>
>> EXPLAIN QUERY PLAN [your SELECT statement]
>>
>> and see which index SQLite decided to use.  You can then delete the other
>> unused indexes and run ANALYZE one final time for a little more
>> optimization.
>>
>> It's worth noting that SQLite, since it has no server, has no
>> server-level caching.  If you're doing repeated queries using mySQL, after
>> the first one most of the index is cached so the times you're seeing are
>> faster than SQLite can ever produce.  Two disadvantages of mySQL are the
>> memory usage and the work involved in administration.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

[sqlite] insert in sqlite (returning value for inserted row)

[Kumar Suraj]

Hi Richard.. this wont work for me due to following reason.

If a separate thread performs a new INSERT
 on the same database connection
while the sqlite3_last_insert_rowid()
 function is running
and thus changes the last insert rowid
, then the value
returned by sqlite3_last_insert_rowid()
 is unpredictable and
might not equal either the old or the new last insert rowid
.

On Tue, Mar 31, 2015 at 6:23 PM, Richard Hipp  wrote:

> https://www.sqlite.org/c3ref/last_insert_rowid.html
>
> On Tue, Mar 31, 2015 at 9:19 PM, Kumar Suraj  wrote:
>
> > Hi
> >
> > I am using sqlite C interface for inserting data in the table. The
> primary
> > key is a 64 bit integer which i need to auto-increment and get populated
> > automatically as we do not provide that value in insert statement. Is
> there
> > a way i can get the autoincremented value for each row inserted when
> ever i
> > execute my insert.
> >
> > -Suraj
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Two different Order By in one statement

[Simon Slavin]

On 1 Apr 2015, at 9:29am, Bart Smissaert  wrote:

> This needs to be sorted on column A asc
> Then when the value in A is 1 the second sort needs to be asc on column B,
> but when the value in A is 2 then the second sort  needs to be asc on
> column C.

You just put it all in your ORDER BY clause.

SELECT * FROM myTable ORDER BY a, (CASE a WHEN 1 THEN b WHEN 2 THEN c END) ASC

What you can't do is create an index which perfects suit this clause.  You'll 
get the right results but not as quickly as a clause with an ideal index.  I 
can think of a few indexes which the planner might take advantage of, but not 
any which are ideal.

Simon.

[sqlite] Behaviour changed from 3.7 to 3.8

[Richard Hipp]

On 4/1/15, Marcel Keller  wrote:
> Hi,
>
> Consider the following database:
>
> sqlite> .schema
> CREATE TABLE counter (i integer);
> CREATE TABLE expenses (date float, month text, amount numeric);
> sqlite> select * from counter;
> 0
> 1
> 2
> sqlite> select * from expenses;
> 2457113.5163101||100
>
> In the latest binary from the website, I get the following:
>
> sqlite> .version
> SQLite 3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d
> sqlite> select strftime("%Y-%m",date,'start of month',i || ' months') as
> month, sum(amount) from expenses join counter where i < 12 group by month;
> 2015-06|300
>
> However, with an earlier version, I get:
>
> sqlite> .version
> SQLite 3.7.15.2 2013-01-09 11:53:05
> c0e09560d26f0a6456be9dd3447f5311eb4f238f
> sqlite> select strftime("%Y-%m",date,'start of month',i || ' months') as
> month, sum(amount) from expenses join counter where i < 12 group by month;
> 2015-04|100
> 2015-05|100
> 2015-06|100
>
> It seems that the meaning of "group by month" changed from referring to
> "as mount" to expenses.month. Is this intentional?
>

Yes.  This was a bug fix.  The current behavior agrees with the
behavior of MySQL, Oracle, PostgreSQL, and SQL Server.

Actually, your query will not run on most other database engines.  But
the following example shows that the new behavior is correct:

CREATE TABLE t1(a INT,b INT);
INSERT INTO t1(a,b) VALUES(2,1),(3,10),(4,100),(5,1000);
SELECT CAST(a/2 AS int) AS a, sum(b) FROM t1 GROUP BY a;



-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Two different Order By in one statement

[Bart Smissaert]

OK, let me give the simplest example possible.

Table with 3 integer fields, A, B and C

AB C

1 1 2
2 1 2
1 2 1
2 2 1

This needs to be sorted on column A asc
Then when the value in A is 1 the second sort needs to be asc on column B,
but when the value in A is 2 then the second sort  needs to be asc on
column C.
So, the result should be this:

AB C

1 1 2
1 2 1
2 2 1
2 1 2

I could think of various ways to achieve this, but not with one statement.


RBS



On Wed, Apr 1, 2015 at 2:43 AM, Igor Tandetnik  wrote:

> On 3/31/2015 7:50 PM, Bart Smissaert wrote:
>
>> Say I have a table with 3 fields. Depending on a value in field 1 (this
>> value will be either 1 or 2)
>> I want to do a different sort order sorting on fields 2 and 3.
>> This will be either order by field2 desc, field3 desc or field3 desc,
>> field2 desc.
>>
>
> Color me dense, but I for one do not understand from this description what
> you are trying to achieve. For one thing, you say you want sort order to
> depend on field1, which only contains two different values, but you plan to
> somehow derive three different sort orders from it.
>
> Could you show some sample data, in the order you want it to appear?
> Perhaps the pattern would be easier to discern this way.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Donald Griggs]

Regarding: "... would love to see the SQLite schema Lightroom uses"

Hi, Rob

I am not a user of Lightroom, but there's a start at this link:

http://www.earthboundlight.com/phototips/querying-sqlite-lightroom-database.html

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Andy Rahn]

Hi Mark

I echo the suggestion that you make sure your HDD is working properly by
doing a disk scan of some sort.  It's dangerous to start trying other
solutions if the drive is about to fail!  While you're at it, it's probably
a good idea to make sure your backups are up to date -- and by backup I
mean not only the .lrcat file that store Lightroom's metadata but also the
original camera files (JPGs or raw files).

It's also possible that some other process is doing some work on your
system.  Do you have Windows Defender or virus software installed?  You can
use Task Manager (right click the task bar and choose "Task Manager") to
see what processes are using a lot of CPU.  If it isn't Lightroom then some
other app is stealing CPU cycles -- try shutting that other app down?  (But
be careful you don't shut down a system process, that can lead to things
not working!)

Adobe has also written several pages about how to optimize Lightroom:

https://helpx.adobe.com/lightroom/kb/optimize-performance-lightroom.html

I'm not sure I agree with the order there -- I'd start with this "Optimize
the Catalog" step:

https://helpx.adobe.com/lightroom/kb/optimize-performance-lightroom.html#main_Optimize_the_catalog

These pages might duplicate what is on that one, but they're possibly also
helpful:

https://helpx.adobe.com/lightroom/kb/performance-hints.html

http://blogs.adobe.com/lightroomjournal/2013/06/lightroom-basic-troubleshooting-to-fix-most-issues.html

And don't forget that Adobe Lightroom has a fantastic user base that helps
each other out -- you can ask your question there and I bet someone will
help you some more:

https://forums.adobe.com/community/lightroom

or if you figure out what is going on you can talk directly to Adobe here:

http://feedback.photoshop.com/photoshop_family

 - Andy




On Tue, Mar 31, 2015 at 7:17 PM, Mark Romero  wrote:

> Hey Everyone:
>
> Was there a recent Windows 7 64-bit update that affected SQLite?
>
> A few months ago one of the programs that I use (Lightroom) started to have
> long periods where it would stop responding. It wouldn't crash, but it
> would just keep my CPU pegged at 100% and the program would stop
> responding. This would happen at random times.
>
> It never acted like this before, and I think it started acting funny just
> right after a win 7 64-bit automatic update.
>
> The SLQlite "server" is built into Lightroom, so I have no access to the
> code.
>
> One thing that is strange is that Adobe has another program (Adobe Camera
> Raw) which is nearly identical to lightroom except that Lightroom uses
> SQLite to store data while Adobe Camera Raw writes information into a
> separate file (an XMP Side Cart file). ACR runs fine, while Lightroom has
> problems.
>
> So if you know of a recent windows 7 update that may affect SQLite, please
> let me know. maybe there are drivers I can download somewhere to fix the
> problem.
>
> Thanks in advance.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Two different Order By in one statement

[R.Smith]

On 2015-04-01 01:50 AM, Bart Smissaert wrote:
> Say I have a table with 3 fields. Depending on a value in field 1 (this
> value will be either 1 or 2)
> I want to do a different sort order sorting on fields 2 and 3.
> This will be either order by field2 desc, field3 desc or field3 desc,
> field2 desc.
> I thought of a union, but doesn't allow this.
> Any suggestions?

Well, this is technically impossible (influencing an SQL statement from 
the values returned from that statement is impossible for obvious reasons).

But, if you do this:

SELECT Field1, Field2, Field3, Field4, (CASE Field1 WHEN 1 THEN Field2 
ELSE Field3) AS Sort1, (CASE Field1 WHEN 0 THEN Field2 ELSE Field3) AS Sort2
FROM SomeTable
WHERE 1
ORDER BY Sort1, Sort2;

You should achieve the exact result.


NOTE: This is a really really bad way of doing things, the sort order 
should not be determined like this in any sane system - those settings 
should live in a DB or at least a table outside of the data being inspected.

[sqlite] insert in sqlite (returning value for inserted row)

[Simon Slavin]

On 1 Apr 2015, at 2:19am, Kumar Suraj  wrote:

> I am using sqlite C interface for inserting data in the table. The primary
> key is a 64 bit integer which i need to auto-increment and get populated
> automatically as we do not provide that value in insert statement. Is there
> a way i can get the autoincremented value for each row inserted when ever i
> execute my insert.

[explanation simplified because I don't want to have to type three pages]

SQLite does this automatically if you do not declare a primary key for a table. 
 It will create an INTEGER column called 'rowid' for you and increment the 
value for each row you enter.  You will never see that column unless you ask 
for it explicitly by doing something like

SELECT rowid,* FROM myTable LIMIT 10;

but it's a real column and you can select, search and sort on it just like any 
other INTEGER column.  If you have already created your table try the above 
statement on it.

Alternatively you can follow the instructions from FAQ 1 on this page:



This does more or less the same thing, with the advantage that you can specify 
your own name for the column.

Simon.

[sqlite] Two different Order By in one statement

[Bart Smissaert]

Say I have a table with 3 fields. Depending on a value in field 1 (this
value will be either 1 or 2)
I want to do a different sort order sorting on fields 2 and 3.
This will be either order by field2 desc, field3 desc or field3 desc,
field2 desc.
I thought of a union, but doesn't allow this.
Any suggestions?

RBS

[sqlite] WAL with linux and win

[MM]

I have a sqlite3 3.8.8.2/3 database me.db. I access it from:

   1. C++/odb (linux64/win64) in read only mode
   2. python3 (linux64 server) in readonly mode from process 1 and
   read/write mode from process 2, concurrently.
   3. python3/DB BrowserSQLite (win64 PC) in read/write mode, not
   concurrently.

I share the file through a git repository.

I just realized my concurrency need in point 2., and discovered WAL.

Is all I need to do: PRAGMA journal_mode=WAL; once from sqlite3 command
line?

Any further arguments when opening the db from sqlite3.connect in python,
or from C++/odb ? How about win/linux dbfile travel through git? I
understand 2 more files appear in WAL, the wal-index and the shmem file. I
can't just share across the 2 boxes server and pc?

Rds,

MM