[sqlite] Will Bitwise Operations Use an Index?

[Daniel Wickes]

I'm trying to optimise some of my queries, and I would like to know if
bitwise operators in terms will still use an index, or if I should be
thinking about moving the more important values to separate columns that
could be checked for equality.

At the moment, I have an index created much like:

CREATE INDEX table_idx ON table(col_a,col_b,col_c);

And then I am performing a query such as:

SELECT count(*) FROM table WHERE col_a = 'apple' AND col_b = 3 AND col_c
& 32;

The key bit, if you'll pardon the pun, being the bitwise -and on col_c.
Will this be using the index or will it be checking the table rows?

Any help is much appreciated.

Many thanks,

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

[sqlite] BUG: The difference between working tclsqlite in tclsh shell and from tcl script

[Alexey Pechnikov]

Hello!

See the test:


$ cat /tmp/test
package require sqlite3
sqlite3 db :memory:

db eval {create table test(a int);insert into test values (1);}
proc test {label sql result} {
global i j
puts -nonewline $label\t
set _result [db eval $sql]
if { $_result eq $result} {
puts OK
} else {
puts ERROR\t$result!=$_result
}
}
set i 1

test 1.0 {select typeof($i)} integer ;# it doesn't work in orig sqlite
test 1.1 {select * from test where a=$i} 1
test 1.2 {select * from test where 1=$i} 1 ;# it doesn't work in orig sqlite
test 1.3 {select a from test where a IN (cast($i AS INT), 160)} 1
test 1.4 {select a from test where 1 IN (cast($i AS INT), 160)} 1


$ tclsh8.5 /tmp/test
1.0 ERROR   integer!=text
1.1 OK
1.2 ERROR   1!=
1.3 OK
1.4 OK


But in the tclsh shell these tests work fine:

$ tclsh8.5
% package require sqlite3
sqlite3 db :memory:

db eval {create table test(a int);insert into test values (1);}
proc test {label sql result} {
global i j
puts -nonewline $label\t
set _result [db eval $sql]
if { $_result eq $result} {
puts OK
} else {
puts ERROR\t$result!=$_result
}
}
set i 1

test 1.0 {select typeof($i)} integer ;# it doesn't work in orig sqlite
test 1.1 {select * from test where a=$i} 1
test 1.2 {select * from test where 1=$i} 1 ;# it doesn't work in orig sqlite
test 1.3 {select a from test where a IN (cast($i AS INT), 160)} 1
test 1.4 {select a from test where 1 IN (cast($i AS INT), 160)} 1

3.6.18
% % % % % % % 1
% 1
% % 1.0 OK
% 1.1   OK
% 1.2   OK
% 1.3   OK
% 1.4   OK
% %



== the patch 
--- tclsqlite.c.old 2009-09-05 00:37:43.0 +0400 
  
+++ tclsqlite.c 2009-10-09 02:50:39.0 +0400 
  
@@ -754,26 +754,18 @@   
  
   }else{   
  
 Tcl_Obj *pVar = Tcl_GetObjResult(p->interp);   
  
 int n; 
  
+Tcl_WideInt v; 
  
+double r;  
  
 u8 *data;  
  
-char *zType = pVar->typePtr ? pVar->typePtr->name : "";
  
-char c = zType[0]; 
  
-if( c=='b' && strcmp(zType,"bytearray")==0 && pVar->bytes==0 ){
  
+if( pVar->typePtr && pVar->typePtr->name[0]=='b' && 
strcmp(pVar->typePtr->name,"bytearray")==0 && pVar->bytes==0 ){
   /* Only return a BLOB type if the Tcl variable is a bytearray and

   ** has no string representation. */  

   data = Tcl_GetByteArrayFromObj(pVar, &n);

   sqlite3_result_blob(context, data, n, SQLITE_TRANSIENT); 

-}else if( c=='b' && strcmp(zType,"boolean")==0 ){  

-  Tcl_GetIntFromObj(0, pVar, &n);  

-  sqlite3_result_int(context, n);  

-}else if( c=='d' && strcmp(zType,"double")==0 ){   

-  double r;

-  Tcl_GetDoubleFromObj(0, pVar, &r);   

-  sqlite3_result_double(context, r);   

-}else if( (c=='w' && strcmp(zType,"wideInt")==0) ||

-  (c=='i' && strcmp(zType,"int")==0) ){

-  Tcl_WideInt v;   

-  Tcl_GetWideIntFromObj(0, pVar, &v);  

+}else if( TCL_OK == Tcl_GetWideIntFromObj(0, pVar, &v)){   
  

Re: [sqlite] Will Bitwise Operations Use an Index?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Daniel Wickes wrote:
> I'm trying to optimise some of my queries, and I would like to know if
> bitwise operators in terms will still use an index,

SQLite provides you the tools to find out for yourself.  In the shell do
'.explain' and then give it your query prefixed with 'EXPLAIN'.  You'll get
the virtual db engine code for the query printed out.

This page gives a rough guide to vdbe code.  (It does now use registers
rather than a stack.)

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

By far the easiest thing to do is explain a query you know uses the index
and then explain your query and look for the differences.

In answer to your question, yes the index is used.  You can see instructions
prefixed by Idx that aren't there when the index doesn't exist.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrO93oACgkQmOOfHg372QRXNQCeNSQ1r3o07Yj4n0RZ+kn9ePyT
cy8An05WyzhnU9M8Y3+00dFf4/tOh6vH
=hOpS
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New open source data synchronization tool

[Cjkalbente]

Hi, 

If you need an ETL open source program, best thing is to check Talend Open
Studio. It is a practical and easily learnable program for ETL, data
migration and synchronization. 

The bug reporting, the community and the forum are active. 

Go look at:  http://www.talend.com/ http://www.talend.com/ 



hfdabler wrote:
> 
> Hello, 
> 
> We are trying to find an ETL tool open source. Basically, we need our
> software to perform ETL, data migration and data synchronization. 
> 
> The program should not be used on larger projects. A few open source tools
> are on the market. 
> 
> Some ideas? Thanks.
> 

-- 
View this message in context: 
http://www.nabble.com/New-open-source-data-synchronization-tool-tp25783393p25817724.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] building permanently loaded extensions

[Alexey Pechnikov]

Hello!

On Thursday 17 September 2009 07:25:33 P Kishor wrote:
> pursuant to a recent email, I am wondering if I can build
> extension-functions.c
> 
> so that it is permanently available within sqlite library, and not
> just when loaded manually via a command. If I can, could someone
> please guide me to a tutorial for doing so?


In main.c add these lines

#ifdef SQLITE_ENABLE_FUNCTIONS
  if( !db->mallocFailed && rc==SQLITE_OK ){
rc = sqlite3FunctionsInit(db);
  }
#endif

The modified extension is available here
http://mobigroup.ru/files/sqlite-ext/functions/

And compile with -DSQLITE_ENABLE_FUNCTIONS

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New open source data synchronization tool

[Jean-Denis Muys]

On 10/9/09 10:54 , "Cjkalbente"  wrote:


> If you need an ETL open source program, best thing is to check Talend Open
> Studio. It is a practical and easily learnable program for ETL, data
> migration and synchronization.
> 
> The bug reporting, the community and the forum are active.
> 
> Go look at:  http://www.talend.com/ http://www.talend.com/
> 

Except that the install procedure is sooo bad on the Mac it makes you wonder
whether *that* level of software "quality" pertains to the rest of the
product.

On the other hand, I don't know of any other free ETL solution.

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

Re: [sqlite] New open source data synchronization tool

[Cjkalbente]

Try it on a PC if you can one day! It is a powerful solution even though it
is free and open source. Quality, to me, is good.



Jean-Denis Muys-2 wrote:
> 
> On 10/9/09 10:54 , "Cjkalbente"  wrote:
> 
> 
>> If you need an ETL open source program, best thing is to check Talend
>> Open
>> Studio. It is a practical and easily learnable program for ETL, data
>> migration and synchronization.
>> 
>> The bug reporting, the community and the forum are active.
>> 
>> Go look at:  http://www.talend.com/ http://www.talend.com/
>> 
> 
> Except that the install procedure is sooo bad on the Mac it makes you
> wonder
> whether *that* level of software "quality" pertains to the rest of the
> product.
> 
> On the other hand, I don't know of any other free ETL solution.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/New-open-source-data-synchronization-tool-tp25783393p25817960.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Will Bitwise Operations Use an Index?

[Daniel Wickes]

Roger Binns wrote:

> SQLite provides you the tools to find out for yourself.  In the shell do
> '.explain' and then give it your query prefixed with 'EXPLAIN'.  You'll get
> the virtual db engine code for the query printed out.
> ...
> In answer to your question, yes the index is used.  You can see instructions
> prefixed by Idx that aren't there when the index doesn't exist.

Roger, thank you!   The EXPLAIN feature opens a whole new world to me.
 How fascinating.

Thanks again,

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

Re: [sqlite] Datetime mystery

[Peter van Dijk]

On Oct 8, 2009, at 16:16 , Jay A. Kreibich wrote:

> On Thu, Oct 08, 2009 at 08:29:10AM +0200, Fredrik Karlsson scratched  
> on the wall:
>
>> Yes, that would have been my guess too, but I am on CET, which I
>> understand is UTC+1.

CET is CEST in summer, which is UTC+2

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

[sqlite] create virtual table if not exists

[cefbear]

Hey there,

this has already been requested over two years ago (2007-08-30), but the ticket 
(#2604) is still pending:

http://www.sqlite.org/cvstrac/tktview?tn=2604

The patch by Scott Hess (2008-08-28) looks sane - should be no problem to apply.

Without this statement, I have to check sqlite_master before creating tables - 
which is "not so nice".

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

Re: [sqlite] Will Bitwise Operations Use an Index?

[Pavel Ivanov]

> In answer to your question, yes the index is used.  You can see instructions
> prefixed by Idx that aren't there when the index doesn't exist.

I just want to warn you: the index is used but not for finding
appropriate value of col_c (as your question seem to imply). It is
used only to find appropriate pair col_a, col_b and then SQLite will
scan all values of col_c to satisfy your third condition. There's no
way one can optimize searching rows satisfying some bitwise condition
with use of an index. So if your query was like this:
SELECT count(*) FROM table WHERE col_c & 32;
and you had index on col_c then it wouldn't be used - whole table
would be scanned.


Pavel

On Fri, Oct 9, 2009 at 4:42 AM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Daniel Wickes wrote:
>> I'm trying to optimise some of my queries, and I would like to know if
>> bitwise operators in terms will still use an index,
>
> SQLite provides you the tools to find out for yourself.  In the shell do
> '.explain' and then give it your query prefixed with 'EXPLAIN'.  You'll get
> the virtual db engine code for the query printed out.
>
> This page gives a rough guide to vdbe code.  (It does now use registers
> rather than a stack.)
>
>  http://www.sqlite.org/vdbe.html
>
> By far the easiest thing to do is explain a query you know uses the index
> and then explain your query and look for the differences.
>
> In answer to your question, yes the index is used.  You can see instructions
> prefixed by Idx that aren't there when the index doesn't exist.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkrO93oACgkQmOOfHg372QRXNQCeNSQ1r3o07Yj4n0RZ+kn9ePyT
> cy8An05WyzhnU9M8Y3+00dFf4/tOh6vH
> =hOpS
> -END PGP SIGNATURE-
> ___
> 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] Will Bitwise Operations Use an Index?

[Michal Seliga]

Daniel Wickes wrote:
> I'm trying to optimise some of my queries, and I would like to know if
> bitwise operators in terms will still use an index, or if I should be
> thinking about moving the more important values to separate columns that
> could be checked for equality.
> 
> At the moment, I have an index created much like:
> 
> CREATE INDEX table_idx ON table(col_a,col_b,col_c);
> 
> And then I am performing a query such as:
> 
> SELECT count(*) FROM table WHERE col_a = 'apple' AND col_b = 3 AND col_c
> & 32;
> 
> The key bit, if you'll pardon the pun, being the bitwise -and on col_c.
> Will this be using the index or will it be checking the table rows?
> 
> Any help is much appreciated.

  change your query a bit when you use bitwise operations, in your case when you
have 'AND col_c&32' you may add 'AND col_c>=32'. now index will be used, but it
only makes sense if there is not too much rows where col_c>=32. you can use this
optimalisation always, it shouldn't slow down things, but real effect will work
only for higher bits. also in cases when you make bitwise & with more bits, you
can add >= condition only for lowest expected bit.

> 
> Many thanks,
> 
> -- Dan
> ___
> 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] Will Bitwise Operations Use an Index?

[Pavel Ivanov]

>  change your query a bit when you use bitwise operations, in your case when 
> you
> have 'AND col_c&32' you may add 'AND col_c>=32'. now index will be used

col_c = 64 does pass second condition and doesn't pass first one. ;-)
Bitwise operators cannot be changed so easily to inequalities.


Pavel

On Fri, Oct 9, 2009 at 8:19 AM, Michal Seliga  wrote:
>
>
> Daniel Wickes wrote:
>> I'm trying to optimise some of my queries, and I would like to know if
>> bitwise operators in terms will still use an index, or if I should be
>> thinking about moving the more important values to separate columns that
>> could be checked for equality.
>>
>> At the moment, I have an index created much like:
>>
>>     CREATE INDEX table_idx ON table(col_a,col_b,col_c);
>>
>> And then I am performing a query such as:
>>
>>     SELECT count(*) FROM table WHERE col_a = 'apple' AND col_b = 3 AND col_c
>> & 32;
>>
>> The key bit, if you'll pardon the pun, being the bitwise -and on col_c.
>> Will this be using the index or will it be checking the table rows?
>>
>> Any help is much appreciated.
>
>  change your query a bit when you use bitwise operations, in your case when 
> you
> have 'AND col_c&32' you may add 'AND col_c>=32'. now index will be used, but 
> it
> only makes sense if there is not too much rows where col_c>=32. you can use 
> this
> optimalisation always, it shouldn't slow down things, but real effect will 
> work
> only for higher bits. also in cases when you make bitwise & with more bits, 
> you
> can add >= condition only for lowest expected bit.
>
>>
>> Many thanks,
>>
>> -- Dan
>> ___
>> 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] Will Bitwise Operations Use an Index?

[Swithun Crowe]

Hello

PI >  change your query a bit when you use bitwise operations, in your case 
when you
PI > have 'AND col_c&32' you may add 'AND col_c>=32'. now index will be used

PI col_c = 64 does pass second condition and doesn't pass first one. ;-) 
PI Bitwise operators cannot be changed so easily to inequalities.

Wasn't the idea that the index would get used for the equality test, and 
the more expensive bitwise comparison would then be run on a smaller 
number of rows?

I don't know if this is possible. But that is how I imagined the 
suggestion.

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

Re: [sqlite] Will Bitwise Operations Use an Index?

[Michal Seliga]

Pavel Ivanov wrote:
>>  change your query a bit when you use bitwise operations, in your case when 
>> you
>> have 'AND col_c&32' you may add 'AND col_c>=32'. now index will be used
> 
> col_c = 64 does pass second condition and doesn't pass first one. ;-)
> Bitwise operators cannot be changed so easily to inequalities.
> 
i didn't said this condition should be *replaced* , i said that inequality
should be *added*. then index could limit resultset, but bitwise operator will
still be used, only on hopefully smaller set of data

> 
> Pavel
> 
> On Fri, Oct 9, 2009 at 8:19 AM, Michal Seliga  
> wrote:
>>
>> Daniel Wickes wrote:
>>> I'm trying to optimise some of my queries, and I would like to know if
>>> bitwise operators in terms will still use an index, or if I should be
>>> thinking about moving the more important values to separate columns that
>>> could be checked for equality.
>>>
>>> At the moment, I have an index created much like:
>>>
>>> CREATE INDEX table_idx ON table(col_a,col_b,col_c);
>>>
>>> And then I am performing a query such as:
>>>
>>> SELECT count(*) FROM table WHERE col_a = 'apple' AND col_b = 3 AND col_c
>>> & 32;
>>>
>>> The key bit, if you'll pardon the pun, being the bitwise -and on col_c.
>>> Will this be using the index or will it be checking the table rows?
>>>
>>> Any help is much appreciated.
>>  change your query a bit when you use bitwise operations, in your case when 
>> you
>> have 'AND col_c&32' you may add 'AND col_c>=32'. now index will be used, but 
>> it
>> only makes sense if there is not too much rows where col_c>=32. you can use 
>> this
>> optimalisation always, it shouldn't slow down things, but real effect will 
>> work
>> only for higher bits. also in cases when you make bitwise & with more bits, 
>> you
>> can add >= condition only for lowest expected bit.
>>
>>> Many thanks,
>>>
>>> -- Dan
>>> ___
>>> 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] Will Bitwise Operations Use an Index?

