MIT Web Programming Crash Course

• The 2019 schedule: http://weblab.mit.edu/schedule/ (flip your device to landscape, or view in desktop mode if you can't see lecture/slide links)
  • The 2019 lectures are terrible production quality, slides are available to pair with them. Or try the 2018 schedule, better quality lectures
• The git repository for the assignments

Optional:

• MIT's 6.170 Software Studio lectures and slides for exercises and review, which is a more advanced course so optional extra material.
• The free book, Functional-Light JavaScript by Kyle Simpson to help explain some of the 6.170 material.

As stated in the intro this workshop assumes no background, not even a programming background. You could absolutely follow the lectures and just hack together a prototype and it will work.

I'm doing the majority of exercises with Eruda, on a $100 Motorola phone 1hr or so per day and it's been working so far. You can also use Chrome DevTools or FireFox Developer Tools to edit javascript in the console as you read the slides on any desktop browser or VSCode. This site itself is Termux + Emacs + org-mode and the generated html is then POST to 1mb.site a free host. 1mb also has an online code editor. For the Node workshops you can run this locally or deploy for free to Zeit.

Some anons have started a Discord channel if you want to collaborate/learn with others.

• business model abstractions, improving existing models
• 'PAFT': (P)roblem thinking, considering the (A)udience, considering (F)eatures, but have no idea what they mean by Theme

• Overdone ideas are talked about, though it directly conflicts with the very first step which talks about successful abstractions based on existing ideas

• MVP = Minimal Viable Product or the bare minimum functionality you need to showcase your idea
• Try to describe your website/app in a single sentence
  • talking about diverging your idea such as extending bathroom codes list w/wifi codes
  • example features: paying 'points' for contributing to a crowdsourced data set to encourage user participation

VSCode is shilled, which is pretty easy to learn. For most of the beginning of the workshop I edited in Eruda (use Chrome DevTools on a desktop) directly changing html and CSS.

The features that define your software should be in the prototype you push early to your audience, with nice to have features left as a TODO. You can make this easier by being able to describe your software in one sentence, which will help make it clear what your critical features are.

Div soup is when you look at the source at many modern websites and see an enormous pile of div containers everywhere.

• HTML now has semantic elements, some advice on semantic element use from the HTML working group WHATWG
  • These elements exist so software like password managers, search engine bots(SEO), pocket, accessibility assistants can parse the page, or for future developers trying to maintain the page (which could be yourself a few months from now)
  • div is defined in the lecture as a block section ie: 'making scope boxes in documents'
  • span is defined as inline sections, such as changing the colors of every letter in a single word

MIT's advice is to 'just google', so I'm going to skim through the actual HTML standard, to clear up these questions like which semantic elements do what, what exactly div is, etc.

• The standard insists there is only HTML and no 'HTML5', as the standard is unversioned.
• A review of semantics
  • Another statement that html markup conveys the meaning, rather than the presentation since the browser can adjust all these things.
  • There's a comment about using grouping elements like hgroup, to organize secondary titles when using headings, so they aren't misinterpreted as a seperate section.
    • This is apparently not adopted by W3C, another standards organization, and MDN claims hgroup is 'theoretical only' linking to a W3C workaround using span

We find what we need:

• Full table of contents for HTML elements and their description
  • A div element is strongly encouraged to only be used as last resort, with other semantic tags used instead for accessibility software
• The <em> element is emphasis, for accessibility readers and other machines parsing html, it subsumes italics element
• Span is exampled here as loading a translation attribute in a sentence on a single word
• Standard also lists various attributes we will want to ensure the [0-9] number keypad pops up on touchscreens.
  • "Not all inputs that contain numbers (credit cards, SS#, etc) should have an input type of number. They are best served up as text inputs, which are still free to use the pattern attribute to pull up the tel keypad on supported devices"
  • In general the number input should only be used if it's something you're going to do math operations on, you are unlikely to add and subtract account numbers and other numeric identifiers.

I just skimmed inputs as there will be future lectures on this.

Which html standard do we use? W3C forks the WHATWG standard, with inconsistent updates. A summary of why there are two different standards bodies (WHATWG and W3C) is here. Unfortunately we can't rely on WHATWG standard either, as it's a 'living document' which translates to unversioned document.

Further research indicates the canonical resource to use in order to find out what works with which browser, should be MDN, with https://caniuse.com/ and https://devdocs.io/ as secondary resources. If you search for 'most popular screen readers' you find a list of products that primarily use IE unfortunately.

• profile4.html and profile4.css are an example to display a background image with a circle border.
  • this seems like a work around/hack and something we would never do in practice

Let's attempt to understand what is going on. With Chrome inspector open while displaying profile4.html, click on the div for large-profile-container, we see there is green padding on each side of the image and an empty spot exactly where the image is shown. Now examine the div for circle-avatar, the green padding covers exactly where the image is. According to this version of the CSS standard color or images can be attached to the padding with the background property, which is exactly what is specified in the circle-avatar selector. The border-radius property is obvious, just play with it's values to see how it curves the border.

The CSS for large-profile-container contains the line: "padding: 0 40%" which from MDN indicates: "When two values are specified, the first padding applies to the top and bottom, the second to the left and right." Examining the div large-profile-container again in Chrome inspector, on the bottom right where there is the box model diagram, we scroll down and see the padding-left, and the padding-right values are identical. What the percentage value has done, is taken the width of the parent div container: profile-container, and multiplied it by 40% to obtain those two values. Examining the div for circle-avatar (we're still in Chrome inspector btw), the padding-top value is exactly the same as the height of it's parent: large-profile-container because the CSS values for the circle-avatar div contains: padding-top: 100% which means multiply the height of the top container by 1 (100%) so you get the same value. Note the browser is automatically calculating this, these values will change depending on how you resize your page.

Another use for the background property is assigning a temporary color if you're scaffolding a bunch of containers by hand, just to see the layout.

The example in the lecture of adding a text-center class to everything can also be used as a debug helper, adding a brightly colored outline to html containers to see the container layout more clearly. As per MDN, outlines (as compared to border) do not move other components on the page.

The convention on how overrides are handled were briefly covered, so let's find out what they are and what exactly is cascading. First we look at MDN which reveals cascading is a process in which the winning declaration is chosen, since we need to consider both user stylesheets (apparently you can write your own CSS, I've just have been enforcing reader mode everywhere), the browser's default stylesheets, and the author of the website or document CSS. These can all conflict so the cascade determines which styling should 'win'. MDN doesn't really elaborate on the decision making process and points out the !important rule all 3 (author, user, user-agent(browser)) can use to try and override all other style sheets, and that the user defined style sheet has precedence. It's recommended as a CSS author to never use !important.

Checking google scholar we see there's a CSS thesis by HW Lie. Survey papers and thesis are better than any online tutorial since they will include design decisions.

• Let's quickly skim through the thesis
• The chapter specific to CSS is chapter 6 so we start there
• A few pages about syntax in detail, and a full listing of properties for CSS2
• A list of possible values for properties, '3em' which is a number with a unit. 'em and ex' are relative to the font sizes, much like our percentage example which was relative to the parent container this size will be expressed relative to whatever font size the user has selected for their browser/reading assistant.
  • these number/unit values can also express angles '90deg', time '10ms' (ie: how long to pause) and freq '3kHz' on the aural properties, which are used by screen readers/speech synthesizers.
  • functions can be values, apparently some properties accept a functional notation when using a string presents a naming conflict.
  • numerous other values are exampled, there's an interesting formal definition for what a pixel is considered to be in CSS
  • space or comma seperated multiple values are fine

An interesting statement about properties and their settings: "Nearly every CSS property has different rules for the values on its right-hand side and it is not much of an exaggeration to say that each property's right-hand side has its own specialized language"

This leads into inconsistent percentage values which we already ran into with the CSS-avatar hack: "Percentage values are similar to relative length units in the sense that they are relative to another value. Each property that accepts a percentage value also defines to what other value the percentage is relative. Most percentage values refer to width of the closest block-level ancestor but some refer to other values, for example, the font size of the parent element."

• Value propagation aka Cascading begins in Chapter 6.5.1
  • 3 principal mechanisms for propagation: cascading, inheritance and initial values
    • the cascade is a 'negotiation' between 3 parties (author, agent, user-agent(browser)) to ensure all element/property combinations have a value
    • if the cascading process doesn't yield a value, the parent element's value will be used
  • By default author declarations (the person who wrote the CSS) wins over user settings unless user marks !important in their style declarations
• Interesting note: the thesis author considered a scenario where users could all share style sheets for different sites on a peer-to-peer basis, too bad this didn't become a mainstream thing where I can use a browser extension and flip through dozens of competing designs for various sites.
• The section on creating boxes from elements describes the parsing process, making a tree, etc. It also suggests implementations can optimize processing by doing several steps in parallel
  • Apparently Stylo in Firefox's Quantum browser parallelizes their CSS rendering implementation. TODO: look at this implementation sometime
  • Design decisions are described for the '3 band' box model in CSS of Margin, Border and Padding.
    • note that negative values can be used for margin, to overlap boxes

The rest of the thesis is put on hold since we'll learn positioning in the upcoming lecture 'Advanced CSS'.

Browsers and other software have proprietary CSS extensions. These are defined as "A CSS feature is a proprietary extension if it is meant for use in a closed environment accessible only to a single vendor’s user agent(s)".

W3C maintains the official CSS standard as snapshots so we're left with the same problem as HTML where we have to rely on software documentation (if it exists), or MDN and https://caniuse.com to figure out which software supports what CSS features.

CSS grid is 'two dimensional' whereas Flexbox layout is 'one dimensional'. The standard gives this example of the difference, but it seems like CSS grid can reference all of row/column at the same time (adjust height, grow the row past the current row into another) and overlap elements, where Flexbox can only reference a single row/column at a time.

• Pitfalls with CSS Grid: The legacy mobile browsers still out there on old devices, and IE version 10 and 11 which refuses to die.
  • There's a workaround for IE using Autoprefixer CSS online, but you could also use javascript to check browser version and present a more barebones page for IE until it finally goes away.

Let's start with the canonical resource for these things (that will tell us what is implemented) which is MDN.

• We begin to read MDN web docs basic concepts of grid layout
  • all the examples can be done in one html file, resize your browser window when you check the changes to see how grid is responsive to your window resizing:

<!DOCTYPE html>
<style>
 .wrapper {
  display: grid;
  grid-template-columns: 1fr 1fr 1fr; }
  </style>
<div class="wrapper">
  <div>One</div>
  <div>Two</div>
  <div>Three</div>
  <div>Four</div>
  <div>Five</div>
</div>

• So far this is all pretty straight forward, the value 'fr' represents a porportion of whatever space is left inside a container, so 1fr and 2fr represents 1/3 and 2/3 avail space.
  • 2fr and 1fr is translated as: "this is twice as big as the other"
• minmax() function can also be input to repeat() as a parameter
  • repeat() parameters are repeat(num-of-repeats, the-values-to-repeat)
  • repeat(4, 1fr) will create: 1fr 1fr 1fr 1fr
  • repeat(2, 10px 1fr) will create: 10px 1fr 10px 1fr
  • repeat(2, minmax(100px, auto)) will create 2 tracks sized of min value 100px, and max value set to auto.
• The rest of the MDN page on basic concepts of grid layout are self explanatory, overlapping boxes and using property z-index to declare their display priority over other boxes, and refers to other guides with layout examples we can look at when we actually start building something.

Let's do some selector CSS exercises from: https://flukeout.github.io

• I get to level 8 and then get stuck on the small oranges in the bentos selections which I figure out are 'bento orange.small'
• I get to level 16 then get stuck on the :only-child selector, it seems like it should be plate:only-child
  • Answer turns out to be plate *:only-child
• Stuck on level 30, however typing in plate[for^="Sa"] selects the plate, so we know what to do: plate[for^="Sa"], bento[for^="Sa"]

• These recitation slides have exercises if you wish to practice flex, such as FlexBoxFroggy.
• If you're completely stuck on the last exercise:
  • flex-direction: column-reverse;
  • flex-wrap: wrap-reverse;
  • justify-content: center;
  • align-content: space-between;

See this chapter on functionals explaining how map/reduce/filter work.

• "functionals" aka first class functions, are functions that take another function as input and return a new array or value, leaving the old one unchanged
  • list.filter(function(item) { … });
    • filter function that tests each element of the array and constructs a new array from whatever values pass the filter
  • map also available in js: list.map(function(item) { … }) which applies a function to every element, returning a new array
  • list.reduce also available for a single output value, using an accumulator: list.reduce(function, init);

Clarity: JS arrays look and act like lists, but are just high level objects, not actually linked lists (or even real arrays for that matter).

These start on slide #58. The recitation slides have optional exercises to practice functionals/closures.

• 1. Create a double function. In the console I type:
  • function double(x) { return x * 2 };
  • a = [2, 3, 5, 7];
  • a.map(double)
• Let's try it with a lambda function/anonymous function literal
  • a = [2, 3, 5, 7];
  • a.map(x => x * 2);
• 2. Sum an array of values
  • So we want reduce
    • function getSum(acc, num) {return acc + num;}
    • a = [2, 3, 5, 7];
    • a.reduce(getSum, 0); (zero is the starting value of the acculumator)
    • Also works: a.reduce((acc, num) => acc + num, 0);
      • With arrow functions () => x is short for () => { return x; }.

This chapter on closures vs object will clarify these slides.

There's an example on slide #62 of pairs(list1, list2, f) function being passed another function as an argument in order to encapsulate the iteration and provide the ability to reference items instead of direct array indices to avoid mistakes. Closures are then covered, using a higher-order function which is a function that returns another function. If you're reading the slides in say, chrome or firefox pdf reader, right click inspect element or inspector, click on console and enter the code you see to understand what it's doing. Type 'hello;' into the console to see it's construction: f(msg){ console.log(prefix + msg);} which is how hello("arvind"); works. There's an explanation of closures here but for clarity let's refer to PSML:

..the concept of a closure, a technique for implementing higher-order functions. When a function expression is evaluated, a copy of the environment is attached to the function. Subsequently, all free variables of the function (i.e., those variables not occurring as parameters) are resolved with respect to the environment attached to the function; the function is therefore said to be “closed” with respect to the attached environment. This is achieved at function application time by “swapping” the attached environment of the function for the environment active at the point of the call. The swapped environment is restored after the call is complete.

There's a memoization exercise in the slides. This is usually covered in an algorithms class (or PAPL book). What it's doing is cacheing the previous computations in a data structure. When you use it for Fibonacci as in the example: memoizedFib(40), the memoized function calls fib(40), and stores the result in the array we declared. The next time you use that function, memoizedFib(40) to call fib(40) again it will look up the entry for index 40 in the array and then produce that previous value instead of having to do exponential work calling fib(40) all over again. Note that it is a generic memoization function, anything else that maps from one input to one output (like calculating factors, or primes) can use the same memoization function, such as newfactor = memoized(factor) or fastprime = memoized(sieve).

Mixins are briefly shown, which won't make any sense unless you've taken an OOP class or PAPL. You may not need the 'this' keyword.

We're asked to go to example.com, open the console, write three ways to select the "More information" link, one of which must be a traversal, and inside the white box add a third paragraph of text. Finally add a 2nd level heading before the paragraph we added. They have different solutions in the slides, notably the ('p > a') selector and using .children to walk the tree.

• Select <a> directly:
  • let link = document.querySelector("a");
  • link.href
• Traverse to <a>:
  • let parent = document.querySelector('p'); (grabs the first paragraph above the link)
  • let traverse = parent.nextElementSibling;
  • traverse.firstElementchild.href
• Another way to select the href:
  • let b = document.querySelectorAll('p');
  • this selects every 'p' and places into a NodeList, which we can access using array notation []:
  • b[​1].firstElementChild.href
  • b[​1].innerHTML also reveals the link
• Let's add a paragraph:
  • let firstP = document.querySelector('p'); (grabs first paragraph)
  • let newChild = document.createElement('p'); (create a new <p>)
  • firstP.appendChild(newChild);
  • newChild.innerText = "Here's another paragraph"

• Let's add the second level heading:
  • let newHeading = document.createElement('h2');
  • firstP.insertBefore(newHeading, newChild);
  • newHeading.innerText = "This is a new Heading";

Let's finish the slides from 6.170, starting on slide #77 for JavaScript events.

• JS is single threaded
• blocking is a problem for long running operations
• JS has a trad call stack and a message queue for events such as clicks, timers, etc.
• when the call stack is empty, the event loop evals FIFO (first in, first out)

Running their example shows how events (timeouts) are deferred:

function one() {
 console.log("one");
 setTimeout(two, 0);
 console.log("three");
 setTimeout(four, 0);
 console.log("five");
}
function two() {
 console.log("two");
}
function four() {
 console.log("four");
}

• setTimeout(function() {…}, ms);
  • Executes the given function after a min delay in milliseconds
• let timerID = setInterval(function() {…}, ms);
  • Executes the given function at a given interval in milliseconds
  • clearInterval(timerID) to stop the timer
• Listening for interaction events:
  • let elem = document.querySelector("h1");
  • elem.addEventListener("click", function(event) {…});
• Browser creates events + adds them to the queue when h1 is clicked
• When the event is processed from the queue, the registered listener function is called.

We're asked to change the font color of the h1 heading everytime we click it, and set a timer to change the background color of the 'Example Domain' box.

// returns a random color as string "rbg(x,x,x)"
function randomRGB() {
    let rgbArray = [255,255,255];
    let rgb = rgbArray.map( x => x * Math.random() );
    return 'rgb(' +rgb[0] +',' +rgb[1] +',' +rgb[2] +')';
}   

// change h1 to a random color on every click
let h1 = document.querySelector('h1');
h1.addEventListener( 'click', function randomColor() { 
    h1.style.color = randomRGB();
});

// timer to change Example Domain div background color randomly every second
let exampleBox = document.querySelector('div');
let timerExampleBox = setInterval( function randomBackground() { exampleBox.style.background = randomRGB();}, 1000);

• Event Handling Gotchas: Context
  • ES6 'arrow functions' are needed to access the variable counter inside the same function, or else that event listener function will create it's own environment/context

function Counter() {
   counter = 0;
   let b = document.createElement("button");
   b.innerText = "Increment me!"
   document.body.append(b);
   b.addEventListener("click", () => { counter += 1; console.log(counter); });
 }
counter();

Let's review the slides for web servers.

• A clear presentation of query strings, path, protocol, host and URL fragments
  • simple webserver is presented
  • let http = require('http'); is how you import modules in js (won't work in browser console without hacks)
• Intro to cookies/sessions

…and that's it. The rest of that lecture is a NodeJS project which we'll do later.

Let's review the slides for web APIs.

• REST: Representational State Transfer
• The idea is URLS identify a representation of a resource
  • Path hierarchies to imply structure
  • GET is the only 'safe' call guaranteeing data will not be modified
  • APIs provide a contract, add versions to API URLs when changing them
    • There will be many more lectures on good API design
• AJAX: Async Javascript XML (JSON these days)
• XMLHttpRequest() object is covered again
  • xhr.onload = function() { let response = JSON.parse(xhr.responseText); }
  • response handler is registered before making the req as we already know
  • xhr.open("GET", "/teas/green", true); boolean is covered again, to make in asynchronous
• Continuations (callbacks) are discussed:
  • get returns immediately, but the callback function isn't run until the response returns
  • The continuation is called at some arbitrary point in the future outside the normal call stack
  • Cannot capture the result with a normal return statement
  • Side effects: introduce additional external state (let greenTeas;), set the state within the continuation
    • complexity increases
    • callback hell again
• Promises: a proxy object, returned synchronously, for a value that will be determined some time in the future
  • Also covered in Functional-Light JS with some better examples
  • Chains execute async operations one after another by returning a new promise within a Promise.then()
    • The idea is to start a new operation with the previous result

MDN has an article on Promises and their usage.

// Using Promises for XMLHttpRequest()

function get(url) {
  return new Promise(function(resolve,reject){
    let xhr = new XMLHttpRequest();

    xhr.onload = function() {
      resolve(xhr.responseText);
    };
    // Make the req
    xhr.open("GET", url, true);
    xhr.send();
   });
}

We're asked to load example.com again and using both nested callbacks, and chained promises:

• Set the body background color after 1 second
• Set the div background color 1 second after we set the body background
• Set the p font color 1 second after we set the div background

// nested callback method
setTimeout( function() {
  document.body.style.background = 'green';
    setTimeout( function() {
      document.querySelector('div').style.background = 'yellow';
        setTimeout (function() {
          document.querySelector('p').style.color = 'blue'; }, 
        1000); }, 
    1000); }, 
1000);

// Promises method
let setColorPromise = function(selector, property, color) {
   return new Promise(function (resolve, reject) {
     setTimeout(function() {
      document.querySelector(selector).style[property] = color;
      resolve();
     }, 1000); 
    })
}

setColorPromise('body', 'background', 'green')
 .then(function () { return setColorPromise('div', 'background', 'yellow'); })
 .then(function () { return setColorPromise('p', 'color', 'blue'); })

Note that the return statement is needed "Important: Always return results, otherwise callbacks won't catch the result of a previous promise". Try removing them and see what happens.

After reading the spec, the DOM we now know is literally the HTML document, modelled as various objects interacting with each other. Events are (usually) initiated by the user-agent (browser) and will in most cases, propagate up the tree by 'bubbling' until the event finds a handler that is listening for it. Since the DOM is the HTML, any attributes can be set, and document elements manipulated except for the shadow tree which are elements that are normally not able to be captured, such as the play button on <video> elements.

For example let's look at HTML video. See this question/solution here with the problem trying to get a local .vtt subtitle file to load in a browser. Note that browsers have security settings to prevent any loading of local files unless you manually override these settings. The solution was to encode the subtitles in the page as data and then create a URL object to attach to the <track> element as the new source of the subtitles.

The original HTML for embedding the video, to display locally that failed:

<video controls preload="none" width="800" height="600" poster="test.jpg">
<source src="test.mp4" type='video/mp4' />
<track kind="subtitles" src="test_EN.vtt" srclang="en" label="English"></track>
<track kind="subtitles" src="test_FR.vtt" srclang="fr" label="French"></track>
</video>

The solution in the HTML, was to give <track> an id, select it with Javascript, and fill in the <track> attributes to point to the raw data subtitles:

<video controls>
  <source type="video/mp4" src="videoFile.mp4">
  <track id="subtitle" kind="subtitles" srclang="en" label="English">
</video>

<script>
var subtitle = "V0VCVlRUDQoNCjENCjAwOjAwOjI4Ljg5NSAtLT4g...";
subtitle = window.atob(subtitle);
var subBlob = new Blob([subtitle]);
var subURL = URL.createObjectURL(subBlob);

document.getElementById("subtitle").setAttribute("src", subURL);
</script>

Since the DOM is the document modelled a different way, we can set the src to a new URL object that points to the data. In fact you could leave off all HTML attributes: <track id="EnglishTrack"> and fill in the rest of the missing attributes by attaching them to that element in the DOM. If you were to open this in your browser, and enable in DevTool settings: "show user agent shadow DOM" you would see the shadow tree in Chrome DevTools for the <video> element how it renders play buttons and other media controls.

Following the slides here

• git clone https://github.com/bamazap/url-shortener
• we're asked to implement listAll, updateOne, and deleteOne.
  • the API is documented in routes/shorts.js

function listAll(fields) {
    axios.get('/api/shorts') 
        .then(function (response) { return showObject(response.data); });
}         

function updateOne(fields) {
    axios.put('/api/shorts/'+ fields.name, fields)
        .then(showResponse)
        .catch(showResponse);
}

function deleteOne(fields) {
    axios.delete('/api/shorts/'+ fields.name, fields)
        .then(showResponse)
        .catch(showResponse);
}

Exercise, use express-session. We haven't covered this yet, I mainly went through github documentation. There is a web.lab lecture later on authentication, this is hacky unsafe auth for playing around purposes:

//add session middleware to app.js
const session = require('express-session');
app.use(session({ secret: 'dontUseInProduction'}));

//update routes/user.js to set the user's username in the session
//this is prob wrong, it worked anyway
router.post('/signin', (req, res) => {
    const sess = req.session;
    sess.username = req.body.username;
    res.write('Hello'+ sess.username);
    res.end('done');
});

//update routes/shorts.js to use the session username and populate creator field
//You add req.session.username anywhere there is a Shorts object being updated
router.post('/', (req, res) => {
  if (Shorts.findOne(req.body.name) !== undefined) {
    res.status(400).json({
      error: `Short URL ${req.body.name} already exists.`,
    }).end();
  } else {
      const short = Shorts.addOne(req.body.name, req.body.url, req.session.username);
    res.status(200).json(short).end();
  }
});

Step 6, we're asked to try/think about ways our toy url-shortener is insecure. For example anybody can rewrite the url's to redirect to their malicious ad site, and nothing is validated by our api. To see how to do this go through some of the SEI CERT coding standards for other languages how they whitelist inputs or look at the documentation of validate.js

• YouTube - Relational Model (Fall 2019 playlist)

This is a course about designing and building a disk storage db management system, but we're going to audit the first couple of lectures. Some set notation for relational algebra is used, this can be learned from Chapter 2.1 of this free book or watch some of this lecture, or read through this brief tutorial.

• Lecture starts around 18:30
• Some talk about problems of flat file/csv data implementations
• 'Relation' means a table
• A schema is a definition of what you're storing in the db
• A record (row) is a tuple, when you declare a function in a programming language (ex: def fun(x, y)) the parameters are also a tuple
• An attribute (column)
• In the example for primary keys, the entire key is Artist(id, name, year, country) not just the id number
• Foreign key is an attribute from one relation that maps to another relation (a different table)
• Relational Algebra is based on sets (union, intersection, difference)
  • Select: filter (\(\sigma\) sigma) in SQL: where clause
  • Projection: reorder/manipulate values (\(\Pi\) pi) in SQL: SELECT bid-100, aid, FROM R (select operator)
  • Union: R \(\cup\) R concatenate sets in SQL: UNION ALL
  • Intersection: R \(\cap\) R contains only the set that appear in both relations, in SQL: INTERSECT
  • Difference: R - S a relation that contains only the tuples in the first and not the second relation in SQL: EXCEPT
    • relations must have exact same type/attributes
  • Product: R x S a cartesian product, in SQL: CROSS JOIN
    • give all unique combinations/configurations
  • Joins: R \(\bowtie\) S generates all tuples that have a common value, in SQL: NATURAL JOIN
    • similar to difference except attributes can be different/not shared between both relations

His observations on performance/declarative way of doing queries are interesting, which lead into the reasons for SQL existing how it will generally be preferred to just do high level queries "Give me this answer" and let the dbms figure out the most optimized way to do so instead of giving a specific sequence of instructions.

• YouTube - Advanced SQL (Same 2019 playlist)

Let's learn something more than basic SQL, and see how doing simple things are difficult since no dbms follows the 1992 SQL standard, it's much like the HTML, CSS and DOM standards we looked at where every browser has a different implementation.

• Query optimizing is shilled as a high demand skill
• SQL name exists because IBM was sued for calling their prototype 'SEQUEL'
  • Structured English Query Language
• Current standard: SQL:2016
  • nobody actually follows the standard, except SQL-92 standard
• SQL is a collection of relational languages
  • DML: Data Manipulation Lang: insert/update/select
  • DDL: Data Definition Lang: define schemas
  • DCL: Data Control Lang: security auth
• Important: SQL is based on 'bag algebra'. No ordering, allows for duplicates.
• Aggregates: take a bag of tuples, return a single value
• ex: AVG (average), MIN, MAX, SUM, COUNT = the # of values
• Aggregate functions can only be used in the SELECT (filter) output list since you need to select first before running functions on said selection
• GROUP BY: Extract information about the aggregates we're computing
• HAVING: filters results from aggregates ie: only show me gpa > 3.0 whereas the aggregate is calculating the avg of all gpas
• Strings:
• Single quotes, usually case sensitive (except MySQL)
• LIKE is used for string matching '%' matches any substring (including empty), underscore matches any character
• SQL-92 standard defines numerous other string functions, operations like || (cat)
  • Postgres and Oracle most closely follow the SQL-92 standard
• Output Control
• We can dump the output to another table, if that table doesn't exist then our dbms will create it for us
  • again it's a declarative language, you tell it what you want, the system does it for you
• We can limit the tuple output, or order it with ORDER BY

Now we are covering what Prof Pavlo considers advanced SQL, such as nested queries. Everything in this lecture is documented in standards and dbms documentation, we'll just look at the high level overview for these queries. Have the slides open as you watch the lecture, pause to understand them then continue as he races through these topics in the lectures.

-Nested Queries

• queries inside of queries, take the output of one query use it as the input to another
  • query optimizers may rewrite the example query as a join
• ALL: every tuple must satisfy expression
• ANY: must satisfy for at least one row
• IN: equivalent to '=ANY()' (is there any tuple that equals my predicate)
• EXISTS: at least one row is returned
  • can be combined w/boolean: NOT EXISTS (nothing is returned)
• Can only reference outer query from inside query, not other way around

Numerous examples of nested queries are shown, and different ways to rewrite them. The high level as already stated you're taking the output of one query and using it as input to another.

-Window Functions

• Computing some function on tuples, like Aggregates but returns the tuple(s) and the value
• SELECT … FUNC-NAME(..) OVER(…)
• OVER() clause specifies how to slice up data (or sort it), can also leave blank
• PARITION BY to specify how to group things, OVER(PARTITION BY ..)
• OVER (ORDER BY ..) sorting results
  • RANK() produces the rank of the sort order

He goes over examples of this, you can open up a local sqlite3 or postgres install and try these queries yourself.

In later versions of 15-445, Prof Pavlo eliminated the lecture on normal forms entirely, mainly because 'people just wing it, and end up with 3rd Normal Form anyway".

YouTube - Normal Forms (2017 Playlist)

The first 10 minutes are a review of the prev lecture, which we skipped about lossless joins, dependency preservation, and avoiding redundancy while performing decomposition. They're good to know and I recommend it since you already know relational algebra, you'll easily understand it but for the sole purposes of MIT web.lab and 6.170 we're good with just a normal forms review.

• There's generally 6 normal forms, most common is 3NF/Boyce-Codd Normal Form(BCNF).
• 1NF (first normal form) is a single table with atomic attributes
• The 4+ normal forms are rare, information theory forms
  • By default you usually end up in 3NF
• 1NF: All types must be atomic, no repeating groups
  • can't have multiple values in columns
  • shouldn't repeat groups (customer-name1, customer-name2)
  • array types are actually valid, but from a theoretical standpoint this violates 1NF
• 2NF: Everything must be atomic, without repeating groups (1NF), but non-key attributes must depend on the candidate key
  • Removing more redundancy
  • Considered 'good enough form'
• BCNF and lossless joins will make no sense since we didn't cover superkeys by skipping lecture 4
  • 'decomposition' means splitting a relation (table) into other relations
  • There's problems with BCNF anyway and you don't want to use it
• 3NF: Preserved dependencies but may have some redundant data
  • As an amateur you'll probably just naturally produce 3NF
  • Prof Pavlo claims nobody actually designs a db schema this way (calculating normal forms)
  • You end up thinking in terms of objects
    • NoSQL drop all data protections (no ACID)
  • He argues mongoDB is still 1NF but with an array
  • Document data model (NoSQL) blurs line between logical and physical layer
    • Problems: you end up now specifying the physical layout of data, no longer declaration model
    • All the problems of the 1970s (System R) returns that were talked about in prev lectures
    • System R was abandoned, too impossible to maintain

Writing your queries in a declarative way is the 'best of both worlds' because the dbms will make the most efficient decision. The takeaway from these lectures is: use Postgres (or sqlite3 latest version for small embedded dbms), it's the dbms that most conforms to the SQL standard, use CTEs, unless you have a specific model that requires the document data model (NoSQL).

Let's try the sqlite3 assignment from this homework. Note make sure you get the latest sqlite3 version so you can use features such as window functions.

• Q2: List the longest title of each type, along with the running time, use the primary title ASC as a tie-breaker.
• We'll need a window function for this, since we want to rank, that way we get back all rankings that are 1 for each type

SELECT type, primary_title, runtime_minutes from ( 
 SELECT *, RANK() OVER(PARTITION BY type ORDER BY runtime_minutes DESC, primary_title ASC) 
  AS rank FROM titles) AS ranking WHERE ranking.rank = 1;

---
movie|Logistics|51420
short|Kuriocity|461
tvEpisode|Téléthon 2012|1800
tvMiniSeries|Kôya no yôjinbô|1755
tvMovie|ArtQuench Presents Spirit Art|2112
tvSeries|The Sharing Circle|8400
tvShort|Paul McCartney Backstage at Super Bowl XXXIX|60
tvSpecial|Katy Perry Live: Witness World Wide|5760
video|Midnight Movie Madness: 50 Movie Mega Pack|5135
videoGame|Flushy Fish VR: Just Squidding Around|1500

You don't have to write sql in all caps, it's just so the reader can understand more clearly what's going on. Try removing the primary title ASC out of the PARTITION BY and change ranking.rank to 2. Notice there are a lot of tvEpisodes rows returned all with 720mins running time. A tie-breaker is what you decide should be returned in a situation like this, and we were asked to use ascending primary title as tie breaker meaning alphabetical sort, choose the first result.

• Q3: Print type and number of associated titles, sort by number of titles in ascending order
• Remember aggregates from the slides

SELECT type, COUNT(type) AS cnt FROM titles GROUP BY type ORDER BY cnt ASC;

---
tvShort|4075
videoGame|9044
tvSpecial|9107
tvMiniSeries|10291
tvMovie|45431
tvSeries|63631
video|90069
movie|197957
short|262038
tvEpisode|1603076

• Q4: Print all decades and number of titles, constructing a string that looks like '2010s'. Sort in desc order with respect to number of titles.
• This is again right out of the slides and teaches you that you can group results by almost anything
  • sqlite uses it's own syntax for substring which you have to look at the documentation for

SELECT SUBSTR(premiered, 1,3) || '0s' AS decade, COUNT(premiered) AS cnt FROM titles WHERE premiered NOT null GROUP BY decade ORDER BY cnt DESC;

---
2010s|1050732
2000s|494639
1990s|211453
1980s|119258
1970s|99707
1960s|75237
1950s|39554
1910s|26596
1920s|13153
1930s|11492
1940s|10011
1900s|9586
2020s|2492
1890s|2286
1880s|22
1870s|1

As the date of this writing (Sep 8) I just helped finish somebody's homework since the solutions haven't been posted yet, though mine seems horribly inefficient and you could also do Q2 with JOIN. Working through and figuring out these few exercises will definitely help you learn the basics of SQL. The rest of that course can be completed if you take something like CMU's 15-213 or MIT 6.004 to learn about mmap, OS signals, etc.

Convert fireSale to a declarative function:

let toys = [
{name: "Woody", price: 10},
{name: "Buzz", price: 15},
{name: "Rex", price: 3},
{name: "Slinky", price: 7}];

function fireSale(minPrice, perc) { 
  return toys
  .filter(function(item) {return item.price > minPrice; })
  .map(function(item) {return {name: item.name, price: item.price * perc }})
}

Event streams treat events as a continuous stream of data rather than one-off occurrences that must be captured.

• filtering events based on their properties
• throttling the freq of events in a stream
• merging individual streams into a single stream
  • example of lightbulb state changing is given, to encapsulate in a pure function

Brief slides of an example of the model-view-viewmodel. The recitation github for Vue requires a school login, so if you want to learn more about Vue there's this. It definitely is a smaller surface area to learn and the community is large.

Some slides from 6.170 on how to attack systems and company protocols.

• Social engineering is also problem for domain management or hosting where somebody can claim to be you and reset credentials
  • Attaching a phone number as Two-Factor ID makes this easy to do via phone number spoofing, don't use phone numbers
• The evil 2018 World Cup chrome addon, in general browser addons are dangerous because they can be sold to adtech without you knowing and turn evil silently pushing obfuscated updates
• Classic phishing attacks talked about, such as breaking into RSA to attack Lockheed Martin
  • The introduction of Unicode in domain names opened up the web to phishing attacks, where you are fooled into using similar looking yet attacker run Signal/Telegram servers
• Backdooring the development toolchain happened recently with NPM package malware being distributed worldwide

Really the best defenses for employee protection are operating systems like QubesOS, where a VM is spun up in isolation everytime you load a browser or email app in order to prevent a phishing or other attack from getting the goods like VPN sign-in credentials on a developer's notebook sitting in their home directory. For 'trusting trust' manual updates to your packages and libraries (use lockfiles) in your web app not blindly running npm. Of course this is a double edged sword with the more security you add, the more complexity, the more opportunities for insecurity.

Another set of slides from 6.170 and why it's often easier to use a framework than roll your own, as you need to mitigate and be aware of the numerous classic web attacks.

• Advice from the slides:
  • sanitize and escape all inputs
  • limit sessions, use multi-level sessions for critical requests like password changing (most sites req you to enter the old pw to create a new one)
  • assume all requests can be out-of-order and replayed/modified, check every request
    • UDP protocol is a good example, if attacker can reply faster, they win (DNS is UDP)
  • encrypt cookies, use TLS
  • use prepared statements as in the db.query() example
  • form tokens to defeat CSRF
  • don't work around same origin policy and defeat your own mitigations

The slides recommend OWASP but it's a seriously disorganized, corporate driven org where most of the advice is either incomplete or outdated. Some of the security cheat sheets are OK such as the Top 10 but you'll be lost if you look into any of their articles on security. The book The Tangled Web ends each chapter with a checklist to follow if you're building an application and is probably still the best resource for timeless general advice, which mitigates some of these attacks. There's plenty of security-as-a-service startups around too with their own checklists and web app firewalls.

Some of the security slides are covered in the next lecture on Abstract Data Design.

In addition to the books mentioned, TrailofBits has a good list of topics such as lectures on web hacking, source code auditing.