#
# Patch name: PennMySQL
# Patch version: 1.7.7p25-01
# Author's name: Hans Engelen + Javelin
# Author's email: engelenh@workspot.net
# Version of PennMUSH: 1.7.7p25 (adaptable for other versions too)
# Date patch made: 17 Nov 2003
# Author is willing to support (yes/no): no
# Patch format: diff -c -r
#
#
# This is a contributed PennMUSH patch. Its use is subject to the
# same restrictions found in PennMUSH's hdrs/copyrite.h file.
#
# No warranty is given for this patch. It is not necessarily going
# to work on your system, with any version of PennMUSH other than
# the one above, etc.
#
# If the author given above was willing to support the patch, you
# should write to the author if you have any questions or problems. Do
# *NOT* send email messages to Javelin or any PennMUSH mailing list about
# this patch!
#
# Below this line is the author's description of the patch,
# followed by the patch itself. If the patch is in context diff
# format, you'll probably apply it by typing: patch < patchfile
# in your top-level MUSH directory, unless instructed otherwise
# below.
#
# -----------------------------------------------------------------------
# Javelin's note on the 1.7.4p11-01 release:
#   This is basically Hans's patch, tested on 1.7.4p11,
#   with 1 additional function added - sqlescape(), which escapes
#   quotes and backslashes for SQL use. Otherwise, see Hans's
#   documentation below.  - Nov 10, 2001
#
#
# MySQL Patch for PennMush :
# --------------------------
#
# Updated for PennMUSH 1.7.4p11 yes, but since it is all done in funlocal.c and
# cmdlocal.c you could use this patch on a whole slew of PennMUSH releases. It
# really is not that hard.
#
# Why ?
# -----
#
# For years now I as a mush addict (hey blame Atuarre and Ekim/Mike for that)
# have been looking for a way in PennMush (most of all) to store sizeable amounts
# of data from within the game. You know the kind of data I mean, mostly static
# data once it is entered. Data such as bulletin boards, online tutorials to 
# mush mechanisms and procedures, magazines you name it. For this, one could use
# things like news or event files but I wanted the data to be creatable from
# inside the game. We have all seen various softcode objects representing things
# like notepads, computers, padds (for the TrekMinded) etc. The DB Bloat on such
# devices can be substantial when used mushwide in an active mush. But that is
# only one problem, often you would like for such objects to have mechanisms for
# indexing, field-typing, ordering, searching data and so on. So when a little
# while back the TinyMush 3.0 people decided to put SQL connectivity into their
# code I said to myself : that is such a great idea !
#
# Hence this port (yes, it is a port of their code, so almost ALL of the credit
# goes to them !!!) for Penn to do the same. This code is the first time I did
# a patch/hack for Penn so be gentle with me. It is indeed likely to have flaws
# bugs and problems. But on my setup it did what I wanted (within restrictions)
# namely offer MySQL connectivity for Penn. There are restrictions to it all
# though. One of them is return of data. If your SQL statement returns too much
# data at once it WILL get truncated. However at this point I don't feel the
# need to fix (or should I say change) that.
#
# Installation instructions :
# ---------------------------
#
# - You must have a MySQL daemon and database running for this. I use 3.22.32 but
#   you can also use others most likely. Including the development versions.
#   If you installed a MySQL RPM or precompiled package of some sort make sure
#   you also have the development files with things like mysql.h and errmsg.h 
#   etc in it. You NEED those !!
# - Go to your pennmush/src directory and patch the file into funlocal.dst and
#   cmdlocal.dst (I took those since funlocal.c and cmdlocal.c are likely to 
#   already hold some local code). If you don't have a funlocal.c and cmdlocal.c
#   you can copy to now patched funlocal.dst and cmdlocal.dst to those two
#   respective files. If you do you get to manually graft the extra code in the
#   .dst files to the .c files.
# - If you haven't allready run the PennMUSH ./Configure.
# - Edit the Makefile now. Add these (verify the paths)
#	to CCFLAGS : -I/usr/include/mysql (mysql.h and errmsg.h are here)
#	to LDFLAGS : -L/usr/lib/mysql (libmysqlclient.a is here)
#       to CLIBS   : -lmysqlclient
# - Break out the following things (if available) :
#	- rabbitsfoot
#	- lucky clover
#	- panties of your first date
#	- bible, koran or whatever regilious scripture you fancy
# - Start praying
# - Compile your PennMUSH and hope it all holds together.
# 
# Note that you ofcourse need to have a database created and a user to connect
# to this database. (Read the MySQL manuals for more on this)
#
# In short this would mean :
# - mysqladmin create penn	(create the database)
# - in mysql :
#   grant all on penn.* to penn@localhost identified by "penn";
#   (or something to that effect)
#
# Note also that the database host, user, password and databasename are at
# present hardcoded in funlocal.c. I didn't want to change other PennMUSH
# files so I just hardcoded them there. Note that your MySQL database
# machine and Mush machine can be 2 different systems !! Not to mention that
# you can also access the information in the database with other programs. You
# could easily access data in things like Perl and so forth. Or in php3 or php4
# webpages. The possebilities this offers are quite amazing. 
#
# Did I mention only wizards can use the SQL commands and functions ? Should you
# change that ? Well, since there is no way of restricting WHAT one can lookup
# and display inside of the database I would say no. Anybody who can do an
# SQL() lookup can retrieve any bit of information in the offline DB, or for
# that matter alter it. You should take care of this in your softcode instead.
# Note that in this version I decided to use the builtin wizard checking routines.
# Which by the way kept me busy for 3 days since I tested this on a 1.7.3p9 and
# as my luck always goes, in that particular version these routines were broken.
# AAARRRRGHH!!!
#
# Description of the commands and functions :
# - SqlOn()  : No return, connects to the DB (just in case, happens automaticly
#              anyway.
# - SqlOff() : Guess.
# - Sql()    : Use like Sql(Query_String[,Row_Delimiter[,Field_Delimiter]]).
#	       Query_String is parsed so you can use U(), V(), %vx etc.
#              Best is to put your query in an attrib and U() or V() it in
#              the Query_String. SQL statements after al can have confusing
#              characters like commas etc. Both delimiters are parsed as well
#              so they could have some logic in them as well.
# - @sqlconnect    : same as 'Th SqlOn()'
# - @sqldisconnect : same as 'Th SqlOff()'
# - @Sql           : Similar to Sql() but with different outputformat
#
# Am I willing to support this code ? Well, yeah, sort of. I am at present an
# extremely busy individual and as such it might take as long as it takes for
# hell to freeze over before I can offer a reply to emails. And even if I give
# you a reply it might not even help you. The only guarantee you get from me is
# the following : If it breaks, you get to keep the pieces.
#
Index: 1_7_7.738/src/funlocal.dst
*** 1_7_7.738/src/funlocal.dst Sun, 01 Dec 2002 21:14:41 -0600 dunemush (pennmush/b/20_funlocal.d 1.5 660)
--- 1_7_7.738(w)/src/funlocal.dst Mon, 17 Nov 2003 14:17:46 -0600 dunemush (pennmush/b/20_funlocal.d 1.5 660)
***************
*** 15,21 ****
--- 15,29 ----
  #include "copyrite.h"
  #include "config.h"
  #include <string.h>
