[delphi-museum-project] r1284 committed - Adding early drafts of sql support for complex inferences. Note that t...

[codesite...@google.com]

Revision: 1284
Author: LudicrousResearcher
Date: Mon Nov 30 10:36:21 2009
Log: Adding early drafts of sql support for complex inferences. Note that
these must be in the main DB (not onto_maint as specified in current files)
to reference the existing ontology tables.
http://code.google.com/p/delphi-museum-project/source/detail?r=1284

Added:
/trunk/schemas/addTestInferences.sql
/trunk/schemas/complexInference.sql

=======================================
--- /dev/null
+++ /trunk/schemas/addTestInferences.sql Mon Nov 30 10:36:21 2009
@@ -0,0 +1,26 @@
+-- This should be in a new DB on dev that is just for maintaining the
ontologies.
+
+use onto_maint;
+
+select id @c from categories where name = 'Carved';
+select id @w from categories where name = 'Wood';
+select id @m from categories where name = 'Mask';
+select id @f from categories where name = 'FigurativeOrRepresentational';
+
+INSERT INTO inferred_concepts( id, name, notes, infer, reliability,
req_all, excl_all, creation_time ) VALUES
+( 1, 'Wood_Mask_Carved', 'Wood+Mask => Carved', @c, 9, 1, 0, now() ),
+( 2, 'Wood_Fig_Carved', 'Wood+Figurative => Carved', @c, 7, 1, 0, now() );
+
+INSERT INTO inf_required( cat_id, inf_id, creation_time ) VALUES
+ ( @w, 1, now() ),
+ ( @m, 1, now() ),
+ ( @w, 2, now() ),
+ ( @f, 2, now() );
+
+-- Note that we have no exclusions yet. Should test one.
+
+-- Note that we need to test multi-step inference. Could posit some bogus
thing like
+-- Carved plus San Francisco implies culture Ashanti,
+-- and then Mask plus Ashanti implies copper unless painted.
+
+
=======================================
--- /dev/null
+++ /trunk/schemas/complexInference.sql Mon Nov 30 10:36:21 2009
@@ -0,0 +1,69 @@
+-- ----------------------------------------------------------------------
+-- SQL create script for delphi complex inference support
+-- This allows inferring one concept given several others, plus
constraints
+-- ----------------------------------------------------------------------
+
+use onto_maint;
+
+-- Define the main object table
+-- The image info is denormalized for simplicity and performance,
+-- based upon the initial system requirements. Must also
+-- provide additional images with a proper normalized table.
+DROP TABLE IF EXISTS `inferred_concepts`;
+CREATE TABLE `inferred_concepts` (
+ `id` INT(10) UNSIGNED PRIMARY KEY NOT NULL,
+ `name` VARCHAR(255) NOT NULL,
+ `notes` text NULL,
+ `infer` INT(10) UNSIGNED NULL,
+ `reliability` TINYINT(1) NOT NULL DEFAULT 9,
+ -- Default to requiring all required concepts
+ `req_all` TINYINT(1) NOT NULL DEFAULT 1,
+ -- Default to excluding any excluded concepts
+ `excl_all` TINYINT(1) NOT NULL DEFAULT 0,
+ `creation_time` timestamp NOT NULL default '0000-00-00 00:00:00',
+ `mod_time` timestamp NOT NULL default CURRENT_TIMESTAMP
+ on update CURRENT_TIMESTAMP,
+ INDEX `infer_id_index` (`infer`),
+ CONSTRAINT `infc_ibfk_1` FOREIGN KEY (`infer`)
+ REFERENCES `categories` (`id`)
+)ENGINE=MyIsam;
+SHOW WARNINGS;
+
+/*
+ * Provides the required concepts for a complex inference
+ * req_id is the required concept id, inf_id is the inferred concept
+ */
+DROP TABLE IF EXISTS `inf_required`;
+CREATE TABLE `inf_required` (
+ `id` INT(10) UNSIGNED PRIMARY KEY NOT NULL auto_increment,
+ `req_id` INT(10) UNSIGNED NULL,
+ `inf_id` INT(10) UNSIGNED NULL,
+ `creation_time` timestamp NOT NULL default '0000-00-00 00:00:00',
+ `mod_time` timestamp NOT NULL default CURRENT_TIMESTAMP
+ INDEX `infrq_cat_index` (`cat_id`),
+ INDEX `infrq_inf_index` (`inf_id`),
+ CONSTRAINT `infrq_ibfk_1` FOREIGN KEY (`cat_id`)
+ REFERENCES `categories` (`id`),
+ CONSTRAINT `infrq_ibfk_2` FOREIGN KEY (`inf_id`)
+ REFERENCES `categories` (`id`)
+)ENGINE=MyIsam;
+SHOW WARNINGS;
+
+/*
+ * Provides the required concepts for a complex inference
+ */
+DROP TABLE IF EXISTS `inf_excluded`;
+CREATE TABLE `inf_excluded` (
+ `id` INT(10) UNSIGNED PRIMARY KEY NOT NULL auto_increment,
+ `excl_id` INT(10) UNSIGNED NULL,
+ `inf_id` INT(10) UNSIGNED NULL,
+ `creation_time` timestamp NOT NULL default '0000-00-00 00:00:00',
+ `mod_time` timestamp NOT NULL default CURRENT_TIMESTAMP
+ INDEX `infex_cat_index` (`cat_id`),
+ INDEX `infex_inf_index` (`inf_id`),
+ CONSTRAINT `infex_ibfk_1` FOREIGN KEY (`cat_id`)
+ REFERENCES `categories` (`id`),
+ CONSTRAINT `infex_ibfk_2` FOREIGN KEY (`inf_id`)
+ REFERENCES `categories` (`id`)
+)ENGINE=MyIsam;
+SHOW WARNINGS;