Replace schema URI comment in Redshift DDL generation with Redshift COMMENT ON

[alexanderdean]

Currently we produce Redshift DDL which looks like this:

        -- AUTO-GENERATED BY schema-ddl DO NOT EDIT
        -- [-- Generated: 2015-08-21 11:47] Generator: schema-ddl 0.1.0: 
        -- Source: iglu:com.snowplowanalytics.snowplow/uri_redirect/jsonschema/1-0-0

        CREATE SCHEMA IF NOT EXISTS atomic;

        CREATE TABLE IF NOT EXISTS atomic.com_snowplowanalytics_snowplow_uri_redirect_1 (
    "schema_vendor"  VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "schema_name"    VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "schema_format"  VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "schema_version" VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "root_id"        CHAR(36)      ENCODE RAW      NOT NULL,
    "root_tstamp"    TIMESTAMP    ENCODE RAW      NOT NULL,
    "ref_root"      VARCHAR(255)  ENCODE RUNLENGTH NOT NULL,
    "ref_tree"      VARCHAR(1500) ENCODE RUNLENGTH NOT NULL,
    "ref_parent"    VARCHAR(255)  ENCODE RUNLENGTH NOT NULL,
    "uri"            VARCHAR(8192)                  NOT NULL,
    FOREIGN KEY (root_id) REFERENCES atomic.events(event_id)
        )
        DISTSTYLE KEY
        DISTKEY (root_id)
        SORTKEY (root_tstamp);

We want to move the iglu: Schema URI from a discarded syntax comment to a retained metadata comment like so:

        -- AUTO-GENERATED BY schema-ddl DO NOT EDIT
        -- [-- Generated: 2015-08-21 11:47] Generator: schema-ddl 0.1.0: 
        CREATE SCHEMA IF NOT EXISTS atomic;

        CREATE TABLE IF NOT EXISTS atomic.com_snowplowanalytics_snowplow_uri_redirect_1 (
    "schema_vendor"  VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "schema_name"    VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "schema_format"  VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "schema_version" VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "root_id"        CHAR(36)      ENCODE RAW      NOT NULL,
    "root_tstamp"    TIMESTAMP    ENCODE RAW      NOT NULL,
    "ref_root"      VARCHAR(255)  ENCODE RUNLENGTH NOT NULL,
    "ref_tree"      VARCHAR(1500) ENCODE RUNLENGTH NOT NULL,
    "ref_parent"    VARCHAR(255)  ENCODE RUNLENGTH NOT NULL,
    "uri"            VARCHAR(8192)                  NOT NULL,
    FOREIGN KEY (root_id) REFERENCES atomic.events(event_id)
        )
        DISTSTYLE KEY
        DISTKEY (root_id)
        SORTKEY (root_tstamp);

        COMMENT ON TABLE atomic.com_snowplowanalytics_snowplow_uri_redirect_1 IS 'iglu:com.snowplowanalytics.snowplow/uri_redirect/jsonschema/1-0-0';

This will retain the metadata in Redshift so that Snowplow can find out which version of a table has been deployed.

[alexanderdean]

How to retrieve a comment using SQL:

db00002=# SELECT c.oid FROM pg_catalog.pg_class c WHERE c.relname = 'events';
  oid
--------
108624
162711
(2 rows)

db00002=# select pg_catalog.obj_description(108624);
obj_description
-----------------
version 0.6.0
(1 row)

[chuwy]

Should we add something in raw mode? Source file?

[alexanderdean]

I think given that we are adding the COMMENT ON we can remove the -- comment line which says the same thing...