Skip to content
Snippets Groups Projects

Feature/refacture l3 core services

Merged Feature/refacture l3 core services
feature/refacture-l3-core-services into develop
Merged Neda Moeini requested to merge feature/refacture-l3-core-services into develop
Viewing commit 34f5ea11
Show latest version
10 files
+ 391
− 204
Compare changes
  • Side-by-side
  • Inline
Files
10
gso/migrations/versions/2025-03-18_c9c9fdf624b5_re_model_ias.py
+ 318
− 131
"""Re-model L3CoreServices
"""Re-model L3CoreServices.
GEANT_IP = "GÉANT IP"
IMPORTED_GEANT_IP = "IMPORTED GÉANT IP"
@@ -10,87 +10,312 @@
Revision ID: e1afa3790f32
Revises: b96b0ecf6906
Create Date: 2025-03-17 10:23:45.917222
"""
import sqlalchemy as sa
from alembic import op
# revision identifiers, used by Alembic.
revision = 'e1afa3790f32'
down_revision = 'b96b0ecf6906'
# revision identifiers, used by Alembic
revision = "e1afa3790f32"
down_revision = "b96b0ecf6906"
branch_labels = None
depends_on = None
_L3_CORE_SERVICE_DICT = [
{
"product": {
"name": "IAS",
"type": "IAS",
},
"imported_product": {
"name": "Imported IAS",
"type": "ImportedIAS"
},
"product_block": {
"name": "IASBlock",
"description": "An Internet Access Service for general internet access",
"tag": "IAS",
},
},
{
"product": {
"name": "GÉANT IP",
"type": "GeantIP"
},
"imported_product": {
"name": "Imported GÉANT IP",
"type": "ImportedGeantIP"
},
"product_block": {
"name": "GeantIPBlock",
"description": "A GÉANT IP product block",
"tag": "G_IP",
},
},
{
"product": {
"name": "LHCOne",
"type": "LHCOne"
},
"imported_product": {
"name": "Imported LHCOne",
"type": "ImportedLHCOne"
},
"product_block": {
"name": "LHCOneBlock",
"description": "A LHCOne product block",
"tag": "LHC",
},
},
{
"product": {
"name": "Copernicus",
"type": "Copernicus"
},
"imported_product": {
"name": "Imported Copernicus",
"type": "ImportedCopernicus"
},
"product_block": {
"name": "CopernicusBlock",
"description": "A Copernicus product block",
"tag": "COP",
},
},
]
def upgrade() -> None:
"""Perform the upgrade for L3CoreServices remodeling."""
conn = op.get_bind()
conn.execute(
sa.text(
"""
DELETE FROM fixed_inputs
WHERE fixed_inputs.product_id IN (
SELECT products.product_id FROM products WHERE products.name IN ('IAS', 'Imported IAS')
)
AND fixed_inputs.name = 'l3_core_service_type'
"""
for l3 in _L3_CORE_SERVICE_DICT:
conn.execute(
sa.text(
f"""
DELETE FROM fixed_inputs
WHERE fixed_inputs.product_id IN (
SELECT products.product_id
FROM products
WHERE products.name IN (
'{l3["product"]["name"]}',
'{l3["imported_product"]["name"]}'
)
)
AND fixed_inputs.name = 'l3_core_service_type'
"""
)
)
)
conn.execute(
sa.text(
"""
INSERT INTO product_blocks (name, description, tag, status)
VALUES ('IASProductBlock', 'An Internet Access Service for general internet access', 'IAS', 'active')
RETURNING product_blocks.product_block_id
"""
conn.execute(
sa.text(
f"""
INSERT INTO product_blocks (name, description, tag, status)
VALUES (
'{l3["product_block"]["name"]}',
'{l3["product_block"]["description"]}',
'{l3["product_block"]["tag"]}',
'active'
)
RETURNING product_blocks.product_block_id
"""
)
)
)
conn.execute(
sa.text(
"""
INSERT INTO resource_types (resource_type, description)
VALUES ('ias_flavor', 'The flavor of the IAS service')
RETURNING resource_types.resource_type_id
"""
if l3["product"]["name"] == "IAS":
conn.execute(
sa.text(
"""
INSERT INTO resource_types (resource_type, description)
VALUES ('ias_flavor', 'The flavor of the IAS service')
RETURNING resource_types.resource_type_id
"""
)
)
conn.execute(
sa.text(
f"""
INSERT INTO product_block_relations
VALUES (
(
SELECT product_blocks.product_block_id
FROM product_blocks
WHERE name = '{l3["product_block"]["name"]}'
),
(
SELECT product_blocks.product_block_id
FROM product_blocks
WHERE name = 'L3CoreServiceBlock'
),
NULL,
NULL
);
"""
)
)
)
conn.execute(
sa.text(
"""
INSERT INTO product_block_relations
VALUES (
(SELECT product_blocks.product_block_id FROM product_blocks WHERE name = 'IASProductBlock'),
(SELECT product_blocks.product_block_id FROM product_blocks WHERE name = 'L3CoreServiceBlock'),
NULL,
NULL
);
"""
conn.execute(
sa.text(
f"""
UPDATE product_product_blocks
SET product_block_id = (
SELECT product_block_id
FROM product_blocks
WHERE name = '{l3["product_block"]["name"]}'
)
WHERE product_block_id = (
SELECT product_block_id
FROM product_blocks
WHERE name = 'L3CoreServiceBlock'
)
AND product_id IN (
SELECT product_id
FROM products
WHERE name IN (
'{l3["product"]["name"]}',
'{l3["imported_product"]["name"]}'
)
);
"""
)
)
)
conn.execute(
sa.text(
"""
UPDATE product_product_blocks
SET product_block_id = (
SELECT product_block_id FROM product_blocks WHERE name = 'IASProductBlock'
)
WHERE product_block_id = (
SELECT product_block_id FROM product_blocks WHERE name = 'L3CoreServiceBlock'
)
AND product_id IN (
SELECT product_id FROM products WHERE name IN ('IAS', 'Imported IAS')
);
"""
conn.execute(
sa.text(
f"""
UPDATE products
SET product_type = '{l3["product"]["type"]}'
WHERE product_type = 'L3CoreService'
AND name = '{l3["product"]["name"]}';
"""
)
)
)
conn.execute(
sa.text(
f"""
UPDATE products
SET product_type = '{l3["imported_product"]["type"]}'
WHERE product_type = 'ImportedL3CoreService'
AND name = '{l3["imported_product"]["name"]}';
"""
)
)
conn.execute(
sa.text(
f"""
-- Step 1: Insert new subscription_instances for '{l3["product_block"]["name"]}' and return their IDs
WITH inserted AS (
INSERT INTO subscription_instances (subscription_id, product_block_id)
SELECT
s.subscription_id,
(
SELECT product_block_id
FROM product_blocks
WHERE name = '{l3["product_block"]["name"]}'
) AS product_block_id
FROM subscriptions s
WHERE s.product_id = (
SELECT product_id
FROM products
WHERE products.name = '{l3["product"]["name"]}'
)
RETURNING subscription_instance_id, subscription_id
)
-- Step 2: Link newly inserted instances to the existing L3CoreServiceBlock instance
INSERT INTO subscription_instance_relations (
in_use_by_id,
depends_on_id,
order_id,
domain_model_attr
)
SELECT
i.subscription_instance_id AS in_use_by_id,
e.subscription_instance_id AS depends_on_id,
0 AS order_id,
'l3_core' AS domain_model_attr
FROM inserted i
JOIN subscription_instances e
ON e.subscription_id = i.subscription_id
AND e.product_block_id = (
SELECT product_block_id
FROM product_blocks
WHERE name = 'L3CoreServiceBlock'
);
"""
)
)
if l3["product"]["name"] == "IAS":
conn.execute(
sa.text(
"""
INSERT INTO product_block_resource_types (product_block_id, resource_type_id)
VALUES (
(
SELECT product_blocks.product_block_id
FROM product_blocks
WHERE product_blocks.name IN ('IASBlock')
),
(
SELECT resource_types.resource_type_id
FROM resource_types
WHERE resource_types.resource_type IN ('ias_flavor')
)
)
"""
)
)
conn.execute(
sa.text(
"""
WITH subscription_instance_ids AS (
SELECT subscription_instances.subscription_instance_id
FROM subscription_instances
WHERE subscription_instances.product_block_id IN (
SELECT product_blocks.product_block_id
FROM product_blocks
WHERE product_blocks.name = 'IASBlock'
)
)
INSERT INTO subscription_instance_values (
subscription_instance_id,
resource_type_id,
value
)
SELECT
subscription_instance_ids.subscription_instance_id,
resource_types.resource_type_id,
'IASPS Opt-OUT'
FROM resource_types
CROSS JOIN subscription_instance_ids
WHERE resource_types.resource_type = 'ias_flavor'
"""
)
)
conn.execute(
sa.text(
"""
UPDATE products
SET product_type = 'IAS'
WHERE product_type = 'L3CoreService'
AND name = 'IAS';
DELETE FROM processes
WHERE processes.pid IN (
SELECT processes_subscriptions.pid
FROM processes_subscriptions
WHERE processes_subscriptions.subscription_id IN (
SELECT subscriptions.subscription_id
FROM subscriptions
WHERE subscriptions.product_id IN (
SELECT products.product_id
FROM products
WHERE products.name IN ('Imported GWS', 'GWS')
)
)
)
"""
)
)
@@ -98,10 +323,16 @@ WHERE product_type = 'L3CoreService'
conn.execute(
sa.text(
"""
UPDATE products
SET product_type = 'ImportedIAS'
WHERE product_type = 'ImportedL3CoreService'
AND name = 'Imported IAS';
DELETE FROM processes_subscriptions
WHERE processes_subscriptions.subscription_id IN (
SELECT subscriptions.subscription_id
FROM subscriptions
WHERE subscriptions.product_id IN (
SELECT products.product_id
FROM products
WHERE products.name IN ('Imported GWS', 'GWS')
)
)
"""
)
)
@@ -109,57 +340,29 @@ WHERE product_type = 'ImportedL3CoreService'
conn.execute(
sa.text(
"""
-- Step 1: Insert new subscription_instances for 'IASProductBlock' and return their IDs
WITH inserted AS (
INSERT INTO subscription_instances (subscription_id, product_block_id)
SELECT
s.subscription_id,
(
SELECT product_block_id
FROM product_blocks
WHERE name = 'IASProductBlock'
) AS product_block_id
FROM subscriptions s
WHERE s.product_id = (
SELECT product_id
FROM products
WHERE products.name = 'IAS'
)
RETURNING subscription_instance_id, subscription_id
)
-- Step 2: Link newly inserted "IASProductBlock" instances to the existing "L3CoreServiceBlock" instance
INSERT INTO subscription_instance_relations (
in_use_by_id,
depends_on_id,
order_id,
domain_model_attr
)
SELECT
i.subscription_instance_id AS in_use_by_id,
e.subscription_instance_id AS depends_on_id,
0 AS order_id,
'l3_core' AS domain_model_attr
FROM inserted i
JOIN subscription_instances e
ON e.subscription_id = i.subscription_id
AND e.product_block_id = (
SELECT product_block_id
FROM product_blocks
WHERE name = 'L3CoreServiceBlock'
);
DELETE FROM subscription_instances
WHERE subscription_instances.subscription_id IN (
SELECT subscriptions.subscription_id
FROM subscriptions
WHERE subscriptions.product_id IN (
SELECT products.product_id
FROM products
WHERE products.name IN ('Imported GWS', 'GWS')
)
)
"""
)
)
conn.execute(
sa.text(
"""
INSERT INTO product_block_resource_types (product_block_id, resource_type_id)
VALUES (
(SELECT product_blocks.product_block_id FROM product_blocks WHERE product_blocks.name IN ('IASProductBlock')),
(SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('ias_flavor'))
)
DELETE FROM subscriptions
WHERE subscriptions.product_id IN (
SELECT products.product_id
FROM products
WHERE products.name IN ('Imported GWS', 'GWS')
)
"""
)
)
@@ -167,30 +370,14 @@ VALUES (
conn.execute(
sa.text(
"""
WITH subscription_instance_ids AS (
SELECT subscription_instances.subscription_instance_id
FROM subscription_instances
WHERE subscription_instances.product_block_id IN (
SELECT product_blocks.product_block_id
FROM product_blocks
WHERE product_blocks.name = 'IASProductBlock'
)
)
INSERT INTO subscription_instance_values (subscription_instance_id, resource_type_id, value)
SELECT
subscription_instance_ids.subscription_instance_id,
resource_types.resource_type_id,
'None'
-- TODO we need to check this with Simone to see what the default value should be, in case it is empty string, this wont be show up in the GUI
FROM resource_types
CROSS JOIN subscription_instance_ids
WHERE resource_types.resource_type = 'ias_flavor'
DELETE FROM products
WHERE products.name IN ('Imported GWS', 'GWS')
"""
)
)
def downgrade() -> None:
"""Perform the downgrade (no actions defined)."""
conn = op.get_bind()
# No downgrade logic provided.