メインメニューを開く

Tep3-onk-2

2022年9月18日 (日) 13:54時点における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

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

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

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