Alter-User-Tables.sql

Aus PPwiki
Wechseln zu: Navigation, Suche 
 
drop table CDSROLEOBJECTS cascade constraints PURGE;
drop table CDSUSERROLES cascade constraints PURGE;
drop table CDSOBJECTS cascade constraints PURGE;
drop table CDSROLES cascade constraints PURGE;
drop table CDSUSERS cascade constraints PURGE;
drop table CDSLOGINDETAILS cascade constraints PURGE;
drop table CDSLOGINHISTORY cascade constraints PURGE;
drop table CDSUSERASSIGNMENT cascade constraints PURGE;
drop table CDSUSERCLAIMS cascade constraints PURGE;
drop table CDSUSERFAVORITES cascade constraints PURGE;
drop table CDSUSERHISTORY cascade constraints PURGE;
drop table CDSUSERLOGINS cascade constraints PURGE;
drop table CDSUSERSETTINGS cascade constraints PURGE;

CREATE TABLE "PAT"."CdsLoginDetails" 
   (	"Id" NVARCHAR2(128), 
	"Fingerprint" NVARCHAR2(128), 
	"UserId" NVARCHAR2(128), 
	"SessionId" NVARCHAR2(256), 
	"UserAgent" NVARCHAR2(256), 
	"UserHostName" NVARCHAR2(256), 
	"UserHostAddress" NVARCHAR2(256), 
	"Browser" NVARCHAR2(256), 
	"MobileDevice" NVARCHAR2(256), 
	"FirstLogin" DATE, 
	"LastLogin" DATE, 
	"LastAliveSignal" DATE, 
	"ModuleId" NVARCHAR2(50)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE TABLE "PAT"."CdsLoginHistory" 
   (	"Id" NVARCHAR2(128), 
	"CdsLoginDetailId" NVARCHAR2(128), 
	"UserId" NVARCHAR2(128), 
	"Text" NVARCHAR2(1000), 
	"LogLevel" NVARCHAR2(128), 
	"Time" DATE, 
	"ModuleId" NVARCHAR2(50), 
	"Version" NVARCHAR2(50)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE TABLE "PAT"."CdsRoles" 
   (	"Id" NVARCHAR2(128), 
	"Name" NVARCHAR2(256), 
	"Discriminator" NVARCHAR2(128)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE TABLE "PAT"."CdsUserClaims" 
   (	"Id" NUMBER(10,0), 
	"UserId" NVARCHAR2(128), 
	"ClaimType" NCLOB, 
	"ClaimValue" NCLOB
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL" 
 LOB ("ClaimType") STORE AS BASICFILE (
  TABLESPACE "JUNGPFL" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
 LOB ("ClaimValue") STORE AS BASICFILE (
  TABLESPACE "JUNGPFL" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));
  
CREATE TABLE "PAT"."CdsUserLogins" 
   (	"LoginProvider" NVARCHAR2(128), 
	"ProviderKey" NVARCHAR2(128), 
	"UserId" NVARCHAR2(128), 
	"IdentityUser_Id" NVARCHAR2(128)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE TABLE "PAT"."CdsUserRoles" 
   (	"UserId" NVARCHAR2(128), 
	"RoleId" NVARCHAR2(128)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE TABLE "PAT"."CdsUsers" 
   (	"Id" NVARCHAR2(128), 
	"Language" NVARCHAR2(10), 
	"Type" NVARCHAR2(20), 
	"WebEnabled" NUMBER(1,0), 
	"Name" NVARCHAR2(50), 
	"PasswordResetEndDate" DATE, 
	"RegisterEmailSent" NUMBER(1,0), 
	"CreatedBy" NVARCHAR2(10), 
	"ChangedBy" NVARCHAR2(10), 
	"CreatedAt" NVARCHAR2(22), 
	"ChangedAt" NVARCHAR2(22), 
	"Email" NVARCHAR2(256), 
	"EmailConfirmed" NUMBER(1,0), 
	"PasswordHash" NCLOB, 
	"SecurityStamp" NCLOB, 
	"PhoneNumber" NCLOB, 
	"PhoneNumberConfirmed" NUMBER(1,0), 
	"TwoFactorEnabled" NUMBER(1,0), 
	"LockoutEndDateUtc" DATE, 
	"LockoutEnabled" NUMBER(1,0), 
	"AccessFailedCount" NUMBER(10,0), 
	"UserName" NVARCHAR2(256), 
	"DesktopEnabled" NUMBER(1,0), 
	"CommunicationType" NVARCHAR2(20), 
	"PasswordResetEnabled" NUMBER(1,0), 
	"PasswordSetDate" DATE, 
	"Remarks" NVARCHAR2(1000), 
	"OneTimeCodeEncrypted" NVARCHAR2(1000), 
	"PasswordEncrypted" NVARCHAR2(1000)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL" 
 LOB ("PasswordHash") STORE AS BASICFILE (
  TABLESPACE "JUNGPFL" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
 LOB ("SecurityStamp") STORE AS BASICFILE (
  TABLESPACE "JUNGPFL" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
 LOB ("PhoneNumber") STORE AS BASICFILE (
  TABLESPACE "JUNGPFL" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));
  
CREATE UNIQUE INDEX "PAT"."PK_CdsLoginDetails" ON "PAT"."CdsLoginDetails" ("Id") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE UNIQUE INDEX "PAT"."PK_CdsLoginHistory" ON "PAT"."CdsLoginHistory" ("Id") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE UNIQUE INDEX "PAT"."PK_CdsRoles" ON "PAT"."CdsRoles" ("Id") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE UNIQUE INDEX "PAT"."IX_CdsRoles_Name" ON "PAT"."CdsRoles" ("Name") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE UNIQUE INDEX "PAT"."PK_CdsUserClaims" ON "PAT"."CdsUserClaims" ("Id") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE UNIQUE INDEX "PAT"."PK_CdsUserLogins" ON "PAT"."CdsUserLogins" ("LoginProvider", "ProviderKey", "UserId") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE INDEX "PAT"."IX_CdsUserLogins_Id_1940767604" ON "PAT"."CdsUserLogins" ("IdentityUser_Id") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE UNIQUE INDEX "PAT"."PK_CdsUserRoles" ON "PAT"."CdsUserRoles" ("UserId", "RoleId") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
CREATE INDEX "PAT"."IX_CdsUserRoles_RoleId" ON "PAT"."CdsUserRoles" ("RoleId") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
REATE UNIQUE INDEX "PAT"."PK_CdsUsers" ON "PAT"."CdsUsers" ("Id") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL";
  
ALTER TABLE "PAT"."CdsLoginDetails" ADD CONSTRAINT "PK_CdsLoginDetails" PRIMARY KEY ("Id")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL"  ENABLE;
  
ALTER TABLE "PAT"."CdsLoginDetails" MODIFY ("LastAliveSignal" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsLoginDetails" MODIFY ("LastLogin" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsLoginDetails" MODIFY ("FirstLogin" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsLoginDetails" MODIFY ("UserId" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsLoginDetails" MODIFY ("Fingerprint" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsLoginDetails" MODIFY ("Id" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsLoginHistory" ADD CONSTRAINT "PK_CdsLoginHistory" PRIMARY KEY ("Id")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL"  ENABLE;
ALTER TABLE "PAT"."CdsLoginHistory" MODIFY ("Time" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsLoginHistory" MODIFY ("LogLevel" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsLoginHistory" MODIFY ("Id" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsRoles" ADD CONSTRAINT "PK_CdsRoles" PRIMARY KEY ("Id")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL"  ENABLE;
  
ALTER TABLE "PAT"."CdsRoles" MODIFY ("Discriminator" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsRoles" MODIFY ("Name" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsRoles" MODIFY ("Id" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUserClaims" ADD CONSTRAINT "PK_CdsUserClaims" PRIMARY KEY ("Id")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL"  ENABLE;
  
ALTER TABLE "PAT"."CdsUserClaims" MODIFY ("UserId" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUserClaims" MODIFY ("Id" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUserLogins" ADD CONSTRAINT "PK_CdsUserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey", "UserId")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL"  ENABLE;
  
ALTER TABLE "PAT"."CdsUserLogins" MODIFY ("UserId" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUserLogins" MODIFY ("ProviderKey" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUserLogins" MODIFY ("LoginProvider" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUserRoles" ADD CONSTRAINT "PK_CdsUserRoles" PRIMARY KEY ("UserId", "RoleId")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL"  ENABLE;
ALTER TABLE "PAT"."CdsUserRoles" MODIFY ("RoleId" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUserRoles" MODIFY ("UserId" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" MODIFY ("PasswordResetEnabled" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" ADD CONSTRAINT "PK_CdsUsers" PRIMARY KEY ("Id")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JUNGPFL"  ENABLE;
ALTER TABLE "PAT"."CdsUsers" MODIFY ("UserName" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" MODIFY ("AccessFailedCount" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" MODIFY ("LockoutEnabled" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" MODIFY ("TwoFactorEnabled" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" MODIFY ("PhoneNumberConfirmed" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" MODIFY ("EmailConfirmed" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" MODIFY ("RegisterEmailSent" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" MODIFY ("PasswordResetEndDate" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" MODIFY ("WebEnabled" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" MODIFY ("DesktopEnabled" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUsers" MODIFY ("Id" NOT NULL ENABLE);
ALTER TABLE "PAT"."CdsUserLogins" ADD CONSTRAINT "FK_CdsUserLogins_Ide_722323630" FOREIGN KEY ("IdentityUser_Id")
	  REFERENCES "PAT"."CdsUsers" ("Id") ENABLE;
ALTER TABLE "PAT"."CdsUserRoles" ADD CONSTRAINT "FK_CdsUserRoles_RoleId" FOREIGN KEY ("RoleId")
	  REFERENCES "PAT"."CdsRoles" ("Id") ON DELETE CASCADE ENABLE;
	  
CREATE TABLE "CdsObjects" 
(
 "Id" NVARCHAR2(150) NOT NULL 
, "Name" NVARCHAR2(150) NOT NULL 
, "ParentId" NVARCHAR2(150) NOT NULL 
, "Project" NVARCHAR2(40) NOT NULL 
, "CheckAuto" NUMBER(1) DEFAULT 0 NOT NULL 
, CONSTRAINT "CdsObjects_PK" PRIMARY KEY 
 (
 "Id" 
 )
 USING INDEX 
 (
 CREATE UNIQUE INDEX "CdsObjects_PK" ON "CdsObjects" ("Id" ASC) 
 )
 ENABLE 
);

CREATE TABLE "CdsRoleObjects" 
(
 "RoleId" NVARCHAR2(128) NOT NULL 
, "ObjectId" NVARCHAR2(150) NOT NULL 
, "PrivilegeType" NVARCHAR2(10) 
, CONSTRAINT "CdsRoleObjects_PK" PRIMARY KEY 
 (
 "ObjectId" 
 , "RoleId" 
 )
 ENABLE 
);

ALTER TABLE "CdsObjects" MODIFY ("ParentId" NULL);
ALTER TABLE "CdsUsers" ADD ("IsSuperuser" NUMBER(1) NOT NULL);
ALTER TABLE "CdsUsers" ADD ("ForceNewPassword" NUMBER(1) NOT NULL);


ALTER TABLE "CdsRoles" ADD ("Type" NVARCHAR2(20) );

CREATE TABLE "CdsUserAssignment" 
(
  "Timest" VARCHAR2(22) NOT NULL 
, "UserId" NVARCHAR2(128) NOT NULL
, "EntityId" NVARCHAR2(50) 
, "EntityType" NVARCHAR2(50) NOT NULL 
, "Scope" NVARCHAR2(50) NOT NULL
, CONSTRAINT "CdsUserAssignment_PK" PRIMARY KEY 
  (
    "Timest" 
  )
  USING INDEX 
  (
      CREATE UNIQUE INDEX "CdsUserAssignment_PK" ON "CdsUserAssignment" ("Timest" ASC) 
  )
  ENABLE 
);


CREATE TABLE "CdsUserSettings" 
(
  "Timest" VARCHAR2(22) NOT NULL 
, "UserId" NVARCHAR2(128) NOT NULL
, "Key" NVARCHAR2(50) NOT NULL
, "Value" NVARCHAR2(20) NOT NULL
, CONSTRAINT "CdsUserSettings_PK" PRIMARY KEY 
  (
    "Timest" 
  )
  USING INDEX 
  (
      CREATE UNIQUE INDEX "CdsUserSettings_PK" ON "CdsUserSettings" ("Timest" ASC) 
  )
  ENABLE 
);

CREATE TABLE "CdsUserFavorites" 
(
  "Id" VARCHAR2(22) NOT NULL 
, "UserId" NVARCHAR2(128) NOT NULL 
, "Url" NVARCHAR2(256) 
, "CreatedAt" DATE 
, "Description" VARCHAR2(256) 
, "Scope" VARCHAR2(50)
, CONSTRAINT "CdsUserFavorites_PK" PRIMARY KEY 
  (
    "Id" 
  )
  USING INDEX 
  (
      CREATE UNIQUE INDEX "CdsUserFavorites_PK" ON "CdsUserFavorites" ("Id" ASC) 
  )
  ENABLE 
);


CREATE TABLE "CdsUserHistory" 
(
  "Id" VARCHAR2(22) NOT NULL 
, "UserId" NVARCHAR2(128) NOT NULL 
, "Url" NVARCHAR2(256) 
, "CreatedAt" DATE 
, "Description" VARCHAR2(256) 
, "Scope" VARCHAR2(50)
, "Type" VARCHAR2(50)
, "TypeId" VARCHAR2(100)
, CONSTRAINT "CdsUserHistory_PK" PRIMARY KEY 
  (
    "Id" 
  )
  USING INDEX 
  (
      CREATE UNIQUE INDEX "CdsUserHistory_PK" ON "CdsUserHistory" ("Id" ASC) 
  )
  ENABLE 
);

ALTER TABLE "CdsUserAssignment" ADD ("ModuleId" NVARCHAR2(50) );
ALTER TABLE "CdsUserAssignment" ADD ("EntityGroup" NVARCHAR2(50) );
ALTER TABLE "CdsUserHistory" ADD "LastAccess" DATE;
ALTER TABLE "CdsUserHistory" ADD "CreatedAt" DATE;
ALTER TABLE "CdsUserFavorites" MODIFY "CreatedAt" VARCHAR2(22);
alter table "CdsUserSettings" modify "Timest" VARCHAR2(22);

ALTER TABLE "CdsObjects" MODIFY "Id" NVARCHAR2(500);
ALTER TABLE "CdsRoleObjects" MODIFY "ObjectId" NVARCHAR2(500);

alter table "CdsUserHistory" drop column "CreatedAt";

--PO 09.01.2018
ALTER TABLE "CdsLoginDetails" ADD "ServerHostName" VARCHAR2(256 CHAR);
ALTER TABLE "CdsLoginHistory" ADD "ServerHostName" VARCHAR2(256 CHAR);
ALTER TABLE "CdsUserSettings" ADD "Project" VARCHAR2(50 CHAR);

--PO 01.03.2018
alter table "CdsUserSettings" modify "Value" NVARCHAR2(200);

--PO 12.04.2018
alter table "CdsObjects" add "CodeFullName" NVARCHAR2(200);
alter table "CdsObjects" add "CodeType" NVARCHAR2(200);

--PO 12.06.2018 Dynamische Objekte werden jetzt über Data Range abgebildet)
delete from "CdsObjects" where "Project" = 'PatMobile' and "Id" like 'Mobile.View.Availability.Filter.Form.%';
delete from "CdsObjects" where "Project" = 'PatMobile' and "Id" like 'Mobile.View.Availability.Filter.Department.%';
delete from "CdsObjects" where "Project" = 'PatMobile' and "Id" like 'Mobile.View.Availability.Filter.ProductGroup.%';
delete from "CdsObjects" where "Project" = 'PatMobile' and "Id" like 'Mobile.View.Availability.Filter.ArticleGroup.%';
delete from "CdsObjects" where "Project" = 'PatMobile' and "Id" like 'Mobile.View.Availability.Filter.ProductionDevice.%';

delete from "CdsRoleObjects" where "ObjectId" like 'Mobile.View.Availability.Filter.Form.%';
delete from "CdsRoleObjects" where "ObjectId" like 'Mobile.View.Availability.Filter.Department.%';
delete from "CdsRoleObjects" where "ObjectId" like 'Mobile.View.Availability.Filter.ProductGroup.%';
delete from "CdsRoleObjects" where "ObjectId" like 'Mobile.View.Availability.Filter.ArticleGroup.%';
delete from "CdsRoleObjects" where "ObjectId" like 'Mobile.View.Availability.Filter.ProductionDevice.%';

--PO 19.11.2018 Neue Spalte für Windows Username
ALTER TABLE "CdsUsers" ADD ("WindowsUsername" NVARCHAR2(256));
Abgerufen von „http://wiki.pplant.de/index.php?title=Alter-User-Tables.sql&oldid=8074“