powered by Jive Software

Sql to add v2stats tables and indexes?

I’‘m not sure if this is the right place to ask, but in the README for the V2Stats plug-in it said support is “mostly” provided in these forums, so here goes. If anyone from V2 is around, could you post the SQL that you use to generate your tables? Having the plug-in create them using Wildfire’‘s db connection isn’‘t going to work in the setup I’‘m working on, as I have Wildfire using a user that doesn’‘t have permission to create tables. I’‘m hoping not to have to reverse engineer the SQL from the tables/indexes, etc., not that it would be too painful, but if anybody could just post the SQL that would save me some time. The plug-in looks great so I’'m excited to get it deployed.

It seems like the forums are pretty quiet these days, so I went ahead and wrote the sql based on the tables. Here it is in case this is useful to anybody else:

INSERT INTO jiveproperty VALUES(’‘plugin.statistics.setup’’, ‘‘true’’);

INSERT INTO jiveproperty VALUES(’‘plugin.statistics.version’’, ‘‘1.0.0 Beta 2’’);

CREATE TABLE v2daypacket (

type varchar2(50) not null,

sender varchar2(50),

time date not null,

psize number not null,

pnumber number not null

);

CREATE INDEX v2daypacket_time_idx ON v2daypacket(time);

CREATE INDEX v2daypacket_type_idx ON v2daypacket(type);

CREATE TABLE v2daystat (

id varchar2(50) not null,

time date not null,

value number not null,

pcount number not null

);

CREATE INDEX v2daystat_id_idx ON v2daystat(id);

CREATE INDEX v2daystat_time_idx ON v2daystat(time);

CREATE TABLE v2hourpacket (

type varchar2(50) not null,

sender varchar2(50),

time date not null,

psize number not null,

pnumber number not null

);

CREATE INDEX v2hourpacket_time_idx ON v2hourpacket(time);

CREATE INDEX v2hourpacket_type_idx ON v2hourpacket(type);

CREATE TABLE v2hourstat (

id varchar2(50) not null,

time date not null,

value number not null,

pcount number not null

);

CREATE INDEX v2hourstat_id_idx ON v2hourstat(id);

CREATE INDEX v2hourstat_time_idx ON v2hourstat(time);

CREATE TABLE v2monthpacket (

type varchar2(50) not null,

sender varchar2(50),

time date not null,

psize number not null,

pnumber number not null

);

CREATE INDEX v2monthpacket_time_idx ON v2monthpacket(time);

CREATE INDEX v2monthpacket_type_idx ON v2monthpacket(type);

CREATE TABLE v2monthstat (

id varchar2(50) not null,

time date not null,

value number not null,

pcount number not null

);

CREATE INDEX v2monthstat_id_idx ON v2monthstat(id);

CREATE INDEX v2monthstat_time_idx ON v2monthstat(time);

CREATE TABLE v2weekpacket (

type varchar2(50) not null,

sender varchar2(50),

time date not null,

psize number not null,

pnumber number not null

);

CREATE INDEX v2weekpacket_time_idx ON v2weekpacket(time);

CREATE INDEX v2weekpacket_type_idx ON v2weekpacket(type);

CREATE TABLE v2weekstat (

id varchar2(50) not null,

time date not null,

value number not null,

pcount number not null

);

CREATE INDEX v2weekstat_id_idx ON v2weekstat(id);

CREATE INDEX v2weekstat_time_idx ON v2weekstat(time);

CREATE TABLE v2yearpacket (

type varchar2(50) not null,

sender varchar2(50),

time date not null,

psize number not null,

pnumber number not null

);

CREATE INDEX v2yearpacket_time_idx ON v2yearpacket(time);

CREATE INDEX v2yearpacket_type_idx ON v2yearpacket(type);

CREATE TABLE v2yearstat (

id varchar2(50) not null,

time date not null,

value number not null,

pcount number not null

);

CREATE INDEX v2yearstat_id_idx ON v2yearstat(id);

CREATE INDEX v2yearstat_time_idx ON v2yearstat(time);

Hi Sam,

Thanks for jumping in and answering your own question. One minor change is that you don’'t need to insert the following:

INSERT INTO jiveproperty VALUES(’‘plugin.statistics.version’’, ‘‘1.0.0 Beta 2’’);

/code

Also, for anyone who might come along and see this thread later, this sql is for Oracle only, the data types for date and text fields vary slightly from database to database.

To avoid this problem in the future we’'re looking at removing the table creation scripts from the code and moving it into *.sql files similar to what Wildfire does.

Thanks again,

Ryan

Version 2 Software