[sqlite] Freeware code generator now supports SQLite
MyGeneration software has added SQLite support to thier freeware template based code generator. http://www.mygenerationsoftware.com/ MyGeneration has a generic meta-data API that supports 9 databases with more on the way. The meta-data (which includes columns, indeces, foreign keys, etc) is made available in the templates which are written in JScript, C#, VBScript, or VB.Net. among other things, you can generate a middle tier, SQL scripts, documentation, export data, etc. I personally added the support for SQLite to MyGeneration, so if anyone out there finds it useful, please send me some feedback. I havn't had much of a test group. In fact, I'm the only guy that's tested it so far. ;) I also wrote a short article about collecting meta data from SQLite which can be found on my blog: http://justingreenwood.blogspot.com/2004/10/sqlite-100-free-database.html I love SQLite! I'm going to be using it in my next project. Thanks to all the people that are maintaining it!
RE: [sqlite] PHP5 && SQLite3
At 8:08 AM -0700 10/19/04, Keith Herold wrote: Out of curiosity, how hard would it be to build a translation layer? I suppose it's easier to keep both versions around, and upgrade when necessary, but as a technical problem, it's at least interesting? Only downwardly, of course, so there would be no expectation that 2.X could use 3.X, just the reverse. It had sounded as if only the file format was the problem; is it more than that? I'm not pushing, in any sense, just wondering if anyone has looked at this issue with an eye to resolving it. --Keith This isn't specific to PHP, but ... I am building a translation layer right now, in Perl. You should be able to, using my library, easily convert SQLite2 databases to SQLite3, or the other way, or between SQLite and various other database products, within the next 2-3 weeks. The library does the standard "scan and write" approach, analagous to a "dump and restore" but not so crude (it translates and emulates SQL too). Go to http://search.cpan.org/ ; look up 'Rosetta' and 'SQL::Routine'. They are pre-alpha, but I should have the functionality you need within 3 more releases of each. My library is also being ported to Parrot asap, so that all the languages which have compilers targeting Parrot (I think someone's even doing PHP) can use it. -- Darren Duncan
Re: [sqlite] SQLite files locking
On Oct 19, 2004, at 2:08 PM, js171864 wrote: The problem: How to exclusively lock SQLite file on opening? I want to prevent application from being opened many times with the same file, but instead - display a warning message. There's only internal sqlite3OsLock() but I want to override all these locks/unlocks made by 'pager'. You'll ned to do it externally unless you really want to spend a bunch of time modifying SQLite's locking code. If you want to exclude just your application from opening multiple copies, then you could create a .lock file or use fcntl() to lock the first byte of the file (assuming Unix) prior to ever passing control to SQLite. SQLite uses a range of bytes near the 2GB mark to perform its locking magic. If you were to lock the first byte for your own exclusivity, that should not be a problem. If you are looking to exclude anything from opening the file-- say, sqlite command line tool-- that is a much harder problem. Probably the easiest way would be to change the file format slightly. b.bum
[sqlite] SQLite files locking
Hello, The problem: How to exclusively lock SQLite file on opening? I want to prevent application from being opened many times with the same file, but instead - display a warning message. There's only internal sqlite3OsLock() but I want to override all these locks/unlocks made by 'pager'. -- regards / pozdrawiam, Jaroslaw Staniek / OpenOffice Polska Kexi Project: http://www.kexi-project.org, http://koffice.kde.org/kexi KDElibs/Windows: http://wiki.kde.org/tiki-index.php?page=KDElibs+for+win32
Re: [sqlite] SQLITE_BUSY returned without busy handler called
On Tue, Oct 19, 2004 at 11:58:48AM -0700, Kevin Schmeichel wrote: > > Really, what I was concerned about was getting > SQLITE_BUSY from sqlite_finalize - if I try and call > sqlite_finalize again, I get SQLITE_MISUSE. I haven't > gone through the code in enough detail to determine > what the effects of an incomplete sqlite_finalize > might be (possible memory leaks?). This confused me as well, but the documentation can clear things up: The sqlite3_finalize() function is called to delete a prepared SQL statement obtained by a previous call to sqlite3_prepare() or sqlite3_prepare16(). If the statement was executed successfully, or not executed at all, then SQLITE_OK is returned. If execution of the statement failed then an error code is returned. So if your sqlite3_step() resulted in SQLITE_BUSY, sqlite3_finalize() will also return SQLITE_BUSY. sqlite3_reset() behaves the same way, although the expected return values are not documented. Cheers, Matt
Re: [sqlite] SQLITE_BUSY returned without busy handler called
What if transactions only executed if they were able to get an EXCLUSIVE lock? Wouldn't that solve the problem you described? Granted, this would be overkill for transactions that only did SELECTs, but... --- "b.bum" <[EMAIL PROTECTED]> wrote: > > Not in a transaction. > > The purpose of a transaction is to group together a > series of > operations for both efficiency's sake and to ensure > integrity. > ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com
Re: [sqlite] SQLITE_BUSY returned without busy handler called
On Oct 19, 2004, at 11:58 AM, Kevin Schmeichel wrote: Maybe I'm a bit confused - wouldn't (A) first give up it's SHARED lock before trying to get the RESERVED lock, thus giving (B) a chance to get the EXCLUSIVE lock? Not in a transaction. The purpose of a transaction is to group together a series of operations for both efficiency's sake and to ensure integrity. For example, you might do a... BEGIN SELECT SELECT SELECT INSERT INSERT UPDATE END Where the INSERTs and UPDATE are dependent upon the results of the SELECTs. By putting everything into a transaction, there cannot be a potentially catastrophic state change between the SELECTs and the INSERTs/UPDATE. If someone else were allowed to grab an EXCLUSIVE between the SELECT and INSERT in the above example, then that EXCLUSIVE might be used to change the database in a fashion where the results of the SELECTs change, thus breaking assumptions made by INSERT or UPDATE. I guess I should really upgrade to sqlite3, which uses PENDING locks to try and eliminate writer starvation. Even with PENDING, you can still write code to cause a deadlock as I originally described. b.bum
Re: [sqlite] SQLITE_BUSY returned without busy handler called
Maybe I'm a bit confused - wouldn't (A) first give up it's SHARED lock before trying to get the RESERVED lock, thus giving (B) a chance to get the EXCLUSIVE lock? Really, what I was concerned about was getting SQLITE_BUSY from sqlite_finalize - if I try and call sqlite_finalize again, I get SQLITE_MISUSE. I haven't gone through the code in enough detail to determine what the effects of an incomplete sqlite_finalize might be (possible memory leaks?). I guess I should really upgrade to sqlite3, which uses PENDING locks to try and eliminate writer starvation. Kevin --- "b.bum" <[EMAIL PROTECTED]> wrote: > On Oct 18, 2004, at 12:01 PM, Kevin Schmeichel > wrote: > > What are some examples of circumstances when > waiting > > and retrying a lock won't help? > > Consider two processes, A and B. Lock state is in > [BRACKETS]. > > (A) does a BEGIN TRANSACTION > > (B) does a BEGIN TRANSACTION > > (A) does a SELECT [SHARED] > > (B) does an INSERT or UPDATE [RESERVED] > > At this point, (B) will be unable to END TRANSACTION > successfully until > (A) does an END TRANSACTION. Now, consider: > > (A) does an INSERT or UPDATE > > Now, (A) will attempt to upgrade its lock to > (RESERVED). It will fail > because (B) already has a RESERVED lock. At the > same time, (B) cannot > end the transaction because it will be unable to > transition the > RESERVED lock to the EXCLUSIVE lock required for > writing. If (A) and > (B) decide to loop waiting for a lock at this point, > they will deadlock > waiting on the other. The only way to resolve is > for one or the other > process to ABORT or for (A) to do an END TRANSACTION > (which will work > because the INSERT/UPDATE attempt on (A) failed to > advance the lock > state). > > b.bum > > ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com
RE: [sqlite] PHP5 && SQLite3
Ummm... Sure, but not really my question, either. Not questioning the wisdom, just the technical issues. Perhaps someone can point me to the relevant archives so I can look? --Keith -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 10:14 AM To: [EMAIL PROTECTED] Subject: RE: [sqlite] PHP5 && SQLite3 On Tue, 19 Oct 2004, Keith Herold wrote: >Out of curiosity, how hard would it be to build a translation layer? I >suppose it's easier to keep both versions around, and upgrade when >necessary, but as a technical problem, it's at least interesting? Only >downwardly, of course, so there would be no expectation that 2.X could >use 3.X, just the reverse. It had sounded as if only the file format >was the problem; is it more than that? > >I'm not pushing, in any sense, just wondering if anyone has looked at >this issue with an eye to resolving it. There's nothing to resolve. In the discussion of SQLite3 requirements earlier in the year, one of the issues that came up was database migration, and the concensus on the list at the time was to forgo automatic migration to keep the library simple. Migration can be done very simply using the sqlite/sqlite3 shells and the .dump command. This was a conscious deliberate decision. > >--Keith > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
RE: [sqlite] PHP5 && SQLite3
On Tue, 19 Oct 2004, Keith Herold wrote: >Out of curiosity, how hard would it be to build a translation layer? I >suppose it's easier to keep both versions around, and upgrade when >necessary, but as a technical problem, it's at least interesting? Only >downwardly, of course, so there would be no expectation that 2.X could use >3.X, just the reverse. It had sounded as if only the file format was the >problem; is it more than that? > >I'm not pushing, in any sense, just wondering if anyone has looked at this >issue with an eye to resolving it. There's nothing to resolve. In the discussion of SQLite3 requirements earlier in the year, one of the issues that came up was database migration, and the concensus on the list at the time was to forgo automatic migration to keep the library simple. Migration can be done very simply using the sqlite/sqlite3 shells and the .dump command. This was a conscious deliberate decision. > >--Keith > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
RE: [sqlite] PHP5 && SQLite3
I wasn't saying SQLite *should* do it, I was wondering how technically difficult such a layer might be. For example, while the API's are different, if the underlying file format is the real problem, it would still be nice to leverage SQLite3's (S3) better concurrency. Clearly, this is a bit of problem, since it would required hacking out the disk read components from S3 and replacing them with a generic interface. Having not looked at the source, I can't imagine this is trivial, since there is no doubt DHP has heavily optimized the format and the reading/writing of it but still... This is one of those "Can it be done, and at what cost", not a "It needs to be done, and damn the cost". --Keith -Original Message- From: Stober, Mel [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 9:01 AM To: '[EMAIL PROTECTED]' Subject: RE: [sqlite] PHP5 && SQLite3 using PHP doesn't help in pure C++ desktop applications that do not run in web-server entironments. Mel Stober -Original Message- From: Craig Morrison [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 10:57 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] PHP5 && SQLite3 Keith Herold wrote: > Out of curiosity, how hard would it be to build a translation layer? > I suppose it's easier to keep both versions around, and upgrade when > necessary, but as a technical problem, it's at least interesting? > Only downwardly, of course, so there would be no expectation that 2.X > could use 3.X, just the reverse. It had sounded as if only the file > format was the problem; is it more than that? > > I'm not pushing, in any sense, just wondering if anyone has looked at > this issue with an eye to resolving it. The PHP library IS the translation layer. The crux of the issue is that the formats are NOT compatible, nor are the API's. What Dr. Hipp suggested is the best possible course. Link with both API's which _DO_ have separate naming conventions making it possible to support both formats concurrently. All new databases follow Sqlite3, legacy follow Sqlite2. There are signatures in the database files to distinguish between the two. Use the tools that are available to add the layer of abstraction necessary. Don't force that on SQlite itself. Its all summed up in one sentence, which is the first one that appears at sqlite.org: "SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine." The focus is there, anything beyond that is up to the implementation. -- Craig Morrison =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= http://www.mtsprofessional.com/ A Win32 email server that works for You.
RE: [sqlite] PHP5 && SQLite3
using PHP doesn't help in pure C++ desktop applications that do not run in web-server entironments. Mel Stober -Original Message- From: Craig Morrison [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 10:57 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] PHP5 && SQLite3 Keith Herold wrote: > Out of curiosity, how hard would it be to build a translation layer? I > suppose it's easier to keep both versions around, and upgrade when > necessary, but as a technical problem, it's at least interesting? Only > downwardly, of course, so there would be no expectation that 2.X could use > 3.X, just the reverse. It had sounded as if only the file format was the > problem; is it more than that? > > I'm not pushing, in any sense, just wondering if anyone has looked at this > issue with an eye to resolving it. The PHP library IS the translation layer. The crux of the issue is that the formats are NOT compatible, nor are the API's. What Dr. Hipp suggested is the best possible course. Link with both API's which _DO_ have separate naming conventions making it possible to support both formats concurrently. All new databases follow Sqlite3, legacy follow Sqlite2. There are signatures in the database files to distinguish between the two. Use the tools that are available to add the layer of abstraction necessary. Don't force that on SQlite itself. Its all summed up in one sentence, which is the first one that appears at sqlite.org: "SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine." The focus is there, anything beyond that is up to the implementation. -- Craig Morrison =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= http://www.mtsprofessional.com/ A Win32 email server that works for You.
Re: [sqlite] PHP5 && SQLite3
Keith Herold wrote: Out of curiosity, how hard would it be to build a translation layer? I suppose it's easier to keep both versions around, and upgrade when necessary, but as a technical problem, it's at least interesting? Only downwardly, of course, so there would be no expectation that 2.X could use 3.X, just the reverse. It had sounded as if only the file format was the problem; is it more than that? I'm not pushing, in any sense, just wondering if anyone has looked at this issue with an eye to resolving it. The PHP library IS the translation layer. The crux of the issue is that the formats are NOT compatible, nor are the API's. What Dr. Hipp suggested is the best possible course. Link with both API's which _DO_ have separate naming conventions making it possible to support both formats concurrently. All new databases follow Sqlite3, legacy follow Sqlite2. There are signatures in the database files to distinguish between the two. Use the tools that are available to add the layer of abstraction necessary. Don't force that on SQlite itself. Its all summed up in one sentence, which is the first one that appears at sqlite.org: "SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine." The focus is there, anything beyond that is up to the implementation. -- Craig Morrison =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= http://www.mtsprofessional.com/ A Win32 email server that works for You.
Re: [sqlite] PHP5 && SQLite3
I dont find any distrib of php_sqlite3 on sqlite website that's why i ask if php will include it rapidly. Is there a windows library DLL (version 3 of course) which work with php5 ? (then just include it in php.ini ok) (The sqlitedll-3_0_8.zip don't contain any valid php library) For compatibily i can't response beacause i juste test version 2 at the moment ... - Out of curiosity, how hard would it be to build a translation layer? I suppose it's easier to keep both versions around, and upgrade when necessary, but as a technical problem, it's at least interesting? Only downwardly, of course, so there would be no expectation that 2.X could use 3.X, just the reverse. It had sounded as if only the file format was the problem; is it more than that? I'm not pushing, in any sense, just wondering if anyone has looked at this issue with an eye to resolving it. --Keith
RE: [sqlite] PHP5 && SQLite3
Out of curiosity, how hard would it be to build a translation layer? I suppose it's easier to keep both versions around, and upgrade when necessary, but as a technical problem, it's at least interesting? Only downwardly, of course, so there would be no expectation that 2.X could use 3.X, just the reverse. It had sounded as if only the file format was the problem; is it more than that? I'm not pushing, in any sense, just wondering if anyone has looked at this issue with an eye to resolving it. --Keith ** - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads ** -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 6:40 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] PHP5 && SQLite3 julien wrote: > I put this question because compatibility between SQLite2 and SQLite3 > seems poor. > Poor? More like non-existent. SQLite2 and SQLite3 have a very similar design but both the interface and the underlying file format are quite different and imcompatible. Derrell Lipman wrote: > > The plan _is_ to integrate SQLite3. There are two of us who may > integrate it, depending on availability and need. There's no schedule > for when it will be completed, but it is reasonably high on some of > our priority lists. > SQLite3 is designed so that it can be statically linked into the same executable as SQLite2. One plan for PHP might be to link in both libraries. When opening an existing database, check the header and use either the SQLite2 or the SQLite3 library depending on what kind of database it is. Create new databases as SQLite3. The scripting interface would be the same and the user would never know the difference. In this way, you preserve backwards compatibility with existing SQLite2 database files while at the same time taking advantage of new SQLite3 features. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] PHP5 && SQLite3
"julien" <[EMAIL PROTECTED]> writes: > does anyone know if PHP will integrate SQLite3 per default in version 5.1 ? > Actually PHP 5.0.2 include only SQLite2. The plan _is_ to integrate SQLite3. There are two of us who may integrate it, depending on availability and need. There's no schedule for when it will be completed, but it is reasonably high on some of our priority lists. Derrell
Re: [sqlite] Info for DEFERRED, IMMEDIATE, and EXCLUSIVE transactions
Thanks a lot Dr. Hipp. Regards, -- Tito On 19 oct 2004, at 13:51, D. Richard Hipp wrote: Tito Ciuro wrote: Version 3.0.8 adds support for DEFERRED, IMMEDIATE, and EXCLUSIVE transactions. Where can I find more info about that? http://www.sqlite.org/lang.html#transaction
Re: [sqlite] PHP5 && SQLite3
julien wrote: does anyone know if PHP will integrate SQLite3 per default in version 5.1 ? Actually PHP 5.0.2 include only SQLite2. That would be a question for the maintainers of PHP. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Info for DEFERRED, IMMEDIATE, and EXCLUSIVE transactions
Tito Ciuro wrote: Version 3.0.8 adds support for DEFERRED, IMMEDIATE, and EXCLUSIVE transactions. Where can I find more info about that? http://www.sqlite.org/lang.html#transaction -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Info for DEFERRED, IMMEDIATE, and EXCLUSIVE transactions
Hello, Version 3.0.8 adds support for DEFERRED, IMMEDIATE, and EXCLUSIVE transactions. Where can I find more info about that? Thanks, -- Tito