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!
  • What does that even mean, "if the row is less than the minimal path" how can that occur? – Evan Carroll Jun 23 '17 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. Jun 23 '17 at 4:11
  • What is "the wrong path" how do you have a "less than the minimal path" can you give us an example? – Evan Carroll Jun 23 '17 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. Jun 23 '17 at 4:41
  • 1
    @EvanCarroll no problem.. please see my latest update – Patrick B. Jun 23 '17 at 4:45
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, privacy policy and cookie policy

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