1.1 --- a/java/sql-vyuka/src/java/cz/frantovo/sql/vyuka/dao/TipyDAO.java	Sun May 31 18:49:00 2009 +0200
     1.2 +++ b/java/sql-vyuka/src/java/cz/frantovo/sql/vyuka/dao/TipyDAO.java	Sun May 31 22:36:58 2009 +0200
     1.3 @@ -1,13 +1,43 @@
     1.4  package cz.frantovo.sql.vyuka.dao;
     1.5  
     1.6 +import java.sql.Connection;
     1.7 +import java.sql.PreparedStatement;
     1.8 +import java.sql.ResultSet;
     1.9 +import java.util.logging.Level;
    1.10 +
    1.11  /**
    1.12 - *
    1.13 + * Rady, tipy, odkazy, citáty… pro zobrazování uživatelům.
    1.14   * @author fiki
    1.15   */
    1.16  public class TipyDAO extends VyukaSuperDAO {
    1.17  
    1.18 +    private enum SQL {
    1.19 +
    1.20 +        NAHODNY_TIP
    1.21 +    }
    1.22 +
    1.23 +    /**
    1.24 +     * @return Náhodný tip načtený z databáze
    1.25 +     */
    1.26      public String getTip() {
    1.27 -        /** TODO: Tipy brát z databáze. */
    1.28 -        return "Stejně vám něco <a href=\"http://frantovo.cz/blog/\">doporučíme</a>.";
    1.29 +        Connection db = getSpojeni(DATABAZE.APLIKACE);
    1.30 +        if (db == null) {
    1.31 +            log.log(Level.SEVERE, "Databáze je null.");
    1.32 +            return null;
    1.33 +        } else {
    1.34 +            PreparedStatement ps = null;
    1.35 +            ResultSet rs = null;
    1.36 +            try {
    1.37 +                ps = db.prepareStatement(getSQL(SQL.NAHODNY_TIP));
    1.38 +                rs = ps.executeQuery();
    1.39 +                rs.next();
    1.40 +                return rs.getString("text");
    1.41 +            } catch (Exception e) {
    1.42 +                log.log(Level.SEVERE, "Chyba při hledání náhodného tipu.", e);
    1.43 +                return null;
    1.44 +            } finally {
    1.45 +                zavri(db, ps, rs);
    1.46 +            }
    1.47 +        }
    1.48      }
    1.49  }

     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/java/sql-vyuka/src/java/cz/frantovo/sql/vyuka/dao/TipyDAO.sql.xml	Sun May 31 22:36:58 2009 +0200
     2.3 @@ -0,0 +1,12 @@
     2.4 +<?xml version="1.0" encoding="UTF-8"?>
     2.5 +<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
     2.6 +<properties>
     2.7 +    <entry key="NAHODNY_TIP">
     2.8 +        <![CDATA[
     2.9 +        SELECT  *
    2.10 +        FROM tip
    2.11 +        ORDER BY random()
    2.12 +        LIMIT 1;
    2.13 +        ]]>
    2.14 +    </entry>
    2.15 +</properties>
    2.16 \ No newline at end of file

     3.1 --- a/java/sql-vyuka/web/ajax.jspx	Sun May 31 18:49:00 2009 +0200
     3.2 +++ b/java/sql-vyuka/web/ajax.jspx	Sun May 31 22:36:58 2009 +0200
     3.3 @@ -14,8 +14,9 @@
     3.4      </jsp:scriptlet>
     3.5  
     3.6      <c:choose>
     3.7 -        <!-- Akce: Historie a vykonání SQL příkazu -->
     3.8 +        <!-- <Akce: Historie a vykonání SQL příkazu> -->
     3.9          <c:when test="${param.akce == 'historie' || param.akce == 'vykonat'}">
    3.10 +            <!-- <Načteme výsledek z beany>  -->
    3.11              <c:choose>
    3.12                  <c:when test="${param.akce == 'historie'}">
    3.13                      <c:set var="vysledek" value="${ajax.historie}" scope="request"/>
    3.14 @@ -25,8 +26,9 @@
    3.15                      <c:set var="vysledek" value="${ajax.SQLVysledek}" scope="request"/>
    3.16                  </c:when>
    3.17              </c:choose>
    3.18 +            <!-- </Načteme výsledek z beany>  -->
    3.19  
    3.20 -            <!-- Tabulky -->
    3.21 +            <!-- <Tabulky> -->
    3.22              <c:forEach var="tabulka" items="${vysledek.tabulky}">
    3.23                  <!-- <lokalizace> -->
    3.24                  <c:choose>
    3.25 @@ -62,6 +64,7 @@
    3.26                                          <c:when test="${bunka.class == 'class java.sql.Timestamp'}">
    3.27                                              <td>
    3.28                                                  <fmt:formatDate value="${bunka}" pattern="dd.MM.yyyy HH:mm:ss"/>
    3.29 +                                                <!-- uvnitř vzoru je nedělitelná mezera -->
    3.30                                              </td>
    3.31                                          </c:when>
    3.32                                          <c:when test="${bunka.class == 'class java.sql.Date'}">
    3.33 @@ -87,18 +90,23 @@
    3.34                      </tbody>
    3.35                  </table>
    3.36              </c:forEach>
    3.37 +            <!-- </Tabulky> -->
    3.38  
    3.39 -            <!-- Hlášky -->
    3.40 +            <!-- <Hlášky> -->
    3.41              <c:forEach var="hlaska" items="${vysledek.hlasky}">
    3.42                  <p class="vysledek${hlaska.typ}">
    3.43                      <c:out value="${hlaska.text}" escapeXml="${hlaska.escapovat}" />
    3.44                  </p>
    3.45              </c:forEach>
    3.46 +            <!-- </Hlášky> -->
    3.47          </c:when>
    3.48 +        <!-- </Akce: Historie a vykonání SQL příkazu> -->
    3.49  
    3.50 -        <!-- Akce: Nápověda -->
    3.51 +
    3.52 +        <!-- <Akce: Nápověda> -->
    3.53          <c:when test="${param.akce == 'napoveda'}">
    3.54              <p>TODO: nápověda.</p>
    3.55          </c:when>
    3.56 +        <!-- </Akce: Nápověda> -->
    3.57      </c:choose>
    3.58  </jsp:root>

     4.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     4.2 +++ b/sql/schéma.sql	Sun May 31 22:36:58 2009 +0200
     4.3 @@ -0,0 +1,133 @@
     4.4 +--
     4.5 +-- PostgreSQL database dump
     4.6 +--
     4.7 +
     4.8 +-- Started on 2009-05-31 22:35:00 CEST
     4.9 +
    4.10 +SET client_encoding = 'UTF8';
    4.11 +SET standard_conforming_strings = off;
    4.12 +SET check_function_bodies = false;
    4.13 +SET client_min_messages = warning;
    4.14 +SET escape_string_warning = off;
    4.15 +
    4.16 +--
    4.17 +-- TOC entry 10 (class 2615 OID 16788)
    4.18 +-- Name: sqlvyukaaplikace; Type: SCHEMA; Schema: -; Owner: -
    4.19 +--
    4.20 +
    4.21 +CREATE SCHEMA sqlvyukaaplikace;
    4.22 +
    4.23 +
    4.24 +SET search_path = sqlvyukaaplikace, pg_catalog;
    4.25 +
    4.26 +--
    4.27 +-- TOC entry 1579 (class 1259 OID 16797)
    4.28 +-- Dependencies: 10
    4.29 +-- Name: historie_seq; Type: SEQUENCE; Schema: sqlvyukaaplikace; Owner: -
    4.30 +--
    4.31 +
    4.32 +CREATE SEQUENCE historie_seq
    4.33 +    INCREMENT BY 1
    4.34 +    NO MAXVALUE
    4.35 +    NO MINVALUE
    4.36 +    CACHE 1;
    4.37 +
    4.38 +
    4.39 +SET default_tablespace = '';
    4.40 +
    4.41 +SET default_with_oids = false;
    4.42 +
    4.43 +--
    4.44 +-- TOC entry 1578 (class 1259 OID 16789)
    4.45 +-- Dependencies: 1848 1849 10
    4.46 +-- Name: historie; Type: TABLE; Schema: sqlvyukaaplikace; Owner: -; Tablespace: 
    4.47 +--
    4.48 +
    4.49 +CREATE TABLE historie (
    4.50 +    id numeric DEFAULT nextval('historie_seq'::regclass) NOT NULL,
    4.51 +    datum timestamp with time zone DEFAULT now(),
    4.52 +    sql text,
    4.53 +    id_sezeni character varying(255),
    4.54 +    ip_adresa character varying(255)
    4.55 +);
    4.56 +
    4.57 +
    4.58 +--
    4.59 +-- TOC entry 1580 (class 1259 OID 16813)
    4.60 +-- Dependencies: 10
    4.61 +-- Name: tip_seq; Type: SEQUENCE; Schema: sqlvyukaaplikace; Owner: -
    4.62 +--
    4.63 +
    4.64 +CREATE SEQUENCE tip_seq
    4.65 +    INCREMENT BY 1
    4.66 +    NO MAXVALUE
    4.67 +    NO MINVALUE
    4.68 +    CACHE 1;
    4.69 +
    4.70 +
    4.71 +--
    4.72 +-- TOC entry 1581 (class 1259 OID 16815)
    4.73 +-- Dependencies: 1850 10
    4.74 +-- Name: tip; Type: TABLE; Schema: sqlvyukaaplikace; Owner: -; Tablespace: 
    4.75 +--
    4.76 +
    4.77 +CREATE TABLE tip (
    4.78 +    id numeric DEFAULT nextval('tip_seq'::regclass) NOT NULL,
    4.79 +    text character varying(512) NOT NULL
    4.80 +);
    4.81 +
    4.82 +
    4.83 +--
    4.84 +-- TOC entry 1859 (class 0 OID 0)
    4.85 +-- Dependencies: 1581
    4.86 +-- Name: TABLE tip; Type: COMMENT; Schema: sqlvyukaaplikace; Owner: -
    4.87 +--
    4.88 +
    4.89 +COMMENT ON TABLE tip IS 'Rady, tipy, odkazy a citáty… které budeme zobrazovat uživateli v náhodném pořadí.';
    4.90 +
    4.91 +
    4.92 +--
    4.93 +-- TOC entry 1860 (class 0 OID 0)
    4.94 +-- Dependencies: 1581
    4.95 +-- Name: COLUMN tip.text; Type: COMMENT; Schema: sqlvyukaaplikace; Owner: -
    4.96 +--
    4.97 +
    4.98 +COMMENT ON COLUMN tip.text IS 'HTML text. Bude vložen do <p>odstavce</p>. → Pozor na validitu.';
    4.99 +
   4.100 +
   4.101 +--
   4.102 +-- TOC entry 1852 (class 2606 OID 16796)
   4.103 +-- Dependencies: 1578 1578
   4.104 +-- Name: historie_pk; Type: CONSTRAINT; Schema: sqlvyukaaplikace; Owner: -; Tablespace: 
   4.105 +--
   4.106 +
   4.107 +ALTER TABLE ONLY historie
   4.108 +    ADD CONSTRAINT historie_pk PRIMARY KEY (id);
   4.109 +
   4.110 +
   4.111 +--
   4.112 +-- TOC entry 1854 (class 2606 OID 16823)
   4.113 +-- Dependencies: 1581 1581
   4.114 +-- Name: tip_pk; Type: CONSTRAINT; Schema: sqlvyukaaplikace; Owner: -; Tablespace: 
   4.115 +--
   4.116 +
   4.117 +ALTER TABLE ONLY tip
   4.118 +    ADD CONSTRAINT tip_pk PRIMARY KEY (id);
   4.119 +
   4.120 +
   4.121 +--
   4.122 +-- TOC entry 1856 (class 2606 OID 16825)
   4.123 +-- Dependencies: 1581 1581
   4.124 +-- Name: tip_uq; Type: CONSTRAINT; Schema: sqlvyukaaplikace; Owner: -; Tablespace: 
   4.125 +--
   4.126 +
   4.127 +ALTER TABLE ONLY tip
   4.128 +    ADD CONSTRAINT tip_uq UNIQUE (text);
   4.129 +
   4.130 +
   4.131 +-- Completed on 2009-05-31 22:35:01 CEST
   4.132 +
   4.133 +--
   4.134 +-- PostgreSQL database dump complete
   4.135 +--
   4.136 +