Creating Data
Getting data into your database
Adding a row
Each item stored in a database
is a single row in a table.
Open the table in SQLite Studio
Double click the users table in the sidebar.
Switch to the data tab
Click the data tab at the top of the window.
The button to insert a row is the
green square with a plus sign on it.
Fill in the row data
Click a cell and then start typing.
Leave user_id and photo as NULL.
Commit your changes
Click the green tick button to save
your changes to the table data.
Adding rows from the shell
We can also script the creation of data.
This is helpful for creating dummy data.
Create a new file dummy-data.sql
Create a new file called dummy-data.sql
in the db folder and open it in your editor.
Create a transaction
BEGIN TRANSACTION;
COMMIT;
Delete all existing users
BEGIN TRANSACTION;
DELETE FROM users;
COMMIT;
Write an insert statement
BEGIN TRANSACTION;
DELETE FROM users;
INSERT INTO users VALUES(
NULL,
'tanya',
'test',
'Tanya',
'Gray',
'tanya@gathergather.co.nz',
NULL
);
COMMIT;
Open the database in the shell
sqlite3 message-board.db
Execute the dummy data script
.read dummy-data.sql
Visually check the result
Open the table in SQLite Studio and refresh
to see the new rows you’ve added.
Challenge: Additional users
Add at least two extra fake users to
your dummy data script and execute it.
Scripting complex data
Many times we want to include data
which goes beyond just plain text.
Current date or time
date(‘now’)
time(‘now’)
datetime(‘now’)
julianday(‘now’)
Specific date or time
date(‘YYYY-MM-DD’)
time(‘HH:MM:SS’)
datetime(‘YYYY-MM-DD HH:MM:SS’)
julianday(‘123456789’)
Images
Stored as a BLOB, which needs to be Hex format.
You can convert images to Hex here
Random numbers
random() % (:high - :low) + :low
random() % (10 - 5) + 5
Inserting linked data
Most tables will have some form of linked data,
and we need to script these relationships too.
Decide correct order of creation
Rows which “own” other rows need to be created first.
For example, a user needs to be created before their posts
so that we can apply the user_id to the posts.
Write an independent insert statement
Write a top-level insert statement which doesn’t
require foreign key data, such as a user.
Write dependent insert statements
Write an insert statement for a row which
depends on the previously created row.
Use the last row ID as the foreign key
INSERT INTO messages VALUES (
NULL,
'Hello',
datetime('now'),
last_insert_rowid()
);
Execute the script in the shell
.read dummy-data.sql
Visually check the result
Open the table in SQLite Studio and refresh
to see the new rows you’ve added.
Challenge: Additional messages
Add a message for each of your users
by adding to your dummy-data script.
