source: TI01-discovery/branches/ingestAutomation-upgrade/OAIBatch/PostgresDAO.py @ 3808

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/TI01-discovery/branches/ingestAutomation-upgrade/OAIBatch/PostgresDAO.py@3808
Revision 3808, 8.0 KB checked in by cbyrom, 12 years ago (diff)

Rename class - DAL -> DAO (data access object) - to make more correctly identified
+ implement most of the basic Insert/Update? code.

  • Property svn:executable set to *
Line 
1#!/usr/bin/env python
2'''
3Class representing the data access object wrapping the Postgres DB
4C Byrom Apr 08
5'''
6import molesReadWrite as MRW
7import sys
8import os
9import db_funcs
10
11class PostgresDAO:
12   
13    def __init__(self, record):
14        '''
15        Constructor - to initialise the DAL and do some initial setting up
16        @param record: the PostgresRecord object to add or update in the DB
17        '''
18        if record == "":
19            sys.exit("USAGE: argument 1 = PostgresRecord object to process")
20        else:
21            print "INFO: Creating/updating DB entry for record, %s" %record.id
22
23        # setup a connection to the db
24        self._connection = db_connect()
25        self._record = record
26       
27           
28    def getRecordID(self):
29        '''
30        Looks up a record in the DB and returns its DB ID, if it exists, otherwise
31        returns '-1'
32        @return: id of record, if it exists, '-1' if it doesn't
33        '''
34        sql = "select id from spatiotemp where id = '" + self._record.id + "';"
35        id = db_funcs.runSQLCommand(self._connection, sql)
36        print "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXKKKK", id
37        self.id = id
38        return id
39
40    def createOrUpdateRecord(self):
41        '''
42        Looks up a record in the DB; if it finds it, update it, otherwise create it
43        '''
44        if getRecordID() > 0:
45            createRecord()
46        else:
47            updateRecord()
48           
49    def createRecord(self):
50        '''
51        Create a record in the postgres DB based on the specified PostgresRecord object
52        '''
53        # firstly store the actual documents contained by the record - i.e. the original doc +
54        # the various transforms required
55        _insertMetadataRecords()
56       
57        # Now add the spatiotemporal data
58        _insertSpatioTemporalData()
59           
60
61    def updateRecord(self):
62        '''
63        Update a record in the postgres DB based on the specified PostgresRecord object
64        '''
65        # firstly update the actual documents contained by the record - i.e. the original doc +
66        # the various transforms required
67        _updateMetadataRecords(record)
68       
69        # Now update the spatiotemporal data
70        _updateSpatioTemporalData(record)
71       
72       
73   
74    def _insertSpatioTemporalData():
75        '''
76        Create a record in the postgres DB based on the spatiotemporal data
77        specified in the PostgresRecord object
78        '''
79        # NB, build the query up in two parts so that we only include the correct data
80        sqlStart = ""
81        sqlEnd = " VALUES (DEFAULT, '" + self._record.id
82        if (not self._record.hasNullCoords()):
83            sqlStart += "coordinates"
84            sqlEnd += "', sbox'((" + self._record.west + \
85                "d , " + self._record.south + "d), (" + self._record.east +"d , " + \
86                self._record.north + "d))' "
87       
88        if str(self._record.startdate)!='nostartdate':
89            if len(sqlStart) > 0:
90                sqlStart += ", "
91                sqlEnd += ", "
92            sqlStart += "startdate"
93            sqlEnd += "'" + self._record.startdate + "'"
94           
95        if str(self._record.enddate)!='noenddate':
96            if len(sqlStart) > 0:
97                sqlStart += ", "
98                sqlEnd += ", "
99            sqlStart += ", enddate"
100            sqlEnd += ", '" + self._record.enddate + "'"
101
102        if len(sqlStart) > 0:
103            sqlStart = ", " + sqlStart
104            sqlEnd = ", " + sqlEnd
105        sqlCmd = "INSERT INTO spatiotemp (id, original_doc_id" + sqlStart + ") " + sqlEnd + ");"
106        print sqlCmd
107        db_funcs.runSQLCommand(self._connection, sqlCmd)
108           
109   
110    def _updateSpatioTemporalData():
111        '''
112        Updates a record in the postgres DB based on the spatiotemporal data
113        specified in the PostgresRecord object
114        '''
115        # NB, build the query up in two parts so that we only include the correct data
116        sqlStart = ""
117        sqlEnd = " WHERE original_doc_id='" + self._record.id + "';" 
118        if (not self._record.hasNullCoords()):
119            sqlStart += "coordinates = sbox'((" + self._record.west + "d , " + \
120                self._record.south + "d), (" + self._record.east + "d , " + \
121                self._record.north + "d))'"
122       
123        if str(self._record.startdate)!='nostartdate':
124            if len(sqlStart) > 0:
125                sqlStart += ", "
126            sqlStart += "startdate = '" + self._record.startdate + "'"
127           
128        if str(self._record.enddate)!='noenddate':
129            if len(sqlStart) > 0:
130                sqlStart += ", "
131            sqlStart += "enddate = '" + enddate + "'"
132
133        if len(sqlStart) > 0:
134            sqlStart += ", "
135        sqlCmd = "UPDATE spatiotemp SET (" + sqlStart + "update_time= now()) " + sqlEnd + ");"
136
137        print sqlCmd
138        db_funcs.runSQLCommand(self._connection, sqlCmd)
139   
140           
141    def _insertOriginalRecord():
142        '''
143        Insert the original metadata doc into the postgres DB
144        '''
145        print "INFO: Inserting new original document in Postgres DB"
146        sqlCmd = "INSERT INTO ORIGINAL_DOCUMENT (original_document_id, original_document_name, original_format, " \
147            "original_document, ts_vector, create_date, harvest_count, scn) VALUES (" \
148            "DEFAULT, '" + self._record.filename + "', '" + self._record.docType + "', '" + self._record.originalFormat + \
149            "', to_tsvector('english', " + self._record.originalFormat + "), current_date, 1, 1);"
150           
151        id = db_funcs.runSQLCommand(self._connection, sqlCmd)
152        self.id = id
153        self._record.db_id = id
154       
155   
156   
157    def _updateOriginalRecord():
158        '''
159        Update the original doc in the postgres DB
160        '''
161        print "INFO: Updating original document in Postgres DB"
162        sqlCmd = "UPDATE ORIGINAL_DOCUMENT SET (original_document_name = '" + self._record.filename + "', " \
163            "original_format = '" + self._record.originalFormat + "', " \
164            "ts_vector = to_tsvector('english', " + self._record.originalFormat + "), " \
165            "update_date = current_date, " \
166            "harvest_count = 1)" \
167            " WHERE original_document_id = " + self._record.db_id + ";"
168   
169        db_funcs.runSQLCommand(self._connection, sqlCmd)
170   
171           
172    def _insertMetadataRecords():
173        '''
174        Insert the metadata docs into the postgres DB
175        '''
176        print "INFO: Inserting transformed documents for original document, %s, in Postgres DB", self._record.filename
177        if self._record.db_id is None:
178            print "No DB ID for the original record exists; cannot add associated transformed docs"
179            return
180       
181        for docType, doc in record.getAllDocs():
182            sqlCmd = "INSERT INTO TRANSFORMED_DOCUMENT (transformed_document_id, " \
183                "original_record_id, transformed_format, " \
184                "transformed_document, create_date, scn) VALUES (" \
185                "DEFAULT, '" + self._record.db_id + "', '" + \
186                docType + "', '" + doc + "'), current_date, 1);"
187       
188   
189   
190    def _updateMetadataRecords():
191        '''
192        Update the metadata docs into the postgres DB
193        '''
194        print "INFO: Inserting transformed documents for original document, %s, in Postgres DB", self._record.filename
195        if self._record.db_id is None:
196            print "No DB ID for the original record exists; cannot update associated transformed docs"
197            return
198       
199        for docType, doc in record.getAllDocs():
200            sqlCmd = "UPDATE TRANSFORMED_DOCUMENT SET (transformed_document = '" + doc + \
201                "', update_date = current_date) WHERE original_record_id = " + \
202                self._record.db_id + " AND transformed_format = '" + docType + "';"
203   
204
205    def setRecord(self, record):
206        '''
207        Set the record to use with the DAL - to allow object re-use
208        @param record: PostgresRecord to use with the DAL
209        '''
210        self._record = record
211        self.getSpatioTemporalData()
212        self.doRecordTransforms()
Note: See TracBrowser for help on using the repository browser.