Re: [sqlite] Integer / Numeric calculations

[John Stanton]

Make your column an underlying Sqlite data type, REAL, which is an 8 
byte floating point number.


Sqlite performs "manifest typing" which means that if you store an 
integer such as 27 in your NUMERIC field it will be stored as an INTEGER 
and if you store a real such as 27.75 it will be stored as a REAL.


You also have the alternative of using a type cast in SQL or of looking 
at the column type in your program and executing code dependent upon it 
being an integer or floating point.

JS

Mikey C wrote:

Hi,

This is my 1st post here.  I hope someone can help.  I've been using SQlite
for about a year and so far very impressed.

Trouble is the typeless nature when doing simple maths on the columns.

I have two columns, rating and votes, both declared as NUMERIC.  I wan't to
calculate the average rating so it's just rating / votes.

If rating and votes contain integer values (e.g. 42 and 11), then

SELECT rating / votes

yields 3 (and not 3.818181)

If have tried cast both rating and votes and the result to NUMERIC but still
an integer.

I can "bodge" the SQL like so:

SELECT (rating + 0.00) / (votes + 0.00)

and it works, but is there a simpler or better SQL solution to ensure a
floating point result even if two integers are involved?

Thanks,

Mike




--
View this message in context: 
http://www.nabble.com/Integer+-+Numeric+calculations-t1661126.html#a4500366
Sent from the SQLite forum at Nabble.com.

[sqlite] Re: Encoding problem ISO 8859-1 / UTF-8

[Hussain KH]

Dear All

The same thing happened to me also when I'm working with Malayalam
(Indic) script.
I'm working in Windows XP, Sqlite3, SqliteOdbc by Werner and Delphi
with Unicode compliant TNT controls.
I have created some records in MS Access using Malaylam script. I have
exported these records to a sqlite3 db and connected to a dbgrid using
ADO Table component (which is Unicode compliant) through sqlite3odbc.
Unfortunately the charaters appearing in the dbgrid are "umlauts"
ä,ü,ß,ö, the same experience of Mr. Pasquale.
What is the way out?

-hussain

On 5/18/06, Pasquale Imbemba <[EMAIL PROTECTED]> wrote:

Hi all,

sorry for repost, but I have gathered some more info for my encoding
problem.

I am using latest SQLite with Java 1.4.2 and openSuSE in order to build
a morphological analyzer for the German language (it's an open source
project for my University). I found out that SuSE has UTF-8 activated by
default, so when I compiled db and the wrapper from Christian Werner
UTF-8 was "on".
When I manually insert data into my db, the "umlauts" ä,ü,ß,ö and the
like are properly displayed. But with Java, when I read either from an
ISO 8859-1 or UTF-8 encoded textfile and then query the db, I see that
the umlauts are substituted with strange letters.
To be more precise, when UTF-8 was still activated, all occurences of
umlaut where substituted with dotted rectangles. I then deactivated
UTF-8 and now I see strange letters (like the 'i' with two dots or the
question mark upside down) instead of the umlauts.

What do I need to do to fix this? Must I compile SQLite again having
UTF-8 deactivated? I'd prefer using ISO 8859-1 over UTF-8 if possible.

_Please_ help
Pasquale

[sqlite] For Mike

[John Stanton]

"Make your column an underlying Sqlite data type, REAL, which is an 8 
byte floating point number".


I just realized that this is not true.  Ignore it

RE: [sqlite] Pragmas

[Anish Enos Mathew]

Hi,
   I used the following command for setting the page size.

   PRAGMA page_size = 4096; before the table is created.

But it is showing an error, "PRAGMA undeclared". What could be the
problem? Do I need to include any header file for it?

-Original Message-
From: Nemanja Corlija [mailto:[EMAIL PROTECTED]
Sent: Monday, May 22, 2006 1:12 AM
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] Pragmas

On 5/21/06, Unit 5 <[EMAIL PROTECTED]> wrote:
> I am a bit confused on how to change the page_size
> using the pragma command.  The documentation says:
>
> "The page-size may only be set if the database has not
> yet been created."

The database is created when you create first table in it. Run the
pragma before your first CREATE TABLE query.

--
Nemanja Corlija <[EMAIL PROTECTED]>


The information contained in, or attached to, this e-mail, contains 
confidential information and is intended solely for the use of the individual 
or entity to whom they are addressed and is subject to legal privilege. If you 
have received this e-mail in error you should notify the sender immediately by 
reply e-mail, delete the message from your system and notify your system 
manager. Please do not copy it for any purpose, or disclose its contents to any 
other person. The views or opinions presented in this e-mail are solely those 
of the author and do not necessarily represent those of the company. The 
recipient should check this e-mail and any attachments for the presence of 
viruses. The company accepts no liability for any damage caused, directly or 
indirectly, by any virus transmitted in this email.

www.aztecsoft.com

RE: [sqlite] Pragmas

[Manzoor Ilahi Tamimy]

Dear Anish

I am using the same
---
 sqlite3_open(database, );
 sqlite3_exec(db, "PRAGMA page_size=4096", NULL, NULL, NULL);
sqlite3_exec(db, "create table t...
---

and its working the only file included is (#include "sqlite3.h")

regards,

TAMIMY


- Original Message - 
From: "Anish Enos Mathew" <[EMAIL PROTECTED]>
To: 
Sent: Monday, May 22, 2006 3:49 PM
Subject: RE: [sqlite] Pragmas


>
> Hi,
>   I used the following command for setting the page size.
>
>   PRAGMA page_size = 4096; before the table is created.
>
> But it is showing an error, "PRAGMA undeclared". What could be the
> problem? Do I need to include any header file for it?
>
> -Original Message-
> From: Nemanja Corlija [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 22, 2006 1:12 AM
> To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> Subject: Re: [sqlite] Pragmas
>
> On 5/21/06, Unit 5 <[EMAIL PROTECTED]> wrote:
>> I am a bit confused on how to change the page_size
>> using the pragma command.  The documentation says:
>>
>> "The page-size may only be set if the database has not
>> yet been created."
>
> The database is created when you create first table in it. Run the
> pragma before your first CREATE TABLE query.

Re: [sqlite] Integer / Numeric calculations

[Mikey C]

Thanks for taking the time John,

It does not seem to matter what the underlying column type is defined at,
SELECT 42 / 9 will always return an integer division.

I fool SQLite by always adding 0.00 to my numbers incase they happen to be
integer values in that row,

SELECT (col1 + 0.00) / (col2 + 0.00)

However this feels like a bodge.  Casting does not fix it either.

SELECT CAST(CAST(col1 AS NUMERIC) / CAST(col2 AS NUMERIC) AS NUMERIC)

still returns an integer if col1 and col2 happen to contain integer values.

What does everyone else do to ensure the division always returns a float?

Mike
--
View this message in context: 
http://www.nabble.com/Integer+-+Numeric+calculations-t1661126.html#a4501777
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Integer / Numeric calculations

[A. Pagaltzis]

* Mikey C <[EMAIL PROTECTED]> [2006-05-22 08:00]:
> If have tried cast both rating and votes and the result to NUMERIC but still
> an integer.

Cast one of them to REAL.

SELECT CAST( rating AS REAL ) / votes FROM foo; 

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] Integer / Numeric calculations

[John Stanton]

I gave you wrong information ro start about REAL, wasn't thinking.

I look at the column type and if it returns an integer transform it to a 
float.


Mikey C wrote:

Thanks for taking the time John,

It does not seem to matter what the underlying column type is defined at,
SELECT 42 / 9 will always return an integer division.

I fool SQLite by always adding 0.00 to my numbers incase they happen to be
integer values in that row,

SELECT (col1 + 0.00) / (col2 + 0.00)

However this feels like a bodge.  Casting does not fix it either.

SELECT CAST(CAST(col1 AS NUMERIC) / CAST(col2 AS NUMERIC) AS NUMERIC)

still returns an integer if col1 and col2 happen to contain integer values.

What does everyone else do to ensure the division always returns a float?

Mike
--
View this message in context: 
http://www.nabble.com/Integer+-+Numeric+calculations-t1661126.html#a4501777
Sent from the SQLite forum at Nabble.com.

[sqlite] SELECT count...

[Danilo]

Hi to all,
I am looking for the way of memorizing the result of:

SELECT count(field1) FROM Table1 WHERE field1 LIKE 'xyz%';

in a int variable, without a callback or sqlite3_prepare-SQLITE_SCHEMA
in C++ program.
Is there a way to do it?

Regards, Danilo.
Home Page: http://www.digitazero.org
lunedì 22 maggio 2006

RE: [sqlite] High retrieval time. Please help

[Anish Enos Mathew]

Hi Michael,
   I came to know that increasing the page size would help in
better performance. So I used PRAGMA and set the page size to 32768
using the command,
  sqlite3_exec (db, "PRAGMA page_size = 32768", NULL, NULL,
NULL);

Still result is the same. Taking a time of 110 sec for 50,000
retrievals. Can u suggest me a method by which the performance of
retrieval can be increased.

-Original Message-
From: Michael Sizaki [mailto:[EMAIL PROTECTED]
Sent: Saturday, May 20, 2006 12:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] High retrieval time. Please help

Anish,

> So my problem of retrieving 1,000,000 random records 1,000,000 times
> works fine for 15 bytes. But it is taking too long a time for 1k
> records. It is almost taking 102 seconds for retrieving 50,000 records
> of size 1k. Can u suggest me a way for reducing the time taken for the
> same? I have'nt done any changes in my program. The only change I made
> was adding primary key to the seq_number column in my data insertion
> program.

My guess is that with 15 byte records you operating system keeps the
entire database in the file cache and no real IO is done. When you use
the 1k records, the entire database does not fit into memory anymore,
and therefore real IO is done. That slows database access dramatically
down. I guess if you watch the CPU usage in the 15 byte case, its close
to 100% and in the 1k case it's very low (an you have a lot of disc
access).

Michael


The information contained in, or attached to, this e-mail, contains 
confidential information and is intended solely for the use of the individual 
or entity to whom they are addressed and is subject to legal privilege. If you 
have received this e-mail in error you should notify the sender immediately by 
reply e-mail, delete the message from your system and notify your system 
manager. Please do not copy it for any purpose, or disclose its contents to any 
other person. The views or opinions presented in this e-mail are solely those 
of the author and do not necessarily represent those of the company. The 
recipient should check this e-mail and any attachments for the presence of 
viruses. The company accepts no liability for any damage caused, directly or 
indirectly, by any virus transmitted in this email.

www.aztecsoft.com

Re: [sqlite] Can't access sqlite_master from VB6 via ODBC

[[EMAIL PROTECTED]]

Hi

I thought it wasn't permissions. The VB interface I'm using is ActiveX Data
Objects, which works through ODBC and the SQLite ODBC driver. Might there
be some problem with this driver?

Robin

Original Message:
-
From: John Stanton [EMAIL PROTECTED]
Date: Mon, 22 May 2006 09:07:17 +1000
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Can't access sqlite_master from VB6 via ODBC


It is not permissions, more like a VB interface problem.

Robin Wilson wrote:
> Hi all,
> 
> I've just started looking at SQLite - and think it is really impressive. 
> It would be just the thing to use with my students for teaching them 
> about RDBMS's (especially with some of the nice Windows UIs which are 
> available for the students who can't cope with command line tools!).
> 
> I have had a few problems though. At the moment I am accessing SQLite 
> through ODBC from Visual Basic 6. This means the students can carry on 
> using the ADO commands that they are familier with (from working with 
> Access DBs). However, when working from VB the SQL query "SELECT * FROM 
> sqlite_master;" does not return any records, but when I run that from 
> the sqlite3 command line program with the same db file it returns 1 row.
> 
> Is this some kind of permissions problem? If this happened elsewhere I 
> would assume it was, but I remember reading on your website that 
> permissions aren't implemented in SQLite.
> 
> Does anyone have any ideas?
> 
> Cheers,
> 
> Robin
> 
> P.S. I hope this is the right list, and that it is ok to just butt in 
> etc...



mail2web - Check your email from the web at
http://mail2web.com/ .

Re: [sqlite] Can't access sqlite_master from VB6 via ODBC

[[EMAIL PROTECTED]]

Hi

Yes I did look at that, but I was wanting to use ODBC to access SQLite as
that is what I and my students are used to. In VB you can use the same
programming commands (ADO) to access any ODBC database. My students are
used to this and I'd like to carry on using this if possible. I will,
however, investigate the link you gave me in more detail when I am at home.

I was playing with the problem a bit more last night, and found that some
queries worked (for example the one specified on your example page: SELECT
name FROM sqlite_master WHERE type='table' UNION ALL SELECT name FROM
sqlite_temp_master WHERE type='table' ORDER BY name; - which apparantly is
the same as the .tables command. However, when I try the query SELECT name
FROM sqlite_master; it gives no results - even though a much more complex
query with WHERE clauses gives some results. Any ideas on this?

Cheers,

Robin


Original Message:
-
From: John Newby [EMAIL PROTECTED]
Date: Mon, 22 May 2006 10:39:54 +0100
To: sqlite-users@sqlite.org, [EMAIL PROTECTED]
Subject: Re: [sqlite] Can't access sqlite_master from VB6 via ODBC


Hi Robin, have you looked here, there are many wrappers for SQLite
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
this one in particular mentions "any ActiveX language such Visual Basic"
http://vfornazin.ipdz.com/

hope this is of help to you

john



On 22/05/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> Hi
>
> I thought it wasn't permissions. The VB interface I'm using is ActiveX
> Data
> Objects, which works through ODBC and the SQLite ODBC driver. Might there
> be some problem with this driver?
>
> Robin
>
> Original Message:
> -
> From: John Stanton [EMAIL PROTECTED]
> Date: Mon, 22 May 2006 09:07:17 +1000
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Can't access sqlite_master from VB6 via ODBC
>
>
> It is not permissions, more like a VB interface problem.
>
> Robin Wilson wrote:
> > Hi all,
> >
> > I've just started looking at SQLite - and think it is really impressive.
> > It would be just the thing to use with my students for teaching them
> > about RDBMS's (especially with some of the nice Windows UIs which are
> > available for the students who can't cope with command line tools!).
> >
> > I have had a few problems though. At the moment I am accessing SQLite
> > through ODBC from Visual Basic 6. This means the students can carry on
> > using the ADO commands that they are familier with (from working with
> > Access DBs). However, when working from VB the SQL query "SELECT * FROM
> > sqlite_master;" does not return any records, but when I run that from
> > the sqlite3 command line program with the same db file it returns 1 row.
> >
> > Is this some kind of permissions problem? If this happened elsewhere I
> > would assume it was, but I remember reading on your website that
> > permissions aren't implemented in SQLite.
> >
> > Does anyone have any ideas?
> >
> > Cheers,
> >
> > Robin
> >
> > P.S. I hope this is the right list, and that it is ok to just butt in
> > etc...
>
>
> 
> mail2web - Check your email from the web at
> http://mail2web.com/ .
>
>
>



mail2web - Check your email from the web at
http://mail2web.com/ .

[sqlite] sqlite schema error

[Shields, Daniel]

I apologise in advance as this topic has been done to death.
Can I assume that a schema error can only occur when calling 
sqlite3_step? 

I've trawled through the documentation but cant find a definitive
answer.

Thanks,

Daniel. 

--
Daniel Shields
Equities IT
+44(0)207 888 9248   
[EMAIL PROTECTED]



==
Please access the attached hyperlink for an important electronic communications 
disclaimer: 

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==

[sqlite] Help creatign a database using Finisar SQLite ADO.Net provider and VB.Net

[John Newby]

Hi, I am trying to open a database, using the input database name given by
the user, if I run the testDB() function where the source of the database is
given, it works fine, but if I try to use the createNewdatabase() function,
passing back the string with the filename from the input and it doesn't
work, I was wondering if anyone could help me as to where I'm going wrong, I
have attached the code I am using below.

Many thanks.

John

This works fine
Public Function testDB()
   ' create a new database
   sqlite_conn = New SQLiteConnection("Data
Source=D:\test.db;Version=3;New=True;Compress=True;")
   End Function

This doesn't
   Public Function createNewdatabase(ByVal strSQL As String)
   ' create a new database
   sqlite_conn = New SQLiteConnection(strSQL)
   End Function


   Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnNew.Click
   Dim dbConn As dbConnection = New dbConnection()
   Dim FlName As String
   With SaveFileDialog1
   '.DefaultExt = ".db"
   .Filter = "SQLite Database Files|*.db|All Files|*.*"
   If .ShowDialog() = DialogResult.OK Then
   .FileName = FileName
   FlName = FileName
   ' create a new database with given name
   dbConn.createNewdatabase("Data Source=" & FlName &
";Version=3;New=True;Compress=True;")
   End If
   End With
   End Sub

Re: [sqlite] Can't access sqlite_master from VB6 via ODBC

[Nemanja Corlija]

On 5/22/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

I was playing with the problem a bit more last night, and found that some
queries worked (for example the one specified on your example page: SELECT
name FROM sqlite_master WHERE type='table' UNION ALL SELECT name FROM
sqlite_temp_master WHERE type='table' ORDER BY name; - which apparantly is
the same as the .tables command. However, when I try the query SELECT name
FROM sqlite_master; it gives no results - even though a much more complex
query with WHERE clauses gives some results. Any ideas on this?


Did you try "SELECT name FROM sqlite_temp_master"?
Perhaps all your tables are temp tables?

--
Nemanja Corlija <[EMAIL PROTECTED]>

[sqlite] How to check whether sqlite_open created the new database ?

[kamil]

I have to create a database schema in such case.

Thanks in advance,
Kamil

Re: [sqlite] create unique index quickly

[drh]

"Brannon King" <[EMAIL PROTECTED]> wrote:
> The statement
> CREATE UNIQUE INDEX primkey ON 4dData (x,y,z)
> is too slow for me.
> 
> Is there some way I can create that unique index without the overhead of
> uniqueness checking? I know all my triples are unique when used =
> together: I
> generated them that way. Or is there some other way I can create that =
> index
> faster?
> 

The following technique is unsupported.  It might change
or go away in a subsequent release.  It might not work.
It might corrupt your database.  Use with extreme caution.

  (1)  CREATE INDEX primkey ON data(x,y,z)
  (2)  PRAMGA writable_schema=ON
  (3)  UPDATE sqlite_master 
  SET sql='CREATE UNIQUE INDEX primkey ON data(x,y,z)'
WHERE name='primkey'
  (4)  close and reopen the database connection

The above technique is unsupported.  It might not work.
It might corrupt your database.  The overhead of the
uniqueness check is not that much so it is unlikely
to help much anyway.  You have been warned.

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

Re: [sqlite] Re: How to check whether sqlite_open created the new database ?

[Thomas Chust]

On Mon, 22 May 2006, Igor Tandetnik wrote:


kamil <[EMAIL PROTECTED]> wrote:

How to check whether sqlite_open created the new database ?
I have to create a database schema in such case.


sqlite_master table would be empty in a freshly created database. [...]


Hello,

checking the sqlite_master table for the existence of tables and indices 
is a good and portable solution.


If you have a fairly recent version of SQLite3 you may also want to look 
at the CREATE [TABLE|INDEX] IF NOT EXISTS ... syntax.


cu,
Thomas

Re: [sqlite] Can't access sqlite_master from VB6 via ODBC

[drh]

Robin Wilson <[EMAIL PROTECTED]> wrote:
> 
> I have had a few problems though. At the moment I am accessing SQLite 
> through ODBC from Visual Basic 6. This means the students can carry on 
> using the ADO commands that they are familier with (from working with 
> Access DBs). However, when working from VB the SQL query "SELECT * FROM 
> sqlite_master;" does not return any records, but when I run that from 
> the sqlite3 command line program with the same db file it returns 1 row.
> 

This sounds like a bug in the ODBC driver.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Can't access sqlite_master from VB6 via ODBC

[[EMAIL PROTECTED]]

Hi

That is what I thought. Who should I report the bug to?

Cheers,

Robin

Original Message:
-
From:  [EMAIL PROTECTED]
Date: Mon, 22 May 2006 08:14:12 -0400
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Can't access sqlite_master from VB6 via ODBC


Robin Wilson <[EMAIL PROTECTED]> wrote:
> 
> I have had a few problems though. At the moment I am accessing SQLite 
> through ODBC from Visual Basic 6. This means the students can carry on 
> using the ADO commands that they are familier with (from working with 
> Access DBs). However, when working from VB the SQL query "SELECT * FROM 
> sqlite_master;" does not return any records, but when I run that from 
> the sqlite3 command line program with the same db file it returns 1 row.
> 

This sounds like a bug in the ODBC driver.
--
D. Richard Hipp   <[EMAIL PROTECTED]>




mail2web - Check your email from the web at
http://mail2web.com/ .

[sqlite] ACID for attached databases

[Martin Pfeifle]

Dear all,
 
We plan to update several sqlite database files within one transaction.
I was just reading the following on the SQLite homepage:
"Transactions involving multiple attached databases are atomic, assuming that 
the main database is not ":memory:".
 
We do not have memory databases. But I do not understand how atomicity is 
achieved.
I understand that the ACID principle can be realized by means of the fsync 
command. But how does this work for more than one database file?
Can anyone explain that to me, please. 
 
Best Martin

Re: [sqlite] create unique index quickly

[Dennis Jenkins]

Brannon King wrote:
> The benefits I'm trying to get out of sqlite are the data queries. I
> collect a large, sparse 2D array from hardware. The hardware device is
> giving me a few GB of data data at 200MB/s. Future hardware versions
> will be four times that fast and give me terabytes of data. After I
> have the data, I then have to go through and make calculations on
> sub-boxes of that data. (I'll post some more about that in a different
> response.) I was trying to avoid coding my own
> sparce-matrix-file-stream-mess that I would have to do if I didn't
> have a nice DB engine. I think sqlite will work. I think it will be
> fast enough. I'll have some nice RAID controllers on the production
> machines with 48-256MB caches.

Hello Brannon,

I am simply curious.  This sounds like an amazing engineering
challenge.  If it is not a secret, can you describe what this data
represents and how it will be used? 

What is the ultimate source of this data? 

How many days/weeks/eons of it do you plan to accumulate?   How much
raw disk space is that?

If backups and journaling are not important, then is it safe to
assume that you can always regenerate that data on demand?  Is each
"set" of data identical, or only statistically similar to prior sets?

Your project sounds like fun though, from what little I've read of
this thread.  Sure beats writing boring financial software ;)

[sqlite] Re: ACID for attached databases

[Igor Tandetnik]

Martin Pfeifle <[EMAIL PROTECTED]> wrote:

We do not have memory databases. But I do not understand how
atomicity is achieved.
I understand that the ACID principle can be realized by means of the
fsync command. But how does this work for more than one database
file? Can anyone explain that to me, please.


See http://sqlite.org/lockingv3.html, particularly section 4 dealing 
with journal files.


Igor Tandetnik 

AW: [sqlite] Re: ACID for attached databases

[Martin Pfeifle]

thank you,
that is exactly what I searched for.
Best Martin


- Ursprüngliche Mail 
Von: Igor Tandetnik <[EMAIL PROTECTED]>
An: SQLite 
Gesendet: Montag, den 22. Mai 2006, 15:03:03 Uhr
Betreff: [sqlite] Re: ACID for attached databases


Martin Pfeifle <[EMAIL PROTECTED]> wrote:
> We do not have memory databases. But I do not understand how
> atomicity is achieved.
> I understand that the ACID principle can be realized by means of the
> fsync command. But how does this work for more than one database
> file? Can anyone explain that to me, please.

See http://sqlite.org/lockingv3.html, particularly section 4 dealing 
with journal files.

Igor Tandetnik

Re: [sqlite] create unique index quickly

[Jay Sprenkle]

On 5/22/06, Dennis Jenkins <[EMAIL PROTECTED]> wrote:

Brannon King wrote:
> The benefits I'm trying to get out of sqlite are the data queries. I
> collect a large, sparse 2D array from hardware. The hardware device is
> giving me a few GB of data data at 200MB/s. Future hardware versions
> will be four times that fast and give me terabytes of data. After I
> have the data, I then have to go through and make calculations on
> sub-boxes of that data. (I'll post some more about that in a different
> response.) I was trying to avoid coding my own
> sparce-matrix-file-stream-mess that I would have to do if I didn't
> have a nice DB engine. I think sqlite will work. I think it will be
> fast enough. I'll have some nice RAID controllers on the production
> machines with 48-256MB caches.

Hello Brannon,

I am simply curious.  This sounds like an amazing engineering
challenge.  If it is not a secret, can you describe what this data
represents and how it will be used?


Me too!

The two largest database setups I've worked with:
the total of all the call records for a major phone company,
and the cumulative records of most of the drugs bought in
the United States, don't add up to as much as this.
What are you sampling at 1/200th of a microsecond?

Re: [sqlite] High retrieval time. Please help

[Michael Sizaki]

Hi Anish,

when a database hits the disk, there's not much you can
do about. You can increase the memory of your system, so
that the entire database fits into memory. If the database
is "cold" (the system has started and the database is not
in the file system cache), you can read the entire database
file once using to get it into the cache. Unfortunately,
this does not help much, if the database is too big to fit
into memory. Another trick that could work in some cases:
if you know you have 50.000 requests and you know the order
in which the data is in the database, you can sort the requests
before you access the database in the order in which they are physically
stored in the database. Normally you would not know the exact order,
but if you do a VACUUM on the database you know that the data
is ordered in order of ROWID. But that works only in some cases,
where you query the data by ROWID.

I general, sqlite is great as long as all data fits into
memory. I have no comparison with other database systems,
how they perform when you hit the disk.

Michael


Anish Enos Mathew wrote:

Hi Michael,
   I came to know that increasing the page size would help in
better performance. So I used PRAGMA and set the page size to 32768
using the command,
  sqlite3_exec (db, "PRAGMA page_size = 32768", NULL, NULL,
NULL);

Still result is the same. Taking a time of 110 sec for 50,000
retrievals. Can u suggest me a method by which the performance of
retrieval can be increased.

-Original Message-
From: Michael Sizaki [mailto:[EMAIL PROTECTED]

Sent: Saturday, May 20, 2006 12:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] High retrieval time. Please help

Anish,


So my problem of retrieving 1,000,000 random records 1,000,000 times
works fine for 15 bytes. But it is taking too long a time for 1k
records. It is almost taking 102 seconds for retrieving 50,000 records
of size 1k. Can u suggest me a way for reducing the time taken for the
same? I have'nt done any changes in my program. The only change I made
was adding primary key to the seq_number column in my data insertion
program.


My guess is that with 15 byte records you operating system keeps the
entire database in the file cache and no real IO is done. When you use
the 1k records, the entire database does not fit into memory anymore,
and therefore real IO is done. That slows database access dramatically
down. I guess if you watch the CPU usage in the 15 byte case, its close
to 100% and in the 1k case it's very low (an you have a lot of disc
access).

Michael


The information contained in, or attached to, this e-mail, contains 
confidential information and is intended solely for the use of the individual 
or entity to whom they are addressed and is subject to legal privilege. If you 
have received this e-mail in error you should notify the sender immediately by 
reply e-mail, delete the message from your system and notify your system 
manager. Please do not copy it for any purpose, or disclose its contents to any 
other person. The views or opinions presented in this e-mail are solely those 
of the author and do not necessarily represent those of the company. The 
recipient should check this e-mail and any attachments for the presence of 
viruses. The company accepts no liability for any damage caused, directly or 
indirectly, by any virus transmitted in this email.

www.aztecsoft.com

Re: [sqlite] create unique index quickly

[Dennis Jenkins]

Jay Sprenkle wrote:
>
> Me too!
>
> The two largest database setups I've worked with:
> the total of all the call records for a major phone company,
> and the cumulative records of most of the drugs bought in
> the United States, don't add up to as much as this.
> What are you sampling at 1/200th of a microsecond?


Jay, do you work for the NSA or the DEA or both?

Re: [sqlite] SELECT count...

[Jay Sprenkle]

On 5/22/06, Danilo <[EMAIL PROTECTED]> wrote:

Hi to all,
I am looking for the way of memorizing the result of:

SELECT count(field1) FROM Table1 WHERE field1 LIKE 'xyz%';

in a int variable, without a callback or sqlite3_prepare-SQLITE_SCHEMA
in C++ program.
Is there a way to do it?


look at sqlite3_bind_int()
http://sqlite.org/capi3ref.html#sqlite3_bind_int

Re: [sqlite] I need help understanding fake columns

[Jay Sprenkle]

On 5/21/06, Brannon King <[EMAIL PROTECTED]> wrote:

Jay Sprenkle wrote:
>> score(x,y) = max(score(x+n,y+n)-n*penalty) where n < drc(x+n,y+n) < 256
>
> So at some 'sample' you have the score and drc, but not the x,y value?
> The x,y values can be interpolated using some algebra and the surrounding
> samples?
Uh, no. Sorry my other emails were unclear. At all values that I do
have, I have an x,y,score, and drc. I can calculate any value that is
missing, though. The n in the above formula refers to all numbers
between 0 and 256. I'm taking the max over all the data I have in that
range: max for all data pieces that fall within n distance of the piece
I'm calculating.


If I understand it right you'll definitely need a correlated subquery.
You need one query to calculate the max() of the 257(0-256?) values.
There must be some way to define which values should be used
in that calculation for a specific piece (the distance). Is the distance
geometric and calculated from x,y?

In pseudo code something like this:

select piece, ( sub query to calculate max where distance from
desired_piece < 256 )
from blah
where piece = desired_piece

Re: [sqlite] create unique index quickly

[Micha Bieber]

Monday, May 22, 2006, 15:17:21, Jay Sprenkle wrote:

> On 5/22/06, Dennis Jenkins <[EMAIL PROTECTED]> wrote:
>> Brannon King wrote:
>> > The benefits I'm trying to get out of sqlite are the data queries. I
>> > collect a large, sparse 2D array from hardware. The hardware device is
>> > giving me a few GB of data data at 200MB/s. Future hardware versions
>> > will be four times that fast and give me terabytes of data. After I
>> > have the data, I then have to go through and make calculations on
>> > sub-boxes of that data. (I'll post some more about that in a different
>> > response.) I was trying to avoid coding my own
>> > sparce-matrix-file-stream-mess that I would have to do if I didn't
>> > have a nice DB engine. I think sqlite will work. I think it will be
>> > fast enough. I'll have some nice RAID controllers on the production
>> > machines with 48-256MB caches.
>>
>> Hello Brannon,
>>
>> I am simply curious.  This sounds like an amazing engineering
>> challenge.  If it is not a secret, can you describe what this data
>> represents and how it will be used?

> Me too!

Me too too :)

_Personally_ I think, this sounds like a task not quite fitting in
sqlites (probably any 'standard' databases) realm. This is a bit
off-topic in this group, but because you mention sub-boxes - did you
ever look into more specialized file-formats like HDF5:

http://hdf.ncsa.uiuc.edu/HDF5/

with support for certain (of course limited) queries ?

Micha  
-- 

Re: [sqlite] create unique index quickly

[Jay Sprenkle]

On 5/22/06, Dennis Jenkins <[EMAIL PROTECTED]> wrote:

Jay Sprenkle wrote:
>
> Me too!
>
> The two largest database setups I've worked with:
> the total of all the call records for a major phone company,
> and the cumulative records of most of the drugs bought in
> the United States, don't add up to as much as this.
> What are you sampling at 1/200th of a microsecond?


Jay, do you work for the NSA or the DEA or both?


LOL!

Worked as a consultant for Sprint and Argus Health systems.
They both do huge data warehouses.

Of course, if I told you the truth I'd have to kill you. ;)

RE: [sqlite] create unique index quickly

[Brannon King]

> _Personally_ I think, this sounds like a task not quite 
> fitting in sqlites (probably any 'standard' databases) realm. 
> This is a bit off-topic in this group, but because you 
> mention sub-boxes - did you ever look into more specialized 
> file-formats like HDF5:
> 
> http://hdf.ncsa.uiuc.edu/HDF5/
> 
> with support for certain (of course limited) queries ?
> 
> Micha
> -- 
> 


What a fantastic link and suggestion! I just may end up using it.

RE: [sqlite] I need help understanding fake columns

[Brannon King]

> If I understand it right you'll definitely need a correlated subquery.
> You need one query to calculate the max() of the 257(0-256?) values.
> There must be some way to define which values should be used 
> in that calculation for a specific piece (the distance). Is 
> the distance geometric and calculated from x,y?
> 
> In pseudo code something like this:
> 
> select piece, ( sub query to calculate max where distance 
> from desired_piece < 256 )  from blah  where piece = desired_piece

Explain what you mean by "correlated subquery."

Like I said in the original post, I need help to modify this exact query:

SELECT qi,ri,(drl- (qi - :qi) ) as drl,MAX(score - ((ri - :ri) * :match)) AS
scr FROM results_1
WHERE (qi - ri) = (:qi - :ri) AND (qi >= :qi) AND (ri >= :ri) AND (qi < :qi
+ 256) 
AND (ri < :ri + 256) AND (drl > qi - :qi)

to make it such that I get the max of the above scr over :qi to
bounds.upperQi and :ri to bounds.upperRi

Re: [sqlite] create unique index quickly

[Jay Sprenkle]

On 5/22/06, Brannon King <[EMAIL PROTECTED]> wrote:

> _Personally_ I think, this sounds like a task not quite
> fitting in sqlites (probably any 'standard' databases) realm.
> This is a bit off-topic in this group, but because you
> mention sub-boxes - did you ever look into more specialized
> file-formats like HDF5:
>
> http://hdf.ncsa.uiuc.edu/HDF5/


What a fantastic link and suggestion! I just may end up using it.


Interesting, but it doesn't have any adhoc query capability:


"Performance-wise, how does HDF5 compare to a relational database?

 It really depends on your application. HDF5 is tuned to do
efficient I/O and storage for "big" data (hundreds of megabytes and
more). It will not work well for small reads/writes.

 It doesn't have indexing capabilities, though we are working on
some limited features. See the HDF5_Prototype_Indexing_Requirements
for details.

 HDF5 was designed to complement DBs and not to compete with them. "

RE: [sqlite] create unique index quickly

[Brannon King]

> I am simply curious.  This sounds like an amazing 
> engineering challenge.  If it is not a secret, can you 
> describe what this data represents and how it will be used? 
Genomics. Look up "Smith-Waterman" or "Needleman-Wunsch-Sellers" on the web.

> What is the ultimate source of this data? 
Custom hardware
 
> How many days/weeks/eons of it do you plan to accumulate? 
Less than a week.

>   How much
> raw disk space is that?
It varies by thresholding. 1GB to several TB.
 
> If backups and journaling are not important, then is it 
> safe to assume that you can always regenerate that data on 
> demand?  Is each "set" of data identical, or only 
> statistically similar to prior sets?
I can always regenerate if I want to wait a few days. It's not exactly an
exact science anyway.

> Your project sounds like fun though, from what little 
> I've read of this thread.  Sure beats writing boring 
> financial software ;)
I admit I'm quite enjoying it as well.

