/*
* ModeShape (http://www.modeshape.org)
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.modeshape.sequencer.ddl.dialect.postgres;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertTrue;
import static org.modeshape.sequencer.ddl.StandardDdlLexicon.TYPE_CREATE_SCHEMA_STATEMENT;
import static org.modeshape.sequencer.ddl.StandardDdlLexicon.TYPE_CREATE_TABLE_STATEMENT;
import static org.modeshape.sequencer.ddl.StandardDdlLexicon.TYPE_DROP_COLUMN_DEFINITION;
import static org.modeshape.sequencer.ddl.dialect.postgres.PostgresDdlLexicon.TYPE_ALTER_FOREIGN_DATA_WRAPPER_STATEMENT;
import static org.modeshape.sequencer.ddl.dialect.postgres.PostgresDdlLexicon.TYPE_ALTER_TABLE_STATEMENT_POSTGRES;
import static org.modeshape.sequencer.ddl.dialect.postgres.PostgresDdlLexicon.TYPE_COMMENT_ON_STATEMENT;
import static org.modeshape.sequencer.ddl.dialect.postgres.PostgresDdlLexicon.TYPE_CREATE_RULE_STATEMENT;
import static org.modeshape.sequencer.ddl.dialect.postgres.PostgresDdlLexicon.TYPE_CREATE_SEQUENCE_STATEMENT;
import static org.modeshape.sequencer.ddl.dialect.postgres.PostgresDdlLexicon.TYPE_GRANT_ON_FUNCTION_STATEMENT;
import static org.modeshape.sequencer.ddl.dialect.postgres.PostgresDdlLexicon.TYPE_LISTEN_STATEMENT;
import static org.modeshape.sequencer.ddl.dialect.postgres.PostgresDdlLexicon.TYPE_RENAME_COLUMN;
import org.junit.Before;
import org.junit.Test;
import org.modeshape.sequencer.ddl.DdlConstants;
import org.modeshape.sequencer.ddl.DdlParserScorer;
import org.modeshape.sequencer.ddl.DdlParserTestHelper;
import org.modeshape.sequencer.ddl.node.AstNode;
/**
*
*/
public class PostgresDdlParserTest extends DdlParserTestHelper {
private static final String SPACE = DdlConstants.SPACE;
public static final String DDL_FILE_PATH = "ddl/dialect/postgres/";
@Before
public void beforeEach() {
parser = new PostgresDdlParser();
setPrintToConsole(false);
parser.setTestMode(isPrintToConsole());
parser.setDoUseTerminator(true);
rootNode = parser.nodeFactory().node("ddlRootNode");
scorer = new DdlParserScorer();
}
@Test
public void shouldParseAlterTableMultipleAddColumns() {
printTest("shouldParseAlterTableMultipleAddColumns()");
String content = "ALTER TABLE distributors \n" + " ADD COLUMN nick_name varchar(30), \n"
+ " ADD COLUMN address varchar(30);";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_ALTER_TABLE_STATEMENT_POSTGRES));
assertEquals(2, childNode.getChildCount());
}
@Test
public void shouldParseAlterTableMultipleMixedActions() {
printTest("shouldParseAlterTableMultipleAddColumns()");
String content = "ALTER TABLE distributors \n" + " ADD COLUMN nick_name varchar(30), \n"
+ " ALTER COLUMN address TYPE varchar(255), \n" + " RENAME COLUMN address TO city, \n"
+ " DROP COLUMN address;";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_ALTER_TABLE_STATEMENT_POSTGRES));
assertEquals(4, childNode.getChildCount());
assertTrue(hasMixinType(childNode.getChild(2), TYPE_RENAME_COLUMN));
assertTrue(hasMixinType(childNode.getChild(3), TYPE_DROP_COLUMN_DEFINITION));
}
@Test
public void shouldParseAlterTableMultipleAlterColumns() {
printTest("shouldParseAlterTableMultipleAlterColumns()");
String content = "ALTER TABLE distributors ALTER COLUMN address TYPE varchar(80), ALTER COLUMN name TYPE varchar(100);";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_ALTER_TABLE_STATEMENT_POSTGRES));
}
@Test
public void shouldParseAlterTableMultipeAlterColumns_2() {
printTest("shouldParseAlterTableMultipeAlterColumns_2()");
String content = "ALTER TABLE foo" + SPACE + "ALTER COLUMN foo_timestamp DROP DEFAULT," + SPACE
+ "ALTER COLUMN foo_timestamp TYPE timestamp with time zone" + SPACE
+ "USING timestamp with time zone ’epoch’ + foo_timestamp *GO interval ’1 second’," + SPACE
+ "ALTER COLUMN foo_timestamp SET DEFAULT now();";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_ALTER_TABLE_STATEMENT_POSTGRES));
}
@Test
public void shouldParseAlterTableMultipeColumns_3() {
printTest("shouldParseAlterTableMultipeColumns_3()");
String content = "ALTER TABLE foo" + SPACE + "ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone" + SPACE
+ "USING timestamp with time zone ’epoch’ + foo_timestamp * interval ’1 second’;";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_ALTER_TABLE_STATEMENT_POSTGRES));
}
@Test
public void shouldParseAlterTableMultipeColumns_4() {
printTest("shouldParseAlterTableMultipeColumns_4()");
String content = "ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_ALTER_TABLE_STATEMENT_POSTGRES));
}
@Test
public void shouldParseAlterTableMultipeColumns_5() {
printTest("shouldParseAlterTableMultipeColumns_5()");
String content = "ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_ALTER_TABLE_STATEMENT_POSTGRES));
}
@Test
public void shouldParseAlterTableMultipeColumns_6() {
printTest("shouldParseAlterTableMultipeColumns_6()");
String content = "ALTER TABLE distributors SET TABLESPACE fasttablespace;";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_ALTER_TABLE_STATEMENT_POSTGRES));
}
@Test
public void shouldParseCreateSchema() {
printTest("shouldParseCreateSchema()");
String content = "CREATE SCHEMA hollywood" + SPACE + "CREATE TABLE films (title text, release date, awards text[])"
+ SPACE + "CREATE VIEW winners AS SELECT title, release FROM films WHERE awards IS NOT NULL;";
assertScoreAndParse(content, null, 1); // schema
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_CREATE_SCHEMA_STATEMENT));
}
@Test
public void shouldParseCreateSequence() {
printTest("shouldParseCreateSequence()");
String content = "CREATE SEQUENCE serial START 101;";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_CREATE_SEQUENCE_STATEMENT));
}
// CREATE TABLE films (
// code char(5) CONSTRAINT firstkey PRIMARY KEY,
// title varchar(40) NOT NULL,
// did integer NOT NULL,
// date_prod date,
// kind varchar(10),
// len interval hour to minute
// );
@Test
public void shouldParseCreateTable_1() {
printTest("shouldParseCreateTable_1()");
String content = "CREATE TABLE films (" + SPACE + "code char(5) CONSTRAINT firstkey PRIMARY KEY," + SPACE
+ "title varchar(40) NOT NULL," + SPACE + "did integer NOT NULL," + SPACE
+ "date_prod date," + SPACE + "kind varchar(10)," + SPACE
+ "len interval hour to minute" + SPACE + ");";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_CREATE_TABLE_STATEMENT));
}
// CREATE TABLE distributors (
// did integer PRIMARY KEY DEFAULT nextval(’serial’),
// name varchar(40) NOT NULL CHECK (name <> ”)
//
// );
@Test
public void shouldParseCreateTable_2() {
printTest("shouldParseCreateTable_2()");
String content = "CREATE TABLE distributors (" + SPACE + "did integer PRIMARY KEY DEFAULT nextval(’serial’)," + SPACE
+ "name varchar(40) NOT NULL CHECK (name <> ”)" + SPACE + ");";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_CREATE_TABLE_STATEMENT));
}
// CREATE TABLE distributors (
// name varchar(40) DEFAULT ’Luso Films’,
// did integer DEFAULT nextval(’distributors_serial’),
// modtime timestamp DEFAULT current_timestamp
// );
@Test
public void shouldParseCreateTable_3() {
printTest("shouldParseCreateTable_3()");
String content = "CREATE TABLE distributors (" + SPACE + "name varchar(40) DEFAULT 'xxxx yyyy'," + SPACE
+ "name varchar(40) DEFAULT ’Luso Films’," + SPACE
+ "did integer DEFAULT nextval(’distributors_serial’)," + SPACE
+ "modtime timestamp DEFAULT current_timestamp" + SPACE + ");";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_CREATE_TABLE_STATEMENT));
}
// CREATE TABLE films_recent AS
// SELECT * FROM films WHERE date_prod >= ’2002-01-01’;
@Test
public void shouldParseCreateTable_4() {
printTest("shouldParseCreateTable_4()");
String content = "CREATE TABLE films_recent AS" + SPACE + "SELECT * FROM films WHERE date_prod >= ’2002-01-01’;";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_CREATE_TABLE_STATEMENT));
}
// LISTEN virtual;
@Test
public void shouldParseListen() {
printTest("shouldParseListen()");
String content = "LISTEN virtual;";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_LISTEN_STATEMENT));
}
// CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
// EXECUTE recentfilms(’2002-01-01’);
@Test
public void shouldParseCreateTempTable() {
printTest("shouldParseCreateTempTable()");
String content = "CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS EXECUTE recentfilms(’2002-01-01’);";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_CREATE_TABLE_STATEMENT));
}
// CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;
@Test
public void shouldParseCreateRule() {
printTest("shouldParseCreateRule()");
String content = "CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_CREATE_RULE_STATEMENT));
}
@Test
public void shouldParseAlterForeignDataWrapper() {
printTest("");
String content = "ALTER FOREIGN DATA WRAPPER dbi OPTIONS (ADD foo ’1’, DROP ’bar’);";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_ALTER_FOREIGN_DATA_WRAPPER_STATEMENT));
}
@Test
public void shouldParseCommentOn() {
printTest("shouldParseCommentOn()");
String content = "COMMENT ON TABLE mytable IS ’This is my table.’;";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_COMMENT_ON_STATEMENT));
}
@Test
public void shouldParseCreateFunctionWithMultipleSemicolons() {
printTest("shouldParseCreateFunctionWithMultipleSemicolons()");
String content = "CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ BEGIN RETURN i + 1; END;" + SPACE
+ "CREATE TABLE tblName_A (col_1 varchar(255));";
assertScoreAndParse(content, null, 2);
}
@Test
public void shouldParseLockTable() {
printTest("shouldParseLockTable()");
String content = "LOCK TABLE films IN SHARE MODE;";
assertScoreAndParse(content, null, 1);
}
@Test
public void shouldParsePrepareStatement() {
printTest("shouldParsePrepareStatement()");
String content = "PREPARE fooplan (int, text, bool, numeric) AS INSERT INTO foo VALUES($1, $2, $3, $4);";
assertScoreAndParse(content, null, 1);
}
@Test
public void shouldParseDropDomain() {
printTest("shouldParseDropDomain()");
String content = "DROP DOMAIN IF EXISTS domain_name CASCADE;";
assertScoreAndParse(content, null, 1);
}
@Test
public void shouldParseDropTableMultiple() {
printTest("shouldParseDropDomain()");
String content = "DROP TABLE films, distributors;";
assertScoreAndParse(content, null, 2);
}
@Test
public void shouldParseGrantOnTable() {
printTest("shouldParseGrantOnTable()");
String content = "GRANT UPDATE, TRIGGER ON TABLE t TO anita,zhi;";
assertScoreAndParse(content, null, 1);
}
@Test
public void shouldParseGrantOnMultipleTables() {
printTest("shouldParseGrantOnMultipleTables()");
String content = "GRANT UPDATE, TRIGGER ON TABLE t1, t2, t3 TO anita,zhi;";
assertScoreAndParse(content, null, 3);
}
@Test
public void shouldParseGrantExecuteOnFunction() {
printTest("shouldParseGrantExecuteOnFunction()");
String content = "GRANT EXECUTE ON FUNCTION divideByTwo(numerator int, IN demoninator int) TO george;";
assertScoreAndParse(content, null, 1);
AstNode childNode = rootNode.getChildren().get(0);
assertTrue(hasMixinType(childNode, TYPE_GRANT_ON_FUNCTION_STATEMENT));
}
@Test
public void shouldParseGrantExecuteAndUpdateOnMultipleFunctions() {
printTest("shouldParseGrantExecuteOnMultipleFunctions()");
String content = "GRANT EXECUTE, UPDATE ON FUNCTION cos(), sin(b double precision) TO peter;";
assertScoreAndParse(content, null, 2);
AstNode childNode = rootNode.getChild(0);
assertTrue(hasMixinType(childNode, TYPE_GRANT_ON_FUNCTION_STATEMENT));
assertEquals(3, childNode.getChildCount());
childNode = rootNode.getChild(1);
assertEquals(4, childNode.getChildCount());
}
@Test
public void shouldParsePostgresStatements_1() {
printTest("shouldParsePostgresStatements_1()");
String content = getFileContent(DDL_FILE_PATH + "postgres_test_statements_1.ddl");
assertScoreAndParse(content, "postgres_test_statements_1.ddl", 82);
}
@Test
public void shouldParsePostgresStatements_2() {
printTest("shouldParsePostgresStatements_2()");
String content = getFileContent(DDL_FILE_PATH + "postgres_test_statements_2.ddl");
assertScoreAndParse(content, "postgres_test_statements_2.ddl", 101);
}
@Test
public void shouldParsePostgresStatements_3() {
printTest("shouldParsePostgresStatements_3()");
String content = getFileContent(DDL_FILE_PATH + "postgres_test_statements_3.ddl");
assertScoreAndParse(content, "postgres_test_statements_3.ddl", 143);
}
@Test
public void shouldParsePostgresStatements_4() {
printTest("shouldParsePostgresStatements_4()");
String content = getFileContent(DDL_FILE_PATH + "postgres_test_statements_4.ddl");
assertScoreAndParse(content, "postgres_test_statements_4.ddl", 34);
}
}