[sqlite] Trigger to another database

[SQlite Sqlite]

Hello,
I have two sqlite databases. (db1, db2)
I try to write a trigger in db1 which inserts data to a table in db2.
In the sqlite docu there is a section about "Temp Triggers".
 
"...Except, it is possible to create a TEMP TRIGGER on a table in another 
database."

CREATE TEMP TRIGGER ex1 AFTER INSERT ON main.tab1 BEGIN ...

I tried this but I get always the error
SQL Error: qualified table names are not allowed on INSERT, UPDATE, and DELETE 
statements within triggers
 
If this is not allowed within a trigger, is there a workournd to synchronize 
two tables between different databases?
 
regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] running testsuite

[sqlite]

Hello. 

I am a newbie both to sqlite and tcl.  I would like to learn how to run the 
test suite, so that later, when I start modifying the source code (e.g. to 
make a customized subset), I can verify that I have not broken anything. 

Is there a document somewhere that describes how to run the test suite?  Or 
can someone describe how they have run the testsuite with the latest version 
on a Windows [XP] machine? 

Thank you! 


 - sam -
 [EMAIL PROTECTED]

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

Re: [sqlite] Introducing... ManagedSQLite

[sqlite]

Hello. 

Thanks for doing this.  I have two questions: 

1.  is there sample code that uses the library in your svn?  [i just scanned 
quickly, but there didn't seem to be] 

2.  do you have plans to port the test suite so they can be run against this 
library? 

Thanks! 

 - sam - 

WHITE, DANIEL writes: 

Howdy all! 


I am just writing tonight to let you know that a project of mine has
opened up to being open source -- ManagedSQLite.  It is a light wrapper
around SQLite 3.4.0 that was originally written by Rob Groves.  I have
added support for Unicode to his wrapper, then added my Managed (.NET)
port to the mix.  One DLL file unlike others out there.  Supports FTS1
and FTS2 out of the box!!! 


I am looking for help with this little project.  I think we could
eventually make an entire ADO.NET wrapper with this thing.  Thanks. 

http://code.google.com/p/managed-sqlite/ 

Thanks for reading. 

Daniel A. White 
{ Kent State University: Computer Science major }

{ JMC TechHelp: Taylor Hall, server techie }
{ E-mail: [EMAIL PROTECTED] }
{ Colossians 3:17 } 



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

[sqlite] SQLITE3_OPEN returns SQLITE_NOMEM

[sqlite]

Dear All,

I have just started using the SQLite Db in my applications. My application
runs under MIPS processor. I have generated the Sqlite3.dll and Sqlite3.lib
file for the MIPS processor and it is geting compiled. When i use
Sqlit3_Open function to open a DB, it fails with the error message
SQLITE_NOMEM. But the same code is running fine in the Windows mode. How
should i rectify this? Can anyone help me in this regard.

Thanks
Kartthi
-- 
View this message in context: 
http://www.nabble.com/SQLITE3_OPEN-returns-SQLITE_NOMEM-tp16254109p16254109.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Moving Backwards in SQLite

[sqlite]

Dear All,

i have developed an application using SQLite which runs in the pocket pc
device. My application has to show some messages in a list box (custom
build) while moving forward im able to move the records one by one in the
recordset. But when i move upwards im not able to move to the corresponding
records i have to reset the recordset pointer to the initial position and
then have to skip the records until i reach the desired record. is there
anyway to skip directly to the desired record. Or even im not sure whether
our workaround to move backwards is optimistic. if some one could help me in
this regard, if could share some code for moving backwards it would be more
helpful to me.


Regards,
Karthi

-- 
View this message in context: 
http://www.nabble.com/Moving-Backwards-in-SQLite-tp17419487p17419487.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLite Step Function

[sqlite]

Dear Stephen,

Thanks for the reply. As you said we checked the EXPLAIN QUERY PLAN with our
query and it has shown that all the four tables we use in the query are
using their indexes and there is no ORDER BY class in our query. So
sqlite3_prepare compiles the query and sqlite3_step executes the query does
it mean the execution time for our query is 40 secs because we are
retrieving the records soon once gets executed.

Regards
Kartthi

Stephen Oberholtzer wrote:
> 
> On Tue, May 27, 2008 at 9:06 AM, sqlite <[EMAIL PROTECTED]>
> wrote:
> 
>>
>> Dear All,
>>
>> We are using SQLite for our application development which would be
>> deployed
>> in a pocket pc. Here we are using a query which has three Inner joins
>> ,while
>> using sqlite3_prepare statement  we can able to prepare the  records soon
>> where as in sqilte3_step function we are facing a problem to fetch first
>> record which makes more time, it takes around 40 seconds to get the fetch
>> the first record whereas all other records are fetched quickly with in a
>> fraction of second. We facing similar kind of problem each time while
>> getting first record using Where condition or inner joins, kindly help us
>> in
>> this regard.
>>
>> Thanks in Advance,
>>
>> Regards,
>> Kartthi
> 
> 
> With no information as to how your database is being formed, I would start
> with:
> 
> 0.  "sqlite3_prepare" does not prepare the data, it just prepares the
> program that will be used to fetch the data.
> 1.   Try EXPLAIN QUERY PLAN [your select statement here] and see what it
> says.  If any of the joins are *not* using an index, that would be a
> problem.
> 2.  Are you using an ORDER BY?  That would mean SQLite has to process the
> entire query (in order to sort the results) before returning the first
> row.
> 
> 
> -- 
> -- Stevie-O
> Real programmers use COPY CON PROGRAM.EXE
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/SQLite-Step-Function-tp17490036p17505065.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Move rowset pointer to Initial record

[sqlite]

Dear All,

 We are using SQLite for our application development which would be deployed
in a pocket pc.Here we are using a query which has three Inner joins and
using sqlite3_prepare and sqilte3_step function to prepare the records and
to fetch the records.By executeing the Query and we moves to certain records
say up to 10 records,while going upwards how to move to the initial record
without reexecuting the Query and preparing the rowset again,

kindly help us in this regard.

Thanks in Advance,

Regards,
kartthi.
-- 
View this message in context: 
http://www.nabble.com/Move-rowset-pointer-to-Initial-record-tp17509266p17509266.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Move rowset pointer to Initial record

[sqlite]

Igor Tandetnik wrote:
> 
> "sqlite" <[EMAIL PROTECTED]> wrote
> in message news:[EMAIL PROTECTED]
>> We are using SQLite for our application development which would be
>> deployed in a pocket pc.Here we are using a query which has three
>> Inner joins and using sqlite3_prepare and sqilte3_step function to
>> prepare the records and to fetch the records.By executeing the Query
>> and we moves to certain records say up to 10 records,while going
>> upwards how to move to the initial record without reexecuting the
>> Query and preparing the rowset again,
> 
> You can call sqlite3_reset, after which the next sqlite3_step would 
> restart from the first row.
> 
> Igor Tandetnik 
> 
> Dear Igor 
> 
> Thanks for your reply, we already tried this method but by executing the
> sqlite_reset function it will again prepare the rowset, which takes more
> time for us to get the initial record, is there any other way to get the
> initial record without reseting the rowset where takes less time to get
> the initial record.
> 
> Regards,
> kartthi
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Move-rowset-pointer-to-Initial-record-tp17509266p17511237.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Query Execution in SQLite

[sqlite]

Dear All,

  We are using SQLite for our application development which would be
deployed in a pocket pc.Here we are using a inner join query which takes
different time during different executions, the query which we using in our
application is

 "SELECT DISTINCT MT.PcNo, MT.SubPcNo, MT.BrandNo, MT.BrandDescription,
MT.ST, MT.TS FROM Brand MT INNER JOIN ProdSubPcControlLink PSCL ON  MT.PcNo
= PSCL.PcNo AND MT.SubPcNo = PSCL.SubPcNo INNER JOIN ShopControlLink SCL ON
SCL.TripCode = PSCL.TripCode AND SCL.AuditClassNo = PSCL.AuditClassNo INNER
JOIN OptimumControlLink OCL ON OCL.TripCode = SCL.TripCode AND
OCL.AuditClassNo = SCL.AuditClassNo AND OCL.ShopSetCode = SCL.ShopSetCode
AND OCL.PcSetCode = PSCL.PcSetCode WHERE PSCL.TripCode = 119  AND
PSCL.AuditClassNo = 1 AND SCL.ShopCode = 26  LIMIT 200"

Kindly let us know why this kind of behaviour is happening to solve this
issue.

Regards,
kartthi
-- 
View this message in context: 
http://www.nabble.com/Query-Execution-in-SQLite-tp17748185p17748185.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Query Execution in SQLite

[sqlite]

Igor Tandetnik wrote:
> 
> "sqlite" <[EMAIL PROTECTED]> wrote
> in message news:[EMAIL PROTECTED]
>>  We are using SQLite for our application development which would be
>> deployed in a pocket pc.Here we are using a inner join query which
>> takes different time during different executions
> 
> What exactly does this mean? Are you running the exact same query 
> several times (if so, why?), or similar queries with different 
> parameters? How much different is the time between runs? Why is this a 
> problem in the first place?
> 
> Igor Tandetnik 
> 
> Thanks for your reply, yes we are running the same query with the same
> parametes only. The execution time gets differed for us when we tested for
> the two different times so we tested the application with the same
> location where the query gets called and found that the different
> execution takes different time. and the execution time varies between 38
> secs for one time and 54 secs for the second time. Yes this problem is
> related to the performance so we are concerned about this.
> 
> Regards,
> kartthi.
> 
> 
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Query-Execution-in-SQLite-tp17748185p17754963.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] sqlite3.lib for ARMV4T processor

[sqlite]

Dear All,

  We are using SQLite for our application development which would be
deployed in a pocket pc of type DTX 10 processor ARMV4T.Also we have
generated the lib file specific for ARM processor using the LIB.exe with the
parameters DEF:sqlite3.def MACHINE:ARM and the sqlite3.lib and sqlite3.dll
files are generated. When we compiled the application with the sqlite
functions like sqlite3_open() the application gets compiled and the exe also
gets generated with out any errors. But when we run the application in the
DTX10 device it throws an error "Application is not a valid WINCE
application" but when we remove the function call sqlite3_open() from the
application and run it in the device it is running without any errors. Have
we generated the sqlite3.lib and sqlite3.dll files correctly? because we
have mentioned the machine name as ARM and using the sqlite3.lib file for
ARMV4T processor, is it correct? if not how to generate the sqlite3.lib file
for the processor ARMV4T, kindly help us in this regard. Or if possible
provide us the sqlite3.lib file meant for ARMV4T processor.

Thanks
Kartthikeyan
-- 
View this message in context: 
http://www.nabble.com/sqlite3.lib-for-ARMV4T-processor-tp17798977p17798977.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite3.lib for ARMV4T processor

[sqlite]

Dear Virgilio,

 Thanks for your Quick reply, we dont know how to enable the funtional level
linking using SQLite with eVC, so kindly tell us how to enable the same.

Thanks & Regards,
kartthikeyan


Virgilio Alexandre Fornazin-2 wrote:
> 
> IF you are using SQLite with eVC, you must enabled function level linking,
> because
> ARM linkers had a bug before eVC SP4 that generated corrupt image files.
> 
> On Thu, Jun 12, 2008 at 9:39 AM, sqlite <[EMAIL PROTECTED]>
> wrote:
> 
>>
>> Dear All,
>>
>>  We are using SQLite for our application development which would be
>> deployed in a pocket pc of type DTX 10 processor ARMV4T.Also we have
>> generated the lib file specific for ARM processor using the LIB.exe with
>> the
>> parameters DEF:sqlite3.def MACHINE:ARM and the sqlite3.lib and
>> sqlite3.dll
>> files are generated. When we compiled the application with the sqlite
>> functions like sqlite3_open() the application gets compiled and the exe
>> also
>> gets generated with out any errors. But when we run the application in
>> the
>> DTX10 device it throws an error "Application is not a valid WINCE
>> application" but when we remove the function call sqlite3_open() from the
>> application and run it in the device it is running without any errors.
>> Have
>> we generated the sqlite3.lib and sqlite3.dll files correctly? because we
>> have mentioned the machine name as ARM and using the sqlite3.lib file for
>> ARMV4T processor, is it correct? if not how to generate the sqlite3.lib
>> file
>> for the processor ARMV4T, kindly help us in this regard. Or if possible
>> provide us the sqlite3.lib file meant for ARMV4T processor.
>>
>> Thanks
>> Kartthikeyan
>> --
>> View this message in context:
>> http://www.nabble.com/sqlite3.lib-for-ARMV4T-processor-tp17798977p17798977.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/sqlite3.lib-for-ARMV4T-processor-tp17798977p17815078.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite3.lib for ARMV4T processor

[sqlite]

Dear Virgilio,

 We enabled the functional level linking using SQLIte, but we are getting
the "unresolved exteral symbol sqlite3_open referenced in function" error
which means the sqlite3.lib file we have generated is not valid? if so
kindly help us to generate the lib file for ARMV4T processor.

Thanks & Regards,
kartthikeyan.s


sqlite wrote:
> 
> Dear Virgilio,
> 
>  Thanks for your Quick reply, we dont know how to enable the funtional
> level linking using SQLite with eVC, so kindly tell us how to enable the
> same.
> 
> Thanks & Regards,
> kartthikeyan
> 
> 
> Virgilio Alexandre Fornazin-2 wrote:
>> 
>> IF you are using SQLite with eVC, you must enabled function level
>> linking,
>> because
>> ARM linkers had a bug before eVC SP4 that generated corrupt image files.
>> 
>> On Thu, Jun 12, 2008 at 9:39 AM, sqlite <[EMAIL PROTECTED]>
>> wrote:
>> 
>>>
>>> Dear All,
>>>
>>>  We are using SQLite for our application development which would be
>>> deployed in a pocket pc of type DTX 10 processor ARMV4T.Also we have
>>> generated the lib file specific for ARM processor using the LIB.exe with
>>> the
>>> parameters DEF:sqlite3.def MACHINE:ARM and the sqlite3.lib and
>>> sqlite3.dll
>>> files are generated. When we compiled the application with the sqlite
>>> functions like sqlite3_open() the application gets compiled and the exe
>>> also
>>> gets generated with out any errors. But when we run the application in
>>> the
>>> DTX10 device it throws an error "Application is not a valid WINCE
>>> application" but when we remove the function call sqlite3_open() from
>>> the
>>> application and run it in the device it is running without any errors.
>>> Have
>>> we generated the sqlite3.lib and sqlite3.dll files correctly? because we
>>> have mentioned the machine name as ARM and using the sqlite3.lib file
>>> for
>>> ARMV4T processor, is it correct? if not how to generate the sqlite3.lib
>>> file
>>> for the processor ARMV4T, kindly help us in this regard. Or if possible
>>> provide us the sqlite3.lib file meant for ARMV4T processor.
>>>
>>> Thanks
>>> Kartthikeyan
>>> --
>>> View this message in context:
>>> http://www.nabble.com/sqlite3.lib-for-ARMV4T-processor-tp17798977p17798977.html
>>> Sent from the SQLite mailing list archive at Nabble.com.
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/sqlite3.lib-for-ARMV4T-processor-tp17798977p17815345.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite3.lib for ARMV4T processor

[sqlite]

Dear Virgilio,

 We enabled the functional level linking using SQLIte, but we are getting
the "unresolved exteral symbol sqlite3_open referenced in function" error
which means the sqlite3.lib file we have generated is not valid? if so
kindly help us to generate the lib file for ARMV4T processor.

Thanks & Regards,
kartthikeyan.s


Virgilio Alexandre Fornazin-2 wrote:
> 
> IF you are using SQLite with eVC, you must enabled function level linking,
> because
> ARM linkers had a bug before eVC SP4 that generated corrupt image files.
> 
> On Thu, Jun 12, 2008 at 9:39 AM, sqlite <[EMAIL PROTECTED]>
> wrote:
> 
>>
>> Dear All,
>>
>>  We are using SQLite for our application development which would be
>> deployed in a pocket pc of type DTX 10 processor ARMV4T.Also we have
>> generated the lib file specific for ARM processor using the LIB.exe with
>> the
>> parameters DEF:sqlite3.def MACHINE:ARM and the sqlite3.lib and
>> sqlite3.dll
>> files are generated. When we compiled the application with the sqlite
>> functions like sqlite3_open() the application gets compiled and the exe
>> also
>> gets generated with out any errors. But when we run the application in
>> the
>> DTX10 device it throws an error "Application is not a valid WINCE
>> application" but when we remove the function call sqlite3_open() from the
>> application and run it in the device it is running without any errors.
>> Have
>> we generated the sqlite3.lib and sqlite3.dll files correctly? because we
>> have mentioned the machine name as ARM and using the sqlite3.lib file for
>> ARMV4T processor, is it correct? if not how to generate the sqlite3.lib
>> file
>> for the processor ARMV4T, kindly help us in this regard. Or if possible
>> provide us the sqlite3.lib file meant for ARMV4T processor.
>>
>> Thanks
>> Kartthikeyan
>> --
>> View this message in context:
>> http://www.nabble.com/sqlite3.lib-for-ARMV4T-processor-tp17798977p17798977.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/sqlite3.lib-for-ARMV4T-processor-tp17798977p17815435.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite3.lib for ARMV4T processor

