sqlite-database-integration icon indicating copy to clipboard operation
sqlite-database-integration copied to clipboard

Add `AUTO_INCREMENT` sequence to `show create` statements

Open jeroenpf opened this issue 1 year ago • 0 comments

When running the show create table query, the returned create statement does not contain the sequence number for the auto-increment column. It would be good to add this. It can be obtained from the sqlite_sequence table.

Here is an example of a create statement that has the current value for the auto increment column:

CREATE TABLE `wp_options` (
    `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
     `option_name` varchar(191) NOT NULL DEFAULT '',
     `option_value` longtext NOT NULL,
     `autoload` varchar(20) NOT NULL DEFAULT 'yes',
      PRIMARY KEY (`option_id`),
      UNIQUE KEY `option_name` (`option_name`),
      KEY `autoload` (`autoload`)
) ENGINE=InnoDB AUTO_INCREMENT=313 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

And here is one that the plugin returns:

DROP TABLE IF EXISTS `wp_options`;
CREATE TABLE `wp_options` (
	`option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	`option_name` varchar(191) NOT NULL DEFAULT '',
	`option_value` longtext NOT NULL DEFAULT '',
	`autoload` varchar(20) NOT NULL DEFAULT 'yes',
	PRIMARY KEY (`option_id`),
	KEY `autoload` (`autoload`),
	UNIQUE KEY `option_name` (`option_name`)
);

jeroenpf avatar Jul 29 '24 15:07 jeroenpf