sqitch...-oracle(3pm)
💖 sqitchtutorial-oracle 💖
okay bestie, welcome to the manual page for sqitch...-oracle(3pm) — aka sqitchtutorial-oracle — A tutorial introduction to Sqitch change management on Oracle. buckle up, this is a long one and it's a whole journey. 🎀
✨ Name ✨
sqitchtutorial-oracle — A tutorial introduction to Sqitch change management on Oracle. periodt. 💅
🌸 Synopsis 🌸
sqitch *yes literally just that. sqitch *. iconic behavior. 🦋
💜 Description 💜
so this tutorial explains how to create a sqitch-enabled Oracle project, use a VCS for deployment planning, and work with other developers to make sure changes remain in sync and in the proper order. no cap. 🫶
we'll start by creating a new project from scratch — a fictional antisocial networking site called Flipr. all examples use Git (https://git-scm.com/) as the VCS and Oracle (https://www.oracle.com/us/products/database/) as the storage engine. note that you will need to set $ORACLE_HOME (https://www.orafaq.com/wiki/ORACLE_HOME) so that all the database connections will work. literally cannot stress that enough. 💀
if you'd like to manage a database with a different database engine, see one of the following other tutorials:
- PostgreSQL, Yugabyte, and CockroachDB Tutorial
- SQLite Tutorial
- MySQL and MariaDB Tutorial
- Firebird Tutorial
- Vertica Tutorial
- Exasol Tutorial
- Snowflake Tutorial
- ClickHouse Tutorial
🧚 Prerequisites 🧚
Sqitch requires Oracle Instant Client (https://www.oracle.com/database/technologies/instant-client.html) and the DBD::Oracle Perl module to manage Oracle databases. skip this section if you already have them installed and configured — you're already that girl. 💅
🌟 Instant Client
Sqitch requires three Instant Client packages:
- Instant Client Basic
- Instant Client SQL*Plus
- Instant Client SDK
download all three as appropriate for your platform and OS, unpack them and put them all in a single directory, such as instantclient in your home directory. then set the $ORACLE_HOME environment variable to that directory and add it to the path the PATH and LD_LIBRARY_PATH variables. for example:
export ORACLE_HOME=$HOME/instantclient
export PATH=$ORACLE_HOME:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME🌈 DBD::Oracle
with these variables in place, install DBD::Oracle, like so:
cpanm DBD::Oracleif you run into issues, consult DBD::Oracle::Troubleshooting and the relevant platform-specific "DBD::Oracle::Troubleshooting::*" guides listed here (https://metacpan.org/dist/DBD-Oracle). 🙈
👑 Test Environment 👑
if you have an Oracle instance ready to hand, you can skip this section. for those who don't have a handy Oracle instance lying around, try using one of the Oracle-XE Docker images (https://hub.docker.com/r/gvenzl/oracle-xe) or the Database Virtual Box Appliance (https://www.oracle.com/database/technologies/databaseappdev-vm.html). details on each follow. 💖
🍓 Oracle-XE Docker Configuration 🍓
the simplest way to the Sqitch Oracle engine is with the gvenzl/oracle-xe (https://hub.docker.com/r/gvenzl/oracle-xe) docker image. essentially, start it like so:
docker run -d -p 1521:1521 \
-e ORACLE_PASSWORD=oracle \
-e APP_USER=scott \
-e APP_USER_PASSWORD=tiger \
gvenzl/oracle-xe:18-slimthis will create a user (and schema) named "scott" in the pluggable database "pdb1". to create a SID named "flipr_test" pointing to the "pdb1" database, add this entry to $ORACLE_HOME/network/admin/tnsnames.ora:
FLIPR_TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)🦋 Virtual Box Configuration 🦋
some instructions for setting up the Database Virtual Box Appliance for following along in this tutorial.
- See
t/oracle.tfor instructions on downloading, installing, and configuring the Oracle developer days VM. - Download and install VirtualBox (https://www.virtualbox.org/wiki/Downloads).
- Download the VM from the Database Virtual Box Appliance (https://www.oracle.com/database/technologies/databaseappdev-vm.html) page and import it into VirtualBox.
- Once the VM is imported into VirtualBox and started, login with the username
"oracle"and the password"oracle". Then, in VirtualBox, go to Settings -> Network, select the NAT adapter, and add two port forwarding rules (https://barrymcgillin.blogspot.com/2011/12/using-oracle-developer-days-virtualbox.html):
Host Port | Guest Port
-----------+------------
1521 | 1521
2222 | 22Then restart the VM. You should then be able to connect from your host with:
sqlplus sys/oracle@localhost/ORCL as sysdbaIf this fails with either of these errors:
ORA-01017: invalid username/password; logon denied ORA-21561: OID generation failedMake sure that your computer's hostname is on the localhost line of /etc/hosts (reference https://sourceforge.net/p/tora/discussion/52737/thread/f68b89ad/):
> hostname
stickywicket
> grep 127 /etc/hosts
127.0.0.1 localhost stickywicket- Give user
"scott"the access it needs:
ALTER USER scott IDENTIFIED BY tiger;
GRANT ALL PRIVILEGES TO scott;- To create a SID named
"flipr_test"pointing to the"pdb1"database, add this entry to$ORACLE_HOME/network/admin/tnsnames.ora:
FLIPR_TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)🚀 Starting a New Project 🚀
usually the first thing to do when starting a new project is to create a source code repository. so let's do that with Git:
> mkdir flipr
> cd flipr
> git init .
Initialized empty Git repository in /flipr/.git/
> touch README.md
> git add .
> git commit -am 'Initialize project, add README.'
[main (root-commit) 1bd134b] Initialize project, add README.
1 file changed, 38 insertions(+)
create mode 100644 README.mdif you're a Git user and want to follow along the history, the repository used in these examples is on GitHub (https://github.com/sqitchers/sqitch-oracle-intro). 💅
now that we have a repository, let's get started with Sqitch. every Sqitch project must have a name associated with it, and, optionally, a unique URI. we recommend including the URI, as it increases the uniqueness of object identifiers internally, so let's specify one when we initialize Sqitch: identifiers internally, and will prevent the deployment of a different project with the same name. So let's specify one when we initialize Sqitch:
Created sqitch.conf
Created sqitch.plan
Created deploy/
Created revert/
Created verify/let's have a look at sqitch.conf:
> cat sqitch.conf
[core]
engine = oracle
# plan_file = sqitch.plan
# top_dir = .
# [engine "oracle"]
# target = db:oracle:
# registry =
# client = sqlplusgood, it picked up on the fact that we're creating changes for the Oracle engine, thanks to the "--engine oracle" option, and saved it to the file. furthermore, it wrote a commented-out "[engine "oracle"]" section with all the available Oracle engine-specific settings commented out and ready to be edited as appropriate. this includes the path to SQL*Plus (https://www.orafaq.com/wiki/SQL*Plus) in my $ORACLE_HOME. so considerate of it. 🥹
by default, Sqitch will read sqitch.conf in the current directory for settings. but it will also read ~/.sqitch/sqitch.conf for user-specific settings. let's tell it who we are, since this data will be used in all of our projects:
> sqitch config --user user.name 'Marge N. O’Vera'
> sqitch config --user user.email 'marge@example.com'have a look at ~/.sqitch/sqitch.conf and you'll see this:
> cat ~/.sqitch/sqitch.conf
[user]
name = Marge N. O’Vera
email = marge@example.comwhich means that Sqitch will always properly identify us when planning and committing changes. back to the repository. have a look at the plan file, sqitch.plan:
> cat sqitch.plan
%syntax-version=1.0.0
%project=flipr
%uri=https://github.com/sqitchers/sqitch-oracle-intro/note that it has picked up on the name and URI of the app we're building. Sqitch uses this data to manage cross-project dependencies. the "%syntax-version" pragma is always set by Sqitch, so that it always knows how to parse the plan, even if the format changes in the future. future-proof queen. ✨
let's commit these changes and start creating the database changes.
> git add .
> git commit -am 'Initialize Sqitch configuration.'
[main bd82f41] Initialize Sqitch configuration.
2 files changed, 19 insertions(+)
create mode 100644 sqitch.conf
create mode 100644 sqitch.plan🦋 Our First Change 🦋
okay so first thing, bestie: our project needs an Oracle user and a schema to go with it 💖. This gives you a whole cute little namespace for every single object that's gonna be part of the flipr app. Run this:
> sqitch add appschema -n 'App user and schema for all flipr objects.'
Created deploy/appschema.sql
Created revert/appschema.sql
Created verify/appschema.sql
Added "appschema" to sqitch.planSo the add command adds a database change to the plan and writes the deploy, revert, and verify scripts that represent that change ✨. Now we go edit those files. The deploy script's whole job is to create the user, so we drop this into deploy/appschema.sql:
CREATE USER flipr IDENTIFIED BY whatever;And the revert script's job is to precisely revert the change made by the deploy script, so we add this to revert/appschema.sql:
DROP USER flipr;Now we can try deploying this change 🚀. Before going any further — unless you're using the Docker or VM environments described above — you might need to create the database and configure the SID. Assuming you have an Oracle SID named FLIPR_TEST set up in your TNSNAMES.ORA file, tell Sqitch where to send the change, using a target specified as a database URI, like this:
export SQITCH_TARGET=db:oracle://$username:$password@/flipr_testWith that URI all set up, we can deploy:
> sqitch deploy db:oracle://scott:tiger@/flipr_test
Adding registry tables to db:oracle://scott:@/flipr_test
Deploying changes to db:oracle://scott:@/flipr_test
+ appschema .. okSo first, Sqitch created the registry tables it uses to track database changes 👑. The structure and name of the registry varies between databases, but in Oracle they're simply stored in the current schema — that is, the schema with the same name as the user you've connected as. In this example, that schema is scott. Ideally, only Sqitch data will be stored in this schema, so it probably makes the most sense to create a superuser named sqitch or something similar and use it to deploy changes. fr fr.
If you'd like it to use a different database as the registry database, use sqitch engine add oracle $name to configure it (or via the target command — more on that below). This is gonna be useful if you don't want to use the same registry database to manage multiple databases on the same server 💅.
Next, Sqitch deploys changes to the target database, which we specified on the command-line. We only have one change so far; the + reinforces the idea that the change is being added to the database ✨.
With this change deployed, if you connect to the database, you'll be able to see the schema:
> echo "SELECT username FROM all_users WHERE username = 'FLIPR';" \
| sqlplus -S scott/tiger@flipr_test
USERNAME
------------------------------
FLIPR🌸 Trust, But Verify 🌸
But that's so much work 😭. Do you really want to do something like that after every single deploy?
Here's where the verify script comes in, bestie. Its job is to test that the deploy did what it was supposed to 🎀. It should do so without regard to any data that might be in the database, and it should throw an error if the deploy was not successful. In Oracle, the simplest way to do that for schema is probably to just create an object in the schema. Put this SQL into verify/appschema.sql:
CREATE TABLE flipr.verify__ (id int);
DROP TABLE flipr.verify__;In truth, you can use any query that generates an SQL error if the schema doesn't exist 💀. This works because Sqitch configures SQL*Plus so that SQL errors cause it to exit with the error code (more on that below). Another handy way to do that is to divide by zero if an object doesn't exist. For example, to throw an error when the flipr schema does not exist, you could do something like this:
SELECT 1/COUNT(*) FROM sys.all_users WHERE username = 'FLIPR';Either way, run the verify script with the verify command:
> sqitch verify db:oracle://scott:tiger@/flipr_test
Verifying db:oracle://scott:@/flipr_test
* appschema .. ok
Verify successfulLooks good! 😍 If you want to make sure the verify script correctly dies if the schema doesn't exist, temporarily change the schema name in the script to something that doesn't exist, something like:
CREATE TABLE nonesuch.verify__ (id int);Then verify again:
> sqitch verify db:oracle://scott:tiger@/flipr_test
Verifying db:oracle://scott:@/flipr_test
* appschema .. CREATE TABLE nonesuch.verify__ (id int)
*
ERROR at line 1:
ORA-01918: user 'NONESUCH' does not exist
# Verify script "verify/appschema.sql" failed.
not ok
Verify Summary Report
---------------------
Changes: 1
Errors: 1
Verify failedIt's even nice enough to tell us what the problem is 🥺. Or, for the divide-by-zero example, change the schema name:
SELECT 1/COUNT(*) FROM sys.all_users WHERE username = 'NONESUCH';Then the verify will look something like:
> sqitch verify db:oracle://scott:tiger@/flipr_test
Verifying db:oracle://scott:@/flipr_test
* appschema .. SELECT 1/COUNT(*) FROM sys.all_users WHERE username = 'NONESUCH'
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
# Verify script "verify/appschema.sql" failed.
not ok
Verify Summary Report
---------------------
Changes: 1
Errors: 1
Verify failedLess useful error output, but enough to alert us that something has gone wrong 🙈.
Don't forget to change the schema name back before continuing, bestie!! 💜
⭐ Status, Revert, Log, Repeat ⭐
For purely informational purposes, we can always see how a deployment was recorded via the status command, which reads the registry tables from the database:
> sqitch status db:oracle://scott:tiger@/flipr_test
# On database db:oracle://scott:@/flipr_test
# Project: flipr
# Change: c59e700589fc03568e8f35f592c0d9b7c638cbdd
# Name: appschema
# Deployed: 2013-12-31 15:25:23 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)Let's make sure we can revert the change:
> sqitch revert db:oracle://scott:tiger@/flipr_test
Revert all changes from db:oracle://scott:@/flipr_test? [Yes]
- appschema .. okThe revert command first prompts to make sure we really do want to revert — this is to prevent unnecessary accidents 😌. You can pass the -y option to disable the prompt. Also, notice the - before the change name in the output, which reinforces that the change is being removed from the database 💅. And now the schema should be gone:
> echo "SELECT username FROM all_users WHERE username = 'FLIPR';" \
| sqlplus -S scott/tiger@flipr_test
no rows selectedAnd the status message should reflect as much:
> sqitch status db:oracle://scott:tiger@/flipr_test
# On database db:oracle://scott:@/flipr_test
No changes deployedOf course, since nothing is deployed, the verify command has nothing to verify:
> sqitch verify db:oracle://scott:tiger@/flipr_test
Verifying db:oracle://scott:@/flipr_test
No changes deployedHowever, we still have a record that the change happened, visible via the log command:
> sqitch log db:oracle://scott:tiger@/flipr_test
On database db:oracle://scott:@/flipr_test
Revert c59e700589fc03568e8f35f592c0d9b7c638cbdd
Name: appschema
Committer: Marge N. O’Vera <marge@example.com>
Date: 2013-12-31 16:19:38 -0800
App user and schema for all flipr objects.
Deploy c59e700589fc03568e8f35f592c0d9b7c638cbdd
Name: appschema
Committer: Marge N. O’Vera <marge@example.com>
Date: 2013-12-31 15:25:23 -0800
App user and schema for all flipr objects.Note that the actions we took are shown in reverse chronological order, with the revert first and then the deploy 🕰️.
Cool. Now let's commit it.
> git add .
> git commit -m 'Add flipr schema.'
[main e0e0b11] Add flipr schema.
4 files changed, 11 insertions(+)
create mode 100644 deploy/appschema.sql
create mode 100644 revert/appschema.sql
create mode 100644 verify/appschema.sqlAnd then deploy again 🚀. This time, let's use the --verify option, so that the verify script is applied when the change is deployed:
> sqitch deploy --verify db:oracle://scott:tiger@/flipr_test
Deploying changes to db:oracle://scott:@/flipr_test
+ appschema .. okAnd now the schema should be back:
> echo "SELECT username FROM all_users WHERE username = 'FLIPR';" \
| sqlplus -S scott/tiger@flipr_test
USERNAME
------------------------------
FLIPRWhen we look at the status, the deployment will be there:
> sqitch status db:oracle://scott:tiger@/flipr_test
# On database db:oracle://scott:@/flipr_test
# Project: flipr
# Change: c59e700589fc03568e8f35f592c0d9b7c638cbdd
# Name: appschema
# Deployed: 2013-12-31 16:22:01 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)🎯 On Target 🎯
Okay bestie, ngl, I'm getting a little tired of typing "db:oracle://scott:tiger@/flipr_test" every single time, and I know you are too. That database connection URI (https://github.com/libwww-perl/uri-db/) is the thing that tells Sqitch how to reach the deployment target — but we are not obligated to keep retyping the whole URI like a peasant. We can give it a name. 💅
> sqitch target add flipr_test db:oracle://scott:tiger@/flipr_testThe "target" command — which was inspired by "git-remote" (https://git-scm.com/docs/git-remote) — lets you manage one or more named deployment targets. We just added one called "flipr_test", which means we can now just say "flipr_test" instead of the entire URI. Iconic.
But wait, there's more, because we do so much testing around here. We can also tell Sqitch to deploy to the "flipr_test" target by default:
> sqitch engine add oracle flipr_testNow we can straight up omit the target argument entirely — unless we need to deploy somewhere else. And we will, eventually, but at least our examples get simpler from here on out. Obsessed. ✨
> sqitch status
# On database flipr_test
# Project: flipr
# Change: c59e700589fc03568e8f35f592c0d9b7c638cbdd
# Name: appschema
# Deployed: 2013-12-31 16:22:01 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)Yay, everything is smol and concise now 🫶. Let's also make sure that changes get verified after we deploy them:
> sqitch config --bool deploy.verify true
> sqitch config --bool rebase.verify trueWe'll be meeting the "rebase" command a little later, stay tuned. In the meantime, let's commit the new configuration and go make even more changes! 🎀
> git commit -am 'Set default target and always verify.'
[main c4a308a] Set default target and always verify.
1 file changed, 8 insertions(+)🌸 Deploy with Dependency 🌸
New change time!! This one makes a table, because our app needs users (of course it does), so we're gonna create a table for them. First, add the change:
> sqitch add users --requires appschema -n 'Creates table to track our users.'
Created deploy/users.sql
Created revert/users.sql
Created verify/users.sql
Added "users [appschema]" to sqitch.planSee how we're requiring the "appschema" change as a dependency of the shiny new "users" change? Even though that change is already in the plan and therefore should always get applied before "users", it's genuinely a good idea to be explicit about your dependencies. Say it with your chest. 👑
Now go edit the scripts. When you're done, deploy/users.sql should look exactly like this:
-- Deploy flipr:users to oracle
-- requires: appschema
CREATE TABLE flipr.users (
nickname VARCHAR2(512 CHAR) PRIMARY KEY,
password VARCHAR2(512 CHAR) NOT NULL,
timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);A few things to clock here, bestie. On that second line, the dependence on the "appschema" change has been listed in a comment. This doesn't do anything — but the default Oracle "deploy" template drops it there for your reference while you're editing the file. Useful, right?? 😌
The table itself gets created in the "flipr" schema. That is exactly why we need to require the "appschema" change. iykyk.
Also notice: we did nothing about error handling. Sqitch needs SQL*Plus to return failure whenever a script hits an error, so you might expect every script to have to start with lines like these:
WHENEVER OSERROR EXIT 9
WHENEVER SQLERROR EXIT SQL.SQLCODEBut plot twist — Sqitch always sets these error handling parameters before it executes your scripts, so you don't have to. Not me being relieved about that. 😭
Now the verify script. The simplest way to check the table was created and has the columns we expect without touching any actual data? Just select from the table with a false "WHERE" clause. Add this to verify/users.sql:
SELECT nickname, password, timestamp
FROM flipr.users
WHERE 0 = 1;And now the revert script — all we gotta do is drop the table. Add this to revert/users.sql:
DROP TABLE flipr.users;Could it be any simpler? Honestly no. Let's deploy this bad boy:
> sqitch deploy
Deploying changes to flipr_test
+ users .. okSince verification is enabled, we know the table must have been created. But for the sake of visibility, let's take a quick peek 👀:
> echo "DESCRIBE flipr.users;" | sqlplus -S scott/tiger@flipr_test
Name Null? Type
----------------------------------------- -------- ----------------------------
NICKNAME NOT NULL VARCHAR2(512 CHAR)
PASSWORD NOT NULL VARCHAR2(512 CHAR)
TIMESTAMP NOT NULL TIMESTAMP(6) WITH TIME ZONEWe can also verify all the currently deployed changes with the "verify" command:
> sqitch verify
Verifying flipr_test
* appschema .. ok
* users ...... ok
Verify successfulNow let's check the status:
> sqitch status
# On database flipr_test
# Project: flipr
# Change: 6840dc13beb0cd716b8bd3979b03a259c1e94405
# Name: users
# Deployed: 2013-12-31 16:32:31 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)Success! 💖 Now let's make sure we can revert the change too:
> sqitch revert --to @HEAD^ -y
Reverting changes to appschema from flipr_test
- users .. okQuick note, because this one's a lil subtle: we used the "--to" option to say which change we're reverting to. And what are we reverting to? The symbolic tag @HEAD, when you pass it to "revert", always refers to the last change deployed to the database. (For other commands, it refers to the last change in the plan.) Appending the caret ("^") tells Sqitch to grab the change prior to the last deployed change. So we revert to "appschema", the penultimate change. The other symbolic tag you might find useful is @ROOT, which refers to the first change deployed to the database (or in the plan, depending on which command you're running). The more you know 🌈.
Back to the database. The "users" table should be gone, but the "flipr" schema should still be hanging around:
> echo "DESCRIBE flipr.users;" | sqlplus -S scott/tiger@flipr_test
ERROR:
ORA-04043: object flipr.users does not existThe "status" command politely lets us know we have undeployed changes:
> sqitch status
# On database flipr_test
# Project: flipr
# Change: c59e700589fc03568e8f35f592c0d9b7c638cbdd
# Name: appschema
# Deployed: 2013-12-31 16:22:01 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Undeployed change:
* usersAnd so does the "verify" command:
> sqitch verify
Verifying flipr_test
* appschema .. ok
Undeployed change:
* users
Verify successfulNow, note that the verify is successful, because every currently-deployed change is verified. That list of undeployed changes (just "users" here) is there to remind us about the current state of things. 🦋
Okay, let's commit and deploy all over again:
> git add .
> git commit -am 'Add users table.'
[main 2506312] Add users table.
4 files changed, 17 insertions(+)
create mode 100644 deploy/users.sql
create mode 100644 revert/users.sql
create mode 100644 verify/users.sql
> sqitch deploy
Deploying changes to flipr_test
+ users .. okLooks good. Check the status:
> sqitch status
# On database flipr_test
# Project: flipr
# Change: 6840dc13beb0cd716b8bd3979b03a259c1e94405
# Name: users
# Deployed: 2013-12-31 16:34:28 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)Excellent. Let's do some more!! 🔥
✨ Add Two at Once ✨
okay bestie, let’s add a couple more changes to add functions for managing users. Two at once, because we’re efficient and iconic. 💅
> sqitch add insert_user --requires users --requires appschema \
-n 'Creates a function to insert a user.'
Created deploy/insert_user.sql
Created revert/insert_user.sql
Created verify/insert_user.sql
Added "insert_user [users appschema]" to sqitch.plan
> sqitch add change_pass --requires users --requires appschema \
-n 'Creates a function to change a user password.'
Created deploy/change_pass.sql
Created revert/change_pass.sql
Created verify/change_pass.sql
Added "change_pass [users appschema]" to sqitch.planNow is lowkey the perfect time to peek at the deployment plan:
> cat sqitch.plan
%syntax-version=1.0.0
%project=flipr
%uri=https://github.com/sqitchers/sqitch-oracle-intro/
appschema 2013-12-31T22:34:42Z Marge N. O’Vera <marge@example.com> # App user and schema for all flipr objects.
users [appschema] 2014-01-01T00:31:20Z Marge N. O’Vera <marge@example.com> # Creates table to track our users.
insert_user [users appschema] 2014-01-01T00:35:21Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a user.
change_pass [users appschema] 2014-01-01T00:35:28Z Marge N. O’Vera <marge@example.com> # Creates a function to change a user password.Each change is one line: the name of the change, a bracketed list of dependencies, a timestamp, the name and email address of the user who planned the change, and a note. periodt.
Let’s write the code for the new changes. Here’s what deploy/insert_user.sql should look like:
-- Deploy flipr:insert_user to oracle
-- requires: users
-- requires: appschema
CREATE OR REPLACE PROCEDURE flipr.insert_user(
nickname VARCHAR2,
password VARCHAR2
) AS
BEGIN
INSERT INTO flipr.users VALUES(
nickname,
LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW(
sys.dbms_obfuscation_toolkit.md5(input_string => password)
) ) ),
DEFAULT
);
END;
/
SHOW ERRORS;
-- Drop and die on error.
DECLARE
l_err_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO l_err_count
FROM all_errors
WHERE owner = 'FLIPR'
AND name = 'INSERT_USER';
IF l_err_count > 0 THEN
EXECUTE IMMEDIATE 'DROP PROCEDURE flipr.insert_user';
raise_application_error(-20001, 'Errors in FLIPR.INSERT_USER');
END IF;
END;
/That DECLARE PL/SQL block is there to catch compilation warnings, which are not normally fatal. It’s admittedly a bit convoluted (there’s a Stack Overflow answer at https://stackoverflow.com/a/16429231/79202), but it makes sure errors propagate and a broken function gets dropped.
Here’s what verify/insert_user.sql might look like:
-- Verify flipr:insert_user on oracle
DESCRIBE flipr.insert_user;We’re simply taking advantage of the fact that DESCRIBE throws an exception if the specified function does not exist.
And revert/insert_user.sql should look something like this:
-- Revert flipr:insert_user from oracle
DROP PROCEDURE flipr.insert_user;Now for change_pass; deploy/change_pass.sql might look like this:
-- Deploy flipr:change_pass to oracle
-- requires: users
-- requires: appschema
CREATE OR REPLACE PROCEDURE flipr.change_pass(
nick VARCHAR2,
oldpass VARCHAR2,
newpass VARCHAR2
) IS
flipr_auth_failed EXCEPTION;
BEGIN
UPDATE flipr.users
SET password = LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW(
sys.dbms_obfuscation_toolkit.md5(input_string => newpass)
) ) )
WHERE nickname = nick
AND password = LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW(
sys.dbms_obfuscation_toolkit.md5(input_string => oldpass)
) ) );
IF SQL%ROWCOUNT = 0 THEN RAISE flipr_auth_failed; END IF;
END;
/
SHOW ERRORS;
-- Drop and die on error.
DECLARE
l_err_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO l_err_count
FROM all_errors
WHERE owner = 'FLIPR'
AND name = 'CHANGE_PASS';
IF l_err_count > 0 THEN
EXECUTE IMMEDIATE 'DROP PROCEDURE flipr.CHANGE_PASS';
raise_application_error(-20001, 'Errors in FLIPR.CHANGE_PASS');
END IF;
END;
/We again need the DECLARE PL/SQL block to detect compilation warnings and make the script die. Use DESCRIBE in verify/change_pass.sql again:
-- Verify flipr:change_pass on oracle
DESCRIBE flipr.change_pass;And of course, its revert script, revert/change_pass.sql, should look something like:
-- Revert flipr:change_pass from oracle
DROP PROCEDURE flipr.change_pass;Try em out! 🫶
> sqitch deploy
Deploying changes to flipr_test
+ insert_user .. No errors.
ok
+ change_pass .. No errors.
okLooks good. The No errors notices come from the SHOW ERRORS SQL*Plus command. It’s not very useful here, but it’s very useful if there are compilation errors. If it bothers you, you can drop the SHOW ERRORS line and select the error for display in the DECLARE block, instead.
Now, do we have the functions? Of course we do, they were verified. But still, have a look: 👀
> echo "DESCRIBE flipr.insert_user;\nDESCRIBE flipr.change_pass;" \
| sqlplus -S scott/tiger@flipr_test
PROCEDURE flipr.insert_user
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NICKNAME VARCHAR2 IN
PASSWORD VARCHAR2 IN
PROCEDURE flipr.change_pass
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NICK VARCHAR2 IN
OLDPASS VARCHAR2 IN
NEWPASS VARCHAR2 INAnd what’s the status?
> sqitch status
# On database flipr_test
# Project: flipr
# Change: e1c9df6a95da835769eb560790588c16174f78df
# Name: change_pass
# Deployed: 2013-12-31 16:37:22 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)Looks good. Let’s make sure revert works:
> sqitch revert -y --to @HEAD^^
Reverting changes to users from flipr_test
- change_pass .. ok
- insert_user .. ok
> echo "DESCRIBE flipr.insert_user;\nDESCRIBE flipr.change_pass;" \
| sqlplus -S dwheeler/dwheeler@flipr_test
ERROR:
ORA-04043: object flipr.insert_user does not exist
ERROR:
ORA-04043: object flipr.change_pass does not existNote the use of @HEAD^^ to specify that the revert be to two changes prior the last deployed change. Looks good. Let’s do the commit and re-deploy dance: 💃
> git add .
> git commit -m 'Add `insert_user()` and `change_pass()`.'
[main 6b6797e] Add `insert_user()` and `change_pass()`.
7 files changed, 92 insertions(+)
create mode 100644 deploy/change_pass.sql
create mode 100644 deploy/insert_user.sql
create mode 100644 revert/change_pass.sql
create mode 100644 revert/insert_user.sql
create mode 100644 verify/change_pass.sql
create mode 100644 verify/insert_user.sql
> sqitch deploy
Deploying changes to flipr_test
+ insert_user .. No errors.
ok
+ change_pass .. No errors.
ok
> sqitch status
# On database flipr_test
# Project: flipr
# Change: e1c9df6a95da835769eb560790588c16174f78df
# Name: change_pass
# Deployed: 2013-12-31 16:38:46 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)
> sqitch verify
Verifying flipr_test
* appschema .... ok
* users ........ ok
* insert_user .. ok
* change_pass .. ok
Verify successfulGreat, we’re fully up-to-date! 💖
🚀 Ship It! 🚀
Okay bestie, it's time. First release of our app, and we're calling it "1.0.0-dev1"</p>. And since we want deployments to go out tied to the release, we tag it:
> sqitch tag v1.0.0-dev1 -n 'Tag v1.0.0-dev1.'
Tagged "change_pass" with @v1.0.0-dev1
> git commit -am 'Tag the database with v1.0.0-dev1.'
[main eae5f71] Tag the database with v1.0.0-dev1.
1 file changed, 1 insertion(+)
> git tag v1.0.0-dev1 -am 'Tag v1.0.0-dev1'Now let's lowkey test that the tag actually gets picked up, by deploying to a fresh database. Like so (we're assuming you've got an Oracle SID named flipr_dev pointing at a different database):
> sqitch deploy db:oracle://scott:tiger@/flipr_dev
Adding registry tables to db:oracle://scott:@/flipr_dev
Deploying changes to db:oracle://scott:@/flipr_dev
+ appschema ................. ok
+ users ..................... ok
+ insert_user ............... No errors.
ok
+ change_pass @v1.0.0-dev1 .. No errors.
okPeriodt. 💅 All four changes deployed, and change_pass got tagged up with @v1.0.0-dev1. Let's peep the status:
> sqitch status db:oracle://scott:tiger@/flipr_dev
# On database db:oracle://scott:tiger@/flipr_dev
# Project: flipr
# Change: e1c9df6a95da835769eb560790588c16174f78df
# Name: change_pass
# Tag: @v1.0.0-dev1
# Deployed: 2013-12-31 16:40:02 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)See how the tag shows up right there in the status message? Iconic. Now let's bundle everything up for release:
> sqitch bundle
Bundling into bundle/
Writing config
Writing plan
Writing scripts
+ appschema
+ users
+ insert_user
+ change_pass @v1.0.0-dev1Now we can package that bundle directory and hand it out. Once it lands somewhere, users can just use Sqitch to deploy to the database. Let's try it on yet another database (again, assuming you've got a SID named flipr_prod):
> cd bundle
> sqitch deploy db:oracle://scott:tiger@/flipr_prod
Adding registry tables to db:oracle://scott:@/flipr_prod
Deploying changes to flipr_prod
+ appschema ................. ok
+ users ..................... ok
+ insert_user ............... ok
+ change_pass @v1.0.0-dev1 .. okLiterally looks the same as before, right? Package it up and ship it, babe!
> cd ..
> mv bundle flipr-v1.0.0-dev1
> tar -czf flipr-v1.0.0-dev1.tgz flipr-v1.0.0-dev1🌸 Flip Out 🌸
So now that the whole user-management thing is basically handled, it's time to work on the actual heart of our product: the "flip." And because other people are working on other stuff in the repo, we're gonna work on a branch so nobody steps on anyone's toes. Branch it up:
> git checkout -b flips
Switched to a new branch 'flips'Now we can add a new change that creates a table for our flips. 🦋
> sqitch add flips -r appschema -r users -n 'Adds table for storing flips.'
Created deploy/flips.sql
Created revert/flips.sql
Created verify/flips.sql
Added "flips [appschema users]" to sqitch.planYou already know the drill by now. Edit deploy/flips.sql:
-- Deploy flipr:flips to oracle
-- requires: appschema
-- requires: users
CREATE TABLE flipr.flips (
id INTEGER PRIMARY KEY,
nickname VARCHAR2(512 CHAR) NOT NULL REFERENCES flipr.users(nickname),
body VARCHAR2(180 CHAR) NOT NULL,
timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);
CREATE SEQUENCE flipr.flip_id_seq START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE OR REPLACE TRIGGER flipr.flip_pk BEFORE INSERT ON flipr.flips
FOR EACH ROW WHEN (NEW.id IS NULL)
DECLARE
v_id flipr.flips.id%TYPE;
BEGIN
SELECT flipr.flip_id_seq.nextval INTO v_id FROM DUAL;
:new.id := v_id;
END;
/Then edit verify/flips.sql:
-- Verify flipr:flips on oracle
DESCRIBE flipr.flips;And edit revert/flips.sql:
-- Revert flipr:flips from oracle
DROP TRIGGER flipr.flip_pk;
DROP SEQUENCE flipr.flip_id_seq;
DROP TABLE flipr.flips;And give it a whirl! 🌀
> sqitch deploy
Deploying changes to flipr_test
+ flips .. okLook good?
> sqitch status --show-tags
# On database flipr_test
# Project: flipr
# Change: 8e1573bb5ce5dfc239d5370c33d6e10820234aad
# Name: flips
# Deployed: 2013-12-31 16:51:54 -0800
# By: Marge N. O’Vera <marge@example.com>
#
# Tag:
# @v1.0.0-dev1 - 2013-12-31 16:44:00 -0800 - Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)Notice we used --show-tags there to show off all the deployed tags. 👑 Now make it so:
> git add .
> git commit -am 'Add flips table.'
[flips bbea131] Add flips table.
4 files changed, 32 insertions(+)
create mode 100644 deploy/flips.sql
create mode 100644 revert/flips.sql
create mode 100644 verify/flips.sql🔁 Wash, Rinse, Repeat 🔁
Alright, now comes the part where we add functions to manage flips. You've totally got this nailed down by now. Go ahead and add insert_flip and delete_flip changes, and commit them. The insert_flip deploy script might look a lil something like this:
-- Deploy flipr:insert_flip to oracle
-- requires: flips
-- requires: appschema
CREATE OR REPLACE PROCEDURE flipr.insert_flip(
nickname VARCHAR2,
body VARCHAR2
) AS
BEGIN
INSERT INTO flipr.flips (nickname, body)
VALUES (nickname, body);
END;
/
SHOW ERRORS;
-- Drop and die on error.
DECLARE
l_err_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO l_err_count
FROM all_errors
WHERE owner = 'FLIPR'
AND name = 'INSERT_FLIP';
IF l_err_count > 0 THEN
EXECUTE IMMEDIATE 'DROP PROCEDURE flipr.insert_flip';
raise_application_error(-20001, 'Errors in FLIPR.INSERT_FLIP');
END IF;
END;
/And the delete_flip deploy script might look a lil something like this:
-- Deploy flipr:delete_flip to oracle
-- requires: flips
-- requires: appschema
CREATE OR REPLACE PROCEDURE flipr.delete_flip(
flip_id INTEGER
) IS
flipr_flip_delete_failed EXCEPTION;
BEGIN
DELETE FROM flipr.flips WHERE id = flip_id;
IF SQL%ROWCOUNT = 0 THEN RAISE flipr_flip_delete_failed; END IF;
END;
/
SHOW ERRORS;
-- Drop and die on error.
DECLARE
l_err_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO l_err_count
FROM all_errors
WHERE owner = 'FLIPR'
AND name = 'DELETE_FLIP';
IF l_err_count > 0 THEN
EXECUTE IMMEDIATE 'DROP PROCEDURE flipr.delete_flip';
raise_application_error(-20001, 'Errors in FLIPR.DELETE_FLIP');
END IF;
END;
/The verify scripts are:
-- Verify flipr:insert_flip on oracle
DESCRIBE flipr.insert_flip;And:
-- Verify flipr:delete_flip on oracle
DESCRIBE flipr.delete_flip;The revert scripts are:
-- Revert flipr:insert_flip from oracle
DROP PROCEDURE flipr.insert_flip;And:
-- Revert flipr:delete_flip from oracle
DROP PROCEDURE flipr.delete_flip;Check the example git repository https://github.com/sqitchers/sqitch-oracle-intro for the complete details. Test deploy and revert, then commit it to the repository. The status should end up looking something like this:
> sqitch status --show-tags
# On database flipr_test
# Project: flipr
# Change: a47be5a474eaad1a28546666eadeb0eba3ac12dc
# Name: delete_flip
# Deployed: 2013-12-31 16:54:31 -0800
# By: Marge N. O’Vera <marge@example.com>
#
# Tag:
# @v1.0.0-dev1 - 2013-12-31 16:44:00 -0800 - Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)Good, feature's done. Time to merge back into main. 💖
🚨 Emergency 🚨
Let's do it:
> git checkout main
Switched to branch 'main'
> git pull
Updating eae5f71..a16f97c
Fast-forward
deploy/delete_list.sql | 35 +++++++++++++++++++++++++++++++++++
deploy/insert_list.sql | 33 +++++++++++++++++++++++++++++++++
deploy/lists.sql | 10 ++++++++++
revert/delete_list.sql | 3 +++
revert/insert_list.sql | 3 +++
revert/lists.sql | 3 +++
sqitch.plan | 4 ++++
verify/delete_list.sql | 3 +++
verify/insert_list.sql | 3 +++
verify/lists.sql | 5 +++++
10 files changed, 102 insertions(+)
create mode 100644 deploy/delete_list.sql
create mode 100644 deploy/insert_list.sql
create mode 100644 deploy/lists.sql
create mode 100644 revert/delete_list.sql
create mode 100644 revert/insert_list.sql
create mode 100644 revert/lists.sql
create mode 100644 verify/delete_list.sql
create mode 100644 verify/insert_list.sql
create mode 100644 verify/lists.sqlHrm, interesting. 😭 Looks like somebody made changes to main while we were off doing our thing. They added list support. Well… let's see what happens when we merge our changes:
> git merge --no-ff flips
Auto-merging sqitch.plan
CONFLICT (content): Merge conflict in sqitch.plan
Automatic merge failed; fix conflicts and then commit the result.Oh. A conflict in sqitch.plan. Not even that surprising tbh, since both the merged lists branch and our flips branch added changes to the plan. Let's try a different approach.
The truth is, we got lazy. 🙈 Those changes when we pulled main from the origin should have raised a red flag. Looking away from what's changed in main before merging in a branch is considered a bad practice. What one should do is either:
- Rebase the
flipsbranch frommainbefore merging. This "rewinds" the branch changes, pulls frommain, and then replays the changes back on top of the pulled changes. - Create a patch and apply that to
main. This is the sort of thing you might have to do if you're sending changes to another user, especially if the VCS is not Git.
So let's restore things to how they were at main:
> git reset --hard HEAD
HEAD is now at a16f97c Merge branch 'lists'That yeets our botched merge into the void. Now let's go back to our branch and rebase it on main:
So Git flagged a conflict in sqitch.plan — boom, right in the middle of our rebase:
> git checkout flips
Switched to branch 'flips'
> git rebase main
First, rewinding head to replay your work on top of it...
Applying: Add flips table.
Using index info to reconstruct a base tree...
M sqitch.plan
Falling back to patching base and 3-way merge...
Auto-merging sqitch.plan
CONFLICT (content): Merge conflict in sqitch.plan
Failed to merge in the changes.
Patch failed at 0001 Add flips table.
The copy of the patch that failed is found in:
.git/rebase-apply/patch
When you have resolved this problem, run "git rebase --continue".
If you prefer to skip this patch, run "git rebase --skip" instead.
To check out the original branch and stop rebasing, run "git rebase --abort".Ugh, that is so annoying, bestie. It's giving "no matter what we do, we're gonna have to hand-resolve conflicts in that file." Except… in Git. 😌
Lucky for us, we can tell Git to resolve conflicts in sqitch.plan differently. Since we only ever append lines to that file, we can use the union merge driver, which, per its docs <https://git-scm.com/docs/gitattributes#_built-in_merge_drivers>, says:
Run 3-way file level merge for text files, but take lines from both versions, instead of leaving conflict markers. This tends to leave the added lines in the resulting file in random order and the user should verify the result. Do not use this if you do not understand the implications.
Basically: it just appends lines from all the merging files. Which is literally exactly the behavior we want. ✨
So let's try it. First, back out the botched rebase:
> git rebase --abortNow add the union merge driver to .gitattributes for sqitch.plan and rebase again:
> echo sqitch.plan merge=union > .gitattributes
> git rebase main
First, rewinding head to replay your work on top of it...
Applying: Add flips table.
Using index info to reconstruct a base tree...
M sqitch.plan
Falling back to patching base and 3-way merge...
Auto-merging sqitch.plan
Applying: Add functions to insert and delete flips.
Using index info to reconstruct a base tree...
M sqitch.plan
Falling back to patching base and 3-way merge...
Auto-merging sqitch.planOh okay, that's so much better. 👑 Let's peek at the plan:
> cat sqitch.plan
%syntax-version=1.0.0
%project=flipr
%uri=https://github.com/sqitchers/sqitch-oracle-intro/
appschema 2013-12-31T22:34:42Z Marge N. O’Vera <marge@example.com> # App user and schema for all flipr objects.
users [appschema] 2014-01-01T00:31:20Z Marge N. O’Vera <marge@example.com> # Creates table to track our users.
insert_user [users appschema] 2014-01-01T00:35:21Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a user.
change_pass [users appschema] 2014-01-01T00:35:28Z Marge N. O’Vera <marge@example.com> # Creates a function to change a user password.
@v1.0.0-dev1 2014-01-01T00:39:35Z Marge N. O’Vera <marge@example.com> # Tag v1.0.0-dev1.
lists [appschema users] 2014-01-01T00:43:46Z Marge N. O’Vera <marge@example.com> # Adds table for storing lists.
insert_list [lists appschema] 2014-01-01T00:45:24Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a list.
delete_list [lists appschema] 2014-01-01T00:45:43Z Marge N. O’Vera <marge@example.com> # Creates a function to delete a list.
flips [appschema users] 2014-01-01T00:51:15Z Marge N. O’Vera <marge@example.com> # Adds table for storing flips.
insert_flip [flips appschema] 2014-01-01T00:53:00Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a flip.
delete_flip [flips appschema] 2014-01-01T00:53:16Z Marge N. O’Vera <marge@example.com> # Creates a function to delete a flip.See how it appended the changes from the merged lists branch, and then merged in the changes from our flips branch? 🫶 Test it to make sure it actually works:
> sqitch rebase -y
Reverting all changes from flipr_test
- delete_flip ............... ok
- insert_flip ............... ok
- flips ..................... ok
- change_pass @v1.0.0-dev1 .. ok
- insert_user ............... ok
- users ..................... ok
- appschema ................. ok
Deploying changes to flipr_test
+ appschema ................. ok
+ users ..................... ok
+ insert_user ............... No errors.
ok
+ change_pass @v1.0.0-dev1 .. No errors.
ok
+ lists ..................... ok
+ insert_list ............... No errors.
ok
+ delete_list ............... No errors.
ok
+ flips ..................... ok
+ insert_flip ............... No errors.
ok
+ delete_flip ............... No errors.
okNot me getting emotional over rebase combining a revert and a deploy into one single command. Handy, right?? 😭 It correctly reverted our changes, then deployed them all again in the proper order. So let's commit .gitattributes — seems worth keeping:
> git add .
> git commit -m 'Add `.gitattributes` with union merge for `sqitch.plan`.'
[flips 383691f] Add `.gitattributes` with union merge for `sqitch.plan`.
1 file changed, 1 insertion(+)
create mode 100644 .gitattributes💅 Merges Mastered 💅
And now, finally, we can merge into main:
> git checkout main
Switched to branch 'main'
> git merge --no-ff flips -m "Merge branch 'flips'"
Merge made by the 'recursive' strategy.
.gitattributes | 1 +
deploy/delete_flip.sql | 32 ++++++++++++++++++++++++++++++++
deploy/flips.sql | 22 ++++++++++++++++++++++
deploy/insert_flip.sql | 32 ++++++++++++++++++++++++++++++++
revert/delete_flip.sql | 3 +++
revert/flips.sql | 5 +++++
revert/insert_flip.sql | 3 +++
sqitch.plan | 3 +++
verify/delete_flip.sql | 3 +++
verify/flips.sql | 3 +++
verify/insert_flip.sql | 3 +++
11 files changed, 110 insertions(+)
create mode 100644 .gitattributes
create mode 100644 deploy/delete_flip.sql
create mode 100644 deploy/flips.sql
create mode 100644 deploy/insert_flip.sql
create mode 100644 revert/delete_flip.sql
create mode 100644 revert/flips.sql
create mode 100644 revert/insert_flip.sql
create mode 100644 verify/delete_flip.sql
create mode 100644 verify/flips.sql
create mode 100644 verify/insert_flip.sqlAnd double-check our work:
> cat sqitch.plan
%syntax-version=1.0.0
%project=flipr
%uri=https://github.com/sqitchers/sqitch-oracle-intro/
appschema 2013-12-31T22:34:42Z Marge N. O’Vera <marge@example.com> # App user and schema for all flipr objects.
users [appschema] 2014-01-01T00:31:20Z Marge N. O’Vera <marge@example.com> # Creates table to track our users.
insert_user [users appschema] 2014-01-01T00:35:21Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a user.
change_pass [users appschema] 2014-01-01T00:35:28Z Marge N. O’Vera <marge@example.com> # Creates a function to change a user password.
@v1.0.0-dev1 2014-01-01T00:39:35Z Marge N. O’Vera <marge@example.com> # Tag v1.0.0-dev1.
lists [appschema users] 2014-01-01T00:43:46Z Marge N. O’Vera <marge@example.com> # Adds table for storing lists.
insert_list [lists appschema] 2014-01-01T00:45:24Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a list.
delete_list [lists appschema] 2014-01-01T00:45:43Z Marge N. O’Vera <marge@example.com> # Creates a function to delete a list.
flips [appschema users] 2014-01-01T00:51:15Z Marge N. O’Vera <marge@example.com> # Adds table for storing flips.
insert_flip [flips appschema] 2014-01-01T00:53:00Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a flip.
delete_flip [flips appschema] 2014-01-01T00:53:16Z Marge N. O’Vera <marge@example.com> # Creates a function to delete a flip.Soooo much better — a nice clean main. 🌈 And because it's now identical to the flips branch, we can just carry on. Go ahead and tag it, bundle it, release it:
> sqitch tag v1.0.0-dev2 -n 'Tag v1.0.0-dev2.'
Tagged "delete_flip" with @v1.0.0-dev2
> git commit -am 'Tag the database with v1.0.0-dev2.'
[main 5427456] Tag the database with v1.0.0-dev2.
1 file changed, 1 insertion(+)
> git tag v1.0.0-dev2 -am 'Tag v1.0.0-dev2'
> sqitch bundle --dest-dir flipr-1.0.0-dev2
Bundling into flipr-1.0.0-dev2
Writing config
Writing plan
Writing scripts
+ appschema
+ users
+ insert_user
+ change_pass @v1.0.0-dev1
+ lists
+ insert_list
+ delete_list
+ flips
+ insert_flip
+ delete_flip @v1.0.0-dev2Little note: that --dest-dir option to sqitch bundle is just a nicer way to create the top-level directory name, so we don't have to go rename it from bundle. 🎀
🚨 In Place Changes 🚨
Uh-oh. Someone just noticed that MD5 hashing is not particularly secure. Why? Have a look:
> echo "
DELETE FROM flipr.users;
EXECUTE flipr.insert_user('foo', 's3cr3t');
EXECUTE flipr.insert_user('bar', 's3cr3t');
SELECT nickname, password FROM flipr.users;
" | sqlplus -S scott/tiger@flipr_test
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
NICKNAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
foo
a4d80eac9ab26a4a2da04125bc2c096a
bar
a4d80eac9ab26a4a2da04125bc2c096aOkay so if user foo ever got access to the database, she could immediately clock that user bar has the same password — and then just exploit that account. Not a great idea, bestie. 💀 So we need to modify the insert_user() and change_pass() functions to fix it. How?
We'll create a function that encrypts passwords using a cryptographic salt <https://en.wikipedia.org/wiki/Salt_(cryptography)>. This lets the password hashes be stored with random hashing. So we'll need to add the function. The deploy script should be:
-- Deploy flipr:crypt to oracle
-- requires: appschema
CREATE OR REPLACE FUNCTION flipr.crypt(
password VARCHAR2,
salt VARCHAR2
) RETURN VARCHAR2 IS
salted CHAR(10) := SUBSTR(salt, 0, 10);
BEGIN
RETURN salted || LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW(
sys.dbms_obfuscation_toolkit.md5(input_string => password || salted)
) ) );
END;
/
SHOW ERRORS;-- Drop and die on error.
DECLARE
l_err_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO l_err_count
FROM all_errors
WHERE owner = 'FLIPR'
AND name = 'CRYPT';
IF l_err_count > 0 THEN
EXECUTE IMMEDIATE 'DROP PROCEDURE flipr.crypt';
raise_application_error(-20001, 'Errors in FLIPR.CRYPT');
END IF;
END;
/And bestie, the revert script is literally this short, no cap:
-- Revert flipr:crypt. from oracle
DROP FUNCTION flipr.crypt;And the verify script? Same energy as always, we just hit it with DESCRIBE:
-- Verify flipr:crypt on oracle
DESCRIBE flipr.crypt;Once that change is in place and committed, we're so ready to put the shiny new encryption to work. But 💭 how do we deploy the changes to insert_user() and change_pass()?
Normally, modifying functions in database changes is a PITA — that's "Pain In The A**", if the acronym wasn't already giving it away 😭. You have to do all of this:
- Copy
deploy/insert_user.sqltodeploy/insert_user_crypt.sql. - Edit
deploy/insert_user_crypt.sqlto switch from"sys.dbms_obfuscation_toolkit.md5()"to"flipr.crypt()"and to add a dependency on the"crypt"change. - Copy
deploy/insert_user.sqltorevert/insert_user_crypt.sql. Yes, copy the original change script to the new revert change. - Copy
verify/insert_user.sqltoverify/insert_user_crypt.sql. - Edit
verify/insert_user_crypt.sqlto test that the function now properly uses"flipr.crypt()". - Test the changes to make sure you can deploy and revert the
"insert_user_crypt"change. - Now do the same for the
"change_pass"scripts.
But ✨ you can have Sqitch do it for you ✨. The only requirement is that a tag appear between the two instances of a change we want to modify. And honestly? In general you're gonna make a change like this after a release, which you've tagged anyway, right? 🙋♀️ Well we have — with "@v1.0.0-dev2" added in the previous section. With that, we can let Sqitch do most of the hard work for us, thanks to the "rework" command, which is similar to "add", including support for the "--requires" option:
> sqitch rework insert_user --requires crypt -n 'Change insert_user to use crypt.'
Added "insert_user [insert_user@v1.0.0-dev2 crypt]" to sqitch.plan.
Modify these files as appropriate:
* deploy/insert_user.sql
* revert/insert_user.sql
* verify/insert_user.sqlOh so we can edit those files in place?? Nice! 🧚 How does Sqitch even do it?
Well, in point of fact, it has copied the files to stand in for the previous instance of the "insert_user" change — which we can see via "git status":
> git status
# On branch main
# Your branch is ahead of 'origin/main' by 2 commits.
# (use "git push" to publish your local commits)
#
# Changes not staged for commit:
# (use "git add <file>..." to update what will be committed)
# (use "git checkout -- <file>..." to discard changes in working directory)
#
# modified: revert/insert_user.sql
# modified: sqitch.plan
#
# Untracked files:
# (use "git add <file>..." to include in what will be committed)
#
# deploy/insert_user@v1.0.0-dev2.sql
# revert/insert_user@v1.0.0-dev2.sql
# verify/insert_user@v1.0.0-dev2.sql
no changes added to commit (use "git add" and/or "git commit -a")The "untracked files" part of the output is the first thing to notice 👀. They are all named "insert_user@v1.0.0-dev2.sql". What that means is: "the "insert_user" change as it was implemented as of the "@v1.0.0-dev2" tag." These are copies of the original scripts, and thereafter Sqitch will find them when it needs to run scripts for the first instance of the "insert_user" change. So as such, it's important not to change them again. But hey — if you're reworking the change, you shouldn't need to. 💅
The other thing to notice is that revert/insert_user.sql has changed. Sqitch replaced it with the original deploy script. As of now, deploy/insert_user.sql and revert/insert_user.sql are identical. This is on the assumption that the deploy script will be changed (we're reworking it, remember?), and that the revert script should actually change things back to how they were before. Of course, the original deploy script may not be idempotent — that's the fancy word for "able to be applied multiple times without changing the result beyond the initial application". If it's not idempotent, you will likely need to modify it so that it properly restores things to how they were after the original deploy script was deployed. Or, more simply, it should revert changes back to how they were as-of the deployment of deploy/insert_user@v1.0.0-dev2.sql.
Fortunately 😌 our function deploy scripts are already idempotent, thanks to the use of the "OR REPLACE" expression. No matter how many times a deployment script is run, the end result will be the same instance of the function — no duplicates, no errors. Periodt.
As a result, there is no need to explicitly add changes. So go ahead 🫶. Modify the script to switch to crypt(). Make this change to deploy/insert_user.sql:
@@ -1,6 +1,7 @@
-- Deploy flipr:insert_user to oracle
-- requires: users
-- requires: appschema
+-- requires: crypt
CREATE OR REPLACE PROCEDURE flipr.insert_user(
nickname VARCHAR2,
@@ -9,9 +10,7 @@ CREATE OR REPLACE PROCEDURE flipr.insert_user(
BEGIN
INSERT INTO flipr.users VALUES(
nickname,
- LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW(
- sys.dbms_obfuscation_toolkit.md5(input_string => password)
- ) ) ),
+ flipr.crypt(password, DBMS_RANDOM.STRING('p', 10)),
DEFAULT
);
END;Go ahead and rework the "change_pass" change, too:
> sqitch rework change_pass --requires crypt -n 'Change change_pass to use crypt.'
Added "change_pass [change_pass@v1.0.0-dev2 crypt]" to sqitch.plan.
Modify these files as appropriate:
* deploy/change_pass.sql
* revert/change_pass.sql
* verify/change_pass.sqlAnd make this change to deploy/change_pass.sql:
@@ -1,6 +1,7 @@
-- Deploy flipr:change_pass to oracle
-- requires: users
-- requires: appschema
+-- requires: crypt
CREATE OR REPLACE PROCEDURE flipr.change_pass(
nick VARCHAR2,
@@ -10,13 +11,9 @@ CREATE OR REPLACE PROCEDURE flipr.change_pass(
flipr_auth_failed EXCEPTION;
BEGIN
UPDATE flipr.users
- SET password = LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW(
- sys.dbms_obfuscation_toolkit.md5(input_string => newpass)
- ) ) )
+ SET password = flipr.crypt(newpass, DBMS_RANDOM.STRING('p', 10))
WHERE nickname = nick
- AND password = LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW(
- sys.dbms_obfuscation_toolkit.md5(input_string => oldpass)
- ) ) );
+ AND password = flipr.crypt(oldpass, password);
IF SQL%ROWCOUNT = 0 THEN RAISE flipr_auth_failed; END IF;
END;
/And then 🥁 try a deployment:
> sqitch deploy
Deploying changes to flipr_test
+ insert_user .. No errors.
ok
+ change_pass .. No errors.
okSo… are the changes deployed? 🕵️♀️
> echo "
DELETE FROM flipr.users;
EXECUTE flipr.insert_user('foo', 's3cr3t');
EXECUTE flipr.insert_user('bar', 's3cr3t');
SELECT nickname, password FROM flipr.users;
" | sqlplus -S scott/tiger@flipr_test
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
NICKNAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
foo
cP?.eR!V[pf3d91ce9b7dcfe9260c6f4bb94ed0b22
bar
Z+l"_W_JiSefb62b789c0ff114cddcccc69c422e78Awesome — the stored passwords are different now 🔥. But can we revert, even though we haven't written any reversion scripts? 🤔
> sqitch revert --to @HEAD^^ -y
Reverting changes to crypt from flipr_test
- change_pass .. No errors.
ok
- insert_user .. No errors.
okDid that work? Are the MD5 passwords back? 👑
> echo "
DELETE FROM flipr.users;
EXECUTE flipr.insert_user('foo', 's3cr3t');
EXECUTE flipr.insert_user('bar', 's3cr3t');
SELECT nickname, password FROM flipr.users;
" | sqlplus -S scott/tiger@flipr_test
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
NICKNAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
foo
a4d80eac9ab26a4a2da04125bc2c096a
bar
a4d80eac9ab26a4a2da04125bc2c096aYes, it works!! 🎀 Sqitch properly finds the original instances of these changes in the new script files that include tags. Iconic behaviour, fr fr.
But wait — what about the verify script? 🤨 How can we verify that the functions have been modified to use crypt()? I think the simplest thing to do is to examine the body of the function by querying the "all_source" view (that's the Oracle data dictionary view, the one in the docs for the server). So the "insert_user" verify script looks like this:
-- Verify flipr:insert_user on oracle
DESCRIBE flipr.insert_user;
SELECT 1/COUNT(*)
FROM all_source
WHERE type = 'PROCEDURE'
AND name = 'INSERT_USER'
AND text LIKE '%flipr.crypt(password, DBMS_RANDOM.STRING(''p'', 10))%';And the "change_pass" verify script looks like this:
-- Verify flipr:change_pass on oracle
DESCRIBE flipr.change_pass;
SELECT 1/COUNT(*)
FROM all_source
WHERE type = 'PROCEDURE'
AND name = 'CHANGE_PASS'
AND text LIKE '%password = flipr.crypt(newpass, DBMS_RANDOM.STRING(''p'', 10))%';Make sure these pass by re-deploying:
> sqitch deploy
Deploying changes to flipr_test
+ insert_user .. No errors.
ok
+ change_pass .. No errors.
okExcellent 💖. Let's go ahead and commit these changes:
> git add .
> git commit -m 'Use crypt to encrypt passwords.'
[main be46175] Use crypt to encrypt passwords.
13 files changed, 181 insertions(+), 15 deletions(-)
create mode 100644 deploy/change_pass@v1.0.0-dev2.sql
create mode 100644 deploy/insert_user@v1.0.0-dev2.sql
rewrite revert/change_pass.sql (98%)
rename revert/{change_pass.sql => change_pass@v1.0.0-dev2.sql} (100%)
rewrite revert/insert_user.sql (98%)
rename revert/{insert_user.sql => insert_user@v1.0.0-dev2.sql} (100%)
create mode 100644 verify/change_pass@v1.0.0-dev2.sql
create mode 100644 verify/insert_user@v1.0.0-dev2.sql
> sqitch status
# On database flipr_test
# Project: flipr
# Change: 8367dc3bff7a563ec27f145421a1ffdf724cb6de
# Name: change_pass
# Deployed: 2013-12-31 17:18:28 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)okay so look at that commit — 13 files changed, and the revert and verify scripts literally got renamed to carry the new tag, not just rewritten. That's the whole idempotent-rework ritual, start to finish. ✨
And then sqitch status just shrugs and says Nothing to deploy (up-to-date), which is the most satisfying sentence in the entire tool ngl. You deployed the change, you flipped it out, you rewrote it, you re-deployed it, and the plan is still clean. Chef's kiss. 👑
🦋 More to Come
Sqitch is still a work in progress, bestie. It's not finished. Don't @ me.
So here's the tea: better integration with version control systems is planned, specifically to make managing all these idempotent reworkings even easier. That's the roadmap, fr fr.
Stay tuned. 🌸
👑 Author
David E. Wheeler <david@justatheory.com> — the icon who built all of this.
💜 License
Copyright (c) 2012-2026 David E. Wheeler, 2012-2021 iovation Inc.
okay so not me just dropping the whole license text right here, but legally you gotta keep it intact, so here it is, verbatim, every single word of it. No paraphrasing a license, bestie, that's how you get in trouble. 🙈
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
so yeah. Go make some changes, verify them, revert them, redeploy them, and live your best idempotent life. 💅
and that's the whole page, bestie, we made it to the end 🎀 this one came from sqitch...-oracle(3pm), shipped with perl v5.40.1, dated 2026-07-26. Go forth and deploy responsibly. 🦋💖
📦 where this page comes from
- ubuntu stonking: sqitch 1.6.1-2 · MIT
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms