Re: [sqlite] Select with dates

[Keith Medcalf]

> On Thu, 12 Sep 2013 14:01:04 +0100
> Simon Davies  wrote:
> 
> > Why not
> > SELECT * FROM "entry" WHERE
> >bankdate >= date('now','start of month')
> >   AND bankdate < date('now','start of month','+1 month')
> 
> The half-open interval strikes again!  :-)

And you are using UTC ...

Notwithstanding the timezone you want to use, 

explain select * 
  from entry 
 where bankdate >= date('now', 'start of month') 
   and bankdate < date('now', 'start of month', '-1 day');

will generate the following code:

SELECT {0:0}
FROM {0,*} = entry
WHERE AND(GE({0:0},FUNCTION:date(item[0] = 'now'
 item[1] = 'start of 
month')),LT({0:0},FUNCTION:date(item[0] = 'now'

 item[1] = 'start of month'

 item[2] = '-1 day')))
END
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 19000
2 OpenRead   0 2 0 1  00
3 Rewind 0 17000
4 Column 0 0 100
5 String80 3 0 now00
6 String80 4 0 start of month  00
7 Function   3 3 2 date(-1)   02
8 Lt 2 161 collseq(BINARY)  6a
9 String80 5 0 now00
10String80 6 0 start of month  00
11String80 7 0 -1 day 00
12Function   7 5 2 date(-1)   03
13Ge 2 161 collseq(BINARY)  6a
14Column 0 0 800
15ResultRow  8 1 000
16Next   0 4 001
17Close  0 0 000
18Halt   0 0 000
19Transaction0 0 000
20VerifyCookie   0 1 000
21TableLock  0 2 0 entry  00
22Goto   0 2 000
sqlite>

You will note that the two date functions are executed for each candidate row.  
But, if you have a suitable index, the date functions are only executed once:

create index entry_bankdate on entry (bankdate);

SELECT {0:0}
FROM {0,*} = entry
WHERE AND(GE({0:0},FUNCTION:date(item[0] = 'now'
 item[1] = 'start of 
month')),LT({0:0},FUNCTION:date(item[0] = 'now'

 item[1] = 'start of month'

 item[2] = '-1 day')))
END
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 21000
2 OpenRead   1 3 0 keyinfo(1,BINARY)  00
3 String80 2 0 now00
4 String80 3 0 start of month  00
5 Function   3 2 1 date(-1)   02
6 IsNull 1 19000
7 SeekGe 1 191 1  00
8 String80 4 0 now00
9 String80 5 0 start of month  00
10String80 6 0 -1 day 00
11Function   7 4 1 date(-1)   03
12IsNull 1 19000
13IdxGE  1 191 1  00
14Column 1 0 700
15IsNull 7 18000
16Column 1 0 800
17ResultRow  8 1 000
18Next   1 13000
19Close  1 0 000
20Halt   0 0 000
21Transaction0 0 000
22VerifyCookie   0 2 000
23TableLock  0 2 0 entry  00
24Goto   0 2 000
sqlite> 

** I cannot say if this is correct behavior or not.  I would say that it is 
correct for each row because date('now') retrieves the date "now", not "then", 
so perhaps it should be executed for each row ... though it may not be exactly 
what you expect since you are probably making an assumption about "now" -- is 
it the "now" at the 

Re: [sqlite] Insert statement

[Keith Medcalf]

Or, if you are binding the values you can always do something like (a single 
statement):

insert or ignore into table (val) values (:val); 
select id from table where val = :val;

Where you bind you long val string value to the parameter named "val".  This 
has the advantage that you only bind (pass in) the long string once, and you 
will always get back the id to use whether the value had to be inserted or not.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of James K. Lowden
> Sent: Friday, 13 September, 2013 20:20
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Insert statement
> 
> On Thu, 12 Sep 2013 18:15:29 +
> "Joseph L. Casale"  wrote:
> 
> > > If you make val unique -- and I see no reason not to -- then you
> > > can select the id for every val you insert with "where val =
> >
> > I omitted the fact that val in table_a is unique.
> 
> Ah, that will be very helpful.
> 
> > Sending one large statement in this case would bypass the overhead,
> > but using val as the reference would make the string very long. That
> > text data might be several thousand chars long.
> 
> So, the integer is a proxy for a giant unique string.  OK, I might
> have done the same thing.
> 
> In principle, because the text is unique, you can find the id with
> 
>   select id where val = 'giant string'
> 
> and that might be fine.  If it's not fine --if it's too slow or
> unwieldy -- you might consider computing, say, an MD5 has of the giant
> string and adding that as a unique column instead of the integer
> primary key.
> 
> --jkl
> ___
> 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] Select with dates

[James K. Lowden]

On Thu, 12 Sep 2013 14:01:04 +0100
Simon Davies  wrote:

> Why not
> SELECT * FROM "entry" WHERE
>bankdate >= date('now','start of month')
>   AND bankdate < date('now','start of month','+1 month')

The half-open interval strikes again!  :-)

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

Re: [sqlite] Insert statement

[James K. Lowden]

On Thu, 12 Sep 2013 18:15:29 +
"Joseph L. Casale"  wrote:

> > If you make val unique -- and I see no reason not to -- then you
> > can select the id for every val you insert with "where val =
>
> I omitted the fact that val in table_a is unique. 

Ah, that will be very helpful.  

> Sending one large statement in this case would bypass the overhead,
> but using val as the reference would make the string very long. That
> text data might be several thousand chars long. 

So, the integer is a proxy for a giant unique string.  OK, I might
have done the same thing.  

In principle, because the text is unique, you can find the id with 

select id where val = 'giant string'

and that might be fine.  If it's not fine --if it's too slow or
unwieldy -- you might consider computing, say, an MD5 has of the giant
string and adding that as a unique column instead of the integer
primary key.  

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

Re: [sqlite] logical to use sqlite to replace Berkeley DB ?

[Howard Chu]

Patrick wrote:

Hi Everyone

After Oracle acquired Berkeley DB they changed the license. If people
don't pay a significant licensing fee, it can now only be used for GPL code.

I don't know Berkeley DB very well but I do know a moderate amount of
Sqlite.

I want to tinker with a compiler that uses DB, I was thinking about
ripping it out and replacing it with Sqlite. Does this make sense?

I know they are both zero configuration embedded DBs but DB is a
key-value based one and I am assuming lighter, is this true? Any idea of
how close they would be in terms of memory use and execution speed?


BDB is much faster than SQLite, yes. In fact Oracle supplies a port of SQLite 
that uses BDB as the underlying Btree engine instead of SQLite's native code, 
and there's a significant performance gain.


If you have an app that is comfortably using the key-value API of BDB it would 
introduce major inefficiencies to convert it to using SQL. So no, this doesn't 
seem like a logical action to take.


If you're using BDB and want to switch off it because of the license issue, 
try OpenLDAP LMDB instead. No license hassles, and also several times smaller 
and faster than BDB.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] logical to use sqlite to replace Berkeley DB ?

[Patrick]

Hi Everyone

After Oracle acquired Berkeley DB they changed the license. If people 
don't pay a significant licensing fee, it can now only be used for GPL code.


I don't know Berkeley DB very well but I do know a moderate amount of 
Sqlite.


I want to tinker with a compiler that uses DB, I was thinking about 
ripping it out and replacing it with Sqlite. Does this make sense?


I know they are both zero configuration embedded DBs but DB is a 
key-value based one and I am assuming lighter, is this true? Any idea of 
how close they would be in terms of memory use and execution speed?


Thanks for reading-Patrick


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

Re: [sqlite] WAL Checkpointing question

[Simon Slavin]

On 13 Sep 2013, at 6:00pm, Andrew Beal  wrote:

> When you issue a checkpoint command to the SQLite system and it fails on a 
> hardware I/O error when interacting with the database, is there a way to use 
> the WAL to rebuild the database or is the database corrupt beyond repair at 
> that point?

What's causing the hardware I/O error ?  For the sake of the question, are we 
pretending that there error was triggered accidentally and will never happen 
again ?

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

Re: [sqlite] WAL Checkpointing question

[Richard Hipp]

On Fri, Sep 13, 2013 at 1:00 PM, Andrew Beal  wrote:

> All,
>
> When you issue a checkpoint command to the SQLite system and it fails on a
> hardware I/O error when interacting with the database, is there a way to
> use the WAL to rebuild the database or is the database corrupt beyond
> repair at that point?
>

That's actually a test scenario for SQLite.  The database should be intact,
as long as the I/O error didn't erase or change any of the disk content.
The next process to open the database file will complete the recovery.

If you are in a situation where some of either the database file or the WAL
file is unreadable due to hardware problems, then that could lead to
corruption.  But as long as the files are readable, recovery should always
be possible.


-- 
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] WAL Checkpointing question

[Andrew Beal]

All,

When you issue a checkpoint command to the SQLite system and it fails on a 
hardware I/O error when interacting with the database, is there a way to use 
the WAL to rebuild the database or is the database corrupt beyond repair at 
that point?

F. Andrew Beal

Woods Hole Oceanographic Institution
266 Woods Hole Road MS#18
Woods Hole, MA 02543

Office: 508-289-2970
Email: ab...@whoi.edu



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

Re: [sqlite] Dynamically generate SQL statements for SQLite

[Simon Slavin]

On 13 Sep 2013, at 3:26am, Mun Wai Chan  
wrote:

> I was wondering if there are any software that would dynamically generate SQL 
> statements for SQLite using C#. For example, I would like to be able to do 
> this:
> 
> var sqlStatement = sqlGenerator.Select("Name").From("Customers").ToSQL();
> 
> I only know the names of tables and fields at runtime depending on what the 
> users have selected and the database at the backend is not fixed. I have 
> found some products that can generate SQL statements dynamically but not 
> specifically for SQLite as there are some SQLite specific features that might 
> not be supported.

At their heart, all SQL statements are simply strings.  For example, the above 
use of a strange API looks to me like you should be passing something like

SELECT Name FROM Customers

to the sqlite3_exec() function.  If you have access to the real API and not 
something put around it you can use any technique you like which assemble 
strings.

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

[sqlite] Dynamically generate SQL statements for SQLite

[Mun Wai Chan]

Hi all,

I was wondering if there are any software that would dynamically generate SQL 
statements for SQLite using C#. For example, I would like to be able to do this:

var sqlStatement = sqlGenerator.Select("Name").From("Customers").ToSQL();

I only know the names of tables and fields at runtime depending on what the 
users have selected and the database at the backend is not fixed. I have found 
some products that can generate SQL statements dynamically but not specifically 
for SQLite as there are some SQLite specific features that might not be 
supported.

Regards,

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