TICC - Transactional Iterative Change Control
(Or: ITCH - Iterative Transaction CHanges?)
Ticc is a system for iteratively managing changes to a database.
Ticc currently only works with PostgreSQL, and by design requires a database management system that supports transactional DDL. Ticc may work with other database management systems in the future. If you are interested in adding support for other back-ends, that would be great!
You might be interested in this if you design databases, especially with PostgreSQL. You may be interested in adapting it to whatever DBMS you are using.
SQL is, in many ways, a terrible language, but it is the native language of most modern DBMSs. Most migrations systems go through an ORM that severely limits what you can do. Even if you are accessing your data through an ORM, if you want to set up a powerful database with more advanced features like views, rules, and triggers, if you are trying in any way to design a quality database, you should want to use the native DDL of your preferred DBMS.
This tool simply makes it easier to do so.
I would love to see more DMBSs supported in the future (and not necessarily just SQL either), but for now, this is a PostgreSQL tool.
Ticc is directly inspired by a similar project called Sqitch. Sqitch is an awesome project and you should definitely check it out, too. I will try to give some guidance in deciding which is right for you, but I would recommend everybody doing SQL architecture use one or the other.
Like Sqitch, Ticc is an attempt to take the pain out of iterative data architecture, by giving you a framework to track a collection of changes and deploy or revert them as needed. Both projects focus on using the native DDL of your database, both are intended to work well with version control software like Git, and both are simple command line tools.
I am biased - I wrote Ticc and think it’s the way to go - but Sqitch is still great and there are definitely reasons some may use Sqitch.
The largest advantage to Sqitch as I see it is support for other back-ends. Ticc currently only supports PostgreSQL and due to feature differences, can never support as many back-end DBMSs as Sqitch. Sqitch can support DBMSs that do not have transactional DDL, or that do not have nested transactions or save-points; Ticc depends on those features.
Some may also prefer the Sqitch approach. Sqitch has explicitly written transactions inside change files, and follows a single clear linear plan file which you can edit yourself, deciding exactly how your plan will deploy, rather than letting the tool figure it out for you.
If anybody has more reasons they love Sqitch, please share!
Ticc manages transactions for you, and protects entire actions. This means that every time you issue a Ticc command, you can be sure it will either complete correctly, or fail and leave the database as it was, with no need to clean up.
Ticc ensures that every change is not only deployable, but verifiable and revertable, before committing it’s deployment. This means that you can work safely and not worry about breaking the synchronization of the Ticc plan and the actual database.
Ticc uses a previously tested script to revert each change when needed, so a bad revert script will never leave you with a change that cannot be reverted.
Ticc offers a single command, ticc sync, that will do the right
thing nearly all the time. It reverts any changes that are necessary
based on your modifications to the master plan, and then deploys
everything to make your deployed database match the plan.
Ticc works by constructing a master plan from a collection of DDL change files and plan fragments. The first thing you need is a project directory to keep your plan in. It is best to keep this in a source control repository like Git.
Every directory - the top level project directory and every directory under it - is considered to represent a “change”, although they need not all actually change something in your database.
You may nest changes under other changes. Change attributes, such as dependencies, are inherited by the changes beneath it, and every change implicitly depends on all the changes beneath it. Suppose that if you have:
Change AChange A1
Change B(explicitly depends onChange A)Change B1
Change B1 will inherit the explicit dependency, so it also depends
on Change A. Change A implicitly depends on Change A1. Change
B implicitly depends on Change B1. Therefore, these must be
performed in the following order:
Change A1Change AChange B1Change B
You may find it useful to group changes with similar dependencies under a common directory where those can be defined.
The config file defaults to ticc.json in the root of your plan.
Currently there is a single top level configuration key, database,
which can contain sub-keys to configure the database Ticc should
connect to.
database- Configure the database connection
engine- The PHP PDO engine to connect with. The default and
only supported option is
pgsql. You can try other PDO engines if you like, but no handling for other engines has been added, so I have no idea what it would do. host- The database host to connect to. Defaults to no host, which with the pgsql engine will connect to a local PostgreSQL over a socket.
port- The database port
name- The name of the database
ticc schema- The schema for Ticc to keep it’s own data in.
Defaults to
ticc, which should be fine, if it doesn’t conflict with something else.
If you are using PostgreSQL running on your local machine, you probably only need something like:
{
"database": {
"name": "my_database"
}
}Rationale: Dependencies should be defined close to the sql files they describe. When looking at any directory, we should not see multiple items representing the same thing, such as a plan file and a directory it describes.
Each directory in the plan represents a change. Each change may optionally contain any of:
- A
plan.jsonfile providing attributes for this change (and possibly sub-changes) - A set of scripts describing how to deploy this change. If any are
present, they all must be:
deploy.sql- DDL to deploy the change
verify.sql- A statement which will only succeed if the change was successfully deployed, such as selecting from a created table
revert.sql- DDL to revert the change
- One or more directories representing changes that should inherit attributes from this one.
ticc sync [ <change-name-to-sync-to> ]Revert any changes that are in name or script different from the master plan (the one represented by your plan directories), as well as any changes which depend directly or indirectly on those; then deploy all undeployed changes. This should sync your deployed database up with the master plan, and is usually the only command you should need.
If you provide the optional change-name-to-sync-to, the deploy phase
will be limited to the minimum set of changes necessary to deploy the
one given. This is primarily useful if you have some errors in your
plan, and are trying to work on one section without having Ticc try to
sync other parts that you know do not work.
ticc sync supply
ticc syncticc revert [ <change-name-to-revert-to> ]Revert all deployed changes.
If you provide the optional change-name-to-sync-to, the command will
be limited to the minimum set of changes necessary to revert the one
given. This is most likely to be useful in the case that you deployed
a Change with insufficient dependencies specified. This allows you to
force Ticc to revert Changes in an order that it might not otherwise
choose and correct the problem.
ticc revert supply
ticc revertticc deploy [ <change-name-to-deploy-to> ]Deploy the master plan (the one represented by your plan directories). Does not re-deploy any already deployed changes (by name, even if they are different from the version deployed).
If you provide the optional change-name-to-deploy-to, the command
will be limited to the minimum set of changes necessary to deploy the
one given.
Although you can use this to simply deploy Changes, it is generally better to use *Sync, which will correctly handle modified Changes.
ticc deploy supply
ticc deployticc verify [ <change-name-to-verify-to> ]Verify the master plan (the one represented by your plan directories). This does not deploy or revert anything, but serves two purposes.
It re-runs the verify script for each Change and confirms that it was deployed. If any Change with a verify script does not verify, it will throw an error. This may be helpful if you, for instance, want to confirm that your database hasn’t been modified outside of Ticc.
It will also mark the successfully verified Changes as being deployed. This may be useful if, somehow, Changes are deployed without Ticc knowing about it, and you want to mark them.
Warning: Since the Changes being verified may have dependent Changes already deployed, they cannot be fully tested. Ticc cannot revert and re-deploy them to confirm the scripts are correct. It can only run the verify script. This should only be used to mark Changes completed when you already know they are correct, and correctly deployed.
ticc verify supply
ticc verifyticc move [ <origin-change-name> <destination-change-name> ]Rename Change origin-change-name to destination-change-name. This
moves the Change directory in your plan; updates and dependencies on
it; and updates the deployed plan to reflect the new path. This does
not change the actual effective plan but is useful when refactoring
your plan, to arrange the plan change tree.
ticc move supply/create_supply_schema schemas/supply
ticc move supply/create_part_table tables/supply/part
ticc move supply/create_supplier_table tables/supply/supplier
ticc move logistics/create_logistics_schema schemas/logistics
ticc move logistics/create_route_table tables/logistics/routeI know a lot of languages. I am often tinkering around in Lisp. However, PHP is the language I know best, simply because people keep insisting on paying me to write stuff in it. I considered node.js for this as it’s what I’m learning right now, but I had a practical and immediate need for Ticc, so I decided to do it in PHP as I could get it working fastest that way. I am still considering rewriting it in a different language.
I have followed quite a number of code style guides in different places I’ve worked. I have my own opinions on what makes good style, and they are all based on practical experience and reasons. Since this is a personal project and, again, I wanted to get it running quickly, I’ve used my own style. I may add a style guide to this project explaining the actual reasons, but in the mean time, give it a try - if you get used to it, I bet you will prefer it.
There isn’t a great reason here. Mostly I was just rushing to get this to the point where it would be useful to me, developing as a single coder rather than on a team, and it’s a small project that is pretty manageable as-is. However, I would welcome pull requests improving all these things.
- At least PHP7
- A Postgres server (version to come)