Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

2.3 Migration Questions/Issues #190

Closed
yang-xiaodong opened this issue Aug 30, 2018 · 10 comments

Comments

@yang-xiaodong
Copy link
Member

commented Aug 30, 2018

Consolidated thread for any migration questions and issues for CAP

Doc: https://www.cnblogs.com/savorboard/p/cap.html

Migration sql scripts:

  • MySQL
ALTER TABLE `cap.published` MODIFY Id BIGINT NOT NULL;
ALTER TABLE `cap.received` MODIFY Id BIGINT NOT NULL;
  • PostgreSql
ALTER TABLE cap.published ALTER COLUMN "Id" TYPE BIGINT;
ALTER TABLE cap.published ALTER COLUMN "Id" DROP DEFAULT;

ALTER TABLE cap.received ALTER COLUMN "Id" TYPE BIGINT;
ALTER TABLE cap.received ALTER COLUMN "Id" DROP DEFAULT;
  • SQLServer
ALTER TABLE Cap.[Published] DROP CONSTRAINT [PK_Cap.Published];
ALTER TABLE Cap.[Published] ALTER COLUMN Id BIGINT NOT NULL;
ALTER TABLE Cap.[Published] ADD CONSTRAINT [PK_Cap.Published] PRIMARY KEY(Id);

ALTER TABLE Cap.[Received] DROP CONSTRAINT [PK_Cap.[Received];
ALTER TABLE Cap.[Received] ALTER COLUMN Id BIGINT NOT NULL;
ALTER TABLE Cap.[Received] ADD CONSTRAINT [PK_Cap.Received] PRIMARY KEY(Id);
@wmowm

This comment has been minimized.

Copy link

commented Sep 19, 2018

最新版2.3里面移除了Dapper吗,没有看到Dapper的相关信息了? ORM框架是否可以支持NHibernate?

@yang-xiaodong

This comment has been minimized.

Copy link
Member Author

commented Sep 19, 2018

@wmowm

最新版2.3里面移除了Dapper吗,没有看到Dapper的相关信息了?

No, Dapper is still on the database extension library of CAP

ORM框架是否可以支持NHibernate?

We have no plans for NHibernate at now !

@mahonecode

This comment has been minimized.

Copy link

commented Oct 5, 2018

Whether there are plans to support the oracle database

@yang-xiaodong

This comment has been minimized.

Copy link
Member Author

commented Oct 5, 2018

@mahonecode We have no plans to support Oracle database now, you can implement Oracle database extension by yourself

@guxing2

This comment has been minimized.

Copy link

commented Oct 12, 2018

Is there a plan to support sqlite

@yang-xiaodong

This comment has been minimized.

Copy link
Member Author

commented Oct 13, 2018

@guxing2 Would you like to describe your scenario of using SQLite?

@guxing2

This comment has been minimized.

Copy link

commented Oct 24, 2018

@guxing2 Would you like to describe your scenario of using SQLite?
@yang-xiaodong ,As we know, EFCore support SQLite database and the SQLite is enough to some tiny site , but mail notification ,SMSS notification ,pay callback and so on are also necessary for these sites , all of these need event notification
too . Once the CAP can support the SQLite ,we can develope site with minimal dependence and have no dependencies on the server .

@yang-xiaodong

This comment has been minimized.

Copy link
Member Author

commented Oct 24, 2018

@guxing2 You can emulate other database implementations to support SQLite, and we have no plan to support SQLite and Oracle.

@zeekay18

This comment has been minimized.

Copy link

commented Nov 19, 2018

how can I add the initial migration for Sql Server?

@kexxxfeng

This comment has been minimized.

Copy link
Contributor

commented Nov 21, 2018

Migration sql scripts:

  • SQLServer
ALTER TABLE Cap.[Published] DROP CONSTRAINT [PK_Cap.Published];
ALTER TABLE Cap.[Published] ALTER COLUMN Id BIGINT NOT NULL;
ALTER TABLE Cap.[Published] ADD CONSTRAINT [PK_Cap.Published] PRIMARY KEY(Id);

ALTER TABLE Cap.[Received] DROP CONSTRAINT [PK_Cap.Received];
ALTER TABLE Cap.[Received] ALTER COLUMN Id BIGINT NOT NULL;
ALTER TABLE Cap.[Received] ADD CONSTRAINT [PK_Cap.Received] PRIMARY KEY(Id);

Next, disable auto increment

/* Before this script executed, close other work/window first */
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE Cap.Tmp_Published
	(
	Id bigint NOT NULL,
	Name nvarchar(200) NOT NULL,
	[Content] nvarchar(MAX) NULL,
	Retries int NOT NULL,
	Added datetime2(7) NOT NULL,
	ExpiresAt datetime2(7) NULL,
	StatusName nvarchar(50) NOT NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE Cap.Tmp_Published SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM Cap.Published)
	 EXEC('INSERT INTO Cap.Tmp_Published (Id, Name, [Content], Retries, Added, ExpiresAt, StatusName)
		SELECT Id, Name, [Content], Retries, Added, ExpiresAt, StatusName FROM Cap.Published WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE Cap.Published
GO
EXECUTE sp_rename N'Cap.Tmp_Published', N'Published', 'OBJECT' 
GO
ALTER TABLE Cap.Published ADD CONSTRAINT
	[PK_Cap.Published] PRIMARY KEY CLUSTERED 
	(
	Id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT
/*  Before this script executed, close other work/window first*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE Cap.Tmp_Received
	(
	Id bigint NOT NULL,
	Name nvarchar(200) NOT NULL,
	[Group] nvarchar(200) NULL,
	[Content] nvarchar(MAX) NULL,
	Retries int NOT NULL,
	Added datetime2(7) NOT NULL,
	ExpiresAt datetime2(7) NULL,
	StatusName nvarchar(50) NOT NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE Cap.Tmp_Received SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM Cap.Received)
	 EXEC('INSERT INTO Cap.Tmp_Received (Id, Name, [Group], [Content], Retries, Added, ExpiresAt, StatusName)
		SELECT Id, Name, [Group], [Content], Retries, Added, ExpiresAt, StatusName FROM Cap.Received WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE Cap.Received
GO
EXECUTE sp_rename N'Cap.Tmp_Received', N'Received', 'OBJECT' 
GO
ALTER TABLE Cap.Received ADD CONSTRAINT
	[PK_Cap.Received] PRIMARY KEY CLUSTERED 
	(
	Id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

I finished this migration with these scripts. Hope it worked for you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
6 participants
You can’t perform that action at this time.