Re: [sqlite] julianday and unixepoch

[Petite Abeille]

On Sep 17, 2011, at 10:53 PM, Kevin Benson wrote:

>> Your point is taken and most likely the documentation intended merely
> to identify the result in terms of Julian days:
> i.e.
> The julianday() function returns the number of Julian
> days since noon in Greenwich on November 24, 4714 B.C. (Proleptic
> Gregorian calendar

All in all, I think the documentation is a bit misleading. It should read 
something along these lines instead:

"The julianday() function returns the Julian date, the interval of time in days 
and fractions of a day since January 1, 4713 BC Greenwich noon, Julian 
proleptic calendar." 

Furthermore, all references to Julian Day Number should be removed and/or 
replace by Julian Date.

P.S.

As far as I know, the Julian date starts on January 1, 4713 BC Greenwich noon, 
not November 24, 4714 B.C.

The Julian date for BCE  4713 January  1 12:00:00.0 UT is
JD  0.00




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

Re: [sqlite] julianday and unixepoch

[Kevin Benson]

On Sat, Sep 17, 2011 at 4:43 PM, Petite Abeille wrote:

>
> On Sep 17, 2011, at 10:42 PM, Kevin Benson wrote:
>
> > The julianday() function returns the Julian
> > day- the number of days...
> >
> >  since noon  in Greenwich on November 24, 4714
> > B.C. (Proleptic
> > Gregorian calendar<
> http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar>).
>
> The SQLite documentation explicitly refers to Julian Day Number (JDN), as
> opposed to Julian Date (JD):
>
> %J  Julian day number
>
> http://www.sqlite.org/lang_datefunc.html
>
> Not to split hairs, but there is a practical differences between a Julian
> Day Number and a Julian Date:
>
> "The Julian date (JD) is the interval of time in days and fractions of a
> day since January 1, 4713 BC Greenwich noon, Julian proleptic calendar."
>
> "The Julian day number (JDN) is the integer part of the Julian date (JD)."
>
> http://asa.usno.navy.mil/SecM/Glossary.html#j
>
>  Your point is taken and most likely the documentation intended merely
to identify the result in terms of Julian days:
i.e.
The julianday() function returns the number of Julian
days since noon in Greenwich on November 24, 4714 B.C. (Proleptic
Gregorian calendar
).
--
   --
  --
 --ô¿ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] julianday and unixepoch

[Petite Abeille]

On Sep 17, 2011, at 10:42 PM, Kevin Benson wrote:

> The julianday() function returns the Julian
> day- the number of days...
> 
>  since noon  in Greenwich on November 24, 4714
> B.C. (Proleptic
> Gregorian 
> calendar).

The SQLite documentation explicitly refers to Julian Day Number (JDN), as 
opposed to Julian Date (JD):

%J  Julian day number

http://www.sqlite.org/lang_datefunc.html

Not to split hairs, but there is a practical differences between a Julian Day 
Number and a Julian Date:

"The Julian date (JD) is the interval of time in days and fractions of a day 
since January 1, 4713 BC Greenwich noon, Julian proleptic calendar."

"The Julian day number (JDN) is the integer part of the Julian date (JD)."

http://asa.usno.navy.mil/SecM/Glossary.html#j

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

Re: [sqlite] julianday and unixepoch

[Kevin Benson]

On Sat, Sep 17, 2011 at 3:52 PM, Petite Abeille wrote:

>
> > So how come does julianday take the time part of date into consideration
> even when it's not provided?
> >
>
> In SQLite:
>
> > select julianday( '2002-08-28' );
> 2452514.5
>
*SNIP*


> I was expecting a julian day number. SQLite's julianday function returned a
> julian date (including a time portion).
>
>

http://www.sqlite.org/lang_datefunc.html


The julianday() function returns the Julian
day- the number of days...

 since noon  in Greenwich on November 24, 4714
B.C. (Proleptic
Gregorian calendar).



--
   --
  --
 --ô¿ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT syntax missing key SQL syntax

[Simon Slavin]

On 17 Sep 2011, at 7:28pm, Petite Abeille wrote:

> (5) contextually typed row value expression list
> 
> http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list
> 
>  ::= 
> 
> [ {   }... ]
> 
> (6) contextually typed row value constructor
> 
> 
> | [ ROW ]   element list> 
> 
> http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20constructor

Hmm.  Okay I didn't interpret that that way.

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

Re: [sqlite] julianday and unixepoch

[Petite Abeille]

On Sep 17, 2011, at 9:29 PM, Igor Tandetnik wrote:

>> So how come does julianday take the time part of date into consideration 
>> even when it's not provided?
> 
> It doesn't.

Hmmm...

http://en.wikipedia.org/wiki/Julian_day#Converting_Gregorian_calendar_date_to_Julian_Day_Number

