Hlasování: hlasovat je možné z jedné IP adresy každý den jednou
authorFrantišek Kučera <franta-hg@frantovo.cz>
Thu, 29 Apr 2010 14:50:15 +0200
changeset 1181c52b1a4a18a
parent 117 607fbe95edda
child 119 d91f600c4645
Hlasování: hlasovat je možné z jedné IP adresy každý den jednou
(dříve bylo možné hlasovat jen jednou celkově).
Přidán index na date(datum) a upraven SQL dotaz.
java/nekurak.net-ejb/src/java/cz/frantovo/nekurak/dao/PodnikDAO.sql.xml
sql/schéma.sql
     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