1# -*- coding: utf-8 -*- 2""" 3 pyrseas.dbobject.view 4 ~~~~~~~~~~~~~~~~~~~~~ 5 6 This module defines two classes: View derived from DbClass and 7 MaterializedView derived from View. 8""" 9from pyrseas.lib.pycompat import PY2 10from pyrseas.yamlutil import MultiLineStr 11from . import commentable, ownable, grantable 12from .table import DbClass 13from .column import Column 14 15 16class View(DbClass): 17 """A database view definition 18 19 A view is identified by its schema name and view name. 20 """ 21 def __init__(self, name, schema, description, owner, privileges, 22 definition, 23 oid=None): 24 """Initialize the view 25 26 :param name-privileges: see DbClass.__init__ params 27 :param definition: prettified definition (from pg_getviewdef) 28 """ 29 super(View, self).__init__(name, schema, description, owner, 30 privileges) 31 if PY2: 32 self.definition = definition 33 else: 34 self.definition = MultiLineStr(definition) 35 self.triggers = {} 36 self.columns = [] 37 self.oid = oid 38 39 @staticmethod 40 def query(dbversion=None): 41 return """ 42 SELECT nspname AS schema, relname AS name, rolname AS owner, 43 array_to_string(relacl, ',') AS privileges, 44 pg_get_viewdef(c.oid, TRUE) AS definition, 45 obj_description(c.oid, 'pg_class') AS description, c.oid 46 FROM pg_class c JOIN pg_roles r ON (r.oid = relowner) 47 JOIN pg_namespace ON (relnamespace = pg_namespace.oid) 48 WHERE relkind = 'v' 49 AND nspname != 'pg_catalog' AND nspname != 'information_schema' 50 ORDER BY nspname, relname""" 51 52 @staticmethod 53 def from_map(name, schema, inobj): 54 """Initialize a view instance from a YAML map 55 56 :param name: view name 57 :param name: schema map 58 :param inobj: YAML map of the view 59 :return: view instance 60 """ 61 obj = View( 62 name, schema.name, inobj.pop('description', None), 63 inobj.pop('owner', None), inobj.pop('privileges', []), 64 inobj.pop('definition', None)) 65 if "columns" in inobj: 66 obj.columns = [Column(list(col.keys())[0], schema.name, name, 67 i + 1, 68 list(col.values())[0].get("type", None)) 69 for i, col in enumerate(inobj.get("columns"))] 70 if 'depends_on' in inobj: 71 obj.depends_on.extend(inobj['depends_on']) 72 obj.fix_privileges() 73 obj.set_oldname(inobj) 74 return obj 75 76 privobjtype = "TABLE" 77 78 @property 79 def allprivs(self): 80 return 'arwdDxt' 81 82 def to_map(self, db, opts): 83 """Convert a view to a YAML-suitable format 84 85 :param opts: options to include/exclude tables, etc. 86 :return: dictionary 87 """ 88 if hasattr(opts, 'excl_tables') and opts.excl_tables \ 89 and self.name in opts.excl_tables: 90 return None 91 dct = super(View, self).to_map(db, opts.no_owner, opts.no_privs) 92 dct['columns'] = [col.to_map(db, opts.no_privs) 93 for col in self.columns] 94 if 'dependent_funcs' in dct: 95 dct.pop('dependent_funcs') 96 if len(self.triggers) > 0: 97 for key in list(self.triggers.values()): 98 dct['triggers'].update(self.triggers[key.name].to_map(db)) 99 else: 100 dct.pop('triggers') 101 return dct 102 103 @commentable 104 @grantable 105 @ownable 106 def create(self, dbversion=None, newdefn=None): 107 """Return SQL statements to CREATE the view 108 109 :return: SQL statements 110 """ 111 defn = newdefn or self.definition 112 if defn[-1:] == ';': 113 defn = defn[:-1] 114 return ["CREATE%s VIEW %s AS\n %s" % ( 115 newdefn and " OR REPLACE" or '', self.qualname(), defn)] 116 117 def alter(self, inview, dbversion=None): 118 """Generate SQL to transform an existing view 119 120 :param inview: a YAML map defining the new view 121 :return: list of SQL statements 122 123 Compares the view to an input view and generates SQL 124 statements to transform it into the one represented by the 125 input. 126 """ 127 stmts = [] 128 for col in self.columns: 129 if col.name != inview.columns[col.number - 1].name: 130 raise KeyError("Cannot change name of view column '%s'" 131 % col.name) 132 if col.type != inview.columns[col.number - 1].type: 133 raise TypeError("Cannot change datatype of view column '%s'" 134 % col.name) 135 if self.definition != inview.definition: 136 stmts.append(self.create(dbversion, inview.definition)) 137 stmts.append(super(View, self).alter(inview)) 138 return stmts 139 140 141class MaterializedView(View): 142 """A materialized view definition 143 144 A materialized view is identified by its schema name and view name. 145 """ 146 def __init__(self, name, schema, description, owner, privileges, 147 definition, with_data=False, 148 oid=None): 149 """Initialize the materialized view 150 151 :param name-privileges: see DbClass.__init__ params 152 :param definition: prettified definition (from pg_getviewdef) 153 :param with_data: is view populated (from relispopulated) 154 """ 155 super(MaterializedView, self).__init__( 156 name, schema, description, owner, privileges, definition) 157 self.with_data = with_data 158 self.indexes = {} 159 self.oid = oid 160 161 @staticmethod 162 def query(dbversion=None): 163 return """ 164 SELECT nspname AS schema, relname AS name, rolname AS owner, 165 array_to_string(relacl, ',') AS privileges, 166 pg_get_viewdef(c.oid, TRUE) AS definition, 167 relispopulated AS with_data, 168 obj_description(c.oid, 'pg_class') AS description, c.oid 169 FROM pg_class c JOIN pg_roles r ON (r.oid = relowner) 170 JOIN pg_namespace ON (relnamespace = pg_namespace.oid) 171 WHERE relkind = 'm' 172 AND nspname != 'pg_catalog' AND nspname != 'information_schema' 173 ORDER BY nspname, relname""" 174 175 @staticmethod 176 def from_map(name, schema, inobj): 177 """Initialize a materialized view instance from a YAML map 178 179 :param name: view name 180 :param name: schema map 181 :param inobj: YAML map of the view 182 :return: materialized view instance 183 """ 184 obj = MaterializedView( 185 name, schema.name, inobj.pop('description', None), 186 inobj.pop('owner', None), inobj.pop('privileges', []), 187 inobj.pop('definition', None)) 188 if "columns" in inobj: 189 obj.columns = [Column(list(col.keys())[0], schema.name, name, 190 i + 1, 191 list(col.values())[0].get("type", None)) 192 for i, col in enumerate(inobj.get("columns"))] 193 obj.fix_privileges() 194 obj.set_oldname(inobj) 195 return obj 196 197 @property 198 def objtype(self): 199 return "MATERIALIZED VIEW" 200 201 def to_map(self, db, opts): 202 """Convert a materialized view to a YAML-suitable format 203 204 :param opts: options to include/exclude tables, etc. 205 :return: dictionary 206 """ 207 if hasattr(opts, 'excl_tables') and opts.excl_tables \ 208 and self.name in opts.excl_tables: 209 return None 210 mvw = super(MaterializedView, self).to_map(db, opts) 211 if len(self.indexes) > 0: 212 for k in list(self.indexes.values()): 213 mvw['indexes'].update(self.indexes[k.name].to_map(db)) 214 else: 215 mvw.pop('indexes') 216 return mvw 217 218 @commentable 219 @grantable 220 @ownable 221 def create(self, dbversion=None, newdefn=None): 222 """Return SQL statements to CREATE the materialized view 223 224 :return: SQL statements 225 """ 226 defn = newdefn or self.definition 227 if defn[-1:] == ';': 228 defn = defn[:-1] 229 return ["CREATE %s %s AS\n %s" % ( 230 self.objtype, self.qualname(), defn)] 231