eavdb.scm 21.7 KB
Newer Older
Dave Griffiths's avatar
Dave Griffiths committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
;; MongooseWeb Copyright (C) 2013 Dave Griffiths
;;
;; This program is free software: you can redistribute it and/or modify
;; it under the terms of the GNU Affero General Public License as
;; published by the Free Software Foundation, either version 3 of the
;; License, or (at your option) any later version.
;;
;; This program is distributed in the hope that it will be useful,
;; but WITHOUT ANY WARRANTY; without even the implied warranty of
;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
;; GNU Affero General Public License for more details.
;;
;; You should have received a copy of the GNU Affero General Public License
;; along with this program.  If not, see <http://www.gnu.org/licenses/>.

;; android/racket stuff
(define db-select db-exec)

Dave Griffiths's avatar
Dave Griffiths committed
19
20
21
22
23
24
25
;; racket
;(define db-exec exec/ignore)
;(define db-select select)
;(define db-insert insert)
;(define (db-status) "")
;(define (time) (list 0 0))

Dave Griffiths's avatar
Dave Griffiths committed
26
27
28
29
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; entity-attribut-value system for sqlite
;;

Dave Griffiths's avatar
Dave Griffiths committed
30

Dave Griffiths's avatar
Dave Griffiths committed
31
;; create eav tables (add types as required)
32
(define (setup db table)
Dave Griffiths's avatar
Dave Griffiths committed
33
34
35
36
37
  (db-exec db (string-append "create table " table "_entity ( entity_id integer primary key autoincrement, entity_type varchar(256), unique_id varchar(256), dirty integer, version integer)"))
  (db-exec db (string-append "create table " table "_attribute ( id integer primary key autoincrement, attribute_id varchar(256), entity_type varchar(256), attribute_type varchar(256))"))
  (db-exec db (string-append "create table " table "_value_varchar ( id integer primary key autoincrement, entity_id integer, attribute_id varchar(255), value varchar(4096), dirty integer)"))
  (db-exec db (string-append "create table " table "_value_int ( id integer primary key autoincrement, entity_id integer, attribute_id varchar(255), value integer, dirty integer)"))
  (db-exec db (string-append "create table " table "_value_real ( id integer primary key autoincrement, entity_id integer, attribute_id varchar(255), value real, dirty integer)")))
Dave Griffiths's avatar
Dave Griffiths committed
38

Dave Griffiths's avatar
Dave Griffiths committed
39
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Dave Griffiths's avatar
Dave Griffiths committed
40
41
42
43
44
45
46

;; basic key/type/value structure
(define (ktv key type value) (list key type value))
(define ktv-key car)
(define ktv-type cadr)
(define ktv-value caddr)

Dave Griffiths's avatar
Dave Griffiths committed
47
;; stringify based on type (for url)
Dave Griffiths's avatar
Dave Griffiths committed
48
49
(define (stringify-value ktv)
  (cond
50
51
52
53
54
55
   ((null? (ktv-value ktv)) "NULL")
   ((equal? (ktv-type ktv) "varchar") (string-append "'" (ktv-value ktv) "'"))
   (else
    (if (not (string? (ktv-value ktv)))
        (number->string (ktv-value ktv))
        (ktv-value ktv)))))
Dave Griffiths's avatar
Dave Griffiths committed
56

Dave Griffiths's avatar
Dave Griffiths committed
57
58
59
60
61
62
63
64
65
66
67
;; stringify based on type (for url)
(define (stringify-value-url ktv)
  (cond
   ((null? (ktv-value ktv)) "NULL")
   ((equal? (ktv-type ktv) "varchar") (ktv-value ktv))
   (else
    (if (not (string? (ktv-value ktv)))
        (number->string (ktv-value ktv))
        (ktv-value ktv)))))


