JQXaWDXJ

1DROP TABLE IF EXISTS `neuron`;
2CREATE TABLE  `neuron` (
3  `neuronID` int(10) unsigned NOT NULL AUTO_INCREMENT,
4  `activation` double NOT NULL DEFAULT '0',
5  `bias` double NOT NULL DEFAULT '-1',
6  `threshold` double NOT NULL DEFAULT '0',
7  `layer` int(10) unsigned NOT NULL DEFAULT '0',
8  `class` char(25) NOT NULL DEFAULT 'Standard',
9  PRIMARY KEY (`neuronID`)
10);
11
12DROP TABLE IF EXISTS `weight`;
13CREATE TABLE  `weight` (
14  `weightID` int(10) unsigned NOT NULL AUTO_INCREMENT,
15  `source_neuronID` int(10) unsigned NOT NULL,
16  `dest_neuronID` int(10) unsigned NOT NULL,
17  `weight_value` double NOT NULL DEFAULT '0',
18  PRIMARY KEY (`weightID`)
19);
20
21# a XOR neuron sample
22INSERT INTO test.`neuron`
23(`neuronID`,`activation`,`bias`,`threshold`,`layer`,`class`) VALUES
24 (1,0,1,0.5,0,'XOR'),
25 (2,1,1,0.75,0,'XOR'),
26 (4,0,1,0.25,1,'XOR'),
27 (5,1,1,1,1,'XOR'),
28 (6,0,1,1,2,'XOR');
29
30# a XOR weighted sample
31INSERT INTO test.`weight`
32(`weightID`,`source_neuronID`,`dest_neuronID`,`weight_value`) VALUES
33 (3,1,4,1),
34 (4,2,4,-1),
35 (5,1,5,-1),
36 (6,2,5,1),
37 (7,4,6,1),
38 (8,5,6,1);
39
40
41
42DROP TRIGGER test.neuron_Add;
43DROP TRIGGER test.neuron_Delete;
44
45DELIMITER ;;
46CREATE TRIGGER test.neuron_Add
47AFTER INSERT
48ON test.neuron
49FOR EACH ROW BEGIN /* create a weight (link) between each neuron in the layer above and/or below */
50  INSERT INTO test.weight (source_neuronID,dest_neuronID,weight_value) (
51  SELECT x.source_neuronID,x.dest_neuronID,x.weight_value FROM (
52    SELECT neuronID as source_neuronID, NEW.neuronID as dest_neuronID, 0 as weight_value FROM test.neuron WHERE layer = (NEW.layer - 1) AND class = NEW.class
53    UNION
54    SELECT NEW.neuronID as source_neuronID, neuronID as dest_neuronID, 0 as weight_value FROM test.neuron WHERE layer = (NEW.layer + 1) AND class = NEW.class
55  ) x
56  );
57END;
58;;
59
60CREATE TRIGGER test.neuron_Delete
61BEFORE DELETE
62ON test.neuron
63FOR EACH ROW BEGIN /* delete any weight (link) between each neuron in the layer above and/or below */
64  delete from test.weight where  (dest_neuronID =  OLD.neuronID OR source_neuronID =  OLD.neuronID);
65END;
66;;
67DELIMITER ;
68
69DELIMITER $$
70
71DROP PROCEDURE IF EXISTS `test`.`NN_calcActivation` $$
72CREATE PROCEDURE `test`.`NN_calcActivation` (prmClass varchar(45))
73BEGIN
74  DECLARE  tmpBottomLayer         INTEGER     DEFAULT -1;
75  DECLARE  tmpCurrentLayer        INTEGER     DEFAULT 0;
76  DECLARE  tmpCurrentNeuronID     INTEGER     DEFAULT 0;
77  DECLARE  tmpActive              INTEGER     DEFAULT 0;
78  DECLARE  done                   INTEGER     DEFAULT 0;
79  DECLARE  cnt                    INTEGER     DEFAULT 0;
80  DECLARE CONTINUE HANDLER FOR NOT FOUND   SET done = 1;
81  SET cnt = (SELECT COUNT(*) FROM test.neuron);
82  IF (cnt > 0) THEN
83  BEGIN
84    set tmpBottomLayer = (select MIN(layer) from test.neuron where class=prmClass);
85    if (tmpBottomLayer > -1) then
86    begin
87      DECLARE NNCur CURSOR FOR
88         SELECT distinct layer, neuronID
89          FROM test.neuron
90                WHERE layer > tmpBottomLayer and class=prmClass
91          order by layer, neuronID;
92      OPEN NNCur;
93      REPEAT
94        FETCH NNCur INTO tmpCurrentLayer, tmpCurrentNeuronID;
95        # determine if the current neuron is activated
96        select count(*) INTO cnt
97          FROM test.neuron WHERE layer = tmpCurrentLayer and neuronID = tmpCurrentNeuronID;
98        if (cnt > 0) then
99        begin
100          select if(sum(w.weight_value * (nnBottom.bias * nnBottom.activation))>=nnTop.threshold,1,0)
101            INTO tmpActive
102            from test.neuron nnTop
103            INNER JOIN test.weight w ON nnTop.neuronID = w.dest_neuronID
104            INNER JOIN test.neuron nnBottom ON w.source_neuronID = nnBottom.neuronID
105            where nnTop.layer = tmpCurrentLayer and nnTop.neuronID = tmpCurrentNeuronID
106            group by nnTop.neuronID;
107          # update the current neuron
108          UPDATE test.neuron SET activation = tmpActive WHERE neuronID = tmpCurrentNeuronID;
109        end;
110        end if;
111      UNTIL done END REPEAT;
112      CLOSE NNCur;
113    end;
114    end if;
115  end;
116  end if;
117END $$
118
119
120DROP PROCEDURE IF EXISTS `test`.`NN_backPropagate` $$
121CREATE PROCEDURE `test`.`NN_backPropagate` (
122  prmNeuronID       integer,
123  prmExpextedValue  integer
124)
125BEGIN
126  DECLARE  tmpCurrentLayer        INTEGER     DEFAULT 0;
127  DECLARE  tmpDelta               INTEGER     DEFAULT 0;
128  DECLARE  tmpClass               VARCHAR(45) DEFAULT "NONE";
129  SET tmpClass = (SELECT class FROM test.neuron WHERE neuronID = prmNeuronID);
130  call `test`.`NN_calcActivation`(tmpClass); /* make sure that we aren't correcting something that was never calculated */
131  SELECT layer, (prmExpextedValue - (activation * bias))
132    INTO tmpCurrentLayer,tmpDelta
133    FROM test.neuron WHERE neuronID = prmNeuronID;
134  if (tmpDelta != 0) then
135  begin
136    # adjust the weights based on weight percentage
137    # how do we do that?
138
139  end;
140  end if;
141
142END $$
143
144DELIMITER ;