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

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 *

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

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 =

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

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

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

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

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

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 )

[sqlite] Unicode support in SQLite

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:

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

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

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

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

[sqlite] Make a database read-only?

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

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

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

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

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

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

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,

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

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

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

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

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

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

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

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

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

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

Re: [sqlite] Unicode support in SQLite

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

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

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

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

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

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

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 >

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

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

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

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

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

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

Re: [sqlite] Unicode support in SQLite

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

Re: [sqlite] sqlite data source not available

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

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

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

Re: [sqlite] sqlite data source not available

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?

Re: [sqlite] sqlite data source not available

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

Re: [sqlite] 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

Re: [sqlite] sqlite data source not available

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

[sqlite] 21th Tcl Conference - Registration & Hotel Reminder

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,

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

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

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,

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

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

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

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

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

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

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

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:

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

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

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

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

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

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

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

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

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

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

[sqlite] Unable to prepare a statement

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'

[sqlite] UPDATE Help

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

Re: [sqlite] UPDATE Help

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,

Re: [sqlite] UPDATE Help

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