球拍服务器和PostgreSQL - BLOB 上传/下载而不保存到内存或磁盘

Question

我正在尝试为 Racket Web 服务器制作一个 servlet，它允许用户将图片上传到网站并在同一页面上将已上传的文件显示为图像。我想直接将图片传入和传出 PostgreSQL 数据库，而不是将它们保存到磁盘或内存中的临时文件中。是否可以？如果是这样，最好的方法是什么？可以用无状态 servlet 来完成吗？非常感谢任何帮助！

Answer 1

应该是。我推荐 db 包< /a> 来自 PLAneT （因为是我写的）。您可以阅读 在线文档。

PostgreSQL 表应该有一个用于图像内容的 bytea 字段；在球拍端，它将表示为字节字符串。

在您的 servlet 中，您可能应该返回 response/full 结构与图像内容。您必须自己处理返回码、MIME 类型等。 （请参阅文档中的示例。）

Answer 2

以科学的名义，我发布了我自己问题的一半答案。此页面将显示数据库中已有的图像。上传页面仍然是一个悬而未决的问题。

瑞安·卡尔佩珀 (Ryan Culpepper) 在私人信件中为我提供了超出此处发布内容的帮助。我感谢他的帮助。所有看似黑魔法的东西都来自他，所有笨拙的错误都是我的。我将感谢所有有关如何改进代码的建议。

#lang racket
#|
================================================================================================================
We are assuming that the PostgreSQL database we are connecting to 
    has a table "person" with columns 
        "id", "firstname", "lastname" and "portrait".

The "portrait" column contains the OID of a BLOB 
    that stores the image file we want to display.

Suppose further that the table "person" has a legitimate entry with 
    id=22, firstname="John", lastname="Doe"
Then the page 
    http://127.0.0.1/page/22
should display greetings "Hello, John Doe!" 
    and show the portrait of the person below the greeting.
The portrait itself should be at 
    http://127.0.0.1/portrait/22.jpg

The program should be run via Racket -t "<filename>"
    after defining the environment variables 
        "DB_USER", "DB_NAME", "DB_PORT", "DB_PASSWORD".
================================================================================================================
|#

(require 
    web-server/servlet 
    web-server/servlet-env 
    web-server/dispatch
    web-server/stuffers/hmac-sha1
    web-server/http
    web-server/http/response-structs
    (planet ryanc/db:1:4)
    (planet ryanc/db:1:4/util/connect)
    net/base64)
;---------------------------------------------------------------------------------------------------------------
;   response
;---------------------------------------------------------------------------------------------------------------
(define (start given-request)
    (site-dispatch given-request))

(define-values (site-dispatch given-request)
    (dispatch-rules
        [("page" (integer-arg)) show-page]
        [("portrait" (string-arg)) show-portrait]))

(define (show-page given-request given-person-id)
    (let* ( [db-person_firstname_lastname
                (query-maybe-row my-connection 
                    "SELECT firstname, lastname FROM person WHERE id = $1" 
                        given-person-id)]
            [my-firstname (vector-ref db-person_firstname_lastname 0)]
            [my-lastname (vector-ref db-person_firstname_lastname 1)])
        (response/xexpr
            `(html ([xmlns "http://www.w3.org/1999/xhtml"])
                (head
                    (title "Page with a portrait"))
                (body
                    (div ([id "greetings"]) 
                        ,(string-append 
                            "Hello, " my-firstname " " my-lastname "! "))
                        (img (  [src ,(string-append "/portrait/" 
                            (number->string given-person-id) ".jpg")])))))))

(define (show-portrait given-request given-portrait-file)
    (let* ( [my-user-id (car (regexp-match #rx"^([0-9]+)" 
                given-portrait-file))]
            [my-portrait-oid (query-value my-connection 
                "SELECT portrait FROM person WHERE id = $1" 
                    (string->number my-user-id))]
            [STREAMOUT_CHUNK_SIZE 1000]
            [INV_READ #x00040000])
    (response
            200                                 ; code
            #"Okay"                             ; message
            (current-seconds)                   ; seconds
            #"image/jpeg"                       ; mime type
            empty                               ; headers
            (lambda (given-output-stream)       ; body generator
                (start-transaction my-connection)
                (define object-descriptor 
                    (query-value my-connection 
                        "SELECT LO_OPEN( $1, $2 )" my-portrait-oid INV_READ))
                (define (stream-next-chunk)
                    (begin
                        (define my-next-chunk 
                            (query-value my-connection 
                                "SELECT LOREAD( $1, $2 )" 
                                    object-descriptor STREAMOUT_CHUNK_SIZE))
                        (if (> (bytes-length my-next-chunk) 0)
                            (begin
                                (write-bytes my-next-chunk given-output-stream)
                                (stream-next-chunk)
                                #t)
                            #f)))
                (stream-next-chunk)
                (commit-transaction my-connection)))))
;---------------------------------------------------------------------------------------------------------------
;   database connection
;---------------------------------------------------------------------------------------------------------------
(define my-connection
    (virtual-connection
        (connection-pool
            (lambda ()
                (eprintf "(Re)establishing database connection...\n")
                (postgresql-connect  
                    #:user (getenv "DB_USER") 
                    #:database (getenv "DB_NAME")
                    #:port (string->number (getenv "DB_PORT"))
                    #:socket #f 
                    #:password (getenv "DB_PASSWORD")    
                    #:allow-cleartext-password? #f   
                    #:ssl 'optional  ; other choices: 'yes 'no
                    )))))
;---------------------------------------------------------------------------------------------------------------
;   servlet parameters
;---------------------------------------------------------------------------------------------------------------
(serve/servlet start
    #:command-line? #t              ; #t to use serve/servlet in a start up script for a Web application, and don't want a browser opened or the DrRacket banner printed
    #:connection-close? #f          ; #t to close every connection after one request. (Otherwise, the client decides based on what HTTP version it uses.)    
    #:launch-browser? #f     
    #:quit? #f                      ; #t makes the URL "/quit" end the server
    #:banner? #t                    ; #t to print an informative banner
    #:listen-ip #f                  ; give an IP to accept connections from external machines
    #:port 80                       ; 443 is the default for SSL, 80 - for open connections
    #:servlet-regexp #rx""          ; #rx"" captures top-level requests
    #:stateless? #t
    #:server-root-path              ; where the server files are rooted, default=(the distribution root)
        (build-path ".")
    #:ssl? #f
    #:log-file (build-path "server.log"))