Auto increment in Starcounter

May 31, 2016 11:04 am Published by Leave your thoughts

It is very common and convenient to have auto increment primary key column in traditional SQL databases. Some of you might want to use the same feature in Starcounter. In this blog post I will explain how to create an auto increment property in a Starcounter database class.

There are quite a few differences between Starcounter and traditional SQL databases.

  • Each Starcounter database object has a unique ulong identifier.
  • The unique identifier is set as soon as object created.
  • The unique identifier is unique among entire database and all running transactions.
  • The unique identifier could not guarantee that an object with lower value was created before an object with higher value.

It is no longer needed to manually create primary key or care about it’s values, but it is often required to sort objects by creation time. What is creation time?

  • When record is inserted and committed.
  • When record is allocated in memory.
  • When user submitted a request, which leads to create the record.
  • When a handler was invoked, which creates the record.

Different applications require different business logic and it is up to the developers to decide which point of time is called creation time. Read more about this issue on stackoverflow.com – AUTO INCREMENT in Starcounter.

In most cases it is enough to declare a public DateTime Created { get; set; } property and fill it with DateTime.Now inside constructor. The DateTime.Now precision is about 50ms depending on hardware. It is still potentially possible to get two objects with the same value. In this case the unique object identifier can be used to determine which of the objects will be declared as created first, and which as created second.

Here is a simple implementation for the solution:

[fusion_builder_container hundred_percent="yes" overflow="visible"][fusion_builder_row][fusion_builder_column type="1_1" background_position="left top" background_color="" border_size="" border_color="" border_style="solid" spacing="yes" background_image="" background_repeat="no-repeat" padding="" margin_top="0px" margin_bottom="0px" class="" id="" animation_type="" animation_speed="0.3" animation_direction="left" hide_on_mobile="no" center_content="no" min_height="none"][Database]
public class Order {
    public Order() {
        this.Created = DateTime.Now;
    }

    public ulong No {
        get { return this.GetObjectNo(); }
    }

    public DateTime Created {
        get;
        set;
    }

    public static QueryResultRow<Order> SelectAll() {
        return Db.SQL<Order>("SELECT o FROM \"Order\" o ORDER BY o.\"Created\", o.\"No\"");
    }
}

Sometimes it is very important to have a unique auto incremented value inside a database table. This can be achieved with Db.Transact.

The main idea is to store the counter value inside database and guarantee uniqueness with usage of Db.Transact. Simultaneous accesses to the counter will result into high amount of transaction conflicts, so the counter may become to be a bottleneck. Think twice before going this way, most likely it is possible to use the solution above.

Here is an implementation of a simple Counter class which generates auto incremented values.

using System;
using Starcounter;

[Database]
public class Counter {
    static Counter() {
        if (Db.SQL(@"SELECT i FROM Starcounter.Metadata.""Index"" i WHERE Name = ?", "UK_Counter_Name").First == null) {
            Db.SQL(@"CREATE UNIQUE INDEX UK_Counter_Name ON Counter (Name ASC)");
        }   
    }

    public string Name { get; set; }
    public ulong Value { get; set; }

    public static ulong GetNextValue(string CounterName) {
        ulong value = Db.Transact<ulong>(() => {
            Counter counter = Db.SQL<Counter>("SELECT c FROM Counter c WHERE c.Name = ?", CounterName).First;

            if (counter == null) {
                counter = new Counter() {
                    Name = CounterName,
                    Value = 0
                };
            }

            counter.Value++;

            return counter.Value;
        });

        return value;
    }
}

[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

Categorised in:

This post was written by Konstantin Cherniavskyi

Leave a Reply

Your email address will not be published. Required fields are marked *