Share
Embed
Почему и как заменить все id на UUID
Dmitry Efimov
December 23, 2015
Transcript
17 slides
-
Украли cookie пользователя, но какой в них смысл если… u
в запросах на редактирование и получение данных пользователя обязателен параметр user_id u при смене пароля user_id меняется -
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 -
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 -
change old rails project - ALTER TABLE u new parameter
u primary key u relationships u foreign key -
new parameter u ALTER TABLE users ADD COLUMN user_id uuid
DEFAULT (uuid_generate_v4()); -
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); -
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; -
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); -
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