-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsqlite_dump.php
109 lines (94 loc) · 3.43 KB
/
sqlite_dump.php
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
<?php
/*
sqlite database dump
written by brodikAE
v.0.0.1
*/
class sqlite_dump {
function dump_sqlite($_db){
$db = new SQLite3($_db);
$db->busyTimeout(30000);
// create common begin sql
$sql = "";
$sql .= "PRAGMA foreign_keys=OFF;\n";
$sql .= "BEGIN TRANSACTION;\n";
// create tables sql
$tables = $db->query("SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%';");
while ($table = $tables->fetchArray(SQLITE3_NUM)) {
$sql .= $db->querySingle("SELECT sql FROM sqlite_master WHERE name = '{$table[0]}'").";";
$rows = $db->query("SELECT * FROM {$table[0]}");
$insert_sql = "INSERT INTO \"{$table[0]}\" ";
$columns = $db->query("PRAGMA table_info({$table[0]})");
$fieldnames = array();
while($column = $columns->fetchArray(SQLITE3_ASSOC)){
$fieldnames[] = $column["name"];
}
$insert_sql .= "VALUES";
while ($row=$rows->fetchArray(SQLITE3_ASSOC)) {
foreach ($row as $k => $v) {
if(is_int($v)){
$val = $db->escapeString($v);
$row[$k] = $val;
}elseif(is_null($v)){
$row[$k] = "NULL";
}else{
//solve some issues in my scripts
$double_chars['in'] = array("\x8c", "\x9c", "\xc6", "\xd0", "\xde", "\xdf", "\xe6", "\xf0", "\xfe");
$double_chars['out'] = array('OE', 'oe', 'AE', 'DH', 'TH', 'ss', 'ae', 'dh', 'th');
$v = str_replace($double_chars['in'], $double_chars['out'], $v);
$val = $db->escapeString($v);
$row[$k] = "'".$val."'";
}
}
$sql .= "\n".$insert_sql."(".implode(",",$row).");";
}
$sql = rtrim($sql, ";").";\n";
}
// create sqlite_sequence sql
$sql .= "DELETE FROM sqlite_sequence;";
$sql_sequence = $db->query("SELECT name FROM sqlite_master WHERE name = 'sqlite_sequence';");
$table = $sql_sequence->fetchArray(SQLITE3_NUM);
$rows = $db->query("SELECT * FROM {$table[0]}");
$insert_sql = "INSERT INTO \"{$table[0]}\" ";
$columns = $db->query("PRAGMA table_info({$table[0]})");
$fieldnames = array();
while($column = $columns->fetchArray(SQLITE3_ASSOC)){
$fieldnames[] = $column["name"];
}
$insert_sql .= "VALUES";
while ($row=$rows->fetchArray(SQLITE3_ASSOC)) {
foreach ($row as $k => $v) {
if(is_int($v)){
$val = $db->escapeString($v);
$row[$k] = $val;
}elseif(is_null($v)){
$row[$k] = "NULL";
}else{
//solve some issues
$double_chars['in'] = array("\x8c", "\x9c", "\xc6", "\xd0", "\xde", "\xdf", "\xe6", "\xf0", "\xfe");
$double_chars['out'] = array('OE', 'oe', 'AE', 'DH', 'TH', 'ss', 'ae', 'dh', 'th');
$v = str_replace($double_chars['in'], $double_chars['out'], $v);
$val = $db->escapeString($v);
$row[$k] = "'".$val."'";
}
}
$sql .= "\n".$insert_sql."(".implode(",",$row).");";
}
$sql = rtrim($sql, ";").";\n";
// create non sqlite index sql
$tables = $db->query("SELECT name FROM sqlite_master WHERE type ='index' AND name NOT LIKE 'sqlite_autoindex%';");
while ($table = $tables->fetchArray(SQLITE3_NUM)) {
$sql .= $db->querySingle("SELECT sql FROM sqlite_master WHERE name = '{$table[0]}'").";"."\n";
}
// create view and trigger sql
$tables = $db->query("SELECT name FROM sqlite_master WHERE type ='view' OR type ='trigger';");
while ($table = $tables->fetchArray(SQLITE3_NUM)) {
$sql .= $db->querySingle("SELECT sql FROM sqlite_master WHERE name = '{$table[0]}'").";"."\n";
}
// create common end sql
$sql .= "COMMIT;";
return $sql;
}
}
$sqlite_dump = new sqlite_dump();
?>