Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

[Tony Papadimitriou]

One obvious shortcut would be to factor out the common part of the select 
using WITH


(Hopefully accurate) example:

with t as (
 select GroupName, JobName, Start, End, Status, (strftime('%s', End) - 
strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13'

 )
select * from (select * from t where GroupName like 'GRP01%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP04%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP12%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP15%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select * from t where GroupName like 'GRP20%' ORDER BY Length 
DESC LIMIT 10);


-Original Message- 
From: pihu...@free.fr

Sent: Tuesday, October 14, 2014 12:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

Hello!

I'm trying to find a way to reduce the length of the following query using 
SQLite:


select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP01%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP04%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP12%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP15%' ORDER BY Length 
DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, 
(strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs 
where PlanDate = '2014-02-13' and GroupName like 'GRP20%' ORDER BY Length 
DESC LIMIT 10);


I want to select the ten longest jobs for five different groups (GRP01%, 
GRP04%, GRP12%, GRP15% and GRP20%). I can't find a solution without using 
this "UNION ALL" trick.


Contents in 'Start' and 'End' columns are ISO 8601 formatted.
I use "(strftime('%s', End) - strftime('%s', Start)) as Length" to calculate 
the length of each job, then "ORDER BY Length DESC LIMIT 10" for each group.


Do you know a simplest/better way to perform this query?
Thanks


Pierre
___
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] [SQL Query] Top 10 of 5 different groups sorted by length

[RSmith]

On 2014/10/13 23:21, pihu...@free.fr wrote:

Hello!

I'm trying to find a way to reduce the length of the following query using 
SQLite:

select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP01%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP04%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP12%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP15%' ORDER BY Length DESC LIMIT 10)
UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', 
End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = 
'2014-02-13' and GroupName like 'GRP20%' ORDER BY Length DESC LIMIT 10);

I want to select the ten longest jobs for five different groups (GRP01%, GRP04%, GRP12%, 
GRP15% and GRP20%). I can't find a solution without using this "UNION ALL" 
trick.


There is nothing wrong with this query, and the UNION ALL "trick" exists exactly for this purpose. I know it seems a bit convoluted 
but it achieves the goal and it should be real fast. To get rid of the convolution I would implement a view, either temporary or 
permanent, something like this:


CREATE TEMPORARY VIEW ReportJobLengths AS
  SELECT GroupName, JobName, Start, End, Status,
 (strftime('%s', End) - strftime('%s', Start)) as Length DESC
  FROM ReportJobs where PlanDate = '2014-02-13'
  ORDER BY Length;

  SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP01%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP04%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP12%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP15%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP20%';

You may also create a persistent view and then always use it (without the 
"temporary" keyword).

To get even more compact, I would go with Igor's SQL which is quite succint, but if those tables are big, that query will run quite 
a bit slower - which is only a problem if the speed really matters.



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

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

[RSmith]

And of course there must be a LIMIT 10 added to every Union'd select (which I 
forgot):
SELECT * FROM ReportJobLengths R WHERE GroupName like 
'GRP01%' LIMIT 10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP04%' LIMIT 
10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP12%' LIMIT 
10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP15%' LIMIT 
10
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP20%' LIMIT 
10;

etc.

On 2014/10/14 09:22, RSmith wrote:


On 2014/10/13 23:21, pihu...@free.fr wrote:

Hello!

I'm trying to find a way to reduce the length of the following query using 
SQLite:

select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from 
ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP01%' ORDER BY Length DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from 
ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP04%' ORDER BY Length DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from 
ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP12%' ORDER BY Length DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from 
ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP15%' ORDER BY Length DESC LIMIT 10)

UNION ALL
select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from 
ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP20%' ORDER BY Length DESC LIMIT 10);


I want to select the ten longest jobs for five different groups (GRP01%, GRP04%, GRP12%, GRP15% and GRP20%). I can't find a 
solution without using this "UNION ALL" trick.


There is nothing wrong with this query, and the UNION ALL "trick" exists exactly for this purpose. I know it seems a bit 
convoluted but it achieves the goal and it should be real fast. To get rid of the convolution I would implement a view, either 
temporary or permanent, something like this:


CREATE TEMPORARY VIEW ReportJobLengths AS
  SELECT GroupName, JobName, Start, End, Status,
 (strftime('%s', End) - strftime('%s', Start)) as Length DESC
  FROM ReportJobs where PlanDate = '2014-02-13'
  ORDER BY Length;

  SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP01%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP04%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP12%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP15%'
UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP20%';

You may also create a persistent view and then always use it (without the 
"temporary" keyword).

To get even more compact, I would go with Igor's SQL which is quite succint, but if those tables are big, that query will run 
quite a bit slower - which is only a problem if the speed really matters.



___
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] [SQL Query] Top 10 of 5 different groups sorted by length

[Mark Lawrence]

On Tue Oct 14, 2014 at 09:25:20AM +0200, RSmith wrote:
> >
> >To get even more compact, I would go with Igor's SQL which is
> >quite succint, but if those tables are big, that query will run
> >quite a bit slower - which is only a problem if the speed really
> >matters.

I'm a little curious about why you say a CTE statement is slower than a
VIEW for large tables. I don't have large tables to test on but I get
the same query plan for both versions on small test tables. What
changes with size?

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

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted bylength

[Tony Papadimitriou]

I forgot to also factor out the ORDER BY.  So, the updated query is:

-
with t as (
 select GroupName, JobName, Start, End, Status,
(strftime('%s', End) - strftime('%s', Start)) as Length
   from ReportJobs
   where PlanDate = '2014-02-13'
   order by Length desc
 )
