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] Most appropriate Web based database? (Newbie)
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. 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.
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] Most appropriate Web based database? (Newbie)
My first thought when you asked this: If you have to ask, just go with MySQL. If for no other reason than to ease learning. As you said before, Googling for each has way different results. I have found that learning how to do things (with PHP in my case) with Mysql was easy just because there are SO MANY people using it, and therefore way more examples out there. Having said that, I love SQLite now that I am learning it. But it took much more effort because the docs and examples are not as abundant. For example, some abstraction layers work with SQLite3, and others only work with SQLite2. In this case, it was so much easier to work with Mysql, for me. But, now I've learned a bit more, and I am liking PDO for access to either Ver 2 or 3. (of course I am using PHP 5.1.2.) I started my app with Pear::DB, which would only work with SQLite 2, only because I tried the examples I found and none worked with other abstraction layers/drivers. Now, I figured out PDO, and I am very happy. As far as the question of "will SQLite handle it?" goes, I say no prob. I have several sites running SQLite now for small data driven tasks, and I love the idea of no db engine running, no separate process to deal with. Of course, if I was to run a large user-base on a system, (like 1000 users) I would go with Mysql or maybe MSSQL. That's just my preference. In your case, do you want to spend more time learning? And want the portability and ease of use of a file-based db? Then go with SQLite. I have found it to be quite reliable. If you want to get up and running quicker by spending less time finding examples and documentation, then go with Mysql. Mysql is very portable and easy to run, and EVERYONE has it. Ah, it's worth noting too, I kind of like the data typing and how you must conform to it in Mysql. On small web apps, like you describe, SQLite will handle it fine, but keep in mind that if you want to have restrictive data types, then Mysql will be better. IMHO. Maybe some of you SQLite experts can elaborate, but as far as I know, with SQLite, if you say you want a varchar(50) column, and you insert 55 chars, it will take it. Whereas in Mysql, it wont. My $.02. Hope it helps. On 4/6/06, Lenster <[EMAIL PROTECTED]> wrote: > > I am investigating which would be the most appropriate RDMS to use for > a new Intranet based application. I have rounded down my choice to two > candidates - SQLite and MySQL. > > > The application needs to be available to about twenty users on a daily > basis, with most of those users making no more than five 'write' > transactions a day, and around twenty 'read' transactions a day. > > > Depending upon its success the application may well develop to > incorporate other datasets and users - so the RDMS needs to be able to > develop without difficulty. > > > IMHO SQLite looks as though it would be the fastest to develop and with > the least resources required but after reading some SQLite > documentation: > > > "A good rule of thumb is that you should avoid using SQLite in > situations where the same database will be accessed simultaneously from > many computers over a network filesystem." > > > I am concerned that even though I expect light usage it is likely that > multiple users will access the same database concurrently. > > > Would I be correct in assuming that MySQL is a safer (longterm) bet? > > > Thanks for any and all responses > > > Len > -- > View this message in context: > http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3780531 > Sent from the SQLite forum at Nabble.com. > > -- Greg Fischer 1st Byte Solutions http://www.1stbyte.com
Re: [sqlite] Most appropriate Web based database? (Newbie)
Hi Len, The question you are asking is "loaded". Stay with the ansi SQL statements that SQLite supports and you will be okay. If you need to use the SQLite extensions now, you will have to write a "translator" in future - not too difficult. For an experienced programmer (which you will be if the situation you describe ever comes to pass) the answer is "use SQLite - it is a no brainer" Good Luck! At 10:13 AM 4/6/06 -0700, you wrote: > >Thanks for your reply Richard > >After further Googling and reading the replies I've received here I am >coming around to the idea that SQLite probably is the best choice. > >I would like to know whether - at some point in the future should it be >necessary - I could convert my entire SQLite databases including tables etc >to another RDMS such as MySQL? Is this straightforward or convoluted? > >Regards >Len >-- >View this message in context: http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404 628.html#a3788822 >Sent from the SQLite forum at Nabble.com. > > Sincerely, Ed Porter
Re: [sqlite] Most appropriate Web based database? (Newbie)
Provided your new database has the features you are using it is simple. For example we use Sqlite and PostgreSQL and transferring between them is simple. Even the application program API is similar so a conditional compile in the programs takes care of that and links appropriately. JS Lenster wrote: Thanks for your reply Richard After further Googling and reading the replies I've received here I am coming around to the idea that SQLite probably is the best choice. I would like to know whether - at some point in the future should it be necessary - I could convert my entire SQLite databases including tables etc to another RDMS such as MySQL? Is this straightforward or convoluted? Regards Len -- View this message in context: http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3788822 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Most appropriate Web based database? (Newbie)
Thanks Dennis, that's great news - I shall give it a try :-) -- View this message in context: http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3793176 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Most appropriate Web based database? (Newbie)
Sounds like an interesting setup! Maybe off topic, but, would you care to elaborate on that topic? Server configuration, virtualization software running, etc.? [EMAIL PROTECTED] wrote: Lenster <[EMAIL PROTECTED]> wrote: The application needs to be available to about twenty users on a daily basis, with most of those users making no more than five 'write' transactions a day, and around twenty 'read' transactions a day. The SQLite website is itself backed by SQLite. It handles between 5000 and 6000 users per day, with each user doing about 10 writes on average and dozens of queries. This is all accomplished on a server that is a virtual machine (using User Mode Linux) that is one of 24 virtual machines on the physical server. There are actually several other websites running on the same virtual machine, though the SQLite website takes most of the load. The SQLite database that backs the SQLite website has no difficulty handling this load. It could scale to much more traffic simply by devoting more of the physical server to the task. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Most appropriate Web based database? (Newbie)
Lenster wrote: I would like to know whether - at some point in the future should it be necessary - I could convert my entire SQLite databases including tables etc to another RDMS such as MySQL? Is this straightforward or convoluted? The .dump command in the sqlite shell will dump your entire database as standard SQL statements that you can execute to recreate the database using some other database engine. HTH Dennis Cote
Re: [sqlite] Most appropriate Web based database? (Newbie)
Thanks for your reply Richard After further Googling and reading the replies I've received here I am coming around to the idea that SQLite probably is the best choice. I would like to know whether - at some point in the future should it be necessary - I could convert my entire SQLite databases including tables etc to another RDMS such as MySQL? Is this straightforward or convoluted? Regards Len -- View this message in context: http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3788822 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Most appropriate Web based database? (Newbie)
Lenster <[EMAIL PROTECTED]> wrote: > > The application needs to be available to about twenty users on a daily > basis, with most of those users making no more than five 'write' > transactions a day, and around twenty 'read' transactions a day. > The SQLite website is itself backed by SQLite. It handles between 5000 and 6000 users per day, with each user doing about 10 writes on average and dozens of queries. This is all accomplished on a server that is a virtual machine (using User Mode Linux) that is one of 24 virtual machines on the physical server. There are actually several other websites running on the same virtual machine, though the SQLite website takes most of the load. The SQLite database that backs the SQLite website has no difficulty handling this load. It could scale to much more traffic simply by devoting more of the physical server to the task. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Most appropriate Web based database? (Newbie)
Sqlite works very well for web applications unless they are large and very busy. It is easy to manage and backup and performs well. For a larger scale operation PostgreSQL would be a better choice than MySql. If you design your application with care you could switch from Sqlite to PostgreSQL if the scale of operation demanded it. JS Lenster wrote: Thanks Guys I appreciate the input. Denis Sbragion how critical and complex is the data stored? Well the data IS critical but NOT complex Gerhard Häring From what you have said Gerhard it seems that SQLite is probably upto the job, however MySQL or PostgreSQL will be more futureproof should I want to expand the datasets, functionality and online support is greater. As far as configuring the server being the main difference between SQLite and MySQL - well I have already configured a test MySQL server and that seemed easy enough. I think I have convinced myself that MySQL would be the best choice for this application. I rate MySQL over PostgreSQL for two reasons: 1) Googling PostgreSQL produced 52,700,000 hits, Googling MySQL produced 397,000,000 hits 2) I've already bought a book about MySQL ;-) Thanks again -- View this message in context: http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3782823 Sent from the SQLite forum at Nabble.com.
RE: [sqlite] Most appropriate Web based database? (Newbie)
Then again only 10,000,000 hits for SQLite (Less information to wade through) and NO book to buy! (zero out of pocket expense!) Good luck! You are most likely headed the right direction. > -Original Message- > From: Lenster [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 06, 2006 6:59 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Most appropriate Web based database? (Newbie) > > > > Thanks Guys > > I appreciate the input. > > >Denis Sbragion > >how critical and complex is the data stored? > Well the data IS critical but NOT complex > > >Gerhard Häring > From what you have said Gerhard it seems that SQLite is > probably upto the > job, however MySQL or PostgreSQL will be more futureproof > should I want to > expand the datasets, functionality and online support is > greater. As far as > configuring the server being the main difference between > SQLite and MySQL - > well I have already configured a test MySQL server and that > seemed easy > enough. > > I think I have convinced myself that MySQL would be the best > choice for this > application. I rate MySQL over PostgreSQL for two reasons: > > 1) Googling PostgreSQL produced 52,700,000 hits, Googling > MySQL produced > 397,000,000 hits > 2) I've already bought a book about MySQL ;-) > > Thanks again > ...
Re: [sqlite] Most appropriate Web based database? (Newbie)
Hello Len, On Thu, April 6, 2006 13:58, Lenster wrote: > 1) Googling PostgreSQL produced 52,700,000 hits, Googling MySQL produced > 397,000,000 hits ... well, this is quite a poor comparison. On the Internet there are many articles comparing MySQL vs PostgreSQL vs other database engines. As a starting point take a look at this: http://builder.com.com/5100-6388_14-1050671.html If the data is critical I would choose PostgreSQL over MySQL. Furthermore there are many useful features of PostgreSQL which are missing in MySQL. BTW, it's your data. :) Bye, -- Denis Sbragion InfoTecna Tel: +39 0362 805396, Fax: +39 0362 805404 URL: http://www.infotecna.it
Re: [sqlite] Most appropriate Web based database? (Newbie)
Thanks Guys I appreciate the input. >Denis Sbragion >how critical and complex is the data stored? Well the data IS critical but NOT complex >Gerhard Häring >From what you have said Gerhard it seems that SQLite is probably upto the job, however MySQL or PostgreSQL will be more futureproof should I want to expand the datasets, functionality and online support is greater. As far as configuring the server being the main difference between SQLite and MySQL - well I have already configured a test MySQL server and that seemed easy enough. I think I have convinced myself that MySQL would be the best choice for this application. I rate MySQL over PostgreSQL for two reasons: 1) Googling PostgreSQL produced 52,700,000 hits, Googling MySQL produced 397,000,000 hits 2) I've already bought a book about MySQL ;-) Thanks again -- View this message in context: http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3782823 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Most appropriate Web based database? (Newbie)
Lenster wrote:
I am investigating which would be the most appropriate RDMS to use for
a new Intranet based application. I have rounded down my choice to two
candidates - SQLite and MySQL.
Ok. I think that PostgreSQL and Firebird are almost always better
choices than MySQL for a database system though.
The application needs to be available to about twenty users on a daily
basis, with most of those users making no more than five 'write'
transactions a day, and around twenty 'read' transactions a day.
[...]
Sounds like soemething SQLite can easily handle. SQLite only gets
inappropriate if concurrent writers happen often. But you have to stress
a web application quite a lot to make that happen.
Depending upon its success the application may well develop to
incorporate other datasets and users - so the RDMS needs to be able to
develop without difficulty.
Unless you build a heavy-traffic web application or need advanced SQL
features, SQLite should be appropriate.
IMHO SQLite looks as though it would be the fastest to develop and with
the least resources required
That's true. But after you have set up and configured a database server,
I don't think there's a big difference between ease of development for
SQLite vs. MySQL vs. PostgreSQL vs. Oracle vs. whatever.
The difference is that setting up and configuring a database server
costs time, and with SQLite you just don't need to do it.
but after reading some SQLite
documentation:
"A good rule of thumb is that you should avoid using SQLite in
situations where the same database will be accessed simultaneously from
many computers over a network filesystem." [...]
That is irrelevant for a web application. Network filesystems are SMB
(Windows shares), NFS, Netware shares, etc. So this paragraph is about
multiple client applications accessing the same SQLite database file
over a network filesystem. In the case of a web application, you only
really have one application that accesses the SQLite database file. And
the SQLite database file is locally accessed.
I am concerned that even though I expect light usage it is likely that
multiple users will access the same database concurrently. [...]
That's abssolutely no problem for read operations ("select"). For write
operations ("insert"/"update"/"delete"), SQLite uses locks, so only one
connection can modify the database at any time. Just be sure to COMMIT
or ROLLBACK as soon as you can.
HTH,
-- Gerhard
Re: [sqlite] Most appropriate Web based database? (Newbie)
Hello Len, On Thu, April 6, 2006 10:49, Lenster wrote: ... > Would I be correct in assuming that MySQL is a safer (longterm) bet? how critical and complex is the data stored? If it's fairly critical and/or complex (many tables with relations between them) PostgreSQL may be an even better choice. Bye, -- Denis Sbragion InfoTecna Tel: +39 0362 805396, Fax: +39 0362 805404 URL: http://www.infotecna.it
Re: [sqlite] Most appropriate Web based database? (Newbie)
Hi Lenster, For a Web application you are probably better off with MySQL, it runs as a seperate process and handles multiple threads really well. I'm probably going to offend the sqlite people out there, but sqlite is fantastic for an embedded database, and a web app is not what I would call embedded. Regards, Chris Lenster wrote: I am investigating which would be the most appropriate RDMS to use for a new Intranet based application. I have rounded down my choice to two candidates - SQLite and MySQL. The application needs to be available to about twenty users on a daily basis, with most of those users making no more than five 'write' transactions a day, and around twenty 'read' transactions a day. Depending upon its success the application may well develop to incorporate other datasets and users - so the RDMS needs to be able to develop without difficulty. IMHO SQLite looks as though it would be the fastest to develop and with the least resources required but after reading some SQLite documentation: "A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem." I am concerned that even though I expect light usage it is likely that multiple users will access the same database concurrently. Would I be correct in assuming that MySQL is a safer (longterm) bet? Thanks for any and all responses Len -- View this message in context: http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3780531 Sent from the SQLite forum at Nabble.com.
[sqlite] Most appropriate Web based database? (Newbie)
I am investigating which would be the most appropriate RDMS to use for a new Intranet based application. I have rounded down my choice to two candidates - SQLite and MySQL. The application needs to be available to about twenty users on a daily basis, with most of those users making no more than five 'write' transactions a day, and around twenty 'read' transactions a day. Depending upon its success the application may well develop to incorporate other datasets and users - so the RDMS needs to be able to develop without difficulty. IMHO SQLite looks as though it would be the fastest to develop and with the least resources required but after reading some SQLite documentation: "A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem." I am concerned that even though I expect light usage it is likely that multiple users will access the same database concurrently. Would I be correct in assuming that MySQL is a safer (longterm) bet? Thanks for any and all responses Len -- View this message in context: http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3780531 Sent from the SQLite forum at Nabble.com.
