 Now, to actually create a table, we first have to get a connection to our database using DAO, and then once we have our database connection, here we call it DB, we then invoke on it the method define underscore table. And with the first argument we specify the name of the table we're defining, and then after that we have any number of field objects representing the columns of this table. And for each field we first specify the name and then the type. So here we're defining two tables, the first called cat, the second called cat underscore owner, and in the cat table we're giving it four columns, a column name of type string, a column age of type integer, and a column weight of type double, and then also a column cat owner, which is a reference to the cat owner table. So this is a foreign key pointing to the primary key of the cat owner table. So in the cat owner table we don't define any primary key because as I said the DAO will automatically give every table a primary key column called ID, which is an integer. So both the cat table and the cat owner table here have primary keys, we just don't explicitly declare them. Having now defined these tables, the connection object DB will have an attribute for each table we defined. So there's now db.cats, which returns the table object representing that table. And db.cat underscore owner returns the table representing the cat owner table. And within each table object you have an attribute representing each column. So within the cat owner table there's dot name, which returns the field object representing that column. So do note there's a bit of a strange asymmetry here because whereas we create actual field objects, which we pass to the defined table method, we don't create any table object. It almost would make more sense if we were to create a table object first and then pass that to the defined table method, but that's not how it works. I imagine simply for the sake of conciseness. Now be clear that the end effect of invoking defined table is to, one, actually create these tables in your database and also to create these table objects which you can then interact with in your Python code. The strange part about this is that you will invoke the defined tables method to define your tables in every request, which sounds odd because wait, you don't want to recreate the tables in the database every single request, that doesn't make any sense. Once we create the tables the first time we didn't just want to use the existing tables. So what actually happens is in your application there's a subdirectly called database in which are kept files describing the tables of your database, such that the Dal knows when you invoke defined table whether or not that table already exists and whether therefore needs to actually then create them in the database or not. Only when we use defined table to create a new table with a new name does the database actually then create a new table. Likewise, we can modify one of our existing tables by simply say here just removing one of the fields from the cat table or adding in another field, the Dal will then see that the table as you just find it differs from how it's defined in the database directory and so it will then actually alter the table in the database, which in SQL is done with the alter table statement. Now in the terminology of databases when we take an existing schema and we modify or remove one of the existing tables or one of its columns, that's what's called a migration. It's an altering of the schema. I believe it's called a migration because usually it's used in the context of taking data from one database and putting it into another so there's like migrating from one database to the next, but the term also came to be used in reference to just altering an existing database itself, not just moving your data from one database to another. Basically, a migration means we're changing the structure of the data and that can be problematic because sometimes you can't just take say an existing column and convert all of its values from one data type to another. So while in some circumstances we can successfully and without error performance called a auto migration, basically a migration that's done for us automatically, but in other circumstances we can't get away with an auto migration because the database is not smart enough to handle the changes for us or we're going to end up losing data if we try and do an auto migration. So by default in Web2Py auto migrations are enabled but there's a configuration setting where you can actually disable them or you can actually disable them on a table by table basis. The defined table takes an optional named argument migrate. If you specify an argument of migrate equals false when you define a table that will stop the dial from even trying to create or alter the table as it exists in the database. It will just assume that the table is as you defined it with the define table method. So the problem there though is that there might be a discrepancy and you might get an error later on when you try and actually use that table if it doesn't conform to the definition as you laid out with the define table but at least it doesn't sure that you won't harm your data so that's why you would set migrate equals false. So to restate what defined table does it creates a table object and assigns it to an attribute of the connection object so like db.cats here but then also it will actually create that table if the table doesn't already exist or it will modify the existing table of that name if it doesn't conform to the definition we just specified with defined table. Now once we have our table we want to put data in it and we do so with the insert method of the table object. So here we're invoking db.catowner.insert and the insert method expects a named argument for each column so our cat owner table has two columns name and address both strings so we provide a name argument and address argument and this inserts a new row in the cat owner table and remember that every table implicitly has a primary key column named id of type integer but that's automatically set so we don't specify an id because the database is picking an id for us. The other notable method of the table object is drop which when invoked will actually remove the whole table from the database. Of course removing tables isn't something you commonly do in the normal course of an application but it's there if you need it. Now to select update or delete rows from your database you need to first create what are called query objects and the primary means we have for creating queries is not to invoke the query constructor itself but to actually use these overloaded operators of the field type. The field class has operator methods for the quality operator the not equality operator less than greater than greater than equal to less than or equal to so when you use those operators and the left operand as a field object what you get back is a query object. So we have three examples here the first expression db.cat.id greater than zero well first db.cat gets you the table object and in that table object we have a column id which is the primary key of that table so db.cat.id is the field representing that column and as i just said the field type overloads the greater than operator so given the way overloaded python operators work this is actually invoking the method double underscore gt double underscore of the field class and it's passing to it first the field itself and then this the second argument the value zero and what the method returns what this expression evaluates into is an as yet unperformed select delete or update statement in which we are selecting the rows from cat where cat.id is greater than zero so be clear this just creates an object representing the as yet unperformed select update or delete likewise in the second line first db.cat.id that's a field object representing the id column of the cat table and when we use the quality operator on a field object when field is a left operand that's actually invoking the method double underscore eq double underscore of the field class and to that is being passed first the field object itself and then the argument four so what we get back is a query object representing all the rows in cat where the id value is four which of course should be just one row because multiple rows aren't supposed to have the same id id is supposed to be unique in the last example here we're again using the quality operator except now both arguments are fields not just the left operand db.cat.catowner that is the foreign key column cat owner in the table cat and db.catowner.id that's the id column in the cat owner table so the query object that results represents a selection of rows from the joining of these two tables because we're involving columns from two several tables so of course they have to be joined first so it doesn't enter join between cat and cat owner and then we're filtering from that join the rows in which cat.catowner equals cat owner.id so note that the thing a bit peculiar about how we express queries in the dowel is that the joining of tables is sort of left implicit it's like we're just specifying predicates to filter and then whether tables get joined together that's that's just implied from whether there's more than one table involved if you just have one table no tables need to be joined but because the expression here involves two tables both cat and cat owner well they have to be joined first so in the query the join is implicitly there now to actually use these queries to do an actual select update or delete we first have to wrap the query object in a set object because it's the set class which actually has the methods select update and delete which as i discussed earlier i believe is actually just a mistake in the api the query object itself should have just had these methods itself but for whatever reason this unnecessary complication made it in in the early days of web2py and that's stuck around since then so here in this example code the first line we're creating a query object with the expression db.cat.id equals two remember db.cat.id that's a field object the equality operator then is overloaded for field objects and that method actually returns a query object which we're assigning to the variable query the parentheses around the expression here are not necessary just included them for clarity and then in the next line the way you get a set object wrapping the query is you pass query to an invocation of the connection itself it's kind of strange you don't pass it to a set constructor rather our object representing our database connection db is an instance of DAO and the DAO class includes a method called double underscore call double underscore and that's effectively overloading the function call operator itself which is a pair of parentheses so when we invoke the connection object db as if it were like a function what we're actually doing is invoking that call method and so query is being passed to that call method and what it does is it returns a set object wrapping the query so not only is this a step which should be unnecessary it's done in a very strange way the justification for this oddity I assume is that at the time it seemed like the syntactically most compact way of expressing this common operation rather than having to create some other method to which we pass both the db object and the query object you could just invoke the connection object itself and therefore not have to pass it in also as an argument but again for whatever reason that was mistake because the query object already contains information about the connection because these field objects belong to tables and tables belong to connections so the query object should have encoded within it knowledge of what the connection object is so we don't need to introduce it this in this very convoluted way either all the information that's needed is there in the query object and I guess for whatever reason when Massimo first created Wattupai he just didn't see this and so he missed this opportunity and introduced this unnecessary complication which is just stuck around but anyway so we have our set object and now we want to actually perform a select update or delete well say we invoke the delete method that then performs a delete of all the rows in the cat table where cat id equals two if instead we were to invoke update we would specify named argument for each column we want to update and so we'd write lives equal eight to set the lives column to the value eight name equals fluffy to set the name column to the value fluffy so this invocation of update will modify all the rows in the cat table where id equals two which again is presumably just one row and so the values of lives and name in that column get updated do the values eight and fluffy respectively now with the same query object if we were to invoke the select method with no arguments that would perform a select and return the results as a rows object and because this would be a select over the cat table in which the id column equals two that should again be just one row and to get the individual rows from a rows object we use the subscript operator just like a list so rows subscript zero here will return the first row the first and only in this case and then with the row object we can then access the columns by an attribute of the same name so row dot lives here is an expression that should evaluate into whatever value lives has in the column where the id equals two now if you don't want to select to return all of the columns you can specify which columns you want by passing field objects to the select method so here we're just selecting for the columns name and lives of the cat table and so if we then try and access some other column in one of the raw objects well that will throw an exception because there is no such attribute because we didn't request it in a select so here row dot wait should throw an exception because the row has no such a tribute because we didn't select for that column lastly an important thing to keep in mind about row objects is that if the query involved multiple tables well then you might get name collisions between columns of the respective tables so when your query involves multiple rows the column values get stored as attributes in storage objects named after the tables so in our row here to get at lives of the cat table we we write row dot cat dot lives row dot cat here is a storage object representing all of the columns of our query from the cat table for the sake of more complex queries the query class overrides the ampersand and pipe operators which effectively then are the and and or operators respectively so if you want to and two queries together you connect them with the ampersand operator if you want to or them together you connect them with the pipe operator and then query also overloads the tilde operator to negate a query but this is a unary operator not a binary operator so it always has just one query argument so we're taking the query object representing cat dot cat owner equals cat owner dot id and then we're negating that so we're filtering for the rows in which cat dot cat owner is not equal to cat owner dot id so expressed in terms of SQL that would look like cat inner join cat owner there's an inner join because there's two tables here right cat and cat owner and then the filtering predicate is cat dot cat owner not equals to cat owner dot id and that less than greater than sign that's just the not equals operator in SQL syntax so just be clear what's going on here we're creating a query object with the equality operator from these two field objects and then we're using the tilde operator on the query object because the query class overloads the tilde operator to act as a negation operator and that negation operator the tilde operator returns a new query object representing the negation of the query that we created with the equality operator in the second example here first we're creating two separate query objects with the equality operators first cat dot cat owner equals cat owner dot id and second cat dot name equals mittens so we have these two separate query objects and then we combine them into one query using the ampersand operator which produces a new query a third query object that represents the inner join of cat and cat owner with the filtering predicate of cat dot cat owner equals cat owner dot id and cat dot name equals mittens so it's a composite predicate here with two conditions two equality tests and both must be true for the whole predicate to be true so it's selecting for only those rows where cat dot cat owner equals cat owner dot id and cat dot name equals mittens any row in the joint table where that's not the case gets excluded from the result set of rows understand that in this example here the parentheses actually aren't necessary because the equality operator has a higher precedence than the ampersand operator i just included the parentheses for clarity now our third example here is exactly the same as our second just we're using the pipe operator instead of the ampersand and so in the effective query instead of the and operator in the sequel it's using the or operator so it's those rows in which cat dot cat owner equals cat owner dot id or cat dot name equals mittens so as long as either one of those is true then the row is selected only when both are false does the row get excluded from the result set and finally in our last example we're taking that same query but we're just negating the whole thing and note that does require placing the whole thing in parentheses and then putting the tilt in front otherwise we're just being negating that first query the cat dot cat owner equals cat owner dot id we want to negate the whole thing so note the effect of this in the sequel is to take that whole predicate expression and then just apply the not operator to the whole thing so the end effect of this is to filter for precisely the opposite set of rows from those selected by the third query so the logical operators of the query class ampersand pipe and the tilde the negation operator be clear they take in query operands and return a new query object that's why in the first example here actually those parentheses are required because otherwise the tilde would be operating upon not a query object but the first field object there db dot cat dot cat owner which is not valid because the field operator doesn't have a tilde operation what's happening there is the equality operator of field is producing a query object and then the negation is operating upon that query to produce a new query now when combining queries using ampersand pipe it then becomes possible to get a query involving more than just two tables and in that case what happens simply is that all the tables are joined together and then all the predicates are applied so here for example first it's again cat dot cat owner equals cat owner dot id but then the second query we're adding to that is dog dot name equals phyto so what happens is first the tables cat cat owner and dog are all joined together and then we filter with the predicate cat dot cat owner equals cat own id and dog name equals phyto so the simple rule is that however many tables are involved in the query they just get joined together first then all the predicates are applied now in the usual case of a request in our model we open a database connection and if in the course of a request we need to do any sort of database business we use that connection to perform selects updates deletes inserts and so forth and then at the end of a request when we return a successful response web2py will then automatically commit the transaction if for whatever reason we need to explicitly commit our transaction we can do so by invoking db.commit assuming of course db is our connection object which is by convention and web2py what we normally call our connection object we normally sign it to a variable db and then likewise if we want to explicitly rollback our transaction to undo any changes we've made we can invoke db.rollback and as i mentioned earlier there are circumstances where a rollback will happen implicitly if an exception other than the http exception is thrown and not caught in the course of processing a request then an automatic rollback is performed on any open database connection so it's not common that we invoke these methods explicitly but occasionally there are cases when we would call them directly