diff snippets

Display the change log (revision history) for a Mercurial repository

Tagged mercurial, diff  Languages bash

Use the following command to print a list of changes (revision history) for a Mercurial repository:

$ hg log

The output looks like this:

changeset:   35:5a17500addb5
tag:         tip
user:        christian@localhost
date:        Fri Jun 29 23:33:59 2007 +0300
summary:     Changed mongrel_cluster configuration

changeset:   34:a73ee17923fe
user:        christian@localhost
date:        Fri Jun 29 23:28:36 2007 +0300
summary:     Fixed bugs.

In the example there are two revisions 35 and 34. Use the following command to display the details for revision 34:

$ hg log -p -r34

The output is a unified diff for the changeset and looks like this:

changeset:   34:a73ee17923fe
user:        christian@localhost
date:        Fri Jun 29 23:28:36 2007 +0300
summary:     Fixed bugs.

diff -r 4c44552841af -r 4e57a76f51d9 lib/daemons/deleted_unused_tags.rb
--- a/lib/daemons/deleted_unused_tags.rb        Sat Jun 23 23:58:38 2007 +0300
+++ b/lib/daemons/deleted_unused_tags.rb        Sun Jun 24 00:03:43 2007 +0300
@@ -11,10 +11,7 @@ end
 end

ClearCase commands for listing changes (between builds, by me, etc)

Tagged clearcase, cleartool, diff, changes  Languages bash
# List changes done by me
cleartool lshistory -since 10-January-2007 -recurse -me

# List view only files
cleartool ls -view_only -recurse

# Find changes between two releases
cleartool find PATH -print -version "lbtype(NEW_BUILD_LABEL) && !version(OLD_BUILD_LABEL)"

Diffs with Postgres' lead and window functions

Tagged lead, diff, window, postgres  Languages sql

Our requirement

Output the previous quarter's value for each company:

ID | NAME              | QUARTER     |  VALUE    | PREVIOUS_VALUE
1  | CORP              | 2015-03-31  |  44317.00 | 38755.00
1  | CORP              | 2014-12-31  |  38755.00 | 33617.00
1  | CORP              | 2014-09-30  |  33617.00 | 32406.00
1  | CORP              | 2014-06-30  |  32406.00 | 29909.00
1  | CORP              | 2014-03-31  |  29909.00 | 0

The solution

To calculate the change we use the window and lead functions:

WITH diffs AS (
  select
    *,
    coalesce(lead(value) over (partition by company_id order by quarter desc), 0) as previous_value
  from
    history
)
select * from diffs;

Note that the window is partitioned by company_id, and ordered by quarter.

Database diff

Tagged database, diff, postgres  Languages bash, ruby, yaml

This script will connect to two databases, named old and new, and print:

  • the names of the tables that cannot be found in the new database
  • the difference in the amount of rows per table, if there is a difference in the number of rows

db_diff.rb:

#
# Postgres database diff script. Prints the names of missing tables and the
# difference in the amount of rows per table.
#
require 'sequel'
require 'pg'
require 'yaml'
require 'pry'

list_tables_sql = "SELECT tablename from pg_tables where schemaname = 'public';"
OLD_DB = Sequel.connect(YAML.load(File.read('old_database.yml')))
NEW_DB = Sequel.connect(YAML.load(File.read('new_database.yml')))
OLD_TABLES = OLD_DB[list_tables_sql].map{ |x| x.fetch(:tablename) }
NEW_TABLES = NEW_DB[list_tables_sql].map{ |x| x.fetch(:tablename) }

# Compare tables
def diff_tables
  OLD_TABLES - NEW_TABLES
end

# Compare row count
def diff_row_count
  OLD_TABLES.sort.reduce({}) do |hash, table|
    sql = "SELECT count(*) FROM %{table}"
    # Sequel's count method does not work.
    diff = OLD_DB[sql % {table: table}].first[:count].to_i - NEW_DB[sql % {table: table}].first[:count].to_i
    hash[table] = diff if diff != 0
    hash
  end
end

puts JSON.pretty_generate(tables: diff_tables, rows: diff_row_count)

Gemfile:

source 'https://rubygems.org'

gem 'sequel'
gem 'pg'
gem 'pry'

new_database.yml and old_database.yml:

adapter: postgres
host: localhost
port: 5432
encoding: unicode
database: x_production
username: x
password: x

To run the script:

gem install bundler
bundle
bundle exec ruby db_diff.rb

Other migration and diff tools

migra is a schema diff tool for PostgreSQL. Use it to compare database schemas or autogenerate migration scripts.

Given two database connections, it output a file that represent the difference between schemas. It means that if you run the output file into the from database, it’ll have the same schema as the to database.

Compares the PostgreSQL schema between two databases and generates SQL statements that can be run manually against the second database to make their schemas match.