sql

README

Using the SQL schema tool

This package contains the tooling for cadence sql operations. The tooling itself is agnostic of the storage engine behind the sql interface. So, this same tool can be used against, say, OracleDB and MySQLDB

For localhost development

SQL_USER=$USERNAME SQL_PASSWORD=$PASSWD make install-schema-mysql

NOTE: See CONTRIBUTING for prerequisite of make command.

For production

Get the SQL Schema tool

• Use brew to install CLI: brew install cadence-workflow which includes cadence-sql-tool
  • The schema files are located at /usr/local/etc/cadence/schema/.
  • Follow the instructions if you need to install older versions of schema tools via homebrew. However, easier way is to use new versions of schema tools with old versions of schemas. All you need is to check out the older version of schemas from this repo. Run git checkout v0.21.3 to get the v0.21.3 schemas in the schema folder.
• Or build yourself, with make cadence-sql-tool. See CONTRIBUTING for prerequisite of make command.

Note: The binaries can also be found in the ubercadence/server docker images

Do one time database creation and schema setup for a new cluster

• All command below are taking MySQL as example. For postgres, simply use with "--plugin postgres"

./cadence-sql-tool --ep $SQL_HOST_ADDR -p $port --plugin mysql create-database --db cadence
./cadence-sql-tool --ep $SQL_HOST_ADDR -p $port --plugin mysql create-database --db cadence_visibility

./cadence-sql-tool --ep $SQL_HOST_ADDR -p $port --plugin mysql --db cadence setup-schema -v 0.0 -- this sets up just the schema version tables with initial version of 0.0
./cadence-sql-tool --ep $SQL_HOST_ADDR -p $port --plugin mysql --db cadence update-schema -d ./schema/mysql/v8/cadence/versioned -- upgrades your schema to the latest version

./cadence-sql-tool --ep $SQL_HOST_ADDR -p $port --plugin mysql --db cadence_visibility setup-schema -v 0.0 -- this sets up just the schema version tables with initial version of 0.0 for visibility
./cadence-sql-tool --ep $SQL_HOST_ADDR -p $port --plugin mysql --db cadence_visibility update-schema -d ./schema/mysql/v8/visibility/versioned  -- upgrades your schema to the latest version for visibility

Update schema as part of a release

You can only upgrade to a new version after the initial setup done above.

./cadence-sql-tool --ep $SQL_HOST_ADDR -p $port --plugin mysql --db cadence update-schema -d ./schema/mysql/v8/cadence/versioned -v x.x --dryrun -- executes a dryrun of upgrade to version x.x
./cadence-sql-tool --ep $SQL_HOST_ADDR -p $port --plugin mysql --db cadence update-schema -d ./schema/mysql/v8/cadence/versioned -v x.x    -- actually executes the upgrade to version x.x

./cadence-sql-tool --ep $SQL_HOST_ADDR -p $port --plugin mysql --db cadence_visibility update-schema -d ./schema/mysql/v8/visibility/versioned -v x.x --dryrun -- executes a dryrun of upgrade to version x.x
./cadence-sql-tool --ep $SQL_HOST_ADDR -p $port --plugin mysql --db cadence_visibility update-schema -d ./schema/mysql/v8/visibility/versioned -v x.x    -- actually executes the upgrade to version x.x

Documentation

Index

• func BuildCLIOptions() *cli.App
• func CheckCompatibleVersion(cfg config.SQL, expectedVersion string) error
• func RunTool(args []string) error
• func ValidateConnectConfig(cfg *config.SQL) error
• func VerifyCompatibleVersion(cfg config.Persistence) error
• type Connection
  • func NewConnection(cfg *config.SQL) (*Connection, error)
  • func (c *Connection) Close()
  • func (c *Connection) CreateDatabase(name string) error
  • func (c *Connection) CreateSchemaVersionTables() error
  • func (c *Connection) DropAllTables() error
  • func (c *Connection) DropDatabase(name string) error
  • func (c *Connection) DropTable(name string) error
  • func (c *Connection) ExecDDLQuery(stmt string, args ...interface{}) error
  • func (c *Connection) ListTables() ([]string, error)
  • func (c *Connection) ReadSchemaVersion() (string, error)
  • func (c *Connection) UpdateSchemaVersion(newVersion string, minCompatibleVersion string) error
  • func (c *Connection) WriteSchemaUpdateLog(oldVersion string, newVersion string, manifestMD5 string, desc string) error

Functions

func BuildCLIOptions

func BuildCLIOptions() *cli.App

BuildCLIOptions builds the options for cli

func CheckCompatibleVersion

func CheckCompatibleVersion(
	cfg config.SQL,
	expectedVersion string,
) error

CheckCompatibleVersion check the version compatibility

func RunTool

func RunTool(args []string) error

RunTool runs the cadence-cassandra-tool command line tool

func ValidateConnectConfig

func ValidateConnectConfig(cfg *config.SQL) error

ValidateConnectConfig validates params

func VerifyCompatibleVersion

func VerifyCompatibleVersion(
	cfg config.Persistence,
) error

VerifyCompatibleVersion ensures that the installed version of cadence and visibility is greater than or equal to the expected version.

Types

type Connection

type Connection struct {
	// contains filtered or unexported fields
}

Connection is the connection to database

func NewConnection

func NewConnection(cfg *config.SQL) (*Connection, error)

NewConnection creates a new connection to database

func (*Connection) Close

func (c *Connection) Close()

Close closes the sql client

func (*Connection) CreateDatabase

func (c *Connection) CreateDatabase(name string) error

CreateDatabase creates a database if it doesn't exist

func (*Connection) CreateSchemaVersionTables

func (c *Connection) CreateSchemaVersionTables() error

CreateSchemaVersionTables sets up the schema version tables

func (*Connection) DropAllTables

func (c *Connection) DropAllTables() error

DropAllTables drops all tables from this database

func (*Connection) DropDatabase

func (c *Connection) DropDatabase(name string) error

DropDatabase drops a database

func (*Connection) DropTable

func (c *Connection) DropTable(name string) error

DropTable drops a given table from the database

func (*Connection) ExecDDLQuery

func (c *Connection) ExecDDLQuery(stmt string, args ...interface{}) error

ExecDDLQuery executes a sql statement

func (*Connection) ListTables

func (c *Connection) ListTables() ([]string, error)

ListTables returns a list of tables in this database

func (*Connection) ReadSchemaVersion

func (c *Connection) ReadSchemaVersion() (string, error)

ReadSchemaVersion returns the current schema version for the keyspace

func (*Connection) UpdateSchemaVersion

func (c *Connection) UpdateSchemaVersion(newVersion string, minCompatibleVersion string) error

UpdateSchemaVersion updates the schema version for the keyspace

func (*Connection) WriteSchemaUpdateLog

func (c *Connection) WriteSchemaUpdateLog(oldVersion string, newVersion string, manifestMD5 string, desc string) error

WriteSchemaUpdateLog adds an entry to the schema update history table