Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[BareFeetWare]

On 06/05/2010, at 2:51 PM, Dan Bishop wrote:

> BareFeetWare wrote:
>> 

>> I've had the same issue. In the end I had to parse my view functions in my 
>> own code and look for functions that give a particular type of result. So, 
>> for instance, round() gives an integer, round(..., 2) gives a real, concat() 
>> gives text. I also look for a cast(... as type) to use that declared type. 
>> It's fiddly and I would have hoped SQLite would have at least declared the 
>> type if a cast was present, but it seems not.
>> 

> A CAST expression could be assigned a declared type

Yes, that should be fairly easy to do in SQLite, simpler than my adding it in 
my own code (which then requires my own parser - not a trivial exercise).

> but it would be difficult to implement it for expressions in general.  Not 
> only would you have to declare a type for every function

Yes, probably half the functions don't have a consistently returned type (eg 
length, concat). The ones that don't (eg coalesce) should just be left to 
return a null (unknown) type. The schema developer can simply wrap those 
expressions in a cast if they want a predetermined output type.

> but you'd have to do it for operators as well, and what type should A * B be 
> if A is "SMALLINT" and B is "UINT"?

I hadn't thought of operators. As far as I know, mathematical operators (* / + 
-) only work with integers and reals, Perhaps the result type could be set to 
"numeric" or some other superclass. Or, to get real fancy, if the two operands 
are declared integers, then the result is an integer; if one is real then the 
result is real. AFAIK SMALLINT and UINT are both treated by SQLite as integer, 
so the result would be an integer.

> Should relational operators have a declared type of BOOLEAN?

Good idea.

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
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] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Dan Bishop]

BareFeetWare wrote:
> On 04/05/2010, at 3:14 AM, Stefan Keller wrote:
>
>   
>> But in SQLite if a view column comes from a function result or some 
>> computation, then the column type is NULL...!? It's not taking the 
>> result-type as mentioned in the manual
>> (http://www.sqlite.org/lang_select.html) - even when I try to do a CAST in 
>> the projection clause, like this:
>>
>>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>>
>> I mean that a VIEW should behave like a (read only) TABLE in any case.
>> => Is there a way to give such columns a type anyway?
>> 
>
> I've had the same issue. In the end I had to parse my view functions in my 
> own code and look for functions that give a particular type of result. So, 
> for instance, round() gives an integer, round(..., 2) gives a real, concat() 
> gives text. I also look for a cast(... as type) to use that declared type. 
> It's fiddly and I would have hoped SQLite would have at least declared the 
> type if a cast was present, but it seems not.
>
>   
A CAST expression could be assigned a declared type, but it would be 
difficult to implement it for expressions in general.  Not only would 
you have to declare a type for every function, but you'd have to do it 
for operators as well, and what type should A * B be if A is "SMALLINT" 
and B is "UINT"?  Should relational operators have a declared type of 
BOOLEAN?

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

Re: [sqlite] I don't understand locking

[Dan Kennedy]

On May 5, 2010, at 8:32 PM, myomancer wrote:

> Dear Users
>
> I've spent hours reading various web-based documents, examined lots of
> code snippets, written some code of my own, but I still patently do
> not understand SQLite locks.

4th paragraph of this page (The presence of a busy...) describes the
condition your program is hitting:

   http://www.sqlite.org/c3ref/busy_handler.html




>
> My misunderstanding is probably best illustrated with a concrete
> example written in C.
>
> #include 
> #include 
> #include 
>
> int main()
> {
>   sqlite3 *db;
>   int error;
>sqlite3_stmt *res;
>   int count;
>   char *sqlite_query;
>
>   error = sqlite3_open_v2("simple.db3", , SQLITE_OPEN_READWRITE, 0);
>   if(error)
>   {
>   fprintf(stderr, "Can't open database: %s\n", 
> sqlite3_errmsg(db));
>   sqlite3_close(db);
>   exit(1);
>   }
>
>   sqlite3_busy_timeout(db, 1);
>
>   sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
>   error = sqlite3_prepare_v2(db, "select count from table1", 100, ,
> NULL);
>   if (error != SQLITE_OK)
>   {
>   printf("SQLITE_OK is not OK - %d\n", error);
>   exit(1);
>   }
>
>   while (sqlite3_step(res) == SQLITE_ROW)
>   {
>   count = sqlite3_column_int(res, 0);
>   printf("%u", count);
>   }
>   sqlite3_finalize(res);
>
>   count++;
>   sqlite_query = sqlite3_mprintf("update table1 set count = %d",
> count);
>   error = sqlite3_exec(db, sqlite_query, NULL, NULL, NULL);
>   if(error != SQLITE_OK)
>   {
>   puts(" - error updating count");
>   }
>   else
>   {
>   puts(" - updated OK");
>   }
>
>   sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
>   sqlite3_free(sqlite_query);
>   sqlite3_close(db);
>
>   return 0;
> }
>
> When I run this serially, I get:
> $ ./demo
> 214 - updated OK
> $ ./demo
> 215 - updated OK
> $ ./demo
> 216 - updated OK
>
> When I run it in parallel, using this script:
>
> #!/bin/sh
>
> count=0
> limit=4
> while [ ${count} -lt ${limit} ]
> do
>   count=`expr $count + 1`
>   ./demo 2>&1 &
> done
>
> I get:
>
> $ ./multi.sh
> 219 - updated OK
> 219 - error updating count
> 220 - updated OK
> 220 - error updating count
>
> I thought that this line...
>
>  sqlite3_busy_timeout(db,
> 1);
>
> ...would give me a 10 second window where SQLite would gracefully,
> invisibly take care of locking for me, as per this documentation:
>
> "This routine sets a busy handler that sleeps for a specified amount
> of time when a table is locked. The handler will sleep multiple times
> until at least "ms" milliseconds of sleeping have accumulated. After
> at least "ms" milliseconds of sleeping, the handler returns 0 which
> causes sqlite3_step() to return SQLITE_BUSY or SQLITE_IOERR_BLOCKED."
>
> But things go wrong long before 10 seconds have elapsed, and I get the
> error messages shown.
>
> Now, clearly, SQLite is capable of handling locks, and the fault lies
> with me. Maybe I've simply missed a nuance somewhere, or maybe my
> understanding is fundamentally wrong. I just don't know. I would turn
> cartwheels if somebody could show me what I'm doing wrong in my code,
> and spoon-feed me the canonical way of doing it the right way. I am
> keen to use SQLite, but this locking impass is currently a sticking
> point for me.
>
> Many thanks
> Peter
> ___
> 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] Read-only tables in in-memory database?

[Igor Tandetnik]

daniel.hagl...@trafikverket.se wrote:
> There seems to be lots of information in the mailing list regarding read-only 
> databases and locking when it comes to databases on
> disk. Both locking and read-only mode seems to be functionality requiring a 
> file on disk. Is it even possible to have locking or
> read-only mode with in-memory databases?  

An in-memory database can never be accessed by more than one connection, so 
locking is moot. A read-only in-memory database makes no sense: the database is 
empty when created, so if you can't write to it, it will remain empty. What 
good is a database with no data in it?

> I am building a cache that will be publicly available. This is done using an 
> SQLite  :memory: database for storage. I want to
> fill a table with data and then make it read-only to prevent any SQL 
> injection attempts. Regular SQL injection mitigation
> techniques such as parameterization is not possible in this application. In a 
> stand-alone database engine this could have been
> handled with access control but using SQLite I see no way to prevent SQL 
> Injection in my specific application.

You can do access control via sqlite3_set_authorizer: 
http://sqlite.org/c3ref/set_authorizer.html
-- 
Igor Tandetnik

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

[sqlite] Misspelling in SQLite 2

[Michael Kohler]

Hi again,

I got another patch for SQLite in which I changed "non-existent" to 
"nonexistent". Maybe you're interested in this too.

Sincerely,

Michael Kohler


diff -r fa1e1974b034 security/nss/lib/sqlite/sqlite3.c
--- a/security/nss/lib/sqlite/sqlite3.cWed May 05 21:57:11 2010 +0200
+++ b/security/nss/lib/sqlite/sqlite3.cThu May 06 02:23:19 2010 +0200
@@ -98109,17 +98109,17 @@ SQLITE_PRIVATE void sqlite3ConnectionClo
 **MERGE_COUNT   segments
 **   16   25
 **8   12
 **4   10
 **26
 **
 ** This appears to have only a moderate impact on queries for very
 ** frequent terms (which are somewhat dominated by segment merge
-** costs), and infrequent and non-existent terms still seem to be fast
+** costs), and infrequent and nonexistent terms still seem to be fast
 ** even with many segments.
 **
 ** TODO(shess) That said, it would be nice to have a better query-side
 ** argument for MERGE_COUNT of 16.  Also, it is possible/likely that
 ** optimizations to things like doclist merging will swing the sweet
 ** spot around.
 **
 **
  
_
http://redirect.gimas.net/?n=M1004xjajah2
Über Messenger günstiger telefonieren? Sagen Sie "Ja" zu JAJAH!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Misspelling in SQLite

[Michael Kohler]

Hi,

I made a patch for Mozilla Firefox and I got told that Mozilla doesn't want to 
fix upstream bugs and that I need to send the patch to you. The 
attached patch is a patch against Mozilla Firefox code, but I guess it 
won't be that hard to convert it to a "normal" patch.

Feel free to reply if you have any questions.

Sincerely,

Michael Kohler
_
http://redirect.gimas.net/?n=M1004xNoSpam2
Angst vor Spam? Hotmail schützt Sie mit modernster Technologie!___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] I don't understand locking

[myomancer]

Dear Users

I've spent hours reading various web-based documents, examined lots of
code snippets, written some code of my own, but I still patently do
not understand SQLite locks.

My misunderstanding is probably best illustrated with a concrete
example written in C.

#include 
#include 
#include 

int main()
{
sqlite3 *db;
int error;
sqlite3_stmt *res;
int count;
char *sqlite_query;

error = sqlite3_open_v2("simple.db3", , SQLITE_OPEN_READWRITE, 0);
if(error)
{
fprintf(stderr, "Can't open database: %s\n", 
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}

sqlite3_busy_timeout(db, 1);

sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
error = sqlite3_prepare_v2(db, "select count from table1", 100, ,
NULL);
if (error != SQLITE_OK)
{
printf("SQLITE_OK is not OK - %d\n", error);
exit(1);
}

while (sqlite3_step(res) == SQLITE_ROW)
{
count = sqlite3_column_int(res, 0);
printf("%u", count);
}
sqlite3_finalize(res);

count++;
sqlite_query = sqlite3_mprintf("update table1 set count = %d",
count);
error = sqlite3_exec(db, sqlite_query, NULL, NULL, NULL);
if(error != SQLITE_OK)
{
puts(" - error updating count");
}
else
{
puts(" - updated OK");
}

sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
sqlite3_free(sqlite_query);
sqlite3_close(db);

return 0;
}

When I run this serially, I get:
$ ./demo
214 - updated OK
$ ./demo
215 - updated OK
$ ./demo
216 - updated OK

When I run it in parallel, using this script:

#!/bin/sh

count=0
limit=4
while [ ${count} -lt ${limit} ]
do
count=`expr $count + 1`
./demo 2>&1 &
done

I get:

$ ./multi.sh
219 - updated OK
219 - error updating count
220 - updated OK
220 - error updating count

I thought that this line...

  sqlite3_busy_timeout(db,
1);

...would give me a 10 second window where SQLite would gracefully,
invisibly take care of locking for me, as per this documentation:

"This routine sets a busy handler that sleeps for a specified amount
of time when a table is locked. The handler will sleep multiple times
until at least "ms" milliseconds of sleeping have accumulated. After
at least "ms" milliseconds of sleeping, the handler returns 0 which
causes sqlite3_step() to return SQLITE_BUSY or SQLITE_IOERR_BLOCKED."

But things go wrong long before 10 seconds have elapsed, and I get the
error messages shown.

Now, clearly, SQLite is capable of handling locks, and the fault lies
with me. Maybe I've simply missed a nuance somewhere, or maybe my
understanding is fundamentally wrong. I just don't know. I would turn
cartwheels if somebody could show me what I'm doing wrong in my code,
and spoon-feed me the canonical way of doing it the right way. I am
keen to use SQLite, but this locking impass is currently a sticking
point for me.

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

Re: [sqlite] A newb performance question

[Astley Le Jasper]

Hi Igor,

I'm getting 

"0","0","TABLE first_table WITH INDEX  first_table_target_field_id"
"1","1","TABLE second_table USING PRIMARY KEY"
"2","2","TABLE third_table USING PRIMARY KEY"

... so I presume it is then. (Is there anywhere that explains how to
interpret this?)

Thanks

ALJ




On May 5, 1:45 pm, "Igor Tandetnik"  wrote:
> Astley Le Jasper wrote:
> > Given the example below where 'first_table' could be huge, are sqlite
> > or other relational databases clever enough to do the WHERE filter
> > before doing the joins.
>
> I would expect SQLite to do it this way, yes. Especially if there's an index 
> on first_table.target_field. You can confirm with EXPLAIN QUERY PLAN
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://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] Read-only tables in in-memory database?

[daniel.haglund]

There seems to be lots of information in the mailing list regarding read-only 
databases and locking when it comes to databases on disk. Both locking and 
read-only mode seems to be functionality requiring a file on disk. Is it even 
possible to have locking or read-only mode with in-memory databases?

I am building a cache that will be publicly available. This is done using an 
SQLite  :memory: database for storage. I want to fill a table with data and 
then make it read-only to prevent any SQL injection attempts. Regular SQL 
injection mitigation techniques such as parameterization is not possible in 
this application. In a stand-alone database engine this could have been handled 
with access control but using SQLite I see no way to prevent SQL Injection in 
my specific application. A read-only attribute on a table would have solved my 
problem. Locking could possibly be used to solve this problem but I am unsure.

Any ideas?

Best regards,

Daniel Haglund

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

Re: [sqlite] SELECT question (computing day of week the usingstrftime() function)

[Ed Sims]

Please cancel my subscription to this list!! My Mail client is overwhelmed.


Roger Andersson wrote:
>> I need to find out how many specific weekdays (e.g., how many 
>> Sundays) I have in any given range of dates.
>> My problem: How to use the COUNT function in combination with 
>> the strftime() function.
>>
>> 
> Maybe something like
>
> sqlite3 test.db
> SQLite version 3.6.23
> sqlite> CREATE TABLE test (date TEXT, money INTEGER);
> sqlite> INSERT INTO test VALUES('2007-07-20', 1000);
> sqlite> INSERT INTO test VALUES('2007-07-21', 2100);
> sqlite> INSERT INTO test VALUES('2007-07-22', 2200);
> sqlite> INSERT INTO test VALUES('2007-07-27', 7000);
> sqlite> INSERT INTO test VALUES('2007-07-28', 2800);
> sqlite> INSERT INTO test VALUES('2007-07-29', 2900);
> sqlite> INSERT INTO test VALUES('2007-07-22', 9200);
> sqlite> SELECT strftime('%w', date) weekday, count(*) cnt FROM test GROUP BY
> weekday;
> weekday|cnt
> 0|3
> 5|2
> 6|2
>
> ___
> 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] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[BareFeetWare]

On 04/05/2010, at 3:14 AM, Stefan Keller wrote:

> But in SQLite if a view column comes from a function result or some 
> computation, then the column type is NULL...!? It's not taking the 
> result-type as mentioned in the manual
> (http://www.sqlite.org/lang_select.html) - even when I try to do a CAST in 
> the projection clause, like this:
> 
>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
> 
> I mean that a VIEW should behave like a (read only) TABLE in any case.
> => Is there a way to give such columns a type anyway?

I've had the same issue. In the end I had to parse my view functions in my own 
code and look for functions that give a particular type of result. So, for 
instance, round() gives an integer, round(..., 2) gives a real, concat() gives 
text. I also look for a cast(... as type) to use that declared type. It's 
fiddly and I would have hoped SQLite would have at least declared the type if a 
cast was present, but it seems not.

I actually raised this issue on this list back in September 2009:

On 22/09/2009, at 10:08 AM, BareFeet wrote:

>> 4. Improve the declared_type result of a column to show a declared type for 
>> an expression in a view. The declared_type C call and the pragma 
>> table_info() SQL call currently return the type of a column in a view if the 
>> column is simply referring to a column in a table. But if the column in the 
>> view is an expression, it returns null. I would like to see it return the 
>> declared type of the outer function of the expression, since most functions 
>> have a known result type. For instance, if a column in a view is defined as 
>> cast(mycomplexfunction() as integer), then the declared type is known to be 
>> an integer. If the function is abs(), the type is real. If the function is 
>> length(), the type is integer. Obviously a few functions such as coalesce() 
>> have varied types so they would still have to show a declared_type of null, 
>> but the cast() function gives the SQL author the chance to explicitly 
>> declare the type of such a column.

On 06/05/2010, at 7:59 AM, Pavel Ivanov wrote:

>> I interpret the silence on the lis that anyone agrees that SQLite has a bug
> 
> Generally silence on this list means that everybody disagrees with you and/or 
> doesn't see enough arguments in your email to even start any
> discussion. When everybody agrees that SQLite has a bug you get a lot of 
> responses almost immediately.

In my case, my silence was "oh good, someone else is mentioning this issue. I 
won't waste bandwidth by saying "me too" but will watch closely for any 
replies".

>> Why do you want declared data type (which basically doesn't have any meaning 
>> in SQLite at all) in a view in a first place? Why having value type is not 
>> enough?

I want declared types of a view column for the same reasons I want declared 
types of a table column. For instance, I can format the output correctly, such 
as tight aligned for integers, two decimal places for reals, left aligned and 
wider for text, special handling for a blob.

Only having value type means I have to search through the results of a query to 
find the first non-null result value and examine it's type and hope that it's 
the same as the column's (un)declared type. That's inefficient. Obviously, when 
I am displaying individual cells of data I can look at the value type, but the 
declared type of the column means I can pre-format the column before displaying 
the data.

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
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] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Pavel Ivanov]

> I interpret the silence on the lis that anyone agrees that SQLite has a bug

Generally silence on this list means that everybody disagrees with you
and/or doesn't see enough arguments in your email to even start any
discussion. When everybody agrees that SQLite has a bug you get a lot
of responses almost immediately.

> because there seems to be no way to get VIEWS returning the column type if the
> column is calculated or a function.

I've asked you a question and you didn't seem to answer it. Why do you
want declared data type (which basically doesn't have any meaning in
SQLite at all) in a view in a first place? Why having value type is
not enough? Why don't you describe your situation and what you are
trying to achieve in more details?

> This also breaks compatibility as
> mentioned in
> http://www.sqlite.org/datatype3.html ("SQL statement that work on
> statically typed
> databases should work the same way in SQLite.").
> => Time for a ticket?

No, it doesn't break compatibility. Queries work the same way as in
other databases and return to you the same results. AFAIK, queries
don't have to always return to you information about data type of
underlying table column or function (which btw can return different
data type for each row in SQLite). Datatype system is completely
different in SQLite and nobody claimed it should be compatible...


Pavel

On Wed, May 5, 2010 at 5:32 PM, Stefan Keller  wrote:
> I interpret the silence on the lis that anyone agrees that SQLite has a bug
> because there seems to be no way to get VIEWS returning the column type if the
> column is calculated or a function. This also breaks compatibility as
> mentioned in
> http://www.sqlite.org/datatype3.html ("SQL statement that work on
> statically typed
> databases should work the same way in SQLite.").
> => Time for a ticket?
>
> -S.
>
> 2010/5/3 Stefan Keller :
>> Unfortunately the application which reads from this view needs that
>> all columns are typed - even if the values types deviate from it - and
>> I think this is a logical assumption. So, I fear I do have only one
>> chance and SQLite doesn't let me do it:
>>
>> CREATE VIEW myview AS
>>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>>
>> To me it seems like an inconsistency (or bug) when TABLES allow
>> declaration of types and VIEWS only eventually.
>>
>> -S.
>>
>> 2010/5/3 Simon Slavin :
>>>
>>> On 3 May 2010, at 6:14pm, Stefan Keller wrote:
>>>
 But in SQLite if a view column comes from a function result or some
 computation, then the column type is NULL...!? It's not taking the
 result-type as mentioned in the manual
 (http://www.sqlite.org/lang_select.html) - even when I try to do a
 CAST in the projection clause, like this:

  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;

 I mean that a VIEW should behave like a (read only) TABLE in any case.
 => Is there a way to give such columns a type anyway?
>>>
>>> You get two chances to CAST, one when you define the VIEW, and another when 
>>> you SELECT from the VIEW.  If one of them doesn't enforce the type of 
>>> evaluation you want, the other probably will.  As you say, VIEW columns 
>>> don't have types at all.
>>>
>>> Simon.
>>> ___
>>> 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] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

I interpret the silence on the lis that anyone agrees that SQLite has a bug
because there seems to be no way to get VIEWS returning the column type if the
column is calculated or a function. This also breaks compatibility as
mentioned in
http://www.sqlite.org/datatype3.html ("SQL statement that work on
statically typed
databases should work the same way in SQLite.").
=> Time for a ticket?

-S.

2010/5/3 Stefan Keller :
> Unfortunately the application which reads from this view needs that
> all columns are typed - even if the values types deviate from it - and
> I think this is a logical assumption. So, I fear I do have only one
> chance and SQLite doesn't let me do it:
>
> CREATE VIEW myview AS
>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>
> To me it seems like an inconsistency (or bug) when TABLES allow
> declaration of types and VIEWS only eventually.
>
> -S.
>
> 2010/5/3 Simon Slavin :
>>
>> On 3 May 2010, at 6:14pm, Stefan Keller wrote:
>>
>>> But in SQLite if a view column comes from a function result or some
>>> computation, then the column type is NULL...!? It's not taking the
>>> result-type as mentioned in the manual
>>> (http://www.sqlite.org/lang_select.html) - even when I try to do a
>>> CAST in the projection clause, like this:
>>>
>>>  SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable;
>>>
>>> I mean that a VIEW should behave like a (read only) TABLE in any case.
>>> => Is there a way to give such columns a type anyway?
>>
>> You get two chances to CAST, one when you define the VIEW, and another when 
>> you SELECT from the VIEW.  If one of them doesn't enforce the type of 
>> evaluation you want, the other probably will.  As you say, VIEW columns 
>> don't have types at all.
>>
>> Simon.
>> ___
>> 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] [server] HTTP + SQLite bundled as single EXE?

[Gilles Ganault]

On Mon, 3 May 2010 08:57:04 -0400, Reid Thompson
 wrote:
>http://code.google.com/p/mongoose/

Right, I got Lua and SQLite working as a CGI call. The alternative is
to use the Lua-based Xavante web server and include SQLite; I'll try
to get this working tomorrow. Thank you.

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

Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

[Reid Thompson]

On Mon, May 03, 2010 at 03:01:26PM +0400, Alexey Pechnikov wrote:
> See http://wiki.tcl.tk/15722 Add SQLite into it - about few minuts of time.
> 
> 2010/5/3 Gilles Ganault :
> > I don't know if it'd be easier to combine existing HTTP server +
> > SQLite

ditto
http://code.google.com/p/mongoose/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite in low memory devices

[Israel Lins Albuquerque]

Well I have a port to latest version (3.6.23.1) of sqlite to palmos, and in 
some devices with this S.O. 
had only 2MB of Dynamic Heap. On this environments sqlite doesn't works pretty 
well, then to work I had 
to set the cache size (PRAGMA cache_size) for all attached database, 
calculating the possible value based 
on the amouth of free memory and the page size. 

Hanging this on mind, on vacuum I had to do the same thing, but on vacuum the 
sqlite make attach of a 
memory database and that need use the same size of cache used by main database, 
then, I modified the 
sqlite code to do this, and now vacuum will work. I'm sending attached the path 
for this. 

-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] Accessing SQLite from Lua?

[Gilles Ganault]

On Wed, 05 May 2010 13:32:24 +0200, Gilles Ganault
 wrote:
>I'd like to know how to use this from a Lua script.

For Lua newbies like me who'd like to access an SQLite database
through the LuaSql interface, here's how to do it:

1. In the directory where the Lua interpreter is located, create a
sub-directory \luasql

2. In this sub-directory, from http://luaforge.net/frs/?group_id=12,
download and unzip luasql-2.1.1-sqlite3-win32-lua51.zip, which
contains sqlite3.dll (a Lua-specific sqlite3.dll, not the one from
www.sqlite.org)

3. In the main directory, create a source file eg. test.lua:

--
require "luasql.sqlite3"

env = luasql.sqlite3()
conn = env:connect("test.sqlite")

assert(conn:execute("create table if not exists tbl1(one varchar(10),
two smallint)"))
assert(conn:execute("insert into tbl1 values('hello!',10)"))
assert(conn:execute("insert into tbl1 values('goodbye',20)"))

cursor = assert(conn:execute("select * from tbl1"))
row = {}
while cursor:fetch(row) do
print(table.concat(row, '|'))
end

cursor:close()
conn:close()

env:close()
--

4. Launch this script:
lua5.1.exe test.lua

HTH,

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

Re: [sqlite] is it possible to query for database file name ?

[Simon Slavin]

On 5 May 2010, at 11:08am, yogibabu wrote:

> in php I declared database object:
> $pdo = new PDO('sqlite:mybase.DB3');
> 
> i know how to get information about engine used in this connection, which
> is:
> $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);   ---> string 'sqlite'
> 
> But I do not know how to get back the actual database file name back from
> this object. Is it possible to be done from query to the database itself
> (maybe some kind of PRAGMA) or from query to the object.

It is indeed.  Check out 'PRAGMA database_list' in this page

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

However, there's one caveat: you may or may not get back the name you used when 
opening the database in the first place.  Some filenames are aliases or 
pointers to other files.  And sometimes a file path gets expanded or otherwise 
interpreted when used (e.g. the use of '~' for the home folder.  So if your 
code is sensitive to either of these you may have to figure out how your 
particular installation of PHP is doing things.

>From a brief look at the PDO documentation you're right: the PHP PDO system 
>has no method of returning the name of the database.  This may be because many 
>database systems keep the information for one database in many different 
>files.  For instance, MySQL makes a folder with five files in before you've 
>even created your first TABLE.  And some systems don't keep separate databases 
>in a trackable way anyway: they really just keep all their databases in an 
>internal 'black box'.

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

Re: [sqlite] Implementing a CREATE_FUNCTION function

[Igor Tandetnik]

Dan Bishop wrote:
> It's convenient to be able to define new functions in C.  But sometimes,
> it would be *more* convenient to be able to define new functions in
> SQL.  This could be done by registering a CREATE_FUNCTION() function;
> then you could write something like:
> 
> SELECT CREATE_FUNCTION('LEFT', 2, 'SUBSTR(?1, 1, ?2)');
> 
> My first idea for implementing CREATE_FUNCTION is:
> 
> 1. Create (if not exists) a table with columns for the function name,
> number of arguments, and SQL expression.
> 2. Add the new function to the table.
> 3. Call sqlite3_create_function to register the new function.
> 
> C doesn't have the ability to create functions at runtime, so the xFunc
> parameter would refer to a common global function, which would:
> 
> 1. Look up the SQL expression corresponding to the SQL function name.
> 2. Evaluate the expression.
> 
> But how do I get the SQL function name from within the xFunc function?
> Can I get it from the sqlite3_context object, or do I have to use
> sqlite3_user_data()?

You have to use sqlite3_user_data. In fact, it's not clear why you would want 
to store anything in the table. Just allocate some structure describing your 
new "function" (containing the same information that you planned to store in 
that table), and pass its address to sqlite3_create_function, to be picked up 
with sqlite3_user_data.
-- 
Igor Tandetnik

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

Re: [sqlite] A newb performance question

[Igor Tandetnik]

Astley Le Jasper wrote:
> Given the example below where 'first_table' could be huge, are sqlite
> or other relational databases clever enough to do the WHERE filter
> before doing the joins.

I would expect SQLite to do it this way, yes. Especially if there's an index on 
first_table.target_field. You can confirm with EXPLAIN QUERY PLAN
-- 
Igor Tandetnik

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

Re: [sqlite] is it possible to query for database file name ?

[Kees Nuyt]

On Wed, 5 May 2010 03:08:42 -0700 (PDT), yogibabu
 wrote:

>
>in php I declared database object:
>$pdo = new PDO('sqlite:mybase.DB3');
>
>i know how to get information about engine used in this connection, which
>is:
>$pdo->getAttribute(PDO::ATTR_DRIVER_NAME);   ---> string 'sqlite'
>
>But I do not know how to get back the actual database file name back from
>this object. Is it possible to be done from query to the database itself
>(maybe some kind of PRAGMA) or from query to the object.


You already know the filename, you passed it to the
constructor. If you want to maintain more state information,
just subclass the PDO classes which do have the extra
attributes you desire.

The constructor can validate the database, and create a new
one when needed. In example below I a text file with the
schema to PDO in order to create the database.

sqlitedbpath = SQLITEDBPATH;
$this->sqlitedbname = $dbname;

/* Make connection to database */
try {
$catchmsg = 'Failed to connect to database
'.$this->sqlitedbname.', ';
parent::__construct('sqlite:'.$this->dbfile());
$this->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$sql = 'SELECT COUNT(name) AS tblcount FROM
sqlite_master WHERE type == \'table\'';
$res = $this->query($sql);
if (!$res){
throw new Exception('Error on query
sqlite_master table count');
}
$row = $res->fetch();
if (!$row){
throw new Exception('Error on fetch
sqlite_master table count');
}
$tblcount = $row['tblcount'];
if ($tblcount < 3){
$fschema = 'inc/mk'.$this->sqlitedbname.'.sql';
if (file_exists($fschema)){
$catchmsg = 'Failed to initialize 
database
'.$this->sqlitedbname.', ';

/**
  * remove -- . \n comments -> \n
  * replace 3 newlines by a single one
  * replace 2 newlines by a single one
  */
$schema = preg_replace(array(
'/\s*--[^\n]*\n/',
'/\n\n\n/',
'/\n\n/'),array(
"\n",
"\n",
"\n"),file_get_contents($fschema));
$this->exec($schema);
unset($schema);
} else {
throw new Exception('No schemafile
'.$fschema.' present, can\'t create database.');
}
}
return TRUE;
} catch (PDOException $e) {
error_log($catchmsg . $e->getMessage());
error_log(print_r($this->errorInfo(),TRUE));
return(FALSE);
}
} // end __construct()

/**
 *  For debugging: tell us when we're done.
 *  Comment out the whole function to suppress.
 */
function __destruct(){
error_log(__FUNCTION__.' '.$this->sqlitedbname);
}

/**
 *  Return the full disk:/path/filename.ext of the sqlite
database file.
 *
 */
function dbfile(){
return $this->sqlitedbpath.$this->sqlitedbname.'.db3';
}

etcetera.

};  // end class sqlitedb

?>
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Accessing SQLite from Lua?

[Gilles Ganault]

Hello

>From what I've been reading this morning, it appears that Lua offers
two ways to access an SQLite database:
- through the database-neutral Lua API LuaSQL
- by calling the SQLite-specific luasqlite

I have no preference, and would like to find the Windows binaries
that I could just copy to the directory where the Lua interpreter is
located and access SQLite through eg. "require("luasql.sqlite")".

The only SQLite file in the "Lua for Windows" package is this:
"C:\Program Files\Lua\5.1\clibs\luasql\sqlite3.dll" 

I'd like to know how to use this from a Lua script.

Thank you.

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

[sqlite] A newb performance question

[Astley Le Jasper]

Given the example below where 'first_table' could be huge, are sqlite
or other relational databases clever enough to do the WHERE filter
before doing the joins.

SELECT
*
FROM
first_table
INNER JOIN second_table ON second_table_id = second_table.id
INNER JOIN third_table ON third_table_id = third_table.id
WHERE
first_table.target_field=x

or would it be better to do

SELECT
*
FROM
(SELECT * FROM first_table WHERE target_field=x)
INNER JOIN second_table ON second_table_id = second_table.id
INNER JOIN third_table ON third_table_id = third_table.id
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT questions

[Kees Nuyt]

On Tue, 4 May 2010 14:22:25 +0100, "Mark Coles"
 wrote:

>Hi there,
>
> trying to INSERT a new row into a table and got a few
> errors which I think I've sorted, I was getting a
> couple of errors that date columns for FirstCreated
> and LastModified (datetime Type) may not be NULL,
> so I included them in my insert line as follows
>
>  INSERT INTO Aircraft  (FirstCreated,LastModified,ModeS)
>  values  ('2010-05-04 09:21:31','2010-05-04 09:21:31','C4');

Datetime is not a type in SQLite, you are storing date/time
stamps as TEXT here.

> and it seems to have worked as that row is now
> in my (test)database, what I was wondering about
> were the boolean fields, they are all filled in
> with zeroes, so this is a good sign, yes?

Boolean is not a type in SQLite. You are encoding them as
integer, deliberately or by accident. We have no way to know
whether zero is a proper value.

> Also, what about the relationships between this table
> and others in the database, should everything be OK
> as I want to share this but not screw up other peoples databases

We cannot answer that question without knowing 
1- the schema of your database
2- the schema of other peoples database
3- a type-and-data dump of your database
4- a type-and-data dump of other peoples databases

You can generate 3 and 4 yourself with something like

SELECT
typeof(col1 AS t_col1), col1,
typeof(col1 AS t_col2), col2,
...
   FROM atable.

I think it is your job to do that comparison, not ours.
If you find anything irregular you can't understand after
consulting  

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

you can ask that specific question here.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it possible to query for database file name ?

[Simon Davies]

On 5 May 2010 11:08, yogibabu  wrote:
>
> in php I declared database object:
> $pdo = new PDO('sqlite:mybase.DB3');
>
> i know how to get information about engine used in this connection, which
> is:
> $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);   ---> string 'sqlite'
>
> But I do not know how to get back the actual database file name back from
> this object. Is it possible to be done from query to the database itself
> (maybe some kind of PRAGMA) or from query to the object.

PRAGMA database_list;

see http://www.sqlite.org/pragma.html#pragma_database_list

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

Re: [sqlite] Expression tree is too large

[Andrea Galeazzi]

Thanks, it works!
Frank Baumgart ha scritto:
>> - Ursprüngliche Nachricht -
>> Von: Andrea Galeazzi
>> Gesendet: 05.05.10 12:14 Uhr
>> An: General Discussion of SQLite Database
>> Betreff: [sqlite] Expression tree is too large
>>
>> 
> Hi guys,
> I've got a DELETE statement with a lot of OR:
> DELETE FROM myTable WHERE id = ? OR id = ?..OR id=?
>
>
>
> -> ... WHERE id IN (?, ?, ...)
>
> Frank
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __ Informazioni da ESET NOD32 Antivirus, versione del database delle 
> firme digitali 5087 (20100505) __
>
> Il messaggio è stato controllato da ESET NOD32 Antivirus.
>
> www.nod32.it
>
>
>
>
>   

 

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

Re: [sqlite] Expression tree is too large

[Frank Baumgart]

> - Ursprüngliche Nachricht -
> Von: Andrea Galeazzi
> Gesendet: 05.05.10 12:14 Uhr
> An: General Discussion of SQLite Database
> Betreff: [sqlite] Expression tree is too large
> 
Hi guys,
I've got a DELETE statement with a lot of OR:
DELETE FROM myTable WHERE id = ? OR id = ?..OR id=?



-> ... WHERE id IN (?, ?, ...)

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

[sqlite] Expression tree is too large

[Andrea Galeazzi]

Hi guys,
I've got a DELETE statement with a lot of OR:
DELETE FROM myTable WHERE id = ? OR id = ?..OR id=?
and SQLite throws this error:  Expression tree is too large.
Do you know a way to avoid such problem or I just have to split the 
large statement into shorter ones?
Cheers

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

[sqlite] is it possible to query for database file name ?

[yogibabu]

in php I declared database object:
$pdo = new PDO('sqlite:mybase.DB3');

i know how to get information about engine used in this connection, which
is:
$pdo->getAttribute(PDO::ATTR_DRIVER_NAME);   ---> string 'sqlite'

But I do not know how to get back the actual database file name back from
this object. Is it possible to be done from query to the database itself
(maybe some kind of PRAGMA) or from query to the object.
-- 
View this message in context: 
http://old.nabble.com/is-it-possible-to-query-for-database-file-name---tp28458950p28458950.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] Small change to support poor man's extended features

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/04/2010 09:41 PM, Aron Rubin wrote:
> Most execution environments that support calling including C and
> Sqlite use a stack of frames.

C has a conceptual stack - the actual implementation does not require
one.  SQLite has no such thing - the interface is the SQL language.

> Looking at the Sqlite code, VDBE maintains a stack of frames.

VDBE is an implementation detail and it doesn't require using frames.

> I am suggesting that these frames are
> assigned an id that is unique for that parallel execution.

I don't get the link between frames and parallel execution.  Frames give
you the depth of recursion.  All threaded environments give you a unique
id per thread eg pthread_self or GetCurrentThreadId.  Also note that
there is no parallel execution - a mutex is held on each connection
while vdbe is executing.

> If each parallel path, i.e.
> database open from programs, is assigned a unique id then the
> combination of that id and the call depth would be sufficient for my
> purposes.

You've avoided actually saying what problem you are trying to solve
(just a potential solution).

In any event you can add a user defined function that gets the current
real (processor level) stack pointer.  It will be thread and vdbe unique.

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

iEYEARECAAYFAkvhE68ACgkQmOOfHg372QSQigCfUQTeQEM3AfDotJoebzCj4UBg
SEEAn1bN2ygr43q8pdUmVwD9XuwluNu5
=N+54
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users