yii2: Support for missing sqlite3 ALTER TABLE commands

Sure!

    /**
     * Builds a SQL statement for dropping a DB column.
     * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method.
     * @param string $column the name of the column to be dropped. The name will be properly quoted by the method.
     * @return string the SQL statement for dropping a DB column.
     * @throws NotSupportedException this is not supported by SQLite
     * @autohr santilin <software@noviolento.es>
     */
    public function dropColumn($tableName, $column)
    {
        // Simulate ALTER TABLE ... DROP COLUMN ...
        // Get the CREATE TABLE statement used to create this table
        $create_table = $this->db->createCommand("select SQL from SQLite_Master where tbl_name = '$tableName' and type='table'")->queryScalar();
        if ($create_table == NULL ) {
            throw new InvalidParamException("Table not found: $tableName");
		}
		// Parse de CREATE TABLE statement to skip any use of this column, namely field definitions and FOREIGN KEYS
        $code = (new SqlTokenizer($create_table))->tokenize();
        $pattern = (new SqlTokenizer('any CREATE any TABLE any()'))->tokenize();
        if (!$code[0]->matches($pattern, 0, $firstMatchIndex, $lastMatchIndex)) {
			throw new InvalidParamException("Table not found: $tableName");
        }
        // Get the fields definition and foreign keys tokens
        $fieldsDef = $code[0][$lastMatchIndex - 1];
        
        $ddl_fields_def = '';
        $sql_fields_to_insert = [];
        $skipping = false;
        $column_found = false;
        $quoted_column = $this->db->quoteColumnName($column);
        $offset = 0;
        // Traverse the tokens looking for either an identifier (field name) or a foreign key
        while( $fieldsDef->offsetExists($offset)) {
			$token = $fieldsDef[$offset++];
			// These searchs could be done whit another SqlTokenizer, but I don't konw how to do them, the documentation for sqltokenizer si really scarse.
			if( $token->type == \yii\db\SqlToken::TYPE_IDENTIFIER ) {
				$identifier = (string)$token;
				if( $identifier == $column || $identifier == $quoted_column) {
					// found column definition for $column, set skipping on up until the next ,
					$column_found = $skipping = true;
				} else {
					// another column definition, keep and add to list of fields to select back
					$sql_fields_to_insert[] = $identifier;
					$skipping = false;
				}
			} else if( $token->type == \yii\db\SqlToken::TYPE_KEYWORD) {
				$keyword = (string)$token;
				if( $keyword == "FOREIGN" ) {
					// Foreign key found
					$other_offset = $offset;
					while( $fieldsDef->offsetExists($other_offset) && $fieldsDef[$other_offset]->type != \yii\db\SqlToken::TYPE_PARENTHESIS) {
						++$other_offset;
					}
					$foreign_field = (string)$fieldsDef[$other_offset];
					if ($foreign_field == $column || $foreign_field == $quoted_column) {
						// Found foreign key for $column, skip it
						$skipping = true;
						$offset = $other_offset;
					}
				}
			} else {
				/// @todo is there anything else. Look it up in the sqlite docs
				die("Unexpected: $token");
			}
			if( !$skipping ) {
				$ddl_fields_def .= $token . " ";
			}
			// Skip or keep until the next ,
			while( $fieldsDef->offsetExists($offset) ) {
				$skip_token = $fieldsDef[$offset];
				if( !$skipping ) {
					$ddl_fields_def .= (string)$skip_token . " ";
				}
				if ($skip_token->type == \yii\db\SqlToken::TYPE_OPERATOR && (string)$skip_token == ',') {
					$ddl_fields_def .= "\n";
					++$offset;
					$skipping = false;
					break;
				}
				++$offset;
			}
		}
		if (!$column_found) {
			throw new InvalidParamException("column '$column' not found in table '$tableName'");
		}
		$create_query = 
 			"BEGIN;\n" /// @todo investigar inmediate
			. "PRAGMA foreign_keys = OFF;\n"
            . "PRAGMA triggers = NO;\n"
 			. "CREATE TABLE {{temp_$tableName}} AS SELECT * FROM {{" . $tableName . "}};\n"
			. "DROP TABLE {{" . $tableName . "}};\n"
			// Remove trailing , from $ddl_fields_def if any
			. "CREATE TABLE {{" . $tableName . "}} (" . trim($ddl_fields_def, " \n\r\t,") . ");\n"
			. "INSERT INTO {{" . $tableName . "}} SELECT " . join(",", $sql_fields_to_insert) . " FROM {{temp_$tableName}};\n"
			. "DROP TABLE `temp_$tableName`;\n";
			
		// Indexes. Skip any index referencing $column
		// Get all indexes on this table
        $indexes = $this->db->createCommand("select SQL from SQLite_Master where tbl_name = '$tableName' and type='index'")->queryAll();
        foreach( $indexes as $key => $index ) {
			$code = (new SqlTokenizer($index["sql"]))->tokenize();
			$pattern = (new SqlTokenizer('any CREATE any INDEX any ON any()'))->tokenize();
			// Extract the list of fields of this index
			if (!$code[0]->matches($pattern, 0, $firstMatchIndex, $lastMatchIndex)) {
				throw new InvalidParamException("Table not found: $tableName");
			}
			$found = false;
			$indexFieldsDef = $code[0][$lastMatchIndex - 1];
			$offset = 0;
			while( $indexFieldsDef->offsetExists($offset) ) {
				$token = $indexFieldsDef[$offset];
				if( $token->type == \yii\db\SqlToken::TYPE_IDENTIFIER) {
					if( (string)$token == $column || (string)$token == $quoted_column) {
						$found = true;
						break;
					}
				}
				++$offset;
			}
			if (!$found) {
				// If the index contains this column, do not add it to the create table statement
				$create_query .= $index["sql"] . ";\n";
			}
		}
		$create_query .=  "PRAGMA foreign_keys = YES;\n"
			. "COMMIT;";
		return $create_query;
    }

I have tested it and it works correctly. The code to parse the foreign key could be improved with anothe SqlTokenizer, but I have not found a good documentation on using SqlTokenizer.

I’m planning to make all the other DDL operations, creating a protected function with all the common code, as all the operations work more or less the same way.

Sorry to say that, but considering the complexity of the method this does not look like something we could reliably maintain within Yii core. It simulates a feature of sqlite, which is not natively supported so it could quite well exist as an extension providing additional functionality in case someone needs this. Its not a common problem so imo it is too special to be in Yii core.