[Pavel Ivanov]

> i didn't said this condition should be *replaced* , i said that inequality
> should be *added*. then index could limit resultset, but bitwise operator will
> still be used, only on hopefully smaller set of data

Oh, sorry, I didn't understand you correctly then. But I believe that
this condition will not narrow number of rows significantly anyway.

Pavel

On Fri, Oct 9, 2009 at 9:25 AM, Michal Seliga  wrote:
>
>
> Pavel Ivanov wrote:
>>>  change your query a bit when you use bitwise operations, in your case when 
>>> you
>>> have 'AND col_c&32' you may add 'AND col_c>=32'. now index will be used
>>
>> col_c = 64 does pass second condition and doesn't pass first one. ;-)
>> Bitwise operators cannot be changed so easily to inequalities.
>>
> i didn't said this condition should be *replaced* , i said that inequality
> should be *added*. then index could limit resultset, but bitwise operator will
> still be used, only on hopefully smaller set of data
>
>>
>> Pavel
>>
>> On Fri, Oct 9, 2009 at 8:19 AM, Michal Seliga  
>> wrote:
>>>
>>> Daniel Wickes wrote:
 I'm trying to optimise some of my queries, and I would like to know if
 bitwise operators in terms will still use an index, or if I should be
 thinking about moving the more important values to separate columns that
 could be checked for equality.

 At the moment, I have an index created much like:

     CREATE INDEX table_idx ON table(col_a,col_b,col_c);

 And then I am performing a query such as:

     SELECT count(*) FROM table WHERE col_a = 'apple' AND col_b = 3 AND 
 col_c
 & 32;

 The key bit, if you'll pardon the pun, being the bitwise -and on col_c.
 Will this be using the index or will it be checking the table rows?

 Any help is much appreciated.
>>>  change your query a bit when you use bitwise operations, in your case when 
>>> you
>>> have 'AND col_c&32' you may add 'AND col_c>=32'. now index will be used, 
>>> but it
>>> only makes sense if there is not too much rows where col_c>=32. you can use 
>>> this
>>> optimalisation always, it shouldn't slow down things, but real effect will 
>>> work
>>> only for higher bits. also in cases when you make bitwise & with more bits, 
>>> you
>>> can add >= condition only for lowest expected bit.
>>>
 Many thanks,

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

[sqlite] Table aliases

[Shaun Seckman (Firaxis)]

Happy Friday everyone!

I've got several tables each representing a specific
language which my application uses to access translated strings.  It'd
be extremely useful if I were able to alias a table as "CurrentLanguage"
as opposed to directly referencing the actual name.  This saves me from
having to tweak my lookup statements on the fly to change the table name
being accessed.  Is it possible to perform such an alias?  I know that I
could execute the line "CREATE TABLE CurrentLanguage AS SELECT * from
SomeLanguage" but will that duplicate all the data or just reference the
data?  I'd rather not take the memory hit as these tables are quite
large.  Suggestions?

 

-Shaun

 

 

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

Re: [sqlite] Table aliases

[Martin Engelschalk]

Hi,

yes, creating a table will duplicate your data. However, a view will not:

create view CurrentLanguage as select * from SomeLanguage;

see http://www.sqlite.org/lang_createview.html

Martin
 
Shaun Seckman (Firaxis) wrote:
> Happy Friday everyone!
>
> I've got several tables each representing a specific
> language which my application uses to access translated strings.  It'd
> be extremely useful if I were able to alias a table as "CurrentLanguage"
> as opposed to directly referencing the actual name.  This saves me from
> having to tweak my lookup statements on the fly to change the table name
> being accessed.  Is it possible to perform such an alias?  I know that I
> could execute the line "CREATE TABLE CurrentLanguage AS SELECT * from
> SomeLanguage" but will that duplicate all the data or just reference the
> data?  I'd rather not take the memory hit as these tables are quite
> large.  Suggestions?
>
>  
>
> -Shaun
>
>  
>
>  
>
> ___
> 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] Table aliases

[BareFeet]

Hi Shaun,

> It'd be extremely useful if I were able to alias a table as  
> "CurrentLanguage" as opposed to directly referencing the actual  
> name.  This saves me from having to tweak my lookup statements on  
> the fly to change the table name being accessed.  Is it possible to  
> perform such an alias?  I know that I could execute the line "CREATE  
> TABLE CurrentLanguage AS SELECT * from SomeLanguage" but will that  
> duplicate all the data or just reference the data?  I'd rather not  
> take the memory hit as these tables are quite
> large.  Suggestions?

You can simply create a view:

create view CurrentLanguage as select * from SomeLanguage;

That won't duplicate the data (create table as select etc would  
duplicate the data and would not update if the source table data  
changed). A view is just a reference to the original data.

If you are wanting to dynamically change the CurrentLanguage view in  
everyday ongoing use, then you probably shouldn't. It's not generally  
good to adjust the schema continually (ie drop and create a table or  
view etc). You'd be better to redesign your data so that it's all in  
one table, with the addition of a "Language" column. Then you can  
select the data you want for a particular language without adjusting  
your schema, such as:

select * from AllLanguages where Language = 'English';

or you could create a view, once, for each language, that refers to  
the one conglomerate table:

create view "English Language" as select * from AllLanguages where  
Language = 'English';

Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml




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

Re: [sqlite] Table aliases

[Wes Freeman]

I agree with Tom. With the proper indexes a single table for all
languages is the way to go.

I don't think a view for each language is necessary--just make a
function that takes language and whatever you use to look up the
localized strings, and returns the localized string.

Further, there are existing frameworks that do this sort of task
fairly well for several architectures. Not that there's anything wrong
with building you're own--but you might look at some of their design
to get ideas.
Various: http://www.gnu.org/software/gettext/manual/gettext.html
Qt C++: http://doc.trolltech.com/4.5/linguist-manual.html
Java: http://java.sun.com/developer/technicalArticles/Intl/ResourceBundles/

Wes

> If you are wanting to dynamically change the CurrentLanguage view in
> everyday ongoing use, then you probably shouldn't. It's not generally
> good to adjust the schema continually (ie drop and create a table or
> view etc). You'd be better to redesign your data so that it's all in
> one table, with the addition of a "Language" column. Then you can
> select the data you want for a particular language without adjusting
> your schema, such as:
>
> select * from AllLanguages where Language = 'English';
>
> or you could create a view, once, for each language, that refers to
> the one conglomerate table:
>
> create view "English Language" as select * from AllLanguages where
> Language = 'English';
>
> Tom
> BareFeet
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table aliases

[Simon Slavin]

On 9 Oct 2009, at 4:07pm, BareFeet wrote:

> If you are wanting to dynamically change the CurrentLanguage view in
> everyday ongoing use, then you probably shouldn't. It's not generally
> good to adjust the schema continually (ie drop and create a table or
> view etc). You'd be better to redesign your data so that it's all in
> one table, with the addition of a "Language" column.