[sqlite] Crashing aplication...

[Esteban Zeller]

Any one any idea?

My app get this backtrace when i execute it:

#0  0xb6f0f3ee in __waitpid_nocancel () from /lib/tls/libpthread.so.0
#1  0xb7b03500 in KCrash::defaultCrashHandler () 
from /opt/kde/lib/libkdecore.so.4
#2  
#3  0xb7f0a246 in sqlite3SafetyOn () from /usr/lib/./libsqlite3.so.0
#4  0xb7efe385 in sqlite3_prepare () from /usr/lib/./libsqlite3.so.0
#5  0x080511aa in bd::cargar_ultimo_uso (this=0x805b980) 
at /home/Programas/preguntas/src/bd.cpp:155

It's open perfectly the db but when i try this code get that:
the code:

string cola = "SELECT * FROM ultimo;";

 int ret = sqlite3_prepare( base_datos, cola.c_str(), cola.size(), , 
NULL );




___ 
1GB gratis, Antivirus y Antispam 
Correo Yahoo!, el mejor correo web del mundo 
http://correo.yahoo.com.ar 

RE: [sqlite] create unique index quickly

[Brannon King]

Thank you for answering the question! Like I said in my other post, the
index creation is about 20% faster than the unique index creation in my
latest tests of an index of two INTEGERs. 20% out of several hours is
significant.

So index names are unique for the whole database? I was thinking they just
had to be unique for the table. I'll double check my names...

> > Is there some way I can create that unique index without 
> the overhead 
> > of uniqueness checking? I know all my triples are unique when used =
> > together: I
> > generated them that way. Or is there some other way I can 
> create that 
> > = index faster?
> > 
> 
> The following technique is unsupported.  It might change or 
> go away in a subsequent release.  It might not work.
> It might corrupt your database.  Use with extreme caution.
> 
>   (1)  CREATE INDEX primkey ON data(x,y,z)
>   (2)  PRAMGA writable_schema=ON
>   (3)  UPDATE sqlite_master 
>   SET sql='CREATE UNIQUE INDEX primkey ON data(x,y,z)'
> WHERE name='primkey'
>   (4)  close and reopen the database connection
> 
> The above technique is unsupported.  It might not work.
> It might corrupt your database.  The overhead of the 
> uniqueness check is not that much so it is unlikely to help 
> much anyway.  You have been warned.
> 
> --
> D. Richard Hipp
 

Re: [sqlite] I need help understanding fake columns

[Jay Sprenkle]

On 5/22/06, Brannon King <[EMAIL PROTECTED]> wrote:


> If I understand it right you'll definitely need a correlated subquery.
> You need one query to calculate the max() of the 257(0-256?) values.
> There must be some way to define which values should be used
> in that calculation for a specific piece (the distance). Is
> the distance geometric and calculated from x,y?
>
> In pseudo code something like this:
>
> select piece, ( sub query to calculate max where distance
> from desired_piece < 256 )  from blah  where piece = desired_piece

