Fun with Views and CTEs

A view is a stored query the results of which can be treated like a table. Note that it is the query that is saved and not the results of the query. Each time you use a view, its associated query is executed.

A related concept is that of the common table expression or CTE. A CTE can be thought of as a short-lived view; you can only use it within the query in which it appears (you can refer to it multiple times, however).

Let's say that you're the SQL-savvy owner of a store that sells kits for building robots. On your site, customers are guided through the process of selecting all of the components needed to build a quadrupedal robot, i.e. getting the right motors, microcontrollers, sensors, batteries, grippers, etc.

Robots have lots of component parts and you have to make sure that the correct components for

alt text
customers
alt text
purchases
alt text
component_purchases
query1
query_4
tree graph
working_table_1
inner_join_1
working_table_2
inner_join_2
working_table_3
inner_join_3
working_table_4
inner_join_4
working_table_5
subcomponents
purchases_made
pm_pc_join

Continue reading “Fun with Views and CTEs”

Document Your Confusion

Like a diary, but for code

On a few occasions, I've started work on a brownfield project and found that, either due to poor programming practices or because there was no elegant way to implement a feature, it's been necessary to create documentation beyond what is normally included in a README. This documentation often took the form of glossaries, diagrams, or additional exposition on the codebase.

Your typical README contains all or some of the following: a short introductory paragraph, a list of dependencies, instructions on how to install the app, configuration, how to run the test suite, database creation and migration, services, and how to deploy the app. If it's an open source project, there will likely be sections devoted to listing authors, rules around contributing, the license under which it can be used, as well as additional documentation around how to use the package.

There are contexts in

Continue reading “Document Your Confusion”