Web Application Database – Programmatically Create Tables or Provide Schema File?

I have created several PHP web applications that I distribute (internally) through Debian packages. Doing so has been straightforward thanks to scripts (simplified here) to automate the process:

create_package.sh:

# create a clean debian package directory
rm -rf debian
mkdir -p debian/DEBIAN
mkdir -p debian/var/www/myapp

# populate the debian directory
cp control    debian/DEBIAN
cp myapp.php  debian/var/www/myapp
cp index.html debian/var/www/myapp

# finish through fakeroot so we can adjust ownerships without needing to be root    
fakeroot ./finish_package.sh debian .

finish_package.sh:

# $1 is the debian directory, $2 is the output directory

# adjust ownerships
chown -R root:root $1
chown -R nobody:nobody $1/var/www/myapp

# finally build the package
dpkg-deb --build $1 $2

control:

Package: myapp
Version: 1.2.3
Maintainer: Your Name <yourname@email.com>
Architecture: all
Depends: apache2, php5
Description: The myapp web application.

All this is fairly easy to do, and works well for the internal package distribution that I do. I am not sure it is sufficient for global distribution, but it might be similar to what the Ubuntu and Debian people do when they take source packages (probably distributed as tarballs with install scripts) and create .deb packages for them.

I think this can address your five points smoothly:

1. The Debian package automatically decompresses into the proper place on the target system when it is installed.

2. You can have the Debian package configure Apache automatically. Some packages like MySQL and Apache have "conf.d" directories in /etc into which you can drop configuration files. This is the ideal. Your packaging script can create a debian/etc/apache2/conf.d directory and copy a configuration file in there. It will be installed on the target system, and you can restart Apache in a script called postinst that you place in debian/DEBIAN.

3. This is probably unavoidable if custom information must be entered, though many prefer systems that can run "out of the box" with no configuration required.

4. Library existence can be guaranteed by including the appropriate package dependencies in the control file. Database connection information can be either installed as a default, or asked of the administrator on the configuration pages. Once entered, the configuration pages should invoke idempotent database migration scripts to update the database schema. Several web frameworks (like Django) make this easy.

5. The code behind the configuration pages should mark the system as configured, not the administrator.

An approach I sometimes use is to separate installation of configuration from installation of the app by making them separate packages. I can then have several different configuration packages (release, development, etc.) that I can install at will. This decoupling is also advantageous because configuration and app can evolve separately, without clobbering each other when reinstalled.

There is a difference between coding only once and compiling/linking/building only once. The former is desirable; the latter (which I think you are suggesting) is usually not worth the bother.

Code the core of your application in a language which exists both on the desktop and on the web server (and on any mobile devices you can see it being ported to in the future). Design a standard API for it.

1. On the server side, build a program which receives requests, uses your API to process them, and presents the results in whatever way is appropriate.

2. On the desktop side, build a program which has a graphical user interface and uses your API to do the actual work.

Even though the internal logic may be the same, the server-side and desktop use cases are sufficiently different that "creating the same .exe file" is quixotic and unnecessary.

I've done this with a substantial project where the core is C++ and the periphery is C++ (server and Windows), Objective-C (Mac and iOS), and Java (Android). Properly partitioned, this reduced the development effort enormously without the heroism of making a single multi-purpose executable file.