Examples of SQL Validation

SQL validation framework

Since 4.0 the JSQLParser framework includes a validaton framework.

The validation framework maps several types of validation, which are implementations of the interface ValidationCapability.

Currently the following implementations exist:

• ParseCapability: Checks if a statement can be parsed (always included with Validation#validate())
• FeatureSetValidation:
  • DatabaseType: Checks if a parsed statement is valid with respect to the supported database syntax
  • Version: Checks if a parsed statement is valid with respect to a specific database version.
  • FeaturesAllowed: Checks if a statement only uses syntax elements which are allowed.
• DatabaseMetaDataValidation: Validates meta data such as names of tables, views, columns for their existence or non-existence

SQL Validation DatabaseType/Version

Check if a parsed statement is valid to the database selected

String sql = "DROP INDEX IF EXISTS idx_tab2_id;";

// validate statement if it's valid for all given databases.
Validation validation = new Validation(Arrays.asList(DatabaseType.SQLSERVER, DatabaseType.MARIADB,
            DatabaseType.POSTGRESQL, DatabaseType.H2), sql);
List<ValidationError> errors = validation.validate();

Check if a parsed statement is valid to a specific database version.

// validate statement if it's valid for all given databases.
Validation validation = new Validation(Arrays.asList(PostgresqlVersion.V10), sql);
List<ValidationError> errors = validation.validate();

SQL Validation FeatuesAllowed

Validate against pre-defined FeaturesAllowed.DML set

// validate against pre-defined FeaturesAllowed.DML set
String sql = "CREATE TABLE tab1 (id NUMERIC(10), val VARCHAR(30))";
Validation validation = new Validation(Arrays.asList(FeaturesAllowed.DML), sql);
List<ValidationError> errors = validation.validate();
// only DML is allowed, got error for using a DDL statement
log.error (errors);

Validate against pre-defined FeaturesAllowed.SELECT set

String sql = "SELECT * FROM myview v JOIN secondview v2 ON v.id = v2.ref";
Validation validation = new Validation(Arrays.asList(FeaturesAllowed.SELECT), sql);
List<ValidationError> errors = validation.validate();
// no errors, select - statement is allowed
if (errors.isEmpty()) {
	// do something else with the parsed statements
	Statements statements = validation.getParsedStatements();
}

Validate against your own FeatureSet

 FeaturesAllowed exec = new FeaturesAllowed("EXECUTE", Feature.execute).unmodifyable();

Combine multiple pre-defined FeaturesAllowed set's

 FeaturesAllowed myAllowedFeatures = new FeaturesAllowed("myAllowedFeatures")
 			.add (FeaturesAllowed.DDL, FeaturesAllowed.DML);

SQL Validation DatabaseMetaDataValidation

Validates meta data such as names of tables, views, columns for their existence or non-existence

java.sql.Connection connection = ...;
String sql = "ALTER TABLE mytable ADD price numeric(10,5) not null";
Validation validation = new Validation(Arrays.asList(new JdbcDatabaseMetaDataCapability(connection, 	
    // NamesLookup: Databases handle names differently
	 NamesLookup.UPPERCASE)), sql);
List<ValidationError> errors = validation.validate();

// do something else with the parsed statements
Statements statements = validation.getParsedStatements();

// check for validation-errors
if (!errors.isEmpty()) {
	...
}

SQL Validation with muliple ValidationCapability's

java.sql.Connection connection = ...;
String sql = "ALTER TABLE mytable ADD price numeric(10,5) not null";
Validation validation = new Validation(Arrays.asList(
	DatabaseType.POSTGRESQL, 
	new JdbcDatabaseMetaDataCapability(connection,NamesLookup.LOWERCASE)), sql);
List<ValidationError> errors = validation.validate();    

Validate a Statement already parsed:

Statement statement = ...
ValidationContext context = Validation.createValidationContext(
	new FeatureConfiguration(), Arrays.asList(DatabaseType.POSTGRESQL));
Map<ValidationCapability, Set<ValidationException>> errors = 
	Validation.validate(statement, context);