select * from (select * from t where GroupName like 'GRP01%' LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP04%' LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP12%' LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP15%' LIMIT 10)
UNION ALL
select * from (select * from t where GroupName like 'GRP20%' LIMIT 10);
-

The double select [select * from  (select * from t ...] is required because 
LIMIT is only allowed at the end of a UNION, so you need a sub-query to 
overcome this.  (BTW, this seems like an artificial restriction that could 
be removed, but I could be wrong.) 


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

[sqlite] Unicode support in SQLite

[Aleksey Tulinov]

Hello,

I'm glad to announce that nunicode SQLite extension was updated to 
support Unicode-conformant case folding and was improved on performance 
of every component provided to SQLite.


You can read about and download this extension at BitBucket page of 
nunicode library: 
https://bitbucket.org/alekseyt/nunicode#markdown-header-sqlite3-extension


This extension provides the following Unicode-aware components:

- upper(X)
- lower(X)
- X LIKE Y ESCAPE Z
- COLLATE NU700 : case-sensitive Unicode 7.0.0 collation
- COLLATE NU700_NOCASE : case-insensitive Unicode 7.0.0 collation
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] line break, or similar, in query results

[Paul Sanderson]

Another morning another question

I have a query that performs a recursive query and outputs a varying
number of rows using group by

sqlite> with recursive path as (select id, previd, location from
cities union all select cities.id, cities.previd, cities.location from
path join cities on (path.previd = cities.id)) select * from path
group by id;

1|0|texas
2|1|new york
3|2|washington
4|0|tampa
5|0|atlanta
6|5|charleston


I'd like to add a break between groups in the results  so it looks somethng like

1|0|texas
2|1|new york
3|2|washington

4|0|tampa

5|0|atlanta
6|5|charleston


(it doesn't have to be a newline - just soemthng to make it more readable)


* I know texas isn't a city :)


Cheers

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] line break, or similar, in query results

[Clemens Ladisch]

Paul Sanderson wrote:
> 1|0|texas
> 2|1|new york
> 3|2|washington
> 4|0|tampa
> 5|0|atlanta
> 6|5|charleston
>
> I'd like to add a break between groups in the results  so it looks somethng 
> like
>
> 1|0|texas
> 2|1|new york
> 3|2|washington
>
> 4|0|tampa
>
> 5|0|atlanta
> 6|5|charleston

SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, *
FROM (SELECT ...);


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

[sqlite] Make a database read-only?

[Ross Altman]

I need to host a fixed, unchanging database online, and I want to make sure
that anyone who downloads it cannot add to it. Is there any way to set the
permissions to be read-only within sqlite?

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

Re: [sqlite] Make a database read-only?

[Martin Engelschalk]

Hello Ross,

you could add triggers to all tables that RAISE(ROLLBACK, 'Forbidden') 
on all operations (insert, update and delete), see 
http://www.sqlite.org/lang_createtrigger.html, bottom of the page.
However, it is difficult to see how to stop the downloaders from 
removing these triggers or indeed any other mechanism to prevent changes 
to the data. It is their file after download


HTH
Martin

Am 14.10.2014 08:19, schrieb Ross Altman:

I need to host a fixed, unchanging database online, and I want to make sure
that anyone who downloads it cannot add to it. Is there any way to set the
permissions to be read-only within sqlite?

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


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] Make a database read-only?

[Ross Altman]

Hi Martin,

Thank you, I'll definitely look into that. It's unfortunate that there
isn't a simpler way to do this... oh well.

Best,
Ross

On Tue, Oct 14, 2014 at 7:22 AM, Martin Engelschalk <
engelsch...@codeswift.com> wrote:

> Hello Ross,
>
> you could add triggers to all tables that RAISE(ROLLBACK, 'Forbidden') on
> all operations (insert, update and delete), see
> http://www.sqlite.org/lang_createtrigger.html, bottom of the page.
> However, it is difficult to see how to stop the downloaders from removing
> these triggers or indeed any other mechanism to prevent changes to the
> data. It is their file after download
>
> HTH
> Martin
>
> Am 14.10.2014 08:19, schrieb Ross Altman:
>
>> I need to host a fixed, unchanging database online, and I want to make
>> sure
>> that anyone who downloads it cannot add to it. Is there any way to set the
>> permissions to be read-only within sqlite?
>>
>> Thanks,
>> Ross
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
>
> *Codeswift GmbH *
> Kräutlerweg 20a
> A-5020 Salzburg
> Tel: +49 (0) 8662 / 494330
> Mob: +49 (0) 171 / 4487687
> Fax: +49 (0) 3212 / 1001404
> engelsch...@codeswift.com
> www.codeswift.com / www.swiftcash.at
>
> Codeswift Professional IT Services GmbH
> Firmenbuch-Nr. FN 202820s
> UID-Nr. ATU 50576309
>
> ___
> 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] Make a database read-only?

[Martin Engelschalk]

Hi Ross,

i don't know if there is a simpler way. Perhaps someone on the list has 
a better idea. What I wanted to say is that you probanly cannot prevent 
your downloaders from removing anything you add to make the data read-only.


Martin.


Am 14.10.2014 13:48, schrieb Ross Altman:

Hi Martin,

Thank you, I'll definitely look into that. It's unfortunate that there
isn't a simpler way to do this... oh well.

Best,
Ross

On Tue, Oct 14, 2014 at 7:22 AM, Martin Engelschalk <
engelsch...@codeswift.com> wrote:


Hello Ross,

you could add triggers to all tables that RAISE(ROLLBACK, 'Forbidden') on
all operations (insert, update and delete), see
http://www.sqlite.org/lang_createtrigger.html, bottom of the page.
However, it is difficult to see how to stop the downloaders from removing
these triggers or indeed any other mechanism to prevent changes to the
data. It is their file after download

HTH
Martin

Am 14.10.2014 08:19, schrieb Ross Altman:


I need to host a fixed, unchanging database online, and I want to make
sure
that anyone who downloads it cannot add to it. Is there any way to set the
permissions to be read-only within sqlite?

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


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
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


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] Make a database read-only?

[Richard Hipp]

On Tue, Oct 14, 2014 at 2:19 AM, Ross Altman 
wrote:

> I need to host a fixed, unchanging database online, and I want to make sure
> that anyone who downloads it cannot add to it. Is there any way to set the
> permissions to be read-only within sqlite?
>

Change the 18th byte of the file from 1 or 2 to 99.

Anyone who downloads the file can always change that byte back to its
original value using a binary editor and then write the database.  But you
have at least then made the problem more difficult for them.

-- 
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] Make a database read-only?

[Clemens Ladisch]

Ross Altman wrote:
> I need to host a fixed, unchanging database online,

An SQLite database is just a file.

> and I want to make sure that anyone who downloads it cannot add to it.

It's possible to run "sqlite3 thedata.db .dump > thedata.sql" and then
to remove any protection that is still present with a text editor.

What are you actually trying to protect against?


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

Re: [sqlite] line break, or similar, in query results

[Paul Sanderson]

Thanks Clemens

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 14 October 2014 12:09, Clemens Ladisch  wrote:
> Paul Sanderson wrote:
>> 1|0|texas
>> 2|1|new york
>> 3|2|washington
>> 4|0|tampa
>> 5|0|atlanta
>> 6|5|charleston
>>
>> I'd like to add a break between groups in the results  so it looks somethng 
>> like
>>
>> 1|0|texas
>> 2|1|new york
>> 3|2|washington
>>
>> 4|0|tampa
>>
>> 5|0|atlanta
>> 6|5|charleston
>
> SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, *
> FROM (SELECT ...);
>
>
> Regards,
> Clemens
> ___
> 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] line break, or similar, in query results

[RSmith]

On 2014/10/14 13:09, Clemens Ladisch wrote:

Paul Sanderson wrote:

SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, *
FROM (SELECT ...);


This solution from Clemens will work perfectly, and depending on the kind of OS you use and output method it might even work to add 
something like '-\n' or '\r\n' as the Inserted item if char(10) causes headaches. On output produced for Windows systems 
that should also be  char(13) || char(10).


Have a great day!
Ryan

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

Re: [sqlite] Make a database read-only?

[RSmith]

On 2014/10/14 13:48, Ross Altman wrote:

Hi Martin,

Thank you, I'll definitely look into that. It's unfortunate that there
isn't a simpler way to do this... oh well.


Let me bud in here since I encounter this question a lot in other matters. There typically are three reasons one would like to 
protect the data in a file from end-users' meddling:

  - You need to protect idiot users against themselves,
  - You need the data to remain clean and untarnished to make some other system 
depending on it function correctly, or
  - The data itself is important for legal reasons or you have some kind of 
liability towards data accuracy.

If it is the first case, then you are stuffed and Richard's byte-change is the 
closest to a solution you can come.

If the second case, then make the other system check the file, add table with encrypted values that has meaning only to the other 
system, or even use file encryption for the entire database - this is common and can be had commercially from 
http://www.hwaci.com/sw/sqlite/see.html


For the latter I suggest recording the file hash (sha512+) whenever you update it and store that in a data list marking release 
dates. That way if someone claims that they have data gotten from you that says x while you claim it says y...  then simply whip out 
the hash list and compare to their file, any changes will be evident immediately.


You probably need to then also keep a register history of DBs that correspond to those hashes, else you cannot prove the data from 
that file to correspond to any specific hash. Also it is safer to upload such hashes to a blog or something that is not under your 
control, where any edits will be marked and timestamped, then it is impossible for yourself to meddle with the files after release 
and a public record exists of the file version hashes. Pretty solid in legal terms.


Whichever way, good luck!
Ryan


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

Re: [sqlite] Unicode support in SQLite

[Kevin Benson]

On Tue, Oct 14, 2014 at 4:37 AM, Aleksey Tulinov 
wrote:

> Hello,
>
> I'm glad to announce that nunicode SQLite extension was updated to support
> Unicode-conformant case folding and was improved on performance of every
> component provided to SQLite.
>
> You can read about and download this extension at BitBucket page of
> nunicode library: https://bitbucket.org/alekseyt/nunicode#markdown-
> header-sqlite3-extension
>
> This extension provides the following Unicode-aware components:
>
> - upper(X)
> - lower(X)
> - X LIKE Y ESCAPE Z
> - COLLATE NU700 : case-sensitive Unicode 7.0.0 collation
> - COLLATE NU700_NOCASE : case-insensitive Unicode 7.0.0 collation
>

https://bitbucket.org/alekseyt/nunicode/downloads/libnusqlite3-1.4-4a0e4773-win32.zip
<---
404 response code

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[John Hascall]

Some code you may find useful to enforce the readonly byte (do this before
your program opens the DB).
John

#include 
#include 
#define

int setRObyte (
const char * sqDBfn
) {
int fd  = open(sqDBfn, O_WRONLY, 0);
int rc  = -1;

if (fd == -1) return -1;
#ifdef  HAVE_PWRITE
if (pwrite(fd, "\143", (size_t)1, (off_t)18) == 1) rc = 0;
#else
if (lseek(fd, (off_t)18, SEEK_SET) == (off_t)18) == 1) ? 0 : -1;
rc = (write(fd, "\143", (size_t)1) == 1) ? 0 : -1;
} else rc = -1;
#endif
(void)close(fd);
return rc;
}

On Tue, Oct 14, 2014 at 7:23 AM, Richard Hipp  wrote:

> On Tue, Oct 14, 2014 at 2:19 AM, Ross Altman 
> wrote:
>
> > I need to host a fixed, unchanging database online, and I want to make
> sure
> > that anyone who downloads it cannot add to it. Is there any way to set
> the
> > permissions to be read-only within sqlite?
> >
>
> Change the 18th byte of the file from 1 or 2 to 99.
>
> Anyone who downloads the file can always change that byte back to its
> original value using a binary editor and then write the database.  But you
> have at least then made the problem more difficult for them.
>
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[John Hascall]

Well some keystroke I hit apparently made the stupid browser send that
before I finished editing it, but you get the idea


Sigh,
John

On Tue, Oct 14, 2014 at 9:12 AM, John Hascall  wrote:

> Some code you may find useful to enforce the readonly byte (do this before
> your program opens the DB).
> John
>
> #include 
> #include 
> #define
>
> int setRObyte (
> const char * sqDBfn
> ) {
> int fd  = open(sqDBfn, O_WRONLY, 0);
> int rc  = -1;
>
> if (fd == -1) return -1;
> #ifdef  HAVE_PWRITE
> if (pwrite(fd, "\143", (size_t)1, (off_t)18) == 1) rc = 0;
> #else
> if (lseek(fd, (off_t)18, SEEK_SET) == (off_t)18) == 1) ? 0 : -1;
> rc = (write(fd, "\143", (size_t)1) == 1) ? 0 : -1;
> } else rc = -1;
> #endif
> (void)close(fd);
> return rc;
> }
>
> On Tue, Oct 14, 2014 at 7:23 AM, Richard Hipp  wrote:
>
>> On Tue, Oct 14, 2014 at 2:19 AM, Ross Altman 
>> wrote:
>>
>> > I need to host a fixed, unchanging database online, and I want to make
>> sure
>> > that anyone who downloads it cannot add to it. Is there any way to set
>> the
>> > permissions to be read-only within sqlite?
>> >
>>
>> Change the 18th byte of the file from 1 or 2 to 99.
>>
>> Anyone who downloads the file can always change that byte back to its
>> original value using a binary editor and then write the database.  But you
>> have at least then made the problem more difficult for them.
>>
>> --
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] line break, or similar, in query results

[Clemens Ladisch]

RSmith wrote:
> On 2014/10/14 13:09, Clemens Ladisch wrote:
>> SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, *
>> FROM (SELECT ...);
>
> This solution from Clemens will work perfectly, and depending on the
> kind of OS you use and output method it might even work to add
> something like '-\n' or '\r\n' as the Inserted item if
> char(10) causes headaches.

  sqlite> select '-\n';
  -\n

That's not very useful.  ;-)

> On output produced for Windows systems that should also be
> char(13) || char(10).

The sqlite3 shell uses the C runtime conventions, where stdout is in
text mode, so char(10) is correct even on Windows.


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

Re: [sqlite] Make a database read-only?

[Igor Tandetnik]

On 10/14/2014 2:19 AM, Ross Altman wrote:

I need to host a fixed, unchanging database online, and I want to make sure
that anyone who downloads it cannot add to it. Is there any way to set the
permissions to be read-only within sqlite?


Why do you care what a person does with a file on their hard drive in 
the privacy of their home? In any case, you can't really stop them from 
doing whatever they want with their own file, even if that file started 
life as a copy of yours.

--
Igor Tandetnik

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

Re: [sqlite] Make a database read-only?

[Andrea Peri]

Against legal. The best approach is to calculate the MD5 of the file
If the file chance, the MD5 change Aldo.
 Il 14/ott/2014 15:37 "RSmith"  ha scritto:

>
> On 2014/10/14 13:48, Ross Altman wrote:
>
>> Hi Martin,
>>
>> Thank you, I'll definitely look into that. It's unfortunate that there
>> isn't a simpler way to do this... oh well.
>>
>
> Let me bud in here since I encounter this question a lot in other matters.
> There typically are three reasons one would like to protect the data in a
> file from end-users' meddling:
>   - You need to protect idiot users against themselves,
>   - You need the data to remain clean and untarnished to make some other
> system depending on it function correctly, or
>   - The data itself is important for legal reasons or you have some kind
> of liability towards data accuracy.
>
> If it is the first case, then you are stuffed and Richard's byte-change is
> the closest to a solution you can come.
>
> If the second case, then make the other system check the file, add table
> with encrypted values that has meaning only to the other system, or even
> use file encryption for the entire database - this is common and can be had
> commercially from http://www.hwaci.com/sw/sqlite/see.html
>
> For the latter I suggest recording the file hash (sha512+) whenever you
> update it and store that in a data list marking release dates. That way if
> someone claims that they have data gotten from you that says x while you
> claim it says y...  then simply whip out the hash list and compare to their
> file, any changes will be evident immediately.
>
> You probably need to then also keep a register history of DBs that
> correspond to those hashes, else you cannot prove the data from that file
> to correspond to any specific hash. Also it is safer to upload such hashes
> to a blog or something that is not under your control, where any edits will
> be marked and timestamped, then it is impossible for yourself to meddle
> with the files after release and a public record exists of the file version
> hashes. Pretty solid in legal terms.
>
> Whichever way, good luck!
> Ryan
>
>
> ___
> 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] [SQL Query] Top 10 of 5 different groups sorted by length

[Igor Tandetnik]

On 10/14/2014 3:51 AM, Mark Lawrence wrote:

On Tue Oct 14, 2014 at 09:25:20AM +0200, RSmith wrote:

To get even more compact, I would go with Igor's SQL which ... will run quite a 
bit slower


I'm a little curious about why you say a CTE statement is slower than a
VIEW for large tables.


My query uses neither views nor CTE. I'm a little curious about how a 
comment on the former could be construed to reflect in any way on the 
latter.

--
Igor Tandetnik

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

Re: [sqlite] Unicode support in SQLite

[Aleksey Tulinov]

On 14/10/14 17:02, Kevin Benson wrote:


https://bitbucket.org/alekseyt/nunicode/downloads/libnusqlite3-1.4-4a0e4773-win32.zip
 <---
404 response code



Thank you, fixed now.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite data source not available

[coffeenkfc]

Thanks Joe,

