I am trying to run the unCommonSql from the cCLan archive (version 1.1.1-1)
against mysql (version 3.23.49-2) but have run into a problem with the
tutorial from the docs.
The tutorial seems to have been made against postgress, so it is quite
likely this is just some difference between the two, but I am hoping
that somebody knows unCommonSql and/or MySQL well enough to help me
out.
The problem hits when trying to create the first table. I get an error
saying
All parts of a PRIMARY KEY must be NOT NULL; If you need NULL
in a key, use UNIQUE instead
I can think of three solutions:
- convince unCommonSql to say "UNIQUE KEY(EMPLID)" rather than
"PRIMARY KEY(EMPLID)"
- have unCommonSql add "NOT NULL" to the EMPLID field
- convince MySQL to do any of the above by default
Solution 2 seems right, but I do not really know how to add that to
the view object definition.
------------------------+-----------------------------------------------------
Christian Lynbech |
------------------------+-----------------------------------------------------
Hit the philistines three times over the head with the Elisp reference manual.
- ·······@hal.com (Michael A. Petonic)
Christian Lynbech <·······@get2net.dk> writes:
> The tutorial seems to have been made against postgress, so it is quite
> likely this is just some difference between the two, but I am hoping
> that somebody knows unCommonSql and/or MySQL well enough to help me
> out.
>
> The problem hits when trying to create the first table. I get an error
> saying
>
> All parts of a PRIMARY KEY must be NOT NULL; If you need NULL
> in a key, use UNIQUE instead
>
> I can think of three solutions:
>
> - convince unCommonSql to say "UNIQUE KEY(EMPLID)" rather than
> "PRIMARY KEY(EMPLID)"
You can change this with by defining a create-pkey-constraint method
specializing on the mysql database class.
> - have unCommonSql add "NOT NULL" to the EMPLID field
Assuming mysql supports per field constraint specs you should be able
to define a method specilizing on the mysql database class for the
generic function sql-sys::%install-class which will append the
constraint to the schemadef for the slots.
The following is untested:
(defmethod %install-class ((self view-metaclass) (database mysql-database)
&aux schemadef)
(dolist (slotdef (class-slots self))
(when-bind (res (mysql-generate-db-definition slotdef))
(push res schemadef)))
(unless schemadef
(error "Class ~s has no :base slots" self))
(create-table (sql-expression :table (view-table self)) schemadef
:database database
:constraints (create-pkey-constraint self database))
(push self (database-view-classes database))
t)
(defun mysql-generate-db-definition (slotdef)
(cond
((eq (view-class-slot-db-kind slotdef) :base)
(list (sql-expression :attribute (view-class-slot-column slotdef))
(slot-definition-type slotdef)))
((eq (view-class-slot-db-kind slotdef) :key)
(list (sql-expression :attribute (view-class-slot-column slotdef))
(slot-definition-type slotdef)
'not-null))
(t nil)))
> - convince MySQL to do any of the above by default
I was thinking that making the generate-db-definition protocol
specializable per database backend is the best solution. it would
make it easier for you to add the not-null constraint to primarykey
fields, and it would also let us add more information to the schema
versioning tables, which use generate-db-definition to populate their
infoze.
--
Craig Brozefsky <·····@red-bean.com>
http://www.red-bean.com/~craig
Ask me about Common Lisp Enterprise Eggplants at Red Bean!
Craig Brozefsky <·····@red-bean.com> writes:
> > I can think of three solutions:
> >
> > - convince unCommonSql to say "UNIQUE KEY(EMPLID)" rather than
> > "PRIMARY KEY(EMPLID)"
>
> You can change this with by defining a create-pkey-constraint method
I went ahead and added this to dbms/mysql-sql.lisp
I do not use mysql at all, so you may need to modify it.
> > - have unCommonSql add "NOT NULL" to the EMPLID field
You can now add a :db-constraints argument to your slot definitions to
add column constraints, not-null and primary-key are the only
supported constraints. There is a protocol for adding universal
constraints as well as DB driver specific constraints now.
for instance
(def-view-class person (sql::metadata-entity)
((person-oid :type integer
:db-kind :key
:reader oid
:initarg :oid
:initarg :id
:documentation "person OID")
(userid :type string
:initarg :userid
:reader userid
:db-constraints not-null
:documentation "unique person userid")
....
Is a fragment from an application using USQL. It will add a NOT NULL
to the column definition when creating that table.
> > - convince MySQL to do any of the above by default
>
> I was thinking that making the generate-db-definition protocol
> specializable per database backend is the best solution. it would
> make it easier for you to add the not-null constraint to primarykey
> fields, and it would also let us add more information to the schema
> versioning tables, which use generate-db-definition to populate their
> infoze.
Well, all of that is in place now, just needs a mysql user to vet it
and get the syntax for the table constraint right.
--
Craig Brozefsky <·····@red-bean.com>
http://www.red-bean.com/~craig
Ask me about Common Lisp Enterprise Eggplants at Red Bean!