Page History

# -*- coding: utf-8 -*-

from javax.servlet.http import HttpServlet
from com.ziclix.python.sql import zxJDBC
from java.util import Properties

DSPACE_DIR = '/dspace'


class db_example(HttpServlet):
    def doGet(self, request, response):
        self.doPost(request, response)

    def doPost(self, request, response):
        response.setContentType("text/html")
        response.setCharacterEncoding("utf-8")
        toClient = response.getWriter()
        toClient.println("<h1>Example of connection to the DSpace DB via ZxJDBC</h1>")

        rows = self.get_data_from_db()
        toClient.println("<h2>Results</h2>")
        toClient.println("<table>")

        toClient.println("<tr>")
        for column in rows[0]:
            toClient.println("<th>%s</th>" % column)
        toClient.println("</tr>")

        for row in rows:
            toClient.println("<tr>")
            for column in row:
                toClient.println("<td>%s</td>" % row[column])
            toClient.println("</tr>")

        toClient.println("</table>")

    def read_dspace_config(self, filename):
        """read dspace.cfg"""
        with open(filename, 'r') as f:
            props = Properties()
            props.load(f)
            return props

    def connect_db(self):
        """
        get DB config from DSpace config, connect in autocommit mode (each
        individual query is commited automatically)
        """
        self.conn = zxJDBC.connect(
            self.props.getProperty('db.url'),
            self.props.getProperty('db.username'),
            self.props.getProperty('db.password'),
            self.props.getProperty('db.driver'),
        )
        self.conn.autocommit = True

    def init(self, config):
        """servlet startup"""
        try:
            self.props = self.read_dspace_config(DSPACE_DIR + '/config/local.cfg')
        except IOError:
            self.props = self.read_dspace_config(DSPACE_DIR + '/config/dspace.cfg')
        self.connect_db()

    def destroy(self):
        """servlet shutdown: clean up DB connections"""
        self.conn.close()

    def get_data_from_db(self):
        """
        Query the DB and return a list of rows
        where each row is a dict of column names and values
        """
        with self.conn.cursor() as c:
            c.execute("SELECT version, description FROM schema_version ORDER BY version DESC")

            columns = [col[0] for col in c.description]
            rows = []
            for row in c:
                rowdata = dict(zip(columns, row))
                rows.append(rowdata)
            return rows