# HG changeset patch
# User František Kučera <franta-hg@frantovo.cz>
# Date 1272545415 -7200
# Node ID 1c52b1a4a18ac93727337537ee51a2214da98e04
# Parent  607fbe95eddae8f55886fce626ce298e5ecf3019
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.

diff -r 607fbe95edda -r 1c52b1a4a18a java/nekurak.net-ejb/src/java/cz/frantovo/nekurak/dao/PodnikDAO.sql.xml
--- a/java/nekurak.net-ejb/src/java/cz/frantovo/nekurak/dao/PodnikDAO.sql.xml	Wed Apr 28 01:52:23 2010 +0200
+++ b/java/nekurak.net-ejb/src/java/cz/frantovo/nekurak/dao/PodnikDAO.sql.xml	Thu Apr 29 14:50:15 2010 +0200
@@ -10,18 +10,17 @@
 	<!--
 	    Osoby identifikujeme na základě IP adres.
 	    Každá osoba může hlasovat kolikrát chce.
-	    Ale do výsledků se počítá vždy jen její poslední hlas.
-	    TODO: možná upravit tak, aby se počítal jeden hlas od jedné osoby za každý den
-	    (bylo by možné efektivně hlasovat víckrát – každý den jednou).
+	    Ale do výsledků se počítá vždy jen její poslední hlas v daném dni
+	    (může efektivně hlasovat každý den jednou).
 	-->
 	SELECT	hlas,
 	    int4(count(*))
 	FROM (
-	    SELECT DISTINCT ON (ip_adresa)
+	    SELECT DISTINCT ON (ip_adresa, date(datum))
 		hlas
 	    FROM hlasovani
 	    WHERE podnik = :podnik
-	    ORDER BY ip_adresa, id DESC
+	    ORDER BY ip_adresa, date(datum), id DESC
 	) AS hlasy
 	GROUP BY hlas;
 	<!--
diff -r 607fbe95edda -r 1c52b1a4a18a sql/schéma.sql
--- a/sql/schéma.sql	Wed Apr 28 01:52:23 2010 +0200
+++ b/sql/schéma.sql	Thu Apr 29 14:50:15 2010 +0200
@@ -2,7 +2,7 @@
 -- PostgreSQL database dump
 --
 
--- Started on 2010-04-28 00:33:47 CEST
+-- Started on 2010-04-29 14:41:40 CEST
 
 SET statement_timeout = 0;
 SET client_encoding = 'UTF8';
@@ -36,7 +36,7 @@
 ALTER DOMAIN nekurak.email OWNER TO nekurak;
 
 --
--- TOC entry 1913 (class 0 OID 0)
+-- TOC entry 1914 (class 0 OID 0)
 -- Dependencies: 366
 -- Name: DOMAIN email; Type: COMMENT; Schema: nekurak; Owner: nekurak
 --
@@ -46,7 +46,7 @@
 
 --
 -- TOC entry 65 (class 1255 OID 35124)
--- Dependencies: 402 10
+-- Dependencies: 10 402
 -- Name: bezny_uzivatel(); Type: FUNCTION; Schema: nekurak; Owner: nekurak
 --
 
@@ -64,7 +64,7 @@
 ALTER FUNCTION nekurak.bezny_uzivatel() OWNER TO nekurak;
 
 --
--- TOC entry 1914 (class 0 OID 0)
+-- TOC entry 1915 (class 0 OID 0)
 -- Dependencies: 65
 -- Name: FUNCTION bezny_uzivatel(); Type: COMMENT; Schema: nekurak; Owner: nekurak
 --
@@ -107,7 +107,7 @@
 ALTER TABLE nekurak.clanek OWNER TO nekurak;
 
 --
--- TOC entry 1916 (class 0 OID 0)
+-- TOC entry 1917 (class 0 OID 0)
 -- Dependencies: 1600
 -- Name: TABLE clanek; Type: COMMENT; Schema: nekurak; Owner: nekurak
 --
@@ -148,7 +148,7 @@
 ALTER TABLE nekurak.fotka OWNER TO nekurak;
 
 --
--- TOC entry 1919 (class 0 OID 0)
+-- TOC entry 1920 (class 0 OID 0)
 -- Dependencies: 1599
 -- Name: TABLE fotka; Type: COMMENT; Schema: nekurak; Owner: nekurak
 --
@@ -157,7 +157,7 @@
 
 
 --
--- TOC entry 1920 (class 0 OID 0)
+-- TOC entry 1921 (class 0 OID 0)
 -- Dependencies: 1599
 -- Name: COLUMN fotka.poradi; Type: COMMENT; Schema: nekurak; Owner: nekurak
 --
@@ -190,7 +190,7 @@
     id integer DEFAULT nextval('hlasovani_seq'::regclass) NOT NULL,
     podnik integer NOT NULL,
     hlas boolean NOT NULL,
-    datum date DEFAULT now() NOT NULL,
+    datum timestamp without time zone DEFAULT now() NOT NULL,
     ip_adresa character varying(255) NOT NULL
 );
 
@@ -198,7 +198,7 @@
 ALTER TABLE nekurak.hlasovani OWNER TO nekurak;
 
 --
--- TOC entry 1923 (class 0 OID 0)
+-- TOC entry 1924 (class 0 OID 0)
 -- Dependencies: 1604
 -- Name: COLUMN hlasovani.id; Type: COMMENT; Schema: nekurak; Owner: nekurak
 --
@@ -209,7 +209,7 @@
 
 
 --
--- TOC entry 1924 (class 0 OID 0)
+-- TOC entry 1925 (class 0 OID 0)
 -- Dependencies: 1604
 -- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak
 --
@@ -258,7 +258,7 @@
 ALTER TABLE nekurak.podnik OWNER TO nekurak;
 
 --
--- TOC entry 1927 (class 0 OID 0)
+-- TOC entry 1928 (class 0 OID 0)
 -- Dependencies: 1593
 -- Name: COLUMN podnik.spravce; Type: COMMENT; Schema: nekurak; Owner: nekurak
 --
@@ -281,7 +281,7 @@
 ALTER TABLE nekurak.role OWNER TO nekurak;
 
 --
--- TOC entry 1929 (class 0 OID 0)
+-- TOC entry 1930 (class 0 OID 0)
 -- Dependencies: 1596
 -- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak
 --
@@ -324,7 +324,7 @@
 ALTER TABLE nekurak.uzivatel OWNER TO nekurak;
 
 --
--- TOC entry 1932 (class 0 OID 0)
+-- TOC entry 1933 (class 0 OID 0)
 -- Dependencies: 1595
 -- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak
 --
@@ -347,7 +347,7 @@
 ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak;
 
 --
--- TOC entry 1934 (class 0 OID 0)
+-- TOC entry 1935 (class 0 OID 0)
 -- Dependencies: 1597
 -- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak
 --
@@ -455,8 +455,17 @@
 
 
 --
--- TOC entry 1909 (class 2620 OID 35125)
--- Dependencies: 65 1595
+-- TOC entry 1904 (class 1259 OID 35309)
+-- Dependencies: 1604
+-- Name: hlsovani_datum_idx; Type: INDEX; Schema: nekurak; Owner: nekurak; Tablespace: 
+--
+
+CREATE INDEX hlsovani_datum_idx ON hlasovani USING btree (date(datum));
+
+
+--
+-- TOC entry 1910 (class 2620 OID 35125)
+-- Dependencies: 1595 65
 -- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak
 --
 
@@ -467,8 +476,8 @@
 
 
 --
--- TOC entry 1907 (class 2606 OID 35191)
--- Dependencies: 1599 1593 1885
+-- TOC entry 1908 (class 2606 OID 35191)
+-- Dependencies: 1599 1885 1593
 -- Name: fotka_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
 --
 
@@ -477,7 +486,7 @@
 
 
 --
--- TOC entry 1908 (class 2606 OID 35285)
+-- TOC entry 1909 (class 2606 OID 35285)
 -- Dependencies: 1885 1604 1593
 -- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
 --
@@ -487,8 +496,8 @@
 
 
 --
--- TOC entry 1904 (class 2606 OID 35178)
--- Dependencies: 1593 1889 1595
+-- TOC entry 1905 (class 2606 OID 35178)
+-- Dependencies: 1889 1595 1593
 -- Name: podnik_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
 --
 
@@ -497,8 +506,8 @@
 
 
 --
--- TOC entry 1905 (class 2606 OID 35138)
--- Dependencies: 1597 1891 1596
+-- TOC entry 1906 (class 2606 OID 35138)
+-- Dependencies: 1597 1596 1891
 -- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
 --
 
@@ -507,8 +516,8 @@
 
 
 --
--- TOC entry 1906 (class 2606 OID 35143)
--- Dependencies: 1889 1595 1597
+-- TOC entry 1907 (class 2606 OID 35143)
+-- Dependencies: 1597 1889 1595
 -- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
 --
 
@@ -517,7 +526,7 @@
 
 
 --
--- TOC entry 1912 (class 0 OID 0)
+-- TOC entry 1913 (class 0 OID 0)
 -- Dependencies: 10
 -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak
 --
@@ -529,7 +538,7 @@
 
 
 --
--- TOC entry 1915 (class 0 OID 0)
+-- TOC entry 1916 (class 0 OID 0)
 -- Dependencies: 1601
 -- Name: clanek_seq; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -541,7 +550,7 @@
 
 
 --
--- TOC entry 1917 (class 0 OID 0)
+-- TOC entry 1918 (class 0 OID 0)
 -- Dependencies: 1600
 -- Name: clanek; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -553,7 +562,7 @@
 
 
 --
--- TOC entry 1918 (class 0 OID 0)
+-- TOC entry 1919 (class 0 OID 0)
 -- Dependencies: 1598
 -- Name: fotka_seq; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -565,7 +574,7 @@
 
 
 --
--- TOC entry 1921 (class 0 OID 0)
+-- TOC entry 1922 (class 0 OID 0)
 -- Dependencies: 1599
 -- Name: fotka; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -577,7 +586,7 @@
 
 
 --
--- TOC entry 1922 (class 0 OID 0)
+-- TOC entry 1923 (class 0 OID 0)
 -- Dependencies: 1603
 -- Name: hlasovani_seq; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -589,7 +598,7 @@
 
 
 --
--- TOC entry 1925 (class 0 OID 0)
+-- TOC entry 1926 (class 0 OID 0)
 -- Dependencies: 1604
 -- Name: hlasovani; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -601,7 +610,7 @@
 
 
 --
--- TOC entry 1926 (class 0 OID 0)
+-- TOC entry 1927 (class 0 OID 0)
 -- Dependencies: 1592
 -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -613,7 +622,7 @@
 
 
 --
--- TOC entry 1928 (class 0 OID 0)
+-- TOC entry 1929 (class 0 OID 0)
 -- Dependencies: 1593
 -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -625,7 +634,7 @@
 
 
 --
--- TOC entry 1930 (class 0 OID 0)
+-- TOC entry 1931 (class 0 OID 0)
 -- Dependencies: 1596
 -- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -637,7 +646,7 @@
 
 
 --
--- TOC entry 1931 (class 0 OID 0)
+-- TOC entry 1932 (class 0 OID 0)
 -- Dependencies: 1594
 -- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -649,7 +658,7 @@
 
 
 --
--- TOC entry 1933 (class 0 OID 0)
+-- TOC entry 1934 (class 0 OID 0)
 -- Dependencies: 1595
 -- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -661,7 +670,7 @@
 
 
 --
--- TOC entry 1935 (class 0 OID 0)
+-- TOC entry 1936 (class 0 OID 0)
 -- Dependencies: 1597
 -- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak
 --
@@ -672,7 +681,7 @@
 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web;
 
 
--- Completed on 2010-04-28 00:33:49 CEST
+-- Completed on 2010-04-29 14:41:42 CEST
 
 --
 -- PostgreSQL database dump complete