If you want to use the files that are provided with SR2 (e.g., SiteRefresh.asp and related .asp files or getSiteRefresh.cfm), then you must define views for each Asset Type. It is also possible to build retrieving queries without views. This is useful when an AssetType table is joined with another table outside of SR2.
In the following examples, replace EXAMPLE with the name of the Asset Type that you created in SR2. In many cases the AssetType name is the same as the Table name.
SR2 Example
AS
SELECT
a.name AS assetName,
a.publishedVersionID,
a.currentVersionID,
a.assetTypeID,
av.assetID,
av.versionnum,
av.id AS versionID,
av.title,
av.activateDate,
av.expireDate,
t.filename AS templateName,
udef.*
FROM
assets a INNER JOIN
assetVersions av ON a.id = av.assetID INNER JOIN
EXAMPLE udef ON av.id = udef.id LEFT OUTER JOIN
templates t ON av.templateID = t.id
Note: In SQL Server, if you add or remove a column from the AssetType table, you must re-create the view.
Example ALTER code:
ALTER VIEW assets_v_EXAMPLE as
select a.name as assetName,
a.publishedVersionID,
a.currentVersionID,
a.assetTypeID,
av.assetID,
av.versionnum,
av.id as versionID,
av.title,
av.activateDate,
av.expireDate,
t.filename as templateName,
udef.*
from assets a
INNER JOIN assetVersions av ON a.id = av.assetID
INNER JOIN EXAMPLE udef ON av.id = udef.id
LEFT OUTER JOIN templates t ON av.templateID = t.id;
Additional Information:
If you want to reference an AssetType view, in a Type 9,10 or 14. Use the following query:
select assetid as id, title as name from assets_v_EXAMPLE where publishedversionid=versionID
This SQL statement will only show published assets. If you want to see all assets, omit the where statement.