-
Notifications
You must be signed in to change notification settings - Fork 16
/
Copy pathindex.html
146 lines (129 loc) · 4.96 KB
/
index.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>MySQL to SQLite online converter 🔨</title>
<style type="text/css">
html,
body {
font-family: sans-serif;
padding: 0;
margin: 0;
width: 100%;
background: #999;
text-align: center;
}
body {
max-width: 1024px;
margin: 1em auto;
}
textarea {
display: block;
width: 100%;
height: 300px;
}
button {
margin: 30px 0;
padding: 5px 10px;
font-size: 32px;
}
p {
font-size: 20px;
}
a, a:visited {
color: #00D;
}
</style>
</head>
<body>
<script>
function convert() {
var sqlite = "" +
"-- import to SQLite by running: sqlite3.exe db.sqlite3 -init sqlite.sql\n\n" +
"PRAGMA journal_mode = MEMORY;\n" +
"PRAGMA synchronous = OFF;\n" +
"PRAGMA foreign_keys = OFF;\n" +
"PRAGMA ignore_check_constraints = OFF;\n" +
"PRAGMA auto_vacuum = NONE;\n" +
"PRAGMA secure_delete = OFF;\n" +
"BEGIN TRANSACTION;\n\n";
var currentTable = '';
var lines = document.getElementById('mysql').value.split('\n');
var skip = [/^CREATE DATABASE/i, /^USE/i, /^\/\*/i, /^--/i];
var keys = [];
// Used this site to test regexes: https://regex101.com/
lineLoop:
for (var i = 0; i < lines.length; i++) {
line = lines[i].trim();
// Skip lines that match regexes in the skip[] array above
for (var j = 0; j < skip.length; j++) if (skip[j].test(line)) continue lineLoop;
// Include all `INSERT` lines. Replace \' by ''
if (/^(INSERT|\()/i.test(line)) {
sqlite += line.replace(/\\'/gi, "''") + "\n";
continue;
}
// Print the ´CREATE´ line as is and capture the table name
if ((m = /^\s*CREATE TABLE.*[`"](.*)[`"]/i.exec(line)) !== null) {
currentTable = m[1];
sqlite += "\n" + line + "\n";
continue;
}
// Clean table end line like:
// ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8 COMMENT='By definition:\r\n- user_group #1 is administrator and will always have all permissions.\r\n- user_group #2 is guest and always have no permissions.\r\n';
if (line.startsWith(")")) {
sqlite += ");\n";
continue;
}
// Remove CONSTRAINT `fk_address_state1`" part from lines
line = line.replace(/^CONSTRAINT [`'"][\w]+[`'"][\s]+/gi, '');
// Replace "XXXXX KEY" by "KEY" except "PRIMARY KEY" "FOREIGN KEY" and "UNIQUE KEY"
line = line.replace(/^[^FOREIGN][^PRIMARY][^UNIQUE]\w+\s+KEY/gi, 'KEY');
// Lines starting with (UNIQUE) KEY are extracted so we declare them all at the end of the script
// We also append key name with table name to avoid duplicate index name
// Example: KEY `name` (`permission_name`)
if ((m = /^(UNIQUE\s)*KEY\s+[`'"](\w+)[`'"]\s+\([`'"](\w+)[`'"]/ig.exec(line)) !== null) {
keyUnique = m[1] || "";
keyName = m[2];
colName = m[3];
keys.push('CREATE '+ keyUnique +'INDEX `'+ currentTable + '_' + keyName + '` ON `' + currentTable +'` (`' + colName + '`);');
continue;
}
// Print all fields definition lines except "KEY" lines and lines starting with ")"
if (/^[^)]((?![\w]+\sKEY).)*$/gi.test(line)) {
// Clear invalid keywords
line = line.replace(/AUTO_INCREMENT|CHARACTER SET [^ ]+|CHARACTER SET [^ ]+|UNSIGNED/ig, "");
line = line.replace(/DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|COLLATE [^ ]+/ig, "");
line = line.replace(/COMMENT\s['"`].*['"`]/ig, "");
line = line.replace(/SET\([^)]+\)|ENUM[^)]+\)/ig, "TEXT ");
// Clear weird MySQL types such as varchar(40) and int(11)
line = line.replace(/int\([0-9]*\)/ig, "INTEGER");
line = line.replace(/varchar\([0-9]*\)|LONGTEXT/ig, "TEXT");
}
if (line != "") {
sqlite += line + "\n";
}
}
sqlite += "\n";
// Fix last table line with comma
sqlite = sqlite.replace(/,\n\);/g, "\n);");
// Include all gathered keys as CREATE INDEX
sqlite += "\n\n" + keys.join("\n") + "\n\n";
// Re-enable foreign key check
sqlite += "COMMIT;\n" +
"PRAGMA ignore_check_constraints = ON;\n" +
"PRAGMA foreign_keys = ON;\n" +
"PRAGMA journal_mode = WAL;\n" +
"PRAGMA synchronous = NORMAL;\n";
document.getElementById('sqlite').value = sqlite.replaceAll(/0x(\w+)/ig, "X'$1'");
}
</script>
<h1>MySQL to SQLite online converter 🔨</h1>
<textarea id="mysql">Paste MySQL SQL code here</textarea>
<button onclick="convert()">Convert</button>
<h1>SQLite result:</h1>
<textarea id="sqlite"></textarea>
<p>Client side only. No dependencies or server side processing. You can save this page for offline use.<br>
Send requests, bug reports & suggestions to git repo: <a href="http://github.com/ww9/mysql2sqlite">http://github.com/ww9/mysql2sqlite</a>
<br>License: <a href="http://unlicense.org/">The Unlicense</a>, <a href="https://gist.github.com/ww9/4c4481fb7b55186960a34266078c88b1">Public Domain</a>. As free as it gets.</p>
</body>
</html>