What Database should I use?

Hi all. I am currently developing an app and am using PostgreSQL.
I like it, however there are a few issues with it that has made me reconsider using it.
These issues are mainly installation related.
Im not sure if another database would suit better, or if there is a different installer available that would work better for me.
My clients tend to be computer illiterate. So I need the install to be SIMPLE!

  1. In mac Postgres App, you can’t set a password or database name at time of install. In windows you can set the password at install but not database name.
    I would like an installer that would ask for the database name to create and set a password. Or that has a very simple interface to change the password or create a database.

  2. PostgreSQL is set to only trust the local computer by default. In the setup or just by default I would need a database that will trust the local network. Or again a simple interface to allow sharing over local network (and even internet if possible).

If there is code I can write to make this happen in my app that would be great, I like Post(greSQL, but to modify the above is something a typical end user would not be capable of.

So in conclusion, I would like to know if:
There is another database with installs or interfaces that suit my needs
A different installer or interface for PostgreSQL that suits my needs
Code to change the settings that suit my needs
All while supporting the above on both Mac and Windows

Any help would be appreciated.
Cheers
Andrew

Use Sqlite

http://www.mysql.com/oem/

Your stated requirements seems to be a simple consistent cross-platform install of a network based database server. You may want to look into [url=http://www.sqlabs.com/cubesql.php]CubeSQL[/sql].

You have not mentioned price considerations, usage, management tools required and so forth. So take this recommendation with a grain of salt.

Do you actually need a multi user server based database like PostgreSQL ?
Or are you using it in a standalone app ?

It will be used by my app which will in turn be used in a variety of ways. Some users will only use it on one machine, some will have anywhere from 2-10 computers running it in the same location (shop) and other users will have it running on anywhere between 2-10 computers per shop but with multiple sites so could be up to 1000 computers at any one time.
So I need a database that can be accessed over the Internet if needed. I didn’t mention it as a requirement for ease of setup because there will only be a few instances of a setup over Internet and I am willing to help setup the database for that.
Most users will just run the install and run the database on a local computer. Most users will have multiple computers on the network. Only a few will need Multisite access and will most likely host the database on a server site somewhere like heroku.
For this reason I assume SQLite would be out of the running?
Hope this clears things up a little.
Cheers
Andrew

Also. Price would have to be free or very low cost. For the Multisite they will of course pay to have a hosted database, but majority of users will not want to pay for a database.
Cheers

[quote=163497:@Andrew Willyan]It will be used by my app which will in turn be used in a variety of ways. Some users will only use it on one machine, some will have anywhere from 2-10 computers running it in the same location (shop) and other users will have it running on anywhere between 2-10 computers per shop but with multiple sites so could be up to 1000 computers at any one time.
So I need a database that can be accessed over the Internet if needed. I didn’t mention it as a requirement for ease of setup because there will only be a few instances of a setup over Internet and I am willing to help setup the database for that.
Most users will just run the install and run the database on a local computer. Most users will have multiple computers on the network. Only a few will need Multisite access and will most likely host the database on a server site somewhere like heroku.
For this reason I assume SQLite would be out of the running?
Hope this clears things up a little.
Cheers
Andrew[/quote]

Well you MIGHT make it so i a person wants to run it standalone it uses SQLite
If they want to connect others to it then you will want a server - maybe CubeSQL since it will let you take a single use SQLite db and turn it into a multi user db.
Or you transition them to PostgreSQL for such a set up
But if you have 1000 computers connecting to it you will want a dedicated “server” and not someones desktop machine

Basically you need to have a strategy for how someone scales or moves up (& maybe down) from one set up style to another

On the cost basis SQLite & PostgreSQL fit very well

Yeah. That’s why I originally chose PostgreSQL. Which works very well as a local db also. Just the setup issues. I don’t want to have to setup everyone’s databases for them.
I would prefer to use the one db for all setups. Although I might look into SQLite and if the language or commands is similar enough to not have to completely rewrite my code it might be possible.
I’m very new to sql I used dbase in VFP. I don’t want to use it again as it is no good over the Internet.

Also. Does SQLite support transactions? I thought that would be what I need to use in PostgreSQL to make sure the database or table or field is locked while writing to it in the case of multiple network users trying to do similar tasks simultaneously and data getting put in out of whack (excuse my technical jargon).

You might look at MariaDB. You can use the MySQLCommunityServer class to access it. It may be easier to install than Postgre and is still free.

Stick with Postgres and write a simplified installer for your clients to use. This could be anything from an app they run after using the standard Postgres installer which completes the setup, to a full blown installer that wraps the entire process (i.e. a custom replacement for the standard Postgres installer).

Postgres is one of the best SQL engines in existence and it’s truly free and open source with no strings attached. It doesn’t seem wise to ditch it because something else might have a slightly easier to use installer when you can write your own. The only case where I would agree that it makes sense to use something else is SQLite for single user scenarios. Norman’s suggestion to scale from one database to another is a good one IF your database design and code is truly database agnostic. In that case I would scale from SQLite for single user to Postgres for multi-user and, again, just build some utility to make the Postgres setup a snap.

Why not a cloud DB like Microsoft Azure SQL? Very scalable, multi-user, access available anywhere your user has internet and quite inexpensive and you only pay for what you use.

Daniel, I would love to keep using PostgreSQL if I can write some code to modify the default install.
Any links to show where I might start to learn about how to:
Change the password
Allow access from entire local network
Allow access from Internet

I know how to do these things manually, but no idea how to write code in xojo to do it.
I would love to have a settings screen where the password can be changed and tickboxs to allow the access from network and Internet.

Cheers

Hi Andrew, what is your main platform you need to install PostgreSQL to? I think for Windows it could be more or less achived by writing a small script that executes the installer and makes some changes to the configuration of the DB. I am no PostgreSQL expert (I must admit I only used Oracle, MySQL and SQLite in the past) but I think it must be feasible to do.

Main platform will be Mac. By that I mean I am developing it with the focus on Mac.
My users will likely be 70/30 Windows/Mac to begin with, but hopefully I aim to even that out to 50/50 or even slightly toward Mac.
I am pushing the Mac platform with my customers and have a lot of promises to change over once my app is done.
I was thinking perhaps a script, maybe applescript could do it? Im not sure, I have very little experience in applescript.
Not sure even what scripting language to use on Windows. But would be will to give it a go.

Haven’t tried it myself, but what you are looking for is called “silent install” and googling for it (silent install postgresql) turns up a number of tools which might well be worth exploring. Such as http://pginstaller.projects.pgfoundry.org/silent.html

are you aware of this one: http://postgresapp.com ?
It is a small, standalone PostgreSQL server that you can start/stop by just launching/closing the application. So this could be a replacement for SQLite at least on Mac, because I think (and have experienced in the past) that writing code for multiple database system can be cumbersome and error prone, even if you use great libraries/ORMs like SQLAlchemy (Python!!). Better stick to one system and work with it as good as you can.

For the setup take a look at silent install options as Maximilian Tyrtania mentioned. I do not know if such an option exists on the Mac installer, but on Windows, most installer have such an option. Do the install and then connect with the defaults and alter the database corresponding to your needs, incl. the change of users, tables etc.

Sadly Maria doesn’t change the dual license nature of mySQL
On that reason I’d avoid it as it appears to be free right up til their license police show up saying “You’re commercial you owe us money”