Start from scratch
Here we will guide you how to create your database using ParitionDB from scratch. If you don’t have PartitionDB installed on your machine yet, please refer to the installation guide.
Building your new database can be done using PartitionDB user interface, or by a script. For more detailed description of PartionDB APIs please refer to the API document.
Here we will guide you how to create the simple configuration of a gate and three partitions (childernDBs).
Create a Gate
As you already know, a PartionDB configuration starts with a gate, and a column by which the Partition will be created.
Here is how to create a gate called ‘TwoWestGate’ using column ‘BranchID’:
exec Pdbinstall 'TwoWestGate',@ColumnName='BranchId';
Running this install command creates a gate named TwoWestGate with the default attributes. PdbInstall can take all attributes as parameters, as described in the API document (link). Or you can set these attributes using the user interface. The next paragraph lists and explains the different attributes of a gate.
Here is a view of a gate in PratitionDB user interface:
Set the Gate’s attributes
A Gate has many attributes that can be set upon your needs. PartitionDB sets default values for all attributes, in case you choose not to change them. However, you should be familiar with the different parameters. Be aware that these attributes are set during the creation of a Gate only, and cannot be changed later.
Basic Gate attributes
We encourage you to avoid using the default values for these attributes, but set a meaningful values instead. Use descriptive names that will help you to manage your database in the future.
Gate name – A string representing for the Gate name.
Partition column name – The name of the column by which the partition is done.
Partition column type – The type of the column by which the partition is done (integer, date etc.)
Partition type name – The partition is performed using the partition type name. PrtitionDB creates it, and you can set its name.
Column length – Maximum length of of the type in case it is string or decimal.
Is Union enable
The decision to enable union or not is very crucial. Setting a union to ‘Enable’ means that a column will be unique across all partitions. Before adding a new entry, all partitions of the database will be compared against the new entry to ensure uniqueness.
Setting union to false means that uniqueness is required in the partition itself, and not across partitions.
Select Folder and Log Location
During the creation of a Gate you will have to choose the location (path) of the data, and the location of the logs. In case no path is passed, the selected location is where the last database was create and placed.
Choose collation for the Gate and the partition databases. Default is Latin.
This is the prefix for object names that are created in the Gate and and are part of the view. Their names will be prefix+object name.
Common Access Active
This attributes defines if a relationship between a partition and CommonDB is allowed (Yes) or not (No).
Partition Identity Active
Partition identity active determines if the indexes for the new entry are unique for the partition only (not active) or for the whole database (active). If union is set to true, the partition identity active must be set as well. However, for a database that is not set as union, you can still choose how to index the entries, either unique for the whole database (active) or unique for the partition itself (not active).
Partition Increment Value
The increment that each partition will use when creating new entry. This is applicable only when union is set. The default increment is 10, meaning that first partition will be indexed using 1, 11, 21 and so fourth, while the second partition will be indexed by 2, 12, 22 etc.
Please note that hovering over the attributes in the user interface displays the attribute’s explanation in a tooltip, so you have the explanation right in from of you:
Once having a Gate, we will create childernDB that logically reside under the Gate:
use TwoWestGate; exec PdbcreatePartition 'TwoWestGate','SunsetDB',1; exec PdbcreatePartition 'TwoWestGate','VeniceDB',2; exec PdbcreatePartition 'TwoWestGate','WilshireDB',3;
This script creates three children databases, ‘SunsetDB’, ‘VeniceDB’, and ‘WilshireDB’. In order to create a childDB you need to pass the Gate name, the childDB name and the value of the column by which the partition was done, in this case the BranchID.
SunsetDB will store all entries whose BranchID is 1, VeniceDB stores entries of BranchID 2 and Wilshire stores BranchID 3.
This can be done form the user interface as well:
In this window you will to fill out the following:
- BranchID (in our case it would be 1 to 1 for the first partition, 2 to 2 for the second and 3 to 3 for the third partition. It can also be a range).
- Partition description
- Comments (optional??).
- Select the Gate name in the database field to which you wish to add the partition.
Create a table
Now that the configuration is ready, it’s time to create your first table:
create table Branches ( Id PartitionDBType not null primary key , BranchNumber nvarchar(16) not null unique , Name nvarchar(128) not null unique , City nvarchar(128) , Address nvarchar(128) , PostalCode nvarchar(8) );
The first column in the table is of type PartitionDBType. This is the branchID that we chose to use as the partition column. The reset of the columns are attributes of a branch.
From here and on you can keep going and create your database. Check here (link) for more advanced options, and read more about CommonDB (link), GlobalDB (link) and standAloneDB (link).