[sqlite]

Dear Virgilio 

  We now solved the problem of creating the sqlite3.lib file specific for
ARMV4T processor, and able to run our application using the sqlite3.lib file
that we generated for ARMV4T processor, thanks for your reply.

Regards,
kartthikeyan.s





Virgilio Alexandre Fornazin-2 wrote:
> 
> IF you are using SQLite with eVC, you must enabled function level linking,
> because
> ARM linkers had a bug before eVC SP4 that generated corrupt image files.
> 
> On Thu, Jun 12, 2008 at 9:39 AM, sqlite <[EMAIL PROTECTED]>
> wrote:
> 
>>
>> Dear All,
>>
>>  We are using SQLite for our application development which would be
>> deployed in a pocket pc of type DTX 10 processor ARMV4T.Also we have
>> generated the lib file specific for ARM processor using the LIB.exe with
>> the
>> parameters DEF:sqlite3.def MACHINE:ARM and the sqlite3.lib and
>> sqlite3.dll
>> files are generated. When we compiled the application with the sqlite
>> functions like sqlite3_open() the application gets compiled and the exe
>> also
>> gets generated with out any errors. But when we run the application in
>> the
>> DTX10 device it throws an error "Application is not a valid WINCE
>> application" but when we remove the function call sqlite3_open() from the
>> application and run it in the device it is running without any errors.
>> Have
>> we generated the sqlite3.lib and sqlite3.dll files correctly? because we
>> have mentioned the machine name as ARM and using the sqlite3.lib file for
>> ARMV4T processor, is it correct? if not how to generate the sqlite3.lib
>> file
>> for the processor ARMV4T, kindly help us in this regard. Or if possible
>> provide us the sqlite3.lib file meant for ARMV4T processor.
>>
>> Thanks
>> Kartthikeyan
>> --
>> View this message in context:
>> http://www.nabble.com/sqlite3.lib-for-ARMV4T-processor-tp17798977p17798977.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/sqlite3.lib-for-ARMV4T-processor-tp17798977p17816781.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] sqlite-3.3.4 and extra float decimals

[sqlite]

Hello,

When using sqlite-3.3.4 with windows I get the following strange behaviour.

create table Muppet (Kermit float);
insert into Muppet values (100);
select * from Muppet;
100.0

As you see it returns 100.0 instead of only 100. This happens in windows
xp but not in win ce.

Anyone know how to fix this?

Thanks,
  Floppe

ps. it works with older versions but I don't want to downgrade.

[sqlite] How can I get the type of a column?

[sqlite]

I want to know the type of a column, ie.:

INTEGER, TEXT, REAL or BLOB

There seems to be no function to do this.


I can get the "declared type" of a column but
that's not easy to decipher as SQL has a zillion
data types.

I tried "sqlite3_column_type()" but that function
only works when there's a valid row (and when there's
a valid row it fails when a column is empty!)

Is there any way to get the column type?

Re: [sqlite] How can I get the type of a column?

[sqlite]

Eric Scouten wrote:
No such thing, really. In SQLite the data types are associated with the 
cell, not the column.




Yes, I saw that. I'm not sure it's a good "feature".

The "declared type" of a column that you've found is used to establish 
preferences for how cells are stored, but it is not a hard requirement 
(unlike most other SQL implementations).




Internally SQLite seems to have a column "affinity".
That's what I need...

Re: [sqlite] How can I get the type of a column?

[sqlite]

Eric Scouten wrote:

SQLite derives that by parsing the string that you've already found.


I found the method in section 2.1 of this page:
http://www.sqlite.org/datatype3.html


> I think it does store that in some internal fashion, so
> it's not *re-parsing* it constantly, but that is not
> available through the API.
>

Pity. I guess I'll have to parse it myself...

Re: [sqlite] How can I get the type of a column?

[sqlite]

[EMAIL PROTECTED] wrote:

sqlite <[EMAIL PROTECTED]> wrote:

Eric Scouten wrote:

SQLite derives that by parsing the string that you've already found.

I found the method in section 2.1 of this page:
http://www.sqlite.org/datatype3.html


 > I think it does store that in some internal fashion, so
 > it's not *re-parsing* it constantly, but that is not
 > available through the API.
 >

Pity. I guess I'll have to parse it myself...



I continue to be bewildered by programmers'
fixation on datatypes.


We're C++ programmers and C++ is all about data types.
It's a statically typed language.


This has been a constant
theme for 6 years now.


I'm sure it has...   :-)


And in all that time, I
have never been able to figure out why so many
people think they need to know the "type" of a
"column".



Ummm...perhaps it's because when you create a
column you give it a type. It's only natural
to believe that the type might actually be
used for something...


The best theory I have is that people who have
always driven a stick shift must have difficulty
driving a car with an automatic transmission.



Yep. We spend a week stomping on the huge American
brake pedal every time we reach a traffic light and
want to de-clutch.

Re: [sqlite] How can I get the type of a column?

[sqlite]

Eric Scouten wrote:

On 10 May 2006, at 16:31, sqlite wrote:


Eric Scouten wrote:
No such thing, really. In SQLite the data types are associated with 
the cell, not the column.


Yes, I saw that. I'm not sure it's a good "feature".


Depends on your application. For us, it's been a very natural fit. It 
may not fit your data or coding style well; if so, then you may want to 
consider other DB engines.




It's like "dynamic typing" vs. "static typing"
in your computer language. Both have advantages...

Whatever, I've reworked the code to associate
the "type" with the data value instead of the
column and it's all working now.

I'm writing a wrapper and I figure it will be
easier to make other database engines work
the SQLite way than to make SQLite work the
other way around.

Re: [sqlite] Re: - Re: [sqlite] How can I get the type of a column?

[sqlite]

[EMAIL PROTECTED] wrote:

As you mention, this is a constant point of discussion on this board.


To me this would indicate a problem.


Perhaps 'Version 3 Data Types' should be given more prominence under
'Documentation'. Alternatively, perhaps the subject should be touched upon
briefly in 'SQLite in 5 Minutes Or Less'.



I think real problem is that you have a function
called "column_type" which doesn't actually return
the type of a column. This is counter-intutive.

If you could make it return the column "affinity"
between the call to prepare() and the first call
to step() then the problem would probably go away.

My $0.02...

Re: [sqlite] How can I get the type of a column?

[sqlite]

Roger Binns wrote:


The types point still baffles me.  If your code already
knows which column it is dealing with then surely the
code should know what type to expect.  (Eg if you are
dealing with a column named 'title' then you would
expect a string)



Let me explain what I was doing...

I was making a C++ wrapper for a generic "SQL query",
thinking that I could map it to various database engines
as needed in the future.

The results of the query were being returned in two parts,
a list of columns and the results themselves as an array.

In the list of columns it seemed natural to include the
type of the column. That doesn't seem too weird to me.

As this isn't possible I changed it so the data type
is in the result array, not the columns. The column
info is now reduced to "name" and "declared type".

PS: I'm also quite new to SQL and was under the illusion
that data types would be standardized. After checking
a couple of them I see this isn't so, maybe SQLite's
approach is more sensible.

> Do people using your code go around
> randomly changing the database
> schema and the values stored without changing the
> corresponding C code?
>

Noted. If you don't know what's in the table then
you shouldn't be writing to it.

If you're only reading the table then it makes more
sense to have the type on a per-entry basis (even
if it's only so you so you can have a "null" value).

Re: [sqlite] How can I get the type of a column?

[sqlite]

John Stanton wrote:

As you postulated, Sqlite's approach is indeed more rational.



Most SQL implementations use fixed-size records
so it makes more sense for them to enforce each
column's data type exactly - inserting a string
into a column which can only hold a single char
isn't very useful.

SQLite's flexibility makes life much easier. I
now see it as A Good Thing. Switching to a SQL
implementation without it must be quite traumatic.


As for what you are doing, the way I did a similar thing for 
compatibility was to make a function which looks at the declared type 
and the actual type and makes the appropriate conversion if necessary to 
match the destination requirements.




Seeing as I'm designing a wrapper it makes no difference
- I simply moved the type info from the column list into
the results.

If I ever support (eg.)MySQL I figure it won't be much of
a problem to emulate this behavior.

Re: [sqlite] Re: - Re: [sqlite] How can I get the type of a column?

[sqlite]

Joe Wilson wrote:

I also agree with Ralf's proposal for sqlite3_column_affinity().
(Not that a vote on this topic will likely make a difference. ;-)



I don't think you need a new function, just make
the existing one do the obvious thing right after
you call "prepare".




--- Ralf Junker <[EMAIL PROTECTED]> wrote:

I second this.


I think real problem is that you have a function
called "column_type" which doesn't actually return
the type of a column. This is counter-intutive.

A more telling name for sqlite3_column_type would probably be sqlite3_cell_type.


If you could make it return the column "affinity"
between the call to prepare() and the first call
to step() then the problem would probably go away.

Yes, some function like sqlite3_column_affinity would indeed be nice to have. 
Just for the sake
of the wrapper writers which try to link sqlite3 to database concepts which 
required fixed
datatypes. It should be available right aftercalling sqlite3_prepare.

Just another 2 cent ...

Ralf 



__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

[sqlite] Recommended method of atomically inserting if data is not present

[sqlite]

I'm working with an application that keeps a list of clients:

  CREATE TABLE clients (
id integer primary key,
fingerprint varchar (40) unique,
...
  );

Clients are uniquely identified by fingerprint but are referenced
by an integer id in most places in the database.

Clients can be referred to by one or more names and so there's
another table:

  CREATE TABLE client_names (
id integer,
name text
  );

Names aren't unique. Two clients can have the same name.

Essentially what I'm trying to do is atomically add a new
name for a client but only if a given id isn't
already associated with that name. In other words, "do both
the given client id and name appear in the same row anywhere
in the client_names table?". Can't make the 'name' column
'unique' as two clients may have the same name. Can't make
the 'id' column 'unique' as a client may have more than one
name...

What is the recommended way to do this with SQLite?

PS: I'm not wrong in thinking that IF EXISTS (...) THEN ...
isn't implemented, am I? I couldn't get the sqlite3 interpreter
to accept any statements of that form ("syntax error near IF").
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recommended method of atomically inserting if data is not present

[sqlite]

On 20081118 15:25:32, MikeW wrote:
>  <[EMAIL PROTECTED]> writes:
> 
>  Can't make the 'name' column
> > 'unique' as two clients may have the same name. Can't make
> > the 'id' column 'unique' as a client may have more than one
> > name...
> 
> However you can specify that the name/id pair is unique ...
> PRIMARY KEY (name, id)
> 
> Regards,
> MikeW

Excellent!

Thanks, I didn't know that...

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

Re: [sqlite] SQLite performance woe

[sqlite]

All:

For comparison I tried several combinations of query orders and indices.
 I found both indices and the join clause sequence make significant
differences in execution time.  Using SQLiteSpy with SQLite v3.6.1 on
Windows XP.

I have two tables:

GPFB with 34830 rows, 10 columns with a 4-column primary key
SET_1 with 320 rows, 10 columns with a 2-column primary key

Indices added on two columns (GID,EID) common to both tables:

create index idx_gpfb_gid_eid on GPFB(GID,EID);
create index idx_set1_gid_eid on SET_1(GID,EID)

(The combination of GID and EID are not unique in either of the tables.)

My basic query:

select SETID,SID,CUT,X,sum(t1*Kx) as Px,sum(t2*Ky) as Py,sum(t3*Kz) as
Pz,sum(R2*Ky+T1*Kx*Z) as My 
from  GPFB join SET_1
where GPFB.GID=SET_1.GID and GPFB.EID=SET_1.EID 
group by SETID,SID,CUT 
order by SETID,SID,CUT;

I also executed the query reversing the join clause to "from SET_1 join
GPFB".

800 rows were returned:

"from GPFB join Set_1" with no indices: 3.3 seconds
"from GPFB join Set_1" with indices: 109.7 ms
"from SET_1 join GPFB" with no indices: 5.5 seconds
"from SET_1 join GPFB" with indices: 55.9 ms

In this example, EXPLAIN QUERY PLAN seems to indicate only the joined
table index is used.  There was no significant time delta in either
query if the from table index was dropped.

Russ Royal

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel
Sent: Tuesday, December 02, 2008 5:03 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite performance woe

Hello Donald & Others,

I have primary keys set for each of the table but no indicies (that I am
aware of) as I simply converted the data from our existing database
system which does not support indicies.  As my current system only
implements primary keys I have no real experience dealing with indicies,
are they like some sort of extra key column?  Are there any guides to
optimising SQLite performance with indicies?  

I tried EXPLAIN QUERY PLAN for the following:
"SELECT * FROM test_item INNER JOIN test_container ON
test_item.container_code = test_container.container_code"

The output was:
0|0|TABLE test_item
1|1|TABLE test_container

Is there a guide I can check for understanding this output?

Daniel

-

Daniel,

I don't know the sizes of your tables nor the cardinality of your joined
items (i.e., how much variation in the values), but you might try
creating creating an index or two, especially on the larger table, e.g.:
CREATE INDEX idx_ti_ccode ON test_item(container_code); 
CREATE INDEX idx_ti_ccode ON test_container(container_code);

Then run the EXPLAIN QUERY PLAN again and see if one of the indices is
mentioned.

It might even help a little to VACUUM the database afterwards.

If the rows are rather large (i.e. if your avg row is measure in Kbytes
rather than bytes) then be sure "container_code" is one of the first
columns in each table.

Note that when benchmarking, your first run may be markedly slower than
repeated runs due to caching.

Is this running on a workstation/laptop/pc type of machine, or some
embedded gizmo with limited resources?

Let us know the results.

Donald 

___
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] Run-Time Check Failure

[sqlite]

Hello all,

I'm compiling sqlite 3.6.13 with Visual 2003, and RunTime Check 
activated, and it gives this error :

Run-Time Check Failure #1 -
A cast to a smaller data type has caused a loss of data.  If this was
intentional, you should mask the source of the cast with the appropriate
bitmask.  For example:

char c = (i & 0xFF);

Changing the code in this way will not affect the quality of the
resulting optimized code.

Is it a known bug? Can I send more detailed informations (call stack, 
source code) on this list, or on dev list?

Thanks,

Gérald

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

Re: [sqlite] Run-Time Check Failure

[sqlite]

> This is probably not a bug.  There are places in the SQLite code where  
> we deliberately discard all but the lower 8 bits of an integer.  But,  
> if you like to tell us *where* in the code this occurs, I'll be happy  
> to verify it for you.


In sqlite3.c big file, it's in static u8 randomByte(void) function, on 
line 16707 :

   wsdPrng.j += wsdPrng.s[i] + k[i];

wsdPrng.j = 246, and wsdPrng.s[i] + k[i] = 28, so adding it will be more 
than 255. If it's deliberate, a bitmask 0xFF would solve the problem.

Gérald

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

Re: [sqlite] Run-Time Check Failure

[sqlite]

D. Richard Hipp a écrit :

> This is not error in the SQLite code.  The code here is correct.  The  
> bug is in your compiler.

Sorry but I don't agree at all.

> Adding a work-around so that this will work in your compiler makes the  
> code rather more complicated:
> 
>  wsdPrng.j = (wsdPrng.j + wsdPrng.s[i] + k[i]) & 0xff;
> 
> I am opposed to obfuscating the code in this way because of your  
> compiler bug.  Is there some command-line option or something on your  
> compiler that can turn off the silly overflow check?

This makes code clearer.


Adding unsigned char with value that exceeds maximum value (255) is a 
potential bug. Compiler doesn't know if it's done deliberately or not, 
and neither other people that read the code.

So this option in compiler is useful to detect some bugs.

If you say it's not one, that's fine, I'll add bit masking in my 
version, I just needed to know that.

Thanks for help,

Gérald
_______
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG - dereferencing type-punned pointer in os_win.c

[sqlite]

Ok it's not really a bug; it's just a compiler warning.  I get this warning
when compiling the amalgamation:

 

gcc -Os -Wall -DFOSSIL_I18N=0 -L/mingw/lib -I/mingw/include  -I. -I./src
-DSQLITE_OMIT_LOAD_EXTENSION=1 -DSQLITE_THREADSAFE=0
-DSQLITE_DEFAULT_FILE_FORMAT=4 -Dlocaltime=fossil_localtime -c
./src/sqlite3.c -o sqlite3.o

./src/sqlite3.c: In function `getLastErrorMsg':

./src/sqlite3.c:28450: warning: dereferencing type-punned pointer will break
strict-aliasing rules

 

I think the change set below resolves the warning without introducing
another bug.  I could use a code review.

 

PS C:\rev\src\sqlite3\src> fossil info

project-name: SQLite

repository:   c:\rev\fossil\sqlite3.f

local-root:   C:/rev/src/sqlite3/

user-home:  : C:/Users/rev/AppData/Local

project-code: 2ab58778c2967968b94284e989e43dc11791f548

server-code:  2fa7c8b2762294d28396292f74c7b94c9c50af75

checkout: a2b1183d9e9898d06d623b342bbb552e85a9b3f6 2010-01-11 12:00:48
UTC

parent:   14dc46a74aafe44c0bf7dffd26268395b2c5edb2 2010-01-09 07:33:54
UTC

tags: trunk

PS C:\rev\src\sqlite3\src> fossil diff os_win.c

--- os_win.c

+++ os_win.c

@@ -1253,25 +1253,29 @@

   ** buffer, excluding the terminating null char.

   */

   DWORD error = GetLastError();

   DWORD dwLen = 0;

   char *zOut = 0;

+  union {

+WCHAR** pzwc;

+LPWSTR lpws;

+  } wu;

 

   if( isNT() ){

-WCHAR *zTempWide = NULL;

+*wu.pzwc = NULL;

 dwLen = FormatMessageW(FORMAT_MESSAGE_ALLOCATE_BUFFER |
FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS,

NULL,

error,

0,

-   (LPWSTR) &zTempWide,

+   wu.lpws,

0,

0);

 if( dwLen > 0 ){

   /* allocate a buffer and convert to UTF8 */

-  zOut = unicodeToUtf8(zTempWide);

+  zOut = unicodeToUtf8(*wu.pzwc);

   /* free the system buffer allocated by FormatMessage */

-  LocalFree(zTempWide);

+  LocalFree(*wu.pzwc);

 }

 /* isNT() is 1 if SQLITE_OS_WINCE==1, so this else is never executed.

 ** Since the ASCII version of these Windows API do not exist for WINCE,

 ** it's important to not reference them for WINCE builds.

 */

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

[sqlite] Binding binary data in 3.0.4

[SQLite]

The docs for sqlite3_bind_blob() states that passing in SQLITE_STATIC
mean that the blob data will be around until "SQLite has finished with
it." But when exactly is this? For an INSERT/UPDATE, is it when the
statement is executed, or the current transaction commmited, or some
other time?

Re: [sqlite] Idea: defining table-valued functions directly in SQL

[sqlite]

I have some of my own ideas about this.

* Perhaps move PARAMETERS before AS, which may make the syntax easier.

* You don't need computed columns in tables; use views instead. You can index 
computed values though.

* I do agree that defining table-valued functions in these way can be useful 
though; I have wanted to define views that take parameters before, and was 
unable to.

* Another (separate) idea can be "CREATE FUNCTION name(args) AS select_stmt;" 
to define your own function. If you write "CREATE AGGREGATE FUNCTION" then the 
function name can be used as a table name within the select_stmt. Both of these 
are separate from table-valued functions (parameterized views) though.
_______
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mistake in documentation about xCreate/xConnect vtab methods

[sqlite]

The documentation for the xCreate and xConnect methods for virtual tables give 
the incorrect type.
It says "char**argv" but the actual type should be "const char*const*argv".
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feature request: A function to read the value of db->u1.isInterrupted

[sqlite]

Feature request: A function to read the value of db->u1.isInterrupted
The purpose of this is so that extensions that implement additional SQL 
functions and/or virtual tables that use loops that aren't VDBE programs can 
still know that it is interrupted.
For example, if the extension uses libcurl then the progress callback can use 
this to know when to stop due to interruption. For example it might use:

int progress_callback(void *clientp,   curl_off_t dltotal,   curl_off_t dlnow,  
 curl_off_t ultotal,  curl_off_t ulnow) {
  return sqlite3_interrupted(clientp);
}

Implementing the sqlite3_interrupted() function (or whatever you want to call 
it) should be very easy to implement. However, it must be added into the 
extension loading mechanism, so if I do it by myself then it will be 
incompatible.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Segfault when authorizer denies some steps of creating a WITHOUT ROWID table

[sqlite]

#if 0
gcc -s -O2 -o ./sqltest1 sqltest1.c sqlite3.o -ldl -lpthread
exit
#endif

/*
  Test with the command:
./sqltest1 2 'create table vt(a integer primary key,b,c) without rowid;'
  It segfaults. If the first argument is 3 or 4 it also segfaults.
*/

#include 
#include 
#include 
#include "sqlite3.h"

static int count=-1;
static sqlite3*db;

static int xAuth(void*aux,int act,const char*p3,const char*p4,const 
char*p5,const char*p6) {
  fprintf(stderr,"%d: %d %s %s %s %s\n",count,act,p3,p4,p5,p6);
  return count--?SQLITE_OK:SQLITE_DENY;
}

int main(int argc,char**argv) {
  if(argc!=3) return 1;
  if(sqlite3_open(":memory:",&db)) return 1;
  count=strtol(argv[1],0,0);
  sqlite3_set_authorizer(db,xAuth,0);
  printf("%d\n",sqlite3_exec(db,argv[2],0,0,0));
  return 0;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can you use ORDER BY clause in aggregate functions?

[sqlite]

Can you use ORDER BY clause in aggregate functions? It seems that you cannot; 
it is only available for window functions.
However, sometimes is useful using ORDER BY with aggregate functions that 
aren't window functions, such as GROUP_CONCAT function.
Therefore is the suggestion to add it if it doesn't already.
_______
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] More bug with sqlite3_declare_vtab; also comments about ALTER TABLE

[sqlite]

There seems a bug with sqlite3_declare_vtab that if you specify both INTEGER 
PRIMARY KEY and WITHOUT ROWID then it segfaults.
It is easily enough to work around, but it shouldn't segfault if the string 
pointer is a valid pointer to a null-terminated string and the database pointer 
is a valid one given to xCreate or xConnect.

Also, thank you to add "PRAGMA legacy_alter_table"; otherwise some things can 
break (including the old documentation specifying different behaviour with no 
hint that it would change).
One thing I wanted to have is to be able to use the ALTER TABLE command to 
rename views; it should not be too difficult to fix. I once fixed this myself 
actually so that ALTER TABLE could also be used to renae views, although 
perhaps it might not be thoroughly tested.

Furthermore, a documentation problem with window functions is that the none of 
lang.html, lang_expr.html, and lang_select.html mention window functions at all 
except as part of the syntax diagram in lang_expr.html (although window 
definitions are also mentioned in lang_select.html, not window functions)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Suggestion about check-in 1fa74930ab

[sqlite]

This check-in is done so that trigger programs can use table-valued-functions. 
But it seems to me that the correct way should be to check if it is a eponymous 
virtual table; whether it uses table-valued-function syntax or not is 
irrelevant. Since, eponymous virtual tables do not belong to any particular 
database.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] If two orders are both the same order?

[sqlite]

I have a schema with the following definition:
  CREATE TABLE "XPOST"("GNAME" TEXT, "AN" INT, "TIME" INT, PRIMARY KEY 
("GNAME", "AN", "TIME")) WITHOUT ROWID;

However, the order by "AN" and the order by "TIME" will be the same order.
(I also have a table "ART" where "AN" is the rowid, and again the order by 
"TIME" will be the same order.)

How can you make SQLite to make that assumption in order to optimize the query?
(It should be done presumably without adding another index, since the data is 
already in the correct order.)

(This is my "sqlnetnews" NNTP server software, which is public domain open 
source. I don't know if maybe you might want to use NNTP for your mailing 
lists?)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] If two orders are both the same order?

[sqlite]

sqlite-users@mailinglists.sqlite.org wrote:

> Your schema implies that there can be more than one TIME for any GNAME and AN 
> combination (otherwise the primary key would not need to include alle three 
> fields). This contradicts your statement that AN and TIME are "the same 
> order". 
> (consider the tuples ("T1",1,1) and ("T2",1,2); the AN field compares equal, 
> so 
> ORDER BY AN is free to return the T2 row before the T1 row).
>
> Which query specifically would you have in mind that relies on your assertion?
>
> Also, if your application requires that rows be returned in a specifc order, 
> your MUST specify this with en ORDER BY clause and not rely on the visitation 
> order. The visitation order may change due to a number of factors including 
> the 
> SQLite version, the "shape" of your data, running ANALYZE and maybe more.

About the PRIMARY KEY you are correct; that is my mistake.

The specific query is this one:
  SELECT `ART`.`MID` FROM `XPOST`, `ART` USING(`AN`) WHERE `XPOST`.`TIME` >= ?1 
AND `XPOST`.`GNAME` = ?2;

(The (GNAME,AN) combinations are actually unique, for any value of AN there is 
exactly one value of TIME. Probably TIME doesn't really belong in XPOST at all; 
I originally put it there due to this confusion I had and then forgot to remove 
it; that is also why it is part of the primary key even though it shouldn't be. 
The next version of my software would probably fix that.)

The above query implements the NEWNEWS command of NNTP. RFC 3977 says "the 
order of the response has no specific significance and may vary from response 
to response in the same session"; so, in order that SQLite can choose the most 
efficient query plan without requiring a specific order, there is no ORDER BY 
clause.

(There is another variant of that query without the second part of the WHERE 
clause, used if "NEWNEWS *" is specified. NEWNEWS followed by anything other 
than * or a single newsgroup currently results in a 503 error in this 
implementation.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] readfile() enhancement request

[sqlite]

sqlite-users@mailinglists.sqlite.org wrote:

> It's quite often (for me, at least) the case I need to do something like this 
> from the command line:
>
> >sqlite3.exe my.db "insert into t values(`simple field','multi-line text 
> >copied 
> >from some other app')
>
> The problem is the multi-line text cannot be copy-pasted directly into the 
> command line as the first newline will terminate the command.  So, I've been 
> using readline() like so:
>
> First, save the copied text into some arbitrary file (e.g., xxx), and then do
>
> >sqlite3.exe my.db "insert into t values(`simple field',readfile(`xxx'))

If you are using a UNIX-based system, you can try my "pipe" extension, which 
would allow you to write:

  insert into t values('simple field',cast(pipe('','xclip -o') as text));

You can download this and other extensions at:

  http://zzo38computer.org/sql/sqlext.zip

(For Macintosh you may need to change "xclip -o" to the proper command on 
Macintosh, which I don't know. For Windows, this extension is unlikely to work, 
but you can try if you want to.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Problem on Windows 10 machines

[sqlite]

ll
LoadedModule[46]=C:\WINDOWS\SYSTEM32\atl.DLL
LoadedModule[47]=C:\WINDOWS\WinSxS\x86_microsoft.windows.
gdiplus_6595b64144ccf1df_1.1.15063.483_none_9e9856e456d5e776\gdiplus.DLL
LoadedModule[48]=C:\WINDOWS\SYSTEM32\winmm.dll
LoadedModule[49]=C:\WINDOWS\SYSTEM32\winmmbase.dll
LoadedModule[50]=C:\Users\User\AppData\Roaming\
Microsoft\AddIns\Bin\SQLite3_StdCall.dll
LoadedModule[51]=C:\WINDOWS\SYSTEM32\MSVCR120.dll
State[0].Key=Transport.DoneStage1
State[0].Value=1
FriendlyEventName=Stopped working
ConsentKey=APPCRASH
AppName=COM32on64
AppPath=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\COM32on64.exe
NsPartner=windows
NsGroup=windows8
ApplicationIdentity=25991C42874038C9686260EA4D8761D8
MetadataHash=-1228563750


COM32on64.exe is VB6 ActiveX exe that loads my VB6 dll. This is needed as
this dll is called from 64 bits Excel and that can't access that 32 bit VB6
dll the
normal way. This loading of the VB6 dll via COM32on64.exe is not the
problem as the dll works all fine, until it makes a call to SQLite.

Not sure this dump of WerFault.exe helps me much as all I got is Stopped
working and APPCRASH.
At least it shows all the dependencies that are involved.
MSVCR120.dll is present and version is 12.0.40660.0.


RBS


On Thu, Sep 7, 2017 at 9:58 AM, Chris Locke 
wrote:


I'd suggest running the Microsoft Process Monitor
https://docs.microsoft.com/en-us/sysinternals/downloads/procmon

When your application crashes, this will show the files it tried to access
before the crash.  It might point to a dependancy missing.
Have you 'installed' SQLite on your Win 10 machines?  I use
system.data.sqlite.dll in my applications, and that requires msvcr120.dll.
Without that, I get a weird 'SQLite.Interop.dll module could not be found'
error ... which makes sense, but its not strictly accurate ... its there,
it just can't be loaded.  SQLite requires a couple of extra files to run
properly.  They may not be installed on the Win 10 box.

Ideally, you need a proper stack trace and error log from your
application.


In Windows you get a frowny face "modern icon"

Thats for a full-on Windows 'blue screen', not an application crash.  I
assume this isn't causing a blue-screen, but is just failing.


Could the problem be that SQLite is installed by MS already on those

machines?

SQLite is a third party product, and would not be pre-installed by
Microsoft.


Thanks,
Chris


On Thu, Sep 7, 2017 at 8:45 AM, Bart Smissaert 
wrote:


Yes, not very helpful. The message is from my VB6 wrapper as is like

this:

Method ProcedureX of object _ClassX failed

ClassX is the class in the wrapper ActiveX dll that also has the

procedure

that makes the call to SQLite that causes the problem, in this
case sqlite3_initialize.
ProcedureX is another procedure in that same class, but that procedure

has

nil to do with the problem.
I can take that ProcedureX out and that I will get another procedure
mentioned in the error message that is again completely unrelated to the
problem.

So the whole thing is just completely puzzling and I am seriously stuck
with this.


RBS



On Thu, Sep 7, 2017 at 2:44 AM, Keith Medcalf 

wrote:

In Windows you get a frowny face "modern icon" (about 5 inches square)

and

"something went wrong, sorry about your luck".


---
The fact that there's a Highway to Hell but only a Stairway to Heaven

says

a lot about anticipated traffic volume.



-Original Message-
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: Wednesday, 6 September, 2017 15:06
To: SQLite mailing list
Subject: Re: [sqlite] Fwd: Problem on Windows 10 machines



On 6 Sep 2017, at 10:03pm, Bart Smissaert 
wrote:


When my wrapper makes the call to the Sqlite dll my app crashes

With what error ?  Segmentation fault ?  Privilege violation ?  I
don’t think I’ve seen any crash which doesn’t produce an error report
of some kind, even if we know that there’s no reason for that error
at that point.

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



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


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


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




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


--

[sqlite] Feature requests for virtual table mechanism of SQLite

[sqlite]

I have some proposals for feature requests for virtual table mechanism of
SQLite. Some of this can be useful when accessing remote data over the
internet or whatever. Here is the list:

* A new method "xInterrupt", called when sqlite3_interrupt() is called.
This can be used to cancel downloads/uploads (e.g. if libcurl is used,
xInterrupt might set a flag that causes the XFERINFOFUNCTION to return
nonzero, which causes libcurl to return CURLE_ABORTED_BY_CALLBACK, which in
turn may cause xFilter or xNext to return SQLITE_INTERRUPT).

* Possibility to declare columns whose values are expressions; these
columns are never given values by UPDATE or INSERT, have no name, and are
always hidden. The expressions need not be deterministic. This can be used
for example to consume "ORDER BY RANDOM() LIMIT 1"; it need only ask the
server for a single random row, rather than downloading everything and
selecting a random row on the client side. There are other uses too, such
as more kind of constraints can be detected in the WHERE clause.

* The ability to consume LIMIT/OFFSET clauses. (Of course, the LIMIT/OFFSET
clause cannot be consumed unless the ORDER BY clause and WHERE clause are
also consumed. Because there may be some unusable constraints, the virtual
table module may not be given the LIMIT/OFFSET clauses even if there are
some, because it cannot be consumed.) For example, the Scryfall API is
paginated, so it would help with that; see also the above, where "ORDER BY
RANDOM() LIMIT 1" is used to request a single random card, it can use that
to know that you only want one and form the request it sends to the server
in that way. (Note also that the built-in MIN() and MAX() functions may
generate a ORDER BY and LIMIT clause automatically; to the virtual table,
they may be considered the same as explicit ORDER BY and LIMIT clauses.)

* A "boolean" constraint type. Together with consuming expressions, this
might be used for implementing some kind of constraints which is otherwise
difficult to do (such as checking if a bit is set in a number, or comparing
if one column's value is greater than another).

There are also some other less important stuff, but that still would help.
The Scryfall documentation mentions many things. While they could be
represented in SQL code, the current virtual table mechanism of SQLite is
not capable to do a lot of these things so that an extension could be made
to automatically convert the query. Some things are:

* Aggregate queries (e.g. the "total_cards" field in Scryfall).

* Queries with JOIN (this may be very difficult).

There is also the possibility that some of the stuff I mentioned is
difficult; in such a case, possibly, only some of the things I mentioned
might be implemented and others aren't.

_______
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mailing list policy change

[SQLite]

On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database <
sqlite-users at mailinglists.sqlite.org> wrote:

> On 28.10.2015 18:52, General Discussion of SQLite Database wrote:
>
>> Hence, we have token the radical approach of denying the sender email
>> address to*everyone*.
>>
>
> Could you preserve the sender's name in the from header instead of
> substituting the generic "General Discussion of SQLite Database"?
>
> This would make it possible to automatically highlight messages by author,
> i.e. the SQLite dev team.


My suggestion is to go whole-hog and find a mailing-list system or host
which allows routing return addresses back through the server.  It could be
blob-7fe742b at mailinglists.sqlite.org , or it could even use info stripped
from the email, so ScottHess-7fe742b at mailinglists.sqlite.org.  The basic
goal being to have a readable part and an unpredictable part.  Then people
abusing the system in simple ways can be directly identified.  [If the
spammer is going to spend time looking up old email addresses, then
changing the list policies will take a long time to help, much, since there
are years of addresses already out there.]

Another option would be to have the server forward emails with various
delays so that when people report spam you could (maybe) figure out by the
timing which subset of recipients were at fault.

Personally, I'd rather know who's communicating on the channel and deal
with periodic spam.

-scott (shess at google.com)

[sqlite] Mailing list policy change

[SQLite]

On Wed, Oct 28, 2015 at 1:32 PM, General Discussion of SQLite Database <
sqlite-users at mailinglists.sqlite.org> wrote:

> On 2015-10-28 10:52 AM, General Discussion of SQLite Database wrote:
>
>> The reason for this change is to combat the "Alexa" spam.  For the
>> past few weeks, whenever anybody posts to the mailing list, that
>> person gets a reply from "Alexa"...
>>
>
> While that was often the case, I recall someone saying they got the Alexa
> spam simply by subscribing to the list, without posting.  This implies a
> server-side leak.  Unless that poster was wrong. -- Darren Duncan


I (Scott Robison) tried to exercise that by signing up a new account with a
new email address and never received Alexa spam to the new address with my
(very obviously faked) user name. I can't say whether it is because the
list admins saw the (very obviously faked) account and deleted it (as they
did a day or so later) or if the Alexa spam generator requires manual
intervention, but at the very least the process of signing up for the
address was not enough.

Also, I have not received Alexa spam to every email I have sent to the
list. I've received a few, but not every time.

-- 
Scott Robison

[sqlite] Mailing list policy change

[SQLite]

Is this over-reacting a bit. I have had one email from alexa (about
3/4 weeks ago). If it starts to become a real problem then do
something about it - until then I would think we all have more
important things to worry about.



Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 28 October 2015 at 19:42, SQLite
 wrote:
> On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database <
> sqlite-users at mailinglists.sqlite.org> wrote:
>
>> On 28.10.2015 18:52, General Discussion of SQLite Database wrote:
>>
>>> Hence, we have token the radical approach of denying the sender email
>>> address to*everyone*.
>>>
>>
>> Could you preserve the sender's name in the from header instead of
>> substituting the generic "General Discussion of SQLite Database"?
>>
>> This would make it possible to automatically highlight messages by author,
>> i.e. the SQLite dev team.
>
>
> My suggestion is to go whole-hog and find a mailing-list system or host
> which allows routing return addresses back through the server.  It could be
> blob-7fe742b at mailinglists.sqlite.org , or it could even use info stripped
> from the email, so ScottHess-7fe742b at mailinglists.sqlite.org.  The basic
> goal being to have a readable part and an unpredictable part.  Then people
> abusing the system in simple ways can be directly identified.  [If the
> spammer is going to spend time looking up old email addresses, then
> changing the list policies will take a long time to help, much, since there
> are years of addresses already out there.]
>
> Another option would be to have the server forward emails with various
> delays so that when people report spam you could (maybe) figure out by the
> timing which subset of recipients were at fault.
>
> Personally, I'd rather know who's communicating on the channel and deal
> with periodic spam.
>
> -scott (shess at google.com)
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mailing list policy change

[SQLite]

On Wed, Oct 28, 2015 at 1:46 PM, SQLite <
sqlite-users at mailinglists.sqlite.org> wrote:

> Is this over-reacting a bit. I have had one email from alexa (about
> 3/4 weeks ago). If it starts to become a real problem then do
> something about it - until then I would think we all have more
> important things to worry about.
>

For some people it is a larger problem. I've received a few (I think 4)
Alexa emails since this began. It sounds like some people get a lot more
(like DRH).

SDR


>
>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic
> <http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit-Forensic>
> Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
>
> On 28 October 2015 at 19:42, SQLite
>  wrote:
> > On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database <
> > sqlite-users at mailinglists.sqlite.org> wrote:
> >
> >> On 28.10.2015 18:52, General Discussion of SQLite Database wrote:
> >>
> >>> Hence, we have token the radical approach of denying the sender email
> >>> address to*everyone*.
> >>>
> >>
> >> Could you preserve the sender's name in the from header instead of
> >> substituting the generic "General Discussion of SQLite Database"?
> >>
> >> This would make it possible to automatically highlight messages by
> author,
> >> i.e. the SQLite dev team.
> >
> >
> > My suggestion is to go whole-hog and find a mailing-list system or host
> > which allows routing return addresses back through the server.  It could
> be
> > blob-7fe742b at mailinglists.sqlite.org , or it could even use info
> stripped
> > from the email, so ScottHess-7fe742b at mailinglists.sqlite.org.  The basic
> > goal being to have a readable part and an unpredictable part.  Then
> people
> > abusing the system in simple ways can be directly identified.  [If the
> > spammer is going to spend time looking up old email addresses, then
> > changing the list policies will take a long time to help, much, since
> there
> > are years of addresses already out there.]
> >
> > Another option would be to have the server forward emails with various
> > delays so that when people report spam you could (maybe) figure out by
> the
> > timing which subset of recipients were at fault.
> >
> > Personally, I'd rather know who's communicating on the channel and deal
> > with periodic spam.
> >
> > -scott (shess at google.com)
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Scott Robison

[sqlite] Mailing list policy change

[SQLite]

Actually looking at this thread (in gmail) since the policy change is
a very retrograde step - all messages are displayed as  from SQLite.

There are numerous scenarios where I want to see the name of the
sender (not necessarily the email address) so that I can pick and
choose which messages I read.

I fear the cure here is going to be worse than the disease.
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 28 October 2015 at 19:46, SQLite
 wrote:
> Is this over-reacting a bit. I have had one email from alexa (about
> 3/4 weeks ago). If it starts to become a real problem then do
> something about it - until then I would think we all have more
> important things to worry about.
>
>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
>
> On 28 October 2015 at 19:42, SQLite
>  wrote:
>> On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database <
>> sqlite-users at mailinglists.sqlite.org> wrote:
>>
>>> On 28.10.2015 18:52, General Discussion of SQLite Database wrote:
>>>
>>>> Hence, we have token the radical approach of denying the sender email
>>>> address to*everyone*.
>>>>
>>>
>>> Could you preserve the sender's name in the from header instead of
>>> substituting the generic "General Discussion of SQLite Database"?
>>>
>>> This would make it possible to automatically highlight messages by author,
>>> i.e. the SQLite dev team.
>>
>>
>> My suggestion is to go whole-hog and find a mailing-list system or host
>> which allows routing return addresses back through the server.  It could be
>> blob-7fe742b at mailinglists.sqlite.org , or it could even use info stripped
>> from the email, so ScottHess-7fe742b at mailinglists.sqlite.org.  The basic
>> goal being to have a readable part and an unpredictable part.  Then people
>> abusing the system in simple ways can be directly identified.  [If the
>> spammer is going to spend time looking up old email addresses, then
>> changing the list policies will take a long time to help, much, since there
>> are years of addresses already out there.]
>>
>> Another option would be to have the server forward emails with various
>> delays so that when people report spam you could (maybe) figure out by the
>> timing which subset of recipients were at fault.
>>
>> Personally, I'd rather know who's communicating on the channel and deal
>> with periodic spam.
>>
>> -scott (shess at google.com)
>> _______
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mailing list policy change

[SQLite]

On 28 Oct 2015, at 7:36pm, General Discussion of SQLite Database  wrote:

> Has anybody received email from Alexa since the policy change?  I have not

Nor me.  I reliably got one for every post I made for about a week before the 
change.

Simon.

[sqlite] (BUG) sqlite cannot search for text, if inserted via sqlite3_bind_blob

[sqlite]

In my endless obsession with premature optimization, I've been using
sqlite3_bind_blob, whenever I know the length of what I'm inserting, even if
it's text or whatnot. It exhibits some very strange properties though, which I
can't imagine is anything other than a bug. Here's my test case:

---mimesucks-

#include 
#include 
#include  // NULL
#include 

#define LITLEN(lit) lit, sizeof(lit)-1

int main(int argc, char *argv[])
{
sqlite3* db;
sqlite3_open(":memory:",&db);
sqlite3_exec(db,"CREATE TABLE foo (id INTEGER PRIMARY KEY, bar
TEXT)",NULL,NULL,NULL);
sqlite3_stmt *ins,*sel;
sqlite3_prepare(db,LITLEN("INSERT INTO foo (bar) VALUES (?)"),&ins,NULL);
sqlite3_prepare(db,LITLEN("SELECT id FROM foo WHERE bar = ?"),&sel,NULL);

puts("This is fine.");
sqlite3_bind_text(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_text(sel,1,"test",4,NULL);
assert(SQLITE_ROW == sqlite3_step(sel));
printf("Got ID %d\n",sqlite3_column_int(sel,0));

sqlite3_stmt* clear;
sqlite3_prepare(db,LITLEN("DELETE FROM foo"),&clear,NULL);
sqlite3_step(clear);
sqlite3_reset(clear);
   
puts("This is NOT fine.");
sqlite3_bind_blob(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_blob(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}

sqlite3_step(clear);
sqlite3_reset(clear);

puts("This is NOT fine.");
sqlite3_bind_blob(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_text(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
   
sqlite3_step(clear);
sqlite3_reset(clear);

puts("This is fine though?");
sqlite3_bind_text(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_blob(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
   
return 0;
}


---mimesucks-----

When I insert anything via sqlite3_bind_blob, sqlite then loses the record, uh,
forever. Searching for the exact same text I just inserted, I cannot get any
results, if sqlite3_bind_blob is used. Even if I use sqlite3_bind_blob on both
the search text and the insert text, sqlite still comes up with no results.
Interestingly, if I use sqlite_bind_text on insert, then sqlite_bind_blob on
select, the database DOES find a result. Only when I use sqlite3_bind_blob, on
an insert statement, does the resulting field become entirely unsearchable. If I
get the row by some other criteria, the data inserted with sqlite3_bind_blob and
sqlite3_bind_text are byte-equivalent, and when I look at them using
sqlite3_column_blob/bytes, neither one has an embedded null terminator.

I'm not... familiar with sqlite's innards, but my best guess is that there must
be a (hidden) null terminator included on disk for stuff inserted with
sqlite3_bind_text, that isn't reported, but when sqlite3_bind_blob is used, that
extra byte for the null doesn't go on disk. When sqlite encounters an "a = b"
expression, it assumes that null byte exists, and adds the null terminator to
the criteria, which then fails to find the row, since the actual data was
inserted with sqlite3_bind_blob and lacks a null terminator.

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

[sqlite] (BUG?) sqlite3_bind_blob oops, not exactly a bug

[sqlite]

Darn it! Sorry! I forgot to reset my prepared select statement. My whole example
was screwed up. The actual behavior is at least sensible-ish, but still not
quite right.

If you use sqlite3_bind_blob on insert and select, it will successfully find the
test row. Only mixing sqlite3_bind_blob, and sqlite3_bind_text causes these
mysterious failures. Does sqlite3_bind_text... encode it to UTF-16 or something?

This is still a (moderately) huge problem for troubleshooting, because for
instance inserting a row with a text field using the "sqlite3" command line
utility won't work if the separate program you wrote to use the database uses
sqlite3_bind_blob. I tried inserting a blob with a trailing null, but that
didn't help either. Am I misunderstanding something about that sqlite3_bind_text
function?

Here's the not stupidly buggy example:

-

#include 
#include 
#include  // NULL
#include  //

#define LITLEN(lit) lit, sizeof(lit)-1

int main(int argc, char *argv[])
{
sqlite3* db;
sqlite3_open(":memory:",&db);
sqlite3_exec(db,"CREATE TABLE foo (id INTEGER PRIMARY KEY, bar
TEXT)",NULL,NULL,NULL);
sqlite3_stmt *ins,*sel;
sqlite3_prepare(db,LITLEN("INSERT INTO foo (bar) VALUES (?)"),&ins,NULL);
sqlite3_prepare(db,LITLEN("SELECT id FROM foo WHERE bar = ?"),&sel,NULL);

puts("This is fine.");
sqlite3_bind_text(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_text(sel,1,"test",4,NULL);
assert(SQLITE_ROW == sqlite3_step(sel));
printf("Got ID %d\n",sqlite3_column_int(sel,0));
sqlite3_reset(sel);

sqlite3_stmt* clear;
sqlite3_prepare(db,LITLEN("DELETE FROM foo"),&clear,NULL);
sqlite3_step(clear);
sqlite3_reset(clear);
   
puts("This is fine too.");
sqlite3_bind_blob(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_blob(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
sqlite3_reset(sel);

sqlite3_step(clear);
sqlite3_reset(clear);

puts("This is NOT fine.");
sqlite3_bind_blob(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_text(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
sqlite3_reset(sel);
   
sqlite3_step(clear);
sqlite3_reset(clear);

puts("This is also NOT fine");
sqlite3_bind_text(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_blob(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
    printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
sqlite3_reset(sel);
   
return 0;
}


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

[sqlite] LSM bug

[sqlite]

Hi,

I'm not exactly sure this is the right forum for my problem, as I know that 
LSM is experimental, but here we go.

I tried loading a whole lot of data using LSM. The majority of the data goes 
int a single huge transaction (begin/commit pair). My program segfaults once 
we're 1.61GB into my data file.

I have attached the source code to my test program. You also will need my data 
file, which is too big for email:

http://www.derkarl.org/~charles/lsmlog.bz2 (744 MiB)

Here is how you can run my test program:

bunzip2 < lsmlog.bz2 | pv | ./a.out lsmdbtocreate

(You can exclude "pv" from the pipeline if you don't have it installed)

Here is the backtrace:

treeShmalloc (pDb=pDb@entry=0x12b20a8, bAlign=bAlign@entry=1, 
nByte=nByte@entry=12, pRc=pRc@entry=0x7fff2fd43f44)
at src/lsm_tree.c:682
682 pNext->iNext = 0;
(gdb) bt
#0  treeShmalloc (pDb=pDb@entry=0x12b20a8, bAlign=bAlign@entry=1, 
nByte=nByte@entry=12, pRc=pRc@entry=0x7fff2fd43f44)
at src/lsm_tree.c:682
#1  0x0041122d in treeShmallocZero (pDb=pDb@entry=0x12b20a8, 
nByte=nByte@entry=12, piPtr=piPtr@entry=0x7fff2fd43f4c, 
pRc=pRc@entry=0x7fff2fd43f44) at src/lsm_tree.c:711
#2  0x00413114 in newTreeLeaf (pRc=0x7fff2fd43f44, 
piPtr=0x7fff2fd43f4c, 
pDb=0x12b20a8) at src/lsm_tree.c:726
#3  treeInsertLeaf (iSlot=1, iTreeKey=2146172860, pCsr=0x7fff2fd43f50, 
pDb=0x12b20a8) at src/lsm_tree.c:1039
#4  treeInsertEntry (pDb=pDb@entry=0x12b20a8, flags=8, 
pKey=pKey@entry=0x12b2058, nKey=nKey@entry=17, pVal=, 
pVal@entry=0x12bb638, nVal=21) at src/lsm_tree.c:1552
#5  0x0041329f in lsmTreeInsert (pDb=pDb@entry=0x12b20a8, 
pKey=pKey@entry=0x12b2058, nKey=nKey@entry=17, 
pVal=pVal@entry=0x12bb638, nVal=) at src/lsm_tree.c:1587
#6  0x00404db0 in doWriteOp (pDb=0x12b20a8, bDeleteRange=, pKey=0x12b2058, nKey=17, pVal=0x12bb638, 
nVal=) at src/lsm_main.c:696
#7  0x0040305d in main (argc=2, argv=0x7fff2fd44418) at runlsm.cpp:41
(gdb) print pNext
$1 = (ShmChunk *) 0x



It's a pity that LSM isn't ready for production, because if the quality of 
sqlite3 is indication, I'm going to really enjoy using it!

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

Re: [sqlite] LSM bug

[sqlite]

I didn't know this list strips attachments, so the source file is here:

http://derkarl.org/~charles/runlsm.cpp

On Monday, April 28, 2014 01:41:02 PM sql...@charles.derkarl.org wrote:
> Hi,
> 
> I'm not exactly sure this is the right forum for my problem, as I know that
> LSM is experimental, but here we go.
> 
> I tried loading a whole lot of data using LSM. The majority of the data
> goes int a single huge transaction (begin/commit pair). My program
> segfaults once we're 1.61GB into my data file.
> 
> I have attached the source code to my test program. You also will need my
> data file, which is too big for email:
> 
> http://www.derkarl.org/~charles/lsmlog.bz2 (744 MiB)
> 
> Here is how you can run my test program:
> 
> bunzip2 < lsmlog.bz2 | pv | ./a.out lsmdbtocreate
> 
> (You can exclude "pv" from the pipeline if you don't have it installed)
> 
> Here is the backtrace:
> 
> treeShmalloc (pDb=pDb@entry=0x12b20a8, bAlign=bAlign@entry=1,
> nByte=nByte@entry=12, pRc=pRc@entry=0x7fff2fd43f44)
> at src/lsm_tree.c:682
> 682 pNext->iNext = 0;
> (gdb) bt
> #0  treeShmalloc (pDb=pDb@entry=0x12b20a8, bAlign=bAlign@entry=1,
> nByte=nByte@entry=12, pRc=pRc@entry=0x7fff2fd43f44)
> at src/lsm_tree.c:682
> #1  0x0041122d in treeShmallocZero (pDb=pDb@entry=0x12b20a8,
> nByte=nByte@entry=12, piPtr=piPtr@entry=0x7fff2fd43f4c,
> pRc=pRc@entry=0x7fff2fd43f44) at src/lsm_tree.c:711
> #2  0x00413114 in newTreeLeaf (pRc=0x7fff2fd43f44,
> piPtr=0x7fff2fd43f4c, pDb=0x12b20a8) at src/lsm_tree.c:726
> #3  treeInsertLeaf (iSlot=1, iTreeKey=2146172860, pCsr=0x7fff2fd43f50,
> pDb=0x12b20a8) at src/lsm_tree.c:1039
> #4  treeInsertEntry (pDb=pDb@entry=0x12b20a8, flags=8,
> pKey=pKey@entry=0x12b2058, nKey=nKey@entry=17, pVal=,
> pVal@entry=0x12bb638, nVal=21) at src/lsm_tree.c:1552
> #5  0x0041329f in lsmTreeInsert (pDb=pDb@entry=0x12b20a8,
> pKey=pKey@entry=0x12b2058, nKey=nKey@entry=17,
> pVal=pVal@entry=0x12bb638, nVal=) at src/lsm_tree.c:1587
> #6  0x00404db0 in doWriteOp (pDb=0x12b20a8, bDeleteRange= out>, pKey=0x12b2058, nKey=17, pVal=0x12bb638,
> nVal=) at src/lsm_main.c:696
> #7  0x0040305d in main (argc=2, argv=0x7fff2fd44418) at
> runlsm.cpp:41 (gdb) print pNext
> $1 = (ShmChunk *) 0x
> 
> 
> 
> It's a pity that LSM isn't ready for production, because if the quality of
> sqlite3 is indication, I'm going to really enjoy using it!
> 
> Charles
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LSM bug

[sqlite]

On Tuesday, April 29, 2014 03:38:57 AM Dan Kennedy wrote:
> On 04/29/2014 03:53 AM, sql...@charles.derkarl.org wrote:
> > I didn't know this list strips attachments, so the source file is here:
> > 
> > http://derkarl.org/~charles/runlsm.cpp
> 
> Thanks for this. It is a problem.
> 
> LSM accumulates data in a tree structure in shared-memory until there is
> "enough" (~1-2 MB) to flush through to the database file. But at the
> moment, it can only flush data to the db file between transactions. And
> the in-memory tree can only hold 2GB of data (including overhead). So
> things fail if a single transaction exceeds that limit. In the short
> term, it should be changed to return LSM_FULL for any transaction too
> large to handle. But the real fix should be to change things so that LSM
> can begin flushing data to the database file mid-transaction.

I'm also seeing a similar problem in which it silently discards entries, but I 
haven't been able to narrow down an example for you. Let me know if that would 
be helpful and I'll try harder.

What could I do to improve LSM?

Charles

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

[sqlite] LSM Leaks memory

[sqlite]

I have this trivial program:

int main(int argc, char **argv)
{
lsm_db* db;
lsm_new(lsm_default_env(), &db);
lsm_open(db, "lsm");

lsm_cursor *csr;
lsm_csr_open(db, &csr);
lsm_csr_seek(csr, "a", 1, LSM_SEEK_GE);
lsm_csr_seek(csr, "a", 1, LSM_SEEK_EQ);
lsm_csr_seek(csr, "a", 1, LSM_SEEK_EQ);
lsm_csr_close(csr);
lsm_begin(db, 1);
lsm_insert(
db, "abc", 3,
"def", 3
);
lsm_commit(db, 0);
lsm_close(db);
}

And I run it under valgrind with --leak-check=full it reports this:

==1741== 1,008 (112 direct, 896 indirect) bytes in 1 blocks are definitely lost 
in loss record 4 of 4
==1741==at 0x4C28BED: malloc (vg_replace_malloc.c:263)
==1741==by 0x413D4B: lsmPosixOsMalloc (lsm_unix.c:472)
==1741==by 0x4046C5: lsmMallocZero (lsm_mem.c:50)
==1741==by 0x404730: lsmMallocZeroRc (lsm_mem.c:69)

In a more complex program, lsm seems to leak memory to no bounds, causing my 
application.

Are bug reports against LSM even helpful?

Charles

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

[sqlite] Test message, please ignore...

[sqlite]

Test.

--
Joe Mistachkin

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

Re: [sqlite] [SQLite]Basic queries

[sqlite]

On Thursday, November 13, 2014 04:27:02 PM Shinichiro Yoshioka wrote:
> Is there any special reason why there are 2 dlls on the page?
> And the dll for win 32bits doesn't work on win 64bits OS
> in spite of exsistance of WOW64?

While a 32 bit version will work on a 64 bit Windows as you said, it will not 
work when linked to a 64 bit application. The 64-bit version is supplied for 
developers of 64-bit applications.

> 
> 2) I have compiled the amalgamation source code on win7 32bits OS.
> Though I haven't specified any compile option, in this case,
> For which OS platform is the generated binary?
> for 32bits win OS or 64bits win OS?

32 bits.

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

[sqlite] Segfault during FTS index creation from huge data

[SQLite]

I don't know if it will actually cause problems, but is the 
"?command.Dispose()" needed? Doesn't the "using" handle disposing?

Graham


Sent from Samsung Mobile

 Original message 
From: Artem  
Date: 28/04/2015  14:29  (GMT+00:00) 
To: General Discussion of SQLite Database  
Subject: Re: [sqlite] Segfault during FTS index creation from huge data 

No, I'm sure that is not a problem in my software, it exactly
error of the SQLite library. My software is very simple - it creates
simple connection to the database with connection string like
"Data Source={0};New=false;Journal Mode=Off;Synchronous=Off;FailIfMissing=True"
and executes a query like
INSERT INTO test_fts(test_fts) VALUES('rebuild');
and that is all.
I'm pretty sure because I got exactly the same error in SQLite Expert 
Professional -
popular? desktop? sqlite-management? software, that uses another
sqlite driver.

P.S. Source code of my function:

Using conn As New SQLiteConnection(String.Format("Data 
Source={0};New=false;Journal Mode=Off;Synchronous=Off;FailIfMissing=True", 
"f:\Suggests\suggests.db"))

??? conn.Open()

??? Using command = conn.CreateCommand
??? command.CommandText = "INSERT INTO suggests_fts(suggests_fts) 
VALUES('rebuild');"
??? command.ExecuteNonQuery()
??? command.Dispose()
??? End Using

??? conn.Close()

End Using

P.S. I can send the database to someone who can try, 19 GB in
rar-archive.

> Getting "NoMem" sounds very much like a memory leak somewhere, with
> the most likely place being your own application, followed by the
> wrapper you are using, the FTS code and lastly the SQLite core.
> Lastly because the SQLite core is extensively tested with an
> explicit emphasis on not leaking memory (or other resources) in the
> first place and secondly recovering gracefully from memory allocation 
> failures.

> Also, since you have swapped out SQLite versions and even operating
> systems without eliminating the problem, it seems rational to look
> into the parts that have remained the same.

> Maybe you could run a test on Linux under the control of valgrind
> and have its memcheck tool take a look.

> -Urspr?ngliche Nachricht-----
> Von: Artem [mailto:devspec at yandex.ru]
> Gesendet: Dienstag, 28. April 2015 14:36
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Segfault during FTS index creation from huge data

>> On 04/03/2015 10:16 PM, Artem wrote:
>>> Hi!
>>>
>>> The situation is like that. There?s a SQLite database with around 3 billion 
>>> records. Each record consists of a certain CHAR field and several other 
>>> additional fields with different types. The file size is approx. 340 gb. 
>>> The maximum content length in the doc field is 256 symbols, the content is 
>>> in Russian.
>>>
>>> I?m trying to create a full-text index, but it results in a Segmentation 
>>> Fault error. I?ve been trying to create it in different possible ways, both 
>>> under Windows (with SQLite Expert and my own .NET software, including one 
>>> with x64 architecture) and Linux (both Ubuntu and Centos). I?ve even 
>>> compiled sqlite from the sources, having included necessary flags for FTS3 
>>> and FTS4, but every time I get one and the same error.

>> This does sound like a real problem, but one that might be difficult
>> to track down.

>> Are you able to get us a stack trace of the crash? Ideally one from a
>> build with compiler options "-g -DSQLITE_DEBUG" set.

>> Thanks,
>> Dan.

> Hi, Dan. Now I can't to do this because I haven't Linux on my PC.
> But I tried to create FTS table again (now it was another database with
> 1 350 000 000 rows, smaller than before). And I got the same error (out of 
> memory) on function:

> internal override SQLiteErrorCode Reset(SQLiteStatement stmt)

> in file SQLite3.cs

> It returns System.Data.SQLite.SQLiteErrorCode.NoMem.
> I home it helps.

> P.S.? It? is? latest? version? of SQLite.Net compiled in Visual Studio 2012.

>>>
>>> I?ve tried two options:
>>> - creating a contentless FTS4, when content is stored in a regular
>>> table, and FTS-table contains only index (create virtual table
>>> docs_fts using fts4(content='docs'... )
>>> - creating a full-fledged FTS table from a regular one (insert into
>>> docs_fts select doc... from docs;)
>>>
>>> SQLite is functioning for about 4 hours, after which Segmentation Fault 
>>> error occurs inevitably.
>>> There?re no NULL fields in the database.
>>>
>>> I?ve worked with 3 different SQLite versions, includ

[sqlite] Segfault during FTS index creation from huge data

[SQLite]

I don't know if it actually causes a problem, but isn't the 
"?command.Dispose()" not needed? Doesn't the "using" take care of disposing?

Graham.


Sent from Samsung Mobile

 Original message 
From: Artem  
Date: 28/04/2015  14:29  (GMT+00:00) 
To: General Discussion of SQLite Database  
Subject: Re: [sqlite] Segfault during FTS index creation from huge data 

No, I'm sure that is not a problem in my software, it exactly
error of the SQLite library. My software is very simple - it creates
simple connection to the database with connection string like
"Data Source={0};New=false;Journal Mode=Off;Synchronous=Off;FailIfMissing=True"
and executes a query like
INSERT INTO test_fts(test_fts) VALUES('rebuild');
and that is all.
I'm pretty sure because I got exactly the same error in SQLite Expert 
Professional -
popular? desktop? sqlite-management? software, that uses another
sqlite driver.

P.S. Source code of my function:

Using conn As New SQLiteConnection(String.Format("Data 
Source={0};New=false;Journal Mode=Off;Synchronous=Off;FailIfMissing=True", 
"f:\Suggests\suggests.db"))

??? conn.Open()

??? Using command = conn.CreateCommand
??? command.CommandText = "INSERT INTO suggests_fts(suggests_fts) 
VALUES('rebuild');"
??? command.ExecuteNonQuery()
??? command.Dispose()
??? End Using

??? conn.Close()

End Using

P.S. I can send the database to someone who can try, 19 GB in
rar-archive.

> Getting "NoMem" sounds very much like a memory leak somewhere, with
> the most likely place being your own application, followed by the
> wrapper you are using, the FTS code and lastly the SQLite core.
> Lastly because the SQLite core is extensively tested with an
> explicit emphasis on not leaking memory (or other resources) in the
> first place and secondly recovering gracefully from memory allocation 
> failures.

> Also, since you have swapped out SQLite versions and even operating
> systems without eliminating the problem, it seems rational to look
> into the parts that have remained the same.

> Maybe you could run a test on Linux under the control of valgrind
> and have its memcheck tool take a look.

> -Urspr?ngliche Nachricht-
> Von: Artem [mailto:devspec at yandex.ru]
> Gesendet: Dienstag, 28. April 2015 14:36
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Segfault during FTS index creation from huge data

>> On 04/03/2015 10:16 PM, Artem wrote:
>>> Hi!
>>>
>>> The situation is like that. There?s a SQLite database with around 3 billion 
>>> records. Each record consists of a certain CHAR field and several other 
>>> additional fields with different types. The file size is approx. 340 gb. 
>>> The maximum content length in the doc field is 256 symbols, the content is 
>>> in Russian.
>>>
>>> I?m trying to create a full-text index, but it results in a Segmentation 
>>> Fault error. I?ve been trying to create it in different possible ways, both 
>>> under Windows (with SQLite Expert and my own .NET software, including one 
>>> with x64 architecture) and Linux (both Ubuntu and Centos). I?ve even 
>>> compiled sqlite from the sources, having included necessary flags for FTS3 
>>> and FTS4, but every time I get one and the same error.

>> This does sound like a real problem, but one that might be difficult
>> to track down.

>> Are you able to get us a stack trace of the crash? Ideally one from a
>> build with compiler options "-g -DSQLITE_DEBUG" set.

>> Thanks,
>> Dan.

> Hi, Dan. Now I can't to do this because I haven't Linux on my PC.
> But I tried to create FTS table again (now it was another database with
> 1 350 000 000 rows, smaller than before). And I got the same error (out of 
> memory) on function:

> internal override SQLiteErrorCode Reset(SQLiteStatement stmt)

> in file SQLite3.cs

> It returns System.Data.SQLite.SQLiteErrorCode.NoMem.
> I home it helps.

> P.S.? It? is? latest? version? of SQLite.Net compiled in Visual Studio 2012.

>>>
>>> I?ve tried two options:
>>> - creating a contentless FTS4, when content is stored in a regular
>>> table, and FTS-table contains only index (create virtual table
>>> docs_fts using fts4(content='docs'... )
>>> - creating a full-fledged FTS table from a regular one (insert into
>>> docs_fts select doc... from docs;)
>>>
>>> SQLite is functioning for about 4 hours, after which Segmentation Fault 
>>> error occurs inevitably.
>>> There?re no NULL fields in the database.
>>>
>>> I?ve worked with 3 different 

Re: [sqlite] Version 3.3.13

[snowcrash+sqlite]

hi,


SQLite version 3.3.13 is now available in the usual place:

...

As always, please let me know if you find any problems.


building 3.3.13 on osx, all's seemingly well, except the process still
fails to build/install one file,

 % cd /usr/ports/sqlite_build
 % ls doc/whentouse.html
/usr/local/bin/ls: cannot access doc/whentouse.html: No such file or 
directory

which is easily remedied,

 % tclsh ../sqlite-3.3.13/www/whentouse.tcl > doc/whentouse.html
 % ls doc/whentouse.html
doc/whentouse.html

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

[sqlite] Transaction detection...

[spaminos-sqlite]

Hi all

Is there a way to detect if a transaction is active on the current connection?
Basically the equivalent of "PQtransactionStatus" in postgresql.

I need that to automate rollback or commit depending on other variables within 
my application while keeping the connection open.

Thanks!

Nicolas

Re: [sqlite] Transaction detection...

[spaminos-sqlite]

- Original Message 
> From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Tuesday, March 27, 2007 1:50:51 PM
> Subject: Re: [sqlite] Transaction detection...
> 
> 
> [EMAIL PROTECTED] wrote:
> > Hi all
> > 
> > Is there a way to detect if a transaction is active on the current 
> > connection?
> > Basically the equivalent of "PQtransactionStatus" in postgresql.
> > 
> > I need that to automate rollback or commit depending on other variables 
> > within my application while keeping the connection open.
> > 
> 
> http://www.sqlite.org/capi3ref#sqlite3_get_autocommit

Cool thanks, that should do it.

Sorry I missed that api call.

Nicolas

Re: [sqlite] sqlite internal structs don't make use of C bitfields?

[spaminos-sqlite]

- Original Message 
> MemPage bitfield patch below. 
> 
> sizeof(MemPage) on Linux: 
> 
>   original: 84
>   patched:  76
> ...
> Break-even for memory is 904/8 = 113 MemPage structs allocated.

I didn't look at the code, so mind me :)

If the MemPage are malloced individually (instead of being put in arrays), then 
they are 16 byte aligned on most platforms, making the allocated block 
effectively the same size (well, that depends on how many bytes are used by 
malloc before the user block in memory).

If on the other hand those structs are packed in arrays then there can be a 
benefit.
But there, I would think that a good experiment would be to split the fields 
into different arrays (the same old optimizations on chunky vs planar for those 
coming from computer graphics) and group data by frequency of use and/or 
locality for the caches.
An example I remember from back in the days was a struct containing data for 
each pixel that we split into two structs (puting the data used less frequently 
in a separate struct), and with this change we got over 500% speed improvement 
on the typical workload just because the processor was doing less cache miss 
and could prefetch much more efficiently when iterating over data.

Also, my take on bitfields is that they are not thread/multi processor friendly 
(there is no atomic "set bit"), and also compilers typically don't optimize 
well with that (so before applying this patch, I would test on other platforms 
than gcc linux x86).

Nicolas

Re: [sqlite] sqlite internal structs don't make use of C bitfields?

[spaminos-sqlite]

- Original Message 
> From: Dennis Cote <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Wednesday, May 30, 2007 12:09:25 PM
> Subject: Re: [sqlite] sqlite internal structs don't make use of C bitfields?

> You may want to look at how the isInited field is used. You may be able 
> to combine it with the others as long as it stays in the first byte and 
> the code only checks for zero vs nonzero values on that byte (then again 
> that may not be safe if other combined bitfield are set nonzero before 
> the isInited field is set). If its safe, you could save another byte per 
> structure.

There seems to be some other removal of redundant fields:
u8 childPtrSize; /* 0 if leaf==1.  4 if leaf==0 */

implies that "leaf" can be replaced by "!childPtrSize", right? Well, on the 
bitfield version, it's only saving 1 bit (we can go the other way and replace 
childPtrSize by something like leaf?0:4 and save more space).
hdrOffset seems to be an other interesting subject as it seems to be the same 
kind of deal.

Nicolas

[sqlite] Database replication question

[spaminos-sqlite]

Hi all

I am trying to put in place a simple replication process to copy a database 
from one machine to an other.
The table I have is something like
CREATE TABLE sn2uid(sn VARCHAR(100) NOT NULL, uid INTEGER NOT NULL, PRIMARY KEY 
(sn));
CREATE INDEX uidindex on sn2uid ( uid )

Where the (sn,uid) pairs are pretty much random.

On my test data, I have around 3 million entries, the size on disk being about 
280 Mb
If I do a 'select * from sn2uid' > db, I get around 100Mb worth of data. I was 
thinking to simply stream the result from that query over tcp (http really), 
and do the inserts on the other side...

The problem I have is that, while doing this select takes about 10 seconds on 
my machine, I didn't find any quick way to insert quickly onto the other 
machine.

After a while, the db file size grows very very slowly, even when using 
transactions

My question is:

is there a way to do a select or a .dump so that when inserting the data on the 
other end, things will be faster? Or maybe there are some pragmas I can use 
that would improve performance?

To me, it seems that the reason things are slow is that even though I do the 
inserts in a transaction, the btrees are modified independently, and in that 
case randomly. If I was getting the data in the right order in terms of the 
btree, I think things could be significantly faster...

What I tried was to simply something like:
sqlite3 myorg.db '.dump sn2uid' > ~/sn2uid.txt
sqlite3 mynew.db < ~/sn2uid.txt

Would grouping inserts together by groups of 1 or so make things faster 
instead of one gigantic transaction? I am wondering in particular if the btree 
insert code is smart enough to build the tree and merge it into the main db 
file faster in that case?

Thanks!

Nicolas

Re: [sqlite] Database replication question

[spaminos-sqlite]

> - Original Message 
> From: Joe Wilson <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Monday, June 11, 2007 8:36:32 PM
> Subject: Re: [sqlite] Database replication question
> 
> 
> Large bulk inserts with more than one index (implicit or explicit) 
> is not SQLite's strong suit.
> 
> If you search the mailing list archives you'll find a few suggestions:
> 
> - "BEGIN EXCLUSIVE" (or is it "BEGIN IMMEDIATE"?) on the 
>   database file and then copy the file over - fastest way

What do you mean by "copy the file over"? A straight copy of the binary content 
of the file? If so, I can't really do that because the version of sqlite are 
potentially different on the two machines.

> 
> or
> 
> - increasing cache sizes 
> - pre-sorting the data in index order prior to bulk insert
> - creating the other indexes after all the data is inserted
> 
> If you do not require a live backup you could use the copy trick
> and augment that with a daily archive via 
> 
> sqlite3 file.db .dump | gzip etc...
> 
> in case the database file becomes corrupted.

If the performance problem is with the seconday index, is there a way to 
"pause" indexing before a large bulk insert and then "resume" it later without 
rebuilding the entire index (to avoid doing: drop index + inserts + create 
index)? Maybe it's a stupid question, but I am guessing that there is some sort 
of version number for the rows in the db, so playing "catchup" on an index 
could work?

Nicolas

Re: [sqlite] Capturing output from SQLlite with variables in a BASH script

[spaminos-sqlite]

- Original Message 
From: Martin Jenkins <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, June 22, 2007 2:00:45 PM
Subject: Re: [sqlite] Capturing output from SQLlite with variables in a BASH 
script


litenoob wrote:
> Hi, I'm wondering how to write a BASH script that will capture my SQLite
> output.
> 
> I can do it for a single line with something like this:
> 
> somevar=`sqlite3 dbfilename "SELECT name FROM tablename WHERE name='smith'
> LIMIT 1;"`
> 
> However, if I want to do anything with multiple lines, I haven't figured out
> a good way.  So far, I'm using a workaround by outputting to a file, then
> reading it after I exit the SQLite commandline, but that slows down the
> script significantly.
> 
> e.g.
> 
> sqlite3 dbfilename << EOF
> 
> .output temp1
> select id from tablename where name = "bush";
> .output temp2
> select id from tablename where name = "osama";
> 
> .quit
> EOF
> 
> read id1 < temp1
> read id2 < temp2
> 
> What's the better way to do this without actually writing to a file?
> 
> Thanks!

If you're using bash you can simply do something like:
sqlite3 dbfilename 'SELECT name FROM tablename WHERE name="smith"' | ( while 
read name ; do echo "--> $name" ; done )

You can actually put whatever you want within parenthesis (even more 
parenthised goodness).

That, or use a scripting language like perl or python :)

Nicolas

[sqlite] Get the data from previous months: please, help me to optimize the query...

[elemaco71-sqlite]

Hello, everybody

I have the following problem: I have the following tables:

Months
--
CREATE TABLE 'Months'
(
  IDMonth  INTEGER PRIMARY KEY NOT NULL,
  MonthRef INTEGER
);

(where MonthRef is the date of the first day of the month - created in the code)

CustomerData
--
CREATE TABLE 'CustomerData'
( 
IDCustomerData  INTEGER PRIMARY KEY NOT NULL,
IDMonth INTEGER,
NdgSingolo TEXT NOT NULL DEFAULT '0',
NdgCliente TEXT NOT NULL DEFAULT '0',
FatturatoNdg REAL DEFAULT 0 ,
FatturatoGruppo REAL DEFAULT 0  ,
MargineIntermediazioneLordo REAL DEFAULT 0  ,
MargineInteresse REAL DEFAULT 0 ,
MargineServizi REAL DEFAULT 0   ,
RaccoltaDirettaSM REAL DEFAULT 0,
RaccoltaIndirettaSM REAL DEFAULT 0  ,
ImpieghiSM REAL DEFAULT 0   ,
RaccoltaDirettaSP REAL DEFAULT 0
);

(where IDMonth is the foreign key to the Months table).

CustomerData contains the data of a single Customer (NdgSingolo), for the 
selected month ID.
What I need to do is to get "some" data in a record from the previous year, and 
from the end of the previous year. For instance, if the current month is March 
2007, then I need the data of March 2006, and of December 2006.
To accomplish this, I created these two views:

_VCustDataMonths
--
CREATE VIEW _VCustDataMonths AS
SELECT * FROM CustomerData A LEFT OUTER JOIN Months B ON A.IDMonth = B.IDMonth;

_VCustomerData_1
--
CREATE VIEW _VCustomerData_1 AS
SELECT AC.*,
   M1.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_m1,
   AP.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_ap,
   M1.MargineInteresseAS MargineInteresse_m1,
   AP.MargineInteresseAS MargineInteresse_ap,
FROM _VCustDataMonths AC
 LEFT OUTER JOIN _VCustDataMonths M1 ON AC.NdgSingolo = M1.NdgSingolo AND 
AC.NdgCliente = M1.NdgCliente AND M1.MonthRef = date( AC.MonthRef, '-1 year' )
 LEFT OUTER JOIN _VCustDataMonths AP ON AC.NdgSingolo = AP.NdgSingolo AND 
AC.NdgCliente = AP.NdgCliente AND AP.MonthRef = date( AC.MonthRef, 'start of 
year', '-1 month' );

Now, the query _VCustomerData_1 (that is the one that I need) takes *145,23 
seconds* to run!! (with about 4000 records in the CustomerData table). This is 
really too much...

I have indexes in the Months and CustomerData tables for the fields NdgSingolo 
and NdgCliente...

How could I increase the performance of this query to get reasonable results??

Any help and idea is greatly appreciated
Thanks in advance,
Marco.





  ___ 
L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: 
http://it.docs.yahoo.com/nowyoucan.html

Re: [sqlite] Problem with SQLite FastCGI module "malformed database schema"

[spaminos-sqlite]

> 
> - Original Message 
> From: Zbigniew Baniewski <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Thursday, July 26, 2007 4:12:00 PM
> Subject: Re: [sqlite] Problem with SQLite FastCGI module "malformed database 
> schema"
> 
> 
> On Thu, Jul 26, 2007 at 12:59:45PM -0700, Joe Wilson wrote:
> 
> > /var on my machine is drwxr-xr-x, and I don't have any issues with sqlite 
> > reading and writing temp files to /var/tmp/. Even if the permissions of /var
> > were d--x--x--x, it would also be fine. As long as /var/tmp is rwx for
> > the sqlite process, it will work.
> > 
> > Just make sure the the first accessible temp dir has enough disk space
> > for the temporary files.
> 
> Of course. It's a little 5 GB HDD - but filled only to 1/3 of it's capacity.
> There's just one big partition (beside swap).
> 
> The problem is, that the error message actually gives not any clue. It just
> tells, that "there was problem while truing to open temporary database file"
> - but there (considering the above) shouldn't be any problem. /var/tmp has
> rwxrwxrwx privileges. Even worse: it's working without any problems most of
> the time - and then, after f.e. a week, it refuses to work any further,
> without any particular reason. It did open that temporary files 100 times
> before - and it can't open it at 101. time.
> 

If it works and then later stops working, it's much more likely that you're 
having a problem with some file descriptor that is not closed properly (on the 
temp db most likely).
If it dies after let's say a week, you can check after a few days using lsof if 
the process is keeping files open in /tmp

Good luck

Nicolas

Re: [sqlite] 3.5.0 alpha TryEnterCriticalSection linker error

[spaminos-sqlite]

- Original Message 
> From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Tuesday, September 4, 2007 3:32:38 PM
> Subject: Re: [sqlite] 3.5.0 alpha TryEnterCriticalSection linker error
> 
> 
> =?ISO-8859-1?Q?Daniel_=D6nnerby?= <[EMAIL PROTECTED]> wrote:
> > Hi all!
> > 
> > Tried upgrading to the 3.5.0 alpha from 3.4.2 using VS 2005 on XP. I 
> > downloaded the ZIP with preprocessed C code.
> > Compiling SQLite to a .lib was no problem, but when linking it to an 
> > ..exe I got the following:
> > sqlite.lib(mutex_w32.obj) : error LNK2019: unresolved external symbol 
> > _TryEnterCriticalSection referenced in function _sqlite3_mutex_try
> > 
> > I made some attempt to fix it checking all kind of possible errors on my 
> > side (defines, compiler/linker settings etc) without any luck.
> > Anyone got the same error?
> > BTW. SQLite 3.4.2 works just fine.
> > 
> > This is not a big deal for me to solve, just thought I share with the 
> > development team.
> > 
> 
> http://www.sqlite.org/cvstrac/chngview?cn=4399
> 
> --
> D. Richard Hipp [EMAIL PROTECTED]
 
Isn't it time to drop the Win9X support from the default build?

I'm thinking that any optimization should be enabled for the majority of users. 
Or if it's not really an optimization, why keeping it in the code then?

If some people still need to compile for legacy OSes, they can always grab the 
source and compile without those optimizations.

An alternative is to call this function when available using "GetProcAddress" 
(this is the case for a lot of other modern calls that cannot be done right 
now).
 
Just my 2c
 
Nicolas

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

[sqlite] Trigger execution sequence/order

[freshie2004-sqlite]

Hi All,

I was wondering if there was a definite way of determining what sequence or 
order that triggers are executed in sqlite3. Have searched this list and the 
internet and the only thing I have found suggested that triggers are executed 
in alphabetical order, which is wrong.

The following SQL:

CREATE TABLE Test(s TEXT);
CREATE TABLE Log(s TEXT);
CREATE TRIGGER btest_1 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_1');
END;
CREATE TRIGGER btest_3 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_3');
END;
CREATE TRIGGER btest_2 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_2');
END;
CREATE TRIGGER atest_1 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_1');
END;
CREATE TRIGGER atest_3 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_3');
END;
CREATE TRIGGER atest_2 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_2');
END;
INSERT INTO Test(s) VALUES('Test');
SELECT rowid,* FROM log;

Returns the following on both windows (3.6.14) and linux (3.4.2):

1|btest_2
2|btest_3
3|btest_1
4|atest_2
5|atest_3
6|atest_1

So sqlite seems to run triggers LIFO. However, there seems to be no 
specification for this.

Cheers!


  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] first few characters of varchar() corrupted when SELECTing from a C++ program?

[freshie2004-sqlite]

(Replying to pierr as I joined the list after uralmazamog sent original email)


uralmazamog,

The code is incomplete. You are not showing us how you are determining what 
testValue points to.

What is returned if you use the following?

sqlite3_open_v2( "testdat", &sqlDB, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE, NULL );
sqlite3_prepare_v2( sqlDB, "SELECT b FROM whee WHERE a='bing';", 
-1,&sqlStat, NULL );
sqlite3_step( sqlStat );
const unsigned char *testValue = sqlite3_column_text( sqlStat, 0 );
printf("testValue=(%s)\n");

Cheers!





____
From: pierr 
To: sqlite-users@sqlite.org
Sent: Tuesday, 30 June, 2009 2:42:16 PM
Subject: Re: [sqlite] first few characters of varchar() corrupted when 
SELECTing from a C++ program?




uralmazamog wrote:
> 
> Greetings,
> 
> maybe it's just me being stupid, I'll best jump right to the code:
> 
> sqlite3_open_v2( "testdat", &sqlDB, SQLITE_OPEN_READWRITE |
> SQLITE_OPEN_CREATE, NULL );
> sqlite3_prepare_v2( sqlDB, "SELECT b FROM whee WHERE a='bing';", -1,
> &sqlStat, NULL );
> sqlite3_step( sqlStat );
> const unsigned char *testValue = sqlite3_column_text( sqlStat, 0 );
> 
> both a and b are varchar(20)s
> 
> calling the query from the command-line tool returns the proper result
> "bang", however, running this code the value testValue shows up as ""
> for longer strings only the first four characters are corrupted, and the
> rest reads okay, what am I doing wrong?
> 
> 
Try this:
char testValue[20];
memcpy(testValue,sqlite3_column_text(sqlStat,0),sqlite3_column_bytes(sqlStat,0));


-- 
View this message in context: 
http://www.nabble.com/first-few-characters-of-varchar%28%29-corrupted-when-SELECTing-from-a-C%2B%2B-program--tp24237176p24266020.html
Sent from the SQLite mailing list archive at Nabble.com.

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



  
____
Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger execution sequence/order

[freshie2004-sqlite]

I should have looked harder...

"The order of arbitrary."
http://www.mail-archive.com/sqlite-users@sqlite.org/msg17641.html





From: "freshie2004-sql...@yahoo.com.au" 
To: sqlite-users@sqlite.org
Sent: Tuesday, 30 June, 2009 2:31:40 PM
Subject: [sqlite] Trigger execution sequence/order

Hi All,

I was wondering if there was a definite way of determining what sequence or 
order that triggers are executed in sqlite3. Have searched this list and the 
internet and the only thing I have found suggested that triggers are executed 
in alphabetical order, which is wrong.

The following SQL:

CREATE TABLE Test(s TEXT);
CREATE TABLE Log(s TEXT);
CREATE TRIGGER btest_1 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_1');
END;
CREATE TRIGGER btest_3 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_3');
END;
CREATE TRIGGER btest_2 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_2');
END;
CREATE TRIGGER atest_1 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_1');
END;
CREATE TRIGGER atest_3 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_3');
END;
CREATE TRIGGER atest_2 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_2');
END;
INSERT INTO Test(s) VALUES('Test');
SELECT rowid,* FROM log;

Returns the following on both windows (3.6.14) and linux (3.4.2):

1|btest_2
2|btest_3
3|btest_1
4|atest_2
5|atest_3
6|atest_1

So sqlite seems to run triggers LIFO. However, there seems to be no 
specification for this.

Cheers!


  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] first few characters of varchar() corrupted when SELECTing from a C++ program?

[freshie2004-sqlite]

(embarrassed)

printf("testValue=(%s)\n", testValue);





From: John Machin 
To: General Discussion of SQLite Database 
Sent: Tuesday, 30 June, 2009 4:51:09 PM
Subject: Re: [sqlite] first few characters of varchar() corrupted when 
SELECTing from a C++ program?

On 30/06/2009 2:56 PM, freshie2004-sql...@yahoo.com.au wrote:

> printf("testValue=(%s)\n");

I've always been afraid to use those new-fangled mind-reading C 
compilers lest they were easily shocked ;-)

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



  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to do 5,000,000 "select"s as fast as possible

[freshie2004-sqlite]

Briefly...

sqlite_prepare_v2(db, "select geneName,exonStart,exonEnd from refGene where 
chromo=? and txStart <=? and txEnd>=?" ... etc etc)

start loop of 500 records

Use bindings to assign the parameters.

step through it

Reset and clear bindings.

end loop

sqlite3_finalize();

See sqlite_bind_[](), sqlite_reset(), sqlite_clear_bindings() in manual






From: knightfeng 
To: sqlite-users 
Sent: Friday, 3 July, 2009 5:02:57 PM
Subject: [sqlite] How to do 5,000,000 "select"s as fast as possible

Dear all,
  We have to do 5,000,000 "select"s from a database with 4 record 
(using C API). We do it as follow:
1. "create table refGene (geneName vchar, geneID vchar, chromo vchar, strand 
char(1), txStart number, txEnd number, cdsStart number, cdsEnd number, exonNum 
number, exonStart vchar, exonEnd vchar)"

2.  insert   4 records.

3.  rc = sqlite3_exec(db, "create index indexwig on refGene (chromo, txStart, 
txEnd)" , NULL , NULL, &zErrMsg);

4. repeat 5,000,000  {
   sprintf(sqlCmd, "select geneName,exonStart,exonEnd from refGene where 
chromo=='%s' and txStart <= %d and txEnd>=%d", one.chromo.c_str(), 
one.start, one.end);

   rc = sqlite3_prepare(db, sqlCmd, strlen(sqlCmd), &stmt, NULL);
   rc = sqlite3_step(stmt);
   while(rc == SQLITE_ROW)
  {  
..
rc = sqlite3_step(stmt);
   }
rc = sqlite3_finalize(stmt);
}

The 5,000,000 "select"s take about 30 minutes in our machine (3Gb memory, 2 x 
1.8G Hz CPU). 

Are there some faster ways to use sqlite to do the 5,000,000 "select"s ?

Thanks

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



  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite-undo: loadable extension to give undo/redo functionality

[freshie2004-sqlite]

Hi All,

As part of a project I am toying with writing I
needed undo/redo functionality, so have ended up writing a loadable
extension for sqlite which implements undo/redo functionality entirely
within the database using custom functions. Kind-of a C implementation
of http://www.sqlite.org/cvstrac/wiki?p=UndoRedo.

Only tested on Linux, so far.

Anyhoo... have fun if you are interested.

http://sourceforge.net/projects/sqlite-undo/

Cheers!


  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem with SQLITE_BUSY

[freshie2004-sqlite]

What about using only one connection and the ATTACH statement:

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

Also, see the select-stmt form of the INSERT statement:

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

Something like...

sqlite3_open database B

ATTACH DATABASE A.db AS dbA

BEGIN


INSERT INTO main.mytable(col1,...colN) SELECT col1,...colN FROM dbA.myothertable

COMMIT

DETACH dbA


sqlite3_close B.db

Cheers!




From: Wenton Thomas 
To: sqlite-users@sqlite.org
Sent: Saturday, 4 July, 2009 7:31:55 PM
Subject: [sqlite] problem  with SQLITE_BUSY

Now in my system I used sqlite  to manage  2  database file A.db and B.db,  and 
each has a connection handle cA, cB.
My operation perform like this:


sqlite3_exec( select records from cA)
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");

All  return value is normal.,but when  I  execute
rc = sqlite3_close(), 
return value rc always be SQLITE_BUSY.

Could anyone help me?

Does the  two database connection disturb each other?
I means, if  there exist a  reading lock on cA, can I write cB?


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



  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS3 and negation operator

[cscs-sqlite]

Did an FTS3 update change how many negation operators (dash/-) can be used in
a
match statement?

For example, in sqlite3.dll version 3.5.7:

colname match 'tetons -bend -jackson -oxbow* -parks' works as expected; bend,
jackson, oxbow* and parks are all removed from the results.

but

With, sqlite3.dll version 3.6.16:

colname match 'tetons -bend -jackson -oxbow* -parks' no longer works
correctly. It acts like only the last negated token is being used and results
have bend, jackson and oxbow* tokens in them.

I used SQLite Expert to test this on the same sqlite database. The DLL being
used was different however.

Should multiple negation be possible? If so, how is that accomplished when
using the new DLL?

Also, can somone point me to updated docs on how to use the new options if
SQLITE_ENABLE_FTS3_PARENTHESIS is set at compile time?


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

Re: [sqlite] FTS3 and negation operator

[cscs-sqlite]

Dan, thank you for the additional information, it's most helpful.  I'll let
you know what I find out.

BTW, any reason that doc is not on the SQLite Web site?  It would help a lot
of people understand the old and new query syntax better.

Craig

> >
> > Did an FTS3 update change how many negation operators (dash/-) can  
> > be used in
> > a
> > match statement?
> >
> > For example, in sqlite3.dll version 3.5.7:
> >
> > colname match 'tetons -bend -jackson -oxbow* -parks' works as  
> > expected; bend,
> > jackson, oxbow* and parks are all removed from the results.
> >
> > but
> >
> > With, sqlite3.dll version 3.6.16:
> >
> > colname match 'tetons -bend -jackson -oxbow* -parks' no longer works
> > correctly. It acts like only the last negated token is being used  
> > and results
> > have bend, jackson and oxbow* tokens in them.
> 
> Please try 3.6.17. I think this problem was fixed here:
> 
>http://www.sqlite.org/src/vdiff/27971
> 
> > Also, can somone point me to updated docs on how to use the new  
> > options if
> > SQLITE_ENABLE_FTS3_PARENTHESIS is set at compile time?
> 
>http://www.sqlite.org/src/annotate?mid=25265&fnid=373
> 
> Dan.




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

Re: [sqlite] FTS3 and negation operator

[cscs-sqlite]

Dan, updating to sqlite3.dll version 3.6.17 did fix the problem.

Also, I should clarify what I meant by having the query syntax doc on the
sqlite web site.  It is indeed on the web site, but I spent a lot of time
yesterday trying to find a document just like it and couldn't.  It just needs
to be in a more accessible spot, perhaps under the documents area?

Thanks again for the help,
Craig

> 
> Dan, thank you for the additional information, it's most helpful.  I'll let
> you know what I find out.
> 
> BTW, any reason that doc is not on the SQLite Web site?  It would help a
lot
> of people understand the old and new query syntax better.
> 
> Craig
> 
> > >
> > > Did an FTS3 update change how many negation operators (dash/-) can  
> > > be used in
> > > a
> > > match statement?
> > >
> > > For example, in sqlite3.dll version 3.5.7:
> > >
> > > colname match 'tetons -bend -jackson -oxbow* -parks' works as  
> > > expected; bend,
> > > jackson, oxbow* and parks are all removed from the results.
> > >
> > > but
> > >
> > > With, sqlite3.dll version 3.6.16:
> > >
> > > colname match 'tetons -bend -jackson -oxbow* -parks' no longer works
> > > correctly. It acts like only the last negated token is being used  
> > > and results
> > > have bend, jackson and oxbow* tokens in them.
> > 
> > Please try 3.6.17. I think this problem was fixed here:
> > 
> >http://www.sqlite.org/src/vdiff/27971
> > 
> > > Also, can somone point me to updated docs on how to use the new  
> > > options if
> > > SQLITE_ENABLE_FTS3_PARENTHESIS is set at compile time?
> > 
> >http://www.sqlite.org/src/annotate?mid=25265&fnid=373
> > 
> > Dan.
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



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

Re: [sqlite] Conditional triggers

[cscs-sqlite]

 
 
I sent this last night, but it didn't post on the list. Trying again to make
sure this is working.
   
   
-- Original Message --
Received: 08:40 PM MDT, 08/27/2009
From: cscs-sql...@usa.net
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Conditional triggers
   
   
Yes, you should be able to do this but I think your conditions need to be:
   
when new.TypeID = 1
   
and
   
when new.TypeID = 2
   
I'm sure you saw this, but note the WHEN clause and text in the document
concerning when you can reference new and old values for the columns.
   
http://www.sqlite.org/lang_createtrigger.html
   
   
   
-- Original Message --
Received: 08:27 PM MDT, 08/27/2009
From: Dennis Volodomanov 
    To: "sqlite-users@sqlite.org" 
Subject: [sqlite] Conditional triggers
   
   
   
   
Hello all,
   
Is it possible to create such an AFTER INSERT trigger
that updates certain fields in a table based on the actual data being
inserted?
   
Let's say:
   
CREATE TABLE abc(TypeID INTEGER)
CREATE TABLE abcCount(TypeCountA, TypeCountB)
   
CREATE TRIGGER CountTypeA AFTER INSERT ON abc /* when
abc.TypeID == 1 */ BEGIN
UPDATE abcCount SET TypeCountA=TypeCountA+1; END
   
CREATE TRIGGER CountTypeB AFTER INSERT ON abc /* when
abc.TypeID == 2 */ BEGIN
UPDATE abcCount SET TypeCountB=TypeCountB+1; END
   
Is something like that possible? I couldn't find any
syntax construct I could put in place of the comment.
   
Thanks in advance,
   
Dennis
   
_______
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] Re: Threads

[spaminos-sqlite]

- Original Message 
> From: John Stanton <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Wednesday, November 14, 2007 11:43:48 AM
> Subject: Re: [sqlite] Re: Threads
> 
> If you machine has a single disk it fundamentally does not have parallel 
> I/O.  If you have a machine with multiple dik spindles and multiple 
> channels then you can have parallel access.  Multiple Sqlite databases 
> residing on the same disk are accessed sequentially because the access 
> depends upon the disk head positioning.

> If you have a mutliple processor machine or a multiple core processor 
> then you have some parallel computing ability.


It can be added that while disks can only perform one operation at a time, 
modern disks have NCQ capabilities that enable them to reduce seek times by 
using an elevator algorithm for example.
The OS is also performing some optimizations when queuing up several I/O 
requests to the same device.
 
So yeah it's possible to increase throughput (and keeping latency in check) by 
running several queries in parallel on the same db (especially if in a scenario 
that involves a large majority of read access) that resides in one file on one 
disk.
 
Also, like you mentioned, the CPU cost of performing one query is not 
negligible (especially when performing complex queries that can use quite a lot 
of CPU), so if the host has multiple cores, things will get a little quicker.
 
Nicolas

[���۟�][sqlite] Pysqlite issue no attribute 'autocommit'

[sqlite-users]

수신자가 sqlite-users@sqlite.org 로부터 오는 메일의 수신을 거부하였습니다.


_
--- Begin Message ---
Hi there,

  Im trying to run a Python based program which uses MySQL with python-sqlite 
and Im recieving this error,

'Connection' object has no attribute 'autocommit'

I´ve had a google for this and its seems like it may be a bug python-sqlite or 
sqlite bug , but also I tried searching 
for it on the python issue traker and didnt find anything. Is anyone else aware 
of this issue and any solution?

thanks for any help! Andy.

PS sorry if I didnt include much info, hoping its a known issue (and also I 
didnt write the code, so not sure what
else to include off the top of my head! :P)___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--- End Message ---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pysqlite issue no attribute 'autocommit'

[sqlite-users]

Hi there,

  Im trying to run a Python based program which uses MySQL with python-sqlite 
and Im recieving this error,

'Connection' object has no attribute 'autocommit'

I´ve had a google for this and its seems like it may be a bug python-sqlite or 
sqlite bug , but also I tried searching 
for it on the python issue traker and didnt find anything. Is anyone else aware 
of this issue and any solution?

thanks for any help! Andy.

PS sorry if I didnt include much info, hoping its a known issue (and also I 
didnt write the code, so not sure what
else to include off the top of my head! :P)___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Vacuum

[sqlite-users]

Hi all, 

Hopefully you can help, while running sqlite version 3.5.5 

Two seperate threads each attempt to run a vacuum command against the same db. 
Each thread has an independent connnection to the db. 

One thread succeeds and the other gets a return code of 1 (SQLITE_ERROR) from 
sqlite3_step. 

The sqlite3_error msg generated is "SQL logic error or missing database". The 
Vacuum command is executed using the sqlite3_prepare_v2 interface. 

This only seems to occur when the database is actually vacuumed by the first 
thread. The second thread then gets this error.

Thanks for any help.
Ken



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

[���۟�][sqlite] Vacuum

[sqlite-users]

수신자가 sqlite-users@sqlite.org 로부터 오는 메일의 수신을 거부하였습니다.


_
--- Begin Message ---

Hi all, 

Hopefully you can help, while running sqlite version 3.5.5 

Two seperate threads each attempt to run a vacuum command against the same db. 
Each thread has an independent connnection to the db. 

One thread succeeds and the other gets a return code of 1 (SQLITE_ERROR) from 
sqlite3_step. 

The sqlite3_error msg generated is "SQL logic error or missing database". The 
Vacuum command is executed using the sqlite3_prepare_v2 interface. 

This only seems to occur when the database is actually vacuumed by the first 
thread. The second thread then gets this error.

Thanks for any help.
Ken



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

[���۟�]Re: [sqlite] Vacuum

[sqlite-users]

수신자가 sqlite-users@sqlite.org 로부터 오는 메일의 수신을 거부하였습니다.


_
--- Begin Message ---
sqlite-users@sqlite.org wrote:
> Hi all, 
> 
> Hopefully you can help, while running sqlite version 3.5.5 
> 
> Two seperate threads each attempt to run a vacuum command against the same 
> db. Each thread has an independent connnection to the db. 
> 
> One thread succeeds and the other gets a return code of 1 (SQLITE_ERROR) from 
> sqlite3_step. 
> 
> The sqlite3_error msg generated is "SQL logic error or missing database". The 
> Vacuum command is executed using the sqlite3_prepare_v2 interface. 
> 
> This only seems to occur when the database is actually vacuumed by the first 
> thread. The second thread then gets this error.
> 
> Thanks for any help.

Are you concerned that you are unable to do two vacuums
simulataneously, or are you asking that the error message
returned be adjusted to be more lucid?

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] Vacuum

[sqlite-users]

sqlite-users@sqlite.org wrote:
> Hi all, 
> 
> Hopefully you can help, while running sqlite version 3.5.5 
> 
> Two seperate threads each attempt to run a vacuum command against the same 
> db. Each thread has an independent connnection to the db. 
> 
> One thread succeeds and the other gets a return code of 1 (SQLITE_ERROR) from 
> sqlite3_step. 
> 
> The sqlite3_error msg generated is "SQL logic error or missing database". The 
> Vacuum command is executed using the sqlite3_prepare_v2 interface. 
> 
> This only seems to occur when the database is actually vacuumed by the first 
> thread. The second thread then gets this error.
> 
> Thanks for any help.

Are you concerned that you are unable to do two vacuums
simulataneously, or are you asking that the error message
returned be adjusted to be more lucid?

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

[sqlite] Queries within loadable extensions

[en94rd3-sqlite]

Hi All,

I have been playing around with loadable extensions and am calling some C code 
to do batch processing from database triggers like so:

SQL:

CREATE TRIGGER mytrigger AFTER UPDATE OF myfield ON mytable
BEGIN
[do some stuff]
INSERT INTO batch_table (id) SELECT ... [ids of rows which need processing];
SELECT myfunc(); /* Do batch processing */
END

C extension:

static sqlite3 *_db;

static int odl_recalc_deco_cb(void *unused, int argc, char **argv, char 
**azColName)
{
[batch functionality]
return 0;
}

static void myfunc(sqlite3_context *context, int argc, sqlite3_value **arg)
{
sqlite3_exec(_db, "SELECT id FROM batch_table;DELETE FROM batch_table", 
myfunc_cb, NULL, NULL);
}

int sqlite3_extension_init(sqlite3 *db, char **pzErrMsg, const 
sqlite3_api_routines *pApi)
{
SQLITE_EXTENSION_INIT2(pApi)
_db = db;
sqlite3_create_function(db, "myfunc", 2, SQLITE_ANY, 0, myfunc, 0, 0);
return 0;
}

Is there anything wrong with doing the above?  The fact that the a sqlite3* is 
not provided to automatically to the custom function (myfunc) makes me think I 
should not be doing anything like this. However, it does work.

Thanks, Si.



   
-
Get the name you always wanted with the new y7mail email address.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] questions about performance

[spaminos-sqlite]

<[EMAIL PROTECTED]> wrote:

> The problem (I suspect) is that you have an index on Table3.
> As you insert to records into Table3, those record go at the
> end, which is very efficient.  But the index entries have to
> be inserted in index order, which means they get scattered
> out all through the index.  This works fine as long as the
> index is small enough to fit in cache (either SQLite's private
> cache, or failing that your operating systems disk cache.)
> Once the index gets too big to fit in cache, you tend to
> start thrashing.
>
> The problem is a lack of locality of reference in the index.
> Each index insertion requires O(logN) disk reads and writes.
> This is no big deal as long as a "disk read and write" is
> serviced from cache but gets to be a very big deal when it
> actually involves real disk I/O.  You start to get real disk
> I/O when the index loses locality of reference and exceeds
> the size of your cache.  I do not know how to fix this and 
> still make the indices useful.

If you remember I had this problem with my app, the work around I found is to 
read the whole DB on startup (well, the one used for indexing my users) to help 
the OS cache it better (for 10M users it's about 600 megs, so it's still 
reasonable).
Now as a separate file for indexes/primary keys is not an option (even though, 
I still think it would be the easiest solution, that's how mysql does it, I 
don't know about postgresql), an alternative would be to change the layout in 
the file so that indexes/primary keys are stored together in the file and the 
rest of the rows somewhere else in the file (the file could grow by having an 
alternation of index/key data and other data).
At first this might seem slower, but in practice it won't (well, it might just 
be worth a try):
the area of the file that contains indexes/primary keys will be accessed all 
the time and thus cached by the OS while the rest of the data will be less 
cached and discarded from memory.
So even though it looks like 2 seeks and 2 reads (or writes) would be needed 
for every row, in practice, the operations on the index/key part will be from 
cache and will be faster (and when writing using transactions, the added seeks 
should not really increase the time by much).

Nicolas

Re: [sqlite] SQLite :memory: performance difference between v2 and v3?

[spaminos-sqlite]

- Original Message 
From: Jay Sprenkle <[EMAIL PROTECTED]>
On 5/1/06, Ivan Voras <[EMAIL PROTECTED]> wrote:
> > I've spent a long time debugging low performance of an
> > application that uses :memory: database and have found
> > that sqlite v2 is much faster than v3. After some
> > digging around it seems that even two proof-of-concept
> > programs that are identical except for used SQLite
> > version reproduce this behaviour just fine:

> Testing is difficult to do correctly. As several people noted on this list 
> just
> today the first time they ran a query it had much different
> performance than subsequent
> runs of the query. Did you run these tests more than one time? What's your
> environment?

The precaching trick desribed earlier can not be done on memory databases as 
they are already... in memory.

The main reason first queries (sometimes it's more like the first few hundred 
queries if the db is big) are significantly slower are because of the way 
sqlite relies blindly on the OS caching mechanism for caching the 
indexes/primary keys in memory.

In any case, when doing any kind of benchmarking that involves disk access, you 
must clear the OS disk cache so that the algorithm used by the OS is removed 
from the equation (and also to be able to compare results), otherwise all 
you're doing is benchmarking a moving target.

To discard the disk caches:
on linux: easy, just umount and mount the partition that contains the db file
on windows: I don't know of any other way than clearing the whole cache with a 
tool such as Clearmem.exe (from the Windows Server 2003 Resource Kit, but you 
can find it on the web very easily)

Hope this helps.

Nicolas

[sqlite] SQLite disk performance

[spaminos-sqlite]

Hi all

As discussed before, I have performance issues when using sqlite on big (multi 
gig) databases.
I am still trying to use sqlite as the main db because it simplifies deployment 
so much.

The main reason seems to be that inserting in the btree is very slow (even 
using transactions) because a lot of data has to be moved around in the file.

Would using sparse files, or at least leave some space between group of nodes, 
help, so that the need to move data around in the file is not needed as much?.

Maybe that could be a pragma of some sort (so that people that still want the 
most compact db file won't suffer)?

Even better, maybe it's a setting I didn't see :)

An other concern I have is row size: the average data in our data contains a 
blob of about 3 kb. My understanding is that in that case, the first kilobyte 
(primary key included) is stored in the b-tree node and the rest somewhere else 
in the file.
My question now is: is there some sort of fragmentation inside the dbfile 
happenning with this extra data?

Nicolas

[sqlite] Group a set of events by day: howto??

[elemaco71-sqlite]

Hello, everybody

I have the following problem: I have a table that contains "Events", with the 
related date and time:

Fields:

EventID
EventDate
EventTime

I would like to "group" these records by day, returning all the "times" of the 
events, like:

EventID, EventDate, EventTime1, EventTime2, EventTime3

(I can assume that no more than 3 events happen on the same day).
I did this query:

SELECT T1.EventDate, T1.EventTime AS Time1, T2.EventTime AS Time2 
FROM Events AS T1 LEFT JOIN Events AS T2 ON T2.EventDate = T1.EventDate AND 
T2.EventTime > T1.EventTime;

This query "works", but it has the following problems: 

1. it returns several times the same "record" (can't understand why)
2. it takes about 30 seconds (!!) to run, and it consumes all the physical 
memory of the system...

Any help on this?
Thanks in advance for any reply.

Kind regards
Marco
 Chiacchiera con i tuoi amici in tempo reale! 
 http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com 

Re: [sqlite] Group a set of events by day: howto??

[elemaco71-sqlite]

Hello, and thanks for the reply.

I dont' know PHP, but I can understand that you are suggesting to "manually" 
iterate the records to find the events, and group them by day. Is this right?


"C.Peachment" <[EMAIL PROTECTED]> ha scritto:  What is wrong with using:

select EventID, EventDate, EventTime
 order by EventDate, EventTime
 group by EventDate;

You have a separate display problem - you want
to put up to three records on the same line. This is
a language and application specific problem. In PHP,
using the PDO module and producing output for a
web page, it can be solved with something like:

$SqlText = "select EventID, EventDate, EventTime " .
   " order by EventDate, EventTime " .
   " group by EventDate";

$Stmt = $dbh->prepare($SqlText);
$Stmt->execute();

$Found = false;
$PriorDate = 0;
while ($Row = $Stmt->fetch(PDO::FETCH_OBJ)) {
  $Found = true;
  if ($Row->EventDate != $PriorDate) {
if ($PriorDate != 0) {
   echo "
\n";
}
echo "$Row->EventID, $Row->EventDate";
  }
  echo "$Row->EventTime";
  $PriorDate = $Row->EventDate;

if ($Found) {
  echo "
\n"; // close off last output statement

$Stmt->closeCursor();


On Mon, 10 Jul 2006 11:45:24 +0200 (CEST), [EMAIL PROTECTED] wrote:

>Hello, everybody

>I have the following problem: I have a table that contains "Events", with the 
>related date and time:

>Fields:

>EventID
>EventDate
>EventTime

>I would like to "group" these records by day, returning all the "times" of the 
>events, like:

>EventID, EventDate, EventTime1, EventTime2, EventTime3

>(I can assume that no more than 3 events happen on the same day).
>I did this query:

>SELECT T1.EventDate, T1.EventTime AS Time1, T2.EventTime AS Time2 
>FROM Events AS T1 LEFT JOIN Events AS T2 ON T2.EventDate = T1.EventDate AND 
>T2.EventTime > T1.EventTime;

>This query "works", but it has the following problems: 

>1. it returns several times the same "record" (can't understand why)
>2. it takes about 30 seconds (!!) to run, and it consumes all the physical 
>memory of the system...

>Any help on this?
>Thanks in advance for any reply.

>Kind regards
>Marco
> Chiacchiera con i tuoi amici in tempo reale! 
> http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com 





 Chiacchiera con i tuoi amici in tempo reale! 
 http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com 

[sqlite] Use "computed" fields to get the value of other fields.

[elemaco71-sqlite]

  Hello, everybody

I'm using a SQL clause to get data from the DB, and I need to use "computed" 
fields to get the value of other fields. A (stupid) example of what I need is 
this:

SELECT IDContractLevel, IDContractLevel > 4 as IAmBoss, (SELECT * FROM Bosses 
WHERE Flag = IAmBoss) FROM Employees;

What I get is an error indicating:

"SQLite error 1 - no such column: IAmBoss"

How can I solve this? Any help is appreciated

Thanks and kind regards,
Marco 
 Chiacchiera con i tuoi amici in tempo reale! 
 http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com 

Re: [sqlite] Use "computed" fields to get the value of other fields.

[elemaco71-sqlite]

Hello, and thanks for the reply.

SELECT *
 from Employees
where IDContractLevel > 4

is not enough, since I actually need to use a field that is created inside the 
SQL statement to create another field. The problem is that it seems that the 
computed fields is not existing yet when it is used for another field.

The example I posted was only a "stupid" example of what I mean, just to 
clarify the situation. Another example would be this:

SELECT TotalInvoice, (SELECT SUM(PaymentValue) FROM Payments WHERE 
Payments.IDInvoice = IDInvoice) AS TotalPaid, TotalPaid = TotalInvoice AS 
FullyPaid FROM Invoices;

Here, I select:
- TotalInvoice the total amount of the invoice
- TotalPaid the total amount paid till now
- FullyPaid a boolean flag indicating if the invoice is paid or not

the problem is that I get an error "No such column: TotalPaid"

Thanks again
Marco

Jay Sprenkle <[EMAIL PROTECTED]> ha scritto:  On 7/17/06, [EMAIL PROTECTED]  
wrote:
>   Hello, everybody
>
> I'm using a SQL clause to get data from the DB, and I need to use "computed" 
> fields to get the value of other fields. A (stupid) example of what I need is 
> this:
>
> SELECT IDContractLevel, IDContractLevel > 4 as IAmBoss, (SELECT * FROM Bosses 
> WHERE Flag = IAmBoss) FROM Employees;
>
> What I get is an error indicating:
>
> "SQLite error 1 - no such column: IAmBoss"

Could you post the definition of the table Employees?

I wasn't sure if you could put boolean expressions in the select
but it appears to work fine:

SQLite version 3.0.8
Enter ".help" for instructions
sqlite> select 1>4;
0
sqlite> select 6>4;
1
sqlite>

sub selects seem to work fine:

sqlite> select (select 2);
2
sqlite>

I suspect you really want something like this?

SELECT *
 from Employees
where IDContractLevel > 4
;



--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


 Chiacchiera con i tuoi amici in tempo reale! 
 http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com 

Re: [sqlite] Insert statement taking too long

[spaminos-sqlite]

- Original Message 
From: Unit 5 <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 9, 2006 8:02:51 AM
Subject: RE: [sqlite] Insert statement taking too long


> --- Robert Simpson <[EMAIL PROTECTED]> wrote:
> > You need to create an index on the columns you're
> > joining.  Otherwise I
> > believe 100,000,000 rows (10k x 10k) in table 2 will
> > be scanned while SQLite
> > looks for matches against the 10,000 rows in table
> > 1.

> While that makes sense, I suspect there is something
> else going on.  
> 
> I did a few more tests.  For example, if I remove the
> INSERT but keep the exact same SELECT statement with
> the joins, it is fast again.  So, it seems that it is
> quite slow when doing the insert's.  I was thinking
> that perhaps the statement was not in a transaction,
> but I tried that too.

Could it just be that your data set is just too big and doesn't fit in memory? 
Your statement most likely results in random inserts in the target table.
 
Talking about this, is there a way to tell sqlite to put "holes" in the file so 
that when doing random inserts (even in a transaction), only portions of the 
file need to be moved around?
 
It would waste some disk space, but for improved performance (it's a 
trade-off), I would be willing to give away large amount of disk. I know this 
is quite the opposite of what (auto) vacuum does but when data doesn't fit in 
memory and most access is random there is not much performance benefit in 
having the data not sparse in the DB file. The "holes" could be recreated from 
time to time to ensure the sparseness of the db file (hence giving a guaranty 
on insert times).
 
Nicolas

Re: [sqlite] indexes in memory

[spaminos-sqlite]

- Original Message 
From: Christian Smith <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 9, 2006 8:38:51 AM
Subject: Re: [sqlite] indexes in memory


> chetana bhargav uttered:

> > Hi,
>
> > I have a question regrading indexes,
> >
> > When I open a connection,
> >
> >  Will indexes be loaded into memory. If one of the tables in the DB, the 
> > connection for which I have opened, has an index.
> >   If, so is there any way to selectively load/unload that from memory.
> 
> 
> Indexes will be loaded into the cache as needed. The whole SQLite database 
> is page based, and the cache caches the pages. The tables and indexes are 
> implemented as page based btrees, with nodes represented by pages.
> 
> The cache is unaware of the higher level structure of the btrees, and 
> there is no way to selectively bring load/unload tables or indexes from 
> memory. The page cache will manage itself on an LRU basis.
> 
> > ...
> > Chetana.

> Christian
 
 
I found that when opening your connection, if you're about to do a lot of 
operations it can be worth doing a "SELECT keyname FROM ... "over the whole 
data to prepopulate the cache with the index data.
 
Even on pretty large datasets this only takes a few seconds and the following 
operations will be much faster (and the overall time to complete the batch is 
much smaller).
 
Nicolas