WebSVN – oidplus – Diff – /trunk/plugins/sqlSlang/mysql/OIDplusSqlSlangPluginMySQL.class.php


<?php
 
/*
 * OIDplus 2.0
 * Copyright 2019 - 2021 Daniel Marschall, ViaThinkSoft
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
 
if (!defined('INSIDE_OIDPLUS')) die();
 
class OIDplusSqlSlangPluginMySQL extends OIDplusSqlSlangPlugin {
 
        public static function id(): string {
                return 'mysql';
        }
 
        public function natOrder($fieldname, $order='asc'): string {
 
                $order = strtolower($order);
                if (($order != 'asc') && ($order != 'desc')) {
                        throw new OIDplusException(_L('Invalid order "%1" (needs to be "asc" or "desc")',$order));
                }
 
                $out = array();
 
                $max_arc_len = OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_ARC_SIZE') > 65 ? 65 : OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_ARC_SIZE'); // Limit of "decimal()" type
 
                // 1. sort by namespace (oid, guid, ...)
                $out[] = "SUBSTRING_INDEX($fieldname,':',1) $order";
 
                // 2. sort by first arc (0,1,2)
                $out[] = "SUBSTRING(SUBSTRING_INDEX($fieldname,'.',1), LENGTH(SUBSTRING_INDEX($fieldname,':',1))+2, $max_arc_len) $order";
 
                for ($i=2; $i<=OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_DEPTH'); $i++) {
                        // 3. Sort by the rest arcs one by one, not that MySQL can only handle decimal(65), not decimal($max_arc_len)
                        $out[] = "cast(SUBSTRING(SUBSTRING_INDEX($fieldname,'.',$i), LENGTH(SUBSTRING_INDEX($fieldname,'.',".($i-1)."))+2, $max_arc_len) as decimal($max_arc_len)) $order";
                }
 
                // 4. as last resort, sort by the identifier itself, e.g. if the casts above did fail (happens if it is not an OID)
                $out[] = "$fieldname $order";
 
                return implode(', ', $out);
 
        }
 
        public function sqlDate(): string {
                return 'now()';
        }
 
        public function detect(OIDplusDatabaseConnection $db): bool {
                try {
                        $vers = $db->query("select version() as dbms_version")->fetch_object()->dbms_version;
                        $vers = strtolower($vers);
                        return (strpos($vers, 'mysql') !== false) || (strpos($vers, 'mariadb') !== false);
                } catch (Exception $e) {
                        return false;
                }
        }
 
        public function insert_id(OIDplusDatabaseConnection $db): int {
                $res = $db->query("SELECT LAST_INSERT_ID() AS ID");
                $row = $res->fetch_array();
                return (int)$row['ID'];
        }
 
 
        public function setupSetTablePrefix($cont, $table, $prefix): string {
                $cont = str_replace('`'.$table.'`', '`'.$prefix.$table.'`', $cont);
                return $cont;
        }
 
        public function setupCreateDbIfNotExists($database): string {
                return "CREATE DATABASE IF NOT EXISTS `$database`;\n\n";
        }
 
        public function setupUseDatabase($database): string {
                return "USE `$database`;\n\n";
        }
 
        public function isNullFunction($expr1, $expr2): string {
                return "ifnull($expr1, $expr2)";
        }
 
        public function filterQuery($sql): string {
                return $sql;
        }
 
        public function getSQLBool($bool): string {
                return $bool ? '1' : '0';
        }
 
        public function escapeString($str): string {
                return str_replace("'", "''", $str);
        }
}
 

Subversion Repositories oidplus

oidplus/trunk/plugins/sqlSlang/mysql/OIDplusSqlSlangPluginMySQL.class.php – Rev 507 → 511

Rev 507		Rev 511
1	<?php	1	<?php
2		2
3	/*	3	/*
4	* OIDplus 2.0	4	* OIDplus 2.0
5	* Copyright 2019 Daniel Marschall, ViaThinkSoft	5	* Copyright 2019 - 2021 Daniel Marschall, ViaThinkSoft
6	*	6	*
7	* Licensed under the Apache License, Version 2.0 (the "License");	7	* Licensed under the Apache License, Version 2.0 (the "License");
8	* you may not use this file except in compliance with the License.	8	* you may not use this file except in compliance with the License.
9	* You may obtain a copy of the License at	9	* You may obtain a copy of the License at
10	*	10	*
11	* http://www.apache.org/licenses/LICENSE-2.0	11	* http://www.apache.org/licenses/LICENSE-2.0
12	*	12	*
13	* Unless required by applicable law or agreed to in writing, software	13	* Unless required by applicable law or agreed to in writing, software
14	* distributed under the License is distributed on an "AS IS" BASIS,	14	* distributed under the License is distributed on an "AS IS" BASIS,
15	* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.	15	* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16	* See the License for the specific language governing permissions and	16	* See the License for the specific language governing permissions and
17	* limitations under the License.	17	* limitations under the License.
18	*/	18	*/
19		19
-		20	if (!defined('INSIDE_OIDPLUS')) die();
-		21
20	class OIDplusSqlSlangPluginMySQL extends OIDplusSqlSlangPlugin {	22	class OIDplusSqlSlangPluginMySQL extends OIDplusSqlSlangPlugin {
21		23
22	public static function id(): string {	24	public static function id(): string {
23	return 'mysql';	25	return 'mysql';
24	}	26	}
25		27
26	public function natOrder($fieldname, $order='asc'): string {	28	public function natOrder($fieldname, $order='asc'): string {
27		29
28	$order = strtolower($order);	30	$order = strtolower($order);
29	if (($order != 'asc') && ($order != 'desc')) {	31	if (($order != 'asc') && ($order != 'desc')) {
30	throw new OIDplusException(_L('Invalid order "%1" (needs to be "asc" or "desc")',$order));	32	throw new OIDplusException(_L('Invalid order "%1" (needs to be "asc" or "desc")',$order));
31	}	33	}
32		34
33	$out = array();	35	$out = array();
34		36
35	$max_arc_len = OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_ARC_SIZE') > 65 ? 65 : OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_ARC_SIZE'); // Limit of "decimal()" type	37	$max_arc_len = OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_ARC_SIZE') > 65 ? 65 : OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_ARC_SIZE'); // Limit of "decimal()" type
36		38
37	// 1. sort by namespace (oid, guid, ...)	39	// 1. sort by namespace (oid, guid, ...)
38	$out[] = "SUBSTRING_INDEX($fieldname,':',1) $order";	40	$out[] = "SUBSTRING_INDEX($fieldname,':',1) $order";
39		41
40	// 2. sort by first arc (0,1,2)	42	// 2. sort by first arc (0,1,2)
41	$out[] = "SUBSTRING(SUBSTRING_INDEX($fieldname,'.',1), LENGTH(SUBSTRING_INDEX($fieldname,':',1))+2, $max_arc_len) $order";	43	$out[] = "SUBSTRING(SUBSTRING_INDEX($fieldname,'.',1), LENGTH(SUBSTRING_INDEX($fieldname,':',1))+2, $max_arc_len) $order";
42		44
43	for ($i=2; $i<=OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_DEPTH'); $i++) {	45	for ($i=2; $i<=OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_DEPTH'); $i++) {
44	// 3. Sort by the rest arcs one by one, not that MySQL can only handle decimal(65), not decimal($max_arc_len)	46	// 3. Sort by the rest arcs one by one, not that MySQL can only handle decimal(65), not decimal($max_arc_len)
45	$out[] = "cast(SUBSTRING(SUBSTRING_INDEX($fieldname,'.',$i), LENGTH(SUBSTRING_INDEX($fieldname,'.',".($i-1)."))+2, $max_arc_len) as decimal($max_arc_len)) $order";	47	$out[] = "cast(SUBSTRING(SUBSTRING_INDEX($fieldname,'.',$i), LENGTH(SUBSTRING_INDEX($fieldname,'.',".($i-1)."))+2, $max_arc_len) as decimal($max_arc_len)) $order";
46	}	48	}
47		49
48	// 4. as last resort, sort by the identifier itself, e.g. if the casts above did fail (happens if it is not an OID)	50	// 4. as last resort, sort by the identifier itself, e.g. if the casts above did fail (happens if it is not an OID)
49	$out[] = "$fieldname $order";	51	$out[] = "$fieldname $order";
50		52
51	return implode(', ', $out);	53	return implode(', ', $out);
52		54
53	}	55	}
54		56
55	public function sqlDate(): string {	57	public function sqlDate(): string {
56	return 'now()';	58	return 'now()';
57	}	59	}
58		60
59	public function detect(OIDplusDatabaseConnection $db): bool {	61	public function detect(OIDplusDatabaseConnection $db): bool {
60	try {	62	try {
61	$vers = $db->query("select version() as dbms_version")->fetch_object()->dbms_version;	63	$vers = $db->query("select version() as dbms_version")->fetch_object()->dbms_version;
62	$vers = strtolower($vers);	64	$vers = strtolower($vers);
63	return (strpos($vers, 'mysql') !== false) \|\| (strpos($vers, 'mariadb') !== false);	65	return (strpos($vers, 'mysql') !== false) \|\| (strpos($vers, 'mariadb') !== false);
64	} catch (Exception $e) {	66	} catch (Exception $e) {
65	return false;	67	return false;
66	}	68	}
67	}	69	}
68		70
69	public function insert_id(OIDplusDatabaseConnection $db): int {	71	public function insert_id(OIDplusDatabaseConnection $db): int {
70	$res = $db->query("SELECT LAST_INSERT_ID() AS ID");	72	$res = $db->query("SELECT LAST_INSERT_ID() AS ID");
71	$row = $res->fetch_array();	73	$row = $res->fetch_array();
72	return (int)$row['ID'];	74	return (int)$row['ID'];
73	}	75	}
74		76
75		77
76	public function setupSetTablePrefix($cont, $table, $prefix): string {	78	public function setupSetTablePrefix($cont, $table, $prefix): string {
77	$cont = str_replace('`'.$table.'`', '`'.$prefix.$table.'`', $cont);	79	$cont = str_replace('`'.$table.'`', '`'.$prefix.$table.'`', $cont);
78	return $cont;	80	return $cont;
79	}	81	}
80		82
81	public function setupCreateDbIfNotExists($database): string {	83	public function setupCreateDbIfNotExists($database): string {
82	return "CREATE DATABASE IF NOT EXISTS `$database`;\n\n";	84	return "CREATE DATABASE IF NOT EXISTS `$database`;\n\n";
83	}	85	}
84		86
85	public function setupUseDatabase($database): string {	87	public function setupUseDatabase($database): string {
86	return "USE `$database`;\n\n";	88	return "USE `$database`;\n\n";
87	}	89	}
88		90
89	public function isNullFunction($expr1, $expr2): string {	91	public function isNullFunction($expr1, $expr2): string {
90	return "ifnull($expr1, $expr2)";	92	return "ifnull($expr1, $expr2)";
91	}	93	}
92		94
93	public function filterQuery($sql): string {	95	public function filterQuery($sql): string {
94	return $sql;	96	return $sql;
95	}	97	}
96		98
97	public function getSQLBool($bool): string {	99	public function getSQLBool($bool): string {
98	return $bool ? '1' : '0';	100	return $bool ? '1' : '0';
99	}	101	}
100		102
101	public function escapeString($str): string {	103	public function escapeString($str): string {
102	return str_replace("'", "''", $str);	104	return str_replace("'", "''", $str);
103	}	105	}
104	}	106	}
105		107