MG Mud User | 88f1247 | 2016-06-24 23:31:02 +0200 | [diff] [blame^] | 1 | CONCEPT |
| 2 | mysql - mySQL support |
| 3 | |
| 4 | DESCRIPTION |
| 5 | On hosts with the mySQL package installed, the driver can be |
| 6 | configured to interface with the mySQL database. If that is done, |
| 7 | the driver defines the macro __MYSQL__ for LPC programs and |
| 8 | activates a number of efuns. |
| 9 | |
| 10 | -- Configuration -- |
| 11 | |
| 12 | Create a dedicated user in the mySQL database for the driver. |
| 13 | Enter this username and password in the file pkg-mysql.c, function |
| 14 | mysql_real_connect(), and compile the driver (the username and |
| 15 | password are built into the driver for security reasons). |
| 16 | If you chose to not create either a username and/or a password, |
| 17 | leave the corresponding entry at 0. |
| 18 | |
| 19 | Use mysqladmin to create any databases you want to provide - the |
| 20 | names are later used in the efun db_connect() to connect to |
| 21 | the databases. |
| 22 | |
| 23 | |
| 24 | -- Usage -- |
| 25 | |
| 26 | The idea behind SQL-support is that you can swap large amounts of |
| 27 | data into a database where it can be accessed very easily. |
| 28 | As mySQL "limits" the number of connections to 100 and as every |
| 29 | connection to the mySQL-server takes time, you should use |
| 30 | database serverobjects in your MUD which constantly keep the |
| 31 | connection to the mySQL-server. |
| 32 | |
| 33 | To connect to your mySQL-server, use the efun db_connect(). It |
| 34 | takes only one argument which is the name of the database (which |
| 35 | must exist). The return-value of db_connect() is an integer |
| 36 | representing the unique handle to the database with which you will |
| 37 | identify your connection later. |
| 38 | |
| 39 | To send or retrieve data from this connection, use db_exec(). The |
| 40 | first parameter for all efuns dealing with an open connection is |
| 41 | always the handle and so is the first argument the handle and the |
| 42 | second one the command you want to issue. The return-value is |
| 43 | either 0 if there was an error in your command (this can have |
| 44 | various reasons), otherwise your handle is returned again. A typical |
| 45 | SQL-statement to retrieve data would be like this: |
| 46 | |
| 47 | select aliases.command from aliases where (name = 'mario' AND |
| 48 | alias regexp 'l.*') |
| 49 | |
| 50 | As you know, mySQL accepts either " or ' to classify strings for |
| 51 | parameters. Most likely, you will pass variables and don't know |
| 52 | whether they contain one or more of these key-chars (or even other |
| 53 | chars that need to be converted). mySQL provides a function for |
| 54 | converting just any string into an acceptable argument and this is |
| 55 | implemented in db_conv_string(). |
| 56 | |
| 57 | So the above example with variables looks like this: |
| 58 | |
| 59 | select aliases.command from aliases where (name ='"+ |
| 60 | db_conv_string(name)+"' AND alias regexp '"+ |
| 61 | db_conv_string(mask)+"') |
| 62 | |
| 63 | I left out the db_exec()-stuff, more complete examples will follow. |
| 64 | |
| 65 | After you initiated a statement that should return rows from the |
| 66 | database, use db_fetch() to retrieve the data. db_fetch() returns |
| 67 | the data row by row and not all at once. You need to call it until |
| 68 | it returns 0. THIS IS IMPORTANT! If stop calling db_fetch() before |
| 69 | it reaches the end of data, serious inconsistencies can happen. |
| 70 | |
| 71 | If you used a DELETE- or UPDATE-statement, you cannot call db_fetch(), |
| 72 | but you might be interested in the number of deleted/changed rows |
| 73 | which can be queried with db_affected_rows(). |
| 74 | |
| 75 | After all operations are done in the database, you should use |
| 76 | db_close() to close the connection again. If you are using a |
| 77 | database-server-concept, place it in the remove()-function. |
| 78 | |
| 79 | The SQL-efuns have some built-in optimization-features to speed up |
| 80 | often used connections. To get a list of all open connections to the |
| 81 | mySQL-server, use db_handles() which returns an array of integers |
| 82 | with all open handles. |
| 83 | |
| 84 | |
| 85 | -- Security -- |
| 86 | |
| 87 | Most SQL efuns (unless execute by the master or the simul-efun object) |
| 88 | trigger a privilege_violation ("mysql", "<efun_name>"). If a more |
| 89 | finegrained control is desired, overload the individual efuns with a |
| 90 | nomask simul-efun. |
| 91 | |
| 92 | The unprivileged efuns are: |
| 93 | |
| 94 | db_conv_string() |
| 95 | |
| 96 | |
| 97 | EXAMPLE |
| 98 | A simple server to store aliases could be implemented like this: |
| 99 | |
| 100 | /* |
| 101 | ** CREATION: |
| 102 | ** |
| 103 | ** create table aliases ( |
| 104 | ** name varchar(15) not NULL, |
| 105 | ** alias varchar(20) not NULL, |
| 106 | ** command varchar(255) not NULL, |
| 107 | ** primary key (name, alias)); |
| 108 | */ |
| 109 | |
| 110 | #define DATABASE "mud" |
| 111 | |
| 112 | private int handle; |
| 113 | |
| 114 | public void create() |
| 115 | { |
| 116 | handle = db_connect(DATABASE); |
| 117 | } |
| 118 | |
| 119 | public int remove() |
| 120 | { |
| 121 | if ( handle ) |
| 122 | db_close(handle); |
| 123 | destruct(ME); |
| 124 | return !ME; |
| 125 | } |
| 126 | |
| 127 | public int AddAlias(string alias, string command, object ob) |
| 128 | { |
| 129 | if ( !handle ) |
| 130 | handle = db_connect(DATABASE); |
| 131 | if ( !db_exec(handle, |
| 132 | "insert into aliases (name, alias, command) values " |
| 133 | "('" + getuid(ob) + "','" + db_conv_string(alias) |
| 134 | + "','"+ |
| 135 | db_conv_string(command) + "')") ) |
| 136 | return -1; |
| 137 | return 1; |
| 138 | } |
| 139 | |
| 140 | public int RemoveAlias(string alias, object ob) |
| 141 | { |
| 142 | int res; |
| 143 | |
| 144 | if ( !handle ) |
| 145 | handle = db_connect(DATABASE); |
| 146 | res = db_exec(handle, |
| 147 | "delete from aliases where (name = '"+ |
| 148 | getuid(ob) + "' AND alias = '" |
| 149 | + db_conv_string(alias)+ |
| 150 | "')"); |
| 151 | if ( !res ) |
| 152 | return 0; |
| 153 | res = db_affected_rows(handle); |
| 154 | return (res > 0)?1:-1; |
| 155 | } |
| 156 | |
| 157 | public mixed *QueryAliases(string mask, object ob) |
| 158 | { |
| 159 | mixed *result; |
| 160 | string *tmp; |
| 161 | |
| 162 | if ( !handle ) |
| 163 | handle = db_connect(DATABASE); |
| 164 | if ( !db_exec(handle, |
| 165 | "select aliases.alias, aliases.command from aliases where " |
| 166 | "(name = '" + getuid(ob)+ |
| 167 | "' AND alias regexp '" + db_conv_string(mask) + "')") ) |
| 168 | return ({ }); |
| 169 | result = ({ }); |
| 170 | while ( sizeof(tmp = db_fetch(handle)) ) |
| 171 | result += ({ tmp }); |
| 172 | return result; |
| 173 | } |
| 174 | |
| 175 | public string QueryAlias(string alias, object ob) |
| 176 | { |
| 177 | mixed *result; |
| 178 | string *tmp; |
| 179 | |
| 180 | if ( !handle ) |
| 181 | handle = db_connect(DATABASE); |
| 182 | if ( !db_exec(handle, |
| 183 | "select aliases.command from aliases where " |
| 184 | "(name = '" + getuid(ob)+ |
| 185 | "' AND alias = '" + db_conv_string(alias) + "')") ) |
| 186 | return 0; |
| 187 | result = ({ }); |
| 188 | while ( sizeof(tmp = db_fetch(handle)) ) |
| 189 | result += tmp; |
| 190 | return sizeof(result)?result[0]:0; |
| 191 | } |
| 192 | |
| 193 | |
| 194 | AUTHOR |
| 195 | Mark Daniel Reidel and others. |
| 196 | |
| 197 | HISTORY |
| 198 | mySQL support was added as a package in 3.2.8 and became and |
| 199 | integral driver part in 3.2.9. |
| 200 | LDMud 3.2.11 added a privilege_violation() call for each efun. |
| 201 | |
| 202 | SEE ALSO |
| 203 | pgsql(C), db_affected_rows(E), db_conv_string(E), db_close(E), |
| 204 | db_connect(E), db_exec(E), db_fetch(E), db_handles(E), |
| 205 | db_insert_id(E), db_coldefs(E), db_error(E), privilege_violation(A) |