Re: [sqlite] Database gets locked for other processes

[Israel Lins Albuquerque]

I know well the qt sqlite to say, maybe you aren't destroing the QSqlQuery 
class or simple call finish function, and the statement is openned helding the 
lock!

Enviado via iPhone

> Em 29/10/2013, às 10:51, Stephan Beal  escreveu:
> 
>> On Tue, Oct 29, 2013 at 1:52 PM, Martin  wrote:
>> 
>> The program is running on Windows7.
>> ...
> 
> The program runs parallel on multiple machines all sharing the same
>> SQLite-Database-file.
> 
> 
> Connecting multiple clients over a network share is a sure-fire way to
> corrupt your database. See the bottom half of this page:
> 
> http://www.sqlite.org/whentouse.html
> 
> as well as any number of threads in this mailing list archives regarding
> this topic.
> 
> -- 
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Since tyranny's the only guaranteed byproduct of those who insist on a
> perfect world, freedom will have to do." -- Bigby Wolf
> ___
> 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] Sqlite locking issue with ATTACH'ed databases

[Israel Lins Albuquerque]

I have the same issue here!

I have an replication system using sqlite where:
- I have 2 databases: one for output* and other for input*;
- I have 2 process accessing booth:
The first is the replicator:
- Get the data on remote server and write on input database;
- Get the data on output database and write on remote server;
The second process:
- Read the input database;
- Write on output database;

In booth of process the databases are attached,
I've used another empty database just to create a connection.

Then I have seen when I replicator commits and the second process tries to 
write the error happens,
but I can not sure about this...

I just saying you are not alone.

--
Israel Lins Albuquerque

Antes de imprimir, pense em sua responsabilidade com o MEIO AMBIENTE.



Em 18/07/2013, às 16:26, Loren Keagle <loren.kea...@braemarllc.com> escreveu:

>> Date: Wed, 17 Jul 2013 17:21:15 +0100
>> From: Simon Slavin <slav...@bigfraud.org>
>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>> Subject: Re: [sqlite] Sqlite locking issue with ATTACH'ed databases
>> Message-ID: <f0ad438b-1165-419d-99bf-57faf9de5...@bigfraud.org>
>> Content-Type: text/plain; charset=us-ascii
> 
> 
>> On 16 Jul 2013, at 11:24pm, Loren Keagle <loren.kea...@braemarllc.com> wrote:
> 
>>> Begin EXCLUSIVE TRANSACTION;
>>> insert several rows of data;
>>> Commit transaction;
>>> 
>>> Prepare query statement;
>>> Iterate through one or more rows;
>>> Reset statement;
>>> 
>>> Attempt to begin transaction; <--- SQLITE_BUSY
>>> Would like to write more here, but can't unless I close/open the
>>> connection;
> 
>> I assume you're checking the result codes returned by all the API calls 
>> before the second BEGIN to see that they all return SQLITE_OK.
> 
>> Please add a _finalize() after the _reset() just for testing purposes.  I 
>> know you may not want it as part of your production code.
> 
>> Is the statement that gets a busy a BEGIN or BEGIN IMMEDIATE or BEGIN 
>> EXCLUSIVE ?
> 
>> Simon.
> 
> 
> Thanks for the reply.  I've written wrapper classes in C++ that automatically 
> check all return codes for every sqlite API call I make.  The only return 
> error is the SQLITE_BUSY from the transaction statement (It's EXCLUSIVE, btw, 
> but it doesn't seem to matter in this context).
> 
> I've tried finalizing all statements.  It definitely seems to be related to 
> having the same database attached multiple times with different names.  I've 
> done this because my data is split up amongst multiple sub-databases, and I 
> simply have a reader and writer object that can work independently.  Of 
> course, they can both end up pointing at the same sub-database, but I never 
> would have thought this was a problem.
> 
> I've written some sample code to illustrate my problem.  I've commented out 
> the actions that don't seem to make any difference.  Simply the fact that 
> I've attached the second database causes the failure.  As soon as I detach 
> it, I can write on the first again:
> 
>// Open master database
>sqlite3* db = NULL;
>int ret = sqlite3_open_v2("Test.sqlite", , SQLITE_OPEN_FULLMUTEX | 
> SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr);
>if(ret != SQLITE_OK)
>{
>exit(1);
>}
>sqlite3_extended_result_codes(db, TRUE);
> 
>// Create table on main.  This probably serves no purpose.
>ret = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS MainTable (id 
> INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL);
>if(ret != SQLITE_OK)
>exit(3);
> 
>// Attach write database
>ret = sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as write1;", 
> NULL, NULL, NULL);
>if(ret != SQLITE_OK)
>exit(2);
> 
>// Create table on subdb
>ret = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS write1.TestTable 
> (id INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL);
>if(ret != SQLITE_OK)
>exit(3);
> 
>// Insert some data in write table
>sqlite3_stmt * insert = nullptr;
>const char* tail = nullptr;
>ret = sqlite3_prepare_v2(db, "INSERT INTO write1.TestTable (IntColumn) 
> VALUES (?1);", -1, , );
>if (ret != SQLITE_OK)
>exit(4);
> 
>for (int i = 0; i < 10; ++i)
>{
>ret = sqlite3_bind_int(insert, 1, i);
>if (ret != SQLITE_OK)
>   

Re: [sqlite] Bug: on opening the same database twice

[Israel Lins Albuquerque]

Ok, Then let me explain my problem to see if I done the best solution!

I have an replication system using sqlite where:
- I have 2 databases: one for output* and other for input*;
- I have 2 process accessing booth:
 The first is the replicator:
 - Get the data on server and write on input database;
 - Get the data on output database and write on server;
 The second process:
 - Read the input database;
 - Write on output database;

And I have triggers and tables to control what data has to be sync;

The problem, I guess solved today, is:
The replicator before open a single connection attaching booth of databases,
then when the replicator commits transactions that are writing only in input 
database and the 
second process try to write on output database the second process receive a 
database locked error,
then after that I have to close and open again the database;

To solve this I done two connection to databases on replicator:
One only for input database that only receive data;
And the other for output database attaching input database (to make some joins);

The point is.

I tried to make the output connection readonly to ensure that.
But that don't works.

Other question:

When the replicator is sending data to the server they acquire read lock on 
output database,
then the second process has some delay trying to acquire write lock on output 
database, there are some way to make 
this delay gone? Making the read process read the data writed until begin of 
read?

Thats it, thanks for patience.

Regards,

--
Israel Lins Albuquerque

Antes de imprimir, pense em sua responsabilidade com o MEIO AMBIENTE.



Em 26/06/2013, às 14:24, Israel Lins Albuquerque <israelin...@yahoo.com.br> 
escreveu:

> I open an database twice times in same process and same thread one in 
> readonly mode, and the other read write.
> With shared_cache enable.
> Opening first the readonly mode and after readwrite mode, the second 
> connection works as read only too.
> 
> Then I looked to sqlite code and I see the problem in:
> sqlite3BtreeOpen line 1788, because the shared cache uses the same structure 
> in booth connections, and pBt->pPager->readOnly is set to 1.
> 
> To workaround this I will open read write mode first, but that make readonly 
> connection become read write.
> 
> There is an know issue?
> 
> Regards,
> --
> Israel Lins Albuquerque
> 
> Antes de imprimir, pense em sua responsabilidade com o MEIO AMBIENTE.
> 
> 
> 
> ___
> 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] Bug: on opening the same database twice

[Israel Lins Albuquerque]

I open an database twice times in same process and same thread one in readonly 
mode, and the other read write.
With shared_cache enable.
Opening first the readonly mode and after readwrite mode, the second connection 
works as read only too.

Then I looked to sqlite code and I see the problem in:
sqlite3BtreeOpen line 1788, because the shared cache uses the same structure in 
booth connections, and pBt->pPager->readOnly is set to 1.

To workaround this I will open read write mode first, but that make readonly 
connection become read write.

There is an know issue?

Regards,
--
Israel Lins Albuquerque

Antes de imprimir, pense em sua responsabilidade com o MEIO AMBIENTE.



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

Re: [sqlite] Bug on real operations

[Israel Lins Albuquerque]

As I can see my problem is solved on sqlite 4.


Em 08/03/2013, às 16:35, Israel Lins Albuquerque <israelin...@yahoo.com.br> 
escreveu:

> Thank you guys, and sort for my bad explanation about what I want. I 
> understand that double problems very well,
> I will continue working with round.
> 
> Regards,
> Israel Lins
> 
> 
> Em 08/03/2013, às 16:14, "Marc L. Allen" <mlal...@outsitenetworks.com> 
> escreveu:
> 
>> Yes.. for what it's worth, I've had this very same problem on MS SQL 2008.  
>> Comparing floating point values in their raw form is always dangerous.  It 
>> just works so much more often than not that it's easy to forget until you 
>> get that one number that doesn't work.
>> 
>> The solution for MS SQL was conversion to smallmoney.
>> 
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org 
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
>> Sent: Friday, March 08, 2013 1:37 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Bug on real operations
>> 
>> 
>> On 8 Mar 2013, at 6:24pm, Israel Lins Albuquerque <israelin...@yahoo.com.br> 
>> wrote:
>> 
>>> I don't know how postgres handle this, may be I can check
>> 
>> Postgres has special datatypes used especially to handle problems like this. 
>>  It has both artbitrary precision and monetary datatypes.  If you present 
>> your problem to postgres and let it use floating point numbers it has the 
>> same problem SQLite has.
>> 
>> However, I used to work with banks and other financial institutions for a 
>> living and I can assure you that the systems I wrote and used used integer 
>> datatypes to handle amounts of money.  For historical reasons they do 
>> sometimes multiply by 10,000 instead of 100, but either way all amounts of 
>> money are stored as integers.  This speeds up calculations, reduces storage 
>> space, and reduces the complexity of testing required.
>> 
>> Just to underline what Richard wrote, this is not a bug in the way SQLite 
>> handles floating point.  The bug is in thinking you can express decimal 
>> fractions as binary floating point numbers and it is very familiar to 
>> computer scientists.
>> 
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
>> This email and any attachments are only for use by the intended recipient(s) 
>> and may contain legally privileged, confidential, proprietary or otherwise 
>> private information. Any unauthorized use, reproduction, dissemination, 
>> distribution or other disclosure of the contents of this e-mail or its 
>> attachments is strictly prohibited. If you have received this email in 
>> error, please notify the sender immediately and delete the original.
>> ___
>> 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] Bug on real operations

[Israel Lins Albuquerque]

Thank you guys, and sort for my bad explanation about what I want. I understand 
that double problems very well,
I will continue working with round.

Regards,
Israel Lins


Em 08/03/2013, às 16:14, "Marc L. Allen" <mlal...@outsitenetworks.com> escreveu:

> Yes.. for what it's worth, I've had this very same problem on MS SQL 2008.  
> Comparing floating point values in their raw form is always dangerous.  It 
> just works so much more often than not that it's easy to forget until you get 
> that one number that doesn't work.
> 
> The solution for MS SQL was conversion to smallmoney.
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: Friday, March 08, 2013 1:37 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Bug on real operations
> 
> 
> On 8 Mar 2013, at 6:24pm, Israel Lins Albuquerque <israelin...@yahoo.com.br> 
> wrote:
> 
>> I don't know how postgres handle this, may be I can check
> 
> Postgres has special datatypes used especially to handle problems like this.  
> It has both artbitrary precision and monetary datatypes.  If you present your 
> problem to postgres and let it use floating point numbers it has the same 
> problem SQLite has.
> 
> However, I used to work with banks and other financial institutions for a 
> living and I can assure you that the systems I wrote and used used integer 
> datatypes to handle amounts of money.  For historical reasons they do 
> sometimes multiply by 10,000 instead of 100, but either way all amounts of 
> money are stored as integers.  This speeds up calculations, reduces storage 
> space, and reduces the complexity of testing required.
> 
> Just to underline what Richard wrote, this is not a bug in the way SQLite 
> handles floating point.  The bug is in thinking you can express decimal 
> fractions as binary floating point numbers and it is very familiar to 
> computer scientists.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> This email and any attachments are only for use by the intended recipient(s) 
> and may contain legally privileged, confidential, proprietary or otherwise 
> private information. Any unauthorized use, reproduction, dissemination, 
> distribution or other disclosure of the contents of this e-mail or its 
> attachments is strictly prohibited. If you have received this email in error, 
> please notify the sender immediately and delete the original.
> ___
> 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] Bug on real operations

[Israel Lins Albuquerque]

I know that very well.

I don't know how postgresql handle this, may be I can check, 
I just charring by problem to brainstorm if sqlite may do or not what 
postgresql do.

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

Re: [sqlite] Bug on real operations

[Israel Lins Albuquerque]

I know that.

I don't know how postgresql handle this, may be I can check, 
I just charring by problem to brainstorm if sqlite may do or not what 
postgresql do.


Em 08/03/2013, às 15:06, Israel Lins Albuquerque <israelin...@yahoo.com.br> 
escreveu:

> But I see this as a workaround, and not a solution.
> Is that the way I'm using to 'solve' this.
> 
> Em 08/03/2013, às 14:11, Michael Black <mdblac...@yahoo.com> escreveu:
> 
>> Nobody should expect float comparisons like that to work.
>> If they do they're asking for trouble.
>> All you're seeing is what the database is letting you see.  Their "0" is not
>> really "0".
>> 
>> Try this in your friendly C compiler
>> main()
>> {
>>  double d = 22.35-(5.45+16.9);
>>  printf("%f\n",d);
>>  printf("%g\n",d);
>>  printf("%e\n",d);
>> }
>> On both MS Visual C and gcc on a Linux system:
>> 0.00
>> 3.55271e-015
>> 3.552714e-015
>> 
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Israel Lins
>> Albuquerque
>> Sent: Thursday, March 07, 2013 12:04 PM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] Bug on real operations
>> 
>> An example speaks more than words:
>> 
>> Execute this:
>> SELECT 22.35 - (5.45 + 16.9), 22.35 = (5.45 + 16.9), (5.45 + 16.9) - 22.35;
>> 
>> The expected result on almost databases is:
>> 0.0, true or 1, 0.0
>> 
>> But in sqlite for some reason they are:
>> 3.5527136788005e-15, 0, -3.5527136788005e-15
>> 
>> I thing this can be a bug on calculation of doubles.
>> 
>> Regards,
>> Israel Lins Albuquerque
>> ___
>> 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] Bug on real operations

[Israel Lins Albuquerque]

The problem is not comparisons the problem is when I do something like this:
CREATE TABLE tb (a REAL);
INSERT INTO tb (a) VALUES(0);
UPDATE tb SET a = a + 5.45;
UPDATE tb SET a = a + 16.9;

SELECT a FROM tb; 

Gives visually right answer: 22.35
But putting on a double variable gives me 22.3499948593433 (something like that)
and when declaring double a = 22.35 => gdb give me 22.34999 

Em 08/03/2013, às 14:11, Michael Black <mdblac...@yahoo.com> escreveu:

> Nobody should expect float comparisons like that to work.
> If they do they're asking for trouble.
> All you're seeing is what the database is letting you see.  Their "0" is not
> really "0".
> 
> Try this in your friendly C compiler
> main()
> {
>   double d = 22.35-(5.45+16.9);
>   printf("%f\n",d);
>   printf("%g\n",d);
>   printf("%e\n",d);
> }
> On both MS Visual C and gcc on a Linux system:
> 0.00
> 3.55271e-015
> 3.552714e-015
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Israel Lins
> Albuquerque
> Sent: Thursday, March 07, 2013 12:04 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Bug on real operations
> 
> An example speaks more than words:
> 
> Execute this:
> SELECT 22.35 - (5.45 + 16.9), 22.35 = (5.45 + 16.9), (5.45 + 16.9) - 22.35;
> 
> The expected result on almost databases is:
> 0.0, true or 1, 0.0
> 
> But in sqlite for some reason they are:
> 3.5527136788005e-15, 0, -3.5527136788005e-15
> 
> I thing this can be a bug on calculation of doubles.
> 
> Regards,
> Israel Lins Albuquerque
> ___
> 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] Bug on real operations

[Israel Lins Albuquerque]

But I see this as a workaround, and not a solution.
Is that the way I'm using to 'solve' this.

Em 08/03/2013, às 14:11, Michael Black <mdblac...@yahoo.com> escreveu:

> Nobody should expect float comparisons like that to work.
> If they do they're asking for trouble.
> All you're seeing is what the database is letting you see.  Their "0" is not
> really "0".
> 
> Try this in your friendly C compiler
> main()
> {
>   double d = 22.35-(5.45+16.9);
>   printf("%f\n",d);
>   printf("%g\n",d);
>   printf("%e\n",d);
> }
> On both MS Visual C and gcc on a Linux system:
> 0.00
> 3.55271e-015
> 3.552714e-015
> 
> -Original Message-----
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Israel Lins
> Albuquerque
> Sent: Thursday, March 07, 2013 12:04 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Bug on real operations
> 
> An example speaks more than words:
> 
> Execute this:
> SELECT 22.35 - (5.45 + 16.9), 22.35 = (5.45 + 16.9), (5.45 + 16.9) - 22.35;
> 
> The expected result on almost databases is:
> 0.0, true or 1, 0.0
> 
> But in sqlite for some reason they are:
> 3.5527136788005e-15, 0, -3.5527136788005e-15
> 
> I thing this can be a bug on calculation of doubles.
> 
> Regards,
> Israel Lins Albuquerque
> ___
> 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] Bug on real operations

[Israel Lins Albuquerque]

An example speaks more than words:

Execute this:
SELECT 22.35 - (5.45 + 16.9), 22.35 = (5.45 + 16.9), (5.45 + 16.9) - 22.35;

The expected result on almost databases is:
0.0, true or 1, 0.0

But in sqlite for some reason they are:
3.5527136788005e-15, 0, -3.5527136788005e-15

I thing this can be a bug on calculation of doubles.

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

Re: [sqlite] Reducing time to create indexes

[Israel Lins Albuquerque]

Hey guy, you are doing wrong, you don't need the booth indexes in same field; 


DROP TABLE tb; 
CREATE TEMP TABLE tb ( 
a INTEGER, 
b TEXT, 
CONSTRAINT 'idx_tb00' PRIMARY KEY (a)); 

CREATE INDEX 'idx_tb01' ON tb (b); 

INSERT INTO tb (a, b) VALUES (1, '1'); 
INSERT INTO tb (a, b) VALUES (2, '2'); 
INSERT INTO tb (a, b) VALUES (3, '3'); 
INSERT INTO tb (a, b) VALUES (4, '4'); 
INSERT INTO tb (a, b) VALUES (5, '5'); 
INSERT INTO tb (a, b) VALUES (6, '6'); 

look that example and take a look in the result of "EXPLAIN QUERY PLAN" 
in booth order are the same index used! 

EXPLAIN QUERY PLAN 
SELECT * FROM tb 
ORDER BY a ASC; 

EXPLAIN QUERY PLAN 
SELECT * FROM tb 
ORDER BY a DESC; 

EXPLAIN QUERY PLAN 
SELECT * FROM tb 
ORDER BY b ASC; 

EXPLAIN QUERY PLAN 
SELECT * FROM tb 
ORDER BY b DESC; 

now take a look on EXPLAIN , 
only 2 operations are changed: 
Rewind(First) <=> Last, 
Next <=> Previous 

EXPLAIN 
SELECT * FROM tb 
ORDER BY b ASC; 

EXPLAIN 
SELECT * FROM tb 
ORDER BY b DESC; 

With this your time can be halved ! 

- "Paul Sanderson" <sandersonforens...@gmail.com> writed: 
> Unfortunately all of the columns are represented in a grid that users 
> can choose to sort on any column, although they are less likely to 
> sort on certain columns, if they do chose to do so then an excessive 
> delay is not really acceptable. 
> 
> Currently I create ascending and descending indexes for each column 
> individually i.e. 
> 
> create index if not exists name_a on table(name asc) 
> create index if not exists name_d on table(name desc) 
> etc. 
> 
> The data is entered in order for the primary index 
> 
> File size varies from case to case but typically prior to indexing it 
> is about 300MB and with indexes 600MB. 
> 
> Ill have a look at FTS as there are other benefits to using that. 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 






-- 
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] Corrupted database with duplicated primary keys

[Israel Lins Albuquerque]

Due to attach limits this is the part 002 of the file! 

- "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> escreveu: 
> Attached has a database corrupted. 
> We use the version 3.6.23.1 in wince. 
> 
> the command: 
> pragma integrity_check; 
> 
> show many errors and 
> 
> Duplicate pk was founded using: 
> SELECT u_pkey, count(*) 
> FROM tp_gpsdata 
> GROUP BY u_pkey 
> HAVING count(*) > 1 
> ; 
> 
> this returns only 1 record 
> SELECT * 
> FROM tp_gpsdata 
> WHERE u_pkey IN (4684, 4879) 
> ORDER BY u_pkey 
> DESC LIMIT 10; 
> 
> deletes one record only 
> DELETE FROM tp_gpsdata WHERE u_pkey IN (4684, 4879); 
> 
> vacuum; 
> doesn't works because of pk constraints. 
> 
> 
> 
> 
> 
> I'm seeding because that can be a bug in OS or in sqlite and maybe someone 
> can see that, 
> Thanks for your time! 
> 
> -- 
> 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 
> 

-- 






-- 
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] Corrupted database with duplicated primary keys

[Israel Lins Albuquerque]

Due to attach limits this is the part 001 of the file! 

- "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> escreveu: 
> Attached has a database corrupted. 
> We use the version 3.6.23.1 in wince. 
> 
> the command: 
> pragma integrity_check; 
> 
> show many errors and 
> 
> Duplicate pk was founded using: 
> SELECT u_pkey, count(*) 
> FROM tp_gpsdata 
> GROUP BY u_pkey 
> HAVING count(*) > 1 
> ; 
> 
> this returns only 1 record 
> SELECT * 
> FROM tp_gpsdata 
> WHERE u_pkey IN (4684, 4879) 
> ORDER BY u_pkey 
> DESC LIMIT 10; 
> 
> deletes one record only 
> DELETE FROM tp_gpsdata WHERE u_pkey IN (4684, 4879); 
> 
> vacuum; 
> doesn't works because of pk constraints. 
> 
> 
> 
> 
> 
> I'm seeding because that can be a bug in OS or in sqlite and maybe someone 
> can see that, 
> Thanks for your time! 
> 
> -- 
> 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 
> 

-- 






-- 
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

[sqlite] Corrupted database with duplicated primary keys

[Israel Lins Albuquerque]

Attached has a database corrupted. 
We use the version 3.6.23.1 in wince. 

the command: 
pragma integrity_check; 

show many errors and 

Duplicate pk was founded using: 
SELECT u_pkey, count(*) 
FROM tp_gpsdata 
GROUP BY u_pkey 
HAVING count(*) > 1 
; 

this returns only 1 record 
SELECT * 
FROM tp_gpsdata 
WHERE u_pkey IN (4684, 4879) 
ORDER BY u_pkey 
DESC LIMIT 10; 

deletes one record only 
DELETE FROM tp_gpsdata WHERE u_pkey IN (4684, 4879); 

vacuum; 
doesn't works because of pk constraints. 





I'm seeding because that can be a bug in OS or in sqlite and maybe someone can 
see that, 
Thanks for your time! 

-- 
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] Question about SQLite features.

[Israel Lins Albuquerque]

Hey "Andy Gibbs". Why your code isn't added into sqlite? 


- "Andy Gibbs" <andyg1...@hotmail.co.uk> escreveu: 
> On Tuesday, November 09, 2010 8:29 AM, Tran Van Hoc wrote: 
> 
> > Dear all. 
> > 
> > I'm using SQLite and many thanks for your supports. 
> > 
> > I have problem about SQLite features. 
> > 
> > That's I don't know SQLite have stored procedure support? 
> > 
> 
> How're your C skills? If you are comfortable with the idea, then it really 
> isn't too hard to add such support to sqlite, but you need to have a good 
> level of C programming skills and also an understanding of the lemon grammar 
> to do it. Speaking from experience, adding a procedural language into 
> sqlite is not particularly hard (I did it in under 2 weeks which included 
> the appropriate additions to the test scripts). I have to say, sqlite is 
> probably the best-written bit of open-source code I know of, in terms of how 
> easy it is to understand the original programmer's intention and to extend 
> it. 
> 
> I wish you all the best, if you try it! 
> 
> Andy 
> 
> 
> 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 






-- 
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] WinCE possible bug

[Israel Lins Albuquerque]

Thanks, "Zaher Dirkey"! 

Only, 
PRAGMA journal_mode = TRUNCATE 

solves my problem. 

By some way the O.S. is locking the one off the journals and the program 
can't delete it. 

-- 

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] WinCE possible bug

[Israel Lins Albuquerque]

No. I use sqlite in proprietary application, not the shell! 
I use cegcc to compile sqlite. 


- "ydlu" <yudian...@gmail.com> escreveu: 
> Hi: 
> Could you give me some info how you use SQLite in WinCE 6.0? Do you convert 
> the shell program to WinCE? I mean run "Sqlite3.exe" in WinCE. 
> If you run x86 platform, could you share some idea with the WinCE developer? 
> 
> Thanks 
> Lu 
> 
> On Fri, Sep 3, 2010 at 5:40 AM, Israel Lins Albuquerque < 
> israel...@polibrasnet.com.br> wrote: 
> 
> > Guys I had a problem. 
> > 
> > We use sqlite for WinCE based devices, then in version 6.1 of this O.S., 
> > the command 'DELETE FROM ...' causes 'IO error', the device have a 
> > lot of space, and dynamic memory too, someone had seen something like this? 
> > 
> > But isn't only make 'DELETE FROM ...;' and the error occur, 
> > the program makes a lot of things, including stay with prepared statements 
> > in memory, but no one are locking the file I wish 
> > I appreciate some help. 
> > 
> > 
> > Ps. I'm using the version 3.6.23.1 of sqlite library. 
> > 
> > 
> > -- 
> > 
> > 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 
> > 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 

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

[sqlite] WinCE possible bug

[Israel Lins Albuquerque]

Guys I had a problem. 

We use sqlite for WinCE based devices, then in version 6.1 of this O.S., 
the command 'DELETE FROM ...' causes 'IO error', the device have a 
lot of space, and dynamic memory too, someone had seen something like this? 

But isn't only make 'DELETE FROM ...;' and the error occur, 
the program makes a lot of things, including stay with prepared statements 
in memory, but no one are locking the file I wish 
I appreciate some help. 


Ps. I'm using the version 3.6.23.1 of sqlite library. 


-- 

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] Fw: Re: SQlite JDBC driver - need help

[Israel Lins Albuquerque]

I want to finish that confusion! 

1st point: 
JSP means Java Server Pages and runs on an Apache TomCat server. 
The java code runs ON server and NOT in client! 

2nd: 
Then the db files don't need be directed accessed by the web client, this is 
unsafe. 

Exemple: 
DriverManager.getConnection("jdbc:sqlite:[DB_PATH]"); 

Where DB_PATH is the path ON the server like "jdbc:sqlite:./test.db", and 
the data will be created if doesn't exists in the same path of current jsp 
page, 
if you don't want this you can put somethink like 
"jdbc:sqlite:C:/www/[my_site]/data/test.db", 
you need ensure that directory exists the sqlite only create the database! 


- "Pavel Ivanov" <paiva...@gmail.com> escreveu: 
> Dev, 
> 
> Do you understand that SQLite is not a server application and it has 
> no access control facilities? It is just some library code that helps 
> accessing files that have some well-defined internal format. So your 
> application will be able to access any database accessible by the user 
> running your executable. In case of JSP page it will be whatever user 
> account your IIS server is running under. And database path like 
> '//ipaddress/spiceworks/db/spiceworks_prod.db' most probably means 
> that you try to access a file located in the network at a shared 
> folder spiceworks of the server ipaddress. So is that path accessible 
> to the user running IIS? I'm not sure though what "out of memory" 
> errors mean. Probably just badly written jdbc driver. 
> 
> 
> Pavel 
> 
> On Wed, Aug 25, 2010 at 3:14 PM, Dev Movva <mov...@yahoo.com> wrote: 
> > Hello, 
> > 
> > Can some one please help me how do I connect to SQLite from a JSP page with 
> > windows authentication mode? 
> > 
> > Thank you, 
> > 
> > Dev 
> > 
> > --- On Wed, 8/25/10, Richard Hipp <d...@sqlite.org> wrote: 
> > 
> > 
> > From: Richard Hipp <d...@sqlite.org> 
> > Subject: Re: SQlite JDBC driver - need help 
> > To: "Dev Movva" <mov...@yahoo.com> 
> > Date: Wednesday, August 25, 2010, 3:02 PM 
> > 
> > 
> > jdbc is maintained separately by Chris Werner. I don't know anything about 
> > it. If you post on sqlite-users@sqlite.org you might be able to get 
> > somebody there to help you. 
> > 
> > 
> > On Wed, Aug 25, 2010 at 2:47 PM, Dev Movva <mov...@yahoo.com> wrote: 
> > 
> > 
> > 
> > 
> > 
> > 
> > Dear Richard, 
> > 
> > I am trying to connect to SQLite from my JSP page and not sure where I am 
> > not doing it right but no luck with it. I posted it on the google forums at 
> > http://groups.google.com/group/sqlitejdbc/browse_thread/thread/841538d7b3679678#
> >  
> > 
> > I am wondering if you can shed some light on my issue and help me wiht 
> > this. 
> > 
> > All I need is how do I connect to SQLite from a JSP page using windows 
> > authentication (like integratedSecurity=true while connecting to a SQL 
> > server). All our users should be able to see view the data with their 
> > windows authentication. Any help is greately appreciated. 
> > 
> > Thank you in advance, 
> > 
> > Dev 
> > 
> > 
> > 
> > -- 
> > D. Richard Hipp 
> > d...@sqlite.org 
> > 
> > 
> > 
> > 
> > ___ 
> > 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 
> 

-- 

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] SQL script help.

[Israel Lins Albuquerque]

Or you can do: 
SELECT * FROM myTable WHERE _rowid_ IN (100, 101, 102) 

depending what you want 


- "Simon Slavin" <slav...@bigfraud.org> escreveu: 
> 
> On 26 Aug 2010, at 3:39pm, Kirk Clemons wrote: 
> 
> > SELECT * FROM myTable WHERE _rowid_ = 100; SELECT * FROM myTable WHERE 
> > _rowid_ = 101; SELECT * FROM myTable WHERE _rowid_ = 102; 
> 
> SELECT * FROM myTable WHERE _rowid_ BETWEEN 100 AND 102 
> 
> or 
> 
> SELECT * FROM myTable WHERE _rowid_ >= 100 AND _rowid_ <= 102 
> 
> will give identical results. Technically speaking you might want to add 
> 'ORDER BY _rowid_' to the end of those if the order matters. 
> 
> Simon. 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 

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] Ticks to unixepoch date

[Israel Lins Albuquerque]

Let me understand you... 

if tick is 0 the date id 0001/01/01 12:00:00? 
if yes the select do you want is 

SELECT datetime((TICK_VALUE / 100) - 62135553600, 'unixepoch') AS Expr1 


use that site for help! 
http://www.epochconverter.com/ 



- "Roberto Dalmonte" <rob...@tiscali.it> escreveu: 
> In theory it should be possible to do it right now using the following 
> syntax ... 
> 
> SELECT datetime((columnAsTicks / 100) - 186796800, 
> 'unixepoch') AS Expr1 
> FROM Table 
> 
> ...unfortunately it doesn't work, at least the way I tried it. 
> 
> The operation is the following: 
> 1) transforms the ticks in seconds (divide ticks per 1 million); 
> 2) subtract the seconds passed from date 0001/01/01 (starting date in 
> System.DateTime) to 1970/1/1 (starting date in Unixepoch) 
> 3) Use the result with the built-in SQLite function datetime. 
> 
> This way you could build a view and be able to see a normal date instead 
> of ticks. 
> 
> Any idea? 
> Roberto 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 

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] Retrieve Specific record number in one shot.

[Israel Lins Albuquerque]

Maybe do you want this! 

http://www.sqlite.org/syntaxdiagrams.html#select-stmt 

Select * From Product order by ProductName LIMIT 210 OFFSET 210; 



- "Piyush Verma" <piyush...@gmail.com> escreveu: 
> Hello All, 
> 
> I want to navigate to specific position in table for example I want 
> row number 210 inspite of nevigating one by one how can get that row. 
> 
> One way could be create a Index and use where clause to get that But 
> it's not useful in my case. 
> 
> I have table which have primary key, and product name(there is another 
> index for ProductName). Now I sort by Product name and want to access 
> row number 210. 
> 
> Is that a way to get it directly? 
> 
> like 
> 
> >>"Select * From Product order by ProductName" 
> >>move_to_row(210); 
> >>read row; 
> 
> 
> something like that. 
> 
> 
> 
> 
> -- 
> Thanks & Regards 
> 
> Piyush Verma 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 

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] Slow query

[Israel Lins Albuquerque]

If your table doesn't have a primary key, this look like your table aren't 
normalized, maybe you can try broke this table in 2 tables like: 

Your definition: 
CREATE TABLE table1(id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year 
INTEGER); 

indexes : index1( name ), index2( id2 ), index3( 
name2 ); 

Will be: 
CREATE TABLE t_names(id INTEGER PRIMARY KEY, name TEXT); 
CREATE INDEX t_name_idx_001 ON (name, id); 

CREATE TABLE t_relation(id1 INTEGER REFERENCES t_name(id), id2 INTEGER 
REFERENCES t_name(id), year INTEGER); 
CREATE INDEX t_relation_idx_001 ON (year, id1, id2); 
CREATE INDEX t_relation_idx_002 ON (id1, id2); 

Where t_relation make the relationship between the 'names' (t_name). 

And the select: 
SELECT id, name, id2, name2, max(year) y 
FROM table1 
GROUP BY id, name2 
ORDER BY name2, y DESC 
LIMIT 0, 15 

Will be: 
SELECT t2.* 
, t3.* 
, MAX(t1.year) AS y 
FROM t_relation AS t1 
JOIN t_name AS t2 ON (t2.id = t1.id1) 
JOIN t_name AS t3 ON (t3.id = t1.id1) 
GROUP BY t2.id, t3.name 
ORDER BY t3.name, y DESC 
LIMIT 0, 15 


And: 
SELECT id, name, id2, name2 
FROM table1 
GROUP BY id 
ORDER BY name2, year DESC 
LIMIT 0,15 

Will be: 
SELECT t2.* 
, t3.* 
FROM t_relation AS t1 
JOIN t_name AS t2 ON (t2.id = t1.id1) 
JOIN t_name AS t3 ON (t3.id = t1.id1) 
GROUP BY t2.id 
ORDER BY t3.name, t1.year DESC 
LIMIT 0, 15 

to export the existing data to the new tables you can do: 
INSERT OR REPLACE INTO t_name 
SELECT DISTINCT * FROM (SELECT id, name FROM table1 
UNION ALL 
SELECT id2, name2 FROM table1); 

INSERT OR REPLACE INTO t_relation 
SELECT id, id2, year FROM table1; 



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

Re: [sqlite] alternative to UNIQUE CONSTRAINT

[Israel Lins Albuquerque]

maybe this works for you! 

http://www.patthoyts.tk/sqlite3odbc.html 
http://wiki.services.openoffice.org/wiki/SummerOfCode2006#Native_SQLite_driver 

- "Oliver Peters" <oliver@web.de> escreveu: 
> Pavel Ivanov <paiva...@...> writes: 
> 
> [...] 
> > 
> > If your ODBC driver doesn't allow you to have any UNIQUE constraint 
> > then, as Darren said, you better consider using some other driver, not 
> > a workaround for this one. I believe there are several ODBC drivers 
> > for SQLite out there. 
> > 
> 
> I strongly believe in human potential for doing good deeds but sometimes I 
> need 
> a proof ;-) - so where are the other ODBC drivers you are writing about (I 
> use 
> http://www.ch-werner.de/sqliteodbc/) ? I wasn't able to find an alternative - 
> and yes I know how to google ;-) 
> 
> [...] 
> 
> Oliver 
> 
> 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 

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

[sqlite] Force Database Corruption

[Israel Lins Albuquerque]

Hello, 

I'm needing a corrupted database for test purpose, someone can help me? 
I want an database with corruption on index like: 
"rowid [%ld] missing from index %s" or "wrong # of entries in index %s" 

-- 

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] Parametrized Queries issue (Possible bug)

[Israel Lins Albuquerque]

I done a path to make this works, if someone wants! 

Now... 

CREATE TABLE a (a, b); 
INSERT INTO a VALUES (0, 0); 
INSERT INTO a VALUES (0, 1); 
INSERT INTO a VALUES (1, 0); 
INSERT INTO a VALUES (1, 1); 
SELECT * FROM a WHERE a.a = :a AND a.b = ?1; 
SELECT * FROM a WHERE a.a = ?1 AND a.b = :b; 

Have the same behavior! 


|Index: D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c 
|=== 
|--- D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (revisão 323) 
|+++ D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (cópia de trabalho) 
|@@ -557,9 +557,21 @@ 
| sqlite3ErrorMsg(pParse, "variable number must be between ?1 and ?%d", 
| db->aLimit[SQLITE_LIMIT_VARIABLE_NUMBER]); 
| } 
|- if( i>pParse->nVar ){ 
|- pParse->nVar = i; 
|+ 
|+ // give to expression parameter with same index, another number to avoid 
conflicts 
|+ // with named parameter (:AAA, $AAA, @AAA) and indexed parameter (?NNN) 
|+ for(i=0; inVarExpr; i++){ 
|+ Expr *pE = pParse->apVarExpr[i]; 
|+ assert( pE!=0 ); 
|+ if( pE->iColumn==pExpr->iColumn ){ 
|+ pE->iColumn = (ynVar)(++pParse->nVar); 
|+ break; 
|+ } 
| } 
|+ 
|+ if( pExpr->iColumn>pParse->nVar ){ 
|+ pParse->nVar = pExpr->iColumn; 
|+ } 
| }else{ 
| /* Wildcards like ":aaa", "$aaa" or "@aaa". Reuse the same variable 
| ** number as the prior appearance of the same name, or if the name 



-- 

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] Parametrized Queries issue (Possible bug)

[Israel Lins Albuquerque]

I read that but that say "To avoid confusion, it is best to avoid mixing...", 
but you need never mix them, if you want have none collateral effects !!! 


- Mensagem original - 
De: "Jay A. Kreibich" <j...@kreibi.ch> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Segunda-feira, 14 de Junho de 2010 15:41:01 
Assunto: Re: [sqlite] Parametrized Queries issue (Possible bug) 

On Mon, Jun 14, 2010 at 03:03:52PM -0300, Israel Lins Albuquerque scratched on 
the wall: 
> I know what you booth are are saying. But if this is a limitation of sqlite, 
> this need be documented, and doesn't have any comment of this behavior. 

http://sqlite.org/lang_expr.html#varparam 

"Named parameters are also numbered. The number assigned is the 
next unused number. To avoid confusion, it is best to avoid mixing 
named and numbered parameters." 

The docs are pretty clear about what happened, and that you shouldn't 
do that, and that if you do it anyways you will likely be confused. 

-j 

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

"Intelligence is like underwear: it is important that you have it, 
but showing it to the wrong people has the tendency to make them 
feel uncomfortable." -- Angela Johnson 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


-- 

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] Parametrized Queries issue (Possible bug)

[Israel Lins Albuquerque]

I know what you booth are are saying. But if this is a limitation of sqlite, 
this need be documented, and doesn't have any comment of this behavior. 
And the name of parameter ':a' are changed, we can't set his value, 
only using the index or your new name '?1' !!! 

- Mensagem original - 
De: "David Bicking" <dbic...@yahoo.com> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Segunda-feira, 14 de Junho de 2010 13:06:02 
Assunto: Re: [sqlite] Parametrized Queries issue (Possible bug) 

I think in your first example, the :a comes first, so it is assigned the first 
index value. You then use ?1, which also uses the first parameter index. 

In the second, you use ?1 first, then :b, which sees the first index has been 
used and thus assigns it to the second index. 

As I believe Jay said, you should not mix named and numbered parameter syntax. 

David 

--- On Mon, 6/14/10, Israel Lins Albuquerque <israel...@polibrasnet.com.br> 
wrote: 

> From: Israel Lins Albuquerque <israel...@polibrasnet.com.br> 
> Subject: Re: [sqlite] Parametrized Queries issue (Possible bug) 
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
> Date: Monday, June 14, 2010, 10:55 AM 
> Once more detail... 
> 
> Doing this... 
> 
> SELECT * 
> FROM a 
> WHERE a.a = :a 
> AND a.b = ?1; 
> 
> and this... 
> 
> SELECT * 
> FROM a 
> WHERE a.a = ?1 
> AND a.b = :b; 
> 
> Will have different behavior! 
> 
> 
> 
> - Mensagem original - 
> De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> 
> 
> Para: "sqlite-users" <sqlite-users@sqlite.org> 
> 
> Enviadas: Segunda-feira, 14 de Junho de 2010 11:51:07 
> Assunto: [sqlite] Parametrized Queries issue (Possible bug) 
> 
> 
> Using the example above I see one not documented issue. 
> When I use parameters ?NNN and : or @ or $ , 
> in same query as above some of then will be omitted. I 
> don't know if this is the expected behavior or not. 
> 
> CREATE TABLE a (a, b); 
> CREATE TABLE b (a, b); 
> 
> INSERT INTO a VALUES (0, 1); 
> INSERT INTO a VALUES (1, 0); 
> INSERT INTO a VALUES (1, 1); 
> INSERT INTO b VALUES (0, 1); 
> INSERT INTO b VALUES (1, 0); 
> INSERT INTO b VALUES (1, 1); 
> 
> SELECT * 
> FROM a 
> JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) 
> WHERE a.a = ?1; 
> 
> In this query as 2 parameters but will be see like only 
> one! 
> 
> -- 
> 
> 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 
> 
> 
> 
> -- 
> 
> 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 
> 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


-- 

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] Parametrized Queries issue (Possible bug)

[Israel Lins Albuquerque]

Yes. I know that, but the names of parameters are different, 
and I debug this and the parameter named as ':a' will be overwrited by '?1', 
and if I use sqlite3_bind_parameter_index(stmt, ":a") will fail, returning 0. 
But actually they are not the same. 


- Mensagem original - 
De: "Jay A. Kreibich" <j...@kreibi.ch> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Segunda-feira, 14 de Junho de 2010 12:00:32 
Assunto: Re: [sqlite] Parametrized Queries issue (Possible bug) 

On Mon, Jun 14, 2010 at 11:51:07AM -0300, Israel Lins Albuquerque scratched on 
the wall: 
> Using the example above I see one not documented issue. When I use parameters 
> ?NNN and : or @ or $ , 
> in same query as above some of then will be omitted. I don't know if this is 
> the expected behavior or not. 
> 
> CREATE TABLE a (a, b); 
> CREATE TABLE b (a, b); 
> 
> INSERT INTO a VALUES (0, 1); 
> INSERT INTO a VALUES (1, 0); 
> INSERT INTO a VALUES (1, 1); 
> INSERT INTO b VALUES (0, 1); 
> INSERT INTO b VALUES (1, 0); 
> INSERT INTO b VALUES (1, 1); 
> 
> SELECT * 
> FROM a 
> JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) 
> WHERE a.a = ?1; 
> 
> In this query as 2 parameters but will be see like only one! 


It is expected. 

Nearly all SQL interfaces work this way. 

It is a feature. The same value will be used in both locations in 
the query. 

-j 

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

"Intelligence is like underwear: it is important that you have it, 
but showing it to the wrong people has the tendency to make them 
feel uncomfortable." -- Angela Johnson 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


-- 

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] Parametrized Queries issue (Possible bug)

[Israel Lins Albuquerque]

Once more detail... 

Doing this... 

SELECT * 
FROM a 
WHERE a.a = :a 
AND a.b = ?1; 

and this... 

SELECT * 
FROM a 
WHERE a.a = ?1 
AND a.b = :b; 

Will have different behavior! 



- Mensagem original - 
De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> 
Para: "sqlite-users" <sqlite-users@sqlite.org> 
Enviadas: Segunda-feira, 14 de Junho de 2010 11:51:07 
Assunto: [sqlite] Parametrized Queries issue (Possible bug) 

Using the example above I see one not documented issue. When I use parameters 
?NNN and : or @ or $ , 
in same query as above some of then will be omitted. I don't know if this is 
the expected behavior or not. 

CREATE TABLE a (a, b); 
CREATE TABLE b (a, b); 

INSERT INTO a VALUES (0, 1); 
INSERT INTO a VALUES (1, 0); 
INSERT INTO a VALUES (1, 1); 
INSERT INTO b VALUES (0, 1); 
INSERT INTO b VALUES (1, 0); 
INSERT INTO b VALUES (1, 1); 

SELECT * 
FROM a 
JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) 
WHERE a.a = ?1; 

In this query as 2 parameters but will be see like only one! 

-- 

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 


-- 

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

[sqlite] Parametrized Queries issue (Possible bug)

[Israel Lins Albuquerque]

Using the example above I see one not documented issue. When I use parameters 
?NNN and : or @ or $ , 
in same query as above some of then will be omitted. I don't know if this is 
the expected behavior or not. 

CREATE TABLE a (a, b); 
CREATE TABLE b (a, b); 

INSERT INTO a VALUES (0, 1); 
INSERT INTO a VALUES (1, 0); 
INSERT INTO a VALUES (1, 1); 
INSERT INTO b VALUES (0, 1); 
INSERT INTO b VALUES (1, 0); 
INSERT INTO b VALUES (1, 1); 

SELECT * 
FROM a 
JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) 
WHERE a.a = ?1; 

In this query as 2 parameters but will be see like only one! 

-- 

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] [BUG] Adding an index changes query result

[Israel Lins Albuquerque]

How this solve the bug? 

The function binaryCompareP5 is called (in query compile time) to know what 
affinity will be used to make the comparition between the expression "(c2 <= 
'2')", 
but that function are returning when index not exists SQLITE_AFF_INTEGER and 
not SQLITE_AFF_NONE because affinity of left expression "c2" (is a collumn) is 
INTEGER and 
rigth expression "'2'" is TEXT! 


----- Mensagem original ----- 
De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Quarta-feira, 9 de Junho de 2010 16:34:22 
Assunto: Re: [sqlite] [BUG] Adding an index changes query result 



I make the possible soluction I did't know if this is the best but is this: 

//
 
Index: D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c 
=== 
--- D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (revisão 323) 
+++ D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (cópia de trabalho) 
@@ -52,6 +52,18 @@ 
assert( pExpr->pTab && jpTab->nCol ); 
return pExpr->pTab->aCol[j].affinity; 
} 
+ if( op==TK_REGISTER ){ 
+ op = pExpr->op2; /* This only happens with SQLITE_ENABLE_STAT2 */ 
+ } 
+ if (op == TK_STRING) { 
+ return SQLITE_AFF_TEXT; 
+ } 
+ if (op == TK_INTEGER) { 
+ return SQLITE_AFF_NUMERIC; 
+ } 
+ if (op == TK_FLOAT) { 
+ return SQLITE_AFF_REAL; 
+ } 
return pExpr->affinity; 
} 

