[web] SQLite and multiple database users

Have you read this one:
http://www.sqlite.org/faq.html#q7

Maybe that's where the "Lite" comes from...

How about PostgreSQL, is that an option for you ?

Why do you want to switch away from MySQL in the first place?

Also Appropriate Uses for SQLite / "Situations Where Another RDBMS May Work Better" (I wish other programs told you what they're unsuitable for...) has "Client/Server Applications" - it's intentionally not designed to work in that kind of architecture, where multiple servers are sharing access to the database. ("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.")

You could add a client/server system on top of it, like with SQL Relay; but it seems potentially more sensible to use something like MySQL or PostgreSQL in that case.

Thank you for the replies! That FAQ link gave a clear answer. Why I'm switching to sqlite is because of setting up the mysql database seems sometimes complicated.

Setting up MySQL isn't significantly easier or harder than setting up any other client-server RDBMS like PostgreSQL. Besides, it's a one-time job. You should pick an RDBMS on how it performs with the workload you have, not on how you have to set it up.

If you really find it too difficult you can always get a sysadmin/friend to set it up for you.

Well it's just that I have to run mySQL database server on my programming PC and setup the server on every machine I test it and the game's editor needs the sql as well.
But I went back using mySql now and I awas thinking of implementing interface that uses sqlite for the game's editor and mysql for the game server. That way you can test the sql functionality (queries) and simulate the game play in the editor even sql server wouldnt be installed, since the editor works locally.

What do you think of this, would it be difficult to maintain mysql/sqlite support for the same queries?

Thanks, advice is needed.

Why do you need a MySQL server on every client? Are you replicating the central game server to the clients or something? You can use SQLite for the clients since, presumably, the database on the client will only be accessed by that client itself and not anyone else. Please explain your game and server setup with a bit more detail so we can give you better advice.

Anyway, I would not recommend mixing MySQL and SQLite. It's certainly possible but you have to be very carefull that you don't MySQL or SQLite specific features and stick to ANSI/ISO SQL.

Quote:

Original post by Sander Anyway, I would not recommend mixing MySQL and SQLite. It's certainly possible but you have to be very carefull that you don't MySQL or SQLite specific features and stick to ANSI/ISO SQL.

This is what I was thinking. Basically I wouldnt like to make this so that you can run the game on your own PC without net connection nor having to install mysql. Then to bring the players together there would be mysql based master server and zone servers.

Well, your master servers and zone servers are all going to be used by many clients, so they should be MySQL (or PostgreSQL) yes. But what are the SQL databases on the client for? Cache for server data? Client-specific data? Or are you only going to use client-side databases for testing and development?

Quote:

Original post by Sander you have to be very carefull that you don't MySQL or SQLite specific features and stick to ANSI/ISO SQL.

That doesn't seem like a feasible approach, since neither MySQL nor SQLite conforms fully to 'the standard' - SQLite "implements most of SQL92", MySQL aims to "work toward compliance with the [latest, 2003] SQL standard", and both have missing/misimplemented features; and they share some non-standard features which you could still usefully use. I think it's more reasonable to just use the intersection of the feature sets from both, rather than trying to follow any standard.

It seems like most successful database abstraction happens at a higher level than SQL - you make a new layer which converts the relevant application commands (get_nth_page_of_forum_postings, etc) into SQL, and implement it once per database, to avoid the pain of trying to get consistent results and performance from the same SQL statement in two databases.

But I agree it's better to not run the same code on two different databases at all, if it can be avoided - the pain is unlikely to be balanced by the benefits.

Quote:

Original post by Sander Well, your master servers and zone servers are all going to be used by many clients, so they should be MySQL (or PostgreSQL) yes. But what are the SQL databases on the client for? Cache for server data? Client-specific data? Or are you only going to use client-side databases for testing and development?

What I was planing goes basically like this:

* Game it self with master server, zone server and clients. All servers use mysql in this scenario.
* Editor that is run by one excutable and it can host one zone and client for editing and testing. It uses sqlite instead of the mysql for SQL queries.

Thanks for helping to think this. The SQL's query compatibly just concerns.

In that case I would just dump the SQLite idea and set up a simple central development/sandbox server that you can test on and to which the editor connects.

Quote:

Original post by Sander In that case I would just dump the SQLite idea and set up a simple central development/sandbox server that you can test on and to which the editor connects.

Yeah I guess that's the best thing to do. It's what have I been using so far, except that I dont have a real server. :)

If you only need to develop/test in-house then just grab any old box and install *nix+MySQL on it. It's very easy. If you need it accessible then you might want someone with more experience do that your you (security and all that). You really don't need much power to run a small MySQL database. An old pentium 1 or 2 will do just fine.

Thanks Sanders. Funny because I have done that too. :) Just need to shut it down when it's not used and I haven't even got the lan wakeup working yet.