Create a Sequence

In this Lesson

You will learn about Soiree Sequences and how they are used with datasources.

Concepts

Soiree provides named values that auto-increment each time you ask for one. These auto-incrementing values are called a Sequence.

The most common use of sequences involves assigning a unique value to each row that is inserted into a database table. That is because the best kind of primary key for a database table is one that has absolutely no meaning in the database except to act as a primary key and a system assigned number is about as meaningless as you can get.

Sequences are not restricted for use with database tables. They can be used for any purpose that would benefit from a series of auto-incrementing values.

Because sequences are so frequently used for database tables you will be pleased to know that datasources have the ability to assign sequences to a database column. You will be doing that in this lesson.

Sequences consist of two parts

  • The sequence definition
  • A row in the SoireeSequence table which is used at runtime to control the assignment of the next number.

Sequence definitions are installed into the SoireeSequence table when the server starts. As the server is starting it looks for sequence definitions on the Java classpath. For each sequence definition it finds it will inspect the SoireeSequence table. If the sequence does not exist in the table the server will insert the definition into the table. If the sequence definition already exists in the table then the existing row in the SoireeSequence table left as-is.

Soiree Sequences vs. Database assigned sequences

Most databases provide the ability to auto-assign an incrementing value to a column.

Why might you choose to use Soiree Sequences instead of database sequences?

  1. Portability
    Soiree sequences are defined and used one way – regardless of which database server you choose to deploy the application on.
  2. Increased control
    Database assigned sequences can negatively impact your ability to migrate database rows across environments.

    Any robust development involves multiple environments such as

    • A development environment where the solution is built
    • A test environment where the solution is certified
    • A production environment where the solution is used by your customer

    Often you find the need to copy rows of information across these environments. This may involve moving configuration information between environments or copying test/production information back to development to create test cases.

    If the rows being copied between environments are contained in tables that use database assigned numbers then those numbers will change as you extract rows from one environment and insert them into another.

    If you use sequences that are not controlled by the database you get to choose when a new number is to be assigned. Assign them when rows are created in the environment and do not assign them when you are attempting to copy rows between environments.

Create a Sequence

In this exercise you will create a sequence that will be used to assign the party_id to rows that are inserted into the Party table.

  1. Select the com.example.party package



  2. Open the Soiree Sequence wizard by right-clicking the package and selecting New … Sequence
  3. Create a sequence as shown here



  4. Your project should now contain the com.example.party.sequence.PartyId sequence item.



Use the sequence in a datasource

You will now use this sequence to assign values to the party_id column of the Party table.

  1. Open the com.example.party.datasource.Party datasource item.



  2. Expand the Values section and select the party_id value.



  3. Select an Insert Value Type of Sequence



  4. Press the Select Sequence button



  5. Select the sequence you created



  6. The party_id should now look like this



  7. Rebuild the datasource.
    This will produce code that includes support for the sequence that you added to the party_id value.



  8. Close the datasource

You have reached the end of this lesson.