automatic salto data import/synchronisationplany.fuw.edu.pl/zamki/doc/salto_data_sync_1_10.pdfsalto...
TRANSCRIPT
AUTOMATIC SALTO DATA SYNCHRONISATION TOOL (DBSYNC)
Version 1.10
SALTO data synchronisation 1.10
2
Historic of changes
Version Status Date Author Change description
0.1 Draft 26/9/2006 Mikel Larreategi First draft based on the M. Unger’s proposals (“Dynamic sync of master data V0.9.pdf”).
0.2 Draft 3/10/2006 Mikel Larreategi Minor modifications.
0.3 Draft 17/10/2006 Mikel Larreategi -User data model: fields “ExtUserID” and “ExtGroupID” enlarged from 16 to 32 chars.
- User data model: “MaximumKey Expiration” replaced by “UserExpiration”.
- User data model. New fields added: “UpdatePeriod” and “ROMCode”.
-Some wording changes.
1.0 Stable 6/11/2006 Mikel Larreategi -Several user fields renamed and new fields defined.
-Several input data files may be selected and processed (through the use of wildcard characters).
-New action: “Cancel key”.
-New error codes.
1.1 Stable 11/12/2006 Mikel Larreategi - Some default values changed.
- Some new business rules included.
- Some new error codes included.
1.2 Stable 15/01/2007 Mikel Larreategi - Null values: empty or blank fields are now considered as null values.
- User data model modified: ‘UserExpiration.enabled’, ‘STKE.Enabled’ and ‘AutoKeyEdit.Enabled’ fields removed; ‘Antipassback’ and ‘PIN.Code’ fields added.
- Minor wording mistakes corrected.
1.3 Stable 27/8/2007 M. L. -User data model modified: new field added ‘WiegandCode’.
-New default value for the ACTION field: now it defaults to 3 (update if exists, add otherwise) rather than 1 (just add).
1.4 Proposal 17/9/2009 M.L. -Card update period can now be expressed in terms of either days or hours (see fields ‘ShortTermKeyExpiration.Period’ and ‘ShortTermKeyExpiration.UnitOfPeriod’.
-Field ‘ExtGroupID’ has been replaced by ‘ExtGroupIDList’, which now represents a list of group IDs the user belongs to.
1.5 Proposal 9/3/2010 M.L. -The CSV parser includes a new rule for representing a list of subfields by means of the ‘{’ and ‘}’ characters (see ‘ExtGroupIDList’).
1.6 Stable 20/4/2010 M.L. - New error code defined (999) for representing unknown or unexpected error.
- The ‘ShortTermKeyExpiration.UnitOfPeriod’ field accepts either 0 (days) or 1 (hours). Any other value will be regarded as ‘days’.
SALTO data synchronisation 1.10
3
- The term ‘User group’ or simply ‘Group’ has been replaced by the term ‘Access Level’.
1.7 Stable 23/12/2010 M.L. - New error codes included: 206, 207, 208 and 209.
- New field ‘NewKeyIsCancellableThroughBL’ to indicate whether issued keys are
cancellable through the black list mechanism.
1.8 Stable 30/3/2011 M.L. - New fields added to the user data model: general purpose fields #4 and #5 (GPF4 & GPF5).
- Appendix B (the one dedicated to Wiegand code) removed.
1.9 Stable 6/5/2011 M.L. - The length of the general purpose fields (GPF1..GPF5) has been extended to 40 characters.
1.10 Stable 29/05/2012 M.L. - Two new fields added to the user data model: ‘OverrideLockDown’ & ‘LockdownEnabled’.
SALTO data synchronisation 1.10
4
1. Introduction......................................................................................... 5
2. SALTO data model for the USER entity ................................................. 5
3. The CSV file format .............................................................................. 9
4. Synchronisation jobs ......................................................................... 11 4.1 Data source parameters ................................................................... 11 4.2 CSV file format parameters .............................................................. 12 4.3 Field mapping parameters ................................................................ 12 4.4 Activation parameters ...................................................................... 12
5. Output files ........................................................................................ 13
6. A day in the life of an import/sync job ............................................... 14
7. Examples ........................................................................................... 16
Appendix A. Data types ........................................................................ 19
SALTO data synchronisation 1.10
5
1. Introduction
This document is aimed at explaining the specifications of the SALTO data
synchronisation tool. This tool allows data produced by non-SALTO systems (named
master systems herein) to be imported into the SALTO DB. This tool is integrated
within the SALTO access control software and can be activated manually on
demand or automatically according to a predefined day/time scheduling.
Bear in mind that this not a closed document and, as such, new features will be
added in so far as new unforeseen necessities arise.
2. SALTO data model for the USER entity
The SALTO DB contains different kinds of entities, such as doors, users, access
levels, etc. Each kind of entity may be represented with a specific data model.
Understanding the data model of the different SALTO entities make it easier to
perform data import/synchronisation between a master system and the SALTO
software.
In this section, the data model of the USER (or cardholder) entity type (also known
as staff user) is explained. The data models for the rest of the entities (such as
doors, access levels, etc) will be left for a future revision of the data
synchronisation specifications.
The data model for the USER entity within the SALTO DB is shown in the table
below. This model may be extended in the future.
Field name Type Description Default Nullable
ExtUserID Alphanumeric(32) An ID that uniquely identifies a staff user.
No
FirstName Alphanumeric(24) First name of the staff user. Null Yes
LastName Alphanumeric(24) Last name of the staff user. Null Yes
Title Alphanumeric(6) Title of the staff user. Null Yes
Office Boolean Put a door in office mode (permanently opened).
0 (false) No
Privacy Boolean Override door’s privacy (hotel).
0 (false) No
AuditOpenings Boolean All the openings are registered within the user’s key.
0 (false) No
ExtendedOpeningTime Boolean Extended opening time for disabled people (ADA).
0 (false) No
Antipassback Boolean Enable antipassback within the user’s key.
0 No
LockdownEnabled Boolean Enable the capability of putting doors in lockdown mode.
0 No
OverrideLockdown Boolean Allows cards to open doors closed in ‘lockdown’ mode.
0 No
CalendarID Integer Code of the calendar to be used by the user.
0 Yes
GPF1 Alphanumeric(40) General purpose field #1. Null Yes
GPF2 Alphanumeric(40) General purpose field #2. Null Yes
GPF3 Alphanumeric(40) General purpose field #3. Null Yes
GPF4 Alphanumeric(40) General purpose field #4. Null Yes
GPF5 Alphanumeric(40) General purpose field #5. Null Yes
ExtAccessLevelIDList List of access level IDs. Each ID is
List of access level IDs the user belongs to. Each access level ID must be delimited
Null Yes
SALTO data synchronisation 1.10
6
Alphanumeric(32) by a secondary separator.
AutoKeyEdit.ROMCode Alphanumeric(14) Serial number (in SALTO format) of the card to be automatically issued to the user when presented in an online key updater.
Null Yes
UserActivation DateTime Starting date of the key. (current date)
No
UserExpiration.ExpDate DateTime Long term expiration date of the user data and access permission.
Current date + 1 month
Yes
ShortTermKeyExpiration. Period
Integer Indicates the period of the short term expiration of the key.
(Softw. param.)
Yes
ShortTermKeyExpiration. UnitOfPeriod
integer Indicates the type of time unit for the above ‘Period’ parameter: 0=Days, 1=Hours.
0 Yes
PIN.Code Alphanumeric(8) PIN code to be typed on the keypad when trying to access online control units.
Null Yes
WiegandCode Alphanumeric(16) Wiegand code to be written on/read from user cards.
Null Yes
NewKeyIsCancellable ThroughBL
Boolean Indicates whether or not the key to be encoded will be cancellable through black list.
1 (true) No
Table 1: data model for the staff user entity.
Please refer to Appendix A to see the exact format of each data type.
In the following paragraphs, each of the fields within the user data model is
explained.
ExtUserID: this is an alphanumeric ID that serves for uniquely identifying a
given staff user. This value must be provided by the master system when
producing the import/synchronisation data files.
Title, FirstName, LastName: name of the cardholder. By default, these fields
correspond to what is known as the SaltoUserID, which is a unique ID
(different from ExtUserID) used by the Salto software for representing
cardholders in windows and reports. More on this later.
Office: if true it means that the user will be capable of setting certain doors
in office mode. It defaults to 0 (false).
Privacy: if this feature is enabled, the user will be capable of overriding
privacy in hotel rooms.
AuditOpenings: indicates whether or not openings must be also stored within
the card’s memory.
ExtendedOpeningTime: extended opening time for disabilities. If true, doors
accessed by the user will be kept opened longer time than usual. It defaults
to 0 (false).
Antipassback: enable/disable the antipassback feature within the key.
LockdownEnabled: if true, cardholders can activate/deactivate the
‘lockdown’ mode in doors. This mode makes the door be permanently
SALTO data synchronisation 1.10
7
closed, even to valid cards, until the mode it is deactivated or overridden by
a card containing the ‘OverrideLockdown’ feature.
OverrideLockdown: this allows cardholders to open locked-down doors.
CalendarID: this field contains the calendar code to be used by the user.
General purpose fields GPF1, GPF2, GPF3, GPF4 and GPF5: these fields may
contain any alphanumeric data.
AutoKeyEdit.ROMCode: serial number (in SALTO format) of the key to be
automatically issued when the user presents it in an online key updater. If a
null (blank) value is specified, this feature is disabled.
UserActivation: indicates the starting date/time of the user’s data and
access permissions. It defaults to the current date.
UserExpiration.ExpDate: long-term expiration date of the user’s data and
access permissions. It represents a maximum limit in that keys assigned to
the user will never exceed this date. A null value in this field will make the
user non-expirable.
ShortTermKeyExpiration.Period: actually, there are two expiration values to
take into account. On the one hand, the expiration of the user (represented
by the field “UserExpiration.ExpDate” explained above) and, on the other
hand, the actual expiration written on the user’s current key, also known as
short-term key expiration. In general, users will have got a long-term
expiration. However, for the sake of security, it is desirable to restrict the
actual expiration of the key to a short-term value. In this way, the system is
less vulnerable since lost or stolen keys are supposed to expire soon. When
keys get expired, they can have their expiration extended by means of
online key updater.
In order to enable short-term key expiration (mostly recommended), the
field “ShortTermKeyExpiration.Period” must contain a non-null value: as a
result, when the key is placed on an online key updater, its expiration will be
extended up to the number of days (or hours, depending on the type of
period) specified in this field.
ShortTermKeyExpiration.UnitOfPeriod: indicates how the value in the above
property (ShortTermKeyExpiration.Period) is to be considered (either as
days or hours). If 0, the period is expressed in terms of days; if 1, the
period is expressed as hours; any other value is regarded as days.
PIN.Code: doors may also require a PIN code (to be typed on an adjacent
keypad) to validate an access attempt. If this field is null, no PIN will be
required, otherwise the value within this field must correspond to the actual
PIN code to be typed.
WiegandCode: some third party systems require a custom code in Wiegand
format to be written on/read from user’s cards.
ExtAccessLevelIDList: contains a list of access level IDs (ExtAccessLevelID)
to which the user belongs to. Since it is a multiple-value field (more on this
later), each value within the list must be delimited by a secondary separator
and the whole list must be surrounded by ‘{’ and ‘}’.
NewKeyIsCancellableThroughBL: indicates whether the key assigned to the
user can be cancelled through black list. The black list is an internal
mechanism used by Salto to make issued keys stop working at standalone
or offline locks (should the user’s key gets lost or stolen). Note that the
SALTO data synchronisation 1.10
8
black list mechanism is not needed for invalidating keys at online update
readers.
If this field is True (blacklistable), the next issued key for the user will be
cancellable through black list at both offline and online locks; on the
contrary, if this field is False, the next issued key will be cancellable at
online devices only, such as Salto encoders or Cus, but not at offline locks.
There are several considerations to take into account regarding this
field:
1) Bear in mind that you may issue up to 65535 blacklistable keys, whereas
there is no limit for the number of non-blacklistable keys.
2) This field cannot be changed while the user has got a key assigned. You
must cancel or erase the key first;
3) For the sake of security, if this field is set to false, then it is not allowed
to issue a key whose expiration date is too long (one week maximum). The
best practice is to use short expiration dates for keys (see the
ShortTermKeyExpiration.UpdatePeriod field) and force cardholders to update
their key at online update readers frequently.
The following business rules must be taken into consideration when generating user
data in import/synchronisation files:
Every value of the user data model must conform to specifications of the
corresponding data type (see Appendix A). For example, if the CalendarID
field, which is an integer parameter, contains alphanumeric characters, an
error would be raised.
Some fields within the data model may contain a null value. Within CSV
files, a null value is represented by space-characters (blank) or empty
column. On the contrary, some other fields require a non-null value to be
provided, otherwise an error is raised.
The user ID ExtUserID must be unique along the whole SALTO DB. If you try
to create two users with the same ExtUserID, an exception would be
returned.
Apart from ExtUserID, there exists another type of unique ID known as
SaltoUserID, which is used by Salto to represent and show cardholders in
the Salto software’s GUI, windows and reports. By default, SaltoUserID
corresponds to the cardholser’s name, that is,
SaltoUserID= title + first name + last name.
Thus, in principle, no more than one cardholder with the same name must
exist within the SALTO DB. However, it is possible to avoid this restriction by
configuring (within the Salto software) the SaltoUserID with a different set
of fields (e.g., SaltoUserID=ExtUserID).
A given expirable key will never exceed the expiration date of the user it
belongs to. In other words, you may set a very large value in the
“ShortTermKeyExpiration.PeriodInDays” field. However, when it comes to
update a key in an online key updater, the new key expiration will be
truncated to that of the user expiration if the following condition holds true:
CurrentDate+ShortermKeyExpiration.PeriodInDays> UserExpiration.ExpDate
SALTO data synchronisation 1.10
9
The zero-day period in the “ShortTermKeyExpiration.PeriodInDays” field has
got a special meaning: it indicates that the actual key expiration will be set
to the midnight of the current date (24:00H).
A given user with no expiration and no short-term key expiration must use
calendar ID 0.
It is not allowed to modify the NewKeyIsCancellableThroughBL field while
the user has got a valid key assigned (error code=208).
In case the NewKeyIsCancellableThroughBL is disabled, it is not allowed to
issue a key whose expiration is longer than 7 days (this limit can even be
configured in the Salto software to a lower value) (error code=209).
Regarding the ExtAccessLevelIDList field, all the specified access levels
within the list must belong to the same department as that of the user’s
(error code=207).
Regarding the AutoKeyEdit.ROMCode field, it is not allowed to assign the
same card serial number to more than one user (error code=206).
3. The CSV file format
The main rules of the CSV file format are as follows:
In general, each record is one line. A record separator may consist of a line
feed (ASCII/LF=0x0A), or a carriage return and line feed pair
(ASCII/CRLF=0x0D 0x0A).
Fields are separated with a previously specified delimiter (such as comma,
semicolon, etc).
Example: 01457,John,Smith,"Anytown, WW".
Leading and trailing space-characters (also known as blank) adjacent to field
separators are ignored. So:
John , Smith , ... resolves to "John" and "Smith", ... Space characters
can be spaces, or tabs.
Text fields with embedded separator must be delimited with the text
qualifier characters (normally double-quote characters). In the above
example, "Anytown, WW" had to be delimited in double quotes because it
has an embedded field separator.
Text fields that contain double quote characters must be surrounded by
double-quotes, and the embedded double-quotes must each be represented
by a pair of consecutive double quotes. So,
John "De Niro", Smith, ... would convert to "John""De Niro""",Smith, ...
If leading or trailing spaces are to be preserved, text fields must be
delimited with the text qualifier characters (double-quote). So to preserve
the leading and trailing spaces around the last name above:
John ," Smith ", ...
Text fields may always be delimited with text qualifier characters (normally
double-quotes). These delimiters will always be discarded.
Some fields, in turn, may include (or be comprised of) a list of sub-fields. A
list of sub-fields must be surrounded by the characters ‘{‘ and ‘}’. As with
SALTO data synchronisation 1.10
10
normal fields, sub-fields are also delimited by a separator character, though
it must be different from the main separator.
In the following example, the main delimiter for the first-level fields is ‘;’,
whereas the secondary delimiter for the sub-fields is ‘/’ (in this case, names
of children):
01457; John; Smith; {Anna/Michael/Maria}; ....
As with normal fields, the same rules regarding space and text qualifier
characters apply to sub-fields also: for example, in the following record:
1234,“Leo Tolstoi”,{ War and Peace /“Anna Karenina”/“Hadji/Murat”}
the bibliography list field contains the subfields “War and Peace”, “Anna
Karenina” and “Hadji/Murat”.
An empty (or blank) field represents a null value within the SALTO data
model. Note the difference between a blank and a void string: the former is
represented by zero o several space characters (space, tab, etc.), whereas
the latter is represented by just two successive text qualifiers (e.g., “”).
The first rows in a CSV file may contain arbitrary data such as a header
record containing column (field) names. In order for these arbitrary records
to be excluded from the import/synchronisation process, the master system
should provide information about the first line where valid records start.
As for the columns in a CSV file, each one must match to one of the fields of the
corresponding data model. So, for example, the columns in the following record
AF132, John, Smith, ... match, respectively, to fields ExtUserID, FirstName,
LastName of the user data model.
Additionally, a CSV file may contain a special column that represents the action to
be performed on the SALTO DB. This column is named _Action and contains an
integer code as shown in the table below. This column is optional and, if not
specified, it defaults to 3 (update or add).
Code Action
1 Create new record.
2 Update existing record.
3 Update record if exists, create new one otherwise.
4 Delete existing record.
8 Cancel key
Table 2: action codes for the action field.
For example, the following CSV record:
1, AF132, John, Smith, ...
indicate that a new user named John Smith must be created in the SALTO DB with
an ID set to AF132.
On the other hand, note that the actual content within each CSV column must be in
accordance with the supported data types (see Appendix A). For example, a given
SALTO data synchronisation 1.10
11
column, where an integer value is expected, can not contain a value that exceeds
231 -1.
It is mandatory that each record within a CSV file must contain an entity ID field to
uniquely identify the corresponding entity within the SALTO DB. The actual entity
ID depends on the data model. For example, when importing users onto the SALTO
DB, the master system must use the “ExtUserID” field.
Finally, an empty column is dealt differently depending on the action to perform: if
you want to update a given record, an empty column means that the corresponding
field in the SALTO DB will remain unchanged. In the case of string fields, you need
to use the text qualifier, such as double quotes (“), to explicitly indicate a null
string value (e.g., “”). On the other hand, if you want to create a new record, an
empty column will make the corresponding DB field be set to its default value (as
stated by the corresponding data model).
4. Synchronisation jobs
In order to import data into the SALTO DB, you will need first to define what is
called a synchronisation job. A synchronisation job contains all the necessary
information for performing a specific synchronisation process. Basically, every
synchronisation parameter falls in one of the following categories:
Data source: indicate where the data to be imported comes from. In a first
stage, just CSV text files will be allowed. In the future, however, other data
sources could be allowed such as MS SQL Server, MS Access, MS Excel, etc.
File format: if the data source is a CSV text file, then you will need to
provide information about its format such as column delimiter, etc.
Field mapping: indicate the matching between the data source (e.g., CSV
columns) and the corresponding data model within the SALTO DB. In a first
stage, only the data model for the SALTO USER entity is allowed.
Activation: indicate when the sync job will be executed. You will be able to
either start the sync job immediately or define a date/time scheduling to
automatically execute the task when the time arrives.
Miscellaneous.
To make life easier, the SALTO software will provide a wizard that will guide you
through all the steps required for setting up a whole sync job.
In the following sections all the import job parameters will be explained in more
details.
4.1 Data source parameters
Data source indicates where the data to be synchronised comes from. Currently,
only the CSV text file type is available. However, other types of data sources will be
expected in subsequent versions (such as MS SQL Server, MS Access, MS Excel,
etc.) The main data source parameters are as follows:
SALTO data synchronisation 1.10
12
Data source type: specify the kind of source that data to be synchronised
comes from. Only one option will be available in a first version: the “CSV
text files” option. In the future more options will be included such as MS SQL
Server, MS Access, MS Excel, etc.
CSV file(s): if the “CSV text files” option is selected as data source, then this
parameter specifies the name (and path) of the CSV file(s) to be
synchronised. Note that DOS wildcard characters may be used (such as
asterisk ‘*’ and question mark ‘?’) to select more than one input file. For
example, “C:\SomeFolder\SALTOData*.csv” means that any file within folder
“C:\SomeFolder” starting with “SALTOData” and having extension “csv” will
be processed (“SALTOData1.csv”, “SALTOData20061025T123000.csv”,
“SALTODataHelloWorld.csv”, etc.)
4.2 CSV file format parameters
These parameters give information about how data is arranged within a CSV file.
Column delimiter: contain the character that will serve as a delimiter
between consecutive columns within rows. It defaults to semicolon (;).
Row delimiter: contain a sequence of characters that is used to separate one
row from the next. It defaults to CR (carriage return) + LF (line feed).
Skip rows: contain the number of rows from the start of the file that you do
not want to import.
Text qualifier: specify which character mark is used to qualify text. It defaults to double quotes (“).
4.3 Field mapping parameters
These parameters mainly indicate the matching between fields within data source
(CSV file) and fields within a data model of a given entity.
Entity type: indicate what kind of SALTO entity the CSV file represents.
Currently, only the “USER” entity type is allowed.
Column matching: specify the meaning of the different columns within the
CSV file. Basically, it consists of matching a CSV column to a specific field in
the corresponding SALTO data model.
4.4 Activation parameters
Import jobs can be executed either immediately on demand or automatically
according to a pre-defined day/time scheduling. The parameters for configuring the
day/time scheduling are as follows:
Valid time interval: specify the time interval during which the import job is
allowed to be executed. For example, no sooner than 10 AM and no later
than 11 PM.
SALTO data synchronisation 1.10
13
Execute once: specify whether the import job should be performed just once
or several times during the valid time interval in the same day.
Execution time frequency: if the job is allowed to be executed several times
in the same day, then this parameter specifies the time to wait for since last
execution before trying again. For example, every 10 minutes.
Day frequency: specify how often (in terms of days) the sync job will be
executed. If this parameter is set, you need also to provide the starting
date. For example, every 2 days starting from 1/1/2006.
Days of week (and weeks of month): specify the days of week in which the
job will be executed. Additionally, you must specify the weeks of month
(first, second, third, fourth and last) on which the selected days of week
apply. For example, Monday and Tuesday on the 2nd and 3rd weeks of
every month.
Days of month: specify the days of month (first day of month, fifth day, last
day, etc.) in which the job will be executed. For example, on the 1st and
15th days of every month.
Specific dates: is a set of specific dates on which the job will be activated.
For example, 14th April 2006 and 18th July 2006.
Dates excluded: specify a set of specific dates on which the import job will
not be allowed. Dates within this parameter have got prevalence over any
previous parameter. For example, avoid executing any sync job on the 20th
November 2006.
In a first SALTO software version, only the first four activation parameters
described above will be implemented. The rest will be supported in a subsequent
release.
5. Output files
A different output file is produced for each of the input data files processed. The
name of the output file is the same as the input file except for its extension, which
is changed to “log”. For instance, the input data file “ADataFile.csv” will result in
“ADataFile.log”.
An output file contains error information about each of the records processed within
the corresponding input data file. Master systems may analyse output files to check
if any error has been produced and, if so, in which record and why.
The structure of the output file is also CSV. The following table shows the fields
included in the output files:
Field Type Description Default position
_Action Integer A numeric code indicating the operation performed on the DB.
Column #1
ID Alphanumeric An ID that uniquely identifies the affected entity.
Column #2
ErrorCode Integer A numeric error code that indicates the error (if any) produced on performing the action.
Column #3
ErrorMessage Alphanumeric A descriptive message about the error. Column #4
Table 3: CSV fields within output files.
SALTO data synchronisation 1.10
14
As for the error codes, the following codes are contemplated:
Error code Description
0 No error.
1 “DB connection error”. This occurs when the SALTO software can not establish a connection to its DB.
202 “User with the same ID already exists”. This occurs when trying to add a new user whose ID is already in use by another user within the same SALTO DB.
203 “User with the same name already exists”. This occurs when trying to add a new user whose name is already in use by another user within the same SALTO DB.
204 “User not found”. This occurs when trying to update a specified user that does not exist within the SALTO DB.
205 “Access level not found”. This occurs, for example, when you specify that a user should belong to a non-existing access level.
206 “Card serial number already in used”. The card serial number (ROM code) cannot be assigned to the user because it is already in use by another user.
207 “User cannot belong to the specified access levels”. This occurs when the access levels to which the user must belong are not valid (for example, they belong to a different department from that of the user’s).
208 “The change is not allowed because the user has already got a key assigned”.
If the user has already got a key encoded then it is not allowed to modify the NewKeyIsCancellableThroughBL field.
209 “Key expiration exceeds the maximum allowed for a non-cancellable key”. This error occurs when the NewKeyIsCancellableThroughBL is disabled and the key’s new expiration exceeds the maximum allowed for a non-cancellable key.
503 “Invalid data format”. This occurs when a value within a given column does not conform to the specifications. For example, when an integer value is expected but an alphanumeric character is found.
601 “Missing column”. This occurs when a specified column does not exist within a given record.
602 “Start record beyond EOF”. This occurs when the starting position of the first record is beyond the end of the file.
604 “Action not supported”. This occurs when specifying an action code that is not supported.
603 “File syntax error”. This occurs when the content of the specified input file does not comply the specs.
605 “Null value assigned to a non-nullable field”. This occurs when a null value (blank) is assigned to a non-nullable data field.
999 “General error”. An error has occurred unexpectedly or does not correspond to any of the above enumerated error codes.
Table 4: error codes within output files.
6. A day in the life of an import/sync job
From time to time, the SALTO software will wake up and check whether the time
has come for a predefined sync job to be executed according to its day/time
scheduling. If the time has arrived, the sync job is considered to be active and the
following actions are carried out:
The SALTO application first opens the folder where the import/sync data files
are supposed to be located. Among all the existing files, only those that
meet the name pattern specified in the input file name parameter are
considered for the import/synchronisation process. For example, if the input
file name was set to “C:\SomeFolder\ADataFile.csv”, this would mean that
only the file named “ADataFile.csv” within the folder “C:\SomeFolder” would
SALTO data synchronisation 1.10
15
be processed. Similarly, if the input file name was set to
“C:\SomeFolder\ADataFile*.csv”, this would mean that any file within the
“C:\SomeFolder” folder, starting with “ADataFile” and having extension “csv”
would be processed (e.g., “ADataFile1.csv”, “ADataFile456.csv”,
“ADataFile2006-10-25T12:30:04.csv”, etc).
When several data files are to be processed at a precise instant, the order in
which they are processed is of fundamental importance: in this case, SALTO
will sort the selected files in alphabetical order. For example, the files
“ADataFile1.csv”, “ADataFile456.csv” and “ADataFile2006-10-25T12:30:04
.csv” will be processed in the following order: firstly “ADataFile1.csv”,
secondly “ADataFile2006-10-25T12:30:04.csv” and finally, “ADataFile456
.csv”.
In order to avoid already processed files from being considered again in
subsequent import/sync wakeups, every processed file will moved to a
subfolder called ‘Historic’. For example, the “C:\SomeFolder\ADataFile1.csv”
file, once processed, would be moved to “C:\SomeFolder\Historic\
ADataFile1.csv”. If, by any chance, a file with the same name already exists
within the ‘Historic’ subfolder, a number will be added to the latest file name
to prevent the old file from being overwritten by the latest one.
On the other hand, an output file is created for each processed file. Output
files contain information about the result of the import/synchronisation
operation for each record within the input file. The name of an output file is
the same as the corresponding input one except for the extension, which is
replaced with the “log” text. In addition, output files are always stored in the
‘Historic’ subfolder. For example, “C:\SomeFolder\ADataFile1.csv” will result
in “C:\SomeFolder\Historic\ADataFile1.log”.
Every input data file is processed in the same way: one row is read at a time
and the following information extracted: 1) action to be performed on the
SALTO DB, such as adding, updating or deleting; 2) identity of the involved
entity; and 3) in case of add or update actions, the new entity-specific field
values to be stored in the SALTO DB. All this information is analysed and
ensured that no business rule is violated before performing the specified
action. If any error is detected before or after the sync action, the current
row is discarded and an error code is written on the corresponding output
file. All these steps are repeated for each row till the end of file is reached.
Finally, once that all the input data files are processed, the sync job falls
asleep until the next scheduled activation time, when the whole cycle starts
again. Note that when a severe error occurs (such as a broken DB
connection) import/sync data files are not moved or erased from the original
folder but left untouched so that they could be considered in the next
wakeup.
Note that the responsibility of the master system just comes down to placing a CSV
files in the correct folder at the correct moments.
SALTO data synchronisation 1.10
16
7. Examples
Example 1. Let’s suppose that we want to import into the SALTO DB a CSV file
(named “NewUsers.csv”) that contains the following users:
_Action; ExtUserID; FirstName; LastName; UserActivation;UserExpiration.ExpDate
1; CDI180; John; Smith; 2006-9-30; 2006-12-24T20:30:00
3; HDI100; Johnny; Walker; 2006-9-20; 2006-11-24T07:45:00
3; DCI200; Oscar; Nicero; 2006-9-24;
We create a new sync job containing the following parameters:
Data source type = CSV text file.
Input file name = \\Server1\SALTOSync\NewUsers.csv
Column delimiter = ;
Skip rows = 1
Entity type = USER
Column matching.Field1 = _Action
Column matching.Field2 = ExtUserID
Column matching.Field3 = FirstName
Column matching.Field4 = LastName
Column matching.Field5 = UserActivation
Column matching.Field6 = UserExpiration.ExpDate
Output file name = \\Server1\SALTOSync\NewUsers_Results.txt
Note that there is no scheduling parameter since we want to execute the import job
right now. Let’s assume that the SALTO DB already contains the CDI180 and
HDI100s users. After executing the process, the output file “NewUsers_Output.txt”
will contain the following results:
_Action; ExtUserID; ErrorCode; ErrorMessage
1; CDI180; 1; User already exists
2; HDI100; 0;
1; DCI200; 0;
As it is appreciated, the first row after the column name row indicates that an error
has been produced when trying to add a new user (CDI180) that already existed
within the SALTO DB. As for the rest of the rows, no error has been produced: even
if user HDI100 previously existed in the SALTO DB, there was no conflict since the
requested action was 3 (add a new user if not existed or update otherwise).
Note also that no expiration date was provided for user DCI200 (null value) and,
therefore, the user is considered as non-expirable.
Example 2. In this case, we want to schedule a sync job to import a list of users
from a CSV file every 5 minutes, from 8 AM to 22 PM on every day. The parameters
of this sync job will be defined as follows:
Data source type = CSV text file.
Input file name = \\Server1\SALTOSync\ListOfUsers.csv
Column delimiter = ,
Skip rows = 2
Entity type = USER
Column matching.Field1 = _Action
Column matching.Field2 = ExtUserID
SALTO data synchronisation 1.10
17
Column matching.Field3 = LastName
Column matching.Field4 = FirstName
Column matching.Field5 = UserActivation
Column matching.Field6 = UserExpiration.ExpDate
Sync. time interval= From 8 AM to 22 PM
Execute once= 0
Execution time frequency = 0:5:0
Output file name = \\Server1\SALTOSync\ ListOfUsers_Results.txt
Let’s imagine that the master system produces a new version of the CSV file (say
“ListOfUsers.csv”) every 30 minutes. Hence, the SALTO systems will see the CSV
file in 1 out 6 attempts (30 min/5). If no file is seen, then the sync job will go back
to sleep. When the “ListOfUsers.csv” file is eventually seen, the SALTO software will
process and remove it from the folder.
Let’s suppose that the master system first produces a CSV file at 8:43 AM with the
following content:
_Action, ExtUserID, LastName, FirstName, UserActivation,UserExpiration.ExpDate
3, CDI180, Smith, George, 2006-9-30, 2006-12-24T20:30:00
3, HDI100, Walker, Johnny, 2006-9-20, 2007-2-1T15:55:00
4, DCI200
The SALTO software sees this file at 8:45 AM and performs the following actions: 1)
update the profile of user CDI180 by changing his name from “John Smith” to
“George Smith”; 2) update the profile of user HDI100 by extending the maximum
expiration of his key (from 2006-11-24 to 2007-2-1); 3) and finally, delete user
DCI200 from the SALTO DB. The resulting output file “ListOfUsers_Results.txt” will
be as follows:
_Action, ExtUserID, ErrorCode, ErrorMessage
3, CDI180, 0,
3, HDI100, 0,
4, DCI200, 0,
Let’s imagine now that after 30 minutes the “ListOfUsers.csv” file is recreated by
the master system with the following content:
_Action, ExtUserID, LastName, FirstName, UserActivation,UserExpiration.ExpDate
3, TDI22, Walker, Johnny, 2006-9-1, 2006-10-29T21:30:00
4, CLX18
In this case, this CSV file will be seen by SALTO at 9:15 AM and perform the
following actions: 1) an error will be arisen due to the fact that the first row violates
a business rule that states that two users (HDI100 and TDI22) can not have the
same name (“Johnny Walker”); 2) user CLX18 will be deleted. Note that even if no
such user existed within the SALTO DB, no error will be produced. All this events
would be reflected in the output file as follows:
_Action, ExtUserID, ErrorCode, ErrorMessage
3, TDI22, 2, User with the same name already exists: Johnny Walker
4, CLX18, 0
SALTO data synchronisation 1.10
18
Example 3. This example shows the use of the list-type field ‘ExtAccessLevelIDList’.
The secondary delimiter is considered to be ‘/’:
_Action; ExtUserID; FirstName; UserExpiration.ExpDate; ExtAccessLevelIDList
3; U1; Mr Walter; 2011-04-14; {Production/Night Shift}
3; U2; Mrs Peggy; 2011-05-15; {Sales Dep/ Managers}
3; U3; Miss Daisy; 2011-06-16; {R&D}
In this CSV file, we are indicating that; 1) Mr Walter belongs to two access levels,
namely, “Production” and “Night Shift”; 2) Mrs Peggy also belongs to two access
levels, namely, “Sales Department” and “Managers”; 3) finally, Miss Daisy belongs
to just one access level, namely, “R&D”.
SALTO data synchronisation 1.10
19
Appendix A. Data types
Field values within CSV files must conform to one of the following types:
Type Format
Boolean The false value is represented with the zero value (e.g., ‘0’). The true value is represented with any integer value different from zero (e.g., ‘1’ for true).
Integer An integer value is 4 bytes long and it ranges from -2147483648 to 2147483647 (e.g., ‘1234’).
Alphanumeric A string of any readable ASCII character (e.g., ‘John Smith’).
Date or DateTime
Format: YYYY-MM-DDThh:mm:ss where ‘YYYY’ means the year, ‘MM’ the month, ‘DD’ the day, ‘hh’ means hours (from 0 to 24), ‘mm’ the minutes and ‘ss’ the seconds. The ‘-’ character is used as a date separator, ‘:’ as a time separator and ‘T’ is the separator between date and time. E.g.: ‘2005-5-10T14:45’ represents May 10th 2005 at quarter to three in the afternoon. Note that time value can be omitted and it defaults to either 00:00 or 24:00 depending on whether it represents a starting date/time or an ending date/time value.
Time Format: hh:mm:ss where ‘hh’ are the hours (from 0 to 24), ‘mm’ the minutes and ‘ss’ the seconds. The ‘:’ character is used as separator. E.g.: ‘23:30:00’ represents half past eleven in the night.
Table 5: format of basic data types.