0

I want to port php code from MySQL to support SQLite, but since SQL is a little different, I need change my sql script from MySQL to SQLite. for example: from

insert into table1 SET field1=value1,field2=value

to

insert into table1 (field1,field2) values(value1,value2)

This seems like it should be trivial, by trying Regex 

/(INSERT *INTO\s+)(\w+)\s+(SET)\s+([\w+\s*=\s*\w+,?\s*]+)/gi

it can parse the sql script, you can check it one regex101 online parser

The difficult is that I need replace the sql script in a php code,I'm not good with regular expressions, and I tried Google it.

$this->db->query("INSERT INTO `" . DB_PREFIX . "extension` SET `type` = 'dashboard', `code` = 'sale'");

and I need change it to:

$this->db->query("INSERT INTO `" . DB_PREFIX . "extension` (`type`,`code`) VALUES('dashboard', 'sale'");

here DB_PREFIX is a kind of const value like 'OC_'

more difficult is this:

$this->db->query("INSERT INTO `" . DB_PREFIX . "upload` SET `name` = '" . $this->db->escape($name) . "', `filename` = '" . $this->db->escape($filename) . "', `code` = '" . $this->db->escape($code) . "', `date_added` = NOW()");

my question is how can I use regex to resolve it.

maple wang
  • 9
  • 1
  • This a little unclear to me: Do you intend to put these regular expressions into production, or use them to transform a codebase once so that it supports SQLite? It sounds like you want to make your database layer more abstract, e.g. use [PDO](http://php.net/manual/en/book.pdo.php) and [prepared statements](https://stackoverflow.com/questions/18655706/pdo-with-insert-into-through-prepared-statements) (example). – sshine Apr 11 '18 at 05:57
  • yes I want to transfer the code to support sqlite. sqlite do not support none stand sql like insert into ... set ... script. so I need change the code via regex. – maple wang Apr 11 '18 at 15:13
  • MySQL supports that SET syntax. There is no need to convert it. – Rick James May 01 '18 at 23:00

0 Answers0