I agree.  The OP is obviously treating the 'table' parameter as if it  
was a column value, so make it a real column value.  Simplifies things  
all round.

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

Re: [sqlite] SQLite database on a certain high-performance "SSD"

[Mark]

It's an ioDrive. We updated the firmware to the just-released version, 
and it made a WORLD of difference. It's now performing at the level we'd 
expect (which is quite impressive!)

Talking with their performance engineer, they do indeed suggest 
disabling the OS cache in some cases. We haven't gone that route yet to 
see what happens.


Ken wrote:
> Interesting
> 
> Mind if we ask what the SSD device brand and model is?
> 
> Is it a disk backed type of device with equal memory in front, I recall 
> seeing devices like this about 7 years ago. I'm thinking that the sync call 
> is causing the device to write its memory contents back out to disk (ie to be 
> persisted). Just a thought...
> 
> 
> 
> --- On Wed, 9/23/09, Pavel Ivanov  wrote:
> 
>> From: Pavel Ivanov 
>> Subject: Re: [sqlite] SQLite database on a certain high-performance "SSD"
>> To: "General Discussion of SQLite Database" 
>> Date: Wednesday, September 23, 2009, 11:21 AM
>>> Is the only change the absence
>> of a call to "fsync()" when turning
>>> synchronous off? If so, I can conclusively say that
>> fsync() is very slow
>>> on this storage device.
>> Yes, the only action of synchronous = off is to turn off
>> calls to
>> fsync() which is called at least twice during each commit.
>>
>> Pavel
>>
>> On Wed, Sep 23, 2009 at 11:40 AM, Mark 
>> wrote:
>>> On a RAID-5 array of 4x SAS disks, turning the sync
>> off made it about 2x
>>> faster, give or take.
>>>
>>> On the "SSD", it was about 150x faster.
>>>
>>> Is the only change the absence of a call to "fsync()"
>> when turning
>>> synchronous off? If so, I can conclusively say that
>> fsync() is very slow
>>> on this storage device.
>>>
>>> Thanks for the suggestion.
>>>
>>> Mark
>>>
>>>
>>> Pavel Ivanov wrote:
 If you execute

 pragma synchronous = off;

 you'll be able to compare performance with syncs
>> and without them. So
 if you make this comparison on standard spinning
>> disk and on SSD
 you'll see if syncs on SSD indeed extra-ordinary
>> slow.
 Pavel

 On Wed, Sep 23, 2009 at 10:09 AM, Mark 
>> wrote:
> It's very possible, but I don't know how to
>> tell. Is there an easy way
> to know if the sync() calls are taking
>> inordinately long?
> Mark
>
>
> Thomas Briggs wrote:
>>Is the sync necessary to commit a
>> transaction slow?  Performance of
>> that sync depends on the OS, file system,
>> hardwar, etc. IIRC, so IOs
>> may be fast but it's possible that the
>> syncs are killing you.
>>-T
>>
>> On Tue, Sep 22, 2009 at 5:14 PM, Mark
>> 
>> wrote:
>>> Lothar Scholz wrote:
 Hello Mark,

 Tuesday, September 22, 2009,
>> 3:53:48 AM, you wrote:
 M> I've currently got a loaner
>> high-performance flash-based "SSD" (let's
 M> just say it doesn't connect
>> to any disk controllers) that I'm testing
 M> for performance. I've run my
>> application against it, and I believe that
 M> I should see numbers MUCH
>> higher than I do. When I run my test app on a
 M> normal SATA 7200 RPM disk, I
>> get a certain performance, and on the "SSD"
 M> I get about 1/10th that
>> speed. On an array of SAS disks I get numbers
 M> that are about 5x faster
>> than my SATA disk, so my software itself isn't
 M> (I believe) the bottleneck.

 M> I'm wondering if anyone has
>> any tips for "optimizing" for this sort of
 M> storage solution.

 Throw it into the trash bin and
>> buy a new one which has a 3rd
 generation controller and at least
>> 64MB fast cache. The old JMicron
 controller that many low cost SSD
>> still use was developed for Flash
 USB sticks.

 With modern SSD like the latest
>> Samsung should give you at least the
 same performance as the SATA. If
>> it gets better depends on file size
 and cache. Are you sure that the
>> SAS RAID Controller is not keeping
 everything in the controller
>> cache?
>>> This isn't an "SSD". It's connected
>> directly to the PCI Express bus, and
>>> "low cost" it certainly is NOT. It's
>> much more valuable than the server
>>> it's plugged into.
>>>
>>> I've run benchmark tests (iometer),
>> and the benchmarks show it's as fast
>>> as the mfgr says it should be
>> (~700MB/sec read and write bandwidth,
>>>  >115,000 IOPS) but it performs
>> quite poorly when I run my app on it. I
>>> can't figure out why.
>>>
>>> Mark

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

[sqlite] rtree's rounding some integer values but not others.

[George Hartzell]

I've reduced a problem that I'm having to the following test case.

I insert two rows into an rtree table.  In the first case when I
select chromStart and chromEnd I get the same values as I inserted.
In the second case the chromEnd is 1 greater than what I inserted.  In
the third case chromStart is 1 les than what I inserted.

I'm using a copy of sqlite-3.6.18 that I compiled myself, using
defaults except for rtree support, on a Suse SLES 10 system.

It seems odd.  Are the values large enought that they're causing
problems with the 32-bit floats?  It almost seems like odd numbers are
rounded up/down depending on whether they're min or max?

Any help would be greatly appreciated.

Thanks,

g.

-- --- snip 

create virtual table snp130_rtree
using rtree
(snp130_rowid,
 chromMin, chromMax,
 strandMin, strandMax,
 chromStart, chromEnd);

insert into snp130_rtree
(snp130_rowid,
 chromMin, chromMax,
 strandMin, strandMax,
 chromStart, chromEnd)
values
(1, 1, 1, 1, 1, 1, 2);
insert into snp130_rtree
(snp130_rowid,
 chromMin, chromMax,
 strandMin, strandMax,
 chromStart, chromEnd)
values
(2, 1, 1, 1, 1, 19201046, 19201047);
insert into snp130_rtree
(snp130_rowid,
 chromMin, chromMax,
 strandMin, strandMax,
 chromStart, chromEnd)
values
(3, 1, 1, 1, 1, 19201045, 19201046);

select * from snp130_rtree;

-- --- snip 

>>~/src/sqlite-3.6.18/sqlite3 ./bug.db < bug.sql
1|1.0|1.0|1.0|1.0|1.0|2.0
2|1.0|1.0|1.0|1.0|19201046.0|19201048.0
3|1.0|1.0|1.0|1.0|19201044.0|19201046.0

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

[sqlite] FTS3 performance oddity

[Mark]

I have a database containing a single table, with FTS3:

create virtual table my_fts_table using fts3
(
last_name   text not null,
first_name  text not null,
middle_name text not null,
identifiers text not null,
address_1   text not null,
citytext not null,
state   text not null,
zip text not null
);

I loaded a large number of records (20 million) into this table. I then 
proceeded to run queries against this table, of the following form:

select rowid from my_fts_table where my_fts_table match @query limit 21

This query (usually) returns very quickly. For example, when @query is 
'nicky nigel', the query returns in mere milliseconds (59, and that's as 
measured by a web browser client when the server's doing the work). If I 
modify the query to be something like 'nicky nigel indiana', the query 
suddenly takes MUCH longer. Over 17 seconds, in fact. It returns fewer 
rows (2 versus 4), but it seems like it must be searching for each term 
individually and then doing the intersection of those results. 'indiana' 
of course returns LOTS of rows (7927809 to be exact), because it's not 
very selective.

I'm wondering if there's something I'm doing wrong, or whether this is 
just an artifact of the FTS3 implementation. I'd love to use it, it's 
simple and perfect for my needs (and I'm already using SQLite in this 
project so it's a great fit), but having the query suddenly take 
"forever" because someone included a state in the search just won't work 
for me.

Mark

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

Re: [sqlite] A rowid request

[D . Richard Hipp]

On Oct 9, 2009, at 9:02 AM, Marco Bambini wrote:

> Hello Dr. Hipp,
>
> I understand that you are probably against this request but I really  
> would like to have a way to automatically retrieve the rowid column  
> from a select statement without the needs to manually add the rowid  
> column to the sql. A pragma statement would be the ideal solution in  
> order to be able to control that behavior.
>
> I realize also that a rowid cannot always be returned as a result of  
> a select statement and in this case a NULL rowid column would be  
> more than acceptable.
>
> Please let me know if we can have some hope that this feature will  
> be added in a future update.


This is very unlikely to be added.


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] rtree's rounding some integer values but not others.

[Jay A. Kreibich]

On Fri, Oct 09, 2009 at 10:50:49AM -0700, George Hartzell scratched on the wall:

> It seems odd.  Are the values large enought that they're causing
> problems with the 32-bit floats?

  Yes.  32-bit IEEE 754 floats offer 23+1 bits of precision, which
  translates to about 7.2 base-10 digits.  Those numbers are 8 digits
  long, so you're going to start to see rounding and clipping oddness.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 performance oddity

[Scott Hess]

Your analysis is correct.

One way this is often fixed in full-text-search systems is to keep
index stats so that the more specific bits of the query can come
first.  In this case, "nicky" AND "nigel" gives you a small enough
result set that it would be more efficient to scan the matched
documents for "Indiana" than it would be to pull the "Indiana" hit
list and intersect with it.

I wouldn't hold your breath on that, though, it would require a lot of work :-).

You might be able to cobble something together above fts3.  Like maybe
isolating the city, state, and zip columns into another table (joined
with the fts3 table on rowid=docid), and indexed on those columns.
Then you could use that table to drop the common items from the query
(something like "select rowid from state_table where state = ? limit
1" will be very fast even if there are many matches).  Run the
resulting query against the fts3 table, putting the results in a temp
table, then join that against the state table for the words you
dropped out of the query.  I know that sounds absurd, but it might end
up not being THAT hard (at least compared to cobbling together an
alternate system entirely).

At minimum, for a query like 'nicky nigel indiana', you should be able
to quickly mock up a test by moving state to a separate table joined
with your fts table on rowid, and then have your WHERE clause throw
'nicky nigel' as a match against the fts table and 'indiana' for the
state in the state table.  If that's still terribly slow, then no need
to implement further.

[BTW, very impressed that you're pumping 20M rows in.  I certainly did
that in testing, but still amazing.]

-scott


On Fri, Oct 9, 2009 at 10:54 AM, Mark  wrote:
> I have a database containing a single table, with FTS3:
>
> create virtual table my_fts_table using fts3
> (
>        last_name   text not null,
>        first_name  text not null,
>        middle_name text not null,
>        identifiers text not null,
>        address_1   text not null,
>        city        text not null,
>        state       text not null,
>        zip         text not null
> );
>
> I loaded a large number of records (20 million) into this table. I then
> proceeded to run queries against this table, of the following form:
>
> select rowid from my_fts_table where my_fts_table match @query limit 21
>
> This query (usually) returns very quickly. For example, when @query is
> 'nicky nigel', the query returns in mere milliseconds (59, and that's as
> measured by a web browser client when the server's doing the work). If I
> modify the query to be something like 'nicky nigel indiana', the query
> suddenly takes MUCH longer. Over 17 seconds, in fact. It returns fewer
> rows (2 versus 4), but it seems like it must be searching for each term
> individually and then doing the intersection of those results. 'indiana'
> of course returns LOTS of rows (7927809 to be exact), because it's not
> very selective.
>
> I'm wondering if there's something I'm doing wrong, or whether this is
> just an artifact of the FTS3 implementation. I'd love to use it, it's
> simple and perfect for my needs (and I'm already using SQLite in this
> project so it's a great fit), but having the query suddenly take
> "forever" because someone included a state in the search just won't work
> for me.
>
> Mark
>
> ___
> 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] rtree's rounding some integer values but not others.

[George Hartzell]

George Hartzell writes:
 > I've reduced a problem that I'm having to the following test case.
 > 
 > I insert two rows into an rtree table.  In the first case when I
 > select chromStart and chromEnd I get the same values as I inserted.
 > In the second case the chromEnd is 1 greater than what I inserted.  In
 > the third case chromStart is 1 les than what I inserted.
 > 
 > I'm using a copy of sqlite-3.6.18 that I compiled myself, using
 > defaults except for rtree support, on a Suse SLES 10 system.
 > 
 > It seems odd.  Are the values large enought that they're causing
 > problems with the 32-bit floats?  It almost seems like odd numbers are
 > rounded up/down depending on whether they're min or max?
 > 
 > Any help would be greatly appreciated.
 > 
 > Thanks,
 > 
 > g.
 > 
 > -- --- snip 
 > 
 > create virtual table snp130_rtree
 >  using rtree
 >  (snp130_rowid,
 >   chromMin, chromMax,
 >   strandMin, strandMax,
 >   chromStart, chromEnd);
 > 
 > insert into snp130_rtree
 >  (snp130_rowid,
 >   chromMin, chromMax,
 >   strandMin, strandMax,
 >   chromStart, chromEnd)
 >  values
 >  (1, 1, 1, 1, 1, 1, 2);
 > insert into snp130_rtree
 >  (snp130_rowid,
 >   chromMin, chromMax,
 >   strandMin, strandMax,
 >   chromStart, chromEnd)
 >  values
 >  (2, 1, 1, 1, 1, 19201046, 19201047);
 > insert into snp130_rtree
 >  (snp130_rowid,
 >   chromMin, chromMax,
 >   strandMin, strandMax,
 >   chromStart, chromEnd)
 >  values
 >  (3, 1, 1, 1, 1, 19201045, 19201046);
 > 
 > select * from snp130_rtree;
 > 
 > -- --- snip 
 > 
 > >>~/src/sqlite-3.6.18/sqlite3 ./bug.db < bug.sql
 > 1|1.0|1.0|1.0|1.0|1.0|2.0
 > 2|1.0|1.0|1.0|1.0|19201046.0|19201048.0
 > 3|1.0|1.0|1.0|1.0|19201044.0|19201046.0
 > 
 > ___
 > sqlite-users mailing list
 > sqlite-users@sqlite.org
 > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
 > 
 > 
 > 

The following small C program seems to indicate that the problem is
that my numbers are too large for a 32-bit float.

Is this the correct interpretation?

A quick glance through the source code suggests that it would be
possible to build a version that stored coord's as either doubles or
ints, but in the integer case it seems like care would need to be
taking when computing areas and such.

g.

#include 

int main(int argc, char **argv)
{
  float f1;
  float f2;
  float f3;

  f1 = 19201045;
  f2 = 19201046;
  f3 = 19201047;

  printf("f1 is %f\n", f1);
  printf("f2 is %f\n", f2);
  printf("f3 is %f\n", f3);
}
~>>./foo
f1 is 19201044.00
f2 is 19201046.00
f3 is 19201048.00
~>>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SOLVED: Re: rtree's rounding some integer values but not others.

[George Hartzell]

George Hartzell writes:
 > I've reduced a problem that I'm having to the following test case.
 > 
 > I insert two rows into an rtree table.  In the first case when I
 > select chromStart and chromEnd I get the same values as I inserted.
 > In the second case the chromEnd is 1 greater than what I inserted.  In
 > the third case chromStart is 1 les than what I inserted.
 > 
 > I'm using a copy of sqlite-3.6.18 that I compiled myself, using
 > defaults except for rtree support, on a Suse SLES 10 system.
 > 
 > It seems odd.  Are the values large enought that they're causing
 > problems with the 32-bit floats?  It almost seems like odd numbers are
 > rounded up/down depending on whether they're min or max?
 > [...]

While browsing the rtree code in the fossil source archive I noticed a
comment about an rtree_i32 virtual table type, which seems to suit my
needs.

I don't see any mention of it on www.sqlite.org/rtree.html.  Is it
a mainstream feature or an undocumented/deprecated/... thing?

Thanks!

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

[sqlite] Need Help SQL

[Rick Ratchford]

I'm trying to correctly word an SQL statement.

Data Fields:  ID, Date, Month, Day, Year, Price

Problem: When provided the starting Month/Day numbers, and ending Month/Day
numbers, what is the correct way to SQL the database so that the recordset
created returns as follows (assume 4 years of data):

Example:

Starting Month = 3
Starting Day = 12

Ending Month = 7
Ending Day = 5

The recordset created needs to return the data in these columns:

Month   Day   Price
===
3  12  24.50
3  12  12.34
3  12  33.01
3  12   8.76
3  13  11.72
3  13  77.55
3  13  12.00
.
.
7   5   99.87
7   56.22
7   5   54.61


In other words, all the 3/12's first, then 3/13's, then 3/14's, etc. all the
way down to the ending Month/Date.

Where I really get stuck is when the Starting Month number is greater than
the Ending Month number. For example, say I want the starting Month/Day as
10/22 and the ending Month/Day as 4/16. Simply stating WHERE Month >= Start
Month AND Month <= End Month doesn't seem correct. Since I want to return
all the prices between 10/22 and 4/16 of each year of data I have in the
table, no Month number could be greater than/equal to 10  and also less
than/equal to 4.

I'm still pretty green on working out these SQL statements. I'm hoping some
suggestions will help.

Thanks.

Rick



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

[sqlite] Wiki consisting of rows in a db table

[Kelly Jones]

Consider a wiki that lets you edit rows in a db table. Each page is a
row in the table, and has fields that anyone can edit. Like all wikis,
it keeps a history of edits (including who made the edits), and lets
you revert an edit, or even delete a row (page) completely.

Has anyone implemented something like this? This MediaWiki extension:

http://www.mediawiki.org/wiki/Extension:WikiDB

looks interesting, but it doesn't appear to support full SQL querying?

I'm looking more for a wiki-editable database, not a database-enabled
wiki, though having MediaWiki functionality would be nice.
-- 
We're just a Bunch Of Regular Guys, a collective group that's trying
to understand and assimilate technology. We feel that resistance to
new ideas and technology is unwise and ultimately futile.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help SQL

[Igor Tandetnik]

Rick Ratchford 
> Data Fields:  ID, Date, Month, Day, Year, Price
> 
> Problem: When provided the starting Month/Day numbers, and ending
> Month/Day numbers, what is the correct way to SQL the database so
> that the recordset created returns as follows (assume 4 years of
> data): 
> 
> In other words, all the 3/12's first, then 3/13's, then 3/14's, etc.
> all the way down to the ending Month/Date.
> 
> Where I really get stuck is when the Starting Month number is greater
> than the Ending Month number. For example, say I want the starting
> Month/Day as 10/22 and the ending Month/Day as 4/16. Simply stating
> WHERE Month >= Start Month AND Month <= End Month doesn't seem
> correct. Since I want to return all the prices between 10/22 and 4/16
> of each year of data I have in the table, no Month number could be
> greater than/equal to 10  and also less than/equal to 4.

Try something like this:

select Month, Day, Price from mytable
where ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300 <
   ((:EndMonth - :StartMonth)*100 + (:EndDay - :StartDay) + 1300) % 1300
order by ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300;

Igor Tandetnik

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

[sqlite] sqlite build issues on IBM's HP-UX 11i

[Dr. David Kirkby]

Hi,

I work on the Sage maths project

http://www.sagemath.org/

which aims to create a viable free open source alternative to Magma, 
Maple, Mathematica and Matlab.

Sage uses sqlite. Whilst there are no official plans to support HP-UX, 
the fact I have an HP C3600 with a PA-RISC processor which runs HP-UX, I 
have recently started testing Sage packages on that.

I tried to build sqlite-3.6.18 on HP-UX 11.11 (v1)

$ uname -a
HP-UX hpbox B.11.11 U 9000/785 2016698240 unlimited-user license

with gcc 4.4.0

$ gcc -v
Using built-in specs.
Target: hppa1.1-hp-hpux11.11
Configured with: /tmp/gcc-4.4.0.tar.gz/gcc-4.4.0/configure 
--host=hppa1.1-hp-hpux11.11 --target=hppa1.1-hp-hpux11.11 
--build=hppa1.1-hp-hpux11.11 --prefix=/opt/hp-gcc-4.4.0 --with-gnu-as 
--without-gnu-ld --enable-threads=posix --enable-languages=c,c++ 
--with-gmp=/proj/opensrc/be/hppa1.1-hp-hpux11.11 
--with-mpfr=/proj/opensrc/be/hppa1.1-hp-hpux11.11
Thread model: posix
gcc version 4.4.0 (GCC)

You will be pleased to know sqlite does build. However, it does give 
over 17,000 warning messages, which I thought I'd convey.

  gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" 
-DPACKAGE_VERSION=\"3.6.18\" "-DPACKAGE_STRING=\"sqlite 3.6.18\"" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\"; -DPACKAGE=\"sqlite\" 
-DVERSION=\"3.6.18\" -D_FILE_OFFSET_BITS=64 -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 
-DHAVE_INTTYPES_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 
-DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 
-DHAVE_GMTIME_R=1 -I. -I. -DSQLITE_THREADSAFE=1 -g -O2 -MT sqlite3.lo 
-MD -MP -MF .deps/sqlite3.Tpo -c sqlite3.c  -fPIC -DPIC -o .libs/sqlite3.o
/var/tmp//ccv990We.s: Assembler messages:
/var/tmp//ccv990We.s:587: Warning: .stabs: description field '1161d' too 
big, try a different debug format
/var/tmp//ccv990We.s:589: Warning: .stabs: description field '11669' too 
big, try a different debug format



I could send you a config.log and make.log, but I don't really have time 
to look at this code myself. The fact HP-UX is not a supported platform, 
and the fact sqlite does build, leaves it well down my list of 
priorities. There are more important platforms where packages do not 
build. They clearly take higher priority over package which just give 
warnings on an unsupported OS.

But if a developer wants to take a look, and does not have access to an 
HP-UX machine, I can give you an account on the machine, where you can 
test it yourself.

PS, it would be good if you did not automatically add -g to generate 
debug information. Why not let people set that in CFLAGS if they want 
it. Or if not, how about a configure option to not add debugging 
information?

If a *serious* developer wants access, drop me a private email with your 
preferred user name.

Dave

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

Re: [sqlite] Need Help SQL

[Rick Ratchford]

Thank you Igor. I will. :-)

Cheers!
 
Rick 
 
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
#>boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Friday, October 09, 2009 6:46 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Need Help SQL
#>
#>Rick Ratchford 
#>> Data Fields:  ID, Date, Month, Day, Year, Price
#>>
#>> Problem: When provided the starting Month/Day numbers, and ending
#>> Month/Day numbers, what is the correct way to SQL the database so
#>> that the recordset created returns as follows (assume 4 years of
#>> data):
#>>
#>> In other words, all the 3/12's first, then 3/13's, then 3/14's, etc.
#>> all the way down to the ending Month/Date.
#>>
#>> Where I really get stuck is when the Starting Month number is greater
#>> than the Ending Month number. For example, say I want the starting
#>> Month/Day as 10/22 and the ending Month/Day as 4/16. Simply stating
#>> WHERE Month >= Start Month AND Month <= End Month doesn't seem
#>> correct. Since I want to return all the prices between 10/22 and 4/16
#>> of each year of data I have in the table, no Month number could be
#>> greater than/equal to 10  and also less than/equal to 4.
#>
#>Try something like this:
#>
#>select Month, Day, Price from mytable
#>where ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300 <
#>   ((:EndMonth - :StartMonth)*100 + (:EndDay - :StartDay) + 1300)
#>% 1300
#>order by ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300;
#>
#>Igor Tandetnik
#>
#>___
#>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