iragu/HOWTO.html at main · gurusami/iragu · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
<!doctype>
<html>
<head>
 <title> Iragu: Badminton Court Management </title>
</head>

<body>
<h1> Iragu: Badminton Court Management </h1>

<p> The purpose of this document is to help me remember various details.  Since
  this is hobby work, I'll work on it erratically.  Hopefully this document
  will remind me about what and why I did something. </p>

<h2> My Development Environment </h2>

<ol>
  <li> Ubuntu 20.04.3 LTS (Focal Fossa) - Ubuntu MATE variant </li>
  <li> Apache Web Server (Apache/2.4.41)</li>
  <li> PHP 7.4.3 </li>
  <li> MySQL 8.0.28 </li>
</ol>

<h2> Setting up MySQL </h2>

As MySQL root user:

<pre>
mysql> CREATE DATABASE kdb;
mysql> CREATE USER 'queen'@'localhost' IDENTIFIED BY 'dummy';
mysql> GRANT ALL ON kdb.* TO 'queen'@'localhost' WITH GRANT;
mysql> GRANT PROCESS ON *.* TO 'queen'@'localhost';
</pre>

<ol>
 <li> Create the necessary databases.  Currently only one is used.
  <pre> mysql> CREATE DATABASE kdb; </pre> </li>

 <li> Create a non-root MySQL user 'queen', who will be the admin of kdb
 database used by the iragu webapp.
  <blockquote> NOTE: Just for a valid SQL statement I have provided a password
  here. Kindly change it while using it. </blockquote>
  <pre> mysql> CREATE USER 'queen'@'localhost' IDENTIFIED BY 'dummy';
  </pre> </li>

 <li> Grant all privileges to the non-root MySQL user 'queen' on the kdb
 database.
  <pre> mysql> GRANT ALL ON kdb.* TO 'queen'@'localhost' WITH GRANT; </pre>
 </li>

 <li> To run mysqldump on kdb, the non-root MySQL user 'queen' need the PROCESS
 privilege. Grant the same.
  <pre> mysql> GRANT PROCESS ON *.* TO 'queen'@'localhost'; </pre></li>
</ol>

<h2> Backup of Database </h2>

<p> For this small database, backup can be taken up using mysqldump.  Also,
backup the user privileges. The kdb-schema.sql file contains only the data
definition and hence will be part of git repo. The kdb-dump.sql will contain
data which must not be in a git repo. </p>

<pre>
  $ mysqldump -u queen -p --databases --no-data kdb > kdb-schema.sql
  $ mysqldump -u queen -p --databases kdb > kdb-dump.sql
  $ sudo mysql -e "show grants for queen@localhost" > grants.sql
</pre>

<h2> Administrative Work on Database </h2>

<p> To do all administrative work, the user queen@localhost is used.  Since this
webapp uses only the database <i>kdb</i>, the following command should work.
</p>

<pre>
  $ mysql -u queen -p kdb
</pre>

<h2> Database Tables </h2>

<ul>
  <li> The table <i>ir_people</i> is used to store information about any person
  who needs to interact with the system. Each person is assigned a unique nick
  name (maximum length of 8 chars). </li>
  <li> The table <i>ir_login</i> is used to store login credentials of any user
  who needs access to the iragu application. Only authenticated users can do
  anything useful. Password is stored as SHA2(password, 256) function of MySQL.
  </li>
</ul>


<h2> Operations </h2>

<ul>
  <li> Register a badminton player. </li>
</ul>

</body>
</html>