Re: [sqlite] SQLITE3 bombs on Windows 95

2007-03-01 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Brownie wrote:
> But a lot of APIs of UNICODE version was not provided for Win95/98.
> So, you cannot use SQLite on Win95.

Yes it was.  You need to link against unicows.

http://www.microsoft.com.nsatc.net/globaldev/handson/dev/mslu_announce.mspx

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF58IPmOOfHg372QQRArerAJ4uiTRsz3A/8mTIdIr9NSq3Fk/dJgCgmq0E
uqi8Qa0r7tFJGOZ2gqMS/Vc=
=kYkg
-END PGP SIGNATURE-

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



Re: [sqlite] sqlite_prepare returns SQLITE_OK but returns NULL statement

2007-03-01 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Sander Jansen wrote:
> Ok, thanks. The reason I am asking this of course is whether this a
> common thing to occur. I assumed the statement would always be
> non-NULL if the prepare command return SQLITE_OK. I'll adjust my code
> to assume it might be null and only raise error when prepare itself
> returns an error as well.

Prepare also returns NULL if the statement is empty/pure whitespace.
Sadly step returns an error on being passed NULL.  If you do any form of
statement caching or user entered queries then you'll also get these nulls.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF58FAmOOfHg372QQRAgMMAJ41Jkd6EpKkh/gewZmrW/g47USHHgCbBQeG
mOepO9A42g3Qe9m4VmN1B9I=
=dBDD
-END PGP SIGNATURE-

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



Re: [sqlite] SQLITE3 bombs on Windows 95

2007-03-01 Thread Brownie

Currently, SQLite uses UNICODE version of Win32 API on Windows implementation.
But a lot of APIs of UNICODE version was not provided for Win95/98.
So, you cannot use SQLite on Win95.

Regards,

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



Re: [sqlite] C

2007-03-01 Thread Lloyd
Thanks John. yes, I am familiar with this way, I just wanted to know
whether there is a way to make bit field array. From Igor's reply I
understood that it is not possible in C.

Thanks,
 Lloyd

On Thu, 2007-03-01 at 09:48 -0600, John Stanton wrote:
> Use a mask to load from and store to your bit field/array by using a 
> bitwise AND (&).
> 
> Lloyd wrote:
> > Even though I know this is not the right question to ask this list, I
> > would expect some help from you.
> > 
> > The question is regarding C bit fields..
> > 
> > struct
> > {
> >  unsigned int a:1;
> > };
> > 
> > This declares a to hold 1 bit value;
> > 
> > How can I make an array of bit fields? something like, using the 16 bits
> > of a short as an array of bits
> > 
> > Thanks,
> >   Lloyd
> > 
> > Sorry for asking irrelevant question to this list. 
> > 
> > 
> > __
> > Scanned and protected by Email scanner
> > 
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> > 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -


__
Scanned and protected by Email scanner

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



Re: [sqlite] SQLITE3 bombs on Windows 95

2007-03-01 Thread Joe Wilson
I don't have access to Windows 95, but you might try finding the highest
version of sqlite3 that did work on 95 and then diff'ing the code (probably
os_win.c) in the next version to see what change broke it and then attach 
it to the ticket you've already created. The problem is likely something 
to do with file locking or unicode.

This may give you some ideas:

http://www.sqlite.org/cvstrac/rlog?f=sqlite/src/os_win.c

--- Zvi Dershowitz <[EMAIL PROTECTED]> wrote:
> Has anyone had any luck running the latest version of SQLITE3 (3.3.13) under 
> Windows 95?
> 
> I have just upgraded from revision 3.2.2 that run with no problem but newer 
> versions do not.
> 



 

It's here! Your new message!  
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/

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



Re: [sqlite] Custom collate - on field or index or both?

2007-03-01 Thread Dan Kennedy


On Thu, 2007-03-01 at 15:13 -0800, jp wrote:
> Hi, I have a custom collation sequence (e.g.
> mycollate).  Are there any advantages in terms of
> performance of declaring this collation at the table
> level, instead of just at the index level?
> 
> For example, if I have:
> 
>  CREATE TABLE people AS (
>  country_id char(02),
>  lastname varchar(100), 
>  phone varchar(50) 
>  );
> 
>  CREATE UNIQUE INDEX people_mycollate on people (
>   country_id, 
>   lastname COLLATE mycollate
>   );
> - - - - - -
> ...will the following use the index (about 500k recs)?
> - - - - - -
>  SELECT lastname FROM people 
>  WHERE country_id='US' AND lastname>'A' 
>  ORDER BY country_id,lastname COLLATE mycollate
>  LIMIT 100;
> - - - - - -

SQLite will use the index to implement the country_id='US' clause,
and the ORDER BY, but not the lastname>'A' clause. This is because
the lastname>'A' doesn't use the "mycollate" collation. 

If the COLLATE clause was specified as part of the table 
definition, then lastname>'A' would be a "mycollate" comparison
and the index would be used for this too.

Dan.



> I want to separate the table definition from the
> search/query/sort order, to have the flexibility of
> creating/dropping indexes as needed for different
> collations depending on the user's locale.
> 
> jp
> 
> 
> 
>  
> 
> The fish are biting. 
> Get more visitors on your site using Yahoo! Search Marketing.
> http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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



RE: [sqlite] Re: Any way to know the numbers of rows affected by a cmd?

2007-03-01 Thread Griggs, Donald
Regarding:

Anderson, James H (IT) wrote:
> I guess I forgot to mention the context. I'm interested in doing this 
> from the cmd shell.
>
>   
James,

You can use the command
  
 pragma count_changes=1

in the shell to have it report the number of rows affected by insert,
update, or delete statements (see
http://www.sqlite.org/pragma.html#modify for details).
==
==

But if you're using the command-line demonstration program, I don't
think the pragma will help you.

Since the command shell program (sqlite3.exe) doesn't attempt to
implement a procedural language (variables, loops, ,etc)  I don't know
that you could do what you need to do with the count anyway.

You *may* find that if you replace your single selects with:
 Select 'The count is ',  count(*) from blah, blah, blah.   --
perform select just to get a count
 Select * from blah, blah, blah -- this
time for real
The second select goes much faster because of caching.

Or maybe run a test with EXPLAIN QUERY PLAN as prefix to the select in
order to be sure it's using the indicies you expect -- maybe
*everything* can be made much faster.

A fancier option would be to cache the results of your select into a
temporary table, such as:
  CREATE TEMP TABLE stuff_temp AS SELECT blah, blah, blah;
  SELECT 'The count of stuff is', SELECT COUNT(*) FROM stuff_temp;

  You might look at "PRAGMA temp_store" to optimize handling of
temporary tables.

I think that the command line sqlite3.exe was mainly intended as a
demonstration and a testing tool -- most users want to link the library
into a language of their choice -- all the more true if speed is
particularly important.

Don't get me wrong; I myself love to mess with sqlite3.exe and create
crazy batch files. ;-)



[opinions are my own, not necessarily those of my company]

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



[sqlite] Custom collate - on field or index or both?

2007-03-01 Thread jp
Hi, I have a custom collation sequence (e.g.
mycollate).  Are there any advantages in terms of
performance of declaring this collation at the table
level, instead of just at the index level?

For example, if I have:

 CREATE TABLE people AS (
 country_id char(02),
 lastname varchar(100), 
 phone varchar(50) 
 );

 CREATE UNIQUE INDEX people_mycollate on people (
  country_id, 
  lastname COLLATE mycollate
  );
- - - - - -
...will the following use the index (about 500k recs)?
- - - - - -
 SELECT lastname FROM people 
 WHERE country_id='US' AND lastname>'A' 
 ORDER BY country_id,lastname COLLATE mycollate
 LIMIT 100;
- - - - - -

I want to separate the table definition from the
search/query/sort order, to have the flexibility of
creating/dropping indexes as needed for different
collations depending on the user's locale.

jp



 

The fish are biting. 
Get more visitors on your site using Yahoo! Search Marketing.
http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php

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



Re: [sqlite] Re: Any way to know the numbers of rows affected by a cmd?

2007-03-01 Thread Dennis Cote

Anderson, James H (IT) wrote:

I guess I forgot to mention the context. I'm interested in doing this
from the cmd shell. 

  

James,

You can use the command
 
pragma count_changes=1


in the shell to have it report the number of rows affected by insert, 
update, or delete statements (see 
http://www.sqlite.org/pragma.html#modify for details).


A count(*) is the best way to get the number of rows a select will 
return. There is really no shortcut way to do this in general, so it 
does have to execute the select to find the rows to count.


HTH
Dennis Cote

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



RE: [sqlite] Any way to know the numbers of rows affected by a cmd?

2007-03-01 Thread Anderson, James H \(IT\)
Yes, thanks, I can do that but if the table is large it's quite slow. I
was hoping there was a quicker way, as there is in sybase. 

-Original Message-
From: Tom VP [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 01, 2007 4:58 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Any way to know the numbers of rows affected by a
cmd?

Indeed, try: select count() ...
update count() ...
delete count() ...

Dr. Tom
- Original Message - 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, March 01, 2007 3:40 PM
Subject: [sqlite] Any way to know the numbers of rows affected by a cmd?


For a select, the number of rows selected.

For an update, the number of rows updates.

For a delete the number of rows deleted.

jim


NOTICE: If received in error, please destroy and notify sender. Sender
does 
not intend to waive confidentiality or privilege. Use of this email is 
prohibited when received in error.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

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



Re: [sqlite] Any way to know the numbers of rows affected by a cmd?

2007-03-01 Thread Tom VP

Indeed, try: select count() ...
update count() ...
delete count() ...

Dr. Tom
- Original Message - 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, March 01, 2007 3:40 PM
Subject: [sqlite] Any way to know the numbers of rows affected by a cmd?


For a select, the number of rows selected.

For an update, the number of rows updates.

For a delete the number of rows deleted.

jim


NOTICE: If received in error, please destroy and notify sender. Sender does 
not intend to waive confidentiality or privilege. Use of this email is 
prohibited when received in error.



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



Re: [sqlite] sqlite_prepare returns SQLITE_OK but returns NULL statement

2007-03-01 Thread Sander Jansen

Ok, thanks. The reason I am asking this of course is whether this a
common thing to occur. I assumed the statement would always be
non-NULL if the prepare command return SQLITE_OK. I'll adjust my code
to assume it might be null and only raise error when prepare itself
returns an error as well.

Sander

On 3/1/07, Dennis Cote <[EMAIL PROTECTED]> wrote:

Sander Jansen wrote:
> Using SqLite 3.3.3 I'm trying to prepare the following statement:
>
> "CREATE INDEX IF NOT EXISTS someindexname ON sometable(somecolumn);"
>
> It returns SQLITE_OK but returns a NULL statement. ( I think a newer
> version doesn't have this behaviour). Does this actually mean that it
> is a unsupported SQL query? If I remove
> "IF NOT EXISTS" the prepare statement will actually return an error
> saying the Index already exists.
Sander,

If the index already exists, then a null statement is all you need to
create it. :-)

I assume you are saying that sqlite3_prepare is setting the statement
pointer to NULL. I think there was a bug report about this behavior in
an earlier version  (< 3.3.13)  of sqlite. You might want to try it with
a current version to see if you get the same behavior.

As a work around, you could simply not execute (i.e. don't call
sqlite3_step) a NULL statement. Now that I think about it, sqlite may
already have that check in place so that sqlite_step(NULL) does nothing
and returns.

HTH
Dennis Cote

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




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



RE: [sqlite] Re: Any way to know the numbers of rows affected by a cmd?

2007-03-01 Thread Anderson, James H \(IT\)
I guess I forgot to mention the context. I'm interested in doing this
from the cmd shell. 

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 01, 2007 4:02 PM
To: SQLite
Subject: [sqlite] Re: Any way to know the numbers of rows affected by a
cmd?

Anderson, James H (IT)
 wrote:
> For a select, the number of rows selected.

Just count them as you step through them.

> For an update, the number of rows updates.
>
> For a delete the number of rows deleted.

sqlite3_changes, sqlite3_total_changes

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

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



[sqlite] Re: Any way to know the numbers of rows affected by a cmd?

2007-03-01 Thread Igor Tandetnik

Anderson, James H (IT)
 wrote:

For a select, the number of rows selected.


Just count them as you step through them.


For an update, the number of rows updates.

For a delete the number of rows deleted.


sqlite3_changes, sqlite3_total_changes

Igor Tandetnik 



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



[sqlite] Any way to know the numbers of rows affected by a cmd?

2007-03-01 Thread Anderson, James H \(IT\)
For a select, the number of rows selected.

For an update, the number of rows updates.

For a delete the number of rows deleted.

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.


Re: [sqlite] sqlite_prepare returns SQLITE_OK but returns NULL statement

2007-03-01 Thread Dennis Cote

Sander Jansen wrote:

Using SqLite 3.3.3 I'm trying to prepare the following statement:

"CREATE INDEX IF NOT EXISTS someindexname ON sometable(somecolumn);"

It returns SQLITE_OK but returns a NULL statement. ( I think a newer
version doesn't have this behaviour). Does this actually mean that it
is a unsupported SQL query? If I remove
"IF NOT EXISTS" the prepare statement will actually return an error
saying the Index already exists.

Sander,

If the index already exists, then a null statement is all you need to 
create it. :-)


I assume you are saying that sqlite3_prepare is setting the statement 
pointer to NULL. I think there was a bug report about this behavior in 
an earlier version  (< 3.3.13)  of sqlite. You might want to try it with 
a current version to see if you get the same behavior.


As a work around, you could simply not execute (i.e. don't call 
sqlite3_step) a NULL statement. Now that I think about it, sqlite may 
already have that check in place so that sqlite_step(NULL) does nothing 
and returns.


HTH
Dennis Cote

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



[sqlite] sqlite_prepare returns SQLITE_OK but returns NULL statement

2007-03-01 Thread Sander Jansen

Using SqLite 3.3.3 I'm trying to prepare the following statement:

"CREATE INDEX IF NOT EXISTS someindexname ON sometable(somecolumn);"

It returns SQLITE_OK but returns a NULL statement. ( I think a newer
version doesn't have this behaviour). Does this actually mean that it
is a unsupported SQL query? If I remove
"IF NOT EXISTS" the prepare statement will actually return an error
saying the Index already exists.

Thanks,

Sander

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



Re: [sqlite] sqlite c++ interfaces

2007-03-01 Thread Dennis Cote

Pavan wrote:

I was googling for c++ interfaces for sqlite and found sqlitemm provides.
But, i am unable to download the code.  Can some one pls point me to link
from where i can download the c++ interfaces for sqlite.

This page http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers has links 
to many C++ interface wrappers for sqlite. I would suggest CppSQLite as 
a good starting point.


HTH
Dennis Cote

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



Re: [sqlite] how to insert and select a date column

2007-03-01 Thread Dennis Cote

Rafi Cohen wrote:

Just another small question to complete this subject: in case of
prepared insert statements, assuming the date is stored as string as in
your example, do I use sqlite3_bind_text with a variable pointing to a
string containing the date I want to insert? In other words, is this a
string just like any other string I insert into the table?

  
Yes, it is a normal string as far as sqlite is concerned. It is only you 
and the date and time functions that interpret it as a date.


HTH
Dennis Cote


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



[sqlite] Re: subselect

2007-03-01 Thread Igor Tandetnik

kokenge <[EMAIL PROTECTED]> wrote:

Here is my exact sql as executed:
---
 This works
SELECT employee.empl_num
FROM employee
JOIN job_history
ON   job_history.empl_num = employee.empl_num

 This does not work
SELECT employee.empl_num
FROM employee
JOIN job_history
ON   job_history.empl_num = employee.empl_num
AND  job_history.last_date = (
SELECT MAX(j1.last_date)
FROM job_history AS j1
WHERE j1.empl_num = employee.empl_num )
-
Here is my error message
Error : SQL logic error or missing database
 no such column: employee.empl_num
-
Here is my table layout
#
# Table structure for table: employee
#
CREATE TABLE employee ( empl_num INT(10) NOT NULL PRIMARY KEY, dept_id
VARCHAR(4) NOT NULL DEFAULT 'WMKR', username VARCHAR(16) NOT NULL,
first_name VARCHAR(22), middle_name VARCHAR(22), last_name
VARCHAR(22), addr1 VARCHAR(22), addr2 VARCHAR(22), city VARCHAR(22),
state CHAR(3), zip VARCHAR(10), country VARCHAR(4) NOT NULL DEFAULT
'USA', phone VARCHAR(17), e_mail VARCHAR(40), password VARCHAR(16)
NOT NULL, password_hint VARCHAR(50) NOT NULL, dial_log_id
VARCHAR(100) NOT NULL, pass_id VARCHAR(20) NOT NULL ); #
# Table structure for table: job_history
#
CREATE TABLE job_history ( job_num INT, empl_num INT , last_date DATE
); # 


Everything just works for me. I've just copied and pasted all statements 
as shown into sqlite3 session.


I'd check the code for typos, very carefully.

Igor Tandetnik 



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



Re: [sqlite] use of sqlite in a multithread C application

2007-03-01 Thread Ken
I found that although sqlite claims thread safeness it is actually in your 
hands to implement a thread safe access pattern. 
 
 Here is how I implemented my sqlite thread saftey. Each thread opens its on 
connection.
 
 All operations begin with a 
   do {
 BEGIN  EXCLUSIVE  
 if (isbusy)  (sleep some amount of time... ).
 
  } while (sqlite isbusy);
 
YOUR SQL STATEMENTS HERE.
 
  And end with a 
  COMMIT;
 
 The begin forces sqlite to lock the DB for exclusive access. This makes 
the remaining access error handling relatively simple. No need to check for 
busy and do rollbacks with restarting logic... Just handle errors,  in my log 
the error to a file and returns a Failure code which typically causes the 
application to exit.
 
 Don't to forget to compile the sqlite library with -DTHREAD_SAFE
 

Rafi Cohen <[EMAIL PROTECTED]> wrote: Hi, I read the good article on this 
subject and also the api refference
in the documentation and still feel that I need to ask the following
question.
My application has 2 threads. The main one needs to retrieve data thru
select commands but does not change the database, while the other thread
will change the database quite often.
My questions are: should I open the database in each thread separately
or can I open it once for the whole program? in the second case, does it
matter inh which thread I open it? last, if the main thread happens to
retrieve data while the other thread is in a transaction changing the
database, I would prefer the main thread wait till the end of the
transaction and retrieve the most updated data. How do you suggest to
implement it?
looping on sqlite3_busy_handler should be the way to go?
Thanks, Rafi.



Re: [sqlite] subselect

2007-03-01 Thread kokenge

Yes your solution will get you the last date they worked on a job.
Probalm is there is other information in the job record I need.  So if I
select other stuff from the job record it may not be from the last job
record. For example - Using this method if I select the MIN(job date) and
salary on that job, I may get the same salary if I selected the MAX(job
date). So not only do I need the last time he worked on the job, I also need
the complete information from that job_history record.
Thanks for the help...
Have a great day.
Dan


Dennis Cote wrote:
> 
> kokenge wrote:
>> This is such a simple SQL statement. So sorry for the question, but I
>> can't
>> get it to work.
>> I'm trying to get a list of employees and the last time they worked on a
>> job. 
>> FIles are. 
>> employee file : with empl_num = employee number
>> job_history file : with empl_num,  job_num, and last_date = last date the
>> employee worked on a job
>> Each employee has worked many jobs during his employment . so employee to
>> job_history is 1 to many
>> The sql is very simple and for some reason I keep getting a error saying
>> the
>> it can't reference stuff in the subselect to the file in the Select?
>> I
>> have it working in all my other databases.
>> -
>> SELECT *
>> FROM employee
>> JOIN job_history 
>> ON   job_history.empl_num = employee.empl_num
>> AND job_history.last_date = (SELECT max(j1.last_date) 
>> FROM job_history as j1
>> WHERE j1.empl_num = employee.empl_num)
>> -
>> Just to simple - so what am I doing wrong
>>
>> Thanks for the help..
>> Dan
>>
>>
>>
>>
> Dan,
> 
> I'm not sure I understand your problem but this is what I would try 
> based on your description.
> 
> select employee_name, job_name, max(last_date) as last_date_on_job
> from employee as e
> left join job_history as j on j.empl_num = e.empl_num
> group by e.empl_num, j.job_num;
> 
> HTH
> Dennis Cote
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/subselect-tf3327306.html#a9253538
Sent from the SQLite mailing list archive at Nabble.com.


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



Re: [sqlite] sqlite c++ interfaces

2007-03-01 Thread Clay Dowling

Pavan wrote:
> Hi,
>
> I was googling for c++ interfaces for sqlite and found sqlitemm provides.
> But, i am unable to download the code.  Can some one pls point me to link
> from where i can download the c++ interfaces for sqlite.

You can try my wrapper at http://www.lazarusid.com/download/sqdataset.tar.gz

It's testing, working, and generally makes my life easier.

Clay
-- 
Simple Content Management
http://www.ceamus.com


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



[sqlite] use of sqlite in a multithread C application

2007-03-01 Thread Rafi Cohen
Hi, I read the good article on this subject and also the api refference
in the documentation and still feel that I need to ask the following
question.
My application has 2 threads. The main one needs to retrieve data thru
select commands but does not change the database, while the other thread
will change the database quite often.
My questions are: should I open the database in each thread separately
or can I open it once for the whole program? in the second case, does it
matter inh which thread I open it? last, if the main thread happens to
retrieve data while the other thread is in a transaction changing the
database, I would prefer the main thread wait till the end of the
transaction and retrieve the most updated data. How do you suggest to
implement it?
looping on sqlite3_busy_handler should be the way to go?
Thanks, Rafi.


[sqlite] sqlite c++ interfaces

2007-03-01 Thread Pavan

Hi,

I was googling for c++ interfaces for sqlite and found sqlitemm provides.
But, i am unable to download the code.  Can some one pls point me to link
from where i can download the c++ interfaces for sqlite.

Thanks,
Pavan.

--
'
Always finish stronger than you start
*


RE: [sqlite] How fast is the sqlite connection created?

2007-03-01 Thread Samuel R. Neff

While cursors are generic to all databases, to me the test and code sample
seems to be very specific to Python.  Looking at the code I can't imagine
there are actually any SQLite C calls within the cursor() method.
sqlite3.Connection() undoubtedly maps to sqlite3_open() and cursor.execute()
would map to sqlite3_exec (or more likely the lower-level functions).  But
there's nothing in SQLite that I can see which would map to cursor() (i.e.,
nothing between opening the connection and preparing a statement).

For example in .NET an analogous routine would be:

using(DbConnection cnn = factory.CreateConnection()) {
  cnn.ConnectionString = "...";
  cnn.Open();
  using(DBCommand cmd = cnn.CreateCommand()) {
cmd.CommandTest = "SELECT * FROM TABLE";
using(DbDataReader reader = cmd.ExecuteReader()) {
  ... read rows here ...
}
  }
}

So the cursor() method in Python is somewhat like the CreateCommand() method
in .NET and in .NET CreateCommand is just an internal object allocation, it
has nothing to do with SQLite.  However, in .NET we would also want to reuse
commands just like we use connections 'cause a command represents a parsed
statement.

Best regards,

Sam



---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 01, 2007 10:50 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How fast is the sqlite connection created?

A cursor is the thing that you use to run your queries. Eg in Python's 
wrappers you import the wrapper (library, module) Connections to the 
database and create cursors on those Connections to do the actual work.

import sqlite3
conn=sqlite3.Connection(dbname)
crsr=conn.cursor()
crsr.execute("select * trom table")
result_set=crsr.fetchone()
...
result_set=crsr.fetchall()

and so on. SQLite cursors can only move forward in the result set. AIUI 
cursors in some older/bigger databases can move in either direction.

FWIW it looks like calling cursor() takes ~1.9us on my machine with 
Python2.5, sqlite3, disk file with schema of "create table t(a,b,c)".

Martin


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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



Re: [sqlite] subselect

2007-03-01 Thread kokenge


Here is my exact sql as executed:
---
  This works 
SELECT employee.empl_num
FROM employee 
JOIN job_history 
ON   job_history.empl_num = employee.empl_num

  This does not work
SELECT employee.empl_num
FROM employee 
JOIN job_history 
ON   job_history.empl_num = employee.empl_num 
AND  job_history.last_date = (
SELECT MAX(j1.last_date)
FROM job_history AS j1
WHERE j1.empl_num = employee.empl_num )
-
Here is my error message
Error : SQL logic error or missing database
no such column: employee.empl_num
-
Here is my table layout
#
# Table structure for table: employee
#
CREATE TABLE employee ( empl_num INT(10) NOT NULL PRIMARY KEY, dept_id
VARCHAR(4) NOT NULL DEFAULT 'WMKR', username VARCHAR(16) NOT NULL,
first_name VARCHAR(22), middle_name VARCHAR(22), last_name VARCHAR(22),
addr1 VARCHAR(22), addr2 VARCHAR(22), city VARCHAR(22), state CHAR(3), zip
VARCHAR(10), country VARCHAR(4) NOT NULL DEFAULT 'USA', phone VARCHAR(17),
e_mail VARCHAR(40), password VARCHAR(16) NOT NULL, password_hint VARCHAR(50)
NOT NULL, dial_log_id VARCHAR(100) NOT NULL, pass_id VARCHAR(20) NOT NULL );
#
# Table structure for table: job_history
#
CREATE TABLE job_history ( job_num INT, empl_num INT , last_date DATE );
# 


Igor Tandetnik wrote:
> 
> kokenge <[EMAIL PROTECTED]> wrote:
>> This is such a simple SQL statement. So sorry for the question, but I
>> can't get it to work.
>> I'm trying to get a list of employees and the last time they worked
>> on a job.
>> FIles are.
>> employee file : with empl_num = employee number
>> job_history file : with empl_num,  job_num, and last_date = last date
>> the employee worked on a job
>> Each employee has worked many jobs during his employment . so
>> employee to job_history is 1 to many
>> The sql is very simple and for some reason I keep getting a error
>> saying the it can't reference stuff in the subselect to the file in
>> the Select? I have it working in all my other databases.
>> -
>> SELECT *
>> FROM employee
>> JOIN job_history
>> ON   job_history.empl_num = employee.empl_num
>> AND job_history.last_date = (SELECT max(j1.last_date)
>> FROM job_history as j1
>> WHERE j1.empl_num = employee.empl_num)
>> -
>> Just to simple - so what am I doing wrong
> 
> I don't get any syntax errors for this statement. The problem must be in 
> something you don't show. Quote the exact error message.
> 
> Igor Tandetnik 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/subselect-tf3327306.html#a9252497
Sent from the SQLite mailing list archive at Nabble.com.


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



Re: [sqlite] How fast is the sqlite connection created?

2007-03-01 Thread Martin Jenkins

Samuel R. Neff wrote:

Eric,

Sorry if this is obvious to everyone else but not to me.. what exactly is
cursor()?  I don't see it anywhere in the C API and the wrapper I'm using
(SQLite .NET) doesn't have any corresponding method.


A cursor is the thing that you use to run your queries. Eg in Python's 
wrappers you import the wrapper (library, module) Connections to the 
database and create cursors on those Connections to do the actual work.


import sqlite3
conn=sqlite3.Connection(dbname)
crsr=conn.cursor()
crsr.execute("select * trom table")
result_set=crsr.fetchone()
...
result_set=crsr.fetchall()

and so on. SQLite cursors can only move forward in the result set. AIUI 
cursors in some older/bigger databases can move in either direction.


FWIW it looks like calling cursor() takes ~1.9us on my machine with 
Python2.5, sqlite3, disk file with schema of "create table t(a,b,c)".


Martin

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



Re: [sqlite] C

2007-03-01 Thread John Stanton
Use a mask to load from and store to your bit field/array by using a 
bitwise AND (&).


Lloyd wrote:

Even though I know this is not the right question to ask this list, I
would expect some help from you.

The question is regarding C bit fields..

struct
{
 unsigned int a:1;
};

This declares a to hold 1 bit value;

How can I make an array of bit fields? something like, using the 16 bits
of a short as an array of bits

Thanks,
  Lloyd

Sorry for asking irrelevant question to this list. 



__
Scanned and protected by Email scanner

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




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



Re: [sqlite] Performance problem

2007-03-01 Thread Stephen Toney
Richard,

Thanks for the additional info. I'll look into the multi-column index
idea. Sounds as if it might be the solution.

Stephen



On Thu, 2007-03-01 at 14:42 +, [EMAIL PROTECTED] wrote:
> Stephen Toney <[EMAIL PROTECTED]> wrote:
> > Thanks, Igor, Richard, and Tom,
> > 
> > Why doesn't SQLite use the index on key? I can see from the plan that it
> > doesn't, but why not? Can only one index be used per query?
> > 
> > This seems strange. I have used SQL Server and Visual Foxpro for this
> > same problem, and they both handle this query in a second if the indexes
> > are there.
> 
> SQLite is limited to a single index per table of the FROM clause.
> (In your case the same table occurs twice in the FROM clause, so
> each instance can use a separate indices, but each instance can
> only use a single index.)  Other systems relax this restriction
> through the use of bitmap indices.  SQLite does not (directly) 
> support bitmap indices.  You can achieve about the same thing
> as a bitmap index by playing games with rowids, but the SQL
> needed to do so is convoluted.  In your case, I think the query
> would need to be:
> 
>  SELECT count(*)
>FROM keyword AS a CROSS JOIN keyword AS b
>   WHERE a.value='music'
> AND b.rowid IN (
>  SELECT rowid FROM keyword WHERE value='history'
>  INTERSECT
>  SELECT rowid FROM keyword WHERE key=a.key
> );
> 
> It seems so much simpler to use a multi-column index.  It is almost
> certainly going to be faster.
> 
> > 
> > Is there a good place to read more about this SQLite behavior? I'm
> > fairly familiar with the online documentation and don't recall reading
> > this.
> > 
> 
> You might get a few hints at http://www.sqlite.org/php2004/page-001.html
> and the pages that follow.  That is from a talk I gave in 2004.  It
> is somewhat out of date.  My goal for this calendar year is to get
> some detailed documentation online about the kinds of issues you
> are seeing.
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
-- 

Stephen Toney
Systems Planning
[EMAIL PROTECTED]
http://www.systemsplanning.com


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



Re: [sqlite] subselect

2007-03-01 Thread Dennis Cote

kokenge wrote:

This is such a simple SQL statement. So sorry for the question, but I can't
get it to work.
I'm trying to get a list of employees and the last time they worked on a
job. 
FIles are. 
employee file : with empl_num = employee number

job_history file : with empl_num,  job_num, and last_date = last date the
employee worked on a job
Each employee has worked many jobs during his employment . so employee to
job_history is 1 to many
The sql is very simple and for some reason I keep getting a error saying the
it can't reference stuff in the subselect to the file in the Select? I
have it working in all my other databases.
-
SELECT *
FROM employee
JOIN job_history 
ON   job_history.empl_num = employee.empl_num
AND job_history.last_date = (SELECT max(j1.last_date) 
FROM job_history as j1

WHERE j1.empl_num = employee.empl_num)
-
Just to simple - so what am I doing wrong

Thanks for the help..
Dan





Dan,

I'm not sure I understand your problem but this is what I would try 
based on your description.


select employee_name, job_name, max(last_date) as last_date_on_job
from employee as e
left join job_history as j on j.empl_num = e.empl_num
group by e.empl_num, j.job_num;

HTH
Dennis Cote



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



[sqlite] Re: subselect

2007-03-01 Thread Igor Tandetnik

kokenge <[EMAIL PROTECTED]> wrote:

This is such a simple SQL statement. So sorry for the question, but I
can't get it to work.
I'm trying to get a list of employees and the last time they worked
on a job.
FIles are.
employee file : with empl_num = employee number
job_history file : with empl_num,  job_num, and last_date = last date
the employee worked on a job
Each employee has worked many jobs during his employment . so
employee to job_history is 1 to many
The sql is very simple and for some reason I keep getting a error
saying the it can't reference stuff in the subselect to the file in
the Select? I have it working in all my other databases.
-
SELECT *
FROM employee
JOIN job_history
ON   job_history.empl_num = employee.empl_num
AND job_history.last_date = (SELECT max(j1.last_date)
FROM job_history as j1
WHERE j1.empl_num = employee.empl_num)
-
Just to simple - so what am I doing wrong


I don't get any syntax errors for this statement. The problem must be in 
something you don't show. Quote the exact error message.


Igor Tandetnik 



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



Re: [sqlite] Performance problem

2007-03-01 Thread drh
Stephen Toney <[EMAIL PROTECTED]> wrote:
> 
> 4. We do not preserve case in the index, so it can ignore incorrect
> capitalization in the search terms. Maybe FTS does this too?

That's a function of your stemmer.  The default stemmers in FTS2
both ignore capitalization.

> 
> 5. For historical reasons, we use NCRs like  instead of UTF-8. Our
> programs remove these before indexing.
> 

You can do this in your stemmer.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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



Re: [sqlite] Performance problem

2007-03-01 Thread drh
Stephen Toney <[EMAIL PROTECTED]> wrote:
> Thanks, Igor, Richard, and Tom,
> 
> Why doesn't SQLite use the index on key? I can see from the plan that it
> doesn't, but why not? Can only one index be used per query?
> 
> This seems strange. I have used SQL Server and Visual Foxpro for this
> same problem, and they both handle this query in a second if the indexes
> are there.

SQLite is limited to a single index per table of the FROM clause.
(In your case the same table occurs twice in the FROM clause, so
each instance can use a separate indices, but each instance can
only use a single index.)  Other systems relax this restriction
through the use of bitmap indices.  SQLite does not (directly) 
support bitmap indices.  You can achieve about the same thing
as a bitmap index by playing games with rowids, but the SQL
needed to do so is convoluted.  In your case, I think the query
would need to be:

 SELECT count(*)
   FROM keyword AS a CROSS JOIN keyword AS b
  WHERE a.value='music'
AND b.rowid IN (
 SELECT rowid FROM keyword WHERE value='history'
 INTERSECT
 SELECT rowid FROM keyword WHERE key=a.key
);

It seems so much simpler to use a multi-column index.  It is almost
certainly going to be faster.

> 
> Is there a good place to read more about this SQLite behavior? I'm
> fairly familiar with the online documentation and don't recall reading
> this.
> 

You might get a few hints at http://www.sqlite.org/php2004/page-001.html
and the pages that follow.  That is from a talk I gave in 2004.  It
is somewhat out of date.  My goal for this calendar year is to get
some detailed documentation online about the kinds of issues you
are seeing.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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



[sqlite] subselect

2007-03-01 Thread kokenge

This is such a simple SQL statement. So sorry for the question, but I can't
get it to work.
I'm trying to get a list of employees and the last time they worked on a
job. 
FIles are. 
employee file : with empl_num = employee number
job_history file : with empl_num,  job_num, and last_date = last date the
employee worked on a job
Each employee has worked many jobs during his employment . so employee to
job_history is 1 to many
The sql is very simple and for some reason I keep getting a error saying the
it can't reference stuff in the subselect to the file in the Select? I
have it working in all my other databases.
-
SELECT *
FROM employee
JOIN job_history 
ON   job_history.empl_num = employee.empl_num
AND job_history.last_date = (SELECT max(j1.last_date) 
FROM job_history as j1
WHERE j1.empl_num = employee.empl_num)
-
Just to simple - so what am I doing wrong

Thanks for the help..
Dan




-- 
View this message in context: 
http://www.nabble.com/subselect-tf3327306.html#a9250870
Sent from the SQLite mailing list archive at Nabble.com.


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



Re: [sqlite] Sqlite3 in MAC OS

2007-03-01 Thread T

Hi Kirrthana,

Im developing an application using sqlite3 in MAC OS,I just wanted  
to know wheather sqlite3 can be used in MAC OS.


Yes. It's already built in. Mac OS X 10.4 includes SQLite version  
3.1.3. And you can easily install the latest version 3.3.13 by  
following the instructions at:

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

Or specifically:

1. Ensure that you have the developer tools installed. They come with  
the Mac OS, on a separate DVD.


2. Download the latest SQLite, currently: http://www.sqlite.org/ 
sqlite-3.3.13.tar.gz


3. Double click on the .tar file. It will create a sqlite-3.3.13 folder.

4. Launch "Terminal" (double click it in /Applications/Utilities).

5. Type "cd " (without the quotes) and drag your expanded folder  
(from step 3) into the Terminal window. Hit return.


6. type: ./configure  (And hit return)

7. type: make  (And hit return)

8. type: sudo make install (And hit return. Enter your password when  
asked.)


Then you can access the new C libraries and the new sqlite3 command  
line tool. To access the new (rather than the old) sqlite3 command,  
specify the full path: /usr/local/bin/sqlite3


There are of course several GUI programs for Mac OS which usually  
include their own compiled SQLite code. You just double click them  
like any other Mac app. For example:


http://www.sqlabs.net/sqlitemanager.php
http://www.software-by-mabe.com/software/freeware.html#sqlitequery
http://sqlitebrowser.sourceforge.net/
http://sqlitecc.sourceforge.net/

There is also a bunch of other programs that use SQLite for their own  
data storage, such as Apple's own Mail program (for mail indexing),  
and the "Core Data" data storage mechanism for developers to include  
in their own software.


If so can the same sqlite3 library and the executable used in  
windows can be used in MAC OS or a

different version has to be used.


Same source, and same result of running the code. But since it's a  
different platform, it has different compiled binaries.


Tom


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



[sqlite] SQLITE3 bombs on Windows 95

2007-03-01 Thread Zvi Dershowitz
Has anyone had any luck running the latest version of SQLITE3 (3.3.13) under 
Windows 95?

I have just upgraded from revision 3.2.2 that run with no problem but newer 
versions do not.


RE: [sqlite] How fast is the sqlite connection created?

2007-03-01 Thread Samuel R. Neff

Eric,

Sorry if this is obvious to everyone else but not to me.. what exactly is
cursor()?  I don't see it anywhere in the C API and the wrapper I'm using
(SQLite .NET) doesn't have any corresponding method.

In any case, only true way to know how expensive it is is to do some
testing.  The closer the test is to your real schema/data the more
applicable will be the test to your situation.  For example, my testing
found that open takes 17 ms for my schema, but simpler schemas require only
one or two.  All testing is relative to exactly what is being tested.

Best regards,

Sam

 


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Eric S. Johansson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 28, 2007 9:30 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How fast is the sqlite connection created?

Samuel R. Neff wrote:
> Some of this performance gain is probably related to caching data and
query
> plan, not just opening the connection, but still that caching is
connection
> related and is lost when you close the connection so it's a very
real-world
> valid comparison.

no surprise that connect() is expensive but what is the cost of 
cursor()?  is it cheap or expensive?

-- 
Speech-recognition in use.  It makes mistakes, I correct some.



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



RE: [sqlite] Performance problem

2007-03-01 Thread Griggs, Donald
 

Regarding:

   "Can only one index be used per query?"


Yes, I believe that *is* the defined behaviour of sqlite (though it does
support compound indicies).  Larger DBMS often have very involved code
to determine query plans.


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



Re: [sqlite] Performance problem

2007-03-01 Thread Stephen Toney
On Thu, 2007-03-01 at 12:46 +, [EMAIL PROTECTED] wrote:

> Or maybe better yet:  Have you looked into using FTS2 for whatever
> it is you are trying to do?  Full-text search is hard to get right
> and you appear to be trying to create your own.  Why not use a FTS
> subsystem that is already written and testing and available to you?
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>


Several reasons:
1. App is 10 years old and working well with other DBMSs, so why mess
with it? This problem only occurred since using SQLite as the DBMS.

2. Queries must work with other DBMSs with minimal tinkering (SQL
Server, Oracle, Foxpro, etc.) -- using ODBC.

3. Our indexing is tuned to museums, libraries, and other cultural
organizations. For example, certain characters are converted before
indexing (such as OE diphthong to the two letters "OE"). We also index
words with hyphens and apostrophes both with and without the punctuation
so the searcher can enter them various ways.

4. We do not preserve case in the index, so it can ignore incorrect
capitalization in the search terms. Maybe FTS does this too?

5. For historical reasons, we use NCRs like  instead of UTF-8. Our
programs remove these before indexing.

I am considering FTS for another project though. I appreciate the
suggestion!

Stephen
-- 

Stephen Toney
Systems Planning
[EMAIL PROTECTED]
http://www.systemsplanning.com


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



[sqlite] R: [sqlite] Sqlite for Embedded Devices

2007-03-01 Thread Francesco Andrisani
Hi,
I've compiled sqlite2 and sqlite3 for some embedded architecture (mipsel, avr, 
arm, cris) and it work fine.
If you want an help, post your problems.

Bye

Francesco 

-Messaggio originale-
Da: Jakub Ladman [mailto:[EMAIL PROTECTED] 
Inviato: giovedì 1 marzo 2007 14.21
A: sqlite-users@sqlite.org
Oggetto: Re: [sqlite] Sqlite for Embedded Devices

Dne čtvrtek 01 březen 2007 13:11 Pavan napsal(a):
> Hi,
>
> Could anyone share their observations/comments on having used sqlite 
> as DB for embedded linux environment.

I have just started.
Now i have sqlite compiled for embedded linux based on kernel 2.4.18 and uclibc 
library on Renesas SuperH cpu SH7760, it seems to be working good, but i am 
thorough beginner in SQL technology generally.
Maybe later ...

Jakub

>
> Thanks,
> Pavan.

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



Re: [sqlite] Sqlite for Embedded Devices

2007-03-01 Thread anis chaaba

I'm using sqlite on embedded device and I didn't have issues till now. good
performance and scalability.

2007/3/1, Jakub Ladman <[EMAIL PROTECTED]>:


Dne čtvrtek 01 březen 2007 13:11 Pavan napsal(a):
> Hi,
>
> Could anyone share their observations/comments on having used sqlite as
DB
> for embedded linux environment.

I have just started.
Now i have sqlite compiled for embedded linux based on kernel 2.4.18 and
uclibc library on Renesas SuperH cpu SH7760, it seems to be working good,
but
i am thorough beginner in SQL technology generally.
Maybe later ...

Jakub

>
> Thanks,
> Pavan.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




RE: [sqlite] Performance problem

2007-03-01 Thread Stephen Toney
Thanks, Igor, Richard, and Tom,

Why doesn't SQLite use the index on key? I can see from the plan that it
doesn't, but why not? Can only one index be used per query?

This seems strange. I have used SQL Server and Visual Foxpro for this
same problem, and they both handle this query in a second if the indexes
are there.

Is there a good place to read more about this SQLite behavior? I'm
fairly familiar with the online documentation and don't recall reading
this.

Thanks a million!
Stephen


On Thu, 2007-03-01 at 07:54 -0500, Tom Briggs wrote:
>You will likely be well served by a compound index on (value,key).
> As the schema stands now, the indexes will help find records with
> matching values, but not with matching keys; providing one index that
> correlates the two should help.
> 
>Disclaimer: I haven't recreated your schema, added said index and
> checked that the query plan produced is better.  Don't assume this to be
> good advice without trying it. :)
> 
>-Tom
> 
> > -Original Message-
> > From: Stephen Toney [mailto:[EMAIL PROTECTED] 
> > Sent: Thursday, March 01, 2007 7:00 AM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] Performance problem
> > 
> > Dear experts:
> > 
> > I'm having a performance problem I can't understand. I am running a
> > "select count(*)" query joining a table on itself, and the query runs
> > for five minutes using Sqlite3.exe before I get bored and 
> > kill it. This
> > is on a dual-core box with 4GB of memory, running Windows XP Pro. The
> > Sqlite version is 3.3.7.
> > 
> > Here's the problem query with the plan:
> > 
> > select count(*) from keyword a, keyword b where a.key=b.key and
> > a.value='music' and b.value='history';
> > 
> > 0|0|TABLE keyword AS a WITH INDEX value
> > 1|1|TABLE keyword AS b WITH INDEX value
> > 
> > Here's the schema
> > 
> > CREATE TABLE keyword (key, contyp int, imagecount int, searchcat int,
> > value, nextword, sec, ipr, fldseq int);
> > CREATE INDEX key on keyword(key);
> > CREATE INDEX nextword on keyword(nextword);
> > CREATE INDEX value on keyword(value);
> > 
> > The table has 3,486,410 records and the SQLite database totals 320MB.
> > There are a few small tables in the db besides the KEYWORD table.
> > 
> > 4,318 records have value='music' and 27,058 have value='history'. The
> > keys are 12-byte strings. That doesn't seem like an extreme 
> > case to me. 
> > 
> > Using DBI::ODBC::SQLite in a web application the result is just as bad
> > -- the server times out.
> > 
> > Any suggestions would be much appreciated!
> > 
> > 
> > Stephen Toney
> > Systems Planning
> > [EMAIL PROTECTED]
> > http://www.systemsplanning.com
> > 
> > 
> > --
> > ---
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > --
> > ---
> > 
> > 
-- 

Stephen Toney
Systems Planning
[EMAIL PROTECTED]
http://www.systemsplanning.com


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



Re: [sqlite] Sqlite for Embedded Devices

2007-03-01 Thread Jakub Ladman
Dne čtvrtek 01 březen 2007 13:11 Pavan napsal(a):
> Hi,
>
> Could anyone share their observations/comments on having used sqlite as DB
> for embedded linux environment.

I have just started.
Now i have sqlite compiled for embedded linux based on kernel 2.4.18 and 
uclibc library on Renesas SuperH cpu SH7760, it seems to be working good, but 
i am thorough beginner in SQL technology generally.
Maybe later ...

Jakub

>
> Thanks,
> Pavan.

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



Re: [sqlite] Re: C

2007-03-01 Thread Lloyd
Thanks Igor. This is what I wanted.

On Thu, 2007-03-01 at 07:46 -0500, Igor Tandetnik wrote:
> Lloyd  wrote:
> > How can I make an array of bit fields? something like, using the 16
> > bits
> > of a short as an array of bits
> 
> You can't. But, if you can use C++ rather than C, there's std::bitset 
> class that does just that.
> 
> Igor Tandetnik 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -


__
Scanned and protected by Email scanner

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



Re: [sqlite] Performance problem

2007-03-01 Thread drh
[EMAIL PROTECTED] wrote:
> Stephen Toney <[EMAIL PROTECTED]> wrote:
>> > 
> > Here's the problem query with the plan:
> > 
> > select count(*) from keyword a, keyword b where a.key=b.key and
> > a.value='music' and b.value='history';
> > 
> 
> A faster approach would be:
> 
>SELECT (SELECT count(*) FROM keyword WHERE value='music')*
>   (SELECT count(*) FROM keyword WHERE value='history');
> 

Never mind.  I overlooked the "a.key=b.key" term in your original
query
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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



Re: [sqlite] Why no sqlite3_exec16?

2007-03-01 Thread drh
=?ISO-8859-1?Q?Daniel_=D6nnerby?= <[EMAIL PROTECTED]> wrote:
> Why isn't there a sqlite3_exec16 function?
> 

Because it is not needed.  

The sqlite3_exec() function is implemented in terms of lower-level
SQLite APis.  See the source file legacy.c for details.  If you
want an sqlite3_exec16() function, then the easiest way to get it
is to make a copy of the legacy.c source file and modify it accordingly.

Please also note that sqlite3_prepare16() works by converting its
UTF-16 argument to UTF-8 and then invoking sqlite3_prepare().  SQLite
can store and retrieve UTF-16 data without conversion using
sqlite3_bind_text16() and sqlite3_column_text16().  But the SQL
parser only understands UTF-8.  

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


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



[sqlite] Re: Performance problem

2007-03-01 Thread Igor Tandetnik

[EMAIL PROTECTED] wrote:

Stephen Toney <[EMAIL PROTECTED]> wrote:

select count(*) from keyword a, keyword b where a.key=b.key and
a.value='music' and b.value='history';

4,318 records have value='music' and 27,058 have value='history'. The
keys are 12-byte strings. That doesn't seem like an extreme case to
me.



The result should be 116,836,444.
A faster approach would be:

   SELECT (SELECT count(*) FROM keyword WHERE value='music')*
  (SELECT count(*) FROM keyword WHERE value='history');


You seem to be overlooking a.key=b.key condition.

Igor Tandetnik 



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



Re: [sqlite] Performance problem

2007-03-01 Thread drh
Stephen Toney <[EMAIL PROTECTED]> wrote:
> Dear experts:
> 
> I'm having a performance problem I can't understand. I am running a
> "select count(*)" query joining a table on itself, and the query runs
> for five minutes using Sqlite3.exe before I get bored and kill it. This
> is on a dual-core box with 4GB of memory, running Windows XP Pro. The
> Sqlite version is 3.3.7.
> 
> Here's the problem query with the plan:
> 
> select count(*) from keyword a, keyword b where a.key=b.key and
> a.value='music' and b.value='history';
> 
> 0|0|TABLE keyword AS a WITH INDEX value
> 1|1|TABLE keyword AS b WITH INDEX value
> 
> Here's the schema
> 
> CREATE TABLE keyword (key, contyp int, imagecount int, searchcat int,
> value, nextword, sec, ipr, fldseq int);
> CREATE INDEX key on keyword(key);
> CREATE INDEX nextword on keyword(nextword);
> CREATE INDEX value on keyword(value);
> 
> The table has 3,486,410 records and the SQLite database totals 320MB.
> There are a few small tables in the db besides the KEYWORD table.
> 
> 4,318 records have value='music' and 27,058 have value='history'. The
> keys are 12-byte strings. That doesn't seem like an extreme case to me. 
> 

The result should be 116,836,444.  SQLite has to go retrieve
over 116 million rows from the database in order to compute your
answer.  This does seem like it should take a while.

A faster approach would be:

   SELECT (SELECT count(*) FROM keyword WHERE value='music')*
  (SELECT count(*) FROM keyword WHERE value='history');

Or maybe better yet:  Have you looked into using FTS2 for whatever
it is you are trying to do?  Full-text search is hard to get right
and you appear to be trying to create your own.  Why not use a FTS
subsystem that is already written and testing and available to you?

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


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



[sqlite] Re: C

2007-03-01 Thread Igor Tandetnik

Lloyd  wrote:

How can I make an array of bit fields? something like, using the 16
bits
of a short as an array of bits


You can't. But, if you can use C++ rather than C, there's std::bitset 
class that does just that.


Igor Tandetnik 



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



[sqlite] Re: Performance problem

2007-03-01 Thread Igor Tandetnik

Stephen Toney 
wrote:

select count(*) from keyword a, keyword b where a.key=b.key and
a.value='music' and b.value='history';

0|0|TABLE keyword AS a WITH INDEX value
1|1|TABLE keyword AS b WITH INDEX value

4,318 records have value='music' and 27,058 have value='history'.


Try running ANALYZE statement. The optimizer might be able to choose 
better plan after that.


If this doesn't help, try this query:

select count(*) from keyword a, keyword b where a.key=b.key and
a.value='music' and b.value||''='history';

Using an expression in place of b.value prevents the optimizer from 
using an index on it, at which point it hopefully would use one on 
b.key. This would result in O(M log N) performance, where M=4318 (the 
number of records with value='music') and N is the total number of 
records. The query plan used now results in O(M*M') where M=4318 and 
M'=27058 - a much worse complexity.


Igor Tandetnik 



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



[sqlite] Why no sqlite3_exec16?

2007-03-01 Thread Daniel Önnerby

Why isn't there a sqlite3_exec16 function?

I've seem this question before, but haven't found any answer.

Best regards
Daniel

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



[sqlite] Sqlite for Embedded Devices

2007-03-01 Thread Pavan

Hi,

Could anyone share their observations/comments on having used sqlite as DB
for embedded linux environment.

Thanks,
Pavan.

--
'
Always finish stronger than you start
*


[sqlite] Performance problem

2007-03-01 Thread Stephen Toney
Dear experts:

I'm having a performance problem I can't understand. I am running a
"select count(*)" query joining a table on itself, and the query runs
for five minutes using Sqlite3.exe before I get bored and kill it. This
is on a dual-core box with 4GB of memory, running Windows XP Pro. The
Sqlite version is 3.3.7.

Here's the problem query with the plan:

select count(*) from keyword a, keyword b where a.key=b.key and
a.value='music' and b.value='history';

0|0|TABLE keyword AS a WITH INDEX value
1|1|TABLE keyword AS b WITH INDEX value

Here's the schema

CREATE TABLE keyword (key, contyp int, imagecount int, searchcat int,
value, nextword, sec, ipr, fldseq int);
CREATE INDEX key on keyword(key);
CREATE INDEX nextword on keyword(nextword);
CREATE INDEX value on keyword(value);

The table has 3,486,410 records and the SQLite database totals 320MB.
There are a few small tables in the db besides the KEYWORD table.

4,318 records have value='music' and 27,058 have value='history'. The
keys are 12-byte strings. That doesn't seem like an extreme case to me. 

Using DBI::ODBC::SQLite in a web application the result is just as bad
-- the server times out.

Any suggestions would be much appreciated!


Stephen Toney
Systems Planning
[EMAIL PROTECTED]
http://www.systemsplanning.com


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



[sqlite] [EMAIL PROTECTED]

2007-03-01 Thread Michael Hooker

So from Oakland, CA to Charlotte, NC in only 62 days.  And one

wonders why the nobody sends letters anymore<<

USPS sucks big time.  I'm in the UK, my best friend, who is blind, is in 
Sacramento, CA.  The stuff I send her only arrives at all about 10% of the 
time.Our Royal Mail actually refuses to insure anything sent to the USA, 
but they'll happily insure for many so-called third world countries.   I 
once sent her some air tickets, registered post (ie a tracked service).  I 
tracked them online from London to Chicago O'Hare, where they arrived the 
day after posting.  They took over six months to make the rest of the trip, 
by which time she'd been here and back on a fresh set of tickets.  Why did I 
send her the tickets?  because Delta Air Lines refused to issue them to her 
in the States if I paid for them here, that's why - absolute nonsense this 
international day and age.  Her utilities companies don't seem to mind if I 
pay her bills now and then...


I reckon those who run USPS get kickbacks from UPS, DHL etc.  The worse USPS 
is the more people will use the other options...


No need to reply, you just set me off on something that irritates me a lot!

Michael Hooker

- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Thursday, March 01, 2007 2:28 AM
Subject: Re: [sqlite] developers mailing list, ignored patches


Adam Megacz <[EMAIL PROTECTED]> wrote:


I also printed out and signed the copyright papers and mailed them in.



Your copyright release and your patches arrived in today's post.
The postmark is smeared somewhat but it does appear to say
"2? DEC 2006" (where the ? is illegible.)

So from Oakland, CA to Charlotte, NC in only 62 days.  And one
wonders why the nobody sends letters anymore

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


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


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



Re: [sqlite] C

2007-03-01 Thread Xavier RAYNAUD

http://en.wikipedia.org/wiki/Bit_field

Lloyd a écrit :

Even though I know this is not the right question to ask this list, I
would expect some help from you.

The question is regarding C bit fields..

struct
{
 unsigned int a:1;
};

This declares a to hold 1 bit value;

How can I make an array of bit fields? something like, using the 16 bits
of a short as an array of bits

Thanks,
  Lloyd

Sorry for asking irrelevant question to this list. 



__
Scanned and protected by Email scanner

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

  



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



[sqlite] C

2007-03-01 Thread Lloyd
Even though I know this is not the right question to ask this list, I
would expect some help from you.

The question is regarding C bit fields..

struct
{
 unsigned int a:1;
};

This declares a to hold 1 bit value;

How can I make an array of bit fields? something like, using the 16 bits
of a short as an array of bits

Thanks,
  Lloyd

Sorry for asking irrelevant question to this list. 


__
Scanned and protected by Email scanner

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