Delete a no more used column or rename a column

[GhaziTriki]

How can we delete a no more used column or rename a column in cortex?

[ikkez]

you can do that with the schema plugin, which is used by cortex itself. You can write your own cleanup or migration script with it ;)

[GhaziTriki]

@ikkez Any example?

[ikkez]

Well for removing fields, have a look at the setup method.. there's already something built in but not active yet for reasons ;) but you can probably make your own method with that lines:

f3-cortex/lib/db/cortex.php

Lines 406 to 409 in 425cb69

	// remove unused fields
	// foreach ($existingCols as $col)
	// if (!in_array($col, array_keys($fields)) && $col!='id')
	// $table->dropColumn($col);

Regarding renaming fields: there's also a method in the schema plugin for renaming columns but it requires that you know how the field was named before and that it's not just a new field and the old one was removed. So with a bit planning and creativity you can handle it, but I have no sample for that laying around here.

For updating tables and column types based on a transportable schema/migration file, I have this rudimentary script here which basically works but hasn't all cases build in yet:

/**
 * read db schema und save to file
 * @param \Base $f3
 * @param $params
 */
function schema_read( \Base $f3,$params) {
	$schema = new \DB\SQL\Schema(\Registry::get('DB'));
	$tables = $schema->getTables();

	$table_cols = [];
	foreach($tables as $table) {
		$table_cols[$table] = $schema->alterTable($table)->getCols(true);;
	}
	$f3->write('schema_table_fields.json',json_encode($table_cols));
}

/**
 * read schema file and try to update db
 * @param \Base $f3
 * @param $params
 */
function schema_write( \Base $f3,$params) {

	$exec = false;
	if ($f3->exists('GET.exec',$e) && $e == true)
		$exec = true;

	$new_schema = $f3->read('schema_table_fields.json');
	if (!$new_schema) {
		$f3->error(500,'Schema File not found');
	}
	$new_schema = json_decode($new_schema,true);

	$schema = new \DB\SQL\Schema(\Registry::get('DB'));
	$tables = $schema->getTables();

	$migrate_sql = array();
	$msg = array();

	foreach ( $new_schema as $table => $new_cols) {

		if (!in_array($table,$tables)) {
			// missing table
			$msg[] = "missing table: ".$table;
			$t = $schema->createTable($table);
			foreach($new_cols as $name=>$conf) {
				$c = $t->addColumn($name);
				$c->type($conf['type'],true);
			}
			$migrate_sql[] = $t->build($exec);

		} else {
			$this_table = $schema->alterTable($table);
			$this_table_cols = $this_table->getCols(true);

			// check fields
			foreach ($new_cols as $col => $conf) {

				if (!isset($this_table_cols[$col])) {
					// column is missing
					$msg[] = "column is missing: ".$table.'.'.$col;
					$t = $schema->alterTable($table);
					$c = $t->addColumn($col);
					$c->type($conf['type'],true);
					$migrate_sql[] = $t->build($exec);
				} else {
					// compare types
					if ($this_table_cols[$col]['type'] != $conf['type']) {
						$msg[] = "column type mismatch: $table.$col is '".$this_table_cols[$col]['type']."' but should be '".$conf['type']."'";
						$t = $schema->alterTable($table);
						$c = $t->updateColumn($col,$conf['type'],true);
						$migrate_sql[] = $t->build($exec);
					}
				}
			}
		}
	}

	foreach($migrate_sql as &$sql) {
		if (is_array($sql))
			$sql = implode("\n",$sql);
	}

	$out = $exec ? '<b>Build:</b><br/>' : '<b>Simulation:</b><br/>';
	$out.= implode("<br/>",$msg);
	if(!$exec)
		$out.= '<br/><br/>'.implode("<br/>",$migrate_sql);
	echo $out;
}

hope that helps for a start. I think there's a database setup/update routine in https://www.pathfinder-w.space/ which is pretty great and uses cortex too. Maybe you can find a better starting point there.. You'll find that repo here https://github.com/exodus4d/pathfinder