Mon Nov 8

First : discuss homework ... cgi scripts & your work.

Second : security issues ... cross site scripting and "code injection" ... discuss.

Third : start SQL. Let's first learn what it is and how to use it, then later add it to our web scripts - CGI or PHP or Flask.

I've posted an assignment for this week that asks you to try some some SQL stuff.

SQL

I'll show you how to work with sqlite3 from the command line, using the attached computer_store.sql file from the wikibooks sql exercises.

topics to discuss :

• working with sqlite3 from the command line: .sql files, .db files
• working with sqlite3 from python or PHP ... similar, using a library.
• tables, fields (column names), primary keys (unique id for a row), foreign keys (id from a different table i.e. a "relation" between two tables
• SELECT syntax , WHERE clause
• INSERT syntax
• combining results from several tables ...
  • with several SELECT statements
  • with embedded SELECT statements
  • with JOIN

what I did in class as an example

Showing how to to get name of products for a given company name - three ways.

$ sqlite3 computer_store.db
sqlite> SELECT Code FROM Manufacturers WHERE Name="Winchester";
6
sqlite> SELECT Name FROM Products WHERE Manufacturer=6;
Memory
Floppy disk
sqlite> SELECT Name FROM Products WHERE Manufacturer=(SELECT Code FROM Manufacturers WHERE Name="Winchester");
Memory
Floppy disk
sqlite> SELECT Products.Name
   ...> FROM Products JOIN Manufacturers
   ...> ON Manufacturer.Code = Products.Manufacturer
   ...> WHERE Manufacturers.Name = "Winchester";

aside

• making emacs popular again | discussion
• a terrible schema from a clueless programmer | discussion
• a CGI example : Jim's finite automata script

attachments

		last modified	size
	computer_store.sql	Mon Nov 08 2021 12:13 am	2.5K