пятница, 21 апреля 2017 г., 17:07:34 UTC+3 пользователь Mike Bayer написал: > > > > On 04/21/2017 09:16 AM, Антонио Антуан wrote: > > Helllo. > > I have a model, with specified __tablename__ = 'base_table'. > > In postgresql, the table has trigger, which executes before each insert: > > it creates partition for current month (if it not exist yet), specifies > > "INHERITS (base_table)" for new partition and insert data into it. > > > > Is there any way to autodetect table inheritance and generate migration > > script for it? > > > What's the actual migration you want to generate? E.g. start with A, > change to B....are you starting with Table(), and then adding > "postgresql_inherits" to it after the fact? it's not clear. > > if you can illustrate what this migration would be I'd have some idea > what you are actually trying to do. The "trigger" you refer to seems > to be something that emits CREATE TABLE upon INSERT, which would be > outside the realm of Alembic. >
For example, here is my model: import time from sqlalchemy import Column, Integer, Unicode, event, DDL from myproject import Base class Foo(Base): __tablename__ = 'foos' id = Column(Integer, primary_key=True, autoincrement=True) ts_spawn = Column(Integer, nullable=False) name = Column(Unicode, nullable=False) data_id = Column(Integer, index=True) def __init__(self): self.ts_spawn = int(time.time()) event.listen(Foo.__table__, 'after_create', DDL(""" CREATE OR REPLACE FUNCTION foos_insert_trigger() RETURNS TRIGGER AS $BODY$ DECLARE table_master VARCHAR(255) := 'foos'; table_part VARCHAR(255) := ''; ts_spawn_date DATE := to_timestamp(NEW.ts_spawn); ts_start INT := date_part('epoch', date_trunc('day', ts_spawn_date))::INT; ts_end INT := date_part('epoch', date_trunc('day', ts_spawn_date + INTERVAL '1day))::INT; BEGIN -- Giving name for partition -------------------------------------------------- table_part := table_master || '_y' || DATE_PART('year', ts_spawn_date)::TEXT || '_m' || DATE_PART('month', ts_spawn_date)::TEXT || '_d' || DATE_PART('day', ts_spawn_date)::TEXT; -- Trying to insert into partition -------------------------------- EXECUTE 'INSERT INTO ' || quote_ident(table_part) || ' SELECT ($1).*' USING NEW; RETURN NULL; -- If not then creating it -------------------------------------------- EXCEPTION WHEN UNDEFINED_TABLE THEN BEGIN -- Creating partition ------------------------------------------- EXECUTE 'CREATE TABLE ' || table_part || ' ( CHECK ( ts_spawn >= ' || ts_start || ' AND ts_spawn < ' || ts_end || '), CONSTRAINT ' || table_part || '_pk PRIMARY KEY (id) ) INHERITS ( ' || table_master || ' ) WITH ( OIDS=FALSE )'; EXECUTE 'CREATE INDEX ids_' || table_part || '_data_id ON ' || table_part || ' USING btree (data_id);' EXCEPTION WHEN DUPLICATE_TABLE THEN -- Do nothing EXECUTE 'INSERT INTO ' || quote_ident(table_part) || ' SELECT ($1).*' USING NEW; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; CREATE TRIGGER insert_foo_trigger BEFORE INSERT ON foos FOR EACH ROW EXECUTE PROCEDURE foos_insert_trigger(); """)) Here is definition. PostgreSQL ensures that all columns that are in "table_master" will be in the inherited tables, but the indexes won't: I should create them manually by describing it in procedure code. So, if I add new index, I should add them into trigger code. That is my migration: from alembic importop import sqlalchemy as sa revision = '77e958e7e1bd' down_revision = 'e47e752436d1' def upgrade(): op.add_column('foos', sa.Column('new_column', sa.Integer)) op.create_index('ix_foos_name', 'foos', ['name']) After execution, table "foos" and its children will have new column ("new_column"), but new index will be only into "foos" table. So, currently I should get all partitions and execute "CREATE INDEX" manually, something like that: conn = op.get_bind() for child_name in conn.execute(text("select child.relname from pg_catelog.pg_ingerits INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid = child.oid WHERE inhparent = 'foos'::regclass")): op.create_index('ix_{table}_name'.format(table=child_name), child_name, [ 'name']) First of all, it would be cool, If I did not have to insert "CREATE INDEX" statement manually in trigger code. But it seems to be impossible now :) Also, as you can see, I should manually find each partition of master table and manually create index for it. Can alembic do it himself? > > > > > If autodetection not working... > > I can get list of inherited tables with such query: > > |SELECT child.relname, parent.relname > > FROM pg_catalog.pg_inherits > > INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid = > > child.oid) > > INNER JOIN pg_catalog.pg_class as parent ON (pg_inherits.inhparent = > > parent.oid) > > WHERE inhparent = 'base_table'::regclass;| > > > > Returned names can be specified as parameter "only" in "reflect()" > > method of MetaData instance. Can I specify target table for each table > > in metadata for migration? > > I found just such solution: > > | > > for table_name in inherit_table_names: > > meta_data.tables[table_name].name = "base_table" > > I'm not really following what you're doing here. Changing the name of > a Table like that is probably not safe in general, depends on the context. > > > > > > > > | > > > > Is my solution safe? > > > > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google > > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > > an email to sqlalchemy+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.