Hi,
I mostly write Webapps and I have only the xojo-licence for the web, not for the databases. But in fact, I do not really miss it. I use SQLite-DBs in my projects and I am mostly satisfied with them.
What is the real advantage of MySQL over SQLite in web-projects?
I only miss the “drop” of columns and tables in SQLite.
SQLite is not really designed for multi-user access… only a single user access… hence why it is great for desktop applications.
MySQL is better geared for large data loads that requires concurrent access IMO.
oha!
what happens, when multiple users access on one SQLite-DB?
[quote=49766:@Alwyn Bester]SQLite is not really designed for multi-user access… only a single user access… hence why it is great for desktop applications.
[/quote]
Totally untrue. SQLite with WAL mode can safely handle lots of simultaneous users. If you’re careful with how you design your transactions (keep them tight), you won’t need the deadlock resolution that the popular servers offer. Design and test to scale as you need to scale. You will be amazed what SQLite can handle.
Another advantage of SQLite… simple sharding. Instead of one huge database for all data in your app, consider having one SQLite database per registered user. It’s easy. It’s just another file. And you get a whole lot of instant, extra security that way.
My bad. I was always under the impression SQLite’s recommendation is to use server side solutions such as MySQL, PostgreSQL or Oracle in architectures that require high concurrency.
http://www.sqlite.org/faq.html#q5
Didn’t know about WAL mode. Will definitely look into that a bit more. I’m a huge SQLite fan and haven’t had a single issue with it in years of production use. But I can say the same about MySQL where I used it server side.
so, where is the difference?
Why should I pay for a DB-Licence?
I suppose that unless you need to do transactions concurrently that exceeds thousands per second SQLite will be sufficient? I have no idea how far SQLite can be pushed and with sharding it is probably scalable…
I’m speaking under correction… But won’t you also have more fine grained control over user permissions with MySQL (e.g. Limiting access to selected tables on a user level). All this could probably be avoided anyway by clever design with SQLite.
If you are or have a DBA and are designing for a “high volume” application, yes, by all means, sink the extra effort into Oracle, MySQL, etc. I’d just say that there are a lot of applications that could actually benefit by “scaling down”, and that you’d be surprised how well SQLite can perform as part of a Xojo web app. The sharding approach, for example, can yield some very nice, easy to deploy, easy to maintain designs that can handle hundreds of simultaneous users per instance, no sweat.
Indeed, many people underestimate Sqlite, especially since the WAL mode.
I think it is well suited for web use. Especially since it is both fast and serialized (the highest level of security. Servers databases are generally quite slow in serialized mode).
There are still some flaws. Memory:
No currency type. This requires additional work for, for example, accounting applications category.
By default, accented characters (French, Spanish …) are poorly managed. Sorting will be biased. Sqlite has a powerful extension for international languages??, but not in Xojo version.
SQLite uses dynamic typing. it can be very convenient, but if you use it, it will be difficult to migrate to another database.
Concurrency:
Of course, SQLITE can not evolve to applications with high levels of concurrent writes. But it will be a problem rarely reached by applications that we develop here.
SQLITE is not a separate server. This fact, to my knowledge, it is not designed to be synchronized across multiple instances, for load balancing. It would be great if someone could develop it!
interesting! 
But if you still need to make a transaction across multiple SQLITE databases, I think atomicity is no longer guaranteed in Wal mode. But it is true that if the databases are independent, it is very interesting.
Well,
I see no really advantages in using MySQL. I read, that it is nearly impossible to maximal utilize the WAL-Mode, and the very most Webpages in the Internet would works with SQLite.
The only disadvantage of SQLite is, in my opinion, that I couldn’t solve mistakes in DB-Design (Names of Columns, datatypes, tables, etc.). So the structure is very fix and I have to design the DB more carefully.
But I really can endure
While this is true, there’s an easy work around. Create a new table with the correct structure and copy over the data from the old one. Then delete the old one and rename the new one to the old one. Applications like Navicat Essentials ($30 or so) do that for you so you just “edit” your DB scheme as you do with any other DB engine.
I use SQLiteManager from SQLabs for the since 2005 for all my db work. i start of using the db design module in RealBasic and after a week i move to SQLiteManager.
Wow,
and I was about to write my own SQLite managing tool as a webapp.
I’ll look at the SQLiteManager and Navicat Essentials!
Thanks!
This is worth checking out as well. Written with RB by Mike Bailey. SQLite Schema Editor
there are some free tools for managing sqlite too…
Valentina Studio is free and let’s you manage Valentina, SQLite, PostgreSQL, MySQL, and ODBC.
Thanks dudes!
or the command line on OS X & probably Linux as well 
Coincidently, I just put up a blog post about this yesterday: http://www.xojo.com/blog/en/2013/12/renaming-columns-in-sqlite-tables.php
Hey Paul! Thanks!
Mates, you helped me a lot!