#! /usr/bin/env python
# vim: expandtab shiftwidth=4 softtabstop=4 tabstop=17 filetype=python :

# Make things as three-ish as possible (requires python >= 2.6)
from __future__ import (unicode_literals, print_function,
                        absolute_import, division)
# Namespace cleanup
del unicode_literals, print_function, absolute_import, division

#
# ----- End header -----
#

from sqlalchemy import (
    Column,
    Integer,
    Text,
    CHAR,
    String,
    )

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
    )

from zope.sqlalchemy import ZopeTransactionExtension

from OpenSSL import crypto
from pyramid.decorator import reify

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()

# Upper bounds from RFC 5280
_UB_CN_LEN = 64
_UB_OU_LEN = 64

# Length of hex digest of a sha256 checksum
_SHA256_LEN = 64

class CSR(Base):
    __tablename__ = "requests"

    id = Column(Integer, primary_key=True)
    sha256sum = Column(CHAR(_SHA256_LEN), unique=True, nullable=False)
    pem = Column(Text, nullable=False)
    orgunit = Column(String(_UB_OU_LEN))
    commonname = Column(String(_UB_CN_LEN))

    def __init__(self, sha256sum, reqtext):
        # XXX: assert sha256(reqtext).hexdigest() == sha256sum ?
        self.sha256sum = sha256sum
        self.pem = reqtext
        self.req.verify(self.req.get_pubkey())
        self.orgunit = self.subject.OU
        self.commonname = self.subject.CN

    @reify
    def req(self):
        req = crypto.load_certificate_request(crypto.FILETYPE_PEM, self.pem)
        # XXX: req.verify(req.get_pubkey()) ?
        return req

    @reify
    def subject(self):
        return self.req.get_subject()

    def __json__(self, request):
        url = request.route_url("cert", sha256=self.sha256sum)
        return dict(sha256=self.sha256sum, url=url)

@@ -1928,8 +1928,380 @@
 rl=url)%0A
+%0A    def __str__(self):%0A        return (b%22%3C%7B0.__class__.__name__%7D %22 # auto-concatenation (no comma)%0A                b%22sha256sum=%7B0.sha256sum:8.8%7D... %22%0A                b%22OU=%7B0.orgunit!r%7D CN=%7B0.commonname!r%7D%3E%22).format(self)%0A%0A    def __repr__(self):%0A        return (b%22%3C%7B0.__class__.__name__%7D id=%7B0.id%7D %22 # (no comma)%0A                b%22sha256sum=%7B0.sha256sum%7D%3E%22).format(self)%0A

Add __repr__ and __str__ for interactive use