Re: [sqlite] OT: Donations?
On 2/28/06, Chethana, Rao (IE10) <[EMAIL PROTECTED]> wrote: > Hi! > > Did u enquire about donations to sqlite.org ? what reply u got? > No didn't. And I dunno exactly about donations, you need to wait the sqlite team or you can ask to D. Ricard Hipp for that. -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
RE: [sqlite] OT: Donations?
Hi! Did u enquire about donations to sqlite.org ? what reply u got? -Original Message- From: Firman Wandayandi [mailto:[EMAIL PROTECTED] Sent: Monday, February 27, 2006 1:08 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] OT: Donations? On 2/27/06, Chethana, Rao (IE10) <[EMAIL PROTECTED]> wrote: > Hello! > > That's a good idea! > I think u can post ur question to www.sqlite.org & see. They'll b able > to tell u. > Post to sqlite.org? why not here? that's nice to get some donations. -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] File format changed !!
On 2/28/06, Pavan Savoy <[EMAIL PROTECTED]> wrote: > I am trying to open a database created in SQLite 3.3.1 in SQlite 3.1.2 > and I get the following err when I try to see the sqlite_master table .. > > SQL error: unsupported file format > > > I suppose the file format has been changed over the versions, but the > reverse does work, i.e db created on 3.1.2 can be opened on 3.3.1. > > > > I just wanted some sort of document which suggests changes in file format > over the versions, " changes " page doesnt represent the full picture > though > > http://sqlite.org/formatchng.html > > > Thank you > -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
RE: [sqlite] File format changed !!
Hi! Just use select * from sqlite_master; -Original Message- From: Pavan Savoy [mailto:[EMAIL PROTECTED] Sent: Monday, February 27, 2006 10:13 PM To: sqlite-users@sqlite.org Subject: [sqlite] File format changed !! I am trying to open a database created in SQLite 3.3.1 in SQlite 3.1.2 and I get the following err when I try to see the sqlite_master table .. SQL error: unsupported file format I suppose the file format has been changed over the versions, but the reverse does work, i.e db created on 3.1.2 can be opened on 3.3.1. I just wanted some sort of document which suggests changes in file format over the versions, " changes " page doesnt represent the full picture though Thank you
[sqlite] File format changed !!
I am trying to open a database created in SQLite 3.3.1 in SQlite 3.1.2 and I get the following err when I try to see the sqlite_master table .. SQL error: unsupported file format I suppose the file format has been changed over the versions, but the reverse does work, i.e db created on 3.1.2 can be opened on 3.3.1. I just wanted some sort of document which suggests changes in file format over the versions, " changes " page doesnt represent the full picture though Thank you
Re: [sqlite] Re: what is on my computer
ProcessExplorer will also list all resources open per process. That is the easiest way of doing it. Start Process Explorer and then do Find > Find Handle and type in a portion of the filename. You'll then get a listing of all processes/handles with that portion of the filename. Roger
Re: [sqlite] Re: what is on my computer
On 2/28/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > look i have over 600 temp file i cannot remove or delete why is this. they > > all start with sqlite_ then there is another over 600 files with 3k > > memory, > > but the sqlite_ files have ok memory > > I'm getting more and more complaints from people, like the one > above, about lots of temp file with sqlite in the name. Presumably > these are windows users. Does anybody have any idea what program > they might be running that leaves lots of sqlite_* temp files > laying around? You might want to direct users to use www.sysinternals.com FileMon or Handle program to see which program has the files open (the Handle program is similar to linux lsof and FileMon monitors all file accesses). ProcessExplorer will also list all resources open per process. For a program that behaves that bad I would not be surprised if sqlite got to be used by some spyware/adware/trojan, etc. Best regards, ~Nuno Lucas
Re: [sqlite] Re: what is on my computer
On February 27, 2006 06:29 pm, [EMAIL PROTECTED] wrote: > [EMAIL PROTECTED] wrote: > > look i have over 600 temp file i cannot remove or delete why is > > this. they all start with sqlite_ then there is another over 600 > > files with 3k memory, but the sqlite_ files have ok memory > > I'm getting more and more complaints from people, like the one > above, about lots of temp file with sqlite in the name. Presumably > these are windows users. Does anybody have any idea what program > they might be running that leaves lots of sqlite_* temp files > laying around? No idea. You might have to ask the user to grep every file on their computer and report back which programs contain "sqlite" Go to a DOS shell, then: change to drive c: change to directory \ then grep -a -r "sqlite" * >filelist.txt They may have to perform it in "safemode" if they have troubles being locked-out of some files currently running. and have them send you filelist.txt so that between that user and other users complaining, their respective filelist.txt may show a common file or at least some files to focus on. Hope that helps.
Re[2]: [sqlite] Triggers and TEMP tables: ticket #1689
I definitely second this since I actually got bitten by this last week :-) We split over 2 databases for the same reason as Derrell and always attach after opening them. Surely it's harmless to remove these checks - if something goes wrong, the trigger will just fail normally with a "no such table"? >> Should I remove the tests from SQLite that prevent >> triggers in one database from referring to tables in >> a different database? Or should I leave things as >> they are and close ticket #1689 with a remark of >> "works as designed". > I have had use for triggers that could operate on tables in a different > database, and been unable to use them. I separate tables into two primary > databases because the tables in one of them can be locked for long periods of > time (long transactions) while the tables in the other database are accessed > frequently. The mechanism for determining which databases will be operated on > is external to SQLite, so I would know that it is safe to issue a request that > would cause a trigger to operate on the other database. > I'd vote to allow a trigger to operate on a table in a different database, if > there are no reasons other than "they shouldn't need to do that" preventing > it. > Derrell -- Best regards, Takamailto:[EMAIL PROTECTED]
[sqlite] Re: what is on my computer
[EMAIL PROTECTED] wrote: > look i have over 600 temp file i cannot remove or delete why is this. they > all start with sqlite_ then there is another over 600 files with 3k memory, > but the sqlite_ files have ok memory I'm getting more and more complaints from people, like the one above, about lots of temp file with sqlite in the name. Presumably these are windows users. Does anybody have any idea what program they might be running that leaves lots of sqlite_* temp files laying around?
Re: [sqlite] Triggers and TEMP tables: ticket #1689
[EMAIL PROTECTED] writes: > Should I remove the tests from SQLite that prevent > triggers in one database from referring to tables in > a different database? Or should I leave things as > they are and close ticket #1689 with a remark of > "works as designed". I have had use for triggers that could operate on tables in a different database, and been unable to use them. I separate tables into two primary databases because the tables in one of them can be locked for long periods of time (long transactions) while the tables in the other database are accessed frequently. The mechanism for determining which databases will be operated on is external to SQLite, so I would know that it is safe to issue a request that would cause a trigger to operate on the other database. I'd vote to allow a trigger to operate on a table in a different database, if there are no reasons other than "they shouldn't need to do that" preventing it. Derrell
[sqlite] Triggers and TEMP tables: ticket #1689
A user complains in ticket #1689 that SQLite does not allow triggers in one database to refer to tables in a different database or to TEMP tables. SQLite actually goes to a lot of trouble to detect these kinds of things since, as we were writing the code, we could not think of any circumstance where a persistent trigger referring to a temp table could be anything other than a mistake in the SQL. But ticket number #1689 http://www.sqlite.org/cvstrac/tktview?tn=1689 provides two examples of cases where it might be useful for a persistent trigger to refer to a temporary table. I am still dubious, but I am willing to consider the possibility that this might actually be useful to someone. So what do other users think? Should I remove the tests from SQLite that prevent triggers in one database from referring to tables in a different database? Or should I leave things as they are and close ticket #1689 with a remark of "works as designed". What do other database engines do? Does anybody know? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Suggestions for optimizing delete
"Brett Wilson" <[EMAIL PROTECTED]> wrote: > Hi, > > I'm working on using sqlite for Firefox's bookmarks and history > system. We need to expire history older than N days when the browser > shuts down. It's currently slower than I would like and I'd appreciate > any tricks you might be able to suggest. > > Database layout: We have a table of URLs and titles called > moz_history. Then there is a separate table called moz_historyvisit > that associates a date and some other stats with the ID of the correct > entry in moz_history. There may be many visits for each URL (each time > you visit the page). We have a table of bookmarks that references the > history table as well. > > Deleting the visits is easy, we have an index over the dates so we just say > "DELETE FROM moz_historyvisit WHERE visit_date < ?1" > This seems fast enough. > > Now we need to expire history entries that have no visits, are not > bookmarked, and do not begin with "place:" (which are used for > internal stuff). The simple command is > DELETE from moz_history WHERE > id NOT IN (SELECT page_id FROM moz_historyvisit) > AND id NOT IN (SELECT page_id FROM moz_bookmarks) > AND SUBSTR(url,0,6) <> 'place:'; > This is pretty slow. We have indices over page_ids in both bookmarks > and visits. The string compare seems like it might be slow but it > doesn't seem to matter much whether we keep it in or not (99% of the > URLs will fail after the first character compare because they begin > with 'http:'). > Some ideas: CREATE INDEX moz_history_id ON moz_history(id); DELETE FROM moz_history WHERE id IN (SELECT page_id FROM moz_historyvisit WHERE visit_date 'place:'; Do the above before deleting from moz_historyvisit, of course. This is similar to your trigger idea. Note that it only works if moz_history.id is indexed. The optimizer rule is this: column IN (subquery) will use an index if an index is available on column. But column NOT IN (subquery) always does a full table scan. Another thought, though less likely to help much is: DELETE FROM moz_history WHERE id NOT IN (SELECT page_id FROM moz_hsitoryvisit UNION SELECT page_id FROM moz_bookmarks) AND SUBSTR(url,0,6) <> 'place:'; -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Suggestions for optimizing delete
On 2/27/06, Brett Wilson <[EMAIL PROTECTED]> wrote: > > I tried some other things like creating a trigger that sets a flag in > moz_history when a visit has been deleted so we only have to check > those items, but it still seems slow. Can anybody offer any insight on > this? I'm not really a SQL expert. You might post the text result when you run an explain on the slow sql http://sqlite.org/lang_explain.html It will help understand what's happening "underneath"
[sqlite] Suggestions for optimizing delete
Hi, I'm working on using sqlite for Firefox's bookmarks and history system. We need to expire history older than N days when the browser shuts down. It's currently slower than I would like and I'd appreciate any tricks you might be able to suggest. Database layout: We have a table of URLs and titles called moz_history. Then there is a separate table called moz_historyvisit that associates a date and some other stats with the ID of the correct entry in moz_history. There may be many visits for each URL (each time you visit the page). We have a table of bookmarks that references the history table as well. Deleting the visits is easy, we have an index over the dates so we just say "DELETE FROM moz_historyvisit WHERE visit_date < ?1" This seems fast enough. Now we need to expire history entries that have no visits, are not bookmarked, and do not begin with "place:" (which are used for internal stuff). The simple command is DELETE from moz_history WHERE id NOT IN (SELECT page_id FROM moz_historyvisit) AND id NOT IN (SELECT page_id FROM moz_bookmarks) AND SUBSTR(url,0,6) <> 'place:'; This is pretty slow. We have indices over page_ids in both bookmarks and visits. The string compare seems like it might be slow but it doesn't seem to matter much whether we keep it in or not (99% of the URLs will fail after the first character compare because they begin with 'http:'). I tried some other things like creating a trigger that sets a flag in moz_history when a visit has been deleted so we only have to check those items, but it still seems slow. Can anybody offer any insight on this? I'm not really a SQL expert. Thanks in advance, Brett
[sqlite] RE: [RBL] RE: [sqlite] Writing wrappers?
In most cases (but not all - C++,Delphi) yes. A wrapper is a lump of C code that bridges the SQLite environment with your own specific language. I just did a bit of research for you and found that Frontier uses a scripting language called UserTalk to allow users to interact with it. It also only runs on Windows. With this in mind, you will need to write a UserTalk extension as a DLL (C, C++ or Delphi) to agregate SQLite functionality into a small number of UserTalk verbs e.g. OpenDB, CloseDB and ExecuteDB perhaps. Although I couldn't find it on the Frontier web site, I'm sure there is an extension specification defined somewhere. I did notice that PostgreSQL has a Frontier extension - perhaps that would be a good starting point for some sample code http://spicynoodles.org/projects/postgresql/ Download the http://www.pivotal-solutions.co.uk/downloads/company/pssqlite.zip package for an example of C DLL (VC6) that provides bothe a VB and a Java interface with dynamic binding to 2.8.x and 3.x.x databases. Just change the references to VB/Java specific stuff (data type conversions) and you're almost there. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Gewirtz Sent: 25 February 2006 05:03 To: sqlite-users@sqlite.org Subject: [RBL] RE: [sqlite] Writing wrappers? Thanks! So, being completely naïve, a wrapper's a chunk of C code, right? It's not some kind of scripty interface into SQLite? So would the docs really be the SQLite API, but with calls from the host language to the SQLite API? -- David -Original Message- From: Clay Dowling [mailto:[EMAIL PROTECTED] Sent: Friday, February 24, 2006 10:07 AM To: sqlite-users@sqlite.org Subject: [RBL] Re: [sqlite] Writing wrappers? David Gewirtz said: > > I've just started to explore SQLite and I've noticed a whole pile of > wrappers for various environments. The environment I'm using (the open > source Frontier Kernel) doesn't have any wrappers, so I'd like to > write one. > Can someone post some pointers to resources on how to get started > writing wrappers? As somebody who has written his own wrapper, the best advice I can give is to see what the needs of your application are first. Your wrapper class effectively becomes the SQLite client program, so make sure that it addresses all of the needs of SQLite such as statement finalization and parameter population. Make sure that the wrapper itself addresses the needs of your program and makes it so that you don't need to make sqlite3_* calls from your code. It's definitely a good idea to be comfortable with writing a couple of C programs that use SQLite first before trying to write a wrapper. The standard C interface to SQLite is pretty good already, and once you become comfortable with it you'll be able to see what you want to do with your wrapper. Clay -- Simple Content Management http://www.ceamus.com
Re: [sqlite] OT: Donations?
On 2/27/06, Chethana, Rao (IE10) <[EMAIL PROTECTED]> wrote: > Hello! > > That's a good idea! > I think u can post ur question to www.sqlite.org & see. They'll b able > to tell u. > Post to sqlite.org? why not here? that's nice to get some donations. -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
RE: [sqlite] OT: Donations?
Hello! That's a good idea! I think u can post ur question to www.sqlite.org & see. They'll b able to tell u. -Original Message- From: Denis Sbragion [mailto:[EMAIL PROTECTED] Sent: Monday, February 27, 2006 12:50 AM To: sqlite-users@sqlite.org Subject: [sqlite] OT: Donations? Hello, might be a bit off topic, but, is there any way to make donations to SQLite? I haven't been able to find informations on the SQLite web site. Bye, -- Denis Sbragion InfoTecna Tel: +39 0362 805396, Fax: +39 0362 805404 URL: http://www.infotecna.it
[sqlite] OT: Donations?
Hello, might be a bit off topic, but, is there any way to make donations to SQLite? I haven't been able to find informations on the SQLite web site. Bye, -- Denis Sbragion InfoTecna Tel: +39 0362 805396, Fax: +39 0362 805404 URL: http://www.infotecna.it
