Common Lisp Package: CLSQL-ORM

This package provides methods to introspect a database Providing features such as generating a CLSQL class based on a table name

README:

CLSQL-ORM # Use this package to help with introspective operations against databases that support information_schema . It supports generating view-class definitions from the database schema.

This was originally clsql-postgres-introspect, though this is a bit far removed from that.

I have used this to successfully generate view-classes for PostgreSQL, MSSQL (through freetds and unixodbc), and mysql (though with minor bugs in mysql).

This project does not attempt to be a persistence layer. It also makes quite a few assumptions about what should be generated and with what types that may not match your desires or existing notions. As such this code might only be useful as a jumping off point for creating your own custom clsql ORM.

Examples ##

Please see the examples directory

(with-a-database (*application*) ;; a clsql-helper macro that sets up clsql:*default-database* (clsql-orm:gen-view-classes :inherits-from '(pg-db-obj) ;; a class I have that I want all my pg-db-objects to inherit from :classes ;; The tables I want to turn into classes '(users user_districts_and_counties titles salaries roles fiscal-years expenses budgets counties districts counties-with-districts reports data-entry-finalizations specialties bad-state-salary-input races)))

` (clsql-orm:gen-view-classes :inherits-from '(pg-db-obj) ;; a class I have that I want all my pg-db-objects to inherit from :classes ;; The tables I want to turn into classes '(users))

;;; Results in the following class definition being evaled: (def-view-class user (pg-db-obj) ((date-entered :column dateentered :accessor date-entered :db-constraints nil :type wall-time :initarg date-entered) (deleted :column deleted :accessor deleted :db-constraints nil :type boolean :initarg deleted) (email :column email :accessor email :db-constraints (not-null) :type (varchar 128) :initarg email) (enabled :column enabled :accessor enabled :db-constraints (not-null) :initform t :type boolean :initarg enabled) (first-name :column firstname :accessor first-name :db-constraints nil :initform nil :type varchar :initarg first-name) (id :column id :accessor id :db-kind key :db-constraints (not-null) :type integer :initarg id) (last-name :column lastname :accessor last-name :db-constraints nil :initform nil :type varchar :initarg last-name) (password :column password :accessor password :db-constraints (not-null) :type (varchar 32) :initarg password) (role-id :column roleid :accessor role-id :db-constraints (not-null) :type integer :initarg role-id) (role-join :accessor role-join :db-kind join :db-info (join-class role :home-key role-id :foreign-key id :set nil)) (salt :column salt :accessor salt :db-constraints (not-null) :type (varchar 4) :initarg salt)) (base-table users))

`

Authors of this Branch ##

Original Author information ##

Alan Shields <Alan-Shields@omrf.ouhsc.edu>

This work was made possible by the Centola Lab of the Oklahoma Medical Research Foundation

License information is in the file LICENSE (LLGPL)

FUNCTION

Public

GEN-VIEW-CLASS (TABLE &KEY CLASSNAME (IS-VIEW NIL) (GENERATE-JOINS T) (GENERATE-REVERSE-JOINS T) (GENERATE-ACCESSORS T) (INHERITS-FROM NIL) (VIEW-INHERITS-FROM NIL) (PACKAGE *PACKAGE*) (NICKNAMES NIL) (SINGULARIZE T) (SCHEMA public) (METACLASS NIL) (SLOTS NIL) (EXPORT-SYMBOLS NIL) (PRINT? NIL))

Generate a view class for clsql, given a table If you want to name the class differently from the table, use the :classname keyword. If you do not want to generate join information for the class, do :generate-joins nil Note: if you specify a classname, join generation to this table won't work properly, as it depends on table names and class names being the same. The join slots/accessors will be named [home key]-[target table]. If you want to have your own naming conventions, it's best to define a class that inherits from your generated class.

GEN-VIEW-CLASSES (&KEY (CLASSES) (EXCLUDES) (GENERATE-JOINS T) (GENERATE-REVERSE-JOINS T) (GENERATE-ACCESSORS T) (SCHEMA public) (PACKAGE *PACKAGE*) (EXPORT-SYMBOLS NIL) (NICKNAMES NIL) (SINGULARIZE T) (INHERITS-FROM NIL) (VIEW-INHERITS-FROM NIL) (METACLASS NIL))

This is the function most people will use to generate table classes. It uses gen-view-class. This function will operate on the default clsql database

Undocumented

COL= (C1 C2)

COLUMN-DEF (SCHEMA TABLE COLUMN DB-TYPE COL-LENGTH SCALE IS-NULL DEFAULT CONSTRAINTS FKEY-SCHEMA FKEY-TABLE FKEY-COL)

COLUMN-DIFF (TABLE-1 TABLE-2 &KEY (SCHEMA-1 *SCHEMA*) (SCHEMA-2 *SCHEMA*))

LIST-COLUMNS (TABLE &OPTIONAL (SCHEMA *SCHEMA*))

Private

CLSQL-COLUMN-DEFINITIONS (TABLE &KEY (SCHEMA *SCHEMA*) (GENERATE-ACCESSORS T) (GENERATE-JOINS T))

