Hi,
I have been studying Sql, by reading a book that use mysql as example. So i have installed it and used on my Pc to do the homeworks. As i find it easy to use, and if the need arise i might use it on a network, I wonder which are the pros and cons of using a Db server in case its the only user sits on the same computer the server is installed and there is no need to serve the conected pc ?
thanks for looking and helping,
Antonio
Well, while a DB Server will allow you to grow your application’s scope and connect more users very easily, it adds quite a bit of overhead to the solution (running a server, running admin tools, connections, configuration, security (!!)). A Single-User DB like SQLite, which usually consists of a local file and is natively supported by XOJO even without a DB license, does not need any server software at all. It has some limits (like very limited ALTER capabilities) but it’s easy to start with.
Antonio, there is nothing wrong with having a database server running on the same machine as your application as long system load and memory allows, which it will for 99% of all applications one develops. Now, when deploying an application on the server, many deploy the application on the same server as the database host. This again is not a problem as long as your system load stays in check. The only place it would become a problem is if you are serving up a serious number of queries and your SQL server just can’t keep up due to it being too busy. This is a problem that most of us would love to have but most will never see, why? Because it means your application is extremely popular and you can probably afford then to hire a database admin to make your problems go away.
Now… one other potential problem is if your application is for general use. Users of your application are not going to want to install a MySQL server and run it on their computer just to use your application. So, in that case it would be a bad idea as well.
In short, for learning and deploying on the server, it is fine in almost all cases to share the computer with the application and database server. Only when demand is high or when you are installing the application on a users desktop does it become a concern.
i use SQLite for the database for single user and when the client want to share the database, they need to get the CubeSQL database server which is really to install and admin (practically zero).
When i develop my application i have a indicator in the program to change from single user to multi user. I have all the codes already in my application to be able support single or multiuser.
So when my client move from single user to multi user, all i need to do is just copy the database over to the server (after installing the database server)
Thanks for helping.
As for my needs, I’d feel more prone to use a DB, for a few reasons. I believe that a DB server might grant better security (encryption and alike) than a single user database, in case I’d need to store more sensible data then the normal. Then It should be easier in case I’d need to bring the Db over a network. In any case, As for Sqlite, I like it. What stopped me to use in my program is about the limited datatypes as I make large use of dates. So I do not exactly know if it could make thinks difficult in case of translating the code to adapt it from one db to sqlite.
SQLite can be pretty secure too as it allows AES256 encryption (I believe). However, configuring a server requires advanced knowledge to make it safe - so many things can go wrong…
As to switching databases: It all depends on you. I have a pretty sophisticated ORM that does not care about the data source (when it comes to data access ) and I can switch by just changing one line of code for the whole app.
Alex,
as for the line of code, do you mean just the connection string?
As for the date value, doesn’t sqlite store them as a string?
I have an ORM - a set of classes that handle all the requests and data for me. Therefore, I do not need to write one single line of SQL. Between my ORM and any data source, I have “Data Connectors” that take care of the interaction between the data source and my ORM. All I need to do is switch the data connector to the appropriate data source. The rest of my code can remain the same. But it’s taken me quite some time to get to that point…
I use turnkeylinux for all my DB servers for a few reasons;
1/ I can deploy my own hardware, load up an iso and I have a DB that works out of the box.
2/ I can deploy to xen, proxmox, vmware and AWS
3/ They are free
4/ Turnkeys community is very helpful
5/ Paid support is available
6/ The developers Alon Swartz and Liraz Siri are known in linux and security circles
7/ Opensource components
8/ Debian based 64 and 32 bit appliances
Try their MYSQL DB on virtual box - its all free and cross platform! There is no harm in trying it out.
There is no difference (from your code’s perspective) between dates in sqlite and in other databases. You always store dates in SQLDate format (ANSI format): YYYY-MM-DD. This format was chosen such that sorting and comparison can be done in either string or binary format. It doesn’t really matter how the data is stored - that’s an implementation detail for the database engine. Your code treats them all the same.
CubeSQL is expensive to deploy.
I would suggest you look up Studio Stable, that is free.
Simon.
Brad, can ur studio stable database need to be install when running as single user??
Not sure I understand the question, but let me try… One of the key ideas behind Studio Stable Database is that it can scale from single user file database mode to server mode with minimal changes to your client app code. So you could deploy an app that connects to a file or one of the servers with changes only to the connection code.
Does that answer your question?
sound good to me… that is what i need… i am using sqlite for single user and the cubesql if need to share data among multiple user.
Sqlite and, if needed, the added capability offered by CubeSql would sounds good to me. Then, I am going to go with Sqlite and give a try at Cubesql.
Cubesql is pretty expensive compare to others but I do enjoy using it and the support is excellent. Thank Marco.
How much work needed to change in my coding to your stable studio?
It uses a parallel API so that everything is implemented in REALbasic/Xojo rather than a plugin. Search and replace should mostly get you going. Details are here: