Re: [sqlite] Mac OS
At 11:32 AM +0600 2/15/06, Kirill wrote: Whether will be SqlLite in the future and under Mac OS if there will be that as soon? SQLite runs under Mac OS X right now, and has for a long time. A version is even bundled with X.4 Tiger. -- Darren Duncan
[sqlite] Mac OS
Whether will be SqlLite in the future and under Mac OS if there will be that as soon?
RE: Re[2]: [sqlite] To whom to inform on a bug?
> -Original Message- > From: Chris Schirlinger [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 14, 2006 8:45 PM > To: sqlite-users@sqlite.org > Subject: Re: Re[2]: [sqlite] To whom to inform on a bug? > > I tried the data you mentioned in SQLite Explorer and the SQL > statemen worked correctlyt: > > > select * from tResult where tex like '%ra%' > > returned the expected record perfectly > > Here is the output of the command line test > > SQLite version 3.3.3 > Enter ".help" for instructions > sqlite> .schema > CREATE TABLE tResult (id INTEGER,tex STRING); > sqlite> select * from tResult; > 3229|...Oracle... > sqlite> select * from tResult where tex like '%ra%'; > 3229|...Oracle... Perhaps the original poster is using a non-English version of Windows? Not sure if that makes a difference in this case. Robert
Re: Re[2]: [sqlite] To whom to inform on a bug?
I tried the data you mentioned in SQLite Explorer and the SQL statemen worked correctlyt: > select * from tResult where tex like '%ra%' returned the expected record perfectly Here is the output of the command line test SQLite version 3.3.3 Enter ".help" for instructions sqlite> .schema CREATE TABLE tResult (id INTEGER,tex STRING); sqlite> select * from tResult; 3229|...Oracle... sqlite> select * from tResult where tex like '%ra%'; 3229|...Oracle... > Script: > select * from tResult where tex like '%ra%' > result = 0 > > select tex from tResult where id = 3229 > tex = "...Oracle..." > > Soft on broblem: > sqlite3explorer, > and my soft
Re[2]: [sqlite] To whom to inform on a bug?
sorry :( >> version: >> 3.3.4 or 3.x >> >> System test: >> Win2003(NTFS) >> >> Script: >> select * from tResult where tex like '%ra%' >> result = 0 >> >> select tex from tResult where id = 3229 >> tex = "...Oracle..." >> >> Soft on broblem: >> sqlite3explorer, >> and my soft >> >> sample db: >> http://www.aidagw.com/files/dba.zip >> dhc> Thank you for the excellent information. dhc> Your query works correctly when run in the dhc> sqlite3 shell. Probably this is a bug in dhc> sqlite3explorer. dhc> -- dhc> D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] To whom to inform on a bug?
Kirill <[EMAIL PROTECTED]> wrote: > version: > 3.3.4 or 3.x > > System test: > Win2003(NTFS) > > Script: > select * from tResult where tex like '%ra%' > result = 0 > > select tex from tResult where id = 3229 > tex = "...Oracle..." > > Soft on broblem: > sqlite3explorer, > and my soft > > sample db: > http://www.aidagw.com/files/dba.zip > Thank you for the excellent information. Your query works correctly when run in the sqlite3 shell. Probably this is a bug in sqlite3explorer. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re[2]: [sqlite] To whom to inform on a bug?
version: 3.3.4 or 3.x System test: Win2003(NTFS) Script: select * from tResult where tex like '%ra%' result = 0 select tex from tResult where id = 3229 tex = "...Oracle..." Soft on broblem: sqlite3explorer, and my soft sample db: http://www.aidagw.com/files/dba.zip dhc> http://www.sqlite.org/cvstrac/tktnew dhc> Hints: Unless you provide more information that you have dhc> shown above, your ticket will likely be ignored. The dhc> following kinds of information will help us to isolate dhc> and fix the problem: dhc>* What version of SQLite you are running. dhc>* What operating system you are using. dhc>* Did you build it yourself or used a precompiled dhc> binary. dhc>* What language bindings you are using. dhc>* The database schema. dhc>* The specific SQL statement that fails. dhc>* Can you reproduce the problem using the command-line dhc> shell or does it only appear in your program? dhc>* Include a reproducible script that demonstrates dhc> the problem. dhc>* If appropriate, attach a ZIP archive of your database dhc> file to the ticket. dhc> -- dhc> D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] To whom to inform on a bug?
Kirill <[EMAIL PROTECTED]> wrote: > hello > > select * from tResult where tex like '%ra%' > result = 0 > > select tex from tResult where id = 3229 > tex = "...Oracle..." > http://www.sqlite.org/cvstrac/tktnew Hints: Unless you provide more information that you have shown above, your ticket will likely be ignored. The following kinds of information will help us to isolate and fix the problem: * What version of SQLite you are running. * What operating system you are using. * Did you build it yourself or used a precompiled binary. * What language bindings you are using. * The database schema. * The specific SQL statement that fails. * Can you reproduce the problem using the command-line shell or does it only appear in your program? * Include a reproducible script that demonstrates the problem. * If appropriate, attach a ZIP archive of your database file to the ticket. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] To whom to inform on a bug?
hello select * from tResult where tex like '%ra%' result = 0 select tex from tResult where id = 3229 tex = "...Oracle..." bag? -- Kirill
Re: [sqlite] :memory: and sessions with PHP
"Robert Foster" <[EMAIL PROTECTED]> wrote: > PHP Sessions are similar to session in ASP, ASP.Net, etc. Objects within > the session are serialised into a stream and stored in the specified storage > medium. By Default, PHP stores sessions in a file in the /tmp directory, > identified by a unique filename that is stored in a cookie on the browser if > it is supported, or encoded onto the URL if not. > > You can also write custom session handlers, allowing you to store the > session anywhere else including a database. There is some documentation on > the Zend.com site for using the Session api, but it's simply a matter of > writing some functions with specific names, and hooking them in via the php > configuration. > > As far as storing a memory database, you would need to somehow grab a handle > to the memory location, keep the database alive between session hits, and > then re-attach to it. Alternatively you could create some kind of database > serialisation method and serialise the database to the session. It would, > however, be a lot more efficient to simply create a file-based database in > the first place and re-open it every time a page is called. > Based on your description above, I would recommend the following for a session database: * Create a file database with some unique name in /tmp * Open the database for each hit, but immediately set PRAGMA synchronous=OFF; With PRAMGA synchronous=OFF, all of your "disk I/O" is really just going into your operating systems disk cache - very little if any of it is actually going to disk. So you get most of the speed benefits of a :memory: database. The reason you do not normally set PRAGMA synchronous=OFF is because with synchronization off, you run a very serious risk of database corruption following an OS crash or power failure. But for a session database, you don't care. If the OS crashes or the power fails, you've lost the session anyway. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] :memory: and sessions with PHP
Comments below... Robert Foster General Manager Mountain Visions P/L http://mountainvisions.com.au -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: Wednesday, 15 February 2006 8:38 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] :memory: and sessions with PHP >> You can also write custom session handlers, allowing you to store the >> session anywhere else including a database. There is some >> documentation on the Zend.com site for using the Session api, but it's >> simply a matter of writing some functions with specific names, and >> hooking them in via the php configuration. >> >> >> >> Serialised is spelt with an 's', not a 'z' (I'm Australian) > > >So is zend.com australian for 'send.com'? ;) Probably the other way around :P > > > >but seriously, if you run php as fastcgi could you keep your session information in memory? Probably, I haven't tried... (of course, if you stored the session info in a :memory: database, and then somehow kept the database alive across the session...) Relevant articles for an off topic post... http://www.zend.com/zend/spotlight/code-gallery-wade8.php http://www.zend.com/manual/ref.session.php Now we're way off the beaten track and calling for the search parties...
Re: [sqlite] :memory: and sessions with PHP
> You can also write custom session handlers, allowing you to store the > session anywhere else including a database. There is some documentation on > the Zend.com site for using the Session api, but it's simply a matter of > writing some functions with specific names, and hooking them in via the php > configuration. > > > > Serialised is spelt with an 's', not a 'z' (I'm Australian) So is zend.com australian for 'send.com'? ;) but seriously, if you run php as fastcgi could you keep your session information in memory?
RE: [sqlite] :memory: and sessions with PHP
PHP Sessions are similar to session in ASP, ASP.Net, etc. Objects within the session are serialised into a stream and stored in the specified storage medium. By Default, PHP stores sessions in a file in the /tmp directory, identified by a unique filename that is stored in a cookie on the browser if it is supported, or encoded onto the URL if not. You can also write custom session handlers, allowing you to store the session anywhere else including a database. There is some documentation on the Zend.com site for using the Session api, but it's simply a matter of writing some functions with specific names, and hooking them in via the php configuration. As far as storing a memory database, you would need to somehow grab a handle to the memory location, keep the database alive between session hits, and then re-attach to it. Alternatively you could create some kind of database serialisation method and serialise the database to the session. It would, however, be a lot more efficient to simply create a file-based database in the first place and re-open it every time a page is called. Hope this helps, Robert Foster General Manager Mountain Visions P/L http://mountainvisions.com.au Serialised is spelt with an 's', not a 'z' (I'm Australian) -Original Message- From: Kervin L. Pierre [mailto:[EMAIL PROTECTED] Sent: Wednesday, 15 February 2006 7:01 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] :memory: and sessions with PHP Hello, I think the problem is that PHP uses a file-based session serialization. Therefore anything that cannot be saved to a file and returned ( eg. you can't do this with file handles, etc. ) cannot be saved in session scope in PHP as it is implemented by default. There is the 'mm' extension ( search for reference on http://us3.php.net/session ) that is suppose to fix this, I've heard. Also, there is word that there will be memory based session in future versions PHP engine by default. I have never used 'mm'. So, your problem is that you have no place to put your SQLite handle after a script has finished executing, so that the next instance of the script can get it. PHP has no such scope by default. Best Regards, Kervin CrazyChris wrote: > We may be at crossed paths... I'm wanting to save the :memory: > database to the session, not the other way round, so that when the 2nd > page loads, the > :memory: database can be recreated and available as it was on the last > page load. The advantage is that after some time, the session is > deleted automatically by the server and the database goes with it, so > short term, high-intensity data can be stored and queried quickly in > :memory: and the add/edits remain through the entire user experience. > An alternative is to use a file based database per user, but this > would require a tidy-up routine to be manually coded, and makes the code less portable. > > An alternative is to create the :memory: database and populate it from > session data each time, then save back to session on script close. Not > as swift or elegant, but if it's the only way then that may be that! > > > > --- > > >>Hi there, >> >>I have a need to create a :memory: sqlite database, but save it into >>the user session (PHP) but can't see a way to access the data to save. >>Looking for a sqlite version of serialize() I guess. >> >>Has anyone managed to do this? Is it even possible? >> >>Wanting to be able to maintain a large chunk of data across a users > > session > >>on a website, and the array's are getting tedious to manage and search >>through! > > > The PHP session information has to be persistent, so it's not going to > be easy to use a :memory: database. There is lots of information > about how to save session information to a database, though, on the > PHP web site. I haven't looked at it in a couple of years, but I'd > guess that you'll get some good pointers if you look at the > documentation for session_set_save_handler(). > > Also, IIRC, PHP provides functions to do serialization. You won't > need them if you go the session_set_save_handler() route, but if you > want to serialize data yourself, those functions should be available. > > Derrell > >
[sqlite] Update to Object Builder announcement
I light of Jay's comments about being unclear what Object Builder does, I've added an additional page to cover that very concept at http://www.ceamus.com/objbuilder/what.html Anyone with more questions, feel free to hit me with them, on list or off. And definitely feel free to use this code in your own application. That's why I've released it. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] :memory: and sessions with PHP
Hello, I think the problem is that PHP uses a file-based session serialization. Therefore anything that cannot be saved to a file and returned ( eg. you can't do this with file handles, etc. ) cannot be saved in session scope in PHP as it is implemented by default. There is the 'mm' extension ( search for reference on http://us3.php.net/session ) that is suppose to fix this, I've heard. Also, there is word that there will be memory based session in future versions PHP engine by default. I have never used 'mm'. So, your problem is that you have no place to put your SQLite handle after a script has finished executing, so that the next instance of the script can get it. PHP has no such scope by default. Best Regards, Kervin CrazyChris wrote: We may be at crossed paths... I'm wanting to save the :memory: database to the session, not the other way round, so that when the 2nd page loads, the :memory: database can be recreated and available as it was on the last page load. The advantage is that after some time, the session is deleted automatically by the server and the database goes with it, so short term, high-intensity data can be stored and queried quickly in :memory: and the add/edits remain through the entire user experience. An alternative is to use a file based database per user, but this would require a tidy-up routine to be manually coded, and makes the code less portable. An alternative is to create the :memory: database and populate it from session data each time, then save back to session on script close. Not as swift or elegant, but if it's the only way then that may be that! --- Hi there, I have a need to create a :memory: sqlite database, but save it into the user session (PHP) but can't see a way to access the data to save. Looking for a sqlite version of serialize() I guess. Has anyone managed to do this? Is it even possible? Wanting to be able to maintain a large chunk of data across a users session on a website, and the array's are getting tedious to manage and search through! The PHP session information has to be persistent, so it's not going to be easy to use a :memory: database. There is lots of information about how to save session information to a database, though, on the PHP web site. I haven't looked at it in a couple of years, but I'd guess that you'll get some good pointers if you look at the documentation for session_set_save_handler(). Also, IIRC, PHP provides functions to do serialization. You won't need them if you go the session_set_save_handler() route, but if you want to serialize data yourself, those functions should be available. Derrell
Re: [sqlite] :memory: and sessions with PHP
On Wed, Feb 15, 2006 at 08:31:17AM +1300, CrazyChris wrote: > We may be at crossed paths... I'm wanting to save the :memory: database to > the session, not the other way round, so that when the 2nd page loads, the > :memory: database can be recreated and available as it was on the last page > load. What is a "session"? I assume that's some special PHP or Apache feature, but which? How is a "session" implemented really? Inter-process shared memory on Unix? Process-wide memory in a multi-threaded Apache 2.x build? What? Having an in-memory database (or in-memory anything) persist across multiple page hits in a web server is certainly feasible, but for anyone not using the exact same tools as you, you probably want to give more background - basically, provide a mapping between tool-specific jargon like "session", and more widely understood programming terms. > The advantage is that after some time, the session is deleted > automatically by the server and the database goes with it, so short term, > high-intensity data can be stored and queried quickly in :memory: and the > add/edits remain through the entire user experience. I don't see why that is any real advantage. Periodically purging an in-memory database of old values should be rather trivial. > An alternative is to create the :memory: database and populate it from > session data each time, then save back to session on script close. That sounds like a bizarre hack. (But then I don't know what your sessions really are, nor do I really understand your application requirements, so perhaps I am missing something.) -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
RE: [sqlite] :memory: and sessions with PHP
We may be at crossed paths... I'm wanting to save the :memory: database to the session, not the other way round, so that when the 2nd page loads, the :memory: database can be recreated and available as it was on the last page load. The advantage is that after some time, the session is deleted automatically by the server and the database goes with it, so short term, high-intensity data can be stored and queried quickly in :memory: and the add/edits remain through the entire user experience. An alternative is to use a file based database per user, but this would require a tidy-up routine to be manually coded, and makes the code less portable. An alternative is to create the :memory: database and populate it from session data each time, then save back to session on script close. Not as swift or elegant, but if it's the only way then that may be that! --- > Hi there, > > I have a need to create a :memory: sqlite database, but save it into the > user session (PHP) but can't see a way to access the data to save. Looking > for a sqlite version of serialize() I guess. > > Has anyone managed to do this? Is it even possible? > > Wanting to be able to maintain a large chunk of data across a users session > on a website, and the array's are getting tedious to manage and search > through! The PHP session information has to be persistent, so it's not going to be easy to use a :memory: database. There is lots of information about how to save session information to a database, though, on the PHP web site. I haven't looked at it in a couple of years, but I'd guess that you'll get some good pointers if you look at the documentation for session_set_save_handler(). Also, IIRC, PHP provides functions to do serialization. You won't need them if you go the session_set_save_handler() route, but if you want to serialize data yourself, those functions should be available. Derrell
Re: [sqlite] Simple ATTACH/memory database question
[EMAIL PROTECTED] wrote: Is this right? You are doing a separate CREATE TABLE for each message? That's going to be the source of your problem. I think Yes we are. I tried a message/row design early on but I could not count on it being behaving well. The problem was fitting Outlook's internal API ( MAPI ) on a SQL database. Every message has a one-to-many relationship with properties, which have a one-to-many with values. Combined with some of the reporting requirements of the API, I thought that a simple message/table would work, at least for version 1. I was hoping that the CREATE hit would not be significant since it is only occured when a message is created. But there doesn't seem to be much we can do about the reads. PS. One approach seems to be to replace the default 'sqlite3OsMalloc' with a pool based malloc for speed. It would be nice if this was a function pointer instead of a define. that way we could replace the function with- out having to modify SQLite source. Best Regards, Kervin
Re: [sqlite] Simple ATTACH/memory database question
"Kervin L. Pierre" <[EMAIL PROTECTED]> wrote: > > How did you implement full text indexing? > I though sqlite did not support full text > index, am I wrong? SQLite does not have *automatic* full-text indexing. But it is easy enough to index the full text of an email message yourself in application code. I just counted 32 lines of code in my email reader used to implement full-text indexing. It is not hard. > Our design has an SQLite table for each > message. Is this right? You are doing a separate CREATE TABLE for each message? That's going to be the source of your problem. I think you have much better success if you put each message in its own row of a single table. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] :memory: databases
Hi, Is it possible to use an :memory: database with a preallocated memory area ? The locking mechanism could still use files, no problem. Or no locking (and I provide the locking mechanism). I have an application with a 5GB shared memory (with the SHM_HUGETLB flag - Linux) and I do all de management of this memory. But it would be much more simple to use sqlite to do this. Besides, there are more shm's in the same machine being used as cache in front of a database. Any sugestions, or how it could be implemented in sqlite ? []'s Mateus Inssa
Re: [sqlite] Simple ATTACH/memory database question
[EMAIL PROTECTED] wrote: I'm reading and replying to this message using an SQLite-backed email client See That's very interesting... All incoming and archival emails are stored as BLOBs in a table. The full text of messages is indexed. It is all very fast and I haven't had to do anything special to make it so. How did you implement full text indexing? I though sqlite did not support full text index, am I wrong? Of course, all this assumes a modern workstation. Perhaps your MUA is designed to work on a handheld with cheapest (read: slowest) flash memory available and a 50 MHz ARM processor or something? Lol, I wish. It's the direct opposite. The project is essentially an alternative to PST in Microsoft Outlook :) http://openconnector.org/ We are stuck with Outlook's internal API. Our design has an SQLite table for each message. Message objects are transacted. Hence the plan is to mirror the message SQLite table in an in memory database until the SaveChanges() API is called, which would copy the changed rows to disk. Currently message saves and retieval is very slow because Outlook makes about 250 calls to our 'sqlite-backed' generic property retrival function when it opens a single message. So that SQLite-based function, GetProps(), needs to be very, very, fast. Any ideas? Best Regards, Kervin
Re: [sqlite] Simple ATTACH/memory database question
Andrew Piskorski <[EMAIL PROTECTED]> wrote: > On Tue, Feb 14, 2006 at 10:16:14AM -0500, Kervin L. Pierre wrote: > > > We are getting ready to start using in- memory database to cache > > sqlite reads/writes in effort to improve speed. For background, > > > Since we read much more than we write, reading from memory should > > improve. > > Shouldn't SQLite's built-in cache for disk-backed databases already > accomplished that for reads? Have you tested the actual performance? > I'm reading and replying to this message using an SQLite-backed email client See http://www.sqlite.org/cvstrac/wiki?p=ExperimentalMailUserAgent. All incoming and archival emails are stored as BLOBs in a table. The full text of messages is indexed. It is all very fast and I haven't had to do anything special to make it so. Of course, all this assumes a modern workstation. Perhaps your MUA is designed to work on a handheld with cheapest (read: slowest) flash memory available and a 50 MHz ARM processor or something? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Simple ATTACH/memory database question
> We are getting ready to start using in- > memory database to cache sqlite reads/writes > in effort to improve speed. For background, > the application is an email client. > > The way we envision the caching working is > that we have a mirror copy of any table in > use in memory. The tables are small and not > many are in use at any time, so we are ok with > space. Since we read much more than we > write, reading from memory should improve. > Also, this should simplify our transaction > model for transacted objects. > > Has anyone done this? Comments? It sounds like overkill to me too. In memory databases are unique per connection. If you have multiple processes they won't be able to share the same database. You could share the same database amoung threads or a database server process. Using the 90/10 rule this last 10% of performance is going to cost you 90% of the effort.
Re: [sqlite] Simple ATTACH/memory database question
On Tue, Feb 14, 2006 at 10:16:14AM -0500, Kervin L. Pierre wrote: > We are getting ready to start using in- memory database to cache > sqlite reads/writes in effort to improve speed. For background, > Since we read much more than we write, reading from memory should > improve. Shouldn't SQLite's built-in cache for disk-backed databases already accomplished that for reads? Have you tested the actual performance? -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
[sqlite] Simple ATTACH/memory database question
Hello, We are getting ready to start using in- memory database to cache sqlite reads/writes in effort to improve speed. For background, the application is an email client. The way we envision the caching working is that we have a mirror copy of any table in use in memory. The tables are small and not many are in use at any time, so we are ok with space. Since we read much more than we write, reading from memory should improve. Also, this should simplify our transaction model for transacted objects. Has anyone done this? Comments? Best Regards, Kervin
Re: [sqlite] Useful SQLite tool?
> I think that I've previously mentioned my Object Builder code on this > list. If I haven't, you might want to take a look at > http://www.ceamus.com/objbuilder/ > > Object Builder is a tool to automate the creation of Active Record classes > for reading and writing data from databases. The included examples are > specific to SQLite 3 using C++. I've also included a more thorough > example in PHP that generates a significantly large portion of the > application automatically. Hopefully these examples will be useful to > people reading this list. Everything I do with the SQL engine is based on sets. A query returns a set (sometimes of 1 row), etc. I use STL to implement the sets. Since my result sets are always small I keep them in memory. Your documentation doesn't explain the design philosophy, other than referencing something else that I'm not familiar with either. Perhaps a little more generalized explanation would help people decide if they want to use it or not?
[sqlite] Useful SQLite tool?
I think that I've previously mentioned my Object Builder code on this list. If I haven't, you might want to take a look at http://www.ceamus.com/objbuilder/ Object Builder is a tool to automate the creation of Active Record classes for reading and writing data from databases. The included examples are specific to SQLite 3 using C++. I've also included a more thorough example in PHP that generates a significantly large portion of the application automatically. Hopefully these examples will be useful to people reading this list. Clay Dowling -- Simple Content Management http://www.ceamus.com
