> 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