@@ -124,10 +136,10 @@ 
char sqlite3CompareAffinity(Expr *pExpr, char aff2){ 
char aff1 = sqlite3ExprAffinity(pExpr); 
if( aff1 && aff2 ){ 
- /* Both sides of the comparison are columns. If one has numeric 
+ /* Both sides of the comparison are columns. If both has numeric 
** affinity, use that. Otherwise use no affinity. 
*/ 
- if( sqlite3IsNumericAffinity(aff1) || sqlite3IsNumericAffinity(aff2) ){ 
+ if( sqlite3IsNumericAffinity(aff1) && sqlite3IsNumericAffinity(aff2) ){ 
return SQLITE_AFF_NUMERIC; 
}else{ 
return SQLITE_AFF_NONE; 
/********/
 

-- 

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 


-- 

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] [BUG] Adding an index changes query result

[Israel Lins Albuquerque]

I make the possible soluction I did't know if this is the best but is this: 

//
 
Index: D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c 
=== 
--- D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (revisão 323) 
+++ D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (cópia de trabalho) 
@@ -52,6 +52,18 @@ 
assert( pExpr->pTab && jpTab->nCol ); 
return pExpr->pTab->aCol[j].affinity; 
} 
+ if( op==TK_REGISTER ){ 
+ op = pExpr->op2; /* This only happens with SQLITE_ENABLE_STAT2 */ 
+ } 
+ if (op == TK_STRING) { 
+ return SQLITE_AFF_TEXT; 
+ } 
+ if (op == TK_INTEGER) { 
+ return SQLITE_AFF_NUMERIC; 
+ } 
+ if (op == TK_FLOAT) { 
+ return SQLITE_AFF_REAL; 
+ } 
return pExpr->affinity; 
} 

@@ -124,10 +136,10 @@ 
char sqlite3CompareAffinity(Expr *pExpr, char aff2){ 
char aff1 = sqlite3ExprAffinity(pExpr); 
if( aff1 && aff2 ){ 
- /* Both sides of the comparison are columns. If one has numeric 
+ /* Both sides of the comparison are columns. If both has numeric 
** affinity, use that. Otherwise use no affinity. 
*/ 
- if( sqlite3IsNumericAffinity(aff1) || sqlite3IsNumericAffinity(aff2) ){ 
+ if( sqlite3IsNumericAffinity(aff1) && sqlite3IsNumericAffinity(aff2) ){ 
return SQLITE_AFF_NUMERIC; 
}else{ 
return SQLITE_AFF_NONE; 
/****/
 

-- 

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] MySQL vs. SQLite

[Israel Lins Albuquerque]

I asked that before because Oracle do this. 
It notify the developer and ask if we want to create that transient index as 
materialized. 
I didn't see this working but I know this by a friend. 


-- 

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] Performance issue on view

[Israel Lins Albuquerque]

An Correction! 

/***/ 
DROP TRIGGER IF EXISTS movement_trg02; 
CREATE TRIGGER movement_trg02 AFTER INSERT ON movement FOR EACH ROW 
BEGIN 
REPLACE INTO movement 
SELECT t1.id 
, t1.account_id 
, t1.payment 
, t1.amount 
, (COALESCE((SELECT balance 
FROM movement s1 
WHERE s1.account_id = t1.account_id 
AND (s1.payment < t1.payment 
OR (s1.payment = t1.payment 
AND s1.id < t1.id)) 
GROUP BY s1.account_id 
HAVING s1.payment = MAX(s1.payment) 
AND s1.id = MAX(s1.id)), 0) 
+ t1.amount) AS balance 
FROM movement t1 
WHERE id = new.id; 

UPDATE movement 
SET balance = balance + new.amount 
WHERE account_id = new.account_id 
AND (payment > new.payment 
OR (payment = new.payment 
AND id > new.id)); 
END; 

-- account_id or payment changed 
DROP TRIGGER IF EXISTS movement_trg03; 
CREATE TRIGGER movement_trg03 AFTER UPDATE ON movement FOR EACH ROW WHEN 
(((old.account_id != new.account_id) OR (old.payment != new.payment)) AND 
(new.amount != old.amount)) 
BEGIN 
-- same code as delete trigger 
UPDATE movement 
SET balance = balance - old.amount 
WHERE account_id = old.account_id 
AND (payment > old.payment 
OR (payment = old.payment 
AND id > old.id)); 

-- same code as insert 
REPLACE INTO movement 
SELECT t1.id 
, t1.account_id 
, t1.payment 
, t1.amount 
, (COALESCE((SELECT balance 
FROM movement s1 
WHERE s1.account_id = t1.account_id 
AND (s1.payment < t1.payment 
OR (s1.payment = t1.payment 
AND s1.id < t1.id)) 
GROUP BY s1.account_id 
HAVING s1.payment = MAX(s1.payment) 
AND s1.id = MAX(s1.id)), 0) 
+ t1.amount) AS balance 
FROM movement t1 
WHERE id = new.id; 

UPDATE movement 
SET balance = balance + new.amount 
WHERE account_id = new.account_id 
AND (payment > new.payment 
OR (payment = new.payment 
AND id > new.id));; 
END; 
/***/ 


-- 

Regards/Atenciosamente, 

Israel Lins Albuquerque 
Developer/esenvolvimento 
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] Performance issue on view

[Israel Lins Albuquerque]

Look this above! This will help you. I did't maked all test cases! 

/***/ 
CREATE TABLE account ( 
id INTEGER PRIMARY KEY AUTOINCREMENT, 
name TEXT, 
initial_balance REAL 
); 

CREATE TABLE movement ( 
id INTEGER PRIMARY KEY AUTOINCREMENT, 
account_id INTEGER REFERENCES account(id), 
payment UNIXEPOCH, 
amount REAL, 
balance REAL 
); 
CREATE INDEX movent_idx01 ON movement (account_id, payment, id); 

DROP TRIGGER IF EXISTS movement_trg01; 
CREATE TRIGGER movement_trg01 AFTER DELETE ON movement FOR EACH ROW 
BEGIN 
UPDATE movement 
SET balance = balance - old.amount 
WHERE account_id = old.account_id 
AND (payment > old.payment 
OR (payment = old.payment 
AND id > old.id)); 
END; 

DROP TRIGGER IF EXISTS movement_trg02; 
CREATE TRIGGER movement_trg02 AFTER INSERT ON movement FOR EACH ROW 
BEGIN 
REPLACE INTO movement 
SELECT t1.id 
, t1.account_id 
, t1.payment 
, t1.amount 
, (COALESCE((SELECT balance 
FROM movement s1 
WHERE s1.account_id = t1.account_id 
AND (s1.payment < t1.payment 
OR (s1.payment = t1.payment 
AND s1.id < t1.id)) 
GROUP BY s1.account_id 
HAVING s1.payment = MAX(s1.payment) 
AND s1.id = MAX(s1.id)), 0) 
+ t1.amount) AS balance 
FROM movement t1 
WHERE id = new.id; 

UPDATE movement 
SET balance = balance + new.amount 
WHERE account_id = new.account_id 
AND payment >= new.payment 
AND id > new.id; 
END; 

-- account and payment not changed 
DROP TRIGGER IF EXISTS movement_trg03; 
CREATE TRIGGER movement_trg03 AFTER UPDATE ON movement FOR EACH ROW WHEN 
((old.account_id == new.account_id) AND (old.payment == new.payment) AND 
(new.amount != old.amount)) 
BEGIN 
UPDATE movement 
SET balance = balance + (new.amount - old.amount) 
WHERE account_id = old.account_id 
AND (payment > old.payment 
OR (payment = old.payment 
AND id >= old.id)); 
END; 

-- account_id or payment changed 
DROP TRIGGER IF EXISTS movement_trg03; 
CREATE TRIGGER movement_trg03 AFTER UPDATE ON movement FOR EACH ROW WHEN 
(((old.account_id != new.account_id) OR (old.payment != new.payment)) AND 
(new.amount != old.amount)) 
BEGIN 
-- same code as delete trigger 
UPDATE movement 
SET balance = balance - old.amount 
WHERE account_id = old.account_id 
AND (payment > old.payment 
OR (payment = old.payment 
AND id > old.id)); 

-- same code as insert 
REPLACE INTO movement 
SELECT t1.id 
, t1.account_id 
, t1.payment 
, t1.amount 
, (COALESCE((SELECT balance 
FROM movement s1 
WHERE s1.account_id = t1.account_id 
AND (s1.payment < t1.payment 
OR (s1.payment = t1.payment 
AND s1.id < t1.id)) 
GROUP BY s1.account_id 
HAVING s1.payment = MAX(s1.payment) 
AND s1.id = MAX(s1.id)), 0) 
+ t1.amount) AS balance 
FROM movement t1 
WHERE id = new.id; 

UPDATE movement 
SET balance = balance + new.amount 
WHERE account_id = new.account_id 
AND payment >= new.payment 
AND id > new.id; 
END; 

INSERT INTO account (name, initial_balance) VALUES ('account 1', 0); 
INSERT INTO account (name, initial_balance) VALUES ('account 2', 100.0); 
INSERT INTO account (name, initial_balance) VALUES ('account 3', -100.0); 

INSERT INTO movement (account_id, payment, amount) VALUES (1, 1275503470, 
123.45); 
INSERT INTO movement (account_id, payment, amount) VALUES (1, 1275503475, 
-24.10); 
INSERT INTO movement (account_id, payment, amount) VALUES (1, 1275503475, 
50.00); 

UPDATE movement 
SET amount = 150.45 
WHERE payment = 1275503470; 

-- to know what are the current balance of a account 
SELECT a.initial_balance + m.balance 
FROM account a 
JOIN movement m ON (a.id = m.account_id) 
GROUP BY m.account_id 
HAVING payment = MAX(payment); 

/***/ 



- Mensagem original - 
De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> 
Para: steph...@mankowski.fr, "General Discussion of SQLite Database" 
<sqlite-users@sqlite.org> 
Enviadas: Terça-feira, 1 de Junho de 2010 17:12:02 
Assunto: Re: [sqlite] Performance issue on view 

Create a new table to do this and add a trigger on op to make the sum. 

- Mensagem original - 
De: "Stéphane MANKOWSKI" <steph...@mankowski.fr> 
Para: sqlite-users@sqlite.org 
Enviadas: Terça-feira, 1 de Junho de 2010 16:57:16 
Assunto: [sqlite] Performance issue on view 

Hi, 

In the this database file (http://skrooge.org/files/test.wrk), I created a 
table 
named "op" containing banking 
transactions. 
A transaction has: 
An unique id 
An account 
A date 
An amount 

I created a view named "v_op" with one more computed attribute named 
"balance". 
This attribute is the sum of all previous transactions (including current one) 
for the same account. 

My problem is that v_op is very slow. This is not usable. 

What can I do to improve performances ? 

PS: I don't want to compute "balance" attribute by code and save it in op 
balance due

Re: [sqlite] Performance issue on view

[Israel Lins Albuquerque]

Create a new table to do this and add a trigger on op to make the sum. 

- Mensagem original - 
De: "Stéphane MANKOWSKI" <steph...@mankowski.fr> 
Para: sqlite-users@sqlite.org 
Enviadas: Terça-feira, 1 de Junho de 2010 16:57:16 
Assunto: [sqlite] Performance issue on view 

Hi, 

In the this database file (http://skrooge.org/files/test.wrk), I created a 
table 
named "op" containing banking 
transactions. 
A transaction has: 
An unique id 
An account 
A date 
An amount 

I created a view named "v_op" with one more computed attribute named 
"balance". 
This attribute is the sum of all previous transactions (including current one) 
for the same account. 

My problem is that v_op is very slow. This is not usable. 

What can I do to improve performances ? 

PS: I don't want to compute "balance" attribute by code and save it in op 
balance due to the fact that I am using an undo/redo mechanism. 

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


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] MySQL vs. SQLite

[Israel Lins Albuquerque]

About that future release functionality. Will be possible to know whats 
temporary index are created? 
Using that information will be easy to know what indexes we need create to 
increase perfomance, 
don't giving chance to sqlite create that indexes! 
-- 

Regards/Atenciosamente, 

Israel Lins Albuquerque 
Developer/Desenvolvimento 
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] MySQL vs. SQLite

[Israel Lins Albuquerque]

- Mensagem original - 
De: "Richard Hipp" <d...@sqlite.org> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Segunda-feira, 31 de Maio de 2010 20:56:33 
Assunto: Re: [sqlite] MySQL vs. SQLite 

On Mon, May 31, 2010 at 3:57 PM, Simon Slavin <slav...@bigfraud.org> wrote: 

> 
> On 31 May 2010, at 7:17pm, Michael Ash wrote: 
> 
> > (Curiously, this is only 
> > the case on the first run of the query; if I run the query again, the 
> > MySQL time drops to close to zero while sqlite appears to take the 
> > same time. Maybe MySQL leaves the database sorted or somehow caches 
> > that particular run?) 
> 
> SQLite finds the best index it can and uses it. If there are no indices, 
> it searches the entire table. 
> 
> MySQL has a server process which runs until you shut it down (usually when 
> you reboot the server). Every time you do anything that could use an index 
> (including the WHERE clause in an UPDATE) it looks for a perfect index. If 
> it finds one, it uses it. If it doesn't find one, it finds the best it can, 
> but constructs a temporary index specially designed for the operating you're 
> doing. These temporary indices are cached, on the assumption that if you've 
> executed a command once, you're probably going to do it again. 
> 
> This is an excellent part of MySQL and has lead many MySQL programmers to 
> completely ignore the CREATE INDEX command because once MySQL has executed 
> one of every command, everything executes quickly. However, it requires a 
> lot of memory to be used for caching, and a persistent server process. And 
> it would require a thorough rewrite of SQLite which would then no longer be 
> practical for small fast embedded devices. 
> 

Great explanation, Simon. Thanks! 

FWIW, the latest versions of SQLite in the source tree will also create a 
temporary index to help with a query, if SQLite estimates that the expense 
of creating and using index is less than doing a full-table scan. SQLite is 
unable to cache indices, though. So the entire cost of building the index 
must be recouped on a single query or SQLite will figure that creating the 
index is not worth the effort and just go ahead with a brute-force query. 
Hence, temporary indices are normally only created for multi-way joins or 
perhaps for subqueries. 

This automatic-indexing feature is new. It has only been in the source tree 
since early April and has not yet appeared in a released version of SQLite. 


> 
> MySQL and SQLite are both excellent examples of their craft, but they're 
> suitable for different situations. About the only thing they have in common 
> is that they both speak SQL. 
> 
> Simon. 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 



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


About that future release functionality. Will be possible to know whats 
temporary index are created? 
Using that information will be easy to know what indexes we need create to 
increase perfomance, 
don't giving chance to sqlite create that indexes! 


-- 

Regards/Atenciosamente, 

Israel Lins Albuquerque 
Developer/Desenvolvimento 
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] What languages can include SQLite statically?

[Israel Lins Albuquerque]

Look for C++ QT framework! http://qt.nokia.com/ 


- Mensagem original - 
De: "Gilles Ganault" <gilles.gana...@free.fr> 
Para: sqlite-users@sqlite.org 
Enviadas: Terça-feira, 1 de Junho de 2010 8:10:34 
Assunto: Re: [sqlite] What languages can include SQLite statically? 

On Tue, 1 Jun 2010 11:57:29 +0100, Simon Slavin 
<slav...@bigfraud.org> wrote: 
>The obvious solution is to use PHP, and have it use one of the three 
>avaialable sets of SQLite calls. 

Can I compile a PHP script + modules into something that will run on 
embedded devices? They don't have enough RAM to run eg. Apache + 
mod_php. 

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


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] data type from join multiple tables