Dave Griffiths's avatar
Dave Griffiths committed
68
;; helper to return first instance from a select
Dave Griffiths's avatar
Dave Griffiths committed
69
70
(define (select-first db str . args)
  (let ((s (apply db-select (append (list db str) args))))
Dave Griffiths's avatar
Dave Griffiths committed
71
72
73
74
75
76
77
78
    (if (or (null? s) (eq? s #t))
        '()
        (vector-ref (cadr s) 0))))

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; putting data in

;; get the type from the attribute table with an entity/key
79
(define (get-attribute-type db table entity-type key)
Dave Griffiths's avatar
Dave Griffiths committed
80
  (let ((sql (string-append
Dave Griffiths's avatar
Dave Griffiths committed
81
82
83
              "select attribute_type from " table
              "_attribute where entity_type = ? and attribute_id = ?")))
    (select-first db sql entity-type key)))
Dave Griffiths's avatar
Dave Griffiths committed
84
85

;; search for a type and add it if it doesn't exist
86
87
(define (find/add-attribute-type db table entity-type key type)
  (let ((t (get-attribute-type db table entity-type key)))
Dave Griffiths's avatar
Dave Griffiths committed
88
89
90
91
92
    ;; add and return passed in type if not exist
    (cond
      ((null? t)
       (msg "adding new attribute for" entity-type " called " key " of type " type)
       (db-insert
Dave Griffiths's avatar
Dave Griffiths committed
93
94
        db (string-append "insert into " table "_attribute values (null, ?, ?, ?)")
        key entity-type type)
Dave Griffiths's avatar
Dave Griffiths committed
95
96
97
98
99
100
101
102
103
104
105
106
       type)
      (else
       (cond
         ((equal? type t) t)
         (else
          (msg "type has changed for" entity-type key "from" t "to" type "???")
          ;; wont work
          ;; what do we do?
          ;; some kind of coercion for existing data???
          type))))))

;; low level insert of a ktv
107
(define (insert-value db table entity-id ktv)
Dave Griffiths's avatar
Dave Griffiths committed
108
  ;; use type to dispatch insert to correct value table
Dave Griffiths's avatar
Dave Griffiths committed
109
110
111
  (db-insert db (string-append "insert into " table "_value_" (ktv-type ktv)
                               " values (null, ?, ?, ?, 0)")
             entity-id (ktv-key ktv) (ktv-value ktv)))
112
113
114
115
116
117

(define (get-unique user)
  (let ((t (time)))
    (string-append
     user "-" (number->string (car t)) ":" (number->string (cadr t)))))

Dave Griffiths's avatar
Dave Griffiths committed
118
;; insert an entire entity
119
(define (insert-entity db table entity-type user ktvlist)
Dave Griffiths's avatar
Dave Griffiths committed
120
  (insert-entity-wholesale db table entity-type (get-unique user) 1 0 ktvlist))
Dave Griffiths's avatar
Dave Griffiths committed
121

122
123
124
125
126
127
;; insert an entire entity
(define (insert-entity/get-unique db table entity-type user ktvlist)
  (let ((uid (get-unique user)))
    (insert-entity-wholesale db table entity-type uid 1 0 ktvlist)
    uid))

Dave Griffiths's avatar
Dave Griffiths committed
128
129
;; all the parameters - for syncing purposes
(define (insert-entity-wholesale db table entity-type unique-id dirty version ktvlist)
Dave Griffiths's avatar
Dave Griffiths committed
130
131
  (let ((id (db-insert
             db (string-append
Dave Griffiths's avatar
Dave Griffiths committed
132
133
                 "insert into " table "_entity values (null, ?, ?, ?, ?)")
             entity-type unique-id dirty version)))
Dave Griffiths's avatar
Dave Griffiths committed
134
135
136
    ;; create the attributes if they are new, and validate them if they exist
    (for-each
     (lambda (ktv)
137
       (find/add-attribute-type db table entity-type (ktv-key ktv) (ktv-type ktv)))
Dave Griffiths's avatar
Dave Griffiths committed
138
139
140
141
     ktvlist)
    ;; add all the keys
    (for-each
     (lambda (ktv)
142
       (insert-value db table id ktv))
Dave Griffiths's avatar
Dave Griffiths committed
143
144
     ktvlist)
    id))
Dave Griffiths's avatar
Dave Griffiths committed
145

146

147
;; update the value given an entity type, a attribute type and it's key (= attriute_id)
148
;; creates the value if it doesn't already exist, updates it otherwise
149
(define (update-value db table entity-id ktv)
Dave Griffiths's avatar
Dave Griffiths committed
150
151
152
153
154
  (if (null? (select-first
              db (string-append
                  "select * from " table "_value_" (ktv-type ktv) " where entity_id = ? and attribute_id = ?")
              entity-id (ktv-key ktv)))
      (insert-value db table entity-id ktv)
155
156
157
158
      (db-exec
       db (string-append "update " table "_value_" (ktv-type ktv)
                         " set value=?  where entity_id = ? and attribute_id = ?")
       (ktv-value ktv) entity-id (ktv-key ktv))))
159

Dave Griffiths's avatar
Dave Griffiths committed
160
161
162
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; getting data out

Dave Griffiths's avatar
Dave Griffiths committed
163
(define (entity-exists? db table unique-id)
Dave Griffiths's avatar
Dave Griffiths committed
164
165
166
167
  (not (null? (select-first
               db (string-append
                   "select * from " table "_entity where unique_id = ?")
               unique-id))))
Dave Griffiths's avatar
Dave Griffiths committed
168

169
(define (get-entity-type db table entity-id)
Dave Griffiths's avatar
Dave Griffiths committed
170
171
  (select-first
   db (string-append
Dave Griffiths's avatar
Dave Griffiths committed
172
173
       "select entity_type from " table "_entity where entity_id = ?")
       entity-id))
Dave Griffiths's avatar
Dave Griffiths committed
174

175
176
177
(define (get-all-entity-types db table)
  (cdr (db-select db (string-append "select distinct entity_type from " table "_entity;"))))

Dave Griffiths's avatar
Dave Griffiths committed
178
;; get all the (current) attributes for an entity type
179
(define (get-attribute-ids/types db table entity-type)
Dave Griffiths's avatar
Dave Griffiths committed
180
181
  (let ((s (db-select
            db (string-append
Dave Griffiths's avatar
Dave Griffiths committed
182
183
                "select * from " table "_attribute where entity_type = ?")
                entity-type)))
Dave Griffiths's avatar
Dave Griffiths committed
184
185
186
187
188
189
190
191
    (if (null? s) '()
        (map
         (lambda (row)
           (list (vector-ref row 1)    ;; id
                 (vector-ref row 3)))  ;; type
         (cdr s)))))

;; get the value given an entity type, a attribute type and it's key (= attriute_id)
192
(define (get-value db table entity-id kt)
Dave Griffiths's avatar
Dave Griffiths committed
193
  (select-first
Dave Griffiths's avatar
Dave Griffiths committed
194
195
196
   db (string-append "select value from " table "_value_" (ktv-type kt)
                     " where entity_id = ? and attribute_id = ?")
   entity-id (ktv-key kt)))
Dave Griffiths's avatar
Dave Griffiths committed
197
198

;; get an entire entity, as a list of key/value pairs
Dave Griffiths's avatar
Dave Griffiths committed
199
200
201
202
203
204
205
206
207
208
209
(define (get-entity-plain db table entity-id)
  (let* ((entity-type (get-entity-type db table entity-id)))
    (cond
      ((null? entity-type) (msg "entity" entity-id "not found!") '())
      (else
       (map
        (lambda (kt)
          (list (ktv-key kt) (ktv-type kt) (get-value db table entity-id kt)))
        (get-attribute-ids/types db table entity-type))))))

;; get an entire entity, as a list of key/value pairs (includes entity id)
210
(define (get-entity db table entity-id)
Dave Griffiths's avatar
Dave Griffiths committed
211
212
213
214
  (let ((unique-id (get-unique-id db table entity-id)))
    (cons
     (list "unique_id" "varchar" unique-id)
     (get-entity-plain db table entity-id))))
215

216
217
(define (all-entities db table type)
  (let ((s (db-select
218
219
220
221
222
223
            db (string-append "select e.entity_id from " table "_entity as e "
                              "join " table "_value_varchar "
                              " as n on n.entity_id = e.entity_id "
                              "where entity_type = ? and n.attribute_id = ? order by n.value")
            type "name")))
    (msg (db-status db))
224
225
226
227
    (if (null? s)
        '()
        (map
         (lambda (i)
Dave Griffiths's avatar
Dave Griffiths committed
228
           (vector-ref i 0))
229
         (cdr s)))))
Dave Griffiths's avatar
Dave Griffiths committed
230

231
232
233
234
235
236
(define (all-entities-where db table type ktv)
  (let ((s (db-select
            db (string-append
                "select e.entity_id from " table "_entity as e "
                "join " table "_value_" (ktv-type ktv)
                " as a on a.entity_id = e.entity_id "
237
238
239
240
241
                "join " table "_value_varchar "
                " as n on n.entity_id = e.entity_id "
                "where e.entity_type = ? and a.attribute_id = ? "
                "and a.value = ? and n.attribute_id = ? order by n.value")
            type (ktv-key ktv) (ktv-value ktv) "name")))
242
243
244
245
246
247
248
249
    (msg (db-status db))
    (if (null? s)
        '()
        (map
         (lambda (i)
           (vector-ref i 0))
         (cdr s)))))

250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
(define (all-entities-where2 db table type ktv ktv2)
  (let ((s (db-select
            db (string-append
                "select e.entity_id from " table "_entity as e "
                "join " table "_value_" (ktv-type ktv)
                " as a on a.entity_id = e.entity_id "
                "join " table "_value_" (ktv-type ktv2)
                " as b on b.entity_id = e.entity_id "
                "where e.entity_type = ? and a.attribute_id = ? and b.attribute_id =? and a.value = ? and b.value = ? ")
            type (ktv-key ktv) (ktv-key ktv2) (ktv-value ktv) (ktv-value ktv2))))
    (msg (db-status db))
    (if (null? s)
        '()
        (map
         (lambda (i)
           (vector-ref i 0))
         (cdr s)))))

(define (all-entities-where-newer db table type ktv ktv2)
  (let ((s (db-select
            db (string-append
                "select e.entity_id from " table "_entity as e "
                "join " table "_value_" (ktv-type ktv)
                " as a on a.entity_id = e.entity_id "
                "join " table "_value_" (ktv-type ktv2)
                " as b on b.entity_id = e.entity_id "
Dave Griffiths's avatar
Dave Griffiths committed
276
277
278
279
280
281
                "where e.entity_type = ? "
                "and a.attribute_id = ? and a.value = ? "
                "and b.attribute_id = ? and b.value > DateTime(?)"
                )
            type (ktv-key ktv) (ktv-value ktv) (ktv-key ktv2) (ktv-value ktv2))))
    (msg "date select" (db-status db))
282
283
284
285
286
287
    (if (null? s)
        '()
        (map
         (lambda (i)
           (vector-ref i 0))
         (cdr s)))))
288

289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
(define (all-entities-where-older db table type ktv ktv2)
  (let ((s (db-select
            db (string-append
                "select e.entity_id from " table "_entity as e "
                "join " table "_value_" (ktv-type ktv)
                " as a on a.entity_id = e.entity_id "
                "join " table "_value_" (ktv-type ktv2)
                " as b on b.entity_id = e.entity_id "
                "where e.entity_type = ? "
                "and a.attribute_id = ? and a.value = ? "
                "and b.attribute_id = ? and b.value < DateTime(?)"
                )
            type (ktv-key ktv) (ktv-value ktv) (ktv-key ktv2) (ktv-value ktv2))))
    (msg "date select" (db-status db))
    (if (null? s)
        '()
        (map
         (lambda (i)
           (vector-ref i 0))
         (cdr s)))))


Dave Griffiths's avatar
Dave Griffiths committed
311
312
313
314
315
316
317
318
319
320
321
322
323
324
(define (validate db)
  ;; check attribute for duplicate entity-id/attribute-ids
  0)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; helpers

(define (ktv-get ktv-list key)
  (cond
   ((null? ktv-list) #f)
   ((equal? (ktv-key (car ktv-list)) key)
    (ktv-value (car ktv-list)))
   (else (ktv-get (cdr ktv-list) key))))

325
326
327
328
329
(define (ktv-set ktv-list ktv)
  (cond
   ((null? ktv-list) (list ktv))
   ((equal? (ktv-key (car ktv-list)) (ktv-key ktv))
    (cons ktv (cdr ktv-list)))
330
   (else (cons (car ktv-list) (ktv-set (cdr ktv-list) ktv)))))
331
332


333
(define (db-all db table type)
334
335
  (prof-start "db-all")
  (let ((r (map
Dave Griffiths's avatar
Dave Griffiths committed
336
   (lambda (i)
337
     (get-entity db table i))
338
339
340
   (all-entities db table type))))
    (prof-end "db-all")
    r))
Dave Griffiths's avatar
Dave Griffiths committed
341

342
343
344
345
346
347
348
349
350
351
352
353
354
;(define (db-all-where db table type clause)
;  (prof-start "db-all-where")
;  (let ((r (foldl
;            (lambda (i r)
;              (let ((e (get-entity db table i)))
;                (if (equal? (ktv-get e (car clause)) (cadr clause))
;                    (cons e r) r)))
;            '()
;            (all-entities db table type))))
;    (prof-end "db-all-where")
;    r))

(define (db-all-where db table type ktv)
355
  (prof-start "db-all-where")
356
357
358
359
  (let ((r (map
            (lambda (i)
              (get-entity db table i))
            (all-entities-where db table type ktv))))
360
361
362
    (prof-end "db-all-where")
    r))

363
(define (db-all-where2 db table type ktv ktv2)
364
365
366
367
  (prof-start "db-all-where2")
  (let ((r (map
            (lambda (i)
              (get-entity db table i))
368
            (all-entities-where2 db table type ktv ktv2))))
369
370
    (prof-end "db-all-where2")
    r))
371

372
373
374
375
376
377
378
379
380
(define (db-all-where2 db table type ktv ktv2)
  (prof-start "db-all-where2")
  (let ((r (map
            (lambda (i)
              (get-entity db table i))
            (all-entities-where2 db table type ktv ktv2))))
    (prof-end "db-all-where2")
    r))

Dave Griffiths's avatar
Dave Griffiths committed
381
382
383
384
385
386
387
388
389
(define (db-all-newer db table type ktv ktv2)
  (prof-start "db-all-where newer")
  (let ((r (map
            (lambda (i)
              (get-entity db table i))
            (all-entities-where-newer db table type ktv ktv2))))
    (prof-end "db-all-where newer")
    r))

390
391
392
393
394
395
396
397
398
(define (db-all-older db table type ktv ktv2)
  (prof-start "db-all-where older")
  (let ((r (map
            (lambda (i)
              (get-entity db table i))
            (all-entities-where-older db table type ktv ktv2))))
    (prof-end "db-all-where older")
    r))

399

400
401
402
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; updating data

403
404
;; update an entire entity (version incl), via a (possibly partial) list of key/value pairs
(define (update-to-version db table entity-id version ktvlist)
Dave Griffiths's avatar
Dave Griffiths committed
405
  (update-entity-values db table entity-id ktvlist)
406
407
408
  (update-entity-version db table entity-id version))

;; auto update version
409
(define (update-entity db table entity-id ktvlist)
410
  (update-entity-changed db table entity-id)
Dave Griffiths's avatar
Dave Griffiths committed
411
  (update-entity-values db table entity-id ktvlist))
412
413

;; update an entity, via a (possibly partial) list of key/value pairs
Dave Griffiths's avatar
Dave Griffiths committed
414
(define (update-entity-values db table entity-id ktvlist)
415
416
  (let* ((entity-type (get-entity-type db table entity-id)))
    (cond
Dave Griffiths's avatar
Dave Griffiths committed
417
418
     ((null? entity-type) (msg "entity" entity-id "not found!") '())
     (else
419
420
421
422
423
424
      ;; update main entity type
      (for-each
       (lambda (ktv)
         (when (not (equal? (ktv-key ktv) "unique_id"))
               (find/add-attribute-type db table entity-type (ktv-key ktv) (ktv-type ktv))))
       ktvlist)
Dave Griffiths's avatar
Dave Griffiths committed
425
426
427
428
      (for-each
       (lambda (ktv)
         (update-value db table entity-id ktv))
       ktvlist)))))
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446

;; update or create an entire entity if it doesn't exist
;; will return the new entity id if it's created
(define (update/insert-entity db table entity-type user entity-id ktvlist)
  (let* ((entity-type (get-entity-type db table entity-id)))
    (cond
     ((null? entity-type)
      (insert-entity db table entity-type user ktvlist))
     (else
      (update-entity db table entity-id ktvlist)
      #f))))

(define (insert-entity-if-not-exists db table entity-type user entity-id ktvlist)
  (let ((found (get-entity-type db table entity-id)))
    (if (null? found)
        (insert-entity db table entity-type user ktvlist)
        #f)))

Dave Griffiths's avatar
Dave Griffiths committed
447
448
449
450
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; versioning

(define (get-entity-version db table entity-id)
Dave Griffiths's avatar
Dave Griffiths committed
451
452
453
  (select-first
   db (string-append "select version from " table "_entity where entity_id = ?")
   entity-id))
Dave Griffiths's avatar
Dave Griffiths committed
454
455

(define (get-entity-dirty db table entity-id)
Dave Griffiths's avatar
Dave Griffiths committed
456
457
458
  (select-first
   db (string-append "select dirty from " table "_entity where entity_id = ?")
   entity-id))
Dave Griffiths's avatar
Dave Griffiths committed
459
460
461
462
463
464
465
466
467
468
469

(define (update-entity-changed db table entity-id)
  (db-exec
   db (string-append
       "update " table "_entity set dirty=?, version=? where entity_id = ?")
   1 (+ 1 (get-entity-version db table entity-id)) entity-id))

(define (update-entity-version db table entity-id version)
  (db-exec
   db (string-append
       "update " table "_entity set dirty=?, version=? where entity_id = ?")
Dave Griffiths's avatar
Dave Griffiths committed
470
   1 version entity-id))
Dave Griffiths's avatar
Dave Griffiths committed
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496

(define (update-entity-clean db table unique-id)
  (db-exec
   db (string-append "update " table "_entity set dirty=? where unique_id = ?")
   0 unique-id))

(define (get-dirty-stats db table)
  (list
   (select-first
    db (string-append "select count(entity_id) from " table "_entity where dirty=1"))
   (select-first
    db (string-append "select count(entity_id) from " table "_entity;"))))

(define (dirty-entities db table)
  (let ((de (db-select
             db (string-append
                 "select entity_id, entity_type, unique_id, dirty, version from " table "_entity where dirty=1;"))))
    (if (null? de)
        '()
        (map
         (lambda (i)
           (list
            ;; build according to url ([table] entity-type unique-id dirty version)
            (cdr (vector->list i))
            ;; data entries (todo - only dirty values!)
            (get-entity-plain db table (vector-ref i 0))))
Dave Griffiths's avatar
Dave Griffiths committed
497
         (cdr de)))))
Dave Griffiths's avatar
Dave Griffiths committed
498

499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
(define (dirty-and-all-entities db table)
  (let ((de (db-select
             db (string-append
                 "select entity_id, entity_type, unique_id, dirty, version from " table "_entity"))))
    (if (null? de)
        '()
        (map
         (lambda (i)
           (list
            ;; build according to url ([table] entity-type unique-id dirty version)
            (cdr (vector->list i))
            ;; data entries (todo - only dirty values!)
            (get-entity-plain db table (vector-ref i 0))))
         (cdr de)))))


515
516
517
518
519
520
521
522
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; syncing

(define (stringify-list l)
  (foldl
   (lambda (i r)
     (string-append r " " i))
   "" l))
523

524
525
526
527
528
529
(define (stringify-ktvlist ktvlist)
  (foldl
   (lambda (i r)
     (string-append r " " (ktv-key i) ":" (stringify-value i)))
   ""
   ktvlist))
530

531
532
533
534
535
(define (build-sync-debug db table)
  (foldl
   (lambda (i r)
     (string-append
      r "\n" (vector-ref i 0) " " (vector-ref i 1) " "
Dave Griffiths's avatar
Dave Griffiths committed
536
      (stringify-ktvlist (get-entity db table (vector-ref i 0)))))
537
538
539
540
541
   ""
   (cdr (db-select
         db (string-append "select * from " table "_entity where dirty=1;")))))


542
543
544
545
546
(define (build-sync db table)
  (map
   (lambda (i)
     (list
      (vector->list i)
Dave Griffiths's avatar
Dave Griffiths committed
547
      (get-entity db table (vector-ref i 0))))
548
549
   (cdr (db-select
         db (string-append "select * from " table "_entity where dirty=1;")))))
Dave Griffiths's avatar
Dave Griffiths committed
550
551
552
553
554
555

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; doing things with unique ids

(define (entity-id-from-unique db table unique-id)
  (select-first
Dave Griffiths's avatar
Dave Griffiths committed
556
557
   db (string-append "select entity_id from " table "_entity where unique_id = ?")
   unique-id))
Dave Griffiths's avatar
Dave Griffiths committed
558
559
560

(define (entity-version-from-unique db table unique-id)
  (select-first
Dave Griffiths's avatar
Dave Griffiths committed
561
562
   db (string-append "select version from " table "_entity where unique_id = ?")
   unique-id))
563
564
565


(define (get-unique-id db table entity-id)
Dave Griffiths's avatar
Dave Griffiths committed
566
567
568
569
  (select-first
   db (string-append
       "select unique_id from " table "_entity where entity_id = ?")
       entity-id))
570
571

(define (get-entity-id db table unique-id)
Dave Griffiths's avatar
Dave Griffiths committed
572
573
574
575
  (select-first
   db (string-append
       "select entity_id from " table "_entity where unique_id = ?")
   unique-id))
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593

(define (get-entity-name db table unique-id)
  (ktv-get (get-entity db table (get-entity-id db table unique-id)) "name"))

(define (get-entity-names db table id-list)
  (foldl
   (lambda (id r)
     (if (equal? r "")
         (get-entity-name db table id)
         (string-append r ", " (get-entity-name db table id))))
   ""
   id-list))

(define (csv-titles db table entity-type)
  (foldl
   (lambda (kt r)
     (if (equal? r "") (string-append "\"" (ktv-key kt) "\"")
         (string-append r ", \"" (ktv-key kt) "\"")))
594
   "id, "
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
   (get-attribute-ids/types db table entity-type)))

(define (csv db table entity-type)
  (foldl
   (lambda (res r)
     (let ((entity (get-entity db table (vector-ref res 0))))
       (string-append
        r "\n"
        (foldl
         (lambda (ktv r)
           (cond
            ((equal? (ktv-key ktv) "unique_id") r)
            ((null? (ktv-value ktv))
             (msg "value not found in csv for " (ktv-key ktv))
             r)
            ;; dereferences lists of ids
            ((and
              (> (string-length (ktv-key ktv)) 8)
              (equal? (substring (ktv-key ktv) 0 8) "id-list-"))
             (string-append r ", \"" (get-entity-names db "sync" (string-split (ktv-value ktv) '(#\,))) "\""))
            ;; look for unique ids and dereference them
            ((and
              (> (string-length (ktv-key ktv)) 3)
              (equal? (substring (ktv-key ktv) 0 3) "id-"))
             (string-append r ", \"" (get-entity-name db "sync" (ktv-value ktv)) "\""))
            (else
621
622
             (string-append r ", \"" (stringify-value-url ktv) "\""))))
         (vector-ref res 1) ;; unique_id
623
624
625
626
         entity))))
   (csv-titles db table entity-type)
   (cdr (db-select
         db (string-append
627
             "select entity_id, unique_id from "
628
             table "_entity where entity_type = ?") entity-type))))