SQLite foundations
Open sqlite repl
Output:
All commands after this assume that you an in the sqlite repl.
Quit sqlite repl
Open (or create) database in repl
Open sqlite database in repl (one command)
List connection
Output:
List databases
Output:
Create table
Output:
List tables
Output:
Output current database structure (“dump”)
Output current database structure (“dump”)
Get sub command help
Output:
Insert full table record (with positional values)
Insert explicit table record
Read explicit columns from table records
You can’t meaningfully materially alter a table once it’s been created
Output:
Asternative:
Reference: https://stackoverflow.com/a/36852775
Use begin and commit to treat transaction as atomic
Read full table record
Insert to table with autoincrementing id
You can omit the id.
Create table with foreign key
Note: you can’t add a foriegn key constraint (just as with primary keys). https://stackoverflow.com/a/1884841
Insert to table with foreign key
Read data with foreign keys
The query can be achieved in reverse (thru habits
).
Note: fancy syntax when column names match.
I don’t think this is worth it, personally. Because it requires calcifying the table name into the column. And, should the query every change, it has to be decomposed to use a different keyword. Both bad, imo.
(possible article id: “USING considered harmful”)
https://www.sqlitetutorial.net/sqlite-join/
Add date column
Note: How to decide which format to use
References:
- https://sqlite.org/datatype3.html
- https://www.sqlitetutorial.net/sqlite-date/
- https://stackoverflow.com/questions/11631390/how-to-add-a-date-column-to-a-table-with-current-date-as-default-value
can’t be done with ALTER TABLE
Output:
TODO: NEXT STEP: Add date to habit_completions.
Other notes
autoincrement
option creates an sqlite_sequence
table (automatically)
https://stackoverflow.com/a/77234217
I think maybe we should ALTER table with a simpler addition earlier on. Maybe description/details/reps.
INNER JOIN vs OUTER JOIN
Naming conventions and best practice
https://stackoverflow.com/a/7724 https://stackoverflow.com/a/2118567
Best practices
- To use column prefixes or not. There’s actually nice refactor potential on both sides, depending on your long-term needs. https://stackoverflow.com/a/7289746
- i think i prefer snake_case. i find it most readable and it works nicely with the convention for capital keywords SELECT id habit_id FROM habit_completions