Re: RFC: common database policy/infrastracture
First of all, this package would be a God-send for me (see below)
Note that 'wwwconfig-common' already contains most of the needed
infrastructure... but it is too php-oriented. Splitting it in purely
Apache/PHP-oriented scripts (which would remain as wwwconfig-common) and
a new 'dbconfig-common' package would probably be in order.
Please note that i find the 'database-common' name choice a very
unfortunate one...it would surely cause confusion for the end user...
dbconfig-common has none of these problems.
Oliver Elphick wrote:
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.
Almost ok, except for the fact that most of us prefer to ask the
database administrator's password(and usually name) during postint, so
that it can be forgotten( db_reset()'d ). This passwords are(and should)
usually only necessary to setup the database, while all the
database-related operations from the packaged program are done via an
unprivileged user which was setup precisely for this purpose.
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.
Ok... assuming this applies to user setup also :-?
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.
Hmmm.....
I think this package we are proposing should contain all the Debconf
templates (to avoid duplicates in all packages, as it is now) and
*routines* which perform the needed tasks as fail-safely as possible...
packages would then only need to be modified to make use of the new
infrastructure; that is, use database-common's templates from
config(after appropiate substitutions and checking) and use the provided
routines from postinst to do the actual work.
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.
...which can be done with a "note" type template where the information
would be substituted.... and this package should provide quite a lot of
pre-defined exit-actions for this purpose (barring actual path
information or other details, which would be substituted in as appropiate)
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.
I have had no problems with this... i maintain 2 packages with precisely
these needs... i simply maintain 3 "flavors" of the needed
executables[which are linked to the corresponding client libs] and ship
postinst scripts suited for each particular database with the
corresponding "flavor"... where one can not afford such luxury (i.e., a
PHP application) a template asking for the desired "flavor"(maybe with
the detected available servers marked in another color and the options
for which no client-libraries have been found greyed-out) would be in order.
( sorry for the "density" of these last paragraphs )
Best,
J.L.
Reply to: