Incorrect conversion of table create clause

[dkam]

Here's a bug converting the create clause:

MySQL:

CREATE TABLE `example` (
  `episodenumber` decimal(10,1) unsigned DEFAULT NULL,
);

Which gets translated to

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE `example` (
  `episodtext   DEFAULT NULL
);
END TRANSACTION;

I think it's caused by the table name containing 'number' because changing the type from decimal(10,1) to another type didn't cause the error

[juelicher]

I encounter the same problem It was caused bei the gsub( /(ENUM|enum)[^)]+)/, "text " ) statement, which matched the string enum as part of the field name.

I changed it to gsub( /(ENUM|enum)([^)]+)/, "text " )

This worked for me, but I am nut sure if it is a valid solution for all possible MySQL databases.

[dumblob]

Thank you @juelicher for the possible workaround, I'll take a look at it.

[zmorris]

I am hitting this problem too, mysql:

  `can_enumerator_add` tinyint(1) NOT NULL DEFAULT '0',

results in sqlite:

,  `can_text  NOT NULL DEFAULT '0'

when it should be:

,  `can_enumerator_add` integer NOT NULL DEFAULT '0'

I'm not really sure what to do here, because mysql2sqlite runs orders of magnitude faster than the ruby gem sequel https://stackoverflow.com/a/11328913/539149 but without fully parsing a dump I don't know if it's possible to swizzle it with just regular expressions (situations like this would seem to crop up?)

[tm1000]

Same issue different type of statement

CREATE TABLE `broadcast_callees` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `phonenum` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `group_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `phonenum` (`phonenum`),
  KEY `name` (`name`),
  KEY `broadcast_callees_group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Gets converted to

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE `broadcast_callees` (
  `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT
,  `name` varchar(40) NOT NULL DEFAULT ''
,  `phontext ) NOT NULL DEFAULT ''
,  `group_id` integer NOT NULL DEFAULT '0'
);
CREATE INDEX "idx_broadcast_callees_phonenum" ON "broadcast_callees" (`phonenum`);
CREATE INDEX "idx_broadcast_callees_name" ON "broadcast_callees" (`name`);
CREATE INDEX "idx_broadcast_callees_broadcast_callees_group_id" ON "broadcast_callees" (`group_id`);
END TRANSACTION;

[vdechef]

I will try to address this in #71 because I will have to change the way BIT are parsed. So it would be the right time to improve other types parsing.