Re: [sqlite] Most appropriate Web based database? (Newbie)
On Mon, Apr 24, 2006 at 08:54:40PM -0400, Paul Malcher wrote: > On 4/24/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > > > Keep in mind, there's *WAY* more Windows users than Linux users; that > > doesn't mean Windows is a better OS. > > > Indeed also keep in mind both Postgres and MySQL are cross platform as well. Look back to the email I quoted: the user was giving the larger number of google hits for MySQL vs PostgreSQL as a reason to choose MySQL over PostgreSQL. My point was that just beacuse a lot of people use it doesn't mean it's actually any good. MySQL: the WindowsME of databases... > One final thought: the support you'll be able to get from the PostgreSQL > > community is absolutely top-notch, possibly even better than the support > > from the SQLite community (which is really saying something). I don't > > believe the same can be said of MySQL. > > > Haha, I have to agree. I've run into performance issues with MySQL in some > of projects, but never with Postgres. Keep in mind I'm a die hard Postgres > user and totally loath MySQL, because its had very annoying issues. Had? :P -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?
On Sun, Apr 23, 2006 at 07:30:21AM +1000, John Stanton wrote: > I have actually done that and it works well for a particular class of > applications, ones with a relatively small number of simultaneous users. > For large numbers we switch to PostgreSQL The basic architecture of > Sqlite, and why it is "Lite", is that it uses a single file and file > locks for synchronization. That is very well adapted to myriads of > applications, but does not make it a competitor to DB2, Oracle or > PostgreSQL in the enterprise area. The single file makes it a dream to > maintain, but there is a price involved for that simplicity. > > Dr Hipp put it succinctly when he pointed out that Sqlite is a > replacement for fopen(), not Oracle. > > In our server we do not rely on the file locks in Sqlite but instead > serialize the transactions using the mutex/condition/event capabilities > which become available in such a framework. > > As for your idea of pluggable storage methods, I consider that a better > approach for an embedded tool like Sqlite is to embed the other > functionality alongside it. Both would be linked into the specific > application. For example the people who are eager to have more BLOB > features could do well to have a second file which just stores BLOBs in > their chosen style and links to Sqlite via a key. On a semi-related note, Sean Chittenden (of FreeBSD fame) created an API from PostgreSQL into memcached (http://pgfoundry.org/projects/pgmemcache/), mainly to allow the database to invalidate objects from memcached that had been updated. I think it would be very interesting to see something similar that would allow using SQLite from within PostgreSQL, since there's a few applications that are difficult to get good performance out of with PostgreSQL. Website session tables that are constantly updated are one example. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Most appropriate Web based database? (Newbie)
On Thu, Apr 06, 2006 at 04:58:43AM -0700, Lenster wrote: > 1) Googling PostgreSQL produced 52,700,000 hits, Googling MySQL produced > 397,000,000 hits I'd be much more concerned with the 'hits' you'll find at http://sql-info.de/mysql/gotchas.html ... Keep in mind, there's *WAY* more Windows users than Linux users; that doesn't mean Windows is a better OS. One final thought: the support you'll be able to get from the PostgreSQL community is absolutely top-notch, possibly even better than the support from the SQLite community (which is really saying something). I don't believe the same can be said of MySQL. If you do decide to go with MySQL, at least do yourself a favor and use InnoDB and turn all the strict mode stuff on. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Re: concers about database size
On Wed, Mar 22, 2006 at 07:35:32PM +0100, Daniel Franke wrote: > > I can tell you that even 750M rows wouldn't be a huge deal for PostgreSQL, > > and 20G of data is nothing. Though your table would take somewhere > > around 30G due to the higher per-row overhead in PostgreSQL; I'm not > > really sure how large the indexes would be. > > AFAIK, PostgreSQL is implemented in a client-server architecture. > For maintainability, I try to avoid such a thing. It is, but I wouldn't let that scare you off. 8.1 with a few config tweaks (mostly just to enable automatic vacuums) is very, very hands-off. Of course if SQLite suffices it'll probably be even more hands off. :) > The data could easily be grouped by chromosome, but I would like to avoid > this, too. I expect, it'd be sort of an hassle to do multi-chromosome > queries. Possibly. I honestly have no idea how partitioning works in SQLite, only in PostgreSQL. Everything would appear as a single table in PostgreSQL, and if you added some rules you'd even be able to insert/update/delete from that single table. But partitioning is not fast=true, so you'd need to do some testing to see how much it helped you. (And indeed, how much different schemes helped you). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Re: concers about database size
On Thu, Mar 16, 2006 at 09:53:27PM +0100, Daniel Franke wrote: > > > That would be an excellent question to add to the FAQ: > > "How do I estimate the resource requirements for a database?" > > I spent some time to create 3GB of sample data (just zeros, about half the > size of the actual data set I have to deal with). I'm currently importing it > into the database. As far as I already can tell: the main index will be > approx 4GB in size. AFAIK, 32bit systems never grant more than 2GB RAM per > process ... > > This may take a while, about 20 hours maybe. The partition has approx 10GB, I > can't afford more. Let's hope that this is sufficient. 20 hours seems rather long. Even if you have to worry about uniqueness constraints, there are ways to deal with that that should be much faster (deal with the data in chunks, load into temp tables, check for dupes, etc). I've not used SQLite, so I can't really speak to it's capabilities. I can tell you that even 750M rows wouldn't be a huge deal for PostgreSQL, and 20G of data is nothing. Though your table would take somewhere around 30G due to the higher per-row overhead in PostgreSQL; I'm not really sure how large the indexes would be. As for performance, I haven't seen a single mention of any kind of metrics you'd like to hit, so it's impossible to guess as to whether SQLite, PostgreSQL, or anything else would suffice. I can give you this metric though: the main table behind http://stats.distributed.net has 134M rows and I can do a group-by count on it in 188 seconds (and that's after blowing out the cache). This table is wider than yours: Table "public.email_contrib" Column | Type | Modifiers +-+--- project_id | integer | not null id | integer | not null date | date| not null team_id| integer | work_units | bigint | not null select project_id, count(*) from email_contrib group by project_id; project_id | count +-- 5 | 56427141 205 | 58512516 3 | 336550 25 | 6756695 8 | 11975041 24 | 626317 It takes up about 8G on disk. I can also do index-scans fairly fast. http://cvs.distributed.net/viewcvs.cgi/stats-proc/daily/audit.sql?annotate=1.45 is an auditing script that (among other things) scans through every record in email_contrib for a particular project_id, while joining to another table (see the code starting at line 170). It takes about 70 seconds to do this for project_id 8 or 25. All this is happening on a dual Opteron (242, I think) with the OS and transaction logs on a 2 SATA drive mirror and the data stored on a 6 SATA drive RAID 10. The machine has 4G of memory. This certainly isn't what I'd consider to be 'big expensive hardware'. As for partitioning, you might still have a win if you can identify some common groupings, and partition based on that. Even if you can't, you could at least get a win on single-person queries. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Running App state in db?
On Wed, Mar 01, 2006 at 01:53:45PM -0800, w b wrote: > Well, my locking data isn't as simple as "locked, not > locked". The resource has ranges that can be locked, and it > can be locked for reading (shared) and writing (exclusive). > It's not really fun. Sounds to me like the best bet is to put the 'resources' into a database and let it handle the locking... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Running App state in db?
BTW, if you're running everything on a single machine there's lots of other ways you can do locking that don't involve the database. On Wed, Mar 01, 2006 at 11:20:01AM -0800, w b wrote: > Unfortunately I think that this would lock the whole database within SQLITE > as there is no row level locking, so probably not the best way to go > forward, unless all of the other applications are only performing reads ? > > > Some othe ideas that might help. > > Have a field in one of your tables (May be a process table as Jim > descriobed) that stores the last update time when your main app performed a > refresh of the data. Your other applications could then infer that if that > value is greater than some threshold that the data within is old and should > not be trusted. So your other applications could infer from that that your > app has crashed. In this case you might not need to clean the DB as the data > is effectively implied as being bad given that the last_refresh time is > outside of your accepted aging window. This assumes that you are > periodically refreshing the data in there which sounds like that is the case > > On recovery (restart ) of your application I think the only thing you > probably dont want to do is go thru the recreation of the tables as that > would invalidate any prepares that your other applications have done. So may > be delete the old data and refresh it (or simply overwrite it). In doing so > your other applications would then see a new time stamp within the accepted > threshold range and so could now trust that data again. > > Wayne > > > "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: On Wed, Mar 01, 2006 at 07:38:58PM > +0100, Elrond wrote: > > > > Hi, > > > > I'm considering to put the state of a running app into an > > sqlite db. I want it in a db, so external tools can query > > it and know, what the app is doing currently. > > > > Any hints on how to clean up the db, when the app crashes? > > > > (I have external resources, that I need to "lock", so the > > idea is to put the locks in the db, so more than one > > instance of the app can run and they don't kill the > > external resource.) > > > > Any hints? > > Depending on your needs, you might be able to just lock a row for > updates and hold that lock. IE, open a seperate connection to the > database and do: > > BEGIN; > UPDATE process SET start_time = now() WHERE process_id = ?; > > And then 'sit' on that connection until you're done. When you're > finished, just issue a COMMIT. Note that some databases won't like you > leaving that transaction open a real long time, so it depends on what > you're doing if this will work. I also don't know if SQLite cares about > such things. > -- > Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] > Pervasive Software http://pervasive.comwork: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Running App state in db?
On Wed, Mar 01, 2006 at 07:38:58PM +0100, Elrond wrote: > > Hi, > > I'm considering to put the state of a running app into an > sqlite db. I want it in a db, so external tools can query > it and know, what the app is doing currently. > > Any hints on how to clean up the db, when the app crashes? > > (I have external resources, that I need to "lock", so the > idea is to put the locks in the db, so more than one > instance of the app can run and they don't kill the > external resource.) > > Any hints? Depending on your needs, you might be able to just lock a row for updates and hold that lock. IE, open a seperate connection to the database and do: BEGIN; UPDATE process SET start_time = now() WHERE process_id = ?; And then 'sit' on that connection until you're done. When you're finished, just issue a COMMIT. Note that some databases won't like you leaving that transaction open a real long time, so it depends on what you're doing if this will work. I also don't know if SQLite cares about such things. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] performance statistics
On Wed, Mar 01, 2006 at 09:25:02AM -0500, [EMAIL PROTECTED] wrote: > I am currently investigating porting my project from postgres to SQLite due > to anticipated performance issues (we will have to start handling lots more > data). My initial speed testing of handling the expanded amount data has > suggested that the postgres performance will be unacceptable. I'm > convinced that SQLite will solve my performance issues, however, the speed > comparison data found on the SQLite site (http://www.sqlite.org/speed.html) > is old. This is the type of data I need, but I'd like to have more recent > data to present to my manager, if it is available. Can anybody point me > anywhere that may have similar but more recent data? What tuning have you done to PostgreSQL? The out-of-the-box postgresql.conf is *VERY* conservative; it's meant to get you up and running, not provide good performance. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] performance statistics
On Wed, Mar 01, 2006 at 05:42:57PM +0100, Denis Sbragion wrote: > Hello Andrew, > > On Wed, March 1, 2006 17:31, Andrew Piskorski wrote: > > Is that in fact true? I am not familiar with how PostgreSQL > > implements the SERIALIZABLE isolation level, but I assume that > > PostgreSQL's MVCC would still give some advantage even under > > SERIALIZABLE: It should allow the readers and (at least one of) the > > writers to run concurrently. Am I mistaken? > > PostgreSQL always played the "readers are never blocked" mantra. Nevertheless > I really wonder how the strict serializable constraints could be satisfied > without blocking the readers while a write is in place. Simple: readers have to handle the possibility that they'll need to re-run their transaction. From http://lnk.nu/postgresql.org/8gf.html: UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row may have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the serializable transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the serializable transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the serializable transaction will be rolled back with the message ERROR: could not serialize access due to concurrent update because a serializable transaction cannot modify or lock rows changed by other transactions after the serializable transaction began. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] performance statistics
On Wed, Mar 01, 2006 at 05:23:05PM +0100, Denis Sbragion wrote: > Insert records as "processing by writer", update them to "ready to be > processed" with a single atomic update after a burst of inserts, update the > status of all "ready to be processed" records to the "to be processed by > reader" status with another single atomic update in the reader, process all > the "to be processed by reader" records, mark all the "to be processed by > reader" records as "processed" again with a single atomic update when > finished, if needed delete "processed" records. FWIW, the performance of that would be pretty bad in most MVCC databases, because you can't do an update 'in place' (Ok, Oracle can, but they still have to write both undo and redo log info, so it's effectively the same as not being 'in place' unless you have a lot of indexes and you're not touching indexed rows). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Trigger and Tree Problem
Not sure if there's a way with your current code, but if you use either an LTREE or nested sets you'd be able to detect this situation quite easily. There was a discussion about both on the list about 2 weeks ago. On Thu, Feb 23, 2006 at 05:13:37PM +0100, Thorsten Kortmann wrote: > sorry for my english.. > > i have two tables: > > 1. GROUP >id >description >is_subgroup > > 2. GROUPDETAIL >id >groups_id >remote_id >remote_type > > - each group can have 1 to x groupdetail. > - groupdetail.remote_type can have two values, 0 and 1 > if it is 0, remote_id points to a product > if it is 1, remote_id points to a group (where is_subgroup=1) > > The is_subgroup problem is fixed by this trigger: > I only can point to (sub)groups. > > CREATE TRIGGER bupdate_groupdetail BEFORE UPDATE ON groupdetail > FOR EACH ROW BEGIN > SELECT RAISE(ROLLBACK, 'error...') > WHERE new.remote_type = 1 > AND (SELECT id FROM group WHERE id = new.remote_id > AND is_subgroup = 1) IS NULL; > END; > > But i can point to (sub)groups that allready part of the same path. > In this way i get an endless recursion (1). > > GROUP-->SUB-->SUB-->SUB-->PRODUCT > | | | > +-<-1-+ +--->SUB-->PRODUCT > > Is there a way to check all parent (sub)groubs inside a trigger? > > HTH > Thorsten > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Managing trees in the database
On Thu, Feb 16, 2006 at 08:23:43PM +0100, Philipp Kn?sel wrote: > SanjayK schrieb: > >Since SQLite is perfect for use in single-user desktop utility applications > >and since such applications typically store hierarchial data (tree) in a > >single table, it would be nice to have support for special features like > >connect by of oracle. > > > >See: > > http://www.adp-gmbh.ch/ora/sql/connect_by.html > >-- > >View this message in context: > >http://www.nabble.com/Managing-trees-in-the-database-t113.html#a2974277 > >Sent from the SQLite forum at Nabble.com. > > > > > Depending on your goals, this concept might give you another solution: > > http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Yet again, examples of mis-information from MySQL... "The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:" Self-join maybe, but certainly not in the way they're suggesting. Databases that support WITH make this easy (and it's technically a self-join). The problem with what he's proposing is that it silently limits you to (in this case) 4 levels of nesting. If you have more levels than that you'll end up missing data from either the top or bottom of the tree. Of course, if you think "Feb 31" is a valid date, maybe that's OK... Their information about using a nested set model seems accurate, though. Another option is to use 'ltree'. There used to be an implimentation for PostgreSQL, but it looks like it's been removed. http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/ltree/README.ltree?rev=1.7 has more info. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] SQLite to MySQL
On Fri, Feb 10, 2006 at 04:44:30PM +0100, Fanda Vacek wrote: > You can choose what to use. An Elephant or a Dolphin :)) Or you could use both! :P http://commandprompt.com/images/mammoth_versus_dolphin_500.jpg
Re: [sqlite] Creating a (really) big table
On Fri, Feb 10, 2006 at 07:44:37AM -0500, Paul Tomblin wrote: > Quoting James Biggs ([EMAIL PROTECTED]): > > Hi. My problem is that i want to create a table which should have around > > 1000 > > columns. Obviously, to add them manually would take a lot of time. Is there > > a way to make this automatically? Any program or a command? I have looked > > around but not much luck. Thanks for looking > > 99.99% of the time when somebody wants to make a table with that many > columns, it's because they don't understand relational databases and are > trying to put information that should be in a joined table into columns in > the main table. Agreed. What is it you're trying to do? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] print for debugging from triggers
On Thu, Feb 09, 2006 at 09:51:44AM -0700, Dennis Cote wrote: > >program). But this didn't work. So I wrote a simple print method that takes > >one argument and printf it to the standard out. This works and actually > >solves my problem. > There isn't a way to get this output printed directly, but you can > easily create a log of your applications activity in a table and then > use a select to dump the table. The problem with logging to a table is that if the transaction aborts (ie: because there's a bug in the trigger), you won't get anything in the table. So when you need the info most you can't get it. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] testing Avg() function in other database engines
In the case of PostgreSQL, avg is actually returning numeric when handed an int. On Wed, Feb 08, 2006 at 11:26:37AM -0800, Rich Shepard wrote: > On Wed, 8 Feb 2006, Paul Tomblin wrote: > > >In PostgreSQL 7.3, I get the same: > > Also in postgres-8.1.2. I entered the reals with a decimal point; made no > difference. > > Rich > > -- > Richard B. Shepard, Ph.D. | Author of "Quantifying > Environmental > Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy > Logic" > <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: > 503-667-8863 > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] SQLite to MySQL
See also http://sql-info.de/mysql/gotchas.html.
About the only downsides I can think of with PostgreSQL is that it's
out-of-the-box configuration is meant for like a 486 and that not quite
as many hosting providers offer it. That url has about 100 downsides to
MySQL (many of which are rather serious). PostgreSQL is also strives to
stay as close to ANSI SQL as possible and makes it nearly impossible to
configure your database in such a way that it's only a matter of time
and luck before you end up with corrupted data.
Granted, MySQL added a lot of features in 5.0, but they still focus much
less on doing things the right way than PostgreSQL does.
And remember: Feb 31st isn't a date. :)
On Thu, Feb 09, 2006 at 07:47:47AM +1100, John Stanton wrote:
> PostgreSQL implements standard SQL as well as the features of an
> enterprise DBMS. On that basis if you are changing it makes sense to
> change to the fuller-featured product, one in the same class as Oracle
> and DB2. In the short term Mysql could be as good as PostgreSQL.
>
> Fanda Vacek wrote:
> >I'm not sure, if Postgres is better choice than MySQL. I have used both
> >of them to find out which is the better one. Both of them can do
> >almost anything. The choice is a matter of taste and person. We are
> >free to choose:)) I'm talking about MySQL 5 and PostgreSQL 8.1.
> >
> >Sorry for writing this to SQLite mail-list.
> >
> >Fanda
> >
> >On Tue, 07 Feb 2006 22:35:09 +0100, John Stanton <[EMAIL PROTECTED]>
> >wrote:
> >
> >>Jim C. Nasby wrote:
> >>
> >>>On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote:
> >>>
> >>>>Hi there,
> >>>>
> >>>>I use SQLite on my website for 2 years now. I do like SQLite a lot and
> >>>>will use it for a lot of new web projects but, because I got more and
> >>>>more traffic, I consider to move this one to MySQL in order to reduce
> >>>>the over load of my computer (I host it @ home).
> >>>>
> >>>>Do you know if there is a way to convert easily an SQLite database
> >>>>into a MySQL one ?
> >>>>(I'm especially thinking about the escapestring and other "create
> >>>>table [myTable] {" issues...)
> >>>
> >>> FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it
> >>>is to
> >>>MySQL, so it might me easier to migrate that direction...
> >>
> >>
> >>If you are migrating to an enterprise level DBMS, PostgreSQL is a
> >>better choice than Mysql. It is fully featured with all the
> >>qualities of DB2 and Oracle but without the expense.
> >>
> >>Note that you will need considerably more machine resources to run a
> >>"heavier" DBMS than Sqlite. For example on our 166MHz, 256MB RS/6000
> >>PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series
> >>it is lightning fast. Sqlite runs fast on the 166MHz machine.
> >>
> >
> >
>
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Versioning in SQL database?
On Wed, Feb 08, 2006 at 08:33:48AM +0100, Arjen Markus wrote: > Paul Tomblin wrote: > > > > I am putting together something that will act like a Wiki for structured > > data (in this case, airport and navigation aid data like id, location, > > runways, etc). I currently store the data in an SQL databasee, but only > > the "current" version. I want to allow people to edit that data, but that > > means being able to compare versions, roll back erroneous edits, get what > > the database looked like before a particular editor came along, etc. Is > > there anything written on this topic? Has anybody ever tried it before? > > > > > > I recently saw something similar to what you describe (well, the airport > and navigation stuff) - http://www.flightaware.com - but maybe that is > just a similar area of application. Sounds pretty different. Flightaware tracks all flights currently in the air that are under the control of the air traffic system (basically, commercial flights and anyone who's filed an IFR flight plan). I actually know the folks behind the site. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Versioning in SQL database?
On Tue, Feb 07, 2006 at 10:55:27AM -0500, Paul Tomblin wrote: > I am putting together something that will act like a Wiki for structured > data (in this case, airport and navigation aid data like id, location, > runways, etc). I currently store the data in an SQL databasee, but only > the "current" version. I want to allow people to edit that data, but that > means being able to compare versions, roll back erroneous edits, get what > the database looked like before a particular editor came along, etc. Is > there anything written on this topic? Has anybody ever tried it before? I would add a version column to the appropriate tables and create views that pull the most recent version of everything. Or if performance becomes an issue, keep a table with only the current versions, and a seperate table with either all older versions or just all versions. An added benefit of a version field is it's a trivial way to detect potential conflicts; if you have the edit code pass back the version it was editing, you can verify that that version is what's still in the database. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 07:54:19PM +0100, Nemanja Corlija wrote: > On 2/7/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > > You might want to put a legend on your results tables so it's clear > > what the numbers represent. I assume these are times but I didn't see > > anything so far that said. > Its time in seconds. > "sync" in case of SQLite is PRAGMA synchronous=FULL; while in case of > MySQL it signifies usage of InnoDB engine. > "nosync" is the opposite, of course synchronous=OFF; and MyISAM engine. BTW, if you want to test both sync and async options you can turn fsync off in PostgreSQL by setting fsync to false. If you do that I'd also turn full_page_writes off as well, since there's no point in writing the extra info. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 08:07:03PM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > AFAIK MySQL ships with a few different config files, so presumably > > choosing the appropriate one would be equivalent to what I provided for > > PostgreSQL. > Yes, and I installed it as a developer machine. That should be the > least performant one of the 3 available. Which is probably targeted at a machine similar to yours. > > BTW, has anyone looked at adding SQLite support to any of the DBT > > benchmarks? http://sourceforge.net/projects/osdldbt > I just glanced over it, but I think they concentrate on heavy load > testing while my focus is on single user environment for now. Well, they're replacements for the various TPC benchmarks. dbt3 is equivalent to TPC:H, which is a warehousing environment. The advantage to these tests is that they're built on real-world applications, while much of this benchmark is stuff you'd never want to see in a real application, at least not if you cared about performance. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 11:51:22AM -0500, [EMAIL PROTECTED] wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > > On Tue, Feb 07, 2006 at 09:54:22AM -0600, Jim C. Nasby wrote: > > > Hrm, that's rather odd. What does top show when it's running through > > > psql? Are the test scripts available for download? I'll try this on my > > > machine as well... > > > > I see theh tcl now... is TCL piping into psql, or are there a set of raw > > files you could post or send me? If you're piping from TCL, I'd be > > curious to see what the difference is if you run this manually. For > > these large data sets I also think it's not very reflective of the > > database to send the result set all the way back through the client, > > since that's not very representative of the real world. In the case of > > PostgreSQL, a good alternative would be > > > > SELECT count(*) FROM ( > > SELECT t1.a FROM ... > > ) a > > ; > > > > But I'm not sure if all the other databases support that. > > SQLite supports the syntax above, FWIW. > > Your theory is that SQLite does well because it doesn't need to > send data back and forth between the client and server? You're > probably right. On the other hand, what good is the data if > the client never sees it? Well, my point was that the test in question is probably generating close to 100k rows if not more. Trying to pull that much data from the database at once is either poor design (something I've seen far too often) or a pretty unusual set of requirements. In any case, it certainly wouldn't surprise me if psql gets in the way here. > You'll notice that SQLite seems to do particularly well on the > tests that involve a lot of SQL. For example, test 2 with > 25000 separate INSERT statements. SQLite ran in 0.7 seconds > versus 16.5 seconds for PostgreSQL. Probably a big fraction of > the 16.5 seconds PostgreSQL used were in transmitting all of > that SQL over a socket to the server. I'm wondering if the > use of prepared statements might reduce the performance gap > somewhat? Notice that when doing an equally large insert in > Test 12, but an insert that involves much less SQL and parsing, > that PostgreSQL is actually a little faster than SQLite. > > Any volunteers to run the experiment? Jim? The original poster is sending me the generated files. I'll run an experiment with prepared statements and see what that gains us. But yes, trying to prepare that many statements over and over is a sure-fire way to slow things down. > Another explanation for the poor performance by PostgreSQL in > test 2 might be the PostgreSQL parser is less efficient. Or > perhaps the PostgreSQL spends a lot more time trying to > optimize - which can pay off on a big query but is a drag for > lots of silly little inserts. A test using prepared statements > would help clearify the issue. Off the top of my head, in the INNER JOIN case I believe there's about 8 different ways to execute that query, and PostgreSQL will consider all of them for every statement. So it certainly wouldn't surprise me if that was a major issue. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 08:11:02PM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > I see theh tcl now... is TCL piping into psql, or are there a set of raw > > files you could post or send me? > TCL generates files and then I'm piping those files to all databases > except Firebird, which doesn't accept commands from stdin so I'm > pointing isql to read that same file from disk. > I'll email you row files if you want. Its less 750KB bziped. Please do. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 10:04:43AM -0500, Clay Dowling wrote: > > Jim C. Nasby said: > > > Well, that certainly won't help things... at a minimum, on your machine, > > you should change the following: > > shared_buffers=1 > > effective_cache_size=10 > > > > The following should also help: > > work_mem=1 > > vacuum_cost_delay=50 > > autovacuum=on > > autovacuum_vacuum_scale_factor=0.2 > > Jim, > > I just want to say thanks for providing these tuning parameters. I not > currently using your product, but I hope to in the near future for a > larger scale version of my own product. Performance tuning is something > of an arcane art from my perspective, so getting any help on it is highly > desirable. You're welcome. Just to clarify, PostgreSQL isn't really a Pervasive product; we just have a bundled installer and offer support and services, but it's all the same as the community code. When it comes to tuning, http://www.powerpostgresql.com/PerfList and http://www.revsys.com/writings/postgresql-performance.html are a couple places to start looking. Finally, and luckily this applies to SQLite as well so this doesn't get too off topic :), PLEASE seek help/advice BEFORE spending a bunch of money on a big server! All too often I see people who spend a load of $$ on equipment they didn't need or won't be able to utilize because they didn't do enough research before hand. Granted, I'm biased since I make money on consulting, but the amount of money I've seen people spend on needless hardware would often buy a pretty good chunk of my time. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 09:54:22AM -0600, Jim C. Nasby wrote: > Hrm, that's rather odd. What does top show when it's running through > psql? Are the test scripts available for download? I'll try this on my > machine as well... I see theh tcl now... is TCL piping into psql, or are there a set of raw files you could post or send me? If you're piping from TCL, I'd be curious to see what the difference is if you run this manually. For these large data sets I also think it's not very reflective of the database to send the result set all the way back through the client, since that's not very representative of the real world. In the case of PostgreSQL, a good alternative would be SELECT count(*) FROM ( SELECT t1.a FROM ... ) a ; But I'm not sure if all the other databases support that. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 08:07:53AM -0500, [EMAIL PROTECTED] wrote: > It is also interesting to note that PostgreSQL get significantly > slower in Test 13 (join with an index) versus Test 6 (the same > join without an index). What is that about? Firebird shows the > same effect, but less dramatically. Could it be a difference in > the data that the tables hold at that point. Test 6B proposed > above really would be instructive here, I think. Well, I'm a bit skeptical as to the usefulness of that test, since if I'm reading things correctly it's essentially a cartesian product. In any case, it's very likely that the lack of analysis and default parameters resulted in a bad query plan. The output of EXPLAIN ANALYZE would be most instructive. > I also wonder if MySQL and Firebird would benefit from tuning. > The MySQL people are rather laid back and probably will say > something like "whatever" if asked. The Firebird crowd, on the > other hand, tend to be edgy and I suspect we will be hearing > some pointed commentary from them in the near future. FWIW, I wouldn't really consider the changes I suggested 'tuning', as they're rather off-the-cuff based strictly on my experience and limited knowledge as to the workload. Personally, I'd prefer if PostgreSQL would at least provide multiple sample configs, but c'est la vie. AFAIK MySQL ships with a few different config files, so presumably choosing the appropriate one would be equivalent to what I provided for PostgreSQL. BTW, has anyone looked at adding SQLite support to any of the DBT benchmarks? http://sourceforge.net/projects/osdldbt -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 10:08:23AM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote: > > > > Well, that certainly won't help things... at a minimum, on your machine, > > > > you should change the following: > > > > shared_buffers=1 > > > > effective_cache_size=10 > > > > > > > > The following should also help: > > > > work_mem=1 > > > > vacuum_cost_delay=50 > > > > autovacuum=on > > > > autovacuum_vacuum_scale_factor=0.2 > > > Sure, I could do that. But then I'd also need to tune all other > > > databases to make things fair and that's not really what I intended to > > > do here. I want to keep things as "out of the box" as possible. > > > > Then you should just drop PostgreSQL from the tests, because they're not > > doing anyone any good. It's pretty well known that the default > > postgresql.conf is meant to allow for bringing the database up on a > > machine with very minimal hardware. It's the equivalent to using MySQL's > > minimum configuration file. > > OK, I've changed above settings but now I get even worse performance. > 265.223 seconds. > File I've edited is C:\Program Files\PostgreSQL\8.1\data\postgresql.conf > AFAICT that's the one. Then I've restarted postgres. I guess that > should load new settings? > Then I ran VACUUM ANALYZE t2; > > Hmmm, now I ran that same script from pgAdmin and it completed in 5 seconds. > I guess its reasonable to assume that psql is actually the bottleneck > here. I tried redirecting to file but that was a minute ago and it's > still running. Any ideas? Hrm, that's rather odd. What does top show when it's running through psql? Are the test scripts available for download? I'll try this on my machine as well... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote: > > Did you happen to do an analyze? > Nope. All databases are run as default as possible. And, they all get > same scripts to execute. Which means PostgreSQL can only take a wild stab at what's in the database. > > > > What changes have you made to the default postgresql.conf? > > > None. > > > > Well, that certainly won't help things... at a minimum, on your machine, > > you should change the following: > > shared_buffers=1 > > effective_cache_size=10 > > > > The following should also help: > > work_mem=1 > > vacuum_cost_delay=50 > > autovacuum=on > > autovacuum_vacuum_scale_factor=0.2 > Sure, I could do that. But then I'd also need to tune all other > databases to make things fair and that's not really what I intended to > do here. I want to keep things as "out of the box" as possible. Then you should just drop PostgreSQL from the tests, because they're not doing anyone any good. It's pretty well known that the default postgresql.conf is meant to allow for bringing the database up on a machine with very minimal hardware. It's the equivalent to using MySQL's minimum configuration file. It certainly doesn't seem unreasonable to tweak a handful of parameters for each database. I wouldn't even consider this to be tuning; everything I recommended is a fairly standard set of adjustments. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 07:06:26AM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > For test 8 on PostgreSQL, what's EXPLAIN ANALYZE for one of those show? > test=# EXPLAIN ANALYZE SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100; > QUERY PLAN > > - > -- > Aggregate (cost=6.02..6.03 rows=1 width=4) (actual time=0.183..0.185 > rows=1 loops=1) >-> Index Scan using i2b on t2 (cost=0.00..6.01 rows=1 width=4) > (actual time=0.030..0.108 rows=12 loo > ps=1) > Index Cond: ((b >= 0) AND (b < 100)) > Total runtime: 0.510 ms > (4 rows) Well, it expected 1 row and got 12. In this example it probably doesn't matter, but for other parameters it probably will. Did you happen to do an analyze? > > What changes have you made to the default postgresql.conf? > None. Well, that certainly won't help things... at a minimum, on your machine, you should change the following: shared_buffers=1 effective_cache_size=10 The following should also help: work_mem=1 vacuum_cost_delay=50 autovacuum=on autovacuum_vacuum_scale_factor=0.2 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] More benchmarks
On Tue, Feb 07, 2006 at 05:57:24AM +0100, Nemanja Corlija wrote: > I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdSQL. > > Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison For test 8 on PostgreSQL, what's EXPLAIN ANALYZE for one of those show? What changes have you made to the default postgresql.conf? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] SQLite to MySQL
On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote:
> Hi there,
>
> I use SQLite on my website for 2 years now. I do like SQLite a lot and
> will use it for a lot of new web projects but, because I got more and
> more traffic, I consider to move this one to MySQL in order to reduce
> the over load of my computer (I host it @ home).
>
> Do you know if there is a way to convert easily an SQLite database
> into a MySQL one ?
> (I'm especially thinking about the escapestring and other "create
> table [myTable] {" issues...)
FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it is to
MySQL, so it might me easier to migrate that direction...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Auto Increment?
On Tue, Jan 31, 2006 at 10:05:47AM -0700, Dennis Cote wrote: > [EMAIL PROTECTED] wrote: > CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32))); I suspect you'll see better performance if you hard-code the value instead of doing a bit-shift every time you insert. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Database design and SQLite
On Mon, Jan 16, 2006 at 04:11:47AM -0600, michael munson wrote: > >At the worst that would be around 6 million columns on a property table. > >I've not used SQLite with tables that large before > >so I'm not sure if searching with 2 index values (name, and object its on) > >is going to be slow. > > Heh, that should obviously read '6 million rows' . Sorry, its early. Unless your rows were exceptionally wide, that shouldn't be difficult to handle at all. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] proposal for improving concurrency in SQLite
On Tue, Jan 10, 2006 at 04:29:19PM -0300, Axel Mammes wrote: > How about using a hard-link to store the log file somewhere else? That > should work transparently... Well, you can't hard-link across filesystems, but a symlink is exactly what I was going to mention. As for SQLite coming up with missing log files, I would certainly hope it would just stop dead in it's tracks rather than corrupt anything. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] proposal for improving concurrency in SQLite
On Mon, Jan 09, 2006 at 09:08:10PM -0800, Dan Kennedy wrote: > > > --- "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > > > On Mon, Jan 09, 2006 at 06:47:04PM +0100, Eduardo wrote: > > > of transactions per second. But because each transaction requires at > > > least two revolutions of the disk platter, SQLite is limited to about > > > > Why does a transaction commit require two seperate writes? > > -- > > Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] > > Pervasive Software http://pervasive.comwork: 512-231-6117 > > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > > > The short version: > > The first write operation writes the parts of the database that > are about to be overwritten to the journal file. If something > goes wrong during the second write, the journal file will be > used to restore the database to it's previous state. The second > write is the one that actually modifies the database file. Yes, but the second write (to the data files) isn't time critical. Because it doesn't (or at least shouldn't) be getting fsync'd very often, it can also be re-ordered by the OS (and possibly the drive). In any case, it's completely inaccurate to say that each transaction requires two revolutions of the drive. Also, if SQLite supports it, putting the log files on a seperate set of drives from the tables is almost always a win. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] proposal for improving concurrency in SQLite
On Mon, Jan 09, 2006 at 06:47:04PM +0100, Eduardo wrote: > of transactions per second. But because each transaction requires at > least two revolutions of the disk platter, SQLite is limited to about Why does a transaction commit require two seperate writes? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] ring buffer table
On Mon, Jan 09, 2006 at 09:19:00AM -0600, Jay Sprenkle wrote: > > A guy on this end had a suggestion that I kicked myself for not > > thinking of earlier: Use a simple numeric ID that you mod by the number > > of records you want, automatically overwriting the oldest record when a > > new one comes along.. I may have to put together a smallish trigger to > > keep the IDs in range, perhaps a manual implementation of a simple > > sequence to pull a value from, then use that as the record ID. > > That works but what do you sort by to get the entries in order? You can either just use a simple sequence and then delete where sequence mod = 0 (hrm... does sqlite support functional indexes?), or just add a timestamp to the table. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] INERT OR REPLACE behavior
On Sat, Jan 07, 2006 at 02:16:46PM -0500, [EMAIL PROTECTED] wrote: > Isaac Raway <[EMAIL PROTECTED]> wrote: > > [I]nstead of dropping the row existing, [the REPLACE algorithm should] > > simply update the provided fields in place, leaving the unmodified fields > > as is. I'd call this behavior OR UPDATE as it would be exactly equivalent > > to doing an UPDATE on the existing row(s). > > There might be two or more rows that conflict. How would it choose > which row to use as the basis? Not according to the original email... > When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] enum in SQLite
On Thu, Jan 05, 2006 at 01:39:02PM -0600, Kurt Welgehausen wrote: > create trigger EnumTrg before insert on MainTbl for each row > when (select count(*) from EnumVals where val = new.EnumCol) = 0 begin >select raise(rollback, 'foreign-key violation: MainTbl.EnumCol'); > end; Wouldn't that be a lot more efficient with some kind of EXISTS test rather than a count(*)? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] specifying field type, any benefit?
On Wed, Jan 04, 2006 at 03:54:49PM -0500, Mark Wyszomierski wrote:
> Hi all,
>
> I switched to sqlite from mysql awhile ago, I maintained the field types
> in my sqlite implementation such as:
>
> create table students (first_name TEXT, age INTEGER);
>
> I'm just wondering if there is any point to specifying the field type as if
> I try adding a string type into the age field, it seems to be accepted ok:
>
> insert into students values('hello');
>
> Does sqlite have any problem regarding setting a field defined as INTEGER
> from a text string (any limits etc?), are there any performance gains to be
> had with specifying the field type?
Well, what's actually happening is that since you didn't supply a value
for age, it's being set to NULL. While that's fine for testing and
what-not, in your code you should really provide a list of fields that
you're inserting into, so there's no ambiguity. IE:
INSERT INTO students(first_name) VALUES('John');
As for inserting text into an int, presumably it will cast it if it can.
So
INSERT INTO students(age) VALUES('John');
would fail, but
INSERT INTO students(age) VALUES('18');
should work. Of course, you're ultimately just making the database do
more work; you should really just insert the int as an int and be done
with it...
INSERT INTO students(age) VALUES(18);
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] LIMIT keyword does work in an UPDATE statement
On Tue, Jan 03, 2006 at 10:15:17AM -0500, [EMAIL PROTECTED] wrote: > test mjom <[EMAIL PROTECTED]> writes: > > > create table tbl1 ( id integer primary key autoincrement, ref > > integer, sts varchar(16)); > > insert into tbl1 (ref,sts) values (10, 'ready' ); > > insert into tbl1 (ref,sts) values (20, 'ready' ); insert into tbl1 > > (ref,sts) values (30, 'ready' ); > > update tbl1 set sts='busy' where sts='ready' ORDER BY ref DESC LIMIT 1; > > > > => i would like to have only the third record (30,'busy') updated. > > How about something like > > UPDATE tbl1 > SET sts = 'busy' > WHERE ref = > (SELECT ref > FROM tbl1 > WHERE sts = 'ready' > ORDER BY ref DESC > LIMIT 1); That won't work. Instead: UPDATE ... WHERE id = (SELECT id FROM tbl1 WHERE ... ); -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