local math = require( 'math' )
local floor = math.floor
local year = 2002
local month = 8
local day = 28
local a = floor( ( 14 - month ) / 12 )
local y = year + 4800 - a
local m = month + 12 * a - 3
local jdn = day + floor( ( 153 * m + 2 ) / 5 ) + 365 * y + floor( y / 4 ) - 
floor( y / 100 ) + floor( y/ 400 ) - 32045

print( jdn )

> 2452515

In SQLite:

> select julianday( '2002-08-28' );
2452514.5

> 
>> JD 2452514.50 is
>> CE 2002 August 28 00:00:00.0 UT  Wednesday
>> 
>> JD 2452514.00 is
>> CE 2002 August 27 12:00:00.0 UT  Tuesday
> 
> Looks OK to me. What again seems to be the problem?

I was expecting a julian day number. SQLite's julianday function returned a 
julian date (including a time portion).

>> Or did I miss something?
> 
> Apparently.

Perhaps :)

In any case, will stick with cast and round and call it a (julian) day:

> select cast( round( julianday( date( 1030561675, 'unixepoch', 'utc' ) ) ) as 
> integer );
2452515



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

Re: [sqlite] Performance problem LEFT OUTERJOINandstringdatafromright table

[Mira Suk]

The difference is that #2 mentions only one field from ItemsME, namely IDR. The 
value of that field comes from the index, the table itself doesn't need to be 
read at all. It's not even clear why #2 bothers to join with ItemsME at all - 
it's a no-op.
#1 uses more fields from ItemsME, so it needs to actually look up and read 
records from that table.


However fact is that IDR is non-existant - for that specific query (which 
SQLite should see from index) so it should not touch table - just fill with 
NULLs.
The more important thing is - even if I add [ItemsME].[insert whatever integer 
field here] into result fields it's same speed as in case 2) - that is 
relatively fast. Just touching any text/blob fields in query make it run ~12x 
slower. And considering resultset containing few rows even if all texts were 
split over 2 pages (none is longer than 300 chars) this would equal to records 
* 2kB IO. in case of 50 records that's 100kB.
Do you really think SQLite needs 1.8 seconds for 100kB in reads on SSD drive ? 
I certainly hope not. Maybe there is reason RIGHT JOIN isn't implemented ?
 
BTW even the 2nd case is considerably slower than single table selects. the 
TZB_MATCHRECURSIVE is doing way more difficult job (selecting all childs and 
filtering them) but only in single table and is still faster (around 40ms per 
top level parent folder which usually contain ~700 folders/items).
 
Just out of curiosity is it possible to download some older versions of SQLite 
to test if this behaviour is present in it ? I can only find current version on 
download page. nOOb here.
 


___
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] julianday and unixepoch

[Igor Tandetnik]

Petite Abeille  wrote:
> Say, I would like to convert that date to a julian day:
> 
>> select julianday( date( 1030561675, 'unixepoch', 'utc'  ) );
> 2452514.5
> 
> Oooops... where does that .5 comes from?

In Julian calendar, 0 represents noon, and .5 represents midnight.

> I was more expecting the following:
>> select cast( julianday( date( 1030561675, 'unixepoch', 'utc' ) ) as integer 
>> );
> 2452514

What was that expectation based on?

> So how come does julianday take the time part of date into consideration even 
> when it's not provided?

It doesn't.

> JD 2452514.50 is
> CE 2002 August 28 00:00:00.0 UT  Wednesday
>
> JD 2452514.00 is
> CE 2002 August 27 12:00:00.0 UT  Tuesday

Looks OK to me. What again seems to be the problem?

> Or did I miss something?

Apparently.
-- 
Igor Tandetnik

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

[sqlite] julianday and unixepoch

[Petite Abeille]

Hello,

Say I got an unix epoch, e.g. 1030561675:

> select datetime( 1030561675, 'unixepoch', 'utc' );
2002-08-28 19:07:55

Say I only need the date part of it:

> select date( 1030561675, 'unixepoch', 'utc'  );
2002-08-28

Say, I would like to convert that date to a julian day:

> select julianday( date( 1030561675, 'unixepoch', 'utc'  ) );
2452514.5

Oooops... where does that .5 comes from?

I was more expecting the following:

> select cast( julianday( date( 1030561675, 'unixepoch', 'utc' ) ) as integer );
2452514

So how come does julianday take the time part of date into consideration even 
when it's not provided?

JD 2452514.50 is
CE 2002 August 28 00:00:00.0 UT  Wednesday

JD 2452514.00 is
CE 2002 August 27 12:00:00.0 UT  Tuesday

Or did I miss something?

Thanks in advance.


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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Petite Abeille]

On Sep 17, 2011, at 8:43 PM, Mr. Puneet Kishor wrote:

> All this chattering among us doesn't really matter (other than for academic 
> purposes).

For the, hmmm, more scholarly inclined on the list, a marginally more readable 
ISO spec, ISO/IEC 9075-2:2003:

http://synthesis.ipi.ac.ru/synthesis/student/oodb/essayRef/sqlFoundation

> All that matters is whether or not Richard and co. deem it worth including as 
> a capability in factory provided SQLite. While I can't attest to it, there 
> are probably instances where SQLite deviates from the so called standard 
> ("the problem with standards is there are plenty of them" and all that). For 
> me, if Pg does it, the SQLite could do it if those who make SQLite could be 
> convinced of its usefulness.
> 
> For me, I don't care either way. I am glad Pg has it because I am trying to 
> convert MySQL data to Pg. While I am failing to do so painlessly for the most 
> part, a few of the (simpler) tables convert fine because both MySQL and Pg 
> support multi-line INSERTs.

FWIW, I personally find that specific flavor of multi-rows INSERT rather 
esoteric and borderline useless, but each to their own. 

I would not mind seeing MERGE tough :))



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

Re: [sqlite] Performance problem LEFT OUTER JOINandstringdatafromright table

[Igor Tandetnik]

Mira Suk  wrote:
> test 1.
> 
> query
> SELECT [IndexME].[IDI], [IndexME].[Status], [IndexME].[Icon], [IndexME].[Text]
> FROM [IndexME] LEFT OUTER JOIN [ItemsME]
> ON [ItemsME].[IDR] = [IndexME].[IDI] WHERE
> [IndexME].[Parent] = ?1 AND
> (TZB_MATCHRECURSIVE([IndexME].[IDI], [IndexME].[Status]) OR
> (TZB_ISCHILD([IndexME].[Status]) AND TZB_MATCHDIM([ItemsME].[Status], 
> [ItemsME].[Points])))
> ORDER BY [IndexME].[Order];
> 
> test 2. (TZB_MATCHDIM removed)
> 
> query
> SELECT [IndexME].[IDI], [IndexME].[Status], [IndexME].[Icon], [IndexME].[Text]
> FROM [IndexME] LEFT OUTER JOIN [ItemsME]
> ON [ItemsME].[IDR] = [IndexME].[IDI] WHERE
> [IndexME].[Parent] = ?1 AND
> (TZB_MATCHRECURSIVE([IndexME].[IDI], [IndexME].[Status]) OR
> (TZB_ISCHILD([IndexME].[Status])))
> ORDER BY [IndexME].[Order];

The difference is that #2 mentions only one field from ItemsME, namely IDR. The 
value of that field comes from the index, the table itself doesn't need to be 
read at all. It's not even clear why #2 bothers to join with ItemsME at all - 
it's a no-op.

#1 uses more fields from ItemsME, so it needs to actually look up and read 
records from that table.
-- 
Igor Tandetnik

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Mr. Puneet Kishor]

On Sep 17, 2011, at 1:06 PM, Simon Slavin wrote:

> 
> On 17 Sep 2011, at 6:42pm, Petite Abeille wrote:
> 
>> On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote:
>> 
>>> As was clear from my post, I was referring to SQL standards.  What various 
>>> implementation vendors choose to do is up to them.  But the multi-spec 
>>> syntax referred to in the OP is not in any SQL standard I've seen.
>> 
>> In BNF Grammar for ISO/IEC 9075:1999 - Database Language SQL (SQL-99), under 
>> contextually typed row value expression list:
>> 
>> http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list
> 
>  ::=|  | 
> 
> 
> ? Which one, and where is the expansion that allows for multiple sets of 
> brackets after "VALUES" ?
> 

All this chattering among us doesn't really matter (other than for academic 
purposes). All that matters is whether or not Richard and co. deem it worth 
including as a capability in factory provided SQLite. While I can't attest to 
it, there are probably instances where SQLite deviates from the so called 
standard ("the problem with standards is there are plenty of them" and all 
that). For me, if Pg does it, the SQLite could do it if those who make SQLite 
could be convinced of its usefulness.

For me, I don't care either way. I am glad Pg has it because I am trying to 
convert MySQL data to Pg. While I am failing to do so painlessly for the most 
part, a few of the (simpler) tables convert fine because both MySQL and Pg 
support multi-line INSERTs.

Puneet.

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Petite Abeille]

On Sep 17, 2011, at 8:06 PM, Simon Slavin wrote:

>  ::=|  | 
> 
> 
> ? Which one, and where is the expansion that allows for multiple sets of 
> brackets after "VALUES" ?

If I'm reading this correctly, the constructor one.

Step by step:

(1)  insert statement

http://savage.net.au/SQL/sql-99.bnf.html#insert%20statement

(2)  insert columns and source

http://savage.net.au/SQL/sql-99.bnf.html#insert%20columns%20and%20source

(3) from constructor

http://savage.net.au/SQL/sql-99.bnf.html#from%20constructor

(4)  contextually typed table value constructor

http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20table%20value%20constructor

(5) contextually typed row value expression list

http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list

  ::= 
 
 [ {   }... ]

(6) contextually typed row value constructor

 
 | [ ROW ]   

http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20constructor

Phew.



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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Simon Slavin]

On 17 Sep 2011, at 6:42pm, Petite Abeille wrote:

> On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote:
> 
>> As was clear from my post, I was referring to SQL standards.  What various 
>> implementation vendors choose to do is up to them.  But the multi-spec 
>> syntax referred to in the OP is not in any SQL standard I've seen.
> 
> In BNF Grammar for ISO/IEC 9075:1999 - Database Language SQL (SQL-99), under 
> contextually typed row value expression list:
> 
> http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list

  ::=|  | 


? Which one, and where is the expansion that allows for multiple sets of 
brackets after "VALUES" ?

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Petite Abeille]

On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote:

> As was clear from my post, I was referring to SQL standards.  What various 
> implementation vendors choose to do is up to them.  But the multi-spec syntax 
> referred to in the OP is not in any SQL standard I've seen.

In BNF Grammar for ISO/IEC 9075:1999 - Database Language SQL (SQL-99), under 
contextually typed row value expression list:

http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Simon Slavin]

On 17 Sep 2011, at 6:08pm, Petite Abeille wrote:

> On Sep 17, 2011, at 6:04 PM, Simon Slavin wrote:
> 
>> Can you find the syntax that allows
> 
> These ANSI specifications are notoriously difficult to pinpoint :)

Not really, they just cost money.  For instance:



That's part 1 and costs US$30.  For a good grasp of SQL you need most of the 
first 11 parts.  Part 11, for instance costs US$321.00.  Consequently nobody 
buys them.  And consequently nobody cares what they say.

> Pretty consistent syntax across vendors, and not totally outlandish

As was clear from my post, I was referring to SQL standards.  What various 
implementation vendors choose to do is up to them.  But the multi-spec syntax 
referred to in the OP is not in any SQL standard I've seen.

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Petite Abeille]

On Sep 17, 2011, at 6:04 PM, Simon Slavin wrote:

> Can you find the syntax that allows

These ANSI specifications are notoriously difficult to pinpoint :)

DB2:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r970.htm

INSERT 
INTO DEPARTMENT 
  (DEPTNO, DEPTNAME, ADMRDEPT)
VALUES  ('B11', 'PURCHASING', 'B01'),
  ('E41', 'DATABASE ADMINISTRATION', 'E01') 

MySQL:

http://dev.mysql.com/doc/refman/5.1/en/insert.html

INSERT INTO tbl_name
 (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);


PostgreSQL:

http://www.postgresql.org/docs/8.2/static/sql-insert.html

INSERT INTO films (code, title, did, date_prod, kind) VALUES
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');


SQL Server:

http://msdn.microsoft.com/en-us/library/dd776382.aspx

INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), 
(N'Y3', N'Cubic Yards', '20080923');

Pretty consistent syntax across vendors, and not totally outlandish  to presume 
it's a "standard"  from the point of view of the casual dilettante :)

Sadly, there is no such thing as "standard sql" :))

Still, in the case of SQLite as with any other product, the fine manual is 
there to sort this out:

"SQL As Understood By SQLite"
http://www.sqlite.org/lang.html








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

Re: [sqlite] Performance problem LEFT OUTER JOIN andstringdatafromright table

[Mira Suk]

>Ok then, show the result of prepending EXPLAIN QUERY PLAN to your statement.
>-- 
>Igor Tandetnik

First of all thanks for bearing with me :)

functions
TZB_MATCHRECURSIVE(int,int) 
- disabled for this test - always return 1. applies filter recursively
TZB_ISCHILD(int) 
- bitmask check
TZB_MATCHDIM(int,text) 
- disabled for this test - always return 1. does weird stuff with text parameter


You probably by now realized the table forms tree of items. Parent links to IDI 
and this way tree structure is formed.
parent parameter is for testing set to 0 - which is root of tree (query should 
return top-most folders)
in this query all ItemsME.Status and ItemsME.Points are NULL. there is no entry 
in ItemsME for directories and there are no "files" in root


test 1.

query
SELECT [IndexME].[IDI], [IndexME].[Status], [IndexME].[Icon], [IndexME].[Text]
FROM [IndexME] LEFT OUTER JOIN [ItemsME]
ON [ItemsME].[IDR] = [IndexME].[IDI] WHERE 
[IndexME].[Parent] = ?1 AND 
(TZB_MATCHRECURSIVE([IndexME].[IDI], [IndexME].[Status]) OR
(TZB_ISCHILD([IndexME].[Status]) AND TZB_MATCHDIM([ItemsME].[Status], 
[ItemsME].[Points])))
ORDER BY [IndexME].[Order];


