flyway를 적용하고, 현재까지의 db 버전을 관리한다.

[jinyoungchoi95]

• 의존성 추가

implementation 'org.flywaydb:flyway-core'

• 평점에 사용되었던 ddl 추가
• prod환경에서 동작하지 않도록 확인

[jinyoungchoi95]

review_count, review_sum, review_avg 동기화 쿼리

alter table restaurant
    add column review_count INT    not null default 0,
    add column review_sum   BIGINT not null default 0,
    add column review_avg   FLOAT  not null default 0;



update restaurant
set review_count = (select count(*)
                    from review
                    where review.restaurant_id = restaurant.id)
where id > 0;

update restaurant
set review_sum = (select sum(rating)
                  from review
                  where review.restaurant_id = restaurant.id)
where review_count > 0;

update restaurant
set review_avg = review_sum / cast(review_count as float)
where review_sum > 0;

[jinyoungchoi95]

북마크 추가 쿼리

create table bookmark
(
    id            bigint      NOT NULL AUTO_INCREMENT,
    member_id     bigint      NOT NULL,
    restaurant_id bigint      NOT NULL,
    primary key (id)
);

alter table bookmark add constraint unique_member_restaurant unique (member_id, restaurant_id);