from sqlalchemy import Table, Column, Integer, Unicode, MetaData, ForeignKey, Boolean, DateTime, create_engine, UnicodeText import datetime import sqlalchemy.exceptions from sqlalchemy.orm import relation, backref, sessionmaker from sqlalchemy.ext.declarative import declarative_base class Uniquer(object): @classmethod def by_value(cls, session, **kwargs): try: return session.query(cls).filter_by(**kwargs).one() except sqlalchemy.exceptions.InvalidRequestError: return cls(**kwargs) Base = declarative_base() class Administrator(Uniquer, Base): __tablename__ = 'administrator' id = Column(Integer, primary_key=True) name = Column(Unicode(20), unique=True) email = Column(Unicode(64)) admin_client = Table('admin_client', Base.metadata, Column('admin_id', Integer, ForeignKey('administrator.id')), Column('client_id', Integer, ForeignKey('client.id'))) class Client(Uniquer, Base): __tablename__ = 'client' id = Column(Integer, primary_key=True) name = Column(Unicode(64), unique=True) admins = relation("Administrator", secondary=admin_client) active = Column(Boolean, default=True) online = Column(Boolean, default=True) online_ts = Column(DateTime) class Group(Uniquer, Base): __tablename__ = 'group' id = Column(Integer, primary_key=True) name = Column(Unicode(32), unique=True) class ConnectorKeyVal(Uniquer, Base): __tablename__ = 'connkeyval' id = Column(Integer, primary_key=True) connector = Column(Unicode(16)) key = Column(Unicode(32)) value = Column(UnicodeText) meta_group = Table('meta_group', Base.metadata, Column('metadata_id', Integer, ForeignKey('metadata.id')), Column('group_id', Integer, ForeignKey('group.id'))) meta_conn = Table('meta_conn', Base.metadata, Column('metadata_id', Integer, ForeignKey('metadata.id')), Column('connkeyval_id', Integer, ForeignKey('connkeyval.id'))) class Metadata(Base): __tablename__ = 'metadata' id = Column(Integer, primary_key=True) client_id = Column(Integer, ForeignKey('client.id')) client = relation(Client) groups = relation("Group", secondary=meta_group) keyvals = relation(ConnectorKeyVal, secondary=meta_conn) timestamp = Column(DateTime) @classmethod def from_metadata(cls, mysession, mymetadata): client = Client.by_value(mysession, name=unicode(mymetadata.hostname)) m = cls(client=client) for group in mymetadata.groups: m.groups.append(Group.by_value(mysession, name=unicode(group))) for connector in mymetadata.connectors: data = getattr(mymetadata, connector) if not isinstance(data, dict): continue for key, value in data.iteritems(): if not isinstance(value, str): continue m.keyvals.append(ConnectorKeyVal.by_value(mysession, connector=unicode(connector), key=unicode(key), value=unicode(value))) return m class Package(Base, Uniquer): __tablename__ = 'package' id = Column(Integer, primary_key=True) name = Column(Unicode(24)) type = Column(Unicode(16)) version = Column(Unicode(16)) verification_status = Column(Boolean) class PackageCorrespondence(Base): __tablename__ = 'package_pair' id = Column(Integer, primary_key=True) start_id = Column(Integer, ForeignKey('package.id')) start = relation(Package, primaryjoin=start_id == Package.id) end_id = Column(Integer, ForeignKey('package.id'), nullable=True) end = relation(Package, primaryjoin=end_id == Package.id) modified = Column(Boolean) correct = Column(Boolean) @classmethod def from_record(cls, mysession, name, record): (mod, corr, ptype, s_vers, e_vers) = record start = Package.by_value(mysession, name=unicode(name), type=ptype, version=s_vers) if s_vers != e_vers: end = Package.by_value(mysession, name=unicode(name), type=ptype, version=e_vers) else: end = start return cls(start=start, end=end, modified=mod, correct=corr) package_snap = Table('package_snap', Base.metadata, Column('ppair_id', Integer, ForeignKey('package_pair.id')), Column('snapshot_id', Integer, ForeignKey('snapshot.id'))) class Service(Base, Uniquer): __tablename__ = 'service' id = Column(Integer, primary_key=True) name = Column(Unicode(16)) type = Column(Unicode(12)) status = Column(Boolean) class ServiceCorrespondence(Base): __tablename__ = 'service_pair' id = Column(Integer, primary_key=True) start_id = Column(Integer, ForeignKey('service.id')) start = relation(Service, primaryjoin=start_id == Service.id) end_id = Column(Integer, ForeignKey('service.id'), nullable=True) end = relation(Service, primaryjoin=end_id == Service.id) modified = Column(Boolean) correct = Column(Boolean) @classmethod def from_record(cls, mysession, name, record): (mod, corr, ptype, s_status, e_status) = record start = Service.by_value(mysession, name=unicode(name), type=ptype, status=s_status) if s_status != e_status: end = Service.by_value(mysession, name=unicode(name), type=ptype, status=e_status) else: end = start return cls(start=start, end=end, modified=mod, correct=corr) service_snap = Table('service_snap', Base.metadata, Column('spair_id', Integer, ForeignKey('service_pair.id')), Column('snapshot_id', Integer, ForeignKey('snapshot.id'))) class File(Base): __tablename__ = 'file' id = Column(Integer, primary_key=True) name = Column(UnicodeText) type = Column(Unicode(12)) owner = Column(Unicode(12)) group = Column(Unicode(16)) perms = Column(Integer(5)) contents = Column(UnicodeText) class FileCorrespondence(Base): __tablename__ = 'file_pair' id = Column(Integer, primary_key=True) start_id = Column(Integer, ForeignKey('file.id')) start = relation(File, primaryjoin=start_id == File.id) end_id = Column(Integer, ForeignKey('file.id'), nullable=True) end = relation(File, primaryjoin=end_id == File.id) modified = Column(Boolean) correct = Column(Boolean) file_snap = Table('file_snap', Base.metadata, Column('fpair_id', Integer, ForeignKey('file_pair.id')), Column('snapshot_id', Integer, ForeignKey('snapshot.id'))) extra_pkg_snap = Table('extra_pkg_snap', Base.metadata, Column('package_id', Integer, ForeignKey('package.id')), Column('snapshot_id', Integer, ForeignKey('snapshot.id'))) extra_file_snap = Table('extra_file_snap', Base.metadata, Column('file_id', Integer, ForeignKey('file.id')), Column('snapshot_id', Integer, ForeignKey('snapshot.id'))) extra_service_snap = Table('extra_service_snap', Base.metadata, Column('service_id', Integer, ForeignKey('service.id')), Column('snapshot_id', Integer, ForeignKey('snapshot.id'))) class Action(Base): __tablename__ = 'action' id = Column(Integer, primary_key=True) command = Column(UnicodeText) return_code = Column(Integer) output = Column(UnicodeText) action_snap = Table('action_snap', Base.metadata, Column('action_id', Integer, ForeignKey('action.id')), Column('snapshot_id', Integer, ForeignKey('snapshot.id'))) class Snapshot(Base): __tablename__ = 'snapshot' id = Column(Integer, primary_key=True) metadata_id = Column(Integer, ForeignKey('metadata.id')) client_metadata = relation(Metadata, primaryjoin=metadata_id==Metadata.id) timestamp = Column(DateTime, default=datetime.datetime.now) client_id = Column(Integer, ForeignKey('client.id')) client = relation(Client, backref=backref('snapshots')) packages = relation(PackageCorrespondence, secondary=package_snap) services = relation(ServiceCorrespondence, secondary=service_snap) files = relation(FileCorrespondence, secondary=file_snap) actions = relation(Action, secondary=action_snap) extra_packages = relation(Package, secondary=extra_pkg_snap) extra_services = relation(Service, secondary=extra_service_snap) extra_files = relation(File, secondary=extra_file_snap) @classmethod def from_data(cls, session, metadata, entries, extra): dbm = Metadata.from_metadata(session, metadata) snap = cls(client_metadata=dbm, timestamp=datetime.datetime.now()) for pkg, pdata in entries['Package'].iteritems(): snap.packages.append(\ PackageCorrespondence.from_record(session, pkg, pdata)) for data in extra['Package']: extra_pkg = Package.by_value(session, **data) snap.extra_packages.append(extra_pkg) for pkg, pdata in entries['Service'].iteritems(): snap.services.append(\ ServiceCorrespondence.from_record(session, pkg, pdata)) for data in extra['Service']: extra_svc = Service.by_value(session, **data) snap.extra_services.append(extra_svc) return snap