Установите значение столбца при обновлении таблицы с перегородкой

Я использую PostgreSQL и Alembic для миграции. Когда я добавил новый столбец в свою таблицу User, Alembic сгенерировал миграцию с помощью следующего скрипта:

revision = '4824acf75bf3'
down_revision = '2f0fbdd56de1'

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column(
        'user', 
        sa.Column(
            'username', 
            sa.Unicode(length=255), 
            nullable=False
        )
    )

def downgrade():
    op.drop_column('user', 'username')

На самом деле я хочу автоматически генерировать значение имени пользователя при обновлении производственной версии. Другими словами, в моей производственной версии много пользователей, и если я запустил на ней вышеуказанное обновление, возникнет ошибка, указывающая, что имя пользователя не может быть NULL, поэтому мне нужно удалить всех пользователей, обновить таблицу User и после добавления пользователей снова, что болезненно. Таким образом, я хочу изменить приведенный выше сценарий на:

revision = '4824acf75bf3'
down_revision = '2f0fbdd56de1'

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column(
        'user', 
        sa.Column(
            'username', 
            sa.Unicode(length=255)
        )
    )
    op.execute(
        'UPDATE "user" set username = <email address with no '@' 
         and everything comes after '@' sign should be removed> 
         WHERE email is not null'
    )
    <only after the above code is executed 'nullable=False' must be set up>

def downgrade():
    op.drop_column('user', 'username')

Как указано выше в коде, я хотел бы выполнить код SQL, который проверяет адрес электронной почты, например [email protected], и выдает все после знака '@' (в данном случае '@ example.com') и устанавливает значение имени пользователя (в данном случае «test») после этого делает значение nullable = false.

Как я могу это сделать? Какой должен быть скрипт вместо username = <email address with no '@' and everything comes after '@' sign should be removed> и установка nullable=false

Или есть другой способ установить значение username по умолчанию, чтобы он был адресом электронной почты без @ sing и всего, что после него?


postgresql upgrade database alembic auto-generate
person Max    schedule 20.08.2014    source источник
comment
Спасибо, что спросили об этом; Хотелось бы, чтобы инструкции перегонного куба были более ясными, что op.execute - это способ сделать это.   -  person jobevers    schedule 24.12.2014

Ответы (2)


arrow_upward
7
arrow_downward

вот как была решена проблема.

def upgrade():
    op.add_column(
        'user',
        sa.Column(
            'username',
            sa.Unicode(length=255)
        )
    )
    op.create_index('ix_user_username', 'user', ['username'], unique=True)
    op.execute(
        '''
        DO
        $do$
        DECLARE uid INTEGER;
        DECLARE username_candidate TEXT;
        BEGIN
        FOR uid, username_candidate IN (
            SELECT
                id,
                lower(
                    substring(email for position('@' in email) - 1)
                )
            FROM "user" WHERE username is null
        ) LOOP
            UPDATE "user"
            SET username = username_candidate
            WHERE
                id = uid AND
                NOT EXISTS (
                SELECT id FROM "user" WHERE username = username_candidate
            );
        END LOOP;
        END
        $do$
        '''
    )
    # Fix name colissions
    op.execute(
        '''
        DO
        $do$
        DECLARE uniqufier INTEGER := 0;
        DECLARE uid INTEGER;
        DECLARE username_candidate TEXT;
        BEGIN
        WHILE EXISTS (SELECT id FROM "user" WHERE username is null) LOOP
            uniqufier := uniqufier + 1;
            FOR uid, username_candidate IN (
                SELECT
                    id,
                    lower(
                        substring(email for position('@' in email) - 1)
                        || uniqufier
                    )
                FROM "user" WHERE username is null
            ) LOOP
                UPDATE "user"
                SET username = username_candidate
                WHERE
                    id = uid AND
                    NOT EXISTS (
                        SELECT id FROM "user" WHERE username = username_candidate
                    );
            END LOOP;
        END LOOP;
        END;
        $do$
        '''
    )
    op.alter_column(
        'user',
        'username',
        nullable=False,
    )


def downgrade():
    op.drop_index('ix_user_username', table_name='user')
    op.drop_column('user', 'username')
person Max    schedule 26.08.2014

arrow_upward
0
arrow_downward

Сценарий для обновления нового столбца на основе значений из другого столбца той же таблицы может быть написан с помощью подзапроса. Единственный трюк заключается в том, что поскольку вы запрашиваете из той же таблицы, вам нужно будет указать псевдонимы для имени таблицы, чтобы гарантировать выбор из соответствующих строк:

update 'user' as target set username = (
  select substring(email from '.+?(?=@)')
  from 'user' as source where source.id = target.id
);
person Timur    schedule 14.05.2018