Database basics for beginners
The techniques described in the Files chapter are fine for dealing with your app data in many cases. But there will be times when you need something faster and more structured. That's where a database becomes useful. A database is a structured way of organizing data that make it easier to find the data later. Databases consist of tables, columns and data.
The most common type of databases used with Xojo are relational databases, of which Xojo has built-in support for SQLite, PostgreSQL, MySQL, Oracle and Microsoft SQL Server. In addition you can use ODBC to connect to just about any other database.
Other types of databases include NoSQL databases. These are not discussed here, but there are open-source Xojo projects that provide support for several including Redis and MongoDB.
Tables, columns and data
A relational database has its data stored in tables. A table is a container for some common set of data. A table has one or more columns that define the data it may contain. Data is stored within the table as rows, with a value for each column. Each row is a single set of data. For example, you could have a table called Team that has these columns: ID, Name, Coach, City. Here is an example of the Team table with sample data.
ID |
Name |
Coach |
City |
|---|---|---|---|
1 |
Seagulls |
Mike |
Albany |
2 |
Pigeons |
Mark |
Springfield |
3 |
Crows |
Matt |
Houston |
The ID column is required by most databases to ensure that each row in the table is uniquely identifiable. This unique column is also often called the Primary Key.
Relationships
A database usually consists of many tables. And these tables are often related in some way, which is why these are called relational databases. For example, to track the players for each team you would have another table, called Player, that is related to the Team table. Here is an example of a Player table with sample data:
ID |
TeamID |
Name |
Position |
|---|---|---|---|
1 |
1 |
Bob |
1B |
2 |
1 |
Tom |
2B |
3 |
2 |
Bill |
1B |
4 |
2 |
Tim |
2B |
5 |
3 |
Ben |
1B |
6 |
3 |
Ty |
2B |
Notice that the Player table has its own ID column as well. But in addition, it also has a TeamID column. This column defines the relationship between Player and Team. It clearly tells you the team to which the player belongs. For example, you can see that Bob and Tom both have TeamID = 1. Looking in the Team table from above, you can see that the team with ID = 1 is the Seagulls. So both Bob and Tom are on the Seagulls team.
You can look up the teams for the other players using the same technique. The collection of your tables is called the "database schema" or "database design".
Database engines
A database design as described above is implemented using a “database engine”. This is the specific database product that is used to store your database design and its associated data. Xojo has built-in support for several database engines, which are usually just referred to as the database: SQLite, PostgreSQL, MySQL, Oracle and Microsoft SQL Server.
You are not limited to the built-in database support, however. You can also use many other databases using ODBC, 3rd party libraries, plugins or components.
There are generally two types of databases that you will use: embedded and server.
Embedded databases
An embedded database is a database where the engine is built into your app with the database usually in a file or series of files. You do not need to install any other software in order to access the database; the necessary software is embedded in your app. Embedded databases usually only allow a single user to access the data at one time because the engine is embedded in the app and there would be no way for multiple apps to coordinate access to a shared database file.
SQLite is one of the most popular databases in the world and is an example of an embedded database. This type of database is often used in apps such as Mail clients, web browsers, photo management software and anything else that needs to manage a lot of data but does not need to share it with others. You can also use an embedded database such as SQLite with web apps that have light to medium usage.
Server databases
Server databases are more powerful databases that often have many advanced features, the most significant of which is multi-user access. A server database allows multiple users to access the database at one time. Examples of server databases include: PostgreSQL, MySQL, Oracle and Microsoft SQL Server.
Other advanced features associated with server databases: stored procedures, backups, user login, permissions and scalability.
A server database is usually installed on its own dedicated server. Your app communicates with this server, either directly for internal apps running on a LAN or using a web service as an intermediary for apps running on the internet. Database servers are used by apps that need to share data among multiple users such as a payroll system, billing system or other business apps. Database servers are often used by large-scale enterprise apps and any other types of app that have multiple users or large numbers of users.
SQL (Structured Query Language)
Database engines all share a similar command structure, which is called SQL, which stands for Structured Query Language and is often pronounced as "sequel". SQL is the way to send commands to the database to do things such as create tables or get data from tables. For simple SQL commands, SQL is often the same across different databases. But as you create more complex SQL, you'll find that each database has their own slightly different variant of SQL. This can make it challenging to switch between database engines (such as SQLite and PostgreSQL, for example) because the SQL used to send commands to the database may be different.
Regardless, there are several common SQL commands that are always available, even if their specific syntax changes slightly depending on the database you are using. These commands are: SELECT, INSERT, UPDATE, DELETE, COMMIT and ROLLBACK. This section shows some example of generic SQL that ought to work across most databases.
When you write SQL, as you will see below, it often ends in a semi-colon (;) character which indicates the "end of the SQL command". This is optional with some databases, but a semi-colon will be used in code samples for consistency.
To learn more about general SQL, visit the W3Schools SQL Tutorial.
SELECT
The SELECT command is used to retrieve specific data from one or more tables. Using the Team tables from above, this command gets the names of all the teams:
SELECT Name FROM Team;
A SELECT command can also use a WHERE clause to limit the data retrieved from the table. This command fetches the data for only the team in Albany:
SELECT * FROM Team WHERE City = 'Albany';
You can combine data from multiple tables by "joining" them together. This command gets the name of all the players on the Pigeons by combing both the Team and Player tables:
SELECT Name FROM Player INNER JOIN Team ON Team.ID = Player.TeamID Where Team.Name = 'Pigeons';
With Xojo, you send SQL commands to the database by using the SelectSQL method of the Database class. The results are stored in the RowSet class, which you can then loop through to get at the data. This code gets the team names and adds them to an array, which you could then use to display:
// db is a database to which you are connected
Var rows As RowSet
Try
rows = db.SelectSQL("SELECT * FROM Team;")
Catch error As DatabaseException
'An error occurred so deal with it here
'The error message can be found in error.Message
Return
End Try
Var teams() As String
Var teamName As String
For Each row As DatabaseRow in rows
teamName = row.Column("Name").StringValue
teams.AddRow(teamName)
// Some database may require you to specify the encoding of the data you received.
// The code below sets the encoding as UTF-8.
// teams.AddRow(teamName.DefineEncoding(Encodings.UTF8))
Next
INSERT
The INSERT command is used to add rows to a table. Using the Team table, this command adds the Seagulls team:
INSERT INTO Team (Name, Coach, City) VALUES ('Seagulls', 'Mike', 'New York');
In Xojo, you send an INSERT command using the ExecuteSQL method of the Database class:
Var SQL As String = "INSERT INTO Team (Name, Coach, City) VALUES ('Seagulls', 'Mike', 'New York');"
db.ExecuteSQL(SQL)
UPDATE
The UPDATE command is used to modify existing rows in a table. This code changes the name of the Coach for the Seagulls in the Team table:
UPDATE Team
SET Coach = 'Mary'
WHERE Team = 'Seagulls';
Generally, you can only update values in a single table at a time. You can however update multiple values in the table at once:
UPDATE Team
SET Coach = 'Mary',
City = 'Boston'
WHERE Team = 'Seagulls';
In Xojo, you send an UPDATE command using the ExecuteSQL method of the Database class:
Var SQL As String = "UPDATE Team SET Coach = 'Mary// WHERE Team = 'Seagulls';"
db.ExecuteSQL(SQL)
DELETE
The DELETE command removes rows from a table. This example removes the Seagulls row from the Team table:
DELETE FROM Team WHERE Name = 'Seagulls';
Warning
Be careful to ensure that your WHERE clause is present and accurate. In the above example, if you did not include it, then all the rows in Team table would be deleted!
COMMIT, ROLLBACK (Transactions)
Changes to a database are made in what is called a transaction. This is a block of processing that either all completes successfully or none completes successfully.
When you are using a transaction, changes made to a database are not made permanent until you commit. This serves several purposes. First, it ensures that data integrity is always maintained. If an error occurs partway through some changes to several tables, you do not want the data to be missing in some tables. A failure allows you to revert everything back to its initial state, sort of like an Undo. Second, for databases that can have multiple users, changes made in a transaction are not usually visible to other users until the transaction is marked as completed (by committing). This prevents people from seeing data before it is ready. Lastly, using transactions can improve performance when adding or changing data in the database as the commit operation can be time consuming so if you do it less often (by only committing data that has been added in large chunks) you can improve performance.
Starting a transaction varies depending on the database you are using. Some databases start a transaction for you automatically and some require you to use a specific command, such as:
BEGIN TRANSACTION;
In Xojo code, you would use the ExecuteSQL method of the Database class to send this command:
db.ExecuteSQL("BEGIN TRANSACTION;")
Regardless, to complete a transaction you use the COMMIT command. To cancel (or undo) a transaction you use the ROLLBACK command. In Xojo, Commit and Rollback are methods on the Database class. You can them like this:
db.Commit
db.Rollback
To learn more about transactions, refer to the Transactions topic.