| Inverness |
04-21-2009 10:03 PM |
SQL Wrapper Class (util_sql)
This is the current util_sql class I'm using for wrapping SQL stuff. Functions in the class are documented.
Main Features: - Error reporting for SQL queries
- Automatic waiting for SQL request completion
- Uses format2(str, array) for placing data into the query string
- Nested SQL transaction control
PHP Code:
// sqlexecute(str query, var[] fmtstr):
// Execute an SQL a non-request SQL query. Returns true
// on success, false on error.
function sqlexecute(query, fmtstr) {
if (temp.fmtstr.type() == 3) {
temp.query = format2(temp.query, temp.fmtstr);
}
temp.req = requestsql(temp.query, false);
if (temp.req.error != "") {
echo("SQL Error: " @ temp.req.error);
echo(" Query: " @ temp.query);
serverr.sqliserror = true;
return false;
}
return true;
}
// sqlexecutereq(str query, var[] fmtstr):
// Execute an SQL request query. Returns a TSQLRequest
// object if success, null on error.
function sqlexecutereq(query, fmtstr) {
if (temp.fmtstr.type() == 3) {
temp.query = format2(temp.query, temp.fmtstr);
}
temp.req = requestsql(temp.query, true);
if (temp.req.error != "") {
echo("SQL Error: " @ temp.req.error);
echo(" Query: " @ temp.query);
serverr.sqliserror = true;
return null;
}
if (!temp.req.completed && !waitfor(temp.req, "onReceiveData", 5))
return null;
return temp.req;
}
// sqlbegin():
// Begins a nested transacton. Returns true if starting a
// new transaction, false if one is already started.
function sqlbegin() {
if (serverr.sqltransactiondepth < 1) {
serverr.sqltransactiondepth = 1;
serverr.sqliserror = false;
this.sqlexecute("BEGIN");
return true;
}
else {
serverr.sqltransactiondepth++;
return false;
}
}
// sqlerror():
// If called, the last sqlcommit() will cause a rollback of
// the transaction. This should be called from anywhere
// inside a sqlbegin() and sqlcommit().
function sqlerror() {
serverr.sqliserror = true;
}
// sqlcommit():
// Returns true if commit is successful, false if error or
// rollback.
function sqlcommit() {
if (serverr.sqltransactiondepth > 1) {
serverr.sqltransactiondepth--;
return !serverr.sqliserror;
}
else {
serverr.sqltransactiondepth = 0;
temp.ret = !serverr.sqliserror;
if (serverr.sqliserror) {
if (!this.sqlexecute("ROLLBACK")) {
temp.ret = false;
}
}
else {
if (!this.sqlexecute("COMMIT")) {
temp.ret = false;
}
}
serverr.sqliserror = false;
return temp.ret;
}
}
// sqlgettablenames() - returns array:
// Returns array of table names in the database.
function sqlgettablenames() {
temp.req = this.sqlexecutereq(
"SELECT tbl_name FROM sqlite_master WHERE type='table'", true
);
temp.out = {};
for (temp.row: req.rows)
temp.out.add(temp.row[0]);
return temp.out;
}
Example of current usage:
PHP Code:
public function createrow(rtype, strid) {
temp.rid = 0;
temp.res = 0;
if (this.types.index(rtype) < 0)
return error("%s is not a valid row type", rtype);
// TODO: Add code for reusing deleted IDs
rid = serverr.mud2idcounter;
strid = (str == null)? "NULL": "'" @ strid.escape() @ "'";
sqlbegin();
res = sqlexecute(
"INSERT INTO mud2master VALUES (%s, '%s', %s, 0, 0)",
{rid, rtype, strid}
);
sqlcommit();
if (res)
return serverr.mud2idcounter++;
}
public function deleterow(rid) {
temp.req = 0;
temp.rtype = 0;
if (rid.type() != 0)
return;
sqlbegin();
req = sqlexecutereq("SELECT * FROM mud2master WHERE id=%s", {rid});
if (req.rows.size() == 1) {
rtype = req.rows[0].type;
sqlexecute("DELETE FROM mud2type%s WHERE id=%s", {rtype, rid});
sqlexecute("UPDATE mud2master SET deleted=1 WHERE id=%s", {rid});
}
else {
sqlerror();
}
return sqlcommit();
}
public function createplyr(acc) {
temp.rid = 0;
temp.cids = 0;
temp.i = 0;
temp.check = true;
temp.res = 0;
if (acc.type() != 1 || acc.length() < 1)
return error("account is not a string, or length < 1");
cids = new[this.plyrcharslots];
sqlbegin();
for (i = 0; i < cids.size(); i ++) {
cids[i] = this.createdefaultchar(null, "*" @ acc @ "-" @ i, 0);
if (cids[i] < 1) {
check = false;
break;
}
}
if (check) {
rid = this.createrow("plyr", null);
if (rid > 0) {
res = sqlexecute(
"INSERT INTO mud2typeplyr VALUES
(%s, '%s', 0, %s, %s, %s, %s, %s)",
{rid, acc.escape(), cids[0], cids[1], cids[2], cids[3], cids[4]}
);
}
}
sqlcommit();
if (res)
return rid;
}
public function createdefaultchar(strid, fullname, raceid) {
return this.createchar(
strid, 50, 50,
5, 5, 5, 5, 5, 5,
0, 0,
0.90, 0.10, 0.075, 0.15,
fullname, raceid
);
}
public function createchar(strid, maxhp, maxmp,
str, wil, int_, agi, dex, luc,
resnormal, resmagic,
hit, dodge, critical, recover,
fullname, raceid) {
temp.rid = 0;
temp.contid = 0;
temp.res = 0;
sqlbegin();
rid = this.createrow("char", strid);
if (rid != null) {
contid = this.createcont(null, rid);
if (contid != null) {
res = sqlexecute(
"INSERT INTO mud2typechar VALUES (
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s,
%s, %s,
%s, %s, %s, %s,
0, '%s', %s, 0, 0, %s)",
{rid, maxhp, maxmp, maxhp, maxmp,
str, wil, int_, agi, dex, luc,
resnormal, resmagic,
hit, dodge, critical, recover,
fullname.escape(), raceid, contid}
);
}
}
sqlcommit();
if (res)
return rid;
}
public function createcont(strid, ownerid) {
temp.rid = 0;
temp.res = 0;
if (ownerid < 0) {
return error("ownerid < 0");
}
sqlbegin();
rid = this.createrow("cont", strid);
if (rid != null) {
res = sqlexecute(
"INSERT INTO mud2typecont VALUES (%s,0)",
{rid, ownerid}
);
}
sqlcommit();
if (res)
return rid;
}
|
