Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Почему и как заменить все id на UUID

Почему и как заменить все id на UUID

Dmitry Efimov

December 23, 2015
Tweet

More Decks by Dmitry Efimov

Other Decks in Programming

Transcript

  1. Украли cookie пользователя, но какой в них смысл если… u

    в запросах на редактирование и получение данных пользователя обязателен параметр user_id u при смене пароля user_id меняется
  2. package in os u linux ubuntu: sudo apt-get install libpq-dev

    u centos: sudo yum install postgresql-contrib u os x: brew install postgis
  3. create new rails project u class CreateUsers < ActiveRecord::Migration def

    change create_table :users, id: :uuid do |t| end end u class CreateBoxes < ActiveRecord::Migration def change create_table :boxes do |t| t.references :user, type: :uuid end end end
  4. change old rails project - ALTER TABLE u new parameter

    u primary key u relationships u foreign key
  5. primary key u ALTER TABLE users DROP CONSTRAINT users_pkey; u

    ALTER TABLE users ALTER COLUMN id DROP NOT NULL; u ALTER TABLE users ALTER COLUMN id DROP DEFAULT; u ALTER TABLE users ALTER COLUMN id TYPE uuid USING (uuid_generate_v4()); u ALTER TABLE users ALTER COLUMN id SET DEFAULT uuid_generate_v4(); u ALTER TABLE users ADD PRIMARY KEY (id);
  6. relationships u ALTER TABLE boxes ADD COLUMN user_uuid uuid; u

    UPDATE boxes T SET user_uuid = (SELECT uuid FROM users WHERE id = T.user_id); u ALTER TABLE boxes ALTER COLUMN user_id TYPE uuid USING user_uuid; u ALTER TABLE boxes DROP COLUMN user_uuid;
  7. foreign key u ALTER TABLE boxes DROP CONSTRAINT user_id_fk; u

    ALTER TABLE boxes ADD COLUMN user_uuid uuid; u UPDATE boxes T SET user_uuid = (SELECT uuid FROM users WHERE id = T.user_id); u ALTER TABLE boxes ALTER COLUMN user_id TYPE uuid USING user_uuid; u ALTER TABLE boxes DROP COLUMN user_uuid; u ALTER TABLE boxes ADD FOREIGN KEY (user_id) REFERENCES users(id);
  8. links u https://ru.wikipedia.org/wiki/UUID u http://postgresql.ru.net/manual/datatype-uuid.html u http://www.postgresql.org/docs/current/static/datatype-uuid.html u http://www.postgresql.org/docs/current/static/pgcrypto.html u

    http://www.postgresql.org/docs/current/static/uuid-ossp.html u http://postgresql.ru.net/manual/ddl-alter.html u http://www.postgresql.org/docs/current/static/ddl-alter.html u http://www.postgresql.org/docs/current/static/sql-altertable.html