predicatesintoviews.sql

来自「derby database source code.good for you.」· SQL 代码 · 共 2,696 行 · 第 1/5 页

	beo.name,
	beo.uname,
	beo.authid,
	bec.name,
	rov.description,
	rov.versionComment,
	ror.type,
	ror.createdate,
	rov.versiondate,
	rov.changedate,
	rec.contentchangedate,
	rov.deletedate,
	ror.versioncontrolled,
	ror.currentversion,
	rov.previousversion,
	ror.checkedout,
	re.mimetype,
	cvmime.value,
	re.stability,
	re.status,
	cvstatus.value,
	re.startdate,
	re.expirationdate,
	re.isopaque
from  xr.repositoryobjectresource ror
	inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
	inner join xr.repositoryentry re on (rov.id = re.versionid)
	left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)
	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 cvmime on (re.mimetype = cvmime.valueid)
	left outer join xr.classificationcurrentvalueview cvstatus on (re.status = cvstatus.valueid)
	where rov.deletedate is null;

create view  xr.repositoryentryallversionview (
	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,
	beo.name,
	beo.uname,
	beo.authid,
	bec.name,
	rov.description,
	rov.versionComment,
	ror.type,
	ror.createdate,
	rov.versiondate,
	rov.changedate,
	rec.contentchangedate,
	rov.deletedate,
	ror.versioncontrolled,
	ror.currentversion,		
	rov.previousversion,
	ror.checkedout,
	re.mimetype,
	cvmime.value,
	re.stability,
	re.status,
	cvstatus.value,
	re.startdate,
	re.expirationdate,
	re.isopaque
from  xr.repositoryobjectresource ror
	inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
	inner join xr.repositoryentry re on (rov.id = re.versionid)
	left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)	
	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 cvmime on (re.mimetype = cvmime.valueid)
	left outer join xr.classificationcurrentvalueview cvstatus on (re.status = cvstatus.valueid)
	where rov.deletedate is null;

create view  xr.repositoryentrylifecycleview (
	id,
	versionid,
	name,
	versionName,
	uri,
	versionuri,
	versioncontentid,
	folderid,
	ownerid,
	ownerdeletedate,
	ownerauthid,
	ownername,
	description,
	objecttype,
	subtypeid,
	subtype,
	checkforlock,
	createdate,
	versiondate,
	changedate,
	contentchangedate,
	deletedate,
	versioncontrolled,
	checkedout,
	currentversion,
	previousversion	
) as select 
	ror.id,
	rov.id,
	rov.name,
	rov.versionName,
	rov.uri,
	rov.versionuri,
	re.versioncontentid,
	rov.folderid,
	rov.ownerid,
	beo.deletedate,
	beo.authid,
	beo.name,
	rov.description,
	ror.type,
	re.mimetype,
	cvmime.value,
	ror.checkforlock,
	ror.createdate,
	rov.versiondate,
	rov.changedate,
	rec.contentchangedate,
	rov.deletedate,
	ror.versioncontrolled,
	ror.checkedout,
	ror.currentversion,
	rov.previousversion
from  xr.repositoryobjectresource ror
	inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
	inner join xr.repositoryentry re on (rov.id = re.versionid)
	left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)
	left outer join xr.businessentity beo on (rov.ownerid = beo.id)
	left outer join xr.classificationcurrentvalueview cvmime on (re.mimetype = cvmime.valueid);

create view  xr.repositoryentrycontentview (
	id,
	versionid,
	versioncontentid,
	uri,
	versionuri,
	content,
	contentchangedate,
	currentversion,
	mimetypeid,
	mimetype
) as select 
	ror.id,
	rov.id,
	re.versioncontentid,
	rov.uri,
	rov.versionuri,
	rec.content,
	rec.contentchangedate,
	ror.currentversion,
	re.mimetype,
	cvmime.value
from  xr.repositoryobjectresource ror
	inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
	inner join xr.repositoryentry re on (rov.id = re.versionid)
	left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)	
	left outer join xr.classificationcurrentvalueview cvmime on (re.mimetype = cvmime.valueid)
	left outer join xr.classificationcurrentvalueview cvstatus on (re.status = cvstatus.valueid)
	where rov.deletedate is null;

create view  xr.objectgroupcurrentversionview (
	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
) 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
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)
	left outer join xr.businessentity bec on (rov.creatorid = bec.id)
	left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
	where ror.type = 'COLLECTION' and rov.deletedate is null;

create view  xr.objectgroupallversionview (
	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
) 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
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)
	left outer join xr.businessentity bec on (rov.creatorid = bec.id)
	left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
	where ror.type = 'COLLECTION' and rov.deletedate is null;

create view  xr.objectgrouplifecycleview (
	id,
	versionid,
	name,
	versionName,
	uri,
	versionuri,
	folderid,
	ownerid,
	ownerdeletedate,
	ownerauthid,
	ownername,
	description,
	objecttype,
	subtypeid,
	subtype,
	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.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
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)
	left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
	where ror.type = 'COLLECTION';

create view  xr.externaldependencycurrentversionview (
	id,
	versionid,
	name,
	uname,
	versionName,
	uri,
	versionuri,
	folderid,
	ownerid,
	ownerdeletedate,
	ownername,
	uownername,
	ownerauthid,
	creatorname,
	description,
	versionComment,
	objecttype,
	subtypeid,
	subtype,
	createdate,
	versiondate,
	changedate,
	deletedate,
	versioncontrolled,
	checkedout,
	currentversion,
	previousversion
) 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.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)
	left outer join xr.businessentity bec on (rov.creatorid = bec.id)
	left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
	where ror.type = 'EXTERNAL_DEPENDENCY' and rov.deletedate is null;

create view  xr.externaldependencyallversionview (
	id,
	versionid,
	name,
	uname,
	versionName,
	uri,
	versionuri,	
	folderid,
	ownerid,
	ownerdeletedate,
	ownername,
	uownername,
	ownerauthid,
	creatorname,
	description,
	versionComment,
	objecttype,
	dependencytypeid,
	dependencytype,
	createdate,
	versiondate,
	changedate,
	deletedate,
	versioncontrolled,	
	checkedout,
	currentversion,
	previousversion	
) 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.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)
	left outer join xr.businessentity bec on (rov.creatorid = bec.id)
	left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
	where ror.type = 'EXTERNAL_DEPENDENCY' and rov.deletedate is null;

create view  xr.objectassociationcurrentversionview (
	id,
	versionid,
	name,
	uname,
	versionName,
	uri,
	versionuri,
	folderid,
	ownerid,
	ownerdeletedate,
	ownername,
	uownername,
	ownerauthid,
	creatorname,
	description,
	versionComment,
	objecttype,
	subtypeid,
	subtype,
	createdate,
	versiondate,
	changedate,
	deletedate,
	versioncontrolled,