-
Notifications
You must be signed in to change notification settings - Fork 51
Useful SQL
Here you will find some useful SQL queries. The number beside the description tells which version of Bricolage it was used in; it may also apply to other versions.
DISCLAIMER: These tips are provided in the hopes that they will be useful, but directly modifying the database can be disastrous. You run these commands at your own risk. Be sure to have a rollback plan.
Sometimes you’ll be working in Bricolage, click an “Edit” link for a story, and get an error such as:
Can't call method "allowed_desks" on an undefined value at /usr/local/bricolage/comp/widgets/story_prof/edit_meta.html line 342, <GEN211> line 165.
This is because stories are placed on desks both by adding the desk ID to the story and by adding the story to a group for the desk, and only one of those ways is actually working. If you know the ID of the story, first check to make sure that the desk__id and workflow__id columns are both set to 0:
SELECT workflow__id, desk__id FROM story WHERE id = $story_id;
If either is not set to 0, but the story is supposed to be checked in, then set them to 0:
UPDATE story SET workflow__id = 0, desk__id = 0 WHERE id = $story_id;
If both are properly set to 0 and you still get the error, then the problem is that the story is still in a desk asset group. You can see a list of stories in this state by running this query:
SELECT sm.id, s.primary_uri FROM story_member sm JOIN story s ON s.id = sm.object_id JOIN member m ON sm.member__id = m.id WHERE s.desk__id = 0 AND m.grp__id IN ( SELECT asset_grp FROM desk );
If you see some stories there, you can fix them with this query:
DELETE FROM member WHERE id IN (
SELECT m.id
FROM story_member sm
JOIN story s ON s.id = sm.object_id
JOIN member m ON sm.member__id = m.id
WHERE s.desk__id = 0
AND m.grp__id IN ( SELECT asset_grp FROM desk )
);
If you’re having this problem with media or templates, just replace “story” with “media” or “template” in the above queries.
(that for some reason weren’t removed from the database automatically when I deleted them using the Bricolage web interface)
In my case I had created some new test sites, then deleted them. However when I added new users to Bricolage the old site groups were still offered as options to be selected. Therefore I had to go directly into the database and set these groups to active = false. Change ‘2nd test site’ to the site name you are trying to remove. Note: should work for all Bric versions
UPDATE "grp"
SET "active" = 'FALSE'
WHERE "name" ~ '2nd test site'
— Bric.DawnBuie – 23 Jul 2008
update member
set active = True
where id in (
select distinct sc.member__id
from story__contributor sc
inner join story_instance si on sc.story_instance__id = si.id
where si.story__id = ID OF YOUR STORY
);
This is the same idea as “Find elements for a story with a given URI (1.8.3)” below, except for looking up by ‘id’ instead of ‘primary_uri’ (and updated for version 1.10).
SELECT f.id,ft.name,ft.key_name,f.place,f.object_order
FROM story_field f,field_type ft
WHERE f.field_type__id = ft.id and ft.active = 't'
and f.parent_id = (
select t.id from story_element t,element_type et,at_type at
where t.element_type__id=et.id and t.active='t' and et.active='t'
and et.type__id=at.id and t.object_instance_id = (
select max(id) from story_instance where story__id = 50706
)
and at.top_level='t'
) and f.active='t'
UNION
SELECT t.id,et.name,et.key_name,t.place,t.object_order
FROM story_element t,element_type et
WHERE t.element_type__id = et.id and et.active = 't'
and t.parent_id = (
select t.id from story_element t,element_type et,at_type at
where t.element_type__id=et.id and t.active='t' and et.active='t'
and et.type__id=at.id and t.object_instance_id = (
select max(id) from story_instance where story__id = 50706
) and at.top_level='t'
) and t.active='t'
ORDER BY place;
— Bric.ScottLanning – 15 Aug 2007
Warning #1: These queries permanently change the URL of a category for all categories and objects across your site. Change =ROLLBACK= to =COMMIT= when you’re ready to do this. You might want to seriously consider whether you should instead just create new categories and republish your assets to those new categories.
Warning #2: The example below is fairly simple and uses the SQL replace() function. You should check to ensure that your replace() function is not going to inadvertantly replace URLs that you don’t intend to replace — e.g. if you’re trying to rename /this/and/that to /this/and/hat, you wouldn’t want to call replace(‘t’,’’) on all categories in your database since it would result in, among other things, /this/and/that being changed to /his/and/ha
Note: There are additional actions that must be taken (outlined below) to ensure that the changes are made to your content management, preview, and production web server environments. Please review all the steps before beginning!!!!
When I found the need to do this on my own site, I looked in the Bric database and found the following tables:columns that seemed to contain path information — I realized that all of these paths would need to be updated as part of a global category path change and that other non-database actions would need to be taken such as deleting and republishing the affected assets and renaming directories in the /bricolage/data directory – see the steps below for a complete procedure…
table:column ------------- story_uri:uri media_uri:uri template:file_name resource:path resource:uri template_instance:file_name story:primary_uri category:uri category:directory media_instance:location media_instance:uri media_uri:uri
1) Shut down all Bric processes
2) Make a backup of your database
/usr/bin/pg_dump -f /var/lib/pgsql/backups/my_bricdb_backup bric
3) Execute the following sql where ‘programs_’ is replaced with the text you’re trying to change, and ‘programs’ is the new name.
begin;
update template Set file_name = replace(file_name, 'programs_', 'programs') Where file_name like '%programs_%';
update template_instance Set file_name = replace(file_name, 'programs_', 'programs') Where file_name like '%programs_%';
update story_uri Set uri = replace(uri, 'programs_', 'programs') Where uri like '%programs_%';
update story Set primary_uri = replace(primary_uri, 'programs_', 'programs') Where primary_uri like '%programs_%';
update media_uri Set uri = replace(uri, 'programs_', 'programs') Where uri like '%programs_%';
update media_instance Set location = replace(location, 'programs_', 'programs') Where location like '%programs_%';
update media_instance Set uri = replace(uri, 'programs_', 'programs') Where uri like '%programs_%';
update resource Set path = replace(path, 'programs_', 'programs') Where path like '%programs_%';
update resource Set uri = replace(uri, 'programs_', 'programs') Where uri like '%programs_%';
update category Set uri = replace(uri, 'programs_', 'programs') Where uri like '%programs_%';
update category Set directory = replace(directory, 'programs_', 'programs') Where directory like '%programs_%';
rollback; -- change to commit when you're sure you want to proceed for real
4) Rename directories in /usr/local/bricolage/data
5) Rename directories on the preview and production servers doc roots if necessary
6) start bricolage processes
7) republish all stories that were using the old path – you can probably do this most easily with the bulk publish feature of bricolage
— Bric.DavidBeaudet – 23 Apr 2007
Warning: These queries permanently remove an element type and anything that depends on them from the database. Change =ROLLBACK= to =COMMIT= when you’re ready to do this.
Note: These queries use the USING clause in the DELETE statement, which is available in PostgreSQL 8.1.
BEGIN;
\set element_type_key_name hnn_global_gr_toc
\set key_name '\'' :element_type_key_name '\''
-- media.fk_element_type__media : on delete restrict
DELETE FROM media
USING element_type e
WHERE element_type__id = e.id
AND e.key_name = :key_name;
-- media_element.fk_media_element__element_type : on delete restrict
DELETE FROM media_element
USING element_type e
WHERE element_type__id = e.id
AND e.key_name = :key_name;
-- story.fk_element_type__story : on delete restrict
DELETE FROM story
USING element_type e
WHERE element_type__id = e.id
AND e.key_name = :key_name;
-- story_element.fk_story_elemenet__element_type : on delete restrict
DELETE FROM story_element
USING element_type e
WHERE element_type__id = e.id
AND e.key_name = :key_name;
-- template.fk_element_type__template : on delete restrict
DELETE FROM TEMPLATE
USING element_type e
WHERE element_type__id = e.id
AND e.key_name = :key_name;
-- The following should be deleted through the ON DELETE CASCADE clause
-- on the foreign keys
-- attr_element_type_val.fk_et__attr_et_val : on delete cascade
-- element_type__output_channel.fk_element_type__et_oc : on delete cascade
-- element_type__site.fk_element_type__et__site__et__id : on delete cascade
-- element_type_member.fk_element__et_member : on delete cascade
-- field_type.fk_element_type__field_type : on delete cascade
DELETE FROM element_type
WHERE key_name = :key_name;
ROLLBACK;
BEGIN;
CREATE TEMP TABLE element_type_to_be_deleted
(
id INTEGER NOT NULL
, key_name TEXT NOT NULL
);
INSERT INTO element_type_to_be_deleted (id, key_name)
FROM element_type
WHERE key_name = 'element_to_delete';
-- media.fk_element_type__media : on delete restrict
DELETE FROM media
USING element_type_to_be_deleted e
WHERE element_type__id = e.id;
-- media_element.fk_media_element__element_type : on delete restrict
DELETE FROM media_element
USING element_type_to_be_deleted e
WHERE element_type__id = e.id;
-- story.fk_element_type__story : on delete restrict
DELETE FROM story
USING element_type_to_be_deleted e
WHERE element_type__id = e.id;
-- story_element.fk_story_elemenet__element_type : on delete restrict
DELETE FROM story_element
USING element_type_to_be_deleted e
WHERE element_type__id = e.id;
-- template.fk_element_type__template : on delete restrict
DELETE FROM TEMPLATE
USING element_type_to_be_deleted e
WHERE element_type__id = e.id;
-- The following should be deleted through the ON DELETE CASCADE clause
-- on the foreign keys
-- attr_element_type_val.fk_et__attr_et_val : on delete cascade
-- element_type__output_channel.fk_element_type__et_oc : on delete cascade
-- element_type__site.fk_element_type__et__site__et__id : on delete cascade
-- element_type_member.fk_element__et_member : on delete cascade
-- field_type.fk_element_type__field_type : on delete cascade
DELETE FROM element_type
USING element_type_to_be_deleted e
WHERE element_type.id = e.id;
ROLLBACK;
— Bric.MichaelGlaesemann – 25 May 2006
Warning: These queries permanently remove an output channel and anything that depends on them from the database. Change =ROLLBACK= to =COMMIT= when you’re ready to do this.
Note: These queries use the USING clause in the DELETE statement, which is available in PostgreSQL 8.1.
-- kill_output_channel.psql
-- killing an output channel psql script
-- to run the script kill_output_channel.psql against database dbname,
-- from the command line, run
-- psql -d dbname -f kill_output_channel.psql
-- use with ROLLBACK to see if it's doing what you expect.
-- change ROLLBACK to COMMIT when you're ready to do it for real.
-- change "Web" to the name of the output channel to be killed
\set output_channel_name Web
\set oc_name '\'' :output_channel_name '\''
BEGIN;
-- story_instance.fk_primary_oc__story_instance: ON DELETE SET NULL
-- why SET NULL? What's to be gained by having this hang around oc-less?
-- and primary_oc__id is set NOT NULL anyway, so this is going to hang
-- let's see what story_instances we'll be deleting
SELECT slug, oc."name" as oc
FROM story_instance
JOIN output_channel oc ON (oc.id = primary_oc__id)
WHERE oc."name" = :oc_name;
DELETE FROM story_instance
USING output_channel oc
WHERE primary_oc__id = oc.id
AND oc."name" = :oc_name;
-- just in case you want to see what templates you're deleting
SELECT "template"."name", "template".description
FROM "template"
JOIN output_channel oc ON (output_channel__id = oc.id)
WHERE oc."name" = :oc_name;
-- template.fk_output_channel__template: ON DELETE RESTRICT
DELETE FROM "template"
USING output_channel oc
WHERE output_channel__id = oc.id
AND oc."name" = :oc_name;
-- output_channel_member.fk_output_channel__oc_member
-- clean up member table entry
DELETE FROM member
USING output_channel_member ocm
, output_channel oc
WHERE member.id = ocm.member__id
AND ocm.object_id = oc.id
AND oc."name" = :oc_name;
-- clean up media
DELETE FROM member
USING media_member mm
, media__output_channel m_oc
, output_channel oc
WHERE member.id = mm.member__id
AND mm.object_id = m_oc.media_instance__id
AND m_oc.output_channel__id = oc.id
AND oc."name" = :oc_name;
DELETE FROM media
USING media__output_channel m_oc
, output_channel oc
WHERE media.id = m_oc.media_instance__id
AND m_oc.output_channel__id = oc.id
AND oc."name" = :oc_name;
DELETE FROM output_channel
WHERE "name" = :oc_name;
-- DELETE FROM output_channel should also clean up those with ON DELETE CASCADE
-- element_type__output_channel.fk_output_channel__et_oc
-- element_type__site.fk_output_channel__et__site
-- media__output_channel.fk_media__oc__oc
-- media_instance.fk_primary_oc__media_instance
-- output_channel_include.fk_oc__oc_include_inc
-- server_type__output_channel.fk_output_channel__st_oc
-- story__output_channel.fk_story__oc__oc
-- output_channel_include.fk_output_channel__oc_include
ROLLBACK;
— Bric.MichaelGlaesemann – 20 Jun 2006
SELECT DISTINCT g.name, lname, fname, mname
FROM person p, person_member pm, member m, grp g
WHERE p.id = pm.object_id
AND pm.member__id = m.id
AND m.grp__id = g.id
AND g.class__id = 9
AND g.id <> 1;
— Bric.DavidWheeler – 1 Nov 2005
DELETE FROM member WHERE id IN (
SELECT sm.member__id
FROM story_member sm, story s
WHERE s.id = sm.object_id
AND s.active = '0'
);
DELETE FROM story WHERE active = '0';
DELETE FROM member WHERE id IN (
SELECT mm.member__id
FROM media_member mm, media m
WHERE m.id = mm.object_id
AND m.active = '0'
);
DELETE FROM media WHERE active = '0';
— Bric.DavidWheeler – 25 Oct 2005
DELETE FROM job WHERE failed = '1';
[note from Scott – you first need to delete from the member table;
job_member rows cascade-delete, but member rows don’t. I think
this is the SQL, but I didn’t test it:
delete from member where id in
(select member__id from job_member, job
where job.id = job_member.object_id
and job.failed = '1');
]
— Bric.MarshallRoch – 15 Jun 2005
David Wheeler suggested this in an email- and it worked for me:
Kill bric_queued and Bricolage, then do this in psql:
UPDATE job SET executing = 0 WHERE executing = 1;Then restart.
— Bric.DawnBuie – 20 Feb 2006
select * from story_container_tile t, element e, at_type at
where t.element__id = e.id and t.active = '1'
and e.active = '1' and e.type__id = at.id
and t.object_instance_id = (
select max(i.id) from story_instance i, story s
where s.id = i.story__id and s.active = '1'
and s.primary_uri = '/some/uri'
)
and at.paginated = '1'
order by t.place;
You could also remove the at.paginated part (as well as all the
‘element e’ stuff) to get all container elements
(called “tiles” internally). The `at_type’ table lets you get whether
the container element is a top-level element, a page element, or a
“cover story” (fixed_url); this corresponds to “Element Type” in the UI.
The `element’ table lets you limit things to particular elements.
— Bric.ScottLanning – 02 May 2005
select primary_uri from story
where primary_uri like '/foo/%' and active = 1 and id in (
select distinct i.story__id from story_instance i, story s
where s.id = i.story__id and s.active = '1' and i.id in (
select object_instance_id from story_container_tile
where element__id = 1784 and active = '1'
group by object_instance_id having count(object_instance_id) > 1
)
) order by primary_uri;
Here 1784 is the ID of our ‘Page’ element, and /foo/% is the URI
you want to match.
— Bric.ScottLanning – 02 May 2005
Find all data elements for a story with a URI (1.8.3)
select t.name, t.short_val
from story_data_tile t
where t.active = '1' and t.object_instance_id = (
select max(i.id) from story_instance i, story s
where s.id = i.story__id and s.active = '1'
and s.primary_uri = '/some/uri'
);
select t.name, t.short_val
from story_data_tile t
where t.active = '1' and t.object_instance_id = (
select max(i.id) from story_instance i, story s
where s.id = i.story__id and s.active = '1'
and s.primary_uri = '/some/uri'
);
The column t.key_name is the name a template would use,
whereas t.name is what’s shown in the UI.
The column t.short_val is usually what a user would enter into
a data element through the UI.
The column t.element_data__id refers to `at_data.id’.
The column t.parent_id refers to the `story_container_tile.id’
(a data element’s parent must necessarily be a container element).
— Bric.ScottLanning – 02 May 2005
Find elements for a story with a given URI (1.8.3)
select id,name,key_name,place,object_order
from story_data_tile where parent_id = (
select t.id from story_container_tile t,element e,at_type at
where t.element__id=e.id and t.active=1 and e.active=1
and e.type__id=at.id and t.object_instance_id = (
select max(i.id) from story_instance i,story s
where s.id=i.story__id and s.active=1
and s.primary_uri = '/some/uri'
)
and at.top_level=1
) and active=1
UNION
select id,name,key_name,place,object_order
from story_container_tile where parent_id = (
select t.id from story_container_tile t,element e,at_type at
where t.element__id=e.id and t.active=1 and e.active=1
and e.type__id=at.id and t.object_instance_id = (
select max(i.id) from story_instance i,story s
where s.id=i.story__id and s.active=1
and s.primary_uri = '/some/uri'
) and at.top_level=1
) and active=1
ORDER BY place;
select id,name,key_name,place,object_order
from story_data_tile where parent_id = (
select t.id from story_container_tile t,element e,at_type at
where t.element__id=e.id and t.active=1 and e.active=1
and e.type__id=at.id and t.object_instance_id = (
select max(i.id) from story_instance i,story s
where s.id=i.story__id and s.active=1
and s.primary_uri = '/some/uri'
)
and at.top_level=1
) and active=1
UNION
select id,name,key_name,place,object_order
from story_container_tile where parent_id = (
select t.id from story_container_tile t,element e,at_type at
where t.element__id=e.id and t.active=1 and e.active=1
and e.type__id=at.id and t.object_instance_id = (
select max(i.id) from story_instance i,story s
where s.id=i.story__id and s.active=1
and s.primary_uri = '/some/uri'
) and at.top_level=1
) and active=1
ORDER BY place;
I’m not sure what to call the first-level elements. These are the
elements that you see on the “Story Profile” page; we can’t call them
“top-level” elements since that name is used to refer to the Story
or Media element (tile) itself. In any case, this UNION of two nearly
identical queries (except for the table name) returns all those elements.
UPDATE:
You can get the next “levels” of elements using
select id,name,key_name,place,object_order
from story_data_tile where parent_id = ? and active=1
UNION
select id,name,key_name,place,object_order
from story_container_tile where parent_id = ? and active=1
ORDER BY place;
Replace the question marks with the id from container elements
in the previous query.
— Bric.ScottLanning – 02 May 2005, updated 19 Jul 2006
The following was gathered by looking at DBI_DEBUG/DBI_CALL_TRACE output.
Say that we know that a desk has an ID called $DESKID.
First, get the asset group ID from the `desk’ table:
SELECT asset_grp FROM desk
WHERE active = '1' AND id = $DESKID;
We’ll use the result called $ASSETGRP below.
Stories:
SELECT s.id, s.primary_uri
FROM story_instance i, story_member sm, member m,
workflow w, story s, member m2, story_member sm2
WHERE s.id = i.story__id AND sm.object_id = s.id AND m.id = sm.member__id
AND m.active = '1' AND s.workflow__id = w.id
AND s.current_version = i.version
AND i.checked_out = (
SELECT max(checked_out) FROM story_instance
WHERE version = i.version AND story__id = i.story__id
)
AND s.active = '1' AND m2.grp__id = $ASSETGRP AND m2.active = '1'
AND sm2.member__id = m2.id AND s.id = sm2.object_id
GROUP BY s.id, s.priority, s.source__id, s.usr__id, s.element__id,
s.first_publish_date, s.publish_date, s.expire_date, s.cover_date,
s.current_version, s.published_version, s.workflow__id, s.publish_status,
s.primary_uri, s.active, s.desk__id, s.site__id, s.alias_id,
i.id, i.name, i.description, i.story__id, i.version, i.usr__id,
i.primary_oc__id, i.slug, i.checked_out ORDER BY s.cover_date ASC, s.id;
Media:
SELECT mt.id, i.uri
FROM media_instance i, media_member mm, member m, at_type at,
element e, workflow w,media mt, member m2, media_member mm2
WHERE mt.id = i.media__id AND mm.object_id = mt.id AND m.id = mm.member__id
AND m.active = '1' AND e.id = mt.element__id AND at.id = e.type__id
AND mt.workflow__id = w.id AND mt.current_version = i.version
AND i.checked_out = (
SELECT max(checked_out) FROM media_instance
WHERE version = i.version AND media__id = i.media__id
)
AND mt.active = '1' AND m2.grp__id = $ASSETGRP AND m2.active = '1'
AND mm2.member__id = m2.id AND mt.id = mm2.object_id
GROUP BY mt.id, mt.element__id, mt.priority, mt.source__id,
mt.current_version, mt.published_version, mt.usr__id,
mt.first_publish_date, mt.publish_date,
mt.expire_date, mt.cover_date, mt.workflow__id, mt.desk__id,
mt.publish_status, mt.active, mt.site__id, mt.alias_id,
i.id, i.name, i.description, i.media__id,
i.usr__id, i.version, i.media_type__id, i.primary_oc__id,
i.file_size, i.file_name, i.location, i.uri, i.checked_out, at.biz_class__id
ORDER BY mt.cover_date ASC, mt.id;
Templates:
SELECT f.id, f.file_name
FROM formatting_instance i, formatting_member fm, member m,
workflow w, formatting f, member m2, formatting_member fm2
WHERE f.id = i.formatting__id AND fm.object_id = f.id AND m.id = fm.member__id
AND m.active = '1' AND f.workflow__id = w.id
AND f.current_version = i.version
AND i.checked_out = (
SELECT max(checked_out) FROM formatting_instance
WHERE version = i.version AND formatting__id = i.formatting__id
)
AND f.active = '1' AND m2.active = '1' AND m2.grp__id = $ASSETGRP
AND f.id = fm2.object_id AND fm2.member__id = m2.id
GROUP BY f.id, f.name, f.priority, f.description, f.usr__id,
f.output_channel__id, f.tplate_type, f.element__id, f.file_name,
f.current_version, f.published_version, f.deploy_status, f.deploy_date,
f.expire_date, f.workflow__id, f.desk__id, f.active, f.site__id, i.id,
i.formatting__id, i.version, i.usr__id, i.data, i.file_name, i.checked_out
ORDER BY f.deploy_date ASC, f.id;
— Bric.ScottLanning – 12 Jul 2005
select t.name, t.key_name, s.primary_uri
from story_data_tile t, story_instance i, story s
where t.active=1 and s.active=1
and i.id = t.object_instance_id and i.story__id = s.id
and (t.short_val like '%/cgi-bin/fm.pl%'
or t.blob_val like '%/cgi-bin/fm.pl%');
— Bric.ScottLanning – 12 Oct 2005
select u.login, date_trunc('month', e.timestamp),
count(date_trunc('month', e.timestamp))
from event e, usr u
where e.usr__id = u.id and u.active = '1' and e.timestamp >= '2005-07-01 00:00:00'
group by date_trunc('month', e.timestamp), u.login
order by date_trunc('month', e.timestamp) desc,
count(date_trunc('month', e.timestamp)) desc, u.login;
select extract(year from comp_time) as year,
extract(month from comp_time) as month,
extract(day from comp_time) as day,
extract(hour from comp_time) as hour,
count(*)
from job
where class__id = (select id from class where key_name='pub_job')
and story__id is not null
group by year,month,day,hour
order by year,month,day,hour;
— Bric.ScottLanning – 11 Jun 2007
E.g. reset password of admin user:
update usr set password='' where id=0;
Now login with empty password and set it to a new value via the user interface.