Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?
You may also want to look at libjlog https://labs.omniti.com/labs/jlog enjoy, -jeremy On Thu, Sep 30, 2010 at 10:11:54AM +0200, Lynton Grice wrote: > Hi there, > > > > I am a HUGE SQLite fan and have an interesting question I have been > scratching my head about for a couple days now. > > > > First off my daily job is very much around "messaging" and I am very > familiar with message queue products like Websphere MQ and Fiorano MQ. > > > > When you install Websphere MQ or Fiorano MQ they have a FILE based queue > underneath by default (typically one file per queue with the messages etc > stored at different offsets). > > > > There messaging systems will have "file writer locks" that in essence will > only allow ONE WRITER per queue at any one time. So any clients sending > messages will have "to wait in line" until the message can be physically > written to file. > > > > Bottomline: We have one writer and multiple readers per queuefine. > > > > Then I scratch my head and wonder why SQLite is not the PERFECT persistence > layer for building an awesome "file based queue" on? It is lightening fast > by default, and also has the same "locking issues" described above. We are > talking milliseconds anywaybetween writes > > > > Bottomline: Is there any reason why anyone would think someone like me > coding a solid message queue using SQLite is a bad idea? Am I missing > something? I personally think it is a wonderful idea and would be hugely > useful to my daily job > > > > Any help or advise in this regard would be hugely appreciated ;-) > > > > Thanks > > > > Lynton > > > > _______ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Jeremy Hinegardner jer...@hinegardner.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Database in Shared Memory
Another library option would be JLog https://labs.omniti.com/trac/jlog enjoy, -jeremy On Tue, May 11, 2010 at 02:44:18PM -0500, Manuj Bhatia wrote: > I need to exchange messages across processes, not threads. And one of the > reasons that I am inclined towards SQLite is that I do not want a separate > Queue-manager process. > I'll just write wrapper APIs around SQLite and embed them into each > application, so I have a manager-less implementation. > > I found a Queue implementation using SQLite at XMLBlaster: > http://www.xmlblaster.org/xmlBlaster/doc/requirements/client.c.queue.html > > I'll see how they have implemented it and might adapt it to my needs. > > Michael, > Thanks for the link! I didn't know about this feature of AIX. I'll see if I > can get my Unix Admins to create a Ram Disk for me to play around with. > > Thanks, > Manuj > > > On Tue, May 11, 2010 at 2:29 PM, Alexey Pechnikov > <pechni...@mobigroup.ru>wrote: > > > Hm... You can use the dedicated thread in your application for SQLite > > in-memory database. Why you want to build external application for > > this? And SQL for you task is not needed I think - you can use the > > simple hash table or any other simple structure. If you have same > > additional needs or ideas - speak it! > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Jeremy Hinegardner jer...@hinegardner.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite database for google maps
On Sat, Nov 28, 2009 at 08:42:54AM -0600, P Kishor wrote: > On Sat, Nov 28, 2009 at 12:14 AM, logan <logan...@gmail.com> wrote: > > Hi All, > > > > I'm trying to find if someone has got a database of Google maps for a > > particular town in an SQLite file. I'm looking to write an application > > which will query this database and find text directions. > > I think you need to learn more about Google Maps to realize that your > question doesn't make any sense. There is no such thing as a "database > of Google Maps... in a SQLite file." Even if one could somehow capture > Google Maps and put them in a sqlite file, it would be illegal to do > so, as it would violate Gmaps terms of service. > > On the other hand, you can take geographic data stored in a sqlite db, > and display it on Google Maps. But, that is a completely different > question. > You may want to look at http://www.openstreetmap.org/ for free geographic data for Earth. Also see their wiki http://wiki.openstreetmap.org/wiki/Main_Page enjoy, -jeremy -- ==== Jeremy Hinegardner jer...@hinegardner.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A constraint bug?
On Sat, Oct 31, 2009 at 08:31:01AM -0400, D. Richard Hipp wrote: > > On Oct 30, 2009, at 10:25 PM, Mick wrote: > > > This is more FYI than needing it (as I have already worked around > > it), but I > > have discovered that an IGNORE constraint on an insert, when one of > > the > > fields in the constraint is NULL, will insert a duplicate record > > into the > > database. > > This is not a bug. See, for example, the explanation on > http://www.sqlite.org/cvstrac/tktview?tn=3463 > > > > > i.e. > > > > CREATE TABLE mytable ( > > ID1 INTEGER NOT NULL, > > ID2 INTEGER NOT NULL, > > SomeText VARCHAR(100) COLLATE NOCASE, > > PRIMARY KEY (ID1, ID2, SomeText) ON CONFLICT IGNORE); > > > > INSERT INTO mytable VALUES (1, 1, NULL); > > INSERT INTO mytable VALUES (1, 1, NULL); As an aside, Postgres 8.4 does not allow the insert to happen in the first place. http://www.postgresql.org/docs/8.4/interactive/sql-createtable.html "The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values." [jer...@[local]] 12:59:21> create table mytable( id1 integer not null, id2 integer not null, sometext varchar(100), primary key(id1, id2, sometext)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "mytable_pkey" for table "mytable" CREATE TABLE Time: 492.766 ms [jer...@[local]] 13:00:04> insert into mytable values(1,1,NULL); ERROR: null value in column "sometext" violates not-null constraint Change the PRIMARY KEY to a UNIQUE contraint and sqlite and postgres agree on the behavior. On the same postgresql page: "For the purpose of a unique constraint, null values are not considered equal." [jer...@[local]] 13:03:32> create table mytable( id1 integer not null, id2 integer not null, sometext varchar(100), unique(id1, id2, sometext)); NOTICE: CREATE TABLE / UNIQUE will create implicit index "mytable_id1_key" for table "mytable" CREATE TABLE Time: 121.473 ms [jer...@[local]] 13:03:49> insert into mytable values(1,1,NULL); INSERT 0 1 Time: 18.476 ms [jer...@[local]] 13:03:54> insert into mytable values(1,1,NULL); INSERT 0 1 Time: 0.539 ms [jer...@[local]] 13:03:56> select * from mytable; id1 | id2 | sometext -+-+-- 1 | 1 | NULL 1 | 1 | NULL (2 rows) Time: 14.775 ms enjoy, -jeremy -- Jeremy Hinegardner jer...@hinegardner.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] build db in memory then dump to a persistent file.
Hi George, On Sat, Oct 03, 2009 at 01:50:31PM -0700, George Hartzell wrote: > > Hi all, > > I use an SQLite database w/ the rtree extension to hold information > about genetic polymorphism (snp's), based on UCSC's mapping work and > their mysql table dumps. My database is write-once, read from then > on. > > One of the tables has almost 19 million rows. This is the table on > which I build my 2-D rtree index. > > I read the data from tab delimited mysql dumps into the basic tables, > then run a query that pulls the data I want to rtree-index out of it's > table, cleans up one of the columns, the inserts it into the rtree > index. Finally I add a set of indices to several columns in the big > table. > > I tried using .import to move the data into the tables, but gave up > after it ran well past the time it took to do the inserts. That > didn't seem to help with the index/rtree creation time either. > > I'm turning synchronous off and doing the inserts inside of a > transaction. > > I was wondering if there was some way to populate the database inside > a ':memory:' database and then dump/copy/... the results into a file. > I've seen posts that suggest that I can select from the memory tables > and insert into the persistent ones, but that seems like it'd take > more work to get the indexes. > > I'd be interested in any suggestions for making my loading go faster. Have you tried working with the backup API? If all the data will fit into memory, do all the work in a memory database and then use the backup api to dump it to disk. http://sqlite.org/backup.html enjoy, -jeremy -- Jeremy Hinegardner jer...@hinegardner.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi column ORDER BY across table peformance problem....
Ah, i see, so you are going to be calling this many times, and slowly incrementing the value of startTime. Yes, in this query, if you have more than 8 shows at a particular start time, then when you increment the time to go to the next 'batch' of shows you will miss all the ones from then end of the previous time. If that is the case, how about just making the inner query 'WHERE showing.startTme = ' and leave off the limit, and call the whole thing repeatedly? I doubt that would do what you want, since you querying for startTime which is what your where clause, so you'll either get back rows that are all the value of your where clause or no rows. I guess more information about the whole problem is going to be in necessary to help further. For instance, it appears you have a table of 'showings' and a table of 'channels' and the goal is, find the next N items from the showings table for channels in the channel table. enjoy, -jeremy On Tue, Apr 21, 2009 at 10:11:41PM -0700, sorka wrote: > > Um, well, except that I'd have huge gaping holes. i.e. say there are 100 > shows at or after the specified time all on different channels. The above > query would limit to the first 8 showings on or after that time *before* > sorting by channel number. > > > > Jeremy Hinegardner wrote: > > > > How about trying: > > > > SELECT s.startTime > > FROM ( SELECT showing.startTime AS startTime > > ,showing.stationId AS stationId > > FROM showing > > WHERE showing.startTime >= 123923 > > ORDER BY showing.startTime > >LIMIT 8 ) AS s > > JOIN channel AS c > > ON s.stationId = c.stationId > > ORDER BY s.startTime, c.ChannelMajorNumber > > > > I believe this should limit your table of 100,000 rows down to just the 8 > > you > > want on the inner query, and then join that against the 100 rows in the > > channel > > table. > > > > Although, to tell you the truth, I don't see the purpose in your original > > query: > > > > SELECT showing.startTime > > FROM showing > > JOIN channel > > ON showing.startTime >= 123923 > >AND showing.stationId = channel.stationId > > ORDER BY showing.startTime, channel.ChannelMajorNumber > > LIMIT 8; > > > > You are not doing anything with the channel table data other than joining > > it > > agains the showing tables. To me that means you are joining against the > > channel > > table in order to filter out rows in the showing table that have > > stationId's > > that do not exist in the channel table. > > > > Is that correct? If that is correct, then the query I gave will not do > > what you > > want. > > > > Can the goal you are attempting be stated as? > > > > Find next or earliest showings from the showings table for channels in > > the > > channel table? > > > > If this is the case, maybe this query will work: > > > > SELECT showing.startTime > > FROM showing > >WHERE showing.startTime >= 123923000 > > AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM > > channel ) > >LIMIT 8; > > > > enjoy, > > > > -jeremy > > > > > > On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote: > >> > >> Writing the query as you said you would returns results in nearly > >> instantly, > >> within a few ms. > >> > >> The problem is when you add a secondary ordering field. The intended > >> indices > >> are being used. > >> > >> The problem, as I've explained several times already is that there is no > >> way > >> to create a multicolumn index across tables. Sqlite is using the > >> stationId > >> index on the channel table to join showings via the stationId. The query > >> plan shows it clearly. When ordering by startTime or even startTime and > >> stationId, the results are returned in a few ms. But when adding channel > >> number(i.e startTime and then channel number), it jumps to two minutes. > >> This > >> is because sqlite is bringing in nearly 100K records from the showing > >> table, > >> sorted by time, because of the startTime index, before it starts sorting > >> by > >> channel number. It would sure be nice to be able to get sqlite to sort on > >> the channel number for each time group. i.e. once all the 10:00
Re: [sqlite] Multi column ORDER BY across table peformance problem....
That last query should be: SELECT showing.startTime FROM showing WHERE showing.startTime >= 123923000 AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM channel ) ORDER BY showing.startTime LIMIT 8; I forgot the order by. enjoy, -jeremy On Tue, Apr 21, 2009 at 11:07:15PM -0600, Jeremy Hinegardner wrote: > How about trying: > > SELECT s.startTime > FROM ( SELECT showing.startTime AS startTime > ,showing.stationId AS stationId > FROM showing > WHERE showing.startTime >= 123923 > ORDER BY showing.startTime >LIMIT 8 ) AS s > JOIN channel AS c > ON s.stationId = c.stationId > ORDER BY s.startTime, c.ChannelMajorNumber > > I believe this should limit your table of 100,000 rows down to just the 8 you > want on the inner query, and then join that against the 100 rows in the > channel > table. > > Although, to tell you the truth, I don't see the purpose in your original > query: > > SELECT showing.startTime > FROM showing > JOIN channel > ON showing.startTime >= 123923 >AND showing.stationId = channel.stationId > ORDER BY showing.startTime, channel.ChannelMajorNumber > LIMIT 8; > > You are not doing anything with the channel table data other than joining it > agains the showing tables. To me that means you are joining against the > channel > table in order to filter out rows in the showing table that have stationId's > that do not exist in the channel table. > > Is that correct? If that is correct, then the query I gave will not do what > you > want. > > Can the goal you are attempting be stated as? > > Find next or earliest showings from the showings table for channels in the > channel table? > > If this is the case, maybe this query will work: > > SELECT showing.startTime > FROM showing >WHERE showing.startTime >= 123923000 > AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM > channel ) >LIMIT 8; > > enjoy, > > -jeremy > > > On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote: > > > > Writing the query as you said you would returns results in nearly instantly, > > within a few ms. > > > > The problem is when you add a secondary ordering field. The intended indices > > are being used. > > > > The problem, as I've explained several times already is that there is no way > > to create a multicolumn index across tables. Sqlite is using the stationId > > index on the channel table to join showings via the stationId. The query > > plan shows it clearly. When ordering by startTime or even startTime and > > stationId, the results are returned in a few ms. But when adding channel > > number(i.e startTime and then channel number), it jumps to two minutes. This > > is because sqlite is bringing in nearly 100K records from the showing table, > > sorted by time, because of the startTime index, before it starts sorting by > > channel number. It would sure be nice to be able to get sqlite to sort on > > the channel number for each time group. i.e. once all the 10:00 PM showings > > have been returned and the first 10:30 PM showing is about to be returned, > > sort all of the 10:00 PM showings by channel number. Sqlite is NOT doing > > this but should. > > > > At this point, I'm going to assume that this is just a limitation in the > > sqlite engine and I'll have to proceed in other ways to solve this issue. > > > > > > > > Ian Walters wrote: > > > > > >> SELECT showing.startTime FROM showing JOIN channel ON > > >> showing.startTime >= > > >> 123923 AND showing.stationId = channel.stationId ORDER BY > > >> showing.startTime LIMIT 8; > > > > > > I don't know if it would be faster... but I would have written that. > > > > > > SELECT showing.startTime FROM showing JOIN channel USING (stationId) > > > WHERE startTime < 123923 ORDER BY startTime LIMIT 8. > > > > > > Also I know in the latest version of SQLite its possible to 'hint' > > > what indexes should be used, which might be helpful. There is also > > > something on the contrib page that lets you check a query to see what > > > indexes it does use. > > > > > > Sorry if the above lacks detail, its kinda a busy day. > > > > > > -- > > > Ian > > >
Re: [sqlite] Multi column ORDER BY across table peformance problem....
How about trying: SELECT s.startTime FROM ( SELECT showing.startTime AS startTime ,showing.stationId AS stationId FROM showing WHERE showing.startTime >= 123923 ORDER BY showing.startTime LIMIT 8 ) AS s JOIN channel AS c ON s.stationId = c.stationId ORDER BY s.startTime, c.ChannelMajorNumber I believe this should limit your table of 100,000 rows down to just the 8 you want on the inner query, and then join that against the 100 rows in the channel table. Although, to tell you the truth, I don't see the purpose in your original query: SELECT showing.startTime FROM showing JOIN channel ON showing.startTime >= 123923 AND showing.stationId = channel.stationId ORDER BY showing.startTime, channel.ChannelMajorNumber LIMIT 8; You are not doing anything with the channel table data other than joining it agains the showing tables. To me that means you are joining against the channel table in order to filter out rows in the showing table that have stationId's that do not exist in the channel table. Is that correct? If that is correct, then the query I gave will not do what you want. Can the goal you are attempting be stated as? Find next or earliest showings from the showings table for channels in the channel table? If this is the case, maybe this query will work: SELECT showing.startTime FROM showing WHERE showing.startTime >= 123923000 AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM channel ) LIMIT 8; enjoy, -jeremy On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote: > > Writing the query as you said you would returns results in nearly instantly, > within a few ms. > > The problem is when you add a secondary ordering field. The intended indices > are being used. > > The problem, as I've explained several times already is that there is no way > to create a multicolumn index across tables. Sqlite is using the stationId > index on the channel table to join showings via the stationId. The query > plan shows it clearly. When ordering by startTime or even startTime and > stationId, the results are returned in a few ms. But when adding channel > number(i.e startTime and then channel number), it jumps to two minutes. This > is because sqlite is bringing in nearly 100K records from the showing table, > sorted by time, because of the startTime index, before it starts sorting by > channel number. It would sure be nice to be able to get sqlite to sort on > the channel number for each time group. i.e. once all the 10:00 PM showings > have been returned and the first 10:30 PM showing is about to be returned, > sort all of the 10:00 PM showings by channel number. Sqlite is NOT doing > this but should. > > At this point, I'm going to assume that this is just a limitation in the > sqlite engine and I'll have to proceed in other ways to solve this issue. > > > > Ian Walters wrote: > > > >> SELECT showing.startTime FROM showing JOIN channel ON > >> showing.startTime >= > >> 123923 AND showing.stationId = channel.stationId ORDER BY > >> showing.startTime LIMIT 8; > > > > I don't know if it would be faster... but I would have written that. > > > > SELECT showing.startTime FROM showing JOIN channel USING (stationId) > > WHERE startTime < 123923 ORDER BY startTime LIMIT 8. > > > > Also I know in the latest version of SQLite its possible to 'hint' > > what indexes should be used, which might be helpful. There is also > > something on the contrib page that lets you check a query to see what > > indexes it does use. > > > > Sorry if the above lacks detail, its kinda a busy day. > > > > -- > > Ian > > ___ > > 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/Multi-column-ORDER-BY-across-table-peformance-problem-tp23109024p23169251.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 -- Jeremy Hinegardner jer...@hinegardner.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] retrieving message set with sqlite3_result_error()
Hi all, I'm using sqlite3_create_function() to create an sql function, and in the implementation of the sql function calling sqlite3_result_error() and sqlite3_result_error_code(). The value set with sqlite3_result_error_code() immediately appears as the return code from sqlite3_step() and is available as the return value from sqlite3_errcode() as soon as sqlite3_step returns. which is good. The problem is the value set with sqlite3_result_error() is not retrievable using sqlite3_errmsg() until after sqlite3_finalize() or sqlite3_reset() is called. Here's an example : http://pastie.org/352677 download as text : http://pastie.org/352677.txt Output from when I run it: SQLite Version: 3.6.7 Writting error msg 'This is an error' and error_code 42 to context Return code from sqlite3_step : 42 Before finalizing error code is: 42 Before finalizing error message is : unknown error Return value from sqlite3_finalize : 42 After finalizing errcode is: 42 After finalizing error message is : This is an error Am I mistaken in thinking that the error message set in the implementation of an sql function should be available at the same time as the error code? Is this a bug? enjoy, -jeremy -- Jeremy Hinegardner jer...@hinegardner.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.0 coming soon...
On Sat, Jul 12, 2008 at 05:00:32PM -0400, D. Richard Hipp wrote: > If you sees any problems with the upcoming release, or finds omissions > or errors or ambiguities in the documentation, now would be a very > good time to speak up. Thank you for your attention. http://www.sqlite.org/draft/doc/c3ref/c_config_getmalloc.html SQLITE_CONFIG_GETMALLOC is listed twice. I'm assuming the 2nd one, at the bottom of the page, should be SQLITE_CONFIG_GETMUTEX. Ticket with patch submitted #3219 enjoy, -jeremy -- ==== Jeremy Hinegardner [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] accessing the rowid of a blob for incremental IO
Hi all, I'm working on getting the incremental Blob API into my Amalgalite ruby extension and I seem to have hit a roadblock. And it is probably my misunderstanding of something in SQLite. I want to have that whomever uses my SQLite wrapper be able to use the Blob IO, with minimal extra work. To do that I'm imagining a scenario where the my users execute a prepared statement similar to: SELECT name, blob FROM blobs WHERE name = $name; And the driver, under the covers uses the blob IO routines to retrieve the blob data from SQLite when the user accesses the result set. At the C level in SQLite this ends up executing something like: sqlite3_prepare_v2( ... ) ... sqlite3_bind_text( ... ) ... sqlite3_step( ... ) /* access the column data after SQLITE_ROW is returned */ sqlite3_column_text( ... ) sqlite3_column_blob( ... ) /* <-- replace with sqlite3_blob access */ At this point, since the next column is a blob column I would like to access it via the sqlite3_blob_open() call and return a handle to it in the driver. I have all the information at this point to call sqlite3_blob_open() except for the rowid in the origin table of the blob that is being returned. And since my user hasn't specified the appropriate ROWID, OID, or _ROWID_ as part of the select, I am unable to get the rowid to invoke sqlite3_blob_open(). My question is: Is there a way to access the appropriate rowid that is associated with a value in a result set if the user has not specified it explicitly in the SQL query ? I'm using SQLITE_ENABLE_COLUMN_METADATA so I have origin information available at result set time, I'm wondering if it is that much of a stretch to have the rowid of the value in question at the same time. If this doesn't make any sense, let me know and I'll try and rephrase. enjoy, -jeremy -- ==== Jeremy Hinegardner [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] Initial release of amalgalite, a ruby wrapper for sqlite3
Hi all, For those interested, I just released a Ruby wrapper for the SQLite3 amalgamation. It embeds the Amalgamation directly into the Ruby extension. I would consider Amalgalite beta right now. It is fully usable; but it does not contain all the features I want yet. Give it a whirl and let me know what you think. * http://copiousfreetime.rubyforge.org/amalgalite/ * http://copiousfreetime.org/articles/2008/06/21/amalgalite-0-1-0-released.html enjoy, -jeremy -- Jeremy Hinegardner [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Skype client using SQLite?
On Tue, Aug 28, 2007 at 03:13:47PM +, [EMAIL PROTECTED] wrote: > In reference to > >http://www.sqlite.org/cvstrac/tktview?tn=2592 > > This is the first public indication we have had that > Skype is using SQLite in their windows clients. However, > the person who wrote the ticket seems to be a bit confused. > Can any able hackers in the SQLite community confirm that > the Skype windows client is using SQLite? It would be > nice to add them to the page of high-profile users. I can confirm that there is a sqlite db in my Skype on Mac OSX In the file $HOME/Library/Application\ Support/Skype//dyncontent/bundle.dat % echo .schema | sqlite3 bundle.dat CREATE TABLE bupdate (uri text, type text, meta text, prio integer,id integer, body blob, terms blob); CREATE TABLE events (id intenger, event integer, event_time integer); CREATE TABLE install (uri text, type text, meta text, prio integer,id integer, body blob, terms blob); CREATE TABLE itemkeys (id integer, keys text); CREATE TABLE reported (id integer, exposed_cnt integer, exposed_time integer, exec_cnt integer, close_cnt integer); CREATE TABLE stats (id integer, exposed_cnt integer,exposed_time integer, exec_cnt integer, close_cnt integer,last_expose_start integer, exposing_now integer); CREATE INDEX bupdate_id on bupdate (id); CREATE INDEX bupdate_uri on bupdate (uri); CREATE INDEX events_id on events (id); CREATE INDEX install_id on install (id); CREATE INDEX install_uri on install (uri); CREATE INDEX itemkeys_id on itemkeys (id); CREATE INDEX reported_id on reported(id); CREATE INDEX stats_id on stats (id); % od -c -N 16 bundle.dat 000S Q L i t e f o r m a t 3 \0 Although in mycase, all the tables are empty. enjoy, -jeremy -- ======== Jeremy Hinegardner [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Need sql query help
On Sun, Jun 25, 2006 at 07:54:13AM -0700, onemind wrote:
> The thing is, i am going to need to use different letters each time to
> search through over 200,000 words in a database and it needs to be fast.
The quick and dirty way to do this using a sqlite would be to keep a
separate column from the work column with the letters in the word
sorted. You would have to keep this column up to date yourself though.
I do not believe there is an out-of-the-box way to populate this column
with sqlite. To do so would involve a trigger and a user defined
function.
In other words you would have:
sqlite> CREATE TABLE words (word text, sorted text);
And inserting into the table :
sqlite> insert into words(word,sorted) values ("quads","adqsu");
sqlite> insert into words(word,sorted) values ("quidas","adiqsu");
sqlite> insert into words(word,sorted) values ("queen","eenqu");
Then your searches would be in the vein of:
sqlite> select word from words where sorted like "%a%d%q%s%";
quads
quidas
But, this would result in a full table scan for all searches and as a
result be O(N) and would not be very efficient. Additionally it would
probably be easier to just write a script that would do the same thing
with a textfile of the words and search through it.
Although... sounds like a fun little project...
% ./init-db
Creating words.db
Inserting words from /usr/share/dict/words...
Inserted 483523 words into db in 58.900854 seconds.
% ./search-db aqds
Searching for aqds -> SELECT word FROM words WHERE sorted LIKE '%a%d%q%s%'
found 754 results in 1.521026 seconds
I still wouldn't suggest this method, but it is a fun exercise. Each
search in this manner is a full table scan.
> What technology would be best suited for this task? I just assumed that a
> databse would be ideal, why do you say sql isn't suited for this and what
> is?
Others have given good suggestions, Ulrick's bitset is particularly
nice. Well, its a lazy sunday, lets see what happens. using the same
approach as above, but using a 'bitset' column instead of 'sorted'.
Also an index is created on the bitset column.
% ./init-db-bitset
Creating words-bitset.db
Inserting words from /usr/share/dict/words...
Inserted 483523 words into db in 75.368803 seconds.
% ./search-db-bitset aqds
Searching for words aqds -> SELECT word FROM words WHERE (bitset & 327689)
= 327689
found 754 results in 0.902595 seconds
Although I believe it should still have to do a table scan here.
But bitwise and is faster than a string comparison in any case.
Since this also does a full table scan, you'll probably want something
more along the lines of an inverted index of the words by letter in some
sort of dedicated data structure.
I started playing with this using sqlite to try and do an inverted index
by letter here, but it didn't get close to the performance of what the
bitset was doing.
Ahh, what a fun way to spend part a Sunday :-)
enjoy,
-jeremy
--
Jeremy Hinegardner [EMAIL PROTECTED]
[sqlite] Congratulations to the Integrator!
I would just like to offer my congratulations to D. Richard Hipp for receiving the Open Source Integrator Award for SQLite. http://osdir.com/Article6677.phtml Your work is appreciated. enjoy, -jeremy -- Jeremy Hinegardner [EMAIL PROTECTED]
Re: [sqlite] Update variable problems. Bug?
On Sun, Jul 03, 2005 at 02:59:19PM -0400, Tom Shaw wrote: > Using PHP 5 and SQLite 2.8.14 (This also occurs when commanding > SQLite directly) > > I create a table: > > CREATE TABLE dnsbl (ip INTEGER PRIMARY KEY, flags VARCHAR(8), ctime > INTEGER, mtime INTEGER, cnt INTEGER, ptr TEXT, refcon INTEGER); > > Then I insert a record: > > INSERT INTO dnsbl VALUES(-596248527,"IP", 1120286944, 1120286944, 1, "", 0); > > Note the value of flags="IP". Now I update the record updating flags > and ctime because I did keep track if they changed: > > UPDATE dnsbl SET flags="IP", ctime=1120286944, mtime=1120311794, > cnt=2, refcon=0 WHERE ip=-596248527; > > After the update I check the update by > > SELECT * FROM dnsbl WHERE ip=-596248527; > > Flags somehow change from "IP" to -596248527. Note that the value > -596248527 now appears in the DB variable ip as well as the flags > variable. > > Now there should be nothing wrong with the above that I can see BUT > if I change the DB variable name from ip to ip_num all works OK > > Help and understanding is appreciated. It appears to me that in the update statement that "IP" is getting set to the value of the "ip" column. That is, when you do the update the value of the flags column is getting set to the value of the "ip" column in the same row. A more concrete example: $sqlite test.db SQLite version 2.8.15 Enter ".help" for instructions sqlite> c eate table t1 (i,j,k); sqlite> insert into t1 values (1,'foo','bar'); sqlite> insert into t1 values (1,'some','value'); sqlite> select * from t1; 1|foo|bar 1|some|value sqlite> update t1 set j="I" where i = 1; sqlite> select * from t1; 1|1|bar 1|1|value Now if you select the possible permutations of K from the table: sqlite>.mode columns sqlite> select k,"k",'k',K,"K",'K',"S",'S' from t1; bar bar k bar bar K S S value value k value value K S S sqlite> select S from t1; SQL error: no such column: S In all of these cases the "K",'K',K,"S", etc. tokens are 'expressions' according to sqlite. See http://www.sqlite.org/lang_update.html http://www.sqlite.org/lang_select.html And http://www.sqlite.org/lang_expr.html explains that an 'expression' can be a column name or a literal value. From your example, since IP is the name of a column in your table "IP" evaluated to the column name. If you wanted the literal 'IP' to be the value in the flags columns then it would be better to use 'IP' instead of "IP" as "" appears to first evaluate to a column name and if that is not the case then it is a literal. That is, as far as expressions are concerned: without single or double quotes -> column name with double quotes -> column name first, if that fails literal with single quotes -> literal This is what I gleaned from the documentation and experimenting. Did I get this correct? enjoy, -jeremy -- Jeremy Hinegardner [EMAIL PROTECTED]
Re: [sqlite] using sqlite as a temporary database to process lots of data
On Tue, Jun 28, 2005 at 04:15:15PM -0400, Patrick Dunnigan wrote: > I am currently using SQLite to process @ 400 million records (and climbing) > a day by reading files, importing them into SQLite, and summarizing. The > summed data goes into Oracle. This is a production application that is very > stable. Holding the data in SQLite in memory as opposed to a C struct > reduced development time and makes it easy to change the summarization > output by just modifying the SQL statements. I would also be interested in hearing about your solution. We have an internal custom ETL solution that works quite well, but I've been toying with the idea of migrating to using SQLite db's as the unit of work for transformations instead of flat files. Are you summarizing in batches of 1 million records? All of them each day? If you could give a brief description of your workflow I would be quite interested. enjoy, -jeremy -- ==== Jeremy Hinegardner [EMAIL PROTECTED]
Re: [sqlite] beat 120,000 inserts/sec
On Fri, Apr 08, 2005 at 11:01:02PM -0400, Al Danial wrote: [...] > What kind of insert performance do you see on your machine? If it > is substantially better than 120 kinserts/s what kind of hardware > do you have? I'm especially interested in how much faster the > code runs on systems with multiple disks in a RAID 0 configuration. > Are there other tricks to speeding insert performance? Is it even > reasonable to ask for more? -- Al ./sqlite_insert 10 5 10 inserts to /tmp/a.db in 0.671 s = 149057.52 inserts/s ./sqlite_insert 200 2 200 inserts to /tmp/a.db in 14.437 s = 138535.38 inserts/s ./sqlite_insert 200 5 200 inserts to /tmp/a.db in 15.322 s = 130530.52 inserts/s Not substantially better. My configuration: AMD Athlon(tm) 64 Processor 3000+ 1GB RAM 2 x Maxtor 200GB SATA150 7200RPM 8MB HD I'm running OpenBSD 3.6 with a customer kernel compiled to support OpenBSD's software raid (RAIDFrame). I have the 2 drives arranged in a RAID 1 configuration. enjoy, -jeremy -- ==== Jeremy Hinegardner [EMAIL PROTECTED]
Re: [sqlite] Conversion mysql -> sqlite
On Tue, Mar 15, 2005 at 03:26:26PM -0500, Peter Jay Salzman wrote: > Hi all, > > I know very little about mysql and sqlite - I've only played around a little > bit with databases, so I'm a newbie in this area. > > There's a blogger called Wheatblog that I've been trying to convert from > mysql to sqlite so I don't have to run a full RDMBS deamon on my > underpowered machine for just a single blog program. [snip] There is a nice tool called SQLFairy[1] which can do sql translation from one database's sql to another's. I've used it many times for mysql to sqlite sql conversion. Right now it really only works with the definition portion of the SQL (CREATE statements) and not with the data manipulation statements. enjoy, -jeremy [1] http://sqlfairy.sourceforge.net/ -- ======== Jeremy Hinegardner [EMAIL PROTECTED]
Re: [sqlite] Version 3.1.3
On Sat, Feb 19, 2005 at 09:30:26PM -0500, D. Richard Hipp wrote: > As always, please let me know if you find any > problem. gmake && gmake test on OpenBSD 3.6/amd64 results in the following warnings and test errors. Looks like it is all because of 64bit architecture. If you want me to test anything I'll be happy to do so. These warnings are mentioned in ticket #848: src/table.c: In function `sqlite3_get_table': src/table.c:146: warning: cast to pointer from integer of different size src/table.c: In function `sqlite3_free_table': src/table.c:191: warning: cast from pointer to integer of different size src/vdbeaux.c: In function `displayP3': src/vdbeaux.c:389: warning: cast from pointer to integer of different size These warnings aren't mentioned anywhere else: src/test1.c: In function `test_collate_func': src/test1.c:1086: warning: cast from pointer to integer of different size src/test1.c: In function `test_collate_needed_cb': src/test1.c:1168: warning: cast to pointer from integer of different size These tests failed: printf-8.1... Error: integer value too large to represent printf-8.2... Error: integer value too large to represent 2 errors out of 19732 tests Failures on these tests: printf-8.1 printf-8.2 enjoy, -jeremy -- ==== Jeremy Hinegardner [EMAIL PROTECTED]
Re: [sqlite] Is there any way to enable recursive triggers?
On Tue, Jan 04, 2005 at 05:54:04AM -0500, D. Richard Hipp wrote: > One stubling block with recursive triggers is that a recursive > trigger can result in an infinite loop. I sent out a query a > month or so ago requesting ideas on how to detect and deal with > infinite loops in recursive triggers. I got a few helpful > responses. More input would be appreciated. Graeme Birchall has an excellent book (The DB2 UDB Cookbook) available for download. It has an entire chapter dedicated to DB2's Recursive SQL and one section of it is "Halting Recursive Processing". Maybe it will provide some insight. http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM enjoy, -jeremy -- ======== Jeremy Hinegardner [EMAIL PROTECTED]
Re: [sqlite] Close() and file locks...
>>>>> "eno" == eno <[EMAIL PROTECTED]> writes: eno> Marco Bambini wrote: >> After the 2.8.10 release ... "This version fixes a critical locking >> bug in Unix. It turns out that any call to close() clears all locks >> on file that was closed (who knew?) which then left the database >> vulnerable to corruption from other processes. That bug has been >> cleared by embargoing all close() calls until all locks of been >> released." I have made some investigation... From "Advanced >> Programming in the UNIX Environment" by R. Stevens: eno> thank you for this clarification. But what does R. Stevens write eno> about how to circumvent this strange behaviour? I sent some info to the list earlier this week on this subject, but I believe I sent it from the wrong address. Anyway, I also looked through Stevens this past weekend and I didn't find any suggestions in particular to work around this. Although, he does have an explanation of why when close() is called that the kernel can't tell if the file descriptor being closed has the associated lock(s) or not. I can look it up again tonight and post the relevant information if people are interested. After looking at the locking and the IPC sections of Stevens, I came up with the following idea. Please make any suggestions, corrections etc. >From my understanding this problem will only appear within the same process id. An advisory lock will work between different process and this includes across a fork(), but maybe not across an exec(). So the corruption can only occur when 2 or more file descriptors are opened within the same process id. This would include multithreaded applications in which all the threads have the same PID, and it also is evident in Linux where threads have different PID's. As a possible solution, use a shared memory semaphore locking scheme to replace the advisory record locking system currently being used. I thought the generation of the shared memory key_t might be a problem but using ftok() with the database filename should work just fine. I can see an advantage with this system in that you wouldn't need to use a combination of advisory locking (for between processes) with mutexes (for within the same process) to ensure safety. One system, semaphores could be used. Additionally, according to data in Stevens, using a semaphore is faster than using an advisory lock. But I don't think this includes the allocation and destruction of the shared memory used by the semaphores. And locking is probably not a performance bottleneck within SQLite. Of course the huge disadvantage to this system is that if the process with sqlite is killed for some reason, there could be spare shared memory lying around. That may be an unacceptable risk. I haven't completely thought through all the ramifications of using semaphores, but I figured it wouldn't hurt to share my thoughts up to this point. Maybe it will help. enjoy, -jeremy -- Jeremy Hinegardner [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
