MoinMoin: attachment:pg_auto.py of AndersEurenius

   1 from Cheetah.Template import Template
   2 import re, sys
   3 
   4 from getopt import getopt
   5 
   6 #try: import config
   7 #except:
   8 class c: pass
   9 config = c()
  10 config.dbparams = {'autocommit': True}
  11 config.dbstring = ''
  12 sys.modules['config'] = config
  13 
  14 #try:    from pgdb  import Query, make_connection
  15 #except: from pgdb2 import Query, make_connection
  16 from pgdb2 import Query
  17 
  18 def help():
  19     print """\
  20     -h,--help    This help text.
  21     -d,--dsn     Data Source Name, e.g. "host=h user=u dbname=frob pass=gr0nk"
  22     -t,--tmpl    Supply a template file to fill
  23     -o,--out     Write output to this file
  24     """
  25     sys.exit(0)
  26 
  27 def error(s):
  28     print >>sys.stderr, s
  29     sys.exit(0)
  30 
  31 opt = {'d:': 'dsn=', 't:': 'tmpl=', 'o:': 'out=','h':'help'}
  32 try:    opts, junk = getopt(sys.argv[1:], ''.join(opt.keys()), opt.values())
  33 except Exception, x:
  34     print x
  35     help()
  36 
  37 tmpl, out = 'moin.tmpl', 'moin.wok'
  38 
  39 for k,v in opts:
  40     if   k in ('--help', '-h'): help()  # does not return
  41     elif k in ('--dsn',  '-d'): config.dbstring = v
  42     elif k in ('--tmpl', '-t'): tmpl = v
  43     elif k in ('--out',  '-o'): out = v
  44 
  45 if opts: print opts
  46 
  47 acl_chars = 'rwadRxtXUCT'
  48 
  49 acl_perms = { 'a': 'Insert',
  50               'r': 'Select',
  51               'w': 'Update',
  52               'd': 'Delete',
  53               'R': 'Rule',
  54               'x': 'References',
  55               't': 'Trigger',
  56               'X': 'Execute',
  57               'U': 'Usage',
  58               'C': 'Create',
  59               'T': 'Temporary',
  60               '*': 'Grant' }
  61 
  62 acl_pat = re.compile('[{,"](?P<user>(?:[^="]|(?:\\\\"))*)='
  63                      '(?P<Insert>a?)' '(?P<GrantInsert>[*]?)'
  64                      '(?P<Select>r?)' '(?P<GrantSelect>[*]?)'
  65                      '(?P<Update>w?)' '(?P<GrantUpdate>[*]?)'
  66                      '(?P<Delete>d?)' '(?P<GrantDelete>[*]?)'
  67                      '(?P<Rule>R?)' '(?P<GrantRule>[*]?)'
  68                      '(?P<References>x?)' '(?P<GrantReferences>[*]?)'
  69                      '(?P<Trigger>t?)' '(?P<GrantTrigger>[*]?)'
  70                      '(?P<Execute>X?)' '(?P<GrantExecute>[*]?)'
  71                      '(?P<Usage>U?)' '(?P<GrantUsage>[*]?)'
  72                      '(?P<Create>C?)' '(?P<GrantCreate>[*]?)'
  73                      '(?P<Temporary>T?)' '(?P<GrantTemporary>[*]?)'
  74                      '/(?P<grantor>(?:[^,}"]|(?:\\\\"))+)')
  75 
  76 def munge_acl(s):
  77     if not s: return []
  78     acl = []
  79     for z in acl_pat.finditer(s):
  80         d = z.groupdict()
  81         d['user'] = d['user'] or 'public'
  82         acl.append(d)
  83     return acl
  84 
  85 typ = [ dict(t) for t in Query("SELECT oid, * FROM pg_catalog.pg_type")() ]
  86 
  87 typd = dict([ (t['oid'], t) for t in typ ])
  88 
  89 usr = [ dict(u) for u in Query("""
  90 SELECT   usename AS uname, usesysid AS uid,
  91          usecreatedb AS createdb, usesuper AS su, usecatupd AS catupd
  92 FROM     pg_user
  93 ORDER BY uid;
  94 """)() ]
  95 usrd = dict([ (u['uid'], u) for u in usr ])
  96 
  97 def fun():
  98     fun = Query("""
  99 SELECT    p.oid, nspname AS schema, proname AS function,
 100           proowner AS own, proacl AS acl, prosecdef AS setuid,
 101           lanname AS language, proisstrict AS strict, provolatile AS volatile,
 102           proisagg AS aggregate, proretset AS multiret, ret.typname AS ret,
 103           pronargs AS argn, proargtypes AS aty, prosrc AS src
 104 FROM      pg_proc p
 105 LEFT JOIN pg_language l    ON l.oid     = prolang
 106 LEFT JOIN pg_type ret      ON ret.oid   = prorettype
 107 LEFT JOIN pg_namespace n   ON n.oid     = pronamespace
 108 WHERE     nspname !~ 'pg_.*|information_schema'
 109 ORDER BY  proname, nspname;
 110 """)
 111     ff = fun()
 112     for f in ff:
 113         f['owner'] = usrd[f['own']]
 114         f['argt'] = [ typ[int(x)] for x in f['aty'].split() ]
 115         f['argn'] = ', '.join([ t['typname'] for t in f['argt'] ])
 116         f['proto'] = ''
 117         f['proto'] += '%(language)s ' % f
 118         f['proto'] += 'setuid ' * bool(f['setuid'])
 119         f['proto'] += 'strict ' * bool(f['strict'])
 120         f['proto'] += 'volatile ' * bool(f['volatile'])
 121         f['proto'] += 'aggregate ' * bool(f['aggregate'])
 122         f['proto'] += '%(ret)s' % f
 123         f['proto'] += '{}' * bool(f['multiret']) +' '
 124         f['proto'] += "%(schema)s.%(function)s(%(argn)s)" % f
 125         f['acl'] = munge_acl(f['acl'])
 126     return ff
 127 
 128 fun = fun()
 129 fund = dict([ (f['oid'], f) for f in fun ])
 130 
 131 def con():
 132     con = Query("""
 133 SELECT    nf.nspname AS from_schema, cf.relname AS from_table,
 134           nt.nspname AS   to_schema, ct.relname AS   to_table,
 135           conrelid, confrelid,
 136           conkey     AS from_col,
 137           confkey    AS to_col,
 138           conname AS constraint,
 139           contype AS "type",
 140           confupdtype AS update,
 141           confdeltype AS delete,
 142           confmatchtype AS match,
 143           pg_get_constraintdef(c.oid) AS src,
 144           description, c.oid
 145 FROM      pg_constraint c
 146 LEFT JOIN pg_class cf      ON cf.oid = conrelid
 147 LEFT JOIN pg_class ct      ON ct.oid = confrelid
 148 LEFT JOIN pg_namespace nf  ON nf.oid = cf.relnamespace
 149 LEFT JOIN pg_namespace nt  ON nt.oid = ct.relnamespace
 150 LEFT JOIN pg_description x ON x.objoid = c.oid
 151 WHERE     conrelid != 0
 152 ORDER BY  conname, from_table;
 153 """)
 154     cc = [ dict(c) for c in con() ]
 155     for c in cc:
 156         if  c['from_col'] and c['from_col'][0] == '{' and c['from_col'][-1]:
 157             c['from_col'] = [ int(k) for k in c['from_col'][1:-1].split(',') ]
 158         if  c[  'to_col'] and c[  'to_col'][0] == '{' and c[  'to_col'][-1]:
 159             c[  'to_col'] = [ int(k) for k in c[  'to_col'][1:-1].split(',') ]
 160     return cc
 161 
 162 con = con()
 163 
 164 def col():
 165     col = Query("""
 166 SELECT    nspname AS schema, relname AS table, attname AS column,
 167           attnum AS colnum, atttypid AS typ_id,
 168           typname, atttypmod AS "mod", attnotnull AS notnull,
 169           adsrc AS default, typlen AS size,
 170           attstorage AS stor,
 171           CASE WHEN attstorage = 'p' THEN 'Plain'
 172                WHEN attstorage = 'e' THEN 'External'
 173                WHEN attstorage = 'm' THEN 'Main'
 174                WHEN attstorage = 'x' THEN 'Extended'
 175                                      ELSE 'Unknown' END AS storage,
 176           pg_catalog.col_description(attrelid, attnum) as description
 177 FROM      pg_attribute a
 178 LEFT JOIN pg_type t        ON t.oid     = atttypid
 179 LEFT JOIN pg_class c       ON c.oid     = attrelid
 180 LEFT JOIN pg_namespace n   ON n.oid     = relnamespace
 181 LEFT JOIN pg_attrdef d     ON d.adrelid = c.oid AND d.adnum = a.attnum
 182 WHERE     nspname !~ 'pg_.*|information_schema'
 183 AND       relkind IN ('r','s','v')
 184 AND       attnum > 0
 185 AND       attisdropped IS FALSE
 186 ORDER BY  attnum
 187 ;
 188 """)
 189     cc = [ dict(c) for c in col() ]
 190     for c in cc:
 191         c['type'] = typd[c['typ_id']]
 192         c['constraints'] = [ k for k in con
 193                              if  c['schema'] == k['from_schema']
 194                              and c['table' ] == k['from_table' ]
 195                              and c['colnum'] in k['from_col'   ] ]
 196         c['constr_in'  ] = [ k for k in con
 197                              if  c['schema'] == k['to_schema']
 198                              and c['table' ] == k['to_table' ]
 199                              and c['colnum'] in k['to_col'   ] ]
 200         c['default'] = c['default'] or ''
 201     return cc
 202 
 203 col = col()
 204 
 205 def ind():
 206     ind = Query("""
 207 SELECT    nspname AS schema, c.relname AS table, i.relname AS index,
 208           t.typname, --i.relam AS access, --z.indpred AS expr,
 209           z.indisunique AS unique, z.indisprimary AS primary,
 210           z.indkey AS cols,
 211           a.amname AS method, i.relpages AS pages, i.reltuples AS rows,
 212           pg_get_indexdef(i.oid) AS def,
 213           substring(pg_get_indexdef(i.oid) FROM
 214                     strpos(pg_get_indexdef(i.oid), '(')+1 FOR
 215                     strpos(pg_get_indexdef(i.oid), ')') -
 216                     strpos(pg_get_indexdef(i.oid), '(')-1
 217                     ) AS deflist,
 218           CASE WHEN strpos(pg_get_indexdef(i.oid), 'WHERE') = 0 THEN ''
 219                ELSE substring(pg_get_indexdef(i.oid) FROM
 220                               strpos(pg_get_indexdef(i.oid), 'WHERE')+6)
 221           END AS pred,
 222           description
 223 FROM      pg_index       z
 224 JOIN      pg_class       c ON c.oid = z.indrelid
 225 JOIN      pg_class       i ON i.oid = z.indexrelid
 226 LEFT JOIN pg_am          a ON a.oid = i.relam
 227 LEFT JOIN pg_type        t ON t.oid = i.relam
 228 LEFT JOIN pg_namespace   n ON n.oid = c.relnamespace
 229 LEFT JOIN pg_description x ON x.objoid = pg_get_indexdef(i.oid)
 230 WHERE     c.relkind = 'r'
 231 AND       i.relkind = 'i'
 232 ORDER BY  i.relname, c.relname
 233 """)
 234     ii = [ dict(i) for i in ind() ]
 235     for i in ii:
 236         i['def'] = i['deflist'].split(', ')
 237     return ii
 238 ind = ind()
 239 
 240 def trg():
 241     trg = Query("""
 242 SELECT   n.nspname AS schema, rel.relname AS table, tgname AS trigger,
 243          trg.*, trg.oid
 244 FROM     pg_trigger   trg
 245 JOIN     pg_class     rel ON rel.oid = tgrelid
 246 JOIN     pg_namespace n   ON n.oid   = relnamespace
 247 WHERE    tgisconstraint IS FALSE
 248 AND      tgname !~ 'pg_.*'
 249 ORDER BY tgname, rel.relname
 250 """)
 251     tt = [ dict(t) for t in trg() ]
 252     for t in tt:
 253         t['function'] = fund[ t['tgfoid'] ]
 254     return tt
 255 
 256 trg = trg()
 257 
 258 def tbl():
 259     tbl = Query("""
 260 SELECT    nspname AS schema, relname AS table, relowner AS own, relacl AS acl,
 261           c.relpages AS pages, c.reltuples AS rows, relkind AS kind,
 262           CASE WHEN relkind = 'v'
 263                THEN pg_get_viewdef(c.oid)
 264                ELSE NULL END AS view,
 265           relhasoids AS has_oids, description, c.oid
 266 FROM      pg_class c
 267 LEFT JOIN pg_namespace   n ON n.oid     = relnamespace
 268 LEFT JOIN pg_description x ON x.objoid  = c.oid AND x.objsubid = 0
 269 WHERE     nspname !~ 'pg_.*|information_schema'
 270 AND       relkind IN ('r','s','v')
 271 ORDER BY  2
 272 """)
 273     tt = [ dict(t) for t in tbl() ]
 274 
 275     vref = '(?:FROM |JOIN )([@A-Za-z][@A-Za-z0-9]+).([@A-Za-z][@A-Za-z0-9]+)'
 276 
 277     for t in tt: t['refs'], t['refd'] = [], []
 278 
 279     for t in tt:
 280         t['owner']       = usrd[t['own']]
 281         t['columns']     = [ c for c in col
 282                              if  t['schema'] == c['schema']
 283                              and t['table' ] == c['table' ] ]
 284         t['indices']     = [ i for i in ind
 285                              if  t['schema'] == i['schema']
 286                              and t['table' ] == i['table' ] ]
 287         t['constraints'] = [ c for c in con
 288                              if  t['schema'] == c['from_schema']
 289                              and t['table' ] == c['from_table' ] ]
 290         t['constr_in']   = [ c for c in con
 291                              if  t['schema'] == c['to_schema']
 292                              and t['table' ] == c['to_table' ] ]
 293         t['triggers']    = [ u for u in trg
 294                              if  t['schema'] == u['schema']
 295                              and t['table' ] == u['table' ] ]
 296         t['acl'] = munge_acl(t['acl'])
 297         if t['view']:
 298            rr = set([ (z, u) for z,u in re.findall(vref, t['view']) ])
 299            for schema, table in rr:
 300                for q in tt:
 301                    if q['schema'] == schema and q['table'] == table:
 302                        t['refs'].append(q)
 303                        q['refd'].append(t)
 304            t['view'] = re.sub(' *(FULL (?:OUTER )?|LEFT |RIGHT |'
 305                               'INNER |CROSS |)?JOIN',
 306                               '\n\g<1>JOIN',
 307                               (t['view']
 308                                .replace(',',',\n')
 309                                .replace('SELECT ','SELECT\n ')
 310                                .replace('CASE','\nCASE')
 311                                .replace('THEN','\n     THEN')
 312                                .replace('ELSE','\n     ELSE')))
 313     return tt
 314 
 315 tbl = tbl()
 316 tbld = dict([ (t['oid'], t) for t in tbl ])
 317 
 318 def sch():
 319     sch = Query("""
 320 SELECT    nspname AS schema, nspowner AS own, nspacl AS acl, description,
 321           nspacl AS rawacl
 322 FROM      pg_namespace n
 323 LEFT JOIN pg_description x ON x.objoid = n.oid
 324 WHERE     nspname !~ 'pg_.*|information_schema'
 325 ORDER BY  nspname;
 326 """)
 327     ss = [ dict(s) for s in sch() ]
 328     for s in ss:
 329         s['owner']     = usrd[s['own']]
 330         s['tables']    = [ t for t in tbl if t['schema'] == s['schema'] ]
 331         s['functions'] = [ f for f in fun if f['schema'] == s['schema'] ]
 332         s['acl'] = munge_acl(s['acl'])
 333     return ss
 334 
 335 sch = sch()
 336 
 337 def db():
 338     db = Query("""
 339 SELECT    datname AS db, datdba AS own, datacl AS acl, description, *
 340 FROM      pg_database d
 341 LEFT JOIN pg_description x ON x.objoid = d.oid
 342 WHERE     datname !~ 'postgres|template.*';
 343 """)
 344     dd = [ dict(d) for d in db() ]
 345     for d in dd:
 346         d['owner'] = usrd[d['own']]
 347         d['acl'] = munge_acl(d['acl'])
 348     return dd
 349 
 350 db = db()[0]
 351 
 352 #w = wiki(searchList = [locals()] )
 353 #del wiki
 354 #w = str(w)
 355 
 356 file(out,'w').write(str(Template(file=tmpl, namespaces=[locals()])))