2

There is a table on my database that stores a path for video files used by my website. Those files are stored in a SSD disk.

path character varying(255)
id serial

The path will always be like this:

/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)

Here are some examples of valid paths:

/files/client/743052/attachment/2123598/main
/files/client/743052/attachment/2123598/thumbnail
/files/client/1475296/user/3541234/main
/files/client/1475296/user/3541234/thumbnail

Question: How can I create a Trigger that checks the value WHEN INSERTED and, if the row is less than the minimal path (/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)) then raise an exception?

I'm using PostgreSQL 9.1.

UPDATE:

Some more examples here:

/files/client/45345/user/3542341234/main -- PASS
/files/client/45345/user/3542341234/thumbnail -- PASS
/files/client/253623/attachment/35334/main -- PASS
/files/client/253623/attachment/35334/thumbnail -- PASS
/files/client/45312341245/users/12545/main -- WRONG!
/files/client/45312341245/users/12545/thumbnail -- WRONG!
/files/client/45345/attachment/1223545/mains -- WRONG!
/files/client/45345/attachment/1223545/thumbnails -- WRONG!
7
  • What does that even mean, "if the row is less than the minimal path" how can that occur? Commented Jun 23, 2017 at 4:06
  • If for some reason the application inserts wrong path, I want the DB to be able to "block" it. Simple like that
    – Patrick B.
    Commented Jun 23, 2017 at 4:11
  • What is "the wrong path" how do you have a "less than the minimal path" can you give us an example? Commented Jun 23, 2017 at 4:36
  • 1
    @EvanCarroll I've already given it to u: Path should be always /files/client/\d+/(attachment|user)/\d+/(main|thumbnail). Anything different from that is wrong.
    – Patrick B.
    Commented Jun 23, 2017 at 4:41
  • 1
    @EvanCarroll no problem.. please see my latest update
    – Patrick B.
    Commented Jun 23, 2017 at 4:45

1 Answer 1

2

You can simply use a check constraint however I would personally restructure my schema. Essentially what you seem to be doing is serializing a lot of data into a path, and then you want to be sure it's correct. That's kind of yuck, from my perspective.

CREATE TABLE foo (
  id    serial,
  path  text
    CHECK ( path ~ '/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)' )
);
INSERT INTO foo(path) VALUES 
  ('/files/client/743052/attachment/2123598/main'),
  ('/files/client/743052/attachment/2123598/thumbnail'),
  ('/files/client/1475296/user/3541234/main'),
  ('/files/client/1475296/user/3541234/thumbnail');

INSERT INTO foo(path) VALUES ('/STUD/EVAN/beefcake_hotstuff.jpg');
ERROR:  new row for relation "foo" violates check constraint "foo_path_check"
DETAIL:  Failing row contains (6, /STUD/EVAN/beefcake_hotstuff.jpg).

I would probably go the opposite direction and normalize to some degree.

Normalizing

Normalizing would look something like this,

CREATE SCHEMA aws;

CREATE TABLE aws.client (
  client_id serial PRIMARY KEY
);
CREATE TABLE aws.attachment (
  client_id       int REFERENCES client,
  attachment_id   int PRIMARY KEY,
  name            text
);
CREATE TABLE aws.user (
  client_id       int REFERENCES client,
  user_id         int PRIMARY KEY,
  name            text
);

SELECT '/files/client' || client_id || '/attachment' || attachment_id || '/' || a.name
FROM aws.attachment AS a
JOIN aws.client AS c USING (client_id);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.