X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=usql%2Fdoc%2Fusql-tutorial.txt;fp=usql%2Fdoc%2Fusql-tutorial.txt;h=dcdeeb2f5cd143ed82ef9f70fd8b0ddf7b399ee9;hb=ce0e343835a040406678dff74a62d1b0cb56f317;hp=0000000000000000000000000000000000000000;hpb=edd1963395a5b5e5f91ef975fcd329975ae367e2;p=clsql.git diff --git a/usql/doc/usql-tutorial.txt b/usql/doc/usql-tutorial.txt new file mode 100644 index 0000000..dcdeeb2 --- /dev/null +++ b/usql/doc/usql-tutorial.txt @@ -0,0 +1,509 @@ +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 + +