predicatesintoviews.sql

derby database source code.good for you.

	ownerdeletedate,
	ownername,
	uownername,
	ownerauthid,
	creatorname,
	description,
	versionComment,
	objecttype,
	subtypeid,
	subtype,
	structuretype,
	createdate,
	versiondate,
	changedate,
	deletedate,
	versioncontrolled,
	currentversion,
	previousversion,	
	checkedout
) 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,
	cls.structuretype,
	ror.createdate,
	rov.versiondate,
	rov.changedate,
	rov.deletedate,
	ror.versioncontrolled,
	ror.currentversion,
	rov.previousversion,	
	ror.checkedout
from  xr.repositoryobjectresource ror
	inner join xr.classificationscheme cls on (ror.id = cls.id)
	inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.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.classificationschemeallversionview (
	id,
	versionid,
	name,
	uname,
	versionName,
	uri,
	versionuri,
	folderid,
	ownerid,
	ownerdeletedate,
	ownername,
	uownername,
	ownerauthid,
	creatorname,
	description,
	versionComment,
	objecttype,
	subtypeid,
	subtype,
	structuretype,
	createdate,
	versiondate,
	changedate,
	deletedate,
	versioncontrolled,
	currentversion,
	previousversion,	
	checkedout
) 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,
	cls.structuretype,
	ror.createdate,
	rov.versiondate,
	rov.changedate,
	rov.deletedate,
	ror.versioncontrolled,
	ror.currentversion,
	rov.previousversion,	
	ror.checkedout
from  xr.repositoryobjectresource ror
	inner join xr.classificationscheme cls on (ror.id = cls.id)
	inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
	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.classificationschemelifecycleview (
	id,
	versionid,
	name,
	versionName,
	uri,
	versionuri,
	folderid,
	ownerid,
	ownerdeletedate,
	ownerauthid,
	ownername,
	description,
	objecttype,
	subtypeid,
	subtype,
	structuretype,
	checkforlock,
	createdate,
	versiondate,
	changedate,
	deletedate,
	versioncontrolled,
	currentversion,
	previousversion,
	checkedout
) 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,
	cls.structuretype,
	ror.checkforlock,
	ror.createdate,
	rov.versiondate,
	rov.changedate,
	rov.deletedate,
	ror.versioncontrolled,
	ror.currentversion,
	rov.previousversion,
	ror.checkedout
from  xr.repositoryobjectresource ror
	inner join xr.classificationscheme cls on (ror.id = cls.id)
	inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.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.classificationvalueview (
	id,
	value,
	description,
	parentid,
	parentvalue,
	schemeid,
	schemeversionid,
	schemename
) as select 
	v.valueid,
	v.value,
	v.description,
	p.valueid,
	p.value,
	rov.resourceid,
	rov.id,
	rov.name	
from  xr.classification_values v 
	inner join xr.repositoryobjectversion rov on (v.versionid = rov.id)
	left outer join xr.classification_values p on (v.parentvalueid = p.valueid)
where rov.deletedate is null;

create view  xr.classification_direct_view (
	objectversionid,
	valueid,
	schemeversionid,
	value
) as select 
	c.objectversionid,
	cv.valueid,
	cv.versionid,
	cv.value
from  xr.classifications c, xr.classification_values cv, 
	xr.repositoryobjectresource ror
	where c.valueid = cv.valueid and cv.versionid = ror.currentversion;

create view  xr.classification_indirect_view (
	objectversionid,
	valueid,
	schemeversionid,
	value
) as select 
	c.objectversionid,
	cv.valueid,
	cv.versionid,
	cv.value
from  xr.classifications c, xr.classification_values cv, 
	xr.classification_value_ancestors cva, xr.repositoryobjectresource ror
	where c.valueid = cva.valueid and 
	cva.ancestorid = cv.valueid and cva.versionid = cv.versionid and
	cv.versionid = ror.currentversion;

create view  xr.businessentityqueryview (
	id,
	name,
	uname,
	type,
	createdate,
	changedate,
	description,
	authID,	
	ownerid,
	ownername,
	uownername,
	businessTypeID,
	businessType
) as select 
	be.id, 
	be.name,
	be.uname,
	be.type,
	be.createdate,
	be.changedate,
	be.description,  
	be.authID , 
	o.id,  
	o.name, 
	o.uname,
	be.businessTypeID, 
	cv.value 
from  xr.businessentity be left outer join xr.businessentity o on be.owneruserUUID = o.id  
left outer join xr.classificationcurrentvalueview cv on cv.valueid = be.businessTypeID 
where be.deletedate is null and o.deletedate is null;

create view  xr.businessassociationqueryview (
	id,
	sourceid,
	sourcename,
	usourcename,
	sourcetype,
	targetid,
	targetname,
	utargetname,
	targettype,
	createdate,
	changedate,
	description,
	ownerid,
	ownername,
	uownername,
	associationTypeID,
	associationType
) as select 
	bea.id,
	bea.sourceid,
	s.name,
	s.uname,
	s.type,
	bea.targetid, 
	t.name,
	t.uname,
	t.type,
	bea.createdate,
	bea.changedate,
	bea.description,  
	o.id,  
	o.name, 
	o.uname,
	bea.assoctypeID, 
	cv.value 	
from  xr.businessentityassociation bea 
left outer join xr.businessentity s on bea.sourceID = s.ID 
left outer join xr.businessentity t on bea.targetID = t.ID 
left outer join xr.businessentity o on bea.ownerID = o.ID  
left outer join xr.classificationcurrentvalueview cv on cv.valueid = bea.assoctypeID 
where bea.deletedate is null and s.deletedate is null and t.deletedate is null and o.deletedate is null;

create view  xr.repositoryobjectcurrentversionview (
	id, versionid, name, uname, versionName, 
	uri, versionuri, folderid, 
	ownerid, ownerdeletedate, ownername, uownername, ownerauthid, creatorname,
	description, versionComment, objecttype, subtypeid, subtype, 
	checkforlock, createdate, versiondate, changedate, deletedate,
	versioncontrolled, currentversion, previousversion, checkedout,
	-- from RepositoryEntry
	stability, statusid, status, isopaque,
	startdate, expirationdate, contentchangedate, versioncontentid,
	-- from ObjectAssociation
	sourceid, targetid,
	-- from ClassificationScheme
	structuretype
) 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, cvsubt.value,  
	ror.checkforlock, ror.createdate, rov.versiondate, rov.changedate, rov.deletedate,
	ror.versioncontrolled, ror.currentversion, rov.previousversion, ror.checkedout,
	-- from RepositoryEntry
	re.stability, re.status, cvstatus.value, re.isopaque,
	re.startdate, re.expirationdate, rec.contentchangedate, re.versioncontentid,
	-- from ObjectAssociation
	oa.sourceid, oa.targetid,
	-- from ClassificationScheme
	cs.structuretype
from  xr.repositoryobjectresource ror
	inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
	left outer join xr.classificationcurrentvalueview cvsubt on (ror.subtype = cvsubt.valueid)
	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.repositoryentry re on (ror.currentversion = re.versionid)
	left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)
	left outer join xr.classificationcurrentvalueview cvstatus on (re.status = cvstatus.valueid)
	left outer join xr.objectassociation oa on (ror.id = oa.id)
	left outer join xr.classificationscheme cs on (ror.id = cs.id)
	where rov.deletedate is null;

create view  xr.repositoryobjectallversionview (
	id, versionid, name, uname, versionName, 
	uri, versionuri, folderid, 
	ownerid, ownerdeletedate, ownername, uownername, ownerauthid, creatorname,
	description, versionComment, objecttype, subtypeid, subtype, 
	checkforlock, createdate, versiondate, changedate, deletedate,
	versioncontrolled, currentversion, previousversion, checkedout, 
	-- from RepositoryEntry
	stability, statusid, status, isopaque,
	startdate, expirationdate, contentchangedate, versioncontentid,
	-- from ObjectAssociation
	sourceid, targetid,
	-- from ClassificationScheme
	structuretype
) 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, cvsubt.value,  
	ror.checkforlock, ror.createdate, rov.versiondate, rov.changedate, rov.deletedate,
	ror.versioncontrolled, ror.currentversion, rov.previousversion, ror.checkedout, 
	-- from RepositoryEntry
	re.stability, re.status, cvstatus.value, re.isopaque,
	re.startdate, re.expirationdate, rec.contentchangedate, re.versioncontentid,
	-- from ObjectAssociation
	oa.sourceid, oa.targetid,
	-- from ClassificationScheme
	cs.structuretype
from  xr.repositoryobjectresource ror
	inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
	left outer join xr.classificationcurrentvalueview cvsubt on (ror.subtype = cvsubt.valueid)
	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.repositoryentry re on (ror.currentversion = re.versionid)
	left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)
	left outer join xr.classificationcurrentvalueview cvstatus on (re.status = cvstatus.valueid)
	left outer join xr.objectassociation oa on (ror.id = oa.id)
	left outer join xr.classificationscheme cs on (ror.id = cs.id)
	where rov.deletedate is null;

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

create view  xr.repositoryentrycurrentversionview (
	id,
	versionid,
	name,
	uname,
	versionName,
	uri,
	versionuri,
	versioncontentid,
	folderid,
	ownerid,
	ownerdeletedate,
	ownername,
	uownername,
	ownerauthid,
	creatorname,
	description,
	versionComment,
	objecttype,
	createdate,
	versiondate,
	changedate,
	contentchangedate,
	deletedate,
	versioncontrolled,
	currentversion,
	previousversion,
	checkedout,
	subtypeid,
	subtype,
	stability,
	statusid,
	status,
	startdate,
	expirationdate,
	isopaque
) as select 
	ror.id,
	rov.id,
	rov.name,
	rov.uname,
	rov.versionName,
	rov.uri,
	rov.versionuri,
	re.versioncontentid,
	rov.folderid,
	rov.ownerid,
	beo.deletedate,