Vous ne pouvez pas sélectionner plus de 25 sujets Les noms de sujets doivent commencer par une lettre ou un nombre, peuvent contenir des tirets ('-') et peuvent comporter jusqu'à 35 caractères.

struct_init.sql 13KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497
  1. --
  2. -- PostgreSQL database dump
  3. --
  4. -- Dumped from database version 9.4.7
  5. -- Dumped by pg_dump version 9.4.7
  6. -- Started on 2016-04-27 09:40:56 CEST
  7. SET statement_timeout = 0;
  8. SET lock_timeout = 0;
  9. SET client_encoding = 'UTF8';
  10. SET standard_conforming_strings = on;
  11. SET check_function_bodies = false;
  12. SET client_min_messages = warning;
  13. --
  14. -- TOC entry 1 (class 3079 OID 11893)
  15. -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
  16. --
  17. CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
  18. --
  19. -- TOC entry 2114 (class 0 OID 0)
  20. -- Dependencies: 1
  21. -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
  22. --
  23. COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
  24. SET search_path = public, pg_catalog;
  25. --
  26. -- TOC entry 201 (class 1255 OID 25843)
  27. -- Name: sp_get_images(boolean, integer); Type: FUNCTION; Schema: public; Owner: -
  28. --
  29. CREATE FUNCTION sp_get_images(_only_mine boolean, _user_id_me integer, OUT id integer, OUT name text, OUT user_id integer, OUT date timestamp without time zone) RETURNS SETOF record
  30. LANGUAGE sql IMMUTABLE SECURITY DEFINER COST 10
  31. AS $$
  32. SELECT i.id, i.name, i.user_id, i.date
  33. FROM images i
  34. WHERE (CASE WHEN _only_mine THEN i.user_id = _user_id_me ELSE TRUE END)
  35. ORDER BY i.date DESC
  36. $$;
  37. --
  38. -- TOC entry 198 (class 1255 OID 25773)
  39. -- Name: sp_lu_get_all_user_permission(integer); Type: FUNCTION; Schema: public; Owner: -
  40. --
  41. CREATE FUNCTION sp_lu_get_all_user_permission(_user_id integer, OUT _permisson_name character varying, OUT val boolean) RETURNS SETOF record
  42. LANGUAGE sql IMMUTABLE SECURITY DEFINER COST 10
  43. AS $$
  44. SELECT p.name, sp_lu_get_user_permission(_user_id, p.name) FROM luticate_permissions p ORDER bY p.name
  45. $$;
  46. --
  47. -- TOC entry 200 (class 1255 OID 25801)
  48. -- Name: sp_lu_get_all_user_settings(integer); Type: FUNCTION; Schema: public; Owner: -
  49. --
  50. CREATE FUNCTION sp_lu_get_all_user_settings(_user_id integer, OUT _settings json) RETURNS SETOF json
  51. LANGUAGE sql IMMUTABLE SECURITY DEFINER COST 10
  52. AS $$
  53. SELECT sp_lu_get_user_setting(_user_id, s.name)
  54. FROM luticate_settings s
  55. WHERE NOT s.is_admin
  56. ORDER BY s.name
  57. $$;
  58. --
  59. -- TOC entry 197 (class 1255 OID 25772)
  60. -- Name: sp_lu_get_user_permission(integer, character varying); Type: FUNCTION; Schema: public; Owner: -
  61. --
  62. CREATE FUNCTION sp_lu_get_user_permission(_user_id integer, _permisson_name character varying, OUT val boolean) RETURNS boolean
  63. LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER COST 10
  64. AS $$
  65. DECLARE
  66. groups int[];
  67. g INT;
  68. BEGIN
  69. SELECT "value" INTO val FROM luticate_permissions_users WHERE "user_id" = _user_id AND "name" = _permisson_name;
  70. IF val is null THEN
  71. SELECT lpg."value" into val FROM luticate_permissions_groups lpg
  72. INNER JOIN luticate_groups lg ON lg."id" = lpg."group_id"
  73. INNER JOIN luticate_users_groups lug ON lug."user_id" = _user_id AND lug."group_id" = lg."id"
  74. WHERE lpg."name" = _permisson_name and lpg."value" = FALSE;
  75. IF val is null then
  76. SELECT lpg."value" into val FROM luticate_permissions_groups lpg
  77. INNER JOIN luticate_groups lg ON lg."id" = lpg."group_id"
  78. INNER JOIN luticate_users_groups lug ON lug."user_id" = _user_id AND lug."group_id" = lg."id"
  79. WHERE lpg."name" = _permisson_name and lpg."value" = TRUE;
  80. IF val is null then
  81. SELECT "value" INTO val FROM luticate_permissions WHERE "name" = _permisson_name;
  82. END IF;
  83. end if;
  84. END IF;
  85. END;
  86. $$;
  87. --
  88. -- TOC entry 199 (class 1255 OID 25800)
  89. -- Name: sp_lu_get_user_setting(integer, character varying); Type: FUNCTION; Schema: public; Owner: -
  90. --
  91. CREATE FUNCTION sp_lu_get_user_setting(_user_id integer, _setting_name character varying, OUT setting json) RETURNS json
  92. LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER COST 10
  93. AS $$
  94. DECLARE
  95. _setting luticate_settings;
  96. _setting_user luticate_settings_users;
  97. BEGIN
  98. SELECT * INTO _setting FROM luticate_settings WHERE "name" = _setting_name AND NOT "is_admin";
  99. IF _setting IS NULL THEN
  100. setting := NULL;
  101. ELSE
  102. IF NOT _setting.is_blocked THEN
  103. SELECT * INTO _setting_user FROM luticate_settings_users WHERE "user_id" = _user_id AND "name" = _setting_name;
  104. IF _setting_user IS NOT NULL THEN
  105. _setting.value := _setting_user.value;
  106. END IF;
  107. END IF;
  108. setting := to_json(_setting);
  109. END IF;
  110. END;
  111. $$;
  112. SET default_tablespace = '';
  113. SET default_with_oids = false;
  114. --
  115. -- TOC entry 184 (class 1259 OID 25804)
  116. -- Name: images; Type: TABLE; Schema: public; Owner: -; Tablespace:
  117. --
  118. CREATE TABLE images (
  119. id integer NOT NULL,
  120. user_id integer NOT NULL,
  121. path text NOT NULL,
  122. date timestamp without time zone DEFAULT now() NOT NULL,
  123. name text NOT NULL
  124. );
  125. --
  126. -- TOC entry 183 (class 1259 OID 25802)
  127. -- Name: images_id_seq; Type: SEQUENCE; Schema: public; Owner: -
  128. --
  129. CREATE SEQUENCE images_id_seq
  130. START WITH 1
  131. INCREMENT BY 1
  132. NO MINVALUE
  133. NO MAXVALUE
  134. CACHE 1;
  135. --
  136. -- TOC entry 2115 (class 0 OID 0)
  137. -- Dependencies: 183
  138. -- Name: images_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
  139. --
  140. ALTER SEQUENCE images_id_seq OWNED BY images.id;
  141. --
  142. -- TOC entry 173 (class 1259 OID 25694)
  143. -- Name: luticate_groups_id_seq; Type: SEQUENCE; Schema: public; Owner: -
  144. --
  145. CREATE SEQUENCE luticate_groups_id_seq
  146. START WITH 1
  147. INCREMENT BY 1
  148. NO MINVALUE
  149. NO MAXVALUE
  150. CACHE 1;
  151. --
  152. -- TOC entry 174 (class 1259 OID 25696)
  153. -- Name: luticate_groups; Type: TABLE; Schema: public; Owner: -; Tablespace:
  154. --
  155. CREATE TABLE luticate_groups (
  156. id integer DEFAULT nextval('luticate_groups_id_seq'::regclass) NOT NULL,
  157. name character varying NOT NULL
  158. );
  159. --
  160. -- TOC entry 175 (class 1259 OID 25703)
  161. -- Name: luticate_permissions; Type: TABLE; Schema: public; Owner: -; Tablespace:
  162. --
  163. CREATE TABLE luticate_permissions (
  164. name character varying(128) NOT NULL,
  165. value boolean NOT NULL
  166. );
  167. --
  168. -- TOC entry 176 (class 1259 OID 25706)
  169. -- Name: luticate_permissions_groups; Type: TABLE; Schema: public; Owner: -; Tablespace:
  170. --
  171. CREATE TABLE luticate_permissions_groups (
  172. name character varying(128) NOT NULL,
  173. value boolean NOT NULL,
  174. group_id integer NOT NULL
  175. );
  176. --
  177. -- TOC entry 177 (class 1259 OID 25709)
  178. -- Name: luticate_permissions_users; Type: TABLE; Schema: public; Owner: -; Tablespace:
  179. --
  180. CREATE TABLE luticate_permissions_users (
  181. name character varying(128) NOT NULL,
  182. value boolean,
  183. user_id integer NOT NULL
  184. );
  185. --
  186. -- TOC entry 181 (class 1259 OID 25774)
  187. -- Name: luticate_settings; Type: TABLE; Schema: public; Owner: -; Tablespace:
  188. --
  189. CREATE TABLE luticate_settings (
  190. name character varying(128) NOT NULL,
  191. type character varying(128) NOT NULL,
  192. value text NOT NULL,
  193. is_blocked boolean NOT NULL,
  194. is_admin boolean NOT NULL
  195. );
  196. --
  197. -- TOC entry 182 (class 1259 OID 25780)
  198. -- Name: luticate_settings_users; Type: TABLE; Schema: public; Owner: -; Tablespace:
  199. --
  200. CREATE TABLE luticate_settings_users (
  201. name character varying(128) NOT NULL,
  202. value text NOT NULL,
  203. user_id integer NOT NULL,
  204. is_blocked boolean NOT NULL
  205. );
  206. --
  207. -- TOC entry 178 (class 1259 OID 25712)
  208. -- Name: luticate_users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
  209. --
  210. CREATE SEQUENCE luticate_users_id_seq
  211. START WITH 1
  212. INCREMENT BY 1
  213. NO MINVALUE
  214. NO MAXVALUE
  215. CACHE 1;
  216. --
  217. -- TOC entry 179 (class 1259 OID 25714)
  218. -- Name: luticate_users; Type: TABLE; Schema: public; Owner: -; Tablespace:
  219. --
  220. CREATE TABLE luticate_users (
  221. id integer DEFAULT nextval('luticate_users_id_seq'::regclass) NOT NULL,
  222. username character varying(255) NOT NULL,
  223. email character varying(255) NOT NULL,
  224. password character varying(255) NOT NULL,
  225. salt character varying(10) NOT NULL,
  226. profile_id integer,
  227. firstname character varying(255) NOT NULL,
  228. lastname character varying(255) NOT NULL
  229. );
  230. --
  231. -- TOC entry 180 (class 1259 OID 25721)
  232. -- Name: luticate_users_groups; Type: TABLE; Schema: public; Owner: -; Tablespace:
  233. --
  234. CREATE TABLE luticate_users_groups (
  235. user_id integer NOT NULL,
  236. group_id integer NOT NULL
  237. );
  238. --
  239. -- TOC entry 1965 (class 2604 OID 25807)
  240. -- Name: id; Type: DEFAULT; Schema: public; Owner: -
  241. --
  242. ALTER TABLE ONLY images ALTER COLUMN id SET DEFAULT nextval('images_id_seq'::regclass);
  243. --
  244. -- TOC entry 1990 (class 2606 OID 25812)
  245. -- Name: images_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  246. --
  247. ALTER TABLE ONLY images
  248. ADD CONSTRAINT images_pkey PRIMARY KEY (id);
  249. --
  250. -- TOC entry 1968 (class 2606 OID 25725)
  251. -- Name: luticate_groups_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  252. --
  253. ALTER TABLE ONLY luticate_groups
  254. ADD CONSTRAINT luticate_groups_name_key UNIQUE (name);
  255. --
  256. -- TOC entry 1970 (class 2606 OID 25727)
  257. -- Name: luticate_groups_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  258. --
  259. ALTER TABLE ONLY luticate_groups
  260. ADD CONSTRAINT luticate_groups_pkey PRIMARY KEY (id);
  261. --
  262. -- TOC entry 1972 (class 2606 OID 25729)
  263. -- Name: luticate_permissions_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  264. --
  265. ALTER TABLE ONLY luticate_permissions
  266. ADD CONSTRAINT luticate_permissions_pkey PRIMARY KEY (name);
  267. --
  268. -- TOC entry 1976 (class 2606 OID 25731)
  269. -- Name: luticate_permissions_users_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  270. --
  271. ALTER TABLE ONLY luticate_permissions_users
  272. ADD CONSTRAINT luticate_permissions_users_pkey PRIMARY KEY (name, user_id);
  273. --
  274. -- TOC entry 1986 (class 2606 OID 25787)
  275. -- Name: luticate_settings_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  276. --
  277. ALTER TABLE ONLY luticate_settings
  278. ADD CONSTRAINT luticate_settings_pkey PRIMARY KEY (name);
  279. --
  280. -- TOC entry 1988 (class 2606 OID 25789)
  281. -- Name: luticate_settings_users_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  282. --
  283. ALTER TABLE ONLY luticate_settings_users
  284. ADD CONSTRAINT luticate_settings_users_pkey PRIMARY KEY (name, user_id);
  285. --
  286. -- TOC entry 1978 (class 2606 OID 25733)
  287. -- Name: luticate_users_email_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  288. --
  289. ALTER TABLE ONLY luticate_users
  290. ADD CONSTRAINT luticate_users_email_key UNIQUE (email);
  291. --
  292. -- TOC entry 1984 (class 2606 OID 25735)
  293. -- Name: luticate_users_groups_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  294. --
  295. ALTER TABLE ONLY luticate_users_groups
  296. ADD CONSTRAINT luticate_users_groups_pkey PRIMARY KEY (user_id, group_id);
  297. --
  298. -- TOC entry 1980 (class 2606 OID 25737)
  299. -- Name: luticate_users_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  300. --
  301. ALTER TABLE ONLY luticate_users
  302. ADD CONSTRAINT luticate_users_pkey PRIMARY KEY (id);
  303. --
  304. -- TOC entry 1982 (class 2606 OID 25739)
  305. -- Name: luticate_users_username_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  306. --
  307. ALTER TABLE ONLY luticate_users
  308. ADD CONSTRAINT luticate_users_username_key UNIQUE (username);
  309. --
  310. -- TOC entry 1974 (class 2606 OID 25741)
  311. -- Name: pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
  312. --
  313. ALTER TABLE ONLY luticate_permissions_groups
  314. ADD CONSTRAINT pkey PRIMARY KEY (name, group_id);
  315. --
  316. -- TOC entry 1991 (class 2606 OID 25742)
  317. -- Name: foreign_group; Type: FK CONSTRAINT; Schema: public; Owner: -
  318. --
  319. ALTER TABLE ONLY luticate_permissions_groups
  320. ADD CONSTRAINT foreign_group FOREIGN KEY (group_id) REFERENCES luticate_groups(id) ON DELETE CASCADE;
  321. --
  322. -- TOC entry 1995 (class 2606 OID 25747)
  323. -- Name: foreign_group; Type: FK CONSTRAINT; Schema: public; Owner: -
  324. --
  325. ALTER TABLE ONLY luticate_users_groups
  326. ADD CONSTRAINT foreign_group FOREIGN KEY (group_id) REFERENCES luticate_groups(id) ON DELETE CASCADE;
  327. --
  328. -- TOC entry 1992 (class 2606 OID 25752)
  329. -- Name: foreign_name; Type: FK CONSTRAINT; Schema: public; Owner: -
  330. --
  331. ALTER TABLE ONLY luticate_permissions_groups
  332. ADD CONSTRAINT foreign_name FOREIGN KEY (name) REFERENCES luticate_permissions(name) ON DELETE CASCADE;
  333. --
  334. -- TOC entry 1993 (class 2606 OID 25757)
  335. -- Name: foreign_name; Type: FK CONSTRAINT; Schema: public; Owner: -
  336. --
  337. ALTER TABLE ONLY luticate_permissions_users
  338. ADD CONSTRAINT foreign_name FOREIGN KEY (name) REFERENCES luticate_permissions(name) ON DELETE CASCADE;
  339. --
  340. -- TOC entry 1994 (class 2606 OID 25762)
  341. -- Name: foreign_user; Type: FK CONSTRAINT; Schema: public; Owner: -
  342. --
  343. ALTER TABLE ONLY luticate_permissions_users
  344. ADD CONSTRAINT foreign_user FOREIGN KEY (user_id) REFERENCES luticate_users(id) ON DELETE CASCADE;
  345. --
  346. -- TOC entry 1996 (class 2606 OID 25767)
  347. -- Name: foreign_user; Type: FK CONSTRAINT; Schema: public; Owner: -
  348. --
  349. ALTER TABLE ONLY luticate_users_groups
  350. ADD CONSTRAINT foreign_user FOREIGN KEY (user_id) REFERENCES luticate_users(id) ON DELETE CASCADE;
  351. --
  352. -- TOC entry 1997 (class 2606 OID 25790)
  353. -- Name: luticate_settings_users_name_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
  354. --
  355. ALTER TABLE ONLY luticate_settings_users
  356. ADD CONSTRAINT luticate_settings_users_name_fkey FOREIGN KEY (name) REFERENCES luticate_settings(name) ON DELETE CASCADE;
  357. --
  358. -- TOC entry 1998 (class 2606 OID 25795)
  359. -- Name: luticate_settings_users_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
  360. --
  361. ALTER TABLE ONLY luticate_settings_users
  362. ADD CONSTRAINT luticate_settings_users_user_id_fkey FOREIGN KEY (user_id) REFERENCES luticate_users(id) ON DELETE CASCADE;
  363. -- Completed on 2016-04-27 09:40:56 CEST
  364. --
  365. -- PostgreSQL database dump complete
  366. --