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
| <?php
//A EDITER
$dsn = 'mysql:host=localhost;dbname=myDB';
$destination = 'D:\\dossier\\destination\\basesqlite.db';
$user = 'root';
$pass = '';
$tables = array(
'table1',
'table2'
//...
);
$opt = array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8' );
$mysql = new PDO($dsn, $user, $pass,$opt);
$sqlite = new PDO("sqlite:$destination");
foreach ($tables as $table) {
convert($mysql, $sqlite, $table);
}
function convert($mysql, $sqlite, $table)
{
$createFields = array();
$pkFields = array();
$indexFields = array();
$tableFields = array();
foreach ($mysql->query('SHOW COLUMNS FROM ' . $table) as $col) {
$tableFields[] = $col["Field"];
$fieldType = "TEXT";
if (stripos($col["Type"], "int(") !== false)
{
$fieldType = "INTEGER";
}
elseif (stripos($col["Type"], "datetime") !== false)
{
$fieldType = "DATETIME";
}
elseif (stripos($col["Type"], "date") !== false)
{
$fieldType = "DATE";
}
if ($col["Key"] == "PRI")
{
//$fieldType = "INTEGER";
$pkFields[] = $col["Field"];
}
else if ($col["Key"] == "MUL")
{
$indexFields[] = "CREATE INDEX " . $col["Field"] . "_index ON " . $table . "(" . $col["Field"] . ")";
}
$createFields[] = $col["Field"] . " " . $fieldType;
}
if (count($pkFields))
{
array_push($createFields, "PRIMARY KEY (" . implode(",", $pkFields) . ")");
}
//SQLITE
$sqlite->exec("CREATE TABLE " . $table . " (" . implode(",", $createFields) . ")");
//Insert
$insertSqlPart = str_repeat("?,", count($tableFields));
$insertSqlPart = substr($insertSqlPart, 0, -1);
$insertSql = "INSERT INTO " . $table . "(" . implode(",", $tableFields) . ") VALUES ( " . $insertSqlPart . " ) ";
$sth = $sqlite->prepare($insertSql);
var_dump($insertSql, $sth);
// get the number of records in the table
$sthCount = $mysql->query("SELECT count(*) FROM " . $table);
$row = $sthCount->fetch();
$numRows = $row[0];
$sthCount->closeCursor();
// read and convert all records
$pageLength = 100000;
$currentPage = 0;
$i = 0;
while (true) {
$sqlite->beginTransaction();
foreach ($mysql->query("SELECT * FROM " . $table . " LIMIT " . $currentPage . "," . $pageLength) as $row) {
$params = array();
foreach ($tableFields as $v) {
$params[] = $row[$v];
}
$r = $sth->execute($params);
if (!$r)
{
// error
echo print_r($sqlite->errorInfo(), true);
}
$i++;
}
$sqlite->commit();
if ($i < $numRows)
{
echo ".";
$currentPage += $pageLength;
}
else
{
break;
}
}
// create index
if (count($indexFields))
{
$sqlite->exec(implode(";", $indexFields));
}
} |
