source: project/wiki/ssql-record @ 33754

Last change on this file since 33754 was 33754, checked in by arthurmaciel, 2 years ago
File size: 8.1 KB
Line 
1[[tags: egg]]
2
3== ssql-record
4
5[[toc:]]
6
7=== Description
8
9This 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
13The 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
24Creates 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
39Returns 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
49No {{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
59Generates 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
69Generates a SSQL {{delete}} statement with a {{where}} clause correctly assigned to {{record}}'s {{<id>s}}.
70
71=== Examples
72
73Record 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
85Record 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
125Editing 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
145An 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 TracBrowser for help on using the repository browser.