const fs = require('fs'); const readline = require('readline'); if (!fs.existsSync('todo')) { fs.mkdirSync('todo'); } const file = process.argv[2] || 'plants_schema_only.sql'; const done = {}; const createTable = (ss) => { let pg = ss .replace('AS ([country_subdivision_hierarchy_id].[GetLevel]())', 'TEXT') // d_country_subdivision .replace('CREATE TABLE ', 'CREATE TABLE plants3.') .replace('[int] IDENTITY(1,1)', 'SERIAL PRIMARY KEY') .replace(/NOT FOR REPLICATION/g, '') .replace(/\[dbo\]\./g, '') .replace(/\[int\]/g, 'INT') .replace(/\[bit\]/g, 'BOOLEAN') .replace(/\[varchar\]\((\d+)\)/g, 'VARCHAR($1)') .replace(/\[datetime2\]\((\d+)\)/g, 'TIMESTAMP($1)') .replace(/uniqueidentifier/g, 'TEXT') .replace(/\) ON .+/gs, ');') .replace(/CONSTRAINT.+/s, '') .replace(/PRIMARY KEY CLUSTERED.+/s, '') .replace(/\[[^\[]+\]/g, (c) => /\[\d| /.test(c) ? `"${c.slice(1, -1)}"` : c.slice(1, -1)) .replace(/nvarchar/g, 'VARCHAR') .replace(/TIMESTAMP\(7\)/g, 'TIMESTAMP(6)') .replace(/datetime/g, 'TIMESTAMP') .replace(/hierarchyid/g, 'TEXT') // ??? .replace(/varchar\(max\)/gi, 'TEXT') .replace(/varbinary\(max\)/gi, 'TEXT') .trim() .replace(/,$/, ');') .replace(/\s+/g, ' '); pg = `${pg.split('(')[0].replace('CREATE', 'DROP')}; ${pg}`; return pg; }; // createTable let lt; const insert = (sql) => { const table = sql.match(/\[.+?\]/g)[1].slice(1, -1); if (!done[table]) { done[table] = true; fs.unlink(table, () => {}); } if (table !== lt) { lt = table; console.log(table); } sql = sql.replace(/CAST\((-?\d+(\.\d+)?) AS Decimal\(\d+, \d+\)\)/g, '$1'); // precision/scale doesn't matter in PostgreSQL sql = sql.replace(/CAST\(N'([^']*)' AS DateTime2\)/g, `'$1'`); const columns = sql.split('VALUES (')[0].match(/\[.+?\]/g).slice(2); const re = /N'((?:''|[^'])*)'|0x[0-9A-F]+|-?[\d\.]+|NULL|'.+?'/g; const values = sql .split('VALUES (')[1] .slice(0, -1) .match(re); if (values.length !== columns.length) { console.error('ERROR:', values.length, columns.length); console.error(values.map((v, i) => columns[i] + ': ' + v)); console.error(sql); console.error('_'.repeat(40)); process.exit(); } else { columns.forEach((column, i) => { if (booleans[column.slice(1, -1)]) { values[i] = values[i] == 0 ? 'false' : 'true'; } }); sql = sql.split('VALUES (')[0] + `VALUES (${values});`; sql = sql .replace('INSERT [dbo].', 'INSERT INTO plants3.') .replace(/\[[^\[]+\]/g, (c) => /\[\d| /.test(c) ? `"${c.slice(1, -1)}"` : c.slice(1, -1)) .replace(/\bN\b'/g, `'`) .replace(/CAST\(.+? AS DateTime2\)/g, `'2018-08-29T20:20:29.8966667'::timestamp`); fs.appendFile(`todo/${table}`, `${sql}\n`, (err) => { if (err) { console.error(err); process.exit(); } }); // console.log(sql); } }; // insert const matchingParentheses = (inputString) => { const stack = []; let insideDoubleQuotes = false; for (let i = 0; i < inputString.length; i++) { const char = inputString[i]; if (char === "'" && inputString[i - 1] !== "\\") { // Toggle the insideDoubleQuotes flag when a single quote is encountered insideDoubleQuotes = !insideDoubleQuotes; } if (!insideDoubleQuotes) { if (char === '(') { stack.push('('); } else if (char === ')') { if (stack.pop() !== '(') { return false; } } } } return stack.length === 0; } const convert = (file) => { // fs.readFile(file, 'utf16le', (err, data) => { fs.readFile(file, 'utf8', (err, data) => { if (err) { console.error(`Error reading file: ${err}`); return; } if (/schema/i.test(file)) { const batches = data.split(/\bGO\b/i); batches .filter((batch) => ( batch.trim().match(/^CREATE TABLE/) && !/ImageLibrary|DocumentLibrary/.test(batch) )) .forEach((batch) => { console.log(createTable(batch)); }); } else { data.split(/[\n\r]/).filter((sql) => /^INSERT/.test(sql)).slice(0, -1).forEach(insert); } }); } // convert const booleans = {}; if (file === 'plants_schema_only.sql') { convert(file); } else if (/^try|plants_data.sql/.test(file)) { fs.readFile('plants3.sql', 'utf8', (err, data) => { data.match(/\w+ BOOLEAN/g).forEach((b) => booleans[b.replace(' BOOLEAN', '')] = true); let last = ''; const fileStream = fs.createReadStream(file, { encoding: 'utf8' }); const rl = readline.createInterface({ input: fileStream, crlfDelay: Infinity, }); rl.on('line', (line) => { if ( last || ( line.trim().match(/^INSERT /) && !/ImageLibrary|DocumentLibrary/.test(line) ) ) { if (line.slice(-1).trim() === ')' && matchingParentheses(last + line)) { if (last) { line = last + ' ' + line; } if (line.trim().match(/^INSERT/)) { insert(line); } else { console.error(line); } last = ''; } else { last = line; // console.log(166, line); process.exit(); } } }); rl.on('close', () => { // console.log('File reading completed.'); }); }); } else { fs.readFile('plants3.sql', 'utf8', (err, data) => { data.match(/\w+ BOOLEAN/g).forEach((b) => booleans[b.replace(' BOOLEAN', '')] = true); // console.log(booleans); const tables = [...data.matchAll(/DROP TABLE plants3\."?(.+?)"?; C/gs)].map(match => match[1]); convert(file); }); }