diff --git a/sql/Makefile b/sql/Makefile index 19cfb43..f42c752 100644 --- a/sql/Makefile +++ b/sql/Makefile @@ -1,78 +1,71 @@ # Depends: postgresql-client, postgresql-autodoc DBNAME = softwareheritage-dev DOCDIR = autodoc -SQL_INIT = 10-swh-init.sql -SQL_ENUMS = 20-swh-enums.sql -SQL_SCHEMA = 30-swh-schema.sql -SQL_FUNC = 40-swh-func.sql -SQL_INDEX = 60-swh-indexes.sql -SQL_TRIGGER = 70-swh-triggers.sql -SQLS = $(SQL_INIT) $(SQL_ENUMS) $(SQL_SCHEMA) $(SQL_FUNC) $(SQL_INDEX) $(SQL_TRIGGER) -SQL_FILES = $(abspath $(addprefix $(CURDIR)/../swh/storage/sql/,$(SQLS))) +SQL_FILES = $(sort $(wildcard $(CURDIR)/../swh/storage/sql/*)) PSQL_BIN = psql PSQL_FLAGS = --echo-errors -X -v ON_ERROR_STOP=1 PSQL = $(PSQL_BIN) $(PSQL_FLAGS) PIFPAF=$(findstring postgresql://,$(PIFPAF_URLS)) all: createdb: createdb-stamp createdb-stamp: $(SQL_FILES) ifeq ($(PIFPAF),) -dropdb $(DBNAME) endif createdb $(DBNAME) ifeq ($(PIFPAF),) touch $@ else rm -f $@ endif filldb: filldb-stamp filldb-stamp: createdb-stamp cat $(SQL_FILES) | $(PSQL) $(DBNAME) ifeq ($(PIFPAF),) touch $@ else rm -f $@ endif dropdb: -dropdb $(DBNAME) dumpdb: swh.dump swh.dump: filldb-stamp pg_dump -Fc $(DBNAME) > $@ $(DOCDIR): test -d $(DOCDIR)/ || mkdir $(DOCDIR) doc: autodoc-stamp $(DOCDIR)/db-schema.pdf $(DOCDIR)/db-schema.svg autodoc-stamp: filldb-stamp $(DOCDIR) postgresql_autodoc -d $(DBNAME) -f $(DOCDIR)/db-schema cp -a $(DOCDIR)/db-schema.dot $(DOCDIR)/db-schema.dot.orig ifeq ($(PIFPAF),) touch $@ else rm -f $@ endif $(DOCDIR)/db-schema.dot: clusters.dot autodoc-stamp $(DOCDIR) bin/dot_add_content $(DOCDIR)/db-schema.dot.orig clusters.dot > $(DOCDIR)/db-schema.dot $(DOCDIR)/db-schema.pdf: $(DOCDIR)/db-schema.dot autodoc-stamp dot -T pdf $< > $@ $(DOCDIR)/db-schema.svg: $(DOCDIR)/db-schema.dot autodoc-stamp dot -T svg $< > $@ clean: rm -rf *-stamp $(DOCDIR)/ distclean: clean dropdb rm -f swh.dump .PHONY: all initdb createdb dropdb doc clean diff --git a/sql/bin/db-upgrade b/sql/bin/db-upgrade index 1dd4e2b..c015215 100755 --- a/sql/bin/db-upgrade +++ b/sql/bin/db-upgrade @@ -1,73 +1,71 @@ #!/bin/bash # Compute a draft upgrade script for the DB schema, based on Git revisions. # Depends: apgdiff set -e -SQLS="swh-*.sql" -VERSION_SQL="swh-schema.sql" UPGRADE_DIR="upgrades" DB_NAME="softwareheritage-dev" usage () { echo "Usage: db-upgrade GIT_REV_FROM [GIT_REV_TO]" echo "Example: db-upgrade HEAD^" echo " db-upgrade HEAD~4 HEAD~2" echo "See also: gitrevisions(7)" exit 1 } pg_dump_revision () { rev="$1" dump="$2" echo "checking out revision $rev, and dumping DB at the time..." if [ "$rev" != "HEAD" ] ; then git checkout --quiet "$rev" fi make distclean filldb > /dev/null pg_dump "$DB_NAME" > "$dump" if [ "$rev" != "HEAD" ] ; then git checkout --quiet - fi } # argument parsing if [ -z "$1" ] ; then usage fi from_rev="$1" shift 1 if [ -z "$1" ] ; then to_rev="HEAD" else to_rev="$1" shift 1 fi old_dump=$(mktemp tmp.swh-db-upgrade.XXXXXXXXXX) new_dump=$(mktemp tmp.swh-db-upgrade.XXXXXXXXXX) trap "rm -f $old_dump $new_dump" EXIT -schema_version=$(grep -i -A 1 '^insert into dbversion' "$VERSION_SQL" | tail -n 1 \ +schema_version=$(grep -hri -A 1 '^insert into dbversion' ../swh/storage/sql | tail -n 1 \ | sed -e 's/.*values(//i' -e 's/,.*//') upgrade_script=$(mktemp -p "$UPGRADE_DIR" $(printf '%.03d' ${schema_version}).XXXX.sql) pg_dump_revision "$from_rev" "$old_dump" pg_dump_revision "$to_rev" "$new_dump" cat > "$upgrade_script" <> "$upgrade_script" echo "all done." echo "Draft upgrade script is at: ${upgrade_script}"