다른 명령
내컴퓨터 파일내용 검색기
주요 기능
검색 기능
텍스트 검색: 일반 텍스트 또는 정규표현식 SQL 객체 검색: 테이블, 프로시저, 함수, 패키지 등 컨텍스트 표시: 검색 결과 전후 라인 표시 대소문자 구분: 선택 가능 ripgrep 지원: 초고속 검색 (설치된 경우) 멀티스레딩으로 병렬 검색 다양한 SQL 확장자 지원 (.sql, .pls, .pck 등)
고급 기능
Whoosh 인덱싱: 전문 검색 엔진으로 빠른 검색 다중 스레드: 병렬 처리로 성능 향상 진행률 표시: 실시간 검색 진행 상황
결과 관리
테이블 뷰: 정렬 가능한 결과 목록 상세 뷰: 컨텍스트와 함께 코드 표시 내보내기: HTML, CSV, TXT 형식 지원 파일 열기: 결과에서 바로 파일 실행
설정
검색 디렉토리: 여러 디렉토리 동시 검색 파일 확장자: 커스텀 확장자 지원 성능 튜닝: 스레드 수 조절 YAML 설정: 설정 파일로 관리
실전 활용 시나리오
- 시나리오 1: 특정 테이블을 DELETE하는 모든 스크립트 찾기
results = searcher.search(r"\bDELETE\s+FROM\s+EMP\b", use_regex=True)
- 시나리오 2: DBMS_OUTPUT.PUT_LINE 사용하는 곳 찾기
results = searcher.search("DBMS_OUTPUT.PUT_LINE", case_sensitive=False)
- 시나리오 3: 특정 스키마의 객체 참조 찾기
results = searcher.search(r"\bHR\.\w+", use_regex=True)
- 시나리오 4: 커밋/롤백 문 찾기
results = searcher.search(r"\b(COMMIT|ROLLBACK)\b", use_regex=True)
프로그래밍
requirements.txt
- 프로젝트 디렉토리에 requirements.txt 파일을 만들면 편리합니다:
PyQt5==5.15.10 textPyQt5==5.15.10 tqdm==4.66.1 whoosh==2.7.4 PyYAML==6.0.1 ripgrepy==2.0.0 pip install -r requirements.txt
config.yaml (설정 파일)
# SQL Script Searcher 설정 파일 search_directories: - "C:/oracle/scripts" - "C:/work/sql" - "D:/projects/database" sql_extensions: - ".sql" - ".pls" - ".pck" - ".pkb" - ".pks" - ".trg" - ".fnc" - ".prc" search_settings: max_workers: 4 default_context_lines: 2 case_sensitive: false use_regex: false ui_settings: window_width: 1200 window_height: 800 recent_searches_count: 10 index_settings: enable_indexing: true index_directory: "./search_index" auto_update_index: false
sql_searcher_core.py (검색 엔진 코어)
import os
import re
from pathlib import Path
from concurrent.futures import ThreadPoolExecutor
from typing import List, Dict, Tuple, Optional
from dataclasses import dataclass
from datetime import datetime
from tqdm import tqdm
import yaml
try:
from ripgrepy import Ripgrepy
RIPGREP_AVAILABLE = True
except ImportError:
RIPGREP_AVAILABLE = False
try:
from whoosh.index import create_in, open_dir, exists_in
from whoosh.fields import Schema, TEXT, ID, DATETIME, NUMERIC
from whoosh.qparser import QueryParser, MultifieldParser
from whoosh import highlight
WHOOSH_AVAILABLE = True
except ImportError:
WHOOSH_AVAILABLE = False
@dataclass
class SearchResult:
filepath: str
line_number: int
line_content: str
context_before: List[str]
context_after: List[str]
file_size: int
modified_date: datetime
score: float = 0.0
class ConfigManager:
"""설정 관리 클래스"""
def __init__(self, config_file: str = "config.yaml"):
self.config_file = config_file
self.config = self._load_config()
def _load_config(self) -> dict:
"""설정 파일 로드"""
if os.path.exists(self.config_file):
with open(self.config_file, 'r', encoding='utf-8') as f:
return yaml.safe_load(f)
else:
return self._get_default_config()
def _get_default_config(self) -> dict:
"""기본 설정 반환"""
return {
'search_directories': [],
'sql_extensions': ['.sql', '.pls', '.pck', '.pkb', '.pks', '.trg', '.fnc', '.prc'],
'search_settings': {
'max_workers': 4,
'default_context_lines': 2,
'case_sensitive': False,
'use_regex': False
},
'ui_settings': {
'window_width': 1200,
'window_height': 800,
'recent_searches_count': 10
},
'index_settings': {
'enable_indexing': True,
'index_directory': './search_index',
'auto_update_index': False
}
}
def save_config(self):
"""설정 파일 저장"""
with open(self.config_file, 'w', encoding='utf-8') as f:
yaml.dump(self.config, f, allow_unicode=True, default_flow_style=False)
def get(self, *keys):
"""중첩된 키로 설정값 가져오기"""
value = self.config
for key in keys:
value = value.get(key, {})
return value
def set(self, value, *keys):
"""중첩된 키로 설정값 설정하기"""
config = self.config
for key in keys[:-1]:
config = config.setdefault(key, {})
config[keys[-1]] = value
class WhooshIndexer:
"""Whoosh 인덱스 관리 클래스"""
def __init__(self, index_dir: str):
self.index_dir = index_dir
self.schema = Schema(
filepath=ID(stored=True, unique=True),
filename=TEXT(stored=True),
content=TEXT(stored=True),
modified=DATETIME(stored=True),
size=NUMERIC(stored=True)
)
self.ix = None
self._init_index()
def _init_index(self):
"""인덱스 초기화"""
if not WHOOSH_AVAILABLE:
return
if not os.path.exists(self.index_dir):
os.makedirs(self.index_dir)
if exists_in(self.index_dir):
self.ix = open_dir(self.index_dir)
else:
self.ix = create_in(self.index_dir, self.schema)
def index_files(self, files: List[Path], progress_callback=None):
"""파일들을 인덱싱"""
if not WHOOSH_AVAILABLE or self.ix is None:
return
writer = self.ix.writer()
for i, filepath in enumerate(files):
try:
with open(filepath, 'r', encoding='utf-8', errors='ignore') as f:
content = f.read()
stat = filepath.stat()
writer.update_document(
filepath=str(filepath),
filename=filepath.name,
content=content,
modified=datetime.fromtimestamp(stat.st_mtime),
size=stat.st_size
)
if progress_callback:
progress_callback(i + 1, len(files))
except Exception as e:
print(f"Error indexing {filepath}: {e}")
writer.commit()
def search(self, query_text: str, limit: int = 100) -> List[Dict]:
"""인덱스에서 검색"""
if not WHOOSH_AVAILABLE or self.ix is None:
return []
results = []
with self.ix.searcher() as searcher:
query = MultifieldParser(["filename", "content"], self.ix.schema).parse(query_text)
search_results = searcher.search(query, limit=limit)
for hit in search_results:
results.append({
'filepath': hit['filepath'],
'filename': hit['filename'],
'score': hit.score
})
return results
class SQLScriptSearcher:
"""SQL 스크립트 검색 엔진"""
def __init__(self, config_manager: ConfigManager):
self.config = config_manager
self.base_directories = self.config.get('search_directories')
self.sql_extensions = self.config.get('sql_extensions')
self.max_workers = self.config.get('search_settings', 'max_workers')
# Whoosh 인덱서 초기화
if self.config.get('index_settings', 'enable_indexing') and WHOOSH_AVAILABLE:
index_dir = self.config.get('index_settings', 'index_directory')
self.indexer = WhooshIndexer(index_dir)
else:
self.indexer = None
self.recent_searches = []
def get_all_sql_files(self) -> List[Path]:
"""모든 SQL 관련 파일 수집"""
files = []
for directory in self.base_directories:
if not os.path.exists(directory):
continue
for ext in self.sql_extensions:
files.extend(Path(directory).rglob(f'*{ext}'))
return files
def search(self,
search_text: str,
case_sensitive: bool = None,
use_regex: bool = None,
context_lines: int = None,
use_ripgrep: bool = False,
progress_callback=None) -> List[SearchResult]:
"""
SQL 스크립트 파일에서 텍스트 검색
"""
# 기본값 설정
if case_sensitive is None:
case_sensitive = self.config.get('search_settings', 'case_sensitive')
if use_regex is None:
use_regex = self.config.get('search_settings', 'use_regex')
if context_lines is None:
context_lines = self.config.get('search_settings', 'default_context_lines')
# 검색 기록 추가
self._add_to_recent_searches(search_text)
# ripgrep 사용 가능하고 요청된 경우
if use_ripgrep and RIPGREP_AVAILABLE:
return self._search_with_ripgrep(search_text, case_sensitive, context_lines)
# 기본 검색
all_files = self.get_all_sql_files()
if progress_callback:
progress_callback(0, len(all_files))
with ThreadPoolExecutor(max_workers=self.max_workers) as executor:
futures = []
for filepath in all_files:
future = executor.submit(
self._search_in_file,
filepath,
search_text,
case_sensitive,
use_regex,
context_lines
)
futures.append(future)
results = []
for i, future in enumerate(futures):
file_results = future.result()
results.extend(file_results)
if progress_callback:
progress_callback(i + 1, len(all_files))
return sorted(results, key=lambda x: (x.filepath, x.line_number))
def _search_in_file(self,
filepath: Path,
search_text: str,
case_sensitive: bool,
use_regex: bool,
context_lines: int) -> List[SearchResult]:
"""단일 파일 내 검색"""
results = []
try:
stat = filepath.stat()
file_size = stat.st_size
modified_date = datetime.fromtimestamp(stat.st_mtime)
with open(filepath, 'r', encoding='utf-8', errors='ignore') as f:
lines = f.readlines()
if use_regex:
flags = 0 if case_sensitive else re.IGNORECASE
pattern = re.compile(search_text, flags)
else:
if not case_sensitive:
search_text = search_text.lower()
for i, line in enumerate(lines):
match_found = False
if use_regex:
match_found = pattern.search(line) is not None
else:
compare_line = line if case_sensitive else line.lower()
match_found = search_text in compare_line
if match_found:
start_idx = max(0, i - context_lines)
end_idx = min(len(lines), i + context_lines + 1)
context_before = [lines[j].rstrip() for j in range(start_idx, i)]
context_after = [lines[j].rstrip() for j in range(i + 1, end_idx)]
results.append(SearchResult(
filepath=str(filepath),
line_number=i + 1,
line_content=line.rstrip(),
context_before=context_before,
context_after=context_after,
file_size=file_size,
modified_date=modified_date
))
except Exception as e:
pass
return results
def _search_with_ripgrep(self, search_text: str, case_sensitive: bool, context_lines: int) -> List[SearchResult]:
"""ripgrep을 사용한 빠른 검색"""
results = []
for directory in self.base_directories:
if not os.path.exists(directory):
continue
rg = Ripgrepy(search_text, directory)
if not case_sensitive:
rg = rg.ignore_case()
rg = rg.with_filename().line_number()
if context_lines > 0:
rg = rg.context(context_lines)
for ext in self.sql_extensions:
rg = rg.glob(f'*{ext}')
try:
for match in rg.run().as_dict:
filepath = Path(match['path'])
stat = filepath.stat()
results.append(SearchResult(
filepath=str(filepath),
line_number=match.get('line_number', 0),
line_content=match.get('line', ''),
context_before=[],
context_after=[],
file_size=stat.st_size,
modified_date=datetime.fromtimestamp(stat.st_mtime)
))
except Exception as e:
print(f"Ripgrep error in {directory}: {e}")
return results
def search_sql_objects(self,
object_name: str,
object_types: List[str] = None,
progress_callback=None) -> List[SearchResult]:
"""SQL 객체(테이블, 프로시저, 함수 등) 검색"""
if object_types is None:
object_types = ['TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER']
patterns = []
for obj_type in object_types:
if obj_type == 'TABLE':
patterns.extend([
rf'\bFROM\s+{object_name}\b',
rf'\bJOIN\s+{object_name}\b',
rf'\bINTO\s+{object_name}\b',
rf'\bUPDATE\s+{object_name}\b',
rf'\bDELETE\s+FROM\s+{object_name}\b'
])
elif obj_type == 'PROCEDURE':
patterns.extend([
rf'\bEXEC(UTE)?\s+{object_name}\b',
rf'\bCREATE\s+(OR\s+REPLACE\s+)?PROCEDURE\s+{object_name}\b'
])
elif obj_type == 'FUNCTION':
patterns.extend([
rf'\b{object_name}\s*\(',
rf'\bCREATE\s+(OR\s+REPLACE\s+)?FUNCTION\s+{object_name}\b'
])
elif obj_type == 'PACKAGE':
patterns.extend([
rf'\b{object_name}\.',
rf'\bCREATE\s+(OR\s+REPLACE\s+)?PACKAGE\s+{object_name}\b'
])
elif obj_type == 'VIEW':
patterns.extend([
rf'\bFROM\s+{object_name}\b',
rf'\bCREATE\s+(OR\s+REPLACE\s+)?VIEW\s+{object_name}\b'
])
elif obj_type == 'TRIGGER':
patterns.append(rf'\bCREATE\s+(OR\s+REPLACE\s+)?TRIGGER\s+{object_name}\b')
combined_pattern = '|'.join(patterns)
return self.search(
combined_pattern,
case_sensitive=False,
use_regex=True,
progress_callback=progress_callback
)
def _add_to_recent_searches(self, search_text: str):
"""최근 검색어 추가"""
if search_text in self.recent_searches:
self.recent_searches.remove(search_text)
self.recent_searches.insert(0, search_text)
max_recent = self.config.get('ui_settings', 'recent_searches_count')
self.recent_searches = self.recent_searches[:max_recent]
def export_results(self, results: List[SearchResult], output_file: str, format: str = 'txt'):
"""검색 결과를 파일로 저장"""
if format == 'txt':
self._export_txt(results, output_file)
elif format == 'html':
self._export_html(results, output_file)
elif format == 'csv':
self._export_csv(results, output_file)
def _export_txt(self, results: List[SearchResult], output_file: str):
"""텍스트 형식으로 저장"""
with open(output_file, 'w', encoding='utf-8') as f:
f.write(f"검색 결과: {len(results)}건\n")
f.write("=" * 80 + "\n\n")
for result in results:
f.write(f"파일: {result.filepath}\n")
f.write(f"라인: {result.line_number}\n")
f.write(f"수정일: {result.modified_date.strftime('%Y-%m-%d %H:%M:%S')}\n")
f.write("-" * 80 + "\n")
for i, line in enumerate(result.context_before):
f.write(f" {result.line_number - len(result.context_before) + i}: {line}\n")
f.write(f">>>{result.line_number}: {result.line_content}\n")
for i, line in enumerate(result.context_after):
f.write(f" {result.line_number + i + 1}: {line}\n")
f.write("\n" + "=" * 80 + "\n\n")
def _export_csv(self, results: List[SearchResult], output_file: str):
"""CSV 형식으로 저장"""
import csv
with open(output_file, 'w', encoding='utf-8-sig', newline='') as f:
writer = csv.writer(f)
writer.writerow(['파일경로', '라인번호', '내용', '파일크기(bytes)', '수정일'])
for result in results:
writer.writerow([
result.filepath,
result.line_number,
result.line_content,
result.file_size,
result.modified_date.strftime('%Y-%m-%d %H:%M:%S')
])
def _export_html(self, results: List[SearchResult], output_file: str):
"""HTML 형식으로 저장"""
html = f"""<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>SQL 스크립트 검색 결과</title>
<style>
body {{ font-family: 'Consolas', 'Courier New', monospace; margin: 20px; background: #f5f5f5; }}
h1 {{ color: #333; border-bottom: 3px solid #0066cc; padding-bottom: 10px; }}
.result {{ border: 1px solid #ddd; margin: 15px 0; padding: 15px; background: white; border-radius: 5px; }}
.filepath {{ font-weight: bold; color: #0066cc; font-size: 14px; }}
.meta {{ color: #666; font-size: 12px; margin: 5px 0; }}
.line-number {{ color: #999; display: inline-block; width: 50px; text-align: right; margin-right: 10px; }}
.matched-line {{ background: #ffff99; font-weight: bold; padding: 2px 0; }}
.context {{ color: #333; }}
pre {{ margin: 10px 0; line-height: 1.4; }}
</style>
</head>
<body>
<h1>SQL 스크립트 검색 결과 ({len(results)} 건)</h1>
"""
for result in results:
html += f""" <div class="result">
<div class="filepath">📄 {result.filepath}</div>
<div class="meta">라인: {result.line_number} | 크기: {result.file_size:,} bytes | 수정일: {result.modified_date.strftime('%Y-%m-%d %H:%M:%S')}</div>
<pre class="context">"""
for i, line in enumerate(result.context_before):
line_num = result.line_number - len(result.context_before) + i
html += f'<span class="line-number">{line_num}</span>{self._html_escape(line)}\n'
html += f'<span class="matched-line"><span class="line-number">{result.line_number}</span>{self._html_escape(result.line_content)}</span>\n'
for i, line in enumerate(result.context_after):
line_num = result.line_number + i + 1
html += f'<span class="line-number">{line_num}</span>{self._html_escape(line)}\n'
html += """</pre>
</div>
"""
html += """</body>
</html>"""
with open(output_file, 'w', encoding='utf-8') as f:
f.write(html)
def _html_escape(self, text: str) -> str:
"""HTML 특수문자 이스케이프"""
return (text.replace('&', '&')
.replace('<', '<')
.replace('>', '>')
.replace('"', '"')
.replace("'", '''))
import sys
import os
from pathlib import Path
from datetime import datetime
from typing import List
from PyQt5.QtWidgets import (
QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout,
QPushButton, QLineEdit, QTextEdit, QFileDialog, QCheckBox,
QLabel, QSpinBox, QComboBox, QTableWidget, QTableWidgetItem,
QProgressBar, QSplitter, QGroupBox, QMessageBox, QTabWidget,
QHeaderView, QMenu, QAction
)
from PyQt5.QtCore import Qt, QThread, pyqtSignal, QTimer
from PyQt5.QtGui import QFont, QColor, QTextCursor, QIcon
from sql_searcher_core import (
SQLScriptSearcher, ConfigManager, SearchResult,
RIPGREP_AVAILABLE, WHOOSH_AVAILABLE
)
class SearchThread(QThread):
"""검색 작업을 별도 스레드에서 실행"""
progress = pyqtSignal(int, int) # current, total
finished = pyqtSignal(list) # results
error = pyqtSignal(str) # error message
def __init__(self, searcher, search_params):
super().__init__()
self.searcher = searcher
self.search_params = search_params
def run(self):
try:
results = self.searcher.search(
**self.search_params,
progress_callback=self.progress.emit
)
self.finished.emit(results)
except Exception as e:
self.error.emit(str(e))
class IndexingThread(QThread):
"""인덱싱 작업을 별도 스레드에서 실행"""
progress = pyqtSignal(int, int)
finished = pyqtSignal()
error = pyqtSignal(str)
def __init__(self, searcher):
super().__init__()
self.searcher = searcher
def run(self):
try:
if self.searcher.indexer:
files = self.searcher.get_all_sql_files()
self.searcher.indexer.index_files(files, self.progress.emit)
self.finished.emit()
except Exception as e:
self.error.emit(str(e))
class SQLSearcherGUI(QMainWindow):
"""SQL 스크립트 검색기 메인 GUI"""
def __init__(self):
super().__init__()
# 설정 및 검색 엔진 초기화
self.config = ConfigManager()
self.searcher = SQLScriptSearcher(self.config)
self.current_results = []
self.search_thread = None
self.init_ui()
self.load_settings()
def init_ui(self):
"""UI 초기화"""
self.setWindowTitle("SQL Script Searcher - Professional Edition")
# 창 크기 설정
width = self.config.get('ui_settings', 'window_width')
height = self.config.get('ui_settings', 'window_height')
self.resize(width, height)
# 중앙 위젯
central_widget = QWidget()
self.setCentralWidget(central_widget)
# 메인 레이아웃
main_layout = QVBoxLayout(central_widget)
# 탭 위젯 생성
self.tabs = QTabWidget()
main_layout.addWidget(self.tabs)
# 검색 탭
search_tab = self.create_search_tab()
self.tabs.addTab(search_tab, "🔍 검색")
# 설정 탭
settings_tab = self.create_settings_tab()
self.tabs.addTab(settings_tab, "⚙️ 설정")
# 인덱스 탭
if WHOOSH_AVAILABLE:
index_tab = self.create_index_tab()
self.tabs.addTab(index_tab, "📇 인덱스")
# 상태바
self.statusBar().showMessage("준비")
# 메뉴바
self.create_menu_bar()
def create_menu_bar(self):
"""메뉴바 생성"""
menubar = self.menuBar()
# 파일 메뉴
file_menu = menubar.addMenu("파일")
export_action = QAction("결과 내보내기", self)
export_action.setShortcut("Ctrl+E")
export_action.triggered.connect(self.export_results)
file_menu.addAction(export_action)
file_menu.addSeparator()
exit_action = QAction("종료", self)
exit_action.setShortcut("Ctrl+Q")
exit_action.triggered.connect(self.close)
file_menu.addAction(exit_action)
# 도구 메뉴
tools_menu = menubar.addMenu("도구")
refresh_action = QAction("파일 목록 새로고침", self)
refresh_action.setShortcut("F5")
refresh_action.triggered.connect(self.refresh_file_list)
tools_menu.addAction(refresh_action)
if WHOOSH_AVAILABLE:
index_action = QAction("인덱스 재구성", self)
index_action.triggered.connect(self.rebuild_index)
tools_menu.addAction(index_action)
# 도움말 메뉴
help_menu = menubar.addMenu("도움말")
about_action = QAction("정보", self)
about_action.triggered.connect(self.show_about)
help_menu.addAction(about_action)
def create_search_tab(self):
"""검색 탭 생성"""
widget = QWidget()
layout = QVBoxLayout(widget)
# 검색 입력 영역
search_group = QGroupBox("검색 조건")
search_layout = QVBoxLayout()
# 검색어 입력
input_layout = QHBoxLayout()
input_layout.addWidget(QLabel("검색어:"))
self.search_input = QComboBox()
self.search_input.setEditable(True)
self.search_input.setMinimumWidth(400)
self.search_input.lineEdit().returnPressed.connect(self.start_search)
input_layout.addWidget(self.search_input, 1)
self.search_button = QPushButton("🔍 검색")
self.search_button.clicked.connect(self.start_search)
self.search_button.setMinimumWidth(100)
input_layout.addWidget(self.search_button)
search_layout.addLayout(input_layout)
# 검색 옵션
options_layout = QHBoxLayout()
self.case_sensitive_cb = QCheckBox("대소문자 구분")
options_layout.addWidget(self.case_sensitive_cb)
self.regex_cb = QCheckBox("정규표현식")
options_layout.addWidget(self.regex_cb)
if RIPGREP_AVAILABLE:
self.ripgrep_cb = QCheckBox("ripgrep 사용")
options_layout.addWidget(self.ripgrep_cb)
options_layout.addWidget(QLabel("컨텍스트 라인:"))
self.context_spin = QSpinBox()
self.context_spin.setRange(0, 10)
self.context_spin.setValue(2)
options_layout.addWidget(self.context_spin)
options_layout.addStretch()
search_layout.addLayout(options_layout)
search_group.setLayout(search_layout)
layout.addWidget(search_group)
# SQL 객체 검색
object_group = QGroupBox("SQL 객체 검색")
object_layout = QHBoxLayout()
object_layout.addWidget(QLabel("객체명:"))
self.object_name_input = QLineEdit()
object_layout.addWidget(self.object_name_input, 1)
object_layout.addWidget(QLabel("타입:"))
self.object_type_combo = QComboBox()
self.object_type_combo.addItems([
"모두", "TABLE", "VIEW", "PROCEDURE",
"FUNCTION", "PACKAGE", "TRIGGER"
])
object_layout.addWidget(self.object_type_combo)
self.object_search_button = QPushButton("객체 검색")
self.object_search_button.clicked.connect(self.search_sql_object)
object_layout.addWidget(self.object_search_button)
object_group.setLayout(object_layout)
layout.addWidget(object_group)
# 진행률 바
self.progress_bar = QProgressBar()
self.progress_bar.setVisible(False)
layout.addWidget(self.progress_bar)
# 결과 영역 (Splitter 사용)
splitter = QSplitter(Qt.Vertical)
# 결과 테이블
self.result_table = QTableWidget()
self.result_table.setColumnCount(4)
self.result_table.setHorizontalHeaderLabels(["파일", "라인", "크기", "수정일"])
self.result_table.horizontalHeader().setSectionResizeMode(0, QHeaderView.Stretch)
self.result_table.setSelectionBehavior(QTableWidget.SelectRows)
self.result_table.setAlternatingRowColors(True)
self.result_table.itemSelectionChanged.connect(self.show_result_detail)
self.result_table.setContextMenuPolicy(Qt.CustomContextMenu)
self.result_table.customContextMenuRequested.connect(self.show_context_menu)
splitter.addWidget(self.result_table)
# 상세 내용 표시
detail_widget = QWidget()
detail_layout = QVBoxLayout(detail_widget)
detail_layout.setContentsMargins(0, 0, 0, 0)
self.detail_label = QLabel("검색 결과를 선택하세요")
detail_layout.addWidget(self.detail_label)
self.detail_text = QTextEdit()
self.detail_text.setReadOnly(True)
self.detail_text.setFont(QFont("Consolas", 10))
detail_layout.addWidget(self.detail_text)
splitter.addWidget(detail_widget)
splitter.setSizes([300, 400])
layout.addWidget(splitter, 1)
# 결과 요약
self.result_summary = QLabel("검색 결과: 0건")
layout.addWidget(self.result_summary)
return widget
def create_settings_tab(self):
"""설정 탭 생성"""
widget = QWidget()
layout = QVBoxLayout(widget)
# 검색 디렉토리 설정
dir_group = QGroupBox("검색 디렉토리")
dir_layout = QVBoxLayout()
self.dir_list = QTextEdit()
self.dir_list.setMaximumHeight(150)
dirs = self.config.get('search_directories')
self.dir_list.setPlainText('\n'.join(dirs))
dir_layout.addWidget(self.dir_list)
dir_button_layout = QHBoxLayout()
add_dir_button = QPushButton("디렉토리 추가")
add_dir_button.clicked.connect(self.add_directory)
dir_button_layout.addWidget(add_dir_button)
clear_dir_button = QPushButton("목록 지우기")
clear_dir_button.clicked.connect(lambda: self.dir_list.clear())
dir_button_layout.addWidget(clear_dir_button)
dir_button_layout.addStretch()
dir_layout.addLayout(dir_button_layout)
dir_group.setLayout(dir_layout)
layout.addWidget(dir_group)
# 파일 확장자 설정
ext_group = QGroupBox("검색 대상 확장자")
ext_layout = QVBoxLayout()
self.ext_list = QTextEdit()
self.ext_list.setMaximumHeight(100)
exts = self.config.get('sql_extensions')
self.ext_list.setPlainText('\n'.join(exts))
ext_layout.addWidget(self.ext_list)
ext_group.setLayout(ext_layout)
layout.addWidget(ext_group)
# 성능 설정
perf_group = QGroupBox("성능 설정")
perf_layout = QHBoxLayout()
perf_layout.addWidget(QLabel("동시 작업 스레드:"))
self.workers_spin = QSpinBox()
self.workers_spin.setRange(1, 16)
self.workers_spin.setValue(self.config.get('search_settings', 'max_workers'))
perf_layout.addWidget(self.workers_spin)
perf_layout.addStretch()
perf_group.setLayout(perf_layout)
layout.addWidget(perf_group)
# 시스템 정보
info_group = QGroupBox("시스템 정보")
info_layout = QVBoxLayout()
info_text = f"""Python: {sys.version.split()[0]}
PyQt5: {from PyQt5.QtCore import QT_VERSION_STR; QT_VERSION_STR}
ripgrep: {'사용 가능' if RIPGREP_AVAILABLE else '사용 불가'}
Whoosh: {'사용 가능' if WHOOSH_AVAILABLE else '사용 불가'}"""
info_label = QLabel(info_text)
info_label.setFont(QFont("Consolas", 9))
info_layout.addWidget(info_label)
info_group.setLayout(info_layout)
layout.addWidget(info_group)
# 저장 버튼
save_layout = QHBoxLayout()
save_layout.addStretch()
save_button = QPushButton("💾 설정 저장")
save_button.clicked.connect(self.save_settings)
save_button.setMinimumWidth(150)
save_layout.addWidget(save_button)
layout.addLayout(save_layout)
layout.addStretch()
return widget
def create_index_tab(self):
"""인덱스 탭 생성"""
widget = QWidget()
layout = QVBoxLayout(widget)
info_label = QLabel("Whoosh 전문 검색 엔진을 사용한 빠른 검색\n"
"파일을 인덱싱하면 검색 속도가 크게 향상됩니다.")
layout.addWidget(info_label)
# 인덱스 상태
status_group = QGroupBox("인덱스 상태")
status_layout = QVBoxLayout()
self.index_status_label = QLabel("인덱스 정보를 불러오는 중...")
status_layout.addWidget(self.index_status_label)
status_group.setLayout(status_layout)
layout.addWidget(status_group)
# 인덱스 작업
action_group = QGroupBox("인덱스 작업")
action_layout = QVBoxLayout()
self.index_progress = QProgressBar()
self.index_progress.setVisible(False)
action_layout.addWidget(self.index_progress)
button_layout = QHBoxLayout()
rebuild_button = QPushButton("🔄 인덱스 재구성")
rebuild_button.clicked.connect(self.rebuild_index)
button_layout.addWidget(rebuild_button)
button_layout.addStretch()
action_layout.addLayout(button_layout)
action_group.setLayout(action_layout)
layout.addWidget(action_group)
layout.addStretch()
# 인덱스 정보 업데이트
QTimer.singleShot(500, self.update_index_status)
return widget
def start_search(self):
"""검색 시작"""
search_text = self.search_input.currentText().strip()
if not search_text:
QMessageBox.warning(self, "검색", "검색어를 입력하세요.")
return
# 검색 버튼 비활성화
self.search_button.setEnabled(False)
self.object_search_button.setEnabled(False)
# 진행률 바 표시
self.progress_bar.setVisible(True)
self.progress_bar.setValue(0)
# 상태바 업데이트
self.statusBar().showMessage("검색 중...")
# 검색 파라미터
search_params = {
'search_text': search_text,
'case_sensitive': self.case_sensitive_cb.isChecked(),
'use_regex': self.regex_cb.isChecked(),
'context_lines': self.context_spin.value(),
'use_ripgrep': self.ripgrep_cb.isChecked() if RIPGREP_AVAILABLE else False
}
# 검색 스레드 시작
self.search_thread = SearchThread(self.searcher, search_params)
self.search_thread.progress.connect(self.update_progress)
self.search_thread.finished.connect(self.search_finished)
self.search_thread.error.connect(self.search_error)
self.search_thread.start()
def search_sql_object(self):
"""SQL 객체 검색"""
object_name = self.object_name_input.text().strip()
if not object_name:
QMessageBox.warning(self, "객체 검색", "객체명을 입력하세요.")
return
# 검색 버튼 비활성화
self.search_button.setEnabled(False)
self.object_search_button.setEnabled(False)
# 진행률 바 표시
self.progress_bar.setVisible(True)
self.progress_bar.setValue(0)
# 상태바 업데이트
self.statusBar().showMessage(f"'{object_name}' 객체 검색 중...")
# 객체 타입
object_type = self.object_type_combo.currentText()
object_types = None if object_type == "모두" else [object_type]
# 별도 스레드에서 실행
from threading import Thread
def search_worker():
try:
results = self.searcher.search_sql_objects(
object_name,
object_types,
progress_callback=lambda c, t: self.progress_bar.setValue(int(c/t*100))
)
self.search_finished(results)
except Exception as e:
self.search_error(str(e))
thread = Thread(target=search_worker, daemon=True)
thread.start()
def update_progress(self, current, total):
"""진행률 업데이트"""
if total > 0:
percentage = int((current / total) * 100)
self.progress_bar.setValue(percentage)
self.statusBar().showMessage(f"검색 중... {current}/{total} 파일")
def search_finished(self, results):
"""검색 완료"""
self.current_results = results
# UI 업데이트
self.search_button.setEnabled(True)
self.object_search_button.setEnabled(True)
self.progress_bar.setVisible(False)
# 결과 표시
self.display_results(results)
# 상태바
self.statusBar().showMessage(f"검색 완료: {len(results)}건 발견", 5000)
# 최근 검색어 업데이트
current_text = self.search_input.currentText()
if self.search_input.findText(current_text) == -1:
self.search_input.addItem(current_text)
def search_error(self, error_msg):
"""검색 오류"""
self.search_button.setEnabled(True)
self.object_search_button.setEnabled(True)
self.progress_bar.setVisible(False)
QMessageBox.critical(self, "검색 오류", f"검색 중 오류가 발생했습니다:\n{error_msg}")
self.statusBar().showMessage("검색 실패", 5000)
def display_results(self, results: List[SearchResult]):
"""검색 결과 표시"""
self.result_table.setRowCount(0)
for result in results:
row = self.result_table.rowCount()
self.result_table.insertRow(row)
# 파일명 (전체 경로의 마지막 부분만)
filename = Path(result.filepath).name
self.result_table.setItem(row, 0, QTableWidgetItem(filename))
# 라인 번호
self.result_table.setItem(row, 1, QTableWidgetItem(str(result.line_number)))
# 파일 크기
size_kb = result.file_size / 1024
size_str = f"{size_kb:.1f} KB" if size_kb < 1024 else f"{size_kb/1024:.1f} MB"
self.result_table.setItem(row, 2, QTableWidgetItem(size_str))
# 수정일
date_str = result.modified_date.strftime('%Y-%m-%d %H:%M')
self.result_table.setItem(row, 3, QTableWidgetItem(date_str))
# 요약 업데이트
self.result_summary.setText(f"검색 결과: {len(results)}건")
# 첫 번째 결과 선택
if results:
self.result_table.selectRow(0)
def show_result_detail(self):
"""선택된 결과의 상세 내용 표시"""
selected_rows = self.result_table.selectionModel().selectedRows()
if not selected_rows or not self.current_results:
return
row = selected_rows[0].row()
if row >= len(self.current_results):
return
result = self.current_results[row]
# 상세 정보 레이블
self.detail_label.setText(
f"📄 {result.filepath} (라인 {result.line_number})"
)
# 내용 표시
content = []
# 컨텍스트 이전
for i, line in enumerate(result.context_before):
line_num = result.line_number - len(result.context_before) + i
content.append(f"{line_num:5d} | {line}")
# 매칭된 라인 (강조)
content.append(f"{result.line_number:5d} >>> {result.line_content}")
# 컨텍스트 이후
for i, line in enumerate(result.context_after):
line_num = result.line_number + i + 1
content.append(f"{line_num:5d} | {line}")
self.detail_text.setPlainText('\n'.join(content))
# 매칭된 라인으로 스크롤
cursor = self.detail_text.textCursor()
cursor.movePosition(QTextCursor.Start)
for _ in range(len(result.context_before)):
cursor.movePosition(QTextCursor.Down)
self.detail_text.setTextCursor(cursor)
def show_context_menu(self, position):
"""컨텍스트 메뉴 표시"""
if not self.result_table.selectionModel().selectedRows():
return
menu = QMenu()
open_file_action = QAction("파일 열기", self)
open_file_action.triggered.connect(self.open_selected_file)
menu.addAction(open_file_action)
copy_path_action = QAction("경로 복사", self)
copy_path_action.triggered.connect(self.copy_file_path)
menu.addAction(copy_path_action)
menu.addSeparator()
export_action = QAction("선택 항목 내보내기", self)
export_action.triggered.connect(self.export_selected)
menu.addAction(export_action)
menu.exec_(self.result_table.viewport().mapToGlobal(position))
def open_selected_file(self):
"""선택된 파일 열기"""
selected_rows = self.result_table.selectionModel().selectedRows()
if not selected_rows or not self.current_results:
return
row = selected_rows[0].row()
result = self.current_results[row]
# 기본 프로그램으로 파일 열기
import subprocess
import platform
if platform.system() == 'Windows':
os.startfile(result.filepath)
elif platform.system() == 'Darwin': # macOS
subprocess.call(['open', result.filepath])
else: # Linux
subprocess.call(['xdg-open', result.filepath])
def copy_file_path(self):
"""파일 경로 복사"""
selected_rows = self.result_table.selectionModel().selectedRows()
if not selected_rows or not self.current_results:
return
row = selected_rows[0].row()
result = self.current_results[row]
clipboard = QApplication.clipboard()
clipboard.setText(result.filepath)
self.statusBar().showMessage("경로가 클립보드에 복사되었습니다.", 3000)
def export_results(self):
"""전체 결과 내보내기"""
if not self.current_results:
QMessageBox.information(self, "내보내기", "내보낼 결과가 없습니다.")
return
# 파일 형식 선택
file_filter = "HTML 파일 (*.html);;CSV 파일 (*.csv);;텍스트 파일 (*.txt)"
filename, selected_filter = QFileDialog.getSaveFileName(
self, "결과 내보내기", "", file_filter
)
if not filename:
return
# 형식 결정
if 'HTML' in selected_filter:
format_type = 'html'
elif 'CSV' in selected_filter:
format_type = 'csv'
else:
format_type = 'txt'
try:
self.searcher.export_results(self.current_results, filename, format_type)
QMessageBox.information(self, "내보내기", f"결과가 {filename}에 저장되었습니다.")
self.statusBar().showMessage(f"파일 저장 완료: {filename}", 5000)
except Exception as e:
QMessageBox.critical(self, "내보내기 오류", f"파일 저장 중 오류:\n{str(e)}")
def export_selected(self):
"""선택된 항목만 내보내기"""
selected_rows = self.result_table.selectionModel().selectedRows()
if not selected_rows:
QMessageBox.information(self, "내보내기", "선택된 항목이 없습니다.")
return
selected_results = [self.current_results[row.row()] for row in selected_rows]
file_filter = "HTML 파일 (*.html);;CSV 파일 (*.csv);;텍스트 파일 (*.txt)"
filename, selected_filter = QFileDialog.getSaveFileName(
self, "선택 항목 내보내기", "", file_filter
)
if not filename:
return
if 'HTML' in selected_filter:
format_type = 'html'
elif 'CSV' in selected_filter:
format_type = 'csv'
else:
format_type = 'txt'
try:
self.searcher.export_results(selected_results, filename, format_type)
QMessageBox.information(self, "내보내기", f"{len(selected_results)}건이 저장되었습니다.")
except Exception as e:
QMessageBox.critical(self, "내보내기 오류", f"파일 저장 중 오류:\n{str(e)}")
def add_directory(self):
"""검색 디렉토리 추가"""
directory = QFileDialog.getExistingDirectory(self, "디렉토리 선택")
if directory:
current_text = self.dir_list.toPlainText()
if current_text:
self.dir_list.setPlainText(current_text + '\n' + directory)
else:
self.dir_list.setPlainText(directory)
def save_settings(self):
"""설정 저장"""
# 디렉토리 목록
dirs = [d.strip() for d in self.dir_list.toPlainText().split('\n') if d.strip()]
self.config.set(dirs, 'search_directories')
# 확장자 목록
exts = [e.strip() for e in self.ext_list.toPlainText().split('\n') if e.strip()]
self.config.set(exts, 'sql_extensions')
# 성능 설정
self.config.set(self.workers_spin.value(), 'search_settings', 'max_workers')
# 설정 파일 저장
self.config.save_config()
# 검색 엔진 재초기화
self.searcher = SQLScriptSearcher(self.config)
QMessageBox.information(self, "설정", "설정이 저장되었습니다.")
self.statusBar().showMessage("설정 저장 완료", 3000)
def load_settings(self):
"""설정 로드"""
# 최근 검색어 복원
for search_text in self.searcher.recent_searches:
self.search_input.addItem(search_text)
def refresh_file_list(self):
"""파일 목록 새로고침"""
files = self.searcher.get_all_sql_files()
count = len(files)
QMessageBox.information(self, "파일 목록", f"총 {count}개의 SQL 파일을 찾았습니다.")
self.statusBar().showMessage(f"파일 목록 새로고침 완료: {count}개", 5000)
def rebuild_index(self):
"""인덱스 재구성"""
if not WHOOSH_AVAILABLE or not self.searcher.indexer:
QMessageBox.warning(self, "인덱스", "Whoosh 인덱싱을 사용할 수 없습니다.")
return
reply = QMessageBox.question(
self, "인덱스 재구성",
"모든 파일을 다시 인덱싱합니다.\n시간이 걸릴 수 있습니다. 계속하시겠습니까?",
QMessageBox.Yes | QMessageBox.No
)
if reply == QMessageBox.No:
return
self.index_progress.setVisible(True)
self.index_progress.setValue(0)
# 인덱싱 스레드 시작
self.indexing_thread = IndexingThread(self.searcher)
self.indexing_thread.progress.connect(self.update_index_progress)
self.indexing_thread.finished.connect(self.indexing_finished)
self.indexing_thread.error.connect(self.indexing_error)
self.indexing_thread.start()
def update_index_progress(self, current, total):
"""인덱싱 진행률 업데이트"""
if total > 0:
percentage = int((current / total) * 100)
self.index_progress.setValue(percentage)
self.statusBar().showMessage(f"인덱싱 중... {current}/{total} 파일")
def indexing_finished(self):
"""인덱싱 완료"""
self.index_progress.setVisible(False)
QMessageBox.information(self, "인덱스", "인덱싱이 완료되었습니다.")
self.statusBar().showMessage("인덱싱 완료", 5000)
self.update_index_status()
def indexing_error(self, error_msg):
"""인덱싱 오류"""
self.index_progress.setVisible(False)
QMessageBox.critical(self, "인덱싱 오류", f"인덱싱 중 오류:\n{error_msg}")
def update_index_status(self):
"""인덱스 상태 업데이트"""
if not WHOOSH_AVAILABLE or not self.searcher.indexer:
return
try:
with self.searcher.indexer.ix.searcher() as searcher:
doc_count = searcher.doc_count_all()
status_text = f"인덱싱된 문서: {doc_count}개\n"
status_text += f"인덱스 디렉토리: {self.searcher.indexer.index_dir}"
self.index_status_label.setText(status_text)
except Exception as e:
self.index_status_label.setText(f"상태 확인 오류: {str(e)}")
def show_about(self):
"""정보 대화상자"""
about_text = """<h2>SQL Script Searcher</h2>
<p><b>Professional Edition v1.0</b></p>
<p>Oracle DBA를 위한 강력한 SQL 스크립트 검색 도구</p>
<p><b>주요 기능:</b></p>
<ul>
<li>다중 스레드 고속 검색</li>
<li>정규표현식 지원</li>
<li>SQL 객체 특화 검색</li>
<li>Whoosh 전문 검색 엔진</li>
<li>ripgrep 통합 (선택)</li>
</ul>
<p><b>기술 스택:</b></p>
<ul>
<li>Python 3.x</li>
<li>PyQt5</li>
<li>Whoosh</li>
<li>PyYAML</li>
</ul>
"""
QMessageBox.about(self, "SQL Script Searcher", about_text)
def closeEvent(self, event):
"""프로그램 종료 시"""
# 윈도우 크기 저장
self.config.set(self.width(), 'ui_settings', 'window_width')
self.config.set(self.height(), 'ui_settings', 'window_height')
self.config.save_config()
event.accept()
def main():
app = QApplication(sys.argv)
app.setStyle('Fusion') # 모던한 스타일
window = SQLSearcherGUI()
window.show()
sys.exit(app.exec_())
if __name__ == '__main__':
main()
실행 방법
- bash# 1. 가상환경 생성 (권장)
python -m venv venv venv\Scripts\activate # Windows # source venv/bin/activate # Linux/Mac
- 2. 필요한 라이브러리 설치
pip install -r requirements.txt
- 3. 프로그램 실행
python sql_searcher_gui.py