SET DATABASE UNIQUE NAME HSQLDB7E118D778F
SET DATABASE GC 0
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
SET DATABASE EVENT LOG LEVEL 0
SET DATABASE TRANSACTION CONTROL LOCKS
SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED
SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE
SET DATABASE TEXT TABLE DEFAULTS ''
SET DATABASE SQL NAMES FALSE
SET DATABASE SQL REFERENCES FALSE
SET DATABASE SQL SIZE TRUE
SET DATABASE SQL TYPES FALSE
SET DATABASE SQL TDC DELETE TRUE
SET DATABASE SQL TDC UPDATE TRUE
SET DATABASE SQL CONCAT NULLS TRUE
SET DATABASE SQL UNIQUE NULLS TRUE
SET DATABASE SQL CONVERT TRUNCATE TRUE
SET DATABASE SQL AVG SCALE 0
SET DATABASE SQL DOUBLE NAN TRUE
SET FILES WRITE DELAY 500 MILLIS
SET FILES BACKUP INCREMENT TRUE
SET FILES CACHE SIZE 10000
SET FILES CACHE ROWS 50000
SET FILES SCALE 32
SET FILES LOB SCALE 32
SET FILES DEFRAG 0
SET FILES NIO TRUE
SET FILES NIO SIZE 256
SET FILES LOG TRUE
SET FILES LOG SIZE 50
SET FILES CHECK 1812
SET DATABASE COLLATION "SQL_TEXT" PAD SPACE
CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'
ALTER USER SA SET LOCAL TRUE
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE PUBLIC.AFFLICTIONCLASS(AFFLICTIONCLASSID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(NAME))
ALTER TABLE PUBLIC.AFFLICTIONCLASS ALTER COLUMN AFFLICTIONCLASSID RESTART WITH 4
COMMENT ON TABLE PUBLIC.AFFLICTIONCLASS IS 'Groups the types of affliction - pest, disease, death'
COMMENT ON COLUMN PUBLIC.AFFLICTIONCLASS.AFFLICTIONCLASSID IS 'pest, disease or death'
CREATE MEMORY TABLE PUBLIC.AFFLICTION(AFFLICTIONID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,AFFLICTIONCLASSID INTEGER DEFAULT NULL,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(NAME),CONSTRAINT FK_AFFLICTION_AFFLICTIONCLASS1 FOREIGN KEY(AFFLICTIONCLASSID) REFERENCES PUBLIC.AFFLICTIONCLASS(AFFLICTIONCLASSID))
ALTER TABLE PUBLIC.AFFLICTION ALTER COLUMN AFFLICTIONID RESTART WITH 1
CREATE INDEX FK_AFFLICTION_AFFLICTIONCLASS1_IDX ON PUBLIC.AFFLICTION(AFFLICTIONCLASSID)
COMMENT ON TABLE PUBLIC.AFFLICTION IS 'These are the various afflictions observed.'
COMMENT ON COLUMN PUBLIC.AFFLICTION.AFFLICTIONCLASSID IS 'An actual type of pest, disease or death, e.g. lily beetle, blight.U&\000D\000ANote that this is optional - I think it''s too restrictive to force users to create AfflictionClasses before they can specify an actual Affliction.  There''s no option to show ''all AfflictionEvents caused by a pest'', for instance.  Having this field mandatory also requires AfflictionClass combos on Husbandry and AfflictionEvent editors.'
CREATE MEMORY TABLE PUBLIC.LOCATION(LOCATIONID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,PARENTLOCATIONID INTEGER DEFAULT NULL,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,UNDERCOVER BOOLEAN DEFAULT FALSE NOT NULL,GEOMETRY VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(NAME),CONSTRAINT FK_LOCATION_LOCATION1 FOREIGN KEY(PARENTLOCATIONID) REFERENCES PUBLIC.LOCATION(LOCATIONID))
ALTER TABLE PUBLIC.LOCATION ALTER COLUMN LOCATIONID RESTART WITH 2
CREATE INDEX FK_LOCATION_LOCATION1_IDX ON PUBLIC.LOCATION(PARENTLOCATIONID)
COMMENT ON TABLE PUBLIC.LOCATION IS 'A physical location, such as a bed or a greenhouse.  The installation scripts include a single entry ''The Garden'' which is a top-level parent for other Locations, such as flower beds, lawns, greenhouses, etc.  ''The Garden'' is given a default geometry of a 100x100 square.  You may have more than one top-level Location, such as ''The Garden'', ''The Allotment''; these will be treated as physically distinct areas, all the children of each top level Location must be physically within that top level parent if a geometry value is supplied.'
COMMENT ON COLUMN PUBLIC.LOCATION.PARENTLOCATIONID IS 'The current Location is considered to be part of the parent Location.  The relationship may be logical, rather than physical.'
COMMENT ON COLUMN PUBLIC.LOCATION.NAME IS 'Name of the location.'
COMMENT ON COLUMN PUBLIC.LOCATION.DESCRIPTION IS 'A brief description of the Location'
COMMENT ON COLUMN PUBLIC.LOCATION.UNDERCOVER IS 'Is this location under cover (e.g. a greenhouse) or outside.'
COMMENT ON COLUMN PUBLIC.LOCATION.GEOMETRY IS 'Describes the boundary of the Location.  At present, only simply connected polygons are supported.  The value should be provided in the OpenGIS ''well known text'' format (this requires polygons to be explicitly closed).  Note that x increases left-to-right (as normal), y increases bottom-to-top (i.e. South to North) NOT the usual computer graphics convention.  If a value is supplied, it must lie physically within the geomentry of the ultimate top-level parent of the Location.'
CREATE MEMORY TABLE PUBLIC.CROPROTATIONGROUP(CROPROTATIONGROUPID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,SUBSEQUENTCROPGROUPID INTEGER DEFAULT NULL,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(NAME),UNIQUE(SUBSEQUENTCROPGROUPID),CONSTRAINT FK_CROPROTATIONGROUP_CROPROTATIONGROUP1 FOREIGN KEY(SUBSEQUENTCROPGROUPID) REFERENCES PUBLIC.CROPROTATIONGROUP(CROPROTATIONGROUPID))
ALTER TABLE PUBLIC.CROPROTATIONGROUP ALTER COLUMN CROPROTATIONGROUPID RESTART WITH 7
COMMENT ON TABLE PUBLIC.CROPROTATIONGROUP IS 'Crops are grouped together when planning a rotation.  All the crops in, say, group ''legumes'' will follw the crops in group ''roots'' and will be followed by crops in group ''brassicas''.'
COMMENT ON COLUMN PUBLIC.CROPROTATIONGROUP.SUBSEQUENTCROPGROUPID IS 'The CropRotationGroup which is grown AFTER this CropRotationGroup.\nFor instance, this = legumes, subsequent = brassicas'
COMMENT ON COLUMN PUBLIC.CROPROTATIONGROUP.NAME IS 'The name of a crop group for rotation purposes, e.g. ''brassicas''.'
COMMENT ON COLUMN PUBLIC.CROPROTATIONGROUP.DESCRIPTION IS 'A description of the crops in this group.'
CREATE MEMORY TABLE PUBLIC.CROPPINGPLAN(CROPPINGPLANID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,CROPROTATIONGROUPID INTEGER NOT NULL,LOCATIONID INTEGER NOT NULL,YEAROFPLAN DATE DEFAULT CURRENT_DATE NOT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(YEAROFPLAN,LOCATIONID,CROPROTATIONGROUPID),CONSTRAINT FK_CROPPINGPLAN_CROPROTATIONGROUP1 FOREIGN KEY(CROPROTATIONGROUPID) REFERENCES PUBLIC.CROPROTATIONGROUP(CROPROTATIONGROUPID),CONSTRAINT FK_CROPPINGPLAN_LOCATION1 FOREIGN KEY(LOCATIONID) REFERENCES PUBLIC.LOCATION(LOCATIONID))
ALTER TABLE PUBLIC.CROPPINGPLAN ALTER COLUMN CROPPINGPLANID RESTART WITH 1
CREATE INDEX FK_CROPPINGPLAN_CROPROTATIONGROUP1_IDX ON PUBLIC.CROPPINGPLAN(CROPROTATIONGROUPID)
CREATE INDEX FK_CROPPINGPLAN_LOCATION1_IDX ON PUBLIC.CROPPINGPLAN(LOCATIONID)
CREATE MEMORY TABLE PUBLIC.PLANTSPECIES(PLANTSPECIESID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,CROPROTATIONGROUPID INTEGER DEFAULT NULL,COMMONNAME VARCHAR(255) NOT NULL,LATINNAME VARCHAR(255) DEFAULT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,UTILITY VARCHAR(45) DEFAULT NULL,HARDINESS VARCHAR(45) DEFAULT NULL,LIFETYPE VARCHAR(45) DEFAULT NULL,PLANTTYPE VARCHAR(45) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(COMMONNAME),CONSTRAINT FK_PLANTSPECIES_CROPROTATIONGROUP1 FOREIGN KEY(CROPROTATIONGROUPID) REFERENCES PUBLIC.CROPROTATIONGROUP(CROPROTATIONGROUPID))
ALTER TABLE PUBLIC.PLANTSPECIES ALTER COLUMN PLANTSPECIESID RESTART WITH 17
CREATE INDEX FK_PLANTSPECIES_CROPROTATIONGROUP1_IDX ON PUBLIC.PLANTSPECIES(CROPROTATIONGROUPID)
COMMENT ON TABLE PUBLIC.PLANTSPECIES IS 'A species of plant grown in the garden, for instance, ''tomato''.  A species will have zero or more varieties.U&\000D\000ANote the use of a Trigger to reflect changes to commonName into Product.'
COMMENT ON COLUMN PUBLIC.PLANTSPECIES.COMMONNAME IS 'The name by which plants of this type are usually known.'
COMMENT ON COLUMN PUBLIC.PLANTSPECIES.LATINNAME IS 'The formal horticultural name for this species.  The common name is language specific, the latin name is internationally standardised.'
COMMENT ON COLUMN PUBLIC.PLANTSPECIES.UTILITY IS 'The plant''s function in the garden, typically vegetable, ornamental, weed.'
COMMENT ON COLUMN PUBLIC.PLANTSPECIES.HARDINESS IS 'The plant''s hardiness or tenderness, typically hardy, half hardy, tender.'
COMMENT ON COLUMN PUBLIC.PLANTSPECIES.LIFETYPE IS 'Typically annual, biennial or perennial.  Variations such as ''perennial grown as annual'' (e.g. runner bean).'
COMMENT ON COLUMN PUBLIC.PLANTSPECIES.PLANTTYPE IS 'Such as climber, shrub, tree.'
CREATE MEMORY TABLE PUBLIC.PLANTVARIETY(PLANTVARIETYID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,PLANTSPECIESID INTEGER NOT NULL,COMMONNAME VARCHAR(255) NOT NULL,LATINNAME VARCHAR(255) DEFAULT NULL,SYNONYMSET VARCHAR(255) DEFAULT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,UTILITY VARCHAR(45) DEFAULT NULL,HARDINESS VARCHAR(45) DEFAULT NULL,LIFETYPE VARCHAR(45) DEFAULT NULL,PLANTTYPE VARCHAR(45) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(PLANTSPECIESID,COMMONNAME),CONSTRAINT FK_PLANTVARIETY_PLANTSPECIES FOREIGN KEY(PLANTSPECIESID) REFERENCES PUBLIC.PLANTSPECIES(PLANTSPECIESID))
ALTER TABLE PUBLIC.PLANTVARIETY ALTER COLUMN PLANTVARIETYID RESTART WITH 10
CREATE INDEX FK_PLANTVARIETY_PLANTSPECIES_IDX ON PUBLIC.PLANTVARIETY(PLANTSPECIESID)
COMMENT ON TABLE PUBLIC.PLANTVARIETY IS 'A particular variety of a plant species.  Note that the common name is NOT a unique value as different species may have varieties with the same name - e.g. Money Maker tomato AND aubergine.U&\000D\000ANote the use of a Trigger to reflect changes to commonName into Product.'
COMMENT ON COLUMN PUBLIC.PLANTVARIETY.COMMONNAME IS 'The usual name for this variety.  The values in this column are NOT unique'
COMMENT ON COLUMN PUBLIC.PLANTVARIETY.LATINNAME IS 'The formal horticultural name for this variety.  Rarely different from the species'' latin name.'
COMMENT ON COLUMN PUBLIC.PLANTVARIETY.SYNONYMSET IS 'This implements the concept of synonyms.U&\000D\000AAll varieties which are synonyms of each other will have the same value for synonymSet which is a user chosen string.  A null value means the variety is NOT a synonym.U&\000D\000ATo get a PV and all its synonyms:U&\000D\000Aselect p2.* from PV p1, PV p2 where p1.id in (...) and (p2.id = p1.id or p2.synonymSet = p1.synonymSet) and p1.PSid = p2.PSidnU&\000D\000Athis relies on the synonymSet being null if NOT in a synonymSetU&\000D\000ANote that the choice of using a String value was pretty much forced on me by the need to be nullable.  The (unexpected) benefit is that the user can chose the value and maintenance just falls out of the generators.  I had to get past the idea that a PV is a synonym of some ''correct'' PV - all names are equally valid!'
COMMENT ON COLUMN PUBLIC.PLANTVARIETY.UTILITY IS 'The plant''s function in the garden, typically vegetable, ornamental, weed.'
COMMENT ON COLUMN PUBLIC.PLANTVARIETY.HARDINESS IS 'The plant''s hardiness or tenderness, typically hardy, half hardy, tender.'
COMMENT ON COLUMN PUBLIC.PLANTVARIETY.LIFETYPE IS 'Typically annual, biennial or perennial.  Variations such as ''perennial grown as annual'' (e.g. runner bean).'
COMMENT ON COLUMN PUBLIC.PLANTVARIETY.PLANTTYPE IS 'Such as climber, shrub, tree.'
CREATE MEMORY TABLE PUBLIC.CROPPINGACTUAL(CROPPINGACTUALID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,CROPPINGPLANID INTEGER NOT NULL,PLANTSPECIESID INTEGER NOT NULL,PLANTVARIETYID INTEGER,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_CROPPINGACTUAL_CROPPINGPLAN1 FOREIGN KEY(CROPPINGPLANID) REFERENCES PUBLIC.CROPPINGPLAN(CROPPINGPLANID),CONSTRAINT FK_CROPPINGACTUAL_PLANTSPECIES1 FOREIGN KEY(PLANTSPECIESID) REFERENCES PUBLIC.PLANTSPECIES(PLANTSPECIESID),CONSTRAINT FK_CROPPINGACTUAL_PLANTVARIETY1 FOREIGN KEY(PLANTVARIETYID) REFERENCES PUBLIC.PLANTVARIETY(PLANTVARIETYID))
ALTER TABLE PUBLIC.CROPPINGACTUAL ALTER COLUMN CROPPINGACTUALID RESTART WITH 1
CREATE INDEX FK_CROPPINGACTUAL_CROPPINGPLAN1_IDX ON PUBLIC.CROPPINGACTUAL(CROPPINGPLANID)
CREATE INDEX FK_CROPPINGACTUAL_PLANTSPECIES1_IDX ON PUBLIC.CROPPINGACTUAL(PLANTSPECIESID)
CREATE INDEX FK_CROPPINGACTUAL_PLANTVARIETY1_IDX ON PUBLIC.CROPPINGACTUAL(PLANTVARIETYID)
COMMENT ON TABLE PUBLIC.CROPPINGACTUAL IS 'The plants actually grown to implement the referenced CroppingPlan'
COMMENT ON COLUMN PUBLIC.CROPPINGACTUAL.CROPPINGPLANID IS 'The plants identified were grown as part of this CroppingPlan'
COMMENT ON COLUMN PUBLIC.CROPPINGACTUAL.PLANTSPECIESID IS 'The PlantSpecies grown'
COMMENT ON COLUMN PUBLIC.CROPPINGACTUAL.PLANTVARIETYID IS 'The variety of the PlantSpecies grown.  If this value is present, plantSpeciesId MUST be present (this denormalises the database design but simplifies searching)'
CREATE MEMORY TABLE PUBLIC.AFFLICTIONEVENT(AFFLICTIONEVENTID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,AFFLICTIONID INTEGER NOT NULL,PLANTSPECIESID INTEGER DEFAULT NULL,PLANTVARIETYID INTEGER DEFAULT NULL,LOCATIONID INTEGER DEFAULT NULL,DATE DATE NOT NULL,QUANTITY VARCHAR(255) DEFAULT NULL,SEVERITY VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_AFFLICTIONEVENT_PLANTSPECIES1 FOREIGN KEY(PLANTSPECIESID) REFERENCES PUBLIC.PLANTSPECIES(PLANTSPECIESID),CONSTRAINT FK_AFFLICTIONEVENT_AFFLICTION1 FOREIGN KEY(AFFLICTIONID) REFERENCES PUBLIC.AFFLICTION(AFFLICTIONID),CONSTRAINT FK_AFFLICTIONEVENT_PLANTVARIETY1 FOREIGN KEY(PLANTVARIETYID) REFERENCES PUBLIC.PLANTVARIETY(PLANTVARIETYID),CONSTRAINT FK_AFFLICTIONEVENT_LOCATION1 FOREIGN KEY(LOCATIONID) REFERENCES PUBLIC.LOCATION(LOCATIONID))
ALTER TABLE PUBLIC.AFFLICTIONEVENT ALTER COLUMN AFFLICTIONEVENTID RESTART WITH 1
CREATE INDEX FK_AFFLICTIONEVENT_PLANTSPECIES1_IDX ON PUBLIC.AFFLICTIONEVENT(PLANTSPECIESID)
CREATE INDEX FK_AFFLICTIONEVENT_PLANTVARIETY1_IDX ON PUBLIC.AFFLICTIONEVENT(PLANTVARIETYID)
CREATE INDEX FK_AFFLICTIONEVENT_AFFLICTION1_IDX ON PUBLIC.AFFLICTIONEVENT(AFFLICTIONID)
CREATE INDEX FK_AFFLICTIONEVENT_LOCATION1_IDX ON PUBLIC.AFFLICTIONEVENT(LOCATIONID)
COMMENT ON TABLE PUBLIC.AFFLICTIONEVENT IS 'Records an observation of an affliction - for instance, an outbreak of red spider mite'
COMMENT ON COLUMN PUBLIC.AFFLICTIONEVENT.PLANTSPECIESID IS 'The problem occured on plants of this species.  If present, plantVarietyId may or may not be given.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes'''
COMMENT ON COLUMN PUBLIC.AFFLICTIONEVENT.PLANTVARIETYID IS 'The problem occured on plants of this variety.  If present, plantSpeciesId must be given.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes'''
COMMENT ON COLUMN PUBLIC.AFFLICTIONEVENT.LOCATIONID IS 'Where the affliction happened.'
COMMENT ON COLUMN PUBLIC.AFFLICTIONEVENT.DATE IS 'When an outbreak is spotted or there is some development'
COMMENT ON COLUMN PUBLIC.AFFLICTIONEVENT.QUANTITY IS 'Quantifies the Affliction event, for instance, the number of plants affected.'
COMMENT ON COLUMN PUBLIC.AFFLICTIONEVENT.SEVERITY IS 'The severity of the outbreak, e.g. ''mild'', ''severe''.'
CREATE MEMORY TABLE PUBLIC.COMMENT(COMMENTID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,OWNERID INTEGER NOT NULL,OWNERTYPE CHARACTER(2) NOT NULL,DATE DATE NOT NULL,COMMENT VARCHAR(1024) NOT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
ALTER TABLE PUBLIC.COMMENT ALTER COLUMN COMMENTID RESTART WITH 1
CREATE INDEX IX_BYOWNER ON PUBLIC.COMMENT(OWNERID,OWNERTYPE)
COMMENT ON TABLE PUBLIC.COMMENT IS 'A comment on a particular entry.  Often the main content of a husbandry item.'
COMMENT ON COLUMN PUBLIC.COMMENT.OWNERTYPE IS 'The comment is associated with a DB record of this type:U&\000D\000APC product categoryU&\000D\000ABR product brandU&\000D\000APR product\U&\000D\000ASU supplier (retailer)U&\000D\000API purchase itemU&\000D\000APU purchaseU&\000D\000ASL shopping listU&\000D\000APS plant speciesU&\000D\000APV plant varietyU&\000D\000APN plant noteU&\000D\000APY plant synonym (no longer used)U&\000D\000AHU husbandryU&\000D\000AHC husbandry classU&\000D\000AGA groundwork activityU&\000D\000AGW groundworkU&\000D\000AAC affliction classU&\000D\000AAF afflictionU&\000D\000AAE affliction eventU&\000D\000AWC weather conditionU&\000D\000AWE weatherU&\000D\000AWD weather detailU&\000D\000AWS wildlife speciesU&\000D\000AWI wildlifeU&\000D\000ARM reminderU&\000D\000ATD to-do listU&\000D\000ARW reviewU&\000D\000AJN journalU&\000D\000ALN locationU&\000D\000ARG crop rotation groupU&\000D\000ACP cropping planU&\000D\000ATH story line (thread)(no longer used)'
CREATE MEMORY TABLE PUBLIC.GROUNDWORKACTIVITY(GROUNDWORKACTIVITYID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(NAME))
ALTER TABLE PUBLIC.GROUNDWORKACTIVITY ALTER COLUMN GROUNDWORKACTIVITYID RESTART WITH 22
COMMENT ON TABLE PUBLIC.GROUNDWORKACTIVITY IS 'A type of groundwork activity.'
CREATE MEMORY TABLE PUBLIC.GROUNDWORK(GROUNDWORKID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,ACTIVITYID INTEGER NOT NULL,PLANTSPECIESID INTEGER DEFAULT NULL,PLANTVARIETYID INTEGER DEFAULT NULL,LOCATIONID INTEGER DEFAULT NULL,DATE DATE NOT NULL,QUANTITY VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_GROUNDWORK_PLANTSPECIES1 FOREIGN KEY(PLANTSPECIESID) REFERENCES PUBLIC.PLANTSPECIES(PLANTSPECIESID),CONSTRAINT FK_GROUNDWORK_ACTIVITY FOREIGN KEY(ACTIVITYID) REFERENCES PUBLIC.GROUNDWORKACTIVITY(GROUNDWORKACTIVITYID),CONSTRAINT FK_GROUNDWORK_PLANTVARIETY1 FOREIGN KEY(PLANTVARIETYID) REFERENCES PUBLIC.PLANTVARIETY(PLANTVARIETYID),CONSTRAINT FK_GROUNDWORK_LOCATION1 FOREIGN KEY(LOCATIONID) REFERENCES PUBLIC.LOCATION(LOCATIONID))
ALTER TABLE PUBLIC.GROUNDWORK ALTER COLUMN GROUNDWORKID RESTART WITH 1
CREATE INDEX FK_GROUNDWORK_ACTIVITY_IDX ON PUBLIC.GROUNDWORK(ACTIVITYID)
CREATE INDEX FK_GROUNDWORK_PLANTVARIETY1_IDX ON PUBLIC.GROUNDWORK(PLANTVARIETYID)
CREATE INDEX FK_GROUNDWORK_PLANTSPECIES1_IDX ON PUBLIC.GROUNDWORK(PLANTSPECIESID)
COMMENT ON TABLE PUBLIC.GROUNDWORK IS 'An instance of groundwork being done.'
COMMENT ON COLUMN PUBLIC.GROUNDWORK.PLANTSPECIESID IS 'The activity is for plants of this species.  If present, plantVarietyId may or may not be given.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.'
COMMENT ON COLUMN PUBLIC.GROUNDWORK.PLANTVARIETYID IS 'The activity is for plants of this variety.  If present, plantSpeciesId must be given.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.'
COMMENT ON COLUMN PUBLIC.GROUNDWORK.LOCATIONID IS 'Where the Groundwork was done.'
COMMENT ON COLUMN PUBLIC.GROUNDWORK.QUANTITY IS 'Quantifies the amount of work, e.g. ''two trenches dug''.'
CREATE MEMORY TABLE PUBLIC.HUSBANDRYCLASS(HUSBANDRYCLASSID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,TERMINAL BOOLEAN DEFAULT FALSE NOT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(NAME))
ALTER TABLE PUBLIC.HUSBANDRYCLASS ALTER COLUMN HUSBANDRYCLASSID RESTART WITH 29
COMMENT ON TABLE PUBLIC.HUSBANDRYCLASS IS 'The various types of diary entry for the husbandry lifecycle.'
COMMENT ON COLUMN PUBLIC.HUSBANDRYCLASS.TERMINAL IS 'If true, this represents an end of life event.  For instance, death, discarded, abandoned, failed.'
CREATE MEMORY TABLE PUBLIC.HUSBANDRY(HUSBANDRYID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,HUSBANDRYCLASSID INTEGER NOT NULL,PLANTSPECIESID INTEGER NOT NULL,PLANTVARIETYID INTEGER DEFAULT NULL,LOCATIONID INTEGER DEFAULT NULL,TERMINALAFFLICTIONID INTEGER DEFAULT NULL,DATE DATE NOT NULL,QUANTITY VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_HUSBANDRY_AFFLICTION1 FOREIGN KEY(TERMINALAFFLICTIONID) REFERENCES PUBLIC.AFFLICTION(AFFLICTIONID),CONSTRAINT FK_HUSBANDRY_PLANTSPECIES1 FOREIGN KEY(PLANTSPECIESID) REFERENCES PUBLIC.PLANTSPECIES(PLANTSPECIESID),CONSTRAINT FK_HUSBANDRY_HUSBANDRYCLASS1 FOREIGN KEY(HUSBANDRYCLASSID) REFERENCES PUBLIC.HUSBANDRYCLASS(HUSBANDRYCLASSID),CONSTRAINT FK_HUSBANDRY_PLANTVARIETY1 FOREIGN KEY(PLANTVARIETYID) REFERENCES PUBLIC.PLANTVARIETY(PLANTVARIETYID),CONSTRAINT FK_HUSBANDRY_LOCATION1 FOREIGN KEY(LOCATIONID) REFERENCES PUBLIC.LOCATION(LOCATIONID))
ALTER TABLE PUBLIC.HUSBANDRY ALTER COLUMN HUSBANDRYID RESTART WITH 1
CREATE INDEX FK_HUSBANDRY_PLANTSPECIES1_IDX ON PUBLIC.HUSBANDRY(PLANTSPECIESID)
CREATE INDEX FK_HUSBANDRY_PLANTVARIETY1_IDX ON PUBLIC.HUSBANDRY(PLANTVARIETYID)
CREATE INDEX FK_HUSBANDRY_HUSBANDRYCLASS1_IDX ON PUBLIC.HUSBANDRY(HUSBANDRYCLASSID)
CREATE INDEX FK_HUSBANDRY_AFFLICTION1_IDX ON PUBLIC.HUSBANDRY(TERMINALAFFLICTIONID)
CREATE INDEX FK_HUSBANDRY_LOCATION1_IDX ON PUBLIC.HUSBANDRY(LOCATIONID)
COMMENT ON TABLE PUBLIC.HUSBANDRY IS 'Diary entries for events in the life of a crop.'
COMMENT ON COLUMN PUBLIC.HUSBANDRY.PLANTSPECIESID IS 'Always required as Husbandry always refers to a plant.  The activity is for plants of this species.  plantVarietyId may or may not be given; if absent the activity is for all (current) e.g. tomato varieties, e.g for spraying.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.'
COMMENT ON COLUMN PUBLIC.HUSBANDRY.PLANTVARIETYID IS 'The activity is for plants of this variety.  If present, plantSpeciesId must be given.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.'
COMMENT ON COLUMN PUBLIC.HUSBANDRY.LOCATIONID IS 'Where the activity was carried out.'
COMMENT ON COLUMN PUBLIC.HUSBANDRY.TERMINALAFFLICTIONID IS 'If this Diary entry is for the demise of a plant, this can be used to record the guilty pest or disease.'
COMMENT ON COLUMN PUBLIC.HUSBANDRY.QUANTITY IS 'Quantifies the activity, e.g. number of plants potted on.'
CREATE MEMORY TABLE PUBLIC.JOURNAL(JOURNALID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,DATE DATE DEFAULT CURRENT_DATE NOT NULL,TITLE VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(1024) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
ALTER TABLE PUBLIC.JOURNAL ALTER COLUMN JOURNALID RESTART WITH 1
COMMENT ON TABLE PUBLIC.JOURNAL IS 'A Diary entry which is not specific to any plant, groundwork, affliction, etc - just a free hand note.'
COMMENT ON COLUMN PUBLIC.JOURNAL.DATE IS 'Date of the Journal entry'
COMMENT ON COLUMN PUBLIC.JOURNAL.TITLE IS 'A brief title for the journal entry.'
COMMENT ON COLUMN PUBLIC.JOURNAL.DESCRIPTION IS 'The full text of the entry.'
CREATE MEMORY TABLE PUBLIC.PLANTNOTE(PLANTNOTEID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,PLANTSPECIESID INTEGER NOT NULL,PLANTVARIETYID INTEGER DEFAULT NULL,TITLE VARCHAR(45) NOT NULL,NOTE VARCHAR(16777216) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(PLANTSPECIESID,PLANTVARIETYID,TITLE),CONSTRAINT FK_PLANTNOTE_PLANTSPECIES1 FOREIGN KEY(PLANTSPECIESID) REFERENCES PUBLIC.PLANTSPECIES(PLANTSPECIESID),CONSTRAINT FK_PLANTNOTE_PLANTVARIETY1 FOREIGN KEY(PLANTVARIETYID) REFERENCES PUBLIC.PLANTVARIETY(PLANTVARIETYID))
ALTER TABLE PUBLIC.PLANTNOTE ALTER COLUMN PLANTNOTEID RESTART WITH 2
CREATE INDEX FK_PLANTNOTE_PLANTSPECIES1_IDX ON PUBLIC.PLANTNOTE(PLANTSPECIESID)
CREATE INDEX FK_PLANTNOTE_PLANTVARIETY1_IDX ON PUBLIC.PLANTNOTE(PLANTVARIETYID)
COMMENT ON TABLE PUBLIC.PLANTNOTE IS 'Various types of notes, such as cultivation notes, too long for or not best organised as comments.'
COMMENT ON COLUMN PUBLIC.PLANTNOTE.PLANTSPECIESID IS 'This note is for a plant species.  Individual varieties may have their own notes as well as or instead of the variety''s notes.'
COMMENT ON COLUMN PUBLIC.PLANTNOTE.PLANTVARIETYID IS 'If given, plantSpeciesId must also be given; this allows easy retrieval of notes for a species and any variety specific notes.'
CREATE MEMORY TABLE PUBLIC.RETAILER(RETAILERID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,ADDRESS VARCHAR(255) DEFAULT NULL,WEBSITE VARCHAR(255) DEFAULT NULL,EMAIL VARCHAR(255) DEFAULT NULL,PHONE VARCHAR(255),MOBILE VARCHAR(255) DEFAULT NULL,OWNBRANDONLY BOOLEAN DEFAULT FALSE NOT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(NAME))
ALTER TABLE PUBLIC.RETAILER ALTER COLUMN RETAILERID RESTART WITH 1
COMMENT ON TABLE PUBLIC.RETAILER IS 'Anywhere that items are purchased, includes garden centres, supermarkets, online shopping, etc, etc.  (A more obvious name would be Supplier but that causes a potential name clash in Java)'
COMMENT ON COLUMN PUBLIC.RETAILER.OWNBRANDONLY IS 'If true, this retailer ONLY supplies Products of their own brand'
CREATE MEMORY TABLE PUBLIC.PRODUCTBRAND(PRODUCTBRANDID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,OWNBRANDRETAILERID INTEGER DEFAULT NULL,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(NAME),CONSTRAINT FK_PRODUCTBRAND_RETAILER1 FOREIGN KEY(OWNBRANDRETAILERID) REFERENCES PUBLIC.RETAILER(RETAILERID))
ALTER TABLE PUBLIC.PRODUCTBRAND ALTER COLUMN PRODUCTBRANDID RESTART WITH 1
CREATE INDEX FK_PRODUCTBRAND_RETAILER1_IDX ON PUBLIC.PRODUCTBRAND(OWNBRANDRETAILERID)
COMMENT ON TABLE PUBLIC.PRODUCTBRAND IS 'A brand for one or more Products - such as Verve ({@literal B&Q} own brand)'
COMMENT ON COLUMN PUBLIC.PRODUCTBRAND.OWNBRANDRETAILERID IS 'If this Brand is a retailer''s ''own brand''.'
CREATE MEMORY TABLE PUBLIC.PRODUCTCATEGORY(PRODUCTCATEGORYID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,PLANTLIKE BOOLEAN DEFAULT FALSE NOT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(NAME))
ALTER TABLE PUBLIC.PRODUCTCATEGORY ALTER COLUMN PRODUCTCATEGORYID RESTART WITH 13
COMMENT ON TABLE PUBLIC.PRODUCTCATEGORY IS 'Categories of purchased products.'
COMMENT ON COLUMN PUBLIC.PRODUCTCATEGORY.PLANTLIKE IS 'True if this category is some sort of plant - tubers, seeds, etc.  This is a denormalisation to simplify Product selection when adding PurchaseItems'
CREATE MEMORY TABLE PUBLIC.PRODUCT(PRODUCTID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,PRODUCTCATEGORYID INTEGER NOT NULL,PLANTSPECIESID INTEGER DEFAULT NULL,PLANTVARIETYID INTEGER DEFAULT NULL,PRODUCTBRANDID INTEGER DEFAULT NULL,NAME VARCHAR(255) NOT NULL,NAMEDETAIL_1 VARCHAR(255) DEFAULT NULL,NAMEDETAIL_2 VARCHAR(255) DEFAULT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(PRODUCTCATEGORYID,PRODUCTBRANDID,NAME,NAMEDETAIL_1,NAMEDETAIL_2),CONSTRAINT FK_PRODUCT_PLANTSPECIES1 FOREIGN KEY(PLANTSPECIESID) REFERENCES PUBLIC.PLANTSPECIES(PLANTSPECIESID),CONSTRAINT FK_PRODUCT_PRODUCTBRAND1 FOREIGN KEY(PRODUCTBRANDID) REFERENCES PUBLIC.PRODUCTBRAND(PRODUCTBRANDID),CONSTRAINT FK_PRODUCT_PLANTVARIETY1 FOREIGN KEY(PLANTVARIETYID) REFERENCES PUBLIC.PLANTVARIETY(PLANTVARIETYID),CONSTRAINT FK_PRODUCT_PRODUCTCATEGORY1 FOREIGN KEY(PRODUCTCATEGORYID) REFERENCES PUBLIC.PRODUCTCATEGORY(PRODUCTCATEGORYID))
ALTER TABLE PUBLIC.PRODUCT ALTER COLUMN PRODUCTID RESTART WITH 1
CREATE INDEX FK_PRODUCT_PRODUCTCATEGORY1_IDX ON PUBLIC.PRODUCT(PRODUCTCATEGORYID)
CREATE INDEX FK_PRODUCT_PLANTVARIETY1_IDX ON PUBLIC.PRODUCT(PLANTVARIETYID)
CREATE INDEX FK_PRODUCT_PLANTSPECIES1_IDX ON PUBLIC.PRODUCT(PLANTSPECIESID)
CREATE INDEX FK_PRODUCT_PRODUCTBRAND1_IDX ON PUBLIC.PRODUCT(PRODUCTBRANDID)
COMMENT ON TABLE PUBLIC.PRODUCT IS 'A purchasable product.'
COMMENT ON COLUMN PUBLIC.PRODUCT.PLANTSPECIESID IS 'The product is plants of this species.  If present, plantVarietyId may or may not be given.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.'
COMMENT ON COLUMN PUBLIC.PRODUCT.PLANTVARIETYID IS 'The product is plants of this species.  If present, plantSpeciesId must be given.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.'
COMMENT ON COLUMN PUBLIC.PRODUCT.NAME IS 'For plant like products (seeds, etc) the plant species common name.  Note the use of a trigger to maintain this if plantSpecies names are changed'
COMMENT ON COLUMN PUBLIC.PRODUCT.NAMEDETAIL_1 IS 'For plant-like products (seeds, etc) the plant variety (if present) common name.  Note the use of a trigger to maintain this if plantVariety names are changed'
COMMENT ON COLUMN PUBLIC.PRODUCT.NAMEDETAIL_2 IS 'Not currently used - supported in most code but NOT in the UI.'
CREATE MEMORY TABLE PUBLIC.PURCHASE(PURCHASEID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,RETAILERID INTEGER NOT NULL,DATE DATE NOT NULL,TOTALCOST DECIMAL(5,2) DEFAULT NULL,CURRENCY CHARACTER(3) DEFAULT NULL,ORDERNO VARCHAR(255) DEFAULT NULL,INVOICENO VARCHAR(255) DEFAULT NULL,DELIVERYDATE DATE DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_PURCHASE_SUPPLIER1 FOREIGN KEY(RETAILERID) REFERENCES PUBLIC.RETAILER(RETAILERID))
ALTER TABLE PUBLIC.PURCHASE ALTER COLUMN PURCHASEID RESTART WITH 1
CREATE INDEX FK_PURCHASE_SUPPLIER1_IDX ON PUBLIC.PURCHASE(RETAILERID)
COMMENT ON TABLE PUBLIC.PURCHASE IS 'Description of a purchase from a retailer.  The items purchased are recorded as PurchaseItems.'
COMMENT ON COLUMN PUBLIC.PURCHASE.CURRENCY IS 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.'
CREATE MEMORY TABLE PUBLIC.PURCHASEITEM(PURCHASEITEMID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,PURCHASEID INTEGER NOT NULL,PRODUCTID INTEGER NOT NULL,QUANTITY DECIMAL(8,3) DEFAULT NULL,UNIT VARCHAR(255) DEFAULT NULL,ITEMCOST DECIMAL(5,2) DEFAULT NULL,CURRENCY CHARACTER(3) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_PURCHASEITEM_PRODUCT1 FOREIGN KEY(PRODUCTID) REFERENCES PUBLIC.PRODUCT(PRODUCTID),CONSTRAINT FK_PURCHASEITEM_PURCHASE1 FOREIGN KEY(PURCHASEID) REFERENCES PUBLIC.PURCHASE(PURCHASEID))
ALTER TABLE PUBLIC.PURCHASEITEM ALTER COLUMN PURCHASEITEMID RESTART WITH 1
CREATE INDEX FK_PURCHASEITEM_PURCHASE1_IDX ON PUBLIC.PURCHASEITEM(PURCHASEID)
CREATE INDEX FK_PURCHASEITEM_PRODUCT1_IDX ON PUBLIC.PURCHASEITEM(PRODUCTID)
COMMENT ON TABLE PUBLIC.PURCHASEITEM IS 'An item in a Purchase.'
COMMENT ON COLUMN PUBLIC.PURCHASEITEM.CURRENCY IS 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.'
CREATE MEMORY TABLE PUBLIC.REMINDER(REMINDERID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,PLANTSPECIESID INTEGER DEFAULT NULL,PLANTVARIETYID INTEGER DEFAULT NULL,HUSBANDRYCLASSID INTEGER DEFAULT NULL,GROUNDWORKACTIVITYID INTEGER DEFAULT NULL,HUSBANDRYID INTEGER DEFAULT NULL,PURCHASEITEMID INTEGER DEFAULT NULL,SHOWFROM DATE NOT NULL,SINGLESHOT BOOLEAN DEFAULT TRUE NOT NULL,REPEATINTERVAL CHARACTER(1) DEFAULT 'W',REPEATQUANTIFIER INTEGER DEFAULT 0,REPEATUNTIL DATE DEFAULT NULL,DESCRIPTION VARCHAR(1024) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_REMINDER_GROUNDWORKACTIVITY1 FOREIGN KEY(GROUNDWORKACTIVITYID) REFERENCES PUBLIC.GROUNDWORKACTIVITY(GROUNDWORKACTIVITYID),CONSTRAINT FK_REMINDER_HUSBANDRYCLASS1 FOREIGN KEY(HUSBANDRYCLASSID) REFERENCES PUBLIC.HUSBANDRYCLASS(HUSBANDRYCLASSID),CONSTRAINT FK_REMINDER_PLANTSPECIES1 FOREIGN KEY(PLANTSPECIESID) REFERENCES PUBLIC.PLANTSPECIES(PLANTSPECIESID),CONSTRAINT FK_REMINDER_PLANTVARIETY1 FOREIGN KEY(PLANTVARIETYID) REFERENCES PUBLIC.PLANTVARIETY(PLANTVARIETYID),CONSTRAINT FK_REMINDER_HUSBANDRY1 FOREIGN KEY(HUSBANDRYID) REFERENCES PUBLIC.HUSBANDRY(HUSBANDRYID),CONSTRAINT FK_REMINDER_PURCHASEITEM1 FOREIGN KEY(PURCHASEITEMID) REFERENCES PUBLIC.PURCHASEITEM(PURCHASEITEMID))
ALTER TABLE PUBLIC.REMINDER ALTER COLUMN REMINDERID RESTART WITH 1
CREATE INDEX FK_REMINDER_PLANTVARIETY1_IDX ON PUBLIC.REMINDER(PLANTVARIETYID)
CREATE INDEX FK_REMINDER_HUSBANDRYCLASS1_IDX ON PUBLIC.REMINDER(HUSBANDRYCLASSID)
CREATE INDEX FK_REMINDER_GROUNDWORKACTIVITY1_IDX ON PUBLIC.REMINDER(GROUNDWORKACTIVITYID)
CREATE INDEX FK_REMINDER_PLANTSPECIES1_IDX ON PUBLIC.REMINDER(PLANTSPECIESID)
CREATE INDEX FK_REMINDER_HUSBANDRY1_IDX ON PUBLIC.REMINDER(HUSBANDRYID)
CREATE INDEX FK_REMINDER_PURCHASEITEM1_IDX ON PUBLIC.REMINDER(PURCHASEITEMID)
COMMENT ON TABLE PUBLIC.REMINDER IS 'A list of reminders of things to do. Reminders can be ''one-off'' or repeated at various intervals (e.g. ''sow lettuce in March'' shown every year).'
COMMENT ON COLUMN PUBLIC.REMINDER.PLANTSPECIESID IS 'The reminder is for an activity on plants of this species.  If present, plantVarietyId may or may not be given.'
COMMENT ON COLUMN PUBLIC.REMINDER.PLANTVARIETYID IS 'The reminder is for an activity on plants of this variety.  If present, plantSpeciesId must be given.'
COMMENT ON COLUMN PUBLIC.REMINDER.HUSBANDRYCLASSID IS 'The reminder is to perform this activity.'
COMMENT ON COLUMN PUBLIC.REMINDER.GROUNDWORKACTIVITYID IS 'The reminder is to perform this activity.'
COMMENT ON COLUMN PUBLIC.REMINDER.HUSBANDRYID IS 'The Husbandry editor can set up a ToDoList entry intended as a ''watch for this to happen'' (e.g. on sowing, add a ''watch for germination'').U&\000D\000AThis field is the id of that parent/ancestor Husbandry event so when this ToDoList entry is processed the appropriate Storyline links can be set up.U&\000D\000ANormally, this action will generate a ToDoList entry; this field (and the purchaseItemId field) are to allow that ToDo to be delayed.'
COMMENT ON COLUMN PUBLIC.REMINDER.PURCHASEITEMID IS 'The Purchase editor can set up a ToDoList entry intended as a ''watch for this to happen'' (e.g. for seed purchasde, add a ''watch for sowing'').U&\000D\000AThis field is the id of that parent/ancestor PurchaseItem event so when this ToDoList entry is processed the appropriate Storyline links can be set up.U&\000D\000ANormally, this action will generate a ToDoList entry; this field (and the husbandryId field) are to allow that ToDo to be delayed.'
COMMENT ON COLUMN PUBLIC.REMINDER.SHOWFROM IS 'For a single shot reminder, the first date it will be shown on.  For a repeating reminder, the current or next interval when the reminder will be active.'
COMMENT ON COLUMN PUBLIC.REMINDER.SINGLESHOT IS 'If true, the Reminder is only activated once.'
COMMENT ON COLUMN PUBLIC.REMINDER.REPEATINTERVAL IS 'For a repeating reminder, the unit of the repeat interval (see repeatQuantifier).U&\000D\000AD - dailyU&\000D\000AW - weeklyU&\000D\000AM - monthlyU&\000D\000AY - annuallyU&\000D\000AA value is required for repeating reminders so set a default for safety'
COMMENT ON COLUMN PUBLIC.REMINDER.REPEATQUANTIFIER IS 'For a repeating Reminder, the number of repeatIntervals between repeats (see repeatInterval).  So ''weekly'' and ''2'' means every fortnight.  0 means no repetition (avoids complications with nullable ints in the code - an impossible combination!)  A value is required for repeating reminders so set non-null with a default for safety'
COMMENT ON COLUMN PUBLIC.REMINDER.REPEATUNTIL IS 'For a repeating reminder, the LAST time this reminder will be shown.  Null means ''repeat forever''.'
COMMENT ON COLUMN PUBLIC.REMINDER.DESCRIPTION IS 'The actual text of the reminder.'
CREATE MEMORY TABLE PUBLIC.RETAILERHASPRODUCT(RETAILERHASPRODUCTID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,RETAILERID INTEGER NOT NULL,PRODUCTID INTEGER NOT NULL,SKU VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_SUPPLIER_HAS_PRODUCT_PRODUCT1 FOREIGN KEY(PRODUCTID) REFERENCES PUBLIC.PRODUCT(PRODUCTID),CONSTRAINT FK_SUPPLIER_HAS_PRODUCT_SUPPLIER1 FOREIGN KEY(RETAILERID) REFERENCES PUBLIC.RETAILER(RETAILERID))
ALTER TABLE PUBLIC.RETAILERHASPRODUCT ALTER COLUMN RETAILERHASPRODUCTID RESTART WITH 1
CREATE INDEX FK_SUPPLIER_HAS_PRODUCT_PRODUCT1_IDX ON PUBLIC.RETAILERHASPRODUCT(PRODUCTID)
CREATE INDEX FK_SUPPLIER_HAS_PRODUCT_SUPPLIER1_IDX ON PUBLIC.RETAILERHASPRODUCT(RETAILERID)
COMMENT ON TABLE PUBLIC.RETAILERHASPRODUCT IS 'Implements the many-many link between retailers and products.  Many products, particularly own-brand products will only have a single supplier, others may be available from many retailers.'
COMMENT ON COLUMN PUBLIC.RETAILERHASPRODUCT.SKU IS 'The product code for a particular product at a particular supplier'
CREATE MEMORY TABLE PUBLIC.REVIEW(REVIEWID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,DATE DATE DEFAULT CURRENT_DATE NOT NULL,YEARINREVIEW DATE DEFAULT CURRENT_DATE NOT NULL,COVERFROM DATE DEFAULT NULL,COVERTO DATE DEFAULT NULL,TITLE VARCHAR(255),DESCRIPTION VARCHAR(16777216) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
ALTER TABLE PUBLIC.REVIEW ALTER COLUMN REVIEWID RESTART WITH 1
COMMENT ON TABLE PUBLIC.REVIEW IS 'A Diary entry to review the performance of a crop, affliction (or its treatment) or a location - just a free hand note.'
COMMENT ON COLUMN PUBLIC.REVIEW.DATE IS 'Date the Review was written.'
COMMENT ON COLUMN PUBLIC.REVIEW.YEARINREVIEW IS 'The calendar period covered by this review.  Only the YEAR part should be used'
COMMENT ON COLUMN PUBLIC.REVIEW.COVERFROM IS 'The start date of the review period'
COMMENT ON COLUMN PUBLIC.REVIEW.COVERTO IS 'The end date of the review period'
COMMENT ON COLUMN PUBLIC.REVIEW.TITLE IS 'A brief title for the review.'
COMMENT ON COLUMN PUBLIC.REVIEW.DESCRIPTION IS 'The full text of the review.'
CREATE MEMORY TABLE PUBLIC.REVIEWREFERENCES(REVIEWREFERENCESID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,REVIEWID INTEGER NOT NULL,PLANTSPECIESID INTEGER DEFAULT NULL,PLANTVARIETYID INTEGER DEFAULT NULL,HUSBANDRYCLASSID INTEGER DEFAULT NULL,GROUNDWORKACTIVITYID INTEGER DEFAULT NULL,AFFLICTIONID INTEGER,LOCATIONID INTEGER DEFAULT NULL,PRODUCTID INTEGER DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_REVIEWREFERENCES_REVIEW1 FOREIGN KEY(REVIEWID) REFERENCES PUBLIC.REVIEW(REVIEWID),CONSTRAINT FK_REVIEWREFERENCES_PLANTSPECIES1 FOREIGN KEY(PLANTSPECIESID) REFERENCES PUBLIC.PLANTSPECIES(PLANTSPECIESID),CONSTRAINT FK_REVIEWREFERENCES_PLANTVARIETY1 FOREIGN KEY(PLANTVARIETYID) REFERENCES PUBLIC.PLANTVARIETY(PLANTVARIETYID),CONSTRAINT FK_REVIEWREFERENCES_HUSBANDRYCLASS1 FOREIGN KEY(HUSBANDRYCLASSID) REFERENCES PUBLIC.HUSBANDRYCLASS(HUSBANDRYCLASSID),CONSTRAINT FK_REVIEWREFERENCES_GROUNDWORKACTIVITY1 FOREIGN KEY(GROUNDWORKACTIVITYID) REFERENCES PUBLIC.GROUNDWORKACTIVITY(GROUNDWORKACTIVITYID),CONSTRAINT FK_REVIEWREFERENCES_AFFLICTION1 FOREIGN KEY(AFFLICTIONID) REFERENCES PUBLIC.AFFLICTION(AFFLICTIONID),CONSTRAINT FK_REVIEWREFERENCES_LOCATION1 FOREIGN KEY(LOCATIONID) REFERENCES PUBLIC.LOCATION(LOCATIONID),CONSTRAINT FK_REVIEWREFERENCES_PRODUCT1 FOREIGN KEY(PRODUCTID) REFERENCES PUBLIC.PRODUCT(PRODUCTID))
ALTER TABLE PUBLIC.REVIEWREFERENCES ALTER COLUMN REVIEWREFERENCESID RESTART WITH 1
CREATE INDEX FK_REVIEWREFERENCES_REVIEW1_IDX ON PUBLIC.REVIEWREFERENCES(REVIEWID)
CREATE INDEX FK_REVIEWREFERENCES_PLANTSPECIES1_IDX ON PUBLIC.REVIEWREFERENCES(PLANTSPECIESID)
CREATE INDEX FK_REVIEWREFERENCES_PLANTVARIETY1_IDX ON PUBLIC.REVIEWREFERENCES(PLANTVARIETYID)
CREATE INDEX FK_REVIEWREFERENCES_HUSBANDRYCLASS1_IDX ON PUBLIC.REVIEWREFERENCES(HUSBANDRYCLASSID)
CREATE INDEX FK_REVIEWREFERENCES_GROUNDWORKACTIVITY1_IDX ON PUBLIC.REVIEWREFERENCES(GROUNDWORKACTIVITYID)
CREATE INDEX FK_REVIEWREFERENCES_AFFLICTION1_IDX ON PUBLIC.REVIEWREFERENCES(AFFLICTIONID)
CREATE INDEX FK_REVIEWREFERENCES_LOCATION1_IDX ON PUBLIC.REVIEWREFERENCES(LOCATIONID)
CREATE INDEX FK_REVIEWREFERENCES_PRODUCT1_IDX ON PUBLIC.REVIEWREFERENCES(PRODUCTID)
COMMENT ON TABLE PUBLIC.REVIEWREFERENCES IS 'A Review will typically refer to more than one plant species or variety, or pest or Husbandry, etc.  This table holds those multiple references.  Each entry may contain 1 or many of the key values (i.e. the data may be compressed into fewer rows than expected).  Note that if PlantVariety is given, the corresponding PlantSpecies MUST be given.'
COMMENT ON COLUMN PUBLIC.REVIEWREFERENCES.PLANTSPECIESID IS 'The species of plant being reviewed'
COMMENT ON COLUMN PUBLIC.REVIEWREFERENCES.PLANTVARIETYID IS 'The variety of plant being reviewed.'
COMMENT ON COLUMN PUBLIC.REVIEWREFERENCES.HUSBANDRYCLASSID IS 'The husbandry (life cycle) activity being reviewed'
COMMENT ON COLUMN PUBLIC.REVIEWREFERENCES.GROUNDWORKACTIVITYID IS 'The type of groundwork activity being reviewd.'
COMMENT ON COLUMN PUBLIC.REVIEWREFERENCES.AFFLICTIONID IS 'The affliction (and/or its treatment) being reviewed.'
COMMENT ON COLUMN PUBLIC.REVIEWREFERENCES.LOCATIONID IS 'The location being reviewed.'
CREATE MEMORY TABLE PUBLIC.SALE(SALEID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,DATE DATE NOT NULL,PURCHASEDBY VARCHAR(255) DEFAULT NULL,TOTALCOST DECIMAL(5,2) DEFAULT NULL,CURRENCY CHARACTER(3) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
ALTER TABLE PUBLIC.SALE ALTER COLUMN SALEID RESTART WITH 1
COMMENT ON TABLE PUBLIC.SALE IS 'A sale of produce.'
COMMENT ON COLUMN PUBLIC.SALE.CURRENCY IS 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.'
CREATE MEMORY TABLE PUBLIC.SALEITEM(SALEITEMID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,SALEID INTEGER NOT NULL,PLANTSPECIESID INTEGER NOT NULL,PLANTVARIETYID INTEGER DEFAULT NULL,QUANTITY DECIMAL(8,3) DEFAULT NULL,UNIT VARCHAR(255) DEFAULT NULL,ITEMCOST DECIMAL(5,2) DEFAULT NULL,CURRENCY CHARACTER(3) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_SALEITEM_PLANTSPECIES1 FOREIGN KEY(PLANTSPECIESID) REFERENCES PUBLIC.PLANTSPECIES(PLANTSPECIESID),CONSTRAINT FK_SALEITEM_PLANTVARIETY1 FOREIGN KEY(PLANTVARIETYID) REFERENCES PUBLIC.PLANTVARIETY(PLANTVARIETYID),CONSTRAINT FK_SALEITEM_SALE1 FOREIGN KEY(SALEID) REFERENCES PUBLIC.SALE(SALEID))
ALTER TABLE PUBLIC.SALEITEM ALTER COLUMN SALEITEMID RESTART WITH 1
CREATE INDEX FK_SALEITEM_SALE1_IDX ON PUBLIC.SALEITEM(SALEID)
CREATE INDEX FK_SALEITEM_PLANTSPECIES1_IDX ON PUBLIC.SALEITEM(PLANTSPECIESID)
CREATE INDEX FK_SALEITEM_PLANTVARIETY1_IDX ON PUBLIC.SALEITEM(PLANTVARIETYID)
COMMENT ON TABLE PUBLIC.SALEITEM IS 'An item in a sale of produce.'
COMMENT ON COLUMN PUBLIC.SALEITEM.CURRENCY IS 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.'
CREATE MEMORY TABLE PUBLIC.SHOPPINGLIST(SHOPPINGLISTID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,PRODUCTID INTEGER DEFAULT NULL,NONSPECIFICITEM VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_SHOPPINGLIST_PRODUCT1 FOREIGN KEY(PRODUCTID) REFERENCES PUBLIC.PRODUCT(PRODUCTID))
ALTER TABLE PUBLIC.SHOPPINGLIST ALTER COLUMN SHOPPINGLISTID RESTART WITH 1
CREATE INDEX FK_SHOPPINGLIST_PRODUCT1_IDX ON PUBLIC.SHOPPINGLIST(PRODUCTID)
COMMENT ON TABLE PUBLIC.SHOPPINGLIST IS 'A list of items which need to be acquired.'
COMMENT ON COLUMN PUBLIC.SHOPPINGLIST.PRODUCTID IS 'A specific product, e.g. ''Jeyes Fluid 1l''.'
COMMENT ON COLUMN PUBLIC.SHOPPINGLIST.NONSPECIFICITEM IS 'Any item which is not an identified product - e.g. ''celery seed''.'
CREATE MEMORY TABLE PUBLIC.STORYLINEINDEX(STORYLINEINDEXID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,ANCESTORID INTEGER NOT NULL,ANCESTORTYPE CHARACTER(2) NOT NULL,DESCENDANTID INTEGER NOT NULL,DESCENDANTTYPE CHARACTER(2) NOT NULL,DEPTH INTEGER DEFAULT 0 NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
ALTER TABLE PUBLIC.STORYLINEINDEX ALTER COLUMN STORYLINEINDEXID RESTART WITH 1
CREATE INDEX ANCESTORID ON PUBLIC.STORYLINEINDEX(ANCESTORID,ANCESTORTYPE)
CREATE INDEX DESCENDANTID ON PUBLIC.STORYLINEINDEX(DESCENDANTID,DESCENDANTTYPE)
COMMENT ON TABLE PUBLIC.STORYLINEINDEX IS 'This is the ''closure table'' for the storyline (thread) hierarchy.  Given a tree structureU&\000D\000AA->B->C, A->DU&\000D\000Athere is an entry for each link, chased all the way back up:U&\000D\000A(A,A,0) (A,B,1) (B,B,0) (A,C,2) (C,C,0) (B,C,1),U&\000D\000A(A,D,1) (D,D,0)U&\000D\000AIt''s easy to get the id of every ancestor or descendant of a given node.U&\000D\000AThe self-referencing nodes (A,A) make it easy to insert new nodes (single SQL rather than several bits).U&\000D\000AMultiple parents are supported (cf a BoM structure).U&\000D\000AFor appropriate SQL see, e.g.,U&\000D\000Ahttps://www.slideshare.net/billkarwin/models-for-hierarchical-data (slide 41 et seq)U&\000D\000Aand alsoU&\000D\000Ahttps://gist.github.com/ekillaby/2377806U&\000D\000Aand alsoU&\000D\000Ahttp://karwin.blogspot.co.uk/2010/03/rendering-trees-with-closure-tables.html'
COMMENT ON COLUMN PUBLIC.STORYLINEINDEX.ANCESTORID IS 'The database key of the ancestor'
COMMENT ON COLUMN PUBLIC.STORYLINEINDEX.ANCESTORTYPE IS 'The type of the ancestor.U&\000D\000AAE - affliction eventU&\000D\000AHU - husbandryU&\000D\000AGW - groundworkU&\000D\000APU - purchaseU&\000D\000API - purchase item'
COMMENT ON COLUMN PUBLIC.STORYLINEINDEX.DESCENDANTID IS 'The database key of the descendant'
COMMENT ON COLUMN PUBLIC.STORYLINEINDEX.DESCENDANTTYPE IS 'The type of the ancestor.U&\000D\000AAE - affliction eventU&\000D\000AHU - husbandryU&\000D\000AGW - groundworkU&\000D\000ASA - saleU&\000D\000ASI - sale item'
COMMENT ON COLUMN PUBLIC.STORYLINEINDEX.DEPTH IS 'How far down the tree the descendent node is compared to the ancestor'
CREATE MEMORY TABLE PUBLIC.TODOLIST(TODOLISTID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,PLANTSPECIESID INTEGER DEFAULT NULL,PLANTVARIETYID INTEGER DEFAULT NULL,HUSBANDRYCLASSID INTEGER DEFAULT NULL,GROUNDWORKACTIVITYID INTEGER DEFAULT NULL,HUSBANDRYID INTEGER DEFAULT NULL,PURCHASEITEMID INTEGER DEFAULT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_TODOLIST_HUSBANDRY1 FOREIGN KEY(HUSBANDRYID) REFERENCES PUBLIC.HUSBANDRY(HUSBANDRYID),CONSTRAINT FK_TODOLIST_HUSBANDRYCLASS1 FOREIGN KEY(HUSBANDRYCLASSID) REFERENCES PUBLIC.HUSBANDRYCLASS(HUSBANDRYCLASSID),CONSTRAINT FK_TODOLIST_PLANTSPECIES1 FOREIGN KEY(PLANTSPECIESID) REFERENCES PUBLIC.PLANTSPECIES(PLANTSPECIESID),CONSTRAINT FK_TODOLIST_PLANTVARIETY1 FOREIGN KEY(PLANTVARIETYID) REFERENCES PUBLIC.PLANTVARIETY(PLANTVARIETYID),CONSTRAINT FK_TABLE1TODOLIST_GROUNDWORKACTIVITY1 FOREIGN KEY(GROUNDWORKACTIVITYID) REFERENCES PUBLIC.GROUNDWORKACTIVITY(GROUNDWORKACTIVITYID),CONSTRAINT FK_TODOLIST_PURCHASEITEM1 FOREIGN KEY(PURCHASEITEMID) REFERENCES PUBLIC.PURCHASEITEM(PURCHASEITEMID))
ALTER TABLE PUBLIC.TODOLIST ALTER COLUMN TODOLISTID RESTART WITH 1
CREATE INDEX FK_TABLE1TODOLIST_GROUNDWORKACTIVITY1_IDX ON PUBLIC.TODOLIST(GROUNDWORKACTIVITYID)
CREATE INDEX FK_TODOLIST_HUSBANDRYCLASS1_IDX ON PUBLIC.TODOLIST(HUSBANDRYCLASSID)
CREATE INDEX FK_TODOLIST_PLANTVARIETY1_IDX ON PUBLIC.TODOLIST(PLANTVARIETYID)
CREATE INDEX FK_TODOLIST_PLANTSPECIES1_IDX ON PUBLIC.TODOLIST(PLANTSPECIESID)
CREATE INDEX FK_TODOLIST_HUSBANDRY1_IDX ON PUBLIC.TODOLIST(HUSBANDRYID)
CREATE INDEX FK_TODOLIST_PURCHASEITEM1_IDX ON PUBLIC.TODOLIST(PURCHASEITEMID)
COMMENT ON TABLE PUBLIC.TODOLIST IS 'A list of tasks to be done.  Reminders are scheduled, possibly repeating and will create items here when they are due.  This list is not user updated - users create reminders which generate todo items'
COMMENT ON COLUMN PUBLIC.TODOLIST.HUSBANDRYID IS 'The Husbandry editor can set up a ToDoList entry intended as a ''watch for this to happen'' (e.g. on sowing, add a ''watch for germination'').  This field is the id of that parent/ancestor Husbandry event so when this ToDoList entry is processed, the appropriate Storyline links can be set up.'
COMMENT ON COLUMN PUBLIC.TODOLIST.PURCHASEITEMID IS 'Used for a ''watch for'' item from a Purchase (e.g. seed purchase will have a ''sow'' watch-for'
CREATE MEMORY TABLE PUBLIC.WEATHERCONDITION(WEATHERCONDITIONID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(NAME))
ALTER TABLE PUBLIC.WEATHERCONDITION ALTER COLUMN WEATHERCONDITIONID RESTART WITH 1
COMMENT ON TABLE PUBLIC.WEATHERCONDITION IS 'The types of weather observed as noteworthy.  For daily observations, use the Weather Details table.'
COMMENT ON COLUMN PUBLIC.WEATHERCONDITION.NAME IS '''condition'' is a reserved word in MySQL'
CREATE MEMORY TABLE PUBLIC.WEATHER(WEATHERID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,WEATHERCONDITIONID INTEGER NOT NULL,DATE DATE NOT NULL,SEVERITY VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_WEATHER_WEATHERTYPE1 FOREIGN KEY(WEATHERCONDITIONID) REFERENCES PUBLIC.WEATHERCONDITION(WEATHERCONDITIONID))
ALTER TABLE PUBLIC.WEATHER ALTER COLUMN WEATHERID RESTART WITH 1
CREATE INDEX FK_WEATHER_WEATHERTYPE1_IDX ON PUBLIC.WEATHER(WEATHERCONDITIONID)
COMMENT ON TABLE PUBLIC.WEATHER IS 'A noteworthy weather event.'
CREATE MEMORY TABLE PUBLIC.WEATHERDETAIL(WEATHERDETAILID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,DATE DATE NOT NULL,TIMEAT TIME DEFAULT NULL,TIMEFROM TIME DEFAULT NULL,TIMETO TIME DEFAULT NULL,TEMPERATUREMAX DOUBLE DEFAULT NULL,TEMPERATUREMIN DOUBLE DEFAULT NULL,TEMPERATURESCALE CHARACTER(2) DEFAULT 'C ',PRESSUREMAX DOUBLE DEFAULT NULL,PRESSUREMIN DOUBLE DEFAULT NULL,PRESSUREAVG DOUBLE DEFAULT NULL,PRESSURESCALE CHARACTER(5) DEFAULT 'mb   ',HUMIDITYMAX DOUBLE DEFAULT NULL,HUMIDITYMIN DOUBLE DEFAULT NULL,HUMIDITYAVG DOUBLE DEFAULT NULL,WINDMAX DOUBLE DEFAULT NULL,WINDAVG DOUBLE DEFAULT NULL,WINDMIN DOUBLE DEFAULT NULL,WINDGUST DOUBLE DEFAULT NULL,WINDSCALE CHARACTER(2) DEFAULT 'M ',RAINFALL DOUBLE DEFAULT NULL,RAINSCALE CHARACTER(3) DEFAULT NULL,SUNSHINE DOUBLE DEFAULT NULL,CLOUDCOVER INTEGER DEFAULT NULL,CLOUDCOVERUNIT CHARACTER(3) DEFAULT '8  ',LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
ALTER TABLE PUBLIC.WEATHERDETAIL ALTER COLUMN WEATHERDETAILID RESTART WITH 1
COMMENT ON TABLE PUBLIC.WEATHERDETAIL IS 'Detailed measurements of the weather, possibly many per day.'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.DATE IS 'Date of the Weather Detail being recorded.  If timeFrom and timeTo are absent, this is a summary for the whole day'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.TIMEAT IS 'The observation is a spot value at this time on the given date'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.TIMEFROM IS 'The Weather Detail spans a period from this time to timeTo.  If timeFrom and timeTo are not present the Detail is for the whole day unless timeAt is given'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.TIMETO IS 'The Weather Detail spans a period from timeFrom to this time.  If timeFrom and timeTo are not present the Detail is for the whole day unless timeAt is given'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.TEMPERATURESCALE IS 'F - Fahrenheit, C - Celsius'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.PRESSUREMAX IS 'Maximum air pressure recorded in the period covered'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.PRESSUREMIN IS 'Minimum air pressure recorded in the period covered'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.PRESSUREAVG IS 'Average air pressure recorded in the period covered'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.PRESSURESCALE IS 'mb - milli Bar, hPa - standard unit, exactly equivalent to mb inHg - inches of mercury'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.HUMIDITYMAX IS 'Maximum relative humidity as a percentage during in the period covered'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.HUMIDITYMIN IS 'Minimum relative humidity as a percentage during in the period covered'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.HUMIDITYAVG IS 'Average relative humidity as a percentage during in the period covered'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.WINDSCALE IS 'B - Beaufort scale, M - Miles per hour, K - km per hour'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.RAINFALL IS 'Rain fall for this period, in the units specified'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.RAINSCALE IS 'in - inches, mm - millimetres, cm - centimetres'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.SUNSHINE IS 'Hours'
COMMENT ON COLUMN PUBLIC.WEATHERDETAIL.CLOUDCOVERUNIT IS 'How the cloud cover is measured: 8 - eighths, PC - percent'
CREATE MEMORY TABLE PUBLIC.WILDLIFESPECIES(WILDLIFESPECIESID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,NAME VARCHAR(255) NOT NULL,DESCRIPTION VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UNIQUE(NAME))
ALTER TABLE PUBLIC.WILDLIFESPECIES ALTER COLUMN WILDLIFESPECIESID RESTART WITH 1
COMMENT ON TABLE PUBLIC.WILDLIFESPECIES IS 'Grouping for wildlife observations.  Completely informal, e.g. ''insect'', ''bee'', ''bird''.'
COMMENT ON COLUMN PUBLIC.WILDLIFESPECIES.DESCRIPTION IS 'Grouping for wildlife observations, completely informal (unlike the plant catalogue)'
CREATE MEMORY TABLE PUBLIC.WILDLIFE(WILDLIFEID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,WILDLIFESPECIESID INTEGER NOT NULL,LOCATIONID INTEGER DEFAULT NULL,DATE DATE NOT NULL,VARIETY VARCHAR(255) DEFAULT NULL,LASTUPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,CONSTRAINT FK_WILDLIFE_LOCATION1 FOREIGN KEY(LOCATIONID) REFERENCES PUBLIC.LOCATION(LOCATIONID),CONSTRAINT FK_WILDLIFE_WILDLIFESPECIES1 FOREIGN KEY(WILDLIFESPECIESID) REFERENCES PUBLIC.WILDLIFESPECIES(WILDLIFESPECIESID))
ALTER TABLE PUBLIC.WILDLIFE ALTER COLUMN WILDLIFEID RESTART WITH 1
CREATE INDEX FK_WILDLIFE_WILDLIFESPECIES1_IDX ON PUBLIC.WILDLIFE(WILDLIFESPECIESID)
CREATE INDEX FK_WILDLIFE_LOCATION1_IDX ON PUBLIC.WILDLIFE(LOCATIONID)
COMMENT ON TABLE PUBLIC.WILDLIFE IS 'A noteworthy observation of wildlife - an interesting event, unusual species, etc'
COMMENT ON COLUMN PUBLIC.WILDLIFE.LOCATIONID IS 'Where the observation took place'
CREATE PROCEDURE PUBLIC.LOADTODOLIST() SPECIFIC LOADTODOLIST_10679 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC DECLARE TABLE IDLIST(REMINDERID INT);INSERT INTO IDLIST(REMINDERID)SELECT REMINDERID FROM PUBLIC.REMINDER WHERE CURRENT_DATE>=SHOWFROM;INSERT INTO PUBLIC.TODOLIST(PLANTSPECIESID,PLANTVARIETYID,HUSBANDRYCLASSID,GROUNDWORKACTIVITYID,HUSBANDRYID,PURCHASEITEMID,DESCRIPTION)SELECT PLANTSPECIESID,PLANTVARIETYID,HUSBANDRYCLASSID,GROUNDWORKACTIVITYID,HUSBANDRYID,PURCHASEITEMID,DESCRIPTION FROM PUBLIC.REMINDER WHERE REMINDERID IN(SELECT REMINDERID FROM IDLIST);DELETE FROM PUBLIC.REMINDER WHERE REMINDERID IN(SELECT REMINDERID FROM IDLIST)AND SINGLESHOT='TRUE';UPDATE PUBLIC.REMINDER SET SHOWFROM=SHOWFROM+(INTERVAL'1'DAY*REPEATQUANTIFIER)WHERE REMINDERID IN(SELECT REMINDERID FROM IDLIST)AND REPEATINTERVAL='D';UPDATE PUBLIC.REMINDER SET SHOWFROM=SHOWFROM+(INTERVAL'7'DAY*REPEATQUANTIFIER)WHERE REMINDERID IN(SELECT REMINDERID FROM IDLIST)AND REPEATINTERVAL='W';UPDATE PUBLIC.REMINDER SET SHOWFROM=SHOWFROM+(INTERVAL'1'MONTH*REPEATQUANTIFIER)WHERE REMINDERID IN(SELECT REMINDERID FROM IDLIST)AND REPEATINTERVAL='M';UPDATE PUBLIC.REMINDER SET SHOWFROM=SHOWFROM+(INTERVAL'1'YEAR*REPEATQUANTIFIER)WHERE REMINDERID IN(SELECT REMINDERID FROM IDLIST)AND REPEATINTERVAL='Y';DELETE FROM PUBLIC.REMINDER WHERE SHOWFROM>REPEATUNTIL;END
SET SCHEMA PUBLIC
CREATE TRIGGER PUBLIC.PLANTSPECIES_AFTER_UPDATE AFTER UPDATE OF COMMONNAME ON PUBLIC.PLANTSPECIES REFERENCING OLD ROW AS OLD1 NEW ROW AS NEW1 FOR EACH ROW WHEN (new1.commonName != old1.commonName) BEGIN ATOMIC UPDATE PUBLIC.PRODUCT SET PRODUCT.NAME=NEW1.COMMONNAME WHERE PRODUCT.PLANTSPECIESID=NEW1.PLANTSPECIESID;END
CREATE TRIGGER PUBLIC.PLANTVARIETY_AFTER_UPDATE AFTER UPDATE OF COMMONNAME ON PUBLIC.PLANTVARIETY REFERENCING OLD ROW AS OLD1 NEW ROW AS NEW1 FOR EACH ROW WHEN (new1.commonName != old1.commonName) BEGIN ATOMIC UPDATE PUBLIC.PRODUCT SET PRODUCT.NAMEDETAIL_1=NEW1.COMMONNAME WHERE PRODUCT.PLANTSPECIESID=NEW1.PLANTSPECIESID AND PRODUCT.PLANTVARIETYID=NEW1.PLANTVARIETYID;END
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT DBA TO SA
SET SCHEMA SYSTEM_LOBS
INSERT INTO BLOCKS VALUES(0,2147483647,0)
SET SCHEMA PUBLIC
INSERT INTO AFFLICTIONCLASS VALUES(1,'pest','any insect or animal which harms the crop','2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO AFFLICTIONCLASS VALUES(2,'disease','any pathogen of the plant, fungal, viral, etc','2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO AFFLICTIONCLASS VALUES(3,'death','any cause of death','2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO LOCATION VALUES(1,NULL,'The Garden','',FALSE,'POLYGON((0 0, 0 100, 100 100, 100 0, 0 0))','2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO CROPROTATIONGROUP VALUES(1,2,'Legumes','Peas and all beans','2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO CROPROTATIONGROUP VALUES(2,3,'Brassicas','Brussels sprouts, cabbage, cauliflower, kale, oriental greens, kohl-rabi, radish, swede, turnips','2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO CROPROTATIONGROUP VALUES(3,4,'Potatoes','Potatoes and closely related species such as tomatoes and aubergines','2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO CROPROTATIONGROUP VALUES(4,5,'Root vegetables','Beetroot, carrot, celeriac, celery, parsley, parsnip, etc','2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO CROPROTATIONGROUP VALUES(5,1,'Alliums','Garlic, leeks, onions, shallots','2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO CROPROTATIONGROUP VALUES(6,NULL,'Fillers','Crops less susceptible to disease build up','2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PLANTSPECIES VALUES(1,3,'tomato','Lycopersicon esculentum',NULL,'vegetable','tender','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(2,3,'aubergine','Solanum melongena',NULL,'vegetable','tender','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(3,1,'broad bean','Vicia faba',NULL,'vegetable','hardy','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(4,1,'french bean','Phaseolus vulgaris',NULL,'vegetable','tender','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(5,1,'runner bean','Phaseolus coccineus',NULL,'vegetable','tender','treat as annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(6,4,'beetroot','Beta vulgaris subsp vulgaris',NULL,'vegetable',NULL,'annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(7,6,'courgette','Cucurbita pepo',NULL,'vegetable','tender','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(8,6,'cucumber','Cucumis sativus',NULL,'salad','tender','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(9,5,'garlic','Allium sativum',NULL,'vegetable','hardy','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(10,5,'leek','Allium porrum',NULL,'vegetable','hardy','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(11,5,'onion','Allium cepa',NULL,'vegetable','hardy','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(12,6,'lettuce','Lactuca sativa',NULL,'salad','tender','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(13,4,'parsnip','Pastinaca sativa',NULL,'vegetable','hardy','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(14,1,'pea','Pisum sativum',NULL,'vegetable',NULL,'annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(15,3,'potato','Solanum tuberosum',NULL,'vegetable',NULL,'perennial',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(16,2,'radish','Raphanus sativus',NULL,'salad','tender','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTSPECIES VALUES(17,2,'cabbage','Brassica oleracea',NULL,'vegetable','hardy','annual',NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTVARIETY VALUES(1,1,'gardeners delight',NULL,NULL,'A reliable salad tomato with good flavour and good yield',NULL,NULL,NULL,NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTVARIETY VALUES(2,1,'ailsa craig',NULL,NULL,'A good slicing tomato for salad or cooking',NULL,NULL,NULL,NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTVARIETY VALUES(3,1,'moneymaker',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTVARIETY VALUES(4,2,'moneymaker',NULL,NULL,'A reliable aubergine with small fruit but a good yield',NULL,NULL,NULL,NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTVARIETY VALUES(5,5,'scarlet emperor',NULL,NULL,'Most popular variety, reliable, good yield and good flavour',NULL,NULL,NULL,NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTVARIETY VALUES(6,7,'parthenon',NULL,NULL,'Parthenocarpic variety, does not need fertilising.  Green fruit.  Very reliable in UK gardens, heavy cropper of dense fruit with good flavour',NULL,NULL,NULL,NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTVARIETY VALUES(7,7,'gold mine',NULL,NULL,'Parthenocarpic variety, does not need fertilising.  Yellow fruit.  Very reliable in UK gardens, good crop of dense fruit with good flavour',NULL,NULL,NULL,NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTVARIETY VALUES(8,15,'king edward',NULL,NULL,'Traditional main crop with good flavour',NULL,NULL,NULL,NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PLANTVARIETY VALUES(9,15,'charlotte',NULL,NULL,'Second early variety.  Waxy texture.  Usually grown as a salad potato but also good roasted or baked',NULL,NULL,NULL,NULL,'2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO GROUNDWORKACTIVITY VALUES(1,'pruning',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(2,'spraying',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(3,'manuring',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(4,'weeding',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(5,'clearing',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(6,'digging',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(7,'plant support','Erecting any form of plant support','2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(8,'mulching',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(9,'greenhouse','Any work on a greenhouse, e.g. cleaning','2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(10,'pest control','Any activity concerned with pest control','2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(11,'compost',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(12,'liming','Applying garden lime to the soil to reduce acidity','2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(13,'mowing',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(14,'hedge trimming',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(15,'cloche',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(16,'watering',NULL,'2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(17,'soil conditioning','Applying any form of soil treatment, specifically chemical (e.g. potash)','2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(18,'scarify','Rake out the ''thatch'' in a lawn','2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(19,'aerate','Make (many) small holes in a lawn to improve drainage','2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(20,'feeding','Any activity to feed established plants','2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO GROUNDWORKACTIVITY VALUES(21,'strimming','Use of strimmer, e.g. to tidy round edge of lawn','2021-12-31 17:35:10.237000','2021-12-31 17:35:10.237000')
INSERT INTO HUSBANDRYCLASS VALUES(1,'sow','when seeds are sown',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(2,'pot up','when seedlings are transferred to their first pot',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(3,'pot on','when a plant is repotted in a larger pot',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(4,'germination','when seeds are seen to germinate, usually use ''emergence''',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(5,'emergence','when seedlings first appear above ground',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(6,'plant out','when plants are moved to their final position',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(7,'harden off','the hardening off process for new plants',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(8,'flowering','when a plant flowers',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(9,'fruiting','when a plant sets fruit',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(10,'harvest','when a crop is taken from a plant',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(11,'bud break','leaf or flower bud',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(12,'observation','an observation on the progress of a crop',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(13,'chit','usually for potatoes, when a tuber is brought into growth before planting',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(14,'failure','when one or more plants fails to thrive or grow',TRUE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(15,'abandoned','when one or more plants are discarded, possibly due to failure or being surplus',TRUE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(16,'earth up','usually for potatoes',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(17,'thin out','reducing fruit load on a plant or reducing overcrowding of plants',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(18,'transplant',NULL,FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(19,'prune','removing excess growth from a plant, usually fruit trees',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(20,'prick out','when seedlings are transferred to their first pot (see pot up)',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(21,'repot','when a potted plant is moved to a new pot',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(22,'discarded','when plants are discarded as unwanted (e.g. excess sown)',TRUE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(23,'death','to record the death of a plant for whatever reason',TRUE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(24,'heel in','when a new plant, usually bare-root, cannot be properly planted, loosely plant it in a convenient bed or pot',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(25,'collect seed','collect the seed either to eat (e.g. fennel) or to sow for a new crop (e.g. tomatoes)',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(26,'take cutting','make a cutting of some sort for propagation',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(27,'cut back','reduce the size of a plant for any of a variety of reasons',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO HUSBANDRYCLASS VALUES(28,'pinch out','remove the growing tip from a young plant to encourage bushy growth',FALSE,'2021-12-31 17:35:10.269000','2021-12-31 17:35:10.269000')
INSERT INTO PLANTNOTE VALUES(1,2,NULL,'Cultivation','Aubergines need plenty of light and warmth - they originated in India.  They are best grown undercover, an upright lean-to grow cabinet at the side of the house is quite adequate.  Aubergines need a long growing season so seed needs to be sown late January to February - St Valentine''s Day is a good target.  Sow seeds just covering them with compost and place in a propagator at 20C; keep at same temperature after germination.  Prick out when when first true leaves have developed.  Grow steadily with consistent warmth and moisture through March to May; pot on as the roots fill the pot.  Overwatering can lead to poor root development and young plants can be sensitive to too much sun.  Plant out in a greenhouse border in early May, 60cm (2'') between plants; they are greedy feeders so add plenty of well-rotted compost.  Support is essential.  Alternatively use a 30cm (12") pot.  Keep soil moist but not saturated with a consistent watering regime.  Once fruit has set, feed weekly with a high potash fertiliser.  Harvest fruits when firm and glossy (they should rebound from a gentle squeeze) using secateurs; left too long the fruits dull and become very seedy and bitter.  There is no need to train the plants nor to cut out side shoots but it''s worth pinching out the growing tip when the plant reaches 30cm (12").  If space is limited train plants into two vertical stems after pinching out.  Mist with tepid water and ensure a gentle breeze in the greenhouse to help pollination.  Aubergines are prone to red spider mite attack - damp down, mist and use biological controls.','2021-12-31 17:35:27.187000','2021-12-31 17:35:27.187000')
INSERT INTO PRODUCTCATEGORY VALUES(1,'potted plants',NULL,TRUE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PRODUCTCATEGORY VALUES(2,'sundries','anything not covered elsewhere',FALSE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PRODUCTCATEGORY VALUES(3,'tubers','tubers, bulbs, corms, etc',TRUE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PRODUCTCATEGORY VALUES(4,'seed',NULL,TRUE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PRODUCTCATEGORY VALUES(5,'planting media','compost, etc',FALSE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PRODUCTCATEGORY VALUES(6,'plug plants','also plants in strips',TRUE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PRODUCTCATEGORY VALUES(7,'fertilisers','Growmore, manure, compost accelerators',FALSE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PRODUCTCATEGORY VALUES(8,'pest control','animal, fungal, etc',FALSE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PRODUCTCATEGORY VALUES(9,'tools',NULL,FALSE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PRODUCTCATEGORY VALUES(10,'labour',NULL,FALSE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PRODUCTCATEGORY VALUES(11,'bare root plants',NULL,TRUE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
INSERT INTO PRODUCTCATEGORY VALUES(12,'root ball plants','plants supplied in a root ball',TRUE,'2021-12-31 17:35:10.253000','2021-12-31 17:35:10.253000')
