Graal Forums  

Go Back   Graal Forums > Development Forums > NPC Scripting > Code Gallery
FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 04-21-2009, 10:03 PM
Inverness Inverness is offline
Incubator
Inverness's Avatar
Join Date: Aug 2004
Location: Houston, Texas
Posts: 3,613
Inverness is a jewel in the roughInverness is a jewel in the rough
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(queryfmtstr) {
  if (
temp.fmtstr.type() == 3) {
    
temp.query format2(temp.querytemp.fmtstr);
  }
  
temp.req requestsql(temp.queryfalse);
  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(queryfmtstr) {
  if (
temp.fmtstr.type() == 3) {
    
temp.query format2(temp.querytemp.fmtstr);
  }
  
temp.req requestsql(temp.querytrue);
  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.rowreq.rows)
    
temp.out.add(temp.row[0]);
  return 
temp.out;

Example of current usage:
PHP Code:
public function createrow(rtypestrid) {
  
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)",
    {
ridrtypestrid}
  );
  
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", {rtyperid});
    
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.0;
  
temp.check true;
  
temp.res 0;
  
  if (
acc.type() != || acc.length() < 1)
    return 
error("account is not a string, or length < 1");
  
  
cids = new[this.plyrcharslots];
  
sqlbegin();
  for (
0cids.size(); ++) {
    
cids[i] = this.createdefaultchar(null"*" acc "-" i0);
    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)"
,
        {
ridacc.escape(), cids[0], cids[1], cids[2], cids[3], cids[4]}
      );
    }
  }
  
sqlcommit();
  if (
res)
    return 
rid;
}
public function 
createdefaultchar(stridfullnameraceid) {
  return 
this.createchar(
    
strid5050,
    
555555,
    
00,
    
0.900.100.0750.15,
    
fullnameraceid
  
);
}
public function 
createchar(stridmaxhpmaxmp,
                           
strwilint_agidexluc,
                           
resnormalresmagic,
                           
hitdodgecriticalrecover,
                           
fullnameraceid) {
  
temp.rid 0;
  
temp.contid 0;
  
temp.res 0;
  
  
sqlbegin();
  
rid this.createrow("char"strid);
  if (
rid != null) {
    
contid this.createcont(nullrid);
    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)"
,
        {
ridmaxhpmaxmpmaxhpmaxmp,
         
strwilint_agidexluc,
         
resnormalresmagic,
         
hitdodgecriticalrecover,
         
fullname.escape(), raceidcontid}
      );
    }
  }
  
sqlcommit();
  if (
res)
    return 
rid;
}
public function 
createcont(stridownerid) {
  
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)",
      {
ridownerid}
    );
  }
  
sqlcommit();
  if (
res)
    return 
rid;

__________________
Reply With Quote
  #2  
Old 04-21-2009, 10:39 PM
Gambet Gambet is offline
Registered User
Join Date: Oct 2003
Posts: 2,712
Gambet is on a distinguished road
I wondered when something like this would be released, great job, should definitely help those transitioning over to SQL.
Reply With Quote
  #3  
Old 04-21-2009, 10:56 PM
fowlplay4 fowlplay4 is offline
team canada
fowlplay4's Avatar
Join Date: Jul 2004
Location: Canada
Posts: 5,200
fowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond repute
Has anyone tried/had any success with storing objects inside a blob datatype?

I'm currently too busy to try it out.
__________________
Quote:
Reply With Quote
  #4  
Old 04-21-2009, 11:08 PM
Inverness Inverness is offline
Incubator
Inverness's Avatar
Join Date: Aug 2004
Location: Houston, Texas
Posts: 3,613
Inverness is a jewel in the roughInverness is a jewel in the rough
Quote:
Originally Posted by fowlplay4 View Post
Has anyone tried/had any success with storing objects inside a blob datatype?

I'm currently too busy to try it out.
SQL stores strings, integers, and floating point numbers.
__________________
Reply With Quote
  #5  
Old 04-21-2009, 11:33 PM
fowlplay4 fowlplay4 is offline
team canada
fowlplay4's Avatar
Join Date: Jul 2004
Location: Canada
Posts: 5,200
fowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond reputefowlplay4 has a reputation beyond repute
So no on storing objects inside SQLite's BLOB datatype, thanks.
__________________
Quote:
Reply With Quote
  #6  
Old 04-27-2009, 10:20 AM
Inverness Inverness is offline
Incubator
Inverness's Avatar
Join Date: Aug 2004
Location: Houston, Texas
Posts: 3,613
Inverness is a jewel in the roughInverness is a jewel in the rough
Made some changes, made it so the TSQLRequest is always returned so you can check errors and affectedrows and such. Also made it so if you timeout while waiting for a query it will cause an error and do a rollback.

PHP Code:
// sqlexecute(str query, var[] fmtstr):
//   Execute an SQL a non-request SQL query.
//   Returns TSQLRequest
function sqlexecute(queryfmtstr) {
  if (
temp.fmtstr.type() == 3) {
    
temp.query format2(temp.querytemp.fmtstr);
  }
  
temp.req requestsql(temp.queryfalse);
  if (
temp.req.error != null) {
    echo(
"SQL Error: " temp.req.error);
    echo(
"      Query: " temp.query);
    
serverr.sqliserror true;
  }
  return 
temp.req;
}
// sqlexecutereq(str query, var[] fmtstr):
//   Execute an SQL request query. Returns TSQLRequest
function sqlexecutereq(queryfmtstr) {
  if (
temp.fmtstr.type() == 3) {
    
temp.query format2(temp.querytemp.fmtstr);
  }
  
temp.req requestsql(temp.querytrue);
  if (
temp.req.error != null) {
    echo(
"SQL Error: " temp.req.error);
    echo(
"      Query: " temp.query.escape());
    
serverr.sqliserror true;
    return 
temp.req;
  }
  if (!
temp.req.completed && !waitfor(temp.req"onReceiveData"60)) {
    echo(
"SQL Timeout: 60 seconds");
    echo(
"       Query: " temp.query.escape());
    
serverr.sqliserror true;
  }
  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.rowreq.rows)
    
temp.out.add(temp.row[0]);
  return 
temp.out;

__________________
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +2. The time now is 06:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Copyright (C) 1998-2019 Toonslab All Rights Reserved.