plan
"SEARCH TABLE IndexME USING AUTOMATIC COVERING INDEX (Parent=?) (~5 rows)"
"SEARCH TABLE ItemsME USING AUTOMATIC COVERING INDEX (IDR=?) (~5 rows)"
"USE TEMP B-TREE FOR ORDER BY"

runtime ~2seconds


test 2. (TZB_MATCHDIM removed)

query
SELECT [IndexME].[IDI], [IndexME].[Status], [IndexME].[Icon], [IndexME].[Text]
FROM [IndexME] LEFT OUTER JOIN [ItemsME]
ON [ItemsME].[IDR] = [IndexME].[IDI] WHERE 
[IndexME].[Parent] = ?1 AND 
(TZB_MATCHRECURSIVE([IndexME].[IDI], [IndexME].[Status]) OR
(TZB_ISCHILD([IndexME].[Status])))
ORDER BY [IndexME].[Order];

plan
"SEARCH TABLE IndexME USING AUTOMATIC COVERING INDEX (Parent=?) (~5 rows)"
"SEARCH TABLE ItemsME USING AUTOMATIC COVERING INDEX (IDR=?) (~5 rows)"
"USE TEMP B-TREE FOR ORDER BY"

runtime ~160miliseconds

test 3. (TZB_MATCHDIM replaced with LENGTH to prove it's not my func)
results equal to 1)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT syntax missing key SQL syntax

[Kees Nuyt]

On Sat, 17 Sep 2011 16:23:23 +0100, Simon Slavin
 wrote:

>
>On 16 Sep 2011, at 8:27pm, Jim Michaels wrote:
>
>> INSERT is supposed to handle multiple rows for VALUES.
>> for example,
>> INSERT INTO table(digit,dialpadstr) VALUES
>> (2,'abc'),
>> (3,'def'),
>> (4,'ghi'),
>> (5,'jkl'),
>> (6,'mno'),
>> (7,'pqrs'),
>> (8,'tuv'),
>> (9,'wxyz');
>
>Can you find support for that 'supposed' in the specification for SQL ?

Probably not.

See:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Specifically:
Page 390, Chapter 13.8, leveling rules, rule 2a.
Page 648, Rule 63a.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT syntax missing key SQL syntax

[Simon Slavin]

On 17 Sep 2011, at 4:52pm, Petite Abeille wrote:

> On Sep 17, 2011, at 5:11 PM, Igor Tandetnik wrote:
> 
>> Supposed by whom? What is the basis for this claim?
> 
> SQL-92:
> 
> http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts

Here's a draft of SQL-92.  You can find the INSERT statement on page 388 et 
seq..



Can you find the syntax that allows

>> INSERT INTO table(digit,dialpadstr) VALUES
>> (2,'abc'),(3,'def'),(4,'ghi'),(5,'jkl'),(6,'mno'),(7,'pqrs'),(8,'tuv'),(9,'wxyz');

?

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Petite Abeille]

On Sep 17, 2011, at 5:55 PM, Mr. Puneet Kishor wrote:

> Essentially, if I had my druthers, I would support whatever Pg supports and 
> be done with it. For most part SQLite seems to follow "good for Pg good for 
> the gander" philosophy.

I would much rather have a MERGE statement:

http://en.wikipedia.org/wiki/Merge_(SQL)

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Mr. Puneet Kishor]

On Sep 17, 2011, at 10:33 AM, Simon Slavin wrote:

> 
> On 17 Sep 2011, at 4:29pm, Mr. Puneet Kishor wrote:
> 
>> The multiline INSERT capability may not be a SQL standard, but it is not 
>> only highly convenient, it is also supported by Pg, the ostensible role 
>> model and inspiration for SQLite. Not that I particularly care either way 
>> because I almost always use Perl to deal with Pg and SQLite, but it 
>> would/might be nice to have for some. Perhaps those folks should continue to 
>> ask Richard nicely instead of seeming to "demand" it on the basis of some 
>> political claim.
> 
> How would you support multiple INSERTs with binding ?  



I wouldn't. Multiple INSERT statement seems to be better suited for large scale 
dump/restore, or even for manual entry, but without binding.

Essentially, if I had my druthers, I would support whatever Pg supports and be 
done with it. For most part SQLite seems to follow "good for Pg good for the 
gander" philosophy.



> Given a statement like
> 
>> INSERT INTO table(digit,dialpadstr) VALUES
>> (2,'abc'),(3,'def'),(4,'ghi'),(5,'jkl'),(6,'mno'),(7,'pqrs'),(8,'tuv'),(9,'wxyz');
> 
> would you expect 16 binding values ?
> 
> Simon.

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Petite Abeille]

On Sep 17, 2011, at 5:11 PM, Igor Tandetnik wrote:

> Supposed by whom? What is the basis for this claim?

 SQL-92:

http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts

This feature is supported by DB2, SQL Server (since version 10.0 - i.e. 2008), 
PostgreSQL (since version 8.2), MySQL, and H2.


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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Simon Slavin]

On 17 Sep 2011, at 4:29pm, Mr. Puneet Kishor wrote:

> The multiline INSERT capability may not be a SQL standard, but it is not only 
> highly convenient, it is also supported by Pg, the ostensible role model and 
> inspiration for SQLite. Not that I particularly care either way because I 
> almost always use Perl to deal with Pg and SQLite, but it would/might be nice 
> to have for some. Perhaps those folks should continue to ask Richard nicely 
> instead of seeming to "demand" it on the basis of some political claim.

How would you support multiple INSERTs with binding ?  Given a statement like

> INSERT INTO table(digit,dialpadstr) VALUES
> (2,'abc'),(3,'def'),(4,'ghi'),(5,'jkl'),(6,'mno'),(7,'pqrs'),(8,'tuv'),(9,'wxyz');

would you expect 16 binding values ?

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Mr. Puneet Kishor]

On Sep 17, 2011, at 10:11 AM, Igor Tandetnik wrote:

> Jim Michaels 
> 
>  wrote:
>> INSERT is supposed to handle multiple rows for VALUES.
> 
> Supposed by whom? What is the basis for this claim?

The multiline INSERT capability may not be a SQL standard, but it is not only 
highly convenient, it is also supported by Pg, the ostensible role model and 
inspiration for SQLite. Not that I particularly care either way because I 
almost always use Perl to deal with Pg and SQLite, but it would/might be nice 
to have for some. Perhaps those folks should continue to ask Richard nicely 
instead of seeming to "demand" it on the basis of some political claim.

--
Puneet Kishor

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Simon Slavin]

On 16 Sep 2011, at 8:27pm, Jim Michaels wrote:

> INSERT is supposed to handle multiple rows for VALUES.
> for example,
> INSERT INTO table(digit,dialpadstr) VALUES
> (2,'abc'),
> (3,'def'),
> (4,'ghi'),
> (5,'jkl'),
> (6,'mno'),
> (7,'pqrs'),
> (8,'tuv'),
> (9,'wxyz');

Can you find support for that 'supposed' in the specification for SQL ?

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

Re: [sqlite] UPDATE of field deletes record

[Igor Tandetnik]

Jim Michaels 
 
wrote:
> cancel the bug report.  further testing revealed that it does NOT
> exhibit a bug, except for the fact that I can't do multirow INSERTs
> (that, unfortunately is not in the manual, and should be a standard
> feature).

Should it be? In which standard is this feature mandated?
-- 
Igor Tandetnik

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Igor Tandetnik]

Jim Michaels 
 
wrote:
> INSERT is supposed to handle multiple rows for VALUES.

Supposed by whom? What is the basis for this claim?
-- 
Igor Tandetnik

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

Re: [sqlite] Performance problem LEFT OUTER JOIN and stringdatafromright table

[Igor Tandetnik]

Mira Suk  wrote:
>> Mira Suk  wrote:
>>> query written here is a lot simplified (for example "Points" column is 
>>> filtered using custom function) however main culprit
>>> seems to be LEFT OUTER JOIN as accessing that same column in query which 
>>> only has B table in it is lightning fast.
>>> 
>>> result of query is
>>> just around 40 rows, (due to Parent filter)
>> 
>> You may want an index on A(Parent) then.
> 
> A.Parent is has Index

Ok then, show the result of prepending EXPLAIN QUERY PLAN to your statement.
-- 
Igor Tandetnik

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Kees Nuyt]

On Fri, 16 Sep 2011 12:27:35 -0700, Jim Michaels
 wrote:

>INSERT is supposed to handle multiple rows for VALUES.
>for example,
>INSERT INTO table(digit,dialpadstr) VALUES
>(2,'abc'),
>(3,'def'),
>(4,'ghi'),
>(5,'jkl'),
>(6,'mno'),
>(7,'pqrs'),
>(8,'tuv'),
>(9,'wxyz');

With the preferred 

{
_open*()
:
_exec('BEGIN')
_prepare*() 
while rows2insert{
_bind*()
_bind*()
:
:
_step() 
_reset() (?)
}
_finalize()
_exec('COMMIT')
:
_close()
}
sequence, there is no need for a multirow INSERT syntax.
It would complicate the _bind()

With shell scipts, it doesn't have much advantage either, as you
can prepend the fixed part
"INSERT INTO tablename (colname1,...colnameN) VALUES "
very easily, e.g.

somepreprocess | awk -f script | sqlite3 database >log

where script contains something like:
{
printf \
"INSERT INTO ... VALUES (%d,'%s',...);\n",\
$1,$2,...
}

