SQL Wrapper
Softcode is ugly. So is SQL. But when you try and combine them, that's a real recipe for disaster. There are so many things to watch for when bridging the gap between mushcode and the database. You get tired pretty quickly of all the sqlescape()s you have to do, of losing where you are in a query because of too much interpolated softcode, escaping all the commas so that the sql() function doesn't think you're passing too many arguments, etc etc etc. The list goes on and on, and I'm sure you can think of more problems to add. This whole process has bugged enough people that it's started to become a fad to use a wrapper. That's what I'm here to describe and give you. With a proper wrapper, all your mushcode can stay mushcode, with no hint of SQL or sqlescape(), and all your SQL can stay SQL, with no hint of mushcode.
Functions:
add(),
before(),
config(),
elements(),
eval(),
firstof(),
if(),
inc(),
localize(),
map(),
mapsql(),
regeditall(),
setq(),
setr(),
sql(),
sqlescape(),
strlen(),
switch(),
u(),
v().
Features:
#lambda.
Instructions
Copy and paste the below code into a compatible MUSH or MUX.
MUSHCode for SQL Wrapper
@@ SQL Wrapper object.
@@
@@ Version: 1.2.
@@
@@ Originally authored by: Walker@M*U*S*H
@@
@@ Documentation in docff format by Cheetah@M*U*S*H
@@
@@ Code placed into the public domain. Friday, June 13th, 2008.
@@ Documentation placed into public domain. Tuesday, January 12th, 2010.
@@
@create SQL Wrapper
@set SQL Wrapper = NO_COMMAND
@DESCRIBE SQL Wrapper=SQL Wrapper. Detailed in a CPO post: http://community.pennmush.org/story/sql-prepared-statements-mushcode
&GLOB2LIKE SQL Wrapper="[regeditall(sqlescape(%0),\[_\%\],\\$0,((?:\[^\\\\\]|^)(?:\\\\\\\\\\\\\\\\)*)(\[?\\*\]),$1[switch($2,\\?,_,\\*,\%)])]"
&GLOB2LIKE`CC SQL Wrapper=glob_pattern r:string:like_pattern func nodirect
&GLOB2LIKE`DOC SQL Wrapper=Turns GLOB_PATTERN (with *s and ?s) into a glob pattern fit for SQL's LIKE: * -> "\%" and "?" -> "_". This can be automatically applied to your bind variable if you prefix the ? with a _. (e.g: _?) - It still does an sqlescape(), so your query is safe when you use _?.
&MAPQUERY SQL Wrapper=localize(setq(i,0)[mapsql(%0,u(prepare,%1,%2,%3,%4,%5,%6,%7,%8,%9),firstof(elements(%1,2,:),%r),elements(%1,3,:))])
&MAPQUERY`CC SQL Wrapper=attrname_options:name:*:args r:string:mapped_query
&MAPQUERY`DOC SQL Wrapper=With ATTRNAME_OPTIONS formatted as <attrname>\[:<rowsep>\[:<dofieldnames>\]\], performs an SQL query, mapping ARGS to its bind variables in SQL`NAME, using either bind method. Result rows are passed to <attrname> as \%1-\%9, with row number as \%0. (Much like mapsql()). <rowsep> defaults to \%r, and <dofieldnames> makes the first call to <attrname> have a rownum of 0 and \%1-\%9 are the names of the columns.%r%tSee `query' for details.
&PREPARE SQL Wrapper=regeditall(firstof(v(sql`[before(%0,:)]`[config(sql_platform)]),v(sql`[before(%0,:)])),(\[_@\]?)\\?(?:<(\\d+)>)?,u(switch($1,_,glob2like,@,sqlarray,sqlescape),v(if(strlen($2),add($2,1),setr(i,inc(%qi))))))
&PREPARE`CC SQL Wrapper=name:*:args r:string:query nodirect qreg:i
&PREPARE`DOC SQL Wrapper=Fills in ARGS in a query in SQL`NAME, taking care of processing prefixes for ? and making sure all data is run through sqlescape().
&QUERY SQL Wrapper=localize(setq(i,0)[sql(eval(me,prepare),firstof(elements(%0,2,:),%b),firstof(elements(%0,3,:),%b))])
&QUERY`CC SQL Wrapper=name_options:*:args r:string:query_result
&QUERY`DOC SQL Wrapper=With NAME_OPTIONS formatted as <name>\[:<rowsep>\[:<colsep>\]\], perform a query that returns results, with result rows separated by <rowsep>, cols by <colsep> (Both a space by default). ARGS are mapped onto the SQL query in `SQL`<name>' as follows:%r%rThe value of the attribute is an SQL query that matches one of two formats:%r%t1) Position-based bind variables: SQL queries that use a ? in place of non-hardcoded variables.%r%t2) Number-based bind variables: SQL queries that use ?<0> through ?<7> for bind variable position.%r%rSQL-Platform specific queries can be created by using `SQL`name`<platform>'. platform is the value of config(sql_platform). If it exists, it will be used instead of `SQL`name'.%r%r?s can be prefixed by _ to tell the parser to turn MUSH-style globs into SQL-style globs, as per `glob2like'.%r%r?s prefixed by a @ tell the parser that that argument is a space-separated list intended to be turned into an SQL Array. e.g, it turns '1 2 3' into '("1", "2", "3")'. (Remember, in SQL, "1" is the same as 1 when it works with numbers.) This is useful for "SELECT * FROM foo WHERE fooid IN @?" queries.
&SQL SQL Wrapper=SQL tree. no_command.
@set SQL Wrapper/SQL=no_command
&SQLARRAY SQL Wrapper=([map(#lambda/"\[sqlescape(\%0)\]",%0,%b,\,)])
&SQLARRAY`CC SQL Wrapper=list r:string:sql_array func nodirect
&SQLARRAY`DOC SQL Wrapper=Turns LIST into an SQL array. Used to implement @? in `query' and co.
&SQLESCAPE SQL Wrapper="[sqlescape(%0)]"
&SQLESCAPE`CC SQL Wrapper=string r:string:escaped_string func nodirect
&SQLESCAPE`DOC SQL Wrapper=Quick wrapper around sqlescape(), turning STRING into an escaped version safe for SQL.
&UPDATE SQL
Wrapper=localize(if(strlen(setr(0,eval(me,query))),%q0,firstof(sql(select last_insert_id()),1)))
&UPDATE`CC SQL Wrapper=name_options:*:args r:string:status_or_result
&UPDATE`DOC SQL Wrapper=Identical to `query', with the same format for NAME_OPTIONS and ARGS, but designed for queries that perform updates - If the result string is empty, `update' assumes success, and returns 1. For details see the documentation for `query'.