view release on metacpan or  search on metacpan

lib/sqitchtutorial-clickhouse.pod  view on Meta::CPAN

L<Docker image|https://hub.docker.com/r/sqitch/sqitch/> because it uses the
client inside the container, not on your host machine):

  > sqitch config --user engine.clickhouse.client /opt/bin/clickhouse

And let's also 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 F<~/.sqitch/sqitch.conf> and you'll see this:

  > cat ~/.sqitch/sqitch.conf
  [engine "clickhouse"]
  	client = /opt/bin/clickhouse
  [user]
  	name = Marge N. O’Vera
  	email = marge@example.com

Which means that Sqitch should be able to find C<clickhouse> for any project,
and that it will always properly identify us when planning and committing
changes.

Back to the repository. Have a look at the plan file, F<sqitch.plan>:

  > cat sqitch.plan
  %syntax-version=1.0.0
  %project=flipr
  %uri=https://github.com/sqitchers/sqitch-clickhouse-intro/


Note that it contains the name and URI of the app we're building. Sqitch uses
this data to manage cross-project dependencies. The C<%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.

Let's commit these changes and start creating the database changes.

  > git add .
  > git commit -am 'Initialize Sqitch configuration.'
  [main d32b8ae] Initialize Sqitch configuration.
   2 files changed, 12 insertions(+)
   create mode 100644 sqitch.conf
   create mode 100644 sqitch.plan

=head1 Our First Change

Let's create a table. Our app will need users, of course, so we'll create a
table for them. Run this command:

  > sqitch add users -n 'Creates table to track our users.'
  Created deploy/users.sql
  Created revert/users.sql
  Created verify/users.sql
  Added "users" to sqitch.plan

The L<C<add>|sqitch-add> command adds a database change to the plan and writes
deploy, revert, and verify scripts that represent the change. Now we edit
these files. The C<deploy> script's job is to create the table. By default,
the F<deploy/users.sql> file looks like this:

  -- Deploy flipr:users to clickhouse

  -- XXX Add DDLs here.

What we want to do is to replace the C<XXX> comment with the C<CREATE TABLE>
statement, like so:

  -- Deploy flipr:users to clickhouse

  CREATE TABLE users (
      nickname   VARCHAR(50)  PRIMARY KEY,
      password   VARCHAR(512) NOT NULL,
      fullname   VARCHAR(512) NOT NULL,
      mastodon   VARCHAR(512) NOT NULL,
      created_at DATETIME64(6, 'UTC') NOT NULL DEFAULT now64(6, 'UTC')
  ) ENGINE = MergeTree;

The C<revert> script's job is to precisely revert the change to the deploy
script, so we edit this to F<revert/users.sql> to look like this:

  -- Revert flipr:users from clickhouse

  DROP TABLE users;

Now we can try deploying this change. First, we need to create a database
to deploy to. Assuming that ClickHouse is running on localhost using the
C<default> user (as is provided by the
L<ClickHouse Docker Image|https://hub.docker.com/_/clickhouse>):

  > clickhouse client --query 'CREATE DATABASE flipr_test'

Now we tell Sqitch where to send the change via a
L<database URI|https://github.com/libwww-perl/uri-db/>, again assuming the
C<default> user & database and an ODBC driver named C<ClickHouse> (see
L</Connection Configuration> for details):

  > sqitch deploy 'db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse'
  Adding registry tables to db:clickhouse://default@localhost/sqitch?Driver=ClickHouse
  Deploying changes to db:clickhouse://default@localhost/flipr_test?Driver=ClickHouse
    + users .. ok

First Sqitch created the registry database and tables used to track database
changes. The registry database is separate from the database to which the
C<users> change was deployed; by default, its name is C<sqitch>, and will be
used to manage I<all> projects on a single ClickHouse server. Ideally, only
Sqitch data will be stored in this database, so it probably makes the most
sense to create a superuser named C<sqitch> or something similar and use it
to deploy changes.

If you'd like it to use a different database as the registry database, use
C<sqitch engine add clickhouse $name> to configure it (or via the
L<C<target> command|sqitch-target>; more L<below|/On Target>). This will 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 C<+> reinforces the idea
that the change is being I<added> to the database.

lib/sqitchtutorial-clickhouse.pod  view on Meta::CPAN

branch changes, pulls from C<main>, and then replays the changes back on top
of the pulled changes.

=item *

Create a patch and apply I<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.

=back

So let's restore things to how they were at main:

  > git merge --abort

That throws out our botched merge. Now let's go back to our branch and rebase
it on C<main>:

  > git checkout hashtags
  Switched to branch 'hashtags'
  > git rebase main
  Auto-merging sqitch.plan
  CONFLICT (content): Merge conflict in sqitch.plan
  error: could not apply 1359929... Add hashtags table.
  hint: Resolve all conflicts manually, mark them as resolved with
  hint: "git add/rm <conflicted_files>", then run "git rebase --continue".
  hint: You can instead skip this commit: run "git rebase --skip".
  hint: To abort and get back to the state before "git rebase", run "git rebase --abort".
  hint: Disable this message with "git config set advice.mergeConflict false"
  Could not apply 1359929... # Add hashtags table.

Oy, that's kind of a pain. It seems like no matter what we do, we'll need to
resolve conflicts in that file. Except in Git. Fortunately for us, we can tell
Git to resolve conflicts in F<sqitch.plan> differently. Because we only ever
append lines to the file, we can have it use the "union" merge driver, which,
according to
L<its docs|https://git-scm.com/docs/gitattributes#_built-in_merge_drivers>:

=over

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.

=back

This has the effect of appending lines from all the merging files, which is
exactly what we need. So let's give it a try. First, back out the botched
rebase:

  > git rebase --abort

Now add the union merge driver to F<.gitattributes> for F<sqitch.plan>
and rebase again:

  > echo sqitch.plan merge=union > .gitattributes
  > git rebase main
  Successfully rebased and updated refs/heads/hashtags.

Ah, that looks a bit better. Let's have a look at the plan:

  > cat sqitch.plan
  %syntax-version=1.0.0
  %project=flipr
  %uri=https://github.com/sqitchers/sqitch-clickhouse-intro/
  
  users 2025-09-19T18:29:21Z Marge N. O’Vera <marge@example.com> # Creates table to track our users.
  flips [users] 2025-09-19T18:35:31Z Marge N. O’Vera <marge@example.com> # Adds table for storing flips.
  userflips [users flips] 2025-09-19T18:38:26Z Marge N. O’Vera <marge@example.com> # Creates the userflips view.
  @v1.0.0-dev1 2025-09-19T18:39:48Z Marge N. O’Vera <marge@example.com> # Tag v1.0.0-dev1.
  
  lists [flips] 2025-09-19T18:41:17Z An Oth R. Developer <another@example.com> # Adds table for storing lists.
  hashtags [flips] 2025-09-19T18:47:02Z Marge N. O’Vera <marge@example.com> # Adds table for storing hashtags.

Note that it has appended the changes from the merged "lists" branch, and then
merged the changes from our "hashtags" branch. Test it to make sure it works
as expected:

  > sqitch rebase -y
  Reverting all changes from flipr_test
    - hashtags ................ ok
    - userflips @v1.0.0-dev1 .. ok
    - flips ................... ok
    - users ................... ok
  Deploying changes to flipr_test
    + users ................... ok
    + flips ................... ok
    + userflips @v1.0.0-dev1 .. ok
    + lists ................... ok
    + hashtags ................ ok

Note the use of L<C<rebase>|sqitch-rebase>, which combines a
L<C<revert>|sqitch-revert> and a L<C<deploy>|sqitch-deploy> into a single
command. Handy, right? It correctly reverted our changes, and then deployed
them all again in the proper order. So let's commit F<.gitattributes>; seems
worthwhile to keep that change:

  > git add .
  > git commit -m 'Add `.gitattributes` with union merge for `sqitch.plan`.'
  [hashtags 862ea7a] Add `.gitattributes` with union merge for `sqitch.plan`.
   1 file changed, 1 insertion(+)
   create mode 100644 .gitattributes

=head2 Merges Mastered

And now, finally, we can merge into C<main>:

  > git checkout main
  Switched to branch 'main'
  > git merge --no-ff hashtags -m "Merge branch 'hashtags'"
  Merge made by the 'ort' strategy.
   .gitattributes      | 1 +
   deploy/hashtags.sql | 9 +++++++++
   revert/hashtags.sql | 3 +++
   sqitch.plan         | 1 +
   verify/hashtags.sql | 3 +++
   5 files changed, 17 insertions(+)
   create mode 100644 .gitattributes
   create mode 100644 deploy/hashtags.sql
   create mode 100644 revert/hashtags.sql