relpipe-data/examples/mercurial-hg-xmltable-sql.sh
author František Kučera <franta-hg@frantovo.cz>
Sat, 26 Oct 2019 20:57:42 +0200
branchv_0
changeset 276 cfaf1fa94154
parent 264 d39cfc926f95
child 317 fce3d6290c40
permissions -rwxr-xr-x
rename --keep-file to --file-keep
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
264
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
     1
#!/bin/bash
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
     2
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
     3
relpipe-in-hglog() {
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
     4
	# on repositories with long history 
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
     5
	# it is good to process just a subset of the log using hg log parameters
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
     6
	hg log --template xml \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
     7
		| relpipe-in-xmltable \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
     8
			--relation hg_log \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
     9
				--records "/log/logentry" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    10
				--attribute "revision" integer "@revision" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    11
				--attribute "date" string "date" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    12
				--attribute "hash" string "@node" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    13
				--attribute "hash_short" string "substring(@node,1,12)" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    14
				--attribute "branch" string "branch" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    15
				--attribute "author_name" string "author" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    16
				--attribute "message" string "msg"
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    17
				# --attribute "author_email" string "author/@email" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    18
}
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    19
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    20
relpipe-in-hgtags() {
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    21
	hg tags \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    22
		| sed -E 's/([^ \s]+)\s+([0-9]+):(.*)/\1\n\2\n\3/g' \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    23
		| tr \\n \\0 \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    24
		| relpipe-in-cli \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    25
			generate-from-stdin hg_tags 3 \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    26
				tag string \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    27
				revision integer \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    28
				hash_short string
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    29
}
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    30
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    31
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    32
# just view everything:
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    33
# (relpipe-in-hglog; relpipe-in-hgtags) | relpipe-out-tabular | less -RSi
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    34
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    35
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    36
# filter and JOIN data, do some statistics:
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    37
(relpipe-in-hglog; relpipe-in-hgtags) \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    38
	| relpipe-tr-sql \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    39
		--relation "tags" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    40
			"SELECT
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    41
				t.*,
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    42
				l.date
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    43
			FROM hg_tags AS t
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    44
			JOIN hg_log AS l USING (hash_short)" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    45
		--relation "filtered_log" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    46
			"SELECT 
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    47
				revision, 
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    48
				date, 
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    49
				hash_short,
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    50
				branch, 
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    51
				author_name
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    52
			FROM hg_log
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    53
			ORDER BY revision DESC
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    54
			LIMIT ?" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    55
			--parameter 10 \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    56
		--relation "author_statistics" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    57
			"SELECT
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    58
				author_name,
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    59
				branch,
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    60
				count(*) AS count,
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    61
				min(date) AS first_commit,
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    62
				max(date) AS last_commit
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    63
				-- TODO: user proper data type for date
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    64
			FROM hg_log
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    65
			GROUP BY author_name, branch
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    66
			ORDER BY count" \
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    67
	| relpipe-out-tabular
d39cfc926f95 XMLTable, SQL, v0.13
František Kučera <franta-hg@frantovo.cz>
parents:
diff changeset
    68