predicatesintoviews.sql

derby database source code.good for you.

	currentversion,
	previousversion,	
	checkedout,
	sourceid,
	targetid
) as select 
	ror.id,
	rov.id,
	rov.name,
	rov.uname,
	rov.versionName,
	rov.uri,
	rov.versionuri,
	rov.folderid,
	rov.ownerid,
	beo.deletedate,
	beo.name,
	beo.uname,
	beo.authid,
	bec.name,
	rov.description,
	rov.versionComment,
	ror.type,
	ror.subtype,
	cvtype.value,
	ror.createdate,
	rov.versiondate,
	rov.changedate,
	rov.deletedate,
	ror.versioncontrolled,
	ror.currentversion,
	rov.previousversion,	
	ror.checkedout,
	oa.sourceid,
	oa.targetid
from  xr.repositoryobjectresource ror
	inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
	inner join xr.objectassociation oa on (ror.id = oa.id)
	left outer join xr.businessentity beo on (rov.ownerid = beo.id)
	left outer join xr.businessentity bec on (rov.creatorid = bec.id)
	left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
	where rov.deletedate is null;

create view  xr.objectassociationallversionview (
	id,
	versionid,
	name,
	uname,
	versionName,
	uri,
	versionuri,
	folderid,
	ownerid,
	ownerdeletedate,
	ownername,
	uownername,
	ownerauthid,
	creatorname,
	description,
	versionComment,
	objecttype,
	subtypeid,
	subtype,
	createdate,
	versiondate,
	changedate,
	deletedate,
	versioncontrolled,
	currentversion,
	previousversion,	
	checkedout,
	sourceid,
	targetid
) as select 
	ror.id,
	rov.id,
	rov.name,
	rov.uname,
	rov.versionName,
	rov.uri,
	rov.versionuri,
	rov.folderid,
	rov.ownerid,
	beo.deletedate,
	beo.name,
	beo.uname,
	beo.authid,
	bec.name,
	rov.description,
	rov.versionComment,
	ror.type,
	ror.subtype,
	cvtype.valueid,
	ror.createdate,
	rov.versiondate,
	rov.changedate,
	rov.deletedate,
	ror.versioncontrolled,
	ror.currentversion,
	rov.previousversion,	
	ror.checkedout,
	oa.sourceid,
	oa.targetid
from  xr.repositoryobjectresource ror
	inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
	inner join xr.objectassociation oa on (ror.id = oa.id)
	left outer join xr.businessentity beo on (rov.ownerid = beo.id)
	left outer join xr.businessentity bec on (rov.creatorid = bec.id)
	left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
	where rov.deletedate is null;

create view  xr.objectassociationlifecycleview (
	id,
	versionid,
	name,
	versionName,
	uri,
	versionuri,
	folderid,
	ownerid,
	ownerdeletedate,
	ownerauthid,
	ownername,
	description,
	objecttype,
	subtypeid,
	subtype,
	checkforlock,
	createdate,
	versiondate,
	changedate,
	deletedate,
	versioncontrolled,
	checkedout,
	currentversion,
	previousversion,
	sourceid,
	targetid
) as select 
	ror.id,
	rov.id,
	rov.name,
	rov.versionName,
	rov.uri,
	rov.versionuri,
	rov.folderid,
	rov.ownerid,
	beo.deletedate,
	beo.authid,
	beo.name,
	rov.description,
	ror.type,
	ror.subtype,
	cvtype.value,
	ror.checkforlock,
	ror.createdate,
	rov.versiondate,
	rov.changedate,
	rov.deletedate,
	ror.versioncontrolled,
	ror.checkedout,
	ror.currentversion,
	rov.previousversion,
	oa.sourceid,
	oa.targetid
from  xr.repositoryobjectresource ror
	inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
	inner join xr.objectassociation oa on (ror.id = oa.id)
	left outer join xr.businessentity beo on (rov.ownerid = beo.id)
	left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid);

create view  xr.objectassociationcurrentversionqueryview (
	id,
	versionid,
	name,
	uname,
	versionName,
	uri,
	versionuri,
	folderid,
	ownerid,
	ownerdeletedate,
	ownername,
	uownername,
	ownerauthid,
	creatorname,
	description,
	versionComment,
	objecttype,
	subtypeid,
	subtype,
	createdate,
	versiondate,
	changedate,
	deletedate,
	versioncontrolled,
	currentversion,
	previousversion,	
	checkedout,
	sourceid,
	sourcename,
	usourcename,
	sourcetype,
	targetid,
	targetname,
	utargetname,
	targettype
) as select 
	ror.id,
	rov.id,
	rov.name,
	rov.uname,
	rov.versionName,
	rov.uri,
	rov.versionuri,
	rov.folderid,
	rov.ownerid,
	beo.deletedate,
	beo.name,
	beo.uname,
	beo.authid,
	bec.name,
	rov.description,
	rov.versionComment,
	ror.type,
	ror.subtype,
	cvtype.value,
	ror.createdate,
	rov.versiondate,
	rov.changedate,
	rov.deletedate,
	ror.versioncontrolled,
	ror.currentversion,
	rov.previousversion,	
	ror.checkedout,
	oa.sourceid,
	s.name,
	s.uname,
	s.objecttype,
	oa.targetid,
	t.name,
	t.uname,
	t.objecttype	
