Blog by Alen Nikolić

MYSQL

Kreiraj bazu podataka:

CREATE database cmdb;

element_groups

Kreiraj tabelu element_groups>

CREATE TABLE element_groups ( 
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    sort_order INT DEFAULT 0
);

dodavanje vrednosti:

INSERT INTO element_groups (name, description, sort_order)
VALUES 
    ('DC Asset Manager', 'Upravljanje fizičkom opremom u data centru', 1),
    ('Workstation Manager', 'Upravljanje radnim stanicama i perifernim uređajima', 2),
    ('Virtual Asset Manager', 'Upravljanje virtuelnim mašinama i resursima', 3),
    ('Contacts Manager', 'Upravljanje zaposlenima, kontaktima i korisnicima', 4),
    ('CMDB Manager', 'Upravljanje konfiguracionim entitetima i logičkim vezama', 5);

Pretraga element_groups

SELECT * FROM element_groups
ORDER BY sort_order ASC;

element_types

Kreiraj tabelu element_types za smeštanje tipove asseta (server, softver, licenca...)

CREATE TABLE element_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    element_group_id INT,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (element_group_id) REFERENCES element_groups(id)
);

Dodavanje element tipova:

INSERT INTO element_types (name, element_group_id, sort_order)
VALUES
-- DC Asset Manager
('Rack', 1, 1),
('PDU', 1, 2),
('UPS', 1, 3),
('Server', 1, 4),
('Storage', 1, 5),
('Network Switch', 1, 6),
('KVM', 1, 7),

-- Workstation Manager
('Workstation', 2, 1),
('Laptop', 2, 2),
('Monitor', 2, 3),
('Docking Station', 2, 4),
('Printer', 2, 5),
('Phone', 2, 6),

-- Virtual Asset Manager
('Virtual Machine', 3, 1),
('Virtual Storage', 3, 2),
('Container', 3, 3),
('vSwitch', 3, 5),

-- Contacts Manager
('Employee', 4, 1),
('External User', 4, 2),
('Team', 4, 3),


-- CMDB Manager
('Application', 5, 1),
('Service', 5, 2),
('System', 5, 3),
('Database', 5, 4),
('Cluster', 5, 7);

Prikaz tipova po grupama i sortiranje

SELECT 
    eg.name AS group_name,
    et.name AS type_name,
    et.sort_order
FROM 
    element_types et
JOIN 
    element_groups eg ON et.element_group_id = eg.id
ORDER BY 
    eg.sort_order ASC,    -- prvo po grupi
    et.sort_order ASC;    -- zatim po tipu unutar grupe

attribute_definitions

Kraraj tabelu attribute_definitions - definicija atributa

CREATE TABLE attribute_definitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,                -- npr. "CPU Count", "RAM"
    code VARCHAR(100) NOT NULL UNIQUE,         -- interni ključ: "cpu_count", "location_id"
    data_type ENUM('string', 'int', 'float', 'bool', 'json_ref') NOT NULL,
    ref_table_name VARCHAR(255),               -- ako se koristi posebna tabela za lookup (npr. "locations")
    description TEXT,
    sort_order INT DEFAULT 0                   -- za ručno ili automatsko sortiranje
);

Popuniti tabelu sa:

INSERT INTO attribute_definitions (
    name, code, data_type, ref_table_name, description, sort_order
)
VALUES
-- OSNOVNI HARDVER
('CPU Count', 'cpu_count', 'int', NULL, 'Broj fizičkih CPU jezgara', 1),
('RAM (GB)', 'ram_gb', 'int', NULL, 'Ukupna količina RAM memorije u GB', 2),
('Disk Size (GB)', 'disk_size_gb', 'float', NULL, 'Kapacitet diska u gigabajtima', 3),
('Is Virtual', 'is_virtual', 'bool', NULL, 'Da li je element virtuelna instanca', 4),

-- MREŽA
('IP Address', 'ip_address', 'string', NULL, 'Staticka IP adresa uređaja', 5),
('MAC Address', 'mac_address', 'string', NULL, 'MAC adresa interfejsa', 6),

-- SOFTVER / SISTEM
('Operating System', 'os', 'string', NULL, 'Naziv i verzija operativnog sistema', 7),
('Hostname', 'hostname', 'string', NULL, 'Naziv mašine na mreži', 8),

-- ORGANIZACIONI PODACI
('Location', 'location_id', 'json_ref', 'locations', 'Referenca na lokaciju iz tabele locations', 9),
('Department', 'department', 'string', NULL, 'Organizaciona jedinica kojoj pripada element', 10),
('Responsible Person', 'responsible', 'string', NULL, 'Ime odgovorne osobe', 11),

-- KONFIGURACIONI PODACI
('Serial Number', 'serial_number', 'string', NULL, 'Serijski broj uređaja', 12),
('Warranty Expiration', 'warranty_until', 'string', NULL, 'Datum isteka garancije (ISO format)', 13),
('Custom Metadata', 'custom_metadata', 'json_ref', NULL, 'Dodatni konfiguracioni podaci u JSON formatu', 14);

elements

Kreraj tabelu elements:

CREATE TABLE elements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    element_type_id INT NOT NULL,
    attributes JSON,  -- ovde idu svi dinamički atributi
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (element_type_id) REFERENCES element_types(id)
);

element_type_attributes – koji atributi pripadaju kom tipu (ako želiš kontrolu)

CREATE TABLE element_type_attributes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    element_type_id INT NOT NULL,
    attribute_definition_id INT NOT NULL,
    required BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (element_type_id) REFERENCES element_types(id),
    FOREIGN KEY (attribute_definition_id) REFERENCES attribute_definitions(id),
    UNIQUE (element_type_id, attribute_definition_id)
);

Kreirati tabelu locations

CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_location_id INT,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (parent_location_id) REFERENCES locations(id)
);

ELEMENT RELATION

Kreiraj tabelu relation_types:

CREATE TABLE relation_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,    -- npr. "hosts", "depends_on"
    label VARCHAR(100) NOT NULL,         -- prikaz za korisnike
    description TEXT
);

INSERT INTO relation_types (code, label, description) VALUES
('located', 'Located In', 'Element is physically located in another (e.g. Server → Rack)'),
('hosted', 'Hosted On', 'Element is hosted on another (e.g. VM → Server)'),
('mounted_in', 'Mounted In', 'Element is physically mounted into another, such as a server mounted in a rack unit (e.g. Server → Rack)'),
('member_of', 'Member Of', 'Element is part of another (e.g. Server → Cluster)'),
('depends_on', 'Depends On', 'Element depends on another element (e.g. App → DB)'),
('installed_on', 'Installed On', 'Element is installed on another (e.g. Software → OS)'),
('part_of', 'Part Of', 'Element is a component or subset of another (e.g. Disk → Server)'),
('belongs_to', 'Belongs To', 'Element is hierarchically or organizationally part of another (e.g. Subproject → Project, Document → Parent Document)');

Kreraj tabelu element_relations :

CREATE TABLE element_relations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    source_element_id INT NOT NULL,
    target_element_id INT NOT NULL,
    relation_type_id INT NOT NULL,
    attributes JSON,                              -- dodatna metadata o vezi
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (source_element_id) REFERENCES elements(id) ON DELETE CASCADE,
    FOREIGN KEY (target_element_id) REFERENCES elements(id) ON DELETE CASCADE,
    FOREIGN KEY (relation_type_id) REFERENCES relation_types(id)
);

Pages