Child pages
  • Rebuilding the XNAT database views

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  1. If Tomcat is currently running, stop it.
  2. Once Tomcat has stopped, run the SQL below (it is also attached to this page as an SQL script file):
    • Start the PostgreSQL command-line client psql and paste the commands in directly
    • Save the commands into a file or download the SQL script file and run them from the command line:

      Code Block
      languagebash
      titleRunning XNAT database view SQL script
      $ psql < drop_user_views.sql
    • Run the commands from pgAdmino r other  or another database-management toolstool
  3. When the script has completed, restart Tomcat.

The SQL is shown below:. Note that you'll want to change the name specified in the call to drop_user_views() to reflect the database user configured for your XNAT deployment.

Code Block
languagesql
titleFinding and dropping all XNAT database views
CREATE OR REPLACE FUNCTION find_user_views(username TEXT)
  RETURNS TABLE(table_schema NAME, view_name NAME) AS $$
BEGIN
  RETURN QUERY
  SELECT
    n.nspname AS table_schema,
    c.relname AS view_name
  FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n
      ON (n.oid = c.relnamespace)
  WHERE c.relkind = 'v'
        AND c.relowner = (SELECT usesysid
                          FROM pg_catalog.pg_user
                          WHERE usename = $1);
END$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION drop_user_views(username TEXT)
  RETURNS INTEGER AS $$
DECLARE
  r RECORD;
  s TEXT;
  c INTEGER := 0;
BEGIN
  RAISE NOTICE 'Dropping views for user %', $1;
  FOR r IN
    SELECT * FROM find_user_views($1)
  LOOP
    S := 'DROP VIEW IF EXISTS ' || quote_ident(r.table_schema) || '.' || quote_ident(r.view_name) || ' CASCADE;';
    EXECUTE s;
    c := c + 1;
    RAISE NOTICE 's = % ', S;
  END LOOP;
  RETURN c;
END$$ LANGUAGE plpgsql;


-- Change 'xnat' to the database user for your XNAT deployment.
SELECT drop_user_views('xnat');

...