>currently, sqlite only handles 1 row.
>INSERT INTO table(digit,dialpadstr) VALUES
>(2,'abc');
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT syntax missing key SQL syntax

[Petite Abeille]

On Sep 16, 2011, at 9:27 PM, Jim Michaels wrote:

> currently, sqlite only handles 1 row.
> INSERT INTO table(digit,dialpadstr) VALUES
> (2,'abc');

try:

insert into foo ( bar ) 
select 1 as bar union all
select 2 as bar
etc...

Please refer back to the fine manual:

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

Re: [sqlite] UPDATE of field deletes record

[Jim Michaels]

On 9/15/2011 6:09 AM, Jean-Christophe Deschamps wrote:


with an existing record, and a fieldname that is in a column that has 
a UNIQUE INDEX, if I do sqlite_exec("UPDATE database SET 
fieldname="_escape("get calculator batteries")&" WHERE 
fieldname=sqlite_escape("get hp50g calc cells")&";"
this code actually deletes the record.  it should not.  I don't ever 
remember seeing this happen in any database like pgsql or mysql.  I 
can test again to see for sure.


the test code can be seen at 
http://www.autoitscript.com/trac/autoit/ticket/2012


I used escape on the strings.  existing examples do not put single 
quotes on the strings.  if single quotes are required to mske strings 
work as I think they do, please tell me.  I am not getting errors.


Could you please post your question on the AutoIt help forum?  It's 
there for a reason.

BTW yes you're getting errors but you don't test for them.
More on the AutoIt help forum rather than on this list.

--
j...@antichoc.net

cancel the bug report.  further testing revealed that it does NOT 
exhibit a bug, except for the fact that I can't do multirow INSERTs 
(that, unfortunately is not in the manual, and should be a standard 
feature).  there is usually a limit as to how many rows you can do at once.
BTW, I have fixed my original program and test code and released my 
to-do-list program as of today.


the new test code looks like this:
obviously, the author of autoit (autoitscript.com) has made the language 
automatically insert single quotes for us, because his example code 
shows no single quotes, and this code works, even with a single quote.


I also had a bug in my debug output routine where I wasn't concatenating 
strings.


;===
;
; Program Name: to-do-list
; Description:  to-do list
; Requirement(s):   None
; Return Value(s):  None
; Author(s):Jim Michaels 
; Create Date:  9/16/2011
; Current Date: 9/16/2011
;
;the test will be to see if SQLITE3 will delete the record that contains 
"that's it" by an UPDATE (no, it does not)

;
;===

$PROGRAM_NAME="sqlite-bug-test"
$PROGRAM_TITLE="SQLIte3 Bug Test"
$PROGRAM_VERSION="1.1"

#AutoIt3Wrapper_au3check_parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6
#include 
#include 

#include 
#include 
#include 
#include 


Global $dbfilename, $hDB, $hQuery, $hquery, $aRow



$dbdir=@AppDataCommonDir&"\JimMichaels\"&$PROGRAM_NAME
DirCreate($dbdir)
$dbpath=$dbdir&"\"&$PROGRAM_NAME&".sqlitedb"



_SQLite_Startup ()
If @error > 0 Then
MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!. report to 
author jmich...@yahoo.com")

Exit - 1
EndIf
$hDB=_SQLite_Open($dbpath); Open a :memory: database
If @error > 0 Then
MsgBox(16, "SQLite Error", "Can't Load Database!. report to author 
jmich...@yahoo.com")

_SQLite_Shutdown()
Exit - 1
EndIf
If $SQLITE_OK <> _SQLite_Exec($hDB,"CREATE TABLE IF NOT EXISTS test (" _
& "entry_id INTEGER CONSTRAINT entry_id_c PRIMARY KEY AUTOINCREMENT, " _
& "shortdesc TEXT NOT NULL DEFAULT ''" _
& ");") Then
MsgBox(0,"SQLite Error","Error Code1: " & _SQLite_ErrCode() & @CRLF 
& "Error Message: " & _SQLite_ErrMsg()&". report to author 
jmich...@yahoo.com")

_SQLite_Close($hDB)
_SQLite_Shutdown()
exit -1
endif
;_SQLite_Exec($hDB,"CREATE UNIQUE INDEX IF NOT EXISTS shortdesc_idx ON 
todolist(shortdesc);")


_SQLite_Exec($hDB,"INSERT INTO test(entry_id,shortdesc) VALUES 
(1,"&_SQLite_Escape("that's it")&");")

_SQLite_Exec($hDB,"INSERT INTO test(entry_id,shortdesc) VALUES (2,'b');")
_SQLite_Exec($hDB,"INSERT INTO test(entry_id,shortdesc) VALUES (3,'c');")
_SQLite_Exec($hDB,"INSERT INTO test(entry_id,shortdesc) VALUES (4,'d');")


If $SQLITE_OK <> _SQLite_Exec($hDB,"UPDATE test SET 
shortdesc="&_SQLite_Escape("")&" WHERE 
shortdesc="&_SQLite_Escape("that's it")&";") Then
MsgBox(0,"SQLite Error","Error Code1: " & _SQLite_ErrCode() & @CRLF 
& "Error Message: " & _SQLite_ErrMsg()&". report to author 
jmich...@yahoo.com")

_SQLite_Close($hDB)
_SQLite_Shutdown()
exit -1
endif

;output shortdesc column to messagebox
dumpfieldname("test","shortdesc")



;output db column plainly to messagebox
func dumpfieldname($tablename,$fieldname)
local $s=""
$err=_SQLite_Query ( $hDB, "SELECT "&$fieldname&" FROM 
"&$tablename&";", $hQuery )

select
case $err==$SQLITE_OK
case $err==-1
msgbox(0,"OOPS",  "SELECT prob0: Error Code: " & 
_SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg()&". 
report to author jmich...@yahoo.com")

case $err=1
msgbox(0,"OOPS",  "SELECT problem0:Error calling SQLite API 
'sqlite3_prepare'. report to author jmich...@yahoo.com")

case $err=2
msgbox(0,"OOPS",  "SELECT problem0: call prevented by safe 
mode. report to author jmich...@yahoo.com")

endselect

While 

[sqlite] INSERT syntax missing key SQL syntax

[Jim Michaels]

INSERT is supposed to handle multiple rows for VALUES.
for example,
INSERT INTO table(digit,dialpadstr) VALUES
(2,'abc'),
(3,'def'),
(4,'ghi'),
(5,'jkl'),
(6,'mno'),
(7,'pqrs'),
(8,'tuv'),
(9,'wxyz');


currently, sqlite only handles 1 row.
INSERT INTO table(digit,dialpadstr) VALUES
(2,'abc');

--
Jim Michaels
j...@jimscomputerrepairandwebdesign.com
http://JimsComputerRepairandWebDesign.com
http://JesusnJim.com

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

Re: [sqlite] Performance problem LEFT OUTER JOIN and string datafromright table

[Mira Suk]

 
> Mira Suk  wrote:
>> query written here is a lot simplified (for example "Points" column is 
>> filtered using custom function) however main culprit seems
>> to be LEFT OUTER JOIN as accessing that same column in query which only has 
>> B table in it is lightning fast.
>>
>> result of query is
>> just around 40 rows, (due to Parent filter)
>
>You may want an index on A(Parent) then.
>-- 
>Igor Tandetnik
 
A.Parent is has Index
 
query
SELECT IDI FROM A WHERE Parent = ?
is fast - aka this have nothing to with that condition. IMHO only with result 
column from B table.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance problem LEFT OUTER JOIN and string data fromright table

[Igor Tandetnik]

Mira Suk  wrote:
> query written here is a lot simplified (for example "Points" column is 
> filtered using custom function) however main culprit seems
> to be LEFT OUTER JOIN as accessing that same column in query which only has B 
> table in it is lightning fast.
>
> result of query is
> just around 40 rows, (due to Parent filter)

You may want an index on A(Parent) then.
-- 
Igor Tandetnik

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

[sqlite] Performance problem LEFT OUTER JOIN and string data from right table

[Mira Suk]

Hey everyone.
 
new to SQLite so please have patience with me
having two tables and doing left outer join
 
A
IDI INTEGER PRIMARY KEY
Parent INTEGER INDEX
Status INTEGER

 
B
IDR UNIQUE INTEGER FOREIGN KEY IndexME.IDI
Points TEXT (at average ~120 character string)
 (this table is primarily used for large data, so it contains some blobs 
etc...)
 
SELECT A.IDI, B.Text
FROM A
LEFT OUTER JOIN B ON B.IDR = A.IDI
WHERE Parent = ?
 
query written here is a lot simplified (for example "Points" column is filtered 
using custom function) however main culprit seems to be LEFT OUTER JOIN as 
accessing that same column in query which only has B table in it is lightning 
fast.
result of query is just around 40 rows, (due to Parent filter) and it takes ~2 
seconds on I7 3.4Ghz with database stored on SSD HDD.
 
am I doing something terribly wrong ? suggestions ?
 
oh and I tried running ANALYZE on database connection without any visible 
improvement.
 
thanks in advance,
M.
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to store and retrieve list of images from sqlite in android

[Mayuri]

*
I wrote the code  in java file for displaying images.But those images are
not displaying in emulator.
If you possible give me a sample application to retrieve list of images from
sqlite.


-- 
View this message in context: 
http://old.nabble.com/How-to-store-and-retrieve-list-of-images-from-sqlite-in-android-tp32469610p32483518.html
Sent from the SQLite mailing list archive at Nabble.com.

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