X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=usql%2Fdoc%2Fusql-tutorial.txt;fp=usql%2Fdoc%2Fusql-tutorial.txt;h=0000000000000000000000000000000000000000;hb=68048d3d30365c460963c9b7d1de53dfa521c307;hp=dcdeeb2f5cd143ed82ef9f70fd8b0ddf7b399ee9;hpb=17ed099bd39998e297b20daefd3f80264b0677f5;p=clsql.git diff --git a/usql/doc/usql-tutorial.txt b/usql/doc/usql-tutorial.txt deleted file mode 100644 index dcdeeb2..0000000 --- a/usql/doc/usql-tutorial.txt +++ /dev/null @@ -1,509 +0,0 @@ -INTRODUCTION - -The goal of this tutorial is to guide a new developer thru the process -of creating a set of USQL 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. - -UncommonSQL (USQL) 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 OO 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. - -USQL is based on the CommonSQL package from Xanalys, so the -documentation that Xanalys makes available online is useful for USQL -as well. It is suggested that developers new to USQL check their -documentation out, as any differences between CommonSQL and USQL are -minor. Xanalys makes the following documents available: - -Xanalys LispWorks User Guide - The CommonSQL Package -http://www.lispworks.com/reference/lw43/LWUG/html/lwuser-167.htm - -Xanalys LispWorks Reference Manual -- The SQL Package -http://www.lispworks.com/reference/lw43/LWRM/html/lwref-383.htm - -CommonSQL Tutorial by Nick Levine -http://www.ravenbrook.com/doc/2002/09/13/common-sql/ - - -DATA MODELING WITH UNCOMMONSQL - -Before we can create, query and manipulate USQL objects, we need to -define our data model. To borrow from Philip Greenspun[1]: - -When data modeling, you are telling the 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 USQL 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 USQL, we would have two "view classes" (a fancy word for a class -mapped into a database). They would be defined as follows: - -(sql: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)) - -(sql: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 DB mapping which is performed for this - slot. :BASE indicates the slot maps to an ordinary column of the - DB 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 DB, 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 DB, 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 DB. 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 (USQL 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 USQL 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 n the DB 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 USQL 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)) - -USQL 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 USQL 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 USQL, 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 usql-tutorial.lisp contains view class definitions -which you can load into your list at this point in order to play along -at home. - -(sql:create-view-from-class 'employee) -(sql: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: - -(sql:update-records-from-instance employee1) -(sql:update-records-from-instance employee2) -(sql: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) -(sql:add-to-relation employee2 'manager employee1) - -;; Lenin and Stalin both work for Widgets Inc. -(sql:add-to-relation company1 'employees employee1) -(sql:add-to-relation company1 'employees employee2) - -;; Lenin is president of Widgets Inc. -(sql:add-to-relation company1 'president employee1) - -After you make any changes to an object, you have to specifically tell -USQL 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 USQL objects re 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 fro 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 USQL interface with [] syntax -(sql: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 -(sql:update-records-from-instance employee1) - -(let ((new-lenin (car (sql: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 SQL:SELECT we need to discuss -the Functional SQL interface and it's integration with the Object -Oriented interface of USQL. - - -FINDING OBJECTS - -Now that we have our objects in the database, how do we get them out -when we need to work with them? USQL 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: - -(sql: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] => # - -;; a attribute identifier with table qualifier -[foo bar] => # - -;; a attribute identifier with table qualifier -[= "Lenin" [first_name]] => - # - -[< [emplid] 3] => - # - -[and [< [emplid] 2] [= [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] => - # - -[= [slot-value 'employee 'emplid] - [slot-value '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. USQL 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 -(sql:select 'employee) -;; all companies -(sql:select 'company) - -;; employees named Lenin -(sql:select 'employee :where [= [slot-value 'employee 'last-name] - "Lenin"]) - -(sql:select 'company :where [= [slot-value 'company 'name] - "Widgets Inc."]) - -;; Employees of Widget's Inc. -(sql: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 alot more nooks and crannies to USQL, some of which are -covered n the Xanalys documents we refered to earlier, some are not. -The best documentation at this time is still the source code for USQL -itself and the inline documentation for it's various function. - - - -[1] Philip Greenspun's "SQL For Web Nerds" - Data Modeling - http://www.arsdigita.com/books/sql/data-modeling.html - -