SQLite 데이터베이스 파일을 VisuaLeaf에서 열면 테이블을 살펴보고 SQL로 필요한 정보를 조회할 수 있다. 이 안내서는 검사 데이터베이스를 예로 들어 테이블 탐색, 여러 테이블 조인, JSON 값 추출, 엑셀 내보내기와 차트 만들기를 설명한다.
SQLite는 데이터를 데이터베이스 파일에 저장하므로 별도 서버를 설정하지 않아도 된다. 파일 안의 데이터는 행과 열로 구성된 테이블에 담긴다. 예시 데이터베이스에서는 inspections가 검사 기록을, sites가 검사 장소를, technicians가 검사 담당자를, inspection_items가 개별 점검 항목의 결과를 저장한다. 한 번의 검사 기록은 여러 점검 항목과 연결될 수 있다.
파일 확장자는 보통 .db, .sqlite, .sqlite3다. VisuaLeaf에서 SQLite 연결을 만들고 기존 파일을 선택한 뒤 연결한다. 사이드바에서 main과 Tables를 펼치고 테이블을 열면 기록을 볼 수 있다. main은 해당 연결에서 기본 데이터베이스를 가리키는 이름이다. 새 파일이 필요하면 연결 화면에서 New DB를 선택할 수 있지만, 새 데이터베이스는 비어 있어 직접 테이블과 데이터를 추가해야 한다.
테이블을 열어 행을 살펴보거나 SQL Editor에서 다음과 같이 필요한 열을 조회할 수 있다.
SELECT id, status, risk_score FROM inspections ORDER BY id LIMIT 10;
이 쿼리는 inspections에서 세 열을 가져와 ID 오름차순으로 정렬하고 최대 10개 행을 반환한다. 데이터를 읽기만 하며 기록을 바꾸지는 않는다. id는 각 검사 기록을 식별하는 기본 키다. 필터를 만들기 전에는 실제 status 값이 무엇인지 먼저 확인해야 한다. 열 이름만 보고 complete나 completed 중 어떤 값이 쓰이는지 짐작해서는 안 된다.
VisuaLeaf의 Table Management에서는 열의 자료형, 결측값 허용 여부(NULL), 기본값을 볼 수 있다. 예시의 INTEGER는 정수, TEXT는 문자열, REAL은 소수점이 있는 수를 나타낸다. active의 기본값이 1이면 새 행을 추가할 때 해당 값을 지정하지 않은 경우 SQLite가 1을 넣는다. 기본값만으로 다른 값의 입력까지 막지는 않는다. 0과 1만 허용하려면 CHECK (active IN (0, 1)) 같은 제약 조건이 필요하며, NOT NULL 규칙은 값이 비어 있는 입력을 거부한다.
Show DDL을 선택하면 테이블 정의 SQL에서 자료형, 기본 키와 외래 키, NOT NULL, 기본값 등을 확인할 수 있다. 인덱스는 특정 값을 찾을 때 모든 행을 읽지 않도록 도울 수 있는 조회 구조지만, 인덱스가 있다고 해서 열이 다른 테이블과 자동으로 연결되는 것은 아니다. 또한 일반 테이블에서는 INTEGER로 표시된 열에도 텍스트가 들어갈 수 있다. 선언된 자료형은 저장하려는 데이터 유형을 나타내지만 항상 이를 강제하지는 않는다.
inspections.site_id는 sites.id를, inspections.technician_id는 technicians의 ID를 가리킨다. 기본 키는 특정 행을 식별하고, 외래 키는 한 테이블의 값과 다른 테이블의 키를 연결하는 규칙이다. VisuaLeaf의 ER 다이어그램에서는 테이블 사이의 연결을 선으로 볼 수 있다.
다음 쿼리는 검사 기록에 장소와 담당자 이름을 붙인다.
SELECT inspections.id, sites.name AS site, technicians.full_name AS technician, inspections.risk_score FROM inspections LEFT JOIN sites ON inspections.site_id = sites.id LEFT JOIN technicians ON inspections.technician_id = technicians.id ORDER BY inspections.id LIMIT 10;
LEFT JOIN은 연결되는 장소나 담당자 기록이 없어도 검사 행을 결과에 남기며, 해당 정보는 NULL로 표시한다. 일반 JOIN은 일치하는 행이 없는 검사를 제외한다. 다만 이름이 NULL이라는 사실만으로 연결된 기록이 없다고 단정할 수는 없다. 대응하는 ID를 확인해야 한다. SQL을 쓰지 않으려면 VisuaLeaf Query Builder에서 테이블과 연결 열을 선택하고 결과에 포함할 필드를 지정할 수 있다.
SQLite에 외래 키가 정의돼 있어도 검사가 꺼져 있을 수 있다. 현재 연결의 설정은 다음 명령으로 확인한다.
PRAGMA foreign_keys;
결과가 1이면 검사가 켜져 있고 0이면 꺼져 있다. 활성 트랜잭션 밖에서 변경을 하기 전에 다음 명령으로 켤 수 있다.
PRAGMA foreign_keys = ON;
이 설정은 현재 연결에만 적용된다. 새로 잘못된 참조가 생기는 것을 막는 데 도움이 되지만 기존의 잘못된 참조를 고치지는 않는다.
inspections의 device_info 열에는 플랫폼, 앱 버전, 배터리 비율, 오프라인 기록 여부 같은 정보가 JSON으로 들어 있다. 먼저 원본 값을 확인할 수 있다.
SELECT id, device_info FROM inspections LIMIT 3;
JSON 안의 platform 값을 별도 열로 표시하려면 json_extract()를 쓴다.
SELECT i.id AS inspection_id, s.name AS site, json_extract(i.device_info, '$.platform') AS platform, json_extract(i.device_info, '$.app_version') AS app_version, json_extract(i.device_info, '$.battery_percent') AS battery, i.risk_score FROM inspections AS i JOIN sites AS s ON s.id = i.site_id WHERE json_extract(i.device_info, '$.offline') = 1 ORDER BY i.risk_score DESC LIMIT 12;
'$.platform'은 JSON에서 platform 값을 찾고, AS platform은 결과 열의 이름을 정한다. 이는 조회 결과에 열을 추가할 뿐 데이터베이스나 저장된 JSON을 바꾸지 않는다. 쿼리는 오프라인으로 기록된 검사를 골라 위험 점수가 높은 순으로 최대 12개 반환한다. SQLite는 JSON의 true 값을 1로 반환한다.
필요한 결과를 얻으면 쿼리를 엑셀 통합 문서로 내보낼 수 있다. 내보내기 화면에서 Source Type을 Query로, Target Type을 Excel Workbook으로 설정하고 SQLite 연결과 main 데이터베이스를 선택한다. Query 입력란의 SQL을 확인한 뒤 저장 위치와 파일 이름을 정하고 Export Now를 누른다. 내보낸 파일에는 쿼리 결과의 복사본이 담기므로 엑셀에서 수정해도 SQLite 데이터베이스는 바뀌지 않는다. 쿼리에 LIMIT 12가 있으면 최대 12개 행만 내보내며, 일치하는 모든 검사를 원하면 제한을 제거해야 한다.
점검 항목의 실패 건수를 범주별로 세려면 GROUP BY와 COUNT(*)를 사용할 수 있다.
SELECT issue_category, COUNT(*) AS failed_checks FROM inspection_items WHERE result = 'fail' GROUP BY issue_category ORDER BY failed_checks DESC LIMIT 8;
이 쿼리는 실패 건수가 많은 범주를 최대 8개 반환한다. VisuaLeaf Chart Builder에서 Pie를 선택하고 Category에 issue_category, Value에 failed_checks를 지정하면 결과에 포함된 실패 항목의 범주별 비중을 원형 차트로 표시한다. 집계 대상은 검사 건수가 아니라 점검 항목이다. 한 검사에 실패 항목이 여러 개 있을 수 있으며, 실패한 모든 범주를 포함하려면 LIMIT 8을 제거해야 한다.
댓글 (0)
로그인하면 이 기사에 내 생각을 남길 수 있어요