Thread: Converting Postgres SQL constraint logic to PHP?
Hi. I was hoping this list might be able to offer some help/advice/suggestions/opinions about feasibility for something I want to implement, namely converting Postgres constraints into PHP logic. Here's the context and explanation:
I work on a PHP web app using Postgres. When possible, we try to build as much logic as possible directly into the DB. The app already automatically reads NOT NULL and foreign key constraints from the DB, and enforces them through the UI thus preventing people from getting ugly database errors. It doesn't do that with check constraints and table constraints though, which means we either end up duplicating the constraint logic in PHP, or else sometimes get lazy/expedient and only put the constraint into PHP. Obviously neither of those is ideal.
What would be ideal is for the app to handle those constraints automatically. It looks like I can pull them out (as SQL) from information_schema.check_constraints, with the remaining issue being how to make them usable in PHP.
I'm wondering if anyone has done this already, or if there is some kind of library available for this purpose?
If not, and absent any better suggestions, I'm looking at trying to parse/search/replace. This might well be imperfect, and error-prone. But if I can get something that at least works in a lot of cases, that would help a lot. So as a simple example, converting from
((sat3 >= 0) AND (sat3 <= 5))
to the needed format:
(($rec['sat3'] >=0) and ($rec['sat3'] <= 5))
seems like it would be relatively straightforward, since the structure of the logic is neatly parallel between SQL and PHP. Each of these below, and many others I could pull, all have additional complications beyond that though:
- (((incentive_sent_date IS NULL) OR (incentive_sent_date >= residence_date)))
- (((application_date IS NOT NULL) OR (employment_date IS NOT NULL)))
- (xor((assessed_by IS NULL), (assessor_organization IS NULL)))
- (((release_to IS NULL) OR ((consent_type_code)::text = ANY ((ARRAY['ROI'::character varying, 'MEDIA'::character varying])::text[]))))
So anyway, there's my issue. Any thoughts/comments/suggestions welcome. Thanks in advance!
Ken
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
> On Jun 10, 2016, at 1:01 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote: > > Hi. I was hoping this list might be able to offer some help/advice/suggestions/opinions about feasibility for somethingI want to implement, namely converting Postgres constraints into PHP logic. Here's the context and explanation: > > I work on a PHP web app using Postgres. When possible, we try to build as much logic as possible directly into the DB. The app already automatically reads NOT NULL and foreign key constraints from the DB, and enforces them through the UIthus preventing people from getting ugly database errors. It doesn't do that with check constraints and table constraintsthough, which means we either end up duplicating the constraint logic in PHP, or else sometimes get lazy/expedientand only put the constraint into PHP. Obviously neither of those is ideal. > > What would be ideal is for the app to handle those constraints automatically. It looks like I can pull them out (as SQL)from information_schema.check_constraints, with the remaining issue being how to make them usable in PHP. > > I'm wondering if anyone has done this already, or if there is some kind of library available for this purpose? > > If not, and absent any better suggestions, You could name the check constraints, catch the errors and use a client-side mapping between constraint name and a friendlyerror message for display in the web interface. You could implement the checks in PHP in the database. https://public.commandprompt.com/projects/plphp/wiki You could look at one of the existing SQL parsers implemented in PHP, and use those to parse the constraint to a tree fromwhich you could easily pull PHP. I'd go for that first one, if possible. Robust, and zero overhead in the happy path. > I'm looking at trying to parse/search/replace. This might well be imperfect, and error-prone. But if I can get somethingthat at least works in a lot of cases, that would help a lot. So as a simple example, converting from Cheers, Steve
On Fri, 2016-06-10 at 13:01 -0700, Ken Tanzer wrote: > Hi. I was hoping this list might be able to offer some > help/advice/suggestions/opinions about feasibility for something I > want to implement, namely converting Postgres constraints into PHP > logic. Here's the context and explanation: > > I work on a PHP web app using Postgres. When possible, we try to > build as much logic as possible directly into the DB. The app > already automatically reads NOT NULL and foreign key constraints from > the DB, and enforces them through the UI thus preventing people from > getting ugly database errors. It doesn't do that with check > constraints and table constraints though, which means we either end > up duplicating the constraint logic in PHP, or else sometimes get > lazy/expedient and only put the constraint into PHP. Obviously > neither of those is ideal. > > What would be ideal is for the app to handle those constraints > automatically. It looks like I can pull them out (as SQL) from > information_schema.check_constraints, with the remaining issue being > how to make them usable in PHP. > > I'm wondering if anyone has done this already, or if there is some > kind of library available for this purpose? > > If not, and absent any better suggestions, I'm looking at trying to > parse/search/replace. This might well be imperfect, and error- > prone. But if I can get something that at least works in a lot of > cases, that would help a lot. So as a simple example, converting > from > > ((sat3 >= 0) AND (sat3 <= 5)) > > to the needed format: > > (($rec['sat3'] >=0) and ($rec['sat3'] <= 5)) > > seems like it would be relatively straightforward, since the > structure of the logic is neatly parallel between SQL and PHP. Each > of these below, and many others I could pull, all have additional > complications beyond that though: > (((incentive_sent_date IS NULL) OR (incentive_sent_date >= > residence_date))) > (((application_date IS NOT NULL) OR (employment_date IS NOT NULL))) > (xor((assessed_by IS NULL), (assessor_organization IS NULL))) > (((release_to IS NULL) OR ((consent_type_code)::text = ANY > ((ARRAY['ROI'::character varying, 'MEDIA'::character > varying])::text[])))) > So anyway, there's my issue. Any thoughts/comments/suggestions > welcome. Thanks in advance! > > Ken > > Hi Ken, Would this be static or dynamic? For example, if you altered a column to become defined as NOT NULL, say, when you build the form used to maintain that table you'd like to have a "required" attribute against the input field for that column. So if it were dynamic you'd have to access the database information_schema each time you wanted to send the HTML down the wire as well as when carrying out validation. Also, views are updateable and you'd have to check the tables and columns making up the view. I've never used pg_meta_data but it might be helpful. Another thought would be to write a program that scanned all the tables in your database and wrote out a table in your application's schema where the table and column constraints were synthesised in a way suitable for use in PHP code. Cheers, Rob
On Fri, Jun 10, 2016 at 1:47 PM, Steve Atkins <steve@blighty.com> wrote:
You could name the check constraints, catch the errors and use a client-side mapping between constraint name and a friendly error message for display in the web interface.
This seems plausible, but not ideal. I could get over the aesthetics of sequence gaps, but don't like throwing lots of database errors on principle, and also for silting up my logs. Seems better to catch these before submitting.
You could implement the checks in PHP in the database. https://public.commandprompt.com/projects/plphp/wiki
I'm aiming as much as possible for this to "just work" with standard Postgres, and this approach gets away from that. Plus people would have to know SQL AND PHP in order to create tables.
You could look at one of the existing SQL parsers implemented in PHP, and use those to parse the constraint to a tree from which you could easily pull PHP.
This one sounds most promising! I took a quick Google, and it looks like there are lots of them, and a heavy Mysql focus. Anyone have experience or suggestions about which of these might be best, and particularly for Postgres?
Thanks for the suggestions!
Ken
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Fri, Jun 10, 2016 at 1:54 PM, rob stone <floriparob@gmail.com> wrote:
Hi Ken,
Would this be static or dynamic?
For example, if you altered a column to become defined as NOT NULL,
say, when you build the form used to maintain that table you'd like to
have a "required" attribute against the input field for that column. So
if it were dynamic you'd have to access the database information_schema
each time you wanted to send the HTML down the wire as well as when
carrying out validation.
Hi Rob. I guess this is currently static, in that whenever you change the tables you have to run an update process that rereads the tables and stores the configuration information.
Also, views are updateable and you'd have to check the tables and
columns making up the view.
Yeah. We're not using any updateable views at the moment, so for now I'd be happy for this to work with tables. One step at a time! :)
I've never used pg_meta_data but it might be helpful.
Another thought would be to write a program that scanned all the tables
in your database and wrote out a table in your application's schema
where the table and column constraints were synthesised in a way
suitable for use in PHP code.
I'm not sure I'm following this one. Wouldn't such a program have to parse the SQL and convert it to PHP anyway?
Cheers,
Rob
Thanks!
Ken
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Fri, Jun 10, 2016 at 2:23 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
-- On Fri, Jun 10, 2016 at 1:47 PM, Steve Atkins <steve@blighty.com> wrote:
You could look at one of the existing SQL parsers implemented in PHP, and use those to parse the constraint to a tree from which you could easily pull PHP.This one sounds most promising! I took a quick Google, and it looks like there are lots of them, and a heavy Mysql focus. Anyone have experience or suggestions about which of these might be best, and particularly for Postgres?
Hi. Just wanted to follow up one more time on this. Best parsers in PHP, especially for Postgres? Anyone? Thanks!
Ken
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.