PHP utility collection with hybrid and fluent APIs.

⌈⌋ branch:  hybrid7 libraries


Check-in [7048ca4be9]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:<b>db()</b> now split up into hybrid constructor and dispatcher, and PDO db_wrap{} just for extended placeholders; betters docs inline.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:7048ca4be94d5d57845c9eaae99392c376bccb90
User & Date: mario 2014-07-11 09:26:46
Context
2014-07-17
14:08
Allow more params for ->into() wrapping. check-in: 585c3b9373 user: mario tags: trunk
2014-07-11
09:26
<b>db()</b> now split up into hybrid constructor and dispatcher, and PDO db_wrap{} just for extended placeholders; betters docs inline. check-in: 7048ca4be9 user: mario tags: trunk
09:22
Introduced <b>_slug()</b> and <b>_strip_markup()</b> filters. Prepated for better doc comments. check-in: 9935d3cfac user: mario tags: trunk
Changes

Changes to php7/db.php.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22



23
24
25
26



27



28
29
30


31
32










33
34
35
36
37

38
39
40
41
42




43
44
45
46
47
48
49
50
51
52
53
54
55
56

57
58
59
60
61



62
63

64
65
66
67







68
69
70
71


72
73
74

75
76
77
78






























79
80
81

82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

101
102
103

104
105
106
107

108
109
110

111
112
113
114

115


116

117
118
119
120
121
122
123
...
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143


144
145

146
147
148

149
150
151
152
153
154
155
156
157
158


159
160
161















162
163


164
165
166
167


168
169
170

171



172
173


174
175
176
177
178
179
180
181
182
183
184
185
186
187


188
189
190
191
192
193
194

195
196














197

198
199
200



201











202
203
204
205
206
207
208
<?php
/**
 * title: database
 * description: basic db() interface for parameterized SQL and result folding
 * api: php
 * type: database
 * version: 0.7
 * depends: pdo
 * license: Public Domain
 * author: Mario Salzer
 * url: http://php7framework.sourceforge.net/
 *
 *
 * QUERY
 *
 * Provides simple database queries with enumerated or named parameters. It's
 * flexible in accepting scalar arguments and arrays. Array args get merged,
 * or transcribed when special placeholders are present:
 *
 *   $r = db("SELECT * FROM tbl WHERE a>=? AND b IN (??)", $a, array($b));
 *
 * Two ?? are used for interpolating arrays, which is useful for IN clauses.



 * The placeholder :? interpolates key names (doesn't add values).
 * And :& or :, or :| become a name=:assign list grouped by AND, comma, OR.
 * Whereas :: turns into a simple :named,:value,:list (for IN clauses).
 * Also configurable {TOKENS} are replaced automatically (db()->tokens[]).



 *



 * RESULT
 *
 * The returned result can be accessed as single data row, with $data->column


 * or using $data["column"].
 * Or if it's a result list, foreach() can iterate over all returned rows.










 * And all PDO ->fetch() methods are still available for use on the result obj.
 * ArrayObjects cannot be used like real arrays in all contexts; typecasting
 * the data out is not possible, in string context curly braces "{$a->x}" are
 * necessary, and in sub-loops needed object syntax "foreach ($a->subarray as)"
 *

 * CONNECT  
 *
 * The db() interface utilizes the global "$db" variable. Which could also be
 * instantiated separately or using:
 * db("connect", array("mysql:host=localhost;dbname=test","username","password"));




 *
 * RECORD WRAPPER
 *
 * There's also a simple table data gateway wrapper implemented here. It
 * accepts db() queries for single entries, and allows ->save()ing back, or
 * to ->delete() records.
 * You should only use it in conjuction with sql2php and its simpler wrappers.
 *
 */



/**
 * SQL query.

 *
 */
function db($sql, $params="...") {
    global $db;
    



    #-- open database
    if ($sql == "connect") {

    
        // DSN
        $params = is_array($params) ? array_values($params) : array($params,"","");
        $db = new PDO($params[0], $params[1], $params[2]);







        
        // save settings
        $db->tokens = array("PREFIX"=>""); // or reference global $config
        $db->broken = strstr($params[0], "sqlite");


    }
    
    #-- singleton use

    elseif (empty($sql)) {
        return $db;
    }
    






























    #-- reject SQL
    elseif (strpos($sql, "'")) {
        trigger_error("SQL query contained raw data. DO NOT WANT", E_USER_WARNING);

    }
    
    #-- execute SQL
    else {
    
        #-- get $params
        $params2 = array();
        $args = func_get_args();
        array_shift($args);

        #-- flattening sub-arrays (works for ? enumarated and :named params)
        foreach ($args as $i=>$a) {
            if (is_array($a)) {
                $enum = is_int(end(array_keys($a)));

                // subarray corresponds to special syntax placeholder?
                if (preg_match("/\?\?|:\?|::|:&|:,|&\|/", $sql, $uu, PREG_OFFSET_CAPTURE)) {
                    list($token, $pos) = $uu[0];
                    switch ($token) {

                        case "??":  // replace ?? array placeholders
                            $replace = implode(",", array_fill(0, count($a), "?"));
                            break;

                        case ":?":  // and :? name placeholder, transforms list into enumerated params
                            $replace = implode(",", db_identifier($enum ? $a : array_keys($a)) );
                            $enum = 1;  $a = array();   // do not actually add values
                            break;

                        case "::":  // inject :named,:value,:list
                            $replace = ":" . implode(",:", db_identifier(array_keys($a)) );
                            break;

                        case ":&":  // associative params - becomes "key=:key AND .."
                        case ":,":  // COMMA-separated
                        case ":|":  // OR-separated
                            $fill = array(":&"=>" AND ", ":,"=>" , ", ":|"=>" OR ");

                            $replace = array(); foreach (db_identifier(array_keys($a)) as $key) { $replace[] = "$key=:$key"; }


                            $replace = implode($fill[$token], $replace);

                    }
                    // update SQL string
                    $sql = substr($sql, 0, $pos) . $replace . substr($sql, $pos + strlen($token));
                }

                // unfold
                if ($enum) {
................................................................................
            }
            else {
                $params2[] = $a;
            }
        }

        #-- placeholders
        if ($db->tokens && strpos($sql, "{")) {
            $sql = preg_replace("/\{(\w+)(.*?)\}/e", 'isset($db->token["$1"]) ? $db->token["$1"]."$2" : @$db->token["$1$2"]', $sql);
        }
        
        #-- SQL incompliance workarounds
        if ($db->broken && strpos($sql, " IN (")) { // only for ?,?,?,? enum params
            $sql = preg_replace("/(`?\w+`?) IN \(([?,]+)\)/e", '"($1=" . implode("OR $1=", array_fill(0, 1+strlen("$2")/2, "? ")) . ")"', $sql);
        }



if (isset($db->test)) { print json_encode($params2)." => " . trim($sql) . "\n"; return; }
    

        #-- run
        $s = $db->prepare($sql);
        $s->setFetchMode(PDO::FETCH_ASSOC);

        $r = $s->execute($params2);

        #-- wrap        
        return $r ? new db_result($s) : $s;
    }
}

// This is a restrictive filter function or column/table name identifiers.
function db_identifier($as) {
    return preg_replace("/[^\w\d_.]/", "_", $as);  // Can only be foregone if it's ensured that none of the passed named db() $arg keys originated from http/user input.


}

















/**
 * Allows list access, or fetches first result[0]


 *
 */
class db_result extends ArrayObject implements IteratorAggregate {



    function __construct($results) {
        $this->results = $results;
        parent::__construct(array(), 2);

    }



    
    // single access


    function __get($name) {
    
        // get first result, transfuse into $this
        if ($this->results) {
            foreach ($this->results->fetch(PDO::FETCH_ASSOC) as $key=>$value) {
                $this->{$key} = $value;
            }
            unset($this->results);
        }
        
        // suffice __get
        return $this->{$name};
    }
    


    // used as PDO statement
    function __call($func, $args) {
        return call_user_func_array(array($this->results, $func), $args);
    }
    
    // iterator
    function getIterator() {

        if (isset($this->results)) {
            $this->results->setFetchMode(PDO::FETCH_CLASS, "ArrayObject", array(array(), 2));














            return $this->results;

        }
        else return new ArrayIterator($this);
    }















}



/**
 * Table data gateway. Don't use directly.
 *


|
|


|



|




|
|
|

|

|
>
>
>
|
<
<
<
>
>
>

>
>
>


|
>
>
|
<
>
>
>
>
>
>
>
>
>
>
|
<
<
<

>


|
<
<
>
>
>
>













|
>


|
<
|
>
>
>

<
>

<
<
<
>
>
>
>
>
>
>
|


<
>
>


<
>




>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
>
|
|
<
<
<


<
<




|





>



>

|


>

|

>




>
|
>
>

>







 







|
|


|
|
|


>
>
|
|
>

|
<
>



|

|

|
<
|
>
>
|

|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

<
>
>




>
>

<

>

>
>
>
|
<
>
>



|
|
<
<




|

|
>
>
|
|
|

|
|
|
>
|
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
|
<
|
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26



27
28
29
30
31
32
33
34
35
36
37
38
39

40
41
42
43
44
45
46
47
48
49
50



51
52
53
54
55


56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

78
79
80
81
82

83
84



85
86
87
88
89
90
91
92
93
94

95
96
97
98

99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139



140
141


142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
...
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209

210
211
212
213
214
215
216
217
218

219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240

241
242
243
244
245
246
247
248
249

250
251
252
253
254
255
256

257
258
259
260
261
262
263


264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281

282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298

299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
<?php
/**
 * title: PDO wrapper
 * description: Hybrid db() interface for extended SQL parameterization and result folding
 * api: php
 * type: database
 * version: 0.9.2
 * depends: pdo
 * license: Public Domain
 * author: Mario Salzer
 * doc: http://fossil.include-once.org/hybrid7/wiki/db
 *
 *
 * QUERY
 *
 * Provides simple database queries with enumerated / named parameters. It's
 * flexible in accepting plain PDO scalar arguments or arrays. Array args get
 * merged, or transcribed when special placeholders are present:
 *
 *   $r = db("SELECT * FROM tbl WHERE a>=? AND b IN (??)", $a, array($b, $c));
 *
 * Extended placeholder syntax:
 *
 *      ??    Interpolation of indexed arrays - useful for IN clauses.
 *      ::    Turns associative arrays into a :named, :value, :list.
 *      :?    Interpolates key names (ignores values).



 *      :&    Becomes a `name`=:value list, joined by AND - for WHERE clauses.
 *      :|    Becomes a `name`=:value list, joined by OR - for WHERE clauses.
 *      :,    Becomes a `name`=:value list, joined by , commas - for UPDATEs.
 *
 * Configurable {TOKENS} from db()->tokens[] are also substituted..
 *
 *
 * RESULT
 *
 * The returned result can be accessed as single data row, when fetching just
 * one:
 *       $result->column
 *       $result["column"]

 *
 * Or just traversed row-wise as usual by iteration
 *
 *       foreach (db("...") as $row)
 *
 * Alternatively by object-wrapping (unlike plain PDO->fetchObject() this
 * hydrates the object using its normal constructor) the result set with:
 *
 *       foreach ($result->into("ArrayObject") as $row)
 *
 * Yet all PDO ->fetch() methods are still available for use on the result obj.



 *
 *
 * CONNECT  
 *
 * The db() interface binds the global "$db" variable. It ought to be


 * initialized with:
 *
 *       db(new PDO(...));
 * 
 *
 * RECORD WRAPPER
 *
 * There's also a simple table data gateway wrapper implemented here. It
 * accepts db() queries for single entries, and allows ->save()ing back, or
 * to ->delete() records.
 * You should only use it in conjuction with sql2php and its simpler wrappers.
 *
 */



/**
 * Hybrid instantiation / query function.
 * Couples `$db` in the shared/global scope.
 *
 */
function db($sql=NULL, $params=NULL) {


    #-- shared PDO handle
    $db = & $GLOBALS["db"];
    
    #-- open database

    if (is_object($sql)) {
    



        // use passed param
        $db = new db_wrap($sql);
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
        $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, is_int(stripos($db->getAttribute(PDO::ATTR_DRIVER_NAME), "mysql")));
        $db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
        $db->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
    
        // save settings
        $db->tokens = array("PREFIX"=>""); // or reference global $config

        $db->in_clause = $db->getAttribute(PDO::ATTR_DRIVER_NAME) == "sqlite"
                     and $db->getAttribute(PDO::ATTR_CLIENT_VERSION) < 3.6;
    }
    

    #-- return PDO handle
    elseif (empty($sql)) {
        return $db;
    }
    
    #-- just dispatch to the wrapper
    else {
        $args = array_slice(func_get_args(), 1);
        return $db($sql, $args);
    }

}


/**
 * Binds PDO handle, allows original calls and extended placeholder use.
 *
 */
class db_wrap {


    function __construct($pdo) {
        $this->pdo = $pdo;
    }

    function __call($func, $args) {
        return call_user_func_array(array($this->pdo, $func), $args);
    }

    /**
     * Handles extended placeholders and parameter unpacking.
     *
     */
    function __invoke($sql, $args=array()) {

        #-- reject SQL
        if (strpos($sql, "'")) {
            trigger_error("SQL query contained raw data. DO NOT WANT", E_USER_WARNING);
            return NULL;
        }
        



        #-- get $params
        $params2 = array();



        #-- flattening sub-arrays (works for ? enumarated and :named params)
        foreach ($args as $i=>$a) {
            if (is_array($a)) {
                $enum = array_keys($a) === range(0, count($a) - 1);

                // subarray corresponds to special syntax placeholder?
                if (preg_match("/\?\?|:\?|::|:&|:,|&\|/", $sql, $uu, PREG_OFFSET_CAPTURE)) {
                    list($token, $pos) = $uu[0];
                    switch ($token) {

                        case "??":  // replace ?? array placeholders
                            $replace = implode(",", array_fill(0, count($a), "?"));
                            break;

                        case ":?":  // and :? name placeholder, transforms list into enumerated params
                            $replace = implode(",", $this->db_identifier($enum ? $a : array_keys($a), "`"));
                            $enum = 1;  $a = array();   // do not actually add values
                            break;

                        case "::":  // inject :named,:value,:list
                            $replace = ":" . implode(",:", $this->db_identifier(array_keys($a)) );
                            break;

                        case ":&":  // associative params - becomes "key=:key AND .."
                        case ":,":  // COMMA-separated
                        case ":|":  // OR-separated
                            $fill = array(":&"=>" AND ", ":,"=>" , ", ":|"=>" OR ");
                            $replace = array();
                            foreach ($this->db_identifier(array_keys($a)) as $key) {
                                $replace[] = "`$key`=:$key";
                            }
                            $replace = implode($fill[$token], $replace);

                    }
                    // update SQL string
                    $sql = substr($sql, 0, $pos) . $replace . substr($sql, $pos + strlen($token));
                }

                // unfold
                if ($enum) {
................................................................................
            }
            else {
                $params2[] = $a;
            }
        }

        #-- placeholders
        if (!empty($this->tokens) && strpos($sql, "{")) {
            $sql = preg_replace_callback("/\{(\w+)(.*?)\}/", array($this, "token"), $sql);
        }
        
        #-- older SQLite workaround
        if (!empty($this->in_clause) && strpos($sql, " IN (")) { // only for ?,?,?,? enum params
            $sql = preg_replace_callback("/(\S+)\s+IN\s+\(([?,]+)\)/", array($this, "in_clause"), $sql);
        }

        #-- just debug
        if (!empty($this->test)) { 
            print json_encode($params2)." => " . trim($sql) . "\n"; return;
        }
    
        #-- run
        $s = $this->prepare($sql)

        and
        $r = $s->execute($params2);

        #-- wrap        
        return $s && $r ? new db_result($s) : $s;
    }


    // This is a restrictive filter function for column/table name identifiers.

    // Can only be foregone if it's ensured that none of the passed named db() $arg keys originated from http/user input.
    function db_identifier($as, $wrap="") {
        return preg_replace(array("/[^\w\d_.]/", "/^|$/"), array("_", $wrap), $as);
    }

    
    // Regex callbacks
    function token($m) {
        list($m, $tok, $ext) = $m;
        return isset($this->token[$tok]) ? $this->token[$tok].$ext : $this->token["$tok$ext"];
    }
    function in_clause($m) {
        list($m, $key, $vals) = $m;
        $num = substr_count($vals, "?");
        return "($key=" . implode("OR $key=", array_fill(0, $num, "? ")) . ")";
    }

}



/**

 * Allows traversing result sets as arrays or hydrated objects,
 * or fetches only first result row on ->column_name accesses.
 *
 */
class db_result extends ArrayObject implements IteratorAggregate {

    protected $results = NULL;

    function __construct($results) {

        parent::__construct(array(), 2);
        $this->results = $results;
    }
    // used as PDO statement
    function __call($func, $args) {
        return call_user_func_array(array($this->results, $func), $args);
    }


    // Single column access
    function __get($name) {
    
        // get first result, transfuse into $this
        if (is_object($this->results)) {
            $this->exchangeArray($this->results->fetch());


            unset($this->results);
        }
        
        // suffice __get
        return $this[$name];
    }

    // Just let PDOStatement handle the Traversable
    function getIterator() {
        return isset($this->results)
             ? $this->results
             : new ArrayIterator($this);
    }

    // Or hydrate specific result objects ourselves
    function into() {
        $into = func_get_args() ?: array("ArrayObject", 2);
        return new db_result_iter($this->results, $into);

    }
}


/**
 * More wrapping for hydrated iteration.
 *
 */
class db_result_iter implements Iterator {

    // Again keep PDOStatement and class specifier
    protected $results = NULL;
    protected $into = array();
    function __construct($results, $into) {
        $this->results = $results;
        $this->into = $into;
    }

    
    // Iterator just fetches and converts on traversal
    protected $row = NULL;
    public function current()
    {
        list($class, $arg2) = $this->into;
        return new $class($this->row, $arg2);
    }
    function valid() {
        return !empty($this->row = $this->results->fetch());
    }
    
    // unused for normal `foreach` operation
    function next() { return NULL; }
    function rewind() { return NULL; }
    function key() { return NULL; }
}



/**
 * Table data gateway. Don't use directly.
 *