* src/html-strip.l: Update Local Variables.

* src/mysql-backend.c: Implement core SQL functions.


git-svn-id: file:///svnroot/swis/trunk@24 05ba3e8d-823b-0410-8fb2-de0ee4edb5ba

3 files changed, 299 insertions, 32 deletions

diff --git a/ChangeLog b/ChangeLog
index 1efe372..6302609 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,5 +1,8 @@
 2007-10-05  Sergey Poznyakoff  <gray@gnu.org.ua>
 
+	* src/html-strip.l: Update Local Variables.
+	* src/mysql-backend.c: Implement core SQL functions.
+
 	* configure.ac: New option --with-mysql
 	* src/mysql-backend.c: New file
 	* src/Makefile.am: Add mysql-backend.c
diff --git a/src/html-strip.l b/src/html-strip.l
index 6e9c04d..17bd6bf 100644
--- a/src/html-strip.l
+++ b/src/html-strip.l
@@ -334,5 +334,5 @@ main (int argc, char **argv)
 
 /* Local Variables: */
 /* mode: c */
-/* buffer-file-coding-system: utf-8 */
+/* eval: (setq buffer-file-coding-system (if window-system 'utf-8 'raw-text-unix) */
 /* End: */
diff --git a/src/mysql-backend.c b/src/mysql-backend.c
index 2959270..f7c7bff 100644
--- a/src/mysql-backend.c
+++ b/src/mysql-backend.c
@@ -15,6 +15,7 @@
    along with SWIS.  If not, see <http://www.gnu.org/licenses/>. */
 
 #include "swis.h"
+#include <stdarg.h>
 #include <fcntl.h>
 #include <mysql/mysql.h>
 #include "getpass.h"
@@ -23,7 +24,10 @@ enum {
   PROGNAME_OPTION,
   CLEANUP_OPTION,
   PASS_FROM_FILE_OPTION,
-  PASS_FROM_FD_OPTION
+  PASS_FROM_FD_OPTION,
+  URL_TABLE_OPTION,
+  WORD_TABLE_OPTION,
+  TEXT_TABLE_OPTION
 };
 
 struct option options[] = {
@@ -37,6 +41,7 @@ struct option options[] = {
   { "pass-from-fd", required_argument, NULL, PASS_FROM_FD_OPTION },
   { "port", required_argument, NULL, 'P' },
   { "socket", required_argument, NULL, 'S' },
+  { "host", required_argument, NULL, 'H' },
   { "help", no_argument, NULL, 'h' },
   { "version", no_argument, NULL, 'v' },
   { NULL }
@@ -45,18 +50,20 @@ struct option options[] = {
 void
 usage ()
 {
-  printf ("Usage: word-split [OPTIONS] DBNAME URL-TABLE WORD-TABLE TEXT-TABLE\n");
+  printf ("Usage: word-split [OPTIONS] [DBNAME]\n");
   printf ("Read input and store it in a MySQL database\n");
   printf ("\n");
   printf ("DBNAME is the name of the MySQL database to use\n");
-  printf ("Three remaining arguments describe corresponding database tables. Their\n");
-  printf ("syntax is: name:text-field:id-field.\n");
   printf ("\nOptions are:\n");
   /*  printf ("  -d, --debug                    output debugging info\n"); */
   printf ("      --cleanup                  clean the database up before proceeding\n");
   printf ("  -f, --file=FILE                read from FILE instead of stdin\n");
+  printf ("  -H, --host=HOST                MySQL host name\n");
   printf ("  -u, --user=USER                MySQL user name\n");
   printf ("  -p, --password[=PASS]          MySQL password\n");
+  printf ("      --url-table=DESCR          describe url table\n");
+  printf ("      --word-table=DESCR         describe word table\n");
+  printf ("      --text-table=DESCR         describe text table\n");
   printf ("      --pass-from-file=FILE      read MySQL password from the given FILE\n");
   printf ("      --pass-from-fd=NUMBER      read MySQL password from the file descriptor\n");
   printf ("                                 NUMBER\n");
@@ -66,6 +73,9 @@ usage ()
   printf ("  -h, --help                     print this help list\n");
   printf ("  -v, --version                  print program version and exit\n");
   printf ("\n");
+  printf ("Table description argumend (DESCR) syntax is:\n");
+  printf ("name:text-field:id-field.\n");
+  printf ("\n");
   printf ("Report bugs to <%s>\n", PACKAGE_BUGREPORT);
 }
 
@@ -73,9 +83,9 @@ FILE *input;
 
 /* MySQL access parameters */ 
 char *host;                 /* host name */
-char *socket;               /* socket ... */
+char *socket_name;          /* socket ... */
 int port;                   /* ... or port */
-char *db;                   /* Database name */
+char *db = "swis";          /* Database name */
 char *user;                 /* User name */
 char *pass;                 /* Password */
 
@@ -89,9 +99,10 @@ struct table_info
   char *field_id;
 };
 
-struct table_info url = { "url", "url", "id" };
-struct table_info word = { "words", "word", "url_id" };
-struct table_info text = { "text", "text", "url_id" };
+struct table_info url_tab = { "url", "url", "id" };
+int url_auto_increment;
+struct table_info word_tab = { "words", "word", "url_id" };
+struct table_info text_tab = { "text", "text", "url_id" };
 
 
 void
@@ -132,18 +143,107 @@ read_table_info (struct table_info *ti, const char *arg)
     ti->table_name = p;
 
   if ((p = strtok (NULL, ":")) == NULL)
-    error (1, 0, "invalid table specification: %s", arg);
-  if (*p)
-    ti->field_text = p;
+    return;
+  ti->field_text = p;
   
   if ((p = strtok (NULL, ":")) == NULL)
-    error (1, 0, "invalid table specification: %s", arg);
-  if (*p)
-    ti->field_id = p;
+    return;
+  ti->field_id = p;
 }
 
 
 MYSQL mysql;
+MYSQL_RES *result;
+
+unsigned long next_url_id;
+unsigned long url_id;
+
+/* Low-level SQL functions */
+
+struct escape_slot
+{
+  char *ptr;
+  size_t size;
+};
+
+struct escape_slot escape_slots[3];
+
+char *
+escape_arg (int slot, const char *arg)
+{
+  size_t arglen = strlen (arg);
+  size_t maxlen = arglen * 2 + 1;
+  struct escape_slot *ps = &escape_slots[slot];
+  
+  if (!ps->ptr)
+    {
+      ps->size = maxlen;
+      ps->ptr = xmalloc (ps->size);
+    }
+  else if (ps->size < maxlen)
+    {
+      ps->size = maxlen;
+      ps->ptr = x2realloc (ps->ptr, &ps->size);
+    }
+
+  mysql_real_escape_string (&mysql, ps->ptr, arg, arglen);
+  return ps->ptr;
+}
+
+void
+exec_sql (const char *fmt, ...)
+{
+  va_list ap;
+  char *qbuf = NULL;
+  
+  va_start (ap, fmt);
+  vasprintf (&qbuf, fmt, ap);
+  va_end (ap);
+  if (!qbuf)
+    error (1, errno, "cannot format query");
+
+  if (result)
+    {
+      mysql_free_result (result);
+      result = NULL;
+    }
+  
+  if (mysql_query (&mysql, qbuf))
+    {
+      error (0, 0, "query failed: %s", mysql_error (&mysql));
+      error (1, 0, "the failed query was: %s", qbuf);
+    }
+}
+
+int
+get_numeric_result (unsigned long *var)
+{
+  unsigned long n;
+  MYSQL_ROW row;
+  char *p;
+  
+  if (!result)
+    {
+      result = mysql_store_result (&mysql);
+      if (!result)
+	return 1;
+    }
+  row = mysql_fetch_row (result);
+  if (row == NULL || row[0] == NULL)
+    return 1;
+  n = strtoul (row[0], &p, 10);
+  if (*p)
+    {
+      error (0, 0, "cannot convert string to number (near %s)", p);
+      return 1;
+    }
+  *var = n;
+  return 0;
+}
+	
+
+
+/* Connect / disconnect */
 
 void
 disconnect ()
@@ -152,15 +252,141 @@ disconnect ()
 }
 
 void
-connect ()
+connect_to_sql ()
 {
   mysql_init (&mysql);
-  if (!mysql_real_connect (&mysql, host, user, pass, db, port, socket, 0))
+  if (!mysql_real_connect (&mysql, host, user, pass, db, port, socket_name, 0))
     error (1, 0, "failed to connect to database %s: error: %s",
 	   db, mysql_error (&mysql));
+  exec_sql ("SET NAMES utf8");
   atexit (disconnect);
 }
 
+
+/* Database verification */
+void
+verify_table_sanity (struct table_info *tab, int *pautoinc)
+{
+  unsigned int num_fields;
+  unsigned int i;
+  MYSQL_FIELD *field;
+  MYSQL_RES *result;
+  int text_ok = 0;
+  int id_ok = 0;
+  
+  result = mysql_list_fields (&mysql, tab->table_name, NULL);
+  if (!result)
+    error (1, 0, "cannot get field list for %s.%s: %s", db, tab->table_name,
+	   mysql_error (&mysql));
+
+  num_fields = mysql_num_fields (result);
+  for (i = 0; !(text_ok && id_ok) && i < num_fields; i++)
+    {
+      field = mysql_fetch_field_direct(result, i);
+      if (!field)
+	error (1, 0, "cannot get description of field %d in table %s", i,
+	       tab->table_name);
+
+      if (strcmp (field->name, tab->field_text) == 0)
+	{
+	  switch (field->type)
+	    {
+	    case FIELD_TYPE_STRING:
+	    case FIELD_TYPE_VAR_STRING:
+	    case FIELD_TYPE_BLOB:
+	      text_ok = 1;
+	      break;
+
+	    default:
+	      error (1, 0, "field %s.%s is not of string type",
+		     tab->table_name, tab->field_text);
+	    }
+	}
+      else if (strcmp (field->name, tab->field_id) == 0)
+	{
+	  if (!IS_NUM (field->type))
+	    error (1, 0, "field %s.%s is not of numeric type",
+		   tab->table_name, tab->field_id);
+	  else
+	    id_ok = 1;
+	  if (pautoinc)
+	    *pautoinc = field->flags & AUTO_INCREMENT_FLAG;
+	}
+    }
+  mysql_free_result (result);
+
+  if (!text_ok)
+    error (1, 0, "no field %s in table %s", tab->field_text, tab->table_name);
+  if (!id_ok)
+    error (1, 0, "no field %s in table %s", tab->field_id, tab->table_name);
+}
+
+void
+verify_db_sanity ()
+{
+  verify_table_sanity (&url_tab, &url_auto_increment);
+  verify_table_sanity (&word_tab, NULL);
+  verify_table_sanity (&text_tab, NULL);
+  if (!url_auto_increment)
+    {
+      exec_sql ("SELECT MAX(%s)+1 FROM %s",
+		url_tab.field_id, url_tab.table_name);
+      if (get_numeric_result (&next_url_id))
+	error (1, 0, "cannot get maximum URL ID: %s", mysql_error (&mysql));
+    }
+}
+
+
+/* High-level functions */
+
+void
+cleanup_db ()
+{
+  exec_sql ("DELETE FROM %s", url_tab.table_name);
+  exec_sql ("DELETE FROM %s", word_tab.table_name);
+  exec_sql ("DELETE FROM %s", text_tab.table_name);
+}
+
+void
+change_url (const char *url)
+{
+  char *p;
+  
+  exec_sql ("SELECT %s FROM %s WHERE %s='%s'",
+	    url_tab.field_id, url_tab.table_name, url_tab.field_text,
+	    p = escape_arg (0, url));
+  if (get_numeric_result (&url_id))
+    {
+      if (url_auto_increment)
+	{
+	  exec_sql ("INSERT INTO %s (%s) VALUES ('%s')",
+		    url_tab.table_name, url_tab.field_text, p);
+	  exec_sql ("SELECT LAST_INSERT_ID()");
+	  if (get_numeric_result (&url_id))
+	    error (1, 0, "cannot get URL ID: %s", mysql_error (&mysql));
+	}
+      else
+	{
+	  url_id = next_url_id++;
+	  exec_sql ("INSERT INTO %s (%s,%s) VALUES (%s,%ul)",
+		    url_tab.table_name, url_tab.field_text, url_tab.field_id,
+		    p, url_id);
+	}
+    }
+}
+
+void
+store_text (const char *text)
+{
+}
+
+void
+store_word (const char *word)
+{
+}
+
+
+/* Main loop */
 void
 read_loop ()
 {
@@ -169,7 +395,29 @@ read_loop ()
   
   while (getline (&buf, &size, input) > 0)
     {
+      int len = strlen (buf);
+
+      if (buf[len-1] == '\n')
+	buf[len-1] = 0;
       
+      if (buf[0] == '>')
+	{
+	  switch (buf[1])
+	    {
+	    case ' ':
+	      change_url (buf + 2);
+	      break;
+
+	    case '*':
+	      store_text (buf + 2);
+	      break;
+
+	    default:
+	      error (1, 0, "unexpected input: %s", buf);
+	    }
+	}
+      else
+	store_word (buf);
     }
   free (buf);
 }
