メインメニューを開く

Tep3-onk-2

2022年9月26日 (月) 18:03時点におけるShudo (トーク | 投稿記録)による版 (→‎新データベースにリストア)

目次

準備

プロジェクトディレクトリ作成 移動 バージョン確認

new

% rails new . -d postgresql --css tailwind
% bundle install
% rails db:create
または
% rails db:reset 
% rails db:migrate
% rails server

daisyui

tailwind css UIコンポーネント　インストール 

% yarn add daisyui

config/tailwind.config.js 

plugins: [
    require('@tailwindcss/forms'),
    require('@tailwindcss/aspect-ratio'),
    require('@tailwindcss/typography'),
    require('daisyui'),    // ← 追記する
  ],

app/views/layouts/application.html.erb 

<html data-theme="light">  // テーマを選ぶ

データベース移行

Herokuからdumpしてローカルにheroku_dumpデータベースを新設しリストアする

$ heroku pg:backups:capture
$ heroku pg:backups:download
$ createdb mydb
$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

作り直すアプリはデータベース構造を変えるのでテーブルごとにダンプ・リストアする

データ数確認

=# select count(*) from clients ;
 count 
-------
  5067
(1 row)

heroku_dumpデータベースでテーブル名を変える

テーブル毎に変える 

=# ALTER TABLE users RENAME TO users_old;
=# ALTER TABLE clients RENAME TO clients_old;

旧テーブルダンプ

テーブル毎にダンプする 

% pg_dump --username=jq --table users_old heroku_dump > users_old.sql

userテーブル

旧テーブルを新アプリのデータベースにリストア

% psql --username=jq tep5_development < ../old_users.sql 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 17
 setval 
--------
     51
(1 row)

ERROR:  relation "users_pkey" already exists
ERROR:  relation "index_users_on_email" already exists
CREATE INDEX

旧テーブルをセレクトして新テーブルにインサートする

=# insert into users
(
    id, name, email, created_at, updated_at, password_digest, remember_digest, activation_digest, activated, activated_at, reset_digest, reset_sent_at
)
select
    id, name, email, created_at, updated_at, password_digest, remember_digest, activation_digest, activated, activated_at, reset_digest, reset_sent_at
from
    users_old;
INSERT 0 17

noteテーブル

旧jiinテーブルをリストアする

% psql --username=jq tep5_development < ../old_jiins.sql 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 14
 setval 
--------
     14
(1 row)

ALTER TABLE
CREATE INDEX
ERROR:  there is no unique constraint matching given keys for referenced table "users_old"

noteテーブルにインサートする

=# insert into notes
(
    id, content, user_id, created_at, updated_at, kind              
)
select
    id, jiin_name, user_id, created_at, updated_at, kind              
from
    jiins;
INSERT 0 14

clientsテーブル　othersテーブル

% pg_dump --username=jq --table clients_old heroku_dump > clients_old.sql

旧clientsテーブルをリストアする

% psql --username=jq tep5_development < ../old_other.sql 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ERROR:  relation "clients_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 5067
 setval 
--------
   5351
(1 row)

ERROR:  relation "clients_pkey" already exists
CREATE INDEX
ERROR:  relation "index_clients_on_user_id" already exists
CREATE INDEX
CREATE INDEX
ERROR:  there is no unique constraint matching given keys for referenced table "users_old"
ALTER TABLE

clientsテーブルにインサートする

旧clientsテーブルをclientsとotherに分ける

新clientsテーブルは代表者名テーブルになる　旧clientsテーブルのnusiをセレクトして新clientsテーブルにインサートする　nusiのie_idを新clientsテーブルのidにする　その前にie_idの重複を修正する

重複確認 

=# SELECT ie_id, COUNT(ie_id)
FROM clients_old
where nusi = true GROUP BY ie_id
HAVING COUNT(ie_id) > 1
ORDER BY ie_id;
 ie_id | count 
-------+-------
  2090 |     2
  2129 |     2
  2167 |     2
  2174 |     2

桁数を変えて重複を解消する 

=# update clients_old SET ie_id = ie_id + 20000 where jiin_id = 2;
UPDATE 776
=# update clients_old SET ie_id = ie_id + 30000 where jiin_id = 3;
UPDATE 790
=# update clients_old SET ie_id = ie_id + 40000 where jiin_id = 4;
UPDATE 307

主重複修正 

=# update clients_old set nusi = false where id = 3703;
=# update clients_old set nusi = false where id = 1346;

不要データ削除　修正 

=# delete from clients_old where id in (4028,3911,3912,2998,3007,4359);

tep5_development=# update clients_old set nusi = true where id = 523;
UPDATE 1
tep5_development=# delete from clients_old where id = 845;
DELETE 1
tep5_development=# update clients_old set ie_id = 1264 where id = 515;
UPDATE 1
tep5_development=# update clients_old set ie_id = 1264 where id = 1463;
UPDATE 1
tep5_development=# update clients_old set nusi = false where id = 515;
UPDATE 1
tep5_development=# delete from clients_old where id = 217;
DELETE 1
tep5_development=# update clients_old set nusi = false where id = 218;
UPDATE 1
tep5_development=# update clients_old set ie_id = 204 where id = 218;
UPDATE 1
tep5_development=# delete from clients_old where id = 811;
DELETE 1
tep5_development=# update clients_old set nusi = true where id = 1792;
UPDATE 1
tep5_development=# delete from clients_old where id = 810;
DELETE 1
tep5_development=# delete from clients_old where ie_id = 22653;
DELETE 3

listsテーブルに一つデータをいれる 

=# insert into lists (user_id , note_id, content, created_at, updated_at)VALUES (1, 1, 'リスト', '2022-9-18', '2022-9-18');

nusiをセレクトして新clientsにインサート 

=# insert into clients
(
    id, user_id, note_id, content, client_line, created_at, updated_at
)
select
    ie_id, user_id, jiin_id, name, table_order,  created_at, updated_at
from
    clients_old
where
    nusi = true;
INSERT 0 1292

othersテーブルにインサートする

親テーブルie_id 子テーブルie_id　差分削除

旧clientsテーブルに使用されていない重複した氏名がある　新clientsテーブルとclients_oldテーブルの差分をとり削除する 

% diff aa_clients_id_ie_id.csv bb_clients_old_ie_id.csv > diff.csv

=# delete from clients_old where ie_id in (21,22,23,24,26,27,30,32,33,34,35,36,39,41,43,44,45,46,48,49,51,52,53,56,57,62,63,64,66,67,68,69,71,73,78,79,80,82,87,90,92,93,94,95,96,97,98,99,100,101,102,104,105,106,108,109,110,111,113,115,116,117,118,121,122,123,124,126,127,128,129,131,132,133,135,136,138,139,140,141,144,145,147,148,150,151,152,153,157,158,159,161,162,166,170,171,173,174,176,177,179,180,182,183,184,185,186,188,189,190,191,192,194,197,199,201,202,205,222,228,230,231,232,249,264,272,310,355,372,373,375,404,461,472,473,490,491,531,550,561,590,649,673,674,676,679,683,686,689,691,694,701,741,754,800,802,804,805,808,814,815,821,848,872,875,876,921,923,936,953,972,993,998,1009,1010,1013,2072,5158,6053)

データ修正したclients_oldテーブルをすべてothersテーブルにインサートする 

=# insert into others
(
    id, name, kana, client_id, client_name, user_id, note_id, created_at, updated_at, memo, other_line, generation
)
select
    id, name, yomi, ie_id, nusi, user_id, jiin_id, created_at, updated_at, memo, each_order, generation
from
    clients_old;
INSERT 0 4747

2つのテーブルが同じならコピーされる

jq@MacBook-Pro tep5 % pg_dump --username=jq --table clients update_heroku > ../new_clients.sql
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_clients.sql

jq@MacBook-Pro tep5 % pg_dump --username=jq --table others update_heroku > ../new_others.sql
jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_others.sql 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
ERROR:  relation "others" already exists
ALTER TABLE
ERROR:  relation "others_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 4747
 setval 
--------
      1
(1 row)

ERROR:  multiple primary keys for table "others" are not allowed
ERROR:  relation "index_others_on_client_id" already exists
ERROR:  relation "index_others_on_client_id_and_other_line" already exists
ERROR:  relation "index_others_on_note_id" already exists
ERROR:  relation "index_others_on_user_id" already exists
ERROR:  relation "index_others_on_user_id_and_note_id" already exists
ERROR:  constraint "fk_rails_10377da6e2" for relation "others" already exists
ERROR:  constraint "fk_rails_26cdbbd90e" for relation "others" already exists
ERROR:  constraint "fk_rails_54f6a74f65" for relation "others" already exists

placesテーブル

確認 

heroku_dump=# select count(*) from places;
 count 
-------
   919
(1 row)

テーブル名変更 

heroku_dump=# ALTER TABLE places RENAME TO places_old;

dump 

% pg_dump --username=jq --table places_old heroku_dump > places_old.sql

新データベースにリストア

% psql --username=jq tep5_development < ../places_old.sql 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 919
 setval 
--------
   1070
(1 row)

ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ERROR:  relation "public.clients_old" does not exist
ERROR:  relation "public.users_old" does not exist
ERROR:  relation "public.jiins" does not exist

tep5_development=# update places_old SET ie_id = ie_id + 20000 where jiin_id = 2;
UPDATE 213
tep5_development=# update places_old SET ie_id = ie_id + 30000 where jiin_id = 3;
UPDATE 151
tep5_development=# update places_old SET ie_id = ie_id + 40000 where jiin_id = 4;
UPDATE 67

=# insert into places (id, user_id, note_id, client_id, zip, ken, city, etc, flag, created_at, updated_at) 
select id, user_id, jiin_id, ie_id, zip, ken, city, etc, flag, created_at, updated_at
from places_old;

generate

% rails generate scaffold Place user:references note:references client:references zip:string ken:string city:string etc:string flag:boolean memo:text

indexを追加 uuid消す

migrationファイル名の日時をenable_extension_for_uuid.rb以前にする 

% rails db:migrate:status

database: tep5_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20220917020659  Create users
   up     20220917023135  Create notes
   up     20220917023510  Create lists
   up     20220917023750  Create clients
   up     20220917024207  Create others
  down    20220917035020  Create places
   up     20220918004020  Enable extension for uuid
   up     20220918004824  Change users id type to uuid
   up     20220918004853  Change notes id type to uuid
   up     20220918004934  Change lists id type to uuid
   up     20220918005011  Change clients id type to uuid
   up     20220918005027  Change others id type to uuid
   up     20220920062901  Create categories
   up     20220920063011  Create relationship categories
   up     20220925035934  Create relationships

すべてdownにする

schema.rbを消す 

% rails db:drop

EnableExtensionForUuid以下のmigrationを移動する 

% rails db:create

% psql tep5_development < ../202209261601_tep5.sql

% rails db:migrate

テーブルごとにリストア　インサート

ダンプ ここまで形を整えたデータベースをバックアップしとく

% pg_dump tep5_development > tep5_uuid.sql

UUIDを有効にする

設定

% rails generate migration enable_extension_for_uuid

migrate編集 

class EnableExtensionForUuid < ActiveRecord::Migration[7.0]
  def change                   
    enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')
  end
end

% rails db:migrate

デフォルトのキーをuuidにする 

config/initializers/generators.rb 作成

Rails.application.config.generators do |g|
  g.orm :active_record, primary_key_type: :uuid
end

途中からUUIDに変更する場合

migrationファイル追加 

 % rails generate migration enable_extension_for_uuid

% rails generate migration change_user_id_type_to_uuid 

% rails destroy migration change_user_id_type_to_uuid

% rails generate migration change_users_id_type_to_uuid

% rails generate migration change_notes_id_type_to_uuid

% rails generate migration change_lists_id_type_to_uuid

% rails generate migration change_clients_id_type_to_uuid

% rails generate migration change_others_id_type_to_uuid

UUIDに変更したデータベースをDUMPする

修正した旧データのテーブルも一緒にDUMPしとく

不要なテーブルを削除し新しいUUIDのデータベースをDUMPする

tep5_development=# drop table clients_old ;
DROP TABLE
tep5_development=# drop table jiins ;
DROP TABLE
tep5_development=# drop table users_old ;

% pg_dump tep5_development > tep5_uuid.sql

generate

$ rails generate scaffold User name:string email:string password_digest:string remember_digest:string role:integer activation_digest:string activated:boolean activated_at:datetime reset_digest:string reset_sent_at:datetime

migrationファイルを確認　indexを書く　以下同じ 

% rails generate scaffold Note user:references content:string kind:integer meeting_time:integer role:integer note_line:integer

% rails generate scaffold List user:references note:references content:string list_line:integer

% rails generate scaffold Client user:references note:references list:references content:string client_line:integer memo:text

% rails generate scaffold Other user:references note:references client:references name:string kana:string client_name:boolean other_line:integer memo:text generation:string

% rails generate scaffold Category user:references note:references content:string category_line:integer

% rails generate scaffold Relationship_category user:references note:references client_id:integer category_id:integer

% rails generate model Relationship follower_id:integer followed_id:integer

$ rails generate controller Relationships

やり直し
% rails destroy scaffold User .................

$ rails generate scaffold Post content:text user:references picture:string

$ rails generate model Relationship follower_id:integer followed_id:integer

$ rails db:migrate RAILS_ENV=production

$ bin/dev

$ rails generate controller StaticPages home help about contact

エラー 

ActionView::Template::Error (The asset "tailwind.css" is not present in the asset pipeline.

config/envitonments/production.rb 

config.assets.compile = true

サーバーへ転送

除外するファイルをまとめる 

exclude-file.txt
    production.rb
    database.yml

% rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/ jq:/home/jq/tep5/

% rsync ~/rails_app_tepnote/tep5_uuid_dump.sql jq:/home/jq/dump_data

サーバー側

$ bundle install  --without test development

database.yml　username:などを確認する　（2箇所） 

/config/environments/production.rb

データベース削除 

postgres=# DROP DATABASE tep5_production;

データベース作成 

$ createdb tep5_production

リストア 

$ psql tep5_production < ../dump_data/tep5_uuid_dump.sql 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 1
COPY 1295
COPY 1
COPY 14
COPY 4747
COPY 11
COPY 17
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

$ rails db:migrate RAILS_ENV=production
「http://pokpok.jp/index.php?title=Tep3-onk-2&oldid=3614」から取得