SQlite work

1

HIT326 Database-Driven Web Applications

Lab Assessment Semester 1, 2021

• Lab Assessment due in Week 3.

You will have to answer the following questions. Keep it as brief as possible. As usual, copying

and pasting from sources will not attract marks (!!attention to plagiarism!!). Your original words,

reflections and questions will attract the most marks. You may, of course, cite sources.

Prerequisites: • Have completed related Workshops (1, 2 and 3)

• You have downloaded and extracted the executable SQLite shell into a folder of your

choice. You may have also set the path to the shell so you can execute it from

anywhere on your system. If you haven’t set the path then just place the executable

file in the folder in which you wish to work in. Refer to workshop 1 for further details.

Answer the following questions:

Part 1 –SQLite activities SQLite only has a handful of data types. MySQL has many more. Please check out the

following link and find out what those types are: http://www.sqlite.org/datatype3.html

Note that for our purposes we shall only use four of the five ‘storage classes’ in SQLite. If you

peruse Section 2.2 (Affinity Name Examples) you’ll see you can use data types from other

database systems too. Complete the following tasks/questions.

-Go to the Workshop location in LearnLine week 1 and download the archived file called

“example_1.zip”.

-Complete Part 1 and Part 2.

-Continue to work on the below exercises.

Exercise 1

1. What happens to your table if you run the ‘load’ script over and over again (i.e. without

running ‘create’)? You’ll have to ‘test’ it along the way to see what happens.

2

2. Re-create and load the original data again.

3. Comment out the SELECT statement in the ‘test script’. Now write (or copy and modify)

the following SELECT statements, and observe the ‘tests’. Note what happens each time.

SELECT name FROM users;

SELECT user_id, name FROM users; SELECT name, user_id FROM users;

SELECT name FROM users WHERE user_id='1';

SELECT name FROM users WHERE user_id='2' AND name='Alice';

SELECT name FROM users WHERE user_id='1' AND name='Alice';

SELECT name FROM users WHERE user_id='1' OR name='Alice';

4. Before you proceed to the next exercise make sure you retain a copy of the previous load

files, especially the ‘test’ script. You may find these useful for future workshops and your

project. You are about to do another download and extract. If you don’t move the current files

they will be overwritten with new load files.

Exercise 2

1. Download, extract, create, load and test ‘example_2.zip’ (Week 1) in the same way you

did in Part 2 in workshop 1.

2. Examine the CREATE TABLE statement in the ‘create’ script. Note that it now has an

extra field (or column) as follows:

created_at DATE DEFAULT(datetime('now','localtime'))

Also note the modification made to the ‘name’ field. It now has UNIQUE included like this:

name TEXT UNIQUE NOT NULL

3. This automatically sets the creation date when new data is INSERT’ed from the ‘load’

script.

Especially note the SQLite function called datetime(). ‘Now’ means the time as it’s represented

on your computer. ‘localtime’ ensures it is adjusted to the Time Zone set on your computer.

3

UNIQUE ensures that the names must be unique for each new record. For example the

database

won’t allow more than one record with the name ‘Alice’.

4. Remove ‘localtime’ from the CREATE TABLE script. Create, load and test it again. What

is the time on each record now?

Put ‘localtime’ back after you see the result and ‘create’, ‘load’ and ‘test’ it again.

5. Please read about date functions and how SQLite handles dates and time at:

http://www.sqlite.org/lang_datefunc.html

What is UTC?

6. Try to ‘load’ the table data twice or more so the same records are attempted to be inserted.

What happens? Why?

7. Try the following SQL statements in the ‘test’ script and observe the results:

a) SELECT * FROM users WHERE created_at > '2012-01-20';

b) INSERT INTO users (name) values ("Margo O'Brien");

c) Run the ‘test’ script again? What happens? Did you see an error? Why?

d) Comment out the INSERT statement.

e) SELECT * FROM users ORDER BY created_at DESC;

f) SELECT * FROM users ORDER BY created_at ASC;

g) What do DESC and ASC do?

Part 2 advanced SQLite tasks

Sometimes the data which you want to load into your database already exists in another

format. We have only dealt with inserting several records so far. But what if there are

thousands of records. You don’t want to be sitting there all day or week writing INSERT

statements do you?

CSV (Comma Separated Values) is a common plain text file format used to store records.

Most database systems allow you to address the CSV file to directly insert the records.

Now download ‘example_3.zip’ from LearnLine (Week 1 workshop) and extract them. Please

read the ‘read_me.txt’ file about some of the modifications I have from the original CSV from

http://openflights.org/data.html

4

This database has over 6500 records.

Exercise 3

1. As usual inspect all three scripts as well as ‘airports.csv’.

2. In the ‘create’ script you should notice the usage of the data type REAL. Why do those

particular fields use REAL and not INTEGER or TEXT?

3. The ‘load’ script is of most interest here. This were we load the CSV into the database.

Notice there are only two lines (not counting comments) which do the trick. .separator "," This

SQLite dot command, .separator, tells the shell that each value in each row in the CSV file is

separated with a comma. .import airports.csv airports This SQLite dot command, .import, tells

the shell to import the contents of ‘airports.csv’ into a table named ‘airports’.

4. The ‘test’ script has an SQLite dot command too. It simply ensures that column names (i.e.

field names) are displayed. Can you see it?

5. Go ahead and create, load, and test the scripts. You’ll notice the ‘load’ take a little longer

than previous examples. The first ‘test’ script will take some to print to the screen. Then try the

following statements and tests and make sure you comment them out before you try new ones

each time:

a. SELECT name, city, country FROM airports WHERE city = "Darwin";

b. SELECT name, city, country FROM airports WHERE country = "Australia";

c. SELECT name, city, timezone FROM airports WHERE country='Australia';

d. SELECT name, city, country FROM airports WHERE city LIKE "New%" AND country =

"United States"; Note the LIKE “New%”. Notice the % sign. Essentially this query asks for

cities beginning with “New” followed by zero or more characters AND where the country is the

United States.

e. SELECT name, city, country FROM airports WHERE city LIKE "New York%" AND country

= "United States";

f. SELECT name, city, country, latitude, longitude FROM airports WHERE longitude > 130

AND longitude <132 AND latitude < 0;

g. When lots of records are printed to a screen it is not always easy to carefully inspect them

and once you close the window they are lost. Sometimes you want a more permanent record

of the results of your queries. In this case we may print to a file.

5

On about the sixth line from the top of the ‘test’ script you’ll see a commented line like this:

--.output out.txt

Uncomment it. This is another SQLite dot command which sets the output to the textfile called

‘out.txt’. The next time you run your ‘test’ script the output will go to the file and not to the

screen.

h. Finally, let’s use the command line to output the text to a text file.

First, comment the “.output out.txt” command in the ‘test’ script so it looks like the example

above i.e. “--.output out.txt”.

Also delete the last “out.txt” file.

This time we will redirect the output from the ‘screen’ (standard output) to a file using the

command line’s output redirection symbol, which is the greater-than sign. Execute the test

command like this:

sqlite3 test.db < test.sql > out.txt

Note the < sign directs input (test.sql) to sqlite. The > sign directs the output to a file (out.txt).

Part 3 – Controller and Views 1. Download example8.zip from Week 3’s workshop.

2. Create the table and load the data from the given ‘create’ and ‘load’ files.

3. Start the application in your Server.

4. Try adding some ‘players’

5. Examine the files in the collection with extension “.php”

Respond to these questions:

A. Which file represents the Controller?

B. Which files represent the Views?

C. Note the files which are not listed in A or B. What is their role?