date:20080211

Re: [sqlite] Help for sqlite syntax

2008-02-11 Thread Mohd Radzi Ibrahim

Could you just use ORDER BY 1 ?

best regards
-- radzi --
- Original Message - 
From: "li yuqian" <[EMAIL PROTECTED]>
To: 
Cc: "Dimitar Penev" <[EMAIL PROTECTED]>; "Mark" <[EMAIL PROTECTED]>
Sent: Tuesday, February 12, 2008 1:56 PM
Subject: [sqlite] Help for sqlite syntax


> Hi guys,
>
> i am a newbie of sqlite3, we want porting the Freepbx(freepbx.org) to our
> project www.astfin.org, the freepbx can support sqlite3, but not very 
> well,
> now i got a problem about sqlite3
> -
> SELECT t.variable, t.value, d.value state FROM `globals` t JOIN (SELECT
> x.variable, x.value FROM globals x WHERE x.variable LIKE 'OUTDISABLE\_%') 
> d
> ON substring(t.variable,5) = substring(d.variable,12) WHERE t.variable 
> LIKE
> 'OUT\_%' UNION ALL SELECT v.variable, v.value,
> concat(substring(v.value,1,0),'off')
> state FROM `globals` v WHERE v.variable LIKE 'OUT\_%' AND
> concat('OUTDISABLE_',substring(v.variable,5)) NOT IN ( SELECT variable 
> from
> globals WHERE variable LIKE 'OUTDISABLE\_%' ) ORDER BY variable
> ---
>
> above sql working very well at mysql, but at sqlite3 will show:
> ---
> SQL error: ORDER BY term number 1 does not match any result column
> ---
>
> the globals table is
> --
> CREATE TABLE `globals` (
>  `variable` char(20) NOT NULL default '',
>  `value` char(50) NOT NULL default '',
>  PRIMARY KEY  (`variable`)
> ) ;
> -
> and insert some contents to this table
> --
> INSERT INTO `globals` VALUES ('CALLFILENAME','\"\"');
> INSERT INTO `globals` VALUES ('DIAL_OPTIONS','tr');
> INSERT INTO `globals` VALUES ('TRUNK_OPTIONS','');
> INSERT INTO `globals` VALUES ('DIAL_OUT','9');
> INSERT INTO `globals` VALUES ('FAX','');
> INSERT INTO `globals` VALUES ('FAX_RX','system');
> INSERT INTO `globals` VALUES ('FAX_RX_EMAIL','[EMAIL PROTECTED]');
> INSERT INTO `globals` VALUES ('FAX_RX_FROM','[EMAIL PROTECTED]');
> INSERT INTO `globals` VALUES ('INCOMING','group-all');
> INSERT INTO `globals` VALUES ('NULL','\"\"');
> INSERT INTO `globals` VALUES ('OPERATOR','');
> INSERT INTO `globals` VALUES ('OPERATOR_XTN','');
> INSERT INTO `globals` VALUES ('PARKNOTIFY','SIP/200');
> INSERT INTO `globals` VALUES ('RECORDEXTEN','\"\"');
> INSERT INTO `globals` VALUES ('RINGTIMER','15');
> INSERT INTO `globals` VALUES ('DIRECTORY','last');
> INSERT INTO `globals` VALUES ('AFTER_INCOMING','');
> INSERT INTO `globals` VALUES ('IN_OVERRIDE','forcereghours');
> INSERT INTO `globals` VALUES ('REGTIME','7:55-17:05');
> INSERT INTO `globals` VALUES ('REGDAYS','mon-fri');
> INSERT INTO `globals` VALUES ('DIRECTORY_OPTS','');
> INSERT INTO `globals` VALUES ('DIALOUTIDS','1');
> INSERT INTO `globals` VALUES ('OUT_1','ZAP/g0');
> INSERT INTO `globals` VALUES ('VM_PREFIX','*');
> INSERT INTO `globals` VALUES ('VM_OPTS','');
> INSERT INTO `globals` VALUES ('VM_GAIN','');
> INSERT INTO `globals` VALUES ('VM_DDTYPE','u');
> INSERT INTO `globals` VALUES ('TIMEFORMAT','kM');
> INSERT INTO `globals` VALUES ('TONEZONE','us');
> INSERT INTO `globals` VALUES ('ALLOW_SIP_ANON','no');
> INSERT INTO `globals` VALUES ('VMX_CONTEXT','from-internal');
> INSERT INTO `globals` VALUES ('VMX_PRI','1');
> INSERT INTO `globals` VALUES ('VMX_TIMEDEST_CONTEXT','');
> INSERT INTO `globals` VALUES ('VMX_TIMEDEST_EXT','dovm');
> INSERT INTO `globals` VALUES ('VMX_TIMEDEST_PRI','1');
> INSERT INTO `globals` VALUES ('VMX_LOOPDEST_CONTEXT','');
> INSERT INTO `globals` VALUES ('VMX_LOOPDEST_EXT','dovm');
> INSERT INTO `globals` VALUES ('VMX_LOOPDEST_PRI','1');
> INSERT INTO `globals` VALUES ('VMX_OPTS_TIMEOUT','');
> INSERT INTO `globals` VALUES ('VMX_OPTS_LOOP','');
> INSERT INTO `globals` VALUES ('VMX_OPTS_DOVM','');
> INSERT INTO `globals` VALUES ('VMX_TIMEOUT','2');
> INSERT INTO `globals` VALUES ('VMX_REPEAT','1');
> INSERT INTO `globals` VALUES ('VMX_LOOPS','1');
> INSERT INTO `globals` VALUES ('TRANSFER_CONTEXT','from-internal-xfer');
> -
>
> how i can change the sql for sqlite3, any idea, thanks
>
> -- 
> Li YuQian
> Your Astfin team
> ___
> uClinux/Asterisk distribution for Blackfin CPU
> http://www.ucpbx.com
> http://astfin.org
> http://sourceforge.net/projects/astfin/
> ___
> ___
> 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] Updatable views

2008-02-11 Thread John Stanton

[EMAIL PROTECTED] wrote:
> John Stanton <[EMAIL PROTECTED]> wrote:
>> That ia a nice idea.  To have a pragma which specied the dialect.  There 
>> could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. 
>>   It would give tighter control over hard to track annoying minor syntax 
>> errors.
>>
> 
> And, it would multiple exponentially the number of test cases
> we have to write and maintain in order to adequately test the
> parser ;-)
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
A practical limitation.  Perhaps a simpler to administer approach would 
be an auxiliary program like lint to decouple the maintenance of the 
dialects from the ongoing enhancement of Sqlite proper.

> ___
> 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] Selecting all and some columns

2008-02-11 Thread jose isaias cabrera


"Igor Tandetnik" wrote...
> jose isaias cabrera wrote:
>> Greetings...
>>
>> I know that Puneet will get on my case about the obscurity of the
>> subject (just kidding), but I am trying to find out if I can do this:
>> Imagine this table and data...
>>
>> Class|ProjID|ProjFund|Invoice|Split
>> Finishers|1045|73||
>> Finishers|1045|75|30|
>> Finishers|1045|75|30|
>> Finishers|1045|75|30|
>> Finishers|1045|75||
>> Finishers|1045|75|75|y
>> Finishers|1045|75|25|
>> Finishers|1045|73||
>> Finishers|1045|73||
>> Finishers|1045|73||
>> Finishers|1045|73|58.4|y
>> Finishers|1045|73||
>>
>> What I would like is to have total of ProjFund, a total of ProjFund -
>> Invoices which Split = 'y' and a total of Invoices which Split = 'y'.
>>
>> I know I can do this programatically, but I would like to be able to
>> have sqlite return the results to me.  Is it possible?  What I have
>> right now is this,
>>
>> SELECT Class, sum(ProjFund), sum(ProjFund) - sum(invoice),
>> sum(invoices) from ClassTable  group by Class, ProjID;
>>
>> I just don-t know how to do the Split = 'y' part.  Any help would be
>> greatly appreciated.
>
> Perhaps something like this:
>
> SELECT Class,
>sum(ProjFund),
>sum(ProjFund) - sum(case split when 'y' then invoice else 0 end),
>sum(case split when 'y' then invoice else 0 end)
> from ClassTable  group by Class, ProjID;

Thanks, Igor.  This worked perfectly.  I have one more ask for help, imagine 
almost the same data,

Class|ProjID|ProjFund|Invoice|PM|Split
Finishers|1045|73||JIC|
Finishers|1045|75|30|LED|
Finishers|1045|75|30|SAN|
Finishers|1045|75|30|JIC|
Finishers|1045|75||ELI|
Finishers|1045|75|75|ELI|y
Finishers|1045|75|25||
Finishers|1045|73||JIC|
Finishers|1045|73||LED|
Finishers|1045|73||KAP|
Finishers|1045|73|58.4|ELI|y
Finishers|1045|73|||

I would like to also get the PM value when split = 'y'.  So, I tried editing 
Igor's solution in many ways, such as this,

SELECT Class,
   distinct(case split when 'y' then PM else null end),
   sum(ProjFund),
   sum(ProjFund) - sum(case split when 'y' then invoice else 0 end),
   sum(case split when 'y' then invoice else 0 end)
from ClassTable  group by Class, ProjID, PM;

but. though this executes, I am unsuccessful getting the correct data that I 
need.  Again, any help would be greatly appreciate it.  And yes, I can do 
this programmatically, but making a few calls to the DB, but I want to try 
to get all of these values in one call.

thanks,

josé



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

Re: [sqlite] Selecting all and some columns

2008-02-11 Thread jose isaias cabrera


Yes, but I want to do them in one shot.  Igor's solution worked perfectly.

josé

- Original Message - 
From: "P Kishor" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Monday, February 11, 2008 6:12 PM
Subject: Re: [sqlite] Selecting all and some columns


