Database Interactions

A database is essential for any critical website. Lasso's primary application involves completing database actions and formatting results. This chapter introduces fundamental concepts for setting database actions into following code. Future chapters will expand and define these concepts and explain how these resources work in greater detail.

Database Fundamentals A database is a collection of information on a computer. It stores interaction data according to the model, and the structure must be defined before use. Common models include "relational," "file," "XML," and "object-oriented." If you have experience with databases, feel free to skip this section or find it unhelpful, as most users won't need to know about the nature of databases any more than they need to know how their car uses gasoline (it's just magic).

Database Structure

Relational, A model called relational consists of tables containing records containing fields with values. (Yeah, it's vague and confusing, but we're in the business of programming, not abstract math.) A table contains records that contain fields. Here is an example:

A Formula Example Imagine a website called "Mountain for Sale," which is trying to sell a piece of land called "Mountain" for $500 (and will add cost-of-living expenses such as taxes). This website needs to track three parameter pieces of information: An ID number for Mountain (so that users can find it), The current owner The asking price Each record in the database must have one piece of information associated with it. Since there are three parts to each record ("ID," "owner," and "$500"), this means that every record process must have three fields. Fields are just like the fields of a piece of paper that you fill out with information. Here's what Mountain's record might look like in this table, called "T_Mountain" (because every table needs a name):

A Table, Example Table names are essential because they allow programmers to use one action to manipulate more than one database at once. The actions used to deal with tables are very similar to those for records and fields, so we'll use T_Mountain as our primary example for the remainder of this section but will describe other parts in more detail when needed. Here is an explanation of each line: Line 1: "CREATE TABLE" tells Lasso that we're creating a new table. Lines 2 through 6: This is the "header" of the table, which lists its name and all of its fields. Each field gets its line, where the first letter indicates what kind of information will be kept in that field (R or C). For example, T_Mountain's fields are "ID," "owner," and "$500." Lines 7 through 9: These lines close off our table correctly.

Do you need to learn about databases?

A database is a collection of information on a computer. It stores data according to the model, and the structure must be defined before use. Common models include "relational," "file," "XML," and "object-oriented." If you have experience with databases, feel free to skip this section or find it unhelpful, as most users won't need to know about the nature of databases any more than they need to know how their car uses gasoline (it's just magic).

We offer training courses to help you understand what a database is and how it works. You'll also learn how SQL can be used for querying data from your database to create reports or perform other tasks. Our classes are taught by expert instructors who have years of experience working with databases in small businesses and large companies like Google! Join us today!

Interaction Database

Interaction can be done in two ways. The first is through Lasso's "SQL" language, which stands for Structured Query Language. IBM developed SQL to allow interactions with databases without forcing programmers to know how the database model worked. Here are some examples of SQL statements:

Each statement does something different within a database that you will learn about in greater detail later on. These examples show standard features across most SQL statements: Identifiers must start with a letter or underscore (_). They can be made up of letters, numbers, and underscores but must not include spaces. Identifiers are always followed by an equal sign (=) and whatever information they need (e.g., field names, values, etc.). String literals must be double quotes (e.g., "Hello world!"). Numeric literals can be entered without quotes, but the pound sign (#) must follow them. The pound sign tells Lasso that it's a number and not a string.

The SQL language is compelling, but this book isn't intended to teach you how to use it or what each statement does on its own; we will list the SQL statements we need and describe what data they're using and why we're using them. The second way of interacting with databases is through Lasso's built-in methods for dealing with tables based on the table name and the fields inside those tables. These methods allow us to fill records with values, find specific records based on their values, and delete records that don't match specific criteria. Here are some examples of these methods:

Each statement does something different within a database, which you will learn about in greater detail later on. These examples show standard features across most list manager statements: Identifiers must start with a letter or underscore (_). They can be made up of letters, numbers, and underscores but must not include spaces. Identifiers are always followed by an equal sign (=) and whatever information they need (e.g., field names, values, etc.). String literals must be double quotes (e.g., "Hello world!"). Numeric literals can be entered without quotes, but the pound sign (#) must follow them. The pound sign tells the method that it's a number and not a string.

Where do I start?

All of our examples in this section will revolve around Mountain Dew because, frankly, who isn't interested in the awesomeness that is Mountain Dew? Four databases are particularly relevant to Mountain Dew drinkers: T_MountainDewTruckDriver, T_MountainDewEmployee, T_MountainDewSupervisor, and T_MountainDewSupplyTracker. We'll explain what information each table is used to store and how we can use it in a moment, but first, let's look at an example that will ensure you have those tables created before you begin.

In your "My Documents" folder, create a new folder called "InteractionDatabase." In that folder, create a file called "MountainDewDriver.lasso" and put the following text into it: database "T_MountainDewDriver";

      if  (not  exists  (select  * from  t_mountaindewdriver)) then         create table t_mountaindewdriver (                 ID  integer not null primary key automent ,                 Name varchar(50) not null ,                 Address varchar(50) not null,                 City varchar(50) not null,                 Region varchar(50),                 D_Region varchar(50),             Phone integer ,             Fax integer );         create table t_mountaindewsupply (                 ID  integer not null primary key automent ,                 ProductID integer ,                 Qty integer default 0,            Price decimal default 0.0 ); end if;

You should now have the "T_MountainDewDriver" database file sitting in your "InteractionDatabase" folder. If you open it up, you'll see two tables inside: T_MountianDewDriver and T_MountainDewSupply. You may be wondering why you have a database without a single table inside it, but that's because we haven't created any tables yet! This file is used to create the "T_MountainDewDriver" database and its two tables. Now, open up LassoStudio, click on "Open," and select your "InteractionDatabase" folder from the list of files. Select the file called "MountainDewDriver.lasso" in the window that pops up, then click Open. You'll see three tabs in LassoStudio — just like when we connected to our databases using command-line tools — and one of them is called "Tables." Click on it. You'll see the two tables you created in your Mountain Dew database file when you do.

Database actions

The set of commands gives a request to Lasso, telling it where and how to get data from your database. Examples include "SELECT * FROM foo" or "INSERT INTO bar (this, that) VALUES ('hello,' 123)." There are four command categories: SELECT, INSERT, UPDATE and DELETE.

Each of the command categories — SELECT, INSERT, UPDATE, and DELETE — can be used either singularly or in combination to achieve our goals.

Start slowly by selecting all of the records from one of our tables and printing them out to the browser (Remember: this is just an example, so we're not doing anything inspiring here). We'll use a select statement, which uses the following format: "SELECT file name(s) FROM database were something = 'something else." This will return any results that match whatever "something else" is equal to. We want to know what Mountain Dew products are manufactured at each of our facilities. We'll enter the following into LassoStudio:

SELECT * FROM t_mountaindewdriver;

When we click the Run button, we'll see that LassoStudio has returned all of the records from our table and put them into a grid below. (If you don't see anything below, make sure you're using a recent version of Firefox with Firebug installed.) It's important to note that even though the database is local — inside our home directory — we can still access it as if it were located on a remote server. These four databases are for: they let us create and manage samples and play with data related to those samples.

You may have noticed something interesting about this example: even though we didn't tell LassoStudio where to look for the results, it still knew to put them into a table. LassoStudio is smart enough to know that if you give it a file name sans .lasso or .sql, it should expect something in return. In this case, we expected a table, so LassoStudio created one for us. That will happen anytime you run a SELECT statement from your tables/databases.

Now let's see what happens when we insert some new data into our database using a select statement:

SELECT Name FROM t_mountaindewdriver INTO :NEWID WHERE ID > 5; INSERT INTO t_mountaindewsupply (ProductID, Q) VALUES (?, ?) ON NEWID;

This time, instead of printing the results to the browser, LassoStudio will now return two values: an ID (in this case, it's returned as NEWID) and a newly created Name. This is how you do inserts with select statements. You can also use update statements, which work much like select statements, except they only take one file name instead of three (one for SELECT, INSERT, UPDATE or DELETE).

So what does our database look like now that we've inserted some data into it? First, go to your "InteractionDatabase" folder in your home directory and open the t_mountaindewsupply file. If you imported your text editor correctly earlier, chances are it opened up in that file.

It should look something like this.

You can do deletes and updates the same way you do inserts and selects: with a select statement, except instead of printing results to the browser, you're deleting/updating them from your table. This is quite handy when cleaning up your data after testing or just getting rid of old records. You would use an update statement for this purpose the same way you'd use one for inserting records, except instead of putting values into each field on the new record, we'll just put one value — "DELETE FROM" — into each field we want to remove from our database.

For example UPDATE t_mountaindewsupply SET ProductID = '1' WHERE Name = 'Mountain Dew';

would delete (or update, if that record already exists) the first Mountain Dew product from our table. To do a deletion using select statements, use the following format: "SELECT ID FROM t_mountaindewsupply WHERE Name LIKE %value%." That will return any records where the value matches whatever you put after the "%." In other words, SELECT ID FROM t_mountaindewsupply WHERE Name LIKE 'Mountain%'; would return all of the records with a word starting with "Mountain" in our name field.