Schema Changes and Deployment¶
During the life time of your application there will schema changes:
- new tables will be created,
- obsolete tables will be dropped,
- tables will be renamed,
- table options will change,
- new columns will added to a table,
- obsolete columns will be drop from a table,
- columns will be renamed,
- column types will change.
In this chapter we discuss how to handle all these types of changes. Also, we discuss how to deploy schema changes on the production environment.
PhpAudit becomes with two commands that helps you to compare the data schema
with the audit schema
:
- The
diff
command, see The diff command. - The
alter-audit-table
command, see The alter-audit-table command.
Schema Changes¶
In this section we discuss all possible schema changes one by one. You can combine many schema changes on one go.
New Table¶
When adding a new table to the database of your application you must decide whether auditing is required for this table.
- Run the DDL statements for creating the new table.
- Run the
audit
command of PhpAudit. PhpAudit will report that it has found a new table.- Auditing is not required for the new table:
- Set the audit flag for the new table to
false
.
- Set the audit flag for the new table to
- Auditing is required for the new table:
- Set the audit flag for the new table to
true
. - Run the
audit
command of PhpAudit again. This time an audit table and audit triggers will be created for the new table.
- Set the audit flag for the new table to
- Auditing is not required for the new table:
- Commit the changes in the audit config file to your VCS.
Obsolete Table¶
- Run the DDL statements for dropping the obsolete table.
- Run the
audit
command of PhpAudit. PhpAudit will report that it has found an obsolete table.- PhpAudit will remove the obsolete table from the tables section.
- PhpAudit will not drop the table from the
audit schema
. The corresponding table in theaudit schema
is still a part of your application’s audit trail.
- Commit the changes in the audit config file to your VCS.
If you decide now or later that the corresponding table in the audit schema
is not longer required you must drop the corresponding table in the audit schema
your self. This does not affect PhpAudit at all nor requires any action by PhpAudit.
Renamed Table¶
When you rename a table in the data schema
there is no reliable way for PhpAudit to detect a table has been renamed. PhpAudit will see an obsolete and a new table.
- Run the DDL statements for renaming the table in the
data schema
. - Run similar DDL statements for renaming the corresponding table in the
audit schema
. - Rename the table in the tables section of the audit config file.
- At this moment the audit triggers on the table in
data schema
are still using the old table name in theaudit schema
.
- At this moment the audit triggers on the table in
- Run the
audit
command of PhpAudit.- The audit triggers on the table in
data schema
are using new table name in theaudit schema
now.
- The audit triggers on the table in
- Commit the changes in the audit config file to your VCS.
If you omit renaming the table in the audit config file, PhpAudit will report an obsolete table and a new table. I this case you must restore the table configuration (i.e. the audit file, alias and skip variable) in the aut config file.
If you omit renaming the corresponding table in the audit schema
, PhpAudit will create a new table in the audit schema
.
Table Options¶
- Run the DDL statements for altering the table options of the table in the
data schema
. - Run similar DDL statements for altering the table options of the corresponding table in the
audit schema
.
PhpAudit is unaware of most table options. It only considers the following table options:
- CHARACTER SET
- COLLATE
- ENGINE
Running the audit
command of PhpAudit will not affect the table options of any table in the audit schema
.
See XXX for a discussing about transactional and non transaction storage engines.
New Column¶
- Run the DDL statements for adding the new column to the table
data schema
. - Run the
audit
command of PhpAudit.- The new column will be added to the corresponding table in the
audit schema
and added to the queries in the audit triggers.
- The new column will be added to the corresponding table in the
Obsolete Column¶
- Run the DDL statements for dropping the obsolete column from the table
data schema
. - Run the
audit
command of PhpAudit.- The obsolete column will be removed from the queries in the audit triggers.
- The obsolete column in the corresponding table in the
audit schema
is still a part of your application’s audit trail and will not be dropped.
If you decide now or later that the obsolete column in the corresponding table in the audit schema
is not longer required you must drop the obsolete column in the corresponding table in the audit schema
your self. This does not affect PhpAudit at all nor requires any action by PhpAudit.
Renamed Column¶
When you rename a column of a table in the data schema
there is no reliable way for PhpAudit to detect a column has been renamed. PhpAudit will see an obsolete and a new column.
- Run the DDL statements for renaming the column of the table in the
data schema
. - Run similar DDL statements for renaming the column of the corresponding table in the
audit schema
.- At this moment the audit triggers on the table in
data schema
are still using the old column name.
- At this moment the audit triggers on the table in
- Run the
audit
command of PhpAudit.- The audit triggers on the table in the
data schema
are using the new column name now.
- The audit triggers on the table in the
Changed Column Type¶
We consider two types of column type changes:
- Changing the column type to a more comprehensive column type. For example:
varchar(10) charset utf8 collation utf8_general_ci
=>varchar(20) charset utf8 collation utf8_general_ci
varchar(80) charset ascii collation ascii_general_ci
=>varchar(80) charset utf8 collation utf8_general_ci
smallint(4)
=>int(6)
- Changing the column type to a less comprehensive or incompatible column type: For example:
varchar(10) charset utf8 collation utf8_general_ci
=>int(10)
varchar(80) charset utf8 collation utf8_general_ci
=>varchar(80) charset latin1 collation latin1_general_ci
longblob
=>medium text
Currently, automatically modification of columns of tables in the audit schema
is not implemented and planned for a future release.
We consider three kinds of less comprehensive or incompatible column types:
The audit trail does not contain any data that cannot be converted to the new column type. For example:
- A
varchar(10)
that holds only integers (as strings) in both the data and audit table can be modified to anint(10)
without any issues. - A
varchar(80) charset utf8 collation utf8_general_ci
that holds only latin1 characters in both the data and audit table can be modified to anvarchar(80) charset latin1 collation latin1_general_ci
without any issues.
- A
The audit trail does contain data that cannot be converted to the new column type however a more comprehensive column type (for the actual data in both columns in the
data schema
andaudit schema
) is available. For example:- A
varchar(10) charset utf8 collation utf8_general_ci
(that must be modified tovarchar(30) charset latin1 collation latin1_general_ci
) that holds only latin1 characters in the data table, but the audit table holds data outside the latin1 character set. In this case the column in thedata schema
can be converted tovarchar(30) charset latin1 collation latin1_general_ci
and the column in theaudit schema
can be converted tovarchar(30) charset utf8 collation utf8_general_ci
.
- A
The audit trail does contain data that cannot be converted to the new column type and a more comprehensive column type is not available. For example:
- A
varbinary(10)
(that must be modified toint(10)
) column holding binary in the audit trail but not any more in the data table.
In this case to only solution is to rename the column in the audit table. The
audit
command of PhpAudit will create a new column in the audit table with the new column type.- A
Deployment¶
In the above section we discuss all possible schema changes one by one. of course you can combine all schema changes in one go. The basic rules are simple:
- Renaming tables:
- Rename the tables in the
data schema
. - Rename the corresponding tables in the
audit schema
. - Rename the tables in the audit config file.
- Rename the tables in the
- Renaming columns:
- Rename the columns in the
data schema
. - Rename the columns in the corresponding tables in the
audit schema
.
- Rename the columns in the
- Changing column types:
- Change the column types in the
data schema
. - Change the column types in the corresponding tables in the
audit schema
. - See Changed Column Type for incompatible column type changes.
- Change the column types in the
- Run the
audit
command of PhpStratum.
Simple Deployment¶
If your deployment script has only DDL statements (affecting tables that require auditing), followed by only (or none) DML statements (affecting tables that require auditing), it is called a simple deployment. You must your deployment as scripts as follows:
Complex Deployment¶
If your deployment script has only DDL statements (affecting tables that require auditing), followed by only (or none) DML statements (affecting tables that require auditing), followed by only DDL statements (affecting tables that require auditing), followed by only (or none) DML statements (affecting tables that require auditing) and so on, it is called a complex deployment. You must your deployment as scripts as follows:
- Run DDL statements.
- Run the
audit
command of PhpAudit (with the latest version of you audit config file).- Use the latest version of your audit config file.
- Make sure that the audit flags for are still correct.
- All audit tables and triggers are in a proper state to capture the data changes caused by the following DML statements.
- Run DML statements.
- Run DDL statements.
- Run the
audit
command of PhpAudit (with the latest version of you audit config file).- Use the latest version of your audit config file.
- Make sure that the audit flags for are still correct.
- All audit tables and triggers are in a proper state to capture the data changes caused by the following DML statements.
- Run DML statements.
- and so on