1.1 --- a/java/nekurak.net-ejb/src/java/cz/frantovo/nekurak/dao/PodnikDAO.sql.xml Wed Apr 28 01:52:23 2010 +0200
1.2 +++ b/java/nekurak.net-ejb/src/java/cz/frantovo/nekurak/dao/PodnikDAO.sql.xml Thu Apr 29 14:50:15 2010 +0200
1.3 @@ -10,18 +10,17 @@
1.4 <!--
1.5 Osoby identifikujeme na základě IP adres.
1.6 Každá osoba může hlasovat kolikrát chce.
1.7 - Ale do výsledků se počítá vždy jen její poslední hlas.
1.8 - TODO: možná upravit tak, aby se počítal jeden hlas od jedné osoby za každý den
1.9 - (bylo by možné efektivně hlasovat víckrát – každý den jednou).
1.10 + Ale do výsledků se počítá vždy jen její poslední hlas v daném dni
1.11 + (může efektivně hlasovat každý den jednou).
1.12 -->
1.13 SELECT hlas,
1.14 int4(count(*))
1.15 FROM (
1.16 - SELECT DISTINCT ON (ip_adresa)
1.17 + SELECT DISTINCT ON (ip_adresa, date(datum))
1.18 hlas
1.19 FROM hlasovani
1.20 WHERE podnik = :podnik
1.21 - ORDER BY ip_adresa, id DESC
1.22 + ORDER BY ip_adresa, date(datum), id DESC
1.23 ) AS hlasy
1.24 GROUP BY hlas;
1.25 <!--
2.1 --- a/sql/schéma.sql Wed Apr 28 01:52:23 2010 +0200
2.2 +++ b/sql/schéma.sql Thu Apr 29 14:50:15 2010 +0200
2.3 @@ -2,7 +2,7 @@
2.4 -- PostgreSQL database dump
2.5 --
2.6
2.7 --- Started on 2010-04-28 00:33:47 CEST
2.8 +-- Started on 2010-04-29 14:41:40 CEST
2.9
2.10 SET statement_timeout = 0;
2.11 SET client_encoding = 'UTF8';
2.12 @@ -36,7 +36,7 @@
2.13 ALTER DOMAIN nekurak.email OWNER TO nekurak;
2.14
2.15 --
2.16 --- TOC entry 1913 (class 0 OID 0)
2.17 +-- TOC entry 1914 (class 0 OID 0)
2.18 -- Dependencies: 366
2.19 -- Name: DOMAIN email; Type: COMMENT; Schema: nekurak; Owner: nekurak
2.20 --
2.21 @@ -46,7 +46,7 @@
2.22
2.23 --
2.24 -- TOC entry 65 (class 1255 OID 35124)
2.25 --- Dependencies: 402 10
2.26 +-- Dependencies: 10 402
2.27 -- Name: bezny_uzivatel(); Type: FUNCTION; Schema: nekurak; Owner: nekurak
2.28 --
2.29
2.30 @@ -64,7 +64,7 @@
2.31 ALTER FUNCTION nekurak.bezny_uzivatel() OWNER TO nekurak;
2.32
2.33 --
2.34 --- TOC entry 1914 (class 0 OID 0)
2.35 +-- TOC entry 1915 (class 0 OID 0)
2.36 -- Dependencies: 65
2.37 -- Name: FUNCTION bezny_uzivatel(); Type: COMMENT; Schema: nekurak; Owner: nekurak
2.38 --
2.39 @@ -107,7 +107,7 @@
2.40 ALTER TABLE nekurak.clanek OWNER TO nekurak;
2.41
2.42 --
2.43 --- TOC entry 1916 (class 0 OID 0)
2.44 +-- TOC entry 1917 (class 0 OID 0)
2.45 -- Dependencies: 1600
2.46 -- Name: TABLE clanek; Type: COMMENT; Schema: nekurak; Owner: nekurak
2.47 --
2.48 @@ -148,7 +148,7 @@
2.49 ALTER TABLE nekurak.fotka OWNER TO nekurak;
2.50
2.51 --
2.52 --- TOC entry 1919 (class 0 OID 0)
2.53 +-- TOC entry 1920 (class 0 OID 0)
2.54 -- Dependencies: 1599
2.55 -- Name: TABLE fotka; Type: COMMENT; Schema: nekurak; Owner: nekurak
2.56 --
2.57 @@ -157,7 +157,7 @@
2.58
2.59
2.60 --
2.61 --- TOC entry 1920 (class 0 OID 0)
2.62 +-- TOC entry 1921 (class 0 OID 0)
2.63 -- Dependencies: 1599
2.64 -- Name: COLUMN fotka.poradi; Type: COMMENT; Schema: nekurak; Owner: nekurak
2.65 --
2.66 @@ -190,7 +190,7 @@
2.67 id integer DEFAULT nextval('hlasovani_seq'::regclass) NOT NULL,
2.68 podnik integer NOT NULL,
2.69 hlas boolean NOT NULL,
2.70 - datum date DEFAULT now() NOT NULL,
2.71 + datum timestamp without time zone DEFAULT now() NOT NULL,
2.72 ip_adresa character varying(255) NOT NULL
2.73 );
2.74
2.75 @@ -198,7 +198,7 @@
2.76 ALTER TABLE nekurak.hlasovani OWNER TO nekurak;
2.77
2.78 --
2.79 --- TOC entry 1923 (class 0 OID 0)
2.80 +-- TOC entry 1924 (class 0 OID 0)
2.81 -- Dependencies: 1604
2.82 -- Name: COLUMN hlasovani.id; Type: COMMENT; Schema: nekurak; Owner: nekurak
2.83 --
2.84 @@ -209,7 +209,7 @@
2.85
2.86
2.87 --
2.88 --- TOC entry 1924 (class 0 OID 0)
2.89 +-- TOC entry 1925 (class 0 OID 0)
2.90 -- Dependencies: 1604
2.91 -- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak
2.92 --
2.93 @@ -258,7 +258,7 @@
2.94 ALTER TABLE nekurak.podnik OWNER TO nekurak;
2.95
2.96 --
2.97 --- TOC entry 1927 (class 0 OID 0)
2.98 +-- TOC entry 1928 (class 0 OID 0)
2.99 -- Dependencies: 1593
2.100 -- Name: COLUMN podnik.spravce; Type: COMMENT; Schema: nekurak; Owner: nekurak
2.101 --
2.102 @@ -281,7 +281,7 @@
2.103 ALTER TABLE nekurak.role OWNER TO nekurak;
2.104
2.105 --
2.106 --- TOC entry 1929 (class 0 OID 0)
2.107 +-- TOC entry 1930 (class 0 OID 0)
2.108 -- Dependencies: 1596
2.109 -- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak
2.110 --
2.111 @@ -324,7 +324,7 @@
2.112 ALTER TABLE nekurak.uzivatel OWNER TO nekurak;
2.113
2.114 --
2.115 --- TOC entry 1932 (class 0 OID 0)
2.116 +-- TOC entry 1933 (class 0 OID 0)
2.117 -- Dependencies: 1595
2.118 -- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak
2.119 --
2.120 @@ -347,7 +347,7 @@
2.121 ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak;
2.122
2.123 --
2.124 --- TOC entry 1934 (class 0 OID 0)
2.125 +-- TOC entry 1935 (class 0 OID 0)
2.126 -- Dependencies: 1597
2.127 -- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak
2.128 --
2.129 @@ -455,8 +455,17 @@
2.130
2.131
2.132 --
2.133 --- TOC entry 1909 (class 2620 OID 35125)
2.134 --- Dependencies: 65 1595
2.135 +-- TOC entry 1904 (class 1259 OID 35309)
2.136 +-- Dependencies: 1604
2.137 +-- Name: hlsovani_datum_idx; Type: INDEX; Schema: nekurak; Owner: nekurak; Tablespace:
2.138 +--
2.139 +
2.140 +CREATE INDEX hlsovani_datum_idx ON hlasovani USING btree (date(datum));
2.141 +
2.142 +
2.143 +--
2.144 +-- TOC entry 1910 (class 2620 OID 35125)
2.145 +-- Dependencies: 1595 65
2.146 -- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak
2.147 --
2.148
2.149 @@ -467,8 +476,8 @@
2.150
2.151
2.152 --
2.153 --- TOC entry 1907 (class 2606 OID 35191)
2.154 --- Dependencies: 1599 1593 1885
2.155 +-- TOC entry 1908 (class 2606 OID 35191)
2.156 +-- Dependencies: 1599 1885 1593
2.157 -- Name: fotka_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
2.158 --
2.159
2.160 @@ -477,7 +486,7 @@
2.161
2.162
2.163 --
2.164 --- TOC entry 1908 (class 2606 OID 35285)
2.165 +-- TOC entry 1909 (class 2606 OID 35285)
2.166 -- Dependencies: 1885 1604 1593
2.167 -- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
2.168 --
2.169 @@ -487,8 +496,8 @@
2.170
2.171
2.172 --
2.173 --- TOC entry 1904 (class 2606 OID 35178)
2.174 --- Dependencies: 1593 1889 1595
2.175 +-- TOC entry 1905 (class 2606 OID 35178)
2.176 +-- Dependencies: 1889 1595 1593
2.177 -- Name: podnik_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
2.178 --
2.179
2.180 @@ -497,8 +506,8 @@
2.181
2.182
2.183 --
2.184 --- TOC entry 1905 (class 2606 OID 35138)
2.185 --- Dependencies: 1597 1891 1596
2.186 +-- TOC entry 1906 (class 2606 OID 35138)
2.187 +-- Dependencies: 1597 1596 1891
2.188 -- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
2.189 --
2.190
2.191 @@ -507,8 +516,8 @@
2.192
2.193
2.194 --
2.195 --- TOC entry 1906 (class 2606 OID 35143)
2.196 --- Dependencies: 1889 1595 1597
2.197 +-- TOC entry 1907 (class 2606 OID 35143)
2.198 +-- Dependencies: 1597 1889 1595
2.199 -- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
2.200 --
2.201
2.202 @@ -517,7 +526,7 @@
2.203
2.204
2.205 --
2.206 --- TOC entry 1912 (class 0 OID 0)
2.207 +-- TOC entry 1913 (class 0 OID 0)
2.208 -- Dependencies: 10
2.209 -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak
2.210 --
2.211 @@ -529,7 +538,7 @@
2.212
2.213
2.214 --
2.215 --- TOC entry 1915 (class 0 OID 0)
2.216 +-- TOC entry 1916 (class 0 OID 0)
2.217 -- Dependencies: 1601
2.218 -- Name: clanek_seq; Type: ACL; Schema: nekurak; Owner: nekurak
2.219 --
2.220 @@ -541,7 +550,7 @@
2.221
2.222
2.223 --
2.224 --- TOC entry 1917 (class 0 OID 0)
2.225 +-- TOC entry 1918 (class 0 OID 0)
2.226 -- Dependencies: 1600
2.227 -- Name: clanek; Type: ACL; Schema: nekurak; Owner: nekurak
2.228 --
2.229 @@ -553,7 +562,7 @@
2.230
2.231
2.232 --
2.233 --- TOC entry 1918 (class 0 OID 0)
2.234 +-- TOC entry 1919 (class 0 OID 0)
2.235 -- Dependencies: 1598
2.236 -- Name: fotka_seq; Type: ACL; Schema: nekurak; Owner: nekurak
2.237 --
2.238 @@ -565,7 +574,7 @@
2.239
2.240
2.241 --
2.242 --- TOC entry 1921 (class 0 OID 0)
2.243 +-- TOC entry 1922 (class 0 OID 0)
2.244 -- Dependencies: 1599
2.245 -- Name: fotka; Type: ACL; Schema: nekurak; Owner: nekurak
2.246 --
2.247 @@ -577,7 +586,7 @@
2.248
2.249
2.250 --
2.251 --- TOC entry 1922 (class 0 OID 0)
2.252 +-- TOC entry 1923 (class 0 OID 0)
2.253 -- Dependencies: 1603
2.254 -- Name: hlasovani_seq; Type: ACL; Schema: nekurak; Owner: nekurak
2.255 --
2.256 @@ -589,7 +598,7 @@
2.257
2.258
2.259 --
2.260 --- TOC entry 1925 (class 0 OID 0)
2.261 +-- TOC entry 1926 (class 0 OID 0)
2.262 -- Dependencies: 1604
2.263 -- Name: hlasovani; Type: ACL; Schema: nekurak; Owner: nekurak
2.264 --
2.265 @@ -601,7 +610,7 @@
2.266
2.267
2.268 --
2.269 --- TOC entry 1926 (class 0 OID 0)
2.270 +-- TOC entry 1927 (class 0 OID 0)
2.271 -- Dependencies: 1592
2.272 -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak
2.273 --
2.274 @@ -613,7 +622,7 @@
2.275
2.276
2.277 --
2.278 --- TOC entry 1928 (class 0 OID 0)
2.279 +-- TOC entry 1929 (class 0 OID 0)
2.280 -- Dependencies: 1593
2.281 -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak
2.282 --
2.283 @@ -625,7 +634,7 @@
2.284
2.285
2.286 --
2.287 --- TOC entry 1930 (class 0 OID 0)
2.288 +-- TOC entry 1931 (class 0 OID 0)
2.289 -- Dependencies: 1596
2.290 -- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak
2.291 --
2.292 @@ -637,7 +646,7 @@
2.293
2.294
2.295 --
2.296 --- TOC entry 1931 (class 0 OID 0)
2.297 +-- TOC entry 1932 (class 0 OID 0)
2.298 -- Dependencies: 1594
2.299 -- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak
2.300 --
2.301 @@ -649,7 +658,7 @@
2.302
2.303
2.304 --
2.305 --- TOC entry 1933 (class 0 OID 0)
2.306 +-- TOC entry 1934 (class 0 OID 0)
2.307 -- Dependencies: 1595
2.308 -- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak
2.309 --
2.310 @@ -661,7 +670,7 @@
2.311
2.312
2.313 --
2.314 --- TOC entry 1935 (class 0 OID 0)
2.315 +-- TOC entry 1936 (class 0 OID 0)
2.316 -- Dependencies: 1597
2.317 -- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak
2.318 --
2.319 @@ -672,7 +681,7 @@
2.320 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web;
2.321
2.322
2.323 --- Completed on 2010-04-28 00:33:49 CEST
2.324 +-- Completed on 2010-04-29 14:41:42 CEST
2.325
2.326 --
2.327 -- PostgreSQL database dump complete