1 ! Copyright (C) 2007, 2008 Doug Coleman.
2 ! See http://factorcode.org/license.txt for BSD license.
3 USING: arrays assocs alien alien.syntax continuations io
4 kernel math math.parser namespaces make prettyprint quotations
5 sequences debugger db db.postgresql.lib db.postgresql.ffi
6 db.tuples db.types tools.annotations math.ranges
7 combinators classes locals words tools.walker
8 nmake accessors random db.queries destructors db.tuples.private ;
12 TUPLE: postgresql-db < db
13 host port pgopts pgtty db user pass ;
15 TUPLE: postgresql-statement < statement ;
17 TUPLE: postgresql-result-set < result-set ;
19 M: postgresql-db make-db* ( seq db -- db )
26 M: postgresql-db db-open ( db -- db )
35 } cleave connect-postgres >>handle ;
37 M: postgresql-db dispose ( db -- )
40 M: postgresql-statement bind-statement* ( statement -- ) drop ;
42 GENERIC: postgresql-bind-conversion ( tuple object -- low-level-binding )
44 M: sql-spec postgresql-bind-conversion ( tuple spec -- object )
45 slot-name>> swap get-slot-named <low-level-binding> ;
47 M: literal-bind postgresql-bind-conversion ( tuple literal-bind -- object )
48 nip value>> <low-level-binding> ;
50 M: generator-bind postgresql-bind-conversion ( tuple generate-bind -- object )
51 dup generator-singleton>> eval-generator
52 [ swap slot-name>> rot set-slot-named ] [ <low-level-binding> ] bi ;
54 M: postgresql-statement bind-tuple ( tuple statement -- )
56 [ postgresql-bind-conversion ] with map
59 M: postgresql-result-set #rows ( result-set -- n )
62 M: postgresql-result-set #columns ( result-set -- n )
65 : result-handle-n ( result-set -- handle n )
66 [ handle>> ] [ n>> ] bi ;
68 M: postgresql-result-set row-column ( result-set column -- object )
69 [ result-handle-n ] dip pq-get-string ;
71 M: postgresql-result-set row-column-typed ( result-set column -- object )
72 dup pick out-params>> nth type>>
73 [ result-handle-n ] 2dip postgresql-column-typed ;
75 M: postgresql-statement query-results ( query -- result-set )
77 over [ bind-statement ] keep
78 do-postgresql-bound-statement
80 dup do-postgresql-statement
82 postgresql-result-set new-result-set
85 M: postgresql-result-set advance-row ( result-set -- )
86 [ 1+ ] change-n drop ;
88 M: postgresql-result-set more-rows? ( result-set -- ? )
89 [ n>> ] [ max>> ] bi < ;
91 M: postgresql-statement dispose ( query -- )
95 M: postgresql-result-set dispose ( result-set -- )
103 M: postgresql-statement prepare-statement ( statement -- )
105 >r db get handle>> f r>
106 [ sql>> ] [ in-params>> ] bi
107 length f PQprepare postgresql-error
110 M: postgresql-db <simple-statement> ( sql in out -- statement )
111 postgresql-statement new-statement ;
113 M: postgresql-db <prepared-statement> ( sql in out -- statement )
114 <simple-statement> dup prepare-statement ;
118 sql-counter [ inc ] [ get 0# ] bi ;
120 M: postgresql-db bind% ( spec -- )
123 M: postgresql-db bind# ( spec object -- )
124 >r bind-name% f swap type>> r> <literal-bind> 1, ;
126 : create-table-sql ( class -- statement )
129 "create table " 0% 0%
133 dup type>> lookup-create-type 0%
140 [ "," 0% ] [ column-name>> 0% ] interleave
144 : create-function-sql ( class -- statement )
147 "create function add_" 0% dup 0%
151 type>> lookup-type 0%
154 " returns bigint as '" 0%
159 over [ ", " 0% ] [ column-name>> 0% ] interleave
161 swap [ ", " 0% ] [ drop bind-name% ] interleave
163 "select currval(''" 0% 0% "_id_seq'');' language sql;" 0%
166 M: postgresql-db create-sql-statement ( class -- seq )
168 [ create-table-sql , ] keep
169 dup db-assigned? [ create-function-sql , ] [ drop ] if
172 : drop-function-sql ( class -- statement )
174 "drop function add_" 0% 0%
177 [ ", " 0% ] [ type>> lookup-type 0% ] interleave
181 : drop-table-sql ( table -- statement )
183 "drop table " 0% 0% drop
186 M: postgresql-db drop-sql-statement ( class -- seq )
188 [ drop-table-sql , ] keep
189 dup db-assigned? [ drop-function-sql , ] [ drop ] if
192 M: postgresql-db <insert-db-assigned-statement> ( class -- statement )
196 dup find-primary-key first 2,
198 [ ", " 0% ] [ bind% ] interleave
202 M: postgresql-db <insert-user-assigned-statement> ( class -- statement )
206 dup [ ", " 0% ] [ column-name>> 0% ] interleave
211 dup type>> +random-id+ = [
217 ] [ type>> ] bi <generator-bind> 1,
225 M: postgresql-db insert-tuple-set-key ( tuple statement -- )
228 M: postgresql-db persistent-table ( -- hashtable )
230 { +db-assigned-id+ { "integer" "serial" f } }
231 { +user-assigned-id+ { f f f } }
232 { +random-id+ { "bigint" "bigint" f } }
234 { +foreign-id+ { f f "references" } }
236 { +on-delete+ { f f "on delete" } }
237 { +restrict+ { f f "restrict" } }
238 { +cascade+ { f f "cascade" } }
239 { +set-null+ { f f "set null" } }
240 { +set-default+ { f f "set default" } }
242 { TEXT { "text" "text" f } }
243 { VARCHAR { "varchar" "varchar" f } }
244 { INTEGER { "integer" "integer" f } }
245 { BIG-INTEGER { "bigint" "bigint" f } }
246 { UNSIGNED-BIG-INTEGER { "bigint" "bigint" f } }
247 { SIGNED-BIG-INTEGER { "bigint" "bigint" f } }
248 { DOUBLE { "real" "real" f } }
249 { DATE { "date" "date" f } }
250 { TIME { "time" "time" f } }
251 { DATETIME { "timestamp" "timestamp" f } }
252 { TIMESTAMP { "timestamp" "timestamp" f } }
253 { BLOB { "bytea" "bytea" f } }
254 { FACTOR-BLOB { "bytea" "bytea" f } }
255 { URL { "varchar" "varchar" f } }
256 { +autoincrement+ { f f "autoincrement" } }
257 { +unique+ { f f "unique" } }
258 { +default+ { f f "default" } }
259 { +null+ { f f "null" } }
260 { +not-null+ { f f "not null" } }
261 { system-random-generator { f f f } }
262 { secure-random-generator { f f f } }
263 { random-generator { f f f } }
266 ERROR: no-compound-found string object ;
267 M: postgresql-db compound ( string object -- string' )
269 { "default" [ first number>string join-space ] }
270 { "varchar" [ first number>string paren append ] }
271 { "references" [ >reference-string ] }
272 [ drop no-compound-found ]