PHP utility collection with hybrid and fluent APIs.

⌈⌋ branch:  hybrid7 libraries


Check-in [dbeeffd82d]

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

Overview
Comment:Restructured into ->fold() and ->expand_syntax() handlers; allowed `:*` placeholder to contain nested expressions.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:dbeeffd82d2fb48671b1ecbab311946a89a12155
User & Date: mario 2014-07-19 18:44:18
Context
2014-07-21
14:10
Refixed `:key` quoting for expand_assoc_* callback check-in: 0612228e65 user: mario tags: trunk
2014-07-19
18:44
Restructured into ->fold() and ->expand_syntax() handlers; allowed `:*` placeholder to contain nested expressions. check-in: dbeeffd82d user: mario tags: trunk
16:04
New placeholder :* for minimal query interpolation ["AND x IN (??)", $params] check-in: 461f07a703 user: mario tags: trunk
Changes

Changes to php7/db.php.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
..
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
...
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
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
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
<?php
/**
 * title: PDO wrapper
 * description: Hybrid db() interface for extended SQL parameterization and result folding
 * api: php
 * type: database
 * version: 0.9.5
 * depends: pdo
 * license: Public Domain
 * author: Mario Salzer
 * doc: http://fossil.include-once.org/hybrid7/wiki/db
 *
 *
 * QUERY
................................................................................
 *      :?    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.
 *
 *      :*    Expression placeholder, where the associated argument should
 *            contain a nested e.g. ["AND foo IN (??)", $params] - which will
 *            only be interpolated if $params contains any value.
 *
 * Configurable {TOKENS} from db()->tokens[] are also substituted..
 *
 *
 * RESULT
 *
 * The returned result can be accessed as single data row, when fetching just
................................................................................
/**
 * 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;
        }
        
        #-- flatten into output parameter list
        $params2 = array();

        #-- flattening sub-arrays (works for ? enumarated and :named params)
        $args_count = count($args);
        for ($i = 0; $skip = 0, $i < $args_count; $i++) {
            $a = $args[$i];


            if (is_array($a)) {
                $enum = array_keys($a) === range(0, count($a) - 1);

                // subarray corresponds to special syntax placeholder?
                if (preg_match("/  \?\?  |  : [?:*  &,|]  /x", $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);



                        case ":*":  // optional expression placeholder
                            $replace = "";
                            if (isset($a[1]) and count($a[1])) {
                                $replace = $a[0];
                                $args[$i] = $a[1];
                                $i--;


                            }
                            $skip = $a = -1;  // omit data for flattened $params2
                            break;





                    }



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

            // unfold into plain parameter list
            if (is_array($a)) {
               $params2 = array_merge($params2, $a);
            }
            elseif (!$skip) {
                $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);
    }






|







 







|
|







 







>
>
>
>
>




>
>
>
>
>




>

|




>
>
>
>
>
|




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

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


|

|



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

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

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
..
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
...
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
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
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
<?php
/**
 * title: PDO wrapper
 * description: Hybrid db() interface for extended SQL parameterization and result folding
 * api: php
 * type: database
 * version: 0.9.9
 * depends: pdo
 * license: Public Domain
 * author: Mario Salzer
 * doc: http://fossil.include-once.org/hybrid7/wiki/db
 *
 *
 * QUERY
................................................................................
 *      :?    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.
 *
 *      :*    Expression placeholder, where the associated argument should
 *            contain an array ["AND foo IN (??)", $params] - which only
 *            interpolates if $params contains any value.  Can be nested.
 *
 * Configurable {TOKENS} from db()->tokens[] are also substituted..
 *
 *
 * RESULT
 *
 * The returned result can be accessed as single data row, when fetching just
................................................................................
/**
 * Binds PDO handle, allows original calls and extended placeholder use.
 *
 */
class db_wrap {


    /**
     * Keep PDO handle.
     *
     */
    public $pdo = NULL;
    function __construct($pdo) {
        $this->pdo = $pdo;
    }


    /**
     * Chain to plain PDO if any other method invoked.
     *
     */
    function __call($func, $args) {
        return call_user_func_array(array($this->pdo, $func), $args);
    }


    /**
     * Prepares and executes query after extended placeholders and parameter unpacking.
     *
     */
    function __invoke($sql, $args=array()) {

        // $sql may contain associative SQL parts and parameters
        if (is_array($sql)) {
            list($sql, $args) = $this->join($sql);
        }

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

        // flatten array arguments and extended placeholders
        list($sql, $args) = $this->fold($sql, $args);
        
        // 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 output
        if (!empty($this->test)) { 
            print json_encode($args)." => " . trim($sql) . "\n"; return;

        }
    
        // run
        $s = $this->prepare($sql)
        and
        $r = $s->execute($args);


        // wrap        
        return $s && $r ? new db_result($s) : $s;

    }




    /**
     * Expands the extended placeholders and flattens arrays from parameter list.
     *

     */
    function fold($sql, $args) {





    

        // output parameter list
        $params2 = array();
        






        #-- flattening sub-arrays (works for ? enumarated and :named params)
        foreach ($args as $i=>$a) {



            // subarray that corresponds to special syntax placeholder?
            if (is_array($a)
            and preg_match("/  \?\?  |  : [?:*  &,|]  /x", $sql, $capture, PREG_OFFSET_CAPTURE))
            {
                list($token, $pos) = current($capture);

                // placeholder substitution, possibly changing $a params
                $replace = $this->{self::$expand[$token]}($a);

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


            // unfold into plain parameter list
            if (is_array($a)) {
                $params2 = array_merge($params2, $a);
            }
            else {
                $params2[] = $a;
            }
        }
        
        return array($sql, $params2);
    }


    /**
     * Syntax expansion callbacks
     *
     */
    static $expand = array(
        "??" => "expand_list",
        ":?" => "expand_keys",
        "::" => "expand_named",
        ":," => "expand_assoc_comma",
        ":&" => "expand_assoc_and",
        ":|" => "expand_assoc_or",
        ":*" => "expand_expr",
    );

    // ?? array placeholders


    function expand_list($a) {
        return implode(",", array_fill(0, count($a), "?"));
    }




    // :? name placeholders, transforms list into enumerated params
    function expand_keys(&$a) {
        $enum = array_keys($a) === range(0, count($a) - 1);
        $r = implode(",", $this->db_identifier($enum ? $a : array_keys($a), "`"));
        $a = array();
        return $r;
    }




    // :: becomes :named,:value,:list
    function expand_named($a) {
        return ":" . implode(",:", $this->db_identifier(array_keys($a)) );
    }





    // for :, expand COMMA-separated key=:key,bar=:bar associative array
    function expand_assoc_comma($a, $fill = " , ", $replace=array()) {
        foreach ($this->db_identifier(array_keys($a), "`") as $key) {
            $replace[] = "$key = :$key";
        }


        return implode($fill, $replace);
    }
    // for :& AND-chained assoc foo=:foo AND bar=:bar
    function expand_assoc_and($a) {
        return $this->expand_assoc_comma($a, " AND ");
    }
    // for :| OR-chained assoc foo=:foo OR bar=:bar
    function expand_assoc_or($a) {
        return $this->expand_assoc_comma($a, " OR ");
    }

    // while :* holds an optional expression and subvalue list
    function expand_expr(&$a) {
        foreach (array_chunk($a, 2) as $pair) if (list($sql, $args) = $pair) {
            // substitute subexpression as if it were a regular SQL string
            if (is_array($args) && count($args)) {
                list ($replace, $a) = $this->fold($sql, array($args));
                return $replace;
            }
        }
        $a = array();  // else replace with nothing and omit current data for flattened $params2
    }

    
    
    /**
     * For readability the SQL may come as list.
     *   ["sql" => $args, ..]
     * Which is separated here into keys as $sql string and $args from values.
     *
     */
    function join($sql_args, $sql="", $args=array()) {
        foreach ($sql_args as $s=>$a) {
            $sql .= $s . "\n  ";
            $args[] = $a;
        }
        return array($sql, $args);
    }



    // 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);
    }