Explain what you mean by "correlated subquery."


As I understand it you need two categories of information with this query.
"A data point" and "a calculation using all the values within a given
distance". Your query has two "subqueries" since you need two different
pieces of info. Those pieces are related to each other so they're "correlated".
It's database terminology you'll see in books and tutorials.

Here they are on separate lines:

SELECT qi,ri,(drl- (qi - :qi) ) as drl,
MAX(score - ((ri - :ri) * :match)) AS scr
FROM results_1


For your second subquery we need to know which values
are "within a given distance". How is that found?
IE: please define " bounds.upperQi and :ri to bounds.upperRi"

Re: [sqlite] Advice on compiling 3.5.5 for Mac OS X?

[Bill Bumgarner]

On May 21, 2006, at 5:05 PM, Kon Lovett wrote:
I second this. I am not even sure /usr/lib/libreadline.dylib is  
readline. It is a symbolic link to libedit.dylib for me. So I use  
my own build of readline as well.


It is the BSD version of readline which lacks the history API and a  
couple of other non-primary bits of the GNU readline library.


Why not the real thing?

Because libreadline is GPL'd (or LPGL'd) and, thus, taints anything  
it touches.   Even the LGPL is a taint in the eyes of many.


b.bum

[sqlite] sqlite3_exec returning SQLITE_CANTOPEN

