Migrations don't happen

[fisxoj]

Right now, changing the definition of a table and evaluating it doesn't perform any migration. for me. I haven't looked into it very hard, yet, but it consistently doesn't happen. (using postgresql)

Let me know if there's somewhere I should look, or some information that would be most useful.

[eudoxia0]

Okay. What does the migrations directory look like before and after creating the table for the first time, and then re-creating it with a change?

[KirillTemnov]

Hi! I have same issues with sqlite3.

Create table with 1 column, then add second collumn, then create row and getting error.

Here's my code:

;; STEP1. evaluate this first
(deftable mytest ()
 (name :type text)) 

;; STEP2. update model 
(deftable mytest ()
 (name :type text)
 (foo  :type text))

;; STEP3. create object
(let ((instance (create 'mytest
                        :name "test"
                        :foo  "bar")))
  (save instance))

;; cause error:
;; DB Error: table mytest has no column named foo (Code: ERROR)
;;   [Condition of type DBI.ERROR:<DBI-PROGRAMMING-ERROR>]
;;
;; Restarts:
;; 0: [RETRY] Retry SLIME interactive evaluation request.
;; 1: [ABORT] Return to sldb level 1.
;; 2: [RETRY] Retry SLIME interactive evaluation request.
;; 3: [*ABORT] Return to SLIME's top level.
;; 4: [REMOVE-FD-HANDLER] Remove #<SB-IMPL::HANDLER INPUT on descriptor 9: #<CLOSURE (LABELS SWANK/SBCL::RUN :IN SWANK/BACKEND:ADD-FD-HANDLER) {100332D08B}>>
;; 5: [ABORT] Exit debugger, returning to top level.

in migrations directory i have ;; AFTER STEP1 : MYTEST.lisp-expr

((:table-options (:DATABASE MAIN) :columns ((:NAME ID :TYPE INTEGER :NULLP NIL :UNIQUEP NIL :PRIMARYP NIL :INDEXP NIL :CHECK NIL :AUTOINCREMENTP T :FOREIGN NIL)
                                            (:NAME NAME :TYPE TEXT :NULLP T :UNIQUEP NIL :PRIMARYP NIL :INDEXP NIL :CHECK NIL :AUTOINCREMENTP NIL :FOREIGN NIL))))

;; AFTER STEP2 : MYTEST.lisp-expr

((:table-options (:DATABASE MAIN) :columns ((:NAME ID :TYPE INTEGER :NULLP NIL :UNIQUEP NIL :PRIMARYP NIL :INDEXP NIL :CHECK NIL :AUTOINCREMENTP T :FOREIGN NIL)
                                            (:NAME FOO :TYPE TEXT :NULLP T :UNIQUEP NIL :PRIMARYP NIL :INDEXP NIL :CHECK NIL :AUTOINCREMENTP NIL :FOREIGN NIL)
                                            (:NAME NAME :TYPE TEXT :NULLP T :UNIQUEP NIL :PRIMARYP NIL :INDEXP NIL :CHECK NIL :AUTOINCREMENTP NIL :FOREIGN NIL))))

Maybe I'm doing something wrong?

[rudolfochrist]

@KirillTemnov Currently there are no migrations for SQLite. See also #4

[KirillTemnov]

Thanks @rudolfochrist I'll try my sample on postgresql in next few days, and write to this issue

[jasonmelbye]

I'm using a postgresql database and migrations do not appear to be taking place. In the migrations directory, a new sexp representation of my table is saved with the changes.

Lightly browsing the source, I don't see where the alter table statement is actually sent to the database. To my untrained eyes, it appears that migrate returns the alter table statement text to build, and nothing is done with it. Am I missing something?

[KirillTemnov]

In postgres i have correct migrations:

;; AFTER STEP1 : MYTEST.lisp-expr

((:table-options (:DATABASE MAIN) :columns ((:NAME ID :TYPE INTEGER :NULLP NIL :UNIQUEP NIL :PRIMARYP T :INDEXP NIL :CHECK NIL :AUTOINCREMENTP T :FOREIGN NIL)
                                            (:NAME NAME :TYPE TEXT :NULLP T :UNIQUEP NIL :PRIMARYP NIL :INDEXP NIL :CHECK NIL :AUTOINCREMENTP NIL :FOREIGN NIL))))

;; AFTER STEP2 : MYTEST.lisp-expr

((:table-options (:DATABASE MAIN) :columns ((:NAME ID :TYPE INTEGER :NULLP NIL :UNIQUEP NIL :PRIMARYP T :INDEXP NIL :CHECK NIL :AUTOINCREMENTP T :FOREIGN NIL)
                                            (:NAME FOO :TYPE TEXT :NULLP T :UNIQUEP NIL :PRIMARYP NIL :INDEXP NIL :CHECK NIL :AUTOINCREMENTP NIL :FOREIGN NIL)
                                            (:NAME NAME :TYPE TEXT :NULLP T :UNIQUEP NIL :PRIMARYP NIL :INDEXP NIL :CHECK NIL :AUTOINCREMENTP NIL :FOREIGN NIL))))

But

;; STEP3. create object Gives error and object not saves:

(let ((instance (create 'mytest
                        :name "test"
                        :foo  "bar")))
  (save instance))

debugger invoked on a UNBOUND-SLOT in thread
#<THREAD "main thread" RUNNING {1002A8B013}>:
  The slot CRANE.CONNECT::CONN is unbound in the object
  #<<DATABASE> {1002B6BE23}>.

select * from mytest;
 id | name 
----+------
(0 rows)

May be it's all becase I'm not save object in initial form, before updating table definition?

[jasonmelbye]

Based on your select output, it looks like the migration did not take place for you. Shouldn't we be expecting that the column foo was added?

I get the correct sexpr saved when I modify a table. But there are in fact no changes to the database.

My attempt to fix it broke other tests. I'm already in over my head, not sure how much help I can be.

As an aside, I believe create should save the object. You shouldn't have to call save immediately after create.

[jasonmelbye]

The immediate test failure that my pull request caused has to do with the adding a null constraint producing a nil query value. The logic in alter-constraint needs to be reversed - only add the constraint if nullp is nil.

This solved the immediate problem of the nil query, but produces other. One has to do with my suggested fix for executing the migration queries. They cannot be executed in one go - DBI needs them to be prepared/executed individually.

I'm committed updates for these issues into my local repository. https://github.com/jasonmelbye/crane/commit/d3ce34c9b245d59b9786627b8bd7d80c2c67a751

I'm not making another/revised pull request yet since the test are still not passing. One of the additional issues this exposes is that adding / altering indexes isn't working correctly. The following SQL is being produced, which is incorrect:

 Running test BASIC-ADDITIONS .
Diff for 'A': (ADDITIONS
               ((NAME FIELD-B TYPE INTEGER NULLP NIL UNIQUEP NIL PRIMARYP NIL
                 INDEXP NIL CHECK NIL AUTOINCREMENTP NIL FOREIGN NIL))
               DELETIONS NIL CHANGES NIL)
Query: ALTER TABLE "a" ADD COLUMN "field-b" INTEGER
Query: ALTER TABLE "a" ADD CONSTRAINT "crane_a_field-b_nullp" CHECK ("field-b" IS NOT NULL)
Diff for 'B': (ADDITIONS
               ((NAME FIELD-B TYPE INTEGER NULLP T UNIQUEP NIL PRIMARYP NIL
                 INDEXP T CHECK NIL AUTOINCREMENTP NIL FOREIGN NIL))
               DELETIONS NIL CHANGES NIL)
Query: ALTER TABLE "b" ADD COLUMN "field-b" INTEGER
Query: ALTER TABLE "b" ADD CREATE INDEX "crane_b_field-b_index" ON "b" ("field-b")

That last query - "ADD CREATE INDEX" is incorrect.