subject:"\[sqlite\] Help with Sqlite"

Hi Igor,

Thank you very much, it working :-)

Cheers,

2008/2/13, Igor Tandetnik <[EMAIL PROTECTED]>:
>
> "li yuqian" <[EMAIL PROTECTED]> wrote in
> message
> news:[EMAIL PROTECTED]
> > now my sql working, but result different mysql result.
> >
> > sqlite> SELECT t.variable, t.value, ifnull(d.value, ''||'off') state
> > FROM globals t LEFT JOIN globals d ON
> > (substr(t.variable,5,length(t.variable)-4) =
> > substr(d.variable,12,length(t.variable) - 11) and t.variable LIKE
> > 'OUT\_%' ESCAPE '\' and d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\')
> > ORDER BY t.variable;
>
> My bad. The condition on t must be out in the WHERE clause:
>
> SELECT t.variable, t.value, ifnull(d.value, 'off')) state
> FROM globals t LEFT JOIN globals d ON (
> substr(t.variable,5, length(t.variable)-4) =
> substr(d.variable,12, length(d.variable)-11) and
> d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\'
> )
> WHERE t.variable LIKE 'OUT\_%' ESCAPE '\'
> ORDER BY t.variable;
>
> Otherwise the result will always include all rows from t.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Help for sqlite syntax

Hi Igor,

Thanks for your kind of help :-)

now my sql working, but result different mysql result.
1. mysql:
---
mysql> 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.variableLIKE 'OUT\_%' AND concat('OUTDISABLE_',substring(
v.variable,5)) NOT IN ( SELECT variable from globals WHERE variable LIKE
'OUTDISABLE\_%' ) ORDER BY variable;
+--++---+
| variable | value  | state |
+--++---+
| OUT_1| ZAP/g0 | off   |
+--++---+
--

2. sqlite :

sqlite> SELECT t.variable, t.value, ifnull(d.value, ''||'off') state FROM
globals t LEFT JOIN globals d ON (substr(t.variable,5,length(t.variable)-4)
= substr(d.variable,12,length(t.variable) - 11) and t.variable LIKE 'OUT\_%'
ESCAPE '\' and d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY
t.variable;
AFTER_INCOMING||off
ALLOW_SIP_ANON|yes|off
CALLFILENAME|\"\"|off
DIALOUTIDS|1|off
DIAL_OPTIONS|tr|off
DIAL_OUT|9|off
DIRECTORY|last|off
DIRECTORY_OPTS||off
FAX||off
FAX_RX|system|off
FAX_RX_EMAIL|[EMAIL PROTECTED]|off
FAX_RX_FROM|[EMAIL PROTECTED]|off
INCOMING|group-all|off
IN_OVERRIDE|forcereghours|off
NULL|\"\"|off
OPERATOR||off
OPERATOR_XTN||off
OUT_1|ZAP/g0|off
PARKNOTIFY|SIP/200|off
RECORDEXTEN|\"\"|off
REGDAYS|mon-fri|off
REGTIME|7:55-17:05|off
RINGTIMER|15|off
TIMEFORMAT|kM|off
TONEZONE|us|off
TRANSFER_CONTEXT|from-internal-xfer|off
TRUNK_OPTIONS||off
VMX_CONTEXT|from-internal|off
VMX_LOOPDEST_CONTEXT||off
VMX_LOOPDEST_EXT|dovm|off
VMX_LOOPDEST_PRI|1|off
VMX_LOOPS|1|off
VMX_OPTS_DOVM||off
VMX_OPTS_LOOP||off
VMX_OPTS_TIMEOUT||off
VMX_PRI|1|off
VMX_REPEAT|1|off
VMX_TIMEDEST_CONTEXT||off
VMX_TIMEDEST_EXT|dovm|off
VMX_TIMEDEST_PRI|1|off
VMX_TIMEOUT|2|off
VM_DDTYPE|b|off
VM_GAIN||off
VM_OPTS||off
VM_PREFIX|*|off
-

thanks,

2008/2/13, Igor Tandetnik <[EMAIL PROTECTED]>:
>
> li yuqian <[EMAIL PROTECTED]> wrote:
> > now i try follow sql
> > SELECT t.variable, t.value, ifnull(d.value, ''||'off') state FROM
> > globals t LEFT JOIN globals d ON (substr(t.variable,5) =
> > substr(d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and
> > d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable;
> >
> > and get a SQL error: wrong number of arguments to function substr(),
> > still not get working :-(
>
> This statement works for me without any errors. Try upgrading to a more
> recent version of SQLite. If for some reason you can't, try replacing
>
> substr(t.variable,5)
>
> with
>
> substr(t.variable,5, length(t.variable) - 4)
>
> and similarly for the other occurence of substr.
>
> ''||'off' is equivalent to simply 'off' (concatenating anything to an
> empty string doesn't change that anything).
> --
> With best wishes,
> Igor Tandetnik
>
> With sufficient thrust, pigs fly just fine. However, this is not
> necessarily a good idea. It is hard to be sure where they are going to
> land, and it could be dangerous sitting under them as they fly
> overhead. -- RFC 1925
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Help for sqlite syntax

li yuqian <[EMAIL PROTECTED]> wrote:
> now i try follow sql
> SELECT t.variable, t.value, ifnull(d.value, ''||'off') state FROM
> globals t LEFT JOIN globals d ON (substr(t.variable,5) =
> substr(d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and
> d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable;
>
> and get a SQL error: wrong number of arguments to function substr(),
> still not get working :-(

This statement works for me without any errors. Try upgrading to a more 
recent version of SQLite. If for some reason you can't, try replacing

substr(t.variable,5)

with

substr(t.variable,5, length(t.variable) - 4)

and similarly for the other occurence of substr.

''||'off' is equivalent to simply 'off' (concatenating anything to an 
empty string doesn't change that anything).
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925

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

Re: [sqlite] Help for sqlite syntax

Hi Igor,

we want porting the FreePBX(freepbx.org) to our project www.astfin.org,
Actually i don't know this substr(t.value,1,0) means :-(

now i try follow sql
SELECT t.variable, t.value, ifnull(d.value, ''||'off') state FROM globals t
LEFT JOIN globals d ON (substr(t.variable,5) = substr(d.variable,12) and
t.variable LIKE 'OUT\_%' ESCAPE '\' and d.variable LIKE 'OUTDISABLE\_%'
ESCAPE '\') ORDER BY t.variable;

and get a SQL error: wrong number of arguments to function substr(), still
not get working :-(

Thanks,

2008/2/12, Igor Tandetnik <[EMAIL PROTECTED]>:
>
> "li yuqian" <[EMAIL PROTECTED]> wrote in
> message
> news:[EMAIL PROTECTED]
> > i follow the documents for sqlite function and change the sql to:
> > ---
> > SELECT t.variable, t.value, ifnull(d.value,
> > group_concat(substr(t.value,1,0),'off'))
> > state FROM globals t LEFT JOIN globals d ON (substr(t.variable,5) =
> > substr( d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and
> > d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable;
> > -
> > always get SQL error: no such function: group_concat, but the sqlite
> > documents have this function!
>
> group_concat is an aggregate function (like max and such), so it's not
> what you need anyway. It's probably new in some version of SQLite later
> than the one you have.
>
> SQLite doesn't have concat() function, but has concatenation operator ||
> (two pipe characters). So simply replace concat(a, b) with a || b
>
> I'm still pretty sure that substr(t.value,1,0) always returns an empty
> string, so you could just as well write  '' (two single quotes). I
> suspect you meant to do something different, but it's not clear what.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Help for sqlite syntax

"li yuqian" <[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
> i follow the documents for sqlite function and change the sql to:
> ---
> SELECT t.variable, t.value, ifnull(d.value,
> group_concat(substr(t.value,1,0),'off'))
> state FROM globals t LEFT JOIN globals d ON (substr(t.variable,5) =
> substr( d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and
> d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable;
> -
> always get SQL error: no such function: group_concat, but the sqlite
> documents have this function!

group_concat is an aggregate function (like max and such), so it's not 
what you need anyway. It's probably new in some version of SQLite later 
than the one you have.

SQLite doesn't have concat() function, but has concatenation operator || 
(two pipe characters). So simply replace concat(a, b) with a || b

I'm still pretty sure that substr(t.value,1,0) always returns an empty 
string, so you could just as well write  '' (two single quotes). I 
suspect you meant to do something different, but it's not clear what.

Igor Tandetnik 



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

Re: [sqlite] Help for sqlite syntax

Hi Igor,

thank you very much.

i follow the documents for sqlite function and change the sql to:
---
SELECT t.variable, t.value, ifnull(d.value,
group_concat(substr(t.value,1,0),'off'))
state FROM globals t LEFT JOIN globals d ON (substr(t.variable,5) = substr(
d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and d.variable LIKE
'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable;
-
always get SQL error: no such function: group_concat, but the sqlite
documents have this function!

thanks

2008/2/12, Igor Tandetnik <[EMAIL PROTECTED]>:
>
> "li yuqian" <[EMAIL PROTECTED]> wrote in
> message
> news:[EMAIL PROTECTED]
> >
> > i tried your sql, but get a error:
> > -
> > SQL error: no such function: substring
> > 
>
> In SQLite, it's named substr. Check the documentation to make sure it
> expects the same parameters as substring in MySQL (with which I'm not
> familiar), adjust as necessary.
>
> http://sqlite.org/lang_expr.html
>
> I'm particularly suspicious of substring(v.value,1,0) - it appears to
> always produce an empty string. What is it supposed to do?
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Help for sqlite syntax

"li yuqian" <[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
>
> i tried your sql, but get a error:
> -
> SQL error: no such function: substring
> 

In SQLite, it's named substr. Check the documentation to make sure it 
expects the same parameters as substring in MySQL (with which I'm not 
familiar), adjust as necessary.

http://sqlite.org/lang_expr.html

I'm particularly suspicious of substring(v.value,1,0) - it appears to 
always produce an empty string. What is it supposed to do?

Igor Tandetnik 



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

Re: [sqlite] Help for sqlite syntax

Hi Igor,

Thanks for your reply.

i tried your sql, but get a error:
-
 SQL error: no such function: substring


is something wrong?

thanks

2008/2/12, Igor Tandetnik <[EMAIL PROTECTED]>:
>
> "li yuqian" <[EMAIL PROTECTED]> wrote in
> message
> news:[EMAIL PROTECTED]
> > 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
>
> You make it a little too complicated. Try this:
>
> SELECT t.variable, t.value,
> ifnull(d.value, concat(substring(t.value,1,0),'off')) state
> FROM globals t LEFT JOIN globals d ON (
> substring(t.variable,5) = substring(d.variable,12) and
> t.variable LIKE 'OUT\_%' ESCAPE '\' and
> d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\'
> )
> ORDER BY t.variable;
>
> Note that in SQLite, a backslash has no special meaning in LIKE
> operator, unless assigned such meaning via ESCAPE clause (any character
> can be used as an escape character, not just backslash).
> --
> With best wishes,
> Igor Tandetnik
>
> With sufficient thrust, pigs fly just fine. However, this is not
> necessarily a good idea. It is hard to be sure where they are going to
> land, and it could be dangerous sitting under them as they fly
> overhead. -- RFC 1925
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Help for sqlite syntax

"li yuqian" <[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
> 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

You make it a little too complicated. Try this:

SELECT t.variable, t.value,
ifnull(d.value, concat(substring(t.value,1,0),'off')) state
FROM globals t LEFT JOIN globals d ON (
substring(t.variable,5) = substring(d.variable,12) and
t.variable LIKE 'OUT\_%' ESCAPE '\' and
d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\'
)
ORDER BY t.variable;

Note that in SQLite, a backslash has no special meaning in LIKE 
operator, unless assigned such meaning via ESCAPE clause (any character 
can be used as an escape character, not just backslash).
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925 



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

Re: [sqlite] Help for sqlite syntax

I tried this, but sqlite3 show:
---
SQL error: no such column: d.variable
---

any idea? thanks


2008/2/12, Mohd Radzi Ibrahim <[EMAIL PROTECTED]>:
>
> Could you just use ORDER BY 1 ?
>
> best regards
> -- radzi --
> - Original Message -
> From: "li yuqian" <[EMAIL PROTECTED]>
> To: <sqlite-users@sqlite.org>
> 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 f

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: <sqlite-users@sqlite.org>
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

[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] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-18 Thread Scott Hess

On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote:
> Yes. Makes sense (not to cache query results for embedded apps).
> So what is cached. Just dirty pages? or are raw tables cached when
> queried?

SQLite implements a tables and indices as btrees over a pager layer.
The pager layer caches pages.  Various strategies are used to keep the
page cache live as long as possible, including across transactions (if
nobody modifies the database in between).

Beyond that, most operating systems cache disk pages in memory.

-scott

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-17 Thread John Stanton

PostgreSQL has the capability of storing pre-compiled SQL so that it can
be reused and have data bound to the compiled statement. I have not
looked at the mechanics, but it would be of interest and educational for
you to see the PostgreSQL approach.

Sqlite does cache the results of a query. The persistence of that cache
varies with the version of Sqlite. It has an optional shared cache
mode which can lift performance in appropriate applications. The
evolution of cache persistence and sharing in successive versions of
Sqlite should give you an insight into the problems of implementing such
features.

Uma Krishnan wrote:
Thanks John and Joe for your responses.

As far as I know, Postgres does not have a virtual engine. I could be wrong.

One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not)

Thanks

Uma
John Stanton <[EMAIL PROTECTED]> wrote: >Moreover, is it typical to have an
implementation like VDBE in other databases as well?

This is a common approach and has been used for a very long time. For
example we used it in products produced during the 1980s because
producing a virtual machine and a compiler for its application-specific
instruction set was a far better solution than masses of procedural
logic. At that time it was a time honored technique and not at all
innovative.

Look at how PostgreSQL compiles and stores SQL statements for background
information on the concept.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-17 Thread Uma Krishnan

Yes. Makes sense (not to cache query results for embedded apps). So what is 
cached. Just dirty pages? or are raw tables cached when queried?

Thanks

Uma

Scott Hess <[EMAIL PROTECTED]> wrote: On 10/17/07, Trevor Talbot  wrote:
> On 10/17/07, Uma Krishnan  wrote:
> > One other question, when a query is issued, does SQLite cache the results, 
> > so that future queries can be processed off the cache (I think not)
>
> Like the "query cache" in some other databases?  No.
>
> SQLite does have a cache of database pages, but they mimic what's on
> disk, not the results of a particular query.
>
> A query cache would not be very useful for an embedded database.  If
> you're caching results, you might as well do it in the application's
> native form -- it's the same process after all.

To add another nail, the reason a query cache is often useful in
database servers is because you can usually share the cache across all
the front-ends.  Since SQLite effectively lives inside the front-end,
this sharing goes away.  Worse, any caching SQLite does is adding to
the memory footprint of the containing app (or, put another way,
stealing memory the app could use in other ways).

-scott

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-17 Thread Scott Hess

On 10/17/07, Trevor Talbot <[EMAIL PROTECTED]> wrote:
> On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote:
> > One other question, when a query is issued, does SQLite cache the results, 
> > so that future queries can be processed off the cache (I think not)
>
> Like the "query cache" in some other databases?  No.
>
> SQLite does have a cache of database pages, but they mimic what's on
> disk, not the results of a particular query.
>
> A query cache would not be very useful for an embedded database.  If
> you're caching results, you might as well do it in the application's
> native form -- it's the same process after all.

To add another nail, the reason a query cache is often useful in
database servers is because you can usually share the cache across all
the front-ends.  Since SQLite effectively lives inside the front-end,
this sharing goes away.  Worse, any caching SQLite does is adding to
the memory footprint of the containing app (or, put another way,
stealing memory the app could use in other ways).

-scott

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-17 Thread Trevor Talbot

On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote:

> As far as I know, Postgres does not have a virtual engine. I could be wrong.

It's not a virtual machine style, where it has a specific instruction
set; instead it's more like a graph of operations.  Execution means
walking a graph instead of interpreting an instruction stream.

It's still an abstract virtual engine, just implemented differently.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-17 Thread Trevor Talbot

On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote:

> One other question, when a query is issued, does SQLite cache the results, so 
> that future queries can be processed off the cache (I think not)

Like the "query cache" in some other databases?  No.

SQLite does have a cache of database pages, but they mimic what's on
disk, not the results of a particular query.

A query cache would not be very useful for an embedded database.  If
you're caching results, you might as well do it in the application's
native form -- it's the same process after all.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-17 Thread Griggs, Donald

Regarding:  "... when a query is issued, does SQLite cache the results,
so that future queries can be processed off the cache (I think not) "

...


=
P.S. And I should certainly have mentioned the sqlite items below:

http://sqlite.org/pragma.html

PRAGMA cache_size=   Number-of-pages;
PRAGMA default_cache_size = Number-of-pages;
PRAGMA page_size = bytes;

 


 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-17 Thread Griggs, Donald

Regarding:  "... when a query is issued, does SQLite cache the results,
so that future queries can be processed off the cache (I think not) "


Hi Uma,

In effect, powerful caching effects *do* occur because of the disk cache
provided by modern operating systems.  Since the hard disk operations
are typically orders of magnitude longer than the sql engine's cpu work
for a query, the fact that the disk sectors required by a recent query
tend to hang around a bit makes for very efficient use of RAM memory --
probably much better for the system as a whole than if sqlite tried to
reserve all this ram for itself.

For some applications, it even makes sense to perform a command-line
copy of the entire sqlite database to a NUL device, since this will
pre-load the operating system cache.


 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-17 Thread Uma Krishnan

Thanks John and Joe for your responses. 

As far as I know, Postgres does not have a virtual engine. I could be wrong.

One other question, when a query is issued, does SQLite cache the results, so 
that future queries can be processed off the cache (I think not) 

Thanks

Uma
John Stanton <[EMAIL PROTECTED]> wrote: >Moreover, is it typical to have an 
implementation like VDBE in other databases as well?

This is a common approach and has been used for a very long time.  For 
example we used it in products produced during the 1980s because 
producing a virtual machine and a compiler for its application-specific 
instruction set was a far better solution than masses of procedural 
logic.  At that time it was a time honored technique and not at all 
innovative.

Look at how PostgreSQL compiles and stores SQL statements for background 
information on the concept.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-16 Thread John Stanton


Moreover, is it typical to have an implementation like VDBE in other databases 
as well?


This is a common approach and has been used for a very long time.  For 
example we used it in products produced during the 1980s because 
producing a virtual machine and a compiler for its application-specific 
instruction set was a far better solution than masses of procedural 
logic.  At that time it was a time honored technique and not at all 
innovative.


Look at how PostgreSQL compiles and stores SQL statements for background 
information on the concept.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-16 Thread Joe Wilson

--- Uma Krishnan <[EMAIL PROTECTED]> wrote:
> I'm a student trying to understand SQLite for my DB project. There are a 
> couple of aspects that
> I don't quite understand:
> 1) VDBE. I see how the vdbe stack is created using VDBEAddOp etc. But once 
> the code is
> generated, I don't see when it's executed. Moreover, is it typical to have an 
> implementation
> like VDBE in other databases as well?

Search for sqlite3VdbeExec. It is called by sqlite3Step.

The best way to understand the code is to step through sqlite3 
example SQL statements with a debugger. Then follow up by reading the
source files involved.

I've heard that FoxPro used to JIT its queries in x86 prior to 
executing them, but I don't know if it's true.

> 2) VIRTUAL TABLES.  Why should the shared_cache be disabled when using 
> VIRTUAL TABLES?

No idea. I've asked the same question myself. I would think it would
be desirable to use FTS[123] with shared cache in a multi-threaded
web server scenario.

I suppose you could uncomment the code that prevents shared cache
running with virtual tables, run it, and see what breaks.

Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables

2007-10-16 Thread Uma Krishnan

Hello,

I'm a student trying to understand SQLite for my DB project. There are a couple 
of aspects that I don't quite understand:
1) VDBE. I see how the vdbe stack is created using VDBEAddOp etc. But once the 
code is generated, I don't see when it's executed. Moreover, is it typical to 
have an implementation like VDBE in other databases as well?
2) VIRTUAL TABLES.  Why should the shared_cache be disabled when using VIRTUAL 
TABLES?

Thanks

Uma

Re: [sqlite] HELP! DDD & SQLite

2007-09-08 Thread Uma Krishnan

Hello Ken,

  I just realized that sqlite3 is a bash program. When I do ddd sqlite3, it 
gives an error. So when I need to load a program, thru GUI interface I load 
main.o. But I guess that's not right

  What should I do?

  Thanks

  Uma

Ken <[EMAIL PROTECTED]> wrote:
  you need to set a breakpoint.
hit the continue button in DDD. That will allow execution of the code and allow 
the attached program to continue.

It would be better however to run ddd as follows:
ddd sqlite3
Then set a breakpoint. Then run the program inside the ddd that way you can 
first set breakpoints prior to execution.

Ken

Uma Krishnan wrote: Hello I'm trying to debug SQLite (to understand the code). 
But e when I attach the process sqlite3, the sqlite3 terminal hangs (ie would 
not accept any user inputs) till I detach.

Can someone please tell me what I'm doing wrong

Thanks

Uma

Cory Nelson 
wrote: On 9/7/07, Yves Goergen wrote:
> Hi,
>
> in a scenario when multiple operations need to be transactionally
> synchronised, I have a file that must be deleted when the database
> records are added successfully, but the database operations must be
> rolled back, if the file cannot be deleted.
>
> I'm currently using a transaction for this on the database side and
> rolling it back if the file cannot be deleted. But what if the file is
> gone and then SQLite says it doesn't accept my records? Since we're
> inside a transaction, integrity checks should be deferred until a
> COMMIT. Is there a way to tell whether the COMMIT will succeed under the
> current conditions so that I can safely delete the file?

My understanding is that if your first insert succeeds you hold a
write lock on the table and barring any exceptional errors a commit
should always succeed.

-- 
Cory Nelson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] HELP! DDD & SQLite

2007-09-08 Thread Ken

you need to set a breakpoint.
hit the continue button in DDD. That will allow execution of the code and allow 
the attached program to continue.

It would be better however to run ddd as follows:
ddd sqlite3
   Then set a breakpoint. Then run the program inside the ddd that way you can 
first set breakpoints prior to execution.

Ken

Uma Krishnan <[EMAIL PROTECTED]> wrote: Hello I'm trying to debug SQLite (to 
understand the code).  But e when I attach the process sqlite3, the sqlite3 
terminal hangs (ie would not accept any user inputs) till I detach.

Can someone please tell me what I'm doing wrong

Thanks

Uma

Cory Nelson 
 wrote: On 9/7/07, Yves Goergen  wrote:
> Hi,
>
> in a scenario when multiple operations need to be transactionally
> synchronised, I have a file that must be deleted when the database
> records are added successfully, but the database operations must be
> rolled back, if the file cannot be deleted.
>
> I'm currently using a transaction for this on the database side and
> rolling it back if the file cannot be deleted. But what if the file is
> gone and then SQLite says it doesn't accept my records? Since we're
> inside a transaction, integrity checks should be deferred until a
> COMMIT. Is there a way to tell whether the COMMIT will succeed under the
> current conditions so that I can safely delete the file?

My understanding is that if your first insert succeeds you hold a
write lock on the table and barring any exceptional errors a commit
should always succeed.

-- 
Cory Nelson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] HELP! DDD & SQLite

2007-09-07 Thread Uma Krishnan

Hello I'm trying to debug SQLite (to understand the code).  But e when I attach 
the process sqlite3, the sqlite3 terminal hangs (ie would not accept any user 
inputs) till I detach.

Can someone please tell me what I'm doing wrong

Thanks

Uma

Cory Nelson <[EMAIL PROTECTED]> wrote: On 9/7/07, Yves Goergen  wrote:
> Hi,
>
> in a scenario when multiple operations need to be transactionally
> synchronised, I have a file that must be deleted when the database
> records are added successfully, but the database operations must be
> rolled back, if the file cannot be deleted.
>
> I'm currently using a transaction for this on the database side and
> rolling it back if the file cannot be deleted. But what if the file is
> gone and then SQLite says it doesn't accept my records? Since we're
> inside a transaction, integrity checks should be deferred until a
> COMMIT. Is there a way to tell whether the COMMIT will succeed under the
> current conditions so that I can safely delete the file?

My understanding is that if your first insert succeeds you hold a
write lock on the table and barring any exceptional errors a commit
should always succeed.

-- 
Cory Nelson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Help In SQLIte

2006-07-04 Thread John Stanton


Looks like you haven't include the Sqlite library for the linker.

Ashish Singh wrote:



Hello SQLite Users
I am using SQlite 2.8.17 version
I am trying to compile this piece of code on linux system when i type the command 
"g++


test.cpp"


I get the following error  which is at the bottom of this mail.
If anybody could which is the library file I need to include I will be greatful 
to him.
Thanks
Ashish


#include
#include "sqlite.h"
#include

using namespace std;

typedef int (*sqlite_callback)(void*, int, char**, char**);

int main() {
const char* DB;
int mode=0;
 //  sqlite_stmt** statment;
 char** errmsg;
 sqlite* dbHandle;
 //  int length_stat;
 //  const char* p = "create table tbl1(one varchar(10), two 
smallint)";

 const void** pzTail;
 //  length_stat = strlen(p);
dbHandle = sqlite_open(DB, mode, errmsg);
 //  sqlite_prepare(*DB, "create table tbl1(one varchar(10), two 
smallint)", length_stat,

statement, pztail);
 sqlite_exec(dbHandle, "create table tbl1(one varchar(10), two 
smallint)", 0, 0, errmsg);


 char* name = "ashish";
 char* numc;
 char* fin_name;
 int num;
 for(int i=0;i<10;i++)
   for(int j=0;j<1000;j++) {
 num = i*1000+j;
 *numc = (char)num;
 fin_name = strcat(name, numc);
 sqlite_exec(dbHandle, "insert into table tbl1(num, fin_name)", 0, 
0, errmsg);
   }  
 //  printf("hi");

 return 0;
}




Error Message
--
g++ test.cpp
/tmp/ccxM7oLI.o(.text+0x24): In function `main':
: undefined reference to `sqlite_open'
/tmp/ccxM7oLI.o(.text+0x41): In function `main':
: undefined reference to `sqlite_exec'
/tmp/ccxM7oLI.o(.text+0xc4): In function `main':
: undefined reference to `sqlite_exec'
collect2: ld returned 1 exit status
---

   Happy Coding!!!

 Warm regards 
 Ashish Singh 
 [EMAIL PROTECTED], [EMAIL PROTECTED] 
Graduate Student (MS-Software Engineering) 
University of Southern California www.cs.usc.edu  
Los angeles California USA 
 323 404 8621(M)  
 213-746-4142-(R)
  











   Happy Coding!!!

 Warm regards 
 Ashish Singh 
 [EMAIL PROTECTED], [EMAIL PROTECTED] 
Graduate Student (MS-Software Engineering) 
University of Southern California www.cs.usc.edu  
Los angeles California USA 
 323 404 8621(M)  
 213-746-4142-(R)
  












Happy Coding!!!

  Warm regards 
  Ashish Singh 
  [EMAIL PROTECTED], [EMAIL PROTECTED] 
Graduate Student (MS-Software Engineering) 
University of Southern California www.cs.usc.edu  
Los angeles California USA 
  323 404 8621(M)  
  213-746-4142-(R)

[sqlite] Help In SQLIte

2006-07-03 Thread Ashish Singh



> > 
> > Hello SQLite Users
> > I am using SQlite 2.8.17 version
> > I am trying to compile this piece of code on linux system when i type the 
> > command "g++
> test.cpp"
> > 
> > I get the following error  which is at the bottom of this mail.
> > If anybody could which is the library file I need to include I will be 
> > greatful to him.
> > Thanks
> > Ashish
> > 
> > 
> > #include
> > #include "sqlite.h"
> > #include
> > 
> > using namespace std;
> > 
> > typedef int (*sqlite_callback)(void*, int, char**, char**);
> > 
> > int main() {
> > const char* DB;
> > int mode=0;
> >   //  sqlite_stmt** statment;
> >   char** errmsg;
> >   sqlite* dbHandle;
> >   //  int length_stat;
> >   //  const char* p = "create table tbl1(one varchar(10), two 
> > smallint)";
> >   const void** pzTail;
> >   //  length_stat = strlen(p);
> > dbHandle = sqlite_open(DB, mode, errmsg);
> >   //  sqlite_prepare(*DB, "create table tbl1(one varchar(10), two 
> > smallint)", length_stat,
> > statement, pztail);
> >   sqlite_exec(dbHandle, "create table tbl1(one varchar(10), two 
> > smallint)", 0, 0, errmsg);
> > 
> >   char* name = "ashish";
> >   char* numc;
> >   char* fin_name;
> >   int num;
> >   for(int i=0;i<10;i++)
> > for(int j=0;j<1000;j++) {
> >   num = i*1000+j;
> >   *numc = (char)num;
> >   fin_name = strcat(name, numc);
> >   sqlite_exec(dbHandle, "insert into table tbl1(num, fin_name)", 0, 
> > 0, errmsg);
> > }  
> >   //  printf("hi");
> >   return 0;
> > }
> > 
> > 
> > 
> > 
> > Error Message
> > --
> > g++ test.cpp
> > /tmp/ccxM7oLI.o(.text+0x24): In function `main':
> > : undefined reference to `sqlite_open'
> > /tmp/ccxM7oLI.o(.text+0x41): In function `main':
> > : undefined reference to `sqlite_exec'
> > /tmp/ccxM7oLI.o(.text+0xc4): In function `main':
> > : undefined reference to `sqlite_exec'
> > collect2: ld returned 1 exit status
> > ---
> > 
> > Happy Coding!!!
> > 
> >   Warm regards 
> >   Ashish Singh 
> >   [EMAIL PROTECTED], [EMAIL PROTECTED] 
> > Graduate Student (MS-Software Engineering) 
> > University of Southern California www.cs.usc.edu  
> > Los angeles California USA 
> >   323 404 8621(M)  
> >   213-746-4142-(R)
> >
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> 
> 
> Happy Coding!!!
> 
>   Warm regards 
>   Ashish Singh 
>   [EMAIL PROTECTED], [EMAIL PROTECTED] 
> Graduate Student (MS-Software Engineering) 
> University of Southern California www.cs.usc.edu  
> Los angeles California USA 
>   323 404 8621(M)  
>   213-746-4142-(R)
>
> 
> 
> 
> 
> 
> 
> 


Happy Coding!!!

  Warm regards 
  Ashish Singh 
  [EMAIL PROTECTED], [EMAIL PROTECTED] 
Graduate Student (MS-Software Engineering) 
University of Southern California www.cs.usc.edu  
Los angeles California USA 
  323 404 8621(M)  
  213-746-4142-(R)

Re: [sqlite] HELP for SQLite MEMORY test.

2006-05-18 Thread Jay Sprenkle


On 5/18/06, Manzoor Ilahi Tamimy <[EMAIL PROTECTED]> wrote:

DEAR All ,

   I want to use SQLite for our project, the main thing is that the
   database contains millions of Records. So for the faster
 operations   on the db I want to use the SQLite as in-memory database.

   I have compared the results of SQLite as Disk db and as Memory db
   but I am not getting much difference. I am surprised that there
  must  be some difference between memory mode and disk mode.


The operating system disk cache is putting the disk database in memory
too. So you basically have two in memory databases.

[sqlite] HELP for SQLite MEMORY test.

2006-05-18 Thread Manzoor Ilahi Tamimy

DEAR All ,
   
   I want to use SQLite for our project, the main thing is that the 
   database contains millions of Records. So for the faster 
 operations   on the db I want to use the SQLite as in-memory database.
 
   I have compared the results of SQLite as Disk db and as Memory db 
   but I am not getting much difference. I am surprised that there 
  must  be some difference between memory mode and disk mode.
 
   Tests were run on 2.4GHz Sempron with 1GB of RAM and running 
  Windows  XP + SP2 with all updates applied. Test 20 : 100 
  INSERTs CREATE  TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100)); 
  INSERT INTO t1  VALUES(1,13153,'thirteen thousand one hundred fifty 
  three');
   
   In Memory 37.51 Sec 
   DISK BASED 39.76 Sec 
   Disk Space consumed 61.6 MB 
   
 
   Test 21`: 300 INSERTs
   In Memory 141.79 Sec 
   DISK BASED 111.906000 Sec 
   Disk Space consumed 185 MB
   
 
   Test 22: 500 INSERTs
 
   In Memory 279.42 Sec
   DISK BASED 201.266000 Sec 
   Disk Space consumed 308 MB
   Test 23: 1000 INSERTs
   
 
   In Memory 784.797000 Sec 
   DISK BASED 399.846000 Sec
   Disk Space consumed 617 MB
   
 
   I am testing it in the following way
   
 
   dwStart = GetTickCount();
   db.execDML("begin transaction;");
   db.execDML("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))
 ;") ; for( int i=1;i<=1000;i++) db.execDML("INSERT INTO t1 
 VALUES(1, 298361,'two hundred ninety eight  thousand three hundred')
 ");  db.execDML("commit Transaction") ;  dwStop = GetTickCount();
 
   
   I think I am missing something or some necessary parameters. I 
  spent  a lot of time to find out the problem. Please Guide me. I 
  will be  really thankful.
 
   Regards,
 
   MANZOOR ILAHI
--
COMSATS Institute of Information Technology (http://www.ciit.edu.pk)

Re: [sqlite] help with sqlite command

2006-03-28 Thread Walter Meerschaert


Dennis Cote wrote:
To get every N'th row after deletions you need some way to assign a 
series of integers to the result rows. The easiest way I can think of 
is to create a temporary table from your initial query. Then you can 
use the modulus operator to select every N'th record from that table 
as you have suggested since the rowids will all be freshly assigned. 
You will also need to drop the temp table when you are done with it.


 create temp table temp_table as select * from my_table where ;
 select * from temp_table where rowid % N = 0;
 drop table temp_table;

If the table rows are large, or if the number of rows is large, you 
might want to do this refinement:


create temp_table as select rowid  as source_rowid from my_table WHERE ...;
select * from my_table, temp_table where temp_table.rowid%N=0 and 
source_rowid=my_table.rowid;

drop table temp_table;

Actually, this looks like a great way to implement many kinds of weird 
sorting/indexing schemes (percentile ranking, hi/low ordering, grouping).


Such a case would be to find the decile rankings of an table (with 
numbers in it). In that case, N would be the count(*) / 10. and the 
original WHERE would describe the order over which the ranking is to be 
done. Or use count/2 to get at the median. (if N < 100, one might also 
need to interpolate).

Re: [sqlite] help with sqlite command

On 3/27/06, Uma Venkataraman <[EMAIL PROTECTED]> wrote:
> I would like to delete n records from a table, based on some condition. Can
> some one please let me know how to do this with sqlite?

http://sqlite.org/lang_delete.html

Re: [sqlite] help with sqlite command

I would like to delete n records from a table, based on some condition. Can 
some one please let me know how to do this with sqlite?


Thanks

Re: [sqlite] help with sqlite command

Thanks Dennis..that seems to do the trick...

- Original Message - 
From: "Dennis Cote" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, March 27, 2006 2:46 PM
Subject: Re: [sqlite] help with sqlite command

Jay Sprenkle wrote:

I believe rowid is assigned dynamically to the result set so it would
give a different
set of results for a different query.

Jay,

The rowid is the key from the btree used to store the table rows. It is 
not generated dynamically.

To get every N'th row after deletions you need some way to assign a 
series of integers to the result rows. The easiest way I can think of is 
to create a temporary table from your initial query. Then you can use 
the modulus operator to select every N'th record from that table as you 
have suggested since the rowids will all be freshly assigned. You will 
also need to drop the temp table when you are done with it.

 create temp table temp_table as select * from my_table where ;
 select * from temp_table where rowid % N = 0;
 drop table temp_table;

HTH
Dennis Cote

Re: [sqlite] help with sqlite command

Too bad sqlite doesn't have Oracle's ROWNUM:

"Pseudo-Columns

While not actual datatypes, Oracle supports several special-purpose
data elements. These elements are not actually contained in a table,
but are available for use in SQL statements as though they were part
of the table.
ROWNUM

For each row of data returned by a SQL query, ROWNUM will contain a
number indicating the order in which the row was retrieved. For
example, the first row retrieved will have a ROWNUM of 1, the second
row will have a ROWNUM of 2, and so on. This approach can be useful
for limiting the number of rows returned by a query. To display only
ten rows of the emp table, the following SQL statement makes use of
the ROWNUM pseudo-column:

SELECT *
FROM emp
WHERE ROWNUM < 11;

WARNING:

ROWNUM returns a number indicating the order in which the row was
retrieved from the table, but this is not always the order in which a
row is displayed. For example, if a SQL statement includes an ORDER BY
clause, rows will not be displayed in ROWNUM sequence, since ROWNUM is
assigned before the sort operation. "

Re: [sqlite] help with sqlite command

On 3/27/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Jay,
>
> The rowid is the key from the btree used to store the table rows. It is
> not generated dynamically.

Ah. Thanks! Learn something new every day.

Re: [sqlite] help with sqlite command

2006-03-27 Thread Dennis Cote


Jay Sprenkle wrote:



I believe rowid is assigned dynamically to the result set so it would
give a different
set of results for a different query.


 


Jay,

The rowid is the key from the btree used to store the table rows. It is 
not generated dynamically.


To get every N'th row after deletions you need some way to assign a 
series of integers to the result rows. The easiest way I can think of is 
to create a temporary table from your initial query. Then you can use 
the modulus operator to select every N'th record from that table as you 
have suggested since the rowids will all be freshly assigned. You will 
also need to drop the temp table when you are done with it.


 create temp table temp_table as select * from my_table where ;
 select * from temp_table where rowid % N = 0;
 drop table temp_table;

HTH
Dennis Cote

Re: [sqlite] help with sqlite command

2006-03-27 Thread Derrell . Lipman

"Uma Venkataraman" <[EMAIL PROTECTED]> writes:

> Hi Jay,
>
> Thanks for your reply. I am trying the command
>
>select * from mytable where row_id = row_id % 5

Try this instead:

  SELECT * FROM mytable WHERE ROWID % 5 = 0;

Note that if you have an integer primary key in mytable, then ROWID and your
primary key are the same thing.  If those ROWID values are not incrementing
numbers (e.g. you inserted values into your primary key which were out of
sequence or if you have deleted any rows) then this method won't work.

Here's an example of one way to do it:

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> .read /tmp/x.sql
CREATE TABLE x (i INTEGER PRIMARY KEY, t TEXT);
INSERT INTO x VALUES (1, 'one');
INSERT INTO x VALUES (2, 'two');
INSERT INTO x VALUES (3, 'three');
INSERT INTO x VALUES (4, 'four');
INSERT INTO x VALUES (5, 'five');
INSERT INTO x VALUES (6, 'six');
INSERT INTO x VALUES (7, 'seven');
INSERT INTO x VALUES (8, 'eight');
-- Retrieve every other row (we hope)
SELECT * FROM x WHERE ROWID % 2 = 0;
2|two
4|four
6|six
8|eight
-- Delete a row
DELETE FROM x WHERE i = 4;
-- The table now looks like this:
SELECT * FROM x;
1|one
2|two
3|three
5|five
6|six
7|seven
8|eight
-- Retrieve what should be every other row, but isn't
SELECT * FROM x WHERE ROWID % 2 = 0;
2|two
6|six
8|eight
-- Insert the values into a new table so pk is properly incrementing
CREATE TEMPORARY TABLE y
(pk INTEGER PRIMARY KEY,
 i INTEGER,
 t TEXT);
INSERT INTO y (i, t) SELECT i, t FROM x;
-- Now we can get every other row
SELECT * FROM y WHERE pk % 2 = 0;
2|2|two
4|5|five
6|7|seven
DROP TABLE y;
sqlite>

Cheers,

Derrell

Re: [sqlite] help with sqlite command

2006-03-27 Thread Clark Christensen

I think

select * from mytable where rowid %5 = 0;

will get you something like every fifth row in the table.  But that assumes 
your rowids are 1-nnn with no gaps.  If your rowids happen to skip a value 
evenly divisible by 5, you won't get another row until the next one divisible 
by 5.

 -Clark

- Original Message 
From: Uma Venkataraman <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, March 27, 2006 11:07:18 AM
Subject: Re: [sqlite] help with sqlite command

Hi Jay,

Thanks for your reply. I am trying the command

select * from mytable where row_id = row_id % 5

from sqlite browser and it says, no such column row_id.. Also I replaced 
row_id with rowid and it gave only the first 4 records from my table. My 
other concern is I will be deleting and adding records to the table. If I 
want to select every nth record after such deletions and additions will the 
row id not get affected?

Thanks
Uma

- Original Message - 
From: "Jay Sprenkle" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Monday, March 27, 2006 1:56 PM
Subject: Re: [sqlite] help with sqlite command

On 3/27/06, Uma Venkataraman <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I need to be able to select  the TOP N rows from a table. How do i do it =

select * from mytable limit 5

> with sqlite? Also  how does one select EVERY Nth row from a table?

use modulus operator for that:
select * from mytable where row_id = row_id % 5

---
On Wednesday, March 1, 2006, at a hearing on the proposed
Constitutional Amendment to prohibit gay marriage, Jamie Raskin,
professor of law at AU, was requested to testify.

At the end of his testimony, Republican Senator Nancy Jacobs said:
"Mr. Raskin, my Bible says marriage is only between a man and a woman.
What do you have to say about that?"

Raskin replied: "Senator, when you took your oath of office, you
placed your hand on the Bible and swore to uphold the Constitution.
You did not place your hand on the Constitution and swear to uphold
the Bible."

The room erupted into applause.

Re: [sqlite] help with sqlite command

> Thanks for your reply. I am trying the command
>
> select * from mytable where row_id = row_id % 5
>
> from sqlite browser and it says, no such column row_id.. Also I replaced
> row_id with rowid and it gave only the first 4 records from my table. My
> other concern is I will be deleting and adding records to the table. If I
> want to select every nth record after such deletions and additions will the
> row id not get affected?

Sorry, I'm a little off today and wrote the wrong formula!

To get the even numbered records use:
where rowid % 2 = 0

To get the odd numbered records use:
where rowid % 2 = 1

To get every 5th record
where rowid % 5 = 0 ( this will return record 5, 10, 15, etc).

look up the 'modulus' or 'modulo' operator to see what this does.

I believe rowid is assigned dynamically to the result set so it would
give a different
set of results for a different query.

If you want the same records from different select statements
you could create an integer column with a primary key and use that
instead of rowid.

Re: [sqlite] help with sqlite command


Hi Jay,

Thanks for your reply. I am trying the command

   select * from mytable where row_id = row_id % 5

from sqlite browser and it says, no such column row_id.. Also I replaced 
row_id with rowid and it gave only the first 4 records from my table. My 
other concern is I will be deleting and adding records to the table. If I 
want to select every nth record after such deletions and additions will the 
row id not get affected?


Thanks
Uma



- Original Message - 
From: "Jay Sprenkle" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, March 27, 2006 1:56 PM
Subject: Re: [sqlite] help with sqlite command


On 3/27/06, Uma Venkataraman <[EMAIL PROTECTED]> wrote:

Hi All,

I need to be able to select  the TOP N rows from a table. How do i do it =


select * from mytable limit 5



with sqlite? Also  how does one select EVERY Nth row from a table?


use modulus operator for that:
select * from mytable where row_id = row_id % 5



---
On Wednesday, March 1, 2006, at a hearing on the proposed
Constitutional Amendment to prohibit gay marriage, Jamie Raskin,
professor of law at AU, was requested to testify.

At the end of his testimony, Republican Senator Nancy Jacobs said:
"Mr. Raskin, my Bible says marriage is only between a man and a woman.
What do you have to say about that?"

Raskin replied: "Senator, when you took your oath of office, you
placed your hand on the Bible and swore to uphold the Constitution.
You did not place your hand on the Constitution and swear to uphold
the Bible."

The room erupted into applause.

Re: [sqlite] help with sqlite command

On 3/27/06, Uma Venkataraman <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I need to be able to select  the TOP N rows from a table. How do i do it =

select * from mytable limit 5

> with sqlite? Also  how does one select EVERY Nth row from a table?

use modulus operator for that:
select * from mytable where row_id = row_id % 5

---
On Wednesday, March 1, 2006, at a hearing on the proposed
Constitutional Amendment to prohibit gay marriage, Jamie Raskin,
professor of law at AU, was requested to testify.

At the end of his testimony, Republican Senator Nancy Jacobs said:
"Mr. Raskin, my Bible says marriage is only between a man and a woman.
What do you have to say about that?"

Raskin replied: "Senator, when you took your oath of office, you
placed your hand on the Bible and swore to uphold the Constitution.
You did not place your hand on the Constitution and swear to uphold
the Bible."

The room erupted into applause.

[sqlite] help with sqlite command