View Attach (2) Info

Add new attachment

Only authorized users are allowed to upload new attachments.

List of attachments

Kind Attachment Name Size Version Date Modified Author Change note
png
 insert_methods.png 112.2 kB 1 20-Aug-2020 04:28 krivacsz
jpg
 sql_stats1.jpg 163.2 kB 1 07-Jul-2020 14:16 Ada Csaba

This page (revision-37) was last changed on 20-Aug-2020 04:41 by krivacsz

This page was created on 25-Oct-2018 04:31 by Ben Spink

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Difference between version and

At line 3 changed one line
[https://dev.mysql.com/downloads/connector/j/5.1.html]\\
[https://dev.mysql.com/downloads/connector/j/5.1.html]
\\
At line 5 changed one line
\\
Alternatively, can browse the Maven repository for the appropriate JDBC drive matching your SQL server version, if not using the latest build of it
[https://mvnrepository.com/artifact/mysql/mysql-connector-java]
\\
At line 9 changed one line
\\
[{Image src='sql_stats1.jpg' width='..' height='..' align='left|center|right' style='..' class='..' }]
\\
At line 15 changed one line
}}}
}}}\\
\\
Use MySQL Workbench and make a new DB with the name "crushftp_stats" (or adjust the URL above to reference the name you choose.) Below is the schema to put in it:\\
\\
{{{
CREATE TABLE META_INFO(RID DOUBLE NOT NULL PRIMARY KEY,SESSION_RID DOUBLE NOT NULL,TRANSFER_RID DOUBLE NOT NULL,ITEM_KEY VARCHAR(100) DEFAULT NULL,ITEM_VALUE VARCHAR(2000) DEFAULT NULL);
At line 17 changed one line
Use MySQL Workbench and make a new DB with the name "crushftp_stats" (or adjust the URL above to reference the name you choose.) Below is the schema to put in it:
CREATE TABLE SESSIONS(RID DOUBLE NOT NULL PRIMARY KEY,SESSION VARCHAR(100) DEFAULT NULL,SERVER_GROUP VARCHAR(50) DEFAULT NULL,USER_NAME VARCHAR(100) DEFAULT NULL,START_TIME DATETIME DEFAULT NULL,END_TIME DATETIME DEFAULT NULL,SUCCESS_LOGIN VARCHAR(10) DEFAULT NULL,IP VARCHAR(50) DEFAULT NULL);
At line 19 changed 9 lines
{{{
CREATE TABLE META_INFO (
RID double NOT NULL,
SESSION_RID double NOT NULL,
TRANSFER_RID double NOT NULL,
ITEM_KEY varchar(100) default NULL,
ITEM_VALUE varchar(2000) default NULL,
PRIMARY KEY (RID)
);
CREATE TABLE TRANSFERS(RID DOUBLE NOT NULL PRIMARY KEY,SESSION_RID DOUBLE NOT NULL,START_TIME DATETIME DEFAULT NULL,DIRECTION VARCHAR(8) DEFAULT NULL,PATH VARCHAR(255) DEFAULT NULL,FILE_NAME VARCHAR(2000) DEFAULT NULL,URL VARCHAR(2000) DEFAULT NULL,SPEED INTEGER DEFAULT NULL,TRANSFER_SIZE DOUBLE DEFAULT NULL,IGNORE_SIZE VARCHAR(1) DEFAULT NULL);
At line 29 changed 11 lines
CREATE TABLE SESSIONS (
RID double NOT NULL,
SESSION varchar(100) default NULL,
SERVER_GROUP varchar(50) default NULL,
USER_NAME varchar(100) default NULL,
START_TIME datetime default NULL,
END_TIME datetime default NULL,
SUCCESS_LOGIN varchar(10) default NULL,
IP varchar(50) default NULL,
PRIMARY KEY (RID)
);
ALTER TABLE `TRANSFERS` ADD INDEX (`SESSION_RID`,`TRANSFER_SIZE`,`DIRECTION`,`IGNORE_SIZE`,`START_TIME`);
At line 41 changed 13 lines
CREATE TABLE TRANSFERS (
RID double NOT NULL,
SESSION_RID double NOT NULL,
START_TIME datetime default NULL,
DIRECTION varchar(8) default NULL,
PATH varchar(255) default NULL,
FILE_NAME varchar(2000) default NULL,
URL varchar(2000) default NULL,
SPEED int default NULL,
TRANSFER_SIZE double default NULL,
IGNORE_SIZE varchar(1) default NULL,
PRIMARY KEY (RID)
);
}}}\\
\\
That is it. Now the statistics data should be going to the MySQL tables, and reports will automatically run against those tables too.\\
\\
!! Setup Auto increment primary keys
\\
1. On prefs.XML (find it at CrushFTP install folder) change the tag value "__stat_auto_increment__" to __true__.\\
\\
2. Change the RID column (on META_INFO, SESSIONS and TRANSFERS table) to be auto incremented and data type from DOUBLE to BIGINT.\\
\\
{{{
ALTER TABLE `META_INFO` CHANGE COLUMN `RID` `RID` BIGINT(20) NOT NULL AUTO_INCREMENT;
At line 55 changed one line
}}}
ALTER TABLE `SESSIONS` CHANGE COLUMN `RID` `RID` BIGINT(20) NOT NULL AUTO_INCREMENT;
At line 57 changed one line
That is it. Now the statistics data should be going to the MySQL tables, and reports will automatically run against those tables too.
ALTER TABLE `TRANSFERS` CHANGE COLUMN `RID` `RID` BIGINT(20) NOT NULL AUTO_INCREMENT;
}}}\\
\\
3. Adjust insert queries. Remove the __RID__ value from all insert query.\\
\\
[attachments|insert_methods.png]\\
\\
Change __stats_insert_meta_info__ from:\\
{{{
INSERT INTO META_INFO (RID, SESSION_RID, TRANSFER_RID, ITEM_KEY, ITEM_VALUE) VALUES (?,?,?,?,?)
}}}\\
to:\\
{{{
INSERT INTO META_INFO (SESSION_RID, TRANSFER_RID, ITEM_KEY, ITEM_VALUE) VALUES (?,?,?,?)
}}}\\
\\
__stats_insert_sessions__ from:\\
{{{
INSERT INTO SESSIONS (RID, SESSION, SERVER_GROUP, USER_NAME, START_TIME, END_TIME, SUCCESS_LOGIN, IP) VALUES (?,?,?,?,?,?,?,?)
}}}\\
to:\\
{{{
INSERT INTO SESSIONS (SESSION, SERVER_GROUP, USER_NAME, START_TIME, END_TIME, SUCCESS_LOGIN, IP) VALUES (?,?,?,?,?,?,?)
}}}\\
\\
__stats_insert_transfers__ from:\\
{{{
INSERT INTO TRANSFERS (RID, SESSION_RID, START_TIME, DIRECTION, PATH, FILE_NAME, URL, SPEED, TRANSFER_SIZE, IGNORE_SIZE) VALUES (?,?,?,?,?,?,?,?,?,'N')
}}}\\
to:\\
{{{
INSERT INTO TRANSFERS (SESSION_RID, START_TIME, DIRECTION, PATH, FILE_NAME, URL, SPEED, TRANSFER_SIZE, IGNORE_SIZE) VALUES (?,?,?,?,?,?,?,?,'N')
}}}\\
Version Date Modified Size Author Changes ... Change note
37 20-Aug-2020 04:41 3.9 kB krivacsz to previous
36 20-Aug-2020 04:40 3.893 kB krivacsz to previous | to last
35 20-Aug-2020 04:39 3.531 kB krivacsz to previous | to last
34 20-Aug-2020 04:38 3.523 kB krivacsz to previous | to last
33 20-Aug-2020 04:37 3.196 kB krivacsz to previous | to last
32 20-Aug-2020 04:36 3.2 kB krivacsz to previous | to last
31 20-Aug-2020 04:36 3.196 kB krivacsz to previous | to last
30 20-Aug-2020 04:32 2.904 kB krivacsz to previous | to last
29 20-Aug-2020 04:32 2.916 kB krivacsz to previous | to last
28 20-Aug-2020 04:30 2.866 kB krivacsz to previous | to last
27 20-Aug-2020 04:28 2.831 kB krivacsz to previous | to last
26 20-Aug-2020 04:22 2.839 kB krivacsz to previous | to last
25 20-Aug-2020 04:21 2.839 kB krivacsz to previous | to last
24 20-Aug-2020 04:21 2.835 kB krivacsz to previous | to last
23 20-Aug-2020 04:20 2.829 kB krivacsz to previous | to last
22 20-Aug-2020 04:19 2.821 kB krivacsz to previous | to last
21 20-Aug-2020 04:18 2.656 kB krivacsz to previous | to last
« This page (revision-37) was last changed on 20-Aug-2020 04:41 by krivacsz
G’day (anonymous guest)
CrushFTP9 | What's New

Referenced by
General Settings

JSPWiki