[pcdb] PCDB/Wiki/Guild Database

jedd jedd at progsoc.org
Sat Jul 21 04:02:38 EDT 2007


 Paul - just found your note in the Discussion page attached to my
 community_portal page that I posted a while back.  To answer your
 question, I don't mind, so long as there's an easy way for me to work
 out what bits are your comments.  Not so much for attribution, but
 just so I know which bits to read.  Tracking conversations in a wiki
 is always a painful business -- compare and contrast email -- but I
 think I've asserted this truth several times previously.

 Anyway, should I be adding the idea of a project driven, collaborative
 custom built database to my list of Nice Ideas That Never Made It?

 I'm resolved to writing my own system, form scratch, and having
 it customised to my own purposes, with regrettably lower priority
 for making it easy to navigate and contribute to for non-Jedd types,
 but such a system would still be infinitely more useful to me than
 a wiki that contains a bunch of free form text only.

 I think I've asserted this a few time previously, too.

 Such a wiki approach could possibly be very useful, iff everyone
 used it, and no one used any other wikis out there .. you end up
 with something pretty useflu (a la wikipedia) but with world+dog
 setting up non-comparative, standalone, non-relational, loose, text-
 based systems .. it's not a particularly compelling path to follow.

 On a slightly more upbeat note, and while fully acking that the
 problems I talk about towards the end of my permaculture.info page
 are almost totally unattended to ... I'll attach below the schema
 that I've set up so far.  I was looking at some GUI versions of same,
 but they're pretty thin on the ground on GNU/Linux, and ultimately
 you end up having to either take your design changes back to the
 GUI, or do it all from there as you go, and neither is appealing.

 Again, I say, this is very far from complete, but gives an inkling to
 any that may have read this far (through the list's history, I mean,
 not just this message) about my intent.  Postfixed some test data
 that may add meaning - but it's pretty rusty and I haven't updated
 that as often as the schema.  Note, tabstops set to 4.

 Jedd.



# CREATE schema
#
# Set up all tables within the database.

# Types used below (taken from MySQL's standards)
# bigint (8 bytes)    unsigned max = 18,446,744,073,709,551,615
# int (4 bytes)       unsigned max =              4,294,967,295
# mediumint (3 bytes) unsigned max =                 16,777,215
# smallint (2 bytes)  unsigned max =                     65,535
# tinyint (1 byte)    unsigned max =                        255
# 
# tinytext   = 255 characters
# mediumtext = 16,777,215 characters
# longtext   = 4,294,967,295 characters
#
# SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT

# pending problems to resolve:
#	o  each person has primary soil type, but probably has many
#		soil types that they own or work on / with.  locations
#		with a different type.  once a type is set, it's cast
#		in stone (due to people:soil == n:m ratio) and cheapness
#		of bigints.
#	o  each person has primary climate and multiple microclimates,
#		and microclimates need separate table obviously.

DROP DATABASE permadb;
CREATE DATABASE permadb;
GRANT ALL on permadb.* to 'jedd'@'%';
USE permadb;



CREATE TABLE taxa_kingdom  (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_phylum  (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_subphylum  (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_class  (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_subclass  (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_order  (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_suborder  (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_family  (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_subfamily  (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_genus  (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_subgenus  (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_species (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);
CREATE TABLE taxa_subspecies (
	id					SERIAL,
	name				char(99) UNIQUE,
	INDEX (name),
	PRIMARY KEY (id)
	);



CREATE TABLE scinamesyn (		# botanical / scientific name synonyms - only one 
is valid
	# anything in this table is a historical name only - the valid current name 
is in the organism table.
	# JEDD - previously used oldfam, oldgen, newfam, newgen - etc - but 
complicates arose with that
	#		approach, and instead the organism.id's position as canon is reaffirmed 
here.  Uniqueness
	#		is based on arbitrary id only (good for change_history) rather than PK of 
6 fields.
	#		This approach elegantly handles 1:n ratio of current to historical 
botanical names.
	id					SERIAL,					# mostly unused, but definitely unique
	organismid			bigint,					# to organism.id - from where we pick up 
fam/gen/spe/etc
	old_taxa_kingdom	bigint,					# to relevant table.id
	old_taxa_phylum		bigint,					# to relevant table.id
	old_taxa_subphylum	bigint,					# to relevant table.id
	old_taxa_class		bigint,					# to relevant table.id
	old_taxa_subclass	bigint,					# to relevant table.id
	old_taxa_order		bigint,					# to relevant table.id
	old_taxa_suborder	bigint,					# to relevant table.id
	old_taxa_family		bigint,					# to relevant table.id
	old_taxa_subfamily	bigint,					# to relevant table.id
	old_taxa_genus		bigint,					# to relevant table.id
	old_taxa_subgenus	bigint,					# to relevant table.id
	old_taxa_species	bigint,					# to relevant table.id
	old_taxa_subspecies	bigint,					# to relevant table.id
	deprecated			datetime,				# when its usage was deprecated
	comment				tinytext,				# why & when it was replaced
	create_who			bigint,					# user-id who posted this synonym
	create_when			datetime,				# when it was posted
	INDEX (organismid),
	INDEX (oldfamily),
	INDEX (oldgenus),
	INDEX (oldspecies),
	PRIMARY KEY (id)
	);


CREATE TABLE varnamesyn (  # variety name synonyms - typically many names of 
varieties are valid
	# do we need weightings in here - the *most* common variety name .. ?
	id					bigint UNIQUE,			# to organism.id
	varid				bigint,					# to vardex.id
	comment				tinytext,				# why & where it's used
	create_who			bigint,					# user-id who posted this synonym
	create_when			datetime,				# when it was posted
	INDEX (varid),
	PRIMARY KEY (id)
	);

CREATE TABLE vardex (	# variety name index
	id					SERIAL,
	name				char(99) UNIQUE,
	PRIMARY KEY (id)
	);

CREATE TABLE cnsyn (	# common name synonyms
	# do we need weightings in here - the *most* common name .. ?
	id					bigint UNIQUE,				# to organism.id
	cnid				bigint,						# to cndex.id
	comment				tinytext,					# why & where it's used
	weighting			tinyint,					# how common this name is - inserted by # of hits?
	create_who			bigint,						# user-id who posted this synonym
	create_when			datetime,					# when it was posted
	INDEX (cnid),
	PRIMARY KEY (id)
	);

CREATE TABLE cndex (	# common name index
	id					SERIAL,
	name				char(200) UNIQUE,
	PRIMARY KEY (id)
	);


# any living entity - can and/or will (?) have any of the following attributes
# JEDD - do we need a [taxa_]variety reference in here, or will varieties 
always
# be handled as part of table.varnamsyn -- pros / cons?
CREATE TABLE organism  (
	id					SERIAL,
	fam					bigint,
	gen					bigint,
	spec				bigint,
	var					bigint,
	taxa_kingdom		bigint,						# to relevant table.id
	taxa_phylum			bigint,						# to relevant table.id
	taxa_subphylum		bigint,						# to relevant table.id
	taxa_class			bigint,						# to relevant table.id
	taxa_subclass		bigint,						# to relevant table.id
	taxa_order			bigint,						# to relevant table.id
	taxa_suborder		bigint,						# to relevant table.id
	taxa_family			bigint,						# to relevant table.id
	taxa_subfamily		bigint,						# to relevant table.id
	taxa_genus			bigint,						# to relevant table.id
	taxa_subgenus		bigint,						# to relevant table.id
	taxa_species		bigint,						# to relevant table.id
	taxa_subspecies		bigint,						# to relevant table.id
	commonname			bigint,						# to cndex.id
	INDEX (fam),
	INDEX (gen),
	INDEX (spec),
	INDEX (var),
	PRIMARY KEY (id)
	);

# organism-to-organism mapping, describing how to distinguish between similar 
entities.
# APP will show diffs in organism attributes (height, etc), and consult items 
in this
#	table (if org.id present in either .id1 or .id2) to find a verbal 
description.
CREATE TABLE diffdex  (
	id						SERIAL					# mostly unused
	id1						bigint					# first organism ID
	id2						bigint					# second organism ID
	difference				char(2048)				# text, possibly html-tagged, describing the 
diffs.
	INDEX (id1),
	INDEX (id2),
	PRIMARY KEY (id)
	);


# Specific to plants (probably)
CREATE TABLE companionplanttype  (
	id						int AUTO_INCREMENT,		# heavily controlled table (admin add only)
	description				char(99) UNIQUE,		# eg. flavour, pest, disease, vigour, shade, 
nitrogen
	PRIMARY KEY (id)
	);


# Specific to plants (probably)
# (but consider animal/plant symbiosis, plant/fungus, plant/bacteria, 
animal/fungus symbiotic r'ships)
CREATE TABLE companionplant  (
	id						SERIAL,
	organism_prov			bigint,					# to organism.id - that provides the benefit
	organism_recv			bigint,					# to organism.id - that receives the benefit
	companiontype			bigint,					# to companionplanttypes.id
	weighting				tinyint,				# effect on scale of -100 to +100?
	report_who				bigint,					# user-id who posted this relationship
	report_when				datetime,				# when it was posted
	report_weight			tinyint,				# how convinced they are / how much research 
they've done
	INDEX (organism_prov),
	INDEX (organism_recv),
	PRIMARY KEY (id)
	);

CREATE TABLE soildex  (
	# soil types can probably only be added rather than modified - maybe owner 
can change?  only if no ext refs to this type?
	# flag for unused -- so unusued or accidental types can be purged - worth 
doing?
	id						SERIAL,					# everyone will have lots of soil types
	description				tinytext,				# used by contributor - people may 
have 'favourite' soil list
	ph						tinyint,
	organicmatter			tinyint unsigned,		# percentage?  Need to be float, or ppm or 
pp1000?
	claysandratio			tinyint unsigned,		# good way of doing ratio - extant metric 
for this?
		# mineral OR elementcontent _N, _P, _K, _Mg and so on .. ?
		# need to come up with ideas on recording this info - mineraldex table?
	watercapacity			tinyint unsigned,		# percentage?  extant metric, or holding 
capacity by mass/vol?
	drainagerate			int unsigned,			# dig a hole, fill with water, count to ... ?
	density					tinyint unsigned,		# specific gravity, or alternative metric?
	depthtoclaypan			int unsigned,			# measured in mm?
	create_who				bigint,					# user-id who posted this synonym
	create_when				datetime,				# when it was posted
	latitude				tinytext,				# or separate deg/min/sec into three fields for 
search/matching?
	longitude				tinytext,				# or separate deg/min/sec into three fields for 
search/matching?
	PRIMARY KEY (id)
	);


CREATE TABLE mulchdex  (
	id						SERIAL,
	description				char(99) UNIQUE,		# used by contributor - people may 
have 'favourite' mulch types
	mulchtype				tinyint,				# to mulchtype.id
	tempdelta				tinyint,				# temperature delta above/below mulch - at 
particular temps?
	water_feature			tinyint,				# water stalling / holding features ... somehow?
	PRIMARY KEY (id)
	);

CREATE TABLE mulchtype  (
	id						int AUTO_INCREMENT,		# heavily controlled table (admin add only)
	description				char(99) UNIQUE,		# straw, bark, none, stone, living, ...	
	PRIMARY KEY (id)
	);

CREATE TABLE climatetype  (
	id					SERIAL,
	description			char(99) UNIQUE,			# used by contributor - people may 
have 'favourite' mulch types
	temp_min			tinyint,					# temp in C
	temp_max			tinyint,					# temp in C
	PRIMARY KEY (id)
	);

CREATE TABLE microclimatetype  (
	id					SERIAL,
	description			char(99) UNIQUE,			# used by contributor - people may 
have 'favourite' mulch types
	externalclimate		bigint,						# to climatetype.id - may be USELESS .. ?
	temp_min			tinyint,					# temp in C
	temp_max			tinyint,					# temp in C
	PRIMARY KEY (id)
	);

CREATE TABLE glossary  (
	id					SERIAL,						# Possible this is best in the wiki - but not so portable 
there
	term				tinytext,					# Word or phrase we're defining
	definition			char(2048),					# The big definition, including URLs and 
citations as needed
	# JEDD - Need a history component here - possibly maintained in a separate 
table of changes, say.
	INDEX (term),
	PRIMARY KEY (id)
	);

CREATE TABLE changehistory  (
	id					SERIAL,						# probably unused
	object_table		tinytext,					# which table the change occured in
	object_id			bigint,						# id of object in that table
	change_who			bigint,						# user id of person who made the change
	change_when			datetime,					# when the change was made
	description			char(200),					# if a description summary was provided ...
	item_before			varchar(4096),				# needs to be as big as the biggest 
table.attribute anywhere else
	INDEX (object_id),
	INDEX (change_who),
	PRIMARY KEY (id),
	);



#######################################################################



#
# INSERT test data
#
# Insert some data for testing - some may be useful for trial installations.

USE permadb;

INSERT INTO
	specdex (name)
	VALUES ("undatus");					# pitaya
INSERT INTO
	specdex (name)
	VALUES ("esculentum");				# marama bean
INSERT INTO
	specdex (name)
	VALUES ("molinae");					# chilean guava (current)
INSERT INTO
	specdex (name)
	VALUES ("ugni");					# chilean guava (deprecated)


INSERT INTO
	gendex (name)
	VALUES ("Hylocereus");				# pitaya
INSERT INTO
	gendex (name)
	VALUES ("Tylosema");				# marama bean
INSERT INTO
	gendex (name)
	VALUES ("Ugni");					# chilean guava (current)
INSERT INTO
	gendex (name)
	VALUES ("Myrtus");					# chilean guava (deprecated)
INSERT INTO
	gendex (name)
	VALUES ("Eugenia");					# chilean guava (deprecated)


INSERT INTO
	famdex (name)
	VALUES ("Cactaceae");				# pitaya
INSERT INTO
	famdex (name)
	VALUES ("Fabaceae");				# marama bean
INSERT INTO
	famdex (name)
	VALUES ("Myrtaceae");				# chilean guava

INSERT INTO
	organism (fam, gen, spec, var, commonname)
	VALUES ((SELECT id FROM famdex WHERE name="Myrtaceae"),
			(SELECT id FROM gendex WHERE name="Ugni"),
			(SELECT id FROM specdex WHERE name="molinae"),
			NULL,
			"Chilean guava"
		);

# Two synonyms of Ugni molinae are used - Myrtus ugni and Eugenia ugni
INSERT INTO
	scinamesyn (id, fam, gen, spec, comment, deprecated_when, create_who, 
create_when)
	# JEDD - fix this up - need to sub-select to find famdex.id based on 
famdex.name
	#VALUES ((SELECT id FROM organism WHERE fam="Myrtaceae" AND gen="Ugni" AND 
spec="molinae"),
	VALUES ((SELECT id FROM organism WHERE 
					fam=(SELECT id FROM famdex WHERE name="Myrtaceae") AND
					gen=(SELECT id FROM gendex WHERE name="Ugni") AND
					spec=(SELECT id FROM specdex WHERE name="molinae")) ,
			(SELECT id FROM famdex   WHERE name="Myrtaceae"),
			(SELECT id FROM gendex   WHERE name="Myrtus"),
			(SELECT id FROM specdex  WHERE name="ugni"),
			NULL,
			NULL,
			NULL
		);



INSERT INTO
	scinamesyn (id, fam, gen, spec, comment, create_who, create_when)
	VALUES ((SELECT id FROM organism WHERE 
					fam=(SELECT id FROM famdex WHERE name="Myrtaceae") AND
					gen=(SELECT id FROM gendex WHERE name="Ugni") AND
					spec=(SELECT id FROM specdex WHERE name="molinae")) ,
			(SELECT id FROM famdex   WHERE name="Myrtaceae"),
			(SELECT id FROM gendex   WHERE name="Eugenia"),
			(SELECT id FROM specdex  WHERE name="ugni"),
			NULL,
			NULL,
			NULL
		);




INSERT INTO
	cndex (name),
	VALUES ("Chilean guava");
INSERT INTO
	cndex (name),
	VALUES ("Murtilla");


INSERT INTO
	cnsyn (id, cnid, comment, weighting, create_who, create_when)
	VALUES ((SELECT id FROM organism WHERE 
		(SELECT id FROM organism WHERE fam="Myrtaceae" AND gen="Ugni" AND 
spec="molinae"),
		(SELECT id FROM cndex WHERE name="Chilean guava"),
		NULL,
		NULL,
		NULL,
		NULL,
		);
INSERT INTO
	cnsyn (id, cnid, comment, weighting, create_who, create_when)
	VALUES ((SELECT id FROM organism WHERE 
		(SELECT id FROM organism WHERE fam="Myrtaceae" AND gen="Ugni" AND 
spec="molinae"),
		(SELECT id FROM cndex WHERE name="Murtilla"),
		NULL,
		NULL,
		NULL,
		NULL,
		);

INSERT INTO
	companionplanttype (description)
	VALUES ("flavour");
INSERT INTO
	companionplanttype (description)
	VALUES ("vigour");
INSERT INTO
	companionplanttype (description)
	VALUES ("pest control");

INSERT INTO
	glossary (term, definition)
	VALUES (umbel, "A kind of flower cluster in which the flower stalks	radiate 
from a common point, as in the carrot and milkweed. It is simple or compound; 
in the latter case, each peduncle bears   another little umbel, called 
umbellet, or umbellule.");
INSERT INTO
	glossary (term, definition)
	VALUES (bulbil, "A small or secondary bulb; hence, now almost exclusively: An 
aerial bulb or deciduous bud, produced in the leaf axils, as in the tiger 
lily, or relpacing the flowers, as in some onions, and capable, when 
separated, of propagating the plant; -- called also bulblet and brood bud.");
	


# Use elephant garlic
# Allium ampeloprasum - common or basic form is elephant garlic
# var: Pearlzwiebel
#		aka multiplier leek, argentine garlic
#		diff similar to elephant but it produces a cluster of solid spherical
#			 white bulbs or rounds and never produces cloves.  The bulbs can
#			be distinguished from onion bulbs because they are solid, not layered.
#
# var babingtonii
#		aka British leek, Welsh leek
#		has flowers and bulbils in the umbel but otherwise resembles elephant 
garlic
#
# var bulbiferum
#		very similar to var babingtonii but has smaller bulbils
#		found in Guernsey and France
#
# Allium giganteum
#		diff similar to elephant garlic but easily distinguished by the breadth of
#		the leaf and the colour of the flower (chap 11 of Garlic & Friends)
#
#CREATE TABLE diffdex
#	id						SERIAL					# PK probably ignored
#	id1						bigint					# first organism ID
#	id2						bigint					# second organism ID
#	difference				char(2048)				# text, possibly html-tagged, describing the 
diffs.
#	INDEX (id1),
#	INDEX (id2),
#	PRIMARY KEY (id)

##varieties
# INSERT INTO
#	vardex (name)
#	VALUES ("big red type");



##companion plants
#INSERT INTO 
#	companionplant (organism_prov, organism_recv, companiontype, weighting,
#					report_who, report_when, report_weight)
#	VALUES ( ... );


# Need to add test data for
#      soildex
#      mulchdex
#      mulchtype
#      climatetype
#      microclimatetype



More information about the pcdb mailing list