 Now, speaking of databases, we can finally talk about model modules. Earlier when we laid out the whole lifecycle of how Web2Py handles a single request, we skipped over one step, and that is that before the controller module is executed, the model modules of the application are executed. So what purpose are model modules supposed to serve? Well, mainly their purpose is to be the place where we put the code that defines the database schema and opens any database connections we need. That's why they're called the models, because in MVC architecture the model is the area concerning everything to do with the data of the application, the data persistence and storage. In Web2Py, though, these models tend to be also the place where we set any configuration variables our application might use, and the reason we glob that stuff together with the database stuff is, well, it's just convenient. That's because any name which we define in these model modules, any name you create in the namespace, if you define a function or if you assign to a variable, Web2Py takes those names and automatically imports them into your controller module and also into the views that get executed. So, for example, when we open a database connection, we typically assign the object representing that connection to a variable, which we usually call DB. And then in our controller module, we can simply refer to DB, and it's just there implicitly, because Web2Py makes sure that anything that you define in your model module is going to get carried over. And in cases where more than one model module gets executed, then all the stuff defined in the earlier modules will be available in all the later modules. So if I have model module A and model module B and A runs first, well, then anything we defined in A will be accessible in B, and we don't have to explicitly import anything. Web2Py makes it happen automatically by the means that we discussed earlier where it will execute models using the exec statement and the exec file function rather than use the usual import statement. So to sum up this automatic importing business that Web2Py does, first, the model modules run, and everything you define in one of those modules is visible in every module that runs subsequently thereafter. And then after the model modules, the controller and then the view modules execute. And while everything from the models is visible in both the controller and the view, the definitions in the controller module are not carried over automatically into the view. The only way things pass from the controller to the view is when you put them in the dictionary, which you return from the action function. Now the question that remains is, for a particular request, which model modules are going to run? Well, first off, all the model modules are included in the model's subdirectory of your application directory. And all the modules directly in that directory are always run for every request. However, any modules placed in a subdirectory of the model's directory only the directory with the name matching that of the controller and the action of the request, only the modules of that one subdirectory of the model's directory get executed. So for example, the modules in the subdirectory models slash foo slash bar, those only get executed in a request where the controller is foo and the action is bar. Similarly, any modules found in just models slash foo, those get executed when the controller is named foo no matter what the action is. Now finally, as for the order of execution of these model modules, well, within each directory, they are run simply in alphabetical order and the order of the directories is that first the model's directory itself is always run first, then those modules, if any, in a subdirectory matching the controller name, and then if that directory has a subdirectory which matches the action name, the modules in that directory will run. So for a single request, at most the modules of three different directories will run and execution always starts with the model's directory itself and works downwards through the subdirectories. Now I would say in a large majority of applications, you're not going to want to have any controller or action specific models. You'll just have everything in the model's directory itself. And within that directory, you may not have much cause to have more than just one model module. You very likely may not have any reason to split that one file up into many, as long as it doesn't get too big. In my own Web2Py applications, I tend to start with just two model modules, the first for defining my schema and then the second for defining what's often called business logic. Basically, it's an API of functions I create to then use in my controller and view modules so that my controller and my view modules don't have to know anything about the internals of the structure of my database schema. How exactly a so-called business logic layer and the reasoning behind it, that's something we'll discuss probably in a supplementary unit in which we actually create a real Web2Py project. The component of Web2Py used to access databases, it calls the DAO as in database abstraction layer. And as the name implies, it's a library that abstracts over database access such that using this API, we don't actually talk to the database in SQL. The abstraction layer provides objects and methods which, when we use, usually gets translated ultimately into SQL. Now, I said usually because some databases which the DAO provides access to aren't actually relational databases using SQL. The DAO supports the database provided by the Google App Engine, which isn't a relational database. So when using the DAO to abstract over the Google App Engine, there's no SQL involved. The methods we invoke with the DAO will get translated into whatever the Google App Engine uses, I'm not familiar. It's something other than SQL, is the point. The only caveat to this is that because the Google App Engine is not a SQL database, it's not a relational database. There are some operations with the DAO which don't apply to the Google App Engine. So if you're using that as your back-end, certain methods just don't do what they normally do with other back-ends. In other words, the abstraction layer isn't perfect because all these back-ends, they can't be abstracted over in exactly the same way. For most of them we can, more or less, because they're all relational databases using SQL, but even there, there's some differences in what features they support. So for the most part, though, the DAO operates transparently no matter what your back-end is, that is, for whatever your back-end, it gives you the same apparent behavior. Now, at the moment, the DAO has support for a good number of different data back-ends, including SQLite, Postgres, MySQL, the Google App Engine, Microsoft SQL, Oracle, and a number of other options, though these are probably the most commonly used. Now, only one of these will really work out of the box when you install Web2Py without any further setup, and that's SQLite, because SQLite is natively supported in Python itself. In the case of MySQL, I believe support comes built in. If you're running on Linux, if you're running on Windows, though, I think you need a driver, you need a database driver. Postgres, you need to install a certain library to get Postgres working with Python, and it's basically a different case-by-case. So there's a little bit of configuration work to get those working with databases other than SQLite. Now, to actually use the DAO, the first thing you're going to want to do is create a connection. A database connection object is created with the class DAO, found in the module gluon.DAO. And to the constructor, we pass a so-called connection string. Database connection strings are just strings containing all the information the database needs to establish a logon. And for most databases, the connection string is expected in the form of a URI, like this one here for Postgres. And it's specifying first my username, Brian, with the password, my password. And then it specifies the host with the port, localhost, and port 5, 4, 3, 2, which is the default Postgres port. And then after the slash, we specify the name of the database. Because remember, for one instance of Postgres, that's a database management system, which potentially has more than one database. So you create a database within the management system, and that database has a name. So here, this is connecting to the database name thing. And assuming there's a user account, Brian, and this is the valid password, and assuming that account has access to a database name-to-thing, then this is a valid connection string. And Web2Py should successfully create a connection object. And we're assigning the connection object here to the variable named db. And that's a well-established convention in Web2Py is to always name it just db. The only reason you'd need to name your connection object variable something else is if you need more than one connection. Otherwise, usually in one request, we're just dealing with one connection. So we just always assign it to db. So just be clear that the format of the connection string is database-specific. So this is an example with Postgres. This is what Postgres connection strings look like. For other databases, most of them look quite similar, but it's really up to each particular database. What you see here are the primary data types that effectively make up the DAO. First, there's the DAO type itself, which as we said represents a connection to the database. Then a table object represents a table within our schema. Be clear, it's not a table that was returned by a query. It's the actual table that makes up a data type within our database. As you'll see when we use the DAO to define a table within our database, the object we get back representing that table is of type table. And then within each table of our schema, each column is represented by a field object. I suppose they went with the term field because it's more generic than column. Column is a relational database-specific term, whereas field is a more general term. It doesn't seem as out of place when used in conjunction with a non-relational database like the Google App Engine. Now, if we want to perform on our database a select, update, or delete statement, or the non-relational equivalents thereof for Google App Engine, then we create a query object. And the part that's a bit confusing is what is called a query in Web2Py is not really the same as in SQL, because in SQL a query pretty much refers to just a select statement, whereas updates and deletes are not queries. The query object actually represents the part common to those three different statements. So in a select statement, update statement, and delete statement, you have the part where you're specifying the joining of the tables and then optionally you have where clauses and other kinds of clauses. Well, those parts are all common to those three different statements, so they're represented together in a single object called query. Once you have a query object, if you want to actually use it to perform a select, update, or delete, you first wrap it in another class called set, and then the set object has three methods, select, update, and delete. And those methods actually trigger the real action. When you define the query, that's just the preliminary stage of defining what kind of select, update, or delete you're going to do later. Now, you're probably wondering why do we wrap queries in set objects to actually perform these operations. And the answer is there isn't any particularly good reason. In fact, I went on the message board and I raised a discussion of, hey, why does this thing exist? Why is there a set class? Why do we have to wrap query objects in set objects? And the answer is that, well, it just, in retrospect, seems to have been a design mistake. The creator, Massimo, said that at some point he suggested to the others that we should change this, and they said, now let's just keep it. So I would argue, if you want to understand what does the set class do, why is it called set? Well, the justification given for that is that the set somehow represents the set of rows which would be selected by the query. It doesn't make sense, to be honest. It doesn't really make any sense. So the answer is the set class doesn't do anything. The query class itself should have had the methods select, update, and delete, as it is they don't. So you have to wrap them in the set object which has those methods. But that, again, was a design mistake. The query object itself should have those methods. So the oddity of that will be clear when we look at a bit of code. In any case, when you perform a query, when you perform a select, what you get back is an object of type rows, rows plural. And that, as the name applies, represents a set of rows. It's the data returned by the query. And a rows object is just a collection of row objects, and each row object represents a single row within that result set. Now, one important purpose of the whole database abstraction layer is that it abstracts over the variance in data types, because databases have their own number types, their own string types, their own date types, and so forth. But in Python, we want to deal with Python types. We want an integer in our Python code to be a Python integer. Also, the whole point with a DAO is that it abstracts over different backends. And different backends, different databases have different, say, integer types and different float types and different text types and so forth. So to deal with this problem, Python itself has to define its own data types for the columns of the tables. So, here's a list of most of those data types. First, string and text are what you use most commonly to store text data, and whereas a text value can be up to 65,536 characters long, string values only go up to 512 characters, though actually in the case of string you can specify a different length. That's just the default. For cases where you're trying to store text in 65,536 characters, for that we use a blob. The blob type is generally used to store file content, binary data, but there's no reason that binary data can't be text data. As for booleans, integers, and doubles, those should all be self-explanatory. The only thing to keep in mind is that integers and doubles are both constrained to the range of negative 1 times 10 to the power of 100 up to positive 1 times 10 to the 100th power. If, though, you need greater range and or precision, there's also a type called decimal, which we'll talk about in the supplement. And a datetime column, of course, stores a datetime value. In Python, these are expressed as Python datetime objects. And then finally we have reference fields, reference columns. A reference field is a foreign key to another table. So, for example, if I want my column to be a foreign key to the cat's table, it's space cat's. And this only works because the dial automatically gives every table automatically and implicitly a primary key column of type integer and with the name id. So, when you create a reference column, it's always an integer foreign key pointing to the primary key named id of some other table.