Let me consider the following simple slot definition (inside
SQL:DEF-VIEW-CLASS):
(date :initarg :date :accessor some-date
:db-kind :base :db-type "date")
My intention is to have db column (column type in database, actually a
PosgreSQL database) of type DATE, but this line above gives me (after
SQL:CREATE-VIEW-FROM-CLASS) column type CHARACTER VARYING.
In tutorial (usql-tutorial.txt) which coming with UncommonSQL I read:
:db-type -- A string which will be used as the type specifier for
this slots column definition in the database.
Had I made a mistake in description of a slot ?
If not, would you mind to point me in UncommonSQL sources I should see
to fix the situation ?
Using UncommonSQL from alpha.onshored.com, recent version
debian_version_1_1_4-1 I believe.
--
Vladimir Zolotykh
Vladimir Zolotykh <······@eurocom.od.ua> writes:
Sorry for the delayed response.
> Let me consider the following simple slot definition (inside
> SQL:DEF-VIEW-CLASS):
>
> (date :initarg :date :accessor some-date
> :db-kind :base :db-type "date")
> Had I made a mistake in description of a slot ?
No. That is a bug in usql's handling of the :db-type argument. There
are workarounds tho that I would reccomend using instead.
Define a mapping from the lisp type to teh column type by deifning a
method specializaing on the name of your type. Then give the slodt
definitiona :type argument.
(defmethod maisql-sys::database-get-type-specifier ((type (eql 'date))
args database)
(declare (ignore database args))
"DATE")
You will also want to define a read-sql-value method and a
database-output-sql and perhaps a coerce-to-ec-key-value if you want
to control the external (DB) representation of the type.
--
Craig Brozefsky <·····@red-bean.com>
http://www.red-bean.com/~craig
Ask me about Common Lisp Enterprise Eggplants at Red Bean!
Craig Brozefsky wrote:
| Define a mapping from the lisp type to teh column type by deifning a
| method specializaing on the name of your type. Then give the slodt
| definitiona :type argument.
|
Would not giving unexistent type to :TYPE be confusing ? Do you mean
that I should create my own type DATE (a class for example) ? I wasn't
going to do so. CL universal-time covers all my needs. In PostgreSQL database
this type naturally represented with DATE database type.
(defmethod maisql-sys::database-get-type-specifier ((type (eql 'date))
args database)
(declare (ignore database args))
"DATE")
| You will also want to define a read-sql-value method and a
| database-output-sql and perhaps a coerce-to-ec-key-value if you want
| to control the external (DB) representation of the type.
|
Could not :db-reader, :db-writer help here ? I'd rather guessed the
following sketch of doing this. Having all dates represented in Common
Lisp as universal time and deal with them with GET-UNIVERSAL-TIME,
(EN|DE)CODE-UNIVERSAL-TIME. All dates in database itself represented
with backend's DATE type. Somehow arrange conversion from PostgreSQL's
DATE type to CL universal time during read and vice versa during
write. No problem to make conversion itself. The question is how to
integrate it with with USQL naturally. When I become more familiar
with USQL I could guess it myself but can't so for now. Could you help
me?
--
Vladimir Zolotykh
Vladimir Zolotykh <······@eurocom.od.ua> writes:
> Craig Brozefsky wrote:
>
> | Define a mapping from the lisp type to teh column type by deifning a
> | method specializaing on the name of your type. Then give the slodt
> | definitiona :type argument.
> |
>
> Would not giving unexistent type to :TYPE be confusing ? Do you mean
> that I should create my own type DATE (a class for example) ? I wasn't
> going to do so. CL universal-time covers all my needs. In PostgreSQL database
> this type naturally represented with DATE database type.
In practice no it doesn't matter because PCL doesn't check the type.
> | You will also want to define a read-sql-value method and a
> | database-output-sql and perhaps a coerce-to-ec-key-value if you want
> | to control the external (DB) representation of the type.
> |
>
> Could not :db-reader, :db-writer help here ?
Yes, but why define those things for just one slot when you can define
a type and then not have to rewrite them whenever you use a slot
storing a date again? The :db-reader :db-writer and :db-type slots
are intended to specialize a particular slot's behavior, but I
anticipate you're going to want to store dates in mroe than just one
slot in your data model.
> following sketch of doing this. Having all dates represented in
> Common Lisp as universal time and deal with them with
> GET-UNIVERSAL-TIME, (EN|DE)CODE-UNIVERSAL-TIME. All dates in
> database itself represented with backend's DATE type. Somehow
> arrange conversion from PostgreSQL's DATE type to CL universal time
> during read and vice versa during write. No problem to make
> conversion itself. The question is how to integrate it with with
> USQL naturally. When I become more familiar with USQL I could guess
> it myself but can't so for now. Could you help me?
Or you can just use the existing cl-local-time-db packages which have
a datetime representing data type already, as well as durations (based
on Eriks localtime API) and all the machinery to move both of those
types in and out of databases. You can get that package from the same
cvs server as IMHO, or as a package wherever you got the deb.
--
Craig Brozefsky <·····@red-bean.com>
http://www.red-bean.com/~craig
Ask me about Common Lisp Enterprise Eggplants at Red Bean!
Craig Brozefsky wrote:
>
> No. That is a bug in usql's handling of the :db-type argument. There
> are workarounds tho that I would reccomend using instead.
>
> Define a mapping from the lisp type to teh column type by deifning a
> method specializaing on the name of your type. Then give the slodt
> definitiona :type argument.
>
> (defmethod maisql-sys::database-get-type-specifier ((type (eql 'date))
> args database)
> (declare (ignore database args))
> "DATE")
>
> You will also want to define a read-sql-value method and a
> database-output-sql and perhaps a coerce-to-ec-key-value if you want
> to control the external (DB) representation of the type.
I've done what you suggested for. At least in the way I could
understand that.
I defined slot the following way:
(date :initarg :date :accessor some-date :type date)
In package MAISQL-SYS defined the following methods (they don't do
actual work yet, my intention was just to see things work):
(defmethod database-get-type-specifier ((type (eql 'date))
args database)
(declare (ignore args database))
"DATE")
(defmethod read-sql-value (val (type (eql 'date)) database)
(declare (ignore database))
val)
(defmethod database-output-sql-as-type ((type (eql 'date)) val database)
(declare (ignore database))
val)
Then I defined my class with DEF-VIEW-CLASS and created database table
with
(sql:create-view-from-class 'my-view)
GENERATE-DB-DEFINITION seems returns right thing,
0: MAISQL-SYS::GENERATE-DB-DEFINITION returned
(#<MAISQL-SYS::SQL-IDENT-ATTRIBUTE DATE> DATE)
but that is all. It seems DATABASE-GET-TYPE-SPECIFIER didn't called at
all. And column type remained unchanged. I mean it is still of type
CHARACTER VARYING.
--
Vladimir Zolotykh
Vladimir Zolotykh <······@eurocom.od.ua> writes:
>
> (date :initarg :date :accessor some-date :type date)
>
> In package MAISQL-SYS defined the following methods (they don't do
> actual work yet, my intention was just to see things work):
>
> (defmethod database-get-type-specifier ((type (eql 'date))
> args database)
> (declare (ignore args database))
> "DATE")
Make sure the date symbol in this method and the others is in the same
package as your def-view-class.
--
Craig Brozefsky <·····@red-bean.com>
http://www.red-bean.com/~craig
Ask me about Common Lisp Enterprise Eggplants at Red Bean!
Craig Brozefsky wrote:
>
> Make sure the date symbol in this method and the others is in the same
> package as your def-view-class.
Of course this was the cause. Symbols were in different packages.
--
Vladimir Zolotykh