 |
| Special August 2017 - Using counters |
|---|
| Category : User |
| Level : Easy |
| Back to Overview |
Unique ids are an essential thing in database environments. There are several ways to implement a management for the id creation. This might either be done in a dedicated table or the next unique id can be retrieved from the consumer table itself. Another comfortable and efficient way is to use database specific counter objects. cego provides this is a way which is illustrated in the special.
A database user can create a tableset specific counter object with the following command
ws > create counter c1; Counter c1 created ok ( 0.000 s ) ws > list counter; +---------+---------------------+ | Counter | Counter | | Name | Value | +---------+---------------------+ | c1 | 0 | +---------+---------------------+ 1 tuples ok ( 0.000 s ) |
Please note : Counter objects are NOT transaction and recovery safe. In case of a system crash, counter objects have to restored manually. We will refer on this procedure at the end of the web special.
We can set the counter now to an appropriate initial value
ws > set counter c1 to 42; Counter c1 set ok ( 0.000 s ) ws > list counter; +---------+---------------------+ | Counter | Counter | | Name | Value | +---------+---------------------+ | c1 | 42 | +---------+---------------------+ 1 tuples ok ( 0.000 s ) |
To illustrate the use of a counter object, we first create a simple table t1
ws > create table t1 ( a int ); Table t1 created ok ( 0.000 s ) |
Now we insert subsequent counter values into the table by calling the inbuild counter function nextcount
ws > insert into t1 values ( nextcount(c1)); 1 tuples inserted ok ( 0.000 s ) ws > insert into t1 values ( nextcount(c1)); 1 tuples inserted ok ( 0.000 s ) ws > insert into t1 values ( nextcount(c1)); 1 tuples inserted ok ( 0.000 s ) ws > select * from t1; +-----------+ | t1 | | a | +-----------+ | 43 | | 44 | | 45 | +-----------+ 3 tuples ok ( 0.000 s ) |
As mentioned above, counter object are not recovery safe. To restore the previously created counter, the following SQL command can be used
ws > set counter c1 to ( select max(a) from t1 ); Counter c1 set ok ( 0.000 s ) |
Appropriate statements for all tableset counters can be stored to a tableset init file, which is executed during initial tableset startup after the database is started ( see cego documentation "Tableset init file" ).
Counter also can be manipulated with the setcount function. The function takes two arguments, a counter id and a counter value. It sets the value of the specified counter to the given value and returns the previous value of the counter. The following sample illustrates this
drop if exists procedure setCountCheck; @ create procedure setCountCheck ( ) return int begin var i int; :i = setcount(c1,42); return :i; end; @ |
After creating the procedure, we can check it with the following commands
ws > set counter c1 to 3; Counter c1 set ok ( 0.037 s ) ws > :r = call setCountCheck(); Procedure executed ok ( 0.036 s ) ws > print :r; 3 ok ( 0.037 s ) ws > list counter; +---------+---------------------+ | Counter | Counter | | Name | Value | +---------+---------------------+ | c1 | 42 | +---------+---------------------+ 1 tuples ok ( 0.073 s ) |