For each user column, find out if it's a primary key, constrain it to not null if necessary, translate its type, and declare an initarg

CLSQL-JOIN-DEFINITION (HOME-TABLE HOME-KEY FOREIGN-TABLE FOREIGN-KEY &KEY (GENERATE-ACCESSORS T))

Creates the definition for the joins. Note that this does not handle multi-column foreign keys at the moment. For that matter, if you wish to have custom names and the like, you'd best define an inheriting class

CLSQL-TYPE-FOR-DB-TYPE (DB-TYPE LEN)

Given a postgres type and a modifier, return the clsql type

DEFAULT-LIST-COLUMNS (TABLE &KEY (SCHEMA *SCHEMA*) (REVERSE-JOINS? NIL) &AUX WHERE ORDER)

Returns a list of #(column type length is-null default (key-types) fkey-table fkey-col) for the user columns of table. Do not confuse a table with the clsql class of a table - this needs the actual table name. User columns are those columns which the user defines. Others are defined for various reasons. OID is often one of these.

IDENTITY-COLUMN-P (TABLE COLUMN)

a function that can determine if a key column is IDENTITY for sqlserver

INTERN-NORMALIZE-FOR-LISP (ME &OPTIONAL (PACKAGE *DB-MODEL-PACKAGE*))

Interns a string after uppercasing and flipping underscores to hyphens

SINGULAR-INTERN-NORMALIZE-FOR-LISP (ME &OPTIONAL (PACKAGE *DB-MODEL-PACKAGE*))

Interns a string after uppercasing and flipping underscores to hyphens

Undocumented

%TABLES-TO-GENERATE (CLASSES EXCLUDES SCHEMA)

ACCESSOR-NAME-FOR-COLUMN (TABLE COLUMN)

CLSQL-JOIN-COLUMN-NAME (TABLE REF-TABLE COLNAME)

CLSQL-REVERSE-JOIN-DEFINITION (COLUMN-DEF &KEY (GENERATE-ACCESSORS T))

CLSQL-REVERSE-JOIN-DEFINITIONS (TABLE &KEY (SCHEMA *SCHEMA*))

ENSURE-LIST (X)

INTERNUP (ME &OPTIONAL (PACKAGE *DB-MODEL-PACKAGE*))

LIST-REVERSE-JOIN-COLUMNS (TABLE &KEY (SCHEMA *SCHEMA*))

LIST-TABLES (&OPTIONAL (SCHEMA *SCHEMA*))

NORMALIZE-FOR-SQL (S)

SQLITE3-COLUMN-DEF (TABLE COLUMN-NAME TYPE IS-NULL? DEFAULT PK?)

SQLITE3-LIST-COLUMNS (TABLE &OPTIONAL (SCHEMA *SCHEMA*))

SQLITE3-LIST-TABLES (&KEY (SCHEMA *SCHEMA*) (OWNER NIL))

MACRO

Private

Undocumented

AWHEN (COND &BODY BODY)

ENSURE-STRINGS ((&REST VARS) &BODY BODY)

SLOT-ACCESSOR

Public

SPEC-TYPE (OBJECT)

the original database type rather than its clsql/lisp keyword

SETFSPEC-TYPE (NEW-VALUE OBJECT)

the original database type rather than its clsql/lisp keyword

Undocumented

COL-LENGTH (OBJECT)

SETFCOL-LENGTH (NEW-VALUE OBJECT)

COLUMN (OBJECT)

SETFCOLUMN (NEW-VALUE OBJECT)

DB-TYPE (OBJECT)

SETFDB-TYPE (NEW-VALUE OBJECT)

IS-NULL (OBJECT)

SETFIS-NULL (NEW-VALUE OBJECT)

SCALE (OBJECT)

SETFSCALE (NEW-VALUE OBJECT)

Private

Undocumented

CONSTRAINTS (OBJECT)

SETFCONSTRAINTS (NEW-VALUE OBJECT)

DEFAULT (OBJECT)

SETFDEFAULT (NEW-VALUE OBJECT)

FKEY-COL (OBJECT)

SETFFKEY-COL (NEW-VALUE OBJECT)

FKEY-SCHEMA (OBJECT)

SETFFKEY-SCHEMA (NEW-VALUE OBJECT)

FKEY-TABLE (OBJECT)

SETFFKEY-TABLE (NEW-VALUE OBJECT)

SCHEMA (OBJECT)

SETFSCHEMA (NEW-VALUE OBJECT)

TABLE (OBJECT)

SETFTABLE (NEW-VALUE OBJECT)

VARIABLE

Private

*EXPORT-SYMBOLS*

Should we export every symbol we intern?

*SCHEMA*

The schema we are generating from

*SINGULARIZE*

Should we try to singularize table names

Undocumented

*DB-MODEL-PACKAGE*

CLASS

Public

Undocumented

COLUMN-DEF (SCHEMA TABLE COLUMN DB-TYPE COL-LENGTH SCALE IS-NULL DEFAULT CONSTRAINTS FKEY-SCHEMA FKEY-TABLE FKEY-COL)