[sqlite] Creating database in RAM
Hi all, I just want to know wheather it is possible to create Sqlite3 database in RAM,if it is possible how can i do it in my C-program. Thanks in Advance. Regards, Kirrthana The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments contained in it. Contact your Administrator for further information. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easy question concerning C++ sqlite3
hello thanks 4 reply ok, i think i understand, i would like that you, if not prob., write the whole solution once with index on words Igor Tandetnik wrote: vl.pavlov [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] there is expression: select count(number) from words where word = bla which, if i am right, searches through whole table Not if you have an index on word. And you need a unique index anyway for insert or replace part to work (replace only kicks in if insert would have violated a uniqueness constraint). Oh, and the subselect should use select number ..., not select count(number) Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://www.nabble.com/Easy-question-concerning-C%2B%2B---sqlite3-tp15561319p15606736.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating database in RAM
Hi Kirrthana, you can open an in-memory database by using :memory: as the filename. Read: http://www.sqlite.org/capi3ref.html#sqlite3_open Regards, Eugene Wee Kirrthana.M wrote: Hi all, I just want to know wheather it is possible to create Sqlite3 database in RAM,if it is possible how can i do it in my C-program. Thanks in Advance. Regards, Kirrthana ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The best way to handle dynamic table creation
I need to create tables on the fly which will persist across sessions. These tables may or may not already exist. Calling sqlite3_exec( create table ... ) for a table which already exists returns SQLITE_ERROR, which I assume can be returned for a range of different errors. You can check the szError string to determine the precise error, but this will fall down if the error text is ever changed, so I'm reluctant to do this. Further it doesn't seem right to try and create a table that already exists. So my solution is do do a query: select name, from sqlite_master where type='table' and name=table_name; and check the result before attempting to create the table. My question is what do other folks do here? Is my solution a good one? -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index for Primary Key column missing?
I have created a table with a column: tag text primary key When I do: sqlite .indices tags sqlite_autoindex_tags_1 I only see the one index which I assume is for the ROWID clm. I have inserted a row into the table, in case that is relevant. My understanding is that a primary key column will have an index. So my question is where is the index for the 'tag' column? This is the complete CREATE TABLE SQL. CREATE TABLE tags ( tag text primary key, description text, date_created text DEFAULT CURRENT_TIMESTAMP, style text ) Thanks. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking that a row exists?
* Gilles Ganault: Is this the standard way to check that a row exists in PHP/SQLite, or is there a better syntax? $query = SELECT count(*) FROM contacts WHERE contacts_phones_tel GLOB '%s*'; This query stops after the first match: SELECT 1 FROM contacts WHERE contacts_phones_tel GLOB ? LIMIT 1 $query = sprintf($query,$row['calls_phones_tel']); Danger: SQL injections lurks here. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The best way to handle dynamic table creation
See IF NOT EXISTS in http://www.sqlite.org/lang_createtable.html Rgds, Simon On 21/02/2008, Neville Franks [EMAIL PROTECTED] wrote: I need to create tables on the fly which will persist across sessions. These tables may or may not already exist. Calling sqlite3_exec( create table ... ) for a table which already exists returns SQLITE_ERROR, which I assume can be returned for a range of different errors. You can check the szError string to determine the precise error, but this will fall down if the error text is ever changed, so I'm reluctant to do this. Further it doesn't seem right to try and create a table that already exists. So my solution is do do a query: select name, from sqlite_master where type='table' and name=table_name; and check the result before attempting to create the table. My question is what do other folks do here? Is my solution a good one? -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unrecognized Z UTC time zone signifier
SQLite does not recognize Z as the zero offset time zone specifier. Z (for
'Zulu time', an alternative name for UTC) is part of the ISO 8601 standard for
date and time representations. See http://en.wikipedia.org/wiki/ISO_8601 for
details.
In this regard, SQLite does not currently follow the standard and rejects the
following valid ISO 8601 dates as NULL:
select datetime ('1981-04-06T14:45:15Z');
select datetime ('14:45:15Z');
As far as I can tell, Z support only requires a very minor change in date.c,
parseTimezone() to recognize the if the Z character is present. No further
timezone modification is necessray.
A test case scenario would be
datetest 5.8 {datetime('1994-04-16 14:00:00Z')} {1994-04-16 14:00:00}
# According to Wikipedia, timezone should directly follow time.
# SQLite, however, allows whitespace inbetween.
# Question: Does this conform to ISO 8601?
datetest 5.9 {datetime('1994-04-16 14:00:00 Z')} {1994-04-16 14:00:00}
# Whitespace after Z.
datetest 5.10 {datetime('1994-04-16 14:00:00Z ')} {1994-04-16 14:00:00}
# Whitespace before and after Z.
datetest 5.11 {datetime('1994-04-16 14:00:00 Z ')} {1994-04-16 14:00:00}
If Z timezone support was implemented, I would volunteer to update the date
time function documentation in the SQLite Wiki.
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The best way to handle dynamic table creation
Simon, Thanks for that. Just shows how new I am at SQL. Thursday, February 21, 2008, 9:01:22 PM, you wrote: SD See IF NOT EXISTS in http://www.sqlite.org/lang_createtable.html SD Rgds, SD Simon SD On 21/02/2008, Neville Franks [EMAIL PROTECTED] wrote: I need to create tables on the fly which will persist across sessions. These tables may or may not already exist. Calling sqlite3_exec( create table ... ) for a table which already exists returns SQLITE_ERROR, which I assume can be returned for a range of different errors. You can check the szError string to determine the precise error, but this will fall down if the error text is ever changed, so I'm reluctant to do this. Further it doesn't seem right to try and create a table that already exists. So my solution is do do a query: select name, from sqlite_master where type='table' and name=table_name; and check the result before attempting to create the table. My question is what do other folks do here? Is my solution a good one? -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index for Primary Key column missing?
Am 21.02.2008 um 10:30 schrieb Neville Franks: I have created a table with a column: tag text primary key When I do: sqlite .indices tags sqlite_autoindex_tags_1 I only see the one index which I assume is for the ROWID clm. No, this is the index for your 'tag' column as can be seen by sqlite EXPLAIN QUERY PLAN SELECT * FROM tags WHERE tag='foo'; 0|0|TABLE tags WITH INDEX sqlite_autoindex_tags_1 (see also http://sqlite.org/lang_createtable.html, especially the section Specifying a PRIMARY KEY... for more details) I have inserted a row into the table, in case that is relevant. My understanding is that a primary key column will have an index. So my question is where is the index for the 'tag' column? This is the complete CREATE TABLE SQL. CREATE TABLE tags ( tag text primary key, description text, date_created text DEFAULT CURRENT_TIMESTAMP, style text ) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index for Primary Key column missing?
Jens, Thanks for that. I incorrectly assumed because the index was named xxx_tags it wasn't for the 'tag' column, but related to the table, which is named 'tags'. I had read the content you referred to. Thursday, February 21, 2008, 11:11:19 PM, you wrote: JM Am 21.02.2008 um 10:30 schrieb Neville Franks: I have created a table with a column: tag text primary key When I do: sqlite .indices tags sqlite_autoindex_tags_1 I only see the one index which I assume is for the ROWID clm. JM No, this is the index for your 'tag' column as can be seen by sqlite EXPLAIN QUERY PLAN SELECT * FROM tags WHERE tag='foo'; JM 0|0|TABLE tags WITH INDEX sqlite_autoindex_tags_1 JM (see also http://sqlite.org/lang_createtable.html, especially the JM section Specifying a PRIMARY KEY... for more details) I have inserted a row into the table, in case that is relevant. My understanding is that a primary key column will have an index. So my question is where is the index for the 'tag' column? This is the complete CREATE TABLE SQL. CREATE TABLE tags ( tag text primary key, description text, date_created text DEFAULT CURRENT_TIMESTAMP, style text ) -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unrecognized Z UTC time zone signifier
Ralf Junker [EMAIL PROTECTED] wrote: SQLite does not recognize Z as the zero offset time zone specifier. SQLite does not currently accept any timezone specifiers, other than a hard-coded timezone offset: 1981-04-06T14:45:15+01:00 If we start accepting any symbolic timezone names, seems like we would then need to start accepting them all. If am reluctant to open the floodgates -- D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Execute PRAGMA max_page_count command
Hi all, I use the sqlite version 3.5.6 and tired to execute a PRAGMA max_page_count command to limit the sqlite database size and received always return value equals to zero. My code is shown as follows: // sqlite database initialization retVal = sqlite3_exec(pDB, sql, callback, ctxt, errMsg); //limit the sqlite data base size to 32768 sqlSt = sqlite3_exec(pDB, PRAGMA max_page_count=32768, NULL, 0, errMsg); The sqlite initialization succeeded, but the sqlSt value always returned as zero. I also tried to change the PRAGMA syntax to sqlSt = sqlite3_exec(zDB-sqlHandle, PRAGMA max_page_count=32768, 0, 0, 0);, but sqlSt still returned zero instead of the max_page_count which set. Does the syntax of the PRAGMA max_page_count is correct? Do I need to first initialize the sqlite database and then to execute the PRAGMA max_page_count or vice versa? Thanks in advance for your help, Raviv. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Execute PRAGMA max_page_count command
Raviv Shasha [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] sqlSt = sqlite3_exec(pDB, PRAGMA max_page_count=32768, NULL, 0, errMsg); The sqlite initialization succeeded, but the sqlSt value always returned as zero. Zero is SQLITE_OK, meaning success. What seems to be the problem? -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Execute PRAGMA max_page_count command
Thanks for your question, The problem is although I got the zero value (-SQLITE_OK), the sqlite database wasn't limited to 32768. Should I use the syntax : sqlSt = sqlite3_exec(pDB, PRAGMA max_page_count=32768, NULL, 0,errMsg); Thnaks, Raviv. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Thursday, February 21, 2008 4:06 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Execute PRAGMA max_page_count command Raviv Shasha [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] sqlSt = sqlite3_exec(pDB, PRAGMA max_page_count=32768, NULL, 0, errMsg); The sqlite initialization succeeded, but the sqlSt value always returned as zero. Zero is SQLITE_OK, meaning success. What seems to be the problem? -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Execute PRAGMA max_page_count command
Raviv Shasha [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] The problem is although I got the zero value (-SQLITE_OK), the sqlite database wasn't limited to 32768. How do you determine this? Be aware that max_page_count is not persistent, it's a per-connection option. Once set, queries executed over the same connection will make sure the database doesn't grow over the limit. But other connections, unless they also set the pragma, are not so restricted. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Execute PRAGMA max_page_count command
Raviv Shasha [EMAIL PROTECTED] wrote: Although I limit the sqlite database to 32768 (32K), the db file which retrieved is equal to 470K. What regrading the operations sequence ? Is it correct to first initialize the sqlite database and then to execute the PRAGMA max_page_count command or not? What can cause this problem? The max_page_count pragma limits the number of database pages not bytes. 32768 pages is 32MiB, assuming a 1KiB page (the default.) -- D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple table SELECT help
Assume the following table structure: http://www.nabble.com/file/p15613178/sbtables.jpg I can get overall player stats for ALL games using this select statement: SELECT p.name, sum(s.stat1), sum(s.stat2) FROM stats s JOIN Players p ON p.ID = s.playerID GROUP BY p.ID And I can get a summary of stats for the last FIVE games with this call: SELECT g.date, g.opponent, g.result, sum(s.stat1), sum(s.stat2) FROM stats s JOIN games g ON g.ID = s.gameID GROUP BY g.ID ORDER BY g.date DESC LIMIT 0,5 But how would I get the overall PLAYER stats for the last FIVE games? I have tried sub-queries and additional joins but this type of SELECT is simply above my knowledge of SQL. I appreciate any help. Thanks. -- View this message in context: http://www.nabble.com/Multiple-table-SELECT-help-tp15613178p15613178.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Optimize query
Manoj Marathayil wrote: Is there a better way to write this query? any help is appreciated. Query: SELECT SUM(metric_diff) FROM snapshot_master WHERE snapshot_master.metric_id = ? AND snapshot_master.timestamp_id in (select timestamp_id from timestamp_master where server_timestamp = ? AND server_timestamp = ? and strftime(?, server_timestamp, 'unixepoch') = strftime(?, ?, 'unixepoch')); Format string may vary depending on context. Table schema: CREATE TABLE [snapshot_master] ( [timestamp_id] INTEGER NOT NULL, [metric_id] INTEGER NOT NULL, [metric_now] TEXT, [metric_diff] TEXT, PRIMARY KEY (metric_id, timestamp_id)) CREATE TABLE [timestamp_master] ( [timestamp_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, [server_timestamp] INTEGER, [server_start_time] INTEGER, [server_uptime] INTEGER, [server_uptime_diff] INTEGER, [server_is_connected] INTEGER) It would help to add an index on server_timestamp in the timestamp_master table. create index server_timestamp_idx on timestamp_master(server_timestamp); HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Execute PRAGMA max_page_count command
Although I limit the sqlite database to 32768 (32K), the db file which retrieved is equal to 470K. What regrading the operations sequence ? Is it correct to first initialize the sqlite database and then to execute the PRAGMA max_page_count command or not? What can cause this problem? -Raviv. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Thursday, February 21, 2008 4:22 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Execute PRAGMA max_page_count command Raviv Shasha [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] The problem is although I got the zero value (-SQLITE_OK), the sqlite database wasn't limited to 32768. How do you determine this? Be aware that max_page_count is not persistent, it's a per-connection option. Once set, queries executed over the same connection will make sure the database doesn't grow over the limit. But other connections, unless they also set the pragma, are not so restricted. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple table SELECT help
Try this.. SELECT p.name, sum(s.stat1), sum(s.stat2) FROM stats s JOIN Players p ON p.ID = s.playerID WHERE s.gameID IN (SELECT gameID FROM games ORDER BY gameID DESC LIMIT 5) GROUP BY p.playerID HTH, Sam On Thu, Feb 21, 2008 at 10:25 AM, RBKanso [EMAIL PROTECTED] wrote: Assume the following table structure: http://www.nabble.com/file/p15613178/sbtables.jpg I can get overall player stats for ALL games using this select statement: SELECT p.name, sum(s.stat1), sum(s.stat2) FROM stats s JOIN Players p ON p.ID = s.playerID GROUP BY p.ID And I can get a summary of stats for the last FIVE games with this call: SELECT g.date, g.opponent, g.result, sum(s.stat1), sum(s.stat2) FROM stats s JOIN games g ON g.ID = s.gameID GROUP BY g.ID ORDER BY g.date DESC LIMIT 0,5 But how would I get the overall PLAYER stats for the last FIVE games? I have tried sub-queries and additional joins but this type of SELECT is simply above my knowledge of SQL. I appreciate any help. Thanks. -- View this message in context: http://www.nabble.com/Multiple-table-SELECT-help-tp15613178p15613178.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple table SELECT help
On 2/21/08, RBKanso [EMAIL PROTECTED] wrote: Assume the following table structure: http://www.nabble.com/file/p15613178/sbtables.jpg I can get overall player stats for ALL games using this select statement: SELECT p.name, sum(s.stat1), sum(s.stat2) FROM stats s JOIN Players p ON p.ID = s.playerID GROUP BY p.ID And I can get a summary of stats for the last FIVE games with this call: SELECT g.date, g.opponent, g.result, sum(s.stat1), sum(s.stat2) FROM stats s JOIN games g ON g.ID = s.gameID GROUP BY g.ID ORDER BY g.date DESC LIMIT 0,5 But how would I get the overall PLAYER stats for the last FIVE games? try SELECT player_id, player_name, Sum(stat1), Sum(stat2) FROM SELECT p.ID AS player_id, p.name AS player_name, s.stat1 AS stat1, s.stat2 AS stat2, g.date AS game_date FROM stats s JOIN players p on p.ID = s.playerID JOIN games g ON g.ID = s.gameID ORDER BY g.date DESC LIMIT 0,5 GROUP BY player_id, player_name ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Data Recovery - Unvacumed DB - OS X Address Book
Any help would be appreciated. Even as much as, No, there really is no way to do this. Josh On Feb 19, 2008, at 7:46 PM, Joshua Galvez wrote: Twice recently, on different computers I've had clients lose their OS X Address Book. Alas, they've not had backups. I've been able to track down much of their data still remains in an sqlite3 data.syncdb file. But when I access the DB, I find that tables with the actual contacts have been rewritten with only a few records. But the file size is still ~52mb. I realize that sqlite being the flat file it is doesn't recover space until you 'vacum' it. So much of the old information is left floating around in the file. How can I recover data from this database? Any help or guidance would be much appreciated. Josh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please test lastest CVS using WinCE
Can somebody with the ability to compile and test for wince please test check-in [4802] for me. Is there a recommended way to run the tests on CE? I created a VisualStudio project for sqlite3 but ran into some problems: 1) I couldn't see a way to generate sqlite3.h. I ended up using cygwin with configure then make to get me sqlite3.h, parse.h, opcodes.h and keywordhash.h and included those. Should I just inspect the configure script to see how these are generated and try and incorporate that into a build step in VisualStudio? 2) All the test files include tcl.h which CE (at least by default) doesn't have, so I'm not sure how to run the unit tests. I haven't yet gone through them to see if they use TCL or not though, to see if I can just ifdef out the include. 3) I'm not familiar with the extensions (FTx and ICU) so I'm not sure whether they need to be compiled in for testing, or if all possible combinations need to be built and then tested. 4) What flavours of CE would you like the tests to be run on? Consumer devices these days typically run either Pocket PC 2003 or Windows Mobile 5 which are basically fixed configurations of Windows CE 4.2 and 5 respectively, plus some apps (Pocket Word etc). Plain-old Windows CE devices can have pretty much any subset of CE's features on them, as it's up to the OEM to decide which components to include when they configure the platform for their device. Cheers, Dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easy question concerning C++ sqlite3
vl.pavlov wrote: hello thanks 4 reply ok, i think i understand, i would like that you, if not prob., write the whole solution once with index on words Here it is again. create table words (word text primary key, number integer); insert or replace into words values(:word, coalesce((select number from words where word = :word) + 1, 1)); I have changed the field name above from the original count to number to avoid confusion with the count() function in SQL. The primary key constraint on the word column creates a unique index on the words with the word field in ascending order. That index is used to quickly locate a word in the table, or determine that the word is missing from the table. The :word is a parameter to the insert statement that you bind to the word you want to insert. If you are generating the SQL as text then simply replace that identifier with a literal string containing the word to be inserted. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking that a row exists?
Florian Weimer wrote: This query stops after the first match: SELECT 1 FROM contacts WHERE contacts_phones_tel GLOB ? LIMIT 1 Yes, but it returns a 1 if it exists and a null result if doesn't. It is usually better to get a 1 or 0, i.e. true or false result for an existence check. This query returns a boolean result and stops as soon as it has determined the result select exists (select * from contacts where contacts_phone_tel glob ?); HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking that a row exists?
This query returns a boolean result and stops as soon as it has determined the result select exists (select * from contacts where contacts_phone_tel glob ?); HTH Dennis Cote I have been using: SELECT NULL FROM sqlite_master WHERE type='table' AND lower(name)=? to determine whether a table exists and looking at the number of rows returned (I'm using sqlite3_get_table through an API). I get one row back if the table exists and no rows when it doesn't. There wouldn't be multiple rows to LIMIT in this case though. Is that bad? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
* Toby Roworth [EMAIL PROTECTED] [2008-02-20 14:35]: I'm not sure if this was the right place to post this, but it would be interesting to hear people's thoughts on the matter. I think the claim is unassailable. I have five different copies of the SQLite code on this computer alone, I think. Every Mac has several of them. One of the servers I deploy to has at least 10 copies of it. Every copy of Firefox 3 contains a copy of SQLite. Already the number of installations is astronomic; even so it is accelerating rapidly. The other libre databases cannot remotely keep up, much less the commercial ones. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Data Recovery - Unvacumed DB - OS X Address Book
Joshua Galvez [EMAIL PROTECTED] wrote: Any help would be appreciated. Even as much as, No, there really is no way to do this. No, there really is no way to do this, at least not without some internal knowledge of what kinds of information Apple is storing in the data.syncdb file. Maybe you could reverse engineer it. -- D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unrecognized Z UTC time zone signifier
[EMAIL PROTECTED] wrote:
Ralf Junker [EMAIL PROTECTED] wrote:
SQLite does not recognize Z as the zero offset time zone specifier.
SQLite does not currently accept any timezone specifiers, other
than a hard-coded timezone offset:
1981-04-06T14:45:15+01:00
If we start accepting any symbolic timezone names, seems like we
would then need to start accepting them all. If am reluctant to
open the floodgates
Yes, I know about your strict policy of adding new features to SQLite, and
please know that I do appreciate it. But this does not mean we have go give up
easily on new features, but provide better arguments instead. Let my try:
1. Z is part of the ISO standard and therefore used with external date and
time data. Supporting it makes it much easier to import such data into SQLite.
2. Z is not a soft-coded timezone specifier like CET or similar. It is just
a special case of the hard-coded 1981-04-06T14:45:15+00:00.
3. Using Z explictly distinguises UTC from local time zones and avoids
disambiguities.
4. Asking for Z I do not want to open any floodgates. If this was my
intention, I would have asked for
* ±[hh][mm] and ±[hh] -- currently missing but nice to have, IMHO
* named timezones ('MET' or 'Europe/Moscow') -- just a joke
* daylight saving time support -- kidding only
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking that a row exists?
Fin Springs wrote: I have been using: SELECT NULL FROM sqlite_master WHERE type='table' AND lower(name)=? to determine whether a table exists and looking at the number of rows returned (I'm using sqlite3_get_table through an API). I get one row back if the table exists and no rows when it doesn't. There wouldn't be multiple rows to LIMIT in this case though. Is that bad? It isn't good or bad, just different. Your application has to perform the extra logic of counting the returned rows to determine existence. If you use a subselect and exists, SQLite will effectively do that for your application and return a boolean value directly. select exists (SELECT * FROM sqlite_master WHERE type='table' AND lower(name)=?) This will always return one row with one column with a value that is either 0 or 1. HTH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
I have five different copies of the SQLite code on this computer alone, I think. Every Mac has several of them. One of the servers I deploy to has at least 10 copies of it. Every copy of Firefox 3 contains a copy of SQLite. And Firefox 2 ;) Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
Aristotle Pagaltzis wrote: I think the claim is unassailable. I think you and most others have missed Tony's point. All he is saying is the claim that the all other database installations can be accurately estimated by using a count of the number of websites on the internet may be suspect. I have to agree with him. There are many other databases used in volumes that approach or exceed the 100 million websites. For example, Nokia sold 350 million cell phones last year alone. Each of those phones probably had some embedded database on them. It may even be SQLite for all I know, but if it is not, then it substantially increases the number of non-SQLite database deployments. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
Dennis Cote [EMAIL PROTECTED] wrote: Nokia sold 350 million cell phones last year alone. Each of those phones probably had some embedded database on them. It may even be SQLite for all I know, but if it is not, then it substantially increases the number of non-SQLite database deployments. I am not privy to the inner workings of Nokia cellphones, but I believe any non-SQLite databases they are using do not understand SQL. The other products might still be a database, but they are not an *SQL* database. -- D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple table SELECT help
Samuel Neff wrote: Try this.. SELECT p.name, sum(s.stat1), sum(s.stat2) FROM stats s JOIN Players p ON p.ID = s.playerID WHERE s.gameID IN (SELECT gameID FROM games ORDER BY gameID DESC LIMIT 5) GROUP BY p.playerID HTH, Sam This worked! Thanks for your help Sam. P Kishor-3, Thanks for your response as well but your query didn't get the expected result. -- View this message in context: http://www.nabble.com/Multiple-table-SELECT-help-tp15613178p15618678.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
[EMAIL PROTECTED] wrote: I am not privy to the inner workings of Nokia cellphones, but I believe any non-SQLite databases they are using do not understand SQL. The other products might still be a database, but they are not an *SQL* database. Richard, Perhaps the title of the page should be changed to Most Widely Deployed SQL Database to reflect this distinction, and you might want to rephrase the and possibly all other database engines combined in the first sentence. I have no doubt that SQLite is very widely deployed, perhaps it is already the most widely deployed database (unqualified), and I think it is almost certain that it will become the most widely deployed database at some some point in time (if it isn't already). That said, I agree with Toby that you must be careful about your assumptions and arguments when making such claims. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
Every copy of Firefox 3 contains a copy of SQLite. And Firefox 2 ;) Really? What is it used for? I'm not sure what uses it internally, but it's exposed via mozIStorageService [1], so add-ons can use it as well! Cheers, Shawn [1] http://developer.mozilla.org/en/docs/mozIStorageService ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
* Shawn Wilsher [EMAIL PROTECTED] [2008-02-21 20:00]: Every copy of Firefox 3 contains a copy of SQLite. And Firefox 2 ;) Really? What is it used for? Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
Aristotle Pagaltzis wrote: * Shawn Wilsher [EMAIL PROTECTED] [2008-02-21 20:00]: Every copy of Firefox 3 contains a copy of SQLite. And Firefox 2 ;) Really? What is it used for? Regards, Apparently it's used for storing profile information in Firefox 2, and Firefox 3 uses it for storing history/bookmarks (it was reading about this that first alerted me to SQLite's existence) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unrecognized Z UTC time zone signifier
* [EMAIL PROTECTED] [EMAIL PROTECTED] [2008-02-21 13:45]: Ralf Junker [EMAIL PROTECTED] wrote: SQLite does not recognize Z as the zero offset time zone specifier. If we start accepting any symbolic timezone names, seems like we would then need to start accepting them all. Not hardly. FWIW, the IETF recommendation for timestamps in any new internet standards is to use the format specified in RFC 3339, which is based on codified experience. For time zones, it prescribes that they be given as either a numeric offset or `Z` a shortcut for `+00`; no provision is made for other symbolic names as those only cause trouble. So you should have no trouble refusing requests to support those. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
* Aristotle Pagaltzis: * Shawn Wilsher [EMAIL PROTECTED] [2008-02-21 20:00]: Every copy of Firefox 3 contains a copy of SQLite. And Firefox 2 ;) Really? What is it used for? It's used to store data used by the client-side URL classifier (phishing filter). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most widely deployed?
On Thu, 21 Feb 2008 14:42:30 -0500, you wrote: Every copy of Firefox 3 contains a copy of SQLite. And Firefox 2 ;) Really? What is it used for? I'm not sure what uses it internally, but it's exposed via mozIStorageService [1], so add-ons can use it as well! And there's an FireFox extension, SQLite Manager, that offers an SQLite GUI tool. http://sqlitemanager.mozdev.org/ -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Read/write loop
I get an error database is locked or database read only when attempting to update a table in the database. I have been reading various articles about this but cannot get a definitive answer from the conflicting discussions. Is it possible (on Windows 2000 using SQLite 3_5_6) to be reading from a file and for each row returned perform an update or write ? If it is possible is there some info available describing what the sequence of SQLite API functions to use ? Colin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read/write loop
Colin, You get the sqlite busy when you are trying to write and another connection/process has the database locked. You can help yourself immensely by using transactions. This improves performance and you can begin transactions immediate. So you only need to test for sqlite busy at the beginning of a transaction. Ken Colin Darcey [EMAIL PROTECTED] wrote: I get an error database is locked or database read only when attempting to update a table in the database. I have been reading various articles about this but cannot get a definitive answer from the conflicting discussions. Is it possible (on Windows 2000 using SQLite 3_5_6) to be reading from a file and for each row returned perform an update or write ? If it is possible is there some info available describing what the sequence of SQLite API functions to use ? Colin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] trying to locate older version of SQLite
Hi, I have a website on a shared hosting which has PHP 5 installed on it. I ran a test program and created a SQLite database from it. When I opened the database file, it has as its first line This file contains an SQLite 2.1 database. I am trying to download the 2.1 version of SQLite from the internet(google) but unable to get any windows binary for it. The SQLite.org website does not carry binaries of older versions. I also tried to search in the sqlite-users mailing list, but the site does not have a search option, and the messages are stored month wise, so I do not know whether this has been answered before on this mailing list. Where can I get it? Any help would be greatly appreciated. Shibu Narayanan Consultant, PrimeSourcing Division, Investment Banking Group Tel.Office: 91-80-2208-6270 or 91-80-6659-6270 e-mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] The answer is 42. DISCLAIMER: This message contains privileged and confidential information and is intended only for an individual named. If you are not the intended recipient, you should not disseminate, distribute, store, print, copy or deliver this message. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required, please request a hard-copy version. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Read/write loops
Ken, I am not sure how to use this facility, looks like one responds by sending another email. When you write use transactions, do you mean BEGIN TRANSACTION / COMMIT TRANSACTION. I am using these SQL commands to achieve transaction control. The first write is accepted, but the second one received the error each time I test. Colin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
