[sqlite] Efficient relational SELECT

On Thu, 4 Feb 2016 20:40:56 + Simon Slavin wrote: > > On 4 Feb 2016, at 7:16pm, Luuk wrote: > > > Will this simple solution be too slow?: > > > > SELECT id, room, date, time > > FROM rooms > > LEFT JOIN bookngs ON rooms.id=bookings.room > > GROUP BY rooms.id, bookings.date > > HAVING

[sqlite] Efficient relational SELECT

On 4 Feb 2016, at 7:16pm, Luuk wrote: > Will this simple solution be too slow?: > > SELECT id, room, date, time > FROM rooms > LEFT JOIN bookngs ON rooms.id=bookings.room > GROUP BY rooms.id, bookings.date > HAVING bookings.date=MAX(bookings.date) OR bookings.date IS NULL That's the solution

[sqlite] json_group_array

I can't argue for the correctness of including nulls in aggregate functions or not. It truly is an arbitrary decision meant for standards-makers. Yet, most aggregate function do not include nulls. Interestingly, some SQL's do include them in count() but sqlite does not. In my example table,

[sqlite] Efficient relational SELECT

On 04-02-16 19:32, Simon Slavin wrote: > Simplified explanation. Here's the setup: > > Two tables: > > rooms: each room has an id (think the standard SQLite rowid/id) and some > other columns > bookings: includes room id, date, time, and some other stuff > > Date/time is encoded as a long

[sqlite] json_group_array

On 2/4/16, TJ O'Donnell wrote: > I was expecting the json_group_array aggregate function to leave out null, > behaving like other aggregate functions. Is this to be expected? > > sqlite> create table x (a integer); > sqlite> insert into x values (1), (2), (null),(4); > sqlite> select

[sqlite] Efficient relational SELECT

Simplified explanation. Here's the setup: Two tables: rooms: each room has an id (think the standard SQLite rowid/id) and some other columns bookings: includes room id, date, time, and some other stuff Date/time is encoded as a long COLLATEable string. In other words sorting a column by my

[sqlite] Bug: Using custom function seems to not work on Universal App Platformv. 3.10.2.0 x86 build only

Hi, I am getting a weird behaviour when using Universal App Platform library v. 3.10.2.0. The problem occurs when application is build for x86 only ? build for arm and x64 works good. Here is the case: I have a wrapper from pure SqLite dll to c# in my code (using DllImport attributes in c#).

[sqlite] Segment fault when running a query

On 3 Feb 2016, at 4:21pm, Fredrik Gustafsson wrote: > 223 rc = sqlite3_exec(db, "SELECT id, date, text FROM events WHERE > account_id=1 ORDER BY date DESC LIMIT 10" , NULL, NULL, ); To help debug this, insert two lines before this one. One checks to see that $zErrMsg is a real

[sqlite] Efficient relational SELECT

On 2/4/16, Simon Slavin wrote: > > rooms: each room has an id (think the standard SQLite rowid/id) and some > other columns > bookings: includes room id, date, time, and some other stuff > > I want to show an HTML table which lists some rooms (using WHERE and ORDER > BY) and the latest time each

[sqlite] Efficient relational SELECT

Might want to split bookings into a transaction table and a reservation table. The reservation table would have one column for each room and one row for each calendar day (assuming this is a respectable joint with no hourly reservations!). Reservation table has primary key of date and room

[sqlite] json_group_array

I was expecting the json_group_array aggregate function to leave out null, behaving like other aggregate functions. Is this to be expected? sqlite> create table x (a integer); sqlite> insert into x values (1), (2), (null),(4); sqlite> select group_concat(a), json_group_array(a) from x;

[sqlite] json1 not escaping CRLF characters

Thanks! I can make do with replace() for now (as in my particular case, I only have CRLF & TAB to replace) On Thu, Feb 4, 2016 at 11:39 AM, Richard Hipp wrote: > On 2/4/16, Richard Hipp wrote: > > On 2/4/16, Eric Grange wrote: > >> Hello, > >> > >> I have been experimenting with using the

[sqlite] Efficient relational SELECT

