class AccountCreateOperation(Base):
"""
Steem Blockchain Example
======================
{
"creator": "hello",
"json_metadata": "{}",
"owner": {
"key_auths": [
[
"STM8MN3FNBa8WbEpxz3wGL3L1mkt6sGnncH8iuto7r8Wa3T9NSSGT",
1
]
],
"account_auths": [],
"weight_threshold": 1
},
"memo_key": "STM6Gkj27XMkoGsr4zwEvkjNhh4dykbXmPFzHhT8g86jWsqu3U38X",
"fee": "0.000 STEEM",
"active": {
"key_auths": [
[
"STM8HCf7QLUexogEviN8x1SpKRhFwg2sc8LrWuJqv7QsmWrua6ZyR",
1
]
],
"account_auths": [],
"weight_threshold": 1
},
"posting": {
"key_auths": [
[
"STM8EhGWcEuQ2pqCKkGHnbmcTNpWYZDjGTT7ketVBp4gUStDr2brz",
1
]
],
"account_auths": [],
"weight_threshold": 1
},
"new_account_name": "fabian"
}
"""
__tablename__ = 'sbds_op_account_creates'
__table_args__ = (
PrimaryKeyConstraint('block_num', 'transaction_num', 'operation_num'),
ForeignKeyConstraint(['creator'], ['sbds_meta_accounts.name'],
deferrable=True, initially='DEFERRED', use_alter=True),
ForeignKeyConstraint(['new_account_name'], ['sbds_meta_accounts.name'],
deferrable=True, initially='DEFERRED', use_alter=True),)
block_num = Column(Integer, nullable=False, index=True)
transaction_num = Column(SmallInteger, nullable=False, index=True)
operation_num = Column(SmallInteger, nullable=False, index=True)
trx_id = Column(String(40),nullable=False)
timestamp = Column(DateTime(timezone=False))
fee = Column(Numeric(20,6), nullable=False) # steem_type:asset
fee_symbol = Column(String(5)) # steem_type:asset
creator = Column(String(16)) # steem_type:account_name_type
new_account_name = Column(String(16)) # steem_type:account_name_type
owner = Column(JSONB) # steem_type:authority
active = Column(JSONB) # steem_type:authority
posting = Column(JSONB) # steem_type:authority
memo_key = Column(String(60), nullable=False) # steem_type:public_key_type
json_metadata = Column(JSONB) # name:json_metadata
operation_type = Column(operation_types_enum,nullable=False,index=True,default='account_create')
_fields = dict(
fee=lambda x: amount_field(x.get('fee'), num_func=float), # steem_type:asset
fee_symbol=lambda x: amount_symbol_field(x.get('fee')), # steem_type:asset
owner=lambda x:json_string_field(x.get('owner')), # steem_type:authority
active=lambda x: json_string_field(x.get('active')), # name:active
posting=lambda x: json_string_field(x.get('posting')), # name:posting
json_metadata=lambda x: json_string_field(x.get('json_metadata')), # name:json_metadata
)
_account_fields = frozenset(['creator','new_account_name',])
def dump(self):
return dissoc(self.__dict__, '_sa_instance_state')
def to_dict(self, decode_json=True):
data_dict = self.dump()
if isinstance(data_dict.get('json_metadata'), str) and decode_json:
data_dict['json_metadata'] = sbds.sbds_json.loads(
data_dict['json_metadata'])
return data_dict
def to_json(self):
data_dict = self.to_dict()
return sbds.sbds_json.dumps(data_dict)
def __repr__(self):
return "<%s (block_num:%s transaction_num: %s operation_num: %s keys: %s)>" % (
self.__class__.__name__, self.block_num, self.transaction_num,
self.operation_num, tuple(self.dump().keys()))
def __str__(self):
return str(self.dump())
Column('name', String)
)
addressesTable = Table('address', metadata,
Column('id', Integer, primary_key=True),
Column('email_address', String(100), nullable=False),
Column('user_id', Integer, ForeignKey('user.id')) # a mojno ForeignKeyConstraint
)
anotherOneTable = Table('another', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('another name', String),
Column('another_id', Integer),
ForeignKeyConstraint(
['id', 'another_id'],
['address.id', 'address.user_id']
)
)
# ______________________________________________________________
# **************************************************************
# creating
metadata.create_all(e)
# dropping(deleting) of table
myTable.drop(e)
# reflexion
# user_reflected = Table('user', metadata, autoload=True, autoload_with=e)
print(userTable.c) # not displayed
class PembayaranSppt(pbb_Base, CommonModel):
__tablename__ = 'pembayaran_sppt'
__table_args__ = (
ForeignKeyConstraint(['kd_propinsi','kd_dati2','kd_kecamatan','kd_kelurahan',
'kd_blok', 'no_urut','kd_jns_op', 'thn_pajak_sppt'],
['sppt.kd_propinsi', 'sppt.kd_dati2',
'sppt.kd_kecamatan','sppt.kd_kelurahan',
'sppt.kd_blok', 'sppt.no_urut',
'sppt.kd_jns_op','sppt.thn_pajak_sppt']),
{'extend_existing':True, 'autoload':True,
'schema': pbb_Base.pbb_schema})
#sppt = relationship("Sppt",
# backref=backref('pembayaransppt'),
# primaryjoin='foreign(Sppt.no_urut) == remote(PembyaranSppt.no_urut)')
@classmethod
def query_data(cls):
return pbb_DBSession.query(cls)
@classmethod
def get_by_nop(cls, p_nop):
pkey = FixLength(NOP)
pkey.set_raw(p_nop)
query = cls.query_data()
return query.filter_by(kd_propinsi = pkey['kd_propinsi'],
kd_dati2 = pkey['kd_dati2'],
kd_kecamatan = pkey['kd_kecamatan'],
kd_kelurahan = pkey['kd_kelurahan'],
kd_blok = pkey['kd_blok'],
no_urut = pkey['no_urut'],
kd_jns_op = pkey['kd_jns_op'],)
@classmethod
def get_by_nop_thn(cls, p_nop, p_tahun):
query = cls.get_by_nop(p_nop)
return query.filter_by(thn_pajak_sppt = p_tahun)
@classmethod
def get_by_kelurahan(cls, p_kode, p_tahun):
pkey = FixLength(DESA)
pkey.set_raw(p_kode)
query = cls.query_data()
return query.filter_by(kd_propinsi = pkey['kd_propinsi'],
kd_dati2 = pkey['kd_dati2'],
kd_kecamatan = pkey['kd_kecamatan'],
kd_kelurahan = pkey['kd_kelurahan'],
thn_pajak_sppt = p_tahun)
@classmethod
def get_by_kecamatan(cls, p_kode, p_tahun):
pkey = FixLength(KECAMATAN)
pkey.set_raw(p_kode)
query = cls.query_data()
return query.filter_by(kd_propinsi = pkey['kd_propinsi'],
kd_dati2 = pkey['kd_dati2'],
kd_kecamatan = pkey['kd_kecamatan'],
kd_kelurahan = pkey['kd_kelurahan'],
thn_pajak_sppt = p_tahun)
@classmethod
def get_by_tanggal(cls, p_kode, p_tahun):
pkey = DateVar
p_kode = re.sub("[^0-9]", "", p_kode)
pkey.set_raw(p_kode)
query = cls.query_data()
return query.filter_by(tgl_pembayaran_sppt = pkey.get_value)
@classmethod
def get_rekap_by_kecamatan(cls, p_kode, p_tahun):
pkey = FixLength(KECAMATAN)
pkey.set_raw(p_kode)
query = pbb_DBSession.query(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan, cls.kd_kelurahan,
func.sum(cls.denda_sppt).label('denda'),
func.sum(cls.pbb_yg_dibayar_sppt).label('jumlah') ).\
group_by(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan, cls.kd_kelurahan)
return query.filter_by(kd_propinsi = pkey['kd_propinsi'],
kd_dati2 = pkey['kd_dati2'],
kd_kecamatan = pkey['kd_kecamatan'],
thn_pajak_sppt = p_tahun)
@classmethod
def get_rekap_by_thn(cls, p_tahun):
query = pbb_DBSession.query(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan,
func.sum(cls.denda_sppt).label('denda'),
func.sum(cls.pbb_yg_dibayar_sppt).label('jumlah')).\
group_by(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan)
return query.filter_by(thn_pajak_sppt = p_tahun)
from sqlalchemy import MetaData, Table, create_engine
from sqlalchemy import select
from sqlalchemy import ForeignKeyConstraint
metadata = MetaData()
engine = create_engine('sqlite:///Chinook_Sqlite.sqlite')
artist = Table('Artist', metadata, autoload=True, autoload_with=engine)
album = Table('album', metadata, autoload=True, autoload_with=engine)
if __name__ == '__main__':
print(album.foreign_keys)
print(metadata.tables['Artist'] is artist)
print(metadata.tables['album'])
print(artist.columns.keys())
s = select([artist]).limit(10)
print(engine.execute(s).fetchall())
print(album.foreign_keys)
album.append_constraint(ForeignKeyConstraint(['ArtistId'], ['Artist.ArtistId']))
print(album.foreign_keys)
print(str(artist.join(album)))
metadata.reflect(bind=engine)
print(metadata.tables.keys())
playlist = metadata.tables['Playlist']
s = select([playlist]).limit(10)
print(engine.execute(s).fetchall())
class FilesystemAnalysis(Base):
"""
A unified and compressed record of the filesystem-level entries in an image. An alternative to the FilesystemItem approach,
this allows much faster index operations due to a smaller index, but no queries into the content of the filesystems themselves.
"""
__tablename__ = 'image_fs_analysis_dump'
compression_level = 6
supported_algorithms = ['gzip']
image_id = Column(String(image_id_length), primary_key=True)
image_user_id = Column(String(user_id_length), primary_key=True)
compressed_content_hash = Column(String(digest_length))
compressed_file_json = Column(LargeBinary, nullable=False)
total_entry_count = Column(Integer, default=0)
file_count = Column(Integer, default=0)
directory_count = Column(Integer, default=0)
non_packaged_count = Column(Integer, default=0)
suid_count = Column(Integer, default=0)
image = relationship('Image', back_populates='fs')
compression_algorithm = Column(String(32), default='gzip')
created_at = Column(DateTime, default=datetime.datetime.utcnow)
updated_at = Column(DateTime,
default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow)
image = relationship('Image', back_populates='fs')
_files = None
__table_args__ = (ForeignKeyConstraint(
columns=[image_id, image_user_id],
refcolumns=['images.id', 'images.user_id']), {})
# NOTE: operations on the content of the dict itself will not trigger dirty updates and a flush to db,
# must explicitly set the value to a new dict if writes need to be persisted.
@property
def files(self):
if not self._files:
self._files = self._files_json()
return self._files
@files.setter
def files(self, value):
self._files = value
self._files_from_json(self._files)
def _files_json(self):
if self.compression_algorithm == 'gzip':
return json.loads(zlib.decompress(self.compressed_file_json))
else:
raise ValueError(
'Got unexpected compresssion algorithm value: {}. Expected {}'.
format(self.compression_algorithm, self.supported_algorithms))
def _files_from_json(self, file_json):
"""
Compress and hash the file_json content
:param file_json:
:return:
"""
self.compressed_file_json = zlib.compress(json.dumps(file_json))
self.compression_algorithm = 'gzip'
self.compressed_content_hash = hashlib.sha256(
self.compressed_file_json).hexdigest()
from sqlalchemy import Column, ForeignKey, ForeignKeyConstraint, Index, Integer, MetaData, Table
from galaxy.model.custom_types import JSONType, TrimmedString, UUIDType
from galaxy.model.migrate.versions.util import add_column, alter_column, create_table, drop_column, drop_table
log = logging.getLogger(__name__)
metadata = MetaData()
WorkflowInvocationToSubworkflowInvocationAssociation_table = Table(
"workflow_invocation_to_subworkflow_invocation_association", metadata,
Column("id", Integer, primary_key=True),
Column("workflow_invocation_id", Integer),
Column("subworkflow_invocation_id", Integer),
Column("workflow_step_id", Integer),
ForeignKeyConstraint(['workflow_invocation_id'], ['workflow_invocation.id'], name='fk_wfi_swi_wfi'),
ForeignKeyConstraint(['subworkflow_invocation_id'], ['workflow_invocation.id'], name='fk_wfi_swi_swi'),
ForeignKeyConstraint(['workflow_step_id'], ['workflow_step.id'], name='fk_wfi_swi_ws')
)
WorkflowRequestInputStepParameter_table = Table(
"workflow_request_input_step_parameter", metadata,
Column("id", Integer, primary_key=True),
Column("workflow_invocation_id", Integer),
Column("workflow_step_id", Integer),
Column("parameter_value", JSONType),
ForeignKeyConstraint(['workflow_invocation_id'], ['workflow_invocation.id'], name='fk_wfreq_isp_wfi'),
ForeignKeyConstraint(['workflow_step_id'], ['workflow_step.id'], name='fk_wfreq_isp_ws')
)
TABLES = [
backref=u'commitaltypes')
commitaltype_prisoncommital = Table(
'commitaltype_prisoncommital', Model.metadata,
Column('commitaltype',
ForeignKey(u'commitaltype.id'),
primary_key=True,
nullable=False),
Column('prisoncommital_prison', Integer, primary_key=True, nullable=False),
Column('prisoncommital_warrantno',
String(100),
primary_key=True,
nullable=False),
ForeignKeyConstraint(
['prisoncommital_prison', 'prisoncommital_warrantno'],
[u'prisoncommital.prison', u'prisoncommital.warrantno']),
Index('idx_commitaltype_prisoncommital', 'prisoncommital_prison',
'prisoncommital_warrantno'))
class Constituency(RefTypeMixin, AuditMixin, Model):
__tablename__ = 'constituency'
id = Column(Integer, primary_key=True, autoincrement=True)
county = Column(ForeignKey(u'county.id'), nullable=False, index=True)
town = Column(ForeignKey(u'town.id'), index=True)
county1 = relationship(u'County',
primaryjoin='Constituency.county == County.id',
backref=u'constituencies')
class N_Trace(Base, _Trace):
__tablename__ = "n_trace"
__table_args__ = (
ForeignKeyConstraint(['scan'], ['n_scan.id'], ondelete='CASCADE'),
)
class V_Port(Base, _Port):
__tablename__ = 'v_port'
__table_args__ = (
ForeignKeyConstraint(['scan'], ['v_scan.id'], ondelete='CASCADE'),
Index('ix_v_port_scan_port', 'scan', 'port', 'protocol', unique=True),
)
def factory(**kw):
return ForeignKeyConstraint(["b"], ["tbl.a"], **kw)
class FillTransferFromSavingsVirtualOperation(Base):
"""
Steem Blockchain Example
======================
"""
__tablename__ = 'sbds_op_virtual_fill_transfer_from_saving'
__table_args__ = (
ForeignKeyConstraint(['from'], ['sbds_meta_accounts.name'],
deferrable=True, initially='DEFERRED', use_alter=True),
ForeignKeyConstraint(['to'], ['sbds_meta_accounts.name'],
deferrable=True, initially='DEFERRED', use_alter=True),)
id = Column(Integer, primary_key=True)
block_num = Column(Integer, nullable=False, index=True)
transaction_num = Column(SmallInteger, nullable=False, index=True)
operation_num = Column(SmallInteger, nullable=False, index=True)
trx_id = Column(String(40),nullable=False)
timestamp = Column(DateTime(timezone=False))
_from = Column('from',String(16)) # name:from
to = Column(String(16)) # steem_type:account_name_type
amount = Column(Numeric(20,6), nullable=False) # steem_type:asset
amount_symbol = Column(String(5)) # steem_type:asset
request_id = Column(Numeric) # steem_type:uint32_t
memo = Column(UnicodeText) # name:memo
operation_type = Column(operation_types_enum,nullable=False,index=True,default='fill_transfer_from_savings')
_fields = dict(
amount=lambda x: amount_field(x.get('amount'), num_func=float), # steem_type:asset
amount_symbol=lambda x: amount_symbol_field(x.get('amount')), # steem_type:asset
)
_account_fields = frozenset(['from','to',])
def dump(self):
return dissoc(self.__dict__, '_sa_instance_state')
def to_dict(self, decode_json=True):
data_dict = self.dump()
if isinstance(data_dict.get('json_metadata'), str) and decode_json:
data_dict['json_metadata'] = sbds.sbds_json.loads(
data_dict['json_metadata'])
return data_dict
def to_json(self):
data_dict = self.to_dict()
return sbds.sbds_json.dumps(data_dict)
def __repr__(self):
return "<%s (block_num:%s transaction_num: %s operation_num: %s keys: %s)>" % (
self.__class__.__name__, self.block_num, self.transaction_num,
self.operation_num, tuple(self.dump().keys()))
def __str__(self):
return str(self.dump())
def __repr__(self):
return "<FeedInfo %s>" % self.feed_publisher_name
# Many-to-many secondary tables. Make sure to also load them properly in
# loader.py
_stop_translations = Table(
'_stop_translations',
Base.metadata,
Column('stop_feed_id', Integer),
Column('translation_feed_id', Integer),
Column('stop_id', Unicode),
Column('trans_id', Unicode),
Column('lang', Unicode),
ForeignKeyConstraint(['stop_feed_id', 'stop_id'],
[Stop.feed_id, Stop.stop_id]),
ForeignKeyConstraint(
['translation_feed_id', 'trans_id', 'lang'],
[Translation.feed_id, Translation.trans_id, Translation.lang]),
)
_trip_shapes = Table(
'_trip_shapes',
Base.metadata,
Column('trip_feed_id', Integer),
Column('shape_feed_id', Integer),
Column('trip_id', Unicode),
Column('shape_id', Unicode),
Column('shape_pt_sequence', Integer),
ForeignKeyConstraint(['trip_feed_id', 'trip_id'],
[Trip.feed_id, Trip.trip_id]),
def _history_mapper(local_mapper): # noqa (C901 too complex)
cls = local_mapper.class_
# set the "active_history" flag
# on on column-mapped attributes so that the old version
# of the info is always loaded (currently sets it on all attributes)
for prop in local_mapper.iterate_properties:
getattr(local_mapper.class_, prop.key).impl.active_history = True
super_mapper = local_mapper.inherits
super_history_mapper = getattr(cls, '__history_mapper__', None)
polymorphic_on = None
super_fks = []
def _col_copy(col):
orig = col
col = col.copy()
orig.info['history_copy'] = col
col.unique = False
# if the column is nullable, we could end up overwriting an on-purpose null value with a default.
# if it's not nullable, however, the default may be relied upon to correctly set values within the database,
# so we should preserve it
if col.nullable:
col.default = col.server_default = None
return col
properties = util.OrderedDict()
if not super_mapper or \
local_mapper.local_table is not super_mapper.local_table:
cols = []
version_meta = {"version_meta": True}
for column in local_mapper.local_table.c:
if _is_versioning_col(column):
continue
col = _col_copy(column)
if super_mapper and \
col_references_table(column, super_mapper.local_table):
super_fks.append(
(col.key,
list(super_history_mapper.local_table.primary_key)[0]))
cols.append(col)
if column is local_mapper.polymorphic_on:
polymorphic_on = col
orig_prop = local_mapper.get_property_by_column(column)
# carry over column re-mappings
if len(orig_prop.columns) > 1 or \
orig_prop.columns[0].key != orig_prop.key:
properties[orig_prop.key] = tuple(col.info['history_copy']
for col in orig_prop.columns)
if super_mapper:
super_fks.append(
('version', super_history_mapper.local_table.c.version))
# "version" stores the integer version id. This column is
# required.
cols.append(
Column('version',
Integer,
primary_key=True,
autoincrement=False,
info=version_meta))
if super_fks:
cols.append(ForeignKeyConstraint(*zip(*super_fks)))
table = Table(local_mapper.local_table.name + '_history',
local_mapper.local_table.metadata,
*cols,
schema=local_mapper.local_table.schema)
else:
# single table inheritance. take any additional columns that may have
# been added and add them to the history table.
for column in local_mapper.local_table.c:
if column.key not in super_history_mapper.local_table.c:
col = _col_copy(column)
super_history_mapper.local_table.append_column(col)
table = None
if super_history_mapper:
bases = (super_history_mapper.class_, )
if table is not None:
properties['changed'] = (
(table.c.changed, ) +
tuple(super_history_mapper.attrs.changed.columns))
else:
bases = local_mapper.base_mapper.class_.__bases__
versioned_cls = type.__new__(type, "%sHistory" % cls.__name__, bases, {})
m = mapper(versioned_cls,
table,
inherits=super_history_mapper,
polymorphic_on=polymorphic_on,
polymorphic_identity=local_mapper.polymorphic_identity,
properties=properties)
cls.__history_mapper__ = m
if not super_history_mapper:
local_mapper.local_table.append_column(
Column('version', Integer, default=1, nullable=False))
local_mapper.add_property("version",
local_mapper.local_table.c.version)
class FeedPublishOperation(Base):
"""
dPay Blockchain Example
======================
{
"exchange_rate": {
"quote": "1000.000 BEX",
"base": "1.000 BBD"
},
"publisher": "abit"
}
"""
__tablename__ = 'dpds_op_feed_publishes'
__table_args__ = (
PrimaryKeyConstraint('block_num', 'transaction_num', 'operation_num'),
ForeignKeyConstraint(['publisher'], ['dpds_meta_accounts.name'],
deferrable=True,
initially='DEFERRED',
use_alter=True),
)
block_num = Column(Integer, nullable=False, index=True)
transaction_num = Column(SmallInteger, nullable=False, index=True)
operation_num = Column(SmallInteger, nullable=False, index=True)
trx_id = Column(String(40), nullable=False)
timestamp = Column(DateTime(timezone=False))
publisher = Column(String(16)) # dpay_type:account_name_type
exchange_rate = Column(JSONB) # dpay_type:price
operation_type = Column(operation_types_enum,
nullable=False,
index=True,
default='feed_publish')
_fields = dict(
exchange_rate=lambda x: json_string_field(x.get('exchange_rate')
), # dpay_type:price
)
_account_fields = frozenset([
'publisher',
])
def dump(self):
return dissoc(self.__dict__, '_sa_instance_state')
def to_dict(self, decode_json=True):
data_dict = self.dump()
if isinstance(data_dict.get('json_metadata'), str) and decode_json:
data_dict['json_metadata'] = dpds.dpds_json.loads(
data_dict['json_metadata'])
return data_dict
def to_json(self):
data_dict = self.to_dict()
return dpds.dpds_json.dumps(data_dict)
def __repr__(self):
return "<%s (block_num:%s transaction_num: %s operation_num: %s keys: %s)>" % (
self.__class__.__name__, self.block_num, self.transaction_num,
self.operation_num, tuple(self.dump().keys()))
def __str__(self):
return str(self.dump())
class CommentOptionsOperation(Base):
"""
Steem Blockchain Example
======================
{
"allow_curation_rewards": true,
"allow_votes": true,
"permlink": "testing6",
"percent_steem_dollars": 5000,
"max_accepted_payout": "1000.000 SBD",
"author": "testing001",
"extensions": []
}
"""
__tablename__ = 'sbds_op_comment_option'
__table_args__ = (
PrimaryKeyConstraint('block_num', 'transaction_num', 'operation_num'),
ForeignKeyConstraint(['author'], ['sbds_meta_accounts.name'],
deferrable=True,
initially='DEFERRED',
use_alter=True),
)
block_num = Column(Integer, nullable=False, index=True)
transaction_num = Column(SmallInteger, nullable=False, index=True)
operation_num = Column(SmallInteger, nullable=False, index=True)
trx_id = Column(String(40), nullable=False)
timestamp = Column(DateTime(timezone=False))
author = Column(String(16)) # steem_type:account_name_type
permlink = Column(Unicode(256), index=True) # name:permlink
max_accepted_payout = Column(Numeric(20, 6),
nullable=False) # steem_type:asset
max_accepted_payout_symbol = Column(String(5)) # steem_type:asset
percent_steem_dollars = Column(Integer) # steem_type:uint16_t
allow_votes = Column(Boolean) # steem_type:bool
allow_curation_rewards = Column(Boolean) # steem_type:bool
extensions = Column(
JSONB) # steem_type:steemit::protocol::comment_options_extensions_type
operation_type = Column(operation_types_enum,
nullable=False,
index=True,
default='comment_options')
_fields = dict(
max_accepted_payout=lambda x: amount_field(
x.get('max_accepted_payout'), num_func=float), # steem_type:asset
max_accepted_payout_symbol=lambda x: amount_symbol_field(
x.get('max_accepted_payout')), # steem_type:asset
extensions=lambda x: json_string_field(
x.get('extensions')
), # steem_type:steemit::protocol::comment_options_extensions_type
)
_account_fields = frozenset([
'author',
])
def dump(self):
return dissoc(self.__dict__, '_sa_instance_state')
def to_dict(self, decode_json=True):
data_dict = self.dump()
if isinstance(data_dict.get('json_metadata'), str) and decode_json:
data_dict['json_metadata'] = sbds.sbds_json.loads(
data_dict['json_metadata'])
return data_dict
def to_json(self):
data_dict = self.to_dict()
return sbds.sbds_json.dumps(data_dict)
def __repr__(self):
return "<%s (block_num:%s transaction_num: %s operation_num: %s keys: %s)>" % (
self.__class__.__name__, self.block_num, self.transaction_num,
self.operation_num, tuple(self.dump().keys()))
def __str__(self):
return str(self.dump())
class V_Hop(Base, _Hop):
__tablename__ = "v_hop"
__table_args__ = (
Index('ix_v_hop_ipaddr_ttl', 'ipaddr', 'ttl'),
ForeignKeyConstraint(['trace'], ['v_trace.id'], ondelete='CASCADE')
)
def _initTablespostgresV2(self):
"""
FIXME: to check
PostgreSQL db tables for audit database version 2
"""
nowsystem = time.strftime("%Y-%m-%d %H:%M:%S")
self.module_table = Table("module", self.metadata,
Column("id", Integer, primary_key=True),
Column("name", String(15), nullable=False))
self.event_table = Table(
"event", self.metadata,
Column("id", Integer, primary_key=True, autoincrement=True),
Column("module_id", Integer, ForeignKey('module.id')),
Column("name", String(50), nullable=False))
self.source_table = Table(
"source", self.metadata, Column("id", Integer, primary_key=True),
Column("hostname", String(32), nullable=False))
self.param_table = Table(
"parameters", self.metadata, Column("id",
Integer,
primary_key=True),
Column("param_name", String(50)),
Column("param_value", String(1024)),
Column("record_id", Integer, ForeignKey('log.id')))
self.initiator_table = Table(
"initiator", self.metadata, Column("id", Integer,
primary_key=True),
Column("application", String(64), nullable=False),
Column("hostname", String(32)))
self.type_table = Table("type", self.metadata,
Column("id", Integer, primary_key=True),
Column("type", String(20), nullable=False))
self.object_table = Table(
"object", self.metadata, Column("id", Integer, primary_key=True),
Column("_uri", String(30), nullable=False),
Column("type_id", Integer, ForeignKey('type.id')),
Column("parent", Integer, ForeignKey('object.id')))
self.object_log_table = Table(
"object_log", self.metadata, Column("id",
Integer,
primary_key=True),
Column("object_id", Integer, ForeignKey('object.id')),
Column("record_id", Integer, ForeignKey('log.id')))
self.previous_value_table = Table(
"previous_value", self.metadata,
Column("id", Integer, primary_key=True),
Column("object_log_id", Integer, ForeignKey('object_log.id')),
Column("value", String(1024)))
self.current_value_table = Table(
"current_value", self.metadata,
Column("id", Integer, primary_key=True),
Column("object_log_id", Integer, ForeignKey('object_log.id')),
Column("value", String(1024)))
self.record_table = Table(
"record", self.metadata, Column("id", Integer, primary_key=True),
Column("date", DateTime, default=nowsystem, nullable=False),
Column("result", Boolean, nullable=False),
Column("initiator_id",
Integer,
ForeignKey('initiator.id'),
nullable=False),
Column("source_id", Integer, ForeignKey('source.id')),
Column("event_id", Integer), Column("module_id", Integer),
Column("user_id", Integer, ForeignKey('object.id')),
ForeignKeyConstraint(('event_id', 'module_id'),
('event.id', 'event.module_id')))
Column("l4", Integer()),
Column("l5", Integer()),
UniqueConstraint("classifier", name="trove_classifiers_classifier_key"),
Index("trove_class_id_idx", "id"),
Index("trove_class_class_idx", "classifier"),
)
release_classifiers = Table(
"release_classifiers",
Warehouse.metadata,
Column("name", UnicodeText()),
Column("version", UnicodeText()),
Column("trove_id", Integer(), ForeignKey("trove_classifiers.id")),
ForeignKeyConstraint(
["name", "version"],
["releases.name", "releases.version"],
onupdate="CASCADE",
),
Index("rel_class_name_idx", "name"),
Index("rel_class_version_id_idx", "version"),
Index("rel_class_name_version_idx", "name", "version"),
Index("rel_class_trove_id_idx", "trove_id"),
)
packages = Table(
"packages",
Warehouse.metadata,
Column("name", UnicodeText(), primary_key=True, nullable=False),
Column("stable_version", UnicodeText()),
Column("normalized_name", UnicodeText()),
Column("autohide", Boolean(), server_default=sql.true()),
class LiquidityRewardVirtualOperation(Base):
"""
dPay Blockchain Example
======================
"""
__tablename__ = 'dpds_op_virtual_liquidity_rewards'
__table_args__ = (ForeignKeyConstraint(['owner'],
['dpds_meta_accounts.name'],
deferrable=True,
initially='DEFERRED',
use_alter=True), )
id = Column(Integer, primary_key=True)
block_num = Column(Integer, nullable=False, index=True)
transaction_num = Column(SmallInteger, nullable=False, index=True)
operation_num = Column(SmallInteger, nullable=False, index=True)
trx_id = Column(String(40), nullable=False)
timestamp = Column(DateTime(timezone=False))
owner = Column(String(16)) # dpay_type:account_name_type
payout = Column(Numeric(20, 6), nullable=False) # dpay_type:asset
payout_symbol = Column(String(5)) # dpay_type:asset
operation_type = Column(operation_types_enum,
nullable=False,
index=True,
default='liquidity_reward')
_fields = dict(
payout=lambda x: amount_field(x.get('payout'), num_func=float
), # dpay_type:asset
payout_symbol=lambda x: amount_symbol_field(x.get('payout')
), # dpay_type:asset
)
_account_fields = frozenset([
'owner',
])
def dump(self):
return dissoc(self.__dict__, '_sa_instance_state')
def to_dict(self, decode_json=True):
data_dict = self.dump()
if isinstance(data_dict.get('json_metadata'), str) and decode_json:
data_dict['json_metadata'] = dpds.dpds_json.loads(
data_dict['json_metadata'])
return data_dict
def to_json(self):
data_dict = self.to_dict()
return dpds.dpds_json.dumps(data_dict)
def __repr__(self):
return "<%s (block_num:%s transaction_num: %s operation_num: %s keys: %s)>" % (
self.__class__.__name__, self.block_num, self.transaction_num,
self.operation_num, tuple(self.dump().keys()))
def __str__(self):
return str(self.dump())
class PlantSteamFERC1(pudl.models.entities.PUDLBase):
"""A large thermal generating plant, as reported to FERC on Form 1."""
__tablename__ = 'plants_steam_ferc1'
__table_args__ = (
ForeignKeyConstraint(
['utility_id_ferc1', 'plant_name'],
['plants_ferc.utility_id_ferc1', 'plants_ferc.plant_name']),
{'comment': "Large thermal generating plants, as reported on page 402 of FERC Form 1."}
)
id = Column(Integer, autoincrement=True,
primary_key=True, comment=id_comment)
record_id = Column(String, nullable=False, comment=record_id_comment)
utility_id_ferc1 = Column(Integer, nullable=False,
comment=utility_id_ferc1_comment)
report_year = Column(Integer, nullable=False, comment=report_year_comment)
plant_id_ferc1 = Column(Integer, comment=plant_id_ferc1_comment)
plant_name = Column(String, nullable=False, comment=plant_name_comment)
plant_type = Column(
Enum(*pc.ferc1_plant_kind_strings, name='ferc1_plant_kind'),
comment="Simplified plant type, categorized by PUDL based on our best guess of what was intended based on freeform string reported to FERC. Unidentifiable types are null."
)
construction_type = Column(
construction_type_enum, # Enum, see top of this file
comment=construction_type_comment
)
construction_year = Column(
Integer,
comment="Year the plant's oldest still operational unit was built."
)
installation_year = Column(
Integer,
comment="Year the plant's most recently built unit was installed."
)
capacity_mw = Column(
Float,
comment="Total installed plant capacity in MW."
)
peak_demand_mw = Column(
Float,
comment="Net peak demand experienced by the plant in MW in report year."
)
plant_hours_connected_while_generating = Column(
Float,
comment="Total number hours the plant was generated and connected to load during report year."
)
plant_capability_mw = Column(
Float,
comment="Net continuous plant capability in MW"
)
water_limited_capacity_mw = Column(
Float,
comment="Plant capacity in MW when limited by condenser water."
)
not_water_limited_capacity_mw = Column(
Float,
comment="Plant capacity in MW when not limited by condenser water."
)
avg_num_employees = Column(
Float,
comment="Average number of plant employees during report year."
)
net_generation_mwh = Column(
Float,
comment="Net generation (exclusive of plant use) in MWh during report year."
)
capex_land = Column(
Numeric(14, 2),
comment="Capital expense for land and land rights."
)
capex_structures = Column(
Numeric(14, 2),
comment="Capital expense for structures and improvements."
)
capex_equipment = Column(
Numeric(14, 2),
comment="Capital expense for equipment."
)
capex_total = Column(
Numeric(14, 2),
comment="Total capital expenses."
)
capex_per_mw = Column(
Numeric(14, 2),
comment="Capital expenses per MW of installed plant capacity."
)
opex_operations = Column(
Numeric(14, 2),
comment="Production expenses: operations, supervision, and engineering."
)
opex_fuel = Column(
Numeric(14, 2),
comment="Total cost of fuel."
)
opex_coolants = Column(
Numeric(14, 2),
comment="Cost of coolants and water (nuclear plants only)"
)
opex_steam = Column(
Numeric(14, 2),
comment="Steam expenses."
)
opex_steam_other = Column(
Numeric(14, 2),
comment="Steam from other sources."
)
opex_transfer = Column(
Numeric(14, 2),
comment="Steam transferred (Credit)."
)
opex_electric = Column(
Numeric(14, 2),
comment="Electricity expenses."
)
opex_misc_power = Column(
Numeric(14, 2),
comment="Miscellaneous steam (or nuclear) expenses."
)
opex_rents = Column(
Numeric(14, 2),
comment="Rents."
)
opex_allowances = Column(
Numeric(14, 2),
comment="Allowances."
)
opex_engineering = Column(
Numeric(14, 2),
comment="Maintenance, supervision, and engineering."
)
opex_structures = Column(
Numeric(14, 2),
comment="Maintenance of structures."
)
opex_boiler = Column(
Numeric(14, 2),
comment="Maintenance of boiler (or reactor) plant."
)
opex_plants = Column(
Numeric(14, 2),
comment="Maintenance of electrical plant."
)
opex_misc_steam = Column(
Numeric(14, 2),
comment="Maintenance of miscellaneous steam (or nuclear) plant."
)
opex_production_total = Column(
Numeric(14, 2),
comment="Total operating epxenses."
)
opex_per_mwh = Column(
Numeric(14, 2),
comment="Total operating expenses per MWh of net generation."
)
asset_retirement_cost = Column(
Numeric(14, 2),
comment="Asset retirement cost."
)
class FileAccess(AlchemyProxy):
"""Represent a file access"""
hide_timestamp = False
__tablename__ = "file_access"
__table_args__ = (
PrimaryKeyConstraint("trial_id", "id"),
ForeignKeyConstraint(
["trial_id", "function_activation_id"],
["function_activation.trial_id", "function_activation.id"],
ondelete="CASCADE"),
ForeignKeyConstraint(["trial_id"], ["trial.id"], ondelete="CASCADE"),
)
trial_id = Column(Integer, index=True)
id = Column(Integer, index=True) # pylint: disable=invalid-name
name = Column(Text)
mode = Column(Text)
buffering = Column(Text)
content_hash_before = Column(Text)
content_hash_after = Column(Text)
timestamp = Column(TIMESTAMP)
function_activation_id = Column(Integer, index=True)
trial = backref_one("trial") # Trial.file_accesses
activation = backref_one("activation") # Activation.file_accesses
prolog_description = PrologDescription(
"access", (
PrologTrial("trial_id", link="activation.trial_id"),
PrologAttribute("id", fn=lambda obj: "f{}".format(obj.id)),
PrologRepr("name"),
PrologRepr("mode"),
PrologNullableRepr("content_hash_before"),
PrologNullableRepr("content_hash_after"),
PrologTimestamp("timestamp"),
PrologNullable("activation_id",
attr_name="function_activation_id",
link="activation.id"),
),
description=(
"informs that in a given trial (*trial_id*),\n"
"a file *name*\n"
"was accessed by a function activation (*activation_id*)\n"
"in a specific read or write *mode*\n"
"at a specific *timestamp*.\n"
"The content of the file\n"
"was captured before (*content_hash_before*)\n"
"and after (*content_hash_after*) the access."))
def __init__(self, *args, **kwargs):
if args and isinstance(args[0], relational.base):
obj = args[0]
trial_ref = obj.id
elif args:
trial_ref = kwargs.get("trial_ref", args[0])
else:
trial_ref = kwargs.get("trial_ref", None)
session = relational.session
obj = FileAccess.load_fileaccess(trial_ref, session=session)
super(FileAccess, self).__init__(obj)
@property
def stack(self):
"""Return the activation stack since the beginning of execution"""
stack = []
activation = self.activation
while activation:
name = activation.name
activation = activation.caller
if activation:
stack.insert(0, name)
if not stack or stack[-1] != "open":
stack.append(" ... -> open")
return " -> ".join(stack)
@property
def brief(self):
"""Brief description of file access"""
result = "({0.mode}) {0.name}".format(self)
if self.content_hash_before is None:
result += " (new)"
return result
@property
def activation_id(self):
return self.function_activation_id
@property
def is_internal(self):
return (not os.path.isabs(self.name)
or persistence_config.base_path in self.name)
@classmethod # query
def find_by_name_and_time(cls, name, timestamp, trial=None, session=None):
"""Return the first access according to name and timestamp
Arguments:
name -- specify the desired file
timestamp -- specify the start of finish time of trial
Keyword Arguments:
trial -- limit search in a specific trial_id
"""
model = cls.m
session = session or relational.session
query = (session.query(model).filter((model.name == name) & (
model.timestamp.like(timestamp + "%"))).order_by(model.timestamp))
if trial:
query = query.filter(model.trial_id == trial)
return proxy(query.first())
def __key(self):
return (self.name, self.content_hash_before, self.content_hash_after,
self.mode)
def __hash__(self):
return hash(self.__key())
def __eq__(self, other):
if not isinstance(other, FileAccess):
return False
return ((self.content_hash_before == other.content_hash_before)
and (self.content_hash_after == other.content_hash_after))
def show(self, _print=print):
"""Show object
Keyword arguments:
_print -- custom print function (default=print)
"""
result = """\
Name: {f.name}
Mode: {f.mode}
Buffering: {f.buffering}
Content hash before: {f.content_hash_before}
Content hash after: {f.content_hash_after}
"""
if not self.hide_timestamp:
result += """Timestamp: {f.timestamp}
"""
result += """Function: {f.stack}\
"""
_print(result.format(f=self))
def __repr__(self):
return "FileAccess({0.trial_id}, {0.id}, {0.name}, {0.mode})".format(
self)
@classmethod # query
def load_fileaccess(cls, trial_ref, session=None):
"""Load fileaccess by fileaccess reference
Find reference on trials id and tags name
"""
session = session or relational.session
result = session.query(cls.m).filter(cls.m.trial_id == trial_ref)
return result.first()
def pull_content(cls, tid, session=None):
session = session or relational.session
ttrial = cls.__table__
result = session.query(ttrial).filter(ttrial.c.trial_id == tid).all()
return result
def push_content(cls, id, reslist, session=None):
session = session or relational.session
ttrial = cls.__table__
for res in reslist:
result = session.execute(
ttrial.insert(), {
"trial_id": id,
"id": res.id,
"name": res.name,
"mode": res.mode,
"buffering": res.buffering,
"content_hash_before": res.content_hash_before,
"content_hash_after": res.content_hash_after,
"function_activation_id": res.function_activation_id
})
session.commit()
class FuelFERC1(pudl.models.entities.PUDLBase):
"""
Annual fuel consumed by plant, as reported to FERC in Form 1.
This information comes from the f1_fuel table in the FERC DB, which is
populated from page 402 of the paper FERC Form 1.
"""
__tablename__ = 'fuel_ferc1'
__table_args__ = (
ForeignKeyConstraint(
['utility_id_ferc1', 'plant_name'],
['plants_ferc.utility_id_ferc1', 'plants_ferc.plant_name']),
{'comment': "Annual fuel consumed by large thermal generating plants. As reported on page 402 of FERC Form 1."}
)
# Each year, for each fuel, there's one report for each plant, which may
# be recorded multiple times for multiple utilities that have a stake in
# the plant... Primary key fields: utility, plant, fuel and year.
id = Column(Integer, autoincrement=True,
primary_key=True, comment=id_comment)
record_id = Column(String, nullable=False, comment=record_id_comment)
utility_id_ferc1 = Column(Integer, nullable=False,
comment=utility_id_ferc1_comment)
report_year = Column(Integer, nullable=False, comment=report_year_comment)
plant_id_ferc1 = Column(Integer, comment=plant_id_ferc1_comment)
plant_name = Column(String, nullable=False, comment=plant_name_comment)
fuel_type_code_pudl = Column(
Enum(*pc.ferc1_fuel_strings.keys(),
name='ferc1_pudl_fuel_codes'),
comment="PUDL assigned code indicating the general fuel type."
)
fuel_unit = Column(
Enum(*pc.ferc1_fuel_unit_strings.keys(),
name='ferc1_pudl_fuel_unit_codes'),
comment="PUDL assigned code indicating reported fuel unit of measure."
)
fuel_qty_burned = Column(
Float,
nullable=False,
comment="Quantity of fuel consumed in the report year, in terms of the reported fuel units."
)
fuel_mmbtu_per_unit = Column(
Float,
nullable=False,
comment="Average heat content of fuel consumed in the report year, in mmBTU per reported fuel unit."
)
fuel_cost_per_unit_burned = Column(
Float,
nullable=False,
comment="Average cost of fuel consumed in the report year, in nominal USD per reported fuel unit."
)
fuel_cost_per_unit_delivered = Column(
Float,
nullable=False,
comment="Average cost of fuel delivered in the report year, in nominal USD per reported fuel unit."
)
fuel_cost_per_mmbtu = Column(
Float,
nullable=False,
comment="Average cost of fuel consumed in the report year, in nominal USD per mmBTU of fuel heat content."
)
# Is this a useful number for any fuel that's not overwhelmingly dominant?
fuel_cost_per_mwh = Column(
Float,
nullable=False,
comment="Average cost of fuel burned per MWh of net generation in the report year. In plants burning multiple fuels, this may not be indicative of overall fuel cost per MWh."
)
# Is this a useful number for any fuel that's not overwhelmingly dominant?
fuel_mmbtu_per_mwh = Column(
Float,
nullable=False,
comment="Average heat content in mmBTU of fuel consumed per MWh of net generation in the report year. In plants burning multiple fuels this may not be indicative of overall plant heat rate."
)
class ImagePackage(Base):
"""
A package detected in an image by analysis
"""
__tablename__ = 'image_packages'
image_id = Column(String(image_id_length), primary_key=True)
image_user_id = Column(String(user_id_length), primary_key=True)
name = Column(String(pkg_name_length), primary_key=True)
version = Column(String(pkg_version_length), primary_key=True)
pkg_type = Column(String(pkg_type_length),
primary_key=True) # RHEL, DEB, APK, etc.
arch = Column(String(16), default='N/A', primary_key=True)
# Could pkg namespace be diff than os? e.g. rpms in Deb?
distro_name = Column(String(distro_length))
distro_version = Column(String(distro_version_length))
like_distro = Column(String(distro_length))
fullversion = Column(String(pkg_version_length))
release = Column(String(pkg_version_length), default='')
origin = Column(String(512), default='N/A')
src_pkg = Column(String(pkg_name_length + pkg_version_length),
default='N/A')
normalized_src_pkg = Column(String(pkg_name_length + pkg_version_length),
default='N/A')
license = Column(String(1024), default='N/A')
size = Column(Integer, nullable=True)
created_at = Column(DateTime, default=datetime.datetime.utcnow)
updated_at = Column(DateTime,
default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow)
vulnerabilities = relationship('ImagePackageVulnerability',
back_populates='package',
lazy='dynamic')
image = relationship('Image', back_populates='packages')
pkg_db_entries = relationship('ImagePackageManifestEntry',
backref='package',
lazy='dynamic',
cascade=['all', 'delete', 'delete-orphan'])
__table_args__ = (ForeignKeyConstraint(
columns=[image_id, image_user_id],
refcolumns=['images.id', 'images.user_id']),
Index('ix_image_package_distronamespace', name, version,
distro_name, distro_version,
normalized_src_pkg), {})
_distro_namespace = None
@property
def distro_namespace_meta(self):
if not self._distro_namespace:
self._distro_namespace = DistroNamespace.for_obj(self)
return self._distro_namespace
@property
def distro_namespace(self):
if self.distro_name and self.distro_version:
return self.distro_name + ':' + self.distro_version
else:
return None
class PlantSmallFERC1(pudl.models.entities.PUDLBase):
"""
Annual data on "small plants" imported from the f1_gnrt_plant table.
Capacity and generation data related to a heterogenous collection of small
plants that have to report to FERC. Includes many renewable energy
facilities, as well as smaller thermal generation stations.
"""
__tablename__ = 'plants_small_ferc1'
__table_args__ = (
ForeignKeyConstraint(
['utility_id_ferc1', 'plant_name_original'],
['plants_ferc.utility_id_ferc1', 'plants_ferc.plant_name']),
{"comment": "Generating plant statistics for small plants, as reported on FERC Form 1 pages 410-411, and extracted from the FERC FoxPro database table f1_gnrt_plant. Small generating plants are defined by having nameplate capacity of less than 25MW for steam plants, and less than 10MW for internal combustion, conventional hydro, and pumped storage plants."}
)
id = Column(Integer, autoincrement=True,
primary_key=True, comment=id_comment)
record_id = Column(String, nullable=False, comment=record_id_comment)
utility_id_ferc1 = Column(Integer, nullable=False,
comment=utility_id_ferc1_comment)
report_year = Column(Integer, nullable=False, comment=report_year_comment)
plant_name_original = Column(
String,
nullable=False,
comment="Original plant name in the FERC Form 1 FoxPro database.",
)
plant_name = Column(
String,
comment="PUDL assigned simplified plant name.",
)
plant_type = Column(
String,
comment="PUDL assigned plant type. This is a best guess based on the fuel type, plant name, and other attributes.",
)
ferc_license_id = Column(
Integer,
comment="FERC issued operating license ID for the facility, if available. This value is extracted from the original plant name where possible.",
)
construction_year = Column(
Integer,
comment="Original year of plant construction.",
)
capacity_mw = Column(
Float,
comment="Name plate capacity in megawatts.",
)
peak_demand_mw = Column(
Float,
comment="Net peak demand for 60 minutes. Note: in some cases peak demand for other time periods may have been reported instead, if hourly peak demand was unavailable.",
)
net_generation_mwh = Column(
Float,
comment="Net generation excluding plant use, in megawatt-hours.",
)
total_cost_of_plant = Column(
Numeric(14, 2),
comment="Total cost of plant. Nominal USD.",
)
capex_per_mw = Column(
Numeric(14, 2),
comment="Plant costs (including asset retirement costs) per megawatt. Nominal USD. ",
)
opex_total = Column(
Numeric(14, 2),
comment="Total plant operating expenses, excluding fuel. Nominal USD.",
)
opex_fuel = Column(
Numeric(14, 2),
comment="Production expenses: Fuel. Nominal USD.",
)
opex_maintenance = Column(
Numeric(14, 2),
comment="Production expenses: Maintenance. Nominal USD.",
)
fuel_type = Column(
String,
comment="Kind of fuel. Originally reported to FERC as a freeform string. Assigned a canonical value by PUDL based on our best guess.",
)
fuel_cost_per_mmbtu = Column(
Numeric(14, 2),
comment="Average fuel cost per mmBTU (if applicable). Nominal USD.",
)
class ImagePackageVulnerability(Base):
"""
Provides a mapping between ImagePackage and Vulnerabilities
"""
__tablename__ = 'image_package_vulnerabilities'
pkg_user_id = Column(String(user_id_length), primary_key=True)
pkg_image_id = Column(String(image_id_length), primary_key=True)
pkg_name = Column(String(pkg_name_length), primary_key=True)
pkg_version = Column(String(pkg_version_length), primary_key=True)
pkg_type = Column(String(pkg_type_length),
primary_key=True) # RHEL, DEB, APK, etc.
pkg_arch = Column(String(16), default='N/A', primary_key=True)
vulnerability_id = Column(String(vuln_id_length), primary_key=True)
vulnerability_namespace_name = Column(String(namespace_length))
created_at = Column(DateTime, default=datetime.datetime.utcnow)
package = relationship('ImagePackage', back_populates='vulnerabilities')
vulnerability = relationship('Vulnerability')
__table_args__ = (ForeignKeyConstraint(
columns=[
pkg_image_id, pkg_user_id, pkg_name, pkg_version, pkg_type,
pkg_arch
],
refcolumns=[
ImagePackage.image_id, ImagePackage.image_user_id,
ImagePackage.name, ImagePackage.version, ImagePackage.pkg_type,
ImagePackage.arch
]),
ForeignKeyConstraint(columns=[
vulnerability_id, vulnerability_namespace_name
],
refcolumns=[
Vulnerability.id,
Vulnerability.namespace_name
]), {})
def fixed_in(self):
"""
Return the fixed_in value given a package matched (in case there are multiple packages specified in the vuln.
:param package: package to find a fix version for, if available
:return: the fixed in version string if any or None if not found
"""
if self.vulnerability.fixed_in:
name_matches = [self.pkg_name, self.package.normalized_src_pkg]
fix_candidates = self.vulnerability.fixed_in
fixes_in = filter(lambda x: x.name in name_matches, fix_candidates)
fix_available_in = fixes_in[0].version if fixes_in else None
if fix_available_in == 'None':
fix_available_in = None
else:
fix_available_in = None
return fix_available_in
@classmethod
def from_pair(cls, package_obj, vuln_obj):
rec = ImagePackageVulnerability()
rec.pkg_name = package_obj.name
rec.pkg_type = package_obj.pkg_type
rec.pkg_arch = package_obj.arch
rec.pkg_image_id = package_obj.image_id
rec.pkg_user_id = package_obj.image_user_id
rec.pkg_version = package_obj.version
rec.vulnerability_id = vuln_obj.vulnerability_id if hasattr(
vuln_obj, 'vulnerability_id') else vuln_obj.id
rec.vulnerability_namespace_name = vuln_obj.namespace_name
return rec
def __repr__(self):
return '<ImagePackageVulnerability img_user_id={}, img_id={}, pkg_name={}, pkg_version={}, vuln_id={}, vuln_namespace={}>'.format(
self.pkg_user_id, self.pkg_image_id, self.pkg_name,
self.pkg_version, self.vulnerability_id,
self.vulnerability_namespace_name)
# To support hash functions like set operations, ensure these align with primary key comparisons to ensure two identical records would match as such.
def __eq__(self, other):
return (isinstance(other, type(self))
and (self.pkg_user_id, self.pkg_image_id, self.pkg_name,
self.pkg_version, self.pkg_type, self.pkg_arch,
self.vulnerability_id)
== ((other.pkg_user_id, other.pkg_image_id, other.pkg_name,
other.pkg_version, other.pkg_type, other.pkg_arch,
other.vulnerability_id)))
def __hash__(self):
return hash((self.pkg_user_id, self.pkg_image_id, self.pkg_name,
self.pkg_version, self.pkg_type, self.pkg_arch,
self.vulnerability_id))
class PlantHydroFERC1(pudl.models.entities.PUDLBase):
"""Annual data on hydro plants from FERC form 1."""
__tablename__ = 'plants_hydro_ferc1'
__table_args__ = (
ForeignKeyConstraint(
['utility_id_ferc1', 'plant_name'],
['plants_ferc.utility_id_ferc1', 'plants_ferc.plant_name']),
{"comment": "Hydroelectric generating plant statistics for large plants. Large plants have an installed nameplate capacity of more than 10 MW. As reported on FERC Form 1, pages 406-407, and extracted from the f1_hydro table in FERC's FoxPro database."}
)
id = Column(Integer, autoincrement=True,
primary_key=True, comment=id_comment)
record_id = Column(String, nullable=False, comment=record_id_comment)
utility_id_ferc1 = Column(Integer, nullable=False,
comment=utility_id_ferc1_comment)
report_year = Column(Integer, nullable=False, comment=report_year_comment)
plant_name = Column(
String,
nullable=False,
comment=plant_name_comment
)
project_num = Column(
Integer,
comment="FERC Licensed Project Number."
)
plant_type = Column(
String,
comment="Kind of plant (Run-of-River or Storage)."
)
construction_type = Column(
construction_type_enum, # Enum, see top of this file
comment=construction_type_comment
)
construction_year = Column(
Integer,
comment="Four digit year of the plant's original construction."
)
installation_year = Column(
Integer,
comment="Four digit year in which the last unit was installed."
)
capacity_mw = Column(
Float,
comment="Total installed (nameplate) capacity, in megawatts."
)
peak_demand_mw = Column(
Float,
comment="Net peak demand on the plant (60-minute integration), in megawatts."
)
plant_hours_connected_while_generating = Column(
Float,
comment="Hours the plant was connected to load while generating."
)
net_capacity_favorable_conditions_mw = Column(
Float,
comment="Net plant capability under the most favorable operating conditions, in megawatts."
)
net_capacity_adverse_conditions_mw = Column(
Float,
comment="Net plant capability under the least favorable operating conditions, in megawatts."
)
avg_num_employees = Column(
Float,
comment="Average number of employees."
)
net_generation_mwh = Column(
Float,
comment="Net generation, exclusive of plant use, in megawatt hours."
)
capex_land = Column(
Numeric(14, 2),
comment="Cost of plant: land and land rights. Nominal USD."
)
capex_structures = Column(
Numeric(14, 2),
comment="Cost of plant: structures and improvements. Nominal USD."
)
capex_facilities = Column(
Numeric(14, 2),
comment="Cost of plant: reservoirs, dams, and waterways. Nominal USD."
)
capex_equipment = Column(
Numeric(14, 2),
comment="Cost of plant: equipment. Nominal USD."
)
capex_roads = Column(
Numeric(14, 2),
comment="Cost of plant: roads, railroads, and bridges. Nominal USD."
)
asset_retirement_cost = Column(
Numeric(14, 2),
comment="Cost of plant: asset retirement costs. Nominal USD."
)
capex_total = Column(
Numeric(14, 2),
comment="Total cost of plant. Nominal USD."
)
capex_per_mw = Column(
Numeric(14, 2),
comment="Cost of plant per megawatt of installed (nameplate) capacity. Nominal USD."
)
opex_operations = Column(
Numeric(14, 2),
comment="Production expenses: operation, supervision, and engineering. Nominal USD."
)
opex_water_for_power = Column(
Numeric(14, 2),
comment="Production expenses: water for power. Nominal USD."
)
opex_hydraulic = Column(
Numeric(14, 2),
comment="Production expenses: hydraulic expenses. Nominal USD."
)
opex_electric = Column(
Numeric(14, 2),
comment="Production expenses: electric expenses. Nominal USD."
)
opex_generation_misc = Column(
Numeric(14, 2),
comment="Production expenses: miscellaneous hydraulic power generation expenses. Nominal USD."
)
opex_rents = Column(
Numeric(14, 2),
comment="Production expenses: rent. Nominal USD."
)
opex_engineering = Column(
Numeric(14, 2),
comment="Production expenses: maintenance, supervision, and engineering. Nominal USD."
)
opex_structures = Column(
Numeric(14, 2),
comment="Production expenses: maintenance of structures. Nominal USD."
)
opex_dams = Column(
Numeric(14, 2),
comment="Production expenses: maintenance of reservoirs, dams, and waterways. Nominal USD."
)
opex_plant = Column(
Numeric(14, 2),
comment="Production expenses: maintenance of electric plant. Nominal USD."
)
opex_misc_plant = Column(
Numeric(14, 2),
comment="Production expenses: maintenance of miscellaneous hydraulic plant. Nominal USD."
)
opex_total = Column(
Numeric(14, 2),
comment="Total production expenses. Nominal USD."
)
opex_per_mwh = Column(
Numeric(14, 2),
comment="Production expenses per net megawatt hour generated. Nominal USD."
)
config.db_prefix + 'zones',
meta,
Column('id', Integer, primary_key=True),
Column('models_id', Integer, ForeignKey(models.c.id), primary_key=True),
Column('area', Geometry(srid=900913, spatial_index=True), nullable=False),
Column('data', ARRAY(Float, zero_indexes=True), nullable=False),
)
rent_adjustments = Table(
config.db_prefix + 'rent_adjustments',
meta,
Column('types_id', Integer, primary_key=True),
Column('zones_id', Integer, primary_key=True),
Column('models_id', Integer, ForeignKey(models.c.id), primary_key=True),
Column('adjustment', Float, nullable=False),
ForeignKeyConstraint(['zones_id', 'models_id'],
[zones.c.id, zones.c.models_id]),
)
supply = Table(
config.db_prefix + 'supply',
meta,
Column('types_id', Integer, primary_key=True),
Column('zones_id', Integer, primary_key=True),
Column('models_id', Integer, ForeignKey(models.c.id), primary_key=True),
Column('nrest', Float, nullable=False),
ForeignKeyConstraint(['zones_id', 'models_id'],
[zones.c.id, zones.c.models_id]),
)
real_estates_zones = Table(
config.db_prefix + 'real_estates_zones',
class PlantPumpedStorage(pudl.models.entities.PUDLBase):
"""Annual data on pumped storage from the f1_pumped_storage table."""
__tablename__ = 'plants_pumped_storage_ferc1'
__table_args__ = (
ForeignKeyConstraint(
['utility_id_ferc1', 'plant_name'],
['plants_ferc.utility_id_ferc1', 'plants_ferc.plant_name']),
{"comment": ""}
)
id = Column(Integer, autoincrement=True,
primary_key=True, comment=id_comment)
record_id = Column(String, nullable=False, comment=record_id_comment)
utility_id_ferc1 = Column(Integer, nullable=False,
comment=utility_id_ferc1_comment)
report_year = Column(Integer, nullable=False, comment=report_year_comment)
plant_name = Column(
String,
nullable=False,
comment=plant_name_comment
)
project_num = Column(
Integer,
comment="FERC Licensed Project Number."
)
construction_type = Column(
construction_type_enum, # Enum, see top of this file
comment=construction_type_comment
)
construction_year = Column(
Integer,
comment="Four digit year of the plant's original construction."
)
installation_year = Column(
Integer,
comment="Four digit year in which the last unit was installed."
)
capacity_mw = Column(
Float,
comment="Total installed (nameplate) capacity, in megawatts."
)
peak_demand_mw = Column(
Float,
comment="Net peak demand on the plant (60-minute integration), in megawatts."
)
plant_hours_connected_while_generating = Column(
Float,
comment="Hours the plant was connected to load while generating."
)
plant_capability_mw = Column(
Float,
comment="Net plant capability in megawatts."
)
avg_num_employees = Column(
Float,
comment="Average number of employees."
)
net_generation_mwh = Column(
Float,
comment="Net generation, exclusive of plant use, in megawatt hours."
)
energy_used_for_pumping_mwh = Column(
Float,
comment="Energy used for pumping, in megawatt-hours."
)
net_load_mwh = Column(
Float,
comment="Net output for load (net generation - energy used for pumping) in megawatt-hours."
)
capex_land = Column(
Numeric(14, 2),
comment="Cost of plant: land and land rights. Nominal USD."
)
capex_structures = Column(
Numeric(14, 2),
comment="Cost of plant: structures and improvements. Nominal USD."
)
capex_facilities = Column(
Numeric(14, 2),
comment="Cost of plant: reservoirs, dams, and waterways. Nominal USD."
)
capex_wheels_turbines_generators = Column(
Numeric(14, 2),
comment="Cost of plant: water wheels, turbines, and generators. Nominal USD."
)
capex_equipment_electric = Column(
Numeric(14, 2),
comment="Cost of plant: accessory electric equipment. Nominal USD."
)
capex_equipment_misc = Column(
Numeric(14, 2),
comment="Cost of plant: miscellaneous power plant equipment. Nominal USD."
)
capex_roads = Column(
Numeric(14, 2),
comment="Cost of plant: roads, railroads, and bridges. Nominal USD."
)
asset_retirement_cost = Column(
Numeric(14, 2),
comment="Cost of plant: asset retirement costs. Nominal USD."
)
capex_total = Column(
Numeric(14, 2),
comment="Total cost of plant. Nominal USD."
)
capex_per_mw = Column(
Numeric(14, 2),
comment="Cost of plant per megawatt of installed (nameplate) capacity. Nominal USD."
)
opex_operations = Column(
Numeric(14, 2),
comment="Production expenses: operation, supervision, and engineering. Nominal USD."
)
opex_water_for_power = Column(
Numeric(14, 2),
comment="Production expenses: water for power. Nominal USD."
)
opex_pumped_storage = Column(
Numeric(14, 2),
comment="Production expenses: pumped storage. Nominal USD."
)
opex_electric = Column(
Numeric(14, 2),
comment="Production expenses: electric expenses. Nominal USD."
)
opex_generation_misc = Column(
Numeric(14, 2),
comment="Production expenses: miscellaneous pumped storage power generation expenses. Nominal USD."
)
opex_rents = Column(
Numeric(14, 2),
comment="Production expenses: rent. Nominal USD."
)
opex_engineering = Column(
Numeric(14, 2),
comment="Production expenses: maintenance, supervision, and engineering. Nominal USD."
)
opex_structures = Column(
Numeric(14, 2),
comment="Production expenses: maintenance of structures. Nominal USD."
)
opex_dams = Column(
Numeric(14, 2),
comment="Production expenses: maintenance of reservoirs, dams, and waterways. Nominal USD."
)
opex_plant = Column(
Numeric(14, 2),
comment="Production expenses: maintenance of electric plant. Nominal USD."
)
opex_misc_plant = Column(
Numeric(14, 2),
comment="Production expenses: maintenance of miscellaneous hydraulic plant. Nominal USD."
)
opex_production_before_pumping = Column(
Numeric(14, 2),
comment="Total production expenses before pumping. Nominal USD."
)
opex_pumping = Column(
Numeric(14, 2),
comment="Production expenses: We are here to PUMP YOU UP! Nominal USD."
)
opex_total = Column(
Numeric(14, 2),
comment="Total production expenses. Nominal USD."
)
opex_per_mwh = Column(
Numeric(14, 2),
comment="Production expenses per net megawatt hour generated. Nominal USD."
)
UPDATE_STATUSES = ['SUCCESS', 'ERROR']
UPDATE_ACTIONS = ['CREATE', 'DELETE', 'UPDATE']
metadata = MetaData()
pool_manager_statuses = Table(
'pool_manager_statuses',
metadata,
Column('id', UUID, default=utils.generate_uuid, primary_key=True),
Column('version', Integer(), default=1, nullable=False),
Column('created_at', DateTime, default=lambda: timeutils.utcnow()),
Column('updated_at', DateTime, onupdate=lambda: timeutils.utcnow()),
Column('nameserver_id', UUID, nullable=False),
Column('domain_id', UUID, nullable=False),
Column('action',
Enum(name='update_actions', *UPDATE_ACTIONS),
nullable=False),
Column('status',
Enum(name='update_statuses', *UPDATE_STATUSES),
nullable=True),
Column('serial_number', Integer, nullable=False),
UniqueConstraint('nameserver_id',
'domain_id',
'action',
name='unique_pool_manager_status'),
ForeignKeyConstraint(['domain_id'], ['domains.id']),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
# ForeignKey is a shortcut for ForeignKeyConstraint, which should be used for composite references.
from sqlalchemy import Unicode, UnicodeText, DateTime
from sqlalchemy import ForeignKeyConstraint
story_table = Table('story', metadata,
Column('story_id', Integer, primary_key=True),
Column('version_id', Integer, primary_key=True),
Column('headline', Unicode(100), nullable=False),
Column('body', UnicodeText))
published_table = Table(
'published', metadata, Column('pub_id', Integer, primary_key=True),
Column('pub_timestamp', DateTime, nullable=False),
Column('story_id', Integer), Column('version_id', Integer),
ForeignKeyConstraint(['story_id', 'version_id'],
['story.story_id', 'story.version_id']))
# create_all() by default checks for tables existing already
metadata.create_all(engine)
# ## title:: Exercises
# 1. Write a Table construct corresponding to this CREATE TABLE
# statement.
#
# CREATE TABLE network (
# network_id INTEGER PRIMARY KEY,
# name VARCHAR(100) NOT NULL,
# created_at DATETIME NOT NULL,
# owner_id INTEGER,
# FOREIGN KEY owner_id REFERENCES user(id)
# )
