105 lines
4.0 KiB
PL/PgSQL
105 lines
4.0 KiB
PL/PgSQL
-- Migration: AddNetworkGroups
|
|
-- Description: Adds SplashNetworkGroup and SplashNetworkGroupMember tables
|
|
|
|
BEGIN;
|
|
|
|
-- Create SplashNetworkGroups table
|
|
CREATE TABLE "SplashNetworkGroups" (
|
|
"Id" serial PRIMARY KEY,
|
|
"Name" character varying(256) NOT NULL,
|
|
"Description" character varying(512),
|
|
"IsActive" boolean NOT NULL DEFAULT true,
|
|
"TenantId" integer NOT NULL,
|
|
"CreationTime" timestamp with time zone NOT NULL DEFAULT now(),
|
|
"CreatorUserId" bigint,
|
|
"LastModificationTime" timestamp with time zone,
|
|
"LastModifierUserId" bigint,
|
|
"IsDeleted" boolean NOT NULL DEFAULT false,
|
|
"DeleterUserId" bigint,
|
|
"DeletionTime" timestamp with time zone
|
|
);
|
|
|
|
-- Create SplashNetworkGroupMembers table
|
|
CREATE TABLE "SplashNetworkGroupMembers" (
|
|
"Id" serial PRIMARY KEY,
|
|
"NetworkGroupId" integer NOT NULL,
|
|
"NetworkId" integer NOT NULL,
|
|
"TenantId" integer NOT NULL,
|
|
"CreationTime" timestamp with time zone NOT NULL DEFAULT now(),
|
|
"CreatorUserId" bigint
|
|
);
|
|
|
|
-- Create indexes for SplashNetworkGroups
|
|
CREATE UNIQUE INDEX "IX_SplashNetworkGroups_Name_TenantId"
|
|
ON "SplashNetworkGroups" ("Name", "TenantId");
|
|
|
|
CREATE INDEX "IX_SplashNetworkGroups_TenantId"
|
|
ON "SplashNetworkGroups" ("TenantId");
|
|
|
|
CREATE INDEX "IX_SplashNetworkGroups_IsActive"
|
|
ON "SplashNetworkGroups" ("IsActive");
|
|
|
|
-- Create indexes for SplashNetworkGroupMembers
|
|
CREATE UNIQUE INDEX "IX_SplashNetworkGroupMembers_NetworkGroupId_NetworkId"
|
|
ON "SplashNetworkGroupMembers" ("NetworkGroupId", "NetworkId");
|
|
|
|
CREATE INDEX "IX_SplashNetworkGroupMembers_NetworkGroupId"
|
|
ON "SplashNetworkGroupMembers" ("NetworkGroupId");
|
|
|
|
CREATE INDEX "IX_SplashNetworkGroupMembers_NetworkId"
|
|
ON "SplashNetworkGroupMembers" ("NetworkId");
|
|
|
|
CREATE INDEX "IX_SplashNetworkGroupMembers_TenantId"
|
|
ON "SplashNetworkGroupMembers" ("TenantId");
|
|
|
|
-- Add foreign key constraints
|
|
ALTER TABLE "SplashNetworkGroupMembers"
|
|
ADD CONSTRAINT "FK_SplashNetworkGroupMembers_SplashNetworkGroups_NetworkGroupId"
|
|
FOREIGN KEY ("NetworkGroupId") REFERENCES "SplashNetworkGroups" ("Id") ON DELETE CASCADE;
|
|
|
|
ALTER TABLE "SplashNetworkGroupMembers"
|
|
ADD CONSTRAINT "FK_SplashNetworkGroupMembers_SplashMerakiNetworks_NetworkId"
|
|
FOREIGN KEY ("NetworkId") REFERENCES "SplashMerakiNetworks" ("Id") ON DELETE CASCADE;
|
|
|
|
-- Add SelectedNetworkGroups column to SplashDashboards table
|
|
ALTER TABLE "SplashDashboards"
|
|
ADD COLUMN "SelectedNetworkGroups" integer[] DEFAULT '{}';
|
|
|
|
-- Create index for the new column
|
|
CREATE INDEX "IX_SplashDashboards_SelectedNetworkGroups"
|
|
ON "SplashDashboards" USING GIN ("SelectedNetworkGroups");
|
|
|
|
-- Insert migration history record (if using EF Core migrations table)
|
|
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
|
|
VALUES ('20250106000000_AddNetworkGroups', '8.0.0');
|
|
|
|
COMMIT;
|
|
|
|
Notas importantes sobre el script:
|
|
|
|
1. Tipos de datos PostgreSQL: Uso serial para auto-incremento y timestamp with time zone para fechas
|
|
2. Arrays: PostgreSQL maneja arrays nativamente, por eso SelectedNetworkGroups es integer[]
|
|
3. Índices GIN: Para el array SelectedNetworkGroups uso un índice GIN que es óptimo para arrays
|
|
4. Cascada: Las foreign keys tienen ON DELETE CASCADE para mantener integridad
|
|
5. Valores por defecto: Arrays vacíos se inicializan con '{}'
|
|
|
|
Para ejecutar este script:
|
|
|
|
# Si usas psql directamente:
|
|
psql -h localhost -U your_user -d your_database -f migration_script.sql
|
|
|
|
# O copiarlo y pegarlo en tu cliente de PostgreSQL preferido
|
|
|
|
Verificación después de ejecutar:
|
|
|
|
-- Verificar que las tablas se crearon correctamente
|
|
SELECT table_name FROM information_schema.tables
|
|
WHERE table_name IN ('SplashNetworkGroups', 'SplashNetworkGroupMembers');
|
|
|
|
-- Verificar la nueva columna en SplashDashboards
|
|
SELECT column_name, data_type FROM information_schema.columns
|
|
WHERE table_name = 'SplashDashboards' AND column_name = 'SelectedNetworkGroups';
|
|
|
|
-- Verificar índices
|
|
SELECT indexname FROM pg_indexes
|
|
WHERE tablename IN ('SplashNetworkGroups', 'SplashNetworkGroupMembers'); |