[Blog Project] Cảm hứng mới ^^

create-database-and-table-and-insert-content:
  runs-on: ubuntu-latest

  steps:
  - name: Install PostgreSQL client
    run: |
      sudo apt-get update
      sudo apt-get install --yes postgresql-client

- name: Create database if not exists
  env:
    PGPASSWORD: ${{ secrets.DB_PASSWORD }}
  run: |
    result=$(psql -h ${{ secrets.DB_HOST }} -d ${{ secrets.DB_NAME_DEFAULT }} -U ${{ secrets.DB_USERNAME }} -p ${{ secrets.DB_PORT }} -tAc "SELECT 'CREATE DATABASE blog' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'blog');")
    if [ "$result" = "CREATE DATABASE blog" ]; then
        echo Create database
        psql -h ${{ secrets.DB_HOST }} -d ${{ secrets.DB_NAME_DEFAULT }} -U ${{ secrets.DB_USERNAME }} -p ${{ secrets.DB_PORT }} -c "CREATE DATABASE blog;"
    fi

- name: Drop table if exists and recreate table
  run: psql -h ${{ secrets.DB_HOST }} -d ${{ secrets.DB_NAME }} -U ${{ secrets.DB_USERNAME }} -p ${{ secrets.DB_PORT }} -f sql/create-table.sql
  env:
    PGPASSWORD: ${{ secrets.DB_PASSWORD }}

drop table if exists contents; 
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE IF NOT EXISTS contents (
  id                        SERIAL              PRIMARY KEY,
  uuid                      UUID                UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
  title                     VARCHAR             NOT NULL,
  content                   TEXT                NOT NULL,
  params                    JSONB               NOT NULL,
  banner_image              VARCHAR(255)        NOT NULL,
  created_at                TIMESTAMP           NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  created_by                VARCHAR(255)        NOT NULL,
  updated_at                TIMESTAMP                   ,
  updated_by                VARCHAR(255)                ,
  deleted_at                TIMESTAMP                   ,
  deleted_by                VARCHAR(255)                
);

  - name: Checkout repository
    uses: actions/checkout@v2

  - name: Run script to generate insert sql
    run: |
      chmod +x run.sh 
      ./run.sh

  - repository
    - content
      - topic
        - post title name
          - content.md 
          - description.json

#!/bin/bash

if [ -d "content" ]; then
    rm -f sql/insert.sql

    for dir in content/*; do
      for dir2 in $dir/*; do
        echo "Processing $dir2"
        if [ -d "$dir2" ]; then
            title=$(jq -r '.title' "$dir2/description.json")
            banner_image=$(jq -r '.banner_image' "$dir2/description.json")
            content=$(cat "$dir2/content.md")
            content=$(echo "$content" | sed "s/'/''/g")
            created_at=$(jq -r '.created_at' "$dir2/description.json")
            params=$(jq -r '.params' "$dir2/description.json")
            
            echo "INSERT INTO contents (title, content, banner_image, params, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by)" >> sql/insert.sql
            echo "VALUES ('$title', '$content', '$banner_image', '$params', '$created_at', 'dongtran', NULL, NULL, NULL, NULL);" >> sql/insert.sql
        fi
      done
    done

    echo "SQL file generated successfully."
else
    echo "Content directory not found in the project."
    exit 1
fi

  - name: Insert data into table
    run: psql -h ${{ secrets.DB_HOST }} -d ${{ secrets.DB_NAME }} -U ${{ secrets.DB_USERNAME }} -p ${{ secrets.DB_PORT }} -f sql/insert.sql
    env:
      PGPASSWORD: ${{ secrets.DB_PASSWORD }}