| Xaraya | M.L. Lutolf |
| Request for Comments: 0053 | Xaraya Development Group |
| Category: Standards Track | September 2004 |
RFC-0053: Structured Queries in Xaraya
This document specifies an Xaraya standards track protocol for the Xaraya community, and requests discussion and suggestions for improvements. Please refer to the current edition of the “Xaraya Official Standards” (STD 1) for the standardization state and status of this protocol. Distribution of this memo is unlimited.
Copyright © The Digital Development Foundation (2004). All Rights Reserved.
1 This RFC describes an implementation of structured queries through query abstraction, based on the ideas presented in rfc35.
2 Structured queries are a way of making the Query Abastraction has been made necessary to give us the hability to generate efficient SQL queries which are portable across all our Database Pool. It will provide us a way to create queries without any preocupations about the underlying storage system.
3 Typical life cycle of a query:
Assembly Phase:
- define the type of query (SELECT, INSERT, etc.)
- define the tables
- define the fields involved
- define conditions and filters
- how is the data ordered and presented (for SELECTS)
------------------------------------------------------------------------
Execution Phase:
- run the query communicate with the database
------------------------------------------------------------------------
Output Phase:
- collect the output
- rework as necessary (add field names, do security checks, etc.)
- send to output templates or
- send to another application for further processing
4 [N.B.: Xaraya uses adodb to communicate with the underlying database(s). The structured query does this at one specific point (class method), making it relatively simple to adjust should the database layer change.]
5 Structured queries are essentially a convenience mechanism, which allows Xaraya to work with database queries in a more flexible and dynamic way in a number of areas:
6 Defaults
13 Dynamic Queries
17 Storage and Retrieval
20 Query Algebra
24 The objective in implementing structured queries is to make query manipulation more flexible and dynamic, while not making the API to do so more complicated than what is currently used, i.e. concatenated strings.
25 This means we are not interested in creating a complete query manipulation language per se. The implementation currently does not support CREATE clauses for instzance, although it can be extended to do so
26 Structured queries are implemented using the xarQuery class. The implementation chosen purposely works with only this single class, which represents a structured query object. All manipulation of the query is performed using this class's methods and properties.
27 A simple example of a structured query:
Assembly Phase:
$q = new xarQuery([type],[table],[fields]); Set the query up
$q->addtable(...); Add more tables
$q->addfield(...); Add more fields
$q->eq(...); Add an EQ clause
------------------------------------------------------------------------
Execution Phase:
$q->run(); Run the query
or
if (!$q->run()) return; Run and return an exception of failed
------------------------------------------------------------------------
Output Phase:
$data['rows'] = $q->output(); Send the output to a template
or
$myobject = new Object($q->output()); Use the output to create an object
or
foreach ($q->output() as $key => $value) { Use the output in a loop
....
}
33 Example: Recalling a user's data from the database. The data of the user with the uname $this->uname is returned as an array.
$q = new xarQuery('SELECT',$this->rolestable);
$q->eq('xar_uname',$this->uname);
if (!$q->run()) return;
$foo = $q->output();
34 The syntax of the class call is
$q = new xarQuery('SELECT'|'INSERT'|'UPDATE'|'DELETE',
{array of query tables},
{array of fields}
);
35 The class methods (to be completed):
36 With the constructor method xarQuery() we can create a new structured query object that subsequently be elaborated on by the objects methods.
37 In general the constructor will include only the type parameter, which defines the type of query created (SELECT, UPDATE etc.). The table and fields parameters are used as a shorthand for simple SELECT queries.
38
40 String whose value can be SELECT, UPDATE, INSERT or DELETE. The default is SELECT.
42 String representing a database table name.
44 An array of database fields as strings or arrays. If [fieldx] is a string, it must correspond to a database field that is uniquely identified, such as "mytable.foo1".
45 If [fieldx] is an array, it can have the form described in the addfields() method.
46 Note that in the case of a single database field, this parameter can also be a string representing the field's name.
47
$q = new xarQuery(); // creates an empty query object
$q = new xarQuery('INSERT'); // creates a query object of type INSERT
$q = new xarQuery('SELECT','xar_roles'); // creates a query object on the roles table
$q = new xarQuery('SELECT','xar_roles','xar_name');
// adds a single field to the constructor
$q = new xarQuery('SELECT','xar_roles',array('xar_uid','xar_name'));
// adds a two fields to the constructor
48 A longer way of writing the last example would be:
$q = new xarQuery('SELECT');
$q->addtable('xar_roles');
$q->addfield('xar_uid');
$q->addfield('xar_name');
49 The open() method opens a database connection. In adodb it is equivalent to
$this->dbconn = xarDBGetConn();
50 The database connection is created in the xarQuery() constructor method. This method is used after unserializing a stored query and before running it.
51
53 The connection to be opened. If the connection already exists it is opened. If it does not exist it is created.
54
$q = unserialize(xarSessionGetVar('foo'));
$q->open();
$q->run();
55 The close() method closes a database connection.
56
58 The connection to be closed
59
$q->close();
60 The openconnection() method closes a database connection.
61
63 The connection to be closed
64
$q->close();
65 The addfield() method adds a single field to the current query. This method is used as a shorthand for adding fields to a SELECT query. For SELECT queries it is also possible to use the addfields() method.
66
68 A string representing the name of a database field to be added to the query. The parameter can have the form "foo AS bar", where bar is the alias of the field foo. If the alias is given it will be used when generating output with the output() method. Only SELECT queries use the alias.
70 The value to be assigned to the field. The value must correspond to the field type in the database, or to a type that PHP can convert to the field type. The value is used for UPDATE and INSERT queries. Queries of the type SELECT or DELETE ignore it
72 An array representing the database field to be added to the query. It has the general form:
array('name'=>'mytable.foo1'[,'value'=>'bar'[,'alias'=>'foobar']])
73 As noted above, the value is only used by INSERTS and UPDATES, while the alias is only used by SELECTS.
74
$q = new xarQuery('INSERT','xar_roles');
$q->addfield('xar_name','Sharon Stone');
$q->addfield('xar_uname','moviestar');
$q->qecho();
displays
INSERT INTO xar_roles (xar_name, xar_uname) VALUES ('Sharon Stone', 'moviestar')
$q = new xarQuery('SELECT','xar_roles');
$q->addfield('xar_uname');
$q->eq('xar_name','Sharon Stone');
$q->run();
$row = $q->row();
echo $row['uname'];
displays
moviestar
75 The addfields() method adds an array of fields to the current query. This method is used as a shorthand for adding fields to a SELECT query. For other query types it is better to use the addfield() method.
76
78 An array of database fields as strings or arrays. If [fieldx] is a string, it must correspond to a database field that is uniquely identified, such as "mytable.foo1".
79 If [fieldx] is an array, it can have the form
array('name'=>'mytable.foo1'[,'value'=>'bar'[,'alias'=>'foobar']])
80 See the addfield() method for details.
81
$q = new xarQuery('SELECT','mytable');
$q->addfields(array('foo1','foo2','foo3'));
$q->run();
82 The addtable() method adds a single table to the current query. This method is used as a shorthand for adding tables to a SELECT query. For a shorthand notation to add tables to a SELECT query, you can also use the addtables() method.
83
85 A string representing the name of a database table. The name can have the form "foo bar" where bar is the alias of foo. Aliases are used to help differentiate between fields of the same name in different tables.
87 A string representing the alias of a database table.
89 An array representing the name of a database table. The array has the form:
array('name'=>'mytable'[,'alias'=>'tablealias'])
The name and alias correspond to the form "foo bar" given in the previous point.90 The following example selects the uids of all the groups the user Sharon Stone is a member of. Note the alternate syntaxes in the two addtable() methods.
$q = new xarQuery('SELECT',);
$q->addfield('r.xar_parentid');
$q->addtable('xar_roles','r');
$q->addtable('xar_rolemembers rm');
$q->join('r.xar_uid','rm.xar_uid');
$q->eq('xar_name','Sharon Stone');
$q->qecho();
displays
SELECT r.xar_parentid FROM xar_roles r, xar_rolemembers rm
WHERE r.xar_uid = rm.xar_uid AND xar_name = 'Sharon Stone'
91 The addtables() method adds an array of tables to the current query. This method is used as a shorthand for adding tables to a SELECT query. For other query types, and also for more complicated SELECTs, it is better to use the addtable() method.
92
94 An array of database tables as strings or an array. If [tablex] is a string, it must correspond to a database table.
95 If [tablex] is an array, it can have the form
array('name'=>'mytable'[,'alias'=>'tablealias'])
96 See the addtable() method for details.
97
...
$q->addtables(array('table1','table2','table3'));
...
98 The getfield() method returns a field in a query or NULL if the field does not exist.
99
101 A string representing the name or alias of a field in the query.
102
...
$q->getfield('foo');
...
103 The removefield() method removes a field from a query or does nothing if the field does not exist.
104
106 A string representing the name or alias of a field in the query.
107
...
$q->removefield('foo');
...
108 The join() method removes a field from a query or does nothing if the field does not exist.
109
111 Strings representing the name or alias of fields in two tables in the query that will be joined.
112 The following example selects the uids of all the groups the user Sharon Stone is a member of:
$q = new xarQuery('SELECT',);
$q->addfield('r.xar_parentid');
$q->addtable('xar_roles','r');
$q->addtable('xar_rolemembers rm');
$q->join('r.xar_uid','rm.xar_uid');
$q->eq('xar_name','Sharon Stone');
$q->qecho();
displays
SELECT r.xar_parentid FROM xar_roles r, xar_rolemembers rm
WHERE r.xar_uid = rm.xar_uid AND xar_name = 'Sharon Stone'
113 The settable() method is a convenience method that clears all the query's current tables and adds a single table. It is an application of the cleartables() method followed by the addtable() method. See the addtable() method for details on the syntax.
114 The eq() method adds an EQUALS condition to a query.
115
117 String representing the name or alias of a field.
119 The value to be assigned to the field as a condition. The value must correspond to the field type in the database, or to a type that PHP can convert to the field type.
120 The following example returns a record from the roles table corresponding to the current user.
$q = new xarQuery('SELECT','xar_roles');
$q->eq('xar_uid', xarSessionGetVar('uid'));
$q->run();
121 The following example returns all records from the roles table that represent groups.
$q = new xarQuery('SELECT','xar_roles');
$q->eq('xar_type', 1);
$q->run();
122 The ge() method adds a GREATER THAN condition to a query.
123
125 String representing the name or alias of a field.
127 The value to be assigned to the field as a condition. The value must correspond to the field type in the database, or to a type that PHP can convert to the field type.
128 The following example returns all records from the privileges table that represent READ access or higher.
$q = new xarQuery('SELECT','xar_roles');
$q->ge('xar_level', 200);
$q->run();
129 The gt() method adds a GREATER THAN condition to a query.
130
132 String representing the name or alias of a field.
134 The value to be assigned to the field as a condition. The value must correspond to the field type in the database, or to a type that PHP can convert to the field type.
135 The following example returns all records from the privileges table that represent READ access or higher. Same result as the example in ge().
$q = new xarQuery('SELECT','xar_privileges');
$q->gt('xar_level', 100);
$q->run();
136 The in() method adds an IN condition to a query.
137
139 String representing the name or alias of a field.
141 An array of values to be compared to the field as a condition. The values must correspond to the field type in the database, or to a type that PHP can convert to the field type.
142 The following example returns the records from the roles table corresponding to users that are not validated or pending approval:
$q = new xarQuery('SELECT','xar_roles');
$q->eq('xar_type', 0);
$q->in('xar_state', array(ROLES_STATE_NOTVALIDATED,ROLES_STATE_PENDING));
$q->run();
143 The le() method adds a LESS THAN OR EQUAL condition to a query.
144
146 String representing the name or alias of a field.
148 The value to be assigned to the field as a condition. The value must correspond to the field type in the database, or to a type that PHP can convert to the field type.
149 The following example returns all records from the roles table that represent users that are deleted, inactive or not validated.
$q = new xarQuery('SELECT','xar_roles');
$q->le('xar_state', ROLES_STATE_NOTVALIDATED);
$q->run();
150 The like() method adds a LIKE condition to a query. This condition is used to create simple conditions based on comparisons for string fields.
151
153 String representing the name or alias of a field.
155 The value to be assigned to the field as a condition. The value must correspond to the field type in the database, or to a type that PHP can convert to the field type.
156 Adding the % character to the value turns it into a wild card, as shown in the example below.
157 The following example returns all records from the roles table that represent roles whose name begin with "John".
$q = new xarQuery('SELECT','xar_roles');
$q->like('xar_name', 'John%');
$q->run();
158 The lt() method adds a LESS THAN condition to a query.
159
161 String representing the name or alias of a field.
163 The value to be assigned to the field as a condition. The value must correspond to the field type in the database, or to a type that PHP can convert to the field type.
164 The following example returns all records from the roles table with a uid less than 5.
$q = new xarQuery('SELECT','xar_roles');
$q->lt('xar_xaruid', 5);
$q->run();
165 The ne() method adds an NOT EQUALS condition to a query.
166
168 String representing the name or alias of a field.
170 The value to be assigned to the field as a condition. The value must correspond to the field type in the database, or to a type that PHP can convert to the field type.
171 The following example returns the records from the roles table corresponding to groups.
$q = new xarQuery('SELECT','xar_roles');
$q->ne('xar_type', 0);
$q->run();
172
179 The output() method returns the output of a query as a 2 dimensional array where the keys of row elements corespond to the field names in the database. If the fields were defined with aliases, the aliases are used as the keys for the row elements.
180 The output() method works with SELECT statements. If the query finds no records the output method will return an empty array. To retrieve a single output row use the row() method.
181
183 The following example selects and displays some data from the roles module.
$q = new xarQuery('SELECT','xar_roles','xar_name');
$q->lt('xar_uid',4);
$q->run();
echo var_dump($q->output());
displays:
array(3) {
[0]=>array(1) {["xar_name"]=>string(9) "Everybody"}
[1]=>array(1) {["xar_name"]=>string(9) "Anonymous"}
[2]=>array(1) {["xar_name"]=>string(5) "Admin"}
}
184 The qecho() method echos a query statement to the screen. This method is used for debugging queries. Note that the output of this method will vary according to whether the query has actually been run, as illustrated below.
185
187 The following example displays a SELECT statement that queries the xar_roles table.
$q = new xarQuery('SELECT','xar_roles');
$q->eq('xar_name','Anonymous');
$q->qecho();
displays:
SELECT * FROM xar_roles WHERE xar_name = 'Anonymous'
188 The same example displays lists the fields by name once the query has actually been run.
$q = new xarQuery('SELECT','xar_roles');
$q->eq('xar_name','Anonymous');
$q->run();
$q->qecho();
displays:
SELECT xar_uid, xar_name, xar_type, xar_users, xar_uname, xar_email, xar_pass,
xar_date_reg, xar_valcode, xar_state, xar_auth_module FROM xar_roles WHERE xar_name = 'Anonymous'
189 The following example displays an UPDATE statement that updates the xar_roles table.
$q = new xarQuery('UPDATE','xar_roles');
$q->addfield('xar_state',ROLES_STATE_ACTIVE);
$q->eq('xar_name','Sharon Stone');
$q->qecho();
displays:
UPDATE xar_roles SET xar_state = 3 WHERE xar_name = 'Sharon Stone'
190 The row() method returns a single output row of a query as a 1 dimensional array where the keys of row elements corespond to the field names in the database. If the fields were defined with aliases, the aliases are used as the keys for the row elements.
191 The row() method works with SELECT statements. If the query finds no records the output method will return an empty array. To retrieve more than one output row at a time use the output() method.
192
194 An integer that refers to the row number of the output record to be retrieved. The default value is rowno = 0, or the first row in the output recordset.
195 Note that since the sequence of query output may be database dependent and the query itself may include sorting directives, in general nothing can be said about the sequence in which the output arrives. It is the responsibility of the programmer to ensure the rowno parameter is a valid number.
196 The following example selects and displays some data of the Anonymous user in the roles data.
$q = new xarQuery('SELECT','xar_roles',array('xar_uid','xar_name'));
$q->eq('xar_name','Anonymous');
$q->run();
echo var_dump($q->row());
displays:
array(2) {
["xar_uid"]=> string(1) "2"
["xar_name"]=> string(9) "Anonymous"
}
197 The run() method assembles and executes a a query. If a string with a statement is given it is directly executed. If $flag is 1 then the method output() contains the resultset. If $flag is 0 then the public variable $result contains the adodb resultset.
198
200 String representing a valid SQL statement. If this parameter is not empty, the run method executes it. This is equivalent to
$this->dbconn->Execute($statement);
In this case the query output can be retrieved as $q->result. No output is available to the output() method.202 If this parameter is set to true or 1, the query output is available via the output() method.
203 If this parameter is set to false or 0, the query output can be gotten from the public variable $q->result.
204 The default value for this parameter is true.
205 The following example inserts a record into the xar_rolemembers table by executing a simple SQL statement. The code below it is equivalent.
$q = new xarQuery();
$q->run("INSERT INTO xar_rolemembers (xar_uid, xar_parentid) VALUES (301, 300)");
$q = new xarQuery('INSERT','xar_rolemembers');
$q->addfield('xar_parentid',300);
$q->addfield('xar_uid',301);
$q->run();
206 The following example gets the record of the current user in the xar_roles table by executing a simple SQL statement. The code below it is equivalent.
$q = new xarQuery();
$q->run("SELECT * FROM xar_roles WHERE xar_uid = " . xarSessionGetVar('uid'));
$q = new xarQuery('SELECT','xar_roles');
$q->eq('xar_uid',xarSessionGetVar('uid'));
$q->run();
Note that in the second case xarQuery by default assumes all fields are to be included in the SELECT statement. Furthermore, although the SELECT executed is the same in both cases, running the raw statement means the output will be available as $q->result, whereas in the second case it will be available through the output() method.207 The tostring() method returns a query statement.
208
210 The following example returns a SELECT statement that queries the xar_roles table.
$q = new xarQuery('SELECT','xar_roles');
$q->eq('xar_name','Anonymous');
$q->tostring();
returns:
SELECT * FROM xar_roles WHERE xar_name = 'Anonymous'
211 The addorder() method adds a ORDER clause to a query. If more than one addorder() is applied, the multiple sorts are applied in the order they are enocuntered. If no prior sort exists, addorder behaves like the setorder() method.
212
214 String representing the name or alias of a field.
216 String representing the direction of the sort. Allowed values are ASC (ascending) and DESC (descending). The default value is ASC.
217
$q = new xarQuery('SELECT','xar_security_levels','xar_leveltext');
$q->setrowstodo(3);
$q->addorder('xar_leveltext','DESC');
$q->run();
echo var_dump($q->output());
displays:
array(3) {
[0]=> array(1) { ["xar_leveltext"]=> string(11) "ACCESS_READ" }
[1]=> array(1) { ["xar_leveltext"]=> string(15) "ACCESS_OVERVIEW" }
[2]=> array(1) { ["xar_leveltext"]=> string(11) "ACCESS_NONE" }
}
218 The clearconditions() method removes all the conditions from a query.
219
221
...
$q->clearconditions();
...
222 The clearfield() method removes a single field from a query.
223
225 String representing the name or alias of a field.
226
$q = new xarQuery('SELECT','xar_roles');
$q->addfield('xar_uid');
$q->addfield('xar_name');
$q->eq('xar_name','Anonymous');
$q->qecho();
displays
SELECT xar_uid, xar_name FROM xar_roles WHERE xar_name = 'Anonymous'
$q->clearfield('xar_name');
$q->qecho();
displays
SELECT xar_uid FROM xar_roles WHERE xar_name = 'Anonymous'
227 The clearfields() method removes all the fields from a query.
228
230
$q = new xarQuery('SELECT','xar_roles');
$q->addfield('xar_uid');
$q->addfield('xar_name');
$q->eq('xar_name','Anonymous');
$q->qecho();
displays
SELECT xar_uid, xar_name FROM xar_roles WHERE xar_name = 'Anonymous'
$q->clearfields();
$q->qecho();
displays
SELECT * FROM xar_roles WHERE xar_name = 'Anonymous'
Note in the last example the fact that if all fields are cleared the query will default to *, i.e. all fields will be chosen.231 The getrows() method returns the total number of rows that a query of type SELECT will return, regardless of whether a LIMIT clause has been defined. (see the setrowstodo() method.)
232
234
$q = new xarQuery('SELECT','xar_security_levels');
$q->run();
$q->getrows();
returns:
10
235 The getrows() method returns the number of rows that a query of type SELECT will return. This method only returns a non zero result if a LIMIT clause has been defined for the query. See also the setrowstodo() method.
236
238
$q = new xarQuery('SELECT','xar_security_levels');
$q->setstartat(3);
$q->setrowstodo(5);
$q->run();
$total1 = count($q->output());
$total2 = $q->getrowstodo();
returns:
$total1 = 5; (records 3 to 8)
$total2 = 5;
239 The getstartat() method returns the row number query output begins at. This applies to SELECT statements with LIMIT clauses. A typical example is the input for a pager that tells it which rows are being displayed on a page. See also the setstartat() method.
240
242
$q = new xarQuery('SELECT','xar_roles');
$q->setstartat(100);
$q->setrowstodo(20);
echo $q->getstartat();
displays:
100
243 The gettype() method returns the type of the query, e.g. SELECT, UPDATE etc. See also the settype() method.
244
246
$q = new xarQuery('SELECT','xar_roles');
$q->eq('xar_name','Anonymous');
echo $q->gettype();
displays:
SELECT
247 The nolimits() method disables the LIMIT clause in a SELECT statement, if such a clause is defined. The SELECT statement will return all the records found. By default limits are in effect unless disabled by this method. See also the uselimits() method.
248
250
$q = new xarQuery('SELECT','xar_security_levels');
$q->setstartat(3);
$q->setrowstodo(5);
$q->run();
$total1 = count($q->output());
$q->nolimits();
$q->run();
$total2 = count($q->output());
returns:
$total1 = 5; (records 3 to 8)
$total2 = 10; (all the records)
251 The setorder() method sets the ORDER clause to a query. If such a clause already exists it is replaced. See also the addorder() method.
252
254 String representing the name or alias of a field.
256 String representing the direction of the sort. Allowed values are ASC (ascending) and DESC (descending). The default value is ASC.
257
$q = new xarQuery('SELECT','xar_security_levels','xar_leveltext');
$q->setrowstodo(3);
$q->setorder('xar_leveltext','DESC');
$q->run();
echo var_dump($q->output());
displays:
array(3) {
[0]=> array(1) { ["xar_leveltext"]=> string(11) "ACCESS_READ" }
[1]=> array(1) { ["xar_leveltext"]=> string(15) "ACCESS_OVERVIEW" }
[2]=> array(1) { ["xar_leveltext"]=> string(11) "ACCESS_NONE" }
}
258 The setrowstodo() method sets the number of rows that a query of type SELECT will return. This method, together with the setstartat() method, defines a LIMIT clause for the query. See also the getrowstodo() method.
259
261 A number that defines how many rows the query will return. If the total number of rows the query selects is smaller than rowno, then the method has no effect.
262
$q = new xarQuery('SELECT','xar_security_levels');
$q->setstartat(3);
$q->setrowstodo(5);
$q->run();
$total1 = count($q->output());
returns:
$total1 = 5; (records 3 to 8)
263 The getstartat() method sets the row number query output begins at. This applies to SELECT statements with LIMIT clauses. A typical example is the input for a pager that tells it which rows are being displayed on a page. See also the getstartat() method.
264
266 Number representing the row query input starts at. The first row is 1. It is the programmer's responsibility to ensure the rowno parameter has a reasonable value.
267
$q = new xarQuery('SELECT','xar_roles');
$q->setstartat(100);
$q->setrowstodo(20);
echo $q->getstartat();
displays:
100
268 The gettype() method sets the type of the query, e.g. SELECT, UPDATE etc. See also the gettype() method.
269
271 String whose value can be SELECT, UPDATE, INSERT or DELETE.
272
$q = new xarQuery();
$q->settype('SELECT');
$q->addtable('xar_roles');
$q->eq('xar_name','Anonymous');
$q->qecho();
displays:
SELECT * FROM xar_roles WHERE xar_name = 'Anonymous'
273 The uselimits() method enables the LIMIT clause in a SELECT statement, if such a clause is defined. The SELECT statement will return the records defined by the setstartat() and setrowstodo() methods. By default limits are in effect if defined unless disabled by the nolimits() method.
274
276
$q = new xarQuery('SELECT','xar_security_levels');
$q->setstartat(3);
$q->setrowstodo(5);
$q->nolimits();
$q->run();
$total1 = count($q->output());
$q->uselimits();
$q->run();
$total2 = count($q->output());
returns:
$total1 = 10; (all the records)
$total2 = 5; (records 3 to 8)
277 Note: This chapter is out of date and will be rewritten!!
278 The following are equivalent:
$dbconn = xarDBGetConn();
$query = "SELECT * FROM $this->rolestable WHERE xar_name = '$this->name'";
if(!$dbconn->Execute($query)) return;
AND
$q = new xarQuery('SELECT',$this->rolestable);
$q->eq('xar_name',$this->name);
if (!$q->run()) return;
AND
$q = new xarQuery();
if (!$q->run("SELECT * FROM $this->rolestable WHERE xar_name = '$this->name'")) return;
279 The following are equivalent:
if (!$q->run($query)) return;
return q->output();
AND
$result = $dbconn->Execute($query);
if (!$result) return;
$invoices = array();
while(!$result->EOF) {
list($this->id) = $result->fields;
$invoices[] = $this->id;
$result->MoveNext();
}
return $invoices;
280 Note that in the last code snippet some knowledge about the contents of $result is assumed.
281 Check if a role already exists:
Old code:
$this->dbconn = xarDBGetConn();
// Confirm that this group or user does not already exist
if ($this->type == 1) {
$query = "SELECT COUNT(*) FROM $this->rolestable
WHERE xar_name = '$this->name'";
} else {
$query = "SELECT COUNT(*) FROM $this->rolestable
WHERE xar_uname = '$this->uname'";
}
$result = $this->dbconn->Execute($query);
if (!$result) return;
list($count) = $result->fields;
if ($count == 1) {//error message}
New code: Note only the WHERE clause changes depending on the role type
// Confirm that this group or user does not already exist
$q = new xarQuery('SELECT',$this->rolestable);
if ($this->type == 1) {
$q->eq('xar_name',$this->name);
} else {
$q->eq('xar_uname',$this->uname);
}
if (!$q->run()) return;
if ($q->getrows() == 1) {//error message}
282 Adding a group or user to the roles table
283 Old code: Note the query code is repeated twice depending on whether the role to be added ia a user or a group.
$this->dbconn = xarDBGetConn();
$nextId = $this->dbconn->genID($this->rolestable);
$createdate = mktime();
if ($this->type == 1) {
$query = "INSERT INTO $this->rolestable
(xar_uid, xar_name, xar_type, xar_uname, xar_valcode, xar_date_reg)
VALUES (?, ?, ?, ?, ?, ?)";
$bindvars = array(
$nextId,
$this->name,
$this->type,
$this->uname,
$this->val_code,
$createdate,
);
} else {
$query = "INSERT INTO $this->rolestable
(xar_uid, xar_name, xar_type, xar_uname, xar_email, xar_pass,
xar_date_reg, xar_state, xar_valcode, xar_auth_module)
VALUES (?,?,?,?,?,?,?,?,?,?)";
$bindvars = array(
$nextId,
$this->name,
$this->type,
$this->uname,
$this->email,
md5($this->pass),
$createdate,
$this->state,
$this->val_code,
$this->auth_module,
);
}
// Execute the query, bail if an exception was thrown
if (!$this->dbconn->Execute($query, $bindvars)) return;
284 New code: Note the query is assembled incrementally. The structured query handles the binding variables (? in the old code) internally.
$nextId = $this->dbconn->genID($this->rolestable);
$q = new xarQuery('INSERT',$this->rolestable);
$q->addfield(xar_uid,$nextId);
$q->addfield(xar_name,$this->name);
$q->addfield(xar_uname,$this->uname);
$q->addfield(xar_date_reg,mktime());
$q->addfield(xar_valcode,$this->val_code);
if ($this->type == 1) {
$q->addfield(xar_type,1);
} else {
$q->addfield(xar_type,0);
$q->addfield(xar_email,$this->email);
$q->addfield(xar_pass,md5($this->pass));
$q->addfield(xar_state,$this->state);
$q->addfield(xar_auth_module,$this->auth_module);
}
// Execute the query, bail if an exception was thrown
if (!$q->run()) return;
285 Any section which is present after the references will become an appendix
The DDF takes no position regarding the validity or scope of any Intellectual Property Rights or other rights that might be claimed to pertain to the implementation or use of the technology described in this document or the extent to which any license under such rights might or might not be available; nor does it represent that it has made any independent effort to identify any such rights. Information on the DDF's procedures with respect to rights in standards-track and standards-related documentation can be found in RFC-0.
The DDF invites any interested party to bring to its attention any copyrights, patents or patent applications, or other proprietary rights which may cover technology that may be required to practice this standard. Please address the information to the DDF Board of Directors.
Funding for the RFC Editor function is provided by the DDF