blob: 4fb6deed52760e30c806e633431cb0d43ffd1976 [file] [log] [blame]
MG Mud User88f12472016-06-24 23:31:02 +02001CONCEPT
2 mysql - mySQL support
3
4DESCRIPTION
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
97EXAMPLE
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
194AUTHOR
195 Mark Daniel Reidel and others.
196
197HISTORY
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
202SEE 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)