PHP utility collection with hybrid and fluent APIs.

⌈⌋ branch:  hybrid7 libraries


Check-in [461f07a703]

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

Overview
Comment:New placeholder :* for minimal query interpolation ["AND x IN (??)", $params]
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:461f07a70337b34b1b3e3d33454c99e18a851c72
User & Date: mario 2014-07-19 16:04:48
Context
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
2014-07-17
14:09
Different rx delimiter for strip_markup. check-in: dbd69cf2a0 user: mario tags: trunk
Changes

Changes to php7/db.php.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
..
20
21
22
23
24
25
26

27
28
29




30
31
32
33
34
35
36
...
135
136
137
138
139
140
141
142
143
144
145
146



147
148
149
150
151
152
153
154
155
156
157
158
...
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
<?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
................................................................................
 *   $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
................................................................................

        #-- 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;

................................................................................
                            $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) {
                   $params2 = array_merge($params2, $a);
                } else {
                   $params2 = array_merge($params2, $a);
                }
            }
            else {
                $params2[] = $a;
            }
        }

        #-- placeholders
        if (!empty($this->tokens) && strpos($sql, "{")) {
            $sql = preg_replace_callback("/\{(\w+)(.*?)\}/", array($this, "token"), $sql);






|







 







>



>
>
>
>







 







|



|
>
>
>




|







 







>
>
>
>
>
>
>
>
>
>




|
>
|
<
|
<
|
|
<
|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
..
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
...
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
...
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
<?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
................................................................................
 *   $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.
 *
 *      :*    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
................................................................................

        #-- 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;

................................................................................
                            $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);