index

Structure of The Data Objects

Base Item

Calendar Item Properties

• item_id -- унікальний ідентифікатор елементу -- 16 автоматично генерованих символів, що однозначно ідентифікують кожен запис журналу календарних подій
• calendar_id -- унікальний ідентифікатор окремого календаря (та/або облікового запису з ним пов'язаного)
• item_type_id -- тип елемента
• item_start -- час початку на стрічці часу календарної події, що відзначає цей конкретний елемент календаря
• item_finish -- час закінчення на стрічці часу календарної події, що відзначає цей конкретний елемент календаря
• busy -- флаг резервації часу "вільний або зайтятий"
• tags -- хмара тегів, що має відношення до цього елементу (json array)
• properties -- json dictionary
• calculated -- json dictionary

class CItem:
	AllowedProperties = {
		'Common': {
			'type': [
				'milestone',
				'task',
				'split',
				'event',
				'recurrent event',
				'appointment',
				'recurrent appointment',
				'note'
			],
			'freebusy': [True, False],
		},
		'Milestone': {
			'properties': {
				'date': 'mandatory',
				'description': 'optional'
			},
			'calculated': {
				'date': '',
				'actual date': '',
				'duration': 0
			}
		},
		'Task': {
			'properties': {
				'hidden': False,
				'start': 'mandatory', 
				'finish': 'mandatory',
				'early start': 'optional',
				'latest finish': 'optional',
				'description': 'optional',
				'assigments', 'optional',
				'priority', 'optional',
			},
			'calculated': {
			}
		},
		'Split': {
			'properties': {
			},
			'calculated': {
			}
		},
		'Event': {
			'properties': {
			},
			'calculated': {
			}
		},
		'Recurrent Event': {
			'properties': {
			},
			'calculated': {
			}
		},
		'Appointment': {
			'properties': {
			},
			'calculated': {
			}
		},
		'Recurrent Appointment': {
			'properties': {
			},
			'calculated': {
			}
		},
		'Note': {
			'properties': {
			},
			'calculated': {
			}
		},
	}
	
	def __init__(self):
		self.uid = ""
		self.tags = []
		self.properties = {}
		self.calculated = {}
		
	def __str__(self):
		return f"uid:{self.uid}\ntasg:{self.tags}\nproperites:{self.properties}\ncalculated:{self.calculated}"

class Milestone (CItem):
	def __init__(self, dt):
		self.uid = "MSxxxxxxxxx"
		self.tags = ['milestone']
		self.properties = {
			'type': 'milestone',
			'start': dt, 
			'finish': dt,
			'date': dt
		}
		self.calculated = {
			'duration': 0
		}

class Task (CItem):
	def __init__(self, name):
		self.uid = "TSxxxxxxxxxxx"
		self.tags = ['task']
		self.properties = {
			'type': 'task'
			'start': '',
			'finish': '',
			'description': '',
			'depends': {},
			'split': {},
			'}

12.2.Structure of The Data Base

-- DROP DATABASE IF EXISTS ubsdb;

CREATE DATABASE ubsdb
    WITH
    OWNER = ubs
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    LOCALE_PROVIDER = 'libc'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
    SCHEMA public
    VERSION "1.1";

dic_user

Справочник пользователей системы

Fields

Key	Name	Type	Not null	Comment
PK	user_id	uuid	Yes	Unique Universal ID of User
	user_login	varchar(255)	Yes	username (login)
	user_password	varchar(255)	Yes	password hash
	user_activation_link	jsonb		address and generated link for activation user account
	user_active	bool		are this user is active?
	user_avatar	bytea		BLOB with serialized avatar picture
	user_permissions	jsonb		serialization of permission of this user
	user_personal_settings	jsonb		serialization of personal value of settings

-- PostgreSQL 16.1
\connect "ubsdb";

-- DROP TABLE IF EXISTS public.dic_user;

-- Create dic_user table
CREATE TABLE IF NOT EXISTS public.dic_user 
(
    "user_id" uuid NOT NULL DEFAULT uuid_generate_v4(),
    "user_login" character varying(255) NOT NULL,
    "user_password" character varying(255) NOT NULL,
    "user_activation_links" jsonb,
    "user_activate" boolean DEFAULT false,
    "user_avatar" bytea,
    "user_permission" jsonb,
    "user_personal_settings" jsonb,
    CONSTRAINT dic_user_pkey PRIMARY KEY (user_id)
)

TABLESPACE pg_default;

-- Add comments to dic_user table
COMMENT ON TABLE public.dic_user IS 'The System Users dictionary';

-- Add comments to fields of dic_users
COMMENT ON COLUMN public.dic_user.user_id IS 'User UUID';
COMMENT ON COLUMN public.dic_user.user_login IS 'system username (login)';
COMMENT ON COLUMN public.dic_user.user_password IS 'password hash';
COMMENT ON COLUMN public.dic_user.user_activation_links 
IS 'json with address and generated link for activation of user account';
COMMENT ON COLUMN public.dic_user.user_activate IS 'is active?';
COMMENT ON COLUMN public.dic_user.user_avatar IS 'BLOB with serialized user''s avatar picture';
COMMENT ON COLUMN public.dic_user.user_permission IS 'json serialization of this user permission';
COMMENT ON COLUMN public.dic_user.user_personal_settings IS 'json serialization of personal value of settings';

user_password

{
  "email": "<activation email>",
  "link": "<generated activation link>",
}

or

{
  "phone": "<mobile phone number for activation>",
  "pin": "<generated activation pin code>"
}

Constraints

-- Constraint: dic_user_pkey

-- ALTER TABLE IF EXISTS public.dic_user DROP CONSTRAINT IF EXISTS dic_user_pkey;

ALTER TABLE IF EXISTS public.dic_user
    ADD CONSTRAINT dic_user_pkey PRIMARY KEY (user_id);

Initial Data

dic_calendar

Справочник календарей системы. Каждый календарь связан с пользователем. Календари могут быть разных типов, но основной тип Master Calendar

Fields

-- DROP TABLE IF EXISTS public.dic_calendar;

CREATE TABLE IF NOT EXISTS public.dic_calendar
(
    calendar_id uuid NOT NULL DEFAULT uuid_generate_v4(),
    user_id uuid NOT NULL,
    calendar_type_id smallint,
    calendar_properties jsonb,
    CONSTRAINT dic_calendar_pkey PRIMARY KEY (calendar_id),
    CONSTRAINT fk_dic_calendar_user_id FOREIGN KEY (user_id)
	    REFERENCES public.dic_user (user_id) MATCH SIMPLE
	    ON UPDATE NO ACTION
	    ON DELETE CASCADE
)

TABLESPACE pg_default;

-- Add comments to dic_calendar table
COMMENT ON TABLE public.dic_calendar IS 'The Calendars dictionary';

-- Add comments to dic_calendar table fields
COMMENT ON COLUMN public.dic_calendar.calendar_id IS 'Unique Universal ID of Calendar';
COMMENT ON COLUMN public.dic_calendar.user_id IS 'references => dic_user (user_id)';
COMMENT ON COLUMN public.dic_calendar.calendar_type_id IS 'references => dic_calendar_type (calendar_type_id)';
COMMENT ON COLUMN public.dic_calendar.calendar_properties IS 'JSON serialized properties of each calendars';

ALTER TABLE IF EXISTS public.dic_calendar
    OWNER to ubs;

Constraints

-- ALTER TABLE IF EXISTS public.dic_calendar DROP CONSTRAINT IF EXISTS fk_dic_calendar_user_id;

-- Add Foreign Key 
ALTER TABLE public.dic_calendar
	ADD CONSTRAINT dic_calendar_pkey PRIMARY KEY (calendar_id),
	ADD CONSTRAINT fk_dic_calendar_user_id FOREIGN KEY (user_id)
	REFERENCES public.dic_user(user_id) MATCH SIMPLE
	ON UPDATE NO ACTION
	ON DELETE CASCADE;

tbl_calendar_items

Fields

CREATE TABLE IF NOT EXISTS tbl_calendar_items (
	item_id uuid NOT NULL DEFAULT uuid_generate_v4(),
	calendar_id uuid NOT NULL,
	item_type_id smallint NOT NULL,
	item_start timestamptz NOT NULL,
	item_finish timestamptz NOT NULL,
	item_properties jsonb,
	item_calculated jsonb,
	CONSTRAINT tbl_calendar_item_id_pkey PRIMARY KEY (item_id),
	CONSTRAINT fk_tbl_calendar_item_calendar_id FOREIGN KEY (calendar_id)
		REFERENCES public.dic_calendar (calendar_id) MATCH SIMPLE
		ON UPDATE NO ACTION
		ON DELETE CASCADE
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.tbl_calendar_items
	OWNER to ubs;

dic_calendar_type

Справочник типов календарей

-- DROP TABLE IF EXISTS public.dic_calendar_type;

CREATE TABLE IF NOT EXISTS public.dic_calendar_type
(
    calendar_type_id integer NOT NULL DEFAULT nextval('dic_calendar_type_calendar_type_id_seq'::regclass),
    calendar_name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    calendar_properties jsonb,
    CONSTRAINT dic_calendar_type_pkey PRIMARY KEY (calendar_type_id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.dic_calendar_type
    OWNER to ubs;

calendar_item_view

-- DROP VIEW public.calendar_item_view;

CREATE OR REPLACE VIEW public.calendar_item_view
 AS
 SELECT tbl_calendar_items.item_id,
    tbl_calendar_items.item_type_id,
    tbl_calendar_items.item_start,
    tbl_calendar_items.item_finish,
    tbl_calendar_items.item_properties,
    dic_item_type.item_type_name,
    dic_item_type.item_properties AS item_type_properties,
    dic_calendar.calendar_id,
    dic_calendar.calendar_properties,
    dic_calendar_type.calendar_name,
    dic_calendar_type.calendar_properties AS calendar_type_properties,
    dic_user.user_id,
    dic_user.user_login,
    dic_user.user_permission,
    dic_user.user_personal_settings AS user_settings
   FROM tbl_calendar_items
     JOIN dic_item_type ON tbl_calendar_items.item_type_id = dic_item_type.item_type_id
     JOIN dic_calendar ON tbl_calendar_items.calendar_id = dic_calendar.calendar_id
     JOIN dic_calendar_type ON dic_calendar.calendar_type_id = dic_calendar_type.calendar_type_id
     JOIN dic_user ON dic_calendar.user_id = dic_user.user_id
  WHERE dic_user.user_activate = true;

ALTER TABLE public.calendar_item_view
    OWNER TO ubs;

Functions

get_calendar_items_recursive

-- Выбор всех корневых элементов для всех календарей
SELECT * FROM get_calendar_items_hierarchy(NULL, NULL);

-- Выбор всех элементов от конкретного root_item_id для всех календарей
SELECT * FROM get_calendar_items_hierarchy(NULL, 'конкретный_root_item_id');

-- Выбор всех корневых элементов для конкретного календаря
SELECT * FROM get_calendar_items_hierarchy('конкретный_calendar_id', NULL);

-- Выбор всех элементов начиная с конкретного root_item_id для конкретного календаря
SELECT * FROM get_calendar_items_hierarchy('конкретный_calendar_id', 'конкретный_root_item_id');

CREATE OR REPLACE FUNCTION get_calendar_items_recursive(input_calendar_id UUID DEFAULT NULL, root_item_id UUID DEFAULT NULL)
RETURNS TABLE(level SMALLINT, item_id UUID, item_name VARCHAR, calendar_id UUID, item_type_id SMALLINT, item_start TIMESTAMPTZ, item_finish TIMESTAMPTZ, item_properties JSONB, item_calculated JSONB, item_parent_id UUID, item_tags JSONB) AS $$
BEGIN
    RETURN QUERY 
    WITH RECURSIVE rec_calendar_item AS (
        -- Выбор начальных узлов иерархии
        SELECT 1 AS level, 
               t.item_id, t.item_name, t.calendar_id, 
               t.item_type_id, t.item_start, t.item_finish, 
               t.item_properties, t.item_calculated, 
               t.item_parent_id, t.item_tags
        FROM tbl_calendar_items t
        WHERE (t.item_parent_id IS NULL AND root_item_id IS NULL) -- элементы первого уровня если root_item_id не указан
              OR (t.item_id = root_item_id AND root_item_id IS NOT NULL) -- или конкретный узел если root_item_id указан
              AND (t.calendar_id = input_calendar_id OR input_calendar_id IS NULL)
        
        UNION ALL
        
        -- Рекурсивный запрос для получения подэлементов
        SELECT cih.level + 1,
               ci.item_id, ci.item_name, ci.calendar_id, 
               ci.item_type_id, ci.item_start, ci.item_finish, 
               ci.item_properties, ci.item_calculated, 
               ci.item_parent_id, ci.item_tags
        FROM tbl_calendar_items ci
        INNER JOIN rec_calendar_item cih ON ci.item_parent_id = cih.item_id
        WHERE (ci.calendar_id = input_calendar_id OR input_calendar_id IS NULL)
    )
    SELECT * FROM rec_calendar_item
	ORDER BY item_start;
END;
$$ LANGUAGE plpgsql;