7znUZSwQ

1"""
2De relevante bitene av databaseskjema ser slik ut:
3
4
5/* change_log
6
7  tstamp
8        Timestamp
9  change_id
10        Unique id
11  subject_entity
12        Entiy id which the operation is performed on
13  subject_type
14        The type of the subject entity
15  change_type_id
16        FK change_type
17  dest_entity
18        Entity id of destination
19  change_params
20        key-value mapping of arguments.
21  change_by
22        Entity id of changer iff it exists.
23  change_program
24        Name of program that performed the change when change_by is
25        null
26*/
27
28category:code;
29CREATE SEQUENCE change_log_seq;
30category:main;
31CREATE TABLE change_log
32(
33  tstamp          TIMESTAMP
34                  DEFAULT [:now]
35                  NOT NULL,
36  change_id       NUMERIC(12,0)
37                  NOT NULL
38                  CONSTRAINT change_id_pk PRIMARY KEY,
39  /* What entity a change applies to */
40  subject_entity  NUMERIC(12,0),
41  /* What kind of change? (name change, account deletion, etc.) */
42  change_type_id  NUMERIC(6,0)
43                  REFERENCES change_type(change_type_id),
44  dest_entity     NUMERIC(12,0),
45  /* Some attributes relevant to the change */
46  change_params   CHAR VARYING(4000),
47  change_by       NUMERIC(12,0)
48                  REFERENCES entity_info(entity_id),
49  change_program  CHAR VARYING(64)
50);
51
52
53Og så var det utvidelsen:
54
55CREATE TABLE pending_change_log
56(
57    confirmation_key    CHAR VARYING(256) NOT NULL,
58    change_id           NUMERIC(12,0) NOT NULL
59                        CONSTRAINT pending_change_log_cl_exists
60                            REFERENCES change_log(change_id),
61);
62
63"""
64
65
66class ChangeLog(object):
67    def get_log_events(self, start_id=0, max_id=None, types=None,
68                       subject_entity=None, dest_entity=None,
69                       any_entity=None, change_by=None, change_program=None,
70                       sdate=None):
71        if any_entity and (dest_entity or subject_entity):
72            raise self.ProgrammingError("any_entity is mutually exclusive "
73                                        "with dest_entity or subject_entity")
74        where = ["change_id >= :start_id"]
75        bind = {'start_id': int(start_id)}
76        if subject_entity is not None:
77            where.append("subject_entity=:subject_entity")
78            bind['subject_entity'] = int(subject_entity)
79        if dest_entity is not None:
80            where.append("dest_entity=:dest_entity")
81            bind['dest_entity'] = int(dest_entity)
82        if any_entity is not None:
83            where.append("subject_entity=:any_entity OR "
84                         "dest_entity=:any_entity")
85            bind['any_entity'] = int(any_entity)
86        if change_by is not None:
87            where.append("change_by=:change_by")
88            bind['change_by'] = int(change_by)
89        if change_program is not None:
90            where.append("change_program=:change_program")
91            bind['change_program'] = change_program
92        if max_id is not None:
93            where.append("change_id <= :max_id")
94            bind['max_id'] = int(max_id)
95        if types is not None:
96            where.append(argument_to_sql(types, "change_type_id", bind, int))
97        if sdate is not None:
98            where.append("tstamp > :sdate")
99            bind['sdate'] = sdate
100        where = "WHERE (" + ") AND (".join(where) + ")"
101        return self.query("""
102        SELECT tstamp, change_id, subject_entity, change_type_id, dest_entity,
103               change_params, change_by, change_program
104        FROM [:table schema=cerebrum name=change_log] %s
105        ORDER BY change_id""" % where, bind, fetchall=False)
106    # end get_log_events
107
108# end class ChangeLog
109
110
111class ChangeLogVH(ChangeLog):
112    def get_log_events(self, start_id=0, max_id=None, types=None,
113                       subject_entity=None, dest_entity=None,
114                       any_entity=None, change_by=None, change_program=None,
115                       sdate=None, confirmation_key=None):
116        # ... do what super().get_log_events() does, except, the join should look like:
117
118        return self.query("""
119        SELECT tstamp, change_id, subject_entity, change_type_id, dest_entity,
120               change_params, change_by, change_program, pcl.confirmation_key
121        FROM [:table schema=cerebrum name=change_log] 
122        RIGHT OUTER JOIN 
123            [:table schema=cerebrum name=pending_change_log] pcl
124            ON change_id = pcl.change_id
125        %s
126        ORDER BY change_id""" % where, bind, fetchall=False)
127        """)