/*#ifndef REINDEX_SQL_HEADER
**#define REINDEX_SQL_HEADER
**static char SCCS_ID[] = @(#) LIV  2001/08/01 11:27:21   reindex.sql \main\callpilot2.0\1 ~
** LIV  2001/08/01 11:27:21   reindex.sql \main\callpilot2.0\1 ~
** [liv/Database/nbdb0078]
**
**#endif
*/
--  *************************************************************************
--  *       Copyright (C) 1988-2000, by Sybase, Inc.                        *
--  *       All rights reserved. No part of this software may be            *
--  *       reproduced in any form or by any means - graphic,               *
--  *       electronic or mechanical, including photocopying,               *
--  *       recording, taping or information storage and retrieval          *
--  *       systems - except with the written permission of                 *
--  *       Sybase, Inc.                                                    *
--  *************************************************************************
--
--  WARNING: This is a program generated file. Do not edit.
--
    
if @reindex_database = 'Y' then
create procedure SYS._upgrade_reindex_db()
begin
DECLARE LOCAL TEMPORARY TABLE
    my_sysreindex( seq INTEGER PRIMARY KEY, stmt LONG VARCHAR ) IN SYSTEM
    ON COMMIT PRESERVE ROWS
 ; 
DECLARE LOCAL TEMPORARY TABLE
    my_sysdropindex( seq INTEGER PRIMARY KEY, stmt LONG VARCHAR ) IN SYSTEM
    ON COMMIT PRESERVE ROWS
 ; 
SET TEMPORARY OPTION ESCAPE_CHARACTER = 'OFF'
 ; 
create variable @seq int
 ; 
set @seq = 0
 ; 
create variable @temp long varchar
 ; 
IF db_property( 'FileVersion' ) < 37 THEN
    SET @temp = 'SELECT *, NULL as remote_location, ''SA'' as server_type INTO #systable FROM SYS.SYSTABLE';
ELSEIF db_property( 'FileVersion' ) < 1000 THEN
    SET @temp = 'SELECT *, (IF remote_location IS NULL THEN ''SA'' ELSE ''OMNI'' ENDIF) as server_type INTO #systable FROM SYS.SYSTABLE';
ELSE
    SET @temp = 'SELECT * INTO #systable FROM SYS.SYSTABLE';
END IF
 ; 
EXECUTE ( @temp );
 ; 
SELECT * INTO #syscolumn FROM SYS.SYSCOLUMN
 ; 
SELECT * INTO #sysuserperm FROM SYS.SYSUSERPERM
 ; 
SELECT * INTO #sysfile FROM SYS.SYSFILE
 ; 
SELECT * INTO #systrigger FROM SYS.SYSTRIGGER
 ; 
SELECT * INTO #sysindex FROM SYS.SYSINDEX
 ; 
SELECT * INTO #sysixcol FROM SYS.SYSIXCOL
 ; 
SELECT * INTO #sysforeignkey FROM SYS.SYSFOREIGNKEY
 ; 
SELECT * INTO #sysfkcol FROM SYS.SYSFKCOL
 ; 
SELECT * INTO #sysdomain FROM SYS.SYSDOMAIN
 ; 
COMMIT
 ; 
CREATE UNIQUE INDEX ix1 ON #sysuserperm( user_id )
 ; 
CREATE UNIQUE INDEX ix2 ON #systable( table_id )
 ; 
CREATE UNIQUE INDEX ix3 ON #sysixcol( table_id, index_id, sequence )
 ; 
CREATE UNIQUE INDEX ix4 ON #sysfile( file_id )
 ; 
CREATE UNIQUE INDEX ix5 ON #sysforeignkey( foreign_table_id, foreign_key_id )
 ; 
CREATE UNIQUE INDEX ix6 ON #syscolumn( table_id, column_id )
 ; 
CREATE INDEX ix7 ON #systrigger( foreign_table_id, foreign_key_id )
 ; 
CREATE INDEX ix8 ON #sysindex( table_id, index_id )
 ; 
BEGIN
    DECLARE @add_comma		int;
    
    FOR sysindex_loop AS sysindex_cursor CURSOR FOR 
	SELECT i.table_id as @table_id, i.index_id as @index_id,
	       i."unique" as @unique,
	       i.index_name as @index_name, i.remarks as @remarks, 
	       t.table_name as @table_name,
	       iu.user_name as @index_creator_name, tu.user_name as @table_creator_name,
	       f.dbspace_name as @dbspace_name
	FROM   #sysindex i, #systable t, #sysuserperm iu, #sysuserperm tu, #sysfile f
	WHERE  i."unique" <> 'U'
	AND    i.table_id = t.table_id
	AND    t.server_type = 'SA'
	AND    iu.user_id = i.creator
	AND    tu.user_id = t.creator
	AND    f.file_id = i.file_id
	AND    EXISTS ( 
		SELECT * 
		FROM #sysixcol ixc, #syscolumn c, #sysdomain d
		WHERE ixc.table_id = i.table_id 
		AND   ixc.index_id = i.index_id
		AND   c.table_id = ixc.table_id
		AND   c.column_id = ixc.column_id
		AND   c.domain_id = d.domain_id
		AND    (d.domain_name LIKE '%char%') 
	       )
	ORDER BY i.table_id DESC, i.index_id DESC
    FOR READ ONLY
    DO
	SET @temp = 'CREATE ' || IF @unique = 'Y' THEN 'UNIQUE ' ELSE '' ENDIF || 'INDEX "' ||
	           @index_name || '" ON "' || @table_creator_name || '"."' || @table_name || '" (';
	
	SET @add_comma = 0;
	FOR sysixcol_loop AS sysixcol_cursor CURSOR FOR 
	    SELECT col.column_name as @column_name, "order" as @order
	    FROM   #sysixcol ixc, #syscolumn col
	    WHERE  ixc.table_id = @table_id
	    AND    ixc.index_id = @index_id
	    AND    ixc.table_id = col.table_id
	    AND    ixc.column_id = col.column_id
	    ORDER BY ixc.sequence
	FOR READ ONLY
	DO
	    IF @add_comma != 0 THEN
		SET @temp = @temp || ',';
	    ELSE
		SET @add_comma = 1;
	    END IF;
	    SET @temp = @temp || ' "' || @column_name || '" ';
	    IF @order = 'A' then
		SET @temp = @temp || 'ASC';
	    ELSE
		SET @temp = @temp || 'DESC';
	    END IF;
	END FOR;
	SET @temp = @temp || ' ) ON "' || @dbspace_name || '"';
	IF @remarks IS NOT NULL THEN
	    INSERT INTO my_sysreindex( seq, stmt )
		
		VALUES( @seq, 'COMMENT ON INDEX "' || @index_creator_name   || '"."' || @index_name || '" is ''' || @remarks || '''' );
	    SET @seq = @seq + 1;
	END IF;
	
	INSERT INTO my_sysreindex( seq, stmt )
	    VALUES( @seq, @temp );
	INSERT INTO my_sysdropindex( seq, stmt )
	    VALUES( @seq, 'DROP INDEX "' || @index_creator_name || '"."' || @table_name || '"."' || @index_name || '"' );
	SET @seq = @seq + 1;
    END FOR;
END;
 ; 
BEGIN
    DECLARE @primary_cols		long varchar;
    DECLARE @foreign_cols		long varchar;
    DECLARE @add_comma 			int;
    
    FOR sysforeignkey_loop AS sysforeignkey_cursor CURSOR FOR 
	SELECT  fk.foreign_table_id as @foreign_table_id, fk.foreign_key_id as @foreign_key_id,
		fk.primary_table_id as @primary_table_id, fk.check_on_commit as @check_on_commit,
		fk.nulls as @nulls, fk.role as @role, fk.remarks as @remarks,
		pt.table_name as @primary_table_name, ptu.user_name as @primary_table_creator_name,
		ft.table_name as @foreign_table_name, ftu.user_name as @foreign_table_creator_name
	FROM   #sysforeignkey fk, #systable pt, #systable ft, #sysuserperm ptu, #sysuserperm ftu
	WHERE pt.table_id = fk.primary_table_id
	AND   ft.table_id = fk.foreign_table_id
	AND   pt.server_type = 'SA'
	AND   ft.server_type = 'SA'
	AND   ptu.user_id = pt.creator
	AND   ftu.user_id = ft.creator
	AND    EXISTS ( 
		SELECT * 
		FROM  #sysfkcol fkc, #syscolumn c, #sysdomain d
		WHERE fk.foreign_table_id = fkc.foreign_table_id
		AND   fk.foreign_key_id = fkc.foreign_key_id
		AND   c.table_id = fk.primary_table_id
		AND   c.column_id = fkc.primary_column_id
		AND   c.domain_id = d.domain_id
		AND    (d.domain_name LIKE '%char%') 
	       )
	ORDER BY fk.foreign_table_id DESC, fk.foreign_key_id DESC
    FOR READ ONLY
    DO
	SET @temp = 'ALTER TABLE "' || @foreign_table_creator_name || '"."' || @foreign_table_name || '" ADD ' ||
		   (IF @nulls = 'N' THEN 'NOT NULL ' ELSE '' ENDIF) ||
		   'FOREIGN KEY ' || '"' || @role || '" ( ';
	SET @primary_cols = '';
	SET @add_comma = 0;
	FOR sysfkcol_loop AS sysfkcol_cursor CURSOR FOR
	    SELECT pc.column_name as @primary_column_name, 
		   fc.column_name as @foreign_column_name
	    FROM   #sysfkcol fkcol, #syscolumn pc, #syscolumn fc
	    WHERE  fkcol.foreign_table_id = @foreign_table_id
	    AND	   fkcol.foreign_key_id = @foreign_key_id
	    AND	   pc.table_id = @primary_table_id
	    AND    pc.column_id = fkcol.primary_column_id
	    AND	   fc.table_id = @foreign_table_id
	    AND    fc.column_id = fkcol.foreign_column_id
	    ORDER BY fkcol.primary_column_id ASC
	FOR READ ONLY
	DO
	    IF @add_comma != 0 THEN
		SET @temp = @temp || ',';
		SET @primary_cols = @primary_cols || ',';
	    ELSE
		SET @add_comma = 1;
	    END IF;
	    SET @temp = @temp || '"' || @foreign_column_name || '" ';
	    SET @primary_cols = @primary_cols || '"' || @primary_column_name || '" ';
	END FOR;
	SET @temp = @temp || ') REFERENCES "' || @primary_table_creator_name || '"."' || @primary_table_name || 
	    '" ( ' || @primary_cols || ') ';
	FOR systrigger_loop AS systrigger_cursor CURSOR FOR
	    SELECT event as @event, referential_action as @referential_action
	    FROM #systrigger
	    WHERE foreign_table_id = @foreign_table_id
	    AND   foreign_key_id = @foreign_key_id
	FOR READ ONLY
	DO
	    IF @event = 'D' THEN
		SET @temp = @temp || 'ON DELETE ';
	    ELSE
		SET @temp = @temp || 'ON UPDATE ';
	    END IF;
	    IF @referential_action = 'C' THEN
		SET @temp = @temp || 'CASCADE ';
	    ELSEIF @referential_action = 'N' THEN
		SET @temp = @temp || 'SET NULL ';
	    ELSEIF @referential_action = 'D' THEN
		SET @temp = @temp || 'SET DEFAULT ';
	    END IF;
	END FOR;
	IF @check_on_commit = 'Y' THEN
	    SET @temp = @temp || 'CHECK ON COMMIT';
	END IF;
	IF @remarks IS NOT NULL THEN
	    
	    INSERT INTO my_sysreindex( seq, stmt )
		VALUES( @seq, 'COMMENT ON FOREIGN KEY "' || @foreign_table_creator_name || '"."' || @foreign_table_name || '"."' || @role || '" is ''' || @remarks || '''' );
	    SET @seq = @seq + 1;
	END IF;
	
	INSERT INTO my_sysreindex( seq, stmt )
	    VALUES( @seq, @temp );
	INSERT INTO my_sysdropindex( seq, stmt )
	    VALUES( @seq, 'ALTER TABLE "' || @foreign_table_creator_name || '"."' || @foreign_table_name || '" ' ||
			  'DELETE FOREIGN KEY "' || @role || '"' );
	SET @seq = @seq + 1;
    END FOR;
END
 ; 
BEGIN
   DECLARE @temp_drop		long varchar;
   DECLARE @add_comma		int;
    FOR sysindex_loop AS unique_cursor CURSOR FOR
	SELECT i.table_id as @table_id, i.index_id as @index_id,
	       i.index_name as @index_name, i.remarks as @remarks,
	       t.table_name as @table_name,
	       iu.user_name as @index_creator_name, tu.user_name as @table_creator_name,
	       f.dbspace_name as @dbspace_name
	FROM   #sysindex i, #systable t, #sysuserperm iu, #sysuserperm tu, #sysfile f
	WHERE  i."unique" = 'U'
	AND    i.table_id = t.table_id
	AND    t.server_type = 'SA'
	AND    iu.user_id = i.creator
	AND    tu.user_id = t.creator
	AND    f.file_id = i.file_id
	AND    EXISTS ( 
		SELECT * 
		FROM #sysixcol ixc, #syscolumn c, #sysdomain d
		WHERE ixc.table_id = i.table_id 
		AND   ixc.index_id = i.index_id
		AND   c.table_id = ixc.table_id
		AND   c.column_id = ixc.column_id
		AND   c.domain_id = d.domain_id
		AND    (d.domain_name LIKE '%char%') 
	       )
	ORDER BY i.table_id DESC, i.index_id DESC
    FOR READ ONLY
    DO
	SET @temp      = 'ALTER TABLE "' || @table_creator_name || '"."' || @table_name || '" ADD UNIQUE (';
	SET @temp_drop = 'ALTER TABLE "' || @table_creator_name || '"."' || @table_name || '" DELETE UNIQUE (';
	SET @add_comma = 0;
	FOR ixcol_loop AS ixcol_cursor CURSOR FOR
	    SELECT col.column_name as @column_name
	    FROM   #sysixcol ixc, #syscolumn col
	    WHERE  ixc.table_id = @table_id
	    AND    ixc.index_id = @index_id
	    AND	   ixc.table_id = col.table_id
	    AND	   ixc.column_id = col.column_id
	    ORDER BY ixc.sequence
	FOR READ ONLY
	DO
	    
	    IF @add_comma != 0 THEN
		SET @temp      = @temp      || ',';
		SET @temp_drop = @temp_drop || ',';
	    ELSE
		SET @add_comma = 1;
	    END IF;
	    SET @temp      = @temp      || ' "' || @column_name || '"';
	    SET @temp_drop = @temp_drop || ' "' || @column_name || '"';
	END FOR;
	SET @temp      = @temp      || ' )';
	SET @temp_drop = @temp_drop || ' )';
	IF @remarks IS NOT NULL THEN
	    
	    INSERT INTO my_sysreindex( seq, stmt )
		VALUES( @seq, 'COMMENT ON INDEX "' || @index_creator_name   || '"."' || @index_name || '" is ''' || @remarks || '''' );
	    SET @seq = @seq + 1;
	END IF;
	
	INSERT INTO my_sysreindex( seq, stmt )
	    VALUES( @seq, @temp );
	INSERT INTO my_sysdropindex( seq, stmt )
	    VALUES( @seq, @temp_drop );
	SET @seq = @seq + 1;
    END FOR;
END
 ; 
BEGIN
    DECLARE @add_comma	int;
    FOR systable_loop AS systable_cursor CURSOR FOR
	SELECT t.table_id as @table_id, u.user_name as @table_creator_name, t.table_name as @table_name
	FROM   #systable t, #sysuserperm u
	WHERE  t.table_type = 'BASE'
        AND    t.creator = u.user_id
	AND    t.server_type = 'SA'
	AND    EXISTS (
		SELECT * 
		FROM  #syscolumn c, #sysdomain d
		WHERE c.table_id = t.table_id
		AND   c.pkey = 'Y'
		AND   c.domain_id = d.domain_id
		AND    (d.domain_name LIKE '%char%') 
	       )
	ORDER BY t.table_id DESC
    FOR READ ONLY
    DO
	
	SET @temp = 'ALTER TABLE "' || @table_creator_name || '"."' || @table_name || '" ' ||
	                    'ADD PRIMARY KEY(';
	SET @add_comma = 0;
	FOR syscolumn_loop AS syscolumn_cursor CURSOR FOR
	    SELECT col.column_name as @column_name
	    FROM   #syscolumn col
	    WHERE  col.table_id = @table_id
	    AND    pkey = 'Y'
	    ORDER BY column_id
	FOR READ ONLY
	DO
	    IF @add_comma != 0 THEN
		SET @temp = @temp || ',';
	    ELSE
		SET @add_comma = 1;
	    END IF;
	    SET @temp = @temp || ' "' || @column_name || '"';
	END FOR;
	IF @add_comma != 0 THEN
	    
	    SET @temp = @temp || ' )';
	    
	    INSERT INTO my_sysreindex( seq, stmt )
		VALUES( @seq, @temp );
	    INSERT INTO my_sysdropindex( seq, stmt )
		VALUES( @seq, 'ALTER TABLE "' || @table_creator_name || '"."' || @table_name || '" ' ||
		              'DELETE PRIMARY KEY' );
	    SET @seq = @seq + 1;
	END IF;
    END FOR;
END
 ; 
BEGIN
    FOR sysdropindex_loop AS sysdropindex_cursor CURSOR FOR
	SELECT stmt as @stmt
	    FROM my_sysdropindex
	    ORDER BY seq
	
    FOR READ ONLY
    DO
	PRINT @stmt;
	EXECUTE (@stmt);
	COMMIT;
    END FOR;
END
 ; 
BEGIN
    FOR sysdropindex_loop AS sysreindex_cursor CURSOR FOR
	SELECT stmt as @stmt
	    FROM my_sysreindex
	    ORDER BY seq DESC
	
    FOR READ ONLY
    DO
	PRINT @stmt;
	EXECUTE (@stmt);
	COMMIT;
    END FOR;
END
 ; 
end
 ; 
call SYS._upgrade_reindex_db
 ; 
drop procedure SYS._upgrade_reindex_db
 ; 
end if
go