Re: RFC: common database policy/infrastracture
Hello
On Mon, Oct 25, 2004 at 01:30:39PM +0200, José Luis Tallón wrote:
> 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.
Yes! This package should really be split into separate parts. Personally
I want to get rid of it all as most of this should(?) or could be provided
by the package that should be configured. Apache2 do no longer need
wwwconfig-common as it has an excelent way to be configured and php is
beginning to be a lot better.
It is just the database part left and you seem to make a good work on this
part. :)
Regards,
// Ola
> 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.
>
>
> --
> To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
> with a subject of "unsubscribe". Trouble? Contact
> listmaster@lists.debian.org
>
>
--
--------------------- Ola Lundqvist ---------------------------
/ opal@debian.org Annebergsslingan 37 \
| opal@lysator.liu.se 654 65 KARLSTAD |
| +46 (0)54-10 14 30 +46 (0)70-332 1551 |
| http://www.opal.dhs.org UIN/icq: 4912500 |
\ gpg/f.p.: 7090 A92B 18FE 7994 0C36 4FE4 18A1 B1CF 0FE5 3DD9 /
---------------------------------------------------------------
Reply to: