Skip to content

Instantly share code, notes, and snippets.

@MarkusJLechner

MarkusJLechner/mysql2sqlite.cjs

Created October 1, 2024 14:33
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save MarkusJLechner/2fadfd2a4e318b738ef235cbd9f18de1 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save MarkusJLechner/2fadfd2a4e318b738ef235cbd9f18de1 to your computer and use it in GitHub Desktop.
Download ZIP
Convert mysql dump to sqlite3
Raw
mysql2sqlite.cjs
#!/usr/bin/env node
const fs = require('fs');
const readline = require('readline');
// Function to print to stderr
function printerr(s) {
process.stderr.write(s + '\n');
}
// Initialize INT_MAX_HALF based on JavaScript's safe integer limits
const INT_MAX_HALF = Math.floor(Number.MAX_SAFE_INTEGER / 2);
// Function to convert bit fields to integers
function bit_to_int(str_bit) {
let powtwo = 1;
let overflow = 0;
let res = 0;
for (let i = str_bit.length - 1; i >= 0; i--) {
const bit = str_bit.charAt(i);
if (overflow || (bit === '1' && res > INT_MAX_HALF)) {
printerr(
`${lineNumber}: WARN Bit field overflow, number truncated (LSBs saved, MSBs ignored).`
);
break;
}
res += bit * powtwo;
if (powtwo > INT_MAX_HALF) {
overflow = 1;
continue;
}
powtwo *= 2;
}
return res;
}
// Check command-line arguments
if (process.argv.length !== 3) {
printerr(
'USAGE:\n' +
' mysql2sqlite dump_mysql.sql > dump_sqlite3.sql\n' +
' OR\n' +
' mysql2sqlite dump_mysql.sql | sqlite3 sqlite.db\n' +
'\n' +
'NOTES:\n' +
' Dash in filename is not supported, because dash (-) means stdin.'
);
process.exit(1);
}
const inputFile = process.argv[2];
// Initialize variables
let inTrigger = 0;
let inView = 0;
let prev = '';
let firstInTable = true;
let tableName = '';
let key = {}; // Stores index statements
let caseIssue = 0;
let lineNumber = 0;
let currentTable = null;
let tableDefinitions = {}; // To store CREATE TABLE definitions
let tableColumns = {}; // Stores list of columns for each table
// New variables to handle ALTER TABLE statements
let inAlterTable = false;
let alterTableLines = [];
// New variable to track if a primary key has been defined
let primaryKeyDefined = {};
// Read the input file
const rl = readline.createInterface({
input: fs.createReadStream(inputFile),
crlfDelay: Infinity,
});
// Output initial SQLite pragmas
console.log('PRAGMA synchronous = OFF;');
console.log('PRAGMA journal_mode = MEMORY;');
console.log('BEGIN TRANSACTION;');
rl.on('line', (line) => {
lineNumber++;
// Handle CREATE TRIGGER statements
if (/^\/\*.*(CREATE.*TRIGGER|create.*trigger)/.test(line)) {
line = line.replace(/^.*(TRIGGER|trigger)/, 'CREATE TRIGGER');
console.log(line);
inTrigger = 1;
return;
}
// Handle end of CREATE TRIGGER
if (/(END|end) \*\/;;/.test(line)) {
line = line.replace(/\*\//, '');
console.log(line);
inTrigger = 0;
return;
}
// Print lines inside a trigger
if (inTrigger !== 0) {
console.log(line);
return;
}
// Handle CREATE VIEW statements
if (/^\/\*.*(CREATE.*TABLE|create.*table)/.test(line)) {
inView = 1;
return;
}
if (/^(\).*(ENGINE|engine).*\*\/;)/.test(line)) {
inView = 0;
return;
}
if (inView !== 0) {
return;
}
// Skip comments
if (/^\/\*/.test(line)) {
return;
}
// Skip PARTITION statements
if (/^ *[(]?(PARTITION|partition) +[^ ]+/.test(line)) {
return;
}
// Handle INSERT statements
if (
(/^ *\(/.test(line) && /\) *[,;] *$/.test(line)) ||
/^(INSERT|insert|REPLACE|replace)/.test(line)
) {
prev = '';
// Handle escaped characters
line = line.replace(/\\\\/g, '\\_');
line = line.replace(/\\'/g, "''");
line = line.replace(/\\n/g, '\n');
line = line.replace(/\\r/g, '\r');
line = line.replace(/\\"/g, '"');
line = line.replace(/\\\032/g, '\032');
line = line.replace(/\\_/g, '\\');
// Trim long hex numbers
let hexIssue = false;
while (/0x[0-9a-fA-F]{17}/.test(line)) {
hexIssue = true;
line = line.replace(/0x[0-9a-fA-F]+/, (match) =>
match.substr(0, match.length - 1)
);
}
if (hexIssue) {
printerr(
`${lineNumber}: WARN Hex number trimmed (length longer than 16 chars).`
);
hexIssue = false;
}
console.log(line);
return;
}
// Skip CREATE DATABASE statements
if (/^(CREATE DATABASE|create database)/.test(line)) {
return;
}
// Handle CREATE TABLE statements
if (/^(CREATE|create)/.test(line)) {
if (
/IF NOT EXISTS|if not exists/.test(line) ||
/TEMPORARY|temporary/.test(line)
) {
caseIssue = 1;
printerr(
`${lineNumber}: WARN Potential case sensitivity issues with table/column naming\n` +
' (see INFO at the end).'
);
}
// Updated regex to capture table names more robustly
const tableNameMatch = line.match(
/^CREATE TABLE\s+(IF NOT EXISTS\s+)?[`"]?([^`" ]+)[`"]?/i
);
if (tableNameMatch) {
tableName = tableNameMatch[2];
currentTable = tableName.replace(/[`"]/g, '').toLowerCase();
tableDefinitions[currentTable] = []; // Initialize an array to store table lines
tableColumns[currentTable] = new Set(); // Initialize set to store column names
primaryKeyDefined[currentTable] = false; // Initialize primary key tracker
}
prev = '';
firstInTable = true;
tableDefinitions[currentTable].push(line);
return;
}
// Replace FULLTEXT KEY statements
if (/^ (FULLTEXT KEY|fulltext key)/.test(line)) {
line = line.replace(/[A-Za-z ]+(KEY|key)/, ' KEY');
}
// Remove field lengths in KEY lines
if (/ (PRIMARY |primary )?(KEY|key)/.test(line)) {
line = line.replace(/\([0-9]+\)/g, '');
}
// Skip duplicate PRIMARY KEY definitions
if (/PRIMARY KEY|primary key/.test(line)) {
if (primaryKeyDefined[currentTable]) {
// Already have a primary key, skip this one
return;
} else {
primaryKeyDefined[currentTable] = true;
}
}
// Replace COLLATE statements
if (/ (COLLATE|collate) [a-z0-9_]*/.test(line)) {
line = line.replace(/(COLLATE|collate) [a-z0-9_]*/g, 'COLLATE BINARY');
}
// Handle field definitions
if (
currentTable &&
/^ /.test(line) &&
!/^( (KEY|key)| (CONSTRAINT|constraint)|\);)/.test(line)
) {
// Parse the column definition
const columnDefMatch = line.match(/^\s*[`"]([^`"]+)[`"]\s+([^\s]+)(.*)$/);
if (columnDefMatch) {
const columnName = columnDefMatch[1];
let dataType = columnDefMatch[2];
let restOfLine = columnDefMatch[3];
// Add column name to tableColumns
if (!tableColumns[currentTable]) {
tableColumns[currentTable] = new Set();
}
tableColumns[currentTable].add(columnName);
// Remove UNSIGNED
dataType = dataType.replace(/UNSIGNED|unsigned/g, '');
// Replace integer types with INTEGER
dataType = dataType.replace(
/\b(TINYINT|SMALLINT|MEDIUMINT|INT|INTEGER|BIGINT)\b/gi,
'INTEGER'
);
// If AUTO_INCREMENT is present in restOfLine
if (/AUTO_INCREMENT|auto_increment/i.test(restOfLine)) {
// Check if dataType is INTEGER
if (dataType.trim().toUpperCase() === 'INTEGER') {
if (!primaryKeyDefined[currentTable]) {
// Replace AUTO_INCREMENT with PRIMARY KEY AUTOINCREMENT
restOfLine = restOfLine.replace(
/AUTO_INCREMENT|auto_increment/gi,
'PRIMARY KEY AUTOINCREMENT'
);
primaryKeyDefined[currentTable] = true;
} else {
// Remove AUTO_INCREMENT, issue warning
restOfLine = restOfLine.replace(
/AUTO_INCREMENT|auto_increment/gi,
''
);
printerr(
`WARN: Multiple AUTO_INCREMENT columns in table ${currentTable}. Only one INTEGER PRIMARY KEY AUTOINCREMENT is allowed in SQLite.`
);
}
} else {
// Remove AUTO_INCREMENT, issue warning
restOfLine = restOfLine.replace(
/AUTO_INCREMENT|auto_increment/gi,
''
);
printerr(
`WARN: AUTO_INCREMENT column ${columnName} in table ${currentTable} is not INTEGER type. Removed AUTO_INCREMENT.`
);
}
}
// Reconstruct the line
line = ` "${columnName}" ${dataType}${restOfLine}`;
// Proceed with other replacements as before
line = line.replace(
/(UNIQUE KEY|unique key) (`.*`|".*") /g,
'UNIQUE '
);
line = line.replace(
/(CHARACTER SET|character set) [^ ]+[ ,]/g,
''
);
line = line.replace(
/(ON|on) (UPDATE|update) (CURRENT_TIMESTAMP|current_timestamp)(\(\))?/g,
''
);
line = line.replace(
/(DEFAULT|default) (CURRENT_TIMESTAMP|current_timestamp)(\(\))?/g,
'DEFAULT current_timestamp'
);
line = line.replace(/(COLLATE|collate) [^ ]+ /g, '');
// Corrected handling of ENUM and SET data types
line = line.replace(/(ENUM|enum)\s*\([^)]+\)/g, 'text ');
line = line.replace(/(SET|set)\s*\([^)]+\)/g, 'text ');
// Handle bit fields
const ere_bit_field = /[bB]'[10]+'/;
const bitFieldMatch = line.match(ere_bit_field);
if (bitFieldMatch) {
const bitValue = bitFieldMatch[0].substring(
2,
bitFieldMatch[0].length - 1
);
line = line.replace(ere_bit_field, bit_to_int(bitValue));
}
// Remove USING BTREE and other suffixes
line = line.replace(/ USING [^, ]+/g, '');
// Remove field comments
line = line.replace(/ (COMMENT|comment).+$/, '');
// Remove commas at end
line = line.replace(/,$/, '');
if (prev) {
// Add previous line to table definitions
if (firstInTable) {
tableDefinitions[currentTable].push(prev);
firstInTable = false;
} else {
tableDefinitions[currentTable].push(',' + prev);
}
}
prev = line;
return;
} else {
// If the line doesn't match, perhaps do nothing
printerr(
`WARN: Could not parse column definition in table ${currentTable}: ${line}`
);
return;
}
} else {
// Not within a column definition
if (prev) {
// Add previous line to table definitions
if (firstInTable) {
tableDefinitions[currentTable].push(prev);
firstInTable = false;
} else {
tableDefinitions[currentTable].push(',' + prev);
}
prev = '';
}
}
// Handle KEY and CONSTRAINT definitions inside CREATE TABLE
if (
currentTable &&
(/^( (KEY|key)| (UNIQUE KEY|unique key)| (CONSTRAINT|constraint))/.test(line) ||
line.trim() === ');')
) {
// Add any remaining 'prev' content
if (prev) {
if (firstInTable) {
tableDefinitions[currentTable].push(prev);
firstInTable = false;
} else {
tableDefinitions[currentTable].push(',' + prev);
}
prev = '';
}
if (line.trim() === ');') {
if (currentTable && tableDefinitions[currentTable]) {
tableDefinitions[currentTable].push(line);
}
} else {
// Process KEY or CONSTRAINT lines
if (/^( (KEY|key)| (UNIQUE KEY|unique key))/.test(line)) {
// Process INDEX definitions
const indexNameMatch = line.match(/KEY\s+[`"]?([^`"]*)[`"]?\s*\(([^)]+)\)/i);
if (indexNameMatch) {
let indexName = indexNameMatch[1] || 'auto_index';
let indexKey = indexNameMatch[2];
const unique = /UNIQUE KEY|unique key/.test(line) ? 'UNIQUE ' : '';
// Split indexKey into individual columns and clean them
const indexColumns = indexKey
.split(',')
.map((col) => col.trim().replace(/[`"]/g, ''));
// Check if all columns exist in the table
let allColumnsExist = true;
indexColumns.forEach((col) => {
if (!tableColumns[currentTable] || !tableColumns[currentTable].has(col)) {
allColumnsExist = false;
printerr(
`WARN: Column '${col}' in index '${indexName}' does not exist in table '${currentTable}'. Index will not be created.`
);
}
});
if (allColumnsExist) {
const indexLine =
'CREATE ' +
unique +
'INDEX "' +
currentTable +
'_' +
indexName +
'" ON "' +
currentTable +
'" (' +
indexKey +
');';
if (!key[currentTable]) {
key[currentTable] = '';
}
key[currentTable] += indexLine + '\n';
} else {
// Skip creating this index
}
} else {
printerr(`WARN: Could not parse INDEX line: ${line}`);
}
} else if (/^( (CONSTRAINT|constraint))/.test(line)) {
// Process FOREIGN KEY constraints
const fkMatch = line.match(
/CONSTRAINT [`"]?([^`"]*)[`"]? FOREIGN KEY\s*\(([^)]+)\)\s*REFERENCES\s+[`"]?([^`"]*)[`"]?\s*\(([^)]+)\)/i
);
if (fkMatch) {
const constraintName = fkMatch[1];
const localColumns = fkMatch[2];
const referencedTable = fkMatch[3];
const referencedColumns = fkMatch[4];
// Split local columns in case there are multiple
const localColsArray = localColumns
.split(',')
.map((col) => col.trim().replace(/[`"]/g, ''));
// Ensure all local columns exist in the table
let allColumnsExist = true;
localColsArray.forEach((columnName) => {
if (!tableColumns[currentTable] || !tableColumns[currentTable].has(columnName)) {
allColumnsExist = false;
printerr(
`WARN: Column '${columnName}' in FOREIGN KEY constraint does not exist in table '${currentTable}'. Constraint will not be created.`
);
}
});
if (allColumnsExist) {
// Build FOREIGN KEY constraint
const foreignKeyStatement = `FOREIGN KEY (${localColumns}) REFERENCES ${referencedTable}(${referencedColumns})`;
// Add to table definitions
if (firstInTable) {
tableDefinitions[currentTable].push(foreignKeyStatement);
firstInTable = false;
} else {
tableDefinitions[currentTable].push(',' + foreignKeyStatement);
}
} else {
// Skip creating this constraint
}
} else {
printerr(`WARN: Could not parse CONSTRAINT line: ${line}`);
}
}
}
return;
}
// Handle ENGINE statements (end of CREATE TABLE)
if (/ ENGINE| engine/.test(line)) {
// Add any remaining 'prev' content
if (prev) {
if (firstInTable) {
tableDefinitions[currentTable].push(prev);
firstInTable = false;
} else {
tableDefinitions[currentTable].push(',' + prev);
}
prev = '';
}
// Add closing parenthesis
if (currentTable && tableDefinitions[currentTable]) {
tableDefinitions[currentTable].push(');');
}
currentTable = null;
firstInTable = true;
return;
}
// Handle ALTER TABLE statements
if (!inAlterTable) {
if (/^ALTER TABLE/.test(line)) {
inAlterTable = true;
alterTableLines = [line];
return;
}
} else {
alterTableLines.push(line);
if (line.trim().endsWith(';')) {
// We've collected the entire ALTER TABLE statement
// Now process it
const alterTableStatement = alterTableLines.join(' ');
inAlterTable = false;
// Process alterTableStatement
const alterMatch = alterTableStatement.match(/^ALTER TABLE\s+[`"]?([^`"]+)[`"]?\s+(.+);$/i);
if (alterMatch) {
let alterTableName = alterMatch[1];
let alterCommands = alterMatch[2];
// Normalize table name
alterTableName = alterTableName.replace(/[`"]/g, '').toLowerCase();
// Split the commands
const commands = splitAlterCommands(alterCommands);
// Separate ADD COLUMN commands and other commands
const addColumnCommands = [];
const otherCommands = [];
commands.forEach((command) => {
command = command.trim();
if (/^ADD\s+COLUMN/i.test(command)) {
addColumnCommands.push(command);
} else {
otherCommands.push(command);
}
});
// Process ADD COLUMN commands first
addColumnCommands.forEach((command) => {
// Process ADD COLUMN
const addColumnMatch = command.match(
/^ADD\s+COLUMN\s+[`"]?([^`"]+)[`"]?\s+(.+)/i
);
if (addColumnMatch) {
const columnName = addColumnMatch[1];
let columnDefinition = addColumnMatch[2];
// Remove 'AFTER' or 'FIRST' if present, as SQLite doesn't support column ordering
columnDefinition = columnDefinition.replace(
/\s+(AFTER|FIRST)\s+[`"]?[^`"]*[`"]?/i,
''
);
// Remove UNSIGNED keyword
columnDefinition = columnDefinition.replace(/UNSIGNED|unsigned/g, '');
// Replace integer types with INTEGER
columnDefinition = columnDefinition.replace(
/\b(TINYINT|SMALLINT|MEDIUMINT|INT|INTEGER|BIGINT)\b/gi,
'INTEGER'
);
// Replace AUTO_INCREMENT with PRIMARY KEY AUTOINCREMENT if applicable
if (/AUTO_INCREMENT|auto_increment/i.test(columnDefinition)) {
if (!primaryKeyDefined[alterTableName]) {
columnDefinition = columnDefinition.replace(
/AUTO_INCREMENT|auto_increment/gi,
'PRIMARY KEY AUTOINCREMENT'
);
primaryKeyDefined[alterTableName] = true;
} else {
// Remove AUTO_INCREMENT, issue warning
columnDefinition = columnDefinition.replace(
/AUTO_INCREMENT|auto_increment/gi,
''
);
printerr(
`WARN: Cannot set ${columnName} as PRIMARY KEY AUTOINCREMENT in table ${alterTableName} because a primary key already exists.`
);
}
}
// Check if the column already exists
let columnExists = false;
if (tableDefinitions[alterTableName]) {
for (let line of tableDefinitions[alterTableName]) {
if (
new RegExp(`^[ \\t]*["\`]${columnName}["\`]`, 'i').test(
line.trim()
)
) {
columnExists = true;
break;
}
}
}
if (!columnExists) {
// Add the column definition to the CREATE TABLE statement
if (tableDefinitions[alterTableName]) {
let found = false;
for (let i = 0; i < tableDefinitions[alterTableName].length; i++) {
if (tableDefinitions[alterTableName][i].trim() === ');') {
// Insert before the closing );
// Ensure previous line ends with ','
if (!tableDefinitions[alterTableName][i - 1].trim().endsWith(',')) {
tableDefinitions[alterTableName][i - 1] += ',';
}
tableDefinitions[alterTableName].splice(
i,
0,
` "${columnName}" ${columnDefinition}`
);
// Add column to tableColumns
if (!tableColumns[alterTableName]) {
tableColumns[alterTableName] = new Set();
}
tableColumns[alterTableName].add(columnName);
found = true;
break;
}
}
if (!found) {
printerr(
`WARN: Could not find end of CREATE TABLE for ${alterTableName} to add COLUMN ${columnName}.`
);
}
} else {
printerr(
`WARN: Table ${alterTableName} not found for ALTER TABLE ADD COLUMN.`
);
}
} else {
printerr(
`INFO: Column ${columnName} already exists in table ${alterTableName}, not adding again.`
);
}
} else {
printerr(`WARN: Could not parse ADD COLUMN command: ${command}`);
}
});
// Now process other commands
otherCommands.forEach((command) => {
command = command.trim();
if (/^ADD PRIMARY KEY/i.test(command)) {
// Process ADD PRIMARY KEY
const pkMatch = command.match(/^ADD PRIMARY KEY\s*\((.+)\)/i);
if (pkMatch) {
const primaryKey = pkMatch[1];
// Find the table definition and add the primary key
if (tableDefinitions[alterTableName]) {
// Modify the table definition to include the primary key
if (!primaryKeyDefined[alterTableName]) {
let found = false;
for (let i = 0; i < tableDefinitions[alterTableName].length; i++) {
if (tableDefinitions[alterTableName][i].trim() === ');') {
// Insert before the closing );
// Check if the line before ');' ends with ',', if not, add ','
if (
!tableDefinitions[alterTableName][i - 1].trim().endsWith(',')
) {
tableDefinitions[alterTableName][i - 1] += ',';
}
tableDefinitions[alterTableName][i - 1] += `\n PRIMARY KEY (${primaryKey})`;
primaryKeyDefined[alterTableName] = true;
found = true;
break;
}
}
if (!found) {
printerr(
`WARN: Could not find end of CREATE TABLE for ${alterTableName} to add PRIMARY KEY.`
);
}
} else {
printerr(
`WARN: Multiple PRIMARY KEY definitions for table ${alterTableName}.`
);
}
} else {
printerr(
`WARN: Table ${alterTableName} not found for ALTER TABLE ADD PRIMARY KEY.`
);
}
}
} else if (/^ADD (UNIQUE )?(KEY|INDEX)/i.test(command)) {
// Process ADD KEY
const keyMatch = command.match(
/^ADD (UNIQUE )?(?:KEY|INDEX) [`"]?([^`"]*)[`"]? \((.+)\)/i
);
if (keyMatch) {
const unique = keyMatch[1] ? 'UNIQUE ' : '';
let indexName = keyMatch[2];
if (!indexName) {
indexName = 'auto_index';
}
const indexColumnsStr = keyMatch[3];
// Split indexColumnsStr into individual columns and clean them
const indexColumns = indexColumnsStr
.split(',')
.map((col) => col.trim().replace(/[`"]/g, ''));
// Check if all columns exist in the table
let allColumnsExist = true;
indexColumns.forEach((col) => {
if (
!tableColumns[alterTableName] ||
!tableColumns[alterTableName].has(col)
) {
allColumnsExist = false;
printerr(
`WARN: Column '${col}' in index '${indexName}' does not exist in table '${alterTableName}'. Index will not be created.`
);
}
});
if (allColumnsExist) {
// Create a CREATE INDEX statement
const indexStatement =
'CREATE ' +
unique +
'INDEX "' +
alterTableName +
'_' +
indexName +
'" ON "' +
alterTableName +
'" (' +
indexColumnsStr +
');';
// Store it to be printed later
if (!key[alterTableName]) {
key[alterTableName] = '';
}
key[alterTableName] += indexStatement + '\n';
} else {
// Skip creating this index
}
}
} else if (/^ADD CONSTRAINT/i.test(command)) {
// Process ADD CONSTRAINT
const fkMatch = command.match(
/^ADD CONSTRAINT [`"]?([^`"]*)[`"]? FOREIGN KEY\s*\(([^)]+)\)\s*REFERENCES\s+[`"]?([^`"]*)[`"]?\s*\(([^)]+)\)/i
);
if (fkMatch) {
const constraintName = fkMatch[1];
const localColumns = fkMatch[2];
const referencedTable = fkMatch[3];
const referencedColumns = fkMatch[4];
// Split local columns in case there are multiple
const localColsArray = localColumns
.split(',')
.map((col) => col.trim().replace(/[`"]/g, ''));
// Ensure all local columns exist in the table
let allColumnsExist = true;
localColsArray.forEach((columnName) => {
if (
!tableColumns[alterTableName] ||
!tableColumns[alterTableName].has(columnName)
) {
allColumnsExist = false;
printerr(
`WARN: Column '${columnName}' in FOREIGN KEY constraint does not exist in table '${alterTableName}'. Constraint will not be created.`
);
}
});
if (allColumnsExist) {
// Build FOREIGN KEY constraint
const foreignKeyStatement = `FOREIGN KEY (${localColumns}) REFERENCES ${referencedTable}(${referencedColumns})`;
// Now, we need to add this constraint to the CREATE TABLE statement
if (tableDefinitions[alterTableName]) {
// Find the table definition and add the foreign key constraint
let found = false;
for (
let i = 0;
i < tableDefinitions[alterTableName].length;
i++
) {
if (tableDefinitions[alterTableName][i].trim() === ');') {
// Insert before the closing );
// Check if the line before ');' ends with ',', if not, add ','
if (
!tableDefinitions[alterTableName][i - 1].trim().endsWith(',')
) {
tableDefinitions[alterTableName][i - 1] += ',';
}
tableDefinitions[alterTableName][i - 1] += `\n ${foreignKeyStatement}`;
found = true;
break;
}
}
if (!found) {
printerr(
`WARN: Could not find end of CREATE TABLE for ${alterTableName} to add FOREIGN KEY.`
);
}
} else {
printerr(
`WARN: Table ${alterTableName} not found for ALTER TABLE ADD CONSTRAINT FOREIGN KEY.`
);
}
} else {
// Skip creating this constraint
}
} else {
printerr(`WARN: Could not parse ADD CONSTRAINT command: ${command}`);
}
} else if (/^MODIFY/i.test(command)) {
// Process MODIFY
const modifyMatch = command.match(
/^MODIFY [`"]?([^`"]*)[`"]?\s+(.+)/i
);
if (modifyMatch) {
const columnName = modifyMatch[1];
let columnDefinition = modifyMatch[2];
// Remove UNSIGNED keyword
columnDefinition = columnDefinition.replace(/UNSIGNED|unsigned/g, '');
// Replace integer types with INTEGER
columnDefinition = columnDefinition.replace(
/\b(TINYINT|SMALLINT|MEDIUMINT|INT|INTEGER|BIGINT)\b/gi,
'INTEGER'
);
// Replace AUTO_INCREMENT with PRIMARY KEY AUTOINCREMENT if applicable
if (/AUTO_INCREMENT|auto_increment/i.test(columnDefinition)) {
if (!primaryKeyDefined[alterTableName]) {
columnDefinition = columnDefinition.replace(
/AUTO_INCREMENT|auto_increment/gi,
'PRIMARY KEY AUTOINCREMENT'
);
primaryKeyDefined[alterTableName] = true;
} else {
// Remove AUTO_INCREMENT, issue warning
columnDefinition = columnDefinition.replace(
/AUTO_INCREMENT|auto_increment/gi,
''
);
printerr(
`WARN: Cannot set ${columnName} as PRIMARY KEY AUTOINCREMENT in table ${alterTableName} because a primary key already exists.`
);
}
}
// Find the column in the CREATE TABLE statement and modify its definition
if (tableDefinitions[alterTableName]) {
let found = false;
for (
let i = 0;
i < tableDefinitions[alterTableName].length;
i++
) {
let line = tableDefinitions[alterTableName][i];
// Match the column definition
const columnMatch = line.match(
new RegExp(`^[ \\t]*[\`"]?${columnName}[\`"]?[ \\t]+.*`, 'i')
);
if (columnMatch) {
// Modify the line to have the new column definition
tableDefinitions[alterTableName][i] =
` "${columnName}" ${columnDefinition}`;
found = true;
break;
}
}
if (!found) {
printerr(
`WARN: Column ${columnName} not found in table ${alterTableName} for MODIFY command.`
);
}
} else {
printerr(
`WARN: Table ${alterTableName} not found for ALTER TABLE MODIFY.`
);
}
} else {
printerr(`WARN: Could not parse MODIFY command: ${command}`);
}
} else {
printerr(`WARN: Unhandled ALTER TABLE command: ${command}`);
}
});
} else {
printerr(
`WARN: Could not parse ALTER TABLE statement: ${alterTableStatement}`
);
}
return;
}
return;
}
});
// Function to split ALTER TABLE commands, considering commas inside parentheses
function splitAlterCommands(alterCommands) {
let commands = [];
let currentCommand = '';
let parenLevel = 0;
let inQuote = false;
let quoteChar = '';
for (let i = 0; i < alterCommands.length; i++) {
let c = alterCommands[i];
currentCommand += c;
if (c === '\'' || c === '"' || c === '`') {
if (!inQuote) {
inQuote = true;
quoteChar = c;
} else if (c === quoteChar) {
inQuote = false;
quoteChar = '';
}
} else if (!inQuote) {
if (c === '(') {
parenLevel++;
} else if (c === ')') {
parenLevel--;
} else if (c === ',' && parenLevel === 0) {
// Found a top-level comma, split here
commands.push(currentCommand.trim());
currentCommand = '';
}
}
}
if (currentCommand.trim() !== '') {
commands.push(currentCommand.trim());
}
return commands;
}
rl.on('close', () => {
// Process any remaining 'prev' content
if (prev && currentTable && tableDefinitions[currentTable]) {
if (firstInTable) {
tableDefinitions[currentTable].push(prev);
firstInTable = false;
} else {
tableDefinitions[currentTable].push(',' + prev);
}
prev = '';
}
// Ensure commas are correctly placed in CREATE TABLE statements
for (const table in tableDefinitions) {
const lines = tableDefinitions[table];
// Remove any extra commas before closing );
for (let i = lines.length - 1; i >= 0; i--) {
if (lines[i].trim() === ');') {
if (i > 0 && lines[i - 1].trim().endsWith(',')) {
lines[i - 1] = lines[i - 1].trim().slice(0, -1);
}
break;
}
}
}
// Output modified CREATE TABLE statements
for (const table in tableDefinitions) {
tableDefinitions[table].forEach((line) => {
console.log(line);
});
}
// Print all accumulated KEY definitions
for (const table in key) {
process.stdout.write(key[table]);
}
// End transaction
console.log('END TRANSACTION;');
// Print case sensitivity warning if necessary
if (caseIssue) {
printerr(
'INFO Pure sqlite identifiers are case insensitive (even if quoted\n' +
' or if ASCII) and doesnt cross-check TABLE and TEMPORARY TABLE\n' +
' identifiers. Thus expect errors like "table T has no column named F".'
);
}
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment