200 lines
8.6 KiB
SQL
200 lines
8.6 KiB
SQL
-- Migration: Add Menu Module Access System
|
|
-- Description: Enhance master_modules and create menu_modules & user_level_module_accesses tables
|
|
-- Date: 2026-01-15
|
|
|
|
-- ============================================================================
|
|
-- Step 1: Enhance master_modules with action_type
|
|
-- ============================================================================
|
|
ALTER TABLE master_modules
|
|
ADD COLUMN IF NOT EXISTS action_type VARCHAR NULL;
|
|
|
|
COMMENT ON COLUMN master_modules.action_type IS 'Tipe aksi: view, create, edit, delete, approve, export, etc';
|
|
|
|
-- ============================================================================
|
|
-- Step 2: Create menu_modules table
|
|
-- ============================================================================
|
|
CREATE TABLE IF NOT EXISTS menu_modules (
|
|
id SERIAL PRIMARY KEY,
|
|
menu_id INT NOT NULL,
|
|
module_id INT NOT NULL,
|
|
position INT NULL,
|
|
client_id UUID NULL,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW(),
|
|
FOREIGN KEY (menu_id) REFERENCES master_menus(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (module_id) REFERENCES master_modules(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Add unique constraint with client_id consideration
|
|
CREATE UNIQUE INDEX idx_menu_modules_unique
|
|
ON menu_modules(menu_id, module_id, COALESCE(client_id, '00000000-0000-0000-0000-000000000000'::UUID));
|
|
|
|
-- Add other indexes for performance
|
|
CREATE INDEX idx_menu_modules_menu_id ON menu_modules(menu_id);
|
|
CREATE INDEX idx_menu_modules_module_id ON menu_modules(module_id);
|
|
CREATE INDEX idx_menu_modules_client_id ON menu_modules(client_id) WHERE client_id IS NOT NULL;
|
|
CREATE INDEX idx_menu_modules_is_active ON menu_modules(is_active);
|
|
|
|
-- Add comments
|
|
COMMENT ON TABLE menu_modules IS 'Relasi many-to-many antara menu dan modul. Satu menu bisa punya banyak modul.';
|
|
COMMENT ON COLUMN menu_modules.position IS 'Urutan modul dalam menu untuk sorting';
|
|
COMMENT ON COLUMN menu_modules.menu_id IS 'Foreign key ke master_menus';
|
|
COMMENT ON COLUMN menu_modules.module_id IS 'Foreign key ke master_modules';
|
|
|
|
-- ============================================================================
|
|
-- Step 3: Create user_level_module_accesses table
|
|
-- ============================================================================
|
|
CREATE TABLE IF NOT EXISTS user_level_module_accesses (
|
|
id SERIAL PRIMARY KEY,
|
|
user_level_id INT NOT NULL,
|
|
module_id INT NOT NULL,
|
|
can_access BOOLEAN DEFAULT TRUE,
|
|
client_id UUID NULL,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW(),
|
|
FOREIGN KEY (user_level_id) REFERENCES user_levels(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (module_id) REFERENCES master_modules(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Add unique constraint with client_id consideration
|
|
CREATE UNIQUE INDEX idx_user_level_module_accesses_unique
|
|
ON user_level_module_accesses(user_level_id, module_id, COALESCE(client_id, '00000000-0000-0000-0000-000000000000'::UUID));
|
|
|
|
-- Add other indexes for performance
|
|
CREATE INDEX idx_user_level_module_accesses_user_level_id ON user_level_module_accesses(user_level_id);
|
|
CREATE INDEX idx_user_level_module_accesses_module_id ON user_level_module_accesses(module_id);
|
|
CREATE INDEX idx_user_level_module_accesses_client_id ON user_level_module_accesses(client_id) WHERE client_id IS NOT NULL;
|
|
CREATE INDEX idx_user_level_module_accesses_is_active ON user_level_module_accesses(is_active);
|
|
CREATE INDEX idx_user_level_module_accesses_can_access ON user_level_module_accesses(can_access);
|
|
|
|
-- Add comments
|
|
COMMENT ON TABLE user_level_module_accesses IS 'Mengatur akses user_level ke modul-modul tertentu. Kontrol akses granular per modul.';
|
|
COMMENT ON COLUMN user_level_module_accesses.can_access IS 'Apakah user level ini boleh akses modul ini. True=boleh, False=tidak boleh';
|
|
COMMENT ON COLUMN user_level_module_accesses.user_level_id IS 'Foreign key ke user_levels';
|
|
COMMENT ON COLUMN user_level_module_accesses.module_id IS 'Foreign key ke master_modules';
|
|
|
|
-- ============================================================================
|
|
-- Step 4: Migrate existing data (optional - only if you have existing data)
|
|
-- ============================================================================
|
|
-- Copy existing menu.module_id relationship to menu_modules
|
|
-- This creates initial menu-module relationships from existing master_menus
|
|
INSERT INTO menu_modules (menu_id, module_id, position, client_id, is_active, created_at, updated_at)
|
|
SELECT
|
|
id as menu_id,
|
|
module_id,
|
|
position as position, -- Use existing position if available
|
|
client_id,
|
|
is_active,
|
|
created_at,
|
|
updated_at
|
|
FROM master_menus
|
|
WHERE module_id IS NOT NULL
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- ============================================================================
|
|
-- Step 5: Insert sample modules for testing (optional)
|
|
-- ============================================================================
|
|
-- You can uncomment this section if you want sample data
|
|
|
|
/*
|
|
-- Sample modules for Article Management
|
|
INSERT INTO master_modules (name, description, path_url, action_type, status_id, is_active) VALUES
|
|
('View Articles', 'View article list and details', '/api/articles', 'view', 1, true),
|
|
('Create Article', 'Create new article', '/api/articles/create', 'create', 1, true),
|
|
('Edit Article', 'Edit existing article', '/api/articles/edit', 'edit', 1, true),
|
|
('Delete Article', 'Delete article', '/api/articles/delete', 'delete', 1, true),
|
|
('Approve Article', 'Approve article submission', '/api/articles/approve', 'approve', 1, true),
|
|
('Export Articles', 'Export articles to file', '/api/articles/export', 'export', 1, true)
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- Sample modules for User Management
|
|
INSERT INTO master_modules (name, description, path_url, action_type, status_id, is_active) VALUES
|
|
('View Users', 'View user list and details', '/api/users', 'view', 1, true),
|
|
('Create User', 'Create new user', '/api/users/create', 'create', 1, true),
|
|
('Edit User', 'Edit existing user', '/api/users/edit', 'edit', 1, true),
|
|
('Delete User', 'Delete user', '/api/users/delete', 'delete', 1, true)
|
|
ON CONFLICT DO NOTHING;
|
|
*/
|
|
|
|
-- ============================================================================
|
|
-- Step 6: Verification queries
|
|
-- ============================================================================
|
|
-- Run these queries to verify the migration
|
|
|
|
-- Check if tables exist
|
|
DO $$
|
|
DECLARE
|
|
v_menu_modules_exists BOOLEAN;
|
|
v_user_level_module_accesses_exists BOOLEAN;
|
|
v_action_type_exists BOOLEAN;
|
|
BEGIN
|
|
-- Check menu_modules table
|
|
SELECT EXISTS (
|
|
SELECT FROM information_schema.tables
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'menu_modules'
|
|
) INTO v_menu_modules_exists;
|
|
|
|
-- Check user_level_module_accesses table
|
|
SELECT EXISTS (
|
|
SELECT FROM information_schema.tables
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'user_level_module_accesses'
|
|
) INTO v_user_level_module_accesses_exists;
|
|
|
|
-- Check action_type column
|
|
SELECT EXISTS (
|
|
SELECT FROM information_schema.columns
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'master_modules'
|
|
AND column_name = 'action_type'
|
|
) INTO v_action_type_exists;
|
|
|
|
-- Report results
|
|
RAISE NOTICE '========================================';
|
|
RAISE NOTICE 'Migration Verification Results:';
|
|
RAISE NOTICE '========================================';
|
|
|
|
IF v_menu_modules_exists THEN
|
|
RAISE NOTICE '✓ Table menu_modules created successfully';
|
|
ELSE
|
|
RAISE WARNING '✗ Table menu_modules NOT created';
|
|
END IF;
|
|
|
|
IF v_user_level_module_accesses_exists THEN
|
|
RAISE NOTICE '✓ Table user_level_module_accesses created successfully';
|
|
ELSE
|
|
RAISE WARNING '✗ Table user_level_module_accesses NOT created';
|
|
END IF;
|
|
|
|
IF v_action_type_exists THEN
|
|
RAISE NOTICE '✓ Column action_type added to master_modules';
|
|
ELSE
|
|
RAISE WARNING '✗ Column action_type NOT added to master_modules';
|
|
END IF;
|
|
|
|
RAISE NOTICE '========================================';
|
|
|
|
IF v_menu_modules_exists AND v_user_level_module_accesses_exists AND v_action_type_exists THEN
|
|
RAISE NOTICE 'Migration completed successfully! ✓';
|
|
ELSE
|
|
RAISE WARNING 'Migration completed with errors! Please check above.';
|
|
END IF;
|
|
|
|
RAISE NOTICE '========================================';
|
|
END $$;
|
|
|
|
-- Show table statistics
|
|
SELECT
|
|
'menu_modules' as table_name,
|
|
COUNT(*) as row_count
|
|
FROM menu_modules
|
|
UNION ALL
|
|
SELECT
|
|
'user_level_module_accesses' as table_name,
|
|
COUNT(*) as row_count
|
|
FROM user_level_module_accesses;
|
|
|