> On 2/11/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
>>
>> Greetings...
>>
>> I know that Puneet will get on my case about the obscurity of the subject
>> (just kidding), but I am trying to find out if I can do this: Imagine 
>> this
>> table and data...
>>
>> Class|ProjID|ProjFund|Invoice|Split
>> Finishers|1045|73||
>> Finishers|1045|75|30|
>> Finishers|1045|75|30|
>> Finishers|1045|75|30|
>> Finishers|1045|75||
>> Finishers|1045|75|75|y
>> Finishers|1045|75|25|
>> Finishers|1045|73||
>> Finishers|1045|73||
>> Finishers|1045|73||
>> Finishers|1045|73|58.4|y
>> Finishers|1045|73||
>>
>> What I would like is to have total of ProjFund, a total of ProjFund -
>> Invoices which Split = 'y' and a total of Invoices which Split = 'y'.
>>
>> I know I can do this programatically, but I would like to be able to have
>> sqlite return the results to me.  Is it possible?  What I have right now 
>> is
>> this,
>>
>> SELECT Class, sum(ProjFund), sum(ProjFund) - sum(invoice), sum(invoices)
>> from ClassTable  group by Class, ProjID;
>>
>> I just don-t know how to do the Split = 'y' part.  Any help would be 
>> greatly
>> appreciated.
>>
>
>
> is this what you are looking for?
>
> sqlite> select * from t;
> class   projid  projfundinvoice split
> --  --  --  --  --
> Finishers   104573
> Finishers   104575  30
> Finishers   104575  30
> Finishers   104575  30
> Finishers   104575
> Finishers   104575  75  y
> Finishers   104575  25
> Finishers   104573
> Finishers   104573
> Finishers   104573
> Finishers   104573  58.4y
> Finishers   104573
> sqlite> select class, sum(projfund) from t group by class;
> class   sum(projfund)
> --  -
> Finishers   888
> sqlite> select class, sum(projfund) from t where split = 'y' group by 
> class;
> class   sum(projfund)
> --  -
> Finishers   148
> sqlite> select class, sum(projfund), sum(projfund)-sum(invoice) as a
> from t where split = 'y' group by class;
> class   sum(projfund)  a
> --  -  --
> Finishers   14814.6
> sqlite> select class, sum(projfund), sum(projfund)-sum(invoice) a,
> sum(invoice) b from t where split = 'y' group by class;
> class   sum(projfund)  a   b
> --  -  --  --
> Finishers   14814.6133.4
> sqlite>
> ___
> 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

[sqlite] Igor's Emails come as NewsGroups Posts

2008-02-11 Thread jose isaias cabrera


Igor,

why is it that your sqlite replies come as NewsGroups posts?  I can not 
reply to them because my company does not allow replies to newsgroups.  Just 
wondering, because I wanted to thank you for your previous help, but I could 
not reply to it.

Thanks,

josé


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

Re: [sqlite] Selecting all and some columns

2008-02-11 Thread jose isaias cabrera

"BareFeet" wrote...

> Hi Jose,
> 
>> Class|ProjID|ProjFund|Invoice|Split
>> Finishers|1045|73||
>> Finishers|1045|75|30|
>> Finishers|1045|75|30|
>> Finishers|1045|75|30|
>> Finishers|1045|75||
>> Finishers|1045|75|75|y
>> Finishers|1045|75|25|
>> Finishers|1045|73||
>> Finishers|1045|73||
>> Finishers|1045|73||
>> Finishers|1045|73|58.4|y
>> Finishers|1045|73||
> 
> I think in the interests of "normalization" (basically removing  
> redundancy and nulls), you'd do best to split the data into four tables:
> 
> ProjID   |Class
> 1045 |Finishers
> 
> Fund:
> FundID   |ProjID   |ProjFund
> 1|1045 |73
> 2|1045 |75
> 3|1045 |75
> 4|1045 |75
> 5|1045 |75
> 6|1045 |75
> 7|1045 |75
> 8|1045 |73
> 9|1045 |73
> 10   |1045 |73
> 11   |1045 |73
> 12   |1045 |73
> 
> Invoice:
> InvoiceID|FundID   |Invoice
> 1|2|30
> 2|3|30
> 3|4|30
> 4|6|75
> 5|7|25
> 6|11   |58.4
> 
> Split:
> InvoiceID
> 4
> 6
> 
> Then you only record an entry in Split for those rows for which a  
> split applies (two rows in this case).
> 
> The above translates into this SQL to create the tables:
> 
> create table Project
> (
>   ProjID integer primary key
> , Class text collate nocase
> )
> ;
> create table Fund
> (
>   FundID integer primary key
> , ProjID integer --> Project.ProjID
> , ProjFund real
> )
> ;
> create table Invoice
> (
>   InvoiceID integer primary key
> , FundID integer --> Fund.FundID
> , Invoice real
> )
> ;
> create table Split
> (
> InvoiceID integer --> Invoice.InvoiceID
> )
> ;
> 
> --Populating the tables with your data:
> delete from Project;
> delete from Fund;
> delete from Invoice;
> delete from Split;
> 
> insert into Project (ProjID, Class) values (1045, 'Finishers');
> 
> insert into Fund (ProjID, ProjFund) values (1045, 73);
> insert into Fund (ProjID, ProjFund) values (1045, 75);
> insert into Fund (ProjID, ProjFund) values (1045, 75);
> insert into Fund (ProjID, ProjFund) values (1045, 75);
> insert into Fund (ProjID, ProjFund) values (1045, 75);
> insert into Fund (ProjID, ProjFund) values (1045, 75);
> insert into Fund (ProjID, ProjFund) values (1045, 75);
> insert into Fund (ProjID, ProjFund) values (1045, 73);
> insert into Fund (ProjID, ProjFund) values (1045, 73);
> insert into Fund (ProjID, ProjFund) values (1045, 73);
> insert into Fund (ProjID, ProjFund) values (1045, 73);
> insert into Fund (ProjID, ProjFund) values (1045, 73);
> 
> insert into Invoice (FundID, Invoice) values (2, 30);
> insert into Invoice (FundID, Invoice) values (3, 30);
> insert into Invoice (FundID, Invoice) values (4, 30);
> insert into Invoice (FundID, Invoice) values (6, 75);
> insert into Invoice (FundID, Invoice) values (7, 25);
> insert into Invoice (FundID, Invoice) values (11, 58.4);
> 
> insert into Split (InvoiceID) values (4);
> insert into Split (InvoiceID) values (6);
> 
>> --What I would like is to have total of ProjFund
> 
> 
> select sum(ProjFund) from Fund;
> 
> --> 888.0
> 
>> --a total of ProjFund - Invoices which Split = 'y'
> 
> 
> select sum(ProjFund - Invoice)
> from Split
> left join Invoice on Split.InvoiceID = Invoice.InvoiceID
> left join Fund on Invoice.FundID = Fund.FundID
> ;
> 
> --> 14.6
> 
>> --and a total of Invoices which Split = 'y'.
> 
> 
> select sum(Invoice)
> from Split
> left join Invoice on Split.InvoiceID = Invoice.InvoiceID
> ;
> 
> --> 133.4
> 
> I hope this helps. The benefits of normalizing increase with the  
> amount and/or complexity of your data. Notice there are no case  
> statements to get what you want. You just start with the table you  
> want (Split, in this case) and join any needed related data, so SQLite  
> only scans the relevant data, rather than testing every row.

Wouldn't this take longer to process then a case statement?

> 
> Tom
> BareFeet
> 
> ___
> 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

[sqlite] Help for sqlite syntax

2008-02-11 Thread li yuqian

Hi guys,

i am a newbie of sqlite3, we want porting the Freepbx(freepbx.org) to our
project www.astfin.org, the freepbx can support sqlite3, but not very well,
now i got a problem about sqlite3
-
SELECT t.variable, t.value, d.value state FROM `globals` t JOIN (SELECT
x.variable, x.value FROM globals x WHERE x.variable LIKE 'OUTDISABLE\_%') d
ON substring(t.variable,5) = substring(d.variable,12) WHERE t.variable LIKE
'OUT\_%' UNION ALL SELECT v.variable, v.value,
concat(substring(v.value,1,0),'off')
state FROM `globals` v WHERE v.variable LIKE 'OUT\_%' AND
concat('OUTDISABLE_',substring(v.variable,5)) NOT IN ( SELECT variable from
globals WHERE variable LIKE 'OUTDISABLE\_%' ) ORDER BY variable
---

above sql working very well at mysql, but at sqlite3 will show:
---
SQL error: ORDER BY term number 1 does not match any result column
---

the globals table is
--
CREATE TABLE `globals` (
  `variable` char(20) NOT NULL default '',
  `value` char(50) NOT NULL default '',
  PRIMARY KEY  (`variable`)
) ;
-
and insert some contents to this table
--
INSERT INTO `globals` VALUES ('CALLFILENAME','\"\"');
INSERT INTO `globals` VALUES ('DIAL_OPTIONS','tr');
INSERT INTO `globals` VALUES ('TRUNK_OPTIONS','');
INSERT INTO `globals` VALUES ('DIAL_OUT','9');
INSERT INTO `globals` VALUES ('FAX','');
INSERT INTO `globals` VALUES ('FAX_RX','system');
INSERT INTO `globals` VALUES ('FAX_RX_EMAIL','[EMAIL PROTECTED]');
INSERT INTO `globals` VALUES ('FAX_RX_FROM','[EMAIL PROTECTED]');
INSERT INTO `globals` VALUES ('INCOMING','group-all');
INSERT INTO `globals` VALUES ('NULL','\"\"');
INSERT INTO `globals` VALUES ('OPERATOR','');
INSERT INTO `globals` VALUES ('OPERATOR_XTN','');
INSERT INTO `globals` VALUES ('PARKNOTIFY','SIP/200');
INSERT INTO `globals` VALUES ('RECORDEXTEN','\"\"');
INSERT INTO `globals` VALUES ('RINGTIMER','15');
INSERT INTO `globals` VALUES ('DIRECTORY','last');
INSERT INTO `globals` VALUES ('AFTER_INCOMING','');
INSERT INTO `globals` VALUES ('IN_OVERRIDE','forcereghours');
INSERT INTO `globals` VALUES ('REGTIME','7:55-17:05');
INSERT INTO `globals` VALUES ('REGDAYS','mon-fri');
INSERT INTO `globals` VALUES ('DIRECTORY_OPTS','');
INSERT INTO `globals` VALUES ('DIALOUTIDS','1');
INSERT INTO `globals` VALUES ('OUT_1','ZAP/g0');
INSERT INTO `globals` VALUES ('VM_PREFIX','*');
INSERT INTO `globals` VALUES ('VM_OPTS','');
INSERT INTO `globals` VALUES ('VM_GAIN','');
INSERT INTO `globals` VALUES ('VM_DDTYPE','u');
INSERT INTO `globals` VALUES ('TIMEFORMAT','kM');
INSERT INTO `globals` VALUES ('TONEZONE','us');
INSERT INTO `globals` VALUES ('ALLOW_SIP_ANON','no');
INSERT INTO `globals` VALUES ('VMX_CONTEXT','from-internal');
INSERT INTO `globals` VALUES ('VMX_PRI','1');
INSERT INTO `globals` VALUES ('VMX_TIMEDEST_CONTEXT','');
INSERT INTO `globals` VALUES ('VMX_TIMEDEST_EXT','dovm');
INSERT INTO `globals` VALUES ('VMX_TIMEDEST_PRI','1');
INSERT INTO `globals` VALUES ('VMX_LOOPDEST_CONTEXT','');
INSERT INTO `globals` VALUES ('VMX_LOOPDEST_EXT','dovm');
INSERT INTO `globals` VALUES ('VMX_LOOPDEST_PRI','1');
INSERT INTO `globals` VALUES ('VMX_OPTS_TIMEOUT','');
INSERT INTO `globals` VALUES ('VMX_OPTS_LOOP','');
INSERT INTO `globals` VALUES ('VMX_OPTS_DOVM','');
INSERT INTO `globals` VALUES ('VMX_TIMEOUT','2');
INSERT INTO `globals` VALUES ('VMX_REPEAT','1');
INSERT INTO `globals` VALUES ('VMX_LOOPS','1');
INSERT INTO `globals` VALUES ('TRANSFER_CONTEXT','from-internal-xfer');
-