On Thu, Feb 4, 2016 at 12:32 PM, Simon Slavin wrote: > Simplified explanation. Here's the setup: > > Two tables: > > rooms: each room has an id (think the standard SQLite rowid/id) and some > other columns > bookings: includes room id, date, time, and some other stuff > > Date/time is encoded

[sqlite] IS a SQLite db of small size as good as reliable cache?

I don't really care about all the MS hate, but will add mine[1]. I am not a participant of that question, but it shows the same problem. I had over 50% CPU usage for hours when the computer was "idle" because Windows was indexing my stuff, forever. A friend of mine had the same problem and after

[sqlite] json1 not escaping CRLF characters

On Thu, Feb 4, 2016 at 11:04 AM, Dominique Devienne wrote: > On Thu, Feb 4, 2016 at 10:43 AM, Eric Grange wrote: > >> I have been experimenting with using the json1 extension to generate json >> sql-side rather than code-side, but I am hitting an issue with the CR & LF >> characters which are

[sqlite] json1 not escaping CRLF characters

On Thu, Feb 4, 2016 at 11:04 AM, Dominique Devienne wrote: > \" represents the quotation mark character (U+0022). > \\ represents the reverse solidus character (U+005C). > \/ represents the solidus character (U+002F). > ... > The wording above doesn't seem to "require" these characters to be

[sqlite] json1 not escaping CRLF characters

On Thu, Feb 4, 2016 at 10:43 AM, Eric Grange wrote: > I have been experimenting with using the json1 extension to generate json > sql-side rather than code-side, but I am hitting an issue with the CR & LF > characters which are not escaped, which results in json that fails > JSON.parse or use as

[sqlite] a sqlite database error

Hello, and welcome, Frank, You just forget to tell what the error was :-P . By the way, what returned the error? Was this SQLite3 itself or a wrapper around it? On Wed, 3 Feb 2016 14:22:40 +0800 "" <1072579296 at qq.com> wrote: > Hello, my name is Frank. Two days ago, I operated sqlite

[sqlite] a sqlite database error

On Wed, 3 Feb 2016 14:22:40 +0800, Frank wrote: > Hello, my name is Frank. Two days ago, I operated sqlite > database, and got an error, I don't known what the error > means, so I hope if you see the email, please respond me. > Thank you very much! To get an answer, your question has to be more

[sqlite] json1 not escaping CRLF characters

Hello, I have been experimenting with using the json1 extension to generate json sql-side rather than code-side, but I am hitting an issue with the CR & LF characters which are not escaped, which results in json that fails JSON.parse or use as JSONP in browsers. Ideally CR / LF should be encoded

[sqlite] IS a SQLite db of small size as good as reliable cache?

On Wed, Feb 3, 2016 at 3:53 PM, Howard Chu wrote: > No. Windows will toss clean pages out even in the total absence of memory > pressure. It is moronic, but true. You can repeat the steps I outlined for > yourself and see. > >

[sqlite] IS a SQLite db of small size as good as reliable cache?

You conclusion about the Windows design goals are correct. Hardware Destroyer (power saving) was invented for the same reason (to maximize the rate of hardware failure through imposition of unnecessary thermal and mechanical stresses on all system components, causing early failure and

[sqlite] json1 not escaping CRLF characters

On 2/4/16, Richard Hipp wrote: > On 2/4/16, Eric Grange wrote: >> Hello, >> >> I have been experimenting with using the json1 extension to generate json >> sql-side rather than code-side, but I am hitting an issue with the CR & >> LF >> characters which are not escaped, which results in json

[sqlite] json1 not escaping CRLF characters

On 2/4/16, Eric Grange wrote: > Hello, > > I have been experimenting with using the json1 extension to generate json > sql-side rather than code-side, but I am hitting an issue with the CR & LF > characters which are not escaped, which results in json that fails > JSON.parse or use as JSONP in

[sqlite] Segment fault when running a query

On 2/3/16, Fredrik Gustafsson wrote: > Hi, > I've a reproduceable error in my code, running a simple SQL question > gives me a segment fault. Running the program i gdb and doing backtrace > gives me this: > > (gdb) backtrace > #0 malloc_consolidate (av=av at entry=0x776be620 ) at >

[sqlite] Fw: new important message

Hello! New message, please read james.ander at tiscali.co.uk