# HG changeset patch # User Sandro Knauß # Date 1324205386 -3600 # Node ID 7dce6c0f06fb9ff0f0f04d35418036021d7f5e20 # Parent 9f7da8dc8df886538720e821039bb1f66d10b0b5 adding database schema to documentation. diff -r 9f7da8dc8df8 -r 7dce6c0f06fb createdoc.py --- a/createdoc.py Sun Dec 18 11:49:12 2011 +0100 +++ b/createdoc.py Sun Dec 18 11:49:46 2011 +0100 @@ -10,6 +10,7 @@ from iro.user import User as Current from iro.newuser import User as New +from createerm import createSchemaPlot,tables @@ -99,11 +100,20 @@ self.args=[Arg(a,m) for a in args] _, self.rets=ret(m) +class Table(Link): + def __init__(self,cls): + name=cls.__name__ + self.tablename=cls.__tablename__ + title=self.tablename[0].upper()+self.tablename[1:] + Link.__init__(self,name,title) + self.description = cls.__doc__.split("\n")[0].decode("utf8") + def main(): sites=[Site("index.html","Iro"), Site("current.html","API Documentation"), Site("new.html","geplante API Documentation"), + Site("database.html","Datenbank Schema"), Site("impressum.html","Impressum"), ] @@ -132,7 +142,9 @@ Method("routes",new_methods), Method("defaultRoute",new_methods), ] - + + t = [Table(f.class_) for f in tables] + createSchemaPlot('doc/images/db-schema.svg') for site in sites: print("generiere %s" % site.name) @@ -140,7 +152,7 @@ def a(s): if s == site: return {"class":"menu active"} - stream = tmpl.generate(sites=sites,active=a,current=current,new=newm) + stream = tmpl.generate(sites=sites,active=a,current=current,new=newm,tables=t) with open('doc/'+site.name, "w") as g: g.write(stream.render('html', doctype='html')) diff -r 9f7da8dc8df8 -r 7dce6c0f06fb createerm.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/createerm.py Sun Dec 18 11:49:46 2011 +0100 @@ -0,0 +1,48 @@ +from iro import schema + +from sqlalchemy.orm import class_mapper +tables = [] +for attr in schema.__tables__: + if attr[0] == '_': continue + try: + cls = getattr(schema, attr) + tables.append(class_mapper(cls)) + except: + pass + + +#schema plot +def createSchemaPlot(fname): + from sqlalchemy_schemadisplay3 import create_schema_graph + graph = create_schema_graph(metadata=schema.Base.metadata, + show_datatypes=True, # The image too large if datatypes shown + show_indexes=True, # ditto for indexes + rankdir='LR', # From left to right (instead of top to bottom) + concentrate=True, # Don't try to join the relation lines together + ) + + graph.set_size('6.5,10') + #graph.set_ratio("fill") + graph.write_svg(fname) + +#umlplot +def createUMLPlot(fname): + from sqlalchemy_schemadisplay3 import create_uml_graph + from sqlalchemy.orm import class_mapper + mappers = [] + for attr in dir(schema.model): + if attr[0] == '_': continue + try: + cls = getattr(schema.model, attr) + mappers.append(class_mapper(cls)) + except: + pass + #pass them to the function and set some formatting options + graph = create_uml_graph(mappers, + show_operations=False, # not necessary in this case + show_multiplicity_one=True, # some people like to see the ones + show_attributes=True, + ) + graph.set_size('6,5') + graph.set_ratio("fill") + graph.write_png('test.png') diff -r 9f7da8dc8df8 -r 7dce6c0f06fb doc/current.html --- a/doc/current.html Sun Dec 18 11:49:12 2011 +0100 +++ b/doc/current.html Sun Dec 18 11:49:46 2011 +0100 @@ -19,7 +19,7 @@ diff -r 9f7da8dc8df8 -r 7dce6c0f06fb doc/database.html --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/doc/database.html Sun Dec 18 11:49:46 2011 +0100 @@ -0,0 +1,75 @@ + + + + + + Iro · Datenbank + + + + + + + + + + + +
+ +
+
+
+
+

Datenbank Schema

+
+

+

+
    +
  1. 1. Datenbankschema
    2. +
  2. 3. + Tabellen +
      +
    1. 2.1 Apiuser
    2. 2.2 Job
    3. 2.3 Message
    4. 2.4 Offer
    5. 2.5 Userright
      6. +
    +
    3. +
+
+

Schema

+ +

Dies ist eine Übersicht der benutzen Tabellen die Iro benötigt.

+
+

Tabellen

+
+

Apiuser

+

Die Benutzerdatenbank von Iro.

+
+

Job

+

