sql: support for Liquibase

[a-robinson]

We should test out whether we work with Liquibase, which is a schema management tool along the same lines as Flyway, and determine how much work it would be to support if it doesn't work already.

Known issues:

• sql: support precision with date/time fields (tool compat) #24714
• sql: JDBC metadata fails to query indexes (liquibase) #24713
• sql: can't add column to table and insert values in the same transaction #31463
• Liquibase: Disable transactions for each changeset appdev-issues#34
• Liquibase: Implement fix for FAMILY in schema appdev-issues#32
• Liquibase: Implement fix for handling primary keys with DESC columns appdev-issues#33

[wdroste]

+1

First issue I see is the postgresql driver for liquibase uses table spaces, which is far as i can see is not supported in cockroach, not that it needs to be.. just it will be a likely syntax error, for anyone trying to get it to work w/ both a real postgresql database and cockroach or least attempting to migrate.

[jjzazuet]

If it helps, I just gave it a quick shot with a development instance via brew install cockroach, and then running the quick start change log example provided here: http://www.liquibase.org/quickstart.html

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
    <changeSet id="1" author="bob">
        <createTable tableName="department">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="active" type="boolean" defaultValueBoolean="true"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

With the following command line, the change set appears to be working with cockroach 1.0.1.

$ liquibase --driver=org.postgresql.Driver --classpath=/opt/sqldeveloper/jdbc/lib/postgresql-42.1.1.jar --changeLogFile=changelog.xml --url="jdbc:postgresql://192.168.1.114:26257/bank" --username="root" --password="" --logLevel=debug migrate
DEBUG 5/28/17 3:59 AM: liquibase: Connected to root@jdbc:postgresql://192.168.1.114:26257/bank
DEBUG 5/28/17 3:59 AM: liquibase: Setting auto commit to false from true
DEBUG 5/28/17 3:59 AM: liquibase: Executing QUERY database command: select count(*) from bank.databasechangeloglock
DEBUG 5/28/17 3:59 AM: liquibase: Create Database Lock Table
DEBUG 5/28/17 3:59 AM: liquibase: Executing EXECUTE database command: CREATE TABLE bank.databasechangeloglock (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))
DEBUG 5/28/17 3:59 AM: liquibase: Created database lock table with name: bank.databasechangeloglock
DEBUG 5/28/17 3:59 AM: liquibase: Executing QUERY database command: select count(*) from bank.databasechangeloglock
DEBUG 5/28/17 3:59 AM: liquibase: Initialize Database Lock Table
DEBUG 5/28/17 3:59 AM: liquibase: Executing EXECUTE database command: DELETE FROM bank.databasechangeloglock
DEBUG 5/28/17 3:59 AM: liquibase: Executing EXECUTE database command: INSERT INTO bank.databasechangeloglock (ID, LOCKED) VALUES (1, FALSE)
DEBUG 5/28/17 3:59 AM: liquibase: Executing QUERY database command: SELECT LOCKED FROM bank.databasechangeloglock WHERE ID=1
DEBUG 5/28/17 3:59 AM: liquibase: Lock Database
DEBUG 5/28/17 3:59 AM: liquibase: Executing UPDATE database command: UPDATE bank.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = '192.168.81.1 (192.168.81.1)', LOCKGRANTED = '2017-05-28 03:59:18.366' WHERE ID = 1 AND LOCKED = FALSE
INFO 5/28/17 3:59 AM: liquibase: Successfully acquired change log lock
DEBUG 5/28/17 3:59 AM: liquibase: Resolving XML entity name='null', publicId='null', baseURI='null', systemId='http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd'
DEBUG 5/28/17 3:59 AM: liquibase: Opening jar:file:/opt/liquibase/liquibase.jar!/liquibase/parser/core/xml/dbchangelog-3.1.xsd as liquibase/parser/core/xml/dbchangelog-3.1.xsd
DEBUG 5/28/17 3:59 AM: liquibase: Computed checksum for 1495958358896 as aba2491e358538f531e523e92e9fc6ee
DEBUG 5/28/17 3:59 AM: liquibase: Create Database Change Log Table
INFO 5/28/17 3:59 AM: liquibase: Creating database history table with name: bank.databasechangelog
DEBUG 5/28/17 3:59 AM: liquibase: Executing EXECUTE database command: CREATE TABLE bank.databasechangelog (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP WITHOUT TIME ZONE NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))
DEBUG 5/28/17 3:59 AM: liquibase: Executing QUERY database command: select count(*) from bank.databasechangelog
INFO 5/28/17 3:59 AM: liquibase: Reading from bank.databasechangelog
DEBUG 5/28/17 3:59 AM: liquibase: Executing QUERY database command: SELECT * FROM bank.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
DEBUG 5/28/17 3:59 AM: liquibase: Executing QUERY database command: select count(*) from bank.databasechangeloglock
DEBUG 5/28/17 3:59 AM: liquibase: changelog.xml: changelog.xml::1::bob: Computed checksum for createTable:[
    columns=[
        [
            [
                nullable="false"
                primaryKey="true"
            ]
            name="id"
            type="int"
        ],
        [
            [
                nullable="false"
            ]
            name="name"
            type="varchar(50)"
        ],
        [
            defaultValueBoolean="true"
            name="active"
            type="boolean"
        ]
    ]
    tableName="department"
] as ffa8508dc9cdd8bd88f8063efa23645a
DEBUG 5/28/17 3:59 AM: liquibase: changelog.xml: changelog.xml::1::bob: Computed checksum for 7:ffa8508dc9cdd8bd88f8063efa23645a: as 17a94fcdd73aa8852c1f53e4d06ee651
DEBUG 5/28/17 3:59 AM: liquibase: changelog.xml: changelog.xml::1::bob: Running Changeset:changelog.xml::1::bob
DEBUG 5/28/17 3:59 AM: liquibase: changelog.xml: changelog.xml::1::bob: Changeset changelog.xml::1::bob
DEBUG 5/28/17 3:59 AM: liquibase: changelog.xml: changelog.xml::1::bob: Reading ChangeSet: changelog.xml::1::bob
DEBUG 5/28/17 3:59 AM: liquibase: changelog.xml: changelog.xml::1::bob: Executing Statement: liquibase.statement.core.CreateTableStatement@5b03b9fe
DEBUG 5/28/17 3:59 AM: liquibase: changelog.xml: changelog.xml::1::bob: Executing EXECUTE database command: CREATE TABLE bank.department (id INT NOT NULL, name VARCHAR(50) NOT NULL, active BOOLEAN DEFAULT TRUE, CONSTRAINT PK_DEPARTMENT PRIMARY KEY (id))
INFO 5/28/17 3:59 AM: liquibase: changelog.xml: changelog.xml::1::bob: Table department created
INFO 5/28/17 3:59 AM: liquibase: changelog.xml: changelog.xml::1::bob: ChangeSet changelog.xml::1::bob ran successfully in 7ms
DEBUG 5/28/17 3:59 AM: liquibase: changelog.xml: changelog.xml::1::bob: Executing QUERY database command: SELECT MAX(ORDEREXECUTED) FROM bank.databasechangelog
DEBUG 5/28/17 3:59 AM: liquibase: changelog.xml: changelog.xml::1::bob: Executing EXECUTE database command: INSERT INTO bank.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'bob', 'changelog.xml', NOW(), 1, '7:17a94fcdd73aa8852c1f53e4d06ee651', 'createTable tableName=department', '', 'EXECUTED', NULL, NULL, '3.5.3', '5958358917')
DEBUG 5/28/17 3:59 AM: liquibase: Release Database Lock
DEBUG 5/28/17 3:59 AM: liquibase: Executing UPDATE database command: UPDATE bank.databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1
INFO 5/28/17 3:59 AM: liquibase: Successfully released change log lock
Liquibase Update Successful

How cool is that??

Cheers!

[jjzazuet]

I'm sure there might some complications down the road creating indexes or other kinds of commands specific to PostgreSQL. I'll ping back if I run into issues. Thanks!

[euan-reid]

Currently using liquibase and investigating cockroach - thus far there's been two main issues presenting:

1. Cockroach 1.1 not supporting non-restrict constraints (which I'm aware is implemented for 2.0, so just noting for others' reference before that releases)
2. Failure on dropping a primary key. This is caused by the postgresql driver using the DO command for these, so given that's psql specific it's an understandable failure, but it's something we should address if we plan to support liquibase officially.

In case it's helpful, here's a sanitised version of the full query generated:

BEGIN
  SELECT tc.CONSTRAINT_NAME into strict constraint_name
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
      AND TABLE_NAME = 'myTable' AND TABLE_SCHEMA = 'mySchema';
    EXECUTE 'alter table mySchema.myTable drop constraint ' || constraint_name;
END $$;]

[ruzkant]

Issues I have seen trying to use liquibase:

Changesets:

• type datetime(6) where cockroachdb does not like the (6) in the generated timestamp(6)
• addForeignKeyConstraint: if there is no existing index
• addForeignKeyConstraint: if the index is added after this element
• addForeignKeyConstraint: if the index is added before this element in the same changeset. This is because cockroachdb does not support adding an index followed by a foreign key in a transaction.

Database metadata:

• Liquibase asks for reserved words by calling getSQLKeywords on DatabaseMetaData which runs the following query with the postgres driver (42.2.2):
  select string_agg(word, ',') from pg_catalog.pg_get_keywords() where word <> ALL ('{a,abs,absolute,action,ada,add,admin,after,all,allocate,alter,always,and,any,are,array,as,asc,asensitive,assertion,assignment,asymmetric,at,atomic,attribute,attributes,authorization,avg,before,begin,bernoulli,between,bigint,binary,blob,boolean,both,breadth,by,c,call,called,cardinality,cascade,cascaded,case,cast,catalog,catalog_name,ceil,ceiling,chain,char,char_length,character,character_length,character_set_catalog,character_set_name,character_set_schema,characteristics,characters,check,checked,class_origin,clob,close,coalesce,cobol,code_units,collate,collation,collation_catalog,collation_name,collation_schema,collect,column,column_name,command_function,command_function_code,commit,committed,condition,condition_number,connect,connection_name,constraint,constraint_catalog,constraint_name,constraint_schema,constraints,constructors,contains,continue,convert,corr,corresponding,count,covar_pop,covar_samp,create,cross,cube,cume_dist,current,current_collation,current_date,current_default_transform_group,current_path,current_role,current_time,current_timestamp,current_transform_group_for_type,current_user,cursor,cursor_name,cycle,data,date,datetime_interval_code,datetime_interval_precision,day,deallocate,dec,decimal,declare,default,defaults,deferrable,deferred,defined,definer,degree,delete,dense_rank,depth,deref,derived,desc,describe,descriptor,deterministic,diagnostics,disconnect,dispatch,distinct,domain,double,drop,dynamic,dynamic_function,dynamic_function_code,each,element,else,end,end-exec,equals,escape,every,except,exception,exclude,excluding,exec,execute,exists,exp,external,extract,false,fetch,filter,final,first,float,floor,following,for,foreign,fortran,found,free,from,full,function,fusion,g,general,get,global,go,goto,grant,granted,group,grouping,having,hierarchy,hold,hour,identity,immediate,implementation,in,including,increment,indicator,initially,inner,inout,input,insensitive,insert,instance,instantiable,int,integer,intersect,intersection,interval,into,invoker,is,isolation,join,k,key,key_member,key_type,language,large,last,lateral,leading,left,length,level,like,ln,local,localtime,localtimestamp,locator,lower,m,map,match,matched,max,maxvalue,member,merge,message_length,message_octet_length,message_text,method,min,minute,minvalue,mod,modifies,module,month,more,multiset,mumps,name,names,national,natural,nchar,nclob,nesting,new,next,no,none,normalize,normalized,not,"null",nullable,nullif,nulls,number,numeric,object,octet_length,octets,of,old,on,only,open,option,options,or,order,ordering,ordinality,others,out,outer,output,over,overlaps,overlay,overriding,pad,parameter,parameter_mode,parameter_name,parameter_ordinal_position,parameter_specific_catalog,parameter_specific_name,parameter_specific_schema,partial,partition,pascal,path,percent_rank,percentile_cont,percentile_disc,placing,pli,position,power,preceding,precision,prepare,preserve,primary,prior,privileges,procedure,public,range,rank,read,reads,real,recursive,ref,references,referencing,regr_avgx,regr_avgy,regr_count,regr_intercept,regr_r2,regr_slope,regr_sxx,regr_sxy,regr_syy,relative,release,repeatable,restart,result,return,returned_cardinality,returned_length,returned_octet_length,returned_sqlstate,returns,revoke,right,role,rollback,rollup,routine,routine_catalog,routine_name,routine_schema,row,row_count,row_number,rows,savepoint,scale,schema,schema_name,scope_catalog,scope_name,scope_schema,scroll,search,second,section,security,select,self,sensitive,sequence,serializable,server_name,session,session_user,set,sets,similar,simple,size,smallint,some,source,space,specific,specific_name,specifictype,sql,sqlexception,sqlstate,sqlwarning,sqrt,start,state,statement,static,stddev_pop,stddev_samp,structure,style,subclass_origin,submultiset,substring,sum,symmetric,system,system_user,table,table_name,tablesample,temporary,then,ties,time,timestamp,timezone_hour,timezone_minute,to,top_level_count,trailing,transaction,transaction_active,transactions_committed,transactions_rolled_back,transform,transforms,translate,translation,treat,trigger,trigger_catalog,trigger_name,trigger_schema,trim,true,type,uescape,unbounded,uncommitted,under,union,unique,unknown,unnamed,unnest,update,upper,usage,user,user_defined_type_catalog,user_defined_type_code,user_defined_type_name,user_defined_type_schema,using,value,values,var_pop,var_samp,varchar,varying,view,when,whenever,where,width_bucket,window,with,within,without,work,write,year,zone}'::text[])

