пятница, 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 [email protected] <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > To post to this group, send email to [email protected]
> <javascript:>
> > <mailto:[email protected] <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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