how i can change the sql for sqlite3, any idea, thanks

-- 
Li YuQian
Your Astfin team
___
uClinux/Asterisk distribution for Blackfin CPU
http://www.ucpbx.com
http://astfin.org
http://sourceforge.net/projects/astfin/
___
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table xBestIndex and when to free index resource

2008-02-11 Thread Dan

On Feb 12, 2008, at 7:05 AM, Evans, Mark (Tandem) wrote:

> SQLite experts:
>
> The xBestIndex method of the SQLite virtual table interface  
> implemented by the VT module returns an output to the core by  
> setting idxNum member var of struct sqlite3_index_info to a value  
> that is meaningful to the VT module.  Assume that a memory resource  
> was created in conjunction with the chosen index that will hold  
> information passed by xFilter.
>
> The question is:  How can VT module tell when it is safe to release  
> that resource?  I'm thinking, it's when the associated statement is  
> finalized.  But how does the VT module know that?  I have found  
> that xClose() call is not the answer because I have stumbled on a  
> test sequence that shows this to be unsafe:
>
>
> do_test update-1.0 {
> execsql {DELETE FROM t1}
> execsql {insert into t1 values(1,2,3)}
> execsql {SELECT * FROM t1 }
> execsql {UPDATE t1 SET y=3 WHERE x=1}
> execsql {SELECT * FROM t1 }
> } {1 2 3 1 3 3}
>
> After execution of the UPDATE, the VT module call sequence for the  
> next SELECT does not include xBestIndex as I was expecting.  It  
> calls xFilter with the idxNum that the previous SELECT created (I  
> think).  I crash and burn because I released the index resource in  
> the xClose call for the first SELECT.
>
> I'd be most appreciative if an expert could steer me in the right  
> direction.

Executing an SQL statement is broken into two parts: compilation
(sqlite3_prepare()) to virtual machine code and execution of that
virtual machine code (sqlite3_step()). The xBestIndex() method is
called as part of compilation, xFilter() is called as part of
execution. As is xClose().

The first time your SELECT statement is run the Tcl interface
calls sqlite3_prepare() to compile it, then
sqlite3_step()/sqlite3_reset() to execute it. The second time,
it is able to re-use the compiled statement. That is why xBestIndex
is not called for the second SELECT.

For passing context, you can also use the sqlite3_index_info.idxStr
variable. Set this to point at a string allocated by sqlite3_malloc()
and sqlite will automatically free it when it is no longer required.
This allows you to store a blob of context data instead of a single
integer.

If you need some resource that really does require a destructor (a
connection handle to some other database etc.), do not open it in
xBestIndex(). Open it in xFilter() and close it in xClose(). Each
xFilter() call should be matched by exactly one xClose().

Regards,
Dan.

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

[sqlite] build system type... x86_64-unknown-linux-gnu

2008-02-11 Thread Jim Dodgen

I get this when I configure from the source tarball for 3.5.6

Is this importaint? Can I fix it?

./configure
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking for gcc... gcc
checking for C compiler default output file name... a.out
...


I'm on RHES5 64bit. twin dual core Xeon's

Thanks

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

[sqlite] Blob incremental i/o via Python

2008-02-11 Thread Norman Young

The documentation outlines the C interface for incremental blob
input/output, and mentions the C typedef for a blob handle.

http://www.sqlite.org/c3ref/blob_open.html
http://www.sqlite.org/c3ref/blob.html

typedef struct sqlite3_blob sqlite3_blob;


Can this same interface be accessed in Python?

My application manipulates image and audio content. However, on my
constrained platform (Python 2.5, sqlite3, Maemo, Nokia Internet Tablet),
reading and writing the media data via the conventional blob interface (as
illustrated in teh following www.initd.org SnippetsBlobs.py example) could
consume excessive memory. Specifically, all of the binary data are read into
blobdata object at once, via the read() call. Since the media files can be
arbitrarily large, this call could easily exceed available memory.

*blob*data = *open*('c:\\*sqlite*3\\img.jpg','rb').read()

con = *sqlite*.connect(':memory:')
cur = con.cursor()

cur.execute("Create table picture_table(images)")
cur.execute("Insert into picture_table(images) values
(?)",(*sqlite*.Binary(*blob*data),))
con.commit()


Instead, I need to incrementally read and write media data to a blob, to
avoid consuming arbitrary memory.

Can this be done from Python via the sqlite3 module? Can you point me to
examples?

Thanks.

Norm.

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

Re: [sqlite] Updatable views

2008-02-11 Thread BareFeet

Hi Dennis,

> I don't think there is any way in SQL to eliminate the redundant  
> lookups.

OK, thanks. I thought I saw syntax of some other SQL engines that  
permit if/then or case/when type branching within a trigger, but I may  
be mistaken.

>>  where new.Amount not null
>>  and new.ID not in ( select ID from [Orders Refunds Amount] )

> The only thing I see that could be done differently is to change the
> where clause in the insert case to use a correlated subquery.

>   select
> new.ID
>   , new.Amount
>   where new.Amount not null
>   and not exists
>   ( select ID from [Orders Refunds Amount]
>   where ID = new.ID)
>   ;
>
> This exists clause will exit the subquery as soon as it finds a  
> matching ID. It will also be able to use the index on the ID to do  
> an O(logN) lookup to find or eliminate the new.ID value.

OK, thanks. I didn't realize that "not exists" in this context would  
be faster than my original "not in" syntax. I thought they'd both exit  
when the first match is found.

> Because all three cases use the same index, all the pages needed  
> should be in the page cache after the first scan, so they should not  
> require any additional I/O, and hence should complete very quickly.

Ah yes, good point :-)

> While SQL does require doing some redundant work, it isn't really  
> that much extra, and I suspect that you would be hard pressed to  
> measure the extra overhead.

Yes, although now I'm keen to see fixed the apparent huge overhead of  
the inefficiency that Steve raised about using updatable views ;-)

Thanks for all your input. I'll hone my updatable views strategy and  
apply it to more cases to find any tangent situations.

Tom
BareFeet

  --
5000 computer accessories delivered anywhere in Australia:
http://www.tandb.com.au/forsale/?sig
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quoting identifiers (was: Updatable views)

2008-02-11 Thread BareFeet

Puneet said:

> Keep SQLite simple, and keep it compliant with whatever the single  
> ANSI SQL standard may be there out there.

Yes, I agree, but only because it seems that SQLite's allowance for  
double quotes for literals (which I have said introduces ambiguity and  
prevents proper error messages) seems to be for the sake of complying  
with a "non-standard" in MySQL. ;-) In other words, if catering for  
non-standards makes it worse, don't do it :-)

Tom
BareFeet

  --
ADSL2+ at the cheapest price in Australia:
http://www.tandb.com.au/broadband/?sig
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting all and some columns

2008-02-11 Thread BareFeet

Hi Jose,

> Class|ProjID|ProjFund|Invoice|Split
> Finishers|1045|73||
> Finishers|1045|75|30|
> Finishers|1045|75|30|
> Finishers|1045|75|30|
> Finishers|1045|75||
> Finishers|1045|75|75|y
> Finishers|1045|75|25|
> Finishers|1045|73||
> Finishers|1045|73||
> Finishers|1045|73||
> Finishers|1045|73|58.4|y
> Finishers|1045|73||

I think in the interests of "normalization" (basically removing  
redundancy and nulls), you'd do best to split the data into four tables:

ProjID   |Class
1045 |Finishers

Fund:
FundID   |ProjID   |ProjFund
1|1045 |73
2|1045 |75
3|1045 |75
4|1045 |75
5|1045 |75
6|1045 |75
7|1045 |75
8|1045 |73
9|1045 |73
10   |1045 |73
11   |1045 |73
12   |1045 |73

Invoice:
InvoiceID|FundID   |Invoice
1|2|30
2|3|30
3|4|30
4|6|75
5|7|25
6|11   |58.4

Split:
InvoiceID
4
6

Then you only record an entry in Split for those rows for which a  
split applies (two rows in this case).

The above translates into this SQL to create the tables:

create table Project
(
  ProjID integer primary key
, Class text collate nocase
)
;
create table Fund
(
  FundID integer primary key
, ProjID integer--> Project.ProjID
, ProjFund real
)
;
create table Invoice
(
  InvoiceID integer primary key
, FundID integer--> Fund.FundID
, Invoice real
)
;
create table Split
(
InvoiceID integer   --> Invoice.InvoiceID
)
;

