Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

Op 6 feb 2014, om 16:46 heeft Simon Slavin het volgende geschreven: On 6 Feb 2014, at 7:15am, big stone wrote: If we wish to have SQLite + Python combination to become "reference choice" in education, I would think that priority list should be : Just to remind you

[sqlite] FW: Need Help with Golf Handicap Calculation

From: Ed Tenholder Sent: Saturday, February 08, 2014 1:44 PM To: 'sqlite-users@sqlite.org' Subject: Need Help with Golf Handicap Calculation I’m just trying to learn SQL, and after lots of google searches and reading posts on this email list, I’ve gotten pretty close. Table: CREATE

Re: [sqlite] Free Page Data usage

On 2014/02/08 19:30, Raheel Gupta wrote: @Simon, Sir I dont want to rearrange the data. I will try to explain more. All my rows have the exact same size. They will not differ in size. My problem is due to the fact that I use 64kB page size. My rows are exactly 8 Bytes + 4096 Bytes. Now for

Re: [sqlite] Avoiding holding a lock for too long

On 08 Feb 2014 at 17:48, Simon Slavin wrote: > On 8 Feb 2014, at 4:58pm, Tim Streater wrote: >> I had a look at the PHP sqlite3 interface code, and it looks like ->query >> does prepare, step, and reset (if there are no errors). > > If ->query() is

Re: [sqlite] Send Mail from sqlite

From: "Keith Medcalf" Date: 7 Feb 2014 18:03:12 GMT+01:00 To: "General Discussion of SQLite Database" Reply-To: General Discussion of SQLite Database Have your application that is performing the update send an email when

Re: [sqlite] Free Page Data usage

On Fri, Feb 7, 2014 at 7:39 AM, Raheel Gupta wrote: > Hi, > > My Page size is 64KB and I store around 4KB of row data in one row. > I store around 1 rows in one table and the database size reaches 42MB. > > Now, I am facing a peculiar problem. When I delete just 2-3

Re: [sqlite] Free Page Data usage

On Sat, Feb 8, 2014 at 11:51 AM, Simon Slavin wrote: > > While a database is in use it might use perhaps 100 pages for a particular > table. Almost every one of those pages will have a little space free: > anything from 1 byte to most of the page, depending on how much

Re: [sqlite] Free Page Data usage

On 8 Feb 2014, at 5:30pm, Raheel Gupta wrote: > I will try to explain more. > All my rows have the exact same size. They will not differ in size. > My problem is due to the fact that I use 64kB page size. > My rows are exactly 8 Bytes + 4096 Bytes. Your very specific use

Re: [sqlite] Avoiding holding a lock for too long

On 8 Feb 2014, at 4:58pm, Tim Streater wrote: > On 07 Feb 2014 at 23:04, Simon Slavin wrote: > >> You should not be manually setting any handle to null. Try calling >> ->finalize() on the statement and eventually ->close() on the database, which

Re: [sqlite] Free Page Data usage

@Simon, Sir I dont want to rearrange the data. I will try to explain more. All my rows have the exact same size. They will not differ in size. My problem is due to the fact that I use 64kB page size. My rows are exactly 8 Bytes + 4096 Bytes. Now for the purpose of ease in calculation lets assume

Re: [sqlite] Avoiding holding a lock for too long

On 07 Feb 2014 at 23:04, Simon Slavin wrote: > You should not be manually setting any handle to null. Try calling > ->finalize() on the statement and eventually ->close() on the database, which > do more than just setting their value. If you are setting anything to null

Re: [sqlite] Free Page Data usage

On 8 Feb 2014, at 11:24am, Raheel Gupta wrote: > I dont want to repack the DB sir. > When a page becomes free I want to make sure that page is used up first and > then new pages are created. Just to explain that this would be extremely inefficient because a new row that

Re: [sqlite] struggling with a query

Just to be clear, it isn't really "mine", just an adaption of the many excellent contributions, from which I too have learned. A huge pleasure and fun exercise no less! On 2014/02/08 14:35, Stephan Beal wrote: On Sat, Feb 8, 2014 at 12:39 PM, RSmith wrote: SELECT name,

Re: [sqlite] Free Page Data usage

Instead of delete and then insert, can you somehow just keep track of which rows are to be deleted, and when new rows come in replace if you can and otherwise insert? A little more bookkeeping, but it might save the space you need. Gerry On Feb 7, 2014 10:57 PM, "Raheel Gupta"

Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

On Fri, Feb 7, 2014 at 1:01 PM, varro wrote: > Regarding the following old post: > > Richard Hipp wrote: > > By making use of memory-mapped I/O, the current trunk of SQLite (which > will > > eventually become version 3.7.17 after much more refinement and testing) > > can

[sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

Regarding the following old post: Richard Hipp wrote: > By making use of memory-mapped I/O, the current trunk of SQLite (which will > eventually become version 3.7.17 after much more refinement and testing) > can be as much as twice as fast, on some platforms and under some > workloads. We would

[sqlite] Compiler warning (treated as error) when using MSVC 2013 to build SQLite 3.8.3

Hello, It seems that VS 2013 has grown stricter by default in treating potentially security-related warnings as errors. Some context here: http://blogs.msdn.com/b/sdl/archive/2012/06/06/warnings-sdl-and-improving-uninitialized-variable-detection.aspx When building the Release configuration

Re: [sqlite] struggling with a query

On Sat, Feb 8, 2014 at 12:39 PM, RSmith wrote: > SELECT name, max(vid=2)-max(vid=1) FROM v GROUP BY name; > > So much more succint than my original, like I predicted :) Indeed!!! This one wins if i am able to refactor it for use with the much more complex structure i'm

Re: [sqlite] struggling with a query

Yeah I quite like some of the solutions posted - got to love this list :) One final optimization, since those values you are looking for essentially maps to Boolean (0 and 1), this query is the smallest and probably fastest (I think) that will produce the correct results from your table:

Re: [sqlite] Free Page Data usage

>> No matter what size you make the pages, a delete function is never going to re-pack the db I dont want to repack the DB sir. When a page becomes free I want to make sure that page is used up first and then new pages are created. VACUUM is not what I want to do. I think that free pages are

Re: [sqlite] struggling with a query

On Sat, Feb 8, 2014 at 11:58 AM, big stone wrote: > with sqlite 3.8.3 (for the with) : > > with v(vid,name) as (values (1,'foo'),(1,'bar'),(2,'bar'),(2,'baz')) > > select name, > -max(case when vid=1 then 1 else 0 end ) + max(case when vid=2 > then 1 else 0 end) >

Re: [sqlite] struggling with a query

Am 08.02.2014 11:03, schrieb Stephan Beal: i have table containing a mapping of logic dataset versions and filenames contained in that dataset version: CREATE TABLE v(vid,name); INSERT INTO "v" VALUES(1,'foo'); INSERT INTO "v" VALUES(1,'bar'); INSERT INTO "v" VALUES(2,'bar'); INSERT INTO "v"

Re: [sqlite] struggling with a query

On Sat, Feb 8, 2014 at 11:57 AM, RSmith wrote: > One way of doing it: > Many thanks to you and Kevin both! These examples give me plenty to study for today :). -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But

Re: [sqlite] struggling with a query

On 08-02-2014 11:58, big stone wrote: with sqlite 3.8.3 (for the with) : with v(vid,name) as (values (1,'foo'),(1,'bar'),(2,'bar'),(2,'baz')) select name, -max(case when vid=1 then 1 else 0 end ) + max(case when vid=2 then 1 else 0 end) from v group by name almost the same as

Re: [sqlite] struggling with a query

with sqlite 3.8.3 (for the with) : with v(vid,name) as (values (1,'foo'),(1,'bar'),(2,'bar'),(2,'baz')) select name, -max(case when vid=1 then 1 else 0 end ) + max(case when vid=2 then 1 else 0 end) from v group by name ___ sqlite-users

Re: [sqlite] struggling with a query

One way of doing it: SELECT IFNULL(V1.name,V2.name) AS VName, CASE WHEN V1.name=V2.name THEN 0 ELSE -1 END AS VInd FROM v AS V1 LEFT JOIN v AS V2 ON (V1.vid<>V2.vid) AND (V1.name=V2.name) WHERE V1.vid=1 UNION SELECT IFNULL(V1.name,V2.name) AS VName, CASE WHEN V1.name=V2.name THEN 0 ELSE 1

Re: [sqlite] struggling with a query

On 8 Feb 2014, at 10:03, Stephan Beal wrote: > i am trying like mad to, but can't seem formulate a query with 2 version > number inputs (1 and 2 in this case) and creates a result set with these > columns: > > - name. must include all names across both versions > -

Re: [sqlite] Free Page Data usage

Hi Raheel, It does make sense what you would like to do, but your concern does not make sense. You say you are "trying to optimize the utilization of the free space available" but give no indication why, it certainly does not seem that space is a problem. I do understand the urge to optimize

[sqlite] struggling with a query

Hi, list, most of the time i judge my SQL skills as mediocre, but at times like this i feel like a complete noob... i have table containing a mapping of logic dataset versions and filenames contained in that dataset version: CREATE TABLE v(vid,name); INSERT INTO "v" VALUES(1,'foo'); INSERT INTO

Re: [sqlite] help needed for major SQLite problem

On Sat, 08 Feb 2014 12:06:01 +0700, Dan Kennedy wrote: >On 02/08/2014 03:00 AM, C M wrote: >> This is a follow-up to a question I asked on this list on Sep 1st, 2013, >> about an error that I was randomly getting with disk-based SQLite database >> in a Python desktop