Gagnasafnsfræði, haust 2011

Verkefni 4 - Forritun á móti SQL

Lausnum skal skilað í hólf viðkomandi dæmakennara (sjá lista yfir dæmatíma).

Skiladagur: mánudaginn 26. september fyrir kl 16:00

Hugbúnaður

Tilbúnir pakkar sem innihalda Apache vefþjón, PHP og PostgreSQL:

Fyrir Windows
Fyrir MacOS X
Fyrir Linux: Notið pakkakerfið í ykkar Linux-dreifingu, t.d. fyrir Ubuntu:
sudo apt-get install apache2 php5 libapache2-mod-php5 postgresql php5-pgsql php5-cli

Tengingar við PostgreSQL

Til að leyfa ytri (remote) tengingar við PostgreSQL þjóninn þarf mögulega að breyta eftirfarandi stillingarskrám.

ATH: Ef þið notið tilbúnu Bitnami pakkana hér að ofan þá þarf ekki að breyta neinu til að leyfa ytri tengingar.

pg_hba.conf

Bæta við eftirfarandi línu:

host all all 127.0.0.1/32 md5

postgresql.conf

Bæta við eftirfarandi línu til að láta PostgreSQL hlusta á öllum IP tölum sem vélin er að nota:

listen_addresses='*'

Java

JDBC fylgir með Java en það þarf að sækja forritasafn fyrir hverja tegund af gagnasafnskerfi (PostgreSQL/MySQL/o.s.frv.)

PostgreSQL JDBC forritasafn: postgresql-9.1-901.jdbc4.jar

Það þarf að bæta JAR skránni í CLASSPATH til að Java keyrslu umhverfið finni hana. Ef þið eruð að nota þróunarumhverfi (t.d. Eclipse) þá er hægt að bæta JAR skránni við lista af libraries í núverandi project.

Ef þið notið skelina þá er hægt að nota java -cp stika til að stilla classpath.

Dæmi um þýðingu og keyrslu í Windows:

hhg@hhg:~/v4$ javac Execute.java
hhg@hhg:~/v4$ java -cp "postgresql-9.1-901.jdbc4.jar;." Execute
hhg@hhg:~/v4$

og í Linux eða MacOS X:

hhg@hhg:~/v4$ javac Execute.java
hhg@hhg:~/v4$ java -cp postgresql-9.1-901.jdbc4.jar:. Execute
hhg@hhg:~/v4$

Sýnidæmi:

Aðrir tenglar:

JDBC tutorial

Python

Sýnidæmi:

Aðrir tenglar:

PHP

Sýnidæmi:

Aðrir tenglar:

Skjölun fyrir SimpleXML í PHP
Skjölun fyrir PostgreSQL (pgsql) í PHP
Skjölun um split() í PHP (til að breyta dagsetningunni)

ATH: Ef þið fáið villu eins og:

pg_insert() Expects scaler values as field values in...

þegar þið eruð að leysa lið 2 með PHP, þá þarf að kasta XML gildunum yfir í streng:

// Breyta:
$mynt->myntnafn
// Yfir í:
(string)$mynt->myntnafn

Skiladæmi:

Skilgreinið töflu til að halda utan um opinbert viðmiðunargengi íslensku krónunnar gagnvart vissum gjaldmiðlum.
Taflan skal innihalda eftirfarandi dálka:
1. dags: dagsetning
2. myntnafn: þrír stafir (t.d. USD, GBP)
3. kaupgengi: tala með tveimur aukastöfum (t.d. 135.98)
4. midgengi: tala með tveimur aukastöfum
5. solugengi: tala með tveimur aukastöfum
Notið numeric tagið í PostgreSQL til að geyma gengistölurnar.
Það er ein skráning á dag per myntnafn. Hagið aðallykli töflunnar samkvæmt því.
Sýnið CREATE TABLE skipunina á læsilegu formi með inndrætti.
Það er hægt að sækja opinbert viðmiðunargengi íslensku krónunnar í dag á XML formi frá vefþjónustu Seðlabankans á eftirfarandi slóð:
http://www.sedlabanki.is/?PageID=289

Einnig er hægt að sækja viðmiðunargengið ákveðinn dag með því að bæta við stikanum dagur=DD.MM.YYYY.
Dæmi:
http://www.sedlabanki.is/?PageID=289&dagur=25.01.2007
Skrifið forrit í Java, Python, PHP eða öðru forritunarmáli að ykkar vali sem virkar á eftirfarandi hátt:
1. Forritið keyrir í skipanalínu sem sjálfstætt forrit
2. Forritið tekur eitt viðfang: dagsetningu á forminu YYYY-MM-DD
3. Forritið sækir opinbert viðmiðunargengi frá vefþjónustu Seðlabankans
4. Forritið þáttar XML gögnin og skráir viðmiðunargengi USD, GBP, CHF og JPY í PostgreSQL töfluna úr dæmi 1
Dæmi um mögulega keyrslu á forritinu:
```
java gengi 2011-09-12
```
eða
```
python gengi.py 2011-09-12
```
Skilið forritstextanum. Passið að textinn sé læsilegur (notið réttan inndrátt/indent). Sýnið dæmi um keyrslu og hvað var skráð í PostgreSQL fyrir þá keyrslu.
Búið til eftirfarandi töflu sem heldur utan um pantanir:
```
CREATE TABLE pantanir (
    id SERIAL PRIMARY KEY,
    customer INTEGER NOT NULL,
    dags DATE NOT NULL,
    vorunumer INTEGER NOT NULL,
    mynt CHAR(3) NOT NULL,
    verd NUMERIC(8,2) NOT NULL,
    magn INTEGER NOT NULL
);
```
Hlaðið inn skránni pantanir.csv (sem inniheldur 10.000 skáldaðar pantanir) með eftirfarandi COPY skipun:
```
COPY pantanir (dags, customer, vorunumer, mynt, verd, magn)
   FROM 'pantanir.csv'
   DELIMITER '|';
```
ATH. Það gæti þurft að slá inn slóðina (full path) á pantanir.csv ef skráin er ekki í vinnumöppunni. Gerið þá /home/xx/.. í Linux/MacOS X eða c:/xx/.. í Windows.

Sýnið SQL skipun sem sækir eftirfarandi töflu: fyrir hvert vörunúmer, mynt og verð, reiknið fjölda pantana, heildarmagn og heildarupphæð (verð*heildarmagn) í júlí (2011-07). Raðið eftir heildarupphæð í lækkandi röð.

Útkoman ætti að vera nákvæmlega sú tafla sem sést hér fyrir neðan:

 vorunumer | mynt | verd  | fjoldi | magn  |   total    
-----------+------+-------+--------+-------+------------
    221517 | USD  | 29.99 |    733 | 36110 | 1082938.90
    273140 | USD  |  8.99 |   1450 | 74086 |  666033.14
    732052 | GBP  | 15.99 |    679 | 34327 |  548888.73
    158173 | USD  | 17.52 |    462 | 23089 |  404519.28
    601314 | GBP  | 19.99 |    242 | 12061 |  241099.39
    770291 | CHF  |  2.99 |    961 | 49041 |  146632.59
    526734 | CHF  |  3.25 |    241 | 12249 |   39809.25
(7 rows)

Ábending: Það er hægt að nota TO_CHAR fallið í PostgreSQL til að vinna með dagsetningar.

Dæmi:

SELECT *, TO_CHAR(dags, 'YYYY-MM') AS month
FROM pantanir
LIMIT 1;

  id   | customer |    dags    | vorunumer | mynt | verd  | magn |  month  
-------+----------+------------+-----------+------+-------+------+---------
 20000 |    10248 | 2011-07-01 |    158173 | USD  | 17.52 |   83 | 2011-07
(1 row)

Notið forritið ykkar sem sækir viðmiðunargengið fyrir gefna dagsetningu til að skrá inn gengið fyrir dagsetningar 2011-08-01, 2011-08-02 og 2011-08-03.
Sýnið SQL skipun sem notar ytri tengingu (OUTER JOIN) til að sækja lista yfir allar dagsetningar+myntir í pöntunum sem eru ekki með neitt gengi skráð í gengistöfluna.
Sýnið SQL skipun sem sækir eftirfarandi töflu: fyrir hvern mánuð, reiknið heildarupphæð í íslenskum krónum fyrir allar pantanir (notið miðgengið til að breyta yfir í krónur).
Taflan ætti að vera ca. svona:
```
 month   | total
---------+--------
 2011-08 | XXXX
```
ATH: Ég geri ráð fyrir að þið séuð bara með gengi skráð fyrir dagsetningar 2011-08-01, 2011-08-02 og 2011-08-03.
Ég geri einnig ráð fyrir að þið notið venjulega innri tengingu (INNER JOIN) og þá eru pantanir án gengis ekki teknar með í útreikningi, sbr. tengireglur fyrir INNER JOIN.