[sqlite] User function calculates for every row before applying any where condition
Does SQlite (3.6.23.1 to be precise) perform any optimization when user function appears in the statement? I defined custom function MY_FUNC(a TEXT, b TEXT) and bound it with sqlite3_create_function. Suppose, we have 2 tables: Tbl1(id1 INTEGER, id2 INTEGER, str TEXT) with 2 records Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) with 5000 records Using query: SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN Tbl1 ON Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50 I hope MY_FUNC would be evaluated 50 times at most, but it appeared 1. Using subquery does not do anything with the problem. Any suggestion on how to avoid 1 evaluation of time-consuming function? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RE: [sqlite] how to cast utf16 text to int?
Sorry again. The problem is solved. I had to read FM :) and use sqlite3_open16 instead of sqlite3_open. -Original Message- From: Maxim V. Shiyanovsky [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 11, 2007 2:34 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] how to cast utf16 text to int? Sorry, I forgot initial problem. As I wrote in the first letter I need cast from utf-16 text to int. In other words I need select like this: Select * from t, d where cast(t.value as int) = d.id Assuming t.value is utf-16 presentation of numeric. This select works fine for one-digit value. It would be more correct to say that cast use first utf-16 symbol only. One more time: sqlite> create table t (value text, field_type int); sqlite> create table d (id int, data int); sqlite> insert into t values (X'31003700', 1); sqlite> insert into d values (1, 1); sqlite> insert into d values (17, 2); sqlite> Select * from t, d where cast(t.value as int) = d.id; 1|1|1|1 sqlite> So should I use other cast? Is this possible at all? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] how to cast utf16 text to int?
Sorry, I forgot initial problem. As I wrote in the first letter I need cast from utf-16 text to int. In other words I need select like this: Select * from t, d where cast(t.value as int) = d.id Assuming t.value is utf-16 presentation of numeric. This select works fine for one-digit value. It would be more correct to say that cast use first utf-16 symbol only. One more time: sqlite> create table t (value text, field_type int); sqlite> create table d (id int, data int); sqlite> insert into t values (X'31003700', 1); sqlite> insert into d values (1, 1); sqlite> insert into d values (17, 2); sqlite> Select * from t, d where cast(t.value as int) = d.id; 1|1|1|1 sqlite> So should I use other cast? Is this possible at all? - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] how to cast utf16 text to int?
Thanks, Trevor. I'll try it. One more question - is there any way to insert utf-16 string from sqlite3.exe? As I understand I can't use syntax like this: insert into t values (X'31003700', 1); -Original Message- From: Trevor Talbot [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 11, 2007 10:42 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] how to cast utf16 text to int? On 12/5/07, Maxim V. Shiyanovsky <[EMAIL PROTECTED]> wrote: > I have table that holds values of different types in utf16. > I also know value type for the current row. > How should I cast value to compare it with integer? > > This test shows 1 instead of 17 that I expected. > > sqlite> create table t (value text, field_type int); > sqlite> insert into t values (X'31003700', 1); > sqlite> select value from t; > 1 Based on your description, I think you mean you're trying to store a string of Unicode characters, and you're just working with them as UTF-16 in your application. In that case, the problem is that you're trying to use blobs. Use text instead. sqlite3_bind_text16() will let you use UTF-16 in your application, and SQLite will take care of the on-disk encoding and byte order. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] how to cast utf16 text to int?
I have table that holds values of different types in utf16. I also know value type for the current row. How should I cast value to compare it with integer? This test shows 1 instead of 17 that I expected. sqlite> create table t (value text, field_type int); sqlite> insert into t values (X'31003700', 1); sqlite> select value from t; 1 sqlite> I know that sqlite3.exe does not support Unicode but problem persist in api call also. If I use this query: select count(1) as num from t where type = 1 and value = ? and bind MBC buffer with sqlite3_bind_blob it works fine for value = 3100 (i.e. 1) but return no data when value consists of more than one digit. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite lock error
Richard, thanks a lot! I was completely sure I use the same connection, but indeed ... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 09, 2007 6:06 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite lock error "Maxim V. Shiyanovsky" <[EMAIL PROTECTED]> wrote: > > I start transaction and delete most of records > > After this sqlite fails on "select from sqlite_master" (or any other query) > because it extend lock levelš to exclusive lock when deleting records. > > It reset exclusive lock on transaction commit only. When one SQLite database connection is writing to the database, other database connections might be able to read, or they might be locked out completely. They get locked out complete when the write transaction is large and the cache must spill to disk. If you want to be able to read and write within a transaction, use the same database connection. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite lock error
Does anybody here knows sqlite internals (especially database file locking algorithm)? Or am I only one who meet this bug? May be someone could persuade me that it's not a bug? Concerning the problem - I have some table (not so big at my point of view but with 6 records). I start transaction and delete most of records After this sqlite fails on "select from sqlite_master" (or any other query) because it extend lock level to exclusive lock when deleting records. It reset exclusive lock on transaction commit only. Locking algorithm is different when table holds a few records less I've already posted sqlite trace here. May be there is known limit of how many records one could delete from table or update in single transaction? It is significant that I have single tread plain application.
[sqlite] why select distinct does not use index?
Suppose I have simple table: CREATE TABLE [profile_data] ( [profile_id] INTEGER, [version] INTEGER); CREATE INDEX [by_id] ON [profile_data] ([id]); Why sqlite> explain query plan ...> select distinct(profile_id) from profile_data; 0|0|TABLE profile_data Does not use index?
[sqlite] database lock error
I've already posted here question about getting SQLITE_BUSY when calling sqlite3_prepare in single thread application dependent on database size. Now I get more detailed diagnostic. I have simple database but with considerable amount of records in some tables. Sql commands looks like: Begin transaction delete from some_table (most of records) select from sqlite_master insert into some_table commit transaction I get SQLITE_BUSY trying to select from sqlite_master. Looking into sqlite code I found that sqlite get exclusive lock on database file when database exceeds some limit. And after this it makes one more lock which of cause ends with error. Here you can find two fragments of sqlite trace. First is for smaller database: First: OPEN 1980 LOCK 1980 1 was 0(0) UNLOCK 1980 to 0 was 1(0) LOCK 1980 1 was 0(0) UNLOCK 1980 to 0 was 1(0) LOCK 1980 1 was 0(0) LOCK 1980 2 was 1(0) OPEN 1972 - journal file OPEN 1968 LOCK 1968 1 was 0(0) TEST WR-LOCK 1968 1 (remote) UNLOCK 1968 to 0 was 1(0) LOCK 1968 1 was 0(0) TEST WR-LOCK 1968 1 (remote) UNLOCK 1968 to 0 was 1(0) UNLOCK 1968 to 0 was 0(0) CLOSE 1968 LOCK 1980 4 was 2(0) unreadlock = 1 CLOSE 1972 UNLOCK 1980 to 1 was 4(0) UNLOCK 1980 to 0 was 1(0) UNLOCK 1980 to 0 was 0(0) CLOSE 1980 And second: OPEN 1980 LOCK 1980 1 was 0(0) UNLOCK 1980 to 0 was 1(0) LOCK 1980 1 was 0(0) UNLOCK 1980 to 0 was 1(0) LOCK 1980 1 was 0(0) LOCK 1980 2 was 1(0) OPEN 1972 - journal file LOCK 1980 4 was 2(0) - exclusive lock unreadlock = 1 OPEN 1968 LOCK 1968 1 was 0(0) LOCK FAILED 1968 trying for 1 but got 0 UNLOCK 1968 to 0 was 0(0) CLOSE 1968 CLOSE 1972 UNLOCK 1980 to 1 was 4(0) UNLOCK 1980 to 0 was 1(0) UNLOCK 1980 to 0 was 0(0) CLOSE 1980
