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 11.3
edited by Kerem Yollu
on 2025/03/22 14:59
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,24 @@
5 5  
6 6  == Address ==
7 7  
8 -DROP TABLE IF EXISTS public.address;
8 +{{code language="SQL"}}
9 +-- Table: public.address
9 9  
10 -CREATE TABLE public.address
11 +-- DROP TABLE IF EXISTS public.address;
12 +
13 +CREATE TABLE IF NOT EXISTS 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
15 - city VARCHAR(100) NOT NULL,
16 - postcode VARCHAR(20) NOT NULL,
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)
16 + country character(2) COLLATE pg_catalog."default" NOT NULL,
17 + region character varying(100) COLLATE pg_catalog."default",
18 + city character varying(100) COLLATE pg_catalog."default" NOT NULL,
19 + postcode character varying(20) COLLATE pg_catalog."default" NOT NULL,
20 + street character varying(255) COLLATE pg_catalog."default" NOT NULL,
21 + "number" character varying(20) COLLATE pg_catalog."default",
22 + unit character varying(50) COLLATE pg_catalog."default",
23 + extra_address character varying(255) COLLATE pg_catalog."default",
24 + lon double precision,
25 + lat double precision
23 23  )
24 24  
25 25  TABLESPACE pg_default;
... ... @@ -29,3 +29,59 @@
29 29  
30 30  COMMENT ON TABLE public.address
31 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.';
35 +
36 +COMMENT ON COLUMN public.address.uid
37 + IS 'Unique ID for each address';
38 +
39 +COMMENT ON COLUMN public.address.country
40 + IS 'ISO 3166-1 alpha-2 (e.g., ''CH'')';
41 +
42 +COMMENT ON COLUMN public.address.region
43 + IS 'State or canton';
44 +
45 +COMMENT ON COLUMN public.address.unit
46 + IS 'Apartment/Suite/Floor/etc.';
47 +
48 +COMMENT ON COLUMN public.address.extra_address
49 + IS 'Any additional info (optional)';
50 +
51 +COMMENT ON COLUMN public.address.lon
52 + IS 'Optional: for geolocation';
53 +
54 +COMMENT ON COLUMN public.address.lat
55 + IS 'Optional: for geolocation';
56 +{{/code}}
57 +
58 +== Common Stand alone tables having the same Cols. and pointing to no other table ==
59 +
60 +=== Company ===
61 +
62 +* company_legal_form
63 +* company_relation
64 +* company_stat
65 +
66 +{{code language="sql"}}
67 +DO $$
68 +DECLARE
69 + tname TEXT;
70 +BEGIN
71 + FOREACH tname IN ARRAY ARRAY'company_legal_form','company_relation','company_status','company_type',
72 + 'task_status', 'task_type', 'task_category',
73 + 'user_role', 'user_type',
74 + 'project_type','project_areas', 'project_person_roles', 'project_company_role']
75 + LOOP
76 + EXECUTE format('
77 + CREATE TABLE IF NOT EXISTS public.%I (
78 + uid SERIAL PRIMARY KEY,
79 + name VARCHAR(100) NOT NULL,
80 + description VARCHAR(255),
81 + creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
82 + updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
83 + );
84 + ALTER TABLE public.%I OWNER TO kpm_rw;
85 + ', tname, tname);
86 + END LOOP;
87 +END$$;
88 +{{/code}}
89 +
90 +== ==