def sqlite_drop_columns(table_name, drop_columns):
"""Implement drop columns for SQLite.
The DROP COLUMN command isn't supported by SQLite specification.
Instead of calling DROP COLUMN it uses the following workaround:
* create temp table '{table_name}_{rand_uuid}', without
dropped columns;
* copy all data to the temp table;
* drop old table;
* rename temp table to the old table name.
"""
connection = op.get_bind()
meta = sqlalchemy.MetaData(bind=connection)
meta.reflect()
# construct lists of all columns and their names
old_columns = []
new_columns = []
column_names = []
indexes = []
for column in meta.tables[table_name].columns:
if column.name not in drop_columns:
old_columns.append(column)
column_names.append(column.name)
col_copy = column.copy()
new_columns.append(col_copy)
for key in meta.tables[table_name].foreign_keys:
# If this is a single column constraint for a dropped column,
# don't copy it.
if isinstance(key.constraint.columns,
sqlalchemy.sql.base.ColumnCollection):
# This is needed for SQLAlchemy >= 1.0.4
columns = [c.name for c in key.constraint.columns]
else:
# This is needed for SQLAlchemy <= 0.9.9. This is
# backwards compat code just in case someone updates
# Hubtty without updating SQLAlchemy. This is simple
# enough to check and will hopefully avoid leaving the
# user's db in an inconsistent state. Remove this after
# Hubtty 1.2.0.
columns = key.constraint.columns
if (len(columns) == 1 and columns[0] in drop_columns):
continue
# Otherwise, recreate the constraint.
constraint = key.constraint
con_copy = constraint.copy()
new_columns.append(con_copy)
for index in meta.tables[table_name].indexes:
# If this is a single column index for a dropped column, don't
# copy it.
idx_columns = [col.name for col in index.columns]
if len(idx_columns) == 1 and idx_columns[0] in drop_columns:
continue
# Otherwise, recreate the index.
indexes.append(
(index.name, table_name, [col.name
for col in index.columns], index.unique))
# create temp table
tmp_table_name = "%s_%s" % (table_name, six.text_type(uuid.uuid4()))
op.create_table(tmp_table_name, *new_columns)
meta.reflect()
try:
# copy data from the old table to the temp one
sql_select = sqlalchemy.sql.select(old_columns)
connection.execute(
sqlalchemy.sql.insert(meta.tables[tmp_table_name]).from_select(
column_names, sql_select))
except Exception:
op.drop_table(tmp_table_name)
raise
# drop the old table and rename temp table to the old table name
op.drop_table(table_name)
op.rename_table(tmp_table_name, table_name)
# (re-)create indexes
for index in indexes:
op.create_index(op.f(index[0]), index[1], index[2], unique=index[3])
def init_postigis(self):
"""Creates all the tables and schema.
The table schema is based on the
[WazeCCPProcessor](github.com/LouisvilleMetro/WazeCCPProcessor)
project in order to achieve maximum compatibility.
It creates a schema: `waze`
and the tables:
- jams
- irregularities
- alerts
- roads
- alert_types
- coordinates
- coordinate_type
"""
if self.force_export:
try:
self.engine_postgis.execute('DROP SCHEMA waze CASCADE')
except:
pass
try:
self.engine_postgis.execute("CREATE SCHEMA waze") #create db
except:
pass
metadata = sa.MetaData(self.engine_postgis)
self.tables_postgis = {}
metadata = sa.MetaData(self.engine_postgis)
self.tables_postgis['alerts'] = sa.Table(
'alerts',
metadata,
Column(
"id",
INTEGER,
primary_key=True,
),
Column("uuid", Text, nullable=False, index=True),
Column("pub_millis", BigInteger, nullable=False),
Column("pub_utc_date", TIMESTAMP, index=True),
Column("road_type", INTEGER, index=True),
Column("location", JSON),
Column("location_geo", Geometry('POINT')),
Column("street", Text),
Column("city", Text),
Column("country", Text),
Column("magvar", INTEGER),
Column("reliability", INTEGER, index=True),
Column("report_description", Text),
Column("report_rating", INTEGER),
Column("confidence", INTEGER, index=True),
Column("type", Text, index=True),
Column("subtype", Text, index=True),
Column("report_by_municipality_user", BOOLEAN),
Column("thumbs_up", INTEGER, index=True),
Column("jam_uuid", Text, index=True),
Column("datafile_id", BigInteger, nullable=False, index=True),
schema='waze',
)
self.tables_postgis['jams'] = sa.Table(
'jams',
metadata,
Column("id", INTEGER, primary_key=True, nullable=False),
Column("uuid", Text, nullable=False, index=True),
Column("pub_millis", BigInteger, nullable=False),
Column("pub_utc_date", TIMESTAMP, index=True),
Column("start_node", Text),
Column("end_node", Text),
Column("road_type", INTEGER),
Column("street", Text, index=True),
Column("city", Text),
Column("country", Text),
Column("delay", INTEGER, index=True),
Column("speed", Float, index=True),
Column("speed_kmh", Float, index=True),
Column("length", INTEGER, index=True),
Column("turn_type", Text),
Column("level", INTEGER, index=True),
Column("blocking_alert_id", Text),
Column("line", JSON),
Column("line_geo", Geometry('LINESTRING')),
Column("type", Text, index=True),
Column("turn_line", JSON),
Column("turn_line_geo", Geometry('LINESTRING')),
Column("datafile_id", BigInteger, nullable=False, index=True),
schema='waze',
)
self.tables_postgis['irregularities'] = sa.Table(
'irregularities',
metadata,
Column("id", INTEGER, primary_key=True, nullable=False),
Column("uuid", Text, nullable=False, index=True),
Column("detection_date_millis", BigInteger, nullable=False),
Column("detection_date", Text),
Column("detection_utc_date", TIMESTAMP, index=True),
Column("update_date_millis", BigInteger, nullable=False),
Column("update_date", Text),
Column("update_utc_date", TIMESTAMP, index=True),
Column("street", Text, index=True),
Column("city", Text),
Column("country", Text),
Column("is_highway", BOOLEAN),
Column("speed", Float, index=True),
Column("regular_speed", Float, index=True),
Column("delay_seconds", INTEGER, index=True),
Column("seconds", INTEGER, index=True),
Column("length", INTEGER, index=True),
Column("trend", INTEGER, index=True),
Column("type", Text, index=True),
Column("severity", Float, index=True),
Column("jam_level", INTEGER, index=True),
Column("drivers_count", INTEGER),
Column("alerts_count", INTEGER, index=True),
Column("n_thumbs_up", INTEGER, index=True),
Column("n_comments", INTEGER),
Column("n_images", INTEGER),
Column("line", JSON),
Column("line_geo", Geometry('LINESTRING')),
Column("cause_type", Text),
Column("start_node", Text),
Column("end_node", Text),
Column("datafile_id", BigInteger, nullable=False, index=True),
schema='waze',
)
self.tables_postgis['coordinate_type'] = sa.Table(
'coordinate_type',
metadata,
Column("id", INTEGER, primary_key=True, nullable=False),
Column("type_name", Text, nullable=False),
schema='waze',
)
self.tables_postgis['coordinates'] = sa.Table(
'coordinates',
metadata,
Column("id", VARCHAR(40), primary_key=True, nullable=False),
Column("latitude", Float(8), nullable=False),
Column("longitude", Float(8), nullable=False),
Column("order", INTEGER, nullable=False),
Column("jam_id", INTEGER, ForeignKey('waze.jams.id')),
Column("irregularity_id", INTEGER,
ForeignKey('waze.irregularities.id')),
Column("alert_id", INTEGER, ForeignKey('waze.alerts.id')),
Column("coordinate_type_id", INTEGER,
ForeignKey('waze.coordinate_type.id')),
schema='waze',
)
self.tables_postgis['roads'] = sa.Table(
'roads',
metadata,
Column("id", INTEGER, primary_key=True, nullable=False),
Column("value", INTEGER, nullable=False),
Column("name", VARCHAR(100), nullable=False),
schema='waze',
)
self.tables_postgis['alert_types'] = sa.Table(
'alert_types',
metadata,
Column("id", INTEGER, primary_key=True, nullable=False),
Column("type", Text, nullable=False),
Column("subtype", Text),
schema='waze',
)
metadata.create_all(self.engine_postgis)
try:
self.engine_postgis.execute("""ALTER TABLE waze.roads
ADD CONSTRAINT roads_unique_combo UNIQUE(value, name);""")
except sa.exc.ProgrammingError:
pass
try:
self.engine_postgis.execute("""ALTER TABLE waze.alert_types
ADD CONSTRAINT alert_types_unique_combo
UNIQUE(type, subtype);""")
except sa.exc.ProgrammingError:
pass
# Insert elements
with self.engine_postgis.connect() as conn:
try:
conn.execute(self.tables_postgis['coordinate_type'].insert(),
[{
'id': 1,
'type_name': 'Line'
}, {
'id': 2,
'type_name': 'Turn Line'
}, {
'id': 3,
'type_name': 'Location'
}])
except sa.exc.IntegrityError:
pass
try:
conn.execute(self.tables_postgis['roads'].insert(),
[{
'value': 1,
'name': 'Streets'
}, {
'value': 2,
'name': 'Primary Street'
}, {
'value': 3,
'name': 'Freeways'
}, {
'value': 4,
'name': 'Ramps'
}, {
'value': 5,
'name': 'Trails'
}, {
'value': 6,
'name': 'Primary'
}, {
'value': 7,
'name': 'Secondary'
}, {
'value': 8,
'name': '4X4 Trails'
}, {
'value': 9,
'name': 'Walkway'
}, {
'value': 10,
'name': 'Pedestrian'
}, {
'value': 11,
'name': 'Exit'
}, {
'value': 12,
'name': '?'
}, {
'value': 13,
'name': '?'
}, {
'value': 14,
'name': '4X4 Trails'
}, {
'value': 15,
'name': 'Ferry crossing'
}, {
'value': 16,
'name': 'Stairway'
}, {
'value': 17,
'name': 'Private road'
}, {
'value': 18,
'name': 'Railroads'
}, {
'value': 19,
'name': 'Runway/Taxiway'
}, {
'value': 20,
'name': 'Parking lot road'
}, {
'value': 21,
'name': 'Service road'
}])
except sa.exc.IntegrityError:
pass
try:
conn.execute(self.tables_postgis['alert_types'].insert(), [
{
'type': 'ACCIDENT',
'subtype': 'ACCIDENT_MINOR'
},
{
'type': 'ACCIDENT',
'subtype': 'ACCIDENT_MAJOR'
},
{
'type': 'ACCIDENT',
'subtype': 'NO_SUBTYPE'
},
{
'type': 'JAM',
'subtype': 'JAM_MODERATE_TRAFFIC'
},
{
'type': 'JAM',
'subtype': 'JAM_HEAVY_TRAFFIC'
},
{
'type': 'JAM',
'subtype': 'JAM_STAND_STILL_TRAFFIC'
},
{
'type': 'JAM',
'subtype': 'JAM_LIGHT_TRAFFIC'
},
{
'type': 'JAM',
'subtype': 'NO_SUBTYPE'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_ROAD'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_SHOULDER'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_WEATHER'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_ROAD_OBJECT'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_ROAD_POT_HOLE'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_ROAD_ROAD_KILL'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_SHOULDER_CAR_STOPPED'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_SHOULDER_ANIMALS'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_SHOULDER_MISSING_SIGN'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_WEATHER_FOG'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_WEATHER_HAIL'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_WEATHER_HEAVY_RAIN'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_WEATHER_HEAVY_SNOW'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_WEATHER_FLOOD'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_WEATHER_MONSOON'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_WEATHER_TORNADO'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_WEATHER_HEAT_WAVE'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_WEATHER_HURRICANE'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_WEATHER_FREEZING_RAIN'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_ROAD_LANE_CLOSED'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_ROAD_OIL'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_ROAD_ICE'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_ROAD_CONSTRUCTION'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_ROAD_CAR_STOPPED'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'HAZARD_ON_ROAD_TRAFFIC_LIGHT_FAULT'
},
{
'type': 'WEATHERHAZARD/HAZARD',
'subtype': 'NO_SUBTYPE'
},
{
'type': 'MISC',
'subtype': 'NO_SUBTYPE'
},
{
'type': 'CONSTRUCTION',
'subtype': 'NO_SUBTYPE'
},
{
'type': 'ROAD_CLOSED',
'subtype': 'ROAD_CLOSED_HAZARD'
},
{
'type': 'ROAD_CLOSED',
'subtype': 'ROAD_CLOSED_CONSTRUCTION'
},
{
'type': 'ROAD_CLOSED',
'subtype': 'ROAD_CLOSED_EVENT'
},
])
except sa.exc.IntegrityError:
pass
from requests import get
from bs4 import BeautifulSoup, Comment
import sqlalchemy as db
#GLOBAL TABLE SETUP
engine = db.create_engine('sqlite:////home/woody/Documents/DataMining/Baseball/project.db')
connection = engine.connect()
metadata = db.MetaData()
PlateAppearance = db.Table('PlateAppearance', metadata, autoload=True, autoload_with=engine)
'''
PlateAppearance = db.Table('PlateAppearance', metadata, autoload=True, autoload_with=engine)
TODO REINITALIZE DATABASE
'''
sqlite_file = 'project.db'
table_name = 'PlateAppearance'
#GLOBAL NAME DICT
teamdict = {
"ATL": "AtlantaBraves",
"NYM": "NewYorkMets",
"NYY": "NewYorkYankees",
"BOS": "BostonRedSox",
"ARI": "ArizonaDiamondbacks",
"BAL": "BaltimoreOrioles",
"CHC": "ChicagoCubs",
"CHW": "ChicagoWhiteSox",
"CIN": "CincinnatiReds",
"CLE": "ClevelandIndians",
"COL": "ColoradoRockies",
"DET": "DetroitTigers",
"FLA": "FloridaMarlins",
"HOU": "HoustonAstros",
**It is representing the topic: Noise Sensitivity Levels**
You can use it to
produce a own new topic for the oereb eco system in the specifications shape. To be able to adapt this
models to your own infrastructure you must implement the same attribute names! In fact that inheritance
is not easily made you need to make your own classes and adapt them to your database.
"""
import sqlalchemy as sa
from pyramid_oereb.standard.models import NAMING_CONVENTION
from pyramid_oereb.lib.config import Config
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2.types import Geometry as GeoAlchemyGeometry
from sqlalchemy.orm import relationship
from sqlalchemy_utils import JSONType
metadata = sa.MetaData(naming_convention=NAMING_CONVENTION)
Base = declarative_base()
srid = Config.get('srid')
class Availability(Base):
"""
A simple bucket for achieving a switch per municipality. Here you can configure via the imported data if
a public law restriction is available or not. You need to fill it with the data you provided in the
app schemas municipality table (fosnr).
Attributes:
fosnr (str): The identifier of the municipality in your system (id_bfs = fosnr)
available (bool): The switch field to configure if this plr is available for the
municipality or not. This field has direct influence on the applications
behaviour. See documentation for more info.
class Model(base.DBConnectorComponent):
#
# schema
#
metadata = sa.MetaData()
# NOTES
# * server_defaults here are included to match those added by the migration
# scripts, but they should not be depended on - all code accessing these
# tables should supply default values as necessary. The defaults are
# required during migration when adding non-nullable columns to existing
# tables.
#
# * dates are stored as unix timestamps (UTC-ish epoch time)
#
# * sqlalchemy does not handle sa.Boolean very well on MySQL or Postgres;
# use sa.Integer instead
# build requests
# A BuildRequest is a request for a particular build to be performed. Each
# BuildRequest is a part of a Buildset. BuildRequests are claimed by
# masters, to avoid multiple masters running the same build.
buildrequests = sa.Table(
'buildrequests',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('buildsetid',
sa.Integer,
sa.ForeignKey("buildsets.id"),
nullable=False),
sa.Column('buildername', sa.String(length=256), nullable=False),
sa.Column('priority',
sa.Integer,
nullable=False,
server_default=sa.DefaultClause("0")),
# if this is zero, then the build is still pending
sa.Column('complete', sa.Integer,
server_default=sa.DefaultClause("0")),
# results is only valid when complete == 1; 0 = SUCCESS, 1 = WARNINGS,
# etc - see master/buildbot/status/builder.py
sa.Column('results', sa.SmallInteger),
# time the buildrequest was created
sa.Column('submitted_at', sa.Integer, nullable=False),
# time the buildrequest was completed, or NULL
sa.Column('complete_at', sa.Integer),
)
# Each row in this table represents a claimed build request, where the
# claim is made by the object referenced by objectid.
buildrequest_claims = sa.Table(
'buildrequest_claims',
metadata,
sa.Column('brid',
sa.Integer,
sa.ForeignKey('buildrequests.id'),
index=True,
unique=True),
sa.Column('objectid',
sa.Integer,
sa.ForeignKey('objects.id'),
index=True,
nullable=True),
sa.Column('claimed_at', sa.Integer, nullable=False),
)
# builds
# This table contains basic information about each build. Note that most
# data about a build is still stored in on-disk pickles.
builds = sa.Table(
'builds',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('number', sa.Integer, nullable=False),
sa.Column('brid',
sa.Integer,
sa.ForeignKey('buildrequests.id'),
nullable=False),
sa.Column('start_time', sa.Integer, nullable=False),
sa.Column('finish_time', sa.Integer),
)
# buildsets
# This table contains input properties for buildsets
buildset_properties = sa.Table(
'buildset_properties',
metadata,
sa.Column('buildsetid',
sa.Integer,
sa.ForeignKey('buildsets.id'),
nullable=False),
sa.Column('property_name', sa.String(256), nullable=False),
# JSON-encoded tuple of (value, source)
sa.Column('property_value', sa.String(1024), nullable=False),
)
# This table represents Buildsets - sets of BuildRequests that share the
# same original cause and source information.
buildsets = sa.Table(
'buildsets',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# a simple external identifier to track down this buildset later, e.g.,
# for try requests
sa.Column('external_idstring', sa.String(256)),
# a short string giving the reason the buildset was created
sa.Column('reason', sa.String(256)),
sa.Column('submitted_at', sa.Integer, nullable=False),
# if this is zero, then the build set is still pending
sa.Column('complete',
sa.SmallInteger,
nullable=False,
server_default=sa.DefaultClause("0")),
sa.Column('complete_at', sa.Integer),
# results is only valid when complete == 1; 0 = SUCCESS, 1 = WARNINGS,
# etc - see master/buildbot/status/builder.py
sa.Column('results', sa.SmallInteger),
# buildset belongs to all sourcestamps with setid
sa.Column('sourcestampsetid', sa.Integer,
sa.ForeignKey('sourcestampsets.id')),
)
# changes
# Files touched in changes
change_files = sa.Table(
'change_files',
metadata,
sa.Column('changeid',
sa.Integer,
sa.ForeignKey('changes.changeid'),
nullable=False),
sa.Column('filename', sa.String(1024), nullable=False),
)
# Properties for changes
change_properties = sa.Table(
'change_properties',
metadata,
sa.Column('changeid',
sa.Integer,
sa.ForeignKey('changes.changeid'),
nullable=False),
sa.Column('property_name', sa.String(256), nullable=False),
# JSON-encoded tuple of (value, source)
sa.Column('property_value', sa.String(1024), nullable=False),
)
# users associated with this change; this allows multiple users for
# situations where a version-control system can represent both an author
# and committer, for example.
change_users = sa.Table(
"change_users",
metadata,
sa.Column("changeid",
sa.Integer,
sa.ForeignKey('changes.changeid'),
nullable=False),
# uid for the author of the change with the given changeid
sa.Column("uid",
sa.Integer,
sa.ForeignKey('users.uid'),
nullable=False))
# Changes to the source code, produced by ChangeSources
changes = sa.Table(
'changes',
metadata,
# changeid also serves as 'change number'
sa.Column('changeid', sa.Integer, primary_key=True),
# author's name (usually an email address)
sa.Column('author', sa.String(256), nullable=False),
# commit comment
sa.Column('comments', sa.String(1024), nullable=False),
# old, CVS-related boolean
sa.Column('is_dir', sa.SmallInteger, nullable=False), # old, for CVS
# The branch where this change occurred. When branch is NULL, that
# means the main branch (trunk, master, etc.)
sa.Column('branch', sa.String(256)),
# revision identifier for this change
sa.Column('revision', sa.String(256)), # CVS uses NULL
sa.Column('revlink', sa.String(256)),
# this is the timestamp of the change - it is usually copied from the
# version-control system, and may be long in the past or even in the
# future!
sa.Column('when_timestamp', sa.Integer, nullable=False),
# an arbitrary string used for filtering changes
sa.Column('category', sa.String(256)),
# repository specifies, along with revision and branch, the
# source tree in which this change was detected.
sa.Column('repository',
sa.String(length=512),
nullable=False,
server_default=''),
# codebase is a logical name to specify what is in the repository
sa.Column('codebase',
sa.String(256),
nullable=False,
server_default=sa.DefaultClause("")),
# project names the project this source code represents. It is used
# later to filter changes
sa.Column('project',
sa.String(length=512),
nullable=False,
server_default=''),
)
# sourcestamps
# Patches for SourceStamps that were generated through the try mechanism
patches = sa.Table(
'patches',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# number of directory levels to strip off (patch -pN)
sa.Column('patchlevel', sa.Integer, nullable=False),
# base64-encoded version of the patch file
sa.Column('patch_base64', sa.Text, nullable=False),
# patch author, if known
sa.Column('patch_author', sa.Text, nullable=False),
# patch comment
sa.Column('patch_comment', sa.Text, nullable=False),
# subdirectory in which the patch should be applied; NULL for top-level
sa.Column('subdir', sa.Text),
)
# The changes that led up to a particular source stamp.
sourcestamp_changes = sa.Table(
'sourcestamp_changes',
metadata,
sa.Column('sourcestampid',
sa.Integer,
sa.ForeignKey('sourcestamps.id'),
nullable=False),
sa.Column('changeid',
sa.Integer,
sa.ForeignKey('changes.changeid'),
nullable=False),
)
# A sourcestampset identifies a set of sourcestamps. A sourcestamp belongs
# to a particular set if the sourcestamp has the same setid
sourcestampsets = sa.Table(
'sourcestampsets',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
)
# A sourcestamp identifies a particular instance of the source code.
# Ideally, this would always be absolute, but in practice source stamps can
# also mean "latest" (when revision is NULL), which is of course a
# time-dependent definition.
sourcestamps = sa.Table(
'sourcestamps',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# the branch to check out. When branch is NULL, that means
# the main branch (trunk, master, etc.)
sa.Column('branch', sa.String(256)),
# the revision to check out, or the latest if NULL
sa.Column('revision', sa.String(256)),
# the patch to apply to generate this source code
sa.Column('patchid', sa.Integer, sa.ForeignKey('patches.id')),
# the repository from which this source should be checked out
sa.Column('repository',
sa.String(length=512),
nullable=False,
server_default=''),
# codebase is a logical name to specify what is in the repository
sa.Column('codebase',
sa.String(256),
nullable=False,
server_default=sa.DefaultClause("")),
# the project this source code represents
sa.Column('project',
sa.String(length=512),
nullable=False,
server_default=''),
# each sourcestamp belongs to a set of sourcestamps
sa.Column('sourcestampsetid', sa.Integer,
sa.ForeignKey('sourcestampsets.id')),
)
# schedulers
# This table references "classified" changes that have not yet been
# "processed". That is, the scheduler has looked at these changes and
# determined that something should be done, but that hasn't happened yet.
# Rows are deleted from this table as soon as the scheduler is done with
# the change.
scheduler_changes = sa.Table(
'scheduler_changes',
metadata,
sa.Column('objectid', sa.Integer, sa.ForeignKey('objects.id')),
sa.Column('changeid', sa.Integer, sa.ForeignKey('changes.changeid')),
# true (nonzero) if this change is important to this scheduler
sa.Column('important', sa.Integer),
)
# objects
# This table uniquely identifies objects that need to maintain state across
# invocations.
objects = sa.Table(
"objects",
metadata,
# unique ID for this object
sa.Column("id", sa.Integer, primary_key=True),
# object's user-given name
sa.Column('name', sa.String(128), nullable=False),
# object's class name, basically representing a "type" for the state
sa.Column('class_name', sa.String(128), nullable=False),
)
# This table stores key/value pairs for objects, where the key is a string
# and the value is a JSON string.
object_state = sa.Table(
"object_state",
metadata,
# object for which this value is set
sa.Column("objectid",
sa.Integer,
sa.ForeignKey('objects.id'),
nullable=False),
# name for this value (local to the object)
sa.Column("name", sa.String(length=256), nullable=False),
# value, as a JSON string
sa.Column("value_json", sa.Text, nullable=False),
)
#users
# This table identifies individual users, and contains buildbot-specific
# information about those users.
users = sa.Table(
"users",
metadata,
# unique user id number
sa.Column("uid", sa.Integer, primary_key=True),
# identifier (nickname) for this user; used for display
sa.Column("identifier", sa.String(256), nullable=False),
# username portion of user credentials for authentication
sa.Column("bb_username", sa.String(128)),
# password portion of user credentials for authentication
sa.Column("bb_password", sa.String(128)),
)
# This table stores information identifying a user that's related to a
# particular interface - a version-control system, status plugin, etc.
users_info = sa.Table(
"users_info",
metadata,
# unique user id number
sa.Column("uid",
sa.Integer,
sa.ForeignKey('users.uid'),
nullable=False),
# type of user attribute, such as 'git'
sa.Column("attr_type", sa.String(128), nullable=False),
# data for given user attribute, such as a commit string or password
sa.Column("attr_data", sa.String(128), nullable=False),
)
# indexes
sa.Index('buildrequests_buildsetid', buildrequests.c.buildsetid)
sa.Index('buildrequests_buildername', buildrequests.c.buildername)
sa.Index('buildrequests_complete', buildrequests.c.complete)
sa.Index('builds_number', builds.c.number)
sa.Index('builds_brid', builds.c.brid)
sa.Index('buildsets_complete', buildsets.c.complete)
sa.Index('buildsets_submitted_at', buildsets.c.submitted_at)
sa.Index('buildset_properties_buildsetid',
buildset_properties.c.buildsetid)
sa.Index('changes_branch', changes.c.branch)
sa.Index('changes_revision', changes.c.revision)
sa.Index('changes_author', changes.c.author)
sa.Index('changes_category', changes.c.category)
sa.Index('changes_when_timestamp', changes.c.when_timestamp)
sa.Index('change_files_changeid', change_files.c.changeid)
sa.Index('change_properties_changeid', change_properties.c.changeid)
sa.Index('scheduler_changes_objectid', scheduler_changes.c.objectid)
sa.Index('scheduler_changes_changeid', scheduler_changes.c.changeid)
sa.Index('scheduler_changes_unique',
scheduler_changes.c.objectid,
scheduler_changes.c.changeid,
unique=True)
sa.Index('sourcestamp_changes_sourcestampid',
sourcestamp_changes.c.sourcestampid)
sa.Index('sourcestamps_sourcestampsetid',
sourcestamps.c.sourcestampsetid,
unique=False)
sa.Index('users_identifier', users.c.identifier, unique=True)
sa.Index('users_info_uid', users_info.c.uid)
sa.Index('users_info_uid_attr_type',
users_info.c.uid,
users_info.c.attr_type,
unique=True)
sa.Index('users_info_attrs',
users_info.c.attr_type,
users_info.c.attr_data,
unique=True)
sa.Index('change_users_changeid', change_users.c.changeid)
sa.Index('users_bb_user', users.c.bb_username, unique=True)
sa.Index('object_identity',
objects.c.name,
objects.c.class_name,
unique=True)
sa.Index('name_per_object',
object_state.c.objectid,
object_state.c.name,
unique=True)
# MySQl creates indexes for foreign keys, and these appear in the
# reflection. This is a list of (table, index) names that should be
# expected on this platform
implied_indexes = [
('change_users', dict(unique=False, column_names=['uid'], name='uid')),
('sourcestamps',
dict(unique=False, column_names=['patchid'], name='patchid')),
('sourcestamp_changes',
dict(unique=False, column_names=['changeid'], name='changeid')),
('buildsets',
dict(unique=False,
column_names=['sourcestampsetid'],
name='buildsets_sourcestampsetid_fkey')),
]
#
# migration support
#
# this is a bit more complicated than might be expected because the first
# seven database versions were once implemented using a homespun migration
# system, and we need to support upgrading masters from that system. The
# old system used a 'version' table, where SQLAlchemy-Migrate uses
# 'migrate_version'
repo_path = util.sibpath(__file__, "migrate")
def is_current(self):
def thd(engine):
# we don't even have to look at the old version table - if there's
# no migrate_version, then we're not up to date.
repo = migrate.versioning.repository.Repository(self.repo_path)
repo_version = repo.latest
try:
# migrate.api doesn't let us hand in an engine
schema = migrate.versioning.schema.ControlledSchema(
engine, self.repo_path)
db_version = schema.version
except exceptions.DatabaseNotControlledError:
return False
return db_version == repo_version
return self.db.pool.do_with_engine(thd)
def upgrade(self):
# here, things are a little tricky. If we have a 'version' table, then
# we need to version_control the database with the proper version
# number, drop 'version', and then upgrade. If we have no 'version'
# table and no 'migrate_version' table, then we need to version_control
# the database. Otherwise, we just need to upgrade it.
def table_exists(engine, tbl):
try:
r = engine.execute("select * from %s limit 1" % tbl)
r.close()
return True
except:
return False
# http://code.google.com/p/sqlalchemy-migrate/issues/detail?id=100
# means we cannot use the migrate.versioning.api module. So these
# methods perform similar wrapping functions to what is done by the API
# functions, but without disposing of the engine.
def upgrade(engine):
schema = migrate.versioning.schema.ControlledSchema(
engine, self.repo_path)
changeset = schema.changeset(None)
for version, change in changeset:
log.msg('migrating schema version %s -> %d' %
(version, version + 1))
schema.runchange(version, change, 1)
def check_sqlalchemy_migrate_version():
# sqlalchemy-migrate started including a version number in 0.7; we
# support back to 0.6.1, but not 0.6. We'll use some discovered
# differences between 0.6.1 and 0.6 to get that resolution.
version = getattr(migrate, '__version__', 'old')
if version == 'old':
try:
from migrate.versioning import schemadiff
if hasattr(schemadiff, 'ColDiff'):
version = "0.6.1"
else:
version = "0.6"
except:
version = "0.0"
version_tup = tuple(map(int, version.split('.')))
log.msg("using SQLAlchemy-Migrate version %s" % (version, ))
if version_tup < (0, 6, 1):
raise RuntimeError("You are using SQLAlchemy-Migrate %s. "
"The minimum version is 0.6.1." %
(version, ))
def version_control(engine, version=None):
migrate.versioning.schema.ControlledSchema.create(
engine, self.repo_path, version)
# the upgrade process must run in a db thread
def thd(engine):
# if the migrate_version table exists, we can just let migrate
# take care of this process.
if table_exists(engine, 'migrate_version'):
upgrade(engine)
# if the version table exists, then we can version_control things
# at that version, drop the version table, and let migrate take
# care of the rest.
elif table_exists(engine, 'version'):
# get the existing version
r = engine.execute("select version from version limit 1")
old_version = r.scalar()
# set up migrate at the same version
version_control(engine, old_version)
# drop the no-longer-required version table, using a dummy
# metadata entry
table = sa.Table('version', self.metadata,
sa.Column('x', sa.Integer))
table.drop(bind=engine)
# clear the dummy metadata entry
self.metadata.remove(table)
# and, finally, upgrade using migrate
upgrade(engine)
# otherwise, this db is uncontrolled, so we just version control it
# and update it.
else:
version_control(engine)
upgrade(engine)
check_sqlalchemy_migrate_version()
return self.db.pool.do_with_engine(thd)
def create_tables_thd(self, conn):
metadata = sa.MetaData()
metadata.bind = conn
changes = sautils.Table(
'changes',
metadata,
sa.Column('changeid', sa.Integer, primary_key=True),
# the rest is unimportant
)
changes.create()
buildsets = sautils.Table(
'buildsets',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# the rest is unimportant
)
buildsets.create()
self.schedulers = sautils.Table(
"schedulers",
metadata,
sa.Column('schedulerid', sa.Integer, primary_key=True),
sa.Column('name', sa.String(128), nullable=False),
sa.Column('class_name', sa.String(128), nullable=False),
)
self.schedulers.create(bind=conn)
sa.Index('name_and_class', self.schedulers.c.name,
self.schedulers.c.class_name).create()
self.scheduler_changes = sautils.Table(
'scheduler_changes',
metadata,
sa.Column('schedulerid', sa.Integer,
sa.ForeignKey('schedulers.schedulerid')),
sa.Column('changeid', sa.Integer,
sa.ForeignKey('changes.changeid')),
sa.Column('important', sa.SmallInteger),
)
self.scheduler_changes.create()
sa.Index('scheduler_changes_schedulerid',
self.scheduler_changes.c.schedulerid).create()
sa.Index('scheduler_changes_changeid',
self.scheduler_changes.c.changeid).create()
sa.Index('scheduler_changes_unique',
self.scheduler_changes.c.schedulerid,
self.scheduler_changes.c.changeid,
unique=True).create()
self.scheduler_upstream_buildsets = sautils.Table(
'scheduler_upstream_buildsets',
metadata,
sa.Column('buildsetid', sa.Integer, sa.ForeignKey('buildsets.id')),
sa.Column('schedulerid', sa.Integer,
sa.ForeignKey('schedulers.schedulerid')),
)
self.scheduler_upstream_buildsets.create()
sa.Index('scheduler_upstream_buildsets_buildsetid',
self.scheduler_upstream_buildsets.c.buildsetid).create()
sa.Index('scheduler_upstream_buildsets_schedulerid',
self.scheduler_upstream_buildsets.c.schedulerid).create()
self.objects = sautils.Table(
"objects",
metadata,
sa.Column("id", sa.Integer, primary_key=True),
sa.Column('name', sa.String(128), nullable=False),
sa.Column('class_name', sa.String(128), nullable=False),
)
self.objects.create(bind=conn)
sa.Index('object_identity',
self.objects.c.name,
self.objects.c.class_name,
unique=True).create()
class HeartRate(Base):
"""Define the HeartRate object schema"""
__tablename__ = 'heart_rate'
__table_args__ = {"schema": "fitbit"}
date = Column(Date, primary_key=True)
r_hr = Column(Integer)
hr = Column(JSONB)
# Standard SQLAlchemy connection configs
connection_string = 'postgresql://james:@localhost:5432/fitbit'
db = sqlalchemy.create_engine(connection_string)
engine = db.connect()
meta = sqlalchemy.MetaData(engine, schema="fitbit")
session_factory = sessionmaker(engine) # New SessionFactory object
Base.metadata.create_all(engine) # Create a table if doesn't exist
def rate_limited(maxPerSecond):
"""Create a rate_limited decorator that limits function calls to x per second"""
minInterval = 1.0 / float(maxPerSecond)
def decorate(func):
lastTimeCalled = [0.0]
def rateLimitedFunction(*args, **kargs):
elapsed = time.clock() - lastTimeCalled[0]
leftToWait = minInterval - elapsed
from aiodataloader import DataLoader
from aiotools import apartial
import sqlalchemy as sa
from sqlalchemy.types import (SchemaType, TypeDecorator, CHAR)
from sqlalchemy.dialects.postgresql import UUID, ENUM
# The common shared metadata instance
convention = {
"ix": 'ix_%(column_0_label)s',
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s",
}
metadata = sa.MetaData(naming_convention=convention)
# helper functions
def zero_if_none(val):
return 0 if val is None else val
class EnumType(TypeDecorator, SchemaType):
'''
A stripped-down version of Spoqa's sqlalchemy-enum34.
It also handles postgres-specific enum type creation.
'''
impl = ENUM
def get_compute_domain(
self,
domain_kwargs: Dict,
domain_type: Union[str, "MetricDomainTypes"],
accessor_keys: Optional[Iterable[str]] = None,
) -> Tuple["sa.sql.Selectable", dict, dict]:
"""Uses a given batch dictionary and domain kwargs to obtain a SqlAlchemy column object.
Args:
domain_kwargs (dict) - A dictionary consisting of the domain kwargs specifying which data to obtain
domain_type (str or "MetricDomainTypes") - an Enum value indicating which metric domain the user would
like to be using, or a corresponding string value representing it. String types include "identity", "column",
"column_pair", "table" and "other". Enum types include capitalized versions of these from the class
MetricDomainTypes.
accessor_keys (str iterable) - keys that are part of the compute domain but should be ignored when describing
the domain and simply transferred with their associated values into accessor_domain_kwargs.
Returns:
SqlAlchemy column
"""
# Extracting value from enum if it is given for future computation
domain_type = MetricDomainTypes(domain_type)
batch_id = domain_kwargs.get("batch_id")
if batch_id is None:
# We allow no batch id specified if there is only one batch
if self.active_batch_data:
data_object = self.active_batch_data
else:
raise GreatExpectationsError(
"No batch is specified, but could not identify a loaded batch."
)
else:
if batch_id in self.loaded_batch_data_dict:
data_object = self.loaded_batch_data_dict[batch_id]
else:
raise GreatExpectationsError(
f"Unable to find batch with batch_id {batch_id}"
)
compute_domain_kwargs = copy.deepcopy(domain_kwargs)
accessor_domain_kwargs = dict()
if "table" in domain_kwargs and domain_kwargs["table"] is not None:
# TODO: Add logic to handle record_set_name once implemented
# (i.e. multiple record sets (tables) in one batch
if domain_kwargs["table"] != data_object.selectable.name:
selectable = sa.Table(
domain_kwargs["table"],
sa.MetaData(),
schema_name=data_object._schema_name,
)
else:
selectable = data_object.selectable
elif "query" in domain_kwargs:
raise ValueError(
"query is not currently supported by SqlAlchemyExecutionEngine"
)
else:
selectable = data_object.selectable
if (
"row_condition" in domain_kwargs
and domain_kwargs["row_condition"] is not None
):
condition_parser = domain_kwargs["condition_parser"]
if condition_parser == "great_expectations__experimental__":
parsed_condition = parse_condition_to_sqlalchemy(
domain_kwargs["row_condition"]
)
selectable = sa.select(
"*", from_obj=selectable, whereclause=parsed_condition
)
else:
raise GreatExpectationsError(
"SqlAlchemyExecutionEngine only supports the great_expectations condition_parser."
)
# Warning user if accessor keys are in any domain that is not of type table, will be ignored
if (
domain_type != MetricDomainTypes.TABLE
and accessor_keys is not None
and len(accessor_keys) > 0
):
logger.warning(
"Accessor keys ignored since Metric Domain Type is not 'table'"
)
if domain_type == MetricDomainTypes.TABLE:
if accessor_keys is not None and len(accessor_keys) > 0:
for key in accessor_keys:
accessor_domain_kwargs[key] = compute_domain_kwargs.pop(key)
if len(domain_kwargs.keys()) > 0:
for key in compute_domain_kwargs.keys():
# Warning user if kwarg not "normal"
if key not in [
"batch_id",
"table",
"row_condition",
"condition_parser",
]:
logger.warning(
f"Unexpected key {key} found in domain_kwargs for domain type {domain_type.value}"
)
return selectable, compute_domain_kwargs, accessor_domain_kwargs
# If user has stated they want a column, checking if one is provided, and
elif domain_type == MetricDomainTypes.COLUMN:
if "column" in compute_domain_kwargs:
# Checking if case- sensitive and using appropriate name
if self.active_batch_data.use_quoted_name:
accessor_domain_kwargs["column"] = quoted_name(
compute_domain_kwargs.pop("column")
)
else:
accessor_domain_kwargs["column"] = compute_domain_kwargs.pop(
"column"
)
else:
# If column not given
raise GreatExpectationsError(
"Column not provided in compute_domain_kwargs"
)
# Else, if column pair values requested
elif domain_type == MetricDomainTypes.COLUMN_PAIR:
# Ensuring column_A and column_B parameters provided
if (
"column_A" in compute_domain_kwargs
and "column_B" in compute_domain_kwargs
):
if self.active_batch_data.use_quoted_name:
# If case matters...
accessor_domain_kwargs["column_A"] = quoted_name(
compute_domain_kwargs.pop("column_A")
)
accessor_domain_kwargs["column_B"] = quoted_name(
compute_domain_kwargs.pop("column_B")
)
else:
accessor_domain_kwargs["column_A"] = compute_domain_kwargs.pop(
"column_A"
)
accessor_domain_kwargs["column_B"] = compute_domain_kwargs.pop(
"column_B"
)
else:
raise GreatExpectationsError(
"column_A or column_B not found within compute_domain_kwargs"
)
# Checking if table or identity or other provided, column is not specified. If it is, warning the user
elif domain_type == MetricDomainTypes.MULTICOLUMN:
if "columns" in compute_domain_kwargs:
# If columns exist
accessor_domain_kwargs["columns"] = compute_domain_kwargs.pop("columns")
# Filtering if identity
elif domain_type == MetricDomainTypes.IDENTITY:
# If we would like our data to become a single column
if "column" in compute_domain_kwargs:
if self.active_batch_data.use_quoted_name:
selectable = sa.select(
[sa.column(quoted_name(compute_domain_kwargs["column"]))]
).select_from(selectable)
else:
selectable = sa.select(
[sa.column(compute_domain_kwargs["column"])]
).select_from(selectable)
# If we would like our data to now become a column pair
elif ("column_A" in compute_domain_kwargs) and (
"column_B" in compute_domain_kwargs
):
if self.active_batch_data.use_quoted_name:
selectable = sa.select(
[
sa.column(quoted_name(compute_domain_kwargs["column_A"])),
sa.column(quoted_name(compute_domain_kwargs["column_B"])),
]
).select_from(selectable)
else:
selectable = sa.select(
[
sa.column(compute_domain_kwargs["column_A"]),
sa.column(compute_domain_kwargs["column_B"]),
]
).select_from(selectable)
else:
# If we would like our data to become a multicolumn
if "columns" in compute_domain_kwargs:
if self.active_batch_data.use_quoted_name:
# Building a list of column objects used for sql alchemy selection
to_select = [
sa.column(quoted_name(col))
for col in compute_domain_kwargs["columns"]
]
selectable = sa.select(to_select).select_from(selectable)
else:
to_select = [
sa.column(col) for col in compute_domain_kwargs["columns"]
]
selectable = sa.select(to_select).select_from(selectable)
# Letting selectable fall through
return selectable, compute_domain_kwargs, accessor_domain_kwargs
def main(connection_uri, schema_name, ohdsi_concept_schema, output_path,
max_size, train_split, annotation_style):
exclude_concepts = [4266367] # Exclude influenza
engine = sa.create_engine(connection_uri)
with engine.connect() as connection:
meta_data = sa.MetaData(connection, schema=schema_name)
meta_data.reflect()
print("Get Concept Labels")
cursor = connection.execute(
f"select concept_id, concept_name from {ohdsi_concept_schema}.concept where concept_id in (select distinct note_nlp_concept_id as concept_id from {schema_name}.note_nlp)"
)
result = list(cursor)
concept_dict = {r["concept_id"]: r["concept_name"] for r in result}
note_nlp_obj = meta_data.tables[schema_name + "." + "note_nlp"]
query_obj_1 = note_nlp_obj.select().where(sa.not_(note_nlp_obj.c.note_nlp_concept_id.\
in_(exclude_concepts))).order_by(note_nlp_obj.c.note_id) # Exclude FLU as there are many false positives
print("Executing query")
cursor = connection.execute(query_obj_1)
snippet_dict = {}
re_date = re.compile("[0-9]{2}/[0-9]{2}/[0-9]{4} [0-9]{2}:[0-9]{2} EST"
) # for finding dates that start a note
i = 0
for row in cursor:
# Some cleaning
snippet = row["snippet"].strip()
re_snippet_obj = re_date.search(snippet)
if re_snippet_obj is not None:
snippet = snippet[re_snippet_obj.end():]
snippet = snippet.strip()
lexical_variant = row["lexical_variant"]
note_nlp_concept_id = row["note_nlp_concept_id"]
note_id = row["note_id"]
note_nlp_id = row["note_nlp_id"]
term_modifiers = row["term_modifiers"]
modifier_list = term_modifiers.split(",")
sha1 = hashlib.sha1(snippet.encode("utf8",
errors="replace")).hexdigest()
if lexical_variant in snippet:
start_offset = snippet.index(lexical_variant)
end_offset = start_offset + len(lexical_variant)
match_dict = {
"sha1": sha1,
"lexical_variant": lexical_variant,
"note_id": note_id,
"note_nlp_id": note_nlp_id,
"term_modifiers": modifier_list,
"note_nlp_concept_id": note_nlp_concept_id,
"offsets": (start_offset, end_offset),
}
if snippet in snippet_dict:
snippet_dict[snippet] += [match_dict]
else:
snippet_dict[snippet] = [match_dict]
if max_size is not None and i == max_size:
break
if i > 0 and i % 1000 == 0:
print(f"Number of fragments processed: {i}")
i += 1
# Build annotations
print(
f"Build annotations for {len(snippet_dict)} distinct text fragments"
)
nlp = spacy.load("en_core_web_sm")
doc_list = []
meta_data_list = []
i = 0
for snippet in snippet_dict:
sha1 = hashlib.sha1(snippet.encode("utf8",
errors="replace")).hexdigest()
doc = nlp(snippet)
doc.user_data["sha1"] = sha1
variants_found = []
spans = []
for variant_dict in snippet_dict[snippet]:
variant = variant_dict["lexical_variant"]
meta_data_list += [variant_dict]
start_position, end_position = variant_dict["offsets"]
term_modifiers = variant_dict["term_modifiers"]
concept_id = variant_dict["note_nlp_concept_id"]
annotation_label = term_modifiers[0].split("=")[1]
if variant not in variants_found and annotation_label is not None:
variants_found += [variant]
if annotation_style == "label_term_usage":
spans += [
doc.char_span(start_position,
end_position,
label=annotation_label,
alignment_mode="expand")
]
elif annotation_style == "label_positive_concepts":
if annotation_label == "Positive":
if concept_id in concept_dict:
concept_name = concept_dict[
concept_id] + "|" + str(concept_id)
else:
concept_name = str(concept_id)
spans += [
doc.char_span(start_position,
end_position,
label=concept_name,
alignment_mode="expand")
]
elif annotation_style == "label_negated_concepts":
if annotation_label == "Negated":
if concept_id in concept_dict:
concept_name = concept_dict[
concept_id] + "|" + str(concept_id)
else:
concept_name = str(concept_id)
spans += [
doc.char_span(start_position,
end_position,
label=concept_name,
alignment_mode="expand")
]
try:
doc.set_ents(spans)
doc_list += [doc]
except ValueError:
print("Unable to set annotations")
print(snippet)
if i > 0 and i % 1000 == 0:
print(f"Processed {i} snippets")
i += 1
p_output_path = pathlib.Path(output_path)
meta_data_df = pd.DataFrame(meta_data_list)
meta_data_df.to_csv(p_output_path / "meta_data.csv", index=False)
full_set_path = p_output_path / f"ohnlp_full_set_{annotation_style}.spacy"
print(f"Writing: '{full_set_path}'")
full_set_obj = DocBin(docs=doc_list, store_user_data=True)
full_set_obj.to_disk(full_set_path)
# Shuffle list to randomize
random.shuffle(doc_list)
number_of_documents = len(doc_list)
test_size = int(math.floor(number_of_documents * train_split))
training_size = number_of_documents - test_size
training_corpora_path = p_output_path / f"ohnlp_ohdsi_train_{annotation_style}.spacy"
print(
f"Writing training set (n={training_size}) to: '{training_corpora_path}'"
)
train_doc_bin_obj = DocBin(docs=doc_list[0:training_size],
store_user_data=True)
train_doc_bin_obj.to_disk(training_corpora_path)
test_doc_bin_obj = DocBin(docs=doc_list[training_size:],
store_user_data=True)
testing_corpora_path = p_output_path / f"ohnlp_ohdsi_test_{annotation_style}.spacy"
print(f"Writing test set (n={test_size}) to '{testing_corpora_path}'")
test_doc_bin_obj.to_disk(testing_corpora_path)
def downgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
access_token_table = sql.Table('access_token_oauth2', meta, autoload=True)
access_token_table.c.authorizing_user_id.alter(nullable=False)
def consumer_credit_histogram(date_start, date_end, axes):
#def percentage_with_credit():
import sqlalchemy as sa
# create metadata object
metadata = sa.MetaData(
'postgres://*****:*****@localhost:5432/gateway')
# define table objects from database
vpl = sa.Table('view_primary_log', metadata, autoload=True)
vm = sa.Table('view_midnight', metadata, autoload=True)
print 'executing non-zero hours query'
# count number of hours per circuit with credit greater than zero
query = sa.select([
vpl.c.circuit_id,
sa.func.count(vpl.c.circuit_id).over(
partition_by=vpl.c.circuit_id).label('count')
],
whereclause=sa.and_(vpl.c.credit > 0,
vpl.c.meter_timestamp > date_start,
vpl.c.meter_timestamp < date_end,
vpl.c.meter_name.in_(meter_name),
vpl.c.ip_address != mains_ip),
order_by=vpl.c.circuit_id,
distinct=True)
#print query
result = query.execute()
circuit_ids = []
hours_with_credit = []
non_zero_hours_dict = {}
for r in result:
circuit_ids.append(r.circuit_id)
hours_with_credit.append(r.count)
non_zero_hours_dict[r.circuit_id] = r.count
nzdk = set(non_zero_hours_dict.keys())
print 'executing total hours query'
# count number of hours per circuit reporting total
query = sa.select([
vpl.c.circuit_id,
sa.func.count(vpl.c.circuit_id).over(
partition_by=vpl.c.circuit_id).label('count')
],
whereclause=sa.and_(vpl.c.meter_timestamp > date_start,
vpl.c.meter_timestamp < date_end,
vpl.c.meter_name.in_(meter_name),
vpl.c.ip_address != mains_ip),
order_by=vpl.c.circuit_id,
distinct=True)
#print query
result = query.execute()
circuit_ids = []
total_hours = []
total_hours_dict = {}
for r in result:
circuit_ids.append(r.circuit_id)
total_hours.append(r.count)
total_hours_dict[r.circuit_id] = r.count
thdk = set(total_hours_dict.keys())
if nzdk == thdk:
print 'circuits match'
else:
print 'circuits do not match'
circuits = nzdk.intersection(thdk)
percentage_with_credit = []
for c in circuits:
percentage_with_credit.append(non_zero_hours_dict[c] /
total_hours_dict[c])
axes.hist(percentage_with_credit,
bins=np.arange(0, 1.1, 0.1),
cumulative=True,
normed=True)
axes.set_title(str(date_start) + ' - ' + str(date_end))
axes.set_xlabel('Percentage of Time With Credit Available')
axes.set_ylabel('Fraction of Customers')
axes.grid(True)
def test_already_name_drum_create(driver):
"""Создание барабана вещания c уже существующим именем"""
test_login(driver)
time.sleep(5)
wait = WebDriverWait(driver, 30)
wait.until(EC.element_to_be_clickable((By.TAG_NAME, "div[class='left-nav__menu-unit-header-title']")))
WebDriverWait(driver, 30)
# Подключаемся к БД
engine = db.create_engine('postgresql://*****:*****@192.168.100.3/zms_task_6012', echo=True)
connection = engine.connect()
metadata = db.MetaData()
revolvers = db.Table('revolvers', metadata, autoload=True, autoload_with=engine)
# TODO Научить выбирать только по одному столбцу
# https://docs.sqlalchemy.org/en/13/core/tutorial.html
# https://stackoverflow.com/questions/37133774/how-can-i-select-only-one-column-using-sqlalchemy
# https://docs.sqlalchemy.org/en/13/orm/query.html
# https://docs.sqlalchemy.org/en/13/orm/loading_columns.html
query = db.select([revolvers])
ResultProxy = connection.execute(query)
ResultSet = ResultProxy.fetchall()
ResultSet[:3]
# Эфир
driver.find_elements_by_tag_name("div[class='left-nav__menu-unit-header-title']")[0].click()
WebDriverWait(driver, 10)
# Управление барабанами вещания
driver.find_element_by_tag_name("a[href='/ether/broadcastRevolvers']").click()
WebDriverWait(driver, 10)
# Создать барабан
driver.find_elements_by_tag_name("button[class='btn btn-primary']")[1].click()
wait_time()
# Город
driver.find_elements_by_tag_name("div[class='col col-6'] [class='vue-select io']")[0].click()
#wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__search'] input[tabindex='-1']")[0].send_keys("Москва")
#wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__search'] input[tabindex='-1']")[0].send_keys(Keys.ENTER)
#wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__option']")[0].click()
#wait_time()
# driver.find_elements_by_tag_name("div[class='col col-6'] [class='vue-select io']")[0].click()
#
# #wait_time()
#
# #driver.find_elements_by_tag_name("div[id='city'] div[id='4']")[1].click()
#
# driver.find_elements_by_tag_name("input[tabindex='-1']")[0].click()
#
# driver.find_elements_by_tag_name("div[id='city'][class='vue-select__options-holder'] div[id='4']")[1].click()
#
#
#
# # TODO Этот гад не хочет находиться. НЕ интерактивный видите ли
#
# wait.until(EC.presence_of_element_located((By.TAG_NAME, "div[class='vue-select__options-box']"
# " div[class='vue-select__options-holder']"
# " div[class='vue-select__option']")))
#
# driver.find_elements_by_tag_name("div[id='city'][class='vue-select__options-holder'] div[id='4']")[1].click()
#
# wait_time()
# Название
driver.find_elements_by_tag_name("div[class='row'] input[type='text']")[0].send_keys("Autotest Drum")
# Описание
driver.find_elements_by_tag_name("div[class='row'] input[type='text']")[1].send_keys("Autotest Drum")
# График для рабочих дней
driver.find_elements_by_tag_name("div[class='vue-select io']")[0].click()
#wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__search'] input[tabindex='-1']")[4].send_keys("Moscow AutoGraph")
#wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__search'] input[tabindex='-1']")[4].send_keys(Keys.ENTER)
wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__option']")[8].click()
#wait_time()
# График для праздников
driver.find_elements_by_tag_name("div[class='vue-select io']")[0].click()
#wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__search'] input[tabindex='-1']")[5].send_keys("Moscow AutoGraph")
#wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__search'] input[tabindex='-1']")[5].send_keys(Keys.ENTER)
wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__option']")[8].click()
#wait_time()
# График для выходных дней
driver.find_elements_by_tag_name("div[class='vue-select io']")[0].click()
#wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__search'] input[tabindex='-1']")[6].send_keys("Moscow AutoGraph")
#wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__search'] input[tabindex='-1']")[6].send_keys(Keys.ENTER)
wait_time()
driver.find_elements_by_tag_name("div[class='vue-select__option']")[8].click()
wait_time()
# Нажать кнопку "Сохранить"
driver.find_element_by_tag_name("button[class='btn btn-success']").click()
wait_time()
# Проверка уведомления об успешно созданном Барабане
notify_drum_created = driver.find_element_by_tag_name("span[data-notify='message']").get_property("innerText")
valid_notify = "Барабан успешно создан"
if notify_drum_created == valid_notify:
print("\n Drum broadcast has created. Notify message is valid.")
def sqlite_alter_columns(table_name, column_defs):
"""Implement alter columns for SQLite.
The ALTER COLUMN command isn't supported by SQLite specification.
Instead of calling ALTER COLUMN it uses the following workaround:
* create temp table '{table_name}_{rand_uuid}', with some column
defs replaced;
* copy all data to the temp table;
* drop old table;
* rename temp table to the old table name.
"""
connection = op.get_bind()
meta = sqlalchemy.MetaData(bind=connection)
meta.reflect()
changed_columns = {}
indexes = []
for col in column_defs:
# If we are to have an index on the column, don't create it
# immediately, instead, add it to a list of indexes to create
# after the table rename.
if col.index:
indexes.append(('ix_%s_%s' % (table_name, col.name), table_name,
[col.name], col.unique))
col.unique = False
col.index = False
changed_columns[col.name] = col
# construct lists of all columns and their names
old_columns = []
new_columns = []
column_names = []
for column in meta.tables[table_name].columns:
column_names.append(column.name)
old_columns.append(column)
if column.name in changed_columns.keys():
new_columns.append(changed_columns[column.name])
else:
col_copy = column.copy()
new_columns.append(col_copy)
for key in meta.tables[table_name].foreign_keys:
constraint = key.constraint
con_copy = constraint.copy()
new_columns.append(con_copy)
for index in meta.tables[table_name].indexes:
# If this is a single column index for a changed column, don't
# copy it because we may already be creating a new version of
# it (or removing it).
idx_columns = [col.name for col in index.columns]
if len(idx_columns) == 1 and idx_columns[0] in changed_columns.keys():
continue
# Otherwise, recreate the index.
indexes.append(
(index.name, table_name, [col.name
for col in index.columns], index.unique))
# create temp table
tmp_table_name = "%s_%s" % (table_name, six.text_type(uuid.uuid4()))
op.create_table(tmp_table_name, *new_columns)
meta.reflect()
try:
# copy data from the old table to the temp one
sql_select = sqlalchemy.sql.select(old_columns)
connection.execute(
sqlalchemy.sql.insert(meta.tables[tmp_table_name]).from_select(
column_names, sql_select))
except Exception:
op.drop_table(tmp_table_name)
raise
# drop the old table and rename temp table to the old table name
op.drop_table(table_name)
op.rename_table(tmp_table_name, table_name)
# (re-)create indexes
for index in indexes:
op.create_index(op.f(index[0]), index[1], index[2], unique=index[3])
def setUp(self):
self.engine = sa.create_engine('crate://')
metadata = sa.MetaData()
self.mytable = sa.Table('mytable', metadata,
sa.Column('name', sa.String),
sa.Column('data', Craty))
def db2sql(ctx, connection, options=None, debug=False, prefix='db2sql'):
"""
Config is a dictionary with settings:
{'db2sql.ignore_tables': [r'^session_.*$']}
"""
if options is None:
options = DBToSQL.sql2cubetl_options_default
connection.url = ctx.interpolate(connection.url)
engine = connection.engine(
) # create_engine(ctx.interpolate(connection.url))
metadata = sqlalchemy.MetaData()
metadata.reflect(engine)
#connection_urn = "%s.conn" % prefix # .%s" % (prefix, db_url)
#connection = ctx.add(connection_urn, sql.Connection(url=engine.url))
#cubetlconfig.load_config(ctx, os.path.dirname(__file__) + "/cubetl-datetime.yaml")
#ctx.register('cubetl.datetime')
#ctx.register(connection)
# Load yaml library definitions that are dependencies
logger.info("Importing CubETL SQL schema from database: %s" %
engine.url)
for dbtable in metadata.sorted_tables:
ignored = False
ignore_re_list = options.get('db2sql.ignore_tables', [])
for ignore_re in ignore_re_list:
if re.match(ignore_re, dbtable.name):
ignored = True
if ignored:
logger.info("Ignored table: %s" % dbtable.name)
continue
logger.info("Table: %s" % dbtable.name)
#tablename = slugify.slugify(dbtable.name, separator="_")
columns = []
for dbcol in dbtable.columns:
#if dbcol.name in exclude_columns:
# logger.info()
# continue
logger.info("Column: %s [type=%s, null=%s, pk=%s, fk=%s]" %
(dbcol.name, coltype(dbcol), dbcol.nullable,
dbcol.primary_key, dbcol.foreign_keys))
if dbcol.primary_key:
column = cubetl.sql.sql.SQLColumn(name=dbcol.name,
pk=True,
type=coltype(dbcol))
elif dbcol.foreign_keys and len(dbcol.foreign_keys) > 0:
if len(dbcol.foreign_keys) > 1:
# TODO: Support this
raise Exception(
"Multiple foreign keys found for column: %s" %
(dbcol.name))
foreign_sqlcolumn_name = "%s.table.%s.col.%s" % (
prefix, list(dbcol.foreign_keys)[0].column.table.name,
list(dbcol.foreign_keys)[0].column.name)
foreign_sqlcolumn = ctx.get(foreign_sqlcolumn_name,
fail=False)
if not foreign_sqlcolumn and (True):
logger.warning(
"Skipped foreign key %s in table %s, as foreign key column (%s.%s) was not found.",
dbcol.name, dbtable.name,
list(dbcol.foreign_keys)[0].column.table.name,
list(dbcol.foreign_keys)[0].column.name)
continue
column = cubetl.sql.sql.SQLColumnFK(
name=dbcol.name,
label=functions.labelify(dbcol.name),
pk=dbcol.primary_key,
type=coltype(dbcol),
fk_sqlcolumn=foreign_sqlcolumn)
else:
column = cubetl.sql.sql.SQLColumn(name=dbcol.name,
pk=dbcol.primary_key,
type=coltype(dbcol),
label=functions.labelify(
dbcol.name))
sqlcol_urn = "%s.table.%s.col.%s" % (prefix, dbtable.name,
column.name)
ctx.add(sqlcol_urn, column)
columns.append(column)
# Define table
sqltable_urn = "%s.table.%s" % (prefix, dbtable.name)
sqltable = ctx.add(
sqltable_urn,
cubetl.sql.sql.SQLTable(name=dbtable.name,
connection=connection,
columns=columns,
label=functions.labelify(
dbtable.name)))
#printconfig = PrintConfig()
#printflow = Chain(fork=True, steps=[printconfig])
#result = ctx.process(printflow)
return ctx
def get_batch(self, batch_kwargs, batch_parameters=None):
# We need to build a batch_id to be used in the dataframe
batch_markers = BatchMarkers({
"ge_load_time":
datetime.datetime.now(
datetime.timezone.utc).strftime("%Y%m%dT%H%M%S.%fZ")
})
if "bigquery_temp_table" in batch_kwargs:
query_support_table_name = batch_kwargs.get("bigquery_temp_table")
elif "snowflake_transient_table" in batch_kwargs:
# Snowflake can use either a transient or temp table, so we allow a table_name to be provided
query_support_table_name = batch_kwargs.get(
"snowflake_transient_table")
else:
query_support_table_name = None
if "query" in batch_kwargs:
if "limit" in batch_kwargs or "offset" in batch_kwargs:
logger.warning(
"Limit and offset parameters are ignored when using query-based batch_kwargs; consider "
"adding limit and offset directly to the generated query.")
if "query_parameters" in batch_kwargs:
query = Template(batch_kwargs["query"]).safe_substitute(
batch_kwargs["query_parameters"])
else:
query = batch_kwargs["query"]
batch_reference = SqlAlchemyBatchReference(
engine=self.engine,
query=query,
table_name=query_support_table_name,
schema=batch_kwargs.get("schema"),
)
elif "table" in batch_kwargs:
table = batch_kwargs["table"]
if batch_kwargs.get("use_quoted_name"):
table = quoted_name(table, quote=True)
limit = batch_kwargs.get("limit")
offset = batch_kwargs.get("offset")
if limit is not None or offset is not None:
logger.info(
"Generating query from table batch_kwargs based on limit and offset"
)
# In BigQuery the table name is already qualified with its schema name
if self.engine.dialect.name.lower() == "bigquery":
schema = None
else:
schema = batch_kwargs.get("schema")
# limit doesn't compile properly for oracle so we will append rownum to query string later
if self.engine.dialect.name.lower() == "oracle":
raw_query = sqlalchemy.select(
[sqlalchemy.text("*")]).select_from(
sqlalchemy.schema.Table(table,
sqlalchemy.MetaData(),
schema=schema))
else:
raw_query = (sqlalchemy.select(
[sqlalchemy.text("*")]).select_from(
sqlalchemy.schema.Table(
table, sqlalchemy.MetaData(),
schema=schema)).offset(offset).limit(limit))
query = str(
raw_query.compile(self.engine,
compile_kwargs={"literal_binds": True}))
# use rownum instead of limit in oracle
if self.engine.dialect.name.lower() == "oracle":
query += "\nWHERE ROWNUM <= %d" % limit
batch_reference = SqlAlchemyBatchReference(
engine=self.engine,
query=query,
table_name=query_support_table_name,
schema=batch_kwargs.get("schema"),
)
else:
batch_reference = SqlAlchemyBatchReference(
engine=self.engine,
table_name=table,
schema=batch_kwargs.get("schema"),
)
else:
raise ValueError(
"Invalid batch_kwargs: exactly one of 'table' or 'query' must be specified"
)
return Batch(
datasource_name=self.name,
batch_kwargs=batch_kwargs,
data=batch_reference,
batch_parameters=batch_parameters,
batch_markers=batch_markers,
data_context=self._data_context,
)
def main():
import subprocess
import yaml
import docker
import os
import yamlarg
import shutil
import sys
pkgdir = sys.modules['guacamole_compose'].__path__[0]
args = yamlarg.parse(os.path.join(pkgdir, 'arguments.yaml'))
if args['version']:
with open(os.path.join(pkgdir, 'VERSION'), 'r') as f:
print(f.read())
if args['init']:
# Check if guacamole-compose is being ran as sudo.
if os.getenv("SUDO_USER") is not None:
cprint(
"Initialization failed! Do not run 'guacamole-compose --init' as sudo.",
'FAIL')
else:
print("Creating structure and paramters.yaml...")
for folder in [
'./guacamole_home', './guacamole_home/extensions',
'./nginx', './nginx/conf', './nginx/certs', './nginx/auth',
'./haproxy', './haproxy/certs', './tomcat', './shared'
]:
if not os.path.exists(folder):
os.makedirs(folder)
pkgfiles = {
'parameters.yaml': './parameters.yaml',
'nginx_init.conf': './nginx/conf/nginx.conf',
'haproxy_init.cfg': './haproxy/haproxy.cfg',
'server.xml': './tomcat/server.xml'
}
for pkgfile, dstfile in pkgfiles.items():
if not os.path.isfile(dstfile):
shutil.copy(os.path.join(pkgdir, 'templates/' + pkgfile),
dstfile)
elif not args['init'] and not args['version']:
params = yaml.load(open('parameters.yaml', 'r'),
Loader=yaml.FullLoader)
client = docker.from_env()
if args['clean']:
print("Running docker-compose down...")
try:
docker_compose_cmd = subprocess.run(['docker-compose down'],
shell=True)
except:
import traceback
print(traceback.format_exc())
print("Clearing generated directories...")
client.containers.prune()
client.volumes.prune()
# Commented out the image prune - so if guacamole images weren't
# updated, you don't have to download them again.
# client.images.prune(filters={'dangling': True})
for folder in ['./shared', './mysql', './init']:
if os.path.exists(folder):
shutil.rmtree(folder)
if args['nginx']:
if os.path.exists('./nginx/conf'):
shutil.rmtree('./nginx/conf')
if args['deploy']:
import string
print("Generating configs...")
if args['nginx']:
nginx_conf_template = string.Template(
open(os.path.join(pkgdir, 'templates/nginx_conf.template'),
'r').read())
with open('./nginx/conf/nginx.conf', 'w') as f:
f.write(nginx_conf_template.substitute(**params))
if args['haproxy_cfg']:
haproxy_conf_template = string.Template(
open(os.path.join(pkgdir, 'templates/haproxy.template'),
'r').read())
with open('./haproxy/haproxy.cfg', 'w') as f:
f.write(haproxy_conf_template.substitute(**params))
with open('./guacamole_home/guacamole.properties', 'w') as f:
yaml.dump(params['guacamole-properties'],
open('./guacamole_home/guacamole.properties', 'w'))
if 'ldap-hostname' in params['guacamole-properties']:
# Copies the guacamole-auth-ldap if ldap is configured.
shutil.copy(
os.path.join(pkgdir,
'templates/guacamole-auth-ldap-1.3.0.jar'),
os.path.join(os.getcwd(), 'guacamole_home/extensions'))
else:
# Copies the guacamole-auth-radius if the new method is used without ldap - defaults to radius.
shutil.copy(
os.path.join(pkgdir,
'templates/guacamole-auth-radius-1.3.0.jar'),
os.path.join(os.getcwd(), 'guacamole_home/extensions'))
if args['haproxy']:
docker_compose_template = string.Template(
open(
os.path.join(
pkgdir,
'templates/docker-compose.yml.haproxy.template'),
'r').read())
else:
docker_compose_template = string.Template(
open(
os.path.join(pkgdir,
'templates/docker-compose.yml.template'),
'r').read())
with open('./docker-compose.yml', 'w') as f:
f.write(docker_compose_template.substitute(**params))
mysql_init_template = string.Template(
open(os.path.join(pkgdir, 'templates/initdb.sh.template'),
'r').read())
with open('./initdb.sh', 'w') as f:
f.write(mysql_init_template.substitute(**params))
shutil.copy(os.path.join(pkgdir, 'templates/initdb.sql.script'),
'./initdb.sql.script')
print("Deploying...")
try:
docker_compose_cmd = subprocess.run(['docker-compose pull'],
shell=True)
except:
import traceback
print(traceback.format_exc())
try:
docker_compose_cmd = subprocess.run(['docker-compose up -d'],
shell=True)
# Clean-up unused images.
client.images.prune()
except:
import traceback
print(traceback.format_exc())
if args['ldap']:
# Connect to ldap
from ldap3 import Server, Connection, ALL, NTLM, ALL_ATTRIBUTES
import json
from copy import deepcopy
import sqlalchemy
import hashlib
import uuid
from datetime import datetime
server = Server(params['guacamole-properties']['ldap-hostname'],
get_info=ALL)
ldap_conn = Connection(
server=server,
user=params['guacamole-properties']['ldap-search-bind-dn'],
password=params['guacamole-properties']
['ldap-search-bind-password'],
auto_bind=True)
#domain_dn = ','.join(['DC=' + d for d in params['ldap']['ldap_domain'].split('.')])
# Connect to MySQL
print("Waiting for mysql availability...")
check_container_status('mysql', 120)
engine = sqlalchemy.create_engine('mysql+pymysql://' +
params['mysql_user'] + ':' +
params['mysql_password'] +
'@127.0.0.1:3306/guacamole_db')
with engine.begin() as sql_conn:
# Set guacadmin password
metadata = sqlalchemy.MetaData()
guacamole_entity = sqlalchemy.Table('guacamole_entity',
metadata,
autoload=True,
autoload_with=engine)
guacamole_user = sqlalchemy.Table('guacamole_user',
metadata,
autoload=True,
autoload_with=engine)
sql_insert(engine,
sql_conn,
'guacamole_entity',
name='guacadmin',
type='USER')
entity_id = sqlalchemy.select([
guacamole_entity
]).where(guacamole_entity.columns.name == 'guacadmin')
result = sql_conn.execute(entity_id)
entity_id_value = result.fetchone()[0]
password_salt = hashlib.sha256(
str(uuid.uuid1().bytes).encode('utf-8'))
password_hash = hashlib.sha256(
(params['guacadmin_password'] +
password_salt.hexdigest().upper()).encode('utf-8'))
sql_insert(engine,
sql_conn,
'guacamole_user',
entity_id=entity_id_value,
password_hash=password_hash.digest(),
password_salt=password_salt.digest(),
password_date=datetime.now())
# Create connections
with engine.begin() as sql_conn:
connections = list()
connection_ids = dict()
connection_search_filter = params['guacamole-properties'][
'ldap-user-search-filter'].replace(
'objectCategory=User', 'objectCategory=Computer')
ldap_conn.search(search_base=params['guacamole-properties']
['ldap-group-base-dn'],
search_filter=connection_search_filter,
attributes=ALL_ATTRIBUTES)
computers = json.loads(ldap_conn.response_to_json())
connection_names = dict()
for computer in computers['entries']:
if params['auto_connection_dns']:
hostname = computer['attributes']['dNSHostName']
conn_name = hostname
else:
import dns.resolver
dns.resolver.default_resolver = dns.resolver.Resolver(
configure=False)
dns.resolver.default_resolver.nameservers = [
params['auto_connection_dns_resolver']
]
hostname = dns.resolver.resolve(
computer['attributes']['dNSHostName'],
'a').response.answer[0][0].address
conn_name = computer['attributes'][
'dNSHostName'] + " - " + hostname
connection = params['auto_connections']
connection['connection']['connection_name'] = conn_name
connection['parameters']['hostname'] = hostname
connections.append(deepcopy(connection))
connection_names[conn_name] = computer
if 'manual_connections' in params.keys():
for connection in params['manual_connections']:
connections.append(deepcopy(connection))
for connection in connections:
sql_insert(engine, sql_conn, 'guacamole_connection',
**connection['connection'])
conn_name = connection['connection']['connection_name']
connection_id = \
sql_conn.execute('SELECT connection_id from guacamole_connection WHERE connection_name = "' +
conn_name + '";').fetchone()['connection_id']
for parameter_name, parameter_value in connection[
'parameters'].items():
sql_insert(engine,
sql_conn,
'guacamole_connection_parameter',
connection_id=connection_id,
parameter_name=parameter_name,
parameter_value=parameter_value)
connection_ids[connection_id] = conn_name
# Clean up undefined connections.
connections = sql_conn.execute(
'SELECT * from guacamole_connection;').fetchall()
for connection in connections:
if connection['connection_id'] not in connection_ids:
sql_conn.execute(
'DELETE from guacamole_connection WHERE connection_id = '
+ str(connection['connection_id']) + ';')
# Create user groups .
with engine.begin() as sql_conn:
group_search_filter = params['guacamole-properties'][
'ldap-user-search-filter'].replace('objectCategory=User',
'objectCategory=Group')
ldap_conn.search(search_base=params['guacamole-properties']
['ldap-group-base-dn'],
search_filter=group_search_filter,
attributes=ALL_ATTRIBUTES)
ldap_groups = json.loads(ldap_conn.response_to_json())
for group in ldap_groups['entries']:
cn = group['attributes']['cn']
dn = group['attributes']['distinguishedName']
sql_insert(engine, sql_conn, 'guacamole_entity', **{
'name': cn,
'type': 'USER_GROUP'
})
entity_id = sql_conn.execute(
'SELECT entity_id from guacamole_entity WHERE name = "'
+ cn + '";').fetchone()['entity_id']
sql_insert(engine, sql_conn, 'guacamole_user_group', **{
'entity_id': entity_id,
'disabled': 0
})
for conn_name, computer in connection_names.items():
sql_statement = 'SELECT connection_id from guacamole_connection WHERE connection_name = "' + \
conn_name + '";'
connection_id = sql_conn.execute(
sql_statement).fetchone()['connection_id']
if dn in computer['attributes']['memberOf']:
sql_insert(
engine, sql_conn,
'guacamole_connection_permission', **{
'entity_id': entity_id,
'connection_id': connection_id,
'permission': 'READ'
})
else:
sql_conn.execute(
'DELETE from guacamole_connection_permission WHERE entity_id = '
+ str(entity_id) + ' AND connection_id = ' +
str(connection_id) + ';')
with engine.begin() as sql_conn:
if 'manual_permissions' in params.keys():
for conn_name, groups in params[
'manual_permissions'].items():
sql_statement = 'SELECT connection_id from guacamole_connection WHERE connection_name = "' + \
conn_name + '";'
connection_id = sql_conn.execute(
sql_statement).fetchone()['connection_id']
entity_ids = list()
for cn in groups:
entity = sql_conn.execute(
'SELECT entity_id from guacamole_entity WHERE name = "'
+ cn + '";').fetchone()
if entity is not None:
entity_id = entity['entity_id']
entity_ids.append(str(entity_id))
else:
print('Manually specified group "' + cn +
'" does not exist.')
if len(entity_ids) > 0:
sql_conn.execute(
'DELETE from guacamole_connection_permission WHERE connection_id = '
+ str(connection_id) +
' AND entity_id NOT IN (' +
','.join(entity_ids) + ');')
for entity_id in entity_ids:
sql_insert(
engine, sql_conn,
'guacamole_connection_permission', **{
'entity_id': entity_id,
'connection_id': connection_id,
'permission': 'READ'
})
# Give guacadmin user permission to all connections and user groups.
with engine.begin() as sql_conn:
sql_statement = 'SELECT entity_id FROM guacamole_entity WHERE name = "guacadmin"'
guacadmin_entity_id = sql_conn.execute(
sql_statement).fetchone()['entity_id']
for connection_id in connection_ids.keys():
for permission in [
'READ', 'UPDATE', 'DELETE', 'ADMINISTER'
]:
sql_insert(
engine, sql_conn,
'guacamole_connection_permission', **{
'entity_id': guacadmin_entity_id,
'connection_id': connection_id,
'permission': permission
})
groups = sql_conn.execute(
'SELECT * from guacamole_user_group;').fetchall()
for group in groups:
for permission in [
'READ', 'UPDATE', 'DELETE', 'ADMINISTER'
]:
sql_insert(
engine, sql_conn,
'guacamole_user_group_permission', **{
'entity_id': guacadmin_entity_id,
'affected_user_group_id':
group['user_group_id'],
'permission': permission
})
from alembic import op
import sqlalchemy as sa
revision = 'b619f44789e4'
down_revision = '14f5651419ad'
branch_labels = None
depends_on = None
profile_helper = sa.Table(
'profile',
sa.MetaData(),
sa.Column('preferred_name', sa.Text(), nullable=False),
)
def upgrade():
connection = op.get_bind()
connection.execute(
profile_helper.delete().where(profile_helper.c.preferred_name == ''))
def downgrade():
pass
import sqlalchemy as SA
from testify import test_reporter
try:
import simplejson as json
_hush_pyflakes = [json]
del _hush_pyflakes
except ImportError:
import json
import yaml
import time
import threading
import Queue
metadata = SA.MetaData()
Tests = SA.Table(
'tests',
metadata,
SA.Column('id', SA.Integer, primary_key=True, autoincrement=True),
SA.Column('module', SA.String(255)),
SA.Column('class_name', SA.String(255)),
SA.Column('method_name', SA.String(255)),
)
SA.Index('ix_individual_test',
Tests.c.module,
Tests.c.class_name,
Tests.c.method_name,
unique=True)
import asyncio
import uuid
import datetime
from typing import List
import databases
import pydantic
import pytest
import sqlalchemy
import ormar
from ormar.exceptions import QueryDefinitionError, NoMatch, ModelError
from tests.settings import DATABASE_URL
database = databases.Database(DATABASE_URL, force_rollback=True)
metadata = sqlalchemy.MetaData()
class JsonSample(ormar.Model):
class Meta:
tablename = "jsons"
metadata = metadata
database = database
id: int = ormar.Integer(primary_key=True)
test_json = ormar.JSON(nullable=True)
class UUIDSample(ormar.Model):
class Meta:
tablename = "uuids"
def create_tables_thd(self, conn):
metadata = sa.MetaData()
metadata.bind = conn
self.buildsets = sautils.Table(
'buildsets',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('external_idstring', sa.String(256)),
sa.Column('reason', sa.String(256)),
sa.Column('sourcestampid', sa.Integer,
nullable=False), # NOTE: foreign key omitted
sa.Column('submitted_at', sa.Integer, nullable=False),
sa.Column('complete',
sa.SmallInteger,
nullable=False,
server_default=sa.DefaultClause("0")),
sa.Column('complete_at', sa.Integer),
sa.Column('results', sa.SmallInteger),
)
self.buildsets.create(bind=conn)
self.buildrequests = sautils.Table(
'buildrequests',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('buildsetid',
sa.Integer,
sa.ForeignKey("buildsets.id"),
nullable=False),
sa.Column('buildername', sa.String(length=256), nullable=False),
sa.Column('priority',
sa.Integer,
nullable=False,
server_default=sa.DefaultClause("0")),
sa.Column('claimed_at',
sa.Integer,
server_default=sa.DefaultClause("0")),
sa.Column('claimed_by_name', sa.String(length=256)),
sa.Column('claimed_by_incarnation', sa.String(length=256)),
sa.Column('complete',
sa.Integer,
server_default=sa.DefaultClause("0")),
sa.Column('results', sa.SmallInteger),
sa.Column('submitted_at', sa.Integer, nullable=False),
sa.Column('complete_at', sa.Integer),
)
self.buildrequests.create(bind=conn)
idx = sa.Index('buildrequests_buildsetid',
self.buildrequests.c.buildsetid)
idx.create()
idx = sa.Index('buildrequests_buildername',
self.buildrequests.c.buildername)
idx.create()
idx = sa.Index('buildrequests_complete', self.buildrequests.c.complete)
idx.create()
self.objects = sautils.Table(
"objects",
metadata,
sa.Column("id", sa.Integer, primary_key=True),
sa.Column('name', sa.String(128), nullable=False),
sa.Column('class_name', sa.String(128), nullable=False),
sa.UniqueConstraint('name', 'class_name', name='object_identity'),
)
self.objects.create(bind=conn)
import datetime
import sqlalchemy as sql
metadata = sql.MetaData()
summary = sql.Table('summary', metadata,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('result', sql.Boolean),
sql.Column('build_date', sql.String))
summary_item = sql.Table(
'summary_item', metadata, sql.Column('id', sql.Integer, primary_key=True),
sql.Column('repo_name', sql.String), sql.Column('exception', sql.String),
sql.Column('commit_id', sql.String), sql.Column('image_id', sql.String),
sql.Column('source_desc', sql.String), sql.Column('tag', sql.String),
sql.Column('summary_id', None, sql.ForeignKey('summary.id')))
class DbManager(object):
def __init__(self, db='/opt/stackbrew/data.db', debug=False):
self._engine = sql.create_engine('sqlite:///' + db, echo=debug)
def generate_tables(self):
metadata.create_all(self._engine)
def insert_summary(self, s):
c = self._engine.connect()
summary_id = None
with c.begin():
ins = summary.insert().values(result=not s.exit_code(),
build_date=str(
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
# License for the specific language governing permissions and limitations
# under the License.
import datetime
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql as pg
import sqlalchemy_utils as sa_utils
from dci.common import signature, utils
metadata = sa.MetaData()
JOB_STATUSES = [
'new', 'pre-run', 'running', 'post-run', 'success', 'failure', 'killed',
'error'
]
STATUSES = sa.Enum(*JOB_STATUSES, name='statuses')
FINAL_STATUSES = ['success', 'failure', 'error']
FINAL_FAILURE_STATUSES = ['failure', 'error']
FINAL_STATUSES_ENUM = sa.Enum(*FINAL_STATUSES, name='final_statuses')
RESOURCE_STATES = ['active', 'inactive', 'archived']
STATES = sa.Enum(*RESOURCE_STATES, name='states')
ISSUE_TRACKERS = ['github', 'bugzilla']
TRACKERS = sa.Enum(*ISSUE_TRACKERS, name='trackers')
Revision ID: f43499b4f914
Revises: 8820a6b34f40
Create Date: 2018-10-14 16:23:22.552013+00:00
"""
from alembic import op
import sqlalchemy as S
from sqlalchemy_utils import JSONType
# revision identifiers, used by Alembic.
revision = 'f43499b4f914'
down_revision = '8820a6b34f40'
branch_labels = None
depends_on = None
schema = S.MetaData()
project = S.Table(
'projects',
schema,
S.Column('id', S.Integer, primary_key=True, nullable=False),
S.Column('name', S.Unicode(2048), nullable=False, unique=True),
S.Column('display_name', S.Unicode(2048), nullable=False, unique=True),
S.Column('summary', S.Unicode(2048), nullable=True),
)
version = S.Table(
'versions',
schema,
S.Column('id', S.Integer, primary_key=True, nullable=False),
S.Column(
def __init__(self, url=None, engine=None, metadata=None, **options):
"""
The options are:
Required (one of the two, `engine` takes precedence):
* `url` - database URL in form of:
``backend://user:password@host:port/database``
* `sqlalchemy_options` - this backend accepts options for SQLAlchemy
in the form: ``option1=value1[&option2=value2]...``
* `engine` - SQLAlchemy engine - either this or URL should be provided
Optional:
* `schema` - default schema, where all tables are located (if not
explicitly stated otherwise)
* `fact_prefix` - used by the snowflake mapper to find fact table for a
cube, when no explicit fact table name is specified
* `dimension_prefix` - used by snowflake mapper to find dimension
tables when no explicit mapping is specified
* `fact_suffix` - used by the snowflake mapper to find fact table for a
cube, when no explicit fact table name is specified
* `dimension_suffix` - used by snowflake mapper to find dimension
tables when no explicit mapping is specified
* `dimension_schema` – schema where dimension tables are stored, if
different than common schema.
Options for denormalized views:
* `use_denormalization` - browser will use dernormalized view instead
of snowflake
* `denormalized_prefix` - if denormalization is used, then this
prefix is added for cube name to find corresponding cube view
* `denormalized_schema` - schema wehere denormalized views are
located (use this if the views are in different schema than fact
tables, otherwise default schema is going to be used)
"""
super(SQLStore, self).__init__(**options)
if not engine and not url:
raise ConfigurationError("No URL or engine specified in options, "
"provide at least one")
if engine and url:
raise ConfigurationError(
"Both engine and URL specified. Use only one.")
# Create a copy of options, because we will be popping from it
self.options = coalesce_options(options, OPTION_TYPES)
self.naming = distill_naming(self.options)
if not engine:
# Process SQLAlchemy options
sa_options = sqlalchemy_options(options)
engine = sa.create_engine(url, **sa_options)
self.logger = get_logger(name=__name__)
self.connectable = engine
self.schema = self.naming.schema
# Load metadata here. This might be too expensive operation to be
# performed on every request, therefore it is recommended to have one
# shared open store per process. SQLAlchemy will take care about
# necessary connections.
if metadata:
self.metadata = metadata
else:
self.metadata = sa.MetaData(bind=self.connectable,
schema=self.schema)
import sqlalchemy as db
from ..sql import MySQLCompatabilityTypes, get_current_timestamp
SqlEventLogStorageMetadata = db.MetaData()
SqlEventLogStorageTable = db.Table(
"event_logs",
SqlEventLogStorageMetadata,
db.Column("id", db.Integer, primary_key=True, autoincrement=True),
db.Column("run_id", db.String(255)),
db.Column("event", db.Text, nullable=False),
db.Column("dagster_event_type", db.Text),
db.Column("timestamp", db.types.TIMESTAMP),
db.Column("step_key", db.Text),
db.Column("asset_key", db.Text),
db.Column("partition", db.Text),
)
SecondaryIndexMigrationTable = db.Table(
"secondary_indexes",
SqlEventLogStorageMetadata,
db.Column("id", db.Integer, primary_key=True, autoincrement=True),
db.Column("name", MySQLCompatabilityTypes.UniqueText, unique=True),
db.Column("create_timestamp",
db.DateTime,
server_default=get_current_timestamp()),
db.Column("migration_completed", db.DateTime),
)
# The AssetKeyTable contains a `last_materialization_timestamp` column that is exclusively
def upgrade(migrate_engine):
meta = sqlalchemy.MetaData(bind=migrate_engine)
stack = sqlalchemy.Table('stack', meta, autoload=True)
sqlalchemy.Column('deleted_at', sqlalchemy.DateTime).create(stack)
def init_db(self, engine):
"""Connect to database and create tables.
Parameters
----------
engine : Engine
An engine to a SQL database.
constraints : bool, optional
If True, create SQL ForeignKey and PrimaryKey constraints.
"""
metadata = sa.MetaData(bind=engine)
self.equities = sa.Table(
'equities',
metadata,
sa.Column(
'sid',
sa.Integer,
unique=True,
nullable=False,
primary_key=True,
),
sa.Column('symbol', sa.Text),
sa.Column('company_symbol', sa.Text, index=True),
sa.Column('share_class_symbol', sa.Text),
sa.Column('fuzzy_symbol', sa.Text, index=True),
sa.Column('asset_name', sa.Text),
sa.Column('start_date', sa.Integer, default=0, nullable=False),
sa.Column('end_date', sa.Integer, nullable=False),
sa.Column('first_traded', sa.Integer, nullable=False),
sa.Column('exchange', sa.Text),
)
self.futures_exchanges = sa.Table(
'futures_exchanges',
metadata,
sa.Column(
'exchange',
sa.Text,
unique=True,
nullable=False,
primary_key=True,
),
sa.Column('timezone', sa.Text),
)
self.futures_root_symbols = sa.Table(
'futures_root_symbols',
metadata,
sa.Column(
'root_symbol',
sa.Text,
unique=True,
nullable=False,
primary_key=True,
),
sa.Column('root_symbol_id', sa.Integer),
sa.Column('sector', sa.Text),
sa.Column('description', sa.Text),
sa.Column(
'exchange',
sa.Text,
sa.ForeignKey(self.futures_exchanges.c.exchange),
),
)
self.futures_contracts = sa.Table(
'futures_contracts',
metadata,
sa.Column(
'sid',
sa.Integer,
unique=True,
nullable=False,
primary_key=True,
),
sa.Column('symbol', sa.Text, unique=True, index=True),
sa.Column('root_symbol',
sa.Text,
sa.ForeignKey(self.futures_root_symbols.c.root_symbol),
index=True),
sa.Column('asset_name', sa.Text),
sa.Column('start_date', sa.Integer, default=0, nullable=False),
sa.Column('end_date', sa.Integer, nullable=False),
sa.Column('first_traded', sa.Integer, nullable=False),
sa.Column(
'exchange',
sa.Text,
sa.ForeignKey(self.futures_exchanges.c.exchange),
),
sa.Column('notice_date', sa.Integer, nullable=False),
sa.Column('expiration_date', sa.Integer, nullable=False),
sa.Column('auto_close_date', sa.Integer, nullable=False),
sa.Column('contract_multiplier', sa.Float),
)
self.asset_router = sa.Table(
'asset_router',
metadata,
sa.Column('sid',
sa.Integer,
unique=True,
nullable=False,
primary_key=True),
sa.Column('asset_type', sa.Text),
)
# Create the SQL tables if they do not already exist.
metadata.create_all(checkfirst=True)
return metadata
Revises: ed9c6ddc5c35
Create Date: 2016-04-13 16:58:42.536431
"""
from alembic import op
import sqlalchemy
# revision identifiers, used by Alembic.
revision = '34c517bcc2dd'
down_revision = 'ed9c6ddc5c35'
branch_labels = None
depends_on = None
resource_type_helper = sqlalchemy.Table(
'resource_type', sqlalchemy.MetaData(),
sqlalchemy.Column('tablename', sqlalchemy.String(18), nullable=False))
to_rename = [
('fk_metric_archive_policy_name_archive_policy_name',
'fk_metric_ap_name_ap_name', 'archive_policy', 'name', 'metric',
'archive_policy_name', "RESTRICT"),
('fk_resource_history_resource_type_name', 'fk_rh_resource_type_name',
'resource_type', 'name', 'resource_history', 'type', "RESTRICT"),
('fk_resource_history_id_resource_id', 'fk_rh_id_resource_id', 'resource',
'id', 'resource_history', 'id', "CASCADE"),
('fk_archive_policy_rule_archive_policy_name_archive_policy_name',
'fk_apr_ap_name_ap_name', 'archive_policy', 'name', 'archive_policy_rule',
'archive_policy_name', "RESTRICT")
]
