一个为力扣 SQL 题目编辑器自动注入表名、列名的插件
Від
// ==UserScript==
// @name LeetCode SQL Autocomplete
// @namespace https://github.com/linyisu/leetcode-sql-autocomplete
// @version 1.1.2
// @author linyisu
// @match https://leetcode.cn/problems/*
// @match https://leetcode.com/problems/*
// @license MIT
// @description 一个为力扣 SQL 题目编辑器自动注入表名、列名的插件
// @grant none
// ==/UserScript==
(function () {
'use strict';
const KEYWORDS = [
// query
'SELECT', 'FROM', 'WHERE', 'AS', 'DISTINCT', 'LIMIT', 'OFFSET', 'ALL', 'ANY',
// join
'JOIN', 'LEFT JOIN', 'RIGHT JOIN', 'INNER JOIN', 'CROSS JOIN', 'NATURAL JOIN', 'ON', 'USING',
// logic / comparison
'AND', 'OR', 'NOT', 'IN', 'NOT IN', 'EXISTS', 'NOT EXISTS',
'BETWEEN', 'LIKE', 'REGEXP', 'IS NULL', 'IS NOT NULL', 'NULL',
// grouping / ordering
'GROUP BY', 'HAVING', 'ORDER BY', 'ASC', 'DESC',
// set operations
'UNION', 'UNION ALL', 'INTERSECT', 'EXCEPT',
// CTE
'WITH', 'RECURSIVE',
// conditional
'CASE', 'WHEN', 'THEN', 'ELSE', 'END',
'IF', 'IFNULL', 'NULLIF', 'COALESCE', 'GREATEST', 'LEAST', 'ISNULL',
// aggregate
'COUNT', 'SUM', 'AVG', 'MAX', 'MIN',
'GROUP_CONCAT', 'SEPARATOR',
// math
'ROUND', 'FLOOR', 'CEIL', 'CEILING', 'ABS', 'MOD', 'POW', 'POWER', 'SQRT', 'TRUNCATE', 'SIGN',
// string
'CONCAT', 'CONCAT_WS', 'LENGTH', 'CHAR_LENGTH', 'SUBSTRING', 'SUBSTR',
'LEFT', 'RIGHT', 'TRIM', 'LTRIM', 'RTRIM', 'UPPER', 'LOWER',
'REPLACE', 'LOCATE', 'INSTR', 'LPAD', 'RPAD', 'REPEAT', 'REVERSE', 'FORMAT',
// type conversion
'CAST', 'CONVERT',
// date / time
'NOW', 'CURDATE', 'CURTIME', 'CURRENT_DATE', 'CURRENT_TIMESTAMP',
'DATE', 'DATE_ADD', 'DATE_SUB', 'DATEDIFF', 'DATE_FORMAT', 'STR_TO_DATE',
'TIMESTAMPDIFF', 'EXTRACT',
'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'WEEK', 'QUARTER', 'LAST_DAY',
// window functions
'RANK', 'DENSE_RANK', 'ROW_NUMBER', 'NTILE',
'LAG', 'LEAD', 'FIRST_VALUE', 'LAST_VALUE', 'NTH_VALUE',
'PERCENT_RANK', 'CUME_DIST',
'OVER', 'PARTITION BY', 'ROWS BETWEEN', 'RANGE BETWEEN',
'UNBOUNDED PRECEDING', 'CURRENT ROW', 'UNBOUNDED FOLLOWING',
];
const SQL_LANGUAGES = ['mysql', 'sql', 'pgsql', 'postgresql', 'mssql', 'oraclesql', 'plaintext'];
const DESCRIPTION_SELECTORS = [
'[data-track-load="description_content"]',
'[data-track-load*="description"]',
'[data-testid*="description"]',
'.question-content__JfgR',
'.question-content',
'[class*="description"]',
];
const DESCRIPTION_SELECTOR = DESCRIPTION_SELECTORS.join(',');
const IGNORED_TABLE_NAMES = /^(input|output|inputs|outputs|example|explanation|note|follow|hint|return|this|that|the|result|results|sample|data)$/i;
const MAX_DOCUMENT_WORD_SUGGESTIONS = 50;
let cachedDescriptionElement = null;
function hasSchemaText(text) {
return /\bcolumn\s+name\b/i.test(text) ||
/列名/.test(text) ||
/(?:^|\n)\s*(?:table|表)\s*[:：]/i.test(text) ||
/(?:^|\n)\s*[`'"]?[a-zA-Z_]\w*[`'"]?\s*(?:table|表)\s*[:：]/i.test(text);
}
function findDescriptionElement() {
for (const selector of DESCRIPTION_SELECTORS) {
for (const el of document.querySelectorAll(selector)) {
const text = el.innerText || el.textContent || '';
if (hasSchemaText(text) || el.querySelector('pre, table')) return el;
}
}
return null;
}
function getDescriptionElement() {
if (cachedDescriptionElement?.isConnected) return cachedDescriptionElement;
cachedDescriptionElement = findDescriptionElement();
return cachedDescriptionElement;
}
function getDescriptionText() {
const descEl = getDescriptionElement();
const domText = descEl ? (descEl.innerText || descEl.textContent || '') : '';
const metaText = document.querySelector('meta[name="description"]')?.content || '';
if (hasSchemaText(domText)) return domText;
if (hasSchemaText(metaText)) return metaText;
return domText || metaText;
}
function normalizeIdentifier(value) {
const match = String(value || '').match(/[a-zA-Z_]\w*/);
return match ? match[0] : '';
}
function parseTableName(line) {
const normalized = line
.replace(/^(?:input|inputs|输入)\s*[:：]\s*/i, '')
.replace(/^示例\s*\d*\s*[:：]?\s*/i, '')
.trim();
const patterns = [
/(?:^|[\s\-–—])(?:table|表)\s*[:：]\s*[`'"]?([a-zA-Z_]\w*)[`'"]?/i,
/(?:^|[\s:：])[`'"]?([a-zA-Z_]\w*)[`'"]?\s*(?:table|表)\s*[:：]/i,
/(?:^|[\s\-–—])(?:table\s+name|表名)\s*[:：]\s*[`'"]?([a-zA-Z_]\w*)[`'"]?/i,
];
for (const pattern of patterns) {
const match = normalized.match(pattern);
if (match?.[1] && !IGNORED_TABLE_NAMES.test(match[1])) return match[1];
}
return '';
}
function splitPipeRow(line) {
const trimmed = line.trim();
if (!trimmed.includes('|')) return [];
const parts = trimmed.split('|').map(part => part.trim());
if (parts[0] === '') parts.shift();
if (parts[parts.length - 1] === '') parts.pop();
return parts;
}
function isTableDividerLine(line) {
const trimmed = line.trim();
return /^\+[-+\s]+\+$/.test(trimmed) ||
/^\|?\s*:?-{2,}:?\s*(\|\s*:?-{2,}:?\s*)+\|?$/.test(trimmed);
}
function isSchemaHeaderLine(line) {
const cells = splitPipeRow(line).map(cell => cell.toLowerCase());
if (cells.length >= 2) {
return /^(column\s+name|列名)$/.test(cells[0]) && /^(type|类型)$/.test(cells[1]);
}
return (/\bcolumn\s+name\b/i.test(line) && /\btype\b/i.test(line)) ||
(/列名/.test(line) && /类型/.test(line));
}
function extractColumnsFromSchemaTable(lines, startIndex, seenCols, columns) {
let i = startIndex;
for (; i < lines.length; i++) {
const trimmed = lines[i].trim();
if (!trimmed) break;
if (isTableDividerLine(trimmed) || isSchemaHeaderLine(trimmed)) continue;
const cells = splitPipeRow(trimmed);
if (cells.length < 2) break;
const colName = normalizeIdentifier(cells[0]);
const colType = cells[1].replace(/\s+/g, ' ').trim();
if (colName && !seenCols.has(colName)) {
seenCols.add(colName);
columns.push({ name: colName, type: colType });
}
}
return i;
}
function extractSchema() {
const tableNames = [];
const columns = [];
const seenTables = new Set();
const seenCols = new Set();
const text = getDescriptionText();
if (!text) return { tableNames, columns };
const lines = text.replace(/\r\n?/g, '\n').replace(/\u00a0/g, ' ').split('\n');
let inOutput = false;
for (let i = 0; i < lines.length; i++) {
const trimmed = lines[i].trim();
if (!trimmed) continue;
if (/^(?:input|inputs)\b/i.test(trimmed) || /^输入/.test(trimmed)) inOutput = false;
if (/^(?:output|outputs)\b/i.test(trimmed) || /^输出/.test(trimmed)) inOutput = true;
if (!inOutput) {
const tableName = parseTableName(trimmed);
if (tableName && tableName.length > 1 && !seenTables.has(tableName)) {
seenTables.add(tableName);
tableNames.push(tableName);
}
}
if (isSchemaHeaderLine(trimmed)) {
i = extractColumnsFromSchemaTable(lines, i + 1, seenCols, columns) - 1;
}
}
return { tableNames, columns };
}
let disposable = null;
let editorOptionsDisposable = null;
let lastSchemaKey = null;
function buildSchemaKey(tableNames, columns) {
if (tableNames.length === 0 && columns.length === 0) return '';
return [
tableNames.join('\0'),
columns.map(({ name, type }) => `${name}:${type}`).join('\0'),
].join('|');
}
function applyEditorOptions(editor) {
editor?.updateOptions?.({
quickSuggestions: { other: true, comments: false, strings: false },
});
}
function installEditorOptions(monaco) {
monaco.editor?.getEditors?.()?.forEach(applyEditorOptions);
if (!editorOptionsDisposable && monaco.editor?.onDidCreateEditor) {
editorOptionsDisposable = monaco.editor.onDidCreateEditor(applyEditorOptions);
}
}
function labelText(item) {
return typeof item.label === 'string' ? item.label : item.label.label;
}
function collectDocumentWords(value, currentWord, knownLabels) {
const words = value.match(/\b[a-zA-Z_]\w*\b/g) || [];
const currentKey = currentWord.toLowerCase();
const seen = new Set();
const result = [];
for (const word of words) {
const key = word.toLowerCase();
if (word.length <= 1 || key === currentKey || seen.has(key) || knownLabels.has(key)) continue;
seen.add(key);
result.push(word);
if (result.length >= MAX_DOCUMENT_WORD_SUGGESTIONS) break;
}
return result;
}
function registerProvider(monaco, tableNames, columns) {
if (disposable) {
try { disposable.dispose(); } catch (_) { }
disposable = null;
}
if (tableNames.length === 0 && columns.length === 0) return;
const { CompletionItemKind } = monaco.languages;
const items = [
...tableNames.map(name => ({
label: { label: name, description: 'table' },
kind: CompletionItemKind.Class,
insertText: name,
sortText: '0_' + name,
})),
...columns.map(({ name, type }) => ({
label: { label: name, description: type },
kind: CompletionItemKind.Field,
insertText: name,
sortText: '1_' + name,
})),
];
const knownLabels = new Set([
...items.map(item => labelText(item).toLowerCase()),
...KEYWORDS.map(kw => kw.toLowerCase()),
]);
const providers = SQL_LANGUAGES.map(lang =>
monaco.languages.registerCompletionItemProvider(lang, {
provideCompletionItems(model, position) {
const textUntilPos = model.getValueInRange({
startLineNumber: position.lineNumber,
startColumn: 1,
endLineNumber: position.lineNumber,
endColumn: position.column,
});
const wordMatch = textUntilPos.match(/\w+$/);
const currentWord = wordMatch ? wordMatch[0] : '';
const range = {
startLineNumber: position.lineNumber,
endLineNumber: position.lineNumber,
startColumn: wordMatch ? position.column - currentWord.length : position.column,
endColumn: position.column,
};
const isLower = currentWord.length > 0 && /[a-z]/.test(currentWord) && currentWord === currentWord.toLowerCase();
const kwItems = KEYWORDS.map(kw => ({
label: isLower ? kw.toLowerCase() : kw,
kind: CompletionItemKind.Keyword,
insertText: isLower ? kw.toLowerCase() : kw,
sortText: '5_' + kw,
range,
}));
const docWords = collectDocumentWords(model.getValue(), currentWord, knownLabels);
return {
suggestions: [
...items.map(item => ({ ...item, range })),
...kwItems,
...docWords.map(w => ({
label: w,
kind: CompletionItemKind.Text,
insertText: w,
sortText: '9_' + w,
range,
})),
],
};
},
})
);
disposable = { dispose() { providers.forEach(p => p.dispose()); } };
installEditorOptions(monaco);
}
function waitForMonaco(timeout = 30_000) {
return new Promise((resolve, reject) => {
let intervalId = null;
let timeoutId = null;
const done = (monaco) => {
clearInterval(intervalId);
clearTimeout(timeoutId);
resolve(monaco);
};
if (window.monaco?.languages?.registerCompletionItemProvider) return resolve(window.monaco);
intervalId = setInterval(() => {
if (window.monaco?.languages?.registerCompletionItemProvider) {
done(window.monaco);
}
}, 500);
timeoutId = setTimeout(() => {
clearInterval(intervalId);
reject(new Error('Monaco timeout'));
}, timeout);
});
}
function syncSchema(monaco) {
const { tableNames, columns } = extractSchema();
const key = buildSchemaKey(tableNames, columns);
if (key !== lastSchemaKey) {
lastSchemaKey = key;
registerProvider(monaco, tableNames, columns);
}
}
function elementMatchesDescriptionSelector(el) {
return DESCRIPTION_SELECTORS.some(selector => el.matches?.(selector));
}
function nodeMayContainDescription(node) {
if (node.nodeType !== 1) return false;
return elementMatchesDescriptionSelector(node) || Boolean(node.querySelector?.(DESCRIPTION_SELECTOR));
}
function mutationsMayChangeDescription(mutations) {
const descEl = getDescriptionElement();
if (!descEl) {
return mutations.some(mutation =>
nodeMayContainDescription(mutation.target) ||
[...mutation.addedNodes].some(nodeMayContainDescription)
);
}
return mutations.some(mutation =>
mutation.target === descEl ||
descEl.contains(mutation.target) ||
[...mutation.addedNodes].some(node => node === descEl || descEl.contains(node))
);
}
async function main() {
let monaco;
try {
monaco = await waitForMonaco();
} catch (_) {
console.warn('[SQ] Monaco 未就绪，退出');
return;
}
installEditorOptions(monaco);
syncSchema(monaco);
let syncTimer = null;
const scheduleSync = (delay = 800) => {
clearTimeout(syncTimer);
syncTimer = setTimeout(() => syncSchema(monaco), delay);
};
let lastUrl = location.href;
new MutationObserver((mutations) => {
if (location.href !== lastUrl) {
lastUrl = location.href;
lastSchemaKey = null;
cachedDescriptionElement = null;
scheduleSync(1500);
return;
}
if (mutationsMayChangeDescription(mutations)) scheduleSync();
}).observe(document.body, { childList: true, subtree: true });
}
if (document.readyState === 'loading') {
document.addEventListener('DOMContentLoaded', () => main());
} else {
main();
}
})();