Re: [sqlite] UPDATE Help

2014-10-14 Thread 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

Re: [sqlite] UPDATE Help

2014-10-14 Thread 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,

[sqlite] UPDATE Help

2014-10-14 Thread 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

[sqlite] Unable to prepare a statement

2014-10-14 Thread 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'

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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:

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

2014-10-14 Thread 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, > >

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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,

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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,

Re: [sqlite] sqlite data source not available

2014-10-14 Thread 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

Re: [sqlite] sqlite data source not available

2014-10-14 Thread 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

Re: [sqlite] sqlite data source not available

2014-10-14 Thread 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

Re: [sqlite] sqlite data source not available

2014-10-14 Thread 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?

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

2014-10-14 Thread 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

Re: [sqlite] sqlite data source not available

2014-10-14 Thread 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

Re: [sqlite] Unicode support in SQLite

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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 >

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

Re: [sqlite] Unicode support in SQLite

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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,

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

[sqlite] Make a database read-only?

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

[sqlite] Unicode support in SQLite

2014-10-14 Thread 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:

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

2014-10-14 Thread 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 )

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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

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

2014-10-14 Thread 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 =

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

2014-10-14 Thread 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 *