tools/anonym/xmlexport.py

#!/usr/bin/python
#
# (c) 2008 by Intevation GmbH
# author: Sascha L. Teichmann ([email protected])
#
import sys
#import os
import codecs
#import getopt
import psycopg2        as dbapi
import psycopg2.extras as extras
from   types import StringTypes

dbapi.extensions.register_type(dbapi.extensions.UNICODE)

DBNAME   = "ka_inte00002_db"
HOST     = "laodameia"
PORT     = 5434
USER     = "postgres"
PASSWORD = ""

ANON_ON = \
"""SELECT wert FROM ka_konfiguration_tbl
   WHERE bez = 'anon_transfer'"""

SELECT_MASTER_IDS = \
"""SELECT id from master_tbl_anonym_view"""

SELECT_MASTER_TBL = \
"""SELECT * FROM master_tbl_anonym_view WHERE id = %(id)s"""

RG_VIEWS = (
    "kompetenzfestellung",
    "angebote_berufliche_qualifizierung",
    "angebote_berufsvorbereitung",
    "angebote_bildenden_bereich",
    "angebote_lebensbewaeltigung")

SELECT_RG = \
"""SELECT * FROM %s WHERE master_id = %d"""

BLACK_LIST = set(['id', 'master_id', 'bearbeiter_id'])

def escape(value, tagname):
    if tagname == 'uuid_id':
        return "%s" % value
    if type(value) in StringTypes:
        return "<![CDATA[%s]]>" % value.replace("]]>", "]]><!CDATA[]]]]><![CDATA[>]]><[!CDATA[")
    if value is None:
        return ""
    return "%s" % value

def dumpColums(columns):
    out = []
    for tagname, value in columns:
        if tagname not in BLACK_LIST and not value is None:
            out.append("<%s>%s</%s>" % (tagname, escape(value, tagname), tagname))
    return out

class Case:

    def __init__(self, master_id):
        self.master_id = int(master_id)
        self.columns   = None
        self.rgs       = []

    def fetchData(self, cur):
        self.fetchMasterTable(cur)
        self.fetchRepeatGroups(cur)

    def fetchMasterTable(self, cur):
        cur.execute(SELECT_MASTER_TBL, { 'id': self.master_id })
        row = cur.fetchone()
        if not row: raise Exception("No dataset for id %d" % self.master_id)
        columns = row._index.items() # XXX: hackish!
        columns.sort(lambda a, b: cmp(a[1], b[1]))
        columns = [(a[0], row[a[1]]) for a in columns]
        self.columns = columns

    def fetchRepeatGroups(self, cur):
        for view in RG_VIEWS:
            viewname = "rg_%s_tbl_anonym_view" % view
            cur.execute(SELECT_RG % (viewname, self.master_id))
            rs = []
            while True:
                row = cur.fetchone()
                if not row: break
                cs = row._index.items() # XXX: hackish!
                cs.sort(lambda a, b: cmp(a[1], b[1]))
                cs = [(a[0], row[a[1]]) for a in cs]
                rs.append(cs)
            if rs:
                self.rgs.append((view, rs))


    def dumpMasterTable(self, out):
        fields = dumpColums(self.columns)
        if fields:
            out.write("<master>%s</master>" % "".join(fields))

    def dumpRepeatGroups(self, out):
        for view, rgs in self.rgs:
            for r in rgs:
                fields = dumpColums(r)
                if fields:
                    out.write("<%s>%s</%s>" % (view, "".join(fields), view))

    def dumpData(self, out):
        out.write("<case>")
        self.dumpMasterTable(out)
        self.dumpRepeatGroups(out)
        out.write("</case>")

def main():
    DBNAME = sys.argv[1]
    HOST = sys.argv[2]
    PORT = sys.argv[3]
    Writer = codecs.getwriter("utf-8")
    out = Writer(sys.stdout)
    con, cur = None, None
    try:
        #PASSWORD = raw_input()
        con = dbapi.connect(
            database = DBNAME,
            port     = PORT,
            user     = USER)
            

        cur = con.cursor(cursor_factory=extras.DictCursor)
        cur.execute(ANON_ON)
        row = cur.next()

        if row:
            if row[0] != 'on':
                print >> sys.stderr, "Anonymer Transfer nicht gestattet"
                sys.exit(1) 
        else:
            print >> sys.stderr, "Anonymer Transfer nicht gestattet"
            sys.exit(1) 


        cases = []

        cur.execute(SELECT_MASTER_IDS)

        while True:
            row = cur.fetchone()
            if not row: break
            cases.append(Case(row[0]))

        out.write('<?xml version="1.0" encoding="UTF-8"?>\n')
        out.write("<cases>")
        for case in cases:
            case.fetchData(cur)
            case.dumpData(out)
        out.write("</cases>")

    finally:
        if cur:
            try: cur.close()
            except: pass
        if con:
            try: con.close()
            except: pass


if __name__ == "__main__":
    main()

