Context Navigation

Back to Ticket #4678

Ticket #4678: mysql2pgsql.php

File mysql2pgsql.php, 8.7 KB (added by usleepless, 18 years ago)

Line
1	<?php
2	$pg_result = 0;
3
4	global $insert_tablename;
5	global $insert_fieldname;
6	global $pg_user, $pg_password, $pg_server;
7
8	function mysql_num_rows($result) { return pg_num_rows($result); }
9	function mysql_numrows($result) { return pg_num_rows($result); }
10	function mysql_num_fields($result) { return 1; }
11	function mysql_fetch_field($result) { return 'tablename'; }
12	function mysql_fetch_object($result) { return pg_fetch_object($result); }
13	function mysql_free_result($result) { return pg_free_result($result); }
14	function mysql_affected_rows() { return pg_affected_rows($GLOBALS[pg_result]); }
15	function mysql_fetch_row($result) { return pg_fetch_row($result); }
16	function mysql_data_seek($result, $offset) { return pg_result_seek ( $result, $offset ); }
17	function mysql_error() { global $pg_user; if($pg_user == '') return pg_last_error(); else return '';}
18	function mysql_fetch_assoc($result) { return pg_fetch_assoc($result); }
19	function mysql_escape_string($s) { return $s; }
20	function mysql_get_server_info() { return '4.1.0'; }
21	function mysql_result($result, $i, $fieldname) { return pg_fetch_result($result, $i, $fieldname); }
22
23	function mysql_connect($dbserver, $dbuser, $dbpass)
24	{
25	global $pg_user, $pg_password, $pg_server;
26
27	$pg_user = $dbuser;
28	$pg_password = $dbpass;
29	$pg_server = $dbserver;
30
31	// return pg_connect("host=$dbserver user=$dbuser password=$dbpass dbname=");
32	return 1;
33	}
34
35	function mysql_fetch_array($result)
36	{
37	$result = pg_fetch_array($result);
38
39	if( is_array($result) )
40	foreach($result as $v => $k )
41	$result[$v] = trim($k);
42
43	return $result;
44	}
45
46	function mysql_select_db($dbname, $connection_id = 0)
47	{
48	global $pg_user, $pg_password, $pg_server;
49	$conn = pg_connect("host=$pg_server user=$pg_user password=$pg_password dbname=$dbname");
50	$pg_user = '';
51	$pg_password = '';
52	$pg_server = '';
53	return $conn;
54	}
55
56	function sanitize_insertinto($sql_in)
57	{
58	$pattern = '/INSERT INTO (\w+)/';
59	preg_match($pattern, $sql_in, $matches, PREG_OFFSET_CAPTURE);
60	$insert_stmt = $matches[0][0];
61	$s = preg_replace($pattern, '', $sql_in);
62	// match fieldnames
63	$s = trim($s, " \n\t()");
64	$fields = split(', ', $s);
65
66	// match quoted values
67	$pattern = "/'(.*?)'/";
68	preg_match_all($pattern, $sql_in, $matches);
69	$matches = $matches[0];
70
71	$field_list = '';
72	$value_list = '';
73
74	foreach($matches as $i=>$match)
75	if($match != "''")
76	{
77	$value_list .= $match . ', ';
78	$field_list .= $fields[$i] . ', ';
79	}
80	$value_list = trim($value_list, ' ,');
81	$field_list = trim($field_list, ' ,');
82
83	$insert_stmt = trim($insert_stmt, " \t\n()");
84
85	$sql_out = trim( "$insert_stmt ($field_list) VALUES ($value_list)", ' (');
86	$sql_out = str_replace("\n", "", $sql_out);
87	return $sql_out;
88	}
89
90	function mysql_query($sql)
91	{
92	global $insert_tablename;
93	global $insert_fieldname;
94
95	// Wordpress
96	$sql = str_replace("SHOW TABLES", "select tablename from pg_tables where schemaname = 'public'", $sql);
97
98	// Sanitize
99	// remove illegal characters
100	$sql = str_replace("`", "", $sql);
101	$sql = str_replace("DEFAULT CHARACTER SET utf8", "", $sql);
102
103	// translate types
104	if(strstr($sql, "CREATE TABLE"))
105	{
106	$sql = str_replace("bigint(20)", "int", $sql);
107	$sql = str_replace("tinyint(1)", "int", $sql);
108	$sql = str_replace("longtext", "text", $sql);
109	$sql = str_replace("tinytext", "text", $sql);
110	$sql = str_replace("mediumtext", "text", $sql);
111	$sql = str_replace("default '0000-00-00 00:00:00'", "default now()", $sql);
112	$sql = str_replace("unsigned", "", $sql);
113	$sql = str_replace("int(11)", "int", $sql);
114	$sql = str_replace("int(4)", "int", $sql);
115	$sql = str_replace("datetime", "timestamp", $sql);
116
117	// WP specific types
118	$sql = str_replace("enum('0','1','spam')", "varchar(4)", $sql);
119	$sql = str_replace("enum('yes','no')", "varchar(3)", $sql);
120	$sql = str_replace("enum('Y','N')", "varchar(1)", $sql);
121	$sql = str_replace("enum('publish','draft','private','static','object','attachment','inherit','future')", "varchar(15)", $sql);
122	$sql = str_replace("enum('open','closed','registered_only')", "varchar(20)", $sql);
123	$sql = str_replace("enum('open','closed')", "varchar(10)", $sql);
124
125	// unsupported INDEX/KEY construct
126	$pattern = '/\,[ \n]+KEY[ ]+[a-zA-Z_]+[ ]+$[a-zA-Z_,]+$/';
127	$sql = preg_replace($pattern, "", $sql);
128
129	// fix auto_increment
130	$pattern = '/CREATE TABLE (\w+)/';
131	preg_match($pattern, $sql, $matches, PREG_OFFSET_CAPTURE);
132	$match = $matches[0][0];
133	$match_list = split(' ', $match);
134	$tablename = $match_list[2];
135
136	$pattern = '/int[ ]+NOT NULL auto_increment/';
137	preg_match($pattern, $sql, $matches, PREG_OFFSET_CAPTURE);
138	if($matches)
139	{
140	$match = $matches[0][0];
141	$match_list = split(' ' , $match);
142	$match = $tablename . '_seq';
143
144	$sql = str_replace("NOT NULL auto_increment", "default nextval('$match'::text) NOT NULL", $sql);
145	$sql .= "; CREATE SEQUENCE $match";
146	}
147
148	$pattern = '/int[ ]+NOT NULL auto_increment/';
149	$sql = preg_replace($pattern, 'serial NOT NULL', $sql);
150
151	if(strstr($sql, "wp_posts"))
152	{
153	$sql .= "; alter table wp_posts alter post_excerpt set default ''; alter table wp_posts alter to_ping set default ''; alter table wp_posts alter pinged set default ''; alter table wp_posts alter post_content_filtered set default '';";
154	$sql .= " ; alter table wp_posts alter post_date_gmt set default now() at time zone 'gmt'; alter table wp_posts alter post_modified_gmt set default now() at time zone 'gmt' ";
155	}
156
157	} // CREATE TABLE
158
159	// field names with CAPITALS
160	if(strstr($sql, "ID"))
161	{
162	$pattern = '/[a-z_]+ID/';
163	$sql = preg_replace($pattern, '"$0"', $sql);
164
165	$pattern = '/ ID /';
166	$sql = preg_replace($pattern, ' "ID" ', $sql);
167
168	$pattern = '/$ID$/';
169	$sql = preg_replace($pattern, '("ID")', $sql);
170
171	$pattern = '/ ID /';
172	$sql = preg_replace($pattern, ' "ID" ', $sql);
173
174	$pattern = '/ ID,/';
175	$sql = preg_replace($pattern, ' "ID",', $sql);
176
177	$pattern = '/ ID=/';
178	$sql = preg_replace($pattern, ' "ID"=', $sql);
179
180	$pattern = '/\.ID/';
181	$sql = preg_replace($pattern, '."ID"', $sql);
182	} // CAPITALS
183
184	// get rid of IGNORE before INSERT manipulation
185	$sql = str_replace(' IGNORE ', ' ' , $sql);
186
187	if(strstr($sql,"INSERT"))
188	{
189	$sql = str_replace('(1,',"('1',", $sql);
190
191	$pattern = '/INSERT INTO (\w+)[ \t\n]+\([ a-zA-Z_"]+/';
192	preg_match($pattern, $sql, $matches, PREG_OFFSET_CAPTURE);
193	$match = $matches[0][0];
194	$match_list = split(' ', $match);
195	$insert_tablename = $match_list[2];
196	if($insert_tablename)
197	{
198	$insert_fieldname = trim($match_list[3],' () ');
199	if(!$insert_fieldname)
200	$insert_fieldname = trim($match_list[4],' () ');
201	}
202
203	// fix inserts into wp_categories
204	if(strstr($sql, "INSERT INTO wp_categories"))
205	{
206	$sql = str_replace('"cat_ID",', '', $sql);
207	$sql = str_replace("VALUES ('0',", "VALUES(", $sql);
208	}
209
210	$sql = str_replace( "INSERT INTO wp_post2cat (rel_id, post_id, category_id) VALUES ('1', 1, 1)", "INSERT INTO wp_post2cat (post_id, category_id) VALUES (1, 1)", $sql);
211
212	// filter out '' entries in inserts
213	if(strstr($sql, "''") && strstr($sql, 'wp_posts'))
214	$sql = sanitize_insertinto($sql);
215
216	} // INSERT
217
218	// Wordpress, operation
219	$sql = str_replace('SQL_CALC_FOUND_ROWS', '', $sql);
220	$sql = str_replace('FOUND_ROWS()', '10', $sql);
221
222	$pattern = '/LIMIT[ ]+(\d+),[ ]*(\d+)/';
223	$sql = preg_replace($pattern, 'LIMIT $2 OFFSET $1', $sql);
224
225	$sql = str_replace('DISTINCT', '', $sql);
226	$sql = str_replace('INTERVAL 120 MINUTE', "'120 minutes'::interval", $sql);
227	$sql = str_replace('ORDER BY post_date', 'ORDER BY YEAR(post_date), MONTH(post_date)', $sql);
228	$sql = str_replace('GROUP BY wp_posts."ID"', '' , $sql);
229	$sql = str_replace('GROUP BY meta_key', '', $sql);
230	$sql = str_replace("!= ''", '<> 0', $sql);
231
232	$GLOBALS[pg_result] = pg_query($sql);
233
234	return $GLOBALS[pg_result];
235	}
236
237	function mysql_insert_id($table)
238	{
239	global $insert_tablename, $insert_fieldname;
240
241	$tbls = split("\n", $insert_tablename); // workaround for bad tablename
242	$t = $tbls[0] . '_seq';
243
244	if($insert_fieldname == '"cat_ID"' \|\| $insert_fieldname == 'rel_id')
245	$res = pg_query("SELECT MAX($insert_fieldname)+1 FROM $insert_tablename");
246	else
247	$res = pg_query("SELECT currval('$t')");
248
249	return pg_fetch_result($res, 0, 0);
250	}
251
252	?>

Trac UI Preferences

Download in other formats:

Original Format

Make WordPress Core

Context Navigation

Ticket #4678: mysql2pgsql.php

Download in other formats: