Commits

György Kohut  committed 445302a

add minute time series table

  • Participants
  • Parent commits 4a73157

Comments (0)

Files changed (4)

File schema/hbstats.erm

 	</page_setting>
 	<category_index>0</category_index>
 	<zoom>1.0</zoom>
-	<x>-28</x>
-	<y>-15</y>
+	<x>0</x>
+	<y>-40</y>
 	<default_color>
 		<r>128</r>
 		<g>128</g>
 			</connections>
 			<display>false</display>
 			<creation_date>2012-07-13 17:13:52</creation_date>
-			<updated_date>2012-07-17 21:33:23</updated_date>
+			<updated_date>2012-07-19 21:43:44</updated_date>
 			<model_property>
 				<name>Project Name</name>
 				<value></value>
 			<unsigned>false</unsigned>
 			<args></args>
 			<description></description>
-			<logical_name>ts_h</logical_name>
-			<physical_name>ts_h</physical_name>
+			<logical_name>ts_last</logical_name>
+			<physical_name>ts_last</physical_name>
 			<type>timestamp with time zone</type>
 		</word>
 		<word>
 			<unsigned>false</unsigned>
 			<args></args>
 			<description></description>
-			<logical_name>ts_last</logical_name>
-			<physical_name>ts_last</physical_name>
+			<logical_name>ts_min</logical_name>
+			<physical_name>ts_min</physical_name>
 			<type>timestamp with time zone</type>
 		</word>
 		<word>
 			<option></option>
 			<columns>
 				<normal_column>
-					<word_id>2</word_id>
+					<word_id>3</word_id>
 					<id>0</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>10</word_id>
+					<word_id>13</word_id>
 					<id>8</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>28</word_id>
+					<word_id>32</word_id>
 					<id>9</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>26</word_id>
+					<word_id>24</word_id>
 					<id>10</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 			<connections>
 				<relation>
 					<id>0</id>
-					<source>3</source>
+					<source>1</source>
 					<target>2</target>
 					<child_cardinality>1..n</child_cardinality>
 					<parent_cardinality>1</parent_cardinality>
 				</relation>
 				<relation>
 					<id>1</id>
-					<source>5</source>
+					<source>6</source>
 					<target>2</target>
 					<child_cardinality>1..n</child_cardinality>
 					<parent_cardinality>1</parent_cardinality>
 				</relation>
 				<relation>
 					<id>2</id>
-					<source>1</source>
+					<source>4</source>
 					<target>2</target>
 					<child_cardinality>1..n</child_cardinality>
 					<parent_cardinality>1</parent_cardinality>
 				</relation>
 				<relation>
 					<id>3</id>
-					<source>4</source>
+					<source>3</source>
 					<target>2</target>
 					<child_cardinality>1..n</child_cardinality>
 					<parent_cardinality>1</parent_cardinality>
 				</relation>
 				<relation>
 					<id>4</id>
-					<source>6</source>
+					<source>5</source>
 					<target>2</target>
 					<child_cardinality>1..n</child_cardinality>
 					<parent_cardinality>1</parent_cardinality>
 				<normal_column>
 					<id>12</id>
 					<referenced_column>6</referenced_column>
-					<relation>2</relation>
+					<relation>0</relation>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name>ident_id</logical_name>
 				<normal_column>
 					<id>13</id>
 					<referenced_column>20</referenced_column>
-					<relation>0</relation>
+					<relation>3</relation>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name>binary_id</logical_name>
 				<normal_column>
 					<id>14</id>
 					<referenced_column>30</referenced_column>
-					<relation>1</relation>
+					<relation>4</relation>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name>source_ip</logical_name>
 				<normal_column>
 					<id>15</id>
 					<referenced_column>26</referenced_column>
-					<relation>3</relation>
+					<relation>2</relation>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name>target_port</logical_name>
 				<normal_column>
 					<id>16</id>
 					<referenced_column>34</referenced_column>
-					<relation>4</relation>
+					<relation>1</relation>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name>target_ip</logical_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>12</word_id>
+					<word_id>15</word_id>
 					<id>17</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>24</word_id>
+					<word_id>25</word_id>
 					<id>19</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 			<option></option>
 			<columns>
 				<normal_column>
-					<word_id>3</word_id>
+					<word_id>2</word_id>
 					<id>20</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>15</word_id>
+					<word_id>11</word_id>
 					<id>23</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>21</word_id>
+					<word_id>22</word_id>
 					<id>25</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>13</word_id>
+					<word_id>16</word_id>
 					<id>27</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>29</word_id>
+					<word_id>28</word_id>
 					<id>28</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>23</word_id>
+					<word_id>21</word_id>
 					<id>29</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>11</word_id>
+					<word_id>10</word_id>
 					<id>31</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>33</word_id>
+					<word_id>27</word_id>
 					<id>32</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>25</word_id>
+					<word_id>23</word_id>
 					<id>33</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>14</word_id>
+					<word_id>12</word_id>
 					<id>35</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>32</word_id>
+					<word_id>29</word_id>
 					<id>36</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>22</word_id>
+					<word_id>26</word_id>
 					<id>37</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 		</table>
 		<table>
 			<id>7</id>
-			<height>86</height>
-			<width>263</width>
+			<height>107</height>
+			<width>335</width>
 				<font_name>Sans</font_name>
 				<font_size>9</font_size>
-			<x>678</x>
-			<y>35</y>
+			<x>694</x>
+			<y>106</y>
 			<color>
 				<r>128</r>
 				<g>128</g>
 					<referenced_complex_unique_key>null</referenced_complex_unique_key>
 				</relation>
 			</connections>
-			<physical_name>ts_main_h</physical_name>
-			<logical_name>ts_main_h</logical_name>
+			<physical_name>ts_main_min</physical_name>
+			<logical_name>ts_main_min</logical_name>
 			<description></description>
 			<constraint></constraint>
 			<primary_key_name></primary_key_name>
 			<option></option>
 			<columns>
 				<normal_column>
-					<word_id>27</word_id>
+					<word_id>33</word_id>
 					<id>38</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<default_value></default_value>
 					<auto_increment>false</auto_increment>
 					<foreign_key>false</foreign_key>
-					<not_null>false</not_null>
+					<not_null>true</not_null>
 					<primary_key>false</primary_key>
 					<unique_key>false</unique_key>
 					<character_set></character_set>
 					<foreign_key>true</foreign_key>
 					<not_null>true</not_null>
 					<primary_key>false</primary_key>
-					<unique_key>true</unique_key>
+					<unique_key>false</unique_key>
 					<character_set></character_set>
 					<collation></collation>
 					<sequence>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>16</word_id>
+					<word_id>14</word_id>
 					<id>40</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 			<indexes>
 			</indexes>
 			<complex_unique_key_list>
+				<complex_unique_key>
+					<id>1</id>
+					<name>ts_main_min_key</name>
+					<columns>
+						<column>
+							<id>38</id>
+						</column>
+						<column>
+							<id>39</id>
+						</column>
+					</columns>
+				</complex_unique_key>
 			</complex_unique_key_list>
 			<table_properties>
 				<without_oids>true</without_oids>

File schema/hbstats.sql

 
 /* Drop Tables */
 
-DROP TABLE IF EXISTS TS_MAIN_H;
+DROP TABLE IF EXISTS TS_MAIN_MIN;
 DROP TABLE IF EXISTS AGG_MAIN;
 DROP TABLE IF EXISTS DIM_BINARY;
 DROP TABLE IF EXISTS DIM_IDENT;
 ) WITHOUT OIDS;
 
 
-CREATE TABLE TS_MAIN_H
+CREATE TABLE TS_MAIN_MIN
 (
-	TS_H TIMESTAMP WITH TIME ZONE,
-	AGG_ID BIGINT NOT NULL UNIQUE,
-	N_COUNT BIGINT
+	TS_MIN TIMESTAMP WITH TIME ZONE NOT NULL,
+	AGG_ID BIGINT NOT NULL,
+	N_COUNT BIGINT,
+	CONSTRAINT ts_main_min_key UNIQUE (TS_MIN, AGG_ID)
 ) WITHOUT OIDS;
 
 
 
 /* Create Foreign Keys */
 
-ALTER TABLE TS_MAIN_H
+ALTER TABLE TS_MAIN_MIN
 	ADD FOREIGN KEY (AGG_ID)
 	REFERENCES AGG_MAIN (AGG_ID)
 	ON UPDATE RESTRICT

File schema/hbstats_functions.sql

 $$ language plpgsql;
 
 
+create or replace function merge_ts_main_min() returns void as $$
+begin
+
+create unlogged table _merge_ts_main_min as
+  select
+    a.ts_min, a.agg_id, a.n_count,
+    ts_main_min.ts_min as ts_min_local, ts_main_min.agg_id as agg_id_local
+  from
+    (select
+        date_trunc('minute', ts) as ts_min, agg_id, count(*) as n_count
+      from _new_main
+      where
+        not (ts is null or agg_id is null)
+      group by ts_min, agg_id
+    ) as a
+    left join ts_main_min on
+      a.ts_min = ts_main_min.ts_min and
+      a.agg_id = ts_main_min.agg_id
+;
+
+-- update existing
+update ts_main_min
+  set
+    n_count = coalesce(ts_main_min.n_count, 0) + _merge_ts_main_min.n_count
+  from _merge_ts_main_min
+  where
+    ts_main_min.ts_min = _merge_ts_main_min.ts_min_local and ts_main_min.agg_id = _merge_ts_main_min.agg_id_local and
+    not (_merge_ts_main_min.ts_min_local is null or _merge_ts_main_min.agg_id_local is null)
+;
+
+-- insert new
+insert into ts_main_min (ts_min, agg_id, n_count)
+  select ts_min, agg_id, n_count
+  from _merge_ts_main_min
+  where
+    ts_min_local is null and agg_id_local is null
+;
+
+drop table _merge_ts_main_min;
+
+end;
+$$ language plpgsql;
+
+
+
 
 create or replace function x_log_main_get(out x_log_main.last_id%type) as $$
 

File src/main/java/org/honeynet/hbbackend/stats/Stats.java

 				return;
 			}
 			
-			// merge
+			// merge agg_main
 			proc = db_hbstats.prepareCall("{ call merge_agg_main() }");
 			proc.execute();
 			proc.close();
 			
+			// merge ts_main_min
+			proc = db_hbstats.prepareCall("{ call merge_ts_main_min() }");
+			proc.execute();
+			proc.close();
+			
 			// log
 			proc = db_hbstats.prepareCall("{ call x_log_main_insert(?,?,?, ?) }");
 			proc.setLong(1, lastId);
 		CopyManager cpm_target = target.unwrap(PGConnection.class).getCopyAPI();
 		
 		CopyOut cpo = cpm_source.copyOut("copy ( select * from hbstats_new_main(" + lastId + ") ) to stdout with csv header");
-		CopyIn cpi= cpm_target.copyIn("copy " + table + " from stdout with csv header");
+		CopyIn cpi= cpm_target.copyIn("copy " + table + " from stdin with csv header");
 		
 		byte[] buf;
 		long n = 0, rows;