sql-oo-paper.ss
(module sql-oo-paper mzscheme
  (require (lib "class.ss")
           (lib "list.ss")
           (lib "url.ss" "net")
           (lib "servlet.ss" "web-server")
           (lib "unitsig.ss")
           "yppdb-util.ss"
           (prefix sql-oo: (planet "sql-oo.ss" ("jaymccarthy" "sql-oo.plt" 1)))
           (prefix sqlite: (planet "sqlite.ss" ("jaymccarthy" "sqlite.plt" 1 5))))
  (provide paper@
           category-list
           papers/search
           papers/categories)
  
  (define-values/invoke-unit/sig sql-oo:db-addon^ 
    (sql-oo:db-class@ "object_paper"
                      (title author year categories url read notes))
    db-paper)
  
  (define paper@
    (unit/sig sql-oo:db-addon^ (import)
      (define <%> db-paper:<%>)
      (define mixin-maker db-paper:mixin-maker)
      (define %
        (class* db-paper:% (db-paper:<%>)
          (inherit categories)
          (define/public (category-list)
            (read/string (categories)))
          
          (define/override (read)
            (read/string (super read)))
          (define/override (read! new)
            (super read! (write/string new)))
          
          (super-new)))))
  
  (define (valid-field? field-string)
    (or (equal? field-string "path") (equal? field-string "author")
        (equal? field-string "year") (equal? field-string "title")
        (equal? field-string "categories") (equal? field-string "read")
        (equal? field-string "notes")))    
  (define STD-SEARCH "ORDER BY read DESC, year DESC, title ASC, author ASC")
  
  (define (papers/search db field-name value)
    (if (not (valid-field? field-name))
        (list)
        (let ([sqlite:stmt (sqlite:prepare (send db get-db)
                                           (string-append
                                            "SELECT path FROM object_paper WHERE "
                                            field-name " LIKE ? " STD-SEARCH))])
          (sqlite:load-params sqlite:stmt 
                              (if (regexp-match "%" value)
                                  value
                                  (string-append "%" value "%")))
          ; Not finalizing on purpose, it messies the code and we are doing a SELECT so the DB is not locked.
          (map (lambda (path)
                 (send db object path))
               (map car (sqlite:step* sqlite:stmt))))))
  (define (papers/categories db filtered-categories)
    (let ([sqlite:db (send db get-db)])
      (map (lambda (path)
             (send db object path))
           (map car (cdr? (sqlite:select sqlite:db
                                         (apply string-append
                                                "SELECT path FROM object_paper WHERE 1=1"
                                                (append
                                                 (map (lambda (category)
                                                        (string-append " AND categories LIKE \"%" (symbol->string category) "%\""))
                                                      filtered-categories)
                                                 (list STD-SEARCH)))))))))

  (define (category-list db)
    (let ([sqlite:db (send db get-db)])
      (quicksort
       (list/uniqued
        (apply append
               (map read/string
                    (map car (cdr? (sqlite:select sqlite:db "SELECT categories FROM object_paper"))))))
       (lambda (a b)
         (string<? (symbol->string a) (symbol->string b)))))))