Programming your database
There are often advantages to setting up a database so that common tasks can be performed easily and by name. This way, we can more simply refer to these operations in our application logic, and rely on always getting the consistently correct behavior.
-
Programming your database Welcome and Tech Check
We’ll get to know each other and ensure everyone is set up for the workshop project.
-
Programming your database Views
Views are just queries stored in our database. We can use them in queries by name, as if they’re another table.
-
Programming your database EXERCISE: Views for Dashboard Stats
We’ll build a few queries for a “dashboard”, showing high-level statistics from our database. As we may have several applications that should retrieve the same result set, we’ll need to set our queries up as views - this may get a bit complicated.
-
Programming your database Prepared Statements
Prepared statements allow us to create, parse and plan a parameterized database query. We'll pass values into a statement object later to evaluate it, just like a regular query. Depending on which database solution you are working with, prepared statements may be stored in the database itself (and shared across all clients), or created as an object in your application code.
-
Programming your database EXERCISE: Prepared Statements
As the database connection is initially set up, build some prepared statements to power the “customer stats” feature.
-
Programming your database Triggers & Procedural SQL
Triggers are pieces of procedural code that are automatically executed at a particular moment in time. There are many uses for triggers, and for the most part, this is a feature which works across SQLite, PostgreSQL and MySQL.
-
Programming your database EXERCISE: Order Totals
Currently, it would be prohibitively expensive (in terms of CPU) to add an “Order Total” column onto the
/orders/page, due to the cost of aggregate function on a HUGE table (Order x OrderDetail). We can use another approach involving newOrderTotalcolumn and a trigger. Whenever an OrderDetail row changes, update theOrderTotalvalue for the appropriate order. -
Programming your database Materialized Views
Materialized views can be used just like regular views. The key difference is that they exist as “refreshable” but nonetheless persisted tables in the database. To put it another way, materialized views need to be recalculated periodically, but certainly not on a per-query basis.
-
Programming your database EXERCISE: Better Dashboard Stats
A dashboard is a great potential use case for materialized views, as it displays stats that are not changing from minute-to-minute. We can probably get away with running a few really intense queries once per hour, per day, etc… Once the work is done, the result set can be queried just as speedily as any other table.
-
Programming your database Lunch
Break for lunch.