--Populating the tables with your data:
delete from Project;
delete from Fund;
delete from Invoice;
delete from Split;

insert into Project (ProjID, Class) values (1045, 'Finishers');

insert into Fund (ProjID, ProjFund) values (1045, 73);
insert into Fund (ProjID, ProjFund) values (1045, 75);
insert into Fund (ProjID, ProjFund) values (1045, 75);
insert into Fund (ProjID, ProjFund) values (1045, 75);
insert into Fund (ProjID, ProjFund) values (1045, 75);
insert into Fund (ProjID, ProjFund) values (1045, 75);
insert into Fund (ProjID, ProjFund) values (1045, 75);
insert into Fund (ProjID, ProjFund) values (1045, 73);
insert into Fund (ProjID, ProjFund) values (1045, 73);
insert into Fund (ProjID, ProjFund) values (1045, 73);
insert into Fund (ProjID, ProjFund) values (1045, 73);
insert into Fund (ProjID, ProjFund) values (1045, 73);

insert into Invoice (FundID, Invoice) values (2, 30);
insert into Invoice (FundID, Invoice) values (3, 30);
insert into Invoice (FundID, Invoice) values (4, 30);
insert into Invoice (FundID, Invoice) values (6, 75);
insert into Invoice (FundID, Invoice) values (7, 25);
insert into Invoice (FundID, Invoice) values (11, 58.4);

insert into Split (InvoiceID) values (4);
insert into Split (InvoiceID) values (6);

> --What I would like is to have total of ProjFund


select sum(ProjFund) from Fund;

--> 888.0

> --a total of ProjFund - Invoices which Split = 'y'


select sum(ProjFund - Invoice)
from Split
left join Invoice on Split.InvoiceID = Invoice.InvoiceID
left join Fund on Invoice.FundID = Fund.FundID
;

--> 14.6

> --and a total of Invoices which Split = 'y'.


select sum(Invoice)
from Split
left join Invoice on Split.InvoiceID = Invoice.InvoiceID
;

--> 133.4

I hope this helps. The benefits of normalizing increase with the  
amount and/or complexity of your data. Notice there are no case  
statements to get what you want. You just start with the table you  
want (Split, in this case) and join any needed related data, so SQLite  
only scans the relevant data, rather than testing every row.

Tom
BareFeet

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

Re: [sqlite] Updatable views

2008-02-11 Thread P Kishor

On 2/11/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> John Stanton <[EMAIL PROTECTED]> wrote:
> > That ia a nice idea.  To have a pragma which specied the dialect.  There
> > could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc.
> >   It would give tighter control over hard to track annoying minor syntax
> > errors.
> >
>
> And, it would multiple exponentially the number of test cases
> we have to write and maintain in order to adequately test the
> parser ;-)
>

One of the dangers of supporting "other standards" is that it becomes
hard to wean folks off of them when you do decide to go "pure."

Microsoft is experiencing a similar issue with IE. IE6 buggered up the
standards support royally, but enough people around the world used it
and made websites that were "compliant" with it that when MS made IE7
which hewed to the standards much better, all those websites broke.
So, they are now proposing a new quirks mode in IE8. A nice collection
of links debating both sides is at

Keep SQLite simple, and keep it compliant with whatever the single
ANSI SQL standard may be there out there. Users of other databases
that adhere to the same standards will feel at home. Others will
adapt.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Innovative examples / user stories

2008-02-11 Thread Mohd Radzi Ibrahim

Hi,

I used SQLite to move "data-cube" from server to client. The server hosts 
data in MS SQL databases. There is a server-app that run query based on some 
input send by client program. The server then run MSSQL query and generate a 
SQLite db consisting of some tables (fact, dimensions, etc), zip it and send 
back to the client. The client program then allows user to manipulate 
dimensions (to maybe slice/dice the result) without having to access the 
huge databases on the server anymore...


best regards,
radzi.


- Original Message - 
From: "Lars Aronsson" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, February 12, 2008 1:51 AM
Subject: [sqlite] Innovative examples / user stories


>
> Is there any documentation of how people use SQLite in odd ways in
> their everyday activities?  For example, do you e-mail SQLite DB
> files between you, as if they were Excel spreadsheets?  Or do you
> distribute SQLite database files via BitTorrent?  Even with multi
> table databases? That would be a kind of database use that was
> unheard of in the Oracle/DB2 era, but it certainly should be
> possible with SQLite.  SQLite databases files could be used in a
> "seti @ home" kind of application, where a screensaver downloads
> an existing DB file, processes it for some hours, and then uploads
> the resulting DB file again.  But are any such applications or
> user stories known? Is there a list already?
>
> I've read the Wikipedia article and its list of Google Gears,
> Android, Mac OS X Tiger, etc.  I've also seen the "well-known
> users of SQLite" page on sqlite.org/famous.html and the "When to
> use SQLite" page.
>
> I've used Oracle since version 7 and MySQL since 3.23 (which isn't
> very long at all, but anyway).  The switch from Oracle to MySQL
> was made possible for many people because their database was
> embedded behind a web application anyway (look, no DBA!), so full
> transaction handling wasn't really needed.  That shift in usage
> pattern opened up for a simpler and more affordable solution.
> Many other such technology shifts are described in an old book
> titled "The Innovator's Dilemma".
>
>
> -- 
>  Lars Aronsson ([EMAIL PROTECTED])
>  Aronsson Datateknik - http://aronsson.se
> ___
> 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] View update performance (was: Updatable views)

2008-02-11 Thread Stephen Oberholtzer

On Feb 11, 2008 1:53 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> Stephen Oberholtzer wrote:
> > I should note that there's a gross inefficiency when using triggers to
> > handle updates or deletes against views; SQLite does the equivalent of
> > this:
> >
> > For UPDATE  ...  WHERE ,  SQLite copies the entire
> > source view into the temp table.
> >
> > SELECT * INTO  FROM 
> >
> > Then, it iterates over , looking for rows that match
> > , and *then* runs the trigger on them.  This means that if
> > your source view is large, this will run slowly.
> >
> > I submitted a patch a long while ago to optimize this by turning the
> > initial temp-table population into "SELECT * INTO  FROM
> >  WHERE ", which worked much faster, but I don't think
> > anything came of it.
> >
> > (my original msg to this list:
> > http://readlist.com/lists/sqlite.org/sqlite-users/2/11029.html )
> >
> >
> >
>
> Stephen,
>
> This does seem like a good idea.
>
> The SQLite mailing list doesn't pass files attached to submissions, so
> no one saw your patch. I would suggest creating at ticket at
> http://www.sqlite.org/cvstrac/captcha?nxp=/cvstrac/tktnew and posting
> your message, or a link to it, along with your patch.
>
> I suspect the patch itself will probably have to be modified, since
> SQLite recently underwent significant changes to its code generation
> routines.
>
> As with all patches, it will be reviewed and accepted much faster if it
> passes the test suite.
>
>
> HTH
> Dennis Cote

If only you'd been around when I'd posted my message! Nobody said
*anything*, so I figured nobody else cared about it.

I have reposted my mailing list message, with attachments, here:
http://www.sqlite.org/cvstrac/tktview?tn=2938

I have not updated the patch, however.  It *should* be pretty
straightforward -- looking at it again, it doesn't actually seem to do
any VDBE code itself, so who knows?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

2008-02-11 Thread drh

John Stanton <[EMAIL PROTECTED]> wrote:
> That ia a nice idea.  To have a pragma which specied the dialect.  There 
> could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. 
>   It would give tighter control over hard to track annoying minor syntax 
> errors.
> 

And, it would multiple exponentially the number of test cases
we have to write and maintain in order to adequately test the
parser ;-)

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] Updatable views

2008-02-11 Thread John Stanton

That ia a nice idea.  To have a pragma which specied the dialect.  There 
could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. 
  It would give tighter control over hard to track annoying minor syntax 
errors.

Dennis Cote wrote:
> BareFeet wrote:
>> No, I have no MS legacy or habits. I use the  
>> square brackets for identifiers because I find that using double  
>> quotes doesn't catch errors. If I say select "column name that does  
>> not exist" I get a string back. But if I use square brackets SQLite  
>> gives me an error that the column doesn't exist, which is far more  
>> useful, especially when embedded in some function.
>>
> 
> Tom,
> 
> Yes I'm aware of that issue. It's an unfortunate side effect of SQLite's 
> mySQL compatibility extension that supports double quotes for string 
> literals. It's sad that because of this extension, you need to use the 
> MS compatible extension for identifiers so that you get useful error 
> messages.
> 
> It would be nice if SQLite added a pragma that could be set to disable 
> the non-standard extensions. It would have to default off for backwards 
> compatibility of course. This would allow users to use only the SQL 
> standard quoting rules, and still get proper error messages if they make 
> a typographical error.
> 
> Dennis Cote
> ___
> 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

[sqlite] Virtual table xBestIndex and when to free index resource

2008-02-11 Thread Evans, Mark (Tandem)

SQLite experts:

The xBestIndex method of the SQLite virtual table interface implemented by the 
VT module returns an output to the core by setting idxNum member var of struct 
sqlite3_index_info to a value that is meaningful to the VT module.  Assume that 
a memory resource was created in conjunction with the chosen index that will 
hold information passed by xFilter.

The question is:  How can VT module tell when it is safe to release that 
resource?  I'm thinking, it's when the associated statement is finalized.  But 
how does the VT module know that?  I have found that xClose() call is not the 
answer because I have stumbled on a test sequence that shows this to be unsafe:


do_test update-1.0 {
execsql {DELETE FROM t1}
execsql {insert into t1 values(1,2,3)}
execsql {SELECT * FROM t1 }
execsql {UPDATE t1 SET y=3 WHERE x=1}
execsql {SELECT * FROM t1 }
} {1 2 3 1 3 3}

After execution of the UPDATE, the VT module call sequence for the next SELECT 
does not include xBestIndex as I was expecting.  It calls xFilter with the 
idxNum that the previous SELECT created (I think).  I crash and burn because I 
released the index resource in the xClose call for the first SELECT.

I'd be most appreciative if an expert could steer me in the right direction.

Mark

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

Re: [sqlite] Updatable views

2008-02-11 Thread Dennis Cote

BareFeet wrote:
> Hi All (especially Dennis I guess ;-) )
> 
> Focusing on the "instead of update" trigger in my example (copied  
> below), you'll notice that the trigger scans through the table [Orders  
> Refunds Amount] three times to find the matching ID, once for each of  
> insert, delete, update, whose where tests contain the following  
> (partial):
> 
> insert: where new.ID not in ( select ID from [Orders Refunds Amount] )
> delete: where [Orders Refunds Amount].ID = new.ID
> update: where [Orders Refunds Amount].ID = new.ID
> 
> When you look at the complete where clauses in each, you'll realize  
> that, by design, only one of the where clauses is ever true. So it  
> seems inefficient for the trigger to test three times. Is there a more  
> efficient method?
> 
> Quoting the relevant section of my original post:
> 
>> Now for updating the Paying and Amount fields. Again, instead of the  
>> view, I want to update the corresponding joined table for each.  
>> Paying has to convert a text label to the related integer value.  
>> Because the [Orders Refunds Paying] and [Orders Refunds Amount]  
>> tables are designed to only contain a row if their value is not  
>> null, I have to:
>>
>> 1. Insert a row if it doesn't already exist for that ID, or
>> 2. Delete the row if the value has changed to null, or
>> 3. Simply update the value if the row exists and the new value is  
>> not null.
>>
>> So I have one trigger ... which performs either an insert, delete or  
>> update:
> 
>> create trigger [Update Orders Refunds Joined Amount]
>> instead of update of Amount
>> on [Orders Refunds Joined]
>> for each row
>> begin
>>  -- insert if row doesn't exist and new value not null
>>  insert into [Orders Refunds Amount]
>>  (
>>ID
>>  , Amount
>>  )
>>  select
>>new.ID
>>  , new.Amount
>>  where new.Amount not null
>>  and new.ID not in ( select ID from [Orders Refunds Amount] )
>>  ;
>>  -- delete if row exists and new value is null
>>  delete from [Orders Refunds Amount]
>>  where new.Amount is null
>>  and [Orders Refunds Amount].ID = new.ID
>>  ;
>>  -- update if row exists and new value not null
>>  update [Orders Refunds Amount]
>>  set Amount = new.Amount
>>  where new.Amount not null
>>  and [Orders Refunds Amount].ID = new.ID
>>  ;
>> end
>> ;
> 

Tom,

I don't think there is any way in SQL to eliminate the redundant lookups.

The only thing I see that could be done differently is to change the 
where clause in the insert case to use a correlated subquery.

insert into [Orders Refunds Amount]
(
  ID
, Amount
)
select
  new.ID
, new.Amount
where new.Amount not null
and not exists
( select ID from [Orders Refunds Amount]
where ID = new.ID)
;

This exists clause will exit the subquery as soon as it finds a matching 
ID. It will also be able to use the index on the ID to do an O(logN) 
lookup to find or eliminate the new.ID value.

The other cases already do O(logN) searches in the index to find the row 
to delete or update, and only need to do a very fast isnull check on 
that one row.

Because all three cases use the same index, all the pages needed should 
be in the page cache after the first scan, so they should not require 
any additional I/O, and hence should complete very quickly.

While SQL does require doing some redundant work, it isn't really that 
much extra, and I suspect that you would be hard pressed to measure the 
extra overhead.

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

Re: [sqlite] replacing all newlines in a field

2008-02-11 Thread P Kishor

On 2/11/08, BareFeet <[EMAIL PROTECTED]> wrote:
> Hi Puneet,
>
> > I have a db in which the data in some fields in some tables has
> > newlines (line breaks). All is fine until I want to export this into
> > a CSV or tab-delimited format in which case the line breaks mess up
> > the
> > row-by-row records.
>
> How does it mess it up? CSV facilitates newlines in the data by
> wrapping in quotes any cells containing them. I just did a quick text
> using:
>
> .mode csv
> select MyField from MyTable;
>
> where data in myField contains newlines etc, and SQLite seemed to
> output correctly, wrapping those data elements in double quotes.
>
> into what other program do you want to import the resulting CSV? All
> software that properly handles CSV importing should handle the output
> from SQLite correctly (according to by brief test).

I don't know what I was smoking. You are very correct.

Ok. This problem solved. I don't have to scramble these eggs. Onto the
next one in a new thread.


>
> > I am thinking, maybe I should not allow storing line breaks in the
> > database
>
> I think that's extreme and starts scrambling the egg.
>
> Tom
> BareFeet
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting all and some columns

2008-02-11 Thread Igor Tandetnik

jose isaias cabrera <[EMAIL PROTECTED]>
wrote:
> Greetings...
>
> I know that Puneet will get on my case about the obscurity of the
> subject (just kidding), but I am trying to find out if I can do this:
> Imagine this table and data...
>
> Class|ProjID|ProjFund|Invoice|Split
> Finishers|1045|73||
> Finishers|1045|75|30|
> Finishers|1045|75|30|
> Finishers|1045|75|30|
> Finishers|1045|75||
> Finishers|1045|75|75|y
> Finishers|1045|75|25|
> Finishers|1045|73||
> Finishers|1045|73||
> Finishers|1045|73||
> Finishers|1045|73|58.4|y
> Finishers|1045|73||
>
> What I would like is to have total of ProjFund, a total of ProjFund -
> Invoices which Split = 'y' and a total of Invoices which Split = 'y'.
>
> I know I can do this programatically, but I would like to be able to
> have sqlite return the results to me.  Is it possible?  What I have
> right now is this,
>
> SELECT Class, sum(ProjFund), sum(ProjFund) - sum(invoice),
> sum(invoices) from ClassTable  group by Class, ProjID;
>
> I just don-t know how to do the Split = 'y' part.  Any help would be
> greatly appreciated.

Perhaps something like this:

SELECT Class,
sum(ProjFund),
sum(ProjFund) - sum(case split when 'y' then invoice else 0 end),
sum(case split when 'y' then invoice else 0 end)
from ClassTable  group by Class, ProjID;

Igor Tandetnik 



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

Re: [sqlite] Selecting all and some columns

2008-02-11 Thread P Kishor

On 2/11/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
>
> Greetings...
>
> I know that Puneet will get on my case about the obscurity of the subject
> (just kidding), but I am trying to find out if I can do this: Imagine this
> table and data...
>
> Class|ProjID|ProjFund|Invoice|Split
> Finishers|1045|73||
> Finishers|1045|75|30|
> Finishers|1045|75|30|
> Finishers|1045|75|30|
> Finishers|1045|75||
> Finishers|1045|75|75|y
> Finishers|1045|75|25|
> Finishers|1045|73||
> Finishers|1045|73||
> Finishers|1045|73||
> Finishers|1045|73|58.4|y
> Finishers|1045|73||
>
> What I would like is to have total of ProjFund, a total of ProjFund -
> Invoices which Split = 'y' and a total of Invoices which Split = 'y'.
>
> I know I can do this programatically, but I would like to be able to have
> sqlite return the results to me.  Is it possible?  What I have right now is
> this,
>
> SELECT Class, sum(ProjFund), sum(ProjFund) - sum(invoice), sum(invoices)
> from ClassTable  group by Class, ProjID;
>
> I just don-t know how to do the Split = 'y' part.  Any help would be greatly
> appreciated.
>


is this what you are looking for?

sqlite> select * from t;
class   projid  projfundinvoice split
--  --  --  --  --
Finishers   104573
Finishers   104575  30
Finishers   104575  30
Finishers   104575  30
Finishers   104575
Finishers   104575  75  y
Finishers   104575  25
Finishers   104573
Finishers   104573
Finishers   104573
Finishers   104573  58.4y
Finishers   104573
sqlite> select class, sum(projfund) from t group by class;
class   sum(projfund)
--  -
Finishers   888
sqlite> select class, sum(projfund) from t where split = 'y' group by class;
class   sum(projfund)
--  -
Finishers   148
sqlite> select class, sum(projfund), sum(projfund)-sum(invoice) as a
from t where split = 'y' group by class;
class   sum(projfund)  a
--  -  --
Finishers   14814.6
sqlite> select class, sum(projfund), sum(projfund)-sum(invoice) a,
sum(invoice) b from t where split = 'y' group by class;
class   sum(projfund)  a   b
--  -  --  --
Finishers   14814.6133.4
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

2008-02-11 Thread BareFeet

Hi All (especially Dennis I guess ;-) )

Focusing on the "instead of update" trigger in my example (copied  
below), you'll notice that the trigger scans through the table [Orders  
Refunds Amount] three times to find the matching ID, once for each of  
insert, delete, update, whose where tests contain the following  
(partial):

insert: where new.ID not in ( select ID from [Orders Refunds Amount] )
delete: where [Orders Refunds Amount].ID = new.ID
update: where [Orders Refunds Amount].ID = new.ID

When you look at the complete where clauses in each, you'll realize  
that, by design, only one of the where clauses is ever true. So it  
seems inefficient for the trigger to test three times. Is there a more  
efficient method?

Quoting the relevant section of my original post:

> Now for updating the Paying and Amount fields. Again, instead of the  
> view, I want to update the corresponding joined table for each.  
> Paying has to convert a text label to the related integer value.  
> Because the [Orders Refunds Paying] and [Orders Refunds Amount]  
> tables are designed to only contain a row if their value is not  
> null, I have to:
>
> 1. Insert a row if it doesn't already exist for that ID, or
> 2. Delete the row if the value has changed to null, or
> 3. Simply update the value if the row exists and the new value is  
> not null.
>
> So I have one trigger ... which performs either an insert, delete or  
> update:

> create trigger [Update Orders Refunds Joined Amount]
> instead of update of Amount
> on [Orders Refunds Joined]
> for each row
> begin
>   -- insert if row doesn't exist and new value not null
>   insert into [Orders Refunds Amount]
>   (
> ID
>   , Amount
>   )
>   select
> new.ID
>   , new.Amount
>   where new.Amount not null
>   and new.ID not in ( select ID from [Orders Refunds Amount] )
>   ;
>   -- delete if row exists and new value is null
>   delete from [Orders Refunds Amount]
>   where new.Amount is null
>   and [Orders Refunds Amount].ID = new.ID
>   ;
>   -- update if row exists and new value not null
>   update [Orders Refunds Amount]
>   set Amount = new.Amount
>   where new.Amount not null
>   and [Orders Refunds Amount].ID = new.ID
>   ;
> end
> ;

