schema.lisp

;;;; schema.lisp -- dreams of an ideal database schema record
;;;
;;; Ideally: this file contains the database schema, represented as a sequence
;;; of migrations. The goal is to have one single database schema, stored in a
;;; DSL. This makes it possible to have easy migations as well as easy database
;;; modelling in code.

;; What kind of migrations can there be?
;; - table changes
;; - views
;; - triggers


;; This is smart enough to know that the ingredient table does not exist yet, so
;; a CREATE statement must be used
(defmig 1 "Initial ingredients table"
  (:table ingredient
          (:row (name :type string))))

;; Ingredient now exists, so ALTER is used to add the row
(defmig 2 "Add optimise bool to ingredients table"
  (:table ingredient
          (:row (optimise :type boolean))))

;; This one is easy
(defmig 3 "Remoe the ingredients table"
  (:drop-table product))


;; ... this should generate SQL migrations like
(defmigration 1 "Initial ingredients table"
  (:up ("create table ingredient"))
  (:down ("drop table ingredient")))
(defmigration 2 "..."
  (:up ("alter table ingredient ..."))
  (:down ...))
;; ...
;; Which can be run with the database-migrations or similar code


;; Also we can then do:
(associate ingredient ((:has-one unit)
                       (:owns-many product)))
;; Which adds JOIN info to the model (using OOOK associations and models)

;; Finally,
(migrate)
(generate-models)
;; This ensures the database is up to date and creates the view classes to
;; access the databases.
;;
;; This should NOT be magical. It should be easy to run migrations manually,
;; easy to determine what version the database is at, etc.


;;
;; Notes

;; Keep the schema version in the database!

;; defmig macro
;; - add the index (restart-case if already exists)
;; - build a list of tables and their rows