데이터베이스 접근

데이터베이스 접근¶

애드온에서 데이터베이스에 접근하는 다양한 방법을 학습합니다.

XML 쿼리 활용¶

XML 쿼리 대신 직접 MySQL 사용하기¶

<?php
// 애드온에서 직접 MySQL 사용하기
class CustomDatabaseAddon
{
    function __construct()
    {
        // 애드온 생성자
    }

    // MySQL 직접 연결
    private function getDirectConnection()
    {
        $db_info = Context::getDBInfo();

        $connection = new mysqli(
            $db_info->master_db['db_hostname'],
            $db_info->master_db['db_userid'],
            $db_info->master_db['db_password'],
            $db_info->master_db['db_database']
        );

        if($connection->connect_error) {
            throw new Exception('Database connection failed: ' . $connection->connect_error);
        }

        $connection->set_charset('utf8mb4');

        return $connection;
    }

    // 게시물 통계 조회
    public function getDocumentStats($module_srl, $days = 30)
    {
        $connection = $this->getDirectConnection();

        // 테이블 프리픽스 가져오기
        $db_info = Context::getDBInfo();
        $table_prefix = $db_info->master_db['db_table_prefix'];

        $query = "
            SELECT 
                DATE(FROM_UNIXTIME(regdate)) as date,
                COUNT(*) as count,
                AVG(readed_count) as avg_views,
                SUM(voted_count) as total_votes
            FROM {$table_prefix}documents 
            WHERE module_srl = ? 
                AND regdate >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL ? DAY))
                AND status = 'PUBLIC'
            GROUP BY DATE(FROM_UNIXTIME(regdate))
            ORDER BY date DESC
        ";

        $stmt = $connection->prepare($query);
        $stmt->bind_param('ii', $module_srl, $days);
        $stmt->execute();

        $result = $stmt->get_result();
        $data = array();

        while($row = $result->fetch_assoc()) {
            $data[] = $row;
        }

        $stmt->close();
        $connection->close();

        return $data;
    }

    // 복잡한 집계 쿼리
    public function getUserActivityStats($member_srl)
    {
        $connection = $this->getDirectConnection();
        $db_info = Context::getDBInfo();
        $table_prefix = $db_info->master_db['db_table_prefix'];

        $query = "
            SELECT 
                u.member_srl,
                u.nick_name,
                COUNT(DISTINCT d.document_srl) as document_count,
                COUNT(DISTINCT c.comment_srl) as comment_count,
                SUM(d.voted_count) as total_document_votes,
                SUM(c.voted_count) as total_comment_votes,
                AVG(d.readed_count) as avg_document_views,
                MAX(d.regdate) as last_document_date,
                MAX(c.regdate) as last_comment_date
            FROM {$table_prefix}member u
            LEFT JOIN {$table_prefix}documents d ON u.member_srl = d.member_srl AND d.status = 'PUBLIC'
            LEFT JOIN {$table_prefix}comments c ON u.member_srl = c.member_srl AND c.status = 'PUBLIC'
            WHERE u.member_srl = ?
            GROUP BY u.member_srl
        ";

        $stmt = $connection->prepare($query);
        $stmt->bind_param('i', $member_srl);
        $stmt->execute();

        $result = $stmt->get_result();
        $data = $result->fetch_assoc();

        $stmt->close();
        $connection->close();

        return $data;
    }

    // 트랜잭션 사용 예제
    public function transferPoints($from_member, $to_member, $points)
    {
        $connection = $this->getDirectConnection();
        $db_info = Context::getDBInfo();
        $table_prefix = $db_info->master_db['db_table_prefix'];

        try {
            $connection->autocommit(false);

            // 보내는 사람 포인트 차감
            $query1 = "UPDATE {$table_prefix}member SET point = point - ? WHERE member_srl = ? AND point >= ?";
            $stmt1 = $connection->prepare($query1);
            $stmt1->bind_param('iii', $points, $from_member, $points);
            $stmt1->execute();

            if($stmt1->affected_rows == 0) {
                throw new Exception('포인트가 부족합니다.');
            }

            // 받는 사람 포인트 추가
            $query2 = "UPDATE {$table_prefix}member SET point = point + ? WHERE member_srl = ?";
            $stmt2 = $connection->prepare($query2);
            $stmt2->bind_param('ii', $points, $to_member);
            $stmt2->execute();

            // 포인트 이력 기록
            $query3 = "INSERT INTO {$table_prefix}point (member_srl, point, content, regdate) VALUES (?, ?, ?, ?)";
            $stmt3 = $connection->prepare($query3);

            $regdate = date('YmdHis');
            $content1 = sprintf('포인트 전송 (to: %d)', $to_member);
            $content2 = sprintf('포인트 수신 (from: %d)', $from_member);
            $negative_points = -$points;

            $stmt3->bind_param('iiss', $from_member, $negative_points, $content1, $regdate);
            $stmt3->execute();

            $stmt3->bind_param('iiss', $to_member, $points, $content2, $regdate);
            $stmt3->execute();

            $connection->commit();

            $stmt1->close();
            $stmt2->close();
            $stmt3->close();

            return true;

        } catch(Exception $e) {
            $connection->rollback();
            throw $e;
        } finally {
            $connection->close();
        }
    }
}

XML 쿼리 언어¶

XML Query 고급 활용¶

<!-- queries/getAdvancedDocumentList.xml -->
<query id="getAdvancedDocumentList" action="select">
    <tables>
        <table name="documents" alias="d" />
        <table name="member" alias="m" />
        <table name="modules" alias="mod" />
    </tables>
    <columns>
        <column name="d.*" />
        <column name="m.nick_name" />
        <column name="m.email_address" />
        <column name="mod.browser_title" alias="module_title" />
        <column name="COUNT(c.comment_srl)" alias="comment_count" />
        <column name="AVG(cv.point)" alias="avg_vote" />
    </columns>
    <conditions>
        <condition operation="equal" column="d.status" var="status" default="PUBLIC" />
        <condition operation="in" column="d.module_srl" var="module_srls" filter="number" />
        <condition operation="more" column="d.regdate" var="start_date" filter="date" />
        <condition operation="less" column="d.regdate" var="end_date" filter="date" />
        <condition operation="like" column="d.title" var="search_keyword" />
        <condition operation="more" column="d.voted_count" var="min_votes" filter="number" />
    </conditions>
    <navigation>
        <index var="sort_index" default="d.list_order" order="order_type" />
        <list_count var="list_count" default="20" />
        <page_count var="page_count" default="10" />
        <page var="page" default="1" />
    </navigation>
    <joins>
        <join type="left" table="member" alias="m" on="d.member_srl = m.member_srl" />
        <join type="left" table="modules" alias="mod" on="d.module_srl = mod.module_srl" />
        <join type="left" table="comments" alias="c" on="d.document_srl = c.document_srl AND c.status = 'PUBLIC'" />
        <join type="left" table="comment_voted_log" alias="cv" on="c.comment_srl = cv.comment_srl" />
    </joins>
    <groups>
        <group column="d.document_srl" />
    </groups>
    <having>
        <condition operation="more" column="COUNT(c.comment_srl)" var="min_comments" filter="number" />
    </having>
</query>

<!-- 동적 조건 처리 -->
<query id="getDynamicDocumentList" action="select">
    <tables>
        <table name="documents" />
    </tables>
    <columns>
        <column name="*" />
    </columns>
    <conditions>
        <condition operation="equal" column="module_srl" var="module_srl" filter="number" notnull="notnull" />

        <!-- 동적 검색 조건 -->
        <group pipe="or">
            <condition operation="like" column="title" var="search_keyword" pipe="or" />
            <condition operation="like" column="content" var="search_keyword" pipe="or" />
            <condition operation="like" column="nick_name" var="search_keyword" pipe="or" />
        </group>

        <!-- 카테고리 필터 -->
        <condition operation="equal" column="category_srl" var="category_srl" filter="number" />

        <!-- 기간 필터 -->
        <condition operation="more" column="regdate" var="start_date" filter="date" />
        <condition operation="less" column="regdate" var="end_date" filter="date" />

        <!-- 상태 필터 -->
        <condition operation="in" column="status" var="status_list" default="PUBLIC" />

        <!-- 회원 그룹 필터 -->
        <condition operation="in" column="member_srl" var="member_srls" subquery="getMembersByGroup" />
    </conditions>
    <navigation>
        <index var="sort_index" default="list_order" order="order_type" />
        <list_count var="list_count" default="20" />
        <page var="page" default="1" />
    </navigation>
</query>

<!-- 서브쿼리 정의 -->
<query id="getMembersByGroup" action="select">
    <tables>
        <table name="member_group_member" />
    </tables>
    <columns>
        <column name="member_srl" />
    </columns>
    <conditions>
        <condition operation="in" column="group_srl" var="group_srls" filter="number" />
    </conditions>
</query>

외부 PHP 파일에서 DB 접근¶

<?php
// xe 외부 php 파일에서 db 접근하기 executeQuery
// external_script.php

// 라이믹스 코어 로드
require_once('./config/config.inc.php');
require_once('./classes/context/Context.class.php');
require_once('./classes/db/DB.class.php');

// Context 초기화
Context::init();

class ExternalDatabaseAccess
{
    public function __construct()
    {
        // DB 연결 확인
        if(!DB::isConnected()) {
            DB::getInstance();
        }
    }

    // executeQuery 사용
    public function getDocumentsByModule($module_srl, $limit = 10)
    {
        $args = new stdClass();
        $args->module_srl = $module_srl;
        $args->list_count = $limit;
        $args->sort_index = 'regdate';
        $args->order_type = 'desc';
        $args->status = 'PUBLIC';

        $output = executeQuery('document.getDocumentList', $args);

        if(!$output->toBool()) {
            throw new Exception($output->getMessage());
        }

        return $output->data;
    }

    // executeQueryArray 사용
    public function getDocumentStatistics($module_srl)
    {
        $args = new stdClass();
        $args->module_srl = $module_srl;

        // 사용자 정의 쿼리 실행
        $query = "
            SELECT 
                DATE(FROM_UNIXTIME(regdate)) as reg_date,
                COUNT(*) as daily_count,
                AVG(readed_count) as avg_views
            FROM xe_documents 
            WHERE module_srl = %d 
                AND regdate >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
            GROUP BY DATE(FROM_UNIXTIME(regdate))
            ORDER BY reg_date DESC
        ";

        $output = executeQueryArray('', $query, $args);

        if(!$output->toBool()) {
            throw new Exception($output->getMessage());
        }

        return $output->data;
    }

    // 복잡한 집계 쿼리
    public function getUserEngagementReport($start_date, $end_date)
    {
        $args = new stdClass();
        $args->start_date = $start_date;
        $args->end_date = $end_date;

        $query = "
            SELECT 
                m.member_srl,
                m.nick_name,
                COUNT(DISTINCT d.document_srl) as documents,
                COUNT(DISTINCT c.comment_srl) as comments,
                SUM(d.voted_count) as document_votes,
                SUM(c.voted_count) as comment_votes,
                AVG(d.readed_count) as avg_views,
                MAX(GREATEST(
                    IFNULL(d.regdate, 0), 
                    IFNULL(c.regdate, 0)
                )) as last_activity
            FROM xe_member m
            LEFT JOIN xe_documents d ON m.member_srl = d.member_srl 
                AND d.regdate BETWEEN '%s' AND '%s'
                AND d.status = 'PUBLIC'
            LEFT JOIN xe_comments c ON m.member_srl = c.member_srl 
                AND c.regdate BETWEEN '%s' AND '%s'
                AND c.status = 'PUBLIC'
            WHERE m.regdate < '%s'
            GROUP BY m.member_srl
            HAVING (documents > 0 OR comments > 0)
            ORDER BY last_activity DESC
        ";

        $formatted_query = sprintf(
            $query, 
            $start_date, $end_date, 
            $start_date, $end_date, 
            $end_date
        );

        $output = executeQueryArray('', $formatted_query);

        return $output->data;
    }

    // 트랜잭션 처리
    public function processUserActivity($member_srl, $activity_data)
    {
        // 트랜잭션 시작
        $oDB = DB::getInstance();
        $oDB->begin();

        try {
            // 사용자 활동 로그 삽입
            $log_args = new stdClass();
            $log_args->member_srl = $member_srl;
            $log_args->activity_type = $activity_data['type'];
            $log_args->activity_data = serialize($activity_data);
            $log_args->regdate = date('YmdHis');

            $output = executeQuery('addon.insertActivityLog', $log_args);
            if(!$output->toBool()) {
                throw new Exception($output->getMessage());
            }

            // 사용자 통계 업데이트
            $stat_args = new stdClass();
            $stat_args->member_srl = $member_srl;
            $stat_args->activity_count = 1;
            $stat_args->last_activity = date('YmdHis');

            $output = executeQuery('addon.updateUserStatistics', $stat_args);
            if(!$output->toBool()) {
                throw new Exception($output->getMessage());
            }

            // 포인트 지급
            if($activity_data['points'] > 0) {
                $point_args = new stdClass();
                $point_args->member_srl = $member_srl;
                $point_args->point = $activity_data['points'];
                $point_args->content = $activity_data['point_desc'];

                $output = executeQuery('point.insertPoint', $point_args);
                if(!$output->toBool()) {
                    throw new Exception($output->getMessage());
                }
            }

            $oDB->commit();
            return true;

        } catch(Exception $e) {
            $oDB->rollback();
            throw $e;
        }
    }

    // 캐시를 활용한 쿼리
    public function getCachedPopularDocuments($module_srl, $days = 7)
    {
        $cache_key = sprintf('popular_docs_%d_%d', $module_srl, $days);

        // 캐시에서 조회
        $cached_data = Rhymix\Framework\Cache::get($cache_key);
        if($cached_data !== null) {
            return $cached_data;
        }

        // 캐시에 없으면 DB에서 조회
        $args = new stdClass();
        $args->module_srl = $module_srl;
        $args->list_count = 10;
        $args->start_date = date('YmdHis', strtotime("-{$days} days"));

        $output = executeQuery('document.getPopularDocuments', $args);

        if($output->toBool() && $output->data) {
            // 1시간 캐시
            Rhymix\Framework\Cache::set($cache_key, $output->data, 3600);
            return $output->data;
        }

        return array();
    }
}

// 사용 예제
try {
    $db_access = new ExternalDatabaseAccess();

    // 문서 목록 조회
    $documents = $db_access->getDocumentsByModule(123, 5);

    // 통계 조회
    $stats = $db_access->getDocumentStatistics(123);

    // 결과 출력
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(array(
        'documents' => $documents,
        'statistics' => $stats
    ), JSON_UNESCAPED_UNICODE);

} catch(Exception $e) {
    header('HTTP/1.1 500 Internal Server Error');
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(array(
        'error' => $e->getMessage()
    ), JSON_UNESCAPED_UNICODE);
}
?>

쿼리 최적화¶

성능 최적화 기법¶

<?php
// 데이터베이스 성능 최적화
class OptimizedDatabaseAccess
{
    // 페이징 최적화
    public function getOptimizedDocumentList($args)
    {
        // LIMIT OFFSET 대신 커서 기반 페이징
        if(isset($args->last_document_srl)) {
            $args->document_srl_less = $args->last_document_srl;
            unset($args->page);
        }

        return executeQuery('document.getDocumentListByCursor', $args);
    }

    // 인덱스 힌트 사용
    public function getDocumentsWithIndexHint($module_srl)
    {
        $query = "
            SELECT /*+ USE INDEX (idx_module_regdate) */ *
            FROM xe_documents 
            WHERE module_srl = %d 
                AND status = 'PUBLIC'
            ORDER BY regdate DESC 
            LIMIT 20
        ";

        return executeQueryArray('', $query, $module_srl);
    }

    // 배치 처리
    public function batchUpdateDocuments($document_srls, $update_data)
    {
        $batch_size = 100;
        $batches = array_chunk($document_srls, $batch_size);

        foreach($batches as $batch) {
            $args = new stdClass();
            $args->document_srls = implode(',', $batch);
            $args->update_data = $update_data;

            $output = executeQuery('document.batchUpdateDocuments', $args);
            if(!$output->toBool()) {
                throw new Exception($output->getMessage());
            }
        }
    }

    // 메모리 사용량 최적화
    public function processLargeDataset($module_srl)
    {
        $offset = 0;
        $limit = 1000;

        do {
            $args = new stdClass();
            $args->module_srl = $module_srl;
            $args->offset = $offset;
            $args->limit = $limit;

            $output = executeQuery('document.getDocumentBatch', $args);

            if($output->toBool() && $output->data) {
                foreach($output->data as $document) {
                    // 문서 처리
                    $this->processDocument($document);

                    // 메모리 해제
                    unset($document);
                }

                $offset += $limit;
            } else {
                break;
            }

            // 가비지 컬렉션 강제 실행
            if($offset % 10000 == 0) {
                gc_collect_cycles();
            }

        } while(count($output->data) == $limit);
    }
}

모범 사례¶

1. 보안: Prepared Statement 사용
2. 성능: 적절한 인덱스와 쿼리 최적화
3. 트랜잭션: 데이터 일관성 보장
4. 캐시: 반복 쿼리 결과 캐싱
5. 에러 처리: 예외 상황 적절히 처리

다음 단계¶

데이터베이스 접근을 마스터했다면, 현재 접속자 표시를 학습하세요.