Exploring the database

So you've got an instance and have logged in. Great! What can we see? The answer: database records.

Almost everything in ServiceNow is an entry in a database. When you look at the user interface, virtually everything you see-from the data typed in by a user to log files and how the views are structured-is stored in the instance's relational database. The scripts you write are kept in a string field, and the files you attach to records are stored in chunks, all in the database.

All this data is organized into many tables. A table is a collection of records, with each record being a row. A field is a column in that table.

Everything is built on top of this structure. You don't need to reboot the server to apply new functionality; you just update data records. You don't need to reload configuration files-any properties you set will be read on the next operation. Even the database metadata-information about the fields themselves-is stored in another table.

This gives you a great deal of control. You organize, configure, and manage almost everything the same way, searching for a script like you search for users: by simply querying the right table. This means you can focus on designing and building great business applications, since the platform just works.

Tip

ServiceNow may be considered a high-level platform based on the Model-view-controller (MVC) concept. When building a ServiceNow application, you can first think of the data structure. You determine what information you need to store and how it all links together, creating tables and fields. This is the model aspect.

Automatically, you get a simple view of this data, with forms and lists showing your information.

You can then build simple ways to manipulate and change the data, through automation and simple manual updates, giving you the controller.

Introducing the Gardiner Hotel data structure

One of the first things that many people learn how to do in ServiceNow is to add a field. This is a straightforward operation: you add the new field to a form or list using the UI. Under the hood, the ServiceNow platform performs, among other operations, a simple SQL command to add a column to the table you are manipulating. When you add a field, you add a column to the table. When you remove a field, you drop it. There is no magic-the platform interface just makes it easy to do.

Tip

ServiceNow allows you to add fields to every table in the system. If you decide that adding another is a good idea, you have the power to do so!

In order to work through the ServiceNow functionality, we will build a hotel management application for your new employer, Gardiner Hotels. It will involve building a simple data structure, but one that is highly interlinked.

Here is a representation of the tables we will create in this and the following chapters:

• Guests: The reason Gardiner Hotels exists! Our guests' details are the most important information we have. We definitely need their names and, optionally, their e-mail addresses and phone numbers.
• Room: This details where our guests will sleep. We store the room number and the floor it is on.
• Check-in: When guests want their room key, they check in. We record who checked in to a room and when and who made the entry.

  We will create a link to the Room table so we can easily see information about the room, such as which floor it is on.

• Reservation: Our guests like staying with us, and they often book months in advance. One reservation might be for many guests, especially if an entire family is coming. A big family might need a big room. We need to record where exactly they might stay.

Note

Over the course of the book, we will expand and further develop the application. Its primary use is to show you as much of the capability of ServiceNow as possible, so some of the examples may be better done in other ways.

Creating tables

Firstly, let's create an app to hold our configuration in. Applications are a container for custom configuration in ServiceNow. It's a great place to capture our work and keep everything we do logically separate. Depending on what configuration you do with it, there may be licensing implications to deploying it in production. But this application will stay safely on our sandbox developer system. Applications are discussed in more detail in the next chapter, and in Chapter 10, Packaging with Applications, Update sets, and Upgrades.

Throughout this book there will be examples to show off features of ServiceNow. To make them as easy to follow as possible, it's best to use the same application scope as me. To make this possible, you should import the very starting point of my Hotel application. At the moment, it's an empty shell.

The easiest way to import the application is through an Update Set. Follow these steps:

1. Download the Update Set from https://www.gardiner-hotels.com/sets/00-HotelApp.xml Save it to disk.
2. Navigate to System Update Sets > Retrieved Update Sets. Click Import Update Set from XML.

   Tip

   The convention for navigating through ServiceNow uses the following structure: Application Menu > Module. For modules with separators, it will be Application Menu > Section > Module. The easiest way to find a particular link is to type it in the Application Filter field at the top-left corner of the menu. Make sure you are choosing the right one, though, because some modules are identically named.

3. Click the Browse button, and choose the XML file you downloaded. Click Upload.
4. Click on the Hotel Bootstrap record, then click on the Preview Update Set button.
5. Once analyzed, click on Commit Update Set. Click Close once done.
6. If you navigate to System Applications > Applications, you should see the Hotel entry in the list.

   Tip

   An alternative is to fork my GitHub repository available at https://github.com/gardinerhotels/hotel.

7. Now, let's create our first table. Navigate to System Definition > Tables, and click New.
8. Fill in the following values, and click on Create, then OK to confirm. Some fields (like Name) will automatically populate.
   • Label: Check-in
   • New menu name: Hotel

   Note

   Notice the auto-generated table name. If you imported the Update Set, the name should be x_hotel_check_in. It's made up of several parts: the vendor prefix, the application name, and the table name itself. More about this in Chapter 2, Developing Custom Applications.

   
9. Click on Submit to create the table.

Tip

Many technical items in ServiceNow have a label and name that are used in the database and in scripts. In this case, the database table is called x_hotel_check_in, while an entry in the Field Labels (sys_documentation) table contains the mapping between the name and the label. I'll use the Label (database_name) format throughout the book.

Adding fields

Let's inspect the table that was just created. Navigate again to System Definition > Tables. Helsinki comes with over 2,000 different data tables, and all of them can be seen here. But we are only after one of them. Find Check-in (sort by Updated or filter the list) to see what has been made.

When you create a new table, you get some system fields. You may need to scroll down the Table form to see them. They include two dates (when a record was created and when it was last updated), two string fields (containing the user ID of who created it and who updated it), a unique GUID called sys_id, and a field that counts the number of updates to the record. They are all updated automatically, and it is generally good practice to leave them alone. They are useful just as they are!

The following screenshot shows how the system fields are represented within the tables:

Tip

The autogenerated fields are very helpful to the system administrator for finding records quickly. I always add the Updated on field to my lists, since it makes finding the records I've been working on (such as scripts) much faster.

In addition to these automatic fields, we need to create some of our own. We will need several, but right now, let's create something to store any requests that the guest may have. Perhaps they may have specifically requested a high floor. Scroll down the table form, and create a new field called Comments by double-clicking on Insert a new row.... Fill out the row, and save the record by clicking on the Update button. Either use the Tab key or double-click to enter the following data:

• Column label: Comments
• Type: String
• Max length: 500

The following screenshot displays how the list of columns looks before saving the changes:

Knowing what's happening

Once you've clicked the Update button, the instance gets busy. Behind the scenes, the application server is running SQL commands against the database. Specifically, at the time of creating the field, the following is executed:

ALTER TABLE x_hotel_check_in ADD `comments` MEDIUMTEXT

Tip

If you wish to see these commands, navigate to System Diagnostics > Debug SQL. This will place lots of information at the bottom of the page. Other diagnostic tools such as this are discussed in Chapter 8, Securing Applications and Data.

This demonstrates a key concept: whenever you perform an action in ServiceNow, it results in a string of database commands. The database is altered and the platform's internal state is updated. These actions are generally carried out quickly, with the whole process completed in about half a second. No downtime is necessary.

Introducing the dictionary

The dictionary is a metatable. It describes your table and fields-their names, how big they are, and any special attributes they may have, similar to information_schema views. For example, one field might be dependent upon another. A dictionary entry might also be referred to as an element descriptor, since it describes the how the field (or element) works.

The table is represented by an entry in the dictionary with a table value, type of Collection, and no column name. When the Comments field was added to the Check-in table, the platform also made a dictionary entry with the table and a column name. You can view it if you want by navigating to System Definition > Dictionary.

The options that are available in the dictionary depend on the type of field you are working with. Reference fields, which we will explore in a moment, have extra functionality that is controlled here, such as dynamic creation.

As we work through ServiceNow, we'll spot functionality that is enabled through the dictionary. However, much of it can be achieved in other ways, often in a better manner.

Older versions of ServiceNow have a read-only tick box available in the dictionary form by default. While it's included and can be used in later versions, it is a better idea to use security rules instead. They give you much more granularity and control than a binary checkbox. We will explore security rules in Chapter 7, Exchanging Data.

Tip

The easiest way to navigate to a dictionary entry is by right-clicking on the label of the field (not the text-entry box) and choosing Configure Dictionary. You can see some details about a field, such as which table it is in, by choosing the Show option from the menu that appears on right-clicking.

The globally unique identifier

The ServiceNow database is a relational database. This means that one data element can relate to another. To ensure that every record or row can be referenced easily, every record has a unique identifier: a primary key.

In ServiceNow, this primary key is something that isn't related to the data itself. It is a globally unique identifier or GUID. This GUID is stored as a 32-character string, made of hexadecimal characters (the numbers 0-9 plus the letters a-f). The number of unique GUID values is so large that the probability of two accidently being the same is negligible. This is an example GUID: 5137153cc611227c000bbd1bd8cd2005.

Tip

This type of identifier is sometimes known as an OID or object identifier. It has no special significance; it just uniquely identifies a data row. It can also be called a surrogate key.

Whenever you create a record in ServiceNow, the platform generates a new GUID. The characters generated are random-a mixture of several sources, including the date and time and details specific to the instance, meaning they're not sequential or predictable. The GUID is saved alongside the record, in a special field called sys_id. The sys_id field is heavily used in the ServiceNow platform-you will start seeing GUIDs everywhere!

As an example of how ServiceNow uses the sys_id field, conduct the following experiment: construct a URL similar to the one that follows, substituting <instance> with the name of your instance, and visit it with your browser: https://<instance>.service-now.com/sys_user.do?sys_id=5137153cc611227c000bbd1bd8cd2005.

In the new instance, you should happen across the user record of Fred Luddy (if the demo data has been removed, you will get a Record not found message).

Tip

If the system sees -1 as a sys_id, it'll assume it is a new record, and will generate a new GUID.

It is useful to examine the structure of the URL. First, spot the sys_user table. Then, spot the GUID. With these two items, the instance knows exactly what data it needs to pull up and present to you.

Every record has a sys_id field. It is the only field that ServiceNow really cares about. It also looks after itself. You don't need to worry about it during day-to-day operations.

Note

Reference fields, as we'll see, are very reliant upon the sys_id fields. When we get into scripting in Chapter 2, Developing Custom Applications , you'll be seeing more of them.

Every other field is optional and non-unique to the database platform. You can have two records that are otherwise identical but only have a differing sys_id field. (It is possible to enforce uniqueness in other fields too, as we'll see later.)

This means that, in general, you can change the value of fields to whatever you like and still maintain referential integrity; no system errors will occur. If you want to rename a user or a group, go ahead. Since everything related to that user will be associated to the user via the sys_id field, the user's name is not important.

Many other products do not use surrogate keys; data is linked together using user-provided data. If you change the name of a group, for example, this could remove all group memberships and task assignment. Not in ServiceNow, though!

Tip

An important exception to this behavior is with roles. Roles are referred to by name in scripts, so if you change the name, all scripts that use it will need to be altered (though security rules do refer to the role through the sys_id field). In general, it is a good idea to keep the names of roles the same.

It is a good idea not to interfere with this flexibility. When building functionality, don't refer to records using their names or sys_id values in scripts. Instead, use the properties or attributes of the record itself to identify it. So, rather than hardcoding the condition that a particular room in our hotel needs special treatment, create another field and use it as a flag. The VIP flag on the User table is a good example of this.