It seems like a bug in the clsql, when someone wants to create and
manage tables with primary keys. Let us get a slightly modified example
from the CLSQL manual
(http://clsql.b9.com/manual/delete-instance-records.html) where I use
SBCL and SQLite (in a fresh session!):
* (asdf:operate 'asdf:load-op 'clsql-sqlite3)
...
* (in-package clsql)
...
* (connect '("files.db") :database-type :sqlite3 :if-exists :old)
...
* (start-sql-recording :type :both)
* (def-view-class tab ()
((a :initarg :a :type integer :db-kind :key)
(b :initarg :b :type string)))
#<CLSQL-SYS::STANDARD-DB-CLASS TAB>
* (create-view-from-class 'tab)
;; 2006-10-15 09:30:34,,0 files.db => CREATE TABLE TAB (A INT, B
VARCHAR(255))
;; 2006-10-15 09:30:34,,0 files.db <= T
There is no primary key definition here, therefore the next
delete-instance-records step truncate the table instead of the deleting
exactly selected records. However, if we would do the same (sic!)
commands in the same lisp session, we will get the right results:
* (drop-view-from-class 'tab)
;; 2006-10-15 09:30:39,,0 files.db => DROP TABLE TAB
;; 2006-10-15 09:30:39,,0 files.db <= T
* (def-view-class tab ()
((a :initarg :a :type integer :db-kind :key)
(b :initarg :b :type string)))
#<CLSQL-SYS::STANDARD-DB-CLASS TAB>
* (create-view-from-class 'tab)
;; 2006-10-15 09:45:12,,0 files.db => CREATE TABLE TAB (A INT, B
VARCHAR(255), CONSTRAINT TABPK PRIMARY KEY(A))
;; 2006-10-15 09:45:12,,0 files.db <= T
The primary key is created here on the table, and further behavior in
the example is also good. But if these steps repeat from scratch in the
new session, we will have the same strange (wrong?) behavior.
Apart from this one may have strange problems, when trying to define
primary keys via :db-constraints on a certain field (e.g., to make it
autoincrementable unique identifier). First time the primary key is
created on the specified field, but it is not recognized appropriately
in delete-instance-records. During the recreation of the view after the
redefinition of the class one will obtain the error of trying to define
two primary keys on the same table -- one for the field and one for the
table.
How to fix this behavior?
Maverik wrote:
> The primary key is created here on the table, and further behavior in
> the example is also good. But if these steps repeat from scratch in the
> new session, we will have the same strange (wrong?) behavior.
>
> Apart from this one may have strange problems, when trying to define
> primary keys via :db-constraints on a certain field (e.g., to make it
> autoincrementable unique identifier). First time the primary key is
> created on the specified field, but it is not recognized appropriately
> in delete-instance-records. During the recreation of the view after the
> redefinition of the class one will obtain the error of trying to define
> two primary keys on the same table -- one for the field and one for the
> table.
>
> How to fix this behavior?
I've worked around similar problems with clsql and postgresql. I wasn't
able to get the auto-unique ID postgresql feature to work (SERIAL) with
clsql, so I just defined a table that contains the next ID for each
type that uses one, and I get that and then increment it each time I
create something. A little bit of a pain, but not too bad.
I also had problems using clsql's create-view-from-class feature with
postgresql. My db objects had varchar types which clsql complained
weren't supported by postgresql even though it created the tables
correctly (just wouldn't let me access the data.)
I worked around the latter by defining my clsql db types using "string
x" instead of "varchar x", and then creating my db schema with a plain
old create-schema.sql text file (and dropping with a corresponding
drop-schema.sql). A little irritating, but not enough to make me
investigate it further (yet).
I don't know this for sure, but it appears that MySQL is the best
supported db with clsql. If you don't care what db you use, you might
have better luck getting the nifty features working if you switch to
that.
- Peter E.
petere <··········@gmail.com> wrote:
+---------------
| Maverik wrote:
| > Apart from this one may have strange problems, when trying to define
| > primary keys via :db-constraints on a certain field (e.g., to make it
| > autoincrementable unique identifier). ...
|
| I've worked around similar problems with clsql and postgresql. I wasn't
| able to get the auto-unique ID postgresql feature to work (SERIAL) with
| clsql, so I just defined a table that contains the next ID for each
| type that uses one, and I get that and then increment it each time I
| create something. A little bit of a pain, but not too bad.
+---------------
I use PG <http://www.cliki.net/Pg> to talk to PostgreSQL [which I
prefer over MySQL because of better transaction support and row-level
concurrency/locking], and have had no problems using columns with
auto-generated sequence numbers, using PostgreSQL's SEQUENCE type,
e.g. for a table of "comments":
CREATE SEQUENCE comment_seq CACHE 1;
CREATE TABLE comments (
comment_id integer PRIMARY KEY DEFAULT nextval('comment_seq'),
...[other columns]...
);
You just have to make sure that when you INSERT new entries into
"comments" that you *don't* specify the "comment_id" attribute.
By the way, the other reason I used PG instead of CLSQL was that
the databases my apps use are often accessed from *other* languages
than just CL (e.g., Perl, C, PHP, etc.), so having to construct
the textual representation of SELECTs, etc., myself is actually
an slight advantage when trying to communicate/negotiate with
people writing in those other languages.
-Rob
-----
Rob Warnock <····@rpw3.org>
627 26th Avenue <URL:http://rpw3.org/>
San Mateo, CA 94403 (650)572-2607
Rob Warnock wrote:
> I use PG <http://www.cliki.net/Pg> to talk to PostgreSQL [which I
> prefer over MySQL because of better transaction support and row-level
> concurrency/locking], and have had no problems using columns with
> auto-generated sequence numbers, using PostgreSQL's SEQUENCE type,
> e.g. for a table of "comments":
Thanks for this info. I'll have to have another go at trying to get
this to work on clsql. I'm using postgresql for the same reasons you
mentioned, but I was hoping that the library would give me a reasonable
degree of database independence in case I did need to switch to another
db for whatever reason.
Having the auto-creation of tables based on db class definitions
feature would have been really handy, at least during development.
I'll keep Pg in mind in the future.
- Peter