34 lines
1.3 KiB
SQL
34 lines
1.3 KiB
SQL
-- principal of primary archives
|
|
select a.archive_id from archives as a where archive_ref_id is null;
|
|
|
|
-- parent archives
|
|
select a.archive_ref_id, a.archive_id from archives as a where archive_ref_id is not null;
|
|
|
|
-- archive owners
|
|
select u.name, ax.archive_id from attribs as a
|
|
join archives as ax on a.attribs_id = ax.attribs_id
|
|
join archive_owners as ao on ax.archive_id = ao.archive_id
|
|
join users as u on ao.owner_id = u.user_id;
|
|
|
|
-- archive group view access
|
|
select g.name, ax.archive_id from attribs as a
|
|
join archives as ax on a.attribs_id = ax.attribs_id
|
|
join archive_groups as ag on ax.archive_id = ag.archive_id
|
|
join groups as g on ag.group_id = g.group_id;
|
|
|
|
-- archive group execute access
|
|
select g.name, ax.archive_id from attribs as a
|
|
join archives as ax on a.attribs_id = ax.attribs_id
|
|
join archive_groups as ag on ax.archive_id = ag.archive_id
|
|
join groups as g on ag.group_id = g.group_id
|
|
where a.type_id = 1;
|
|
|
|
-- associated stats group access
|
|
select g.name, aa.archive_id from archives as a
|
|
join associations as ax on ax.attributes_id = a.attribs_id
|
|
join archives as aa on ax.ref_id = aa.attribs_id
|
|
join attribs as at on aa.attribs_id = at.attribs_id
|
|
join archive_groups as ag on ag.archive_id = a.archive_id
|
|
join groups as g on ag.group_id = g.group_id
|
|
where at.type_id between 85 and 87;
|