SQLAlchemy 声明:定义触发器和索引(Postgres 9) [英] SQLAlchemy declarative: defining triggers and indexes (Postgres 9)

问题描述

在表的 SQLAlchemy 类中有没有办法为该表定义/创建触发器和索引?

Is there a way in the SQLAlchemy class of a table to define/create triggers and indexes for that table?

例如，如果我有一个像...这样的基本表

For instance if i had a basic table like ...

class Customer(DeclarativeBase):
    __tablename__ = 'customers'
    customer_id = Column(Integer, primary_key=True,autoincrement=True)
    customer_code = Column(Unicode(15),unique=True)
    customer_name = Column(Unicode(100))
    search_vector = Column(tsvector) ## *Not sure how do this yet either in sqlalchemy*.

我现在想创建一个触发器来更新search_vector"

I now want to create a trigger to update "search_vector"

CREATE TRIGGER customers_search_vector_update BEFORE INSERT OR UPDATE
ON customers
FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(search_vector,'pg_catalog.english',customer_code,customer_name);

然后我想将该字段也添加为索引...

Then I wanted to add that field also as an index ...

create index customers_search_vector_indx ON customers USING gin(search_vector);

现在，在我从我的应用程序进行任何类型的数据库重新生成之后，我必须为 tsvector 列添加列、触发器定义，然后是来自 psql 的索引语句.不是世界末日，但很容易忘记一步.我全心全意自动化，所以如果我能在应用设置过程中实现这一切，那就是奖励！

Right now after i do any kind of database regeneration from my app i have to do the add column for the tsvector column, the trigger definition, and then the index statement from psql. Not the end of the world but its easy to forget a step. I am all about automation so if I can get this all to happen during the apps setup then bonus!

推荐答案

索引 可以直接创建.对于带有 index=True 参数的单列，如下所示:

Indicies are straight-forward to create. For single-column with index=True parameter like below:

customer_code = Column(Unicode(15),unique=True,index=True)

但如果您想对名称和选项进行更多控制，请使用显式 Index() 构造:

But if you want more control over the name and options, use the explicit Index() construct:

Index('customers_search_vector_indx', Customer.__table__.c.search_vector, postgresql_using='gin')

触发器 也可以创建，但那些仍然需要基于 SQL 并与 DDL 事件挂钩.有关详细信息，请参阅自定义 DDL，但代码可能类似于:>

Triggers can be created as well, but those need to still be SQL-based and hooked to the DDL events. See Customizing DDL for more info, but the code might look similar to this:

from sqlalchemy import event, DDL
trig_ddl = DDL("""
    CREATE TRIGGER customers_search_vector_update BEFORE INSERT OR UPDATE
    ON customers
    FOR EACH ROW EXECUTE PROCEDURE
    tsvector_update_trigger(search_vector,'pg_catalog.english',customer_code,customer_name);
""")
tbl = Customer.__table__
event.listen(tbl, 'after_create', trig_ddl.execute_if(dialect='postgresql'))

旁注:我不知道如何配置 tsvector 数据类型:值得一个单独的问题.

Sidenote: I do not know how to configure tsvector datatype: deserves a separate question.

这篇关于SQLAlchemy 声明:定义触发器和索引(Postgres 9)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！