[Israel Lins Albuquerque]

Or you can use sqlite3_column_decltype in conjunction with 
sqlite3_column_table_name, sqlite3_column_database_name, 
sqlite3_column_origin_name but to use the last three functions you will need 
compile sqlite with -DSQLITE_ENABLE_COLUMN_METADATA directive on LCFLAGS or 
something like this! 

- Mensagem original - 
De: "P Kishor" <punk.k...@gmail.com> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Sexta-feira, 14 de Maio de 2010 11:05:43 
Assunto: Re: [sqlite] data type from join multiple tables 

On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro <fabiolinos...@gmail.com> wrote: 
> I need to identify data types extracted from a 
> join between multiple tables without using cross-checking table_info more 
> pragmatic. 
> 

Could you clarify what you really want to do? Your question is not 
clear at all, at least to me. What do you mean by "using 
cross-checking table_info more pragmatic"? More pragmatic than what? 

> Is there a faster way to do it? 
> 

Faster than what? 

If you want data_types, you can use SELECT Typeof(column_name) 




> 



-- 
Puneet Kishor http://www.punkish.org 
Carbon Model http://carbonmodel.org 
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org 
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor 
Nelson Institute, UW-Madison http://www.nelson.wisc.edu 
--- 
Assertions are politics; backing up assertions with evidence is science 
=== 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


___
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] SQLite 16 bits

[Israel Lins Albuquerque]

Actually, if your machine is 32 bits or 64 bits to run command-line 
version of sqlite you don't need 16 bit version! 

What OS are you using? 

- Mensagem original - 
De: "Andi Suhandi" <suha...@informatika.co.id> 
Para: sqlite-users@sqlite.org 
Enviadas: Quarta-feira, 24 de Março de 2010 23:04:36 
Assunto: [sqlite] SQLite 16 bits 

Is there SQLite for 16 bit OS ? 
I want to use sqlite in my program in turbo c++ and running in DOS. 

regards 


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


-- 

Regards , 

Israel Lins Albuquerque 
Desenvolvimento 
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] Why we don't have strpos function?

[Israel Lins Albuquerque]

I have to talk with someone to add this function on the sqlite? Or this will be 
tested and added later? 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] Why we don't have strpos function?

[Israel Lins Albuquerque]

I fink now is all right: 

I follow your instructions and no more call to strlen are maked. 
And utf8 chars are ignored returning the correct char index when non blob, 
and if in text have any '\0' (only in blob fields i believe), 
this code will work too. 


Porwerfull test cases: 
-- with text 
SELECT DISTINCT 'strpos(' || quote(str) || ', ' 
|| quote(substr) || ', ' 
|| CAST(ti.i * ts.s AS TEXT) || ')' as expr 
, strpos(str, substr, ti.i * ts.s) as result 
FROM (SELECT 'áéíóúáéíóúáéíóúáéíóú' as str, 'áéíóú' as substr) 
JOIN (SELECT 0 as i UNION ALL 
SELECT 1 as i UNION ALL 
SELECT 2 as i UNION ALL 
SELECT 3 as i UNION ALL 
SELECT 4 as i UNION ALL 
SELECT 5 as i) as ti 
JOIN (SELECT 1 as s UNION ALL 
SELECT -1 as s) as ts 
ORDER BY ti.i * ts.s ; 

-- with blob 
SELECT DISTINCT 'strpos(' || quote(str) || ', ' 
|| quote(substr) || ', ' 
|| CAST(ti.i * ts.s AS TEXT) || ')' as expr 
, strpos(str, substr, ti.i * ts.s) as result 
FROM (SELECT X'0001020001020001020001020001' as str, X'000102' as substr) 
JOIN (SELECT 0 as i UNION ALL 
SELECT 1 as i UNION ALL 
SELECT 2 as i UNION ALL 
SELECT 3 as i UNION ALL 
SELECT 4 as i UNION ALL 
SELECT 5 as i) as ti 
JOIN (SELECT 1 as s UNION ALL 
SELECT -1 as s) as ts 
ORDER BY ti.i * ts.s ; 

-- with integer 
SELECT DISTINCT 'strpos(' || quote(str) || ', ' 
|| quote(substr) || ', ' 
|| CAST(ti.i * ts.s AS TEXT) || ')' as expr 
, strpos(str, substr, ti.i * ts.s) as result 
FROM (SELECT 102102102102 as str, 102 as substr) 
JOIN (SELECT 0 as i UNION ALL 
SELECT 1 as i UNION ALL 
SELECT 2 as i UNION ALL 
SELECT 3 as i UNION ALL 
SELECT 4 as i UNION ALL 
SELECT 5 as i) as ti 
JOIN (SELECT 1 as s UNION ALL 
SELECT -1 as s) as ts 
ORDER BY ti.i * ts.s ; 

/**/ 
Index: func.c 
=== 
--- func.c (revision 264) 
+++ func.c (working copy) 
@@ -117,6 +117,77 @@ 
} 

/* 
+** Implementation of the strpos() function 
+*/ 
+static void strposFunc( 
+ sqlite3_context *context, 
+ int argc, 
+ sqlite3_value **argv 
+){ 
+ const char *z; 
+ const char *z1; 
+ const char *z2; 
+ int len; 
+ int len1; 
+ int instnum; 
+ int pass; 
+ 
+ assert( argc==2 || argc==3 ); 
+ if( sqlite3_value_type(argv[1]) == SQLITE_NULL 
+ || (argc==3 && sqlite3_value_type(argv[2]) != SQLITE_INTEGER) 
+ ){ 
+ return; 
+ } 
+ 
+ z = (char*)sqlite3_value_blob(argv[0]); 
+ if (z == NULL) return; 
+ 
+ z1 = (char*)sqlite3_value_blob(argv[1]); 
+ if (z1 == NULL) return; 
+ 
+ if (argc>=3) { 
+ instnum = sqlite3_value_int64(argv[2]); 
+ if (instnum == 0) return; 
+ } else instnum = 1; 
+ 
+ len = sqlite3_value_bytes(argv[0]); 
+ len1 = sqlite3_value_bytes(argv[1]); 
+ if (instnum < 0) { 
+ pass = -1; 
+ z2 = z + len - len1; 
+ } else { 
+ pass = 1; 
+ z2 = z; 
+ } 
+ 
+ while ((z2 >= z) && ((z2 + len1) <= (z + len))) { 
+ if (memcmp(z2, z1, len1) == 0) { 
+ instnum -= pass; 
+ if (instnum == 0) break; 
+ } 
+ z2 += pass; 
+ } 
+ 
+ 
+ if (instnum == 0) { 
+ int pos = 0; 
+ if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) { 
+ // remove the utf-8 chars until here 
+ while (z <= z2) { 
+ SQLITE_SKIP_UTF8((u_char*)z); 
+ ++pos; 
+ } 
+ } else { 
+ pos = (int)(z2 - z) + 1; 
+ } 
+ sqlite3_result_int64(context, pos); 
+ return; 
+ } else { 
+ sqlite3_result_int64(context, 0); 
+ } 
+} 
+ 
+/* 
** Implementation of the abs() function. 
** 
** IMP: R-23979-26855 The abs(X) function returns the absolute value of 
@@ -1527,6 +1598,8 @@ 
AGGREGATE(max, 1, 1, 1, minmaxStep, minMaxFinalize ), 
FUNCTION(typeof, 1, 0, 0, typeofFunc ), 
FUNCTION(length, 1, 0, 0, lengthFunc ), 
+ FUNCTION(strpos, 2, 0, 0, strposFunc ), 
+ FUNCTION(strpos, 3, 0, 0, strposFunc ), 
FUNCTION(substr, 2, 0, 0, substrFunc ), 
FUNCTION(substr, 3, 0, 0, substrFunc ), 
FUNCTION(abs, 1, 0, 0, absFunc ), 

/**/ 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] Why we don't have strpos function?

[Israel Lins Albuquerque]

are you right the call to strpos("áéíóú", "í") are returning 5 and not 3 

I'm looking for this... 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] Why we don't have strpos function?

[Israel Lins Albuquerque]

actually yes, because this is the minimal size of a char in UTF-8, because 
sqlite3_value_text returns the 


- Mensagem original - 
De: "Jay A. Kreibich" <j...@kreibi.ch> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Quarta-feira, 17 de Março de 2010 15:19:43 
Assunto: Re: [sqlite] Why we don't have strpos function? 

On Wed, Mar 17, 2010 at 01:15:35PM -0300, Israel Lins Albuquerque scratched on 
the wall: 
> There are the patch: 

Which seems to assume one character equals one byte. 

-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 


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] Why we don't have strpos function?

[Israel Lins Albuquerque]

There are the patch: 

/***/ 
Index: func.c 
=== 
--- func.c (revision 264) 
+++ func.c (working copy) 
@@ -117,6 +117,65 @@ 
} 

/* 
+** Implementation of the strpos() function 
+*/ 
+static void strposFunc( 
+ sqlite3_context *context, 
+ int argc, 
+ sqlite3_value **argv 
+){ 
+ const char *z; 
+ const char *z1; 
+ const char *z2; 
+ int len; 
+ int len1; 
+ int pos; 
+ int pass; 
+ 
+ assert( argc==2 || argc==3 ); 
+ if( sqlite3_value_type(argv[1]) == SQLITE_NULL 
+ || (argc==3 && sqlite3_value_type(argv[2]) != SQLITE_INTEGER) 
+ ){ 
+ return; 
+ } 
+ 
+ z = (char*)sqlite3_value_text(argv[0]); 
+ if (z == NULL) return; 
+ 
+ z1 = (char*)sqlite3_value_text(argv[1]); 
+ if (z1 == NULL) return; 
+ 
+ if (argc>=3) { 
+ pos = sqlite3_value_int64(argv[2]); 
+ if (pos == 0) return; 
+ } else pos = 1; 
+ 
+ if (pos < 0) { 
+ pass = -1; 
+ z2 = z + strlen(z) - 1; 
+ } else { 
+ pass = 1; 
+ z2 = z; 
+ } 
+ 
+ len = strlen(z); 
+ len1 = strlen(z1); 
+ do { 
+ if (strncmp(z2, z1, len1) == 0) { 
+ pos -= pass; 
+ if (pos == 0) break; 
+ } 
+ z2 += pass; 
+ } while ((z2 >= z) && (z2 < (z + len))); 
+ 
+ if (pos == 0) { 
+ sqlite3_result_int64(context, (int)(z2 - z) + 1); 
+ } else { 
+ sqlite3_result_int64(context, 0); 
+ } 
+} 
+ 
+/* 
** Implementation of the abs() function. 
** 
** IMP: R-23979-26855 The abs(X) function returns the absolute value of 
@@ -1527,6 +1586,8 @@ 
AGGREGATE(max, 1, 1, 1, minmaxStep, minMaxFinalize ), 
FUNCTION(typeof, 1, 0, 0, typeofFunc ), 
FUNCTION(length, 1, 0, 0, lengthFunc ), 
+ FUNCTION(strpos, 2, 0, 0, strposFunc ), 
+ FUNCTION(strpos, 3, 0, 0, strposFunc ), 
FUNCTION(substr, 2, 0, 0, substrFunc ), 
FUNCTION(substr, 3, 0, 0, substrFunc ), 
FUNCTION(abs, 1, 0, 0, absFunc ), 

/***/ 


- Mensagem original - 
De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Quarta-feira, 17 de Março de 2010 12:08:56 
Assunto: Re: [sqlite] Why we don't have strpos function? 

I forgot attach... 

This are based in the current release 3.6.23. 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 



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


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] Why we don't have strpos function?

[Israel Lins Albuquerque]

I forgot attach... 

This are based in the current release 3.6.23. 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] Why we don't have strpos function?

[Israel Lins Albuquerque]

I've make an own implementation and I want to contribute see the patch 
attached. 

Sintax: 
strpos(string, substring[, pos]); 

string -> the master string, can be any type (INTEGER, REAL, BLOB) 
substring -> to be searched, can be any type (INTEGER, REAL, BLOB) 
pos [optional default = 1] -> is the number of instance of substring in want, 
this value can be negative, negative values 
will be the search in reverse order like (strrchr) 

Return: 
The return is the index (1 based) of the char, or 0 if not found, or null if 
invalid parameters passed 

Exemples: 
dirname = substr(filename, 1, strpos(filename, "/", -1) - 1) 
basename = substr(filename, strpos(filename, "/", -1) + 1) 
strpos(123231, 2) == 2 
strpos(123231, 2, 1) == 2 
strpos(123231, 2, -1) == 4 
strpos(123231, 2, -2) == 2 
strpos(123231, 2, -3) == 0 


