-- -- PostgreSQL database dump -- -- -- Name: camera_types; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE camera_types ( id integer NOT NULL, name character varying(128) NOT NULL, class character varying(128) NOT NULL, default_data text NOT NULL ); -- -- Name: camera_types_id_seq; Type: SEQUENCE; Schema: public; Owner: dev -- CREATE SEQUENCE camera_types_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: camera_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: dev -- ALTER SEQUENCE camera_types_id_seq OWNED BY camera_types.id; -- -- Name: cameras; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE cameras ( id integer NOT NULL, name character varying(128) NOT NULL, description character varying(256) NOT NULL, host_id integer NOT NULL, camera_type_id integer NOT NULL, data text NOT NULL ); -- -- Name: cameras_id_seq; Type: SEQUENCE; Schema: public; Owner: dev -- CREATE SEQUENCE cameras_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: cameras_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: dev -- ALTER SEQUENCE cameras_id_seq OWNED BY cameras.id; -- -- Name: command_types; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE command_types ( id integer NOT NULL, name character varying(128) NOT NULL, class character varying(128) NOT NULL, default_data text NOT NULL ); -- -- Name: command_types_id_seq; Type: SEQUENCE; Schema: public; Owner: dev -- CREATE SEQUENCE command_types_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: command_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: dev -- ALTER SEQUENCE command_types_id_seq OWNED BY command_types.id; -- -- Name: commands; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE commands ( id integer NOT NULL, name character varying(128) NOT NULL, description character varying(256) NOT NULL, host_id integer NOT NULL, command_type_id integer NOT NULL, data text NOT NULL ); -- -- Name: commands_id_seq; Type: SEQUENCE; Schema: public; Owner: dev -- CREATE SEQUENCE commands_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: commands_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: dev -- ALTER SEQUENCE commands_id_seq OWNED BY commands.id; -- -- Name: hosts; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE hosts ( id integer NOT NULL, name character varying(128) NOT NULL, url character varying(256) NOT NULL, token character varying(128) NOT NULL ); -- -- Name: hosts_id_seq; Type: SEQUENCE; Schema: public; Owner: dev -- CREATE SEQUENCE hosts_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: hosts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: dev -- ALTER SEQUENCE hosts_id_seq OWNED BY hosts.id; -- -- Name: luticate_groups_id_seq; Type: SEQUENCE; Schema: public; Owner: dev -- CREATE SEQUENCE luticate_groups_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: luticate_groups; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE luticate_groups ( id integer DEFAULT nextval('luticate_groups_id_seq'::regclass) NOT NULL, name character varying NOT NULL ); -- -- Name: luticate_permissions; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE luticate_permissions ( name character varying(128) NOT NULL, value boolean NOT NULL ); -- -- Name: luticate_permissions_groups; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE luticate_permissions_groups ( name character varying(128) NOT NULL, value boolean NOT NULL, group_id integer NOT NULL ); -- -- Name: luticate_permissions_users; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE luticate_permissions_users ( name character varying(128) NOT NULL, value boolean, user_id integer NOT NULL ); -- -- Name: luticate_users_id_seq; Type: SEQUENCE; Schema: public; Owner: dev -- CREATE SEQUENCE luticate_users_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: luticate_users; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE luticate_users ( id integer DEFAULT nextval('luticate_users_id_seq'::regclass) NOT NULL, username character varying(255) NOT NULL, email character varying(255) NOT NULL, password character varying(255) NOT NULL, salt character varying(10) NOT NULL, profile_id integer, firstname character varying(255) NOT NULL, lastname character varying(255) NOT NULL ); -- -- Name: luticate_users_groups; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE luticate_users_groups ( user_id integer NOT NULL, group_id integer NOT NULL ); -- -- Name: sensor_types; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE sensor_types ( id integer NOT NULL, name character varying(128) NOT NULL, class character varying(128) NOT NULL, default_data text NOT NULL ); -- -- Name: sensor_types_id_seq; Type: SEQUENCE; Schema: public; Owner: dev -- CREATE SEQUENCE sensor_types_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: sensor_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: dev -- ALTER SEQUENCE sensor_types_id_seq OWNED BY sensor_types.id; -- -- Name: sensors; Type: TABLE; Schema: public; Owner: dev; Tablespace: -- CREATE TABLE sensors ( id integer NOT NULL, name character varying(128) NOT NULL, description character varying(256) NOT NULL, host_id integer NOT NULL, sensor_type_id integer NOT NULL, data text NOT NULL ); -- -- Name: sensors_id_seq; Type: SEQUENCE; Schema: public; Owner: dev -- CREATE SEQUENCE sensors_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- Name: sensors_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: dev -- ALTER SEQUENCE sensors_id_seq OWNED BY sensors.id; -- -- Name: id; Type: DEFAULT; Schema: public; Owner: dev -- ALTER TABLE ONLY camera_types ALTER COLUMN id SET DEFAULT nextval('camera_types_id_seq'::regclass); -- -- Name: id; Type: DEFAULT; Schema: public; Owner: dev -- ALTER TABLE ONLY cameras ALTER COLUMN id SET DEFAULT nextval('cameras_id_seq'::regclass); -- -- Name: id; Type: DEFAULT; Schema: public; Owner: dev -- ALTER TABLE ONLY command_types ALTER COLUMN id SET DEFAULT nextval('command_types_id_seq'::regclass); -- -- Name: id; Type: DEFAULT; Schema: public; Owner: dev -- ALTER TABLE ONLY commands ALTER COLUMN id SET DEFAULT nextval('commands_id_seq'::regclass); -- -- Name: id; Type: DEFAULT; Schema: public; Owner: dev -- ALTER TABLE ONLY hosts ALTER COLUMN id SET DEFAULT nextval('hosts_id_seq'::regclass); -- -- Name: id; Type: DEFAULT; Schema: public; Owner: dev -- ALTER TABLE ONLY sensor_types ALTER COLUMN id SET DEFAULT nextval('sensor_types_id_seq'::regclass); -- -- Name: id; Type: DEFAULT; Schema: public; Owner: dev -- ALTER TABLE ONLY sensors ALTER COLUMN id SET DEFAULT nextval('sensors_id_seq'::regclass); -- -- Name: camera_types_name_key; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY camera_types ADD CONSTRAINT camera_types_name_key UNIQUE (name); -- -- Name: camera_types_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY camera_types ADD CONSTRAINT camera_types_pkey PRIMARY KEY (id); -- -- Name: cameras_name_key; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY cameras ADD CONSTRAINT cameras_name_key UNIQUE (name); -- -- Name: cameras_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY cameras ADD CONSTRAINT cameras_pkey PRIMARY KEY (id); -- -- Name: command_types_name_key; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY command_types ADD CONSTRAINT command_types_name_key UNIQUE (name); -- -- Name: command_types_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY command_types ADD CONSTRAINT command_types_pkey PRIMARY KEY (id); -- -- Name: commands_name_key; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY commands ADD CONSTRAINT commands_name_key UNIQUE (name); -- -- Name: commands_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY commands ADD CONSTRAINT commands_pkey PRIMARY KEY (id); -- -- Name: hosts_name_key; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY hosts ADD CONSTRAINT hosts_name_key UNIQUE (name); -- -- Name: hosts_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY hosts ADD CONSTRAINT hosts_pkey PRIMARY KEY (id); -- -- Name: luticate_groups_name_key; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY luticate_groups ADD CONSTRAINT luticate_groups_name_key UNIQUE (name); -- -- Name: luticate_groups_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY luticate_groups ADD CONSTRAINT luticate_groups_pkey PRIMARY KEY (id); -- -- Name: luticate_permissions_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY luticate_permissions ADD CONSTRAINT luticate_permissions_pkey PRIMARY KEY (name); -- -- Name: luticate_permissions_users_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY luticate_permissions_users ADD CONSTRAINT luticate_permissions_users_pkey PRIMARY KEY (name, user_id); -- -- Name: luticate_users_email_key; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY luticate_users ADD CONSTRAINT luticate_users_email_key UNIQUE (email); -- -- Name: luticate_users_groups_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY luticate_users_groups ADD CONSTRAINT luticate_users_groups_pkey PRIMARY KEY (user_id, group_id); -- -- Name: luticate_users_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY luticate_users ADD CONSTRAINT luticate_users_pkey PRIMARY KEY (id); -- -- Name: luticate_users_username_key; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY luticate_users ADD CONSTRAINT luticate_users_username_key UNIQUE (username); -- -- Name: pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY luticate_permissions_groups ADD CONSTRAINT pkey PRIMARY KEY (name, group_id); -- -- Name: sensor_types_name_key; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY sensor_types ADD CONSTRAINT sensor_types_name_key UNIQUE (name); -- -- Name: sensor_types_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY sensor_types ADD CONSTRAINT sensor_types_pkey PRIMARY KEY (id); -- -- Name: sensors_name_key; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY sensors ADD CONSTRAINT sensors_name_key UNIQUE (name); -- -- Name: sensors_pkey; Type: CONSTRAINT; Schema: public; Owner: dev; Tablespace: -- ALTER TABLE ONLY sensors ADD CONSTRAINT sensors_pkey PRIMARY KEY (id); -- -- Name: cameras_camera_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY cameras ADD CONSTRAINT cameras_camera_type_id_fkey FOREIGN KEY (camera_type_id) REFERENCES camera_types(id); -- -- Name: cameras_host_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY cameras ADD CONSTRAINT cameras_host_id_fkey FOREIGN KEY (host_id) REFERENCES hosts(id) ON DELETE CASCADE; -- -- Name: commands_command_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY commands ADD CONSTRAINT commands_command_type_id_fkey FOREIGN KEY (command_type_id) REFERENCES command_types(id) ON DELETE CASCADE; -- -- Name: commands_host_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY commands ADD CONSTRAINT commands_host_id_fkey FOREIGN KEY (host_id) REFERENCES hosts(id) ON DELETE CASCADE; -- -- Name: foreign_group; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY luticate_permissions_groups ADD CONSTRAINT foreign_group FOREIGN KEY (group_id) REFERENCES luticate_groups(id) ON DELETE CASCADE; -- -- Name: foreign_group; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY luticate_users_groups ADD CONSTRAINT foreign_group FOREIGN KEY (group_id) REFERENCES luticate_groups(id) ON DELETE CASCADE; -- -- Name: foreign_name; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY luticate_permissions_groups ADD CONSTRAINT foreign_name FOREIGN KEY (name) REFERENCES luticate_permissions(name) ON DELETE CASCADE; -- -- Name: foreign_name; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY luticate_permissions_users ADD CONSTRAINT foreign_name FOREIGN KEY (name) REFERENCES luticate_permissions(name) ON DELETE CASCADE; -- -- Name: foreign_user; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY luticate_permissions_users ADD CONSTRAINT foreign_user FOREIGN KEY (user_id) REFERENCES luticate_users(id) ON DELETE CASCADE; -- -- Name: foreign_user; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY luticate_users_groups ADD CONSTRAINT foreign_user FOREIGN KEY (user_id) REFERENCES luticate_users(id) ON DELETE CASCADE; -- -- Name: sensors_host_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY sensors ADD CONSTRAINT sensors_host_id_fkey FOREIGN KEY (host_id) REFERENCES hosts(id) ON DELETE CASCADE; -- -- Name: sensors_sensor_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dev -- ALTER TABLE ONLY sensors ADD CONSTRAINT sensors_sensor_type_id_fkey FOREIGN KEY (sensor_type_id) REFERENCES sensor_types(id) ON DELETE CASCADE; -- -- Name: sp_lu_get_user_permission(integer, character varying); Type: FUNCTION; Schema: public; Owner: dev -- CREATE FUNCTION sp_lu_get_user_permission(_user_id integer, _permisson_name character varying, OUT val boolean) RETURNS boolean LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER COST 10 AS $$ DECLARE groups int[]; g INT; BEGIN SELECT "value" INTO val FROM luticate_permissions_users WHERE "user_id" = _user_id AND "name" = _permisson_name; IF val is null THEN SELECT lpg."value" into val FROM luticate_permissions_groups lpg INNER JOIN luticate_groups lg ON lg."id" = lpg."group_id" INNER JOIN luticate_users_groups lug ON lug."user_id" = _user_id AND lug."group_id" = lg."id" WHERE lpg."name" = _permisson_name and lpg."value" = FALSE; IF val is null then SELECT lpg."value" into val FROM luticate_permissions_groups lpg INNER JOIN luticate_groups lg ON lg."id" = lpg."group_id" INNER JOIN luticate_users_groups lug ON lug."user_id" = _user_id AND lug."group_id" = lg."id" WHERE lpg."name" = _permisson_name and lpg."value" = TRUE; IF val is null then SELECT "value" INTO val FROM luticate_permissions WHERE "name" = _permisson_name; END IF; end if; END IF; END; $$; -- -- Name: sp_lu_get_all_user_permission(integer); Type: FUNCTION; Schema: public; Owner: dev -- CREATE FUNCTION sp_lu_get_all_user_permission(_user_id integer, OUT _permisson_name character varying, OUT val boolean) RETURNS SETOF record LANGUAGE sql IMMUTABLE SECURITY DEFINER COST 10 AS $$ SELECT p.name, sp_lu_get_user_permission(_user_id, p.name) FROM luticate_permissions p ORDER bY p.name $$; -- -- Name: public; Type: ACL; Schema: -; Owner: postgres -- -- -- PostgreSQL database dump complete --