|
In addition to making it easy to add user-defined fields to the animal table(s) (e.g. color, registration number, etc.), and to the users table, you can also add completely new user-defined record types.
The typical situation that calls for a user-defined record type, rather than a user-defined field, is when you have data that can occur multiple times for each animal. E.g. show records or health records.
For each different user-defined record type you add, Pedserve will provide you with a search page, accessed directly from the main site menu, that gives all the usual search/display facilities.
User-defined record types are not supported by Pedserve Standard Edition.
Before reading further, please familiarize yourself with overall database design if you have not already.
To add a user-defined record type you need to:
- Add the new tables to the database. You may well also want to index the table on some or all of its fields - you need to decide this on a field-by-field basis depending on whether the fields are going to be searchable, or used in ordering search results.
- Configure the new table into the Pedserve user interface.
These steps are now covered in more detail.
1. Adding the new table to the database
You first need to choose a unique table suffix for your table. This should be a short mnemonic or acronym for your record type, and should start with the letter X e.g. you might use XSR if adding a show record type. You will be using the table suffix in many places when adding your record type.
Now, edit the file pedserve-createauxtables-sql.txt before you transfer it to your web server, and add the definition of your user-defined table.
The name of your table must comprise the tableset name, an underscore ('_'), and then your table suffix. E.g. if your tableset name is GR and your table suffix is XSR, then your user-defined table name would be GR_XSR. If this does not make sense to you, please go and re-read the page on database design.
Note also that:
- All the fields within your table must also have names beginning with
XSR_.
- You must include an integer primary key field, which has the name
<S>_ID where <S> is your table suffix. E.g. XSR_ID.
- You must include a foreign key field which contains the animal ID with which each user-defined record is associated. This must have the name
<S>_AID where <S> is your table suffix. E.g. XSR_AID.
Here is an example of a user-defined show record table schema:
Show/Hide Sample Code
DROP TABLE IF EXISTS MYBREED_XSR;
CREATE TABLE MYBREED_XSR
(
XSR_ID INT UNSIGNED NOT NULL,
XSR_AID INT UNSIGNED NOT NULL,
XSR_DATE DATE,
XSR_SHOWNAME VARCHAR(80),
XSR_CLASSNAME VARCHAR(40),
XSR_CLASSSEX VARCHAR(10),
XSR_PLACING VARCHAR(10),
XSR_JUDGE VARCHAR(80),
XSR_CLASSNO VARCHAR(10),
XSR_SHOWKIND VARCHAR(20),
XSR_SHOWORGANISER VARCHAR(80),
XSR_OTHERAWARDS VARCHAR(80),
XSR_SHOWSTATUS VARCHAR(20),
XSR_NUMENTERED INT,
XSR_NUMSHOWN INT,
XSR_BIS CHAR(1),
XSR_RBIS CHAR(1),
XSR_BOB CHAR(1),
XSR_RBOB CHAR(1),
XSR_BOS CHAR(1),
XSR_BP CHAR(1),
XSR_BPIB CHAR(1),
XSR_RBPIB CHAR(1),
XSR_BV CHAR(1),
XSR_RBV CHAR(1),
XSR_CC CHAR(1),
XSR_RCC CHAR(1)
) ENGINE = MyISAM;
Any indexes you want to add should be placed in the file pedserve-indexauxtables-sql.txt, e.g.:
Show/Hide Sample Code
ALTER TABLE MYBREED_XSR ADD INDEX (XSR_ID);
ALTER TABLE MYBREED_XSR ADD INDEX (XSR_AID);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_DATE);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_SHOWNAME);
ALTER TABLE MYBREED_XSR ADD FULLTEXT (XSR_SHOWNAME);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_CLASSNAME);
ALTER TABLE MYBREED_XSR ADD FULLTEXT (XSR_CLASSNAME);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_CLASSSEX);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_PLACING);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_JUDGE);
ALTER TABLE MYBREED_XSR ADD FULLTEXT (XSR_JUDGE);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_CLASSNO);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_SHOWKIND);
ALTER TABLE MYBREED_XSR ADD FULLTEXT (XSR_SHOWKIND);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_SHOWORGANISER);
ALTER TABLE MYBREED_XSR ADD FULLTEXT (XSR_SHOWORGANISER);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_OTHERAWARDS);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_SHOWSTATUS);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_NUMENTERED);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_NUMSHOWN);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_BIS);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_RBIS);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_BOB);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_RBOB);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_BOS);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_BP);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_BPIB);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_RBPIB);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_BV);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_RBV);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_CC);
ALTER TABLE MYBREED_XSR ADD INDEX(XSR_RCC);
(In case you are wondering what the FULLTEXT lines are, these are needed to enable full text searching of the particular fields. This is also why the table schema is declared with ENGINE = MyISAM - in MySQL full text searching is only supported with MyISAM tables.)
2. Configuring the new table into Pedserve user interface
You need to add some code to pedserve-hook-setup.pl for this.
First, near the start of this script, you need to 'declare' your user-defined table, like this:
Show/Hide Sample Code
#!/usr/bin/perl
# Copyright (C) 2007, Tenset Technologies Ltd.
# This file is part of the Pedserve Online Pedigree Database system, Advanced Edition.
# All rights reserved.
#
# You may not copy, publish, license, sell, distribute, transmit, display, perform,
# modify, reproduce, transfer or create derivative works from this file
# except without prior written permission of Tenset Technologies Ltd.
#
# You may not alter or remove any trademark, copyright or other notice from this file
# or from any web page content (HTML) that is generated by program logic in this file.
#
use warnings;
no warnings qw(once);
use strict;
use DBI;
use CGI;
use CGI::Carp;
use CGI::Carp qw(fatalsToBrowser);
# -----------------------------------------------------------------------------------------
# AUXILLIARY TABLE NAMES: DECLARE THE NAMES OF AUXILLIARY TABLES HERE
# NOTE: Pedserve uses the term 'auxilliary record' internally to mean 'user-defined records'.
# They mean the same thing.
#
# NOTE: Aux tables must be declared (using pdsSetAuxRecordType) BEFORE you call pretty much
# anything else, because many of the other functions called from this setup script
# need to know which aux tables exist. BUT all other aspects of auxilliary record setup
# need to come AFTER other pdsHookStdImpl_Setup_..() calls. E.g. &pdsSetAuxQueryDef()
# requires the animal records query definition to be set up first.
#
# So stick to the ordering of pdsSetAux... calls shown here.
# -----------------------------------------------------------------------------------------
&pdsSetAuxRecordType('XSR', 'Show Record', 'Show Records');
.
.
Next, at the end of the pedserve-hook-setup.pl script, you need to supply the details of your user-defined table, like this:
Show/Hide Sample Code
.
.
# -----------------------------------------------------------------------------------------
# AUXILLIARY TABLES
#
# NOTE: All setup for aux tables should be done here, EXCEPT calls to &pdsSetAuxRecordType()
# which should be made earlier in this file. See comments above.
# -----------------------------------------------------------------------------------------
# Add in auxilliary table for Show Records
# Set the max #hits that will be returned per 'page' when a user is browsing search results
# for this record type:
&pdsSetAuxMaxHitsPerPage('XSR', 50);
# Set the default search mode - basic ('b') or advanced ('a') - for this record type.
# This governs the type of search screen the user is first presented with:
&pdsSetAuxDefaultSearchMode('XSR', 'b');
# Declare the names and properties of each individual field within the user-defined
# record type EXCEPT for the primary key (XSR_ID) and animal foreign key (XSR_AID):
&pdsSetFldDef('XSR_DATE', 'type=date;label=Date of Show;naturalordering=desc');
&pdsSetFldDef('XSR_SHOWNAME', 'type=str;label=Show Name;qopts=f;indexvalueatback=;');
&pdsSetFldDef('XSR_SHOWKIND', 'type=str;label=Kind of Show;qopts=f;indexvalueatback=;');
&pdsSetFldDef('XSR_SHOWORGANISER', 'type=str;label=Show Organizer;qopts=f;indexvalueatback=;');
&pdsSetFldDef('XSR_SHOWSTATUS', 'type=str;label=Show Status;indexvalueatback=;');
&pdsSetFldDef('XSR_CLASSNAME', 'type=str;label=Class Name;qopts=f;indexvalueatback=;');
&pdsSetFldDef('XSR_CLASSNO', 'type=str;label=Class#;indexvalueatback=;');
&pdsSetFldDef('XSR_CLASSSEX', 'type=str;label=Class Sex;indexvalueatback=;');
&pdsSetFldDef('XSR_NUMENTERED', 'type=int;subtype=count;hidevalue=0;label=#Entered');
&pdsSetFldDef('XSR_NUMSHOWN', 'type=int;subtype=count;hidevalue=0;label=#Shown');
&pdsSetFldDef('XSR_JUDGE', 'type=str;label=Judge;qopts=f;isdefaultqueryfield=1;indexvalueatback=;');
&pdsSetFldDef('XSR_PLACING', 'type=str;label=Placing;indexvalueatback=;');
&pdsSetFldDef('XSR_CC', 'type=flag;label=CC');
&pdsSetFldDef('XSR_RCC', 'type=flag;label=RCC');
&pdsSetFldDef('XSR_BOB', 'type=flag;label=BOB');
&pdsSetFldDef('XSR_RBOB', 'type=flag;label=RBOB');
&pdsSetFldDef('XSR_BIS', 'type=flag;label=BIS');
&pdsSetFldDef('XSR_RBIS', 'type=flag;label=RBIS');
&pdsSetFldDef('XSR_BOS', 'type=flag;label=BOS');
&pdsSetFldDef('XSR_BP', 'type=flag;label=BP');
&pdsSetFldDef('XSR_BPIB', 'type=flag;label=BPIB');
&pdsSetFldDef('XSR_RBPIB', 'type=flag;label=RBPIB');
&pdsSetFldDef('XSR_BV', 'type=flag;label=BV');
&pdsSetFldDef('XSR_RBV', 'type=flag;label=RBV');
&pdsSetFldDef('XSR_OTHERAWARDS', 'type=str;label=Other Awards;indexvalueatback=;');
# Add a query definition for this record type.
# This is what determines which fields Pedserve will provide search capabilities
# for, for both the basic and advanced search screens.
&pdsSetAuxQueryDef('XSR',
'basicfields=XSR_SHOWNAME,XSR_JUDGE;'
.'fields=XSR_DATE,XSR_SHOWNAME,XSR_SHOWKIND,XSR_SHOWORGANISER,XSR_SHOWSTATUS,'
.'XSR_CLASSNAME,XSR_CLASSNO,XSR_CLASSSEX,XSR_NUMENTERED,XSR_NUMSHOWN,'
.'XSR_JUDGE,XSR_PLACING,XSR_CC,XSR_RCC,XSR_BOB,XSR_RBOB,XSR_BIS,XSR_RBIS,'
.'XSR_BOS,XSR_BP,XSR_BPIB,XSR_RBPIB,XSR_BV,XSR_RBV,XSR_OTHERAWARDS;');
# Add Display Fields Definitions - these are the entries that go in the pull-down
# menu above search results screens that the user uses to select which fields are
# tabulated in the results (i.e, which columns are displayed).
# NOTE: this must include a <tablesuffix>_all entry, as its needed by the aux
# record details screen.
&pdsSetAuxDisplayFldsDef('XSR', 'XSR_basic',
'label=Basic Details;fields1=;'
.'fields2=XSR_DATE,XSR_SHOWNAME,XSR_CLASSNAME,XSR_CLASSSEX,XSR_NUMENTERED,'
.'XSR_NUMSHOWN,XSR_JUDGE,XSR_PLACING,XSR_CC,XSR_BOB');
&pdsSetAuxDisplayFldsDef('XSR', 'XSR_all',
'label=All Details;fields1=;'
.'fields2=XSR_DATE,XSR_SHOWNAME,XSR_SHOWKIND,XSR_SHOWORGANISER,XSR_SHOWSTATUS,'
.'XSR_CLASSNAME,XSR_CLASSNO,XSR_CLASSSEX,XSR_NUMENTERED,XSR_NUMSHOWN,XSR_JUDGE,'
.'XSR_PLACING,XSR_CC,XSR_RCC,XSR_BOB,XSR_RBOB,XSR_BIS,XSR_RBIS,XSR_BOS,XSR_BP,'
.'XSR_BPIB,XSR_RBPIB,XSR_BV,XSR_RBV,XSR_OTHERAWARDS');
# Select the default display fields definition:
&pdsSetAuxDefaultDisplayFldsKey('XSR', 'XSR_basic');
# Set the 'secondary ordering' - these are the fields by which the
# user-defined records are ordered AFTER any primary ordering:
&pdsSetStdSecondaryOrderingFlds('XSR', 'XSR_AID.A_NAME,XSR_DATE:desc-1');
# Add Ordering Definitions - these are the entries that go in the pull-down menu
# above the search results that the user can use to control the ordering of
# search results.
{
# In all cases we set a specific primary ordering which is
# merged with the secondary ordering:
my $strSecondaryFlds = &pdsGetStdSecondaryOrderingFlds('XSR');
&pdsSetAuxOrderingDef('XSR', 'XSR_o1',
'fields='.&pdsCombineOrderingFlds('XSR_SHOWNAME', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o2',
'fields='.&pdsCombineOrderingFlds('XSR_DATE', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o3_d',
'fields='.&pdsCombineOrderingFlds('XSR_DATE:desc-1', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o4',
'fields='.&pdsCombineOrderingFlds('XSR_NUMENTERED:desc-1', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o5',
'fields='.&pdsCombineOrderingFlds('XSR_NUMSHOWN:desc-1', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o6',
'fields='.&pdsCombineOrderingFlds('XSR_SHOWKIND', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o7',
'fields='.&pdsCombineOrderingFlds('XSR_SHOWORGANISER', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o8',
'fields='.&pdsCombineOrderingFlds('XSR_SHOWSTATUS', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o9',
'fields='.&pdsCombineOrderingFlds('XSR_CLASSNAME', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o10',
'fields='.&pdsCombineOrderingFlds('XSR_CLASSNO', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o11',
'fields='.&pdsCombineOrderingFlds('XSR_CLASSSEX', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o12',
'fields='.&pdsCombineOrderingFlds('XSR_JUDGE', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o13',
'fields='.&pdsCombineOrderingFlds('XSR_PLACING', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o14',
'fields='.&pdsCombineOrderingFlds('XSR_CC', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o15',
'fields='.&pdsCombineOrderingFlds('XSR_RCC', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o16',
'fields='.&pdsCombineOrderingFlds('XSR_BOB', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o17',
'fields='.&pdsCombineOrderingFlds('XSR_RBOB', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o18',
'fields='.&pdsCombineOrderingFlds('XSR_BIS', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o19',
'fields='.&pdsCombineOrderingFlds('XSR_RBIS', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o20',
'fields='.&pdsCombineOrderingFlds('XSR_BOS', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o21',
'fields='.&pdsCombineOrderingFlds('XSR_BP', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o22',
'fields='.&pdsCombineOrderingFlds('XSR_BPIB', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o23',
'fields='.&pdsCombineOrderingFlds('XSR_RBPIB', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o24',
'fields='.&pdsCombineOrderingFlds('XSR_BV', $strSecondaryFlds));
&pdsSetAuxOrderingDef('XSR', 'XSR_o25',
'fields='.&pdsCombineOrderingFlds('XSR_RBV', $strSecondaryFlds));
}
# Select the default ordering:
&pdsSetAuxDefaultOrderingKey('XSR', 'XSR_o1');
# Add other standard orderings for user-defined records: these will include
# 'by animal name', etc:
&pdsAddAuxStandardOrderings('XSR');
1;
3. Adding Data to the User Defined Tables
If you're populating your database tables using your own means, obviously its up to you how to get the data into the user-defined tables. They are, after all, just other database tables.
Alternatively our pdsmakedata tool can handle user-defined tables as part of your standard data loading process.
|
User-Defined Record Search
|
USEFUL LINKS:
 EULA
Pedserve Editions
Sample City
Consultancy
Data Preparation
Installation
Regular Expressions
Similarity Searching
Configuration File
Setup Script
Stylesheet
Database Design
Hooks
Date/Time
User Defined Fields
User Defined Records
Display Fields Definitions
Ordering Fields Definitions
Highlighting
Page Layout
Field Definitions
Field Formatting
Shortcut Query Buttons
Plates
Command Buttons
Connecting to the Database
Warning Footer Message
|
