3 * @copyright Copyright (C) 2010-2022, the Friendica project
5 * @license GNU AGPL version 3 or any later version
7 * This program is free software: you can redistribute it and/or modify
8 * it under the terms of the GNU Affero General Public License as
9 * published by the Free Software Foundation, either version 3 of the
10 * License, or (at your option) any later version.
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU Affero General Public License for more details.
17 * You should have received a copy of the GNU Affero General Public License
18 * along with this program. If not, see <https://www.gnu.org/licenses/>.
22 namespace Friendica\Util\Writer;
25 use Friendica\Database\Definition\DbaDefinition;
28 * SQL writer utility for the database definition
30 class DbaDefinitionSqlWriter
33 * Creates a complete SQL definition bases on a give DBA Definition class
35 * @param DbaDefinition $definition The DBA definition class
37 * @return string The SQL definition as a string
39 * @throws Exception in case of parameter failures
41 public static function create(DbaDefinition $definition): string
43 $sqlString = "-- ------------------------------------------\n";
44 $sqlString .= "-- " . FRIENDICA_PLATFORM . " " . FRIENDICA_VERSION . " (" . FRIENDICA_CODENAME . ")\n";
45 $sqlString .= "-- DB_UPDATE_VERSION " . DB_UPDATE_VERSION . "\n";
46 $sqlString .= "-- ------------------------------------------\n\n\n";
48 foreach ($definition->getAll() as $tableName => $tableStructure) {
50 $sqlString .= "-- TABLE $tableName\n";
52 $sqlString .= static::createTable($tableName, $tableStructure);
59 * Creates the SQL definition of one table
61 * @param string $tableName The table name
62 * @param array $tableStructure The table structure
64 * @return string The SQL definition
66 * @throws Exception in cases of structure failures
68 public static function createTable(string $tableName, array $tableStructure): string
76 foreach ($tableStructure['fields'] as $fieldName => $field) {
77 $sql_rows[] = '`' . static::escape($fieldName) . '` ' . self::fieldCommand($field);
78 if (!empty($field['primary'])) {
79 $primary_keys[] = $fieldName;
81 if (!empty($field['foreign'])) {
82 $foreign_keys[$fieldName] = $field;
86 if (!empty($tableStructure['indexes'])) {
87 foreach ($tableStructure['indexes'] as $indexName => $fieldNames) {
88 $sql_index = self::createIndex($indexName, $fieldNames, '');
89 if (!is_null($sql_index)) {
90 $sql_rows[] = $sql_index;
95 foreach ($foreign_keys as $fieldName => $parameters) {
96 $sql_rows[] = self::foreignCommand($fieldName, $parameters);
99 if (isset($tableStructure['engine'])) {
100 $engine = ' ENGINE=' . $tableStructure['engine'];
103 if (isset($tableStructure['comment'])) {
104 $comment = " COMMENT='" . static::escape($tableStructure['comment']) . "'";
107 $sql = implode(",\n\t", $sql_rows);
109 $sql = sprintf("CREATE TABLE IF NOT EXISTS `%s` (\n\t", static::escape($tableName)) . $sql .
110 "\n)" . $engine . " DEFAULT COLLATE utf8mb4_general_ci" . $comment;
111 return $sql . ";\n\n";
115 * Standard escaping for SQL definitions
117 * @param string $sqlString the SQL string to escape
119 * @return string escaped SQL string
121 public static function escape(string $sqlString): string
123 return str_replace("'", "\\'", $sqlString);
127 * Creates the SQL definition to add a foreign key
129 * @param string $keyName The foreign key name
130 * @param array $parameters The given parameters of the foreign key
132 * @return string The SQL definition
134 public static function addForeignKey(string $keyName, array $parameters): string
136 return sprintf("ADD %s", static::foreignCommand($keyName, $parameters));
140 * Creates the SQL definition to drop a foreign key
142 * @param string $keyName The foreign key name
144 * @return string The SQL definition
146 public static function dropForeignKey(string $keyName): string
148 return sprintf("DROP FOREIGN KEY `%s`", $keyName);
152 * Creates the SQL definition to drop an index
154 * @param string $indexName The index name
156 * @return string The SQL definition
158 public static function dropIndex(string $indexName): string
160 return sprintf("DROP INDEX `%s`", static::escape($indexName));
164 * Creates the SQL definition to add a table field
166 * @param string $fieldName The table field name
167 * @param array $parameters The parameters of the table field
169 * @return string The SQL definition
171 public static function addTableField(string $fieldName, array $parameters): string
173 return sprintf("ADD `%s` %s", static::escape($fieldName), static::fieldCommand($parameters));
177 * Creates the SQL definition to modify a table field
179 * @param string $fieldName The table field name
180 * @param array $parameters The paramters to modify
182 * @return string The SQL definition
184 public static function modifyTableField(string $fieldName, array $parameters): string
186 return sprintf("MODIFY `%s` %s", static::escape($fieldName), self::fieldCommand($parameters, false));
190 * Returns SQL statement for field
192 * @param array $parameters Parameters for SQL statement
193 * @param boolean $create Whether to include PRIMARY KEY statement (unused)
194 * @return string SQL statement part
196 public static function fieldCommand(array $parameters, bool $create = true): string
198 $fieldstruct = $parameters['type'];
200 if (isset($parameters['Collation'])) {
201 $fieldstruct .= ' COLLATE ' . $parameters['Collation'];
204 if (isset($parameters['not null'])) {
205 $fieldstruct .= ' NOT NULL';
208 if (isset($parameters['default'])) {
209 if (strpos(strtolower($parameters['type']), 'int') !== false) {
210 $fieldstruct .= ' DEFAULT ' . $parameters['default'];
212 $fieldstruct .= " DEFAULT '" . $parameters['default'] . "'";
215 if (isset($parameters['extra'])) {
216 $fieldstruct .= ' ' . $parameters['extra'];
219 if (isset($parameters['comment'])) {
220 $fieldstruct .= " COMMENT '" . static::escape($parameters['comment']) . "'";
223 /*if (($parameters['primary'] != '') && $create)
224 $fieldstruct .= ' PRIMARY KEY';*/
230 * Creates the SQL definition to create an index
232 * @param string $indexName The index name
233 * @param array $fieldNames The field names of this index
234 * @param string $method The method to create the index (default is ADD)
236 * @return string The SQL definition
237 * @throws Exception in cases the paramter contains invalid content
239 public static function createIndex(string $indexName, array $fieldNames, string $method = 'ADD'): string
241 $method = strtoupper(trim($method));
242 if ($method != '' && $method != 'ADD') {
243 throw new Exception("Invalid parameter 'method' in self::createIndex(): '$method'");
246 if (in_array($fieldNames[0], ['UNIQUE', 'FULLTEXT'])) {
247 $index_type = array_shift($fieldNames);
248 $method .= " " . $index_type;
252 foreach ($fieldNames as $fieldName) {
257 if (preg_match('|(.+)\((\d+)\)|', $fieldName, $matches)) {
258 $names .= "`" . static::escape($matches[1]) . "`(" . intval($matches[2]) . ")";
260 $names .= "`" . static::escape($fieldName) . "`";
264 if ($indexName == 'PRIMARY') {
265 return sprintf("%s PRIMARY KEY(%s)", $method, $names);
269 return sprintf("%s INDEX `%s` (%s)", $method, static::escape($indexName), $names);
273 * Creates the SQL definition for foreign keys
275 * @param string $foreignKeyName The foreign key name
276 * @param array $parameters The parameters of the foreign key
278 * @return string The SQL definition
280 public static function foreignCommand(string $foreignKeyName, array $parameters): string
282 $foreign_table = array_keys($parameters['foreign'])[0];
283 $foreign_field = array_values($parameters['foreign'])[0];
285 $sql = "FOREIGN KEY (`" . $foreignKeyName . "`) REFERENCES `" . $foreign_table . "` (`" . $foreign_field . "`)";
287 if (!empty($parameters['foreign']['on update'])) {
288 $sql .= " ON UPDATE " . strtoupper($parameters['foreign']['on update']);
290 $sql .= " ON UPDATE RESTRICT";
293 if (!empty($parameters['foreign']['on delete'])) {
294 $sql .= " ON DELETE " . strtoupper($parameters['foreign']['on delete']);
296 $sql .= " ON DELETE CASCADE";