|
It is quite straightforward to add user-defined fields to both the animals table(s), and to the users table (ADMIN_U).
If you have Pedserve Pro, Advanced or Developer Edition, you can also add user-defined record types.
Before reading further, please familiarize yourself with overall database design if you have not already.
Adding User Defined Fields in Animal Table(s)
To do this you need to:
- Add the new fields to the tables in the database. You might also want to index the new fields; you will probably want to do this if you need to order search results by the new fields.
- Make arrangements for your new fields to be included in standard display fields definitions, search screens, and possibly also ordering definitions.
These steps are now covered in more detail.
1. Adding the new fields to the database tables
How you do this depends on whether the database tables already exist.
Most likely you are adding the new fields before you have created the database. In this case, all you need to do is edit the file pedserve-createanimaltables-sql.txt before you transfer it to your web server, and amend the definition of the animals table. You want to add a separate line for each field just after the 'Add user defined fields here' comment, like this:
Show/Hide Sample Code
# 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.
#
# This table must use MyISAM engine because it uses full text search indexes,
# and these are only supported in MySQL with MyISAM table engine
DROP TABLE IF EXISTS MYBREED_A;
CREATE TABLE MYBREED_A
(
A_ID INT UNSIGNED NOT NULL,
# If you change the length of the A_NAME field, you must also change the
# $::custom_nDbConf_Animal_Name_Length variable in pedserve-customize-sys.pl:
A_NAME VARCHAR(80) NOT NULL,
# If you change the length of the A_SEX field, you must also change the
# $::custom_nDbConf_Animal_Sex_Length variable in pedserve-customize-sys.pl:
A_SEX VARCHAR(2),
A_DOB DATE,
# Sire and Dam animal ID's.
A_AID_S INT UNSIGNED,
A_AID_D INT UNSIGNED,
.
.
.
.
# A_IMG_THUMB - image thumbnail field - is optional.
# If you include this field, you probably also want to turn on the thumbnail support
# - see pedserve-customize-sys.pl.
# You should also set the $::custom_fAnimalTablesHaveImgThumb variable appropriately.
#A_IMG_THUMB VARCHAR(200),
# Add user defined fields here:
,A_REG VARCHAR(20)
,A_COLOR VARCHAR(40)
) ENGINE = MyISAM;
If you also want to index the new fields, you also need to edit the file pedserve-indexanimaltables-sql.txt, by adding lines beyond the 'Add user defined fields here' comment, like this:
Show/Hide Sample Code
# 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.
#
# Adds indexes for select fields in animal + related tables.
# Note: This is separated from the sql for creating the tables, specifically
# so you can create the tables without indexes prior to filling with data,
# then add the indexes after. This greatly speeds up the overally data
# loading process.
# This table must use MyISAM engine because it uses full text search indexes,
# and these are only supported in MySQL with MyISAM table engine
ALTER TABLE MYBREED_A ADD INDEX (A_ID);
ALTER TABLE MYBREED_A ADD INDEX (A_NAME);
ALTER TABLE MYBREED_A ADD FULLTEXT (A_NAME);
ALTER TABLE MYBREED_A ADD INDEX (A_SEX);
ALTER TABLE MYBREED_A ADD INDEX (A_DOB);
ALTER TABLE MYBREED_A ADD INDEX (A_AID_S);
ALTER TABLE MYBREED_A ADD INDEX (A_AID_D);
ALTER TABLE MYBREED_A ADD INDEX (A_AID_S, A_AID_D);
ALTER TABLE MYBREED_A ADD INDEX (A_AID_D, A_AID_S);
ALTER TABLE MYBREED_A ADD INDEX (A_AID_S, A_AID_D, A_DOB);
ALTER TABLE MYBREED_A ADD INDEX (A_AID_D, A_AID_S, A_DOB);
.
.
.
# Add user defined fields here:
ALTER TABLE MYBREED_A ADD INDEX (A_REG);
ALTER TABLE MYBREED_A ADD INDEX (A_COLOR);
# Finally we use ANALYZE TABLE so that MySQL will work out the cardinality of
# the indexes etc:
ANALYZE TABLE MYBREED_A;
If however you are adding the new fields after you have created the database, and you wont be re-creating the animals table from scratch at the same time as adding the new field(s), you need to change the existing table schema. To do this you need to execute the SQL statements directly that add the fields, and index them if necessary. If you have shell access to your web server you can do this by logging in and using the mysql command shell to issue the table modification commands directly (or use a control panel interface such as phpMyAdmin):
Show/Hide Sample Code
ALTER TABLE MYBREED_A ADD A_REG VARCHAR(20);
ALTER TABLE MYBREED_A ADD A_COLOR VARCHAR(40);
ALTER TABLE MYBREED_A ADD INDEX (A_REG);
ALTER TABLE MYBREED_A ADD INDEX (A_COLOR);
2. Making changes to pedserve-hook-setup.pl
You also need to make changes to pedserve-hook-setup.pl.
Firstly you need to declare the names and properties of your new fields.
Then, you need to arrange for Pedserve to use your new fields in:
- display fields definitions - so your field can be seen in search results and pedigrees etc.
- ordering definitions - so the user may order search results according to your new fields
- search screens - to the user may query the database using the new fields
Click the button below to see an example of how this is done:
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.
#
.
.
.
# -----------------------------------------------------------------------------------------
# USER DEFINED FIELDS: ADD FIELD CONFIGURATIONS
# -----------------------------------------------------------------------------------------
#
# We call pdsSetFldDef() for each new field we have added. This gives all the basic
# properties for the field - its type, label, and other properties.
&pdsSetFldDef('A_REG', 'type=str;group=core;label=Reg.;dbsize=20;indexvalueatback=;');
&pdsSetFldDef('A_COLOR', 'type=str;group=core;label=Color;dbsize=40;indexvalueatback=;');
# -----------------------------------------------------------------------------------------
# USER DEFINED FIELDS: DECLARE COMBINATIONS OF FIELDS THAT ARE TO BE USED IN DIFFERENT SITUATIONS
# -----------------------------------------------------------------------------------------
#
# We call pdsSetUserDefinedFlds() to inform Pedserve about how we want our new user-defined
# fields to be used in various places. The 2st argument to this sub is the table suffix ('A').
# The 2nd argument is the 'user field context', and the 3rd is the list of user fields specific
# to this 'user field context'. The 'user field context' may be one of the following:
# '@' - default context; used in most places and should be the 'core'
# user defined fields you normally want displayed.
# USER-DEFINED FIELDS IN THE ANIMALS TABLE(S):
# 'alldisplay' - should be the complete list of all user fields you want displayed in
# the search results when the user selects 'All Details'
# 'advancedquery' - should be the list of user fields that are to be made searchable in
# the advanced mode search screen
# 'basicquery' - should be the list of user fields that you want to be searched when
# the user enters a basic search term
# 'submit' - should be the list of fields you want the user to be prompted for
# when he uses the 'Submit Pedigree' screen
&pdsSetUserDefinedFlds('A', '@', 'A_REG');
&pdsSetUserDefinedFlds('A', 'alldisplay', 'A_REG,A_COLOR');
&pdsSetUserDefinedFlds('A', 'advancedquery', 'A_REG,A_COLOR');
&pdsSetUserDefinedFlds('A', 'basicquery', '');
&pdsSetUserDefinedFlds('A', 'submit', 'A_REG,A_COLOR');
# -----------------------------------------------------------------------------------------
# USER DEFINED FIELDS: ADDITIONAL RECORD ORDERINGS
# -----------------------------------------------------------------------------------------
#
# Add a call to pdsSetOrderingDef() for each new ordering you want to add for user-defined
# fields. You can use the construct:
# &pdsCombineOrderingFlds(FIELD-NAME, &pdsGetStdSecondaryOrderingFlds(TABLESUFFIX))
# to merge a list of field ordering terms with the default 'secondary' ordering fields
# for a given table.
&pdsSetOrderingDef('A_ureg',
'fields='.&pdsCombineOrderingFlds('A_REG', &pdsGetStdSecondaryOrderingFlds('A')));
&pdsSetOrderingDef('A_ucolor',
'fields='.&pdsCombineOrderingFlds('A_COLOR', &pdsGetStdSecondaryOrderingFlds('A')));
.
.
.
3. Adding Data to the User Defined Fields
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 fields. They are, after all, just other fields in your existing tables.
Alternatively you can populate the user-defined fields using our pdsmakedata tool as part of your standard data loading process.
Adding User Defined Fields in the Users Table
You use a very similar technique to add new fields to the users table (ADMIN_U). E.g. you might want to add fields to hold the user's breeding name, or phone number. These fields can all be made searchable, they can be added to the joining form, and to the admin screens for editing/adding users.
If you are adding the new fields before you have created the database, you should edit the file pedserve-createsupporttables-sql.txt before you transfer it to your web server, and amend the definition of the ADMIN_U table. You want to add a separate line for each field just after the 'Add user defined fields here' comment, like this:
Show/Hide Sample Code
# 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.
#
DROP TABLE IF EXISTS ADMIN_U;
CREATE TABLE ADMIN_U
(
U_ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
.
.
.
# What follow are user defined fields:
,U_AFFIX VARCHAR(40)
,INDEX (U_AFFIX)
) ENGINE = MyISAM;
The above example also shows the addition of an index for the new field; whether you index your fields depends on the number of users you are likely to have, and whether or not you expect to search and/or order user records on your new field.
If your Pedserve system is already up and running, with users already added to the user table, you need instead to modify the existing table schema directly on the live database. To do this you need to execute the SQL statements directly that add the fields, and index them if necessary. If you have shell access to your web server you can do this by logging in and using the mysql command shell to issue the table modification commands directly (or use a control panel interface such as phpMyAdmin):
Show/Hide Sample Code
ALTER TABLE ADMIN_U ADD U_AFFIX VARCHAR(40);
ALTER TABLE ADMIN_U ADD INDEX (U_AFFIX);
Next, you need to make changes to pedserve-hook-setup.pl in a very similar way to that described above for user-defined fields in the animals table(s). E.g.:
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.
#
.
.
.
# -----------------------------------------------------------------------------------------
# USER DEFINED FIELDS: ADD FIELD CONFIGURATIONS
# -----------------------------------------------------------------------------------------
#
# We call pdsSetFldDef() for each new field we have added. This gives all the basic
# properties for the field - its type, label, and other properties.
&pdsSetFldDef('U_AFFIX', 'type=str;label=Affix;dbsize=40;indexvalueatback=;');
# -----------------------------------------------------------------------------------------
# USER DEFINED FIELDS: DECLARE COMBINATIONS OF FIELDS THAT ARE TO BE USED IN DIFFERENT SITUATIONS
# -----------------------------------------------------------------------------------------
#
# We call pdsSetUserDefinedFlds() to inform Pedserve about how we want our new user-defined
# fields to be used in various places. The 1st argument to this sub is the table suffix ('A').
# The 2nd argument is the 'user field context', and the 3rd is the list of user fields specific
# to this 'user field context'. The 'user field context' may be one of the following:
# '@' - default context; used in most places and should be the 'core'
# user defined fields you normally want displayed.
# USER-DEFINED FIELDS IN THE USERS TABLE (ADMIN_U):
# 'submit' - the list of fields you want the user to be prompted for
# when he uses the 'Apply For Login' screen - the joining form
# 'user' - the list of fields you want the user to be able to see in
# the 'Summary' area of the 'My Account' page
# 'admin' - the list of fields you want the administrator to be able to
# see in user search results etc
# 'adminedit' - the list of fields you want the administrator to be able to
# enter/edit in the add/edit user screen
# ** to include a user-defined field that is hidden from users - only visible to
# the administrator - only include it in the 'adminedit', and possibly the
# the 'admin' context.
&pdsSetUserDefinedFlds('U', '@', 'U_AFFIX');
&pdsSetUserDefinedFlds('U', 'submit', 'U_AFFIX');
&pdsSetUserDefinedFlds('U', 'user', 'U_AFFIX');
&pdsSetUserDefinedFlds('U', 'admin', 'U_AFFIX');
&pdsSetUserDefinedFlds('U', 'adminedit', 'U_AFFIX');
# -----------------------------------------------------------------------------------------
# USER DEFINED FIELDS: ADDITIONAL RECORD ORDERINGS
# -----------------------------------------------------------------------------------------
#
# Add a call to pdsSetOrderingDef() for each new ordering you want to add for user-defined
# fields. You can use the construct:
# &pdsCombineOrderingFlds(FIELD-NAME, &pdsGetStdSecondaryOrderingFlds(TABLESUFFIX))
# to merge a list of field ordering terms with the default 'secondary' ordering fields
# for a given table.
&pdsSetOrderingDef('U_uaffix',
'fields='.&pdsCombineOrderingFlds('U_AFFIX', &pdsGetStdSecondaryOrderingFlds('U')));
.
.
.
|
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
|