@@ -183,7 +431,7 @@ main (int argc, char **argv)
   char *p;
   
   program_name = argv[0];
-  while ((c = getopt_long (argc, argv, "df:hP:p::S:u:v", options, NULL))
+  while ((c = getopt_long (argc, argv, "df:H:hP:p::S:u:v", options, NULL))
 	 != EOF)
     {
       switch (c)
@@ -197,6 +445,10 @@ main (int argc, char **argv)
 	  if (!input)
 	    error (1, errno, "cannot open file %s", optarg);
 	  break;
+
+	case 'H':
+	  host = optarg;
+	  break;
 	  
 	case 'h':
 	  usage ();
@@ -231,7 +483,7 @@ main (int argc, char **argv)
 	  break;
 	  
 	case 'S':
-	  socket = optarg;
+	  socket_name = optarg;
 	  break;
 	  
 	case 'u':
@@ -242,6 +494,17 @@ main (int argc, char **argv)
 	  swis_version (stdout, "mysql-backend");
 	  exit (0);
 
+	case URL_TABLE_OPTION:
+	  read_table_info (&url_tab, optarg);
+	  break;
+	  
+	case WORD_TABLE_OPTION:
+	  read_table_info (&word_tab, optarg);
+	  break;
+	  
+	case TEXT_TABLE_OPTION:
+	  read_table_info (&text_tab, optarg);
+	  break;
 	  
 	default:
 	  exit (1);
@@ -251,21 +514,22 @@ main (int argc, char **argv)
   argc -= optind;
   argv += optind;
 
-  if (argc < 4)
-    error (1, 0, "Not enough arguments. Try %s --help for more info.",
-	   program_name); 
-  else if (argc > 4)
-    error (0, 0, "extra arguments ignored.");
-
+  if (argc >= 1)
+    {
+      if (argc > 1)
+	error (0, 0, "extra arguments ignored");
+      db = argv[0];
+    }
+  
   if (!input)
     input = stdin;
-  
-  db = argv[0];
-  read_table_info (&url, argv[1]);
-  read_table_info (&word, argv[2]);
-  read_table_info (&text, argv[1]);
 
-  connect ();
+  connect_to_sql ();
+  verify_db_sanity ();
+
+  if (cleanup_option)
+    cleanup_db ();
+
   read_loop ();
 
   exit (0);