retrictions: pos need be different of zero 


- Mensagem original - 
De: "Simon Slavin" <slav...@bigfraud.org> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Quarta-feira, 17 de Março de 2010 11:05:04 
Assunto: Re: [sqlite] Why we don't have strpos function? 

Strings are 16-bit strings which may or may not be Unicode strings, I think. 
It's hard to work out the position in a string if you don't know what encoding 
you're using. 

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


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

[sqlite] Why we don't have strpos function?

[Israel Lins Albuquerque]

... 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] SQLite version 3.6.23 - date.c

[Israel Lins Albuquerque]

My apologies everybody. I'm wrong. 

forget what I said. 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] SQLite version 3.6.23 - date.c

[Israel Lins Albuquerque]

previous is the 3.6.22 and current is 3.6.23! 

The sqlite site says: 
'For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, -MM-DD. The 
format for CURRENT_TIMESTAMP is "-MM-DD HH:MM:SS".' 

And looks for me this are changed to: 
For CURRENT_TIMESTAMP, -MM-DD. The format for CURRENT_DATE is "-MM-DD 
HH:MM:SS" 


- Mensagem original - 
De: "D. Richard Hipp" <d...@hwaci.com> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Quarta-feira, 10 de Março de 2010 10:36:15 
Assunto: Re: [sqlite] SQLite version 3.6.23 - date.c 


On Mar 10, 2010, at 7:36 AM, Israel Lins Albuquerque wrote: 

> In this new version: 
> 
> 
> at file date.c line 1095 are changed? Why? 
> 
> previous version 
> STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc), 
> STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, 
> currentTimeFunc), 
> 
> new version 
> STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d", 0, currentTimeFunc), 
> STR_FUNCTION(current_date, 0, "%Y-%m-%d %H:%M:%S", 0, 
> currentTimeFunc), 
> 
> I fink this is a problem because: 
> SELECT CURRENT_DATE will return 2010-03-10 09:34:55 
> SELECT CURRENT_TIMESTAMP will return 2010-03-10 
> 
> Are this correct? 

I think you have it backwards. What you have labeled as "previous 
version" above is what the current code looks like and your "current 
version" is what it used to look like. See 
http://www.sqlite.org/src/ci/eb98265b59 
for a diff of the change. This was a bug fix. 

The code in question only comes into play when you compile with 
SQLITE_OMIT_DATETIME_FUNCS and so it doesn't matter for most people. 
We didn't discover the problem until recently when we were adding some 
tests that make use of SQLITE_OMIT_DATETIME_FUNCS. 


> 
> 
> Regards , 
> 
> Israel Lins Albuquerque 
> Developer 
> Polibrás Brasil Software Ltda. 
> 
> 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

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



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


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] SQLite version 3.6.23 - date.c

[Israel Lins Albuquerque]

previous is the 3.6.22 and current is 3.6.23! 

The sqlite site says: 
'For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, -MM-DD. The 
format for CURRENT_TIMESTAMP is "-MM-DD HH:MM:SS".' 

And looks for me this are changed to: 
For CURRENT_DATE, CURRENT_TIMESTAMP. The format for -MM-DD is "-MM-DD 
HH:MM:SS". 

- Mensagem original - 
De: "D. Richard Hipp" <d...@hwaci.com> 
Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> 
Enviadas: Quarta-feira, 10 de Março de 2010 10:36:15 
Assunto: Re: [sqlite] SQLite version 3.6.23 - date.c 


On Mar 10, 2010, at 7:36 AM, Israel Lins Albuquerque wrote: 

> In this new version: 
> 
> 
> at file date.c line 1095 are changed? Why? 
> 
> previous version 
> STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc), 
> STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, 
> currentTimeFunc), 
> 
> new version 
> STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d", 0, currentTimeFunc), 
> STR_FUNCTION(current_date, 0, "%Y-%m-%d %H:%M:%S", 0, 
> currentTimeFunc), 
> 
> I fink this is a problem because: 
> SELECT CURRENT_DATE will return 2010-03-10 09:34:55 
> SELECT CURRENT_TIMESTAMP will return 2010-03-10 
> 
> Are this correct? 

I think you have it backwards. What you have labeled as "previous 
version" above is what the current code looks like and your "current 
version" is what it used to look like. See 
http://www.sqlite.org/src/ci/eb98265b59 
for a diff of the change. This was a bug fix. 

The code in question only comes into play when you compile with 
SQLITE_OMIT_DATETIME_FUNCS and so it doesn't matter for most people. 
We didn't discover the problem until recently when we were adding some 
tests that make use of SQLITE_OMIT_DATETIME_FUNCS. 


> 
> 
> Regards , 
> 
> Israel Lins Albuquerque 
> Developer 
> Polibrás Brasil Software Ltda. 
> 
> 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

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



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


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
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] SQLite version 3.6.23 - date.c

[Israel Lins Albuquerque]

In this new version: 


at file date.c line 1095 are changed? Why? 

previous version 
STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc), 
STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc), 

new version 
STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d", 0, currentTimeFunc), 
STR_FUNCTION(current_date, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc), 

I fink this is a problem because: 
SELECT CURRENT_DATE will return 2010-03-10 09:34:55 
SELECT CURRENT_TIMESTAMP will return 2010-03-10 

Are this correct? 


Regards , 

Israel Lins Albuquerque 
Developer 
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] One data base versus two smaller ones

[Israel Lins Albuquerque]

In the file 'sqliteLimit.h' has constants that can changed at compiler time but 
the defaults SQLITE_DEFAULT_PAGE_SIZE = 1024 
SQLITE_DEFAULT_CACHE_SIZE 2000 

this info is used to create the database when the database already created the 
size used is in the database header. 

The size of cache is used to reduce the disk access . If the record is less 
than 100 bytes on 1024 will contain aprox. 10 records if you table has no more 
than 10.000 you will need 976 pages to contain all (remember this is a 
prevision, can be different). If you define your cache will contain only 30% of 
data you need only 292 pages. These are only statistics, but you can make a 
similar calculation to set how much memory you will use. 
In my case I take the amount of memory, split 30% of it to sqlite using a 
proportionality to the size of the database for each attach ed; 

- Mensagem original - 
De: "a1rex" <a1rex2...@yahoo.com> 
Para: j...@kreibi.ch, "General Discussion of SQLite Database" 
<sqlite-users@sqlite.org> 
Enviadas: Sexta-feira, 19 de Fevereiro de 2010 12:57:15 (GMT-0300) 
Auto-Detected 
Assunto: Re: [sqlite] One data base versus two smaller ones 

Thank you very much for your help! 

Since my typical record is less than 100 bytes I guess that I can use Page Size 
= 512 bytes 
without degradation of database performance.This would conserve memory. 

How vital is default number of pages for database performance? 
Can I drastically drop the number of pages to number of records accessed by 
user per his typical database session? 

Regards, 
Samuel 


- Original Message  
From: Jay A. Kreibich <j...@kreibi.ch> 

On Fri, Feb 19, 2010 at 09:39:08AM -0300, Israel Lins Albuquerque scratched on 
the wall: 
> Samuel, 
> 
> Each one attached database has its own page cache with 2000 
> (default number of pages in cache) * 1024 (default size in 
> bytes of a page), 

On many Windows systems it will default to 4096. It tries to match the 
cluster size on NTFS volumes. 

> totaling 2 Mb of ram. 

Actually, it is closer to 3MB (or 9MB) of used memory, as each entry 
in the page cache has some overhead. 

-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 



__ 
Make your browsing faster, safer, and easier with the new Internet Explorer® 8. 
Optimized for Yahoo! Get it Now for Free! at 
http://downloads.yahoo.com/ca/internetexplorer/ 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


-- 

Israel Lins Albuquerque 
Desenvolvimento 
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] Memory usage – one data base versus tw o smaller ones

[Israel Lins Albuquerque]

Samuel, 

Each one attached database has its own page cache with 2000 (default number of 
pages in cache) * 1024 (default size in bytes of a page), totaling 2 Mb of ram. 
But you can define the number of pages in cache. 
I work using 5 attached databases this needs 10Mb, but some devices (PalmOS) 
has only 2.5 Mb!!! And I resolve using the SQL command 'PRAGMA 
[DB_ALIAS].cache_size = [NEW_CACHE_SIZE]'. 

P.S. 2000 is maximum number of pages cached, not used at all instantly on 
attach. 

- Mensagem original - 
De: "a1rex" <a1rex2...@yahoo.com> 
Para: sqlite-users@sqlite.org 
Enviadas: Quarta-feira, 17 de Fevereiro de 2010 16:38:27 (GMT-0300) 
Auto-Detected 
Assunto: [sqlite] Memory usage – one data base versus two smaller ones 


For some reasons it is more convenient for the project to 
have a few smaller databases with unrelated data than one containing 
everything. My only concern is RAM memory. 
How much burden/memory overhead an additional database would introduce? 

Thank you for your input, 

Samuel 


__ 
Ask a question on any topic and get answers from real people. Go to Yahoo! 
Answers and share what you know at http://ca.answers.yahoo.com 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


-- 

Israel Lins Albuquerque 
Desenvolvimento 
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] How to discover the state of a statement

[Israel Lins Albuquerque]

Thank you, that's work for me. 


-- 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

[sqlite] How to discover the state of a statement

[Israel Lins Albuquerque]

I want to now if a statement are in a valid row. 

Some one will tell me to get the sqlite_step result, but I don't want pass this 
value to a lot of functions. 
That functions receive the stament to build some operations and that can be 
more fast if a now that information. 


Thank s for atention. 

-- 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

[sqlite] Crash Calling sqlite3_vfs_unregister

[Israel Lins Albuquerque]

if I call sqlite3_vfs_unregister without initialize the sqlite I have a crash. 
Or if i execute the pseudo-code above too: 
sqlite3_vfs_register(vfs); 
db = sqlite3_open(..., "vfs"); 
sqlite3_close(db); 
sqlite3_vfs_unregister(vfs); 

I'm talking about 3.6.22 version. 

I have maked a patch for this, see file attached. 

-- 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

[sqlite] Crash Calling sqlite3_vfs_unregister

[Israel Lins Albuquerque]

De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> 
Para: "sqlite-users-bounces" <sqlite-users-boun...@sqlite.org> 
Enviadas: Quarta-feira, 20 de Janeiro de 2010 16:48:52 (GMT-0200) Auto-Detected 
Assunto: Crash Calling sqlite3_vfs_unregister 


if I call sqlite3_vfs_unregister without initialize the sqlite I have a crash. 
Or if i execute the pseudo-code above too: 
sqlite3_vfs_register(vfs); 
db = sqlite3_open(..., "vfs"); 
sqlite3_close(db); 
sqlite3_vfs_unregister(vfs); 

I'm talking about 3.6.22 version. 

I have maked a patch for this, see file attached. 

-- 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 




-- 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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