DATA BASE SCHEMAS

This chapter describes in detail each of the ISAM data tables which are built into UxFax2. All these data tables are located in sub-directories below the spool directory which is defined by environment variable $SpoolDir.

The data tables and their sub-directories are:

Each data table is described in terms of how the data file is indexed and how the records are structured, under the following headings.

Index/Flags
Most data tables are simply indexed by the first field which will be an IS_STRING type, and duplicate records will not be allowed (ISNODUPS). This is true for all tables except name, ipnode and req. These include separate description of the indexing method.

Field
This is the field index within the record. This value is used to access the field member for a record array when using the PI ISAM functions to access any of these data tables.

Name Each field has a name which is used when addressing individual fields from a record within reports, using the glosdump() PI ISAM function call.

Offset & Size The physical byte offset and size of each field is given if you choose to access this data using another ISAM API.

PI Data Type This is the data type which will be returned by the PI ISAM functions when reading records. These data types are described in the header file $Utools/Utools/include/pi.h.

ISAM Data Type These are the ISAM data types of the individual fields. These data types are not used by PI, but would be defined within an ISAM API. The only ISAM data types used by UxFax2 are;

Name and Address Data Table

The name and address data base is integral within UxFax2. The following tables show the default schema shipped with the software, although this may be reconfigured within the program.
Location $SpoolDir/db
Table name name
Record size 424
Index

Unlike all other data tables used within UxFax2, the address data base allows duplicate keys. Usually the "LastName" field is used as the index field. When searching for an exact record, find the first matching "LastName", and then read forward and matching each record until the "LastName" field does not match.

Flags ISDUPS + COMPRESS
Field Name Offset Size PI Type ISAM Type
2 LastName 56 40 IS_STRING CHARTYPE 
Schema
Field Name Offset Size PI Type ISAM Type
0 Title 0 16 IS_STRING CHARTYPE
1 FirstName 16 40 IS_STRING CHARTYPE
2 LastName 56 40 IS_STRING CHARTYPE
3 Company 96 40 IS_STRING CHARTYPE
4 Position 136 40 IS_STRING CHARTYPE
5 Address 176 40 IS_STRING CHARTYPE
6 City 216 40 IS_STRING CHARTYPE
7 State 256 40 IS_STRING CHARTYPE
8 Country 296 40 IS_STRING CHARTYPE
9 Telephone 336 24 IS_STRING CHARTYPE
10 FaxNo 360 24 IS_STRING CHARTYPE
11 Email 384 40 IS_STRING CHARTYPE

Address Broadcast Groups

The address group data base is used primarily to create groups of fax recipients. A group is composed of one or more members who are selected from the name and address data base. a fax may be sent to multiple recipients using the "broadcast" facilities by identifying a address group as the destination.
Location $SpoolDir/db
Table name agroup
Record size 127
The actual address group member data is maintained in files in the same location as the data table and which are named after the address group with a ".grp" extension. e.g. a group named "ID" will have the member list maintained in the file, $SpoolDir/db/ID.grp. These flat ASCII files contain the member records as "tags"; one per line.

Index

Flags ISNODUPS
Field Name Offset Size PI Type ISAM Type
0 group 0 10 IS_STRING CHARTYPE 
Schema
Field Name Offset Size PI Type ISAM Type
0 group 0 10 IS_STRING CHARTYPE
1 desc 12 48 IS_STRING CHARTYPE

User Details and Preferences

 
The user data table maintains the details and preferences of individual users. As a minimum, there should also be a "default" record which contains the deafult user preferences.
Location $SpoolDir/db
Table name user
Record size 127
Index
Flags ISNODUPS
Field Name Offset Size PI Type ISAM Type
0 userid 0 8 IS_STRING CHARTYPE 
Schema
Field Name Offset Size PI Type ISAM Type
0 userid 0 8 IS_STRING CHARTYPE
1 group 10 8 IS_STRING CHARTYPE
2 domain 20 26 IS_STRING CHARTYPE
3 printer 48 16 IS_STRING CHARTYPE
4 pref_type 66 2 IS_INTEGER INTTYPE
5 pref_editor 68 2 IS_INTEGER INTTYPE
6 pref_failure 70 2 IS_INTEGER INTTYPE
7 pref_success 72 2 IS_INTEGER INTTYPE
8 pref_alter 74 2 IS_INTEGER INTTYPE
9 pref_send 76 2 IS_INTEGER INTTYPE
10 FirstName 78 20 IS_STRING CHARTYPE
11 LastName 98 20 IS_STRING CHARTYPE
12 uid 118 2 IS_INTEGER INTTYPE
13 access 120 2 IS_INTEGER INTTYPE
14 pref_fine 122 1 IS_STRING CHARTYPE
15 pref_immediate 123 1 IS_STRING CHARTYPE
16 pref_rx_failure 124 1 IS_STRING CHARTYPE
17 pref_rx_success 125 1 IS_STRING CHARTYPE

User groups

 
The user data table allows individual users to be associated within administrative groups. These users groups are named within within user details.
Location $SpoolDir/db
Table name ugroup
Record size 127
Index
Flags ISNODUPS
Field Name Offset Size PI Type ISAM Type
0 group 0 8 IS_STRING CHARTYPE 
Schema
Field Name Offset Size PI Type ISAM Type
0 group 0 8 IS_STRING CHARTYPE
1 desc1 10 40 IS_STRING CHARTYPE
2 desc2 52 40 IS_STRING CHARTYPE
4 gid 92 2 IS_INTEGER INTTYPE

Prepared G3 Files

The prepared file data base is used identify pre-prepared G3 files which may be attached to outgoing faxes. The prepared files are identified by an ID, and this data table allows the user to associate a verbose decsription with the prepared file.
Location $SpoolDir/db
Table name prep
Record size 127
The actual prepared G3 images are maintained within the "px" sub-directory of the spool directory. The prepared file will have the same name as the prepared file record ID.

Index

Flags ISNODUPS
Field Name Offset Size PI Type ISAM Type
0 group 0 10 IS_STRING CHARTYPE 
Schema
Field Name Offset Size PI Type ISAM Type
0 group 0 10 IS_STRING CHARTYPE
1 desc 12 48 IS_STRING CHARTYPE

Outgoing Fax Routing Table

The route data table controls the scheduling of outgoing faxes which may be selected for off-peak transmission. It also provides a translation table for outgoing fax number prefixes.
Location $SpoolDir/db
Table name routing
Record size 255
Index
Flags ISNODUPS
Field Name Offset Size PI Type ISAM Type
0 prefix1 0 24 IS_STRING CHARTYPE 
Schema
Field Name Offset Size PI Type ISAM Type
0 prefix1 0 24 IS_STRING CHARTYPE
1 prefix2 24 24 IS_STRING CHARTYPE
2 retry 48 16 IS_STRING CHARTYPE
3 dest 64 32 IS_STRING CHARTYPE
4 r1_prefix 96 24 IS_STRING CHARTYPE
5 r1_dayb 120 2 IS_INTEGER INTTYPE
6 r1_timeb 122 4 IS_INTEGER INTTYPE
7 r1_daye 126 2 IS_INTEGER INTTYPE
8 r1_timee 128 4 IS_INTEGER INTTYPE
9 r1_prefix 132 24 IS_STRING CHARTYPE
10 r1_dayb 156 2 IS_INTEGER INTTYPE
11 r1_timeb 158 4 IS_INTEGER INTTYPE
12 r1_daye 162 2 IS_INTEGER INTTYPE
13 r1_timee 164 4 IS_INTEGER INTTYPE
14 r1_prefix 168 24 IS_STRING CHARTYPE
15 r1_dayb 192 2 IS_INTEGER INTTYPE
16 r1_timeb 194 4 IS_INTEGER INTTYPE
17 r1_daye 198 2 IS_INTEGER INTTYPE
18 r1_timee 200 4 IS_INTEGER INTTYPE
19 resolution 204 2 IS_INTEGER INTTYPE
20 tariff 206 8 IS_STRING CHARTYPE
21 r1_tariff 214 8 IS_STRING CHARTYPE
22 r2_tariff 222 8 IS_STRING CHARTYPE
23 r3_tariff 230 8 IS_STRING CHARTYPE

Tariff Table

The tariff table defines the call costs. Each record in the routing table can name a corresponding tariff table record to assciate call cost with different routes. This table also maintains a record of costs and usage to date for the current day, week, month and year.
Location $SpoolDir/db
Table name tariff
Record size 127
Index
Flags ISNODUPS
Field Name Offset Size PI Type ISAM Type
0 code 0 8 IS_STRING CHARTYPE 
Schema
Field Name Offset Size PI Type ISAM Type
0 code 0 8 IS_STRING CHARTYPE
1 desc 8 24 IS_STRING CHARTYPE
2 flagfall 32 4 IS_INTEGER LONGTYPE
3 tickcost 36 4 IS_INTEGER LONGTYPE
4 ticktime 40 4 IS_INTEGER LONGTYPE
5 lastused 44 4 IS_INTEGER LONGTYPE
6 curr_day_cost 48 4 IS_INTEGER LONGTYPE
7 curr_day_usage 52 4 IS_INTEGER LONGTYPE
8 curr_week_cost 56 4 IS_INTEGER LONGTYPE
9 curr_week_usage 60 4 IS_INTEGER LONGTYPE
10 curr_month_cost 64 4 IS_INTEGER LONGTYPE
11 curr_month_usage 68 4 IS_INTEGER LONGTYPE
12 curr_year_cost 72 4 IS_INTEGER LONGTYPE
13 curr_year_usage 76 4 IS_INTEGER LONGTYPE
14 prev_day_cost 80 4 IS_INTEGER LONGTYPE
15 prev_day_usage 84 4 IS_INTEGER LONGTYPE
16 prev_week_cost 88 4 IS_INTEGER LONGTYPE
17 prev_week_usage 92 4 IS_INTEGER LONGTYPE
18 prev_month_cost 96 4 IS_INTEGER LONGTYPE
19 prev_month_usage 100 4 IS_INTEGER LONGTYPE
20 prev_year_cost 104 4 IS_INTEGER LONGTYPE
21 prev_year_usage 108 4 IS_INTEGER LONGTYPE

