diff options
Diffstat (limited to 'www/wiki/extensions/SemanticMediaWiki/src/SQLStore/TableBuilder/SQLiteTableBuilder.php')
| -rw-r--r-- | www/wiki/extensions/SemanticMediaWiki/src/SQLStore/TableBuilder/SQLiteTableBuilder.php | 373 |
1 files changed, 373 insertions, 0 deletions
diff --git a/www/wiki/extensions/SemanticMediaWiki/src/SQLStore/TableBuilder/SQLiteTableBuilder.php b/www/wiki/extensions/SemanticMediaWiki/src/SQLStore/TableBuilder/SQLiteTableBuilder.php new file mode 100644 index 00000000..acd7e3c9 --- /dev/null +++ b/www/wiki/extensions/SemanticMediaWiki/src/SQLStore/TableBuilder/SQLiteTableBuilder.php @@ -0,0 +1,373 @@ +<?php + +namespace SMW\SQLStore\TableBuilder; + +/** + * @license GNU GPL v2+ + * @since 2.5 + * + * @author mwjames + * @author Markus Krötzsch + * @author Marcel Gsteiger + * @author Jeroen De Dauw + */ +class SQLiteTableBuilder extends TableBuilder { + + /** + * @since 2.5 + * + * {@inheritDoc} + */ + public function getStandardFieldType( $fieldType ) { + + $charLongLength = FieldType::CHAR_LONG_LENGTH; + + $fieldTypes = [ + // like page_id in MW page table + 'id' => 'INTEGER', + 'id_primary' => 'INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT', + + // (see postgres, mysql on the difference) + 'id_unsigned' => 'INTEGER', + + // like page_namespace in MW page table + 'namespace' => 'INT(11)', + // like page_title in MW page table + 'title' => 'VARBINARY(255)', + // like iw_prefix in MW interwiki table + 'interwiki' => 'TEXT', + 'iw' => 'TEXT', + 'hash' => 'VARBINARY(40)', + // larger blobs of character data, usually not subject to SELECT conditions + 'blob' => 'MEDIUMBLOB', + 'text' => 'TEXT', + 'boolean' => 'TINYINT(1)', + 'double' => 'DOUBLE', + 'integer' => 'INT(8)', + 'char_long' => "VARBINARY($charLongLength)", + 'char_nocase' => 'VARCHAR(255) NOT NULL COLLATE NOCASE', + 'char_long_nocase' => "VARCHAR($charLongLength) NOT NULL COLLATE NOCASE", + 'usage_count' => 'INT(8)', + 'integer_unsigned' => 'INTEGER' + ]; + + return FieldType::mapType( $fieldType, $fieldTypes ); + } + + /** Create */ + + /** + * @since 2.5 + * + * {@inheritDoc} + */ + protected function doCreateTable( $tableName, array $attributes = null ) { + + $mode = ''; + $option = ''; + + $ftsOptions = null; + $tableName = $this->connection->tableName( $tableName ); + + if ( isset( $attributes['fulltextSearchTableOptions']['sqlite'] ) ) { + $ftsOptions = $attributes['fulltextSearchTableOptions']['sqlite']; + } + + // Filter extra module options + // @see https://www.sqlite.org/fts3.html#fts4_options + // + // $smwgFulltextSearchTableOptions can define: + // - 'sqlite' => array( 'FTS4' ) + // - 'sqlite' => array( 'FTS4', 'tokenize=porter' ) + if ( $ftsOptions !== null && is_array( $ftsOptions ) ) { + $mode = isset( $ftsOptions[0] ) ? $ftsOptions[0] : ''; + $option = isset( $ftsOptions[1] ) ? $ftsOptions[1] : ''; + } elseif ( $ftsOptions !== null ) { + $mode = $ftsOptions; + } + + $fieldSql = []; + $fields = $attributes['fields']; + + foreach ( $fields as $fieldName => $fieldType ) { + $fieldSql[] = "$fieldName " . $this->getStandardFieldType( $fieldType ); + } + + if ( $mode === '' ) { + $sql = 'CREATE TABLE ' . $tableName .'(' . implode( ',', $fieldSql ) . ') '; + } else { + $sql = 'CREATE VIRTUAL TABLE ' . $tableName . ' USING ' . strtolower( $mode ) .'(' . implode( ',', $fieldSql ) . $option . ') '; + } + + $this->connection->query( $sql, __METHOD__ ); + } + + /** Update */ + + /** + * @since 2.5 + * + * {@inheritDoc} + */ + protected function doUpdateTable( $tableName, array $attributes = null ) { + + $tableName = $this->connection->tableName( $tableName ); + $currentFields = $this->getCurrentFields( $tableName ); + + $fields = $attributes['fields']; + $position = 'FIRST'; + + // Loop through all the field definitions, and handle each definition for either postgres or MySQL. + foreach ( $fields as $fieldName => $fieldType ) { + $this->doUpdateField( $tableName, $fieldName, $fieldType, $currentFields, $position, $attributes ); + + $position = "AFTER $fieldName"; + $currentFields[$fieldName] = false; + } + + // The updated fields have their value set to false, so if a field has a value + // that differs from false, it's an obsolete one that should be removed. + foreach ( $currentFields as $fieldName => $value ) { + if ( $value !== false ) { + $this->doDropField( $tableName, $fieldName, $attributes ); + } + } + } + + private function getCurrentFields( $tableName ) { + + $sql = 'PRAGMA table_info(' . $tableName . ')'; + + $res = $this->connection->query( $sql, __METHOD__ ); + $currentFields = []; + + foreach ( $res as $row ) { + $row->Field = $row->name; + $row->Type = $row->type; + $type = $row->type; + + if ( $row->notnull == '1' ) { + $type .= ' NOT NULL'; + } + + if ( $row->pk == '1' ) { + $type .= ' PRIMARY KEY AUTOINCREMENT'; + } + + $currentFields[$row->Field] = $type; + } + + return $currentFields; + } + + private function doUpdateField( $tableName, $fieldName, $fieldType, $currentFields, $position, array $attributes ) { + + if ( !isset( $this->activityLog[$tableName] ) ) { + $this->activityLog[$tableName] = []; + } + + $fieldType = $this->getStandardFieldType( $fieldType ); + $default = ''; + + if ( isset( $attributes['defaults'][$fieldName] ) ) { + $default = "DEFAULT '" . $attributes['defaults'][$fieldName] . "'"; + } + + if ( !array_key_exists( $fieldName, $currentFields ) ) { + $this->doCreateField( $tableName, $fieldName, $position, $fieldType, $default ); + } elseif ( $currentFields[$fieldName] != $fieldType ) { + $this->doUpdateFieldType( $tableName, $fieldName, $position, $currentFields[$fieldName], $fieldType ); + } else { + $this->reportMessage( " ... field $fieldName is fine.\n" ); + } + } + + private function doCreateField( $tableName, $fieldName, $position, $fieldType, $default ) { + + if ( strpos( $tableName, 'ft_search' ) !== false ) { + return $this->reportMessage( " ... virtual tables can not be altered in SQLite ...\n" ); + } + + $this->activityLog[$tableName][$fieldName] = self::PROC_FIELD_NEW; + + if ( $default === '' ) { + // @see https://www.sqlite.org/lang_altertable.html states that + // "If a NOT NULL constraint is specified, then the column must have a default value other than NULL." + $default = "DEFAULT NULL"; + + // Add DEFAULT '' to avoid + // Query: ALTER TABLE sunittest_rdbms_test ADD `t_num` INT(8) NOT NULL + // Function: SMW\SQLStore\TableBuilder\SQLiteTableBuilder::doCreateField + // Error: 1 Cannot add a NOT NULL column with default value NULL + if ( strpos( $fieldType, 'NOT NULL' ) !== false ) { + $default = "DEFAULT ''"; + } + } + + $this->reportMessage( " ... creating field $fieldName ... " ); + $this->connection->query( "ALTER TABLE $tableName ADD `$fieldName` $fieldType $default", __METHOD__ ); + $this->reportMessage( "done.\n" ); + } + + private function doUpdateFieldType( $tableName, $fieldName, $position, $oldFieldType, $newFieldType ) { + $this->reportMessage( " ... changing field type is not supported in SQLite (http://www.sqlite.org/omitted.html) \n" ); + $this->reportMessage( " Please delete and reinitialize the tables to remove obsolete data, or just keep it.\n" ); + } + + private function doDropField( $tableName, $fieldName, $attributes ) { + + $this->activityLog[$tableName][$fieldName] = self::PROC_FIELD_DROP; + + $fields = $attributes['fields']; + $temp_table = "{$tableName}_temp"; + + // https://stackoverflow.com/questions/5938048/delete-column-from-sqlite-table + // Deleting obsolete fields is not possible in SQLite therefore create a + // temp table, copy the content, remove the table with obsolete/ fields, + // and rename the temp table + $field_def = []; + $field_list = []; + + foreach ( $fields as $field => $type ) { + $field_def[] = "$field " . $this->getStandardFieldType( $type ); + $field_list[] = $field; + } + + $this->reportMessage( " ... field $fieldName is obsolete ...\n" ); + $this->reportMessage( " ... creating a temporary table ...\n" ); + $this->connection->query( 'DROP TABLE IF EXISTS ' . $temp_table, __METHOD__ ); + $this->connection->query( 'CREATE TABLE ' . $temp_table .' (' . implode( ',', $field_def ) . ') ', __METHOD__ ); + $this->reportMessage( " ... copying table contents ...\n" ); + $this->connection->query( 'INSERT INTO ' . $temp_table . ' SELECT ' . implode( ',', $field_list ) . ' FROM ' . $tableName, __METHOD__ ); + $this->reportMessage( " ... dropping table with obsolete field definitions ...\n" ); + $this->connection->query( 'DROP TABLE IF EXISTS ' . $tableName, __METHOD__ ); + $this->reportMessage( " ... renaming temporary table to $tableName ...\n" ); + $this->connection->query( 'ALTER TABLE ' . $temp_table . ' RENAME TO ' . $tableName, __METHOD__ ); + $this->reportMessage( " ... done.\n" ); + } + + /** Index */ + + /** + * @since 2.5 + * + * {@inheritDoc} + */ + protected function doCreateIndices( $tableName, array $indexOptions = null ) { + + $indices = $indexOptions['indices']; + $ix = []; + + // In case an index has a length restriction indexZ(200), remove it since + // SQLite doesn't know such syntax + foreach ( $indices as $k => $columns ) { + $ix[$k] = preg_replace("/\([^)]+\)/", "", $columns ); + } + + $indices = $ix; + + // First remove possible obsolete indices + $this->doDropObsoleteIndices( $tableName, $indices ); + + // Add new indexes. + foreach ( $indices as $indexName => $index ) { + // If the index is an array, it contains the column + // name as first element, and index type as second one. + if ( is_array( $index ) ) { + $columns = $index[0]; + $indexType = count( $index ) > 1 ? $index[1] : 'INDEX'; + } else { + $columns = $index; + $indexType = 'INDEX'; + } + + $this->doCreateIndex( $tableName, $indexType, $indexName, $columns, $indexOptions ); + } + } + + private function doDropObsoleteIndices( $tableName, array &$indices ) { + + $currentIndices = $this->getIndexInfo( $tableName ); + + // TODO We do not currently get the right column definitions in + // SQLite; hence we can only drop all indexes. Wasteful. + foreach ( $currentIndices as $indexName => $indexColumn ) { + $this->doDropIndex( $tableName, $indexName, $indexColumn ); + } + } + + private function getIndexInfo( $tableName ) { + + $tableName = $this->connection->tableName( $tableName ); + $indices = []; + + $res = $this->connection->query( 'PRAGMA index_list(' . $tableName . ')', __METHOD__ ); + + if ( !$res ) { + return []; + } + + foreach ( $res as $row ) { + /// FIXME The value should not be $row->name below?! + if ( !array_key_exists( $row->name, $indices ) ) { + $indices[$row->name] = $row->name; + } else { + $indices[$row->name] .= ',' . $row->name; + } + } + + return $indices; + } + + private function doDropIndex( $tableName, $indexName, $columns ) { + $this->reportMessage( " ... removing index $columns ..." ); + $this->connection->query( 'DROP INDEX ' . $indexName, __METHOD__ ); + $this->reportMessage( "done.\n" ); + } + + private function doCreateIndex( $tableName, $indexType, $indexName, $columns, array $indexOptions ) { + + if ( $indexType === 'FULLTEXT' ) { + return $this->reportMessage( " ... skipping the fulltext index creation ..." ); + } + + if ( strpos( $tableName, 'ft_search' ) !== false ) { + return $this->reportMessage( " ... virtual tables can not be altered in SQLite ...\n" ); + } + + $tableName = $this->connection->tableName( $tableName ); + $indexName = "{$tableName}_index{$indexName}"; + + $this->reportMessage( " ... creating new index $columns ..." ); + $this->connection->query( "CREATE $indexType $indexName ON $tableName ($columns)", __METHOD__ ); + $this->reportMessage( "done.\n" ); + } + + /** Drop */ + + /** + * @since 2.5 + * + * {@inheritDoc} + */ + protected function doDropTable( $tableName ) { + $this->connection->query( 'DROP TABLE ' . $this->connection->tableName( $tableName ), __METHOD__ ); + } + + /** + * @since 3.0 + * + * {@inheritDoc} + */ + protected function doOptimize( $tableName ) { + + $this->reportMessage( "Checking table $tableName ...\n" ); + + // https://sqlite.org/lang_analyze.html + $this->reportMessage( " ... analyze " ); + $this->connection->query( 'ANALYZE ' . $this->connection->tableName( $tableName ), __METHOD__ ); + + $this->reportMessage( "done.\n" ); + } + +} |