# vim:set ts=4 sw=4 si et sta sts=4:
1	teichmann	242	#!/usr/bin/python
2	teichmann	231	#
3			# (c) 2008 by Intevation GmbH
4			# author: Sascha L. Teichmann ([email protected])
5			#
6			import sys
7			#import os
8			import codecs
9			#import getopt
10			import psycopg2 as dbapi
11			import psycopg2.extras as extras
12			from types import StringTypes
13
14			dbapi.extensions.register_type(dbapi.extensions.UNICODE)
15
16			DBNAME = "ka_inte00002_db"
17			HOST = "laodameia"
18			PORT = 5434
19			USER = "postgres"
20			PASSWORD = ""
21
22	torsten	235	ANON_ON = \
23			"""SELECT wert FROM ka_konfiguration_tbl
24			WHERE bez = 'anon_transfer'"""
25
26	teichmann	231	SELECT_MASTER_IDS = \
27	teichmann	258	"""SELECT id from master_tbl_anonym_view"""
28	teichmann	231
29			SELECT_MASTER_TBL = \
30	torsten	235	"""SELECT * FROM master_tbl_anonym_view WHERE id = %(id)s"""
31	teichmann	231
32			RG_VIEWS = (
33			"kompetenzfestellung",
34			"angebote_berufliche_qualifizierung",
35			"angebote_berufsvorbereitung",
36			"angebote_bildenden_bereich",
37			"angebote_lebensbewaeltigung")
38
39			SELECT_RG = \
40			"""SELECT * FROM %s WHERE master_id = %d"""
41
42			BLACK_LIST = set(['id', 'master_id', 'bearbeiter_id'])
43
44			def escape(value, tagname):
45			if tagname == 'uuid_id':
46			return "%s" % value
47			if type(value) in StringTypes:
48	teichmann	258	return "<![CDATA[%s]]>" % value.replace("]]>", "]]><!CDATA[]]]]><![CDATA[>]]><[!CDATA[")
49	teichmann	231	if value is None:
50			return ""
51			return "%s" % value
52
53			def dumpColums(columns):
54			out = []
55			for tagname, value in columns:
56			if tagname not in BLACK_LIST and not value is None:
57			out.append("<%s>%s</%s>" % (tagname, escape(value, tagname), tagname))
58			return out
59
60			class Case:
61
62			def __init__(self, master_id):
63			self.master_id = int(master_id)
64			self.columns = None
65			self.rgs = []
66
67			def fetchData(self, cur):
68			self.fetchMasterTable(cur)
69			self.fetchRepeatGroups(cur)
70
71			def fetchMasterTable(self, cur):
72			cur.execute(SELECT_MASTER_TBL, { 'id': self.master_id })
73			row = cur.fetchone()
74			if not row: raise Exception("No dataset for id %d" % self.master_id)
75			columns = row._index.items() # XXX: hackish!
76			columns.sort(lambda a, b: cmp(a[1], b[1]))
77			columns = [(a[0], row[a[1]]) for a in columns]
78			self.columns = columns
79
80			def fetchRepeatGroups(self, cur):
81			for view in RG_VIEWS:
82	torsten	235	viewname = "rg_%s_tbl_anonym_view" % view
83	teichmann	231	cur.execute(SELECT_RG % (viewname, self.master_id))
84			rs = []
85			while True:
86			row = cur.fetchone()
87			if not row: break
88			cs = row._index.items() # XXX: hackish!
89			cs.sort(lambda a, b: cmp(a[1], b[1]))
90			cs = [(a[0], row[a[1]]) for a in cs]
91			rs.append(cs)
92			if rs:
93			self.rgs.append((view, rs))
94
95
96			def dumpMasterTable(self, out):
97			fields = dumpColums(self.columns)
98			if fields:
99			out.write("<master>%s</master>" % "".join(fields))
100
101			def dumpRepeatGroups(self, out):
102			for view, rgs in self.rgs:
103			for r in rgs:
104			fields = dumpColums(r)
105			if fields:
106			out.write("<%s>%s</%s>" % (view, "".join(fields), view))
107
108			def dumpData(self, out):
109			out.write("<case>")
110			self.dumpMasterTable(out)
111			self.dumpRepeatGroups(out)
112			out.write("</case>")
113
114			def main():
115	torsten	235	DBNAME = sys.argv[1]
116			HOST = sys.argv[2]
117			PORT = sys.argv[3]
118	teichmann	231	Writer = codecs.getwriter("utf-8")
119			out = Writer(sys.stdout)
120			con, cur = None, None
121			try:
122	torsten	235	#PASSWORD = raw_input()
123	teichmann	231	con = dbapi.connect(
124			database = DBNAME,
125			port = PORT,
126	torsten	235	user = USER)
127
128	teichmann	231
129			cur = con.cursor(cursor_factory=extras.DictCursor)
130	torsten	235	cur.execute(ANON_ON)
131			row = cur.next()
132	teichmann	231
133	torsten	235	if row:
134			if row[0] != 'on':
135			print >> sys.stderr, "Anonymer Transfer nicht gestattet"
136			sys.exit(1)
137			else:
138			print >> sys.stderr, "Anonymer Transfer nicht gestattet"
139			sys.exit(1)
140
141
142	teichmann	231	cases = []
143
144			cur.execute(SELECT_MASTER_IDS)
145
146			while True:
147			row = cur.fetchone()
148			if not row: break
149			cases.append(Case(row[0]))
150
151			out.write('<?xml version="1.0" encoding="UTF-8"?>\n')
152			out.write("<cases>")
153			for case in cases:
154			case.fetchData(cur)
155			case.dumpData(out)
156			out.write("</cases>")
157
158			finally:
159			if cur:
160			try: cur.close()
161			except: pass
162			if con:
163			try: con.close()
164			except: pass
165
166
167			if __name__ == "__main__":
168			main()
169
170			# vim:set ts=4 sw=4 si et sta sts=4: