#! /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,
    )

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()


class CSR(Base):
    __tablename__ = "requests"

    id = Column(Integer, primary_key=True)
    sha256sum = Column(Text, unique=True, nullable=False)
    pem = Column(Text, nullable=False)
    orgunit = Column(Text)
    commonname = Column(Text)

    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)

@@ -438,16 +438,38 @@
   Text,%0A
+    CHAR,%0A    String,%0A
     )%0A%0Af
@@ -822,16 +822,139 @@
 base()%0A%0A
+# Upper bounds from RFC 5280%0A_UB_CN_LEN = 64%0A_UB_OU_LEN = 64%0A%0A# Length of hex digest of a sha256 checksum%0A_SHA256_LEN = 64%0A
 %0Aclass C
@@ -1061,20 +1061,33 @@
  Column(
-Text
+CHAR(_SHA256_LEN)
 , unique
@@ -1169,20 +1169,34 @@
  Column(
-Text
+String(_UB_OU_LEN)
 )%0A    co
@@ -1213,20 +1213,34 @@
  Column(
-Text
+String(_UB_CN_LEN)
 )%0A%0A    d

Use limited length fields where appropriate.