[Pat Wibbeler]

I'm periodically seeing sqlite3_exec return SQLITE_CANTOPEN.
Unfortunately, I haven't boiled this down to a simple sample that I can
send to this list, but I thought I'd check to see if there is a known
solution before I dive too deep.  I'm using sqlite version 3.2.7 on
windows.

Thanks!
Pat

The Details
---
The sql statement that causes this always seems to be:
"BEGIN IMMEDIATE"

When I inspect my threads in the debugger, there is generally another
transaction closing the database after a "COMMIT", and a third doing
"BEGIN IMMEDIATE" concurrently.  One peculiarity of my system is that I
open and close a new "connection" to the database for each transaction.
I also use a busy_handler that returns 1.  I have debug log statement in
my busy handler and I usually see it called just before this problem
occurs.  

The threads look like this (snipped to sqlite calls):

THREAD A - The thread from which the SQLITE_CANTOPEN is returned.  The
windows GetLastError appears to be "Access is denied" in an attempt to
open the journal file.  I've paused this stack at the line where the
SQLITE_CANTOPEN is returned - line 286 of os_win.c

App.exe!sqlite3OsOpenExclusive(const char * zFilename=0x02425b6a, OsFile
* id=0x02423a58, int delFlag=0)  Line 286
App.exe!pager_open_journal(Pager * pPager=0x024239f8)  Line 2654
App.exe!sqlite3pager_begin(void * pData=0x024276f8, int exFlag=0)  Line
2748 
App.exe!sqlite3BtreeBeginTrans(Btree * pBt=0x02421730, int wrflag=1)
Line 1638 
App.exe!sqlite3VdbeExec(Vdbe * p=0x0240ccb8)  Line 2337
App.exe!sqlite3_step(sqlite3_stmt * pStmt=0x0240ccb8)  Line 217
App.exe!sqlite3_exec(sqlite3 * db=0x02427240, const char *
zSql=0x0268c7e4, int (void *, int, char * *, char * *)*
xCallback=0x, void * pArg=0x, char * *
pzErrMsg=0x0268c468) Line 79
  

THREAD B, also doing a "BEGIN IMMEDIATE"
App.exe!sqlite3OsFileExists(const char * zFilename=0x0241bb72)  Line
148App.exe!hasHotJournal(Pager * pPager=0x02419a00)  Line 2270
App.exe!sqlite3pager_get(Pager * pPager=0x02419a00, unsigned int pgno=1,
void * * ppPage=0x0278b90c)  Line 2336
App.exe!getPage(Btree * pBt=0x0241f7c0, unsigned int pgno=1, MemPage * *
ppPage=0x0278ba10)  Line 1125
App.exe!lockBtree(Btree * pBt=0x0241f7c0)  Line 1445
App.exe!sqlite3BtreeBeginTrans(Btree * pBt=0x0241f7c0, int wrflag=1)
Line 1634
App.exe!sqlite3VdbeExec(Vdbe * p=0x02422d58)  Line 2337
App.exe!sqlite3_step(sqlite3_stmt * pStmt=0x02422d58)  Line 217
App.exe!sqlite3_exec(sqlite3 * db=0x0241f970, const char *
zSql=0x0278d02c, int (void *, int, char * *, char * *)*
xCallback=0x, void * pArg=0x, char * *
pzErrMsg=0x0278ccb0)  Line 79

THREAD C, calling close after finishing up a transaction:
App.exe!sqlite3OsClose(OsFile * id=0x0240a770)  Line 416
App.exe!sqlite3pager_close(Pager * pPager=0x0240a718)  Line 2011
App.exe!sqlite3BtreeClose(Btree * pBt=0x0243e800)  Line 1299
App.exe!sqlite3_close(sqlite3 * db=0x0240d088)  Line 159

Re: [sqlite] scary warnings

[Roger Binns]

This is pretty much a bogus warning since in windows size_t is an integer:


size_t should always be unsigned.  ssize_t is the signed variant.

Roger

Re: [sqlite] create unique index quickly

[Jay Sprenkle]

On 5/22/06, Micha Bieber <[EMAIL PROTECTED]> wrote:

> Interesting, but it doesn't have any adhoc query capability:

Take in consideration, that this is not the whole truth - simply
selecting and getting what the HDF5 authors call a 'hyperslab' is a
great feature and an example for a simple query task. This special task
alone might become an essential part of your problem.


I wouldn't classify ad hoc queries as 'simple'. If all you need is simple
queries this might be a much better choice. Not knowing any details of
how the data will be used makes it hard to judge which would be better
for the application. Thanks for pointing it out! :)

Re: [sqlite] Make it faster

[Anne . Kirchhellen]

Hi John

> Anne, you don't say that you make your 100 inserts a transaction. 

Yes, I don't make the inserts in a transaction... because in that 
moment don't know how to use it.

Now I have read in the Docu and after it seems to me, that I understand,
I insert a Test-Function in my App. I start it... and nothing goes
on. I start it again... same result. I check my Source and see, 
thats ok. I start it at the third time, also the same... no result.
*grrr*

I Check my source again, and it seems furthermore to be ok. 
Then I use "my" SQLiteSpy and take a look in the Database. 
And I wonder... all Records being created. Whats that?

SQLite works so fast, that I cannot see it works...  :-D
Famous... very famous...

Thanks and best Greetings from Germany
Anne



-- 
Mobile Internet - E-Mail und Internet immer und überall!
GMX zum Mitnehmen: http://www.gmx.net/de/go/pocketweb

Re: [sqlite] scary warnings

[Cory Nelson]

On 5/22/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:

On 5/22/06, Brannon King <[EMAIL PROTECTED]> wrote:
> Is the number of size conversion warnings during sqlite compile
> disconcerting to anyone but myself? I've attached a log of them below. I

> vdbemem.c
> vdbemem.c(194) : warning C4267: '=' : conversion from 'size_t' to 'int',
> possible loss of data

This is pretty much a bogus warning since in windows size_t is an integer:

#ifndef _SIZE_T_DEFINED
typedef unsigned int size_t;
#define _SIZE_T_DEFINED
#endif

The possible loss of data is the sign. Since sizes are almost always
non negative in practice there won't be a problem. It's poor practice to
not declare things correctly, but it won't make any practical difference.


The warning is likely because  size_t can be 64bit and "int" is always
32bit.  Either way, it should be casted.


etc.

Given that DRH maintains an extensive regression test suite with sqlite
I'm not concerned. This is much more rigrourous than most any other
product I've worked with.




--
Cory Nelson
http://www.int64.org

[sqlite] reset function speed

[Brannon King]

I noticed some interesting things about the step function. I have a query
with a MAX in it; hence, it always returns one row. (Well, it returns one
row every time I use the step function and when I use the sqlite3.exe
browser, but the sqlite3_exec function does not run the callback in the case
where all the return data was empty.) Now if I use the return data and then
call the reset function, it takes the same amount of time as if I call the
step function again and then the reset function. Apparently it cannot tell
it is on the last row. It just seems strange to me that the reset
functionality or the functionality to acquire the SQLITE_DONE signal is the
equivalent of another step call time-wise. From the profiler, it appears
that most of the time is in memory deallocation. That is strange because
there is very little time spent in memory allocation in general. Maybe the
memory is allocated in blocks, but deallocated one piece at a time.
 
__
Brannon King
¯¯

[sqlite] can you speed this query up?

[Brannon King]

It seems that I yet need help with another query. This one is just too slow.
I've included the "explain" and the table schema. I've been using the
prepare/step model directly. What should I change on my indexing to make it
faster?

The schema:
CREATE TEMPORARY TABLE IF NOT EXISTS bounds (bi INTEGER PRIMARY KEY
AUTOINCREMENT DEFAULT NULL, bqi INTEGER, bri INTEGER, bqis INTEGER, bris
INTEGER);
CREATE UNIQUE INDEX IF NOT EXISTS qisris ON bounds (bqis, bris);
CREATE UNIQUE INDEX IF NOT EXISTS qiri ON bounds (bqi, bri);
insert into bounds values(NULL,1,1,5880,5880);
CREATE TABLE results_1 (qi INTEGER, ri INTEGER, drl INTEGER, score INTEGER,
qis INTEGER, ris INTEGER);
CREATE UNIQUE INDEX loc_1 ON results_1 (qi,ri); 

The queries (both of these run slow but I care about the second):
"select count(*) from results_1 where qi = 5604 OR ri = 5468;"
returns 102

So you can see the following query should only be doing a max over a 102
pieces; that's not very many. 

"explain 
select qi,ri,drl,max(score) as scr from results_1, bounds where (qi = 5604
OR ri = 5468) AND (qi >= bqis
 AND qi <= 5604) AND (ri >= bris AND ri <= 5468) AND bi = 1 and qis = bqis
AND ris = bris;"


0|MemNull|0|0|
1|MemNull|1|0|
2|MemNull|2|0|
3|MemNull|4|0|
4|MemNull|3|0|
5|Goto|0|73|
6|Integer|1|0|
7|OpenRead|1|2|
8|SetNumColumns|1|5|
9|Integer|0|0|
10|OpenRead|0|6|
11|SetNumColumns|0|6|
12|Integer|0|0|
13|OpenRead|2|8226|keyinfo(2,BINARY,BINARY)
14|Integer|1|0|
15|MustBeInt|1|63|
16|NotExists|1|63|
17|Integer|5604|0|
18|NotNull|-1|21|
19|Pop|1|0|
20|Goto|0|63|
21|MakeRecord|1|0|dd
22|MemStore|6|1|
23|Column|1|3|
24|NotNull|-1|27|
25|Pop|1|0|
26|Goto|0|63|
27|MakeRecord|1|0|dd
28|MoveGe|2|63|
29|MemLoad|6|0|
30|IdxGE|2|63|+
31|RowKey|2|0|
32|IdxIsNull|1|62|
33|IdxRowid|2|0|
34|MoveGe|0|0|
35|Column|0|0|
36|Integer|5604|0|
37|Eq|100|41|collseq(BINARY)
38|Column|0|1|
39|Integer|5468|0|
40|Ne|356|62|collseq(BINARY)
41|Column|0|1|
42|Column|1|4|
43|Lt|355|62|collseq(BINARY)
44|Column|0|1|
45|Integer|5468|0|
46|Gt|356|62|collseq(BINARY)
47|Column|0|4|
48|Column|1|3|
49|Ne|355|62|collseq(BINARY)
50|Column|0|5|
51|Column|1|4|
52|Ne|355|62|collseq(BINARY)
53|Column|0|3|
54|CollSeq|0|0|collseq(BINARY)
55|AggStep|3|1|max(1)
56|Column|0|0|
57|MemStore|0|1|
58|Column|0|1|
59|MemStore|1|1|
60|Column|0|2|
61|MemStore|2|1|
62|Next|2|29|
63|Close|1|0|
64|Close|0|0|
65|Close|2|0|
66|AggFinal|3|1|max(1)
67|MemLoad|0|0|
68|MemLoad|1|0|
69|MemLoad|2|0|
70|MemLoad|3|0|
71|Callback|4|0|
72|Halt|0|0|
73|Transaction|0|0|
74|VerifyCookie|0|4|
75|Transaction|1|0|
76|VerifyCookie|1|3|
77|Goto|0|6|
78|Noop|0|0|

Thanks again for any help.
__
Brannon King
¯¯

Re: [sqlite] scary warnings

[Nuno Lucas]

On 5/22/06, Cory Nelson <[EMAIL PROTECTED]> wrote:

The warning is likely because  size_t can be 64bit and "int" is always
32bit.  Either way, it should be casted.


Not really, size_t can be 64 or any other size in bits and same with int's.

If I'm not mistaken, size_t is 64 bits on Microsoft 64 bits compiler
and int is 32 bits on the same compiler, but gcc 64 bits  version have
both as 64 bits (at least for x86-64). I'll omit the 16 bit cases of
some years ago.

I'm not fully sure about this as I never had to do any programming for
64 bits, yet, so correct me if I'm wrong.

If you really want to be sure of the size of types, use the C99
 header and use things like int32_t, uint32_t, int64_t and
uint64_t (and all other types defined there).


Best regards,
~Nuno Lucas

Re: [sqlite] can you speed this query up?

[Brannon King]

select qi, ri, drl, max(score), min(score) from ...

What values of qi, ri, and drl would you want
this query to return?

What you have to do is:

   SELECT qi, ri, drl, score
 FROM ...
WHERE score=(SELECT max(score) FROM ...)

  
Thank you for the instruction, although the other query seemed to return 
the right results. (Not that I did any extensive testing)


So do I have to repeat all my constraints for both the main query and 
the subquery? Or is it even legal to specify a where clause when doing 
the max?


Also, please help me understand how the indexes are used on it in that 
situation as well.


So let's say I change my query. Coming from these tables:

CREATE TABLE bounds (bi INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT NULL, 
bqi INTEGER, bri INTEGER, bqis INTEGER, bris INTEGER);
CREATE TABLE results (qi INTEGER, ri INTEGER, drl INTEGER, score 
INTEGER, qis INTEGER, ris INTEGER);


Assume that qi and ri are only unique together. Same for bqi, bri. All 
the others are never guaranteed to be unique.


What indexes will maximize the speed of this query?

"select count(*) from results where qi = 5604 OR ri = 5468;"

Assuming I do need to repeat the constraints, what indexes will maximize the 
speed of this query?

"select qi, ri, drl, score from results, bounds where 
score=(SELECT max(score) from results, bounds 
where (qi = 5604 OR ri = 5468)

AND qi >= bqis AND qi <= 5604 AND ri >= bris AND ri <= 5468
AND bi = 1 and qis = bqis AND ris = bris)
AND (qi = 5604 OR ri = 5468)
AND qi >= bqis AND qi <= 5604 AND ri >= bris AND ri <= 5468
AND bi = 1 and qis = bqis AND ris = bris
;"

That's kind of ugly.

Re: [sqlite] can you speed this query up?

[A. Pagaltzis]

Hi Brannon,

* Brannon King <[EMAIL PROTECTED]> [2006-05-23 05:35]:
* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-23 02:35]:
> >select qi, ri, drl, max(score), min(score) from ...
> >
> >What values of qi, ri, and drl would you want
> >this query to return?
> >
> >What you have to do is:
> >
> >   SELECT qi, ri, drl, score
> > FROM ...
> >WHERE score=(SELECT max(score) FROM ...)
> >
> Thank you for the instruction, although the other query seemed
> to return the right results. (Not that I did any extensive
> testing)
> 
> So do I have to repeat all my constraints for both the main
> query and the subquery? Or is it even legal to specify a where
> clause when doing the max?

Hmm. I would normally suggest an inline view:

SELECT qi, ri, drl, score
FROM ( SELECT qi, ri, drl, score FROM [ rest of your original query here ] 
) candidates
WHERE score = ( SELECT MAX( score ) FROM candidates );

Unfortunately SQLite doesn’t seem to capable of referring to
subqueries everywhere I’d expect to be able to – it complains
that it doesn’t know of a table `candidates`.

Using a named view will work:

CREATE VIEW candidates AS SELECT qi, ri, drl, score FROM [ rest of your 
original query here ];

SELECT qi, ri, drl, score
FROM candidates 
WHERE score = ( SELECT MAX( score ) FROM candidates );

However I have a hunch that it computes the result set for the
`WHERE` clause from scratch by redoing the entire query, instead
of using the already-computed result set from the `FROM` clause.
In that case you don’t gain any performance, “only” clarity.

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] scary warnings

[Cory Nelson]

On 5/22/06, Nuno Lucas <[EMAIL PROTECTED]> wrote:

On 5/22/06, Cory Nelson <[EMAIL PROTECTED]> wrote:
> The warning is likely because  size_t can be 64bit and "int" is always
> 32bit.  Either way, it should be casted.

Not really, size_t can be 64 or any other size in bits and same with int's.


He is compiling it with VC++, where size_t is always the size of a
pointer and int is always 32bit.


If I'm not mistaken, size_t is 64 bits on Microsoft 64 bits compiler
and int is 32 bits on the same compiler, but gcc 64 bits  version have
both as 64 bits (at least for x86-64). I'll omit the 16 bit cases of
some years ago.

I'm not fully sure about this as I never had to do any programming for
64 bits, yet, so correct me if I'm wrong.


GCC's int is also 32bit on x64 (likely because x64 supports 32bit
arithmetic in 64bit apps without penalty, while 64bit operations
require a REX prefix which increases code size and can hurt cache).
The type that differs between the compilers is long, which is 32bit in
vc++ and 64bit in gcc.


If you really want to be sure of the size of types, use the C99
 header and use things like int32_t, uint32_t, int64_t and
uint64_t (and all other types defined there).


I agree, but we're going to hijack Brannon's thread if we aren't careful.

To get back on track:  Brannon, submit a patch.  An alarmingly high
number of people believe those warnings are superfluous and from what
I understand drh is one of them, so I imagine a patch is the only way
proper casting will be put in.



Best regards,
~Nuno Lucas




--
Cory Nelson
http://www.int64.org