playground: Database schema migration over TLS.
Added pgadmin tool wrapping the rubenv/sql-migrate library and using
v.io/x/lib/dbutil for TLS connections.
Fixed wspr install in Dockerfile.
MultiPart: 1/2
Change-Id: Iac8de313c2131e36e5144494afdbfb26f3b60a1f
diff --git a/client/stylesheets/components/results.css b/client/stylesheets/components/results.css
index c8312d0..737ab10 100644
--- a/client/stylesheets/components/results.css
+++ b/client/stylesheets/components/results.css
@@ -1,3 +1,7 @@
+/* Copyright 2015 The Vanadium Authors. All rights reserved. */
+/* Use of this source code is governed by a BSD-style */
+/* license that can be found in the LICENSE file. */
+
.results {
display: flex;
flex-direction: column;
diff --git a/client/test/test-component-results.js b/client/test/test-component-results.js
index 815cc6f..374ad3c 100644
--- a/client/test/test-component-results.js
+++ b/client/test/test-component-results.js
@@ -1,3 +1,7 @@
+// Copyright 2015 The Vanadium Authors. All rights reserved.
+// Use of this source code is governed by a BSD-style
+// license that can be found in the LICENSE file.
+
var test = require('tape');
var results = require('../browser/components/results');
var raf = require('raf');
diff --git a/go/src/v.io/x/playground/.gitignore b/go/src/v.io/x/playground/.gitignore
index 17618d0..a5f062c 100644
--- a/go/src/v.io/x/playground/.gitignore
+++ b/go/src/v.io/x/playground/.gitignore
@@ -1,3 +1,4 @@
netrc
config/*.json
!config/db-*-example.json
+!config/db-*-default.json
diff --git a/go/src/v.io/x/playground/Dockerfile b/go/src/v.io/x/playground/Dockerfile
index c70158d..cb701f6 100644
--- a/go/src/v.io/x/playground/Dockerfile
+++ b/go/src/v.io/x/playground/Dockerfile
@@ -35,6 +35,7 @@
RUN npm install --production $V23_ROOT/release/javascript/core
# Install Vanadium libraries and playground binaries.
+RUN v23 go install -a -tags wspr -v v.io/x/ref/services/wspr/...
RUN v23 go install -v v.io/...
# Uncomment the following lines to install a version of the builder tool using
diff --git a/go/src/v.io/x/playground/Makefile b/go/src/v.io/x/playground/Makefile
index e41b9b4..55a266a 100644
--- a/go/src/v.io/x/playground/Makefile
+++ b/go/src/v.io/x/playground/Makefile
@@ -37,6 +37,16 @@
--origin='*' \
--use-docker=false
+.PHONY: pgadmin
+pgadmin:
+ v23 go install v.io/x/playground/pgadmin
+
+.PHONY: updatedb
+updatedb: config/db.json pgadmin
+ pgadmin \
+ --sqlconf=$< \
+ migrate up
+
config/db.json:
@echo "You are missing config/db.json, create this file based"
@echo "on the appropriate examples in the config directory."
diff --git a/go/src/v.io/x/playground/README.md b/go/src/v.io/x/playground/README.md
index 9392bfa..88f6550 100644
--- a/go/src/v.io/x/playground/README.md
+++ b/go/src/v.io/x/playground/README.md
@@ -82,19 +82,17 @@
Create playground databases:
- MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS playground;
+ MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS pg_moria;
Create a playground user who has access to the playground database:
- MariaDB [(none)]> GRANT ALL PRIVILEGES ON playground.* TO 'playground'@'localhost';
+ MariaDB [(none)]> GRANT ALL PRIVILEGES ON pg_moria.* TO 'pg_gandalf'@'localhost' IDENTIFIED BY 'mellon';
-Create config/db.json from example:
+Create `config/db.json` from default:
- $ cp config/db-local-example.json config/db.json
+ $ cp config/db-local-default.json config/db.json
-Edit config/db.json and set username, password, and database.
-
-TODO(ivanpi): Describe cloud storage.
+Alternatively, make your own from example.
# Running tests
@@ -119,30 +117,28 @@
## Running migrations
-Build the `sql-migrate` tool:
+Migrations use the `github.com/rubenv/sql-migrate` library, wrapped in a tool
+`pgadmin` to allow TLS connections.
- $ v23 go install github.com/rubenv/sql-migrate/sql-migrate
+Create the database and `config/db.json` file following instructions above.
-Edit config/migrate.yml. Find or define whatever environment you plan to
-migrate, and make sure the datasource is correct.
+To migrate up, first run with -n (dry run):
-To see the current migration status, run:
+ $ $V23_ROOT/release/projects/playground/go/bin/pgadmin -sqlconf=./config/db.json migrate up -n
- $ $V23_ROOT/third_party/go/bin/sql-migrate status -config=./config/migrate.yml -env=<environment>
+If everything looks good, run the same command without -n; alternatively, run:
-To migrate up, first run with -dryrun:
-
- $ $V23_ROOT/third_party/go/bin/sql-migrate up -config=./config/migrate.yml -env=<environment> -dryrun
-
-If everything looks good, run the tool without -dryrun:
-
- $ $V23_ROOT/third_party/go/bin/sql-migrate up -config=./config/migrate.yml -env=<environment>
+ $ make updatedb
You can undo the last migration with:
- $ $V23_ROOT/third_party/go/bin/sql-migrate down -limit=1 -config=./config/migrate.yml -env=<environment>
+ $ $V23_ROOT/release/projects/playground/go/bin/pgadmin -sqlconf=./config/db.json migrate down -limit=1
-For more options and infomation, see https://github.com/rubenv/sql-migrate#usage
+For more options and infomation, run:
+
+ $ $V23_ROOT/release/projects/playground/go/bin/pgadmin help
+
+and see https://github.com/rubenv/sql-migrate
## Writing migrations
diff --git a/go/src/v.io/x/playground/config/db-local-default.json b/go/src/v.io/x/playground/config/db-local-default.json
new file mode 100644
index 0000000..3fdbcd9
--- /dev/null
+++ b/go/src/v.io/x/playground/config/db-local-default.json
@@ -0,0 +1,4 @@
+{
+ "dataSourceName": "pg_gandalf:mellon@tcp(localhost:3306)/pg_moria",
+ "tlsDisable": true
+}
diff --git a/go/src/v.io/x/playground/config/migrate.yml b/go/src/v.io/x/playground/config/migrate.yml
deleted file mode 100644
index 84dc6e8..0000000
--- a/go/src/v.io/x/playground/config/migrate.yml
+++ /dev/null
@@ -1,12 +0,0 @@
-development:
- dialect: mysql
- datasource: playground:@tcp(localhost:3306)/playground?parseTime=true
- dir: ./migrations
- table: migrations
-
-production:
- dialect: mysql
- datasource: <username>:<password>@tcp(<ip_address>:3306)/<database>
- dir: ./migrations
- table: migrations
-
diff --git a/go/src/v.io/x/playground/pgadmin/main.go b/go/src/v.io/x/playground/pgadmin/main.go
new file mode 100644
index 0000000..cbbdaf2
--- /dev/null
+++ b/go/src/v.io/x/playground/pgadmin/main.go
@@ -0,0 +1,37 @@
+// Copyright 2015 The Vanadium Authors. All rights reserved.
+// Use of this source code is governed by a BSD-style
+// license that can be found in the LICENSE file.
+
+// Admin tool for managing playground database and default bundles.
+
+package main
+
+import (
+ "flag"
+ "os"
+
+ "v.io/x/lib/cmdline"
+ "v.io/x/lib/dbutil"
+)
+
+func main() {
+ os.Exit(cmdPGAdmin.Main())
+}
+
+var cmdPGAdmin = &cmdline.Command{
+ Name: "pgadmin",
+ Short: "Playground database management tool",
+ Long: `
+Tool for managing the playground database and default bundles.
+Supports database schema migration.
+TODO(ivanpi): bundle bootstrap
+`,
+ Children: []*cmdline.Command{cmdMigrate},
+}
+
+var (
+ flagDryRun = flag.Bool("n", false, "Show what commands will run, but do not execute them.")
+
+ // Path to SQL configuration file, as described in v.io/x/lib/dbutil/mysql.go. Required parameter for most commands.
+ flagSQLConf = flag.String("sqlconf", "", "Path to SQL configuration file. "+dbutil.SqlConfigFileDescription)
+)
diff --git a/go/src/v.io/x/playground/pgadmin/migrate.go b/go/src/v.io/x/playground/pgadmin/migrate.go
new file mode 100644
index 0000000..fcb2b25
--- /dev/null
+++ b/go/src/v.io/x/playground/pgadmin/migrate.go
@@ -0,0 +1,147 @@
+// Copyright 2015 The Vanadium Authors. All rights reserved.
+// Use of this source code is governed by a BSD-style
+// license that can be found in the LICENSE file.
+
+// Wrapper around rubenv/sql-migrate to allow MySQL SSL connections using
+// dbutil (uses dbutil sqlconf files and flags with playground-specific
+// defaults instead of rubenv/sql-migrate YAML config).
+//
+// WARNING: MySQL doesn't support rolling back DDL transactions, so any failure
+// after migrations have started requires restoring from backup or manually
+// repairing database state!
+
+package main
+
+import (
+ "database/sql"
+ "fmt"
+ "os"
+
+ "github.com/rubenv/sql-migrate"
+
+ "v.io/x/lib/cmdline"
+ "v.io/x/lib/dbutil"
+)
+
+const mysqlWarning = `
+WARNING: MySQL doesn't support rolling back DDL transactions, so any failure
+after migrations have started requires restoring from backup or manually
+repairing database state!
+`
+
+// TODO(ivanpi): Add status command and sanity checks (e.g. "skipped" migrations are incorrectly applied by rubenv/sql-migrate).
+// TODO(ivanpi): Guard against version skew corrupting data (e.g. add version check to client).
+
+var cmdMigrate = &cmdline.Command{
+ Name: "migrate",
+ Short: "Database schema migrations",
+ Long: `
+See github.com/rubenv/sql-migrate
+` + mysqlWarning,
+ Children: []*cmdline.Command{cmdMigrateUp, cmdMigrateDown},
+}
+
+var cmdMigrateUp = &cmdline.Command{
+ Run: runWithDBConn(runMigrate(migrate.Up)),
+ Name: "up",
+ Short: "Apply new database schema migrations",
+ Long: `
+See github.com/rubenv/sql-migrate
+` + mysqlWarning,
+}
+
+var cmdMigrateDown = &cmdline.Command{
+ Run: runWithDBConn(runMigrate(migrate.Down)),
+ Name: "down",
+ Short: "Roll back database schema migrations",
+ Long: `
+See github.com/rubenv/sql-migrate
+` + mysqlWarning,
+}
+
+const (
+ migrationsTable = "migrations"
+ sqlDialect = "mysql"
+ pgMigrationsDir = "${V23_ROOT}/release/projects/playground/go/src/v.io/x/playground/migrations"
+)
+
+var (
+ flagMigrationsDir string
+ flagMigrationsLimit int
+)
+
+func init() {
+ cmdMigrate.Flags.StringVar(&flagMigrationsDir, "dir", pgMigrationsDir, "Path to directory containing migrations.")
+ cmdMigrateUp.Flags.IntVar(&flagMigrationsLimit, "limit", 0, "Maximum number of up migrations to apply. 0 for unlimited.")
+ cmdMigrateDown.Flags.IntVar(&flagMigrationsLimit, "limit", 1, "Maximum number of down migrations to apply. 0 for unlimited.")
+}
+
+// Returns a DBCommand for applying migrations in the provided direction.
+func runMigrate(direction migrate.MigrationDirection) DBCommand {
+ return func(db *sql.DB, cmd *cmdline.Command, args []string) error {
+ migrate.SetTable(migrationsTable)
+
+ source := migrate.FileMigrationSource{
+ Dir: os.ExpandEnv(flagMigrationsDir),
+ }
+
+ if *flagDryRun {
+ planned, _, err := migrate.PlanMigration(db, sqlDialect, source, direction, flagMigrationsLimit)
+ if err != nil {
+ return fmt.Errorf("Failed getting migrations to apply: %v", err)
+ }
+ for i, m := range planned {
+ fmt.Fprintf(cmd.Stdout(), "#%d: %q\n", i, m.Migration.Id)
+ for _, q := range m.Queries {
+ fmt.Fprint(cmd.Stdout(), q)
+ }
+ }
+ return nil
+ } else {
+ amount, err := migrate.ExecMax(db, sqlDialect, source, direction, flagMigrationsLimit)
+ if err != nil {
+ return fmt.Errorf("Migration FAILED (applied %d migrations): %v", amount, err)
+ }
+ fmt.Fprintf(cmd.Stdout(), "Successfully applied %d migrations\n", amount)
+ return nil
+ }
+ }
+}
+
+// Command to be wrapped with runWithDBConn().
+type DBCommand func(db *sql.DB, cmd *cmdline.Command, args []string) error
+
+// runWithDBConn is a wrapper method that handles opening and closing the
+// database connection.
+func runWithDBConn(fx DBCommand) cmdline.Runner {
+ return func(cmd *cmdline.Command, args []string) (rerr error) {
+ if *flagSQLConf == "" {
+ return cmd.UsageErrorf("SQL configuration file (-sqlconf) must be provided")
+ }
+
+ // Open database connection from config,
+ db, err := dbutil.NewSqlDBConnFromFile(*flagSQLConf, "SERIALIZABLE")
+ if err != nil {
+ return fmt.Errorf("Error opening database connection: %v", err)
+ }
+ // Best effort close.
+ defer func() {
+ if cerr := db.Close(); cerr != nil {
+ cerr = fmt.Errorf("Failed closing database connection: %v", cerr)
+ // Merge errors.
+ if rerr == nil {
+ rerr = cerr
+ } else {
+ rerr = fmt.Errorf("%v\n%v", rerr, cerr)
+ }
+ }
+ }()
+ // Ping database to check connection.
+ if err := db.Ping(); err != nil {
+ return fmt.Errorf("Error connecting to database: %v", err)
+ }
+
+ // Run wrapped function.
+ return fx(db, cmd, args)
+ }
+}