Thanks,
Tom
BareFeet

--
Widest range of Macs & accessories in Australia:
http://www.tandb.com.au/forsale/?sig

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

[sqlite] Selecting all and some columns

2008-02-11 Thread jose isaias cabrera


Greetings...

I know that Puneet will get on my case about the obscurity of the subject 
(just kidding), but I am trying to find out if I can do this: Imagine this 
table and data...

Class|ProjID|ProjFund|Invoice|Split
Finishers|1045|73||
Finishers|1045|75|30|
Finishers|1045|75|30|
Finishers|1045|75|30|
Finishers|1045|75||
Finishers|1045|75|75|y
Finishers|1045|75|25|
Finishers|1045|73||
Finishers|1045|73||
Finishers|1045|73||
Finishers|1045|73|58.4|y
Finishers|1045|73||

What I would like is to have total of ProjFund, a total of ProjFund - 
Invoices which Split = 'y' and a total of Invoices which Split = 'y'.

I know I can do this programatically, but I would like to be able to have 
sqlite return the results to me.  Is it possible?  What I have right now is 
this,

SELECT Class, sum(ProjFund), sum(ProjFund) - sum(invoice), sum(invoices) 
from ClassTable  group by Class, ProjID;

I just don-t know how to do the Split = 'y' part.  Any help would be greatly 
appreciated.

thanks,

josé


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

[sqlite] Quoting identifiers (was: Updatable views)

2008-02-11 Thread BareFeet

Hi Dennis,

>> I use the  square brackets for identifiers because I find that  
>> using double quotes doesn't catch errors. If I say select "column  
>> name that does not exist" I get a string back. But if I use square  
>> brackets SQLite
>> gives me an error that the column doesn't exist, which is far more  
>> useful, especially when embedded in some function.
>>

> Yes I'm aware of that issue. It's an unfortunate side effect of  
> SQLite's mySQL compatibility extension that supports double quotes  
> for string literals.

Oh I see. I suspected it was due to some legacy compatibility.

> It's sad that because of this extension, you need to use the MS  
> compatible extension for identifiers so that you get useful error  
> messages.

Yes, there's a certain irony there ;-)

I use the square brackets to remove ambiguity and give useful errors,  
such as when an intermediate view is inadvertently dropped.

> It would be nice if SQLite added a pragma that could be set to  
> disable the non-standard extensions.

> This would allow users to use only the SQL standard quoting rules,  
> and still get proper error messages if they make a typographical  
> error.

Sounds great :-) I'd be happy to use double quotes for identifiers if  
I could be sure that SQLite would give me an error if that identifier  
doesn't exist.

Thanks,
Tom
BareFeet

--
Best value broadband in Australia.
http://www.tandb.com.au/broadband/?sig

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

Re: [sqlite] Updatable views

2008-02-11 Thread Dennis Cote

BareFeet wrote:
> 
>> Since you can't use a create table statement in a trigger,
> 
> Yes, I thought of doing that and realized the limitation of triggers  
> not allowing create temporary table. Is this restriction part of  
> standard SQL, or just SQLite? Is it likely to change? It seems a  
> common need.
> 

Tom,

Standard SQL doesn't allow any SQL-Schema statements (basically any 
create or drop statements) in a trigger body. I doubt this will ever change.

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

Re: [sqlite] Updatable views

2008-02-11 Thread BareFeet

Hi Stephen,

> I should note that there's a gross inefficiency when using triggers  
> to handle updates or deletes against views; SQLite does the  
> equivalent of this:
>
> For UPDATE  ...  WHERE ,  SQLite copies the entire  
> source view into the temp table.
>
> SELECT * INTO  FROM 
>
> Then, it iterates over , looking for rows that match  
> , and *then* runs the trigger on them.  This means that  
> if your source view is large, this will run slowly.

That's horrendous, and surprisingly (and exponentially) inefficient  
for such otherwise efficient software.

> I submitted a patch a long while ago to optimize this by turning the  
> initial temp-table population into "SELECT * INTO  FROM  
>  WHERE ", which worked much faster

Great thinking.

> but I don't think anything came of it.

I'm hoping it will now that Dennis has suggested mechanisms ;-) Thanks  
for your efforts.

Thanks,
Tom
BareFeet

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

Re: [sqlite] Updatable views

2008-02-11 Thread Dennis Cote

BareFeet wrote:
> 
> No, I have no MS legacy or habits. I use the  
> square brackets for identifiers because I find that using double  
> quotes doesn't catch errors. If I say select "column name that does  
> not exist" I get a string back. But if I use square brackets SQLite  
> gives me an error that the column doesn't exist, which is far more  
> useful, especially when embedded in some function.
> 

Tom,

Yes I'm aware of that issue. It's an unfortunate side effect of SQLite's 
mySQL compatibility extension that supports double quotes for string 
literals. It's sad that because of this extension, you need to use the 
MS compatible extension for identifiers so that you get useful error 
messages.

It would be nice if SQLite added a pragma that could be set to disable 
the non-standard extensions. It would have to default off for backwards 
compatibility of course. This would allow users to use only the SQL 
standard quoting rules, and still get proper error messages if they make 
a typographical error.

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

Re: [sqlite] replacing all newlines in a field

2008-02-11 Thread BareFeet

Hi Puneet,

> I have a db in which the data in some fields in some tables has  
> newlines (line breaks). All is fine until I want to export this into  
> a CSV or tab-delimited format in which case the line breaks mess up  
> the
> row-by-row records.

How does it mess it up? CSV facilitates newlines in the data by  
wrapping in quotes any cells containing them. I just did a quick text  
using:

.mode csv
select MyField from MyTable;

where data in myField contains newlines etc, and SQLite seemed to  
output correctly, wrapping those data elements in double quotes.

into what other program do you want to import the resulting CSV? All  
software that properly handles CSV importing should handle the output  
from SQLite correctly (according to by brief test).

> I am thinking, maybe I should not allow storing line breaks in the  
> database

I think that's extreme and starts scrambling the egg.

Tom
BareFeet

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

Re: [sqlite] replacing all newlines in a field

2008-02-11 Thread Griggs, Donald

Hi Puneet,

Question: Is there a way I can search and replace all the line breaks?

x'hh' should work, where hh are hex digits.  So if your file contained
hex 0D type line breaks:

UPDATE t SET essay_without_newlines = Replace(essay, x'0D', '~');  



This email and any attachments have been scanned for known viruses using 
multiple scanners. We believe that this email and any attachments are virus 
free, however the recipient must take full responsibility for virus checking. 
This email message is intended for the named recipient only. It may be 
privileged and/or confidential. If you are not the named recipient of this 
email please notify us immediately and do not copy it or use it for any 
purpose, nor disclose its contents to any other person.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] replacing all newlines in a field

2008-02-11 Thread P Kishor

I have a db in which the data in some fields in some tables has
newlines (line breaks). All is fine until I want to export this into a
CSV or tab-delimited format in which case the line breaks mess up the
row-by-row records. I am thinking, maybe I should not allow storing
line breaks in the database (the fields are populated via a web
application) and instead, I should store some other "restricted (by
me)" character, and replace that character when I have to display the
content of these fields. That would allow me to do exports of the
entire db easily and accurately.

Question: Is there a way I can search and replace all the line breaks? So, given

CREATE TABLE t (id INTEGER, essay TEXT);

I'd like to

ALTER TABLE t ADD COLUMN essay_without_newlines TEXT;

UPDATE t SET essay_without_newlines = Replace(essay, '\n', '~');

where ~ happens to be my proxy for a newline

One, I am not quite  sure how to identify these newlines. A simple

SELECT id FROM t WHERE essay LIKE '%\n%';

returns nothing at all. Since the values have been inserted via the
web, I am not even sure if they are \n or \r\n (Unixy or Mac-y or
Window-y).

Suggestions? Or, any other way to handle this problem, perhaps more
elegantly than what I am thinking of?

Many thanks in advance,


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

[sqlite] test post via gmane

2008-02-11 Thread Adam Megacz


Hopefully now that sqlite-users is on mailman it will take posts sent
via gmane.  If this message shows up, then that is the case.

  - a


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

Re: [sqlite] Updatable views

2008-02-11 Thread BareFeet

Hi Dennis,

Thanks for your reply. I really appreciate the feedback.

> This is a very nice set of triggers to handle the base tables of a  
> view
> I believe this is the best way to handle this.

Thanks, it's good to at least know that I'm heading the right way.

I believe this is the best way to handle this.

> (except for all the non-standard MS style quoting of identifiers  
> using square brackets :-)).

Wash your mouth ;-) No, I have no MS legacy or habits. I use the  
square brackets for identifiers because I find that using double  
quotes doesn't catch errors. If I say select "column name that does  
not exist" I get a string back. But if I use square brackets SQLite  
gives me an error that the column doesn't exist, which is far more  
useful, especially when embedded in some function.

> The only problem I see is with your use of the last_insert_rowid()  
> function in the instead of insert trigger.

Well spotted.

> If your new row has non-default values for both the Paying and  
> Amount fields, you will have a problem. After the insert into "Order  
> Refunds Paying" the value returned by last_insert_rowid() changes to  
> the rowid of the newly inserted row in that table.

I actually tested that scenario (and others) and it seems to work. It  
works in this case because all the joined tables have the same rowid  
for related records. So each insert in the subsequent tables is using  
the same rowid, so last_insert_rowid() gives the same result after  
each insert.

> You need to call last_insert_rowid() once and save the result for  
> use in both the subsequent inserts.

Yes. I will have to do that if the joined tables don't have the same  
rowid for related records (which is not the case in this example).

> Since you can't use a create table statement in a trigger,

Yes, I thought of doing that and realized the limitation of triggers  
not allowing create temporary table. Is this restriction part of  
standard SQL, or just SQLite? Is it likely to change? It seems a  
common need.

> you have to create a table to use for this purpose, which you can  
> update and query in the trigger. This could be a temporary table  
> that is created and initialized when the database is opened.

Oh I see, yes, I guess I could prefix the initiating insert in my  
program with a create temporary table, but since that would be outside  
of the SQL, my database would lose portability. That is to say, if I  
use my database outside of my program, such as via the command line,  
then inserting into the view will fail with some error like "table  
'New ID' doesn't exist".

In order to facilitate portability (ie my database will function in  
any environment, not just when running in my program), I guess I'll  
have to create a permanent (ie not temporary) table to track the  
last_row_id of each table.

>
Thanks,
Tom

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

Re: [sqlite] Updatable views

2008-02-11 Thread Dennis Cote

Stephen Oberholtzer wrote:
> I should note that there's a gross inefficiency when using triggers to
> handle updates or deletes against views; SQLite does the equivalent of
> this:
> 
> For UPDATE  ...  WHERE ,  SQLite copies the entire
> source view into the temp table.
> 
> SELECT * INTO  FROM 
> 
> Then, it iterates over , looking for rows that match
> , and *then* runs the trigger on them.  This means that if
> your source view is large, this will run slowly.
> 
> I submitted a patch a long while ago to optimize this by turning the
> initial temp-table population into "SELECT * INTO  FROM
>  WHERE ", which worked much faster, but I don't think
> anything came of it.
> 
> (my original msg to this list:
> http://readlist.com/lists/sqlite.org/sqlite-users/2/11029.html )
> 
> 
> 

Stephen,

This does seem like a good idea.

The SQLite mailing list doesn't pass files attached to submissions, so 
no one saw your patch. I would suggest creating at ticket at 
http://www.sqlite.org/cvstrac/captcha?nxp=/cvstrac/tktnew and posting 
your message, or a link to it, along with your patch.

I suspect the patch itself will probably have to be modified, since 
SQLite recently underwent significant changes to its code generation 
routines.

As with all patches, it will be reviewed and accepted much faster if it 
passes the test suite.

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

Re: [sqlite] Innovative examples / user stories

2008-02-11 Thread drh

Lars Aronsson <[EMAIL PROTECTED]> wrote:
> Is there any documentation of how people use SQLite in odd ways in 
> their everyday activities? 

Did you see

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

The document above is not exactly what you are asking for
since it does not list real-world examples, but it does contain
several suggestions on how to best use SQLite.

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] Updatable views

2008-02-11 Thread Stephen Oberholtzer

I should note that there's a gross inefficiency when using triggers to
handle updates or deletes against views; SQLite does the equivalent of
this:

For UPDATE  ...  WHERE ,  SQLite copies the entire
source view into the temp table.

SELECT * INTO  FROM 

Then, it iterates over , looking for rows that match
, and *then* runs the trigger on them.  This means that if
your source view is large, this will run slowly.

I submitted a patch a long while ago to optimize this by turning the
initial temp-table population into "SELECT * INTO  FROM
 WHERE ", which worked much faster, but I don't think
anything came of it.

(my original msg to this list:
http://readlist.com/lists/sqlite.org/sqlite-users/2/11029.html )



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Innovative examples / user stories

2008-02-11 Thread Lars Aronsson


Is there any documentation of how people use SQLite in odd ways in 
their everyday activities?  For example, do you e-mail SQLite DB 
files between you, as if they were Excel spreadsheets?  Or do you 
distribute SQLite database files via BitTorrent?  Even with multi 
table databases? That would be a kind of database use that was 
unheard of in the Oracle/DB2 era, but it certainly should be 
possible with SQLite.  SQLite databases files could be used in a 
"seti @ home" kind of application, where a screensaver downloads 
an existing DB file, processes it for some hours, and then uploads 
the resulting DB file again.  But are any such applications or 
user stories known? Is there a list already?

I've read the Wikipedia article and its list of Google Gears, 
Android, Mac OS X Tiger, etc.  I've also seen the "well-known 
users of SQLite" page on sqlite.org/famous.html and the "When to 
use SQLite" page.

I've used Oracle since version 7 and MySQL since 3.23 (which isn't 
very long at all, but anyway).  The switch from Oracle to MySQL 
was made possible for many people because their database was 
embedded behind a web application anyway (look, no DBA!), so full 
transaction handling wasn't really needed.  That shift in usage 
pattern opened up for a simpler and more affordable solution.  
Many other such technology shifts are described in an old book 
titled "The Innovator's Dilemma".


-- 
  Lars Aronsson ([EMAIL PROTECTED])
  Aronsson Datateknik - http://aronsson.se
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Join trouble

2008-02-11 Thread Dennis Cote

Johnstone, Simon wrote:
> 
> $query = "SELECT a.company, b.company FROM tblusers a inner join
> tblcompany b on (a.company = b.company) ";
> 
> This works but yet again doesn't print any results to the screen.
> 

Simon,

Try the following to see if you have any trailing spaces in your table

select '"' || company || '"' from tblcompany where company like '% ';

and

select '"' || company || '"' from tblusers where company like '% ';

If you have trailing spaces, you might want to cleanup your data by 
removing them before.

The rtrim function is only available in versions of SQLite since 3.5.5. 
Note the trim() function has been around for a long time. It will trim 
both trailing and leading spaces. If you don't need to preserve leading 
spaces, then it should be sufficient to clean up your data.

You can check your version with a simple select.

select sqlite_version();

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

Re: [sqlite] Join trouble

2008-02-11 Thread Fowler, Jeff

As a suggestion, try doing this outside of your application first,
simply by running sqlite3 from the command line. That way you can see if
the problem has is because of something you're doing vs. sqlite itself.
The join syntax I suggested (including the rtrim function) works fine
here using the current release of sqlite, as does the "inner join"
syntax.

- Jeff

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Johnstone, Simon
Sent: Monday, February 11, 2008 11:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Join trouble

I have tried the following but get the following error 

Warning: sqlite_query() [function.sqlite-query]: no such function: rtrim
in C:\Program Files\Apache Software
Foundation\Apache2.2\htdocs\swiftdist\website\database\loginsuccess.php
on line 22
SQL logic error or missing database
SELECT tblusers.company, tblcompany.company FROM tblusers, tblcompany
WHERE rtrim(tblusers.company) = rtrim(tblcompany.company)

How do I create the sql statement so that it includes the functions
I followed the link you suggested but it was a bit too complicated for
me I am a very very new programmer.

I have also tried 

$query = "SELECT a.company, b.company FROM tblusers a inner join
tblcompany b on (a.company = b.company) ";

This works but yet again doesn't print any results to the screen.

Simon Johnstone
Customer Service Data Officer

Tel + 44 (0) 1484 465 500 Ext 5154
Fax: +44 (0) 1484 465 586
E-mail [EMAIL PROTECTED]
Web: www.davidbrown.com

David Brown Engineering Limited
Company No 331925 England
Park Works, Park Road, Huddersfield HD4 5DD England

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Fowler, Jeff
Sent: 11 February 2008 16:17
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Join trouble

Simon,

If the query is returning no rows, my guess would be trailing spaces in
the data. This has been discussed at length and there is a new collating
sequence (http://www.sqlite.org/cvstrac/chngview?cn=4732)to address it.
Either use it or try saying:
WHERE rtrim(tblusers.company) = rtrim(tblcompany.company)

- Jeff

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Johnstone, Simon
Sent: Monday, February 11, 2008 11:11 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Join trouble

I am having serious trouble creating a join on my database. I have
tested it in other databases and it works so it must be a problem with
sqlite and the join??? I don't know but am really struggling. 

 

$query = "SELECT name, username, currency, company, salescontact,
orders2006, orders2007, sales2006, sales2007, company FROM tblusers,
tblcompany WHERE tblusers.company = tblcompany.company";

 

The page loads up but there is just no data from the database, if I use
any of the databases on their own they work fine its only when I join
them

 

Cheers for any help

 

Simon Johnstone

Customer Service Data Officer

 

Tel + 44 (0) 1484 465 500 Ext 5154

Fax: +44 (0) 1484 465 586

E-mail [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]> 

Web: www.davidbrown.com http://www.davidbrown.com/> 

 

David Brown Engineering Limited

Company No 331925 England

Park Works, Park Road, Huddersfield HD4 5DD England

 

___
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
___
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] Updatable views

2008-02-11 Thread Dennis Cote

BareFeet wrote:
> create trigger [Insert Orders Refunds Joined]
> instead of insert
> on [Orders Refunds Joined]
> for each row
> begin
>   insert into [Orders Refunds]
>   (
> ID
>   , [Order ID]
>   , Date
>   , Reason
>   )
>   select
> new.ID
>   , new.[Order ID]
>   , julianday( new.Date, 'utc' )  -- convert date from string in  
> localtime to real
>   , new.Reason
>   ;
>   insert into [Orders Refunds Paying]
>   (
> ID
>   , Paying
>   )
>   select
> last_insert_rowid()
>   , ( select ID from Paying where Label = new.Paying )
>   where new.Paying not null
>   ;
>   insert into [Orders Refunds Amount]
>   (
> ID
>   , Amount
>   )
>   select
> last_insert_rowid()
>   , new.Amount
>   where new.Amount not null
>   ;
> end
> ;
> 
> 
> So, is this the best way to tackle the objective of having updatable  
> views? Or is there a better way? Are there any bugs in my approach?  
> Any feedback appreciated.
> 

Tom,

This is a very nice set of triggers to handle the base tables of a view 
(except for all the non-standard MS style quoting of identifiers using 
square brackets :-)). I believe this is the best way to handle this.

The only problem I see is with your use of the last_insert_rowid() 
function in the instead of insert trigger. If your new row has 
non-default values for both the Paying and Amount fields, you will have 
a problem.

After the insert into "Order Refunds Paying" the value returned by 
last_insert_rowid() changes to the rowid of the newly inserted row in 
that table. When you call it again to insert into "Orders Refunds 
Amount" you will get the wrong value (i.e. it is no longer the value of 
the ID column in the newly inserted row in "Orders Refunds" table).

You need to call last_insert_rowid() once and save the result for use in 
both the subsequent inserts. Since you can't use a create table 
statement in a trigger, you have to create a table to use for this 
purpose, which you can update and query in the trigger. This could be a 
temporary table that is created and initialized when the database is 
opened. Then you update and query that table to get the ids used to link 
the joined tables to the new record.

create temp table "New Id" (
"table name"text primary key,
id  integer
);
insert into "New Id" values ('Orders Refunds', null);

create trigger [Insert Orders Refunds Joined]
instead of insert
on [Orders Refunds Joined]
for each row
begin
insert into [Orders Refunds]
(
  ID
, [Order ID]
, Date
, Reason
)
select
  new.ID
, new.[Order ID]
, julianday( new.Date, 'utc' )  -- convert date from string in
localtime to real
, new.Reason
;
update "New Id"
set id = last_insert_rowid()
where "table name" = 'Orders Refunds';
insert into [Orders Refunds Paying]
(
  ID
, Paying
)
select
  ( select id from "New Id" where "table name" = 'Orders 
Refunds' )
, ( select ID from Paying where Label = new.Paying )
where new.Paying not null
;
insert into [Orders Refunds Amount]
(
  ID
, Amount
)
select
  ( select id from "New Id" where "table name" = 'Orders 
Refunds' )
, new.Amount
where new.Amount not null
;
end
;

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

Re: [sqlite] Join trouble

2008-02-11 Thread Johnstone, Simon

I have tried the following but get the following error 

Warning: sqlite_query() [function.sqlite-query]: no such function: rtrim
in C:\Program Files\Apache Software
Foundation\Apache2.2\htdocs\swiftdist\website\database\loginsuccess.php
on line 22
SQL logic error or missing database
SELECT tblusers.company, tblcompany.company FROM tblusers, tblcompany
WHERE rtrim(tblusers.company) = rtrim(tblcompany.company)

How do I create the sql statement so that it includes the functions
I followed the link you suggested but it was a bit too complicated for
me I am a very very new programmer.

I have also tried 

$query = "SELECT a.company, b.company FROM tblusers a inner join
tblcompany b on (a.company = b.company) ";

This works but yet again doesn't print any results to the screen.

Simon Johnstone
Customer Service Data Officer

Tel + 44 (0) 1484 465 500 Ext 5154
Fax: +44 (0) 1484 465 586
E-mail [EMAIL PROTECTED]
Web: www.davidbrown.com

David Brown Engineering Limited
Company No 331925 England
Park Works, Park Road, Huddersfield HD4 5DD England

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Fowler, Jeff
Sent: 11 February 2008 16:17
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Join trouble

Simon,

If the query is returning no rows, my guess would be trailing spaces in
the data. This has been discussed at length and there is a new collating
sequence (http://www.sqlite.org/cvstrac/chngview?cn=4732)to address it.
Either use it or try saying:
WHERE rtrim(tblusers.company) = rtrim(tblcompany.company)

- Jeff

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Johnstone, Simon
Sent: Monday, February 11, 2008 11:11 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Join trouble

I am having serious trouble creating a join on my database. I have
tested it in other databases and it works so it must be a problem with
sqlite and the join??? I don't know but am really struggling. 

 

$query = "SELECT name, username, currency, company, salescontact,
orders2006, orders2007, sales2006, sales2007, company FROM tblusers,
tblcompany WHERE tblusers.company = tblcompany.company";

 

The page loads up but there is just no data from the database, if I use
any of the databases on their own they work fine its only when I join
them

 

Cheers for any help

 

Simon Johnstone

Customer Service Data Officer

 

Tel + 44 (0) 1484 465 500 Ext 5154

Fax: +44 (0) 1484 465 586

E-mail [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]> 

Web: www.davidbrown.com http://www.davidbrown.com/> 

 

David Brown Engineering Limited

Company No 331925 England

Park Works, Park Road, Huddersfield HD4 5DD England

 

___
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Join trouble

2008-02-11 Thread bartsmissaert

Did you try with an inner join with table aliases?
so:
from tblusers a inner join tblcompany b on
(a.company = b.company)

RBS

> I am having serious trouble creating a join on my database. I have
> tested it in other databases and it works so it must be a problem with
> sqlite and the join??? I don't know but am really struggling.
>
>
>
> $query = "SELECT name, username, currency, company, salescontact,
> orders2006, orders2007, sales2006, sales2007, company FROM tblusers,
> tblcompany WHERE tblusers.company = tblcompany.company";
>
>
>
> The page loads up but there is just no data from the database, if I use
> any of the databases on their own they work fine its only when I join
> them
>
>
>
> Cheers for any help
>
>
>
> Simon Johnstone
>
> Customer Service Data Officer
>
>
>
> Tel + 44 (0) 1484 465 500 Ext 5154
>
> Fax: +44 (0) 1484 465 586
>
> E-mail [EMAIL PROTECTED]
> mailto:[EMAIL PROTECTED]>
>
> Web: www.davidbrown.com http://www.davidbrown.com/>
>
>
>
> David Brown Engineering Limited
>
> Company No 331925 England
>
> Park Works, Park Road, Huddersfield HD4 5DD England
>
>
>
> ___
> 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] Join trouble

2008-02-11 Thread Fowler, Jeff

Simon,

If the query is returning no rows, my guess would be trailing spaces in
the data. This has been discussed at length and there is a new collating
sequence (http://www.sqlite.org/cvstrac/chngview?cn=4732)to address it.
Either use it or try saying:
WHERE rtrim(tblusers.company) = rtrim(tblcompany.company)

- Jeff

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Johnstone, Simon
Sent: Monday, February 11, 2008 11:11 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Join trouble

I am having serious trouble creating a join on my database. I have
tested it in other databases and it works so it must be a problem with
sqlite and the join??? I don't know but am really struggling. 

 

$query = "SELECT name, username, currency, company, salescontact,
orders2006, orders2007, sales2006, sales2007, company FROM tblusers,
tblcompany WHERE tblusers.company = tblcompany.company";

 

The page loads up but there is just no data from the database, if I use
any of the databases on their own they work fine its only when I join
them

 

Cheers for any help

 

Simon Johnstone

Customer Service Data Officer

 

Tel + 44 (0) 1484 465 500 Ext 5154

Fax: +44 (0) 1484 465 586

E-mail [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]> 

Web: www.davidbrown.com http://www.davidbrown.com/> 

 

David Brown Engineering Limited

Company No 331925 England

Park Works, Park Road, Huddersfield HD4 5DD England

 

___
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

[sqlite] Join trouble

2008-02-11 Thread Johnstone, Simon

I am having serious trouble creating a join on my database. I have
tested it in other databases and it works so it must be a problem with
sqlite and the join??? I don't know but am really struggling. 

 

$query = "SELECT name, username, currency, company, salescontact,
orders2006, orders2007, sales2006, sales2007, company FROM tblusers,
tblcompany WHERE tblusers.company = tblcompany.company";

 

The page loads up but there is just no data from the database, if I use
any of the databases on their own they work fine its only when I join
them

 

Cheers for any help

 

Simon Johnstone

Customer Service Data Officer

 

Tel + 44 (0) 1484 465 500 Ext 5154

Fax: +44 (0) 1484 465 586

E-mail [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]> 

Web: www.davidbrown.com http://www.davidbrown.com/> 

 

David Brown Engineering Limited

Company No 331925 England

Park Works, Park Road, Huddersfield HD4 5DD England

 

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

Re: [sqlite] INSERTing records in two tables?

2008-02-11 Thread Dennis Cote

Gilles wrote:
> 
> I'm an SQL newbie, and would like to know how to perform the following
> INSERT's:
> 
> BEGIN;
> INSERT INTO customers (id,name) VALUES (NULL,'John Doe');
> ;How to get ROW_ID?
> INSERT INTO phones (tel,id_customers) VALUES ('1234567',ROW_ID);
> COMMIT;
> 

Gilles,

In this particular case you can use last_insert_rowid() in the second 
insert as Igor has suggested. (Only if the id column is declared 
"ineteger primary key". If the id column is not declared that way then 
the rowid and the id are not the same value. Since you are inserting a 
null for the id, I assume it is declared that way.)

In the general case where you want to add multiple telephone numbers 
linked to a customer record that won't work, because the next call to 
last_insert_rowid() will return the rowid of the row that was inserted 
into the phones table.

In general you will need to use a select after the first insert to get 
the customer id you want, and save that value to be used in the 
subsequent telephone number insert operations. You can do that in SQL or 
in your application code.

In pseudo code it would be something like this:

in_tel = "insert into phones (tel, id_customer) values ('%s', %d)"
do_sql("begin")
do_sql("insert into customers (id,name) values (NULL,'John Doe')")
id = do_qry("select last_insert_rowid()")
sprintf(sql, in_tel, "1234567", id)
do_sql(sql)
sprintf(sql, in_tel, "9876543", id)
do_sql(sql)
do_sql("commit")

If you need to do this directly in SQL, you can store the rowid in a 
temp table like this.

BEGIN;
INSERT INTO customers (id,name) VALUES (NULL,'John Doe');
create temp table cust_id as select last_insert_rowid as id;
INSERT INTO phones (tel,id_customers) VALUES ('1234567',
(select id from cust_id));
INSERT INTO phones (tel,id_customers) VALUES ('9876543',
(select id from cust_id));
drop table cust_id;
COMMIT;

You could of course also repeat the select lookup for each subsequent 
telephone insert using a subselect. If you do that you need to ensure 
the customer name values are unique.

insert into phones (tel, id_customer)
   values ('1234567', (select id from customer where name = 'John Doe'));
insert into phones (tel, id_customer)
   values ('9876543', (select id from customer where name = 'John Doe'));

HTH
Dennis Cote

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

Re: [sqlite] Serious problem: lower/upper malfunction

2008-02-11 Thread Z.B.

On Sun, Feb 10, 2008 at 10:11:24PM -0500, Shawn Wilsher wrote:

> As I understand it, sqlite upper and lower functions only work on
> ASCII.  There is an icu extension to make it work with UTF-8/UTF-16 as
> far as I know.

Is it a special extension for SQLite?
-- 
pozdrawiam / regards

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

47 matches

Mail list logo