ARmGxbvz

1\documentclass[a4paper,12pt]{article}
2\usepackage[T1]{fontenc}
3\usepackage[utf8]{inputenc}
4\usepackage{lmodern}
5\usepackage{ngerman}
6\usepackage{hyperref}
7\title{SQL Dokumentation für Wooden Dreams EU}
8\author{Henrik Halbritter\\Nico Tiedemann\\Lukas Wiechern}
9\begin{document}
10\maketitle
11\newpage
12\tableofcontents
13\newpage
14
15\section{SQL-Abfragen}
16
17Alle folgenden Querrys sind als ,,Prepared Statements'' realisiert.
18Fragezeichen (?) sind Platzhalter und werden vom SQL Connector, in unserem Falle SQLI, mit den eigentlichen Werten ersetzt.\\
19\subsection{Sessions}
20\subsubsection{Session prüfen}
21Wir prüfen, ob es die vom Nutzer angegebene Session gibt:
22\begin{verbatim}
23SELECT 1 AS value
24    FROM sessions
25    WHERE idsession = ?
26    LIMIT 1
27\end{verbatim}
28{[1]} = PHP Session ID
29\subsubsection{Session anlegen}
30Hiermit legen wir eine neue Session in der Datenbank an:
31\begin{verbatim}
32INSERT INTO sessions (idsession) value (?)
33\end{verbatim}
34{[1]} = PHP Session ID
35\subsection{Artikel abrufen}
36Wir rufen die Artikel mit folgendem Querry aus der Datenbank ab:
37\begin{verbatim}
38SELECT *
39    FROM article
40\end{verbatim}
41\subsection{Warenkorb}
42\subsubsection{Artikel hinzufügen}
43Artikel werden mit einer ,,Stored Procedure'' in den Warenkorb gelegt. Mehr dazu unter \ref{sec:addtochart}.
44\begin{verbatim}
45CALL add_article_to_chart(?, ?, ?)
46\end{verbatim}
47{[1]} = PHP Session ID\\
48{[2]} = Artikel ID\\
49{[3]} = Anzahl
50\subsubsection{Menge ändern}
51Das Ändern der Menge im Warenkorb geschieht ebenfalls über eine ,,Stored Procedure''. Mehr dazu unter \ref{sec:updatechart}.
52\begin{verbatim}
53CALL updateChart(?, ?, ?)
54\end{verbatim}
55{[1]} = Artikel ID\\
56{[2]} = Anzahl\\
57{[3]} = PHP Session ID
58\subsubsection{Artikel löschen}
59Soll ein Artikel aus dem Warenkorb gelöscht werden, geschieht das mit:
60\begin{verbatim}
61DELETE FROM chart 
62    WHERE idarticle = ?
63    AND idsession = ?
64\end{verbatim}
65{[1]} = Artikel ID\\
66{[2]} = PHP Session ID
67\subsubsection{Wert ermitteln}
68Der Gesamtwert des Warenkorbs wird folgendermaßen gebildet:
69\begin{verbatim}
70SELECT sum(article.price*chart.count) summe
71    FROM chart 
72    JOIN article ON(chart.idarticle=article.id)
73    WHERE chart.idsession = ?
74\end{verbatim}
75{[1]} = PHP Session ID
76\subsection{Anmeldung}
77\subsubsection{Passwort prüfen}
78Das Passwort, oder viel mehr der Hash des Passowortes, wird wie folgt abgerufen. Die Überprüfung ist in PHP realisiert:
79\begin{verbatim}
80SELECT u.password
81    FROM user u
82    WHERE u.email = ?
83\end{verbatim} 
84{[1]} = E-Mailadresse des Benutzers
85\subsubsection{Login}
86Für den eigentlichen Login verwenden wir folgenden Querry:
87\begin{verbatim}
88UPDATE sessions
89    SET user = (SELECT id 
90                    FROM user 
91                    WHERE email = ? LIMIT 1) 
92        WHERE sessions.idsession = ?
93\end{verbatim}
94{[1]} = Username\\
95{[2]} = PHP Session ID
96\subsubsection{Logout}
97Abgemeldet werden Nutzer mit:
98\begin{verbatim}
99UPDATE sessions
100    SET sessions.user = NULL
101    WHERE idsession = ?
102\end{verbatim}
103{[1]} = PHP Session ID
104\subsubsection{Anmeldung prüfen}
105Mit dem folgendem Querry prüfen wir, ob der Nutzer angemeldet ist:
106\begin{verbatim}
107SELECT 1
108    FROM user u 
109    JOIN sessions s ON u.id = s.user 
110    WHERE s.idsession = ?
111\end{verbatim}
112{[1]} = PHP Session ID
113\pagebreak
114\subsection{Kundendaten}
115\subsubsection{Kunden anlegen}
116Für die Registrierung wird folgender Querry verwendet:
117\begin{verbatim}
118INSERT INTO user (fname,lname,address,email,plz,ort,tele,bday,password)
119    VALUE (?, ?, ?, ?, ?, ?, ?, ?, ?)
120\end{verbatim}
121{[1]} = Vorname\\
122{[2]} = Nachname\\
123{[3]} = Straße + Hausnummer\\
124{[4]} = E-Mail Adresse\\
125{[5]} = Postleitzahl\\
126{[6]} = Ort\\
127{[7]} = Telefonnummer\\
128{[8]} = Geburtsdatum\\
129{[9]} = Passwort als Hash
130\subsubsection{Kundendaten abrufen}
131Kundendaten werden mit folgendem Querry abgerufen:
132\begin{verbatim}
133SELECT u.id id, u.email email, u.fname fname,
134    u.lname lname, u.plz plz, u.address address,
135    u.ort ort, u.tele tele, u.bday bday
136    FROM user u 
137    JOIN sessions s ON s.user = u.id 
138    WHERE s.idsession = ?
139\end{verbatim}
140{[1]} = PHP Session ID
141\pagebreak
142\subsubsection{Daten aktualisieren}
143Kundendaten können mit folgendem Querry aktualisiert werden:
144\begin{verbatim}
145UPDATE user 
146    SET fname = ?, lname = ?, address = ?, email = ?,
147    plz = ?, ort = ?, tele = ?, bday = ? 
148    WHERE id = (SELECT user 
149                    FRROM sessions 
150                    WHERE idsession = ? 
151                    LIMIT 0,1)
152\end{verbatim}
153{[1]} = Vorname\\
154{[2]} = Nachname\\
155{[3]} = Straße + Hausnummer\\ 
156{[4]} = E-Mail Adresse\\
157{[5]} = Postleitzahl\\
158{[6]} = Ort\\
159{[7]} = Telefonnummer\\
160{[8]} = Geburtsdatum\\
161{[9]} = PHP Session ID
162\subsection{Bestellung}
163\subsubsection{Bestellung erzeugen}
164Eine Bestellung wird folgendermaßen erzeugt:
165\begin{verbatim}
166INSERT INTO `order` (fname,lname,plz,street,ort,email,knummer)
167    VALUES (?, ?, ?, ?, ?, ?, ?)
168\end{verbatim}
169{[1]} = Vorname\\
170{[2]} = Nachname\\
171{[3]} = Postleitzahl\\
172{[4]} = Straße + Hausnummer\\
173{[5]} = Ort\\
174{[6]} = E-Mail Adresse\\
175{[7]} = User ID
176\pagebreak
177\subsubsection{Bestellpositionen}
178Positionen werden wie folgt zu den Bestellungen hinzugefügt:
179\begin{verbatim}
180INSERT INTO `orderp` (oderid, artid, anzahl, preis )
181    VALUES (?,?,?,?)
182\end{verbatim}
183{[1]} = ID der Bestellung\\
184{[2]} = Artikelnummer\\
185{[3]} = Anzahl\\
186{[4]} = Stückpreis
187\section{Definition der Tabellen}
188\subsection{Artikel}
189\begin{verbatim}
190CREATE TABLE `article` (
191`id` INT(11) NOT NULL AUTO_INCREMENT,
192`name` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL,
193`price` VARCHAR(45) CHARACTER SET 'utf8' NULL DEFAULT NULL,
194`img` VARCHAR(255) CHARACTER SET 'utf8' NULL DEFAULT NULL,
195`desclong` TEXT CHARACTER SET 'utf8' NULL DEFAULT NULL,
196PRIMARY KEY (`id`, `name`))
197ENGINE = InnoDB
198DEFAULT CHARACTER SET = utf8
199COLLATE = utf8_bin;
200\end{verbatim}
201\pagebreak
202\subsection{Benutzer}
203\begin{verbatim}
204CREATE TABLE `user` (
205`id` INT(11) NOT NULL AUTO_INCREMENT,
206`email` VARCHAR(45) NOT NULL,
207`password` VARCHAR(60) NOT NULL,
208`lastlogin` TIMESTAMP NOT NULL DEFAULT
209CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
210`fname` VARCHAR(45) NULL DEFAULT NULL,
211`lname` VARCHAR(45) NULL DEFAULT NULL,
212`plz` VARCHAR(45) NULL DEFAULT NULL,
213`address` VARCHAR(45) NULL DEFAULT NULL,
214`ort` VARCHAR(45) NULL DEFAULT NULL,
215`tele` VARCHAR(45) NULL DEFAULT NULL,
216`bday` VARCHAR(45) NULL DEFAULT NULL,
217PRIMARY KEY (`id`),
218UNIQUE INDEX `name_UNIQUE` (`email` ASC))
219ENGINE = InnoDB
220DEFAULT CHARACTER SET = latin1;
221\end{verbatim}
222\subsection{Session}
223\begin{verbatim}
224DROP TABLE IF EXISTS `sessions` ;
225CREATE TABLE IF NOT EXISTS `sessions` (
226`idsession` VARCHAR(64) NOT NULL,
227`user` INT(11) NULL DEFAULT NULL,
228PRIMARY KEY (`idsession`),
229INDEX `iduser_idx` (`user` ASC),
230CONSTRAINT `iduser`
231FOREIGN KEY (`user`)
232REFERENCES `user` (`id`))
233ENGINE = InnoDB
234DEFAULT CHARACTER SET = utf8;
235\end{verbatim}
236\pagebreak
237\subsection{Warenkorb}
238\begin{verbatim}
239CREATE TABLE `chart` (
240`id` INT(11) NOT NULL AUTO_INCREMENT,
241`idsession` VARCHAR(45) NOT NULL,
242`idarticle` INT(11) NOT NULL,
243`count` INT(11) NOT NULL,
244PRIMARY KEY (`id`),
245INDEX `id_session_idx` (`idsession` ASC),
246INDEX `id_article_idx` (`idarticle` ASC),
247CONSTRAINT `id_article`
248FOREIGN KEY (`idarticle`)
249REFERENCES `article` (`id`),
250CONSTRAINT `id_session`
251FOREIGN KEY (`idsession`)
252REFERENCES `sessions` (`idsession`)
253ON DELETE CASCADE)
254ENGINE = InnoDB
255DEFAULT CHARACTER SET = utf8;
256\end{verbatim}
257\subsection{Bestellung}
258\begin{verbatim}
259CREATE TABLE `order` (
260`id` INT(11) NOT NULL AUTO_INCREMENT,
261`fname` VARCHAR(45) NULL DEFAULT NULL,
262`lname` VARCHAR(45) NULL DEFAULT NULL,
263`plz` VARCHAR(45) NULL DEFAULT NULL,
264`street` VARCHAR(45) NULL DEFAULT NULL,
265`ort` VARCHAR(45) NULL DEFAULT NULL,
266`email` VARCHAR(45) NULL DEFAULT NULL,
267`knummer` INT(11) NULL DEFAULT NULL,
268PRIMARY KEY (`id`),
269INDEX `fk_order_1_idx` (`knummer` ASC),
270CONSTRAINT `fk_order_1`
271FOREIGN KEY (`knummer`)
272REFERENCES `user` (`id`))
273ENGINE = InnoDB
274DEFAULT CHARACTER SET = utf8;
275\end{verbatim}
276\subsection{Bestellposition}
277\begin{verbatim}
278CREATE TABLE `orderp` (
279`id` INT(11) NOT NULL AUTO_INCREMENT,
280`oderid` INT(11) NOT NULL,
281`artid` INT(11) NULL DEFAULT NULL,
282`anzahl` INT(11) NULL DEFAULT NULL,
283`preis` DOUBLE NULL DEFAULT NULL,
284PRIMARY KEY (`id`),
285INDEX `fk_orderp_1_idx` (`artid` ASC),
286INDEX `fk_orderp_2_idx` (`oderid` ASC),
287CONSTRAINT `fk_orderp_1`
288FOREIGN KEY (`artid`)
289REFERENCES `article` (`id`),
290CONSTRAINT `fk_orderp_2`
291FOREIGN KEY (`oderid`)
292REFERENCES `order` (`id`))
293ENGINE = InnoDB
294DEFAULT CHARACTER SET = utf8;
295\end{verbatim}
296\section{Stored Procedure}
297\subsection{add\char"5F article\char"5F to\char"5F chart(?,?,?)}\label{sec:addtochart}
298\begin{verbatim}
299CREATE DEFINER=`root`@`localhost` PROCEDURE `add_article_to_chart`
300(IN sessionid varchar(45), IN article INT(11), IN anzahl INT(11))
301BEGIN
302    SET @c = (
303        SELECT count(*) 
304            FROM chart 
305            WHERE idsession = sessionid 
306            AND idarticle = article);
307    IF(IFNULL(@c,0) >= 1) THEN 
308        UPDATE chart
309        SET count = count + anzahl WHERE idsession = sessionid
310        AND idarticle = article;
311    ELSE 
312        INSERT INTO chart (idsession, idarticle, count) 
313        values(sessionid, article, anzahl);
314    END IF; 
315END
316\end{verbatim}
317\subsection{updateChart(?,?,?)}\label{sec:updatechart}
318\begin{verbatim}
319CREATE DEFINER=`root`@`localhost` PROCEDURE `updateChart`
320(IN artid int(11),IN anzahl int(11),IN sessionid varchar(45))
321BEGIN
322    IF(anzahl = 0) THEN DELETE FROM chart 
323        WHERE idsession = sessionid 
324        AND idarticle = artid; 
325    ELSE
326        UPDATE chart c SET c.count = anzahl
327        WHERE idsession = sessionid AND idarticle = artid;
328    END IF;
329END
330\end{verbatim}
331\end{document}