On Mon, 2004-10-18 at 08:19, Javier Fernández-Sanguino Peña wrote:
>
> I'm missing some "Best practice" on how to setup the database itself. That
> is, how to setup the tables (indexes, whatever...) that the application
> will use from the database and, maybe, even some initial data in some of
> the tables.
I would suggest something like this:
1. Identify the server, database type (PostgreSQL, MySQL, Firebird,
etc.) and access method (UNIX socket, TCP/IP, TCP/IP with SSL)
2. If your package needs to create a user or database, identify the
database administrator's id and password; note that this may include
doing "su -c postgres" or similar.
3. Determine and, if necessary, create the database user which will own
your package's database and other DB objects. If your chosen server is
remote, or the server package's policy forbids application packages to
change the authentication setup, this may require manual intervention by
a database administrator. In that case, your package will be left
installed but not yet usable - any attempt to use it should return a
message saying what steps are needed to get it working.
4. For PostgreSQL, the preferred method of supplying a password from a
script is by creating ~HOME/.pgpass (perms=0600) and specifying the
password there as described in the PostgreSQL manual. If
password-authenticated access to the database is required, the
installation should create this file for the duration of the
installation only; if it already exists with different contents, it
should be moved aside. The installation script should use trap
statements to ensure that everything is put back as it was at the
termination of the script.
5. If the database does not already exist,
a. Create the database, assigning it to the ownership of the
chosen database user. For PostgreSQL:
createdb -O <owner> [-E <encoding>] <database_name>
b. As the owner, run an SQL script (appropriate to the kind of
database) to create the schema and populate it. For PostgreSQL:
psql -d <database_name> -f <script_file> -e [-h <host>]
[-p <port>] -U <database_owner>
or
su - <database_owner> -c "psql -d <database_name>
-f <script_file> -e [-h <host>]
[-p <port>]"
The latter is preferable if the system user <database_owner>
exists, because it matches PostgreSQL's default authentication
setup.
At this point, database authentication may forbid the execution of
the script; this again may need manual intervention by the
database administrator.
6. If the database does exist,
a. As the owner, run any script necessary to update the database
objects. (The PostgreSQL script command is as above; the same
caveats apply, though one would expect that password access as
database_owner would already be set up and would therefore
succeed.)
If the database supports SQL transactions (as PostgreSQL does), SQL
scripts should do everything inside a transaction, so that either all
objects are successfully created and populated or else there is no
change at all to the database.
> One common issue is that the application depends on that in order to work
> and it's not done automatically. Maybe the user is prompted to do it but he
> might be unable to do so until the installation is finished. For an example
> of this problem see #205683 (and #219696, #265735, #265878).
The problem there is that the prompting is being done in the preinst,
which is useless, because the files referred to do not yet exist. That
is not specifically a database-using problem; it is simply a packaging
error. That package should hold all the information it needs in its
preinst script, or else not attempt to do things in the preinst.
It is, however, quite possible for the application installation to fail
because of circumstances beyond the packaging system's ability to
manage. Therefore, the package installation scripts need to be able to
report what further steps are needed in order for installation to be
completed.
> It might be good to provide a common mechanism to setup the database so
> that users are not asked to run an SQL script under /usr/share/XXX (usually
> doc/package/examples). Maybe even defining a common location for these
> (/usr/share/db-setup/PACKAGE/XXXX.{mysql,pgsql}?). Notice that the SQL
> script that needs to be run might difer between RDBMS.
Almost certainly it will. See above for the commands to run.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Delight thyself also in the LORD; and he shall give
thee the desires of thine heart." Psalms 37:4
