Unique keys on reference columns generate incorrect table definitions

[fisxoj]

Defining a class like so:

(defclass tweet ()
  ((user :col-type user :references user)
   (content :col-type :text))
  (:metaclass mito:dao-table-class)
  (:unique-keys user))

Causes a table definition with (mito:table-definition 'tweet) of

CREATE TABLE tweet (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    UNIQUE (user),
    UNIQUE (user_id)
)

where the UNIQUE (user) is invalid because the real column name is user_id. It seems like this is because mito.dao.table::expand-relational-keys expands they unique-keys slot on the table definition to be

CL-USER> (slot-value (find-class 'tweet) 'mito.class.table::unique-keys)
(USER USER-ID)

even though it looks like that function is trying to filter out ghost slots. Using sqlite, I get this <dbi-programming-error>:

DB Error: expressions prohibited in PRIMARY KEY and UNIQUE constraints (Code: ERROR)
   [Condition of type DBI.ERROR:<DBI-PROGRAMMING-ERROR>]

[fukamachi]

Cannot specify :references for a column whose :col-type is another table class.

Try this:

(defclass tweet ()
  ((user :col-type user)
   (content :col-type :text))
  (:metaclass mito:dao-table-class)
  (:unique-keys user))

or this, if you prefer :references:

(defclass tweet ()
  ((user-id :col-type :integer :references user)
   (content :col-type :text))
  (:metaclass mito:dao-table-class)
  (:unique-keys user-id))

cf. https://github.com/fukamachi/mito#relationship

[fisxoj]

I guess I'm confused why :references and :col-type can both do some of the same things. If you use :references, it doesn't seem like you can use the (mito:select-dao 'tweet (mito:includes 'user) ...) sort of query (I get an error about the missing user slot to store the retrieved user in) whereas :col-type does everything right (makes a user-id column and also does includes correctly.)

What is :references good for that :col-type can't do? I think I'm making mistakes using the library because the difference is unclear to me. Maybe that is the real issue.

Thanks for your help and suggestions!

[fisxoj]

Interesting. I feel like I got in the habit of specifying both because something unexpected happened if I didn't, but I can't remember what. It's probably not an issue any more.

Perhaps this issue should be that no error or warning happens when you specify both.

Or, which way is recommended? :col-type seems like it should be discouraged compared to :references, which is explicitly a relationship.

On Sat, Oct 22, 2016, 11:15 PM Eitaro Fukamachi [email protected] wrote:

Cannot specify :references for a column whose :col-type is another table class.

Try this:

(defclass tweet () ((user :col-type user) (content :col-type :text)) (:metaclass mito:dao-table-class) (:unique-keys user))

or this, if you prefer :references:

(defclass tweet () ((user-id :col-type :integer :references user) (content :col-type :text)) (:metaclass mito:dao-table-class) (:unique-keys user-id))

cf. https://github.com/fukamachi/mito#relationship

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/fukamachi/mito/issues/22#issuecomment-255567087, or mute the thread https://github.com/notifications/unsubscribe-auth/ABdb2mVHFcUPrHOJ8ZwxusQZDZUi78XJks5q2tE_gaJpZM4Kd6sj .

[svetlyak40wt]

I've rechecked the table definition and it seems work as expected now:

(mito:deftable user ()
  ((name :col-type (:varchar 64))
   (email :col-type (or (:varchar 128) :null))))

(mito:deftable tweet ()
  ((status :col-type :text)
   ;; This slot refers to USER class
   (user :col-type user :references user)))

CL-USER> (mito:table-definition (find-class 'tweet))
(#<SXQL-STATEMENT: CREATE TABLE tweet (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    status TEXT NOT NULL,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ
)>)

[fisxoj]

I'm not currently using mito anymore, so I don't have a real use case to check.

I'm still confused about what combination of :col-type and :references is considered correct, here. Each example above and the relationship section of the readme use different combinations of the two kwargs.

It appears that :col-type should be enough to reference another table, but maybe :reference exists to let you specify the foreign key, although I don't think you would ever need to if mito can infer that from the other table's definition.

Thanks for checking up on it, @svetlyak40wt, but I think the issue ended up being that I couldn't figure out how to use this feature based on the docs and API. I don't use mito anymore because there were a couple things like this that I couldn't figure out and didn't have sufficient documentation/examples. I wish I could be more helpful and offer to help with that, but I don't really have time, right now.

[Mr-Dispatch]

I recommend closing this, the difference between :references (just create a column for ID from another table) and :col-type table_type (same column, but added machinery for querying referenced objects) seems clear from the docs (maybe it wasn't like so in 2018).

Perhaps checking this & throwing an error "use one or the other, not both" would be best.