Exporting from SQLite to MSSQL fails when trying to reimport

[tobias--]

• Gitea version (or commit ref): 1.1.0
• Git version: irrelevant
• Operating system: Windows 2008 R2
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • Not relevant
• Log gist:

Description

When dumping a gitea instance setup with sqlite to be exported to mssql, the resulting SQL seems to be invalid

gitea dump --database mssql

Beginning of the gitea-db.sql with private values replaced with "XXX" for privacy

/*Generated by xorm v0.6.0.1022 2017-03-29 09:10:14, from sqlite3 to MSSQL*/

IF NOT EXISTS (SELECT [name] FROM sys.tables WHERE [name] = 'user' ) CREATE TABLE "user" ("id" BIGINT PRIMARY KEY IDENTITY NOT NULL, "lower_name" VARCHAR(255) NOT NULL, "name" VARCHAR(255) NOT NULL, "full_name" VARCHAR(255) NULL, "email" VARCHAR(255) NOT NULL, "keep_email_private" TINYINT NULL, "passwd" VARCHAR(255) NOT NULL, "login_type" INT NULL, "login_source" BIGINT NOT NULL DEFAULT 0, "login_name" VARCHAR(255) NULL, "type" INT NULL, "location" VARCHAR(255) NULL, "website" VARCHAR(255) NULL, "rands" VARCHAR(10) NULL, "salt" VARCHAR(10) NULL, "created_unix" BIGINT NULL, "updated_unix" BIGINT NULL, "last_login_unix" BIGINT NULL, "last_repo_visibility" TINYINT NULL, "max_repo_creation" INT NOT NULL DEFAULT -1, "is_active" TINYINT NULL, "is_admin" TINYINT NULL, "allow_git_hook" TINYINT NULL, "allow_import_local" TINYINT NULL, "allow_create_organization" TINYINT NULL DEFAULT 1, "prohibit_login" TINYINT NULL, "avatar" VARCHAR(2048) NOT NULL, "avatar_email" VARCHAR(255) NOT NULL, "use_custom_avatar" TINYINT NULL, "num_followers" INT NULL, "num_following" INT NOT NULL DEFAULT 0, "num_stars" INT NULL, "num_repos" INT NULL, "description" VARCHAR(255) NULL, "num_teams" INT NULL, "num_members" INT NULL, "diff_view_style" VARCHAR(255) NOT NULL DEFAULT '');;
CREATE INDEX "IDX_user_updated_unix" ON "user" ("updated_unix");
CREATE INDEX "IDX_user_last_login_unix" ON "user" ("last_login_unix");
CREATE INDEX "IDX_user_is_active" ON "user" ("is_active");
CREATE UNIQUE INDEX "UQE_user_lower_name" ON "user" ("lower_name");
CREATE UNIQUE INDEX "UQE_user_name" ON "user" ("name");
CREATE INDEX "IDX_user_created_unix" ON "user" ("created_unix");
INSERT INTO "user" ("id", "lower_name", "name", "full_name", "email", "keep_email_private", "passwd", "login_type", "login_source", "login_name", "type", "location", "website", "rands", "salt", "created_unix", "updated_unix", "last_login_unix", "last_repo_visibility", "max_repo_creation", "is_active", "is_admin", "allow_git_hook", "allow_import_local", "allow_create_organization", "prohibit_login", "avatar", "avatar_email", "use_custom_avatar", "num_followers", "num_following", "num_stars", "num_repos", "description", "num_teams", "num_members", "diff_view_style") VALUES (1, 'XXX', 'XXX', 'XXX', 'XXX@XXX', false, 'XXXX', 0, 0, '', 0, '', '', 'XXXX', 'XXXX', 1474029352, 1490713202, 1490599283, false, -1, true, true, true, true, true, false, '1', 'XXX@XXX', false, 0, 1, 1, 0, '', 0, 0, 'unified');
[...]

SQL-Error reported when executing:

Msg 207, Level 16, State 1, Line 10
Invalid column name 'false'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'false'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'true'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'true'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'true'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'true'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'true'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'false'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'false'.

Screenshots

If this issue involves the Web Interface, please include a screenshot

[tobias--]

Even though this might be stating the obvious: The problem is that booleans are mapped to TINYINT (I guess BIT would be more appropriate) and the inserts use true and false instead of 0 and 1 as values.

When correcting this manually, you will encounter the next problem with the generated script: INDENTITY_INSERT needs to be set to ON before inserting rows:

Cannot insert explicit value for identity column in table 'user' when IDENTITY_INSERT is set to OFF.

[lunny]

dump will be deprecated and replaced by #1637

[ptman]

Does it even make sense to handle translation between databases?

[bkcsoft]

@ptman IMO no, and #1637 will not handle that either.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

[techknowlogick]

Closing issue. Please open a post in the forum, or re-open this ticket if this is still an issue.