[ruzkant]

So the reserved words query is halfway there. Cockroachdb 2.0 supports pg_get_keywords(), but string_agg is not yet supported but referenced in #10495

[ruzkant]

Related issues:

#24714
#24713

[ruzkant]

In general making sure all JDBC metadata queries in the postgres driver works, will help the effort as liquibase relies a lot on this metadata.

https://github.com/pgjdbc/pgjdbc/blob/7a586b6e492e8911a928d50113a68569981fa731/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java

[patelh]

Any updates on this?

[jordanlewis]

@patelh, @timveil has done some investigation on this and discovered that Liquibase support has drastically improved since the last time we commented on this issue. However, we still haven't figured out how to run comprehensive tests on our compatibility.

I would recommend trying out CockroachDB with Liquibase on your application and reporting back. We'll be able to help more if you do that.

Thanks!

[awoods187]

Also worth noting that Liquibase doesn't respect the order of primary keys on Postgres databases. Instead, it uses the order of columns in the table, so keep an eye out for that.

[patelh]

@jordanlewis @awoods187 will do thanks!

[jordanlewis]

@rafiss, do you have a tracking issue for this one? I'm going to move it to the AppDev board, but feel free to close!

[rafiss]

Thanks Jordan.

@vy-ton: we can use this to track any further issues as they come up.