Useful SQL

phillipadsmith edited this page Aug 16, 2010 · 8 revisions
Clone this wiki locally

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.

Bricolage 2.0

Had a scenario where a bunch of stories that were created with bric_soap had the wrong workflow. I used this to fix them:

UPDATE public.story SET workflow__id = 101, desk__id = 101 where "primary_uri" LIKE '%themes%' AND "workflow__id" LIKE '%1027%' AND "desk__id" LIKE '%1027%'

You’d want to replace the workflow__id and desk__id with the IDs that you’re system uses for the workflow & desk your after.

Bricolage 1.10.5

Fix Broken Checkouts

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, usr__id
  FROM story
 WHERE id = $story_id;

If either the workflow_id or desk__id 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 usr__id is not NULL but the story is supposed to be checked in, then set it to NULL:

UPDATE story SET usr__id = NULL WHERE id = $story_id;

If workflow__id and desk__id are properly set to 0 and usr__id is NULL but 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.

Delete old site groups

(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

Reactivate deactivated contributor


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 
); 

Bricolage 1.10.3

Display the first-level elements of a story given its ‘id’

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

Bricolage 1.10.2

Change the URL of a category

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

Bricolage 1.10.1

Permanently remove element type

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.

Using psql variables


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;

Using temp table (if you’re not using the =psql= client)


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

Permanently remove an output channel

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

Bricolage 1.8.5

Show all contributors


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 deactivated stories and media (1.8.5)


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 all failed jobs (1.8.5)


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 = false WHERE executing = true;

Then restart.

— Bric.DawnBuie – 20 Feb 2006

Bricolage 1.8.3

Find all “Pages” for a story with a given URI (1.8.3)

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

Find multi-page stories with a given URI (1.8.3)


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'
);

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;

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

List all assets on a particular desk (1.8.3)

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

Find all stories where a field has a certain string of text (1.8.3)


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

Top users by number of events in a certain timeframe (1.8.3)


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;

Report number of publish jobs completed per hour (1.8.3)


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

Bricolage 1.8.x

Reset a forgotten password

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.