RE: [sqlite] Would SQLite be a good choice
Most languages have the ability to kick off different threads that run in the background. You can have a writer thread that dumps the queue and then sleeps for another minute and then continues the loop. I don't know PHP, but a quick search found that it does have a Thread class which I presume could be used for this purpose. If you need more help on implementing that part I'm sure a PHP-specific mailing list would be able to provide better assistance. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: FredAt [mailto:[EMAIL PROTECTED] Sent: Friday, November 16, 2007 10:17 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Would SQLite be a good choice Thank you! I like the idea of batching the writes and dispensing with the intermediate flat file. However, it is not clear to me where I would run the dequeuer from. The only thing I can think of is running the dequeuer as a PHP script which I arrange to call every few minutes. I can certainly do this from one of my development machines and there is probably a way to run the script directly on my server but I don't know of that. What did you have in mind? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Would SQLite be a good choice
FredAt wrote: Samuel R. Neff wrote: You could maintain a queue in memory of all the data to be written, have each page view queue up the new data and have a single db writer thread that dequeues items and writes to the db. That way you get the benefit of writing directly to the db, but do not have the extra overhead on each page view of writing to the db. There is a risk of data loss for whatever is queued up and not yet written to the db but as long as you're running the writer thread often enough it's not a great concern (especially since the data is not critical in that if you lose a few records it's no big loss). By batching the writes you also can take advantage of transactions to speed up writes. HTH, Sam Thank you! I like the idea of batching the writes and dispensing with the intermediate flat file. However, it is not clear to me where I would run the dequeuer from. The only thing I can think of is running the dequeuer as a PHP script which I arrange to call every few minutes. I can certainly do this from one of my development machines and there is probably a way to run the script directly on my server but I don't know of that. What did you have in mind? You can write to a message queue or a named pipe connected to another process. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Would SQLite be a good choice
Dear Fred, I am using SQLite always in conjunction with PHP for web applications. To give you an idea about the possibilities : a. web application for law office : - holds history for approx. 10.000 files with regards to accounting and timesheets referenced to those files - holds complete accounting records for 5 years - credit control tool with possibilities to easily extract information b. invoice and deferral details for electronic appliances company : - holds records of all invoicing for the last two years 10.000 + records , plus model fro analytical redistribution c. tracking sales and purchase orders for IT company : - less impressive, but like the above two: All applications rely solely on SQLite as RDBMS and prove to be very stable. The added convenience when working with SQLITE 3 (the first application still uses SQLITE 2 -type databases) , keeps convincing me I do not need another system. In the past I always worked with MySQL, but never feel the need to switch back. I actually love SQLITE so much, I made a small tool in PHP to have PHPMyAdmin-like functionality. With best wishes, Wim FredAt schreef: Hello All, I have used SQLite off and on in Windows applications I have written and I really like it. However, what I am now considering doing is using a SQLite database to log access to one of my websites - I need to get a great deal more information than I can get via normal log files. Doing this will involve making the following entries to SQLite tables each time someone views a document on my site 1. A 20 byte string providing me with information regarding the user's computer. 2. 48 bytes of data, in three columns, in another table to allow me to track user activity during a session. On the server side I will be manipulating my SQLite databases using PHP. My site currently has around a 1000 page views per day and the number is growing. Is SQLite a good choice for the job? I like it and I want to avoid getting messy with more heavyweight options but I need to be sure that it is going to work. I would much appreciate any advice. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Would SQLite be a good choice
FredAt <[EMAIL PROTECTED]> wrote: > > It generally works better to append logging information to a file > > (since this avoids the overhead of updating indices and doing > > atomic commits) then transfering the information into an RDBMS > > (SQLite or other) for analysis. > > > > So, I guess I could write to a flatfile and then run a daily PHP script to > transfer the information into my SQLite DB for analysis. I take it that > you are telling me that with the quantity of information I anticipate > writing per day - at current estimates no more than 256 bytes per page view > with around a 1000 page views per day (but growing) - I am not likely to run > into any size issues with SQLite? Like I said, as far as possible I want to > stick with SQLite. > Size should not be a problem. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Would SQLite be a good choice
> It generally works better to append logging information to a file > (since this avoids the overhead of updating indices and doing > atomic commits) then transfering the information into an RDBMS > (SQLite or other) for analysis. > So, I guess I could write to a flatfile and then run a daily PHP script to transfer the information into my SQLite DB for analysis. I take it that you are telling me that with the quantity of information I anticipate writing per day - at current estimates no more than 256 bytes per page view with around a 1000 page views per day (but growing) - I am not likely to run into any size issues with SQLite? Like I said, as far as possible I want to stick with SQLite. -- View this message in context: http://www.nabble.com/Would-SQLite-be-a-good-choice-tf4821074.html#a13793448 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Would SQLite be a good choice
Samuel R. Neff wrote: > > > You could maintain a queue in memory of all the data to be written, have > each page view queue up the new data and have a single db writer thread > that > dequeues items and writes to the db. That way you get the benefit of > writing directly to the db, but do not have the extra overhead on each > page > view of writing to the db. There is a risk of data loss for whatever is > queued up and not yet written to the db but as long as you're running the > writer thread often enough it's not a great concern (especially since the > data is not critical in that if you lose a few records it's no big loss). > > By batching the writes you also can take advantage of transactions to > speed > up writes. > > HTH, > > Sam > Thank you! I like the idea of batching the writes and dispensing with the intermediate flat file. However, it is not clear to me where I would run the dequeuer from. The only thing I can think of is running the dequeuer as a PHP script which I arrange to call every few minutes. I can certainly do this from one of my development machines and there is probably a way to run the script directly on my server but I don't know of that. What did you have in mind? -- View this message in context: http://www.nabble.com/Would-SQLite-be-a-good-choice-tf4821074.html#a13794975 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Would SQLite be a good choice
You could maintain a queue in memory of all the data to be written, have each page view queue up the new data and have a single db writer thread that dequeues items and writes to the db. That way you get the benefit of writing directly to the db, but do not have the extra overhead on each page view of writing to the db. There is a risk of data loss for whatever is queued up and not yet written to the db but as long as you're running the writer thread often enough it's not a great concern (especially since the data is not critical in that if you lose a few records it's no big loss). By batching the writes you also can take advantage of transactions to speed up writes. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: FredAt [mailto:[EMAIL PROTECTED] Sent: Friday, November 16, 2007 8:26 AM To: sqlite-users@sqlite.org Subject: [sqlite] Would SQLite be a good choice Hello All, I have used SQLite off and on in Windows applications I have written and I really like it. However, what I am now considering doing is using a SQLite database to log access to one of my websites - I need to get a great deal more information than I can get via normal log files. Doing this will involve making the following entries to SQLite tables each time someone views a document on my site 1. A 20 byte string providing me with information regarding the user's computer. 2. 48 bytes of data, in three columns, in another table to allow me to track user activity during a session. On the server side I will be manipulating my SQLite databases using PHP. My site currently has around a 1000 page views per day and the number is growing. Is SQLite a good choice for the job? I like it and I want to avoid getting messy with more heavyweight options but I need to be sure that it is going to work. I would much appreciate any advice. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Would SQLite be a good choice
Size should not be a problem. Thank you very much! -- View this message in context: http://www.nabble.com/Would-SQLite-be-a-good-choice-tf4821074.html#a13794433 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Would SQLite be a good choice
FredAt <[EMAIL PROTECTED]> wrote: > Hello All, > > I have used SQLite off and on in Windows applications I have written and I > really like it. However, what I am now considering doing is using a SQLite > database to log access to one of my websites - I need to get a great deal > more information than I can get via normal log files. Doing this will > involve making the following entries to SQLite tables each time someone > views a document on my site > > 1. A 20 byte string providing me with information regarding the user's > computer. > 2. 48 bytes of data, in three columns, in another table to allow me to track > user activity during a session. > > On the server side I will be manipulating my SQLite databases using PHP. My > site currently has around a 1000 page views per day and the number is > growing. Is SQLite a good choice for the job? I like it and I want to > avoid getting messy with more heavyweight options but I need to be sure that > it is going to work. I would much appreciate any advice. It generally works better to append logging information to a file (since this avoids the overhead of updating indices and doing atomic commits) then transfering the information into an RDBMS (SQLite or other) for analysis. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -