def test_delete_extra_froms_alias(self):
a1 = table('t1', column('c1')).alias('a1')
t2 = table('t2', column('c1'))
q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
self.assert_compile(
q, "DELETE FROM a1 USING t1 AS a1, t2 WHERE a1.c1 = t2.c1")
self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")
def update_changes(self, table, changes,
insert_f=None, where_f=None, delete_f=None):
for action, obj in changes:
if action == 'new':
if insert_f is not None:
iquery = table.insert().values(
**insert_f(obj)
)
self.session.execute(iquery)
else:
self.conn.update(obj)
elif action == 'remove_by_id':
dquery = delete(table)\
.where(table.c.id==obj)
self.session.execute(dquery)
elif action == 'remove':
if delete_f is not None:
dquery = delete(table).where(
delete_f(obj)
)
self.session.execute(dquery)
elif not hasattr(obj, '_alchemy_pk') or not obj._alchemy_pk:
raise NotImplementedError
else:
raise NotImplementedError
else:
raise NotImplementedError(action)
def remove_note(id, note):
try:
owner = authenticate()
if check_owner(owner, id):
conn = engine.connect()
# define query for db request to get all nodes for workspace id
notes_query = sql.select([Note.__table__])\
.where(Note.workspace == id)
notes = conn.execute(notes_query).fetchall()
if len(notes) < 2:
return jsonify({
"status":
"ok",
"message":
"Not possible to remove last note in workspace"
})
else:
query = sql.delete(Note.__table__, Note.id == note)
result = conn.execute(query)
query = sql.delete(Connection.__table__,
Connection.origin == note)
result = conn.execute(query)
query = sql.delete(Connection.__table__,
Connection.target == note)
result = conn.execute(query)
return jsonify({"status": "ok", "message": "deleted"})
else:
raise InvalidInformation(
"You don't have access to this workspace.")
except MissingInformation as e:
return jsonify({"status": "error", "message": e.message})
def toolbar_icon_clicked(self, widget, movie):
if question(_('Are you sure you want to remove %d movies?') % self.app.total):
session = self.db.Session()
# first: remove all dependend data (associated tags, languages, ...)
query = select([movies_table.c.movie_id])
# FIXME: self.app._search_conditions contains advfilter conditions only (no other filters)
query = update_whereclause(query, self.app._search_conditions)
query = query.where(movies_table.c.loaned==False) # don't delete loaned movies
for movie_entry in session.execute(query):
# tags
query_movie_tags = delete(movie_tag_table)
query_movie_tags = query_movie_tags.where(movie_tag_table.c.movie_id==movie_entry.movie_id)
session.execute(query_movie_tags)
# languages
query_movie_lang = delete(movie_lang_table)
query_movie_lang = query_movie_lang.where(movie_lang_table.c.movie_id==movie_entry.movie_id)
session.execute(query_movie_lang)
# TODO: removing posters if no longer used by another movie?
# second: remove the movie entries
query = delete(movies_table)
# FIXME: self.app._search_conditions contains advfilter conditions only (no other filters)
query = update_whereclause(query, self.app._search_conditions)
query = query.where(movies_table.c.loaned==False) # don't delete loaned movies
session.execute(query)
session.commit()
self.app.populate_treeview()
def test_delete_extra_froms_alias(self):
a1 = table("t1", column("c1")).alias("a1")
t2 = table("t2", column("c1"))
q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
self.assert_compile(
q, "DELETE FROM a1 FROM t1 AS a1, t2 WHERE a1.c1 = t2.c1")
self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")
def test_delete_extra_froms_alias(self):
a1 = table("t1", column("c1")).alias("a1")
t2 = table("t2", column("c1"))
q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
self.assert_compile(
q, "DELETE FROM a1 FROM t1 AS a1, t2 WHERE a1.c1 = t2.c1"
)
self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")
def test_delete_extra_froms_alias(self):
a1 = table('t1', column('c1')).alias('a1')
t2 = table('t2', column('c1'))
q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
self.assert_compile(
q, "DELETE FROM a1 USING t1 AS a1, t2 WHERE a1.c1 = t2.c1"
)
self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")
def delete(self, scheme):
if hasattr(scheme, '_alchemy_pk') and scheme._alchemy_pk is not None:
query = delete(tbl.scheme)\
.where(tbl.scheme.c.id==scheme._alchemy_pk)
else:
query = delete(tbl.scheme)\
.where(tbl.scheme.c.name==scheme.name)
self.session.execute(query)
def delete_feed(self, feed_id):
"""Delete a feed and associated entries."""
delete_entries = sql.delete(self._entry_table)
delete_entries = delete_entries.where(
self._entry_table.c.feed_id == feed_id)
self._conn.execute(delete_entries)
delete_feed = sql.delete(self._feed_table)
delete_feed = delete_feed.where(self._feed_table.c.id == feed_id)
self._conn.execute(delete_feed)
def customer_del(self, customer_id):
"""Delete a customer from database, bringing along all it's machines
and services
"""
# Get customer
cust = query(model.Customer).options(model.eagerload('machines'))\
.get(int(customer_id))
if not cust:
return vaultMsg(True, "No such customer: c#%s" % customer_id)
# Get all the services that will be deleted
servs = query(model.Service).join(['machine', 'customer']) \
.filter(model.Customer.id == customer_id) \
.all()
servs_ids = [s.id for s in servs]
# Make sure no service is child of this one
childs = query(model.Service) \
.filter(model.Service.parent_service_id.in_(servs_ids))\
.all()
# Don't bother for parents/childs if we're going to delete it anyway.
remnants = list(set(childs).difference(set(servs)))
if len(remnants):
# There are still some childs left, we can't delete this one.
retval = []
for x in remnants:
retval.append({'id': x.id, 'url': x.url})
return vaultMsg(False, "Services still child of this customer's machine's services",
{'childs': retval})
# Delete all related groupciphers
d = sql.delete(model.servicegroups_table) \
.where(model.servicegroups_table.c.service_id.in_(servs_ids))
# Delete the services related to customer_id's machines
d2 = sql.delete(model.services_table) \
.where(model.services_table.c.id.in_(servs_ids))
# Delete the machines related to customer_id
mach_ids = [m.id for m in cust.machines]
d3 = sql.delete(model.machines_table) \
.where(model.machines_table.c.id.in_(mach_ids))
# Delete the customer
d4 = sql.delete(model.customers_table) \
.where(model.customers_table.c.id == customer_id)
meta.Session.execute(d)
meta.Session.execute(d2)
meta.Session.execute(d3)
meta.Session.execute(d4)
meta.Session.commit()
return vaultMsg(True,
'Deleted customer c#%s successfully' % customer_id)
def delete_item(item_id, item_type):
if item_type == 'article':
u = delete(articles_table).where(articles_table.c.articleID == item_id)
elif item_type == 'category':
u = delete(categories_table).where(
categories_table.c.categoryID == item_id)
else:
print('Invalid delete command. Return to main menu.')
result = connection.execute(u)
print(result.rowcount)
def test_delete_from_entity(self):
from sqlalchemy.sql import delete
User = self.classes.User
self.assert_compile(delete(User), "DELETE FROM users")
self.assert_compile(
delete(User).where(User.id == 5),
"DELETE FROM users WHERE users.id = :id_1",
checkparams={"id_1": 5},
)
def clean_stats(self, tags=None):
eng = self.treatment.experiment.database.engine
se = delete(StatsRecord).where(and_(
# StatsRecord.kind.in_(names),
StatsRecord.replicate_id == self.seq,
StatsRecord.treatment_id == self.treatment.seq))
eng.execute(se)
ge = delete(StatsRecord).where(and_(
# StatsRecord.kind.in_(names),
StatsRecord.replicate_id == self.seq,
StatsRecord.treatment_id == self.treatment.seq))
eng.execute(ge)
def delete_vocabulary(id, cascade=True):
"""
Delete a vocabulary, by id
:param id: vocabulary id
:param cascade: if True, delete all tags in this vocabulary first
"""
conn = Session.connection()
with conn.begin():
if cascade:
query = delete(tag_table).where(tag_table.c.vocabulary_id == id)
query.execute()
query = delete(vocabulary_table).where(vocabulary_table.c.id == id)
def delete(self):
cls = self.__class__
if self.added_id:
stmt = delete(
cls.table).where(cls.table.c.added_id == self.added_id)
elif self.id:
stmt = delete(cls.table).where(cls.table.c.id == self.id)
else:
raise RuntimeError('cannot delete instance from database.')
stmt.execute()
for index_table in cls.index_tables:
delete(index_table).where(index_table.c.id == self.id).execute()
def remove_note(id, note):
try:
owner = authenticate()
conn = engine.connect()
query = sql.delete(Note.__table__, Note.id == note)
result = conn.execute(query)
query = sql.delete(Connection.__table__, Connection.origin == note)
result = conn.execute(query)
query = sql.delete(Connection.__table__, Connection.target == note)
result = conn.execute(query)
return jsonify({"status" : "ok", "message": "deleted"})
except MissingInformation as e:
return jsonify({"status": "error", "message": e.message})
def delete_workspace(id):
try:
owner = authenticate()
conn = engine.connect()
query = sql.delete(Note.__table__, Note.workspace == id)
result = conn.execute(query)
query = sql.delete(Connection.__table__, Connection.workspace == id)
result = conn.execute(query)
query = sql.delete(Workspace.__table__, Workspace.id == id)
result = conn.execute(query)
return jsonify({"status": "ok", "message": "deleted"})
except MissingInformation as e:
return jsonify({"status": "error", "message": e.message})
def delete(self, obj_id, commit=True):
from jarr.controllers.cluster import ClusterController
feed = self.get(id=obj_id)
logger.debug('DELETE %r - Found feed', feed)
clu_ctrl = ClusterController(self.user_id)
logger.info('DELETE %r - removing back ref from cluster to article',
feed)
clu_ctrl.update(
{
'user_id':
feed.user_id,
'main_article_id__in':
self.__actrl.read(feed_id=obj_id).with_entities('id')
}, {'main_article_id': None})
def select_art(col):
return select([col]).where(and_(Cluster.id == Article.cluster_id,
Article.user_id == feed.user_id))\
.order_by(Article.date.asc()).limit(1)
logger.info('DELETE %r - removing articles', feed)
session.execute(
delete(Article).where(
and_(Article.feed_id == feed.id,
Article.user_id == feed.user_id)))
logger.info('DELETE %r - fixing cluster without main article', feed)
clu_ctrl.update({
'user_id': feed.user_id,
'main_article_id': None
}, {
'main_title':
select_art(Article.title),
'main_article_id':
select_art(Article.id),
'main_feed_title':
select([Feed.title]).where(
and_(Cluster.id == Article.cluster_id, Article.user_id
== feed.user_id, Feed.id == Article.feed_id, Feed.user_id
== feed.user_id)).order_by(Article.date.asc()).limit(1)
})
logger.info('DELETE %r - removing clusters without main article', feed)
session.execute(
delete(Cluster).where(
and_(Cluster.user_id == feed.user_id,
Cluster.main_article_id.__eq__(None))))
return super().delete(obj_id)
def copy_resources_to_database(session):
"""
Syncs the source-of-truth data from files into the database. Call this at the end of a migration.
Foreign key constraints that refer to resource tables need to be set to DEFERRABLE.
We sync as follows:
1. Lock the table to be updated fully
2. Defer all constraints
3. Truncate the table
4. Re-insert everything
Truncating and recreating guarantees the data is fully in sync.
"""
with open(resources_folder / "regions.json", "r") as f:
regions = [(region["alpha3"], region["name"])
for region in json.load(f)]
with open(resources_folder / "languages.json", "r") as f:
languages = [(language["code"], language["name"])
for language in json.load(f)]
timezone_areas_file = resources_folder / "timezone_areas.sql"
if not timezone_areas_file.exists():
if not config["DEV"]:
raise Exception(
"Missing timezone_areas.sql and not running in dev")
timezone_areas_file = resources_folder / "timezone_areas.sql-fake"
logger.info(f"Using fake timezone areas")
with open(timezone_areas_file, "r") as f:
tz_sql = f.read()
# set all constraints marked as DEFERRABLE to be checked at the end of this transaction, not immediately
session.execute(text("SET CONSTRAINTS ALL DEFERRED"))
session.execute(delete(Region))
for code, name in regions:
session.add(Region(code=code, name=name))
session.execute(delete(Language))
for code, name in languages:
session.add(Language(code=code, name=name))
session.execute(delete(TimezoneArea))
session.execute(text(tz_sql))
def machine_del(self, machine_id):
"""Delete a machine from database, bringing on all child services."""
# Get machine
machine = query(model.Machine).get(int(machine_id))
if not machine:
return vaultMsg(True, "No such machine: m#%s" % machine_id)
# Get all the services that will be deleted
servs = query(model.Service).join('machine') \
.filter(model.Machine.id == machine_id).all()
servs_ids = [s.id for s in servs]
# Make sure no service is child of this one
childs = query(model.Service) \
.filter(model.Service.parent_service_id.in_(servs_ids))\
.all()
# Don't bother for parents/childs if we're going to delete it anyway.
remnants = list(set(childs).difference(set(servs)))
if len(remnants):
# There are still some childs left, we can't delete this one.
retval = []
for x in remnants:
retval.append({'id': x.id, 'url': x.url})
return vaultMsg(False, "Services still child of this machine's services",
{'childs': retval})
# Delete all related groupciphers
d = sql.delete(model.servicegroups_table) \
.where(model.servicegroups_table.c.service_id.in_(servs_ids))
# Delete the services related to machine_id
d2 = sql.delete(model.services_table) \
.where(model.services_table.c.id.in_(servs_ids))
# Delete the machine
d3 = sql.delete(model.machines_table) \
.where(model.machines_table.c.id == machine_id)
meta.Session.execute(d)
meta.Session.execute(d2)
meta.Session.execute(d3)
meta.Session.commit()
return vaultMsg(True, 'Deleted machine m#%s successfully' % machine_id)
async def remove_manga(self, manga_id):
"""
Removes a manga with the provided id along with all its chapters.
Args:
*manga_id (positional) (int)
Returns None on success.
"""
async with self.lock:
await self.session.execute(
delete(chapter.Chapter).where(
chapter.Chapter.manga_id == manga_id))
await self.session.execute(
delete(manga.Manga).where(manga.Manga.id == manga_id))
await self.session.commit()
async def _test_execute(self, engines, binds):
test_table1 = self.test_models['db1'].test_table1
test_table2 = self.test_models['db2'].test_table2
async with Session(engines, binds) as session:
q = sql.insert(test_table1).values(id=5, title='test_title')
result = await session.execute(q)
self.assertEqual(result.lastrowid, 5)
q = sql.select(test_table1.c).where(test_table1.c.id == 5)
result = await session.execute(q)
self.assertEqual(result.rowcount, 1)
result = list(result)
self.assertEqual(result[0]['id'], 5)
self.assertEqual(result[0]['title'], 'test_title')
q = sql.update(test_table1).where(test_table1.c.id == 5).\
values(title='test_title2')
result = await session.execute(q)
self.assertEqual(result.rowcount, 1)
q = sql.select(test_table1.c).\
where(test_table1.c.id == 5)
result = await session.execute(q)
self.assertEqual(result.rowcount, 1)
result = list(result)
self.assertEqual(result[0]['id'], 5)
self.assertEqual(result[0]['title'], 'test_title2')
q = sql.delete(test_table1).where(test_table1.c.id == 5)
result = await session.execute(q)
self.assertEqual(result.rowcount, 1)
q = sql.select(test_table1.c).\
where(test_table1.c.id == 5)
result = await session.execute(q)
self.assertEqual(result.rowcount, 0)
def downgrade():
conn = op.get_bind()
# First delete all the records for each unique url except for the most
# recent
id = column('id', sa.Integer)
url = column('url', sa.String)
time_created = column('time_created', sa.DateTime)
Bookmark = table('bookmark', url)
deletable = []
for row in conn.execute(
select((url, )).select_from(Bookmark).group_by(url)):
deletable.extend([row['id'] for row in conn.execute(
select((id, )).\
select_from(Bookmark).\
order_by(time_created.desc()).\
where(url == row['url']).\
offset(1)
)])
conn.execute(delete(Bookmark).where(id.in_(deletable)))
# Add the UNIQUE constraint on the 'url' field
# SQLite needs batch_alter_table()
# http://alembic.zzzcomputing.com/en/latest/batch.html
# https://www.sqlite.org/faq.html#q11
with op.batch_alter_table(
"bookmark",
table_args=(sa.UniqueConstraint('url'), ),
) as batch_op:
batch_op.create_unique_constraint('url', ['url'])
async def unlike(self, user_id, post_id):
result = await self.post_like_repo.execute(
sasql.delete(self.post_like_repo.table).where(
sasql.and_(self.post_like_repo.table.c.user_id == user_id,
self.post_like_repo.table.c.post_id == post_id)))
return result.rowcount
async def delete_scenario_features(
self, db: AsyncSession, current_user: models.User, scenario_id: int, layer_name: str
) -> Any:
layer = scenario_layer_models[layer_name.value]
await db.execute(delete(layer).where(layer.scenario_id == scenario_id))
await db.commit()
return {"msg": "Features deleted successfully"}
def refresh_user_packages(user):
if not user.packages_retrieved:
db = Session.object_session(user)
names = query_users_packages(user.name)
if names is not None:
user.packages_retrieved = True
existing = {
p
for [p] in db.query(Package.name).filter(
Package.name.in_(names))
}
for name in names:
if name not in existing:
pkg = Package(name=name, tracked=False)
db.add(pkg)
db.flush()
packages = db.query(Package.id)\
.filter(Package.name.in_(names)).all()
entries = [{
'user_id': user.id,
'package_id': pkg.id
} for pkg in packages]
db.execute(
delete(UserPackageRelation,
UserPackageRelation.user_id == user.id))
db.execute(insert(UserPackageRelation, entries))
db.commit()
def ConfirmDeleteAccount(self, request, context):
"""
Confirm account deletion using account delete token
"""
with session_scope() as session:
res = session.execute(
select(User, AccountDeletionToken).join(
AccountDeletionToken,
AccountDeletionToken.user_id == User.id).where(
AccountDeletionToken.token == request.token).where(
AccountDeletionToken.is_valid)).one_or_none()
if not res:
context.abort(grpc.StatusCode.NOT_FOUND, errors.INVALID_TOKEN)
user, account_deletion_token = res
session.execute(
delete(AccountDeletionToken).where(
AccountDeletionToken.user_id == user.id))
undelete_days = 7
user.is_deleted = True
user.undelete_until = now() + timedelta(days=undelete_days)
user.undelete_token = urlsafe_secure_token()
send_account_deletion_successful_email(user, undelete_days)
return empty_pb2.Empty()
async def i_clear_tracking_by_analysis_module_type(
self, amt: AnalysisModuleType):
async with self.get_db() as db:
await db.execute(
delete(AnalysisRequestTracking).where(
AnalysisRequestTracking.analysis_module_type == amt.name))
await db.commit()
def upgrade(ver, session):
if ver is 0:
table_names = [
"rottentomatoes_actors",
"rottentomatoes_alternate_ids",
"rottentomatoes_directors",
"rottentomatoes_genres",
"rottentomatoes_links",
"rottentomatoes_movie_actors",
"rottentomatoes_movie_directors",
"rottentomatoes_movie_genres",
"rottentomatoes_movies",
"rottentomatoes_posters",
"rottentomatoes_releasedates",
"rottentomatoes_search_results",
]
tables = [table_schema(name, session) for name in table_names]
for table in tables:
session.execute(table.delete())
table_add_column("rottentomatoes_actors", "rt_id", String, session)
ver = 1
if ver is 1:
table = table_schema("rottentomatoes_search_results", session)
session.execute(sql.delete(table, table.c.movie_id == None))
ver = 2
return ver
def test_logout_invalid_token(db):
# Create our test user using signup
_quick_signup()
with auth_api_session() as (auth_api, metadata_interceptor):
reply = auth_api.Login(auth_pb2.LoginReq(user="******"))
assert reply.next_step == auth_pb2.LoginRes.LoginStep.NEED_PASSWORD
with auth_api_session() as (auth_api, metadata_interceptor):
auth_api.Authenticate(
auth_pb2.AuthReq(user="******",
password="******"))
reply_token = get_session_cookie_token(metadata_interceptor)
# delete all login tokens
with session_scope() as session:
session.execute(delete(LoginToken))
# log out with non-existent token should still return a valid result
with auth_api_session() as (auth_api, metadata_interceptor):
auth_api.Deauthenticate(empty_pb2.Empty(),
metadata=(("cookie",
f"couchers-sesh={reply_token}"), ))
reply_token = get_session_cookie_token(metadata_interceptor)
# make sure we set an empty cookie
assert reply_token == ""
def test_delete_extra_froms(self):
t1 = table("t1", column("c1"))
t2 = table("t2", column("c1"))
q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
self.assert_compile(
q, "DELETE FROM t1 USING t1, t2 WHERE t1.c1 = t2.c1"
)
async def _test_execute(self, engines, binds):
test_table1 = self.test_models['db1'].test_table1
test_table2 = self.test_models['db2'].test_table2
async with Session(engines, binds) as session:
q = sql.insert(test_table1).values(id=5, title='test_title')
result = await session.execute(q)
self.assertEqual(result.lastrowid, 5)
q = sql.select(test_table1.c).where(test_table1.c.id == 5)
result = await session.execute(q)
self.assertEqual(result.rowcount, 1)
result = list(result)
self.assertEqual(result[0]['id'], 5)
self.assertEqual(result[0]['title'], 'test_title')
q = sql.update(test_table1).where(test_table1.c.id == 5).\
values(title='test_title2')
result = await session.execute(q)
self.assertEqual(result.rowcount, 1)
q = sql.select(test_table1.c).\
where(test_table1.c.id == 5)
result = await session.execute(q)
self.assertEqual(result.rowcount, 1)
result = list(result)
self.assertEqual(result[0]['id'], 5)
self.assertEqual(result[0]['title'], 'test_title2')
q = sql.delete(test_table1).where(test_table1.c.id == 5)
result = await session.execute(q)
self.assertEqual(result.rowcount, 1)
q = sql.select(test_table1.c).\
where(test_table1.c.id == 5)
result = await session.execute(q)
self.assertEqual(result.rowcount, 0)
def test_delete_extra_froms(self):
t1 = table("t1", column("c1"))
t2 = table("t2", column("c1"))
q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
self.assert_compile(
q, "DELETE FROM t1 FROM t1, t2 WHERE t1.c1 = t2.c1"
)
async def unfollow(self, follower_id, following_id):
await self.user_follow_repo.execute(
sasql.delete(self.user_follow_repo.table).where(
sasql.and_(
self.user_follow_repo.table.c.follower_id == follower_id,
self.user_follow_repo.table.c.following_id ==
following_id)))
def upgrade(ver, session):
if ver is 0:
table_names = [
'rottentomatoes_actors',
'rottentomatoes_alternate_ids',
'rottentomatoes_directors',
'rottentomatoes_genres',
'rottentomatoes_links',
'rottentomatoes_movie_actors',
'rottentomatoes_movie_directors',
'rottentomatoes_movie_genres',
'rottentomatoes_movies',
'rottentomatoes_posters',
'rottentomatoes_releasedates',
'rottentomatoes_search_results',
]
tables = [table_schema(name, session) for name in table_names]
for table in tables:
session.execute(table.delete())
table_add_column('rottentomatoes_actors', 'rt_id', String, session)
ver = 1
if ver is 1:
table = table_schema('rottentomatoes_search_results', session)
session.execute(sql.delete(table, table.c.movie_id == None))
ver = 2
return ver
async def i_delete_api_key(self, name: str) -> bool:
async with self.get_db() as db:
row_count = (await db.execute(
delete(ApiKeyDbModel).where(ApiKeyDbModel.name == name)
)).rowcount
await db.commit()
return row_count == 1
def delete_data():
u = delete(cookies).where(cookies.c.cookie_name == "dark chocolate chip")
result = engine.execute(u)
print(result.rowcount)
s = select([cookies]).where(cookies.c.cookie_name == "dark chocolate chip")
result = engine.execute(s).fetchall()
print(len(result))
def clear_reset_code(self, user_id):
if self._engine is None:
raise NotImplementedError()
query = delete(reset_codes).where(reset_codes.c.username == user_id)
res = safe_execute(self._engine, query)
return res.rowcount > 0
def test_delete_extra_froms(self):
t1 = table('t1', column('c1'))
t2 = table('t2', column('c1'))
q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
self.assert_compile(
q, "DELETE FROM t1 FROM t1, t2 WHERE t1.c1 = t2.c1"
)
def test_delete_extra_froms(self):
t1 = table('t1', column('c1'))
t2 = table('t2', column('c1'))
q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
self.assert_compile(
q, "DELETE FROM t1 USING t1, t2 WHERE t1.c1 = t2.c1"
)
def filled_engine():
"""
filled_engine manages database records for each test.
データベースを利用してテストする際に利用するfixture。
テストケース実行前にレコードを保存し、実行後に保存したレコードを全て削除する。
:return:
"""
# テストのたびにデータベースに事前データを登録する。
engine = new_engine()
jst = timezone(timedelta(hours=9), 'JST')
# 端数が出ているとwhere句での検索が大変厳しいので、決め打ちの値にしておく。
now = datetime(2019, 12, 1, 11, 30, tzinfo=jst)
# prepare users
til_users = til_users_tables()
users = preset_til_users(now)
engine.execute(insert(til_users), users)
# テストを実施する。
yield engine
# テストのたびにprepare以降に作成したデータを削除する。
stmt = delete(til_users).where(column("created").__eq__(now))
engine.execute(stmt)
def clear_reset_code(self, user_id):
if self._engine is None:
raise NotImplementedError()
query = delete(reset_codes).where(reset_codes.c.username == user_id)
res = safe_execute(self._engine, query)
return res.rowcount > 0
def unalias_nick(self, alias: str) -> None:
"""Remove an alias.
:param alias: an alias with at least one other nick in its group
:raise ValueError: if there is not at least one other nick in the
group, or the ``alias`` is not known
:raise ~sqlalchemy.exc.SQLAlchemyError: if there is a database error
.. seealso::
To delete an entire group, use :meth:`forget_nick_group`.
To *add* an alias for a nick, use :meth:`alias_nick`.
"""
slug = self.make_identifier(alias).lower()
nick_id = self.get_nick_id(alias)
with self.session() as session:
count = session.scalar(
select(func.count()).select_from(Nicknames).where(
Nicknames.nick_id == nick_id))
if count <= 1:
raise ValueError('Given alias is the only entry in its group.')
session.execute(
delete(Nicknames).where(
Nicknames.slug == slug).execution_options(
synchronize_session="fetch"))
session.commit()
def delete(self):
relationship_table = self.params['relationship_table']
with self.obj.backend.transaction(implicit=True):
condition = relationship_table.c[
self.params['pk_field_name']] == self.obj.pk
self.obj.backend.connection.execute(
delete(relationship_table).where(condition))
def forget_user(self, uid=None):
if uid is None:
return False
if type(uid) is str: # Sorry little Billy '); Drop Tables *;
return False
query = delete([user_info]).where(user_info.c.uid == uid)
self._db.execute(query)
return True
def delete(self, concept):
if hasattr(concept, '_alchemy_pk') and concept._alchemy_pk is not None:
query = delete(tbl.concept)\
.where(tbl.concept.c.id==concept._alchemy_pk)
else:
raise NotImplementedError
self.session.execute(query)
def delete(self, id=None):
id = request.params.getone('id')
post_q = meta.Session.query(model.Post)
post = post_q.filter_by(id=id).first()
if post is None:
abort(404)
# delete tags and comments associated with post
meta.Session.execute(delete(model.poststags_table, model.poststags_table.c.post_id==post.id))
meta.Session.execute(delete(model.comments_table, model.comments_table.c.post_id==post.id))
meta.Session.delete(post)
meta.Session.commit()
if request.is_xhr:
response.content_type = 'application/json'
return "{'success':true,'msg':'The post has been deleted'}"
else:
session['flash'] = 'Post successfully deleted.'
session.save()
return redirect_to(controller='post', action='list')
async def test_get_engine(self, engines, binds):
test_table1 = self.test_models['db1'].test_table1
test_table2 = self.test_models['db2'].test_table2
async with Session(engines, binds) as session:
i1 = sql.insert(test_table1)
i2 = sql.insert(test_table2)
u1 = sql.update(test_table1)
u2 = sql.update(test_table2)
d1 = sql.delete(test_table1)
d2 = sql.delete(test_table2)
s1 = sql.insert(test_table1)
s2 = sql.insert(test_table2)
for q1, q2 in [(i1, i2), (u1, u2), (d1, d2), (s1, s2)]:
engine1 = session.get_engine(q1)
engine2 = session.get_engine(q2)
self.assertEqual(engine1, engines['db1'])
self.assertEqual(engine2, engines['db2'])
with self.assertRaises(exc.OrmError):
session.get_engine('error query')
def _delete_unused_balances(self):
abandoned = self.db.execute(select([self.model.Balance.uid], self.model.UserBalance.c.balance_uid==null(),
from_obj=self.model.balances_table.outerjoin(self.model.users_balances_table))).fetchall()
where = []
for uid in abandoned:
where.append(self.model.Balance.uid==uid[0])
if len(where) > 0:
self.db.execute(delete(self.model.balances_table, or_(*where)))
def remove(self,obj):
"""
Remove an object from the relation
"""
with self.obj.backend.transaction(implicit = True):
relationship_table = self.params['relationship_table']
condition = and_(relationship_table.c['pk_%s' % self.params['collection']] == obj.pk,
relationship_table.c['pk_%s' % self.collection] == self.obj.pk)
self.obj.backend.connection.execute(delete(relationship_table).where(condition))
self._queryset = None
def _delete_unused_changes_tags(self, balance_uid):
abandoned_tags = self.db.execute(select([self.model.BalanceTag.uid],
and_(self.model.BalanceTag.balance_uid==balance_uid, self.model.changes_tags_table.c.tag_uid==null()),
from_obj=self.model.balance_tags_table.outerjoin(self.model.changes_tags_table))).fetchall()
where = []
for tag in abandoned_tags:
where.append(self.model.BalanceTag.uid==tag[0])
if len(where) > 0:
self.db.execute(delete(self.model.balance_tags_table, or_(*where)))
def forget_site_association(self, site_id, request, uid=None):
if uid is None:
uid = self.uid
if uid is None:
return False
if re.search("[^\w]", uid):
return False
site_loc = self._site_loc(request)
where = _and(site_id_to_uid.c.site_id == site_id, site_id_to_uid.c.site_loc == site_loc)
query = delete(site_id_to_uid).where(where)
return True
def toolbar_icon_clicked(self, widget, movie):
if question(_('Are you sure you want to remove %d movies?') % self.app.total):
session = self.db.Session()
# first: remove all dependend data (associated tags, languages, ...)
query = select([movies_table.c.movie_id])
# add conditions from simple filter
change_filter_update_whereclause(self.app, query)
# add conditions from adv filter
query = update_whereclause(query, self.app._search_conditions)
query = query.where(movies_table.c.loaned==False) # don't delete loaned movies
log.debug(query)
movie_ids = []
for movie_entry in session.execute(query):
movie_ids.append(movie_entry.movie_id)
# tags
query_movie_tags = delete(movie_tag_table)
query_movie_tags = query_movie_tags.where(movie_tag_table.c.movie_id==movie_entry.movie_id)
log.debug(query_movie_tags)
session.execute(query_movie_tags)
# languages
query_movie_lang = delete(movie_lang_table)
query_movie_lang = query_movie_lang.where(movie_lang_table.c.movie_id==movie_entry.movie_id)
log.debug(query_movie_lang)
session.execute(query_movie_lang)
# TODO: removing posters if no longer used by another movie?
# second: remove the movie entries
if len(movie_ids):
query = delete(movies_table)
# use the collected movie ids because other conditions are not true anymore
# (f.e. tags are already deleted)
query = query.where(movies_table.c.movie_id.in_(movie_ids))
log.debug(query)
session.execute(query)
session.commit()
self.app.populate_treeview()
def delete(self, id=None):
# @todo: delete confirmation
if id is None:
abort(404)
tag_q = meta.Session.query(model.Tag)
tag = tag_q.filter_by(id=id).first()
if tag is None:
abort(404)
meta.Session.execute(delete(model.poststags_table, model.poststags_table.c.tag_id==tag.id))
meta.Session.delete(tag)
meta.Session.commit()
session['flash'] = 'Tag successfully deleted.'
session.save()
return redirect_to(controller='tag', action='cloud')
def delete(self, id=None):
# @todo: delete confirmation
if id is None:
abort(404)
post_q = meta.Session.query(model.Post)
post = post_q.filter_by(id=id).first()
if post is None:
abort(404)
meta.Session.execute(delete(model.poststags_table, model.poststags_table.c.post_id == post.id))
meta.Session.delete(post)
meta.Session.commit()
session["flash"] = "Post successfully deleted."
session.save()
return redirect_to(controller="post", action="list")
def service_del(self, service_id):
"""Delete a service, making sure no other child remains attached."""
# Integerize
service_id = int(service_id)
# Get service
serv = query(model.Service).get(int(service_id))
if not serv:
return vaultMsg(True, "No such service: s#%s" % service_id)
# Make sure no service is child of this one
childs = query(model.Service).filter_by(parent_service_id=service_id).all()
if len(childs):
# There are still some childs left, we can't delete this one.
retval = []
for x in childs:
retval.append({'id': x.id, 'url': x.url})
return vaultMsg(False, 'Services still child of this service',
{'childs': retval})
# TODO: verify permissions:
# if either user is global admin, or is admin of all groups in which this
# service is in, otherwise, disallow.
# Delete all related user-ciphers
d = sql.delete(model.servicegroups_table) \
.where(model.servicegroups_table.c.service_id == service_id)
# Delete the service
d2 = sql.delete(services_table) \
.where(services_table.c.id == service_id)
meta.Session.execute(d)
meta.Session.execute(d2)
meta.Session.commit()
return vaultMsg(True, 'Deleted service s#%s successfully' % service_id)
def _set_reset_code(self, user_id):
code = self.rc._generate_reset_code()
expiration = datetime.datetime.now() + datetime.timedelta(hours=6)
query = delete(reset_codes).where(reset_codes.c.username == user_id)
self._engine.execute(query)
query = insert(reset_codes).values(reset=code, expiration=expiration, username=user_id)
res = safe_execute(self._engine, query)
if res.rowcount != 1:
logger.debug("Unable to add a new reset code in the" " reset_code table")
return None # XXX see if appropriate
return code
def _set_reset_code(self, user_id):
code, expiration = generate_reset_code()
query = delete(reset_codes).where(reset_codes.c.username == user_id)
self._engine.execute(query)
query = insert(reset_codes).values(reset=code,
expiration=expiration,
username=user_id)
res = safe_execute(self._engine, query)
if res.rowcount != 1:
logger.debug('Unable to add a new reset code in the'
' reset_code table')
return None # XXX see if appropriate
return code
def checkResponse(response, solution):
"""Compares a given solution hash with the response provided"""
valid = False
digest = hmac.new(KEY, response.upper()).hexdigest()
if hmac.compare_digest(digest, solution.encode('ascii','ignore')):
conn = _engine.connect()
result = conn.execute(
select([_captcha]).where(_captcha.c.hmac == digest)
)
row = result.first()
if row is not None:
valid = True
conn.execute(
delete(_captcha).where(_captcha.c.hmac==row['hmac'])
)
conn.close()
return valid
def delete(self, id=None):
id = request.params.getone('id')
tag_q = meta.Session.query(model.Tag)
tag = tag_q.filter_by(id=id).first()
if tag is None:
abort(404)
# delete post/tag associations
meta.Session.execute(delete(model.poststags_table, model.poststags_table.c.tag_id==tag.id))
meta.Session.delete(tag)
meta.Session.commit()
if request.is_xhr:
response.content_type = 'application/json'
return "{'success':true,'msg':'The tag has been deleted'}"
else:
session['flash'] = 'Tag successfully deleted.'
session.save()
return redirect_to(controller='page', action='list')
