[sqlite] How to find out encoding for a table
Hello all. I have found that I can create a table and write data to that table as either UTF-8 or UTF-16 by using "PRAGMA encoding...", so that all of the field names and text data are written as the encoding type. I need to be able to query (discover) the encoding of a table when the encoding is unknown. How do I do this? TIA -brett -- try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com
Re: [sqlite] host parameters / bind variables - further workouts
On 26 Aug 2004, at 19:15, Darren Duncan wrote:
At 2:39 PM +0100 8/26/04, Matt Sergeant wrote:
I already support sqlite3's numeric placeholders via the standard DBI
API. Switching to non-numeric placeholders will be more complex (I'll
have to use a hash instead of an array to store the placeholders) but
quite doable.
In case I was giving off the wrong idea, I don't mean to lose support
for the positional parameters, but rather to support both posit/named
concurrently.
But yes, the ability to do this would be very powerful, but hopefully
very simple to implement:
...
my $sth = $dbh->prepare(
"SELECT * FROM bar ".
"WHERE baz = :yours OR foo = :mine OR zee = :yours" );
$sth->execute( { 'yours' => 3, 'mine' => 'hello' } );
...
$sth->execute( { 'yours' => 6, 'mine' => 'goodbye' } );
...
Yes. Should be possible - I'll have to switch from an array storage to
hash storage of the parameters, but that's not a huge deal.
For Richard's benefit though, I tested the currently documented: ":N:"
style parameters and I can't compile a SQL statement with those in,
which is a bit worrying (this is with sqlite 3.0.4).
Matt.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
Re: [sqlite] host parameters / bind variables - further workouts
Matt Sergeant wrote: For Richard's benefit though, I tested the currently documented: ":N:" style parameters and I can't compile a SQL statement with those in, which is a bit worrying (this is with sqlite 3.0.4). The ":N:" style variables were implemented briefly, but never in a released version. The latest in CVS supports ":AAA" (alphanumeric AAA with no closing colon) because that is what (I am told) is the SQL standard. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] host parameters / bind variables - further workouts
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 D. Richard Hipp wrote: | The ":N:" style variables were implemented briefly, but never in a | released version. The latest in CVS supports ":AAA" (alphanumeric AAA | with no closing colon) because that is what (I am told) is the | SQL standard. And what's the fate of "?NNN" (integer NNN)? -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBLzyGSIrOxc3jOmoRAk70AJ4uspNbYuxi8u324xuKHz//ZWStRgCfQQgN NiBgzUBAZpXaiAW65jHEW2Q= =Qk7n -END PGP SIGNATURE-
Re: [sqlite] host parameters / bind variables - further workouts
From: Michael Roth <[EMAIL PROTECTED]>
D. Richard Hipp wrote:
| The ":N:" style variables were implemented briefly, but never in a
| released version. The latest in CVS supports ":AAA" (alphanumeric AAA
| with no closing colon) because that is what (I am told) is the
| SQL standard.
And what's the fate of "?NNN" (integer NNN)?
Hi all,
I have modified the latest CVS version of SQLite3 to support positional
parameters ("?"), numbered parameters ("?nnn"), and named parameters
(":aaa"). It also allows all instances of a parameter that appear in an SQL
statement to be bound with a single_bind call.
I'm currently testing my changes and trying to prepare additional tests for
the test suite. So far everything works fine. Next I have to resolve the
issue reported on ticket #871, since I'm building under Windows with MinGW.
Right now I can't run the test suite.
I'll be busy with other matters today, but hope to have everything done, and
be ready to submit a patch to Richard this weekend.
Have a good day.
_
MSN® Calendar keeps you organized and takes the effort out of scheduling
get-togethers.
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines
Start enjoying all the benefits of MSN® Premium right now and get the
first two months FREE*.
Re: [sqlite] host parameters / bind variables - further workouts
Friday, August 27, 2004, 10:47:30 AM, Dennis wrote: > [...] Next I have to resolve the issue reported on ticket #871, > since I'm building under Windows with MinGW. Right now I can't run > the test suite. There is a simple workaround; at the end of src/test1.c use #if defined(OS_UNIX) && OS_UNIX Tcl_LinkVar(interp, "sqlite_temp_directory", (char*)&sqlite_temp_directory, TCL_LINK_STRING); #endif and everything will compile. I still have trouble running the tests (error 128 from msys at odd times that may be tcl subst related) but at least you can build testfixture and run some tests. e
Re: [sqlite] host parameters / bind variables - further workouts
Doug Currie wrote: Friday, August 27, 2004, 10:47:30 AM, Dennis wrote: [...] Next I have to resolve the issue reported on ticket #871, since I'm building under Windows with MinGW. Right now I can't run the test suite. There is a simple workaround; at the end of src/test1.c use #if defined(OS_UNIX) && OS_UNIX Tcl_LinkVar(interp, "sqlite_temp_directory", (char*)&sqlite_temp_directory, TCL_LINK_STRING); #endif and everything will compile. I still have trouble running the tests (error 128 from msys at odd times that may be tcl subst related) but at least you can build testfixture and run some tests. The correct fix, of course, is to add sqlite_temp_directory to os_win.c. I thought I had done that, but I guess it didn't make it into CVS. I'll fix it as soon as I can. In the meantime, there is always Knoppix -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] SQLite for large bulletin board systems?
Hello everyone. I am interested in creating a PHP/SQLite powered bulletin board system similar to phpBB. However, I have read that SQLite is best suited for applications that are mainly read-only (because it locks the database during writes). Do you think a SQLite powered bulletin board is a bad idea? How would I go about handling the case where two users are trying to write to the database simultaneously? -- Larry Kubin
Re: [sqlite] host parameters / bind variables - further workouts
Earlier I said (to Dennis and the list): > I still have trouble running the tests (error 128 from msys at odd > times that may be tcl subst related) but at least you can build > testfixture and run some tests. I have also reported privately to DRH a problem running tests bigrow-2.2 & bigrow-2.3 Since upgrading from gcc 3.4.0 to gcc 3.4.1 all of these problems seem to have gone away. e
Re: [sqlite] SQLite for large bulletin board systems?
At 3:45 PM -0500 8/27/04, Larry Kubin wrote: Hello everyone. I am interested in creating a PHP/SQLite powered bulletin board system similar to phpBB. However, I have read that SQLite is best suited for applications that are mainly read-only (because it locks the database during writes). Do you think a SQLite powered bulletin board is a bad idea? How would I go about handling the case where two users are trying to write to the database simultaneously? The appropriateness really depends on how busy your bulletin board will be. If it has hundreds or thousands of people *simultaneously* trying to post, then you may run into problems. Otherwise, for a typical website, such as with no more than a few dozen posts per minute (and most likely a lot less than that), then SQLite should be able to handle the BB fine. SQLite being fast in general should help. Most BB writes are inserts, also, with few-to-none updates or deletes. While the whole DB is locked, the locking period should be milliseconds short, so for typical usage no one should notice slowdowns. Of course, try it and see. -- Darren Duncan
Re: [sqlite] SQLite for large bulletin board systems?
Larry Kubin wrote: Hello everyone. I am interested in creating a PHP/SQLite powered bulletin board system similar to phpBB. However, I have read that SQLite is best suited for applications that are mainly read-only (because it locks the database during writes). Do you think a SQLite powered bulletin board is a bad idea? How would I go about handling the case where two users are trying to write to the database simultaneously? The CVSTrac system on www.sqlite.org is backed by an SQLite database (of course). Every single hit does a write to the database. It gets 20K hits/day from 2K distinct IPs and runs on the equivalent of a 150MHz machine with no problems at all. It could easily handle more traffic. On a faster machine, it could handle *lots* more traffic. I've run tests on a workstation where an SQLite-backed website was handling 10 to 20 hits per second (simulated load). Use the busy handler on SQLite so that if one thread is writing, all other threads simply wait their turn. The trick is not to linger of your writes. Decide what you want to write into the database, start the transaction, make your update, and commit. You can make a big change in 10 or 20 milliseconds. What you should avoid doing is starting the transaction, then doing a bunch of slow computations, then writing the results and committing. Compute the results first, before you start the transaction, so that your lock window is small. If you want to accumulate a lot of results over time and store them all atomically, write the results initially into a TEMP table. Then copy the TEMP table contents into the main database in a single (atomic) operation. Writing to a TEMP table does not lock the database. A good rule of thumb is that if your website is small enough that it can be run off of a single webserver and you do not need a load-sharing arrangement, then SQLite will probably meet your needs. If you website traffic gets to be so much that you are thinking about offloading the database onto a separate processor or splitting the load between two or more machines, then you should probably use a client/server database instead. The best design would be to make the application generic so that it could use either SQLite or a client/server database. Then smaller sites could use SQLite and take advantage of the reduce management and overhead it provides while larger sites could use a client/server database for scalability. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] How to find out encoding for a table
No one has answered this query yet. Please can someone help with it. Thanks. try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com EzTools Support wrote: Hello all. I have found that I can create a table and write data to that table as either UTF-8 or UTF-16 by using "PRAGMA encoding...", so that all of the field names and text data are written as the encoding type. I need to be able to query (discover) the encoding of a table when the encoding is unknown. How do I do this? TIA -brett
Re: [sqlite] How to find out encoding for a table
At 9:06 AM +1000 8/28/04, EzTools Support wrote: No one has answered this query yet. Please can someone help with it. Thanks. try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com EzTools Support wrote: Hello all. I have found that I can create a table and write data to that table as either UTF-8 or UTF-16 by using "PRAGMA encoding...", so that all of the field names and text data are written as the encoding type. I need to be able to query (discover) the encoding of a table when the encoding is unknown. How do I do this? TIA -brett While this doesn't answer your question, I would ask why you need to know this information? SQLite 3 provides APIs for both encodings, so you can just use the one that corresponds to the encoding that your application uses internally, for simplicity. SQLite 3 will internally convert back and forth between the API you use and the encoding used on disk, so you don't have to. Also, unless I'm incorrect, all text in a SQLite database uses the same encoding; you can't choose different ones on a by-table basis. -- Darren Duncan
Re: [sqlite] SQLite for large bulletin board systems?
On Fri, Aug 27, 2004 at 03:45:30PM -0500, Larry Kubin wrote: > Hello everyone. I am interested in creating a PHP/SQLite powered > bulletin board system similar to phpBB. However, I have read that In that case, please pay careful attention to the features and UI of the OpenACS Forums package. It does have its flaws and lacks, but it's the only web-based bulletin board software I've ever personally used that doesn't suck. (There may be other non-sucky bulletin board apps, but I've never used seen or used them.) E.g., this old-ish running instance: http://openacs.org/forums/ Or here's a related code-base (OpenACS 3.x actually, very old) with a different look and feel - but very similar functionality: http://www.carnageblender.com/bboard/q-and-a-fetch-msg.tcl?msg_id=000Cnt One flaw with those particular versions of OpenACS Forums is that they only allows two types of input, "plain text" and HTML. Other OpenACS applications offer a pallete of 3 or 4 standard textual input types: Plain text, HTML, Preformatted Text, and simplified HTML markup. Those various input options can be VERY useful for certain classes of users and types of discussions. If you want to read some anecdotes from someone else who implemented a web-based bulletin board (long, long ago), check out "Case 4: The Bulletin Board", here: http://philip.greenspun.com/panda/case-studies > SQLite is best suited for applications that are mainly read-only > (because it locks the database during writes). Do you think a SQLite > powered bulletin board is a bad idea? How would I go about handling Well, yes. But only because I think Yet Another stand-alone PHP bulletin board package is probably a bad idea; nothing to do with SQLite. > the case where two users are trying to write to the database > simultaneously? SQLite has limited write concurrency, but my guess is that the average website running some bulletin board software will never even come close to getting enough concurrent user submissions going at once to cause much trouble due to writes. SQLite will (unnecessarily) serialize them all (while PostgreSQL or Oracle would not), but that should be fine, for most sites. You MIGHT however have a VERY large number of peak concurrent readers (Slashdot Effect), so you should think about how to best use SQLite to insure that a small number of writers can't starve your thousands of readers. That should be doable, one way or another. E.g., one simple (not necessarily the best) way might be to simply cache the highest-hit pages in memory, and only update the cache at most once every 4 seconds or so. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] SQLite for large bulletin board systems?
On Fri, Aug 27, 2004 at 05:30:40PM -0400, D. Richard Hipp wrote: > The best design would be to make the application generic so > that it could use either SQLite or a client/server database. In an ideal world, yes. In practice... My guess is it's probably a LOT more trouble than it's worth. SQLite and (for example) PostgreSQL are pretty different. Much more different than Oracle vs. PostgreSQL, and those are different enough (even though they share virtually identical MVCC concurrency models)! Also, a more hand-wavy argument: If you're app is going to maybe - ever - need the scalability of a client server database like PostgreSQL, your target audience is likely such that you'll want to use other features of the client server database as well. E.g., contrast a stand-alone discussion board on the web page of a local club, vs. an entire company or university intranet with many different applications, all integrated. In the latter case, well, if you really want that software to be used by and scale to a Fortune 500 company, that audience is also going to want a whole lot of features that your local stamp collecting club would never care about. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] How to find out encoding for a table
I have a COM wrapper product, and also a Database Manager tool. The COM wrapper will have a property for specifying the encoding to use for the table (UTF-8 or 16). I haven't gotten to the point of finding out if different tables can use different encoding within a given database file. Can they? (BTW, where is all of this documented?). There are cases, such as with the Database Manager tool, where you might want to be able query the table (or DB file) to discover and use the correct encoding scheme, so that no coversions will need to be done. For example, if the encoding in the table is UTF-16, I don't want to use the UTF8 functions to read the data. Do you see? thanks try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com Darren Duncan wrote: At 9:06 AM +1000 8/28/04, EzTools Support wrote: No one has answered this query yet. Please can someone help with it. Thanks. try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com EzTools Support wrote: Hello all. I have found that I can create a table and write data to that table as either UTF-8 or UTF-16 by using "PRAGMA encoding...", so that all of the field names and text data are written as the encoding type. I need to be able to query (discover) the encoding of a table when the encoding is unknown. How do I do this? TIA -brett While this doesn't answer your question, I would ask why you need to know this information? SQLite 3 provides APIs for both encodings, so you can just use the one that corresponds to the encoding that your application uses internally, for simplicity. SQLite 3 will internally convert back and forth between the API you use and the encoding used on disk, so you don't have to. Also, unless I'm incorrect, all text in a SQLite database uses the same encoding; you can't choose different ones on a by-table basis. -- Darren Duncan
Re: [sqlite] SQLite for large bulletin board systems?
Le vendredi 27 Août 2004 23:30, D. Richard Hipp a écrit : > The best design would be to make the application generic so > that it could use either SQLite or a client/server database. > Then smaller sites could use SQLite and take advantage of > the reduce management and overhead it provides while larger > sites could use a client/server database for scalability. For PHP users, ADOdb library needs a glance, as its abstract engine is fast and really well designed. It has drivers for most databases (sqlite included) and an abstract XML schema for databases. -- JCR aka DJ Anubis LAB Project Initiator & coordinator