+ #include <mysql/mysql.h>
+ #include <mysql/errmsg.h>
  #include "externs.h"
+ #include "attrib.h"
+ #include "flags.h"
+ #include "lock.h"
+ #include "log.h"
+ #include "dbdefs.h"
+ #include "mushdb.h"
  #include "parse.h"
  #include "confmagic.h"
  #include "function.h"
***************
*** 26,31 ****
--- 34,250 ----
   * Example included :)
   */
  
+ static MYSQL *mysql_struct = NULL;
+ 
+ /* Implements accessing a mySQL 3.22+ database. 
+  * Number of times to retry a connection if we fail in the middle of
+  * a query.
+  */
+ 
+ /* Yeah I know this is kludgy but I had no desire to go outside of funlocal.c
+  * to add extra config options for these things in netmush.conf
+  */
+ 
+ #define MYSQL_RETRY_TIMES 3
+ #define DB_HOST "localhost"
+ #define DB_SOCKET "/var/lib/mysql/mysql.sock"
+ #define DB_USER "YOURNAME"
+ #define DB_PASS "YOURPASS"
+ #define DB_BASE "YOURDB"
+ 
+ 
+ void
+ sql_shutdown(player)
+     dbref player;
+ {
+   MYSQL *mysql;
+ 
+   if (!mysql_struct)
+     return;
+   mysql = mysql_struct;
+ 
+   mysql_close(mysql);
+   free(mysql);
+   mysql_struct = NULL;
+ }
+ 
+ int
+ sql_init(player)
+     dbref player;
+ {
+   MYSQL *mysql, *result;
+ 
+   /* If we are already connected, drop and retry the connection, in
+    * case for some reason the server went away.
+    */
+ 
+   if (mysql_struct)
+     sql_shutdown(player);
+ 
+   /* Try to connect to the database host. If we have specified
+    * localhost, use the Unix domain socket instead.
+    */
+   mysql = (MYSQL *) malloc(sizeof(MYSQL));
+   if (!mysql)
+     mush_panic("Out of memory");
+ 
+   mysql_init(mysql);
+ 
+   result = mysql_real_connect(mysql, DB_HOST, DB_USER, DB_PASS, DB_BASE,
+ 			      3306, DB_SOCKET, 0);
+ 
+   if (!result) {
+     free(mysql);
+     return -1;
+   }
+ 
+ 
+   mysql_struct = mysql;
+   return 1;
+ }
+ 
+ #define print_sep(s,b,p) \
+ if (s) { \
+     if (s != '\n') { \
+       safe_chr(s,b,p); \
+     } else { \
+       safe_str((char *) "\n",b,p); \
+     } \
+ }
+ 
+ int
+ sql_query(player, q_string, row_delim, field_delim, buff, bp)
+     dbref player;
+     char *q_string;
+     char *buff;
+     char **bp;
+     char row_delim, field_delim;
+ {
+   MYSQL_RES *qres;
+   MYSQL_ROW row_p;
+   MYSQL *mysql;
+   int num_rows, got_rows, got_fields;
+   int i, j;
+   int retries;
+ 
+   /* If we have no connection, and we don't have auto-reconnect on
+    * (or we try to auto-reconnect and we fail), this is an error
+    * generating a #-1. Notify the player, too, and set the return code.
+    */
+ 
+   mysql = mysql_struct;
+   if (!mysql) {
+     /* Try to reconnect. */
+     retries = 0;
+     while ((retries < MYSQL_RETRY_TIMES) && !mysql) {
+       sleep(1);
+       sql_init(player);
+       mysql = mysql_struct;
+       retries++;
+     }
+   }
+   if (!mysql) {
+     notify(player, "No SQL database connection.");
+     if (buff)
+       safe_str("#-1", buff, bp);
+     return -1;
+   }
+   if (!q_string || !*q_string)
+     return 0;
+ 
+   /* Send the query. */
+ 
+   got_rows = mysql_real_query(mysql, q_string, strlen(q_string));
+   if ((got_rows) && (mysql_errno(mysql) == CR_SERVER_GONE_ERROR)) {
+ 
+     /* We got this error because the server died unexpectedly
+      * and it shouldn't have. Try repeatedly to reconnect before
+      * giving up and failing. This induces a few seconds of lag,
+      * depending on number of retries; we put in the sleep() here
+      * to see if waiting a little bit helps.
+      */
+ 
+     retries = 0;
+     sql_shutdown(player);
+ 
+     while ((retries < MYSQL_RETRY_TIMES) && (!mysql)) {
+       sleep(1);
+       sql_init(player);
+       mysql = mysql_struct;
+       retries++;
+     }
+ 
+     if (mysql)
+       got_rows = mysql_real_query(mysql, q_string, strlen(q_string));
+   }
+   if (got_rows) {
+     notify(player, mysql_error(mysql));
+     if (buff)
+       safe_str("#-1", buff, bp);
+     return -1;
+   }
+ 
+   /* A number of affected rows greater than 0 means it wasnt a SELECT */
+ 
+   num_rows = mysql_affected_rows(mysql);
+   if (num_rows > 0) {
+     notify(player, tprintf("SQL query touched %d %s.",
+ 			   num_rows, (num_rows == 1) ? "row" : "rows"));
+     return 0;
+   } else if (num_rows == 0) {
+     return 0;
+   }
+ 
+   /* Check to make sure we got rows back. */
+ 
+   qres = mysql_store_result(mysql);
+   got_rows = mysql_num_rows(qres);
+   if (got_rows == 0) {
+     return 0;
+   }
+ 
+   /* Construct properly-delimited data. */
+ 
+   if (buff) {
+     for (i = 0; i < got_rows; i++) {
+       if (i > 0) {
+ 	print_sep(row_delim, buff, bp);
+       }
+       row_p = mysql_fetch_row(qres);
+       if (row_p) {
+ 	got_fields = mysql_num_fields(qres);
+ 	for (j = 0; j < got_fields; j++) {
+ 	  if (j > 0) {
+ 	    print_sep(field_delim, buff, bp);
+ 	  }
+ 	  if (row_p[j] && *row_p[j])
+ 	    safe_str(row_p[j], buff, bp);
+ 	}
+       }
+     }
+   } else {
+     for (i = 0; i < got_rows; i++) {
+       row_p = mysql_fetch_row(qres);
+       if (row_p) {
+ 	got_fields = mysql_num_fields(qres);
+ 	for (j = 0; j < got_fields; j++) {
+ 	  if (row_p[j] && *row_p[j]) {
+ 	    notify(player, tprintf("Row %d, Field %d: %s",
+ 				   i + 1, j + 1, row_p[j]));
+ 	  } else {
+ 	    notify(player, tprintf("Row %d, Field %d: NULL", i + 1, j + 1));
+ 	  }
+ 	}
+       } else {
+ 	notify(player, tprintf("Row %d: NULL", i + 1));
+       }
+     }
+   }
+ 
+   mysql_free_result(qres);
+   return 0;
+ }
+ 
  #ifdef EXAMPLE
  FUNCTION(local_fun_silly)
  {
***************
*** 34,43 ****
--- 253,350 ----
  
  #endif
  
+ FUNCTION(local_fun_sql)
+ {
+   char row_delim, field_delim;
+ 
+   if (nargs >= 2) {
+     /* we have a delimiter in args[2]. Got to parse it */
+     char insep[BUFFER_LEN];
+     char *isep = insep;
+     const char *arg2 = args[1];
+     process_expression(insep, &isep, &arg2, executor, caller, enactor,
+ 		       PE_DEFAULT, PT_DEFAULT, pe_info);
+     *isep = '\0';
+     strcpy(args[1], insep);
+   }
+ 
+   if (nargs >= 3) {
+     /* we have a delimiter in args[3]. Got to parse it */
+     char insep[BUFFER_LEN];
+     char *isep = insep;
+     const char *arg3 = args[2];
+     process_expression(insep, &isep, &arg3, executor, caller, enactor,
+ 		       PE_DEFAULT, PT_DEFAULT, pe_info);
+     *isep = '\0';
+     strcpy(args[2], insep);
+   }
+ 
+   if (!delim_check(buff, bp, nargs, args, 2, &row_delim))
+     return;
+   if (nargs < 3)
+     field_delim = row_delim;
+   else if (!delim_check(buff, bp, nargs, args, 3, &field_delim))
+     return;
+ 
+   sql_query(executor, args[0], row_delim, field_delim, buff, bp);
+ 
+ }
+ 
+ FUNCTION(local_fun_sql_connect)
+ {
+ 
+   sql_init(executor);
+ 
+ }
+ 
+ FUNCTION(local_fun_sql_disconnect)
+ {
+ 
+   sql_shutdown(executor);
+ 
+ }
+ 
+ FUNCTION(local_fun_sql_escape)
+ {
+   MYSQL *mysql;
+   int retries;
+   char bigbuff[BUFFER_LEN * 2 + 1];
+ 
+   if (!args[0] || !*args[0])
+     return;
+ 
+   mysql = mysql_struct;
+   if (!mysql) {
+     /* Try to reconnect. */
+     retries = 0;
+     while ((retries < MYSQL_RETRY_TIMES) && !mysql) {
+       sleep(1);
+       sql_init(executor);
+       mysql = mysql_struct;
+       retries++;
+     }
+   }
+   if (!mysql) {
+     notify(executor, "No SQL database connection.");
+     safe_str("#-1", buff, bp);
+     return;
+   }
+   if (mysql_real_escape_string(mysql, bigbuff, args[0], strlen(args[0])) <
+       BUFFER_LEN)
+     safe_str(bigbuff, buff, bp);
+   else
+     safe_str("#-1 TOO LONG", buff, bp);
+ }
+ 
+ 
  void
  local_functions()
  {
  #ifdef EXAMPLE
    function_add("SILLY", local_fun_silly, 1, 1, FN_REG);
  #endif
+   function_add("SQL", local_fun_sql, 1, 3, FN_WIZARD);
+   function_add("SQLESCAPE", local_fun_sql_escape, 1, 1, FN_WIZARD);
+   function_add("SQLON", local_fun_sql_connect, 0, 0, FN_WIZARD);
+   function_add("SQLOFF", local_fun_sql_disconnect, 0, 0, FN_WIZARD);
  }
Index: 1_7_7.738/src/cmdlocal.dst
*** 1_7_7.738/src/cmdlocal.dst Mon, 27 Jan 2003 09:40:07 -0600 dunemush (pennmush/b/21_cmdlocal.d 1.13 660)
--- 1_7_7.738(w)/src/cmdlocal.dst Mon, 17 Nov 2003 14:15:05 -0600 dunemush (pennmush/b/21_cmdlocal.d 1.13 660)
***************
*** 16,21 ****
--- 16,22 ----
  #include "command.h"
  #include "cmds.h"
  #include "confmagic.h"
+ #include "flags.h"
  
  extern HASHTAB htab_reserved_aliases;
  
***************
*** 53,58 ****
--- 54,80 ----
  }
  #endif
  
+ COMMAND (do_sql) {
+ 
+   sql_query(player, arg_left, ' ', ' ', NULL, NULL);
+ 
+ }
+ 
+ COMMAND (do_sql_connect) {
+ 
+   if (sql_init(player) < 0) {
+     notify(player, "Database connection attempt failed.");
+   } else {
+     notify(player, "Database connection succeeded.");
+   }
+ 
+ }
+ 
+ COMMAND (do_sql_shutdown) {
+ 
+   sql_shutdown(player);
+ 
+ }
  
  /* Called during the command init sequence.
   * This is where you'd put calls to command_add to insert a local
***************
*** 75,78 ****
--- 97,104 ----
    command_add("@SILLY", CMD_T_ANY, "WIZARD ROYALTY", SEE_ALL, "NOISY NOEVAL",
  	      cmd_local_silly);
  #endif
+   command_add("@SQL", CMD_T_ANY, WIZARD, 0, 0, 0, do_sql);
+   command_add("@SQLCONNECT", CMD_T_ANY, WIZARD, 0, 0, 0, do_sql_connect);
+   command_add("@SQLDISCONNECT", CMD_T_ANY, WIZARD, 0, 0, 0, do_sql_shutdown);
+  
  }