How to implement viewclass with foreign keys also used as keys

Question

Assume I have SQL table definitions like this

CREATE TABLE X (
   id    integer not null,
   value character varying,
   PRIMARY KEY (id)
);

CREATE TABLE Y (
  start   integer not null,
  end     integer not null,
  value   character vartying,
  PRIMARY KEY (start,end),
  FOREIGN KEY(start)
  REFERENCES X (id)
  ON DELETE CASCADE,
  FOREIGN KEY(end)
  REFERENCES X (id)
  ON DELETE CASCADE
);

The first table is straight forward

(clsql:def-view-class x ()
  ((id
    :db-kind :key
    :db-type integer
    :db-constraints :not-null
    :reader id)
   (value
    :initarg :value
    :initform nil
    :db-type (string 255)
    :reader value))
  (:base-table xes))

But I do not know how to do the second as I can either define db-kind :key or :join. Further I did not find any specifications concerning ON DELETE ...

Is it even possible to implement the given table combination using the clsql oop model and if yes how?

score 2 · Accepted Answer · answered Jul 10 '15 at 06:12

I think the biggest problem is the declaration of the composite primary key (i.e. PRIMARY KEY (start, end)). Setting up the join with a non-composite primary key constraint is straight-forward:

(clsql:def-view-class y ()
  ((start
    :db-kind :join
    :db-info (:join-class x
               :home_key y_start
               :foreign_key id
               :set nil)
    :db-type integer
    :db-constraints :primary-key
    :reader start)
  ((end
    :db-kind :base
    :db-type integer
    :db-constraints :not-null
    :reader start)
   (value
    :initarg :value
    :initform nil
    :db-type (string 255)
    :reader value))
  (:base-table yes))

In principle, one would want to setup the composite key as a class option, but this is currently not supported by CL-SQL's OODML. Neither is there support for expressing ON DELETE behavior.

If you need both of these, you should be able to do so by falling back to execute-commands.

How to implement viewclass with foreign keys also used as keys

1 Answers1