Skip to Content

Changes for page Data Structures

Last modified by Kerem Yollu on 2025/03/23 11:28
From version 1.1
edited by Kerem Yollu
on 2025/03/21 15:17
Change comment: There is no comment for this version
To version 10.1
edited by Kerem Yollu
on 2025/03/22 14:48
Change comment: There is no comment for this version

Summary

Details

Page properties
Parent
... ... @@ -1,1 +1,1 @@
1 -Kynsight.Projects.KPM.Data Structure And Models.WebHome
1 +xwiki:Kynsight.Projects.KPM.Data Structures.Data Structure And Models.WebHome
Content
... ... @@ -5,21 +5,21 @@
5 5  
6 6  == Address ==
7 7  
8 -DROP TABLE IF EXISTS public.address;
8 +* {{code language="sql" layout="LINENUMBERS"}}DROP TABLE IF EXISTS public.address;
9 9  
10 10  CREATE TABLE public.address
11 11  (
12 12   uid SERIAL PRIMARY KEY,
13 - country CHAR(2) NOT NULL,              ~-~- ISO 3166-1 alpha-2 (e.g., 'CH')
14 - region VARCHAR(100),                   ~-~- State or canton
13 + country CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 (e.g., 'CH')
14 + region VARCHAR(100), -- State or canton
15 15   city VARCHAR(100) NOT NULL,
16 16   postcode VARCHAR(20) NOT NULL,
17 17   street VARCHAR(255) NOT NULL,
18 - number VARCHAR(20),          
19 - unit VARCHAR(50),                      ~-~- Apartment/Suite/Floor/etc.
20 - extra_address VARCHAR(255),            ~-~- Additional info
21 - lon DOUBLE PRECISION,                  ~-~- Longitude (optional)
22 - lat DOUBLE PRECISION                   ~-~- Latitude (optional)
18 + number VARCHAR(20),
19 + unit VARCHAR(50), -- Apartment/Suite/Floor/etc.
20 + extra_address VARCHAR(255), -- Additional info
21 + lon DOUBLE PRECISION, -- Longitude (optional)
22 + lat DOUBLE PRECISION -- Latitude (optional)
23 23  )
24 24  
25 25  TABLESPACE pg_default;
... ... @@ -28,4 +28,32 @@
28 28   OWNER to kpm_rw;
29 29  
30 30  COMMENT ON TABLE public.address
31 - IS 'addresses table, instead of making use of addresses for ever company, customer and co... Prefer to make addresses table and then assign it to a company or a user etc.';
31 + IS 'addresses table, instead of making use of addresses for ever company, customer and co... Prefer to make addresses table and then assign it to a company or a user etc.';{{/code}}
32 +
33 +== Common table having the same Cols. an pointing to no other table ==
34 +
35 +{{code language="sql"}}
36 +DO $$
37 +DECLARE
38 + tname TEXT;
39 +BEGIN
40 + FOREACH tname IN ARRAY ARRAY'company_legal_form','company_relation','company_status','company_type',
41 + 'task_status', 'task_type', 'task_category',
42 + 'user_role', 'user_type',
43 + 'project_type','project_areas', 'project_person_roles', 'project_company_role']
44 + LOOP
45 + EXECUTE format('
46 + CREATE TABLE IF NOT EXISTS public.%I (
47 + uid SERIAL PRIMARY KEY,
48 + name VARCHAR(100) NOT NULL,
49 + description VARCHAR(255),
50 + creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
51 + updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
52 + );
53 + ALTER TABLE public.%I OWNER TO kpm_rw;
54 + ', tname, tname);
55 + END LOOP;
56 +END$$;
57 +{{/code}}
58 +
59 +== ==