public function tableTemplate($auditTableName, $changesetTableName = null, $schemaName = null)
    {
        if ($schemaName === null) {
            $schemaName = 'public';
        }
        $result = [];
        $auditColumns = [
            'action_id'         => 'bigserial NOT NULL PRIMARY KEY',
            'schema_name'       => 'text NOT NULL',
            'table_name'        => 'text NOT NULL',
            'relation_id'       => 'oid NOT NULL',
            'transaction_date'  => 'timestamp with time zone NOT NULL',
            'statement_date'    => 'timestamp with time zone NOT NULL',
            'action_date'       => 'timestamp with time zone NOT NULL',
            'transaction_id'    => 'bigint',
            'session_user_name' => 'text',
            'application_name'  => 'text',
            'client_addr'       => 'inet',
            'client_port'       => 'integer',
            'query'             => 'text',
            'action_type'       => "$schemaName.action_type NOT NULL",
            'row_data'          => 'jsonb',
            'changed_fields'    => 'jsonb',
            'statement_only'    => 'boolean NOT NULL DEFAULT FALSE',
            'key_type'          => "char(1) NOT NULL CHECK (key_type IN ('t', 'a'))",
        ];
        if ($changesetTableName !== null) {
            $auditColumns['key_type'] = "char(1) NOT NULL CHECK (key_type IN ('c', 't', 'a'))";
            $auditColumns['changeset_id'] = "integer REFERENCES $schemaName.$changesetTableName (id) ON UPDATE CASCADE ON DELETE CASCADE";
            $metaColumns = [
                'id'             => 'serial NOT NULL PRIMARY KEY',
                'transaction_id' => 'bigint',
                'user_id'        => 'integer',
                'session_id'     => 'text',
                'request_date'   => 'timestamp with time zone NOT NULL',
                'request_url'    => 'text',
                'request_addr'   => 'inet',
            ];
            $result[] = [
                'name' => "$schemaName.$changesetTableName",
                'exists' => $this->db->schema->getTableSchema("$schemaName.$changesetTableName") !== null,
                'columns' => $metaColumns,
                'indexes' => [
                    'transaction_id', 'User_id', 'session_id', 'request_url', 'request_addr',
                ],
            ];
        }
        $result[] = [
            'name' => "$schemaName.$auditTableName",
            'exists' => $this->db->schema->getTableSchema("$schemaName.$auditTableName") !== null,
            'columns' => $auditColumns,
            'indexes' => array_merge([
                'schema_name, table_name',
                'relation_id', 'statement_date',
                'action_type', 'key_type', 'statement_only',
                'row_data' => 'USING GIN (row_data jsonb_path_ops)',
            ], $changesetTableName === null ? [
                "key_type, (CASE key_type WHEN 'c' THEN changeset_id WHEN 't' THEN transaction_id ELSE action_id END))",
            ] : [
                "key_type, (CASE key_type WHEN 't' THEN transaction_id ELSE action_id END))",
                'changeset_id',
            ]),
        ];
        return $result;
    }
public function tableTemplate($auditTableName, $changesetTableName = null, $schemaName = null)
    {
        if ($schemaName === null) {
            $schemaName = 'public';
        }
        $result = [];
        $auditColumns = [
            'action_id'         => 'bigserial NOT NULL PRIMARY KEY',
            'schema_name'       => 'text NOT NULL',
            'table_name'        => 'text NOT NULL',
            'relation_id'       => 'oid NOT NULL',
            'transaction_date'  => 'timestamp with time zone NOT NULL',
            'statement_date'    => 'timestamp with time zone NOT NULL',
            'action_date'       => 'timestamp with time zone NOT NULL',
            'transaction_id'    => 'bigint',
            'session_user_name' => 'text',
            'application_name'  => 'text',
            'client_addr'       => 'inet',
            'client_port'       => 'integer',
            'query'             => 'text',
            'action_type'       => "$schemaName.action_type NOT NULL",
            'row_data'          => 'jsonb',
            'changed_fields'    => 'jsonb',
            'statement_only'    => 'boolean NOT NULL DEFAULT FALSE',
            'key_type'          => "char(1) NOT NULL CHECK (key_type IN ('t', 'a'))",
        ];
        if ($changesetTableName !== null) {
            $auditColumns['key_type'] = "char(1) NOT NULL CHECK (key_type IN ('c', 't', 'a'))";
            $auditColumns['changeset_id'] = "integer REFERENCES $schemaName.$changesetTableName (id) ON UPDATE CASCADE ON DELETE CASCADE";
            $metaColumns = [
                'id'             => 'serial NOT NULL PRIMARY KEY',
                'transaction_id' => 'bigint',
                'user_id'        => 'integer',
                'session_id'     => 'text',
                'request_date'   => 'timestamp with time zone NOT NULL',
                'request_url'    => 'text',
                'request_addr'   => 'inet',
            ];
            $result[] = [
                'name' => "$schemaName.$changesetTableName",
                'exists' => $this->db->schema->getTableSchema("$schemaName.$changesetTableName") !== null,
                'columns' => $metaColumns,
                'indexes' => [
                    'transaction_id', 'User_id', 'session_id', 'request_url', 'request_addr',
                ],
            ];
        }
        $result[] = [
            'name' => "$schemaName.$auditTableName",
            'exists' => $this->db->schema->getTableSchema("$schemaName.$auditTableName") !== null,
            'columns' => $auditColumns,
            'indexes' => array_merge([
                'schema_name, table_name',
                'relation_id', 'statement_date',
                'action_type', 'key_type', 'statement_only',
                'row_data' => 'USING GIN (row_data jsonb_path_ops)',
            ], $changesetTableName === null ? [
                "key_type, (CASE key_type WHEN 'c' THEN changeset_id WHEN 't' THEN transaction_id ELSE action_id END))",
            ] : [
                "key_type, (CASE key_type WHEN 't' THEN transaction_id ELSE action_id END))",
                'changeset_id',
            ]),
        ];
        return $result;
    }
public function tableTemplate ( $ auditTableName , $ changesetTableName = null , $ schemaName = null ) { if ( $ schemaName === null ) { $ schemaName = 'public' ; } $ result = [ ] ; $ auditColumns = [ 'action_id' => 'bigserial NOT NULL PRIMARY KEY' , 'schema_name' => 'text NOT NULL' , 'table_name' => 'text NOT NULL' , 'relation_id' => 'oid NOT NULL' , 'transaction_date' => 'timestamp with time zone NOT NULL' , 'statement_date' => 'timestamp with time zone NOT NULL' , 'action_date' => 'timestamp with time zone NOT NULL' , 'transaction_id' => 'bigint' , 'session_user_name' => 'text' , 'application_name' => 'text' , 'client_addr' => 'inet' , 'client_port' => 'integer' , 'query' => 'text' , 'action_type' => "$schemaName.action_type NOT NULL" , 'row_data' => 'jsonb' , 'changed_fields' => 'jsonb' , 'statement_only' => 'boolean NOT NULL DEFAULT FALSE' , 'key_type' => "char(1) NOT NULL CHECK (key_type IN ('t', 'a'))" , ] ; if ( $ changesetTableName !== null ) { $ auditColumns [ 'key_type' ] = "char(1) NOT NULL CHECK (key_type IN ('c', 't', 'a'))" ; $ auditColumns [ 'changeset_id' ] = "integer REFERENCES $schemaName.$changesetTableName (id) ON UPDATE CASCADE ON DELETE CASCADE" ; $ metaColumns = [ 'id' => 'serial NOT NULL PRIMARY KEY' , 'transaction_id' => 'bigint' , 'user_id' => 'integer' , 'session_id' => 'text' , 'request_date' => 'timestamp with time zone NOT NULL' , 'request_url' => 'text' , 'request_addr' => 'inet' , ] ; $ result [ ] = [ 'name' => "$schemaName.$changesetTableName" , 'exists' => $ this -> db -> schema -> getTableSchema ( "$schemaName.$changesetTableName" ) !== null , 'columns' => $ metaColumns , 'indexes' => [ 'transaction_id' , 'User_id' , 'session_id' , 'request_url' , 'request_addr' , ] , ] ; } $ result [ ] = [ 'name' => "$schemaName.$auditTableName" , 'exists' => $ this -> db -> schema -> getTableSchema ( "$schemaName.$auditTableName" ) !== null , 'columns' => $ auditColumns , 'indexes' => array_merge ( [ 'schema_name, table_name' , 'relation_id' , 'statement_date' , 'action_type' , 'key_type' , 'statement_only' , 'row_data' => 'USING GIN (row_data jsonb_path_ops)' , ] , $ changesetTableName === null ? [ "key_type, (CASE key_type WHEN 'c' THEN changeset_id WHEN 't' THEN transaction_id ELSE action_id END))" , ] : [ "key_type, (CASE key_type WHEN 't' THEN transaction_id ELSE action_id END))" , 'changeset_id' , ] ) , ] ; return $ result ; }
Returns an array with columns list for the audit table that stores row version.
If the table already exists, also returns current columns for comparison.
@param string $auditTableName name of the audit table to store row versions
@param string $changesetTableName name of the changeset table name to store metadata
@param string $schemaName     if null, defaults to 'public'
@return array with three keys: exists(boolean), columns(array of strings) and currentColumns(array of strings)
@throws Exception
Returns an array with columns list for the audit table that stores row version . If the table already exists also returns current columns for comparison .