X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=doc%2Fcsql.xml;fp=doc%2Fcsql.xml;h=d0d09fa4c76caaa7efab2ccecd36dd4d1a59eac1;hb=595658021ebaf450894bb143a346e409b55e02af;hp=0000000000000000000000000000000000000000;hpb=c4ffac239e4910bff542dadf3212ad95803af64e;p=clsql.git diff --git a/doc/csql.xml b/doc/csql.xml new file mode 100644 index 0000000..d0d09fa --- /dev/null +++ b/doc/csql.xml @@ -0,0 +1,740 @@ + + +%myents; +]> + + + &commonsql; Tutorial + Based on the &usql; Tutorial + + + Introduction + + + The goal of this tutorial is to guide a new developer thru the + process of creating a set of &clsql; classes providing a + Object-Oriented interface to persistent data stored in an &sql; + database. We will assume that the reader is familiar with how + &sql; works, how relations (tables) should be structured, and + has created at least one &sql; application previously. We will + also assume a minor level of experience with Common Lisp. + + + + &clsql; provides two different interfaces to &sql; databases, a + Functional interface, and an Object-Oriented interface. The + Functional interface consists of a special syntax for embedded + &sql; expressions in Lisp, and provides lisp functions for &sql; + operations like SELECT and + UPDATE. The object-oriented interface provides + a way for mapping Common Lisp Objects System (CLOS) objects into + databases and includes functions for inserting new objects, + querying objects, and removing objects. Most applications will + use a combination of the two. + + + + &clsql; is based on the CommonSQL package from Xanalys, so the + documentation that Xanalys makes available online is useful for + &clsql; as well. It is suggested that developers new to &clsql; read + their documentation as well, as any differences between CommonSQL + and &clsql; are minor. Xanalys makes the following documents + available: + + + + + + + Xanalys &lw; User Guide - The &commonsql; + Package + + + + + + + + + Xanalys &lw; Reference Manual - The SQL + Package + + + + + + + + &commonsql; Tutorial by Nick Levine + + + + + + + + Data Modeling with &clsql; + + + Before we can create, query and manipulate &clsql; objects, we + need to define our data model as noted by Philip Greenspun + + + + Philip Greenspun's "SQL For Web Nerds" - Data + Modeling + + + + + + + When data modeling, you are telling the relational database + management system (RDBMS) the following: + + + + + What elements of the data you will store. + + + How large each element can be. + + + What kind of information each element can contain. + + + What elements may be left blank. + + + Which elements are constrained to a fixed range. + + + Whether and how various tables are to be linked. + + + + + With &sql; database one would do this by defining a set of + relations, or tables, followed by a set of queries for joining + the tables together in order to construct complex records. + However, with &clsql; we do this by defining a set of CLOS + classes, specifying how they will be turned into tables, and how + they can be joined to one another via relations between their + attributes. The &sql; tables, as well as the queries for + joining them together are created for us automatically, saving + us from dealing with some of the tedium of &sql;. + + + + Let us start with a simple example of two &sql; tables, and the + relations between them. + + + +CREATE TABLE EMPLOYEE ( + emplid NOT NULL number(38), + first_name NOT NULL varchar2(30), + last_name NOT NULL varchar2(30), + emall varchar2(100), + companyid NOT NULL number(38), + managerid number(38) +) + +CREATE TABLE COMPANY ( + companyid NOT NULL number(38), + name NOT NULL varchar2(100), + presidentid NOT NULL number(38) +) + + + +This is of course the canonical &sql; tutorial example, "The Org Chart". + + + +In &clsql;, we would have two "view classes" (a fancy word for a class +mapped into a database). They would be defined as follows: + + + +(clsql:def-view-class employee () + ((emplid + :db-kind :key + :db-constraints :not-null + :type integer + :initarg :emplid) + (first-name + :accessor first-name + :type (string 30) + :initarg :first-name) + (last-name + :accessor last-name + :type (string 30) + :initarg :last-name) + (email + :accessor employee-email + :type (string 100) + :nulls-ok t + :initarg :email) + (companyid + :type integer) + (managerid + :type integer + :nulls-ok t)) + (:base-table employee)) + +(clsql:def-view-class company () + ((companyid + :db-type :key + :db-constraints :not-null + :type integer + :initarg :companyid) + (name + :type (string 100) + :initarg :name) + (presidentid + :type integer)) + (:base-table company)) + + + + The DEF-VIEW-CLASS macro is just like the + normal CLOS DEFCLASS macro, except that it + handles several slot options that DEFCLASS + doesn't. These slot options have to do with the mapping of the slot + into the database. We only use a few of the slot options in the + above example, but there are several others. + + + + + + :column - The name of the &sql; column this slot is stored in. + Defaults to the slot name. If the slot name is not a valid &sql; + identifier, it is escaped, so foo-bar becomes foo_bar. + + + + + :db-kind - The kind of database mapping which + is performed for this slot. :base indicates + the slot maps to an ordinary column of the database view. + :key indicates that this slot corresponds to + part of the unique keys for this view, :join + indicates a join slot representing a relation to another view + and :virtual indicates that this slot is an ordinary CLOS slot. + Defaults to :base. + + + + :db-reader - If a string, then when reading + values from the database, the string will be used for a format + string, with the only value being the value from the database. + The resulting string will be used as the slot value. If a + function then it will take one argument, the value from the + database, and return the value that should be put into the slot. + + + + + :db-writer - If a string, then when reading + values from the slot for the database, the string will be used + for a format string, with the only value being the value of the + slot. The resulting string will be used as the column value in + the database. If a function then it will take one argument, the + value of the slot, and return the value that should be put into + the database. + + + + :db-type - A string which will be used as the + type specifier for this slots column definition in the database. + + + + + :nulls-ok - If &t;, all &sql; &null; values + retrieved from the database become nil; if &nil;, all &null; + values retrieved are converted by + DATABASE-NULL-VALUE. + + + + :db-info - A join specification. + + + + + In our example each table as a primary key attribute, which is + required to be unique. We indicate that a slot is part of the + primary key (&clsql; supports multi-field primary keys) by specifying + the :db-kind key slot option. + + + + The &sql; type of a slot when it is mapped into the database is + determined by the :type slot option. The argument + for the :type option is a Common Lisp datatype. + The &clsql; framework will determine the appropriate mapping + depending on the database system the table is being created in. If + we really wanted to determine what &sql; type was used for a slot, + we could specify a :db-type option like + "NUMBER(38)" and we would be guaranteed that the slot would be + stored in the database as a NUMBER(38). This is not recomended + because it could makes your view class unportable across database + systems. + + + + DEF-VIEW-CLASS also supports some class + options, like :base-table. The + :base-table option specifies what the table name + for the view class will be when it is mapped into the database. + + + + +Class Relations + + +In an &sql; only application, the EMPLOYEE and +COMPANY tables can be queried to determine things +like, "Who is Vladamir's manager?", What company does Josef work +for?", and "What employees work for Widgets Inc.". This is done by +joining tables with an &sql; query. + + + +Who works for Widgets Inc.? + + + +SELECT first_name, last_name FROM employee, company + WHERE employee.companyid = company.companyid + AND company.company_name = "Widgets Inc." + + + +Who is Vladamir's manager? + + + +SELECT managerid FROM employee + WHERE employee.first_name = "Vladamir" + AND employee.last_name = "Lenin" + + + +What company does Josef work for? + + + +SELECT company_name FROM company, employee + WHERE employee.first_name = "Josef" + AND employee.last-name = "Stalin" + AND employee.companyid = company.companyid + + + +With &clsql; however we do not need to write out such queries because +our view classes can maintain the relations between employees and +companies, and employees to their managers for us. We can then access +these relations like we would any other attribute of an employee or +company object. In order to do this we define some join slots for our +view classes. + + + +What company does an employee work for? If we add the following slot +definition to the employee class we can then ask for it's +COMPANY slot and get the appropriate result. + + + + ;; In the employee slot list + (company + :accessor employee-company + :db-kind :join + :db-info (:join-class company + :home-key companyid + :foreign-key companyid + :set nil)) + + + +Who are the employees of a given company? And who is the president of +it? We add the following slot definition to the company view class and +we can then ask for it's EMPLOYEES slot and get the +right result. + + + + ;; In the company slot list + (employees + :reader company-employees + :db-kind :join + :db-info (:join-class employee + :home-key companyid + :foreign-key companyid + :set t)) + + (president + :reader president + :db-kind :join + :db-info (:join-class employee + :home-key presidentid + :foreign-key emplid + :set nil)) + + + +And lastly, to define the relation between an employee and their +manager: + + + + ;; In the employee slot list + (manager + :accessor employee-manager + :db-kind :join + :db-info (:join-class employee + :home-key managerid + :foreign-key emplid + :set nil)) + + + +&clsql; join slots can represent one-to-one, one-to-many, and +many-to-many relations. Above we only have one-to-one and one-to-many +relations, later we will explain how to model many-to-many relations. +First, let's go over the slot definitions and the available options. + + + +In order for a slot to be a join, we must specify that it's +:db-kind :join, as opposed to +:base or :key. Once we do that, we +still need to tell &clsql; how to create the join statements for the +relation. This is what the :db-info option does. It +is a list of keywords and values. The available keywords are: + + + + + + :join-class - The view class to which we want + to join. It can be another view class, or the same view class + as our object. + + + + :home-key - The slot(s) in the immediate object + whose value will be compared to the foreign-key slot(s) in the + join-class in order to join the two tables. It can be a single + slot-name, or it can be a list of slot names. + + + + :foreign-key - The slot(s) in the join-class + which will be compared to the value(s) of the home-key. + + + + + :set - A boolean which if false, indicates that + this is a one-to-one relation, only one object will be returned. + If true, than this is a one-to-many relation, a list of objects + will be returned when we ask for this slots value. + + + + +There are other :join-info options available in &clsql;, but we will +save those till we get to the many-to-many relation examples. + + + + + +Object Creation + + +Now that we have our model laid out, we should create some object. +Let us assume that we have a database connect set up already. We +first need to create our tables in the database: + + + +Note: the file doc/clsql-tutorial.lisp contains +view class definitions which you can load into your list at this point +in order to play along at home. + + + +(clsql:create-view-from-class 'employee) +(clsql:create-view-from-class 'company) + + + +Then we will create our objects. We create them just like you would +any other CLOS object: + + + +(defvar employee1 (make-instance 'employee + :emplid 1 + :first-name "Vladamir" + :last-name "Lenin" + :email "lenin@soviet.org")) + +(defvar company1 (make-instance 'company + :companyid 1 + :name "Widgets Inc.")) + + +(defvar employee2 (make-instance 'employee + :emplid 2 + :first-name "Josef" + :last-name "Stalin" + :email "stalin@soviet.org")) + + + +In order to insert an objects into the database we use the +UPDATE-RECORDS-FROM-INSTANCE function as follows: + + + +(clsql:update-records-from-instance employee1) +(clsql:update-records-from-instance employee2) +(clsql:update-records-from-instance company1) + + + +Now we can set up some of the relations between employees and +companies, and their managers. The +ADD-TO-RELATION method provides us with an easy +way of doing that. It will update both the relation slot, as well as +the home-key and foreign-key slots in both objects in the relation. + + + +;; Lenin manages Stalin (for now) +(clsql:add-to-relation employee2 'manager employee1) + +;; Lenin and Stalin both work for Widgets Inc. +(clsql:add-to-relation company1 'employees employee1) +(clsql:add-to-relation company1 'employees employee2) + +;; Lenin is president of Widgets Inc. +(clsql:add-to-relation company1 'president employee1) + + + + After you make any changes to an object, you have to specifically + tell &clsql; to update the &sql; database. The + UPDATE-RECORDS-FROM-INSTANCE method will write + all of the changes you have made to the object into the database. + + + + Since &clsql; objects are just normal CLOS objects, we can manipulate + their slots just like any other object. For instance, let's say + that Lenin changes his email because he was getting too much spam + from the German Socialists. + + + +;; Print Lenin's current email address, change it and save it to the +;; database. Get a new object representing Lenin from the database +;; and print the email + +;; This lets us use the functional &clsql; interface with [] syntax +(clsql:locally-enable-sql-reader-syntax) + +(format t "The email address of ~A ~A is ~A" + (first-name employee1) + (last-name employee1) + (employee-email employee1)) + +(setf (employee-email employee1) "lenin-nospam@soviets.org") + +;; Update the database +(clsql:update-records-from-instance employee1) + +(let ((new-lenin (car (clsql:select 'employee + :where [= [slot-value 'employee 'emplid] 1])))) + (format t "His new email is ~A" + (employee-email new-lenin))) + + + + Everything except for the last LET expression + is already familiar to us by now. To understand the call to + CLSQL:SELECT we need to discuss the + Functional &sql; interface and it's integration with the Object + Oriented interface of &clsql;. + + + + + +Finding Objects + + + Now that we have our objects in the database, how do we get them out + when we need to work with them? &clsql; provides a functional + interface to &sql;, which consists of a special Lisp reader macro + and some functions. The special syntax allows us to embed &sql; in + lisp expressions, and lisp expressions in &sql;, with ease. + + + + Once we have turned on the syntax with the expression: + + + +(clsql:locally-enable-sql-reader-syntax) + + + + We can start entering fragments of &sql; into our lisp reader. We + will get back objects which represent the lisp expressions. These + objects will later be compiled into &sql; expressions that are + optimized for the database backed we are connected to. This means + that we have a database independent &sql; syntax. Here are some + examples: + + + +;; an attribute or table name +[foo] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO> + +;; a attribute identifier with table qualifier +[foo bar] => #<CLSQL-SYS::SQL-IDENT-ATTRIBUTE FOO.BAR> + +;; a attribute identifier with table qualifier +[= "Lenin" [first_name]] => + #<CLSQL-SYS::SQL-RELATIONAL-EXP ('Lenin' = FIRST_NAME)> + +[< [emplid] 3] => + #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLID < 3)> + +[and [< [emplid] 2] [= [first_name] "Lenin"]] => + #<CLSQL-SYS::SQL-RELATIONAL-EXP ((EMPLID < 2) AND + (FIRST_NAME = 'Lenin'))> + + +;; If we want to reference a slot in an object we can us the +;; SLOT-VALUE sql extension +[= [slot-value 'employee 'emplid] 1] => + #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = 1)> + +[= [slot-value 'employee 'emplid] + [slot-value 'company 'presidentid]] => + #<CLSQL-SYS::SQL-RELATIONAL-EXP (EMPLOYEE.EMPLID = COMPANY.PRESIDENTID)> + + + + The SLOT-VALUE operator is important because it + let's us query objects in a way that is robust to any changes in the + object->table mapping, like column name changes, or table name + changes. So when you are querying objects, be sure to use the + SLOT-VALUE &sql; extension. + + + + Since we can now formulate &sql; relational expression which can be + used as qualifiers, like we put after the WHERE + keyword in &sql; statements, we can start querying our objects. + &clsql; provides a function SELECT which can return + use complete objects from the database which conform to a qualifier, + can be sorted, and various other &sql; operations. + + + + The first argument to SELECT is a class name. it + also has a set of keyword arguments which are covered in the + documentation. For now we will concern ourselves only with the + :where keyword. Select returns a list of objects, or nil if it + can't find any. It's important to remember that it always returns a + list, so even if you are expecting only one result, you should + remember to extract it from the list you get from + SELECT. + + + +;; all employees +(clsql:select 'employee) +;; all companies +(clsql:select 'company) + +;; employees named Lenin +(clsql:select 'employee :where [= [slot-value 'employee 'last-name] + "Lenin"]) + +(clsql:select 'company :where [= [slot-value 'company 'name] + "Widgets Inc."]) + +;; Employees of Widget's Inc. +(clsql:select 'employee + :where [and [= [slot-value 'employee 'companyid] + [slot-value 'company 'companyid]] + [= [slot-value 'company 'name] + "Widgets Inc."]]) + +;; Same thing, except that we are using the employee +;; relation in the company view class to do the join for us, +;; saving us the work of writing out the &sql;! +(company-employees company1) + +;; President of Widgets Inc. +(president company1) + +;; Manager of Josef Stalin +(employee-manager employee2) + + + + + +Deleting Objects + + + Now that we know how to create objects in our database, manipulate + them and query them (including using our predefined relations to + save us the trouble writing alot of &sql;) we should learn how to + clean up after ourself. It's quite simple really. The function + DELETE-INSTANCE-RECORDS will remove an object + from the database. However, when we remove an object we are + responsible for making sure that the database is left in a correct + state. + + + + For example, if we remove a company record, we need to either remove + all of it's employees or we need to move them to another company. + Likewise if we remove an employee, we should make sure to update any + other employees who had them as a manager. + + + + + +Conclusion + + + There are many nooks and crannies to &clsql;, some of which are + covered in the Xanalys documents we refered to earlier, some are + not. The best documentation at this time is still the source code + for &clsql; itself and the inline documentation for its various + functions. + + + + +