I removed the line, cleaned the solution, rebuilt, and still don't see
'System.Data.Sqlite Database File' in the 'Choose Data Source' window. :(

Is there a way to get older versions of the setup bundle? Some time ago on a
different machine I was able install 1.0.93.0. I wonder if this is something
related to 1.0.94.0.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite-data-source-not-available-tp78521p78601.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

Re: [sqlite] Make a database read-only?

[Igor Tandetnik]

On 10/14/2014 10:12 AM, John Hascall wrote:

Some code you may find useful to enforce the readonly byte


Of course, anyone smart enough to change the byte from read-only to 
read-write before making changes, would also be smart enough to set it 
back afterwards.

--
Igor Tandetnik

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

Re: [sqlite] sqlite data source not available

[Kevin Benson]

On Tue, Oct 14, 2014 at 11:47 AM, coffeenkfc  wrote:

> Thanks Joe,
>
> I removed the line, cleaned the solution, rebuilt, and still don't see
> 'System.Data.Sqlite Database File' in the 'Choose Data Source' window. :(


> Is there a way to get older versions of the setup bundle? Some time ago on
> a
> different machine I was able install 1.0.93.0. I wonder if this is
> something
> related to 1.0.94.0.
>

I just tried substituting the string 1.0.93.0 in place of 1.0.94.0 in the
download link URLs (both places) and got offered a download. Maybe that'll
do ya, until Joe Mistachkin (the maintainer) sees this and can respond.


--
   --
  --
 --Ô¿Ô--
K e V i N


>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/sqlite-data-source-not-available-tp78521p78601.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

Re: [sqlite] sqlite data source not available

[coffeenkfc]

Thanks Kevin,

That’s a good idea; I tried and after the countdown to download I get ‘The 
document you are looking for could not be located.’

Curses if only I didn’t delete the .93 install, even tried to recover the 
deleted file. I don’t know if it’s a .94 issue but would have liked to give it 
a try.

From: Kevin Benson [via SQLite] [mailto:ml-node+s1065341n78603...@n5.nabble.com]
Sent: October 14, 2014 10:20 AM
To: Ben Lam
Subject: Re: sqlite data source not available

On Tue, Oct 14, 2014 at 11:47 AM, coffeenkfc <[hidden 
email]> wrote:

> Thanks Joe,
>
> I removed the line, cleaned the solution, rebuilt, and still don't see
> 'System.Data.Sqlite Database File' in the 'Choose Data Source' window. :(


> Is there a way to get older versions of the setup bundle? Some time ago on
> a
> different machine I was able install 1.0.93.0. I wonder if this is
> something
> related to 1.0.94.0.
>

I just tried substituting the string 1.0.93.0 in place of 1.0.94.0 in the
download link URLs (both places) and got offered a download. Maybe that'll
do ya, until Joe Mistachkin (the maintainer) sees this and can respond.


--
   --
  --
 --Ô¿Ô--
K e V i N


>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/sqlite-data-source-not-available-tp78521p78601.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
___
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/sqlite-data-source-not-available-tp78521p78603.html
To unsubscribe from sqlite data source not available, click 
here.
NAML


I am using the Free version of SPAMfighter.
SPAMfighter has removed 744 of my spam emails to date.

Do you have a slow PC? Try 
a free scan!




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite-data-source-not-available-tp78521p78604.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

Re: [sqlite] sqlite data source not available

[Joe Mistachkin]

coffeenkfc wrote:
> 
> I removed the line, cleaned the solution, rebuilt, and still don't see
> 'System.Data.Sqlite Database File' in the 'Choose Data Source' window. :( 
> 

Which setup package did you install?  Were there any error messages?

Could you provide more details about the target system and the log files
from
the installation, etc?

The log files should be located in the %TEMP% directory on the target
machine.
They will have names similar to "Setup Log 2014-10-14 #001.txt" and
"Installer.exe.trace.tmp29.log".

Any other additional information you could provide would be useful as well.

> 
> Is there a way to get older versions of the setup bundle? Some time ago on
a
> different machine I was able install 1.0.93.0. I wonder if this is
something
> related to 1.0.94.0.
> 

Yes.  As Kevin Benson pointed out, you can simply replace the version
numbers
(*BOTH* of them) in the URI and get it from there.

For example:


https://system.data.sqlite.org/downloads/1.0.94.0/sqlite-netFx45-setup-bundl
e-x86-2012-1.0.94.0.exe

Would become:


https://system.data.sqlite.org/downloads/1.0.93.0/sqlite-netFx45-setup-bundl
e-x86-2012-1.0.93.0.exe

--
Joe Mistachkin

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

Re: [sqlite] sqlite data source not available

[coffeenkfc]

Thanks Joe and Kevin,

I missed changing the folder version #. After doing so was able to download .93 
and it works!

From: Joe Mistachkin-3 [via SQLite] 
[mailto:ml-node+s1065341n7860...@n5.nabble.com]
Sent: October 14, 2014 10:37 AM
To: Ben Lam
Subject: Re: sqlite data source not available


coffeenkfc wrote:
>
> I removed the line, cleaned the solution, rebuilt, and still don't see
> 'System.Data.Sqlite Database File' in the 'Choose Data Source' window. :(
>

Which setup package did you install?  Were there any error messages?

Could you provide more details about the target system and the log files
from
the installation, etc?

The log files should be located in the %TEMP% directory on the target
machine.
They will have names similar to "Setup Log 2014-10-14 #001.txt" and
"Installer.exe.trace.tmp29.log".

Any other additional information you could provide would be useful as well.

>
> Is there a way to get older versions of the setup bundle? Some time ago on
a
> different machine I was able install 1.0.93.0. I wonder if this is
something
> related to 1.0.94.0.
>

Yes.  As Kevin Benson pointed out, you can simply replace the version
numbers
(*BOTH* of them) in the URI and get it from there.

For example:


https://system.data.sqlite.org/downloads/1.0.94.0/sqlite-netFx45-setup-bundl
e-x86-2012-1.0.94.0.exe

Would become:


https://system.data.sqlite.org/downloads/1.0.93.0/sqlite-netFx45-setup-bundl
e-x86-2012-1.0.93.0.exe

--
Joe Mistachkin

___
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/sqlite-data-source-not-available-tp78521p78605.html
To unsubscribe from sqlite data source not available, click 
here.
NAML


I am using the Free version of SPAMfighter.
SPAMfighter has removed 745 of my spam emails to date.

Do you have a slow PC? Try 
a free scan!




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite-data-source-not-available-tp78521p78606.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] 21th Tcl Conference - Registration & Hotel Reminder

[Andreas Kupries]

21'th Annual Tcl/Tk Conference (Tcl'2014)
http://www.tcl.tk/community/tcl2014/

This is a reminder that Registration for the Conference is open and
can be done at

http://www.tcl.tk/community/tcl2014/reg.html

Note that the holding period for hotel rooms has passed. To register
for a room, call 1-503-796-3851, speak to Mary Kirchner and mention
the Tcl Conference to receive the reduced rate.

See you in Portland,

Andreas Kupries
Tcl 2014 Program Chair
ActiveState Software Inc.
Vancouver, BC, Canada

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

Re: [sqlite] Make a database read-only?

[Ross Altman]

Thanks everyone for the helpful answers. Here's some context:

The database I have is for academic purposes. Research groups will need to
be able to download it in order to do large-scale scans using it as input,
so putting it in a wrapper (in PHP, say) isn't useful. But, I don't want
someone else to take it, add to it, and put it online somewhere else, so
that there are multiple versions floating around the web. I don't mind if
there are multiple COPIES, but I want to make sure that they're all the
same.

Thanks again,
Ross

On Tue, Oct 14, 2014 at 12:16 PM, Igor Tandetnik  wrote:

> On 10/14/2014 10:12 AM, John Hascall wrote:
>
>> Some code you may find useful to enforce the readonly byte
>>
>
> Of course, anyone smart enough to change the byte from read-only to
> read-write before making changes, would also be smart enough to set it back
> afterwards.
> --
> Igor Tandetnik
>
>
> ___
> 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] Make a database read-only?

[Igor Tandetnik]

On 10/14/2014 2:38 PM, Ross Altman wrote:

I don't mind if there are multiple COPIES, but I want to make sure that they're 
all the
same.


Well, you can't, really. If nothing else, whoever has read access to the 
database can read all the data out, then create a new database of their 
own, insert all that data into it (altered to taste), then publish it on 
their site (with the same file name as yours).


The best you can do, from technical standpoint, is publish it on your 
site, complete with an MD5 hash or similar, and encourage the community 
to always get it from the "official" source (or at least, check the hash 
against the known-good one).


From non-technical standpoint, there might be options for legal 
enforcement against unauthorized copies - but I'm not a lawyer and would 
not speculate on this topic. Consult your lawyer if you are interested 
in going down this path.

--
Igor Tandetnik

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

Re: [sqlite] Make a database read-only?

[Jungle Boogie]

Dear Ross,

From: Ross Altman 
Sent:  Tue, 14 Oct 2014 14:38:41 -0400
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Make a database read-only?
>

Thanks everyone for the helpful answers. Here's some context:

The database I have is for academic purposes. Research groups will need to
be able to download it in order to do large-scale scans using it as input,
so putting it in a wrapper (in PHP, say) isn't useful. But, I don't want
someone else to take it, add to it, and put it online somewhere else, so
that there are multiple versions floating around the web. I don't mind if
there are multiple COPIES, but I want to make sure that they're all the
same.



To a degree this sounds like the Streisand effect:
https://en.wikipedia.org/wiki/Streisand_effect


Pasting the sha256 is your best bet, but anyone who's going to verify the 
sha256 can already dump the database as explained by Clemens Ladisch.



Thanks again,
Ross




--
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance issue when copying data from one sqlite database to another

[Pontus Bergsten]

Hi,
I have the following problem setup:
We use sqlite3 in an embedded signal logger application. The "Main" in-memory 
signal signal database consists of some minor signal definition tables + two 
large tables (shards) with the actual signal data. The sharding technique is 
used in order to implement an efficient ringbuffer in sqlite.

Now, from time to time in the application, it is desired to extract some 
signals in some specified time window from the "Main" database, and save the 
selected signals to another smaller "Dest" database on USB memory. The "Dest" 
database will have the same signal definition tables as "Main", but only one 
signal data table. No ringbuffer functionality, and hence no sharding, is 
needed for the "Dest" database.
The actual copying is done by first creating the "Dest" database file with the 
required empty tables on USB, and then attach it to the "Main" in-memory 
database. Then the signal definitions and data is copied using a series of 
statements looking much like

INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE time 
BETWEEN t1 AND t2
And here is the performance problem: When the application is executed on 
Windows on a desktop computer, the copying works fine and the performance is 
fairly ok, even when saving to USB. However, when the same code is executed on 
the embedded system, the copying of data is extremely slow, even though the CPU 
load is very moderate. Profiling the thread that executes the sql-statements 
above, reveals that the thread is active in many very small steps, while 
waiting for the USB driver for very long time (compared to the active time), in 
between. During profiling the copy-thread only did useful work for about 5% of 
of the total time, the rest was waiting.

Is there any technique that can be used for tuning the performance of sqlite3 
in this scenario? For example, writing larger chunks of data to the "Dest" 
database?

Regards,
Pontus Bergsten

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

Re: [sqlite] Make a database read-only?

[Ross Altman]

Yeah, that's actually a really good point. Oh well, I guess I'll just have
to hope that people decide to use the database responsibly... haha

Best,
Ross

On Tue, Oct 14, 2014 at 2:57 PM, Jungle Boogie 
wrote:

> Dear Ross,
> 
> From: Ross Altman 
> Sent:  Tue, 14 Oct 2014 14:38:41 -0400
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] Make a database read-only?
> >
>
>> Thanks everyone for the helpful answers. Here's some context:
>>
>> The database I have is for academic purposes. Research groups will need to
>> be able to download it in order to do large-scale scans using it as input,
>> so putting it in a wrapper (in PHP, say) isn't useful. But, I don't want
>> someone else to take it, add to it, and put it online somewhere else, so
>> that there are multiple versions floating around the web. I don't mind if
>> there are multiple COPIES, but I want to make sure that they're all the
>> same.
>>
>>
> To a degree this sounds like the Streisand effect:
> https://en.wikipedia.org/wiki/Streisand_effect
>
>
> Pasting the sha256 is your best bet, but anyone who's going to verify the
> sha256 can already dump the database as explained by Clemens Ladisch.
>
>  Thanks again,
>> Ross
>>
>>
>
> --
> inum: 883510009027723
> sip: jungleboo...@sip2sip.info
> xmpp: jungle-boo...@jit.si
>
> ___
> 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] Performance issue when copying data from one sqlite database to another

[Ketil Froyn]

Depends on how safe/robust you want the copying to be, but if you can
simply rerun if something goes wrong, you might look into stuff like:

pragma journal_mode = MEMORY;
pragma synchronous = off;

But make sure you understand the consequences first by reading about these
commands:

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

Cheers, Ketil
On 14 Oct 2014 23:25, "Pontus Bergsten"  wrote:

> Hi,
> I have the following problem setup:
> We use sqlite3 in an embedded signal logger application. The "Main"
> in-memory signal signal database consists of some minor signal definition
> tables + two large tables (shards) with the actual signal data. The
> sharding technique is used in order to implement an efficient ringbuffer in
> sqlite.
>
> Now, from time to time in the application, it is desired to extract some
> signals in some specified time window from the "Main" database, and save
> the selected signals to another smaller "Dest" database on USB memory. The
> "Dest" database will have the same signal definition tables as "Main", but
> only one signal data table. No ringbuffer functionality, and hence no
> sharding, is needed for the "Dest" database.
> The actual copying is done by first creating the "Dest" database file with
> the required empty tables on USB, and then attach it to the "Main"
> in-memory database. Then the signal definitions and data is copied using a
> series of statements looking much like
>
> INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE
> time BETWEEN t1 AND t2
> And here is the performance problem: When the application is executed on
> Windows on a desktop computer, the copying works fine and the performance
> is fairly ok, even when saving to USB. However, when the same code is
> executed on the embedded system, the copying of data is extremely slow,
> even though the CPU load is very moderate. Profiling the thread that
> executes the sql-statements above, reveals that the thread is active in
> many very small steps, while waiting for the USB driver for very long time
> (compared to the active time), in between. During profiling the copy-thread
> only did useful work for about 5% of of the total time, the rest was
> waiting.
>
> Is there any technique that can be used for tuning the performance of
> sqlite3 in this scenario? For example, writing larger chunks of data to the
> "Dest" database?
>
> Regards,
> Pontus Bergsten
>
> ___
> 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] Performance issue when copying data from one sqlite database to another

[Mikael]

On the embedded unit write caching disabled?


2014-10-14 23:24 GMT+02:00 Pontus Bergsten :

> Hi,
> I have the following problem setup:
> We use sqlite3 in an embedded signal logger application. The "Main"
> in-memory signal signal database consists of some minor signal definition
> tables + two large tables (shards) with the actual signal data. The
> sharding technique is used in order to implement an efficient ringbuffer in
> sqlite.
>
> Now, from time to time in the application, it is desired to extract some
> signals in some specified time window from the "Main" database, and save
> the selected signals to another smaller "Dest" database on USB memory. The
> "Dest" database will have the same signal definition tables as "Main", but
> only one signal data table. No ringbuffer functionality, and hence no
> sharding, is needed for the "Dest" database.
> The actual copying is done by first creating the "Dest" database file with
> the required empty tables on USB, and then attach it to the "Main"
> in-memory database. Then the signal definitions and data is copied using a
> series of statements looking much like
>
> INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE
> time BETWEEN t1 AND t2
> And here is the performance problem: When the application is executed on
> Windows on a desktop computer, the copying works fine and the performance
> is fairly ok, even when saving to USB. However, when the same code is
> executed on the embedded system, the copying of data is extremely slow,
> even though the CPU load is very moderate. Profiling the thread that
> executes the sql-statements above, reveals that the thread is active in
> many very small steps, while waiting for the USB driver for very long time
> (compared to the active time), in between. During profiling the copy-thread
> only did useful work for about 5% of of the total time, the rest was
> waiting.
>
> Is there any technique that can be used for tuning the performance of
> sqlite3 in this scenario? For example, writing larger chunks of data to the
> "Dest" database?
>
> Regards,
> Pontus Bergsten
>
> ___
> 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] Performance issue when copying data from one sqlite database to another

[Simon Slavin]

On 14 Oct 2014, at 10:24pm, Pontus Bergsten  wrote:

> INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE time 
> BETWEEN t1 AND t2
> And here is the performance problem: When the application is executed on 
> Windows on a desktop computer, the copying works fine and the performance is 
> fairly ok, even when saving to USB. However, when the same code is executed 
> on the embedded system, the copying of data is extremely slow, even though 
> the CPU load is very moderate. Profiling the thread that executes the 
> sql-statements above, reveals that the thread is active in many very small 
> steps, while waiting for the USB driver for very long time (compared to the 
> active time), in between. During profiling the copy-thread only did useful 
> work for about 5% of of the total time, the rest was waiting.
> 
> Is there any technique that can be used for tuning the performance of sqlite3 
> in this scenario? For example, writing larger chunks of data to the "Dest" 
> database?

Your description makes perfect sense, bearing in mind that cheap USB drives are 
slow.  A relatively cheap piece of research might be to see if you can find a 
(more expensive) fast USB drive and see whether that makes your operation 
faster.  Other than that, two possibilities occur to me:

(A) Create your new database file on main storage, and create the Dest table 
there.  Once it is complete, close the database, then copy the database file to 
the USB drive using file copy commands rather than SQLite commands.  That 
should give you the fastest possible way of getting that data onto the drive.

(B) Write your Dest table to memory, then use the SQLite backup API to copy 
that to a file on the USB drive.  Copying the entire database page by page 
should be faster than copying the data row by row.



I do not know that either of these will definitely help you.  It depends too 
much on the relative speed of various components of your embedded system and on 
the width of your data bottleneck.  But they might be worth exploring.

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

Re: [sqlite] Performance issue when copying data from one sqlite database to another

[Richard Hipp]

On Tue, Oct 14, 2014 at 5:24 PM, Pontus Bergsten 
wrote:

>  When the application is executed on Windows on a desktop computer, the
> copying works fine and the performance is fairly ok, even when saving to
> USB. However, when the same code is executed on the embedded system, the
> copying of data is extremely slow, even though the CPU load is very
> moderate.
>


That sounds like a file-system problem to me.  What is your embedded OS?

-- 
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] Performance issue when copying data from one sqlite database to another

[Kees Nuyt]

On Tue, 14 Oct 2014 21:24:40 + (UTC), Pontus Bergsten
 wrote:

>Hi,
>I have the following problem setup:

[...]

> INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE time 
> BETWEEN t1 AND t2
>
> Is there any technique that can be used for tuning the performance of sqlite3 
> in this scenario?

I suspect this is a case of 
http://knuyt.demon.nl/sqlite.org/faq.html#q19


-- 
Regards,

Kees Nuyt


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

Re: [sqlite] Performance issue when copying data from one sqlite database to another

[Kees Nuyt]

On Wed, 15 Oct 2014 01:26:10 +0200, Kees Nuyt 
wrote:

> http://knuyt.demon.nl/sqlite.org/faq.html#q19

Oops, make that 
http://www.sqlite.org/faq.html#q19

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

[sqlite] Unable to prepare a statement

[Sam Carleton]

When I use the SQLite Manager, I am able to run this query just fine:

UPDATE EventNode
   SET IsActive = 1
 WHERE EventNodeId IN (SELECT w.EventNodeId
 FROM EventNode as w, EventNode as m on
m.objectId = 'a09f0f8a-a37c-44c2-846f-16a59b1c34c1'
 WHERE w.lft BETWEEN m.lft AND m.rgt )

But when I try to prepare the same statement to be used with my C++ code:

const char * updateString =
"UPDATE EventNode "
   "SET IsActive = @isActive "
 "WHERE EventNodeId IN (SELECT w.EventNodeId "
 "FROM EventNode AS w, EventNode AS m ON
m.objectId = @objectId "
"WHERE w.lft BETWEEN m.lft AND m.rgt)";

I get an error where sqlite3_errmsg() returns: no such table: EventNode

Now the code that is opening the DB is in a base class which is used other
places to access the EventNode table, so I am a bit mystified as to what
exactly is going on.  Any thoughts?


-- 
Pax vobiscum,
Sam Carleton
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPDATE Help

[Shantanu Namjoshi]

Hello,

I was writing for some help with a problem that has me stumped.

I have two tables in a DB. Table1 = dailydelete Table2 = dailyfactors.

dailyfactors is a list of dates and some corresponding information HML, SMB, 
UMD and Rf for each of these dates
dates are NOT repeated in this table

dailydelete is data for say 100 stocks for each of the dates in dailyfactors 
including price, return, etc.
dates ARE repeated in this table, for each stock

the table dailydelete has many more rows than the table dailyfactors.

I want to add columns to dailydelete. I want to pull information from table 
dailyfactors corresponding to the date in each row in table dailydelete (I use 
varchar as some data may be missing or input as a character)


The following is the code I use:
ALTER TABLE dailydelete ADD COLUMN SMB varchar(11);
ALTER TABLE dailydelete ADD COLUMN HML varchar(11);
ALTER TABLE dailydelete ADD COLUMN UMD varchar(11);
ALTER TABLE dailydelete ADD COLUMN Rf varchar(11);

UPDATE dailydelete
SET SMB = (SELECT dailyfactors.SMB FROM dailyfactors WHERE dailydelete.data = 
dailyfactors.Date),
SET HML = (SELECT dailyfactors.HML FROM dailyfactors WHERE dailydelete.data = 
dailyfactors.Date),
SET UMD = (SELECT dailyfactors.UMD FROM dailyfactors WHERE dailydelete.data = 
dailyfactors.Date),
SET Rf = (SELECT dailyfactors.Rf FROM dailyfactors WHERE dailydelete.data = 
dailyfactors.Date)

WHERE EXISTS (select * from dailyfactors WHERE dailydelete.data = 
dailyfactors.Date);


I get the following error:
Error: near "SET": syntax error

I have tried many different combinations of the above statement, but keep 
getting a syntax error somewhere or the other.

Can someone please help and point out what I am doing wrong?

Thank you for your help

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

Re: [sqlite] UPDATE Help

[Simon Slavin]

On 15 Oct 2014, at 1:10am, Shantanu Namjoshi 
 wrote:

> ALTER TABLE dailydelete ADD COLUMN SMB varchar(11);

If you find yourself doing things like this your schema is messed up.  SMB is 
obviously data, not a column.  Redesign your table so that HML, SMB, UMD and Rf 
are values in a column, not the names of columns.

By the way, SQLite doesn't have a varchar type.  Your data will be interpreted 
as TEXT, and will not be truncated to 11 characters.  It would be better just 
to declare the column as TEXT.

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

Re: [sqlite] UPDATE Help

[Richard Hipp]

On Tue, Oct 14, 2014 at 8:10 PM, Shantanu Namjoshi <
shantanu.namjo...@business.uconn.edu> wrote:

>
> UPDATE dailydelete
> SET SMB = (SELECT dailyfactors.SMB FROM dailyfactors WHERE
> dailydelete.data = dailyfactors.Date),
> SET Rf = (SELECT dailyfactors.Rf FROM dailyfactors WHERE dailydelete.data
> = dailyfactors.Date)
>
> WHERE EXISTS (select * from dailyfactors WHERE dailydelete.data =
> dailyfactors.Date);
>
>
> I get the following error:
> Error: near "SET": syntax error
>

On the syntax diagram (http://www.sqlite.org/lang_update.html) the SET
keyword only occurs once.


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