メインメニューを開く

Tep3-onk-2

2022年9月18日 (日) 09:13時点におけるShudo (トーク | 投稿記録)による版 (→‎旧clientsテーブルをclientsとotherに分ける)

目次

準備

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

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

clientテーブル

% 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
tep5_development=# insert into others

list_idをnullを許可する　あと適切な時点で戻す 

class CreateClients < ActiveRecord::Migration[7.0]
  def change
    create_table :clients do |t|
      t.references :user, null: false, foreign_key: true
      t.references :note, null: false, foreign_key: true
      t.references :list, foreign_key: true
      #t.references :list, null: false, foreign_key: true

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

親テーブル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テーブルをすべてotherテーブルにインサートする 

=# 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

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

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 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

参考

=# 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)
DELETE 307
「http://pokpok.jp/index.php?title=Tep3-onk-2&oldid=3572」から取得