[dev] Do we need database scripts?

[Brent J. Nordquist]

On Sat, 22 Dec 2001, Rich Lafferty <rich@horde.org> wrote:

> On Sat, Dec 22, 2001 at 10:10:26PM -0500, Chuck Hagenbuch (chuck@horde.org) wrote:
> > Quoting Rich Lafferty <rich@horde.org>:
> > 
> > > I like having scripts available, but I
> > > think we need more duplication -- instead of separate files for auth
> > > and prefs and so on, I'd like to provide scripts that do everything
> > > for a particular database -- i.e., a "mysql-create.sql" and a
> > > "pgsql-create.sql" and so on, instead of "prefs.sql" and friends.

I do agree with the need for an easy, all-inclusive file that does all
database setup.  (I also think there should be a matching one for database
tear-down.)

Also to add to this discussion, I chatted with Chuck one day in IRC about
my concern that the current SQL scripts are trying to be one-size-fits-all
(we're trying to write SQL code that will be portable across all the db
vendors we support) and I know from experience this can get tricky.  I'm
not sure we'll be able to sustain it long-term.

Right, so:

> > Alright, this is slightly more work-intensive, but:
> > 
> > What about every app that uses SQL includes scripts for the table
> > creation that a generic Horde tool can use to create those tables?
> > We're not going to write ourselves phpMyAdmin, but something to
> > manage tables could be really useful...
> 
> I don't know that I like telling users to give their administrative
> database password to a Web application. It works for the MySQL users
> but not the Oracle users, in other words. :-)

My idea was instead of having a web-based application that would run off 
and actually connect to the database and make the changes, we develop some 
kind of meta-format for describing what our database layouts should look 
like, and then have some generation code that would produce SQL scripts 
for each database vendor.  The generation code would use portable 
constructs wherever possible (so the resulting SQL would be very close 
across all vendors), but, like browser "quirks", if we encounter things 
that need to be different, we'd have a way of handling them.

Each Horde application could maintain the meta-data for its table formats
under its own tree, and the SQL-generation could be done centrally by
choosing which apps you want (or finding all the ones under the current
directory), and generating a single SQL file.  (The code to do it could be
command-line PHP.)  This addresses Rich's concern in that admins would
have the opportunity to review the SQL and then run it themselves, so
they'd trust it... but it's one file, so it's simple for the non-db-savvy.

We might even be able to extend this idea to LDAP schema generation.

> More practically, I don't think it works with the release schedule,
> unless you see a quick way to get it working.

My idea doesn't fit with today's STABLE timeframe either, but I think it
would be cool long-term.

-- 
Brent J. Nordquist <bjn@horde.org> N0BJN       / OPN: #horde
Yahoo!: Brent_Nordquist / AIM: BrentJNordquist / ICQ: 76158942