from  xr.repositoryobjectresource ror
	inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
	inner join xr.objectassociation oa on (ror.id = oa.id)
	left outer join xr.repositoryobjectcurrentversionview s on (oa.sourceid = s.id)
	left outer join xr.repositoryobjectcurrentversionview t on (oa.targetid = t.id)		
	left outer join xr.businessentity beo on (rov.ownerid = beo.id)
	left outer join xr.businessentity bec on (rov.creatorid = bec.id)
	left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
	where rov.deletedate is null;

create view  xr.lockview (
	locktoken,
	resourceid,
	resourcename,
	userid,
	username,
	exclusive,
	timeoutseconds,
	expirationdate
) as select 
	l.locktoken,
	l.resourceid,
	rov.name,
	l.ownerid,
	be.name,
	l.exclusive,
	l.timeoutseconds,
	l.expirationdate
from  xr.lock l, xr.repositoryobjectresource ror, xr.repositoryobjectversion rov, 
	xr.businessentity be 	
where l.resourceid = ror.id and ror.currentversion = rov.id and l.ownerid = be.id;

--echo === Create Users ================================================;

-- if we don't commit, the following insert will block due to fix of beetle 4821
commit;

-- The following insert statement shouldn't block for 2 minutes!! The compile of the trigger
-- shouldn't wait for timeout!!
insert into xr.businessentity ( ID, type, name, authID, createdate, changedate )
	values ('26747154-0dfc-46af-a85d-1dc30c230c4e', 
		'USER', 
		'Administrator', 		
		'xradmin', 
		CURRENT TIMESTAMP, CURRENT TIMESTAMP);
insert into xr.xruser 
		(businessentityid, 
		userid)
	values ('26747154-0dfc-46af-a85d-1dc30c230c4e',	
		'xradmin');


insert into xr.businessentity ( ID, type, name, authID, createdate, changedate )
	values ('013026c8-1b22-487a-a189-1c7b16811035', 
		'USER', 
		'Sample XR User', 
		'xrguest', CURRENT TIMESTAMP, CURRENT TIMESTAMP);
insert into xr.xruser (businessentityid, userid)
	values ('013026c8-1b22-487a-a189-1c7b16811035',	
		'xrguest');

--echo == Create locators ==============================================;

insert into xr.locator ( 
	ownerID,
	type, 
	usetypeID, 
	value, 
	street1, 
	street2, 
	city, 
	state, 
	country, 
	postalcode
) values (
	'26747154-0dfc-46af-a85d-1dc30c230c4e', 
	'EMAIL',
	'67c249a4-d160-11d6-bb9c-646533376c37',
	'[e-mail]',
	'',
	'',
	'',
	'',
	'',
	'');


insert into xr.locator ( 
	ownerID,
	type, 
	usetypeID, 
	value, 
	street1, 
	street2, 
	city, 
	state, 
	country, 
	postalcode
) values (
	'013026c8-1b22-487a-a189-1c7b16811035', 
	'EMAIL',
	'67c249a4-d160-11d6-bb9c-646533376c37',
	'[e-mail]',
	'',
	'',
	'',
	'',
	'',
	'');

--echo =============================================================================;
--echo xr database insert script
--echo =============================================================================;

--XRADMIN ID '26747154-0dfc-46af-a85d-1dc30c230c4e' 
-- repositoryobjectversion.folderid references xr.repositoryobjectresource(id)
--ROOT FolderID foreign '225924f8-1a72-42c9-a58d-05b41d8415ce' 


--echo == ROOT Folder ====================================;
--echo == ROOT Folder ====================================;



insert into xr.repositoryobjectversion (
	id, resourceid, name, versionName, uri, versionuri, description,
	ownerid, creatorid, versiondate, changedate, previousversion
	) values (
	'077bb8c4-0894-4d99-937a-356c315d26e2',
	'225924f8-1a72-42c9-a58d-05b41d8415ce', 
	'', '1.0', '/',
	'1.0/',
	'Root XR folder',
	'26747154-0dfc-46af-a85d-1dc30c230c4e',
	'26747154-0dfc-46af-a85d-1dc30c230c4e',
	CURRENT TIMESTAMP, CURRENT TIMESTAMP,
	null
	);

-- Root folder
insert into xr.repositoryobjectresource (
	id, type, subtype, creatorid, createdate, currentVersion, versionControlled
	) values (
	'225924f8-1a72-42c9-a58d-05b41d8415ce', 
	'COLLECTION', 
	'11a832a5-0bb1-46db-a000-147906f70021',
	'013026c8-1b22-487a-a189-1c7b16811035',
	CURRENT TIMESTAMP, 
	'077bb8c4-0894-4d99-937a-356c315d26e2', 
	0);

-- This ObjectGroup classified as INTERNAL_USE
insert into xr.classifications ( objectversionid, valueid )
	values ('077bb8c4-0894-4d99-937a-356c315d26e2', '196b15da-136f-4e19-933f-036f01481f5f');






--echo ==================================================;

--echo =============================================================================;
--echo xr database insert script
--echo =============================================================================;

--XRADMIN ID '26747154-0dfc-46af-a85d-1dc30c230c4e' 
-- repositoryobjectversion.folderid references xr.repositoryobjectresource(id)
--ROOT FolderID foreign '225924f8-1a72-42c9-a58d-05b41d8415ce' 



--echo == Business types =======================================;
--echo =========================================================;



insert into xr.repositoryobjectversion (
	id, 
	resourceid, 
	name, 
	versionName,
	folderid, 
	uri, 
	versionuri, 
	description,
	ownerid, 
	creatorid, 
	versiondate, 
	changedate, 
	previousversion
) values (
	'6e7fb600-d184-11d6-85ee-646533376c37',
	'717a3150-d184-11d6-85ee-646533376c37', 
	'Business Types', '1.0',  
	'225924f8-1a72-42c9-a58d-05b41d8415ce',
	'Business+Types',
	'1.0/Business+Types/1.0',
	'Valid values for the Business TYpe property of a Business',
	'26747154-0dfc-46af-a85d-1dc30c230c4e',
	'26747154-0dfc-46af-a85d-1dc30c230c4e',
	CURRENT TIMESTAMP, 
	CURRENT TIMESTAMP,
	null
	);


insert into xr.repositoryobjectresource (
	id, 
	type, 
	subtype, 
	creatorid, 
	createdate, 
	currentVersion, 
	versionControlled
) values (
	'717a3150-d184-11d6-85ee-646533376c37', 
	'CLASSIFICATION_SCHEME', 
	'19adf2cf-0a2e-4d98-8f68-221708370b4c',
	'26747154-0dfc-46af-a85d-1dc30c230c4e',
	CURRENT TIMESTAMP,
	'6e7fb600-d184-11d6-85ee-646533376c37', 
	0);

-- This scheme goes into root folder
insert into xr.objectgroup_content (
	versionid,
	memberid
) values (
	'077bb8c4-0894-4d99-937a-356c315d26e2',
	'717a3150-d184-11d6-85ee-646533376c37'
);


insert into xr.classificationscheme (
	id, 
	structuretype
) values (
	'717a3150-d184-11d6-85ee-646533376c37', 
	'LIST');



-- This scheme classified as INTERNAL_USE
insert into xr.classifications ( 
	objectversionid, 
	valueid 
) values (
	'6e7fb600-d184-11d6-85ee-646533376c37',
	'196b15da-136f-4e19-933f-036f01481f5f');


--echo =======================================================;


insert into xr.classification_values ( 
	versionid, 
	valueid, 
	value, 
	description 
) values ( 
	'6e7fb600-d184-11d6-85ee-646533376c37', 
	'717a3151-d184-11d6-85ee-646533376c37', 
	'CORPORATION',
	'CORPORATION');

--echo ==========================================================;


insert into xr.classification_values ( 
	versionid, 
	valueid, 
	value, 
	description 
)values ( 
	'6e7fb600-d184-11d6-85ee-646533376c37', 
	'717bb7f0-d184-11d6-85ee-646533376c37', 
	'ORGANIZATION',
	'ORGANIZATION');

insert into xr.classification_value_ancestors ( 
	versionid, 
	valueid, 
	ancestorid
)values ( 
	'6e7fb600-d184-11d6-85ee-646533376c37', 
	'717bb7f0-d184-11d6-85ee-646533376c37', 
	'717bb7f0-d184-11d6-85ee-646533376c37');

--echo ==========================================================;


insert into xr.classification_values ( 
	versionid, 
	valueid, 
	value, 
	description