Exemplo n.º 1
0
 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")
Exemplo n.º 2
0
    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)
Exemplo n.º 3
0
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})
Exemplo n.º 4
0
    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()
Exemplo n.º 5
0
 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")
Exemplo n.º 6
0
 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")
Exemplo n.º 7
0
 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")
Exemplo n.º 8
0
    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)
Exemplo n.º 9
0
 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)
Exemplo n.º 10
0
    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)
Exemplo n.º 11
0
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)
Exemplo n.º 12
0
    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},
        )
Exemplo n.º 13
0
    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)
Exemplo n.º 14
0
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)
Exemplo n.º 15
0
    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()
Exemplo n.º 16
0
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})
Exemplo n.º 17
0
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})
Exemplo n.º 18
0
    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)
Exemplo n.º 19
0
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))
Exemplo n.º 20
0
    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)
Exemplo n.º 21
0
 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()
Exemplo n.º 22
0
    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)
Exemplo n.º 23
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'])
Exemplo n.º 24
0
    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
Exemplo n.º 25
0
 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"}
Exemplo n.º 26
0
 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()
Exemplo n.º 27
0
    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()
Exemplo n.º 28
0
 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()
Exemplo n.º 29
0
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
Exemplo n.º 30
0
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 == ""
Exemplo n.º 31
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 USING t1, t2 WHERE t1.c1 = t2.c1"
     )
Exemplo n.º 32
0
    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)
Exemplo n.º 33
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"
     )
Exemplo n.º 34
0
 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)))
Exemplo n.º 35
0
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
Exemplo n.º 36
0
 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
Exemplo n.º 37
0
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))
Exemplo n.º 38
0
    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
Exemplo n.º 39
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"
     )
Exemplo n.º 40
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 USING t1, t2 WHERE t1.c1 = t2.c1"
     )
Exemplo n.º 41
0
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
Exemplo n.º 43
0
Arquivo: db.py Projeto: dgw/sopel
    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()
Exemplo n.º 44
0
 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))
Exemplo n.º 45
0
 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
Exemplo n.º 46
0
    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)
Exemplo n.º 47
0
 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')
Exemplo n.º 48
0
 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')
Exemplo n.º 49
0
    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)))
Exemplo n.º 50
0
 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
Exemplo n.º 51
0
    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)))
Exemplo n.º 52
0
 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
Exemplo n.º 53
0
    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()
Exemplo n.º 54
0
 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')
Exemplo n.º 55
0
 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")
Exemplo n.º 56
0
    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)
Exemplo n.º 57
0
    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
Exemplo n.º 58
0
    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
Exemplo n.º 60
0
 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')