升级

云印星本地部署版本需要的sql

云印象改造sql

创建支付配置表

CREATE TABLE [dbo].[tblSysPay] (
  [Id] bigint NOT NULL,
  [PayName] varchar(255) NOT NULL,
  [ChannelId] varchar(50) NOT NULL,
  [PayChannel] varchar(50) NOT NULL,
  [PayConfig] nvarchar(max) NULL,
  [SupportType] int NOT NULL,
  [IsEnable] int DEFAULT 1 NULL,
  [Memo] varchar(500) NULL,
  [Property] int DEFAULT 0 NULL,
  [Kind] int DEFAULT 0 NULL,
  [CreateTime] datetime2 DEFAULT GETDATE() NULL,
  [UpdateTime] datetime2 DEFAULT GETDATE() NULL,
  PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

CREATE NONCLUSTERED INDEX [idx_ChannelId]
ON [dbo].[Untitled] (
  [ChannelId]
)
GO

CREATE NONCLUSTERED INDEX [idx_PayChannel]
ON [dbo].[Untitled] (
  [PayChannel]
)
GO

CREATE TRIGGER [dbo].[trg_tblSysPay_UpdateTime]
ON [dbo].[Untitled]
FOR UPDATE
AS
BEGIN
  UPDATE tblSysPay 
  SET UpdateTime = GETDATE()
  WHERE Id IN (SELECT Id FROM inserted)
END
GO

EXEC sp_addextendedproperty
'MS_Description', N'支付方式名称',
'SCHEMA', N'dbo',
'TABLE', N'Untitled',
'COLUMN', N'PayName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'支付标识',
'SCHEMA', N'dbo',
'TABLE', N'Untitled',
'COLUMN', N'ChannelId'
GO

EXEC sp_addextendedproperty
'MS_Description', N'枚举名称存储',
'SCHEMA', N'dbo',
'TABLE', N'Untitled',
'COLUMN', N'PayChannel'
GO

EXEC sp_addextendedproperty
'MS_Description', N'JSON 类型，使用自定义处理器 CustomJsonTypeHandler',
'SCHEMA', N'dbo',
'TABLE', N'Untitled',
'COLUMN', N'PayConfig'
GO

EXEC sp_addextendedproperty
'MS_Description', N'支持类型（1/2/4/8 位运算）',
'SCHEMA', N'dbo',
'TABLE', N'Untitled',
'COLUMN', N'SupportType'
GO

EXEC sp_addextendedproperty
'MS_Description', N'是否启用',
'SCHEMA', N'dbo',
'TABLE', N'Untitled',
'COLUMN', N'IsEnable'
GO

EXEC sp_addextendedproperty
'MS_Description', N'备注信息',
'SCHEMA', N'dbo',
'TABLE', N'Untitled',
'COLUMN', N'Memo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'支付设置表',
'SCHEMA', N'dbo',
'TABLE', N'Untitled'

tblUserOrder改造channel

ALTER TABLE [dbo].[tblUserOrder] ALTER COLUMN [Channel] varchar(50) NOT NULL


还要加一个WalletId(钱包id),Property,Kind

tblUserWxPay改造openId

ALTER TABLE [dbo].[tblUserWxPay] ALTER COLUMN [OpenId] varchar(255) COLLATE Chinese_PRC_CI_AS NULL

新增个人钱包表tblWallet

CREATE TABLE dbo.tblWallet (
    id BIGINT PRIMARY KEY NOT NULL,
    accNo INT NOT NULL,
    totalAmount BIGINT DEFAULT 0,
    CanUseAmount BIGINT DEFAULT 0,
    FreezeAmount BIGINT DEFAULT 0,
    WalletStatus INT NOT NULL,
    Memo VARCHAR(255),
    Kind INT DEFAULT 0,
    Property INT DEFAULT 0,
    Version INT DEFAULT 0,
    CreateTime DATETIME DEFAULT GETDATE(),
    UpdateTime DATETIME
);

-- 创建唯一索引（会员号唯一）
CREATE UNIQUE INDEX UQ_Wallet_accNo ON dbo.tblWallet(accNo);

-- 创建更新时间触发器
GO
CREATE TRIGGER UpdateWalletTimestamp
ON dbo.tblWallet
AFTER UPDATE
AS
BEGIN
    UPDATE dbo.tblWallet
    SET UpdateTime = GETDATE()
    FROM inserted
    WHERE dbo.tblWallet.id = inserted.id
END;

CREATE TRIGGER InsertWalletTimestamp
ON dbo.tblWallet
AFTER INSERT
AS
BEGIN
    UPDATE dbo.tblWallet
    SET CreateTime = GETDATE()
    FROM inserted
    WHERE dbo.tblWallet.id = inserted.id
END;

新增个人钱包和订单关联表tblWalletOrderRelated

CREATE TABLE [dbo].[tblWalletOrderRelated] (
  [Id] bigint NOT NULL,
  [WalletId] bigint NOT NULL,
  [OrderOid] int NOT NULL,
  [OrderNo] varchar(255) NOT NULL,
  [TotalAmount] bigint DEFAULT 0 NOT NULL,
  [RemainAmount] bigint DEFAULT 0 NOT NULL,
  [Kind] int DEFAULT 0 NULL,
  [Property] int DEFAULT 0 NULL,
  [Memo] varchar(512) NULL,
  [Version] int DEFAULT 0 NULL,
  [CreateTime] datetime2 DEFAULT getdate() NULL,
  [UpdateTime] datetime2 DEFAULT getdate() NULL,
  PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

CREATE NONCLUSTERED INDEX [IDX_WalletId]
ON [dbo].[tblWalletOrderRelated] (
  [WalletId]
)
GO

EXEC sp_addextendedproperty
'MS_Description', N'钱包id',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletOrderRelated',
'COLUMN', N'WalletId'
GO

EXEC sp_addextendedproperty
'MS_Description', N'订单id',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletOrderRelated',
'COLUMN', N'OrderOid'
GO

EXEC sp_addextendedproperty
'MS_Description', N'订单编号',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletOrderRelated',
'COLUMN', N'OrderNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'充值金额',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletOrderRelated',
'COLUMN', N'TotalAmount'
GO

EXEC sp_addextendedproperty
'MS_Description', N'剩余金额',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletOrderRelated',
'COLUMN', N'RemainAmount'
GO

EXEC sp_addextendedproperty
'MS_Description', N'钱包订单关联表',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletOrderRelated'

CREATE TRIGGER UpdateWalletOrderRelatedTimestamp
ON dbo.tblWalletOrderRelated
AFTER UPDATE
AS
BEGIN
    UPDATE dbo.tblWalletOrderRelated
    SET UpdateTime = GETDATE()
    FROM inserted
    WHERE dbo.tblWalletOrderRelated.id = inserted.id
END;

CREATE TRIGGER [dbo].[InsertWalletOrderRelatedTimestamp]
ON [dbo].[tblWalletOrderRelated]
FOR INSERT
AS
BEGIN
    UPDATE dbo.tblWalletOrderRelated
    SET CreateTime = GETDATE()
    FROM inserted
    WHERE dbo.tblWalletOrderRelated.id = inserted.id
END;

钱包消费记录表

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[tblWalletConsumeRecord]') AND type IN ('U'))
    DROP TABLE [dbo].[tblWalletConsumeRecord]
GO

CREATE TABLE [dbo].[tblWalletConsumeRecord] (
  [Id] bigint  NOT NULL,
  [OrderId] int  NOT NULL,
  [RelatedId] bigint  NOT NULL,
  [Amount] bigint  NOT NULL,
  [RefundAmount] bigint DEFAULT 0 NULL,
  [Kind] int DEFAULT 0 NULL,
  [Property] int DEFAULT 0 NULL,
  [Memo] nvarchar(max) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[tblWalletConsumeRecord] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'主键ID',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletConsumeRecord',
'COLUMN', N'Id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'任务订单ID',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletConsumeRecord',
'COLUMN', N'OrderId'
GO

EXEC sp_addextendedproperty
'MS_Description', N'关联ID',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletConsumeRecord',
'COLUMN', N'RelatedId'
GO

EXEC sp_addextendedproperty
'MS_Description', N'金额',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletConsumeRecord',
'COLUMN', N'Amount'
GO

EXEC sp_addextendedproperty
'MS_Description', N'已退金额',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletConsumeRecord',
'COLUMN', N'RefundAmount'
GO

EXEC sp_addextendedproperty
'MS_Description', N'类型',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletConsumeRecord',
'COLUMN', N'Kind'
GO

EXEC sp_addextendedproperty
'MS_Description', N'属性',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletConsumeRecord',
'COLUMN', N'Property'
GO

EXEC sp_addextendedproperty
'MS_Description', N'备注',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletConsumeRecord',
'COLUMN', N'Memo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'钱包消费记录表',
'SCHEMA', N'dbo',
'TABLE', N'tblWalletConsumeRecord'
GO


-- ----------------------------
-- Primary Key structure for table tblWalletConsumeRecord
-- ----------------------------
ALTER TABLE [dbo].[tblWalletConsumeRecord] ADD CONSTRAINT [PK__tblWalle__3214EC075614BF03] PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

用户和第三方数据关联表

CREATE TABLE dbo.tblUserThirdRelated (
    Id BIGINT PRIMARY KEY NOT NULL,
    AccNo INT NOT NULL,
    RelatedData VARCHAR(255) NULL,
    RelatedType VARCHAR(255) NULL,
    Kind INT DEFAULT 0 NULL,
    Property INT DEFAULT 0 NULL,
    Memo VARCHAR(255) NULL,
    CreateTime DATETIME NULL,
    UpdateTime DATETIME NULL
);
GO

EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', @value = N'用户号',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'tblUserThirdRelated',
    @level2type = N'COLUMN', @level2name = N'AccNo';
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', @value = N'关联数据',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'tblUserThirdRelated',
    @level2type = N'COLUMN', @level2name = N'RelatedData';
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', @value = N'关联类型',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'tblUserThirdRelated',
    @level2type = N'COLUMN', @level2name = N'RelatedType';
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', @value = N'备注',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'tblUserThirdRelated',
    @level2type = N'COLUMN', @level2name = N'Memo';
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', @value = N'创建时间',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'tblUserThirdRelated',
    @level2type = N'COLUMN', @level2name = N'CreateTime';
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', @value = N'更新时间',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'tblUserThirdRelated',
    @level2type = N'COLUMN', @level2name = N'UpdateTime';
GO

CREATE TRIGGER trg_tblUserThirdRelated_Insert
ON dbo.tblUserThirdRelated
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE t
    SET 
        t.CreateTime = GETDATE(),
        t.UpdateTime = GETDATE()
    FROM tblUserThirdRelated t
    INNER JOIN inserted i ON t.Id = i.Id;
END;
GO

CREATE TRIGGER trg_tblUserThirdRelated_Update
ON dbo.tblUserThirdRelated
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE t
    SET t.UpdateTime = GETDATE()
    FROM tblUserThirdRelated t
    INNER JOIN inserted i ON t.Id = i.Id;
END;
GO

达梦改造timestamp

SELECT DISTINCT TABLE_NAME 
FROM USER_TAB_COLUMNS 
WHERE DATA_TYPE = 'TIMESTAMP';


ALTER TABLE CCMADMIN.TBLJOBLOG MODIFY CREATETIME timestamp with time zone NULL;
ALTER TABLE CCMADMIN.TBLMANAGEROPLOG MODIFY OPDATE timestamp with time zone NOT NULL;
ALTER TABLE CCMADMIN.TBLMANAGER_NEW MODIFY MODTIME timestamp with time zone NOT NULL;
ALTER TABLE CCMADMIN.TBLMENU MODIFY CREATETIME timestamp with time ZONE NOT NULL;
ALTER TABLE CCMADMIN.TBLMENU MODIFY MODIFYTIME timestamp with time zone NOT NULL;
ALTER TABLE CCMADMIN.TBLPRINTER MODIFY CREATETIME timestamp with time zone NULL;
ALTER TABLE CCMADMIN.TBLPRINTERMAINTAININFOREC MODIFY CREATETIME timestamp with time ZONE NOT NULL;
ALTER TABLE CCMADMIN.TBLPRINTERREGISTERLOG MODIFY REGISTERDATE timestamp with time zone NULL;
ALTER TABLE CCMADMIN.TBLPRTSRVCOUNTER MODIFY DATE timestamp with time ZONE NOT NULL;
ALTER TABLE CCMADMIN.TBLROLE MODIFY CREATETIME timestamp with time ZONE NOT NULL;
ALTER TABLE CCMADMIN.TBLROLE MODIFY MODIFYTIME timestamp with time ZONE NOT NULL;
ALTER TABLE CCMADMIN.TBLSYSPAY MODIFY CREATETIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLSYSPAY MODIFY UPDATETIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLUSEROPERATELOG MODIFY OPERATETIME timestamp with time ZONE NOT NULL;
ALTER TABLE CCMADMIN.TBLUSERORDER MODIFY ORDERTIME timestamp with time ZONE NOT NULL;
ALTER TABLE CCMADMIN.TBLUSERORDER MODIFY PAYDATE timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLUSERORDER MODIFY REFUNDDATE timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLUSERREFUND MODIFY SUBMITTIME timestamp with time ZONE NOT NULL;
ALTER TABLE CCMADMIN.TBLUSERREFUND MODIFY REFUNDTIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLUSERTHIRDRELATED MODIFY CREATETIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLUSERTHIRDRELATED MODIFY UPDATETIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLUSERWXPAY MODIFY SUCCESSTIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLUSERWXREFUND MODIFY SUCCESSTIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLUSERWXREFUND MODIFY CREATETIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLUSERZYPAY MODIFY PAIDTIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLUSERZYPAY MODIFY CREATETIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLWALLET MODIFY CREATETIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLWALLET MODIFY UPDATETIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLWALLETORDERRELATED MODIFY CREATETIME timestamp WITH time ZONE NULL;
ALTER TABLE CCMADMIN.TBLWALLETORDERRELATED MODIFY UPDATETIME timestamp WITH time ZONE NULL;
COMMIT;