Ezmlm-idx + SQL

Hi All,

If you are an ezmlm-idx + SQL user (or if you're just interested), please
read on...

I have an idea for some changes to the behavior of ezmlm-idx that I would
like your feedback on.

If this patch were incorporated into the standard ezmlm-idx code base, some
users would have work to do as part of the upgrade process.  In particular,
those who rely on the default table name "ezmlm".  Please read the
description below and then answer these questions.


Do you use the built-in table name default of "ezmlm"?
If not, do you use table names that match your list names? (e.g. if you have
a list named "dogs", do you have SQL table names "dogs", "dogs_slog",
"dogs_mod", etc.?)

Do you use one database for all tables in a particular domain or do you
create multiple databases?  In other words, how do you typically organize
your databases and tables?

Thanks in advance for your thoughtful feedback...

-- David I. Bell

================================================================

This patch makes 2 changes to the subdb.c file

1) The standard format for the -6 string (the database connection info
   for the DIR/subdb file) of the ezmake command is

   plugin[:host[:port[:user[:pw[:db[:base_table]]]]]]

   Prior to this patch if the base_table element is missing, the base_table
   would be set to the default vaule "ezmlm".  This patch changes the
   behavior in the parsesubdb() routine in subdb.c so that the default is
   now the name of the list (i.e. the contents of the DIR/outlocal file).

2) This patch also changes The loadsubdb() routine in subdb.c so that
   the contents of the DIR/subdb can be a file path or the database
   connection info.  If the contents of the DIR/subdb file is a
   pathname, the path will be followed and the database connection info
   will be loaded from that file (the loadsubdb() routine is now recursive).
   If, on the other hand, the contents of the DIR/subdb file is database
   connection info, then that information will be used.

   In essence, this part of the patch provides the capability for
   symlink like behavior for DIR/subdb.  I chose not to use symlinks
   because that would have required more extensive code base changes.
   The approach shown here works within the existing framework of
   emlm-make, ezmlmrc and the other utilities that are part of ezmlm-idx.


By combining 1 & 2 above you can now have a configuration such that the
database connect information is kept in one shared file and the subdb
files for all the lists effectively point to the one file.  This makes
maintenance easier AND you don't have to put sensitive database login
information (i.e. the password) in any ezmlm-make wrapper scripts you
might be using nor do you have to specify the database connection info
on the ezmlm-make command line (you specify the file pathname instesad)
In particular, if you run multiple virtual domains, you can store the
database connection info once per domain in a per-domain shared file.

Here is an example:
===================
Suppose you have virtual domains and you use a base dir of /var/vdomain.
Each virtual domain has it's own subdir in the base dir and the lists for
each domain go in the subdir.  So, if you have a virtual domain named
azurevista.org, then the ezmlm DIR would be /var/vdomain/azurevista.org
for any list in the domain.  For a list named testlist the basic ezmlm-make
command would be

$ ezmlm-make <flags> /var/vdomain/auzrevista.org/testlist \
  /var/vdomain/azurevista.org/.qmail-testlist testlist azurevista.org

For an SQL list, it would look like this:

$ echo "mysql:localhost::myuser:mypasswd:azurevista_org:" \
  > /var/vdomain/azurevista.org/mysql
$ ezmlm-make <flags> -6 /var/vdomain/azurevista.org/mysql \
  /var/vdomain/auzrevista.org/testlist \
  /var/vdomain/azurevista.org/.qmail-testlist testlist azurevista.org
# mysql command will pick up login info from $MYSQL_HOME/my.cnf
$ export MYSQL_HOME=/var/vdomain/azurevista.org
$ ezmlm-mktab-mysql.sh -d testlist | mysql --database=azurevista_org

You now have a list named "testlist" and database tables named testlist,
testlist_slog, etc.

$ cd /var/vdomain/azurevista.org
$ cat mysql
mysql:localhost::myuser:mypasswd:azurevista_org:
$ cat testlist/subdb
/var/vdomain/azurevista.org/mysql

Because the SQL table names bear the name of the list, using the
new default table naming code implemented by this patch works as
expected for any list in the virtual domain.  And, because of the
file path indirection implemented for subdb, all the lists in the
virtual domain can share a single database login info file.  Of
course, for backwards compatibility, the old way of having DIR/subdb
contain the database info also still works as expected.


Here is the patch...
#===== ~~~ SNIP ~~~ =====
--- orig/subdb.c        2007-10-06 13:00:07.000000000 -0700
+++ ./subdb.c   2007-11-13 08:16:43.000000000 -0800
@@ -18,6 +18,8 @@

 static stralloc line = {0};
 static stralloc path = {0};
+static stralloc mylocal = {0};
+static unsigned int recursionLevel = 0;
 static struct sub_plugin *plugin = 0;
 static struct subdbinfo info;

@@ -33,7 +35,7 @@
   info.host = info.user = info.pw = info.base_table = info.conn = 0;
   info.port = 0;

-               /* [plugin:]host:port:db:table:user:pw:name */
+  /* [plugin:]host:port:db:table:user:pw:name */
   port = 0;
   if (!stralloc_append(&line,"\n")) die_nomem();
   if (!stralloc_0(&line)) die_nomem();
@@ -82,21 +84,53 @@
     info.pw = (char *) 0;
   if (info.db && !*info.db)
     info.db = (char *) 0;
-  if (!info.base_table || !*info.base_table)
-    info.base_table = "ezmlm";
+  if (!info.base_table || !*info.base_table) {
+    getconf_line(&mylocal,"outlocal",1);
+    if (mylocal.len < 1) {
+      strerr_die2x(111,FATAL,ERR_NOLOCAL);
+    }
+    if (!stralloc_0(&mylocal)) die_nomem();
+    info.base_table = mylocal.s;
+  }
 }

 static int loadsubdb(const char *filename, const char *plugin)
 {
+  recursionLevel++;
   line.len = 0;
   switch (slurp(filename,&line,128)) {
   case -1:
     strerr_die3x(111,FATAL,ERR_READ,filename);
   case 0:
+    if (recursionLevel > 1) {
+      strerr_die3x(111,FATAL,filename,ERR_NOEXIST);
+    }
     return 0;
   default:
-    parsesubdb(plugin);
-    return 1;
+    if (case_startb(line.s, line.len, "/")
+        ||
+       case_startb(line.s, line.len, "../")
+       ||
+       case_startb(line.s, line.len, "./")
+       )
+    {
+      if (recursionLevel > 255) {
+       strerr_die3x(111,FATAL,ERR_OPEN,"DIR/subdb: looping or too deeply
nested");
+      }
+      stralloc newFilename = {0};
+      unsigned int j;
+      slurp(filename,&newFilename,1024);
+      if (!stralloc_append(&newFilename,"\n")) die_nomem();
+      if (!stralloc_0(&newFilename)) die_nomem();
+      if (newFilename.s[j = str_chr(newFilename.s,'\n')]) {
+       newFilename.s[j] = '\0';
+      }
+      return loadsubdb(newFilename.s, plugin);
+    }
+    else {
+      parsesubdb(plugin);
+      return 1;
+    }
   }
 }

@@ -256,6 +290,7 @@
 {
   void *handle;

+  recursionLevel = 0;
   if (subdbline == 0) {
     if (!loadsubdb("subdb",0))
       if (!loadsubdb("sql","sql"))
#===== ~~~ SNIP ~~~ =====