Changeset 33756 in project


Ignore:
Timestamp:
12/04/16 11:10:55 (10 months ago)
Author:
arthurmaciel
Message:
 
File:
1 edited

Legend:

Unmodified
Added
Removed
  • wiki/ssql-record

    r33754 r33756  
    1 [[tags: egg]]
    2 
    3 == ssql-record
    4 
    5 [[toc:]]
    6 
    7 === Description
    8 
    9 This is a simple egg to avoid SSQL boilerplate code when prototyping. It '''does not''' provide a optimized SSQL code, only generic SSQL for sketching up some DB programs. The code is located at [[https://github.com/arthurmaciel/ssql-record]].
    10 
    11 === Dependencies
    12 
    13 The egg depends on [[/egg/typed-records|typed-records]] and {{srfi-1}}.
    14 
    15 === Author
    16 
    17 [[/users/arthurmaciel|Arthur Maciel]]
    18 
    19 === Documentation
    20 
    21 ==== define-ssql-record
    22 <macro>(define-ssql-record <record-name> (<id1> [<id2> ...] ) <other-field1> [<other-field2> ...] )</macro>
    23 
    24 Creates a record with useful procedures to generate SSQL code. The first item after the {{<record-name>}} '''MUST''' be a list containing one or more {{<id>s}}.
    25 
    26 ==== <ssql-record>-select
    27 <procedure>(<ssql-record>-select cols [ssql])</procedure>
    28 
    29 {{cols}} must be a list as {{'(columns <col1> [<col2> ...] )}}. {{ssql}} is optional and if provided must be a list of lists like {{'((where (< 1 id)))}} or {{'((where (= 123 id)) (order name))}}.
    30 
    31 ==== <ssql-record>-rec-select
    32 <procedure>(<ssql-record>-rec-select record [ssql])</procedure>
    33 
    34 {{record}} must be a <ssql-record>. {{ssql}} is optional and if provided must be a list of lists. Automatically builds a {{where}} clause with {{record}}'s {{<id>s}}.
    35 
    36 ==== <ssql-record>-select-all
    37 <procedure>(<ssql-record>-select-all [ssql])</procedure>
    38 
    39 Returns a SSQL {{select}} statement that selects all columns defined at the creation of <ssql-record> in the same specified order. {{ssql}} is optional and if provided must be a list of lists like {{'((where (< 1 id))}} or {{'((where (= 123 id)) (order name))}}.
    40 
    41 ==== <ssql-record>-update
    42 <procedure>(<ssql-record>-update ssql)</procedure>
    43 
    44 {{ssql}} is mandatory and must be a list of lists like {{'((set (name "Bob") (surname "Spitzer")) [(where (< 1 id))])}}.
    45 
    46 ==== <ssql-record>-rec-update
    47 <procedure>(<ssql-record>-rec-update record)</procedure>
    48 
    49 No {{ssql}} can be appended to this command. Automatically builds a {{where}} clause with {{record}}'s {{<id>s}}.
    50 
    51 ==== <ssql-record>-insert
    52 <procedure>(<ssql-record>-insert ssql)</procedure>
    53 
    54 {{ssql}} is mandatory and must be a list of lists like {{'([(columns name surname)] (values #("Bob" "Spitzer")))}}.
    55 
    56 ==== <ssql-record>-rec-insert
    57 <procedure>(<ssql-record>-rec-insert record)</procedure>
    58 
    59 Generates a SSQL {{insert}} statement with all {{record}}'s fields, except for {{<id>s}} ''if'' anyone of them is {{'NULL}} (in both single or composite id scenarios).
    60 
    61 ==== <ssql-record>-delete
    62 <procedure>(<ssql-record>-delete [ssql])</procedure>
    63 
    64 {{ssql}} is optional and if provided must be a list of lists like {{'((where (in id #(10 11 93))))}}.
    65 
    66 ==== <ssql-record>-rec-delete
    67 <procedure>(<ssql-record>-rec-delete record)</procedure>
    68 
    69 Generates a SSQL {{delete}} statement with a {{where}} clause correctly assigned to {{record}}'s {{<id>s}}.
    70 
    71 === Examples
    72 
    73 Record creation:
    74 <enscript hilight="scheme>
    75 (use ssql ssql-record)
    76 
    77 ;; A record with composite ids.
    78 (define-ssql-record doc (num date) type author access_type)
    79 
    80 (define d1 (alist->doc '((num . 1) (date . "5/11/2016") (type . 2) (author . "Bob"))))
    81 (define d2 (make-doc num: 1 date: "5/11/2016" type: 2 author: "Bob"))
    82 (define d3 (list->doc '(1 "5/11/2016" 2 "Bob" NULL))) ; list-><ssql-record> needs all fields
    83 </enscript>
    84 
    85 Record accessors:
    86 <enscript hilight="scheme>
    87 ;; All unspecified fields are set to 'NULL.
    88 (doc->alist d1)
    89 => `((num . 1) (date . "5/11/2016") (type . 2) (author . "Bob") (access_type . NULL))
    90 
    91 (doc-num d1)
    92 => 1
    93 
    94 (doc-fields)
    95 => '(num date type author access_type)
    96 
    97 (doc-select '(columns num date) '((where (= id 3))))
    98 => '(select (columns num date) (from doc) (where (= id 3)))
    99 
    100 (doc-rec-select d1)
    101 => '(select (columns num date type author access_type) (from doc) (where (= num 1) (= date "5/11/2016")))
    102 
    103 (doc-select-all)
    104 => '(select (columns num date type author access_type) (from doc))
    105 
    106 (doc-update '((set (type 3) (author "Bob")) (where (= id 4))))
    107 => '(update (table doc) (set (type 3) (author "Bob")) (where (= id 4)))
    108 
    109 (doc-rec-update d1)
    110 => `(update (table doc) (set (type 2) (author "Bob") (access_type NULL)) (where (= num 1) (= date "5/11/2016")))
    111 
    112 (doc-insert '((values #(3 "11/5/2016" 8 9 "Boo" NULL)))
    113 => '(insert (into doc) (values #(3 "11/5/2016" 8 9 "Boo" NULL)))
    114 
    115 (doc-rec-insert d1)
    116 => `(insert (into doc) (columns num date type author access_type) (values #(1 "5/11/2016" 2 "Bob" NULL)))
    117 
    118 (doc-delete '((where (= id 3))))
    119 => '(delete (from doc) (where (= id 3)))
    120 
    121 (doc-rec-delete d1)
    122 => '(delete (from doc) (where (= num 1) (= date "5/11/2016")))
    123 </enscript>
    124 
    125 Editing records:
    126 <enscript hilight="scheme>
    127 (use ssql ssql-record)
    128 
    129 (define-ssql-record person (id) name dob gender)
    130 
    131 (define p (alist->person '((name . "Bob") (dob . "5/11/1992") (gender . "male"))))
    132 
    133 ;; Does not insert id if it is not set.
    134 (person-rec-insert p)
    135 => (insert (into person) (columns name dob gender) (values #("Bob" "5/11/1992" "male"))
    136 
    137 (person-id-set! p 1)
    138 (person-rec-insert p)
    139 => (insert (into person) (columns id name dob gender) (values #(1 "Bob" "5/11/1992" "male")))
    140 
    141 (ssql->sql #f (person-rec-insert p))
    142 => "INSERT INTO person (id, name, dob, gender) VALUES (1, 'Bob', '5/11/1992', 'male')"
    143 </enscript>
    144 
    145 An egg for syntax convenience when prototyping (not designed to be an ORM):
    146 <enscript hilight="scheme>
    147 (use ssql ssql-record)
    148 
    149 (define-ssql-record author (id) firstname lastname)
    150 (define-ssql-record ticket (id) title author_id description)
    151 
    152 ;; Suppose the procedure $db transforms SSQL into SQL and
    153 ;; send statements to the database.
    154 (define t
    155   (alist->ticket '((title . "Smashed stack")
    156                    (author_id . ($db (author-select '(columns id)
    157                                                     '((where (and (= firstname "Bob")
    158                                                                   (= lastname "Spitzer")))))
    159                    (description . "No idea how to fix it"))))
    160 
    161 ($db (ticket-rec-insert t))
    162 
    163 ;; Ticket t needs to be edited
    164 (ticket-description-set! t "Found the culprit: Alex One")
    165 ($db (ticket-rec-update t))
    166 </enscript>
    167 
    168 {{select-all}} and {{list-><ssql-record>}}:
    169 <enscript hilight="scheme>
    170 (use ssql ssql-record)
    171 
    172 (define-ssql-record authors (id) firstname lastname)
    173 
    174 ;; Suppose the procedure $db transforms SSQL into SQL and
    175 ;; send statements to the database, retrieving a list of
    176 ;; lists as result.
    177 (define authors-list
    178    (map list->authors
    179         ($db (authors-select-all))
    180 
    181 (define sxml
    182    (map (lambda (a)
    183            `(div (span ,(authors-id a))
    184                  (span ,(authors-firstname a))
    185                  (span ,(authors-lastname a))))
    186         authors-list))
    187 
    188 ;; send 'sxml' to be rendered...
    189 </enscript>
    190 
    191 
    192 === License
    193 
    194   Copyright (C) 2016 Arthur Maciel
    195  
    196   All rights reserved.
    197  
    198   Redistribution and use in source and binary forms, with or without
    199   modification, are permitted provided that the following conditions are met:
    200       * Redistributions of source code must retain the above copyright
    201         notice, this list of conditions and the following disclaimer.
    202       * Redistributions in binary form must reproduce the above copyright
    203         notice, this list of conditions and the following disclaimer in the
    204         documentation and/or other materials provided with the distribution.
    205       * Neither the name of the <organization> nor the
    206         names of its contributors may be used to endorse or promote products
    207         derived from this software without specific prior written permission.
    208  
    209   THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
    210   ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
    211   WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
    212   DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
    213   DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
    214   (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
    215   LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
    216   ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
    217   (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
    218   SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
Note: See TracChangeset for help on using the changeset viewer.