[Dev] LLM을 활용 단어장 앱 개발일지 005: CRUD - 단어 DB 읽어서 표시하기

DB를 파이어베이스를 사용해도 되지만 여러 스택을 공부하고 싶어서 MySQL에 데이터를 저장해놓고 가져오는 것까지만 일단해보려 한다. SQLD 자격증은 있지만 DB를 다뤄보는 건 처음;;

뭐 그래서 MySQL을 공부하려고 전자 도서관에서 책빌렸는데 Real MySQL이란 책이 DB를 다룬다기보단 SQL의 심화를 배우는 느낌이어서 환경 설치만 하고 간단한 쿼리로 데이터 넣는 것만 진행해보았다.

Model

우선 단어 모델을 만들어본다. 전체적으로 영어 단어, 뜻, 예문, 태그가 중심인 데이터 모델이다. 따라서 이 구조로 테이블을 만들어보겠다.

Create Table

CREATE TABLE words (
    word_id INT AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(255) UNIQUE NOT NULL
);

word_id : 단어 인덱스, word : 단어

AUTO_INCREMENT : id 자동 증가
PRIMARY KEY : 기본키 설정
UNIQUE : 단어 중복 방지
NOT NULL : NULL 금지

CREATE TABLE tags (
    tag_id INT AUTO_INCREMENT PRIMARY KEY,
    tag VARCHAR(255) NOT NULL,
    word_id INT,
    FOREIGN KEY (word_id) REFERENCES words(word_id) ON DELETE CASCADE
);

tag_id : 태그 인덱스, tag : 태그, word_id : 참조된 인덱스

REFERENCES : 참조
ON DELETE CASCADE : 부모키가 삭제되면 같이 삭제

CREATE TABLE examples (
    example_id INT AUTO_INCREMENT PRIMARY KEY,
    example TEXT NOT NULL,
    word_id INT,
    FOREIGN KEY (word_id) REFERENCES words(word_id) ON DELETE CASCADE
);

example_id : 예문 인덱스, example : 예문, word_id 참조된 인덱스

CREATE TABLE meanings (
    meaning_id INT AUTO_INCREMENT PRIMARY KEY,
    meaning TEXT NOT NULL,
    word_id INT,
    FOREIGN KEY (word_id) REFERENCES words(word_id) ON DELETE CASCADE
);

meaning_id : 의미 인덱스, meaning : 의미, word_id ; 참조된 인덱스

Insert Data

# 단어 삽입
INSERT INTO words (word) VALUES ('apple');

# 태그 5개, 단어 외래키 등록
INSERT INTO tags (tag, word_id) VALUES ('fruit', 1);
INSERT INTO tags (tag, word_id) VALUES ('food', 1);
INSERT INTO tags (tag, word_id) VALUES ('healthy', 1);
INSERT INTO tags (tag, word_id) VALUES ('snack', 1);
INSERT INTO tags (tag, word_id) VALUES ('red', 1);

# 예문 3개, 단어 외래키 등록
INSERT INTO examples (example, word_id) VALUES ('An apple a day keeps the doctor away.', 1);
INSERT INTO examples (example, word_id) VALUES ('She ate an apple for breakfast.', 1);
INSERT INTO examples (example, word_id) VALUES ('The apple fell from the tree.', 1);

# 뜻 4개, 단어 외래키 등록
INSERT INTO meanings (meaning, word_id) VALUES ('A round fruit with red, green, or yellow skin.', 1);
INSERT INTO meanings (meaning, word_id) VALUES ('A technology company based in Cupertino.', 1);
INSERT INTO meanings (meaning, word_id) VALUES ('A common fruit often eaten as a snack.', 1);
INSERT INTO meanings (meaning, word_id) VALUES ('The fruit of the apple tree, Malus domestica.', 1);

조회

조인한 후 한번에 보겠다.

SELECT w.word, t.tag, e.example, m.meaning
FROM words w
LEFT JOIN tags t ON w.word_id = t.word_id
LEFT JOIN examples e ON w.word_id = e.word_id
LEFT JOIN meanings m ON w.word_id = m.word_id
WHERE w.word = 'apple';

+-------+---------+---------------------------------------+------------------------------------------------+
| word  | tag     | example                               | meaning                                        |
+-------+---------+---------------------------------------+------------------------------------------------+
| apple | fruit   | An apple a day keeps the doctor away. | A round fruit with red, green, or yellow skin. |
| apple | fruit   | An apple a day keeps the doctor away. | A technology company based in Cupertino.       |
| apple | fruit   | An apple a day keeps the doctor away. | A common fruit often eaten as a snack.         |
| apple | fruit   | An apple a day keeps the doctor away. | The fruit of the apple tree, Malus domestica.  |
| apple | fruit   | She ate an apple for breakfast.       | A round fruit with red, green, or yellow skin. |
| apple | fruit   | She ate an apple for breakfast.       | A technology company based in Cupertino.       |
| apple | fruit   | She ate an apple for breakfast.       | A common fruit often eaten as a snack.         |
| apple | fruit   | She ate an apple for breakfast.       | The fruit of the apple tree, Malus domestica.  |
| apple | fruit   | The apple fell from the tree.         | A round fruit with red, green, or yellow skin. |
| apple | fruit   | The apple fell from the tree.         | A technology company based in Cupertino.       |
| apple | fruit   | The apple fell from the tree.         | A common fruit often eaten as a snack.         |
| apple | fruit   | The apple fell from the tree.         | The fruit of the apple tree, Malus domestica.  |
| apple | food    | An apple a day keeps the doctor away. | A round fruit with red, green, or yellow skin. |
| apple | food    | An apple a day keeps the doctor away. | A technology company based in Cupertino.       |
| apple | food    | An apple a day keeps the doctor away. | A common fruit often eaten as a snack.         |
| apple | food    | An apple a day keeps the doctor away. | The fruit of the apple tree, Malus domestica.  |
| apple | food    | She ate an apple for breakfast.       | A round fruit with red, green, or yellow skin. |
| apple | food    | She ate an apple for breakfast.       | A technology company based in Cupertino.       |
| apple | food    | She ate an apple for breakfast.       | A common fruit often eaten as a snack.         |
| apple | food    | She ate an apple for breakfast.       | The fruit of the apple tree, Malus domestica.  |
| apple | food    | The apple fell from the tree.         | A round fruit with red, green, or yellow skin. |
| apple | food    | The apple fell from the tree.         | A technology company based in Cupertino.       |
| apple | food    | The apple fell from the tree.         | A common fruit often eaten as a snack.         |
| apple | food    | The apple fell from the tree.         | The fruit of the apple tree, Malus domestica.  |
| apple | healthy | An apple a day keeps the doctor away. | A round fruit with red, green, or yellow skin. |
| apple | healthy | An apple a day keeps the doctor away. | A technology company based in Cupertino.       |
| apple | healthy | An apple a day keeps the doctor away. | A common fruit often eaten as a snack.         |
| apple | healthy | An apple a day keeps the doctor away. | The fruit of the apple tree, Malus domestica.  |
| apple | healthy | She ate an apple for breakfast.       | A round fruit with red, green, or yellow skin. |
| apple | healthy | She ate an apple for breakfast.       | A technology company based in Cupertino.       |
| apple | healthy | She ate an apple for breakfast.       | A common fruit often eaten as a snack.         |
| apple | healthy | She ate an apple for breakfast.       | The fruit of the apple tree, Malus domestica.  |
| apple | healthy | The apple fell from the tree.         | A round fruit with red, green, or yellow skin. |
| apple | healthy | The apple fell from the tree.         | A technology company based in Cupertino.       |
| apple | healthy | The apple fell from the tree.         | A common fruit often eaten as a snack.         |
| apple | healthy | The apple fell from the tree.         | The fruit of the apple tree, Malus domestica.  |
| apple | snack   | An apple a day keeps the doctor away. | A round fruit with red, green, or yellow skin. |
| apple | snack   | An apple a day keeps the doctor away. | A technology company based in Cupertino.       |
| apple | snack   | An apple a day keeps the doctor away. | A common fruit often eaten as a snack.         |
| apple | snack   | An apple a day keeps the doctor away. | The fruit of the apple tree, Malus domestica.  |
| apple | snack   | She ate an apple for breakfast.       | A round fruit with red, green, or yellow skin. |
| apple | snack   | She ate an apple for breakfast.       | A technology company based in Cupertino.       |
| apple | snack   | She ate an apple for breakfast.       | A common fruit often eaten as a snack.         |
| apple | snack   | She ate an apple for breakfast.       | The fruit of the apple tree, Malus domestica.  |
| apple | snack   | The apple fell from the tree.         | A round fruit with red, green, or yellow skin. |
| apple | snack   | The apple fell from the tree.         | A technology company based in Cupertino.       |
| apple | snack   | The apple fell from the tree.         | A common fruit often eaten as a snack.         |
| apple | snack   | The apple fell from the tree.         | The fruit of the apple tree, Malus domestica.  |
| apple | red     | An apple a day keeps the doctor away. | A round fruit with red, green, or yellow skin. |
| apple | red     | An apple a day keeps the doctor away. | A technology company based in Cupertino.       |
| apple | red     | An apple a day keeps the doctor away. | A common fruit often eaten as a snack.         |
| apple | red     | An apple a day keeps the doctor away. | The fruit of the apple tree, Malus domestica.  |
| apple | red     | She ate an apple for breakfast.       | A round fruit with red, green, or yellow skin. |
| apple | red     | She ate an apple for breakfast.       | A technology company based in Cupertino.       |
| apple | red     | She ate an apple for breakfast.       | A common fruit often eaten as a snack.         |
| apple | red     | She ate an apple for breakfast.       | The fruit of the apple tree, Malus domestica.  |
| apple | red     | The apple fell from the tree.         | A round fruit with red, green, or yellow skin. |
| apple | red     | The apple fell from the tree.         | A technology company based in Cupertino.       |
| apple | red     | The apple fell from the tree.         | A common fruit often eaten as a snack.         |
| apple | red     | The apple fell from the tree.         | The fruit of the apple tree, Malus domestica.  |
+-------+---------+---------------------------------------+------------------------------------------------+

하나씩 보면 다음과 같다.

 MySQL  localhost:3306 ssl  words_db  SQL > SELECT tag FROM tags WHERE word_id = 1;
+---------+
| tag     |
+---------+
| fruit   |
| food    |
| healthy |
| snack   |
| red     |
+---------+
5 rows in set (0.0004 sec)
 MySQL  localhost:3306 ssl  words_db  SQL > SELECT example FROM examples WHERE word_id = 1;
+---------------------------------------+
| example                               |
+---------------------------------------+
| An apple a day keeps the doctor away. |
| She ate an apple for breakfast.       |
| The apple fell from the tree.         |
+---------------------------------------+
3 rows in set (0.0004 sec)
 MySQL  localhost:3306 ssl  words_db  SQL > SELECT meaning FROM meanings WHERE word_id = 1;
+------------------------------------------------+
| meaning                                        |
+------------------------------------------------+
| A round fruit with red, green, or yellow skin. |
| A technology company based in Cupertino.       |
| A common fruit often eaten as a snack.         |
| The fruit of the apple tree, Malus domestica.  |
+------------------------------------------------+
4 rows in set (0.0004 sec)

Backend : Express.js

노드기반 익스프레스 서버를 실행하는 방법은 간단하다. 노드를 깔고 익스프레스를 npm 설치한 후 필요한 패키지를 설치하면 된다.

npm install express
npm install mysql2
node server.js

server.js 파일은 다음과 같이 작성한다.

// server.js
const express = require('express');
const mysql = require('mysql2');
const app = express();
const port = 3000;

// MySQL 연결 설정
const db = mysql.createConnection({
  host: 'localhost',
  user: 'Bull',
  password: '0000',
  database: 'words_db'
});

// 연결
db.connect((err) => {
  if (err) {
    throw err;
  }
  console.log('MySQL Connected...');
});

app.get('/words', (req, res) => {
  const sql = `
    SELECT w.word, 
           GROUP_CONCAT(DISTINCT t.tag SEPARATOR ', ') AS tags,
           GROUP_CONCAT(DISTINCT e.example SEPARATOR '\n') AS examples,
           GROUP_CONCAT(DISTINCT m.meaning SEPARATOR '; ') AS meanings
    FROM words w
    LEFT JOIN tags t ON w.word_id = t.word_id
    LEFT JOIN examples e ON w.word_id = e.word_id
    LEFT JOIN meanings m ON w.word_id = m.word_id
    GROUP BY w.word
  `;

  db.query(sql, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

app.listen(port, () => {
  console.log(`Server started on port ${port}`);
});

핵심만 보자.
mysql.createConnection : mysql에서 DB를 설정해주는 메소드이다. 콜백으로 프로퍼티를 설정해주는 것 같다.

db.connect : 로컬에 켜진 DB에 연결한다. (MySQL은 3060 포트로 자동 매핑되는 듯)

sql :

SELECT w.word, 
       GROUP_CONCAT(DISTINCT t.tag SEPARATOR ', ') AS tags,
       GROUP_CONCAT(DISTINCT e.example SEPARATOR '\n') AS examples,
       GROUP_CONCAT(DISTINCT m.meaning SEPARATOR '; ') AS meanings
FROM words w
LEFT JOIN tags t ON w.word_id = t.word_id
LEFT JOIN examples e ON w.word_id = e.word_id
LEFT JOIN meanings m ON w.word_id = m.word_id
GROUP BY w.word

이 SQL 쿼리는 words 테이블에 저장된 단어에 대해 관련된 태그, 예문, 뜻을 하나의 결과로 묶어서 반환하는 작업을 수행합니다. 각 단어에 대해 태그, 예문, 뜻이 각각 여러 개일 수 있으므로, 이들을 그룹화하여 하나의 필드에 결합하는 방식으로 출력합니다.

쿼리 구성 요소 설명

SELECT w.word, 
       GROUP_CONCAT(DISTINCT t.tag SEPARATOR ', ') AS tags,
       GROUP_CONCAT(DISTINCT e.example SEPARATOR '\n') AS examples,
       GROUP_CONCAT(DISTINCT m.meaning SEPARATOR '; ') AS meanings
FROM words w
LEFT JOIN tags t ON w.word_id = t.word_id
LEFT JOIN examples e ON w.word_id = e.word_id
LEFT JOIN meanings m ON w.word_id = m.word_id
GROUP BY w.word

GROUP_CONCAT 아래로는 이전에 테이블 조회할 때 중복으로 조회한 거와 같다. 차이점은 GROUP BY 로 인해 단어를 'apple'만 가져오는 게 아니라 모든 단어를 그룹화하여 불러온다.

 

w.word : words 테이블의 word 열을 선택한다.

 

GROUP_CONCAT(DISTINCT t.tag SEPARATOR ', ') AS tags:
- GROUP_CONCAT : 여러 행의 값을 하나의 문자열로 결합한다.
- DISTINCT : 중복된 값을 제거한다.
- t.tag : tags 동일한 단어에 대한 여러 태그를 하나의 문자열로 결합한다.
- SEPARATOR ', ' : 는 각 태그 사이에 쉼표와 공백을 추가하여 구분하도록 한다.
정리하자면 하나의 문자열로 만드는 과정이다.

 

LEFT JOIN tags t ON w.word_id = t.word_id:
- tags 테이블과 words 테이블을 word_id를 기준으로 연결한다.
- LEFT JOIN이기 때문에, words 테이블에 해당 단어가 존재하면 tags 테이블에 태그가 없더라도 결과에 포함된다. 즉 왼쪽 테이블 기준으로 테이블을 매치시킨다. 그 이후에 LEFT JOIN으로도 전부 왼쪽 기준으로 매치시킨다.

쿼리 결과

[
    {
        "word": "apple",
        "tags": "food, fruit, healthy, red, snack",
        "examples": "An apple a day keeps the doctor away.\nShe ate an apple for breakfast.\nThe apple fell from the tree.",
        "meanings": "A common fruit often eaten as a snack.; A round fruit with red, green, or yellow skin.; A technology company based in Cupertino.; The fruit of the apple tree, Malus domestica."
    }
]

db.query : 쿼리를 요청하고 위의결과를 json 파일로 response 해준다.

Flutter에 읽어온 데이터 출력하기

일단 사전에 준비한 코드를 Flutter에 적어준다.

import 'dart:convert';
import 'package:flutter/material.dart';
import 'package:http/http.dart' as http;

void main() {
  runApp(MyApp());
}

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter MySQL Example',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: WordListScreen(),
    );
  }
}

class WordListScreen extends StatefulWidget {
  @override
  _WordListScreenState createState() => _WordListScreenState();
}

class _WordListScreenState extends State<WordListScreen> {
  List words = [];

  @override
  void initState() {
    super.initState();
    fetchWords();
  }

  Future<void> fetchWords() async {
    final response =
        await http.get(Uri.parse('http://ooo.ooo.ooo.ooo:3000/words'));

    if (response.statusCode == 200) {
      setState(() {
        words = json.decode(response.body);
        print(words);
      });
    } else {
      throw Exception('Failed to load words');
    }
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('Word List'),
      ),
      body: ListView.builder(
        itemCount: words.length,
        itemBuilder: (context, index) {
          final wordData = words[index];
          return Card(
            margin: EdgeInsets.all(8.0),
            child: Padding(
              padding: EdgeInsets.all(16.0),
              child: Column(
                crossAxisAlignment: CrossAxisAlignment.start,
                children: [
                  Text(
                    wordData['word'],
                    style: TextStyle(fontSize: 24, fontWeight: FontWeight.bold),
                  ),
                  SizedBox(height: 8.0),
                  Divider(),
                  if (wordData['meanings'] != null)
                    Text(
                      'Meanings: ${wordData['meanings']}',
                      style: TextStyle(fontSize: 16),
                    ),
                  Divider(),
                  if (wordData['tags'] != null)
                    Text(
                      'Tags: ${wordData['tags']}',
                      style: TextStyle(fontSize: 16),
                    ),
                  Divider(),
                  if (wordData['examples'] != null)
                    Text(
                      'Examples:\n${wordData['examples']}',
                      style: TextStyle(fontSize: 16),
                    ),
                ],
              ),
            ),
          );
        },
      ),
    );
  }
}

참고로 에뮬레이터는 localhost 적용안되서 포트포워딩을 해주었다. 위의 내용은 크게 설명할 내용이 없다. List인 words에 요청받은 json 데이터를 디코딩하여 List[<Dictionary>] 형태로 저장한다. words[0]은 그러면 첫번째 Map(Dict) 형태로 데이터를 가져올 수 있게 된다. 이 데이터를 출력해주면 된다.

DB에서 데이터 읽기

아직 단어 위젯 디자인을 구상하지 않았기 때문에 우선적으로 DB 다루는 것 먼저 하고 실제 앱에 적용하는 건 보류했다.