def upgrade():
op.add_column(
'dbs',
sa.Column(
'password',
EncryptedType(sa.String(1024)),
nullable=True))
class RemoteToken(db.Model):
"""Storage for the access tokens for linked accounts."""
__tablename__ = 'oauthclient_remotetoken'
#
# Fields
#
id_remote_account = db.Column(
db.Integer,
db.ForeignKey(RemoteAccount.id,
name='fk_oauthclient_remote_token_remote_account'),
nullable=False,
primary_key=True)
"""Foreign key to account."""
token_type = db.Column(db.String(40),
default='',
nullable=False,
primary_key=True)
"""Type of token."""
access_token = db.Column(EncryptedType(type_in=db.Text, key=_secret_key),
nullable=False)
"""Access token to remote application."""
secret = db.Column(db.Text(), default='', nullable=False)
"""Used only by OAuth 1."""
#
# Relationships properties
#
remote_account = db.relationship(RemoteAccount,
backref=backref(
'remote_tokens',
cascade='all, delete-orphan'))
"""SQLAlchemy relationship to RemoteAccount objects."""
def __repr__(self):
"""String representation for model."""
return ('Remote Token <token_type={0.token_type} '
'access_token={0.access_token}>'.format(self))
def token(self):
"""Get token as expected by Flask-OAuthlib."""
return (self.access_token, self.secret)
def update_token(self, token, secret):
"""Update token with new values.
:param token: The token value.
:param secret: The secret key.
"""
if self.access_token != token or self.secret != secret:
with db.session.begin_nested():
self.access_token = token
self.secret = secret
db.session.add(self)
@classmethod
def get(cls, user_id, client_id, token_type='', access_token=None):
"""Get RemoteToken for user.
:param user_id: The user id.
:param client_id: The client id.
:param token_type: The token type. (Default: ``''``)
:param access_token: If set, will filter also by access token.
(Default: ``None``)
:returns: A :class:`invenio_oauthclient.models.RemoteToken` instance.
"""
args = [
RemoteAccount.id == RemoteToken.id_remote_account,
RemoteAccount.user_id == user_id,
RemoteAccount.client_id == client_id,
RemoteToken.token_type == token_type,
]
if access_token:
args.append(RemoteToken.access_token == access_token)
return cls.query.options(
db.joinedload('remote_account')).filter(*args).first()
@classmethod
def get_by_token(cls, client_id, access_token, token_type=''):
"""Get RemoteAccount object for token.
:param client_id: The client id.
:param access_token: The access token.
:param token_type: The token type. (Default: ``''``)
:returns: A :class:`invenio_oauthclient.models.RemoteToken` instance.
"""
return cls.query.options(db.joinedload('remote_account')).filter(
RemoteAccount.id == RemoteToken.id_remote_account,
RemoteAccount.client_id == client_id,
RemoteToken.token_type == token_type,
RemoteToken.access_token == access_token,
).first()
@classmethod
def create(cls,
user_id,
client_id,
token,
secret,
token_type='',
extra_data=None):
"""Create a new access token.
.. note:: Creates RemoteAccount as well if it does not exists.
:param user_id: The user id.
:param client_id: The client id.
:param token: The token.
:param secret: The secret key.
:param token_type: The token type. (Default: ``''``)
:param extra_data: Extra data to set in the remote account if the
remote account doesn't exists. (Default: ``None``)
:returns: A :class:`invenio_oauthclient.models.RemoteToken` instance.
"""
account = RemoteAccount.get(user_id, client_id)
with db.session.begin_nested():
if account is None:
account = RemoteAccount(
user_id=user_id,
client_id=client_id,
extra_data=extra_data or dict(),
)
db.session.add(account)
token = cls(
token_type=token_type,
remote_account=account,
access_token=token,
secret=secret,
)
db.session.add(token)
return token
class Demo(db.Model):
__tablename__ = 'demo'
pk = db.Column(sa.Integer, primary_key=True)
et = db.Column(
EncryptedType(type_in=db.Unicode, key=_secret_key), nullable=False
)
class Token(db.Model):
"""A bearer token is the final token that can be used by the client."""
__tablename__ = 'oauth2server_token'
__table_args__ = (
Index('ix_oauth2server_token_access_token',
'access_token',
unique=True,
mysql_length=255),
Index('ix_oauth2server_token_refresh_token',
'refresh_token',
unique=True,
mysql_length=255),
)
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
"""Object ID."""
client_id = db.Column(
db.String(255),
db.ForeignKey(Client.client_id),
nullable=False,
)
"""Foreign key to client application."""
client = db.relationship('Client',
backref=db.backref('oauth2tokens',
cascade="all, delete-orphan"))
"""SQLAlchemy relationship to client application."""
user_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=True)
"""Foreign key to user."""
user = db.relationship(User,
backref=db.backref(
"oauth2tokens",
cascade="all, delete-orphan",
))
"""SQLAlchemy relationship to user."""
token_type = db.Column(db.String(255), default='bearer')
"""Token type - only bearer is supported at the moment."""
access_token = db.Column(
EncryptedType(
type_in=db.String(255),
key=secret_key,
), )
refresh_token = db.Column(
EncryptedType(
type_in=db.String(255),
key=secret_key,
engine=NoneAesEngine,
),
nullable=True,
)
expires = db.Column(db.DateTime, nullable=True)
_scopes = db.Column(db.Text)
is_personal = db.Column(db.Boolean, default=False)
"""Personal accesss token."""
is_internal = db.Column(db.Boolean, default=False)
"""Determines if token is an internally generated token."""
@property
def scopes(self):
"""Return all scopes."""
if self._scopes:
return self._scopes.split()
return []
@scopes.setter
def scopes(self, scopes):
"""Set scopes."""
validate_scopes(scopes)
self._scopes = " ".join(set(scopes)) if scopes else ""
def get_visible_scopes(self):
"""Get list of non-internal scopes for token."""
return [
k for k, s in current_oauth2server.scope_choices()
if k in self.scopes
]
@classmethod
def create_personal(cls, name, user_id, scopes=None, is_internal=False):
"""Create a personal access token.
A token that is bound to a specific user and which doesn't expire, i.e.
similar to the concept of an API key.
"""
with db.session.begin_nested():
scopes = " ".join(scopes) if scopes else ""
c = Client(name=name,
user_id=user_id,
is_internal=True,
is_confidential=False,
_default_scopes=scopes)
c.gen_salt()
t = Token(
client_id=c.client_id,
user_id=user_id,
access_token=gen_salt(
current_app.config.get(
'OAUTH2SERVER_TOKEN_PERSONAL_SALT_LEN')),
expires=None,
_scopes=scopes,
is_personal=True,
is_internal=is_internal,
)
db.session.add(c)
db.session.add(t)
return t
class RemoteToken(db.Model):
"""Storage for the access tokens for linked accounts."""
__tablename__ = 'oauthclient_remotetoken'
#
# Fields
#
id_remote_account = db.Column(db.Integer,
db.ForeignKey(RemoteAccount.id),
nullable=False,
primary_key=True)
"""Foreign key to account."""
token_type = db.Column(db.String(40),
default='',
nullable=False,
primary_key=True)
"""Type of token."""
access_token = db.Column(EncryptedType(type_in=db.Text, key=_secret_key),
nullable=False)
"""Access token to remote application."""
secret = db.Column(db.Text(), default='', nullable=False)
"""Used only by OAuth 1."""
def __repr__(self):
"""String representation for model."""
return ('Remote Token <token_type={0.token_type} '
'access_token={0.access_token}'.format(self))
def token(self):
"""Get token as expected by Flask-OAuthlib."""
return (self.access_token, self.secret)
def update_token(self, token, secret):
"""Update token with new values."""
if self.access_token != token or self.secret != secret:
with db.session.begin_nested():
self.access_token = token
self.secret = secret
db.session.add(self)
@classmethod
def get(cls, user_id, client_id, token_type='', access_token=None):
"""Get RemoteToken for user."""
args = [
RemoteAccount.id == RemoteToken.id_remote_account,
RemoteAccount.user_id == user_id,
RemoteAccount.client_id == client_id,
RemoteToken.token_type == token_type,
]
if access_token:
args.append(RemoteToken.access_token == access_token)
return cls.query.options(
db.joinedload('remote_account')).filter(*args).first()
@classmethod
def get_by_token(cls, client_id, access_token, token_type=''):
"""Get RemoteAccount object for token."""
return cls.query.options(db.joinedload('remote_account')).filter(
RemoteAccount.id == RemoteToken.id_remote_account,
RemoteAccount.client_id == client_id,
RemoteToken.token_type == token_type,
RemoteToken.access_token == access_token,
).first()
@classmethod
def create(cls,
user_id,
client_id,
token,
secret,
token_type='',
extra_data=None):
"""Create a new access token.
Creates RemoteAccount as well if it does not exists.
"""
account = RemoteAccount.get(user_id, client_id)
with db.session.begin_nested():
if account is None:
account = RemoteAccount(
user_id=user_id,
client_id=client_id,
extra_data=extra_data or dict(),
)
db.session.add(account)
token = cls(
token_type=token_type,
remote_account=account,
access_token=token,
secret=secret,
)
db.session.add(token)
return token
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('daily_number',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('obj_type', sa.String(length=32), nullable=False),
sa.Column('count', sa.Integer(), nullable=False),
sa.Column('dt', sa.Date(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('dashboards',
sa.Column('created_on', sa.DateTime(), nullable=True),
sa.Column('changed_on', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('dashboard_title', sa.String(length=500), nullable=True),
sa.Column('position_json', sa.Text(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('department', sa.Text(), nullable=True),
sa.Column('css', sa.Text(), nullable=True),
sa.Column('online', sa.Boolean(), nullable=True),
sa.Column('json_metadata', sa.Text(), nullable=True),
sa.Column('slug', sa.String(length=255), nullable=True),
sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('slug')
)
op.create_table('dbs',
sa.Column('created_on', sa.DateTime(), nullable=True),
sa.Column('changed_on', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('database_name', sa.String(length=250), nullable=True),
sa.Column('sqlalchemy_uri', sa.String(length=1024), nullable=True),
sa.Column('password', EncryptedType(), nullable=True),
sa.Column('cache_timeout', sa.Integer(), nullable=True),
sa.Column('select_as_create_table_as', sa.Boolean(), nullable=True),
sa.Column('expose_in_sqllab', sa.Boolean(), nullable=True),
sa.Column('allow_run_sync', sa.Boolean(), nullable=True),
sa.Column('allow_run_async', sa.Boolean(), nullable=True),
sa.Column('allow_ctas', sa.Boolean(), nullable=True),
sa.Column('allow_dml', sa.Boolean(), nullable=True),
sa.Column('force_ctas_schema', sa.String(length=250), nullable=True),
sa.Column('extra', sa.Text(), nullable=True),
sa.Column('perm', sa.String(length=1000), nullable=True),
sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('database_name')
)
op.create_table('favstar',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('class_name', sa.String(length=50), nullable=True),
sa.Column('obj_id', sa.Integer(), nullable=True),
sa.Column('dttm', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('logs',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('action', sa.String(length=512), nullable=True),
sa.Column('action_type', sa.String(length=200), nullable=True),
sa.Column('obj_type', sa.String(length=50), nullable=True),
sa.Column('obj_id', sa.Integer(), nullable=True),
sa.Column('user_id', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('json', sa.Text(), nullable=True),
sa.Column('dttm', sa.DateTime(), nullable=True),
sa.Column('dt', sa.Date(), nullable=True),
sa.Column('duration_ms', sa.Integer(), nullable=True),
sa.Column('referrer', sa.String(length=1024), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('slices',
sa.Column('created_on', sa.DateTime(), nullable=True),
sa.Column('changed_on', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('slice_name', sa.String(length=250), nullable=True),
sa.Column('online', sa.Boolean(), nullable=True),
sa.Column('datasource_id', sa.Integer(), nullable=True),
sa.Column('datasource_type', sa.String(length=200), nullable=True),
sa.Column('datasource_name', sa.String(length=2000), nullable=True),
sa.Column('database_id', sa.Integer(), nullable=True),
sa.Column('full_table_name', sa.String(length=2000), nullable=True),
sa.Column('viz_type', sa.String(length=250), nullable=True),
sa.Column('params', sa.Text(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('department', sa.Text(), nullable=True),
sa.Column('cache_timeout', sa.Integer(), nullable=True),
sa.Column('perm', sa.String(length=1000), nullable=True),
sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('dashboard_slices',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('dashboard_id', sa.Integer(), nullable=True),
sa.Column('slice_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['dashboard_id'], ['dashboards.id'], ),
sa.ForeignKeyConstraint(['slice_id'], ['slices.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('dashboard_user',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('dashboard_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['dashboard_id'], ['dashboards.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('database_account',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('database_id', sa.Integer(), nullable=True),
sa.Column('username', sa.String(length=255), nullable=True),
sa.Column('password', EncryptedType(), nullable=True),
sa.ForeignKeyConstraint(['database_id'], ['dbs.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('query',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('client_id', sa.String(length=11), nullable=False),
sa.Column('database_id', sa.Integer(), nullable=False),
sa.Column('tmp_table_name', sa.String(length=256), nullable=True),
sa.Column('user_id', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('status', sa.String(length=16), nullable=True),
sa.Column('tab_name', sa.String(length=256), nullable=True),
sa.Column('sql_editor_id', sa.String(length=256), nullable=True),
sa.Column('schema', sa.String(length=256), nullable=True),
sa.Column('sql', sa.Text(), nullable=True),
sa.Column('select_sql', sa.Text(), nullable=True),
sa.Column('executed_sql', sa.Text(), nullable=True),
sa.Column('limit', sa.Integer(), nullable=True),
sa.Column('limit_used', sa.Boolean(), nullable=True),
sa.Column('limit_reached', sa.Boolean(), nullable=True),
sa.Column('select_as_cta', sa.Boolean(), nullable=True),
sa.Column('select_as_cta_used', sa.Boolean(), nullable=True),
sa.Column('progress', sa.Integer(), nullable=True),
sa.Column('rows', sa.Integer(), nullable=True),
sa.Column('error_message', sa.Text(), nullable=True),
sa.Column('results_key', sa.String(length=64), nullable=True),
sa.Column('start_time', sa.Numeric(precision=3), nullable=True),
sa.Column('end_time', sa.Numeric(precision=3), nullable=True),
sa.Column('changed_on', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['database_id'], ['dbs.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('client_id')
)
op.create_index('ti_user_id_changed_on', 'query', ['user_id', 'changed_on'], unique=False)
op.create_table('slice_user',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('slice_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['slice_id'], ['slices.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('tables',
sa.Column('created_on', sa.DateTime(), nullable=True),
sa.Column('changed_on', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('table_name', sa.String(length=250), nullable=True),
sa.Column('main_dttm_col', sa.String(length=250), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('default_endpoint', sa.Text(), nullable=True),
sa.Column('database_id', sa.Integer(), nullable=False),
sa.Column('is_featured', sa.Boolean(), nullable=True),
sa.Column('filter_select_enabled', sa.Boolean(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('offset', sa.Integer(), nullable=True),
sa.Column('cache_timeout', sa.Integer(), nullable=True),
sa.Column('schema', sa.String(length=255), nullable=True),
sa.Column('sql', sa.Text(), nullable=True),
sa.Column('params', sa.Text(), nullable=True),
sa.Column('perm', sa.String(length=1000), nullable=True),
sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.ForeignKeyConstraint(['database_id'], ['dbs.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('database_id', 'schema', 'table_name', name='_customer_location_uc')
)
op.create_table('sql_metrics',
sa.Column('created_on', sa.DateTime(), nullable=True),
sa.Column('changed_on', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('metric_name', sa.String(length=512), nullable=True),
sa.Column('verbose_name', sa.String(length=1024), nullable=True),
sa.Column('metric_type', sa.String(length=32), nullable=True),
sa.Column('table_id', sa.Integer(), nullable=True),
sa.Column('expression', sa.Text(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('is_restricted', sa.Boolean(), nullable=True),
sa.Column('d3format', sa.String(length=128), nullable=True),
sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.ForeignKeyConstraint(['table_id'], ['tables.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('table_columns',
sa.Column('created_on', sa.DateTime(), nullable=True),
sa.Column('changed_on', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('table_id', sa.Integer(), nullable=True),
sa.Column('column_name', sa.String(length=255), nullable=True),
sa.Column('verbose_name', sa.String(length=1024), nullable=True),
sa.Column('is_dttm', sa.Boolean(), nullable=True),
sa.Column('is_active', sa.Boolean(), nullable=True),
sa.Column('type', sa.String(length=32), nullable=True),
sa.Column('groupby', sa.Boolean(), nullable=True),
sa.Column('count_distinct', sa.Boolean(), nullable=True),
sa.Column('sum', sa.Boolean(), nullable=True),
sa.Column('avg', sa.Boolean(), nullable=True),
sa.Column('max', sa.Boolean(), nullable=True),
sa.Column('min', sa.Boolean(), nullable=True),
sa.Column('filterable', sa.Boolean(), nullable=True),
sa.Column('expression', sa.Text(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('python_date_format', sa.String(length=255), nullable=True),
sa.Column('database_expression', sa.String(length=255), nullable=True),
sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
sa.ForeignKeyConstraint(['table_id'], ['tables.id'], ),
sa.PrimaryKeyConstraint('id')
)