Support for check constraints
Thomas Kellerer
I was thinking about a simple yet good way to support check constraints in PA.
After checking the syntax and support across different DBMS I noticed that the basic stuff is really pretty standard.
I think a simple yet pretty powerful way to support this, would be to define them on table level (as a check constraint could possibly involve more than one column). A check constraint would simply consist of two input fields: the name of the constraint and the expression (e.g. salary > 0). The UI would need to allow multiple entries and the DDL Generators need to be adjusted in order to include the constraints after the column definitions.
I have seen pretty sophisticated "designers" for this kind of thing, but after all any "guided" selection will always have some kind of limitation which a simple text field does not suffer from :)
If there is a need to support multiple DBMS (which the "constraint designers" try to support by having a very high-level definition) I think a third property that selects the DBMS (or more precisely the DDL Generator) for which the specific constraint applies should take care of most of cases.
My proposal for managing and storing them is something similar to the index handling.
The table popup menu would have two new items: "New Check Constraint", and"Check Constraint Properties" (as a sub-menu, just like the indexes).
Each of them would open a dialog with the above mentioned input fields (a JTextField for the name and a JTextArea for the expression)
In the XML this could be stored in a new folder (name="Check Constraints" or something similar)
The biggest problem will be the model compare I guess.
What do you think about this?
I can do the implementation as having check constraints inside PA would be a major improvement for us.
I will most probably need help for the XML reading/saving part though
Regards
Thomas
Thomas O'Brien
>
> I was thinking about a simple yet good way to support check constraints in PA.
>
working on a feature similar to this on our enterprise edition.
> I think a simple yet pretty powerful way to support this, would be to define them on table level (as a check constraint could possibly involve more than one column). A check constraint would simply consist of two input fields: the name of the constraint and the expression (e.g. salary > 0). The UI would need to allow multiple entries and the DDL Generators need to be adjusted in order to include the constraints after the column definitions.
>
edition yet but we have added the concept of domains and user defined
data types where you can also add constraints. If you get a client
from an enterprise server installation you will see a new 'enterprise'
menu which includes an editor for domains and user defined data types.
The user defined data types allows you to create or modify any basic
data types and specify platform specific options. Allowing new data
types also gives users the much desired feature of adding in data
types specific to the platform they are working on without making the
tool platform specific. The domains are extensions on data types that
allow you to define column types that are specific to your work
environment. The domains can override data type settings to set their
own defaults as well. For both of these object types you can (soon)
specify check constraints and enumerations that then get added to the
columns. We currently have constraints working for postgreSQL, Oracle
and SQL Server 2005 and are working on MySQL, DB2 and SQL Server 2008
for the DDL Generators. Unfortunately, the latest build on our website
does not have the check constraints enabled as they are not complete.
The idea to have check constraints on columns and tables would
actually be a nice extension on the constraints of a column. If you
looked at a recent build of SQL Power Architect you would see there
are now check boxes beside some of the fields in the column editor.
This is because on the enterprise edition you can choose to override
the data type information or un-check the box beside the field to keep
the values defined in the domain or type. To introduce constraints
into the community edition of SQL Power Architect we would need to do
a similar overriding field on the column to override the domain or
types constraints and the community edition would always be overriding
the empty constraint of the data type.
> I have seen pretty sophisticated "designers" for this kind of thing, but after all any "guided" selection will always have some kind of limitation which a simple text field does not suffer from :)
>
> If there is a need to support multiple DBMS (which the "constraint designers" try to support by having a very high-level definition) I think a third property that selects the DBMS (or more precisely the DDL Generator) for which the specific constraint applies should take care of most of cases.
>
just one text area that a user can enter free-form text into. They can
also override any check constraint on a specific platform to make the
constraint work when forward engineering. Having the constraints in a
free-form text field also allows for combining the constraints in any
way the user wants so we probably don't need to get more complicated
than that.
> In the XML this could be stored in a new folder (name="Check Constraints" or something similar)
>
> The biggest problem will be the model compare I guess.
>
object directly and doesn't need to be a new folder in the XML.
Hopefully comparing will also be simple enough to do.
>
> What do you think about this?
have any problems or questions just post them here or in a different
thread. Terry is currently working on the constraints in the
enterprise server and will be working on them again on Tuesday.
Thomas
Thomas Kellerer
Thomas O'Brien, 17.06.2010 00:10:
> It's interesting that you bring this up now because we are actually
> working on a feature similar to this on our enterprise edition.
>
> I don't know if you've tried out the preview release of the enterprise
> edition yet but we have added the concept of domains and user defined
> data types where you can also add constraints.
A domain is not the same thing and can especially not capture a constraint like (column1 > column2).
Additionally I'm not very happy that such an important feature is only available in the enterprise edition. Check constrains are an important part of the model.
As we are using the "community" version, any feature in the enterprise version is not available for us.
> If you looked at a recent build of SQL Power Architect you would see
> there are now check boxes beside some of the fields in the column
> editor.
Yes I noticed that. Very nice, especially for data types that don't need scale or precision.
> This is because on the enterprise edition you can choose to override
> the data type information or un-check the box beside the field to keep
> the values defined in the domain or type. To introduce constraints
> into the community edition of SQL Power Architect we would need to do
> a similar overriding field on the column to override the domain or
> types constraints and the community edition would always be overriding
> the empty constraint of the data type.
Hmm, I don't think it would need to override the domain check.
Just think of a domain "positive_integer", you might want a check constraint (column1 > column2).
One shouldn't think of a check constraint as a column level but a table level constraint.
> If the constraint stays as a text field it can be a property of the
> object directly and doesn't need to be a new folder in the XML.
> Hopefully comparing will also be simple enough to do.
As I said: there could be multiple constraints on the table (col1 > col2), (col4 between 1 and 100) and so on
So it will at least be a collection attribute of the table.
Regards
Thomas
Thomas O'Brien
> A domain is not the same thing and can especially not capture a constraint like (column1 > column2).
> Additionally I'm not very happy that such an important feature is only available in the enterprise edition. Check constrains are an important part of the model.
>
terms of constraints in the community edition versus constraints in
the enterprise edition. We would like to put constraints into both the
table and the column editors for both editions. For constraints at the
table level you would be able to do constraints like column1 > column2
and more.
What I was trying to get at in my last post was some of the
considerations that need to be included when developing the
constraints into the columns on the community edition.
> Hmm, I don't think it would need to override the domain check.
> Just think of a domain "positive_integer", you might want a check constraint (column1 > column2).
similar to how it currently works for precision and scale between
domains and columns. For constraints on a column you would then be
able to modify or append to the constraint on the domain for that
single column. However, you could just put the two constraints
together in the constraint in the end and anyone who wants to change
the constraint on the domain would have to go back to the domain
editor. I know some people would rather have the constraint on the
domains not editable at the column level so you can take that
direction to start if you want.
While we are discussing how the constraints work with columns we do
still want constraints on the table level. The constraints on the
table level would not need to override anything and would just be on
the table itself. The constraint on the table level will also simpler
as you don't have to consider the enterprise edition in the change as
much.
> As I said: there could be multiple constraints on the table (col1 > col2), (col4 between 1 and 100) and so on
> So it will at least be a collection attribute of the table.
just a text field so the suggestion was just to keep the UI consistent
for the column editors. However, just because the current constraint
editor is a text field doesn't mean we shouldn't change it, we should
just keep them consistent. If we choose to go with a table showing a
collection of check constraints on the column editor we will just need
to update the domain and type editors. Now before I continue on I need
to admit that I haven't written many constraints so there may be
things that I have the wrong idea about. That being said what benefit
do you get in terms of constraints on a table or in terms of the
editor if you place two constraints on a table (col1 > col2), (col4
between 1 and 100) versus one constraint that ands them together (col1
> col2 and col4 between 1 and 100)?
Thomas
Thomas Kellerer
> Sorry, I must not have been clear about what we would like to do in
> terms of constraints in the community edition versus constraints in
> the enterprise edition. We would like to put constraints into both the
> table and the column editors for both editions.
Great! Thanks for the clarification.
> In the current UI design of the domain editor the constraint editor is
> just a text field so the suggestion was just to keep the UI consistent
> for the column editors.
With "just a textfield" I miss the possibility to give the constraint a name.
> That being said what benefit
> do you get in terms of constraints on a table or in terms of the
> editor if you place two constraints on a table (col1> col2), (col4
> between 1 and 100) versus one constraint that ands them together (col1
> col2 and col4 between 1 and 100)?
Having different names for the constraint, so that the error message would immediately indicate which constraint has been violated.
Naming constraints is very useful when you need to find an error in logfiles. As you usually don't see the actual values in there the name of the constraint can be of great help. I even know people who give every NOT NULL constraint a name (which is very handy sometimes...)
Regards
Thomas
Terry
> With "just a textfield" I miss the possibility to give the constraint a name.
named check constraints for tables and columns, it would be useful to
add this feature in.
The UI would be changed such that the column edit panel and table edit
panel each have a JTable with 2 columns, the name of the constraint
and the check constraint condition. The check constraints for columns
would be appended on any already defined check constraints on its
domains. We could have the domain's constraints prepended and
unselectable on the JTable, and any additional column constraints
would go after these entries.
Similarly, to keep with consistent UI design, this JTable component
would be placed on the domains and data type editor panels for the
Enterprise Edition, replacing the text field.
If that's ok with you, I'll make changes to the enterprise feature
constraints accordingly.
I have been working on check constraints and enumerations most
recently, and I will be getting back on this work next week. There are
some changes underway to the DDL generators to support check
constraints and enumerations.
Reverse engineering and comparing data models on tables and columns
that have constraints may be a bit tricky though. From my initial
research, it may be platform specific to retrieve these constraints,
and parsing it may be problematic as well. For example, Oracle has an
all_constraints table which has to be queried to get these
constraints. However, the search_condition field that holds the check
constraint is a LONG value, which is actually a character string.
However, it is apparently unusable in this type and is difficult to
convert the value to something that is usabled. I have not done any
additional research into retrieve constraints for other platforms yet,
they may just work in the same way. It is strange that this
information is not contained within the column metadata.
Regards,
Terry
Thomas Kellerer
> Reverse engineering and comparing data models on tables and columns
> that have constraints may be a bit tricky though.
Been there, done that ;)
Check out my SQL Workbench/J source code.
I have implemented the retrieval for check constraints (table and column level) for all major DBMS.
The interesting classes are workbench.db.AbstractConstraintReader and then descendant classes for the specific databases.
The source code can be downloaded from my homepage (http://www.sql-workbench.net) or through svn (details on the homepage)
Feel free to either re-use the source code, or to simply grab the necessary SQL statements from the classes.
Regards
Thomas
Terry
> Check out my SQL Workbench/J source code.
>
> I have implemented the retrieval for check constraints (table and column level) for all major DBMS.
> The interesting classes are workbench.db.AbstractConstraintReader and then descendant classes for the specific databases.
>
> The source code can be downloaded from my homepage (http://www.sql-workbench.net) or through svn (details on the homepage)
>
> Feel free to either re-use the source code, or to simply grab the necessary SQL statements from the classes.
look through your implementation once I get back on check constraint
work next week.
Regards,
Terry
Terry
I have committed some changes in regards to check constraints.
A simple SQLCheckConstraint class has been created which takes 2
properties, name and check constraint condition (a string). The check
constraint condition should be able to handle variables (currently
only ${this} exists - defines the column the check constraint is
applied to). For example, a ${this} = 'bar' constraint applied on a
column named 'foo' should resolve to foo = 'bar'. The SQLColumn
implements SPVariableResolver to achieve this functionality.
There is now a SQLCheckConstraintContainer interface which defines an
SPObject that can "hold" SQLCheckConstraint objects. A
SQLCheckConstraintContainer can either have children of type
SQLCheckConstraint, or have SQLCheckConstraintContainer as
descendants. This container has a few methods:
addCheckConstraint(SQLCheckConstraint[, int]),
removeCheckConstraint(SQLCheckConstraint), and getCheckConstraints().
The addCheckConstraint method either adds a check constraint directly
to a container if it is an allowed child type, or to its descendant
containers, where the optional index is in respect to the direct
container parent of the check constraint object. Similarly, the
removeCheckConstraint method removes a check constraint from its
parent if it exists in the container tree. Lastly, the
getCheckConstraints() method retrieves the List of SQLCheckConstraint
objects from the container tree. The order of this list should be: the
list of child SQLCheckConstraint objects first, followed by the list
of SQLCheckConstraint objects that are contained within
subcontainers.
Currently, I have defined SQLColumn and SQLTypePhysicalProperties as
SQLCheckConstraintContainers. The UserDefinedSQLType class does not
implement the container interface because it needs to know which
database platform to perform operations on, and the container
interface has no specification for multiple platform support. There
are no column level check constraints implemented yet, so there are no
children of type SQLCheckConstraint in SQLColumn. The 3 methods from
the container interface basically propagates down to
UserDefinedSQLType, and finally down to SQLTypePhysicalProperties.
With respect to the UI, there are CheckConstraintTable,
CheckConstraintTableModelListener, AddCheckConstraintTableRowAction,
and RemoveSelectedTableRowsAction classes that deal with operations on
a SQLCheckConstraintContainer. When table and column level check
constraints are implemented, these components should be able to be
reused without loss of generality. If you would like to see how it
works, SQL Power Architect Enterprise Edition Preview 5 has this
implemented in the Domains & Data Types managers.
DDL generation of check constraints is mostly there as well. Some more
modifications to the UI may be warranted to ensure uniqueness of check
constraint names across all columns and tables. For example, in
Oracle, check constraint names between 2 different columns in the same
table cannot be the same. Additionally, check constraint names across
2 different tables cannot be the same.
For now, reverse engineering and comparing data models for check
constraints is out of scope for our development for the 1.0.0 final
release. However, it would be very useful to do in the future.
I hope this isn't too confusing as my explanation is a bit wordy.
Please do take a look at my implementation, and let me know if this is
enough to allow for table and column check constraints to be
implemented. Thanks!
Regards,
Terry
Thomas Kellerer
thanks a lot for the long explanation.
> With respect to the UI, there are CheckConstraintTable,
> CheckConstraintTableModelListener, AddCheckConstraintTableRowAction,
> and RemoveSelectedTableRowsAction classes that deal with operations on
> a SQLCheckConstraintContainer. When table and column level check
> constraints are implemented, these components should be able to be
> reused without loss of generality. If you would like to see how it
> works, SQL Power Architect Enterprise Edition Preview 5 has this
> implemented in the Domains& Data Types managers.
Do I understand this correctly, that the UI and code for supporting check constraints is there, but not available in the community edition?
Regards
Thomas
Terry
The check constraint code is all there to be implemented for the
Community Edition. This code is being used for domain and data type
level check constraints in the Enterprise Edition, but table and
column level check constraints have not been implemented yet in the
Community Edition. We would like them to be. If you are still open to
implementing these features, we will gladly accept your contribution.
We would like to have these features in by the end of November, as it
is a missing desirable core feature. Would that be alright with you?
Thanks for helping us, your contributions are much appreciated.
Regards,
Terry
On Aug 16, 5:47 am, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote: