-- This script will update Insight Collection server v3.1 to version v4.1--
-- Please Make a backup copy of your database before applying this script.--


create table DTEntityToEntityMap(
  EntityTypeID    number not null,
  EntityID        number not null,
  RelEntityTypeID number not null,
  RelEntityID     number not null,
  constraint PK_DTEntityToEntityMap primary key(EntityTypeID,EntityID,RelEntityTypeID,RelEntityID)
);





alter table DTHierarchyNodes drop constraint
  PK_DTHierarchyNodes
;

alter table DTHierarchyNodes add
  IsLocalVocabularyNode number
;

alter table DTHierarchyNodes  add
  constraint PK_DTHierarchyNodes primary key(HierarchyID,HierarchyIndex);




create table DTTermToEntityMap(
  TermID       number not null,
  EntityTypeID number not null,
  EntityID     number not null,
  FieldID      number not null,
  Grouping     number not null,
  constraint PK_DTTermToEntityMap primary key(TermID,EntityTypeID,EntityID,FieldID,Grouping)
);


create table DTValueToEntityMap(
  ValueID            number          not null,
  EntityTypeID       number          not null,
  EntityID           number          not null,
  RelEntityTypeID    number          not null,
  RelEntityID        number          not null,
  ParentEntityTypeID number          not null,
  ParentEntityID     number          not null,
  Preferred          number,
  DisplayOrder       number,
  Grouping           varchar2(50),
  constraint PK_DTValueToEntity primary key(ValueID,EntityTypeID,EntityID,RelEntityTypeID,RelEntityID,ParentEntityTypeID,ParentEntityID)
);



alter table DTValues add
  (ValueDate_Start number,
  ValueDate_End number)
;






create table IRApprovalLevels(
  ApprovalLevel        number not null constraint PK_IRApprovalLevels primary key,
  CommitToInvertedData number default (0),
  CommitToSourceData   number default (0)
);

INSERT INTO IRApprovalLevels (ApprovalLevel, CommitToInvertedData, CommitToSourceData) VALUES (0, 1, 1);


create table IREntityChanges(
  ChangeID                number            not null constraint PK_IREntityChanges primary key,
  ChangeCategory          number,
  ChangeType              number,
  ChangeStatus            number,
  ApprovalLevel           number,
  CommittedToInvertedData number            default (0),
  CommittedToSourceData   number            default (0),
  EntityTypeID            number,
  EntityID                number,
  UserID                  number,
  ChangeTimeStamp         varchar2(50),
  LastEditorUserID        number            default (0),
  LastEditorTimeStamp     varchar2(50),
  FieldID                 number,
  ValueChange             varchar2(4000),
  RelEntityTypeID         number,
  RelEntityID             number,
  MediaID                 number,
  MetaData                varchar2(255)
);

create table IREntityLocks(
  EntityTypeID  number          not null,
  EntityID      number          not null,
  UserID        number,
  IsLocked      number,
  LockTimeStamp varchar2(50),
  constraint PK_IREntityLocks primary key(EntityTypeID,EntityID)
);

create table IREntityMediaMap(
  EntityTypeID number not null,
  EntityID     number not null,
  MediaID      number not null,
  constraint PK_IREntityMediaMap primary key(EntityTypeID,EntityID,MediaID)
);

ALTER TABLE IREntityMediaMap MODIFY(EntityTypeID DEFAULT 1);

CREATE TABLE TMP_IRFIELDS (FIELDID NUMBER NOT NULL, 
    FIELDGROUPID NUMBER, DISPLAYNAME VARCHAR2(100), 
    FIELDNAME VARCHAR2(100), PREFERREDFIELDNAME 
    VARCHAR2(50), FIELDTYPE NUMBER, TABLEID NUMBER, 
    JOINPATH NUMBER, DELIMITERTYPE NUMBER, DISPLAYORDER 
    NUMBER, DATAFIELDSEARCHABLE NUMBER, KEYWORDSEARCHABLE 
    NUMBER, DISPLAYEDINTHUMBNAIL NUMBER, DISPLAYEDINDATA 
    NUMBER, SORTABLE NUMBER, SEARCHPICK NUMBER, VOCABFIELD 
    NUMBER, LONGSTRING NUMBER, MULTIVALUE NUMBER, ISDATE 
    NUMBER, HIERARCHYMODE NUMBER, HIERARCHYID NUMBER, 
    GROUPINGFIELDNAME VARCHAR2(50), GROUPINGTABLEID 
    NUMBER, SELECTLISTBEHAVIOR NUMBER(10) DEFAULT 1);


 INSERT INTO TMP_IRFIELDS (FIELDID, FIELDGROUPID,DISPLAYNAME ,FIELDNAME, PREFERREDFIELDNAME, FIELDTYPE, TABLEID, JOINPATH, DELIMITERTYPE, DISPLAYORDER, DATAFIELDSEARCHABLE, KEYWORDSEARCHABLE, DISPLAYEDINTHUMBNAIL, DISPLAYEDINDATA, SORTABLE, SEARCHPICK, VOCABFIELD, LONGSTRING, MULTIVALUE, ISDATE, HIERARCHYMODE, HIERARCHYID, GROUPINGFIELDNAME, GROUPINGTABLEID)select FIELDID, FIELDGROUPID,DISPLAYNAME ,FIELDNAME, PREFERREDFIELDNAME, FIELDTYPE, TABLEID, JOINPATH, DELIMITERTYPE, DISPLAYORDER, DATAFIELDSEARCHABLE, KEYWORDSEARCHABLE, DISPLAYEDINTHUMBNAIL, DISPLAYEDINDATA, SORTABLE, SEARCHPICK, VOCABFIELD, LONGSTRING, MULTIVALUE, ISDATE, HIERARCHYMODE, HIERARCHYID, GROUPINGFIELDNAME, GROUPINGTABLEID FROM IRFIELDS;
 
 rename IRFIELDS to IRFIELDS_v31;
 rename TMP_IRFIELDS to IRFIELDS;
 
 alter table IRFIELDS add  CONSTRAINT PK_IRFIELDS PRIMARY KEY(FIELDID);
 
 commit;

CREATE TABLE TMP_IRGROUPIMAGEMAP (GROUPID NUMBER 
    DEFAULT 1 NOT NULL, IMAGEID NUMBER NOT NULL, MVISERIES 
    NUMBER DEFAULT 0 NOT NULL, MPDSERIES NUMBER DEFAULT 0 NOT NULL); 


alter table TMP_IRGROUPIMAGEMAP  add
  constraint PK_tmpIRGroupImageMap primary key(GroupID,ImageID,MviSeries,MpdSeries);

INSERT INTO TMP_IRGROUPIMAGEMAP(groupid, imageid, mviseries)
 select groupid, imageid, mviseries FROM IRGroupImageMap;
   
alter table TMP_IRGROUPIMAGEMAP drop constraint PK_tmpIRGroupImageMap;

--drop table IRGroupImageMap CASCADE CONSTRAINT;
rename IRGROUPIMAGEMAP to IRGroupImageMap_v31;
rename TMP_IRGROUPIMAGEMAP to IRGroupImageMap;


alter table IRGroupImageMap  add
  constraint PK_IRGroupImageMap primary key(GroupID,ImageID,MviSeries,MpdSeries);

commit;


create table IRImageToDocumentMap(
  ImageID    number not null,
  DocumentID number not null,
  constraint PK_IRImageToDocumentMap primary key(ImageID,DocumentID) 
);



create table IRLinkFields(
  FieldID          number          not null constraint PK_IRLinkFields primary key ,
  FieldName        varchar2(50),
  FieldDisplayName varchar2(50),
  DisplayOrder     number,
  IsDate           number
);

create table IRLinkRepository(
  LinkID          number,
  LinkCode        varchar2(1000),
  LinkTimeStamp   varchar2(255),
  LinkRegion      varchar2(255),
  ImageID         number,
  ObjectID        number,
  Author          varchar2(255),
  Subject         varchar2(255),
  Category        varchar2(255),
  Organization    varchar2(255),
  Email           varchar2(255),
  Username        varchar2(255),
  DateStamp       varchar2(255),
  DateStamp_Start number,
  DateStamp_End   number,
  AnnotationText  varchar2(2000),
  WebAddress      varchar2(2000)
);



alter table IRObjectDimension drop constraint
  PK_IRObjectDimension
;

alter table IRObjectDimension add
  (WidthQualifierFieldID number,
  WidthQualifierValue varchar2(50),
  HeightQualifierFieldID number,
  HeightQualifierValue varchar2(50),
  WidthMetricFieldID number,
  HeightMetricFieldID number,
  MetricNameMap varchar2(100));

alter table IRObjectDimension  add
  constraint PK_IRObjectDimension primary key(WidthFieldID,HeightFieldID,Metric);


alter table IRProfile drop constraint
  PK_IRProfile
;

alter table IRProfile add
  (ApprovalLevel number,
  MEDEPermissions number)
;

alter table IRSPS drop constraint
  PK_IRSPS
;

alter table IRSPS modify
  (URL varchar2(255));

alter table IRSPS  add
  constraint PK_IRSPS primary key(SPSNum,ResolutionSize,MediaType,CollectionId,Format);



alter table IRUserGroups drop constraint
  PK_IRUserGroups
;

ALTER TABLE IRUSERGROUPS 
    DROP (STARTIPADDRESS, ENDIPADDRESS) CASCADE CONSTRAINTS;

alter table IRUserGroups  add
  constraint PK_IRUserGroups primary key(GroupID);

create table IRUsers(
  UserID             number          not null constraint PK_IRUsers primary key,
  LoginName          varchar2(50),
  FullName           varchar2(50),
  Organization       varchar2(50),
  Email              varchar2(50),
  Phone              varchar2(50),
  Address            varchar2(50),
  LastVisitTimeStamp varchar2(50)
);



create table ISDocumentDescriptions(
  DocumentID number          not null,
  LevelID    number          not null,
  LevelValue number          not null,
  LevelTerm  varchar2(50),
  constraint PK_ISDocumentLevelDescriptions primary key(DocumentID,LevelID,LevelValue)
);

create table ISDocumentLevelDescriptions(
  DocumentID number          not null,
  LevelID    number          not null,
  LevelValue number          not null,
  LevelTerm  varchar2(50)
);

create table ISDocumentPages(
  PageID       number not null constraint PK_ISDocumentPages primary key,
  DocumentID   number not null,
  DisplayOrder number,
  ObjectID     number not null,
  ImageID      number not null,
  LinkCount    number,
  Level1       number,
  Level2       number,
  Level3       number,
  Level4       number,
  Level5       number,
  Level6       number,
  Level7       number
);

create table ISDocuments(
  DocumentID         number           not null constraint PK_ISDocuments primary key,
  DocumentName       varchar2(255),
  LinkVisibilityMode number,
  GenerateDocument   number,
  IdentityFieldID    number,
  IdentityValue      varchar2(255),
  TargetGroupID      number,
  MaxLevelCount      number,
  LevelName1         varchar2(50),
  LevelFieldID1      number,
  LevelName2         varchar2(50),
  LevelFieldID2      number,
  LevelName3         varchar2(50),
  LevelFieldID3      number,
  LevelName4         varchar2(50),
  LevelFieldID4      number,
  LevelName5         varchar2(50),
  LevelFieldID5      number,
  LevelName6         varchar2(50),
  LevelFieldID6      number,
  LevelName7         varchar2(50),
  LevelFieldID7      number
);

create table ISEntityFields(
  EntityTypeID              number          not null,
  FieldID                   number          not null,
  JoinID                    number,
  FieldTableID              number,
  FieldValueColumnName      varchar2(50),
  PreferredValueColumnName  varchar2(50),
  RestrictionLevel          number,
  IsRequired                number,
  IntegerRange              varchar2(50),
  IsValueListAllowed        number,
  MinApprovalToAddNewValue  number,
  RepeatValuesAllowed       number,
  DisplayInRelatedEntities  number,
  AlwaysValidateNewValues   number,
  HierarchyIDColumnName     varchar2(50),
  HierarchyNodeIDColumnName varchar2(50),
  constraint PK_ISEntityTypeFields primary key(EntityTypeID,FieldID)
);

create table ISEntityRelationships(
  EntityTypeID     number not null,
  RelEntityTypeID  number not null,
  JoinID           number,
  SourceMapTableID number,
  PublishRelatedMedia number,
  RelatedEntitiesAllowed number,
  constraint PK_ISEntityTypeRelationships primary key(EntityTypeID,RelEntityTypeID)
);

create table ISEntityTypes(
  EntityTypeID          number          not null constraint PK_ISEntityTypes primary key,
  EntityTypeName        varchar2(50),
  EntityTypeDisplayName varchar2(50),
  EntityTableID         number,
  RestrictionLevel      number,
  EntityIDColumnName    varchar2(50),
  MaxEntityID           number,
  IsVocabulary          number,
  IsDependent           number,
  IsDupeCheckEnabled    number,
  IsRefCheckEnabled     number,
  IsPreferredEntity	number,
  EntityTypeDisplayOrder	number
);

alter table ISHierarchies drop constraint
  PK_HierarchyID
;

alter table ISHierarchies modify
  (IsANSICompliant number not null)
;

alter table ISHierarchies modify
  (Generate number not null)
;


alter table ISTables modify
  (Hierarchy number)
;

alter table ISTables drop
  (Flattened)
;



create table ITEntityToEntityMap(
  EntityTypeID    number not null,
  EntityID        number not null,
  RelEntityTypeID number not null,
  RelEntityID     number not null,
  constraint PK_ITEntityToEntityMap primary key(EntityTypeID,EntityID,RelEntityTypeID,RelEntityID)
);



alter table ITHierarchyNodes drop constraint
  PK_ITHierarchyNodes
;

alter table ITHierarchyNodes add
  (IsLocalVocabularyNode number)
;

alter table ITHierarchyNodes  add
  constraint PK_ITHierarchyNodes primary key(HierarchyID,HierarchyIndex);



create table ITTermToEntityMap(
  TermID       number not null,
  EntityTypeID number not null,
  EntityID     number not null,
  FieldID      number not null,
  Grouping     number not null,
  constraint PK_ITTermToEntityMap primary key(TermID,EntityTypeID,EntityID,FieldID,Grouping)
);



create table ITValueToEntityMap(
  ValueID            number          not null,
  EntityTypeID       number          not null,
  EntityID           number          not null,
  RelEntityTypeID    number          not null,
  RelEntityID        number          not null,
  ParentEntityTypeID number          not null,
  ParentEntityID     number          not null,
  Preferred          number,
  DisplayOrder       number,
  Grouping           varchar2(50),
  constraint PK_ITValueToEntityMap primary key(ValueID,EntityTypeID,EntityID,RelEntityTypeID,RelEntityID,ParentEntityTypeID,ParentEntityID)
);



alter table ITValues add
  (ValueDate_Start number,
  ValueDate_End number)
;



alter table SLStandardFields modify
  (SelectListBehavior number)
;




create table lexicon(
  thesauri_id             number          not null,
  lexicon_id              number          not null,
  term                    varchar2(80),
  node_type               varchar2(10),
  differentiator          varchar2(40),
  access_code             number,
  valid_catalog_term_flag number,
  constraint PK_lexicon primary key(thesauri_id,lexicon_id)
);

create table lexicon_term_relation(
  thesauri_id       number         not null,
  lexicon_id        number         not null,
  relation_type     varchar2(4) not null,
  relatedlexicon_id number         not null,
  constraint PK_lexicon_term_relation primary key(thesauri_id,lexicon_id,relation_type,relatedlexicon_id)
);



ALTER TABLE ISJOINS
    ADD (JOINTYPE NUMBER(10) DEFAULT 1 NOT NULL);

ALTER TABLE IRVersion ADD
	(IsInscribeCompliant number NULL)
;



    

ALTER TABLE IRFIELDS 
    ADD ("SELECTLISTBEHAVIOR" NUMBER(10) DEFAULT 1); 


alter table IRCollectionConfigurationInfo add
  (RemoteLaunchBaseURL varchar2(255),
  RemoteLaunchHyperlinkTemplate varchar2(1000),
  IsPersonalCollection NUMBER NULL,
  PcOwnerID NUMBER NULL,
  PcOwnerUserGroupID NUMBER NULL,
  PcStandardName varchar2(255) NULL,
  PcUserGroupMemberPrivs NUMBER NULL,
  PcNonUserGroupMemberPrivs NUMBER NULL,
  IsPcMediaShared NUMBER NULL
  )
;
ALTER TABLE SLSTANDARDSLOOKUP ADD (ISTEMPLATE NUMBER NULL, STANDARDCREATORID NUMBER NULL)

alter table IRUserGroups add BrowserIPAddressRanges varchar2(1000);

alter table IRMaxIDs add MaxLpsID NUMBER;

alter table IRProfile add 
(ExportPresentationPriv NUMBER, EditPersonalCollections NUMBER);

UPDATE IRMaxIDs SET MaxLpsID=(SELECT (MAX(LPSid)+1) AS MaxLpsID FROM  IRLPS);

--(optional)--UPDATE IRMaxIDs SET MaxMediaID=(SELECT (MAX(Imageid)+10000000) AS MaxMediaID FROM  IRImagefiles);

UPDATE IRVersion SET Version='4';

UPDATE IRVersion SET IsInscribeCompliant=0 WHERE IsInscribeCompliant IS NULL OR IsInscribeCompliant='';

UPDATE IRFields SET SELECTLISTBEHAVIOR=1 WHERE SELECTLISTBEHAVIOR IS NULL OR SELECTLISTBEHAVIOR='';

UPDATE SLStandardFields SET SELECTLISTBEHAVIOR=1 WHERE SELECTLISTBEHAVIOR IS NULL OR SELECTLISTBEHAVIOR='';

UPDATE IRUserGroups SET BrowserIPAddressRanges='0.0.0.0-255.255.255.255' WHERE BrowserIPAddressRanges IS NULL OR BrowserIPAddressRanges='';

UPDATE IRProfile SET ExportPresentationPriv=8;

UPDATE ISEntityRelationships SET RelatedEntitiesAllowed=0 WHERE RelatedEntitiesAllowed IS NULL;

UPDATE ISEntityRelationships SET PublishRelatedMedia=0 WHERE PublishRelatedMedia IS NULL;

UPDATE ISEntityTypes SET IsPreferredEntity=0 WHERE IsPreferredEntity IS NULL;

UPDATE IRCollectionConfigurationInfo SET HelpGroup='http://www.lunaimaging.com/v4_0help/insight/insight4.htm' WHERE HelpGroup like '%help-group.html' OR HelpGroup = '' OR HelpGroup  IS NULL;

UPDATE IRCollectionConfigurationInfo SET HelpImage='http://www.lunaimaging.com/v4_0help/insight/insight4.htm' WHERE HelpImage like '%help-image.html' OR HelpImage = '' OR HelpImage  IS NULL;

INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (1,'Author','author',0,0); 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (2,'Subject','subject',0,0); 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (3,'Category','category',0,0); 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (4,'Organization','organization',0,0); 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (5,'Email','email',0,0); 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (6,'Username','user name',0,0); 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (7,'DateStamp','date stamp',0,1); 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (8,'AnnotationText','annotation text',0,0); 
INSERT INTO IRLINKFIELDS (FIELDID, FIELDNAME, FIELDDISPLAYNAME, DISPLAYORDER, ISDATE) VALUES (9,'WebAddress','web address',0,0); 


ALTER TABLE DTFLATOBJECTDATA MODIFY(THUMBDATA1 
    VARCHAR2(100), THUMBDATA2 VARCHAR2(100), THUMBDATA3 
    VARCHAR2(100), THUMBDATA4 VARCHAR2(100), SORTDATA1 
    VARCHAR2(100), SORTDATA2 VARCHAR2(100), SORTDATA3 
    VARCHAR2(100), SORTDATA4 VARCHAR2(100));

-------------------------Remove Indexes and Primary key for ISDOCUMENTPAGES------------

SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'ISDOCUMENTPAGES';

select 'Alter table ISDOCUMENTPAGES DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'ISDOCUMENTPAGES' and constraint_type='P';
COMMIT;	
SPOOL off;
	
START drop_TABLE1_index.sql;


-------------------------Add Indexes and Primary key for ISDOCUMENTPAGES------------

ALTER TABLE ISDOCUMENTPAGES MODIFY(PAGEID NULL);

-------------------------End Changes for ISDOCUMENTPAGES----------------------------


-------------------------Remove Indexes and Primary key for IRLINKREPOSITORY------------

SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'IRLINKREPOSITORY';

select 'Alter table IRLINKREPOSITORY DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'IRLINKREPOSITORY' and constraint_type='P';
COMMIT;	
SPOOL off;
	
START drop_TABLE1_index.sql;


-------------------------Add Indexes and Primary key for IRLINKREPOSITORY------------

ALTER TABLE IRLINKREPOSITORY MODIFY(LINKID NULL);

-------------------------End Changes for IRLINKREPOSITORY----------------------------
    
-------------------------Remove Indexes and Primary key for IRImageFiles------------

SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'IRIMAGEFILES';

select 'Alter table IRIMAGEFILES DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'IRIMAGEFILES' and constraint_type='P';
COMMIT;	
SPOOL off;
	
START drop_TABLE1_index.sql;


-------------------------Add Indexes and Primary key for IRImageFiles------------

ALTER TABLE IRIMAGEFILES ADD CONSTRAINT PK_IRIMAGEFILES
    PRIMARY KEY (IMAGEID, RESOLUTIONSIZE, FORMAT, MEDIATYPE);

CREATE INDEX IDX_IRIMGEFLS_IMIDLPSFMTRESMED 
ON IRIMAGEFILES (IMAGEID, LPSID, FORMAT, RESOLUTIONSIZE, MEDIATYPE)
; 

CREATE INDEX IDX_IRIMGEFLS_FMTRESMEDLPSIMD 
ON IRIMAGEFILES (FORMAT, RESOLUTIONSIZE, MEDIATYPE, LPSID, IMAGEID, WIDTH, HEIGHT, FILENAME)
; 

ANALYZE TABLE IRIMAGEFILES COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for IRImageFiles----------------------------

-------------------------Remove Indexes and Primary key for DTFLATOBJECTDATA------------




SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'DTFLATOBJECTDATA';


select 'Alter table DTFLATOBJECTDATA DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'DTFLATOBJECTDATA' and constraint_type='P';
COMMIT;		
SPOOL off;
	
START drop_TABLE1_index.sql;
-------------------------Add Indexes and Primary key for DTFLATOBJECTDATA------------

ALTER TABLE DTFLATOBJECTDATA ADD CONSTRAINT PK_DTFLATOBJECTDATA
    PRIMARY KEY (OBJECTID);

CREATE UNIQUE INDEX IDX__DTFLAT_OBJSRTTHUMB1234 
ON DTFLATOBJECTDATA (OBJECTID, SORTORDER, THUMBDATA1, THUMBDATA2, THUMBDATA3, THUMBDATA4)
; 

CREATE UNIQUE INDEX IDX_DTFLAT_SORTORDER
ON DTFLATOBJECTDATA (SORTORDER)
; 

CREATE UNIQUE INDEX IDX_DTFLAT_SORTOBJID 
ON DTFLATOBJECTDATA (SORTORDER, OBJECTID)
; 
ANALYZE TABLE DTFLATOBJECTDATA COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for DTFLATOBJECTDATA----------------------------

-------------------------Remove Indexes and Primary key for DTTERMOBJECTMAP------------




SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'DTTERMOBJECTMAP';


select 'Alter table DTTERMOBJECTMAP DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'DTTERMOBJECTMAP' and constraint_type='P';
COMMIT;		
SPOOL off;
	
START drop_TABLE1_index.sql;

-------------------------Add Indexes and Primary key for DTTERMOBJECTMAP------------


CREATE INDEX IDX_DTTRMOBJMAP_TERMIDOBJID 
ON DTTERMOBJECTMAP (TERMID, OBJECTID)
; 

CREATE INDEX IDX_DTTRMOBJMAP_FLDIDTERMID 
ON DTTERMOBJECTMAP (FIELDID, TERMID)
; 

ANALYZE TABLE DTTERMOBJECTMAP COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for DTTERMOBJECTMAP---------------------------- 

-------------------------Remove Indexes and Primary key for DTVALUES------------




SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'DTVALUES';


select 'Alter table DTVALUES DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'DTVALUES' and constraint_type='P';
COMMIT;		
SPOOL off;
	
START drop_TABLE1_index.sql;
-------------------------Add Indexes and Primary key for DTVALUES------------

CREATE INDEX IDX_DTVAL_VALIDFLDIDENBLD 
ON DTVALUES (VALUEID, FIELDID, ENABLED)
; 

CREATE INDEX IDX_DTVAL_VALIDVALTXT 
ON DTVALUES (VALUEID, VALUETEXT)
; 

CREATE INDEX IDX_DTVAL_FLDIDVALID 
ON DTVALUES (FIELDID, VALUEID)
; 


CREATE INDEX IDX_DTVAL_VALTXT
ON DTVALUES (VALUETEXT)
; 
ANALYZE TABLE DTVALUES COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for DTVALUES----------------------------


-------------------------Remove Indexes and Primary key for DTVALUETOOBJECT------------




SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'DTVALUETOOBJECT';


select 'Alter table DTVALUETOOBJECT DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'DTVALUETOOBJECT' and constraint_type='P';
COMMIT;		
SPOOL off;
	
START drop_TABLE1_index.sql;
-------------------------Add Indexes and Primary key for DTVALUETOOBJECT------------

ALTER TABLE DTVALUETOOBJECT ADD CONSTRAINT PK_DTVALUETOOBJECT
    PRIMARY KEY (VALUEID, OBJECTID, GROUPING);

CREATE INDEX IDX_DTVALTOOBJ_OBJID 
ON DTVALUETOOBJECT (OBJECTID)
; 

CREATE INDEX IDX_DTVALTOOBJ_PFRDOBJIDVALID 
ON DTVALUETOOBJECT (PREFERRED, OBJECTID, VALUEID)
; 

ANALYZE TABLE DTVALUETOOBJECT COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for DTVALUETOOBJECT----------------------------

-------------------------Remove Indexes and Primary key for IRFIELDS------------




SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'IRFIELDS';


select 'Alter table IRFIELDS DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'IRFIELDS' and constraint_type='P';
COMMIT;		
SPOOL off;
	
START drop_TABLE1_index.sql;


-------------------------Add Indexes and Primary key for IRFIELDS------------
ALTER TABLE IRFIELDS ADD CONSTRAINT PK_IRFIELDS
    PRIMARY KEY (FIELDID);
    
CREATE INDEX IDX_IRFLDS_FLDIDDSPLODR 
ON IRFIELDS (FIELDTYPE, DISPLAYORDER)
; 

CREATE INDEX IDX_IRFLDS_FLDIDDLDTYP 
ON IRFIELDS (FIELDID, FIELDTYPE)
; 
ANALYZE TABLE IRFIELDS COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for IRFIELDS----------------------------


-------------------------Remove Indexes and Primary key for IROBJECTIMAGEMAP------------




SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'IROBJECTIMAGEMAP';


select 'Alter table IROBJECTIMAGEMAP DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'IROBJECTIMAGEMAP' and constraint_type='P';
COMMIT;		
SPOOL off;
	
START drop_TABLE1_index.sql;
-------------------------Add Indexes and Primary key for IROBJECTIMAGEMAP------------

ALTER TABLE IROBJECTIMAGEMAP ADD CONSTRAINT PK_IROBJECTIMAGEMAP
    PRIMARY KEY (OBJECTID, IMAGEID);

CREATE INDEX IDX_IROBJIMGMP_IMGID
ON IROBJECTIMAGEMAP (IMAGEID)
; 
ANALYZE TABLE IROBJECTIMAGEMAP COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for IROBJECTIMAGEMAP----------------------------

-------------------------Remove Indexes and Primary key for ITSEARCHRESULTS------------




SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'ITSEARCHRESULTS';


select 'Alter table ITSEARCHRESULTS DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'ITSEARCHRESULTS' and constraint_type='P';
COMMIT;	
SPOOL off;
	
START drop_TABLE1_index.sql;
-------------------------Add Indexes and Primary key for ITSEARCHRESULTS------------

CREATE INDEX IDX_ITSRCHRSLTS_SRCHOBJ
ON ITSEARCHRESULTS (SEARCHQUERYID, OBJECTID)
; 

ANALYZE TABLE ITSEARCHRESULTS COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for ITSEARCHRESULTS----------------------------

-------------------------Remove Indexes and Primary key for ITTERMOBJECTMAP------------




SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'ITTERMOBJECTMAP';


select 'Alter table ITTERMOBJECTMAP DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'ITTERMOBJECTMAP' and constraint_type='P';
COMMIT;		
SPOOL off;
	
START drop_TABLE1_index.sql;
-------------------------Add Indexes and Primary key for ITTERMOBJECTMAP------------

CREATE INDEX IDX_ITTRMOBJCTMAP_TRMID 
ON ITTERMOBJECTMAP (TERMID)
;

ANALYZE TABLE ITTERMOBJECTMAP COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for ITTERMOBJECTMAP----------------------------

-------------------------Remove Indexes and Primary key for ITVALUES------------




SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'ITVALUES';


select 'Alter table ITVALUES DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'ITVALUES' and constraint_type='P';
COMMIT;		
SPOOL off;
	
START drop_TABLE1_index.sql;
-------------------------Add Indexes and Primary key for ITVALUES------------
    
CREATE INDEX IDX_ITVALUES_VALIDFLDID
ON ITVALUES (VALUEID, FIELDID)
; 

ANALYZE TABLE ITVALUES COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for ITVALUES----------------------------

-------------------------Remove Indexes and Primary key for ITVALUETOOBJECT------------




SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'ITVALUETOOBJECT';


select 'Alter table ITVALUETOOBJECT DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'ITVALUETOOBJECT' and constraint_type='P';
COMMIT;		
SPOOL off;
	
START drop_TABLE1_index.sql;
-------------------------Add Indexes and Primary key for ITVALUETOOBJECT------------
    
CREATE INDEX IDX_ITVALUETOOBJECT_VALID
ON ITVALUETOOBJECT (VALUEID)
; 
    
ANALYZE TABLE ITVALUETOOBJECT COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for ITVALUETOOBJECT----------------------------

-------------------------Remove Indexes and Primary key for DTTERMS------------




SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'DTTERMS';


select 'Alter table DTTERMS DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'DTTERMS' and constraint_type='P';
COMMIT;		
SPOOL off;
	
START drop_TABLE1_index.sql;
-------------------------Add Indexes and Primary key for DTTERMS------------
ALTER TABLE DTTERMS ADD CONSTRAINT PK_DTTERMS
    PRIMARY KEY (TERMID);
    
CREATE INDEX IDX_DTTERMS_KEYWD 
ON DTTERMS (KEYWORD)
;
ANALYZE TABLE DTTERMS COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for DTTERMS---------------------------- 

-------------------------Remove Indexes and Primary key for DTVALUETOENTITYMAP------------

SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;

SPOOL drop_TABLE1_index.sql;

SELECT 'DROP INDEX ' || index_name || ';'
	FROM user_indexes
	WHERE table_name = 'DTVALUETOENTITYMAP';

select 'Alter table DTVALUETOENTITYMAP DROP CONSTRAINT ' || constraint_name || ' CASCADE;' 
FROM user_constraints
WHERE table_name = 'DTVALUETOENTITYMAP' and constraint_type='P';
COMMIT;	
SPOOL off;
	
START drop_TABLE1_index.sql;


-------------------------Add Indexes and Primary key for DTVALUETOENTITYMAP------------

ALTER TABLE DTVALUETOENTITYMAP ADD CONSTRAINT PK_DTVALUETOENTITY
    PRIMARY KEY (VALUEID, ENTITYTYPEID, ENTITYID, RELENTITYTYPEID, RELENTITYID, PARENTENTITYTYPEID, PARENTENTITYID);

CREATE INDEX IDX_DTVALUETOENTITYMAP_01
ON DTVALUETOENTITYMAP (EntityID, EntityTypeID)
; 

CREATE INDEX IDX_DTVALUETOENTITYMAP_02
ON DTVALUETOENTITYMAP (Preferred, EntityID)
; 

ANALYZE TABLE DTVALUETOENTITYMAP COMPUTE STATISTICS FOR ALL INDEXES;
-------------------------End Changes for DTVALUETOENTITYMAP----------------------------





commit;
