Re: [sqlite] SQLITE3 bombs on Windows 95
-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
-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
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
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
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?
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?
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?
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?
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?
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?
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
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?
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?
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?
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
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
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] use of sqlite in a multithread C application
Rafi Cohen 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. Rafi, For your access you would be best off to have the main thread open the connection and pass a reference to it to the other thread (or you could use a global connection variable). Then use a mutex to have the threads block until they can get exclusive access to the connection. Once they have the mutex they can do their read or write with no concern for interruption from the other thread (it will block on the mutex if it tries to start a database access). HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite c++ interfaces
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
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
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
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
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
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
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] how to insert and select a date column
Hi Denis, thank you very much for the briliant explanation and the
refference you pointed.
This clearly explains my options here.
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?
Thanks, Rafi.
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 28, 2007 5:28 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] how to insert and select a date column
Rafi Cohen wrote:
>
> Hi, I apologize in advance for the probably very basic question as I'm
> a newbie to sqlite and integrating sql in C applications. One of my
> tables in my database requires a date column. I understand from a
> tutorial not related to C/C++ api that insering date columns is not
> trivial and triggers need to be used. As I am not knowledgeable about
> triggers, I would like to ask how can I insert a date column in a C
> application and later retrieve this column or compare it with current
> date in a select statement? I need to insert just any date in this
> column and not only the current date.
> If somebody can send me an example off list to implement this, I'll
much
> appreciate it.
> Thanks, Rafi.
>
>
Rafi,
SQLite does not support SQL standard date and time types. It does
however have a very flexible set of date and time functions that use
strings (i.e. -MM-DD and/or HH:MM:SS etc), integer numbers (i.e.
unix epoch times, seconds since 1970-01-01), or floating point numbers
(i.e julian day numbers) which can all be stored in the database. These
function also allow these formats to be easily converted from one type
to another, in particular, from integer to string or floating point to
string for display. These numeric formats make date comparisons very
simple. For more information see
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
Once you have decided how you want to store your dates you can insert
them easily using a default value for your column or supplying a date in
the required format. Lets assume you decide to store strings since they
are human readable, even if somewhat inefficient from a storage point of
view.
create table t (id, data, created default current_date);
insert into t (id, data) values (1, 'test');
insert into t (id, data) values (2, 'more');
select created from t;
2007-02-28
2007-02-28
You can also provide an explicit value that overrides the default value
when you add a record.
insert into t values (3, 'explicit', '2007-02-14');
insert into t values (3, 'explicit', date('now', '+2 days'));
And you can update these dates using the date and time functions
update t set created = date(created, '-1 month') where id = 2;
Finally you can use the date and time functions to convert the dates for
display.
select created, julianday(created) from t;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.4/705 - Release Date:
2/27/2007 3:24 PM
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] sqlite c++ interfaces
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?
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
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?
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
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
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
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
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
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
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
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
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
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?
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
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
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
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
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
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
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
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
[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?
=?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
[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
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] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance problem
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
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
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?
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
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
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]
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
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
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]
-