Outgoing Fax Retry Schedule

The retry data table schedules any retransmissions of outgoing faxes which may fail due to a busy, no answer or other condition. The retry scheme may be nominated as part of a fax request and may be associated with a route.
Location $SpoolDir/db
Table name retry
Record size 127
Index
Flags ISNODUPS
Field Name Offset Size PI Type ISAM Type
0 scheme 0 16 IS_STRING CHARTYPE 
Schema
Field Name Offset Size PI Type ISAM Type
0 scheme 0 16 IS_STRING CHARTYPE
1 desc 18 40 IS_STRING CHARTYPE
2 cond1 60 2 IS_INTEGER INTTYPE
3 cond2 62 2 IS_INTEGER INTTYPE
4 cond3 64 2 IS_INTEGER INTTYPE
5 cond4 66 2 IS_INTEGER INTTYPE
6 cond5 68 2 IS_INTEGER INTTYPE
7 cond6 70 2 IS_INTEGER INTTYPE
8 cond7 72 2 IS_INTEGER INTTYPE
9 cond8 74 2 IS_INTEGER INTTYPE
10 cond9 76 2 IS_INTEGER INTTYPE
11 cond10 78 2 IS_INTEGER LONGTYPE
12 time1 80 4 IS_INTEGER LONGTYPE
13 time2 84 4 IS_INTEGER LONGTYPE
14 time3 88 4 IS_INTEGER LONGTYPE
15 time4 92 4 IS_INTEGER LONGTYPE
16 time5 96 4 IS_INTEGER LONGTYPE
17 time6 100 4 IS_INTEGER LONGTYPE
18 time7 104 4 IS_INTEGER LONGTYPE
19 time8 108 4 IS_INTEGER LONGTYPE
20 time9 112 4 IS_INTEGER LONGTYPE
21 time10 116 4 IS_INTEGER LONGTYPE

Modem Device Definition

The modem data table declares the hardware and operational characteristics of each attached modem.
Location $SpoolDir/db
Table name modem
Record size 127
Index
Flags ISNODUPS
Field Name Offset Size PI Type ISAM Type
0 id 0 16 IS_STRING CHARTYPE 
Schema
Field Name Offset Size PI Type ISAM Type
0 id 0 16 IS_STRING CHARTYPE
1 device 18 48 IS_STRING CHARTYPE
2 remoteid 68 20 IS_STRING CHARTYPE
3 header 90 42 IS_STRING CHARTYPE
4 prefix 134 36 IS_STRING CHARTYPE
5 driver 172 2 IS_INTEGER INTTYPE
6 mode 174 2 IS_INTEGER INTTYPE
7 lockfile 176 48 IS_STRING CHARTYPE
8 debug 226 2 IS_INTEGER INTTYPE

Printer Device Definition

The printer data table declares the characteristics of each attached printer. This table is also used to define the characteristics of softcopy devices which may be used for displaying G3 images. As a minimum, this table will include a definition for stdout .
Location $SpoolDir/db
Table name printer
Record size 127
Index
Flags ISNODUPS
Field Name Offset Size PI Type ISAM Type
0 id 0 16 IS_STRING CHARTYPE 
Schema
Field Name Offset Size PI Type ISAM Type
0 id 0 16 IS_STRING CHARTYPE
1 command 18 48 IS_STRING CHARTYPE
2 driver 68 2 IS_INTEGER INTTYPE
3 resolution 70 2 IS_INTEGER INTTYPE
4 papersize 72 2 IS_INTEGER INTTYPE

Inbound Fax Routing Table

The inbound data table controls how received faxes will be handled. This defines how notification will be achieved, and may be used to identify the sender of a fax according the the remote ID of the sending fax machine. As a minimum, this table will include a default definition.
Location $SpoolDir/db
Table name inbound
Record size 127
Index
Flags ISNODUPS
Field Name Offset Size PI Type ISAM Type
0 remoteid 0 20 IS_STRING CHARTYPE 
Schema
Field Name Offset Size PI Type ISAM Type
0 remoteid 0 20 IS_STRING CHARTYPE
1 userid 22 8 IS_STRING CHARTYPE
2 not_used1 32 2 IS_INTEGER INTTYPE
3 not_used2 34 2 IS_INTEGER INTTYPE
4 company 36 40 IS_STRING CHARTYPE
5 faxno 78 40 IS_STRING CHARTYPE

Fax Activity/History

The retry data table schedules any retransmissions of outgoing faxes which may fail due to a busy, no answer or other condition. The  scheme may be nominated as part of a fax request and may be associated with a route.
 
Location $SpoolDir/db
Table name tariff
Record size 127


Index 1

The first index is used to order faxes as they appear within the client queue windows. The first index orders the records primarily by their type, which is used to separate the current, transmitted and received faxes. Then faxes which appear within the current queue are displayed in order of their state which forces active faxes to be shown at the bottom of the list. Beyond this, all other records are sorted by request and sequence number.

 
Flags ISDUPS
Field Name Offset Size PI Type ISAM Type
1 type 2 2 IS_INTEGER INTTYPE 
0 state 0 2 IS_INTEGER INTTYPE
2 req 4 2 IS_INTEGER INTTYPE
3 seq 6 2 IS_INTEGER INTTYPE


Index 2

The second index is used to search for records by request and sequence number, for example when generating a request history summary. This index will probably be most often used when creating reports in PI.

 
Flags ISDUPS
Field Name Offset Size PI Type ISAM Type
2 req 4 2 IS_INTEGER INTTYPE
3 seq 6 2 IS_INTEGER INTTYPE


Index 3

The third index is used primarily by the server to identify the "next due" facsimile transmission. The first fax which is in a waiting state, and which has a due_time earlier than the current time will be made active.

 
Flags ISDUPS
Field Name Offset Size PI Type ISAM Type
1 stat 0 2 IS_INTEGER INTTYPE 
0 due_time 10 4 IS_INTEGER LONGTYPE
Index 4

The fourth index is the same as the first index, except it is primarily indexed by the user_id. This key is used by the client for users who have restricted access to the fax, and are only allowed to see their own faxes.

Flags ISDUPS
Field Name Offset Size PI Type ISAM Type
22 user_id 116 2 IS_INTEGER INTTYPE
1 type 2 2 IS_INTEGER INTTYPE 
0 state 0 2 IS_INTEGER INTTYPE
2 req 4 2 IS_INTEGER INTTYPE
3 seq 6 2 IS_INTEGER INTTYPE


Index 5

The fifth index is the same as the first index, except it is primarily indexed by the group_id. This key is used by the client for users who have restricted access to the fax, and are only allowed to see faxes belonging to members of their user group.

Flags ISDUPS
Field Name Offset Size PI Type ISAM Type
23 group_id 118 2 IS_INTEGER INTTYPE
1 type 2 2 IS_INTEGER INTTYPE 
0 state 0 2 IS_INTEGER INTTYPE
2 req 4 2 IS_INTEGER INTTYPE
3 seq 6 2 IS_INTEGER INTTYPE
Schema
Field Name Offset Size PI Type ISAM Type
0 state 0 2 IS_INTEGER CHARTYPE
1 type 2 2 IS_INTEGER CHARTYPE
2 req 4 2 IS_INTEGER INTTYPE
3 seq 6 2 IS_INTEGER INTTYPE
4 job 8 2 IS_INTEGER INTTYPE
5 due_time 10 4 IS_INTEGER LONGTYPE
6 submit_time 14 4 IS_INTEGER LONGTYPE
7 last_exit 18 2 IS_INTEGER INTTYPE
8 retry_count 20 2 IS_INTEGER INTTYPE
9 next_op 22 2 IS_INTEGER INTTYPE
10 uid 24 2 IS_INTEGER INTTYPE
11 npag 26 2 IS_INTEGER INTTYPE
12 userid 28 9 IS_STRING CHARTYPE
13 hostid 37 9 IS_STRING CHARTYPE
14 alias 46 16 IS_STRING CHARTYPE
15 faxno 62 24 IS_STRING CHARTYPE
16 route 86 24 IS_STRING CHARTYPE
17 offpeak 110 1 IS_STRING CHARTYPE
18 resolution 111 1 IS_STRING CHARTYPE
19 first_page 112 1 IS_STRING CHARTYPE
20 last_page 113 1 IS_STRING CHARTYPE
21 duration 114 2 IS_INTEGER INTTYPE
22 user_id 116 2 IS_INTEGER INTTYPE
23 group_id 118 2 IS_INTEGER INTTYPE
24 cost 120 4 IS_INTEGER LONGTYPE