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

Rev	Author	Line No.	Line
635	daniel-mar	1	<?php
		2
		3	/*
		4	* OIDplus 2.0
1086	daniel-mar	5	* Copyright 2019 - 2023 Daniel Marschall, ViaThinkSoft
635	daniel-mar	6	*
		7	* Licensed under the Apache License, Version 2.0 (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
		10	*
		11	* http://www.apache.org/licenses/LICENSE-2.0
		12	*
		13	* Unless required by applicable law or agreed to in writing, software
		14	* distributed under the License is distributed on an "AS IS" BASIS,
		15	* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
		16	* See the License for the specific language governing permissions and
		17	* limitations under the License.
		18	*/
		19
1050	daniel-mar	20	namespace ViaThinkSoft\OIDplus;
635	daniel-mar	21
1086	daniel-mar	22	// phpcs:disable PSR1.Files.SideEffects
		23	\defined('INSIDE_OIDPLUS') or die;
		24	// phpcs:enable PSR1.Files.SideEffects
		25
635	daniel-mar	26	class OIDplusSqlSlangPluginMySQL extends OIDplusSqlSlangPlugin {
		27
1116	daniel-mar	28	/**
		29	* @return string
		30	*/
635	daniel-mar	31	public static function id(): string {
		32	return 'mysql';
		33	}
		34
1116	daniel-mar	35	/**
		36	* @param string $fieldname
		37	* @param string $order
		38	* @return string
		39	* @throws OIDplusException
		40	*/
		41	public function natOrder(string $fieldname, string $order='asc'): string {
635	daniel-mar	42
		43	$order = strtolower($order);
		44	if (($order != 'asc') && ($order != 'desc')) {
		45	throw new OIDplusException(_L('Invalid order "%1" (needs to be "asc" or "desc")',$order));
		46	}
		47
		48	$out = array();
		49
		50	$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
		51
		52	// 1. sort by namespace (oid, guid, ...)
		53	$out[] = "SUBSTRING_INDEX($fieldname,':',1) $order";
		54
		55	// 2. sort by first arc (0,1,2)
		56	$out[] = "SUBSTRING(SUBSTRING_INDEX($fieldname,'.',1), LENGTH(SUBSTRING_INDEX($fieldname,':',1))+2, $max_arc_len) $order";
		57
		58	for ($i=2; $i<=OIDplus::baseConfig()->getValue('LIMITS_MAX_OID_DEPTH'); $i++) {
		59	// 3. Sort by the rest arcs one by one, not that MySQL can only handle decimal(65), not decimal($max_arc_len)
		60	$out[] = "cast(SUBSTRING(SUBSTRING_INDEX($fieldname,'.',$i), LENGTH(SUBSTRING_INDEX($fieldname,'.',".($i-1)."))+2, $max_arc_len) as decimal($max_arc_len)) $order";
		61	}
		62
		63	// 4. as last resort, sort by the identifier itself, e.g. if the casts above did fail (happens if it is not an OID)
		64	$out[] = "$fieldname $order";
		65
		66	return implode(', ', $out);
		67
		68	}
		69
1116	daniel-mar	70	/**
		71	* @return string
		72	*/
635	daniel-mar	73	public function sqlDate(): string {
		74	return 'now()';
		75	}
		76
1116	daniel-mar	77	/**
		78	* @param OIDplusDatabaseConnection $db
		79	* @return bool
		80	*/
635	daniel-mar	81	public function detect(OIDplusDatabaseConnection $db): bool {
		82	try {
		83	$vers = $db->query("select version() as dbms_version")->fetch_object()->dbms_version;
		84	$vers = strtolower($vers);
		85	return (strpos($vers, 'mysql') !== false) \|\| (strpos($vers, 'mariadb') !== false);
1050	daniel-mar	86	} catch (\Exception $e) {
635	daniel-mar	87	return false;
		88	}
		89	}
		90
1116	daniel-mar	91	/**
		92	* @param OIDplusDatabaseConnection $db
		93	* @return int
		94	* @throws OIDplusException
		95	*/
635	daniel-mar	96	public function insert_id(OIDplusDatabaseConnection $db): int {
		97	$res = $db->query("SELECT LAST_INSERT_ID() AS ID");
		98	$row = $res->fetch_array();
		99	return (int)$row['ID'];
		100	}
		101
1116	daniel-mar	102	/**
		103	* @param string $cont
		104	* @param string $table
		105	* @param string $prefix
		106	* @return string
		107	*/
		108	public function setupSetTablePrefix(string $cont, string $table, string $prefix): string {
1130	daniel-mar	109	return str_replace('`'.$table.'`', '`'.$prefix.$table.'`', $cont);
635	daniel-mar	110	}
		111
1116	daniel-mar	112	/**
		113	* @param string $database
		114	* @return string
		115	*/
		116	public function setupCreateDbIfNotExists(string $database): string {
635	daniel-mar	117	return "CREATE DATABASE IF NOT EXISTS `$database`;\n\n";
		118	}
		119
1116	daniel-mar	120	/**
		121	* @param string $database
		122	* @return string
		123	*/
		124	public function setupUseDatabase(string $database): string {
635	daniel-mar	125	return "USE `$database`;\n\n";
		126	}
		127
1116	daniel-mar	128	/**
		129	* @param string $expr1
		130	* @param string $expr2
		131	* @return string
		132	*/
		133	public function isNullFunction(string $expr1, string $expr2): string {
635	daniel-mar	134	return "ifnull($expr1, $expr2)";
		135	}
		136
1116	daniel-mar	137	/**
		138	* @param string $sql
		139	* @return string
		140	*/
		141	public function filterQuery(string $sql): string {
635	daniel-mar	142	return $sql;
		143	}
		144
1116	daniel-mar	145	/**
		146	* @param bool $bool
		147	* @return string
		148	*/
		149	public function getSQLBool(bool $bool): string {
635	daniel-mar	150	return $bool ? '1' : '0';
		151	}
		152
1116	daniel-mar	153	/**
		154	* @param string $str
		155	* @return string
		156	*/
		157	public function escapeString(string $str): string {
635	daniel-mar	158	return str_replace("'", "''", $str);
		159	}
		160	}

Subversion Repositories oidplus

oidplus/trunk/plugins/viathinksoft/sqlSlang/mysql/OIDplusSqlSlangPluginMySQL.class.php – Rev 1130