Ein kompletter Auftrag, der an Iro zum verschicken übergeben wird. Status zeigt den generellen Status des Auftrages an (init, started, sending, sended oder error). info wird verwendet um dem Benutzer eine Möglickeit zu geben verschiede Auftragsgruppen zu erstellen.

+
+

Message

+

Wenn ein Vorgang von Iro Kosten erzeugt hat wird eine neue Zeile eingefügt. Solange nicht bezahlt wurde ist isBilled=0.

+
+

Offer

+

Alle Routen über die SMS, Faxe und Mails verschickt werden könnnen. provider, typ und route werden verwendet, um die entsprechenden Zugangsdaten laden zu können.

+
+

Userright

+

Über welche Routen darf ein Benutzer Daten verschicken und welches sind die Standardrouten (isDefault=1) für den Benuter.

+
+
+
+
+
+
+
+ +
+ + \ No newline at end of file diff -r 9f7da8dc8df8 -r 7dce6c0f06fb doc/images/db-schema.svg --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/doc/images/db-schema.svg Sun Dec 18 11:49:46 2011 +0100 @@ -0,0 +1,96 @@ + + + + + + +G + + +message + +message + +- id : INTEGER +- recipient : VARCHAR +- isBilled : BOOLEAN +- date : DATETIME +- price : NUMERIC(8, 2) +- job : INTEGER +- offer : VARCHAR + + +job + +job + +- hash : VARCHAR +- info : VARCHAR +- status : VARCHAR(7) +- user : VARCHAR + + +message->job + + ++ hash ++ job + + +offer + +offer + +- name : VARCHAR +- provider : VARCHAR +- route : VARCHAR +- typ : VARCHAR + + +message->offer + + ++ name ++ offer + + +userright + +userright + +- user : VARCHAR +- offer : VARCHAR +- isDefault : BOOLEAN + + +apiuser + +apiuser + +- name : VARCHAR +- apikey : VARCHAR + + +job->apiuser + + ++ name ++ user + + +apiuser->userright + ++ name ++ user + + +offer->userright + ++ name ++ offer + + + diff -r 9f7da8dc8df8 -r 7dce6c0f06fb doc/impressum.html --- a/doc/impressum.html Sun Dec 18 11:49:12 2011 +0100 +++ b/doc/impressum.html Sun Dec 18 11:49:46 2011 +0100 @@ -19,7 +19,7 @@ diff -r 9f7da8dc8df8 -r 7dce6c0f06fb doc/index.html --- a/doc/index.html Sun Dec 18 11:49:12 2011 +0100 +++ b/doc/index.html Sun Dec 18 11:49:46 2011 +0100 @@ -19,7 +19,7 @@ diff -r 9f7da8dc8df8 -r 7dce6c0f06fb doc/new.html --- a/doc/new.html Sun Dec 18 11:49:12 2011 +0100 +++ b/doc/new.html Sun Dec 18 11:49:46 2011 +0100 @@ -19,7 +19,7 @@ diff -r 9f7da8dc8df8 -r 7dce6c0f06fb doc/tmpl/database.html --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/doc/tmpl/database.html Sun Dec 18 11:49:46 2011 +0100 @@ -0,0 +1,43 @@ + + + + + Datenbank + + + Datenbank Schema +
+

+ +

+
    +
  1. 1. Datenbankschema
    2. +
  2. 3. + Tabellen +
      +
    1. 2.${key+1} ${table.title}
      2. +
    +
    3. +
+
+ + +
+

Schema

+ +

Dies ist eine Übersicht der benutzen Tabellen die Iro benötigt.

+
+
+

Tabellen

+
+

${table.title}

+

+ Dieser Tabelle fehlt noch die Beschreibung. +

+
+
+ + diff -r 9f7da8dc8df8 -r 7dce6c0f06fb iro/schema.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/iro/schema.py Sun Dec 18 11:49:46 2011 +0100 @@ -0,0 +1,70 @@ +# -*- coding: utf-8 -*- + +from sqlalchemy import Column, Integer, String, Sequence, Boolean, DateTime, Numeric, Enum +from sqlalchemy import create_engine +from sqlalchemy.ext.declarative import declarative_base + +#relationship +from sqlalchemy import ForeignKey +from sqlalchemy.orm import relationship, backref + +engine = create_engine('sqlite:///:memory:', echo=True) +Base = declarative_base() + +__tables__=["User", "Job", "Message", "Offer", "Userright"] + +class Userright(Base): + """Über welche Routen darf ein Benutzer Daten verschicken und welches sind die Standardrouten (isDefault=1) für den Benuter.""" + __tablename__ = 'userright' + user_name = Column('user', String, ForeignKey('apiuser.name'), primary_key=True) + offer_name = Column('offer', String, ForeignKey('offer.name'), primary_key=True) + isDefault = Column(Boolean) + offer = relationship("Offer") + +class Offer(Base): + """Alle Routen über die SMS, Faxe und Mails verschickt werden könnnen. provider, typ und route werden verwendet, um die entsprechenden Zugangsdaten laden zu können.""" + __tablename__ = "offer" + name = Column(String, primary_key=True) + provider = Column(String) + route = Column(String) + typ = Column(String) + + +class Message(Base): + """Wenn ein Vorgang von Iro Kosten erzeugt hat wird eine neue Zeile eingefügt. Solange nicht bezahlt wurde ist isBilled=0.""" + __tablename__ = "message" + id = Column(Integer, Sequence('message_id_seq'), primary_key=True) + recipient = Column(String) + isBilled = Column(Boolean) + date = Column(DateTime) + price = Column(Numeric(8,2)) + job_hash = Column("job",Integer, ForeignKey('job.hash')) + job = relationship("Job", backref=backref('messages')) + offer_id = Column("offer",String, ForeignKey('offer.name')) + offer = relationship("Offer", backref=backref('messages')) + + +class Job(Base): + """Ein kompletter Auftrag, der an Iro zum verschicken übergeben wird. Status zeigt den generellen Status des Auftrages an (init, started, sending, sended oder error). info wird verwendet um dem Benutzer eine Möglickeit zu geben verschiede Auftragsgruppen zu erstellen.""" + __tablename__ = "job" + hash = Column(String, primary_key=True) + info = Column(String) + status = Column(Enum("init","started","sending","sended","error")) + user_id = Column("user", String, ForeignKey('apiuser.name')) + user = relationship("User", backref=backref('jobs')) + +class User(Base): + """Die Benutzerdatenbank von Iro.""" + __tablename__ = "apiuser" + name = Column(String, primary_key=True) + apikey = Column(String,unique=True) + rights = relationship('Userright') + def __init__(self, name, apikey): + self.name=name + self.apikey=apikey + + def __repr__(self): + return ""%(self.name,self.apikey) + + +#Base.metadata.create_all(engine) diff -r 9f7da8dc8df8 -r 7dce6c0f06fb sqlalchemy_schemadisplay3.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlalchemy_schemadisplay3.py Sun Dec 18 11:49:46 2011 +0100 @@ -0,0 +1,173 @@ +# updated SQLA schema display to work with pydot 1.0.2 +# download from: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay + + +from sqlalchemy.orm.properties import PropertyLoader +import pydot +import types + +__all__ = ['create_uml_graph', 'create_schema_graph', 'show_uml_graph', 'show_schema_graph'] + +def _mk_label(mapper, show_operations, show_attributes, show_datatypes, bordersize): + html = '<' % (bordersize, mapper.class_.__name__) + def format_col(col): + colstr = '+%s' % (col.name) + if show_datatypes: + colstr += ' : %s' % (col.type.__class__.__name__) + return colstr + + if show_attributes: + html += '' % '
'.join(format_col(col) for col in sorted(mapper.columns, key=lambda col:not col.primary_key)) + else: + [format_col(col) for col in sorted(mapper.columns, key=lambda col:not col.primary_key)] + if show_operations: + html += '' % '
'.join( + '%s(%s)' % (name,", ".join(default is _mk_label and ("%s") % arg or ("%s=%s" % (arg,repr(default))) for default,arg in + zip((func.func_defaults and len(func.func_code.co_varnames)-1-(len(func.func_defaults) or 0) or func.func_code.co_argcount-1)*[_mk_label]+list(func.func_defaults or []), func.func_code.co_varnames[1:]) + )) + for name,func in mapper.class_.__dict__.items() if isinstance(func, types.FunctionType) and func.__module__ == mapper.class_.__module__ + ) + html+= '
%s
%s
%s
>' + return html + + +def create_uml_graph(mappers, show_operations=True, show_attributes=True, show_multiplicity_one=False, show_datatypes=True, linewidth=1.0, font="Bitstream-Vera Sans"): + graph = pydot.Dot(prog='neato',mode="major",overlap="0", sep="0.01",dim="3", pack="True", ratio=".75") + relations = set() + for mapper in mappers: + graph.add_node(pydot.Node(mapper.class_.__name__, + shape="plaintext", label=_mk_label(mapper, show_operations, show_attributes, show_datatypes, linewidth), + fontname=font, fontsize="8.0", + )) + if mapper.inherits: + graph.add_edge(pydot.Edge(mapper.inherits.class_.__name__,mapper.class_.__name__, + arrowhead='none',arrowtail='empty', style="setlinewidth(%s)" % linewidth, arrowsize=str(linewidth))) + for loader in mapper.iterate_properties: + if isinstance(loader, PropertyLoader) and loader.mapper in mappers: + if hasattr(loader, 'reverse_property'): + relations.add(frozenset([loader, loader.reverse_property])) + else: + relations.add(frozenset([loader])) + + for relation in relations: + #if len(loaders) > 2: + # raise Exception("Warning: too many loaders for join %s" % join) + args = {} + def multiplicity_indicator(prop): + if prop.uselist: + return ' *' + if any(col.nullable for col in prop.local_side): + return ' 0..1' + if show_multiplicity_one: + return ' 1' + return '' + + if len(relation) == 2: + src, dest = relation + from_name = src.parent.class_.__name__ + to_name = dest.parent.class_.__name__ + + def calc_label(src,dest): + return '+' + src.key + multiplicity_indicator(src) + args['headlabel'] = calc_label(src,dest) + + args['taillabel'] = calc_label(dest,src) + args['arrowtail'] = 'none' + args['arrowhead'] = 'none' + args['constraint'] = False + else: + prop, = relation + from_name = prop.parent.class_.__name__ + to_name = prop.mapper.class_.__name__ + args['headlabel'] = '+%s%s' % (prop.key, multiplicity_indicator(prop)) + args['arrowtail'] = 'none' + args['arrowhead'] = 'vee' + + graph.add_edge(pydot.Edge(from_name,to_name, + fontname=font, fontsize="7.0", style="setlinewidth(%s)"%linewidth, arrowsize=str(linewidth), + **args) + ) + + return graph + +#from sqlalchemy import Table, text + +def _render_table_html(table, metadata, show_indexes, show_datatypes): + def format_col_type(col): + try: + return col.type.get_col_spec() + except NotImplementedError: + return str(col.type) + except AttributeError: + return str(col.type) + def format_col_str(col): + if show_datatypes: + return "- %s : %s" % (col.name, format_col_type(col)) + else: + return "- %s" % col.name + html = '<' % table.name + + html += ''.join('' % (col.name, format_col_str(col)) for col in table.columns) + html += '
%s
%s
>' + return html + +def create_schema_graph(tables=None, metadata=None, show_indexes=True, show_datatypes=True, font="Bitstream-Vera Sans", + concentrate=True, relation_options={}, rankdir='TB'): + relation_kwargs = { + 'fontsize':"7.0" + } + relation_kwargs.update(relation_options) + + if not metadata and len(tables): + metadata = tables[0].metadata + elif not tables and metadata: + if not len(metadata.tables): + metadata.reflect() + tables = metadata.tables.values() + else: + raise Exception("You need to specify at least tables or metadata") + + graph = pydot.Dot(prog="dot",mode="ipsep",overlap="ipsep",sep="0.01",concentrate=str(concentrate), rankdir=rankdir) + for table in tables: + graph.add_node(pydot.Node(str(table.name), + shape="plaintext", + label=_render_table_html(table, metadata, show_indexes, show_datatypes), + fontname=font, fontsize="7.0" + )) + + for table in tables: + for fk in table.foreign_keys: + edge = [table.name, fk.column.table.name] + is_inheritance = fk.parent.primary_key and fk.column.primary_key + if is_inheritance: + edge = edge[::-1] + graph_edge = pydot.Edge( + headlabel="+ %s"%fk.column.name, taillabel='+ %s'%fk.parent.name, + arrowhead=is_inheritance and 'none' or 'odot' , + arrowtail=(fk.parent.primary_key or fk.parent.unique) and 'empty' or 'crow' , + fontname=font, + #samehead=fk.column.name, sametail=fk.parent.name, + *edge, **relation_kwargs + ) + graph.add_edge(graph_edge) + +# not sure what this part is for, doesn't work with pydot 1.0.2 +# graph_edge.parent_graph = graph.parent_graph +# if table.name not in [e.get_source() for e in graph.get_edge_list()]: +# graph.edge_src_list.append(table.name) +# if fk.column.table.name not in graph.edge_dst_list: +# graph.edge_dst_list.append(fk.column.table.name) +# graph.sorted_graph_elements.append(graph_edge) + return graph + +def show_uml_graph(*args, **kwargs): + from cStringIO import StringIO + from PIL import Image + iostream = StringIO(create_uml_graph(*args, **kwargs).create_png()) + Image.open(iostream).show(command=kwargs.get('command','gwenview')) + +def show_schema_graph(*args, **kwargs): + from cStringIO import StringIO + from PIL import Image + iostream = StringIO(create_schema_graph(*args, **kwargs).create_png()) + Image.open(iostream).show(command=kwargs.get('command','gwenview'))