First and Last timestamp of the day/week / month
I need to be able to get a first and last timestamp for a day a week or a month. I have an example of what I did so far that gets me that info for a week... but I fear that it is far more complex than it needs to be. Anyone have a simple way to get first and last timestamp for these intervals? SELECT timestamp(ADDDATE(CURDATE(), -1 * (DAYOFWEEK(CURDATE()) - 1))) , from_unixtime(unix_timestamp(ADDDATE(CURDATE()+6, -1 * (DAYOFWEEK(CURDATE()) - 1)))+86399)
RE: Index analyser
Ya, that one is helpful... just trying to land on a solution like I've seen in other DB's that have index-advisor that listens and creates what it thinks is the perfect indexes ... but thx... From: mos [mo...@fastmail.fm] Sent: Tuesday, February 23, 2010 4:33 PM To: mysql@lists.mysql.com Subject: Re: Index analyser At 03:28 PM 2/23/2010, you wrote: >Is there still no such thing anywhere for Mysql as an index analyser? >Many others have such a thing that will sit and monitor db activity over a >poeriod of time and suggest the exact indexes on each table based on what >it has seen to improve performance >Anyone got that for MySQL? You can look at www.maatkit.org/doc/mk-query-digest.html DESCRIPTION This tool was formerly known as mk-log-parser. mk-query-digest is a framework for doing things with events from a query source such as the slow query log or PROCESSLIST. By default it acts as a very sophisticated log analysis tool. You can group and sort queries in many different ways simultaneously and find the most expensive queries, or create a timeline of queries in the log, for example. It can also do a "query review," which means to save a sample of each type of query into a MySQL table so you can easily see whether you've reviewed and analyzed a query before. The benefit of this is that you can keep track of changes to your server's queries and avoid repeated work. You can also save other information with the queries, such as comments, issue numbers in your ticketing system, and so on. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bcantw...@firescope.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Index analyser
Is there still no such thing anywhere for Mysql as an index analyser? Many others have such a thing that will sit and monitor db activity over a poeriod of time and suggest the exact indexes on each table based on what it has seen to improve performance Anyone got that for MySQL?
dynamic sql using INTO
I have a function built that will get me X percentile from my history table for last X days. It works fine as long as I hard code the certain values in where they go... what I really need to know is how can I make dynamic sql still do a select into a variable? simply replacing the '31' below with 'duration' does not work in here because it is not a proper place for a variable substitution DELIMITER $$ -- -- Definition for function firescope.getPercentile -- DROP FUNCTION IF EXISTS firescope.getPercentile$$ CREATE FUNCTION getPercentile(iid BIGINT(20), duration INT, percentile INT) RETURNS TEXT CHARSET LATIN1 BEGIN DECLARE cnt, pct BIGINT(20); DECLARE temp TEXT; SELECT COUNT(*), AVG(value) INTO cnt FROM history WHERE itemid = iid AND clock > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 31 DAY)); /* this 31 should be duration from the in list*/ SET @calc = 100 / (100 - percentile); SET @yarp = cnt / @calc; SELECT value INTO temp FROM history WHERE itemid = iid AND clock > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 31 DAY)) /* this 31 should be duration from the in list*/ ORDER BY value DESC LIMIT 0, 1; /*this 0 should be @yarp*/ RETURN temp; END $$ DELIMITER ;
RE: compare column value to anything in a list of values
A simple IN would not match if the actual value of the values column is
litterally ''produce(grape,orange,pear,apple,lettuce)' and you want to see if
any one of ('apple','orange','banana') is in there
From: dryd...@optonline.net [dryd...@optonline.net]
Sent: Wednesday, February 17, 2010 11:31 AM
To: Cantwell, Bryan
Subject: Re: compare column value to anything in a list of values
yes, there is... IN
SELECT * FROM tbl WHERE col IN ("v1", "v2", "v3")
- Original Message -
From: "Cantwell, Bryan"
Date: Wednesday, February 17, 2010 12:25 pm
Subject: compare column value to anything in a list of values
To: "mysql@lists.mysql.com"
> Is there a simple function or method to compare a value in a
> column to one or more items in a comma separated list?
>
> select * from table where value
> ('apple','orange','banana');
> and say value may = something like
> 'produce(grape,orange,pear,apple,lettuce)'--
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=dryd...@optonline.net
>
compare column value to anything in a list of values
Is there a simple function or method to compare a value in a column to one or
more items in a comma separated list?
select * from table where value ('apple','orange','banana');
and say value may = something like 'produce(grape,orange,pear,apple,lettuce)'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Lesser of two values in list
I need to find the lesser of two values provided in a list. I know I can do it with a couple of IF's in a function, but I'd really like to know if there is already a function that I can use. min(12,3,1,4) of course doesnt work, but is there some other math function I am overlooking in MySql? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Return row even if nothing found
I have a situation where I need to always get a row returned even if no match is in the table (only 1 or many rows are acceptable). I can use: select a, b, c from mytable where a = 'yarp'; and might get 20 rows if there are matches, but I at least need 1 default row back... using : select ifnull(a,'NOTHING') as a, ifnull(b, 'NOTHING') b, ifnull(c, 'NOTHING') c from mytable where a = 'yarp'; just returns nothing... Anything I can add in here to have a recordset of at least (nothing, nothing, nothing) ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication recovery on restart
Yes I am trying to simulate total failure. In this test case I am using 2 Virtual Machines and I just kill one and then when it comes back I have the challenge described. How can I go about getting the slave back in tune with the newly restarted master? Thanks -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Friday, July 31, 2009 1:21 PM To: Cantwell, Bryan; mysql@lists.mysql.com Subject: RE: Replication recovery on restart Bryan, How are you restarting mysql? In the case a master crashes, it's definitely common for the slave to miss the fact that the master is using a different binlog. The slave advances to a position past the end of the previous binlog, and stops with and error like "tried to read impossible position." In this case you do have to intervene, but that's an easy enough case to write a script to handle. When restarting mysql normally, you shouldn't have this problem: i.e. service mysql restart / /etc/ini.d/mysql restart Regards, Gavin Towey -Original Message----- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Friday, July 31, 2009 10:08 AM To: mysql@lists.mysql.com Subject: RE: Replication recovery on restart Before I simulate a total server failure, master1 is using binary file msyql-bin1 position 2231467 and it's slave master2 is following the correct file at the correct position. This is after initial setup. Once I restart master1, it will then start to use msyql-bin2 position 98 and master 2 is still trying to follow msyql-bin1 position 2231467. And since I have this as dual master setup, if I simulate both boxes restarting in a total catastrophe, the masters both change files and the slaves remain trying to follow on the old information. -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Thursday, July 30, 2009 5:08 PM To: Cantwell, Bryan; mysql@lists.mysql.com Subject: RE: Replication recovery on restart Hi Bryan, Please define "out of whack." Tell us exactly what you're doing when you restart, and what the replication state is before and after, and where the updates are coming from. Regards, Gavin Towey -----Original Message- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Thursday, July 30, 2009 11:00 AM To: mysql@lists.mysql.com Subject: Replication recovery on restart I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databases both replicating wonderfully. They are configured in a dual master scenario so that one can take over for the other in my HA environment I've built. All is working great until... If one or the other box reboots or the mysql restarts, the replication gets out of whack. Especially if I simulate both of them crashing in a worst case scenario, they are then both trying to sync from the wrong Master_log_file and Read_Master_Log_Pos... Since catastrpohe WILL happen eventually (heence the need for HA) how do I direct the newly restarted boxes to the right position in the correct files on restart? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication recovery on restart
Before I simulate a total server failure, master1 is using binary file msyql-bin1 position 2231467 and it's slave master2 is following the correct file at the correct position. This is after initial setup. Once I restart master1, it will then start to use msyql-bin2 position 98 and master 2 is still trying to follow msyql-bin1 position 2231467. And since I have this as dual master setup, if I simulate both boxes restarting in a total catastrophe, the masters both change files and the slaves remain trying to follow on the old information. -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Thursday, July 30, 2009 5:08 PM To: Cantwell, Bryan; mysql@lists.mysql.com Subject: RE: Replication recovery on restart Hi Bryan, Please define "out of whack." Tell us exactly what you're doing when you restart, and what the replication state is before and after, and where the updates are coming from. Regards, Gavin Towey -Original Message----- From: Cantwell, Bryan [mailto:bcantw...@firescope.com] Sent: Thursday, July 30, 2009 11:00 AM To: mysql@lists.mysql.com Subject: Replication recovery on restart I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databases both replicating wonderfully. They are configured in a dual master scenario so that one can take over for the other in my HA environment I've built. All is working great until... If one or the other box reboots or the mysql restarts, the replication gets out of whack. Especially if I simulate both of them crashing in a worst case scenario, they are then both trying to sync from the wrong Master_log_file and Read_Master_Log_Pos... Since catastrpohe WILL happen eventually (heence the need for HA) how do I direct the newly restarted boxes to the right position in the correct files on restart? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication recovery on restart
I have 2 machines 'master' and 'slave'. I have the mysql 5.0.51a-log databases both replicating wonderfully. They are configured in a dual master scenario so that one can take over for the other in my HA environment I've built. All is working great until... If one or the other box reboots or the mysql restarts, the replication gets out of whack. Especially if I simulate both of them crashing in a worst case scenario, they are then both trying to sync from the wrong Master_log_file and Read_Master_Log_Pos... Since catastrpohe WILL happen eventually (heence the need for HA) how do I direct the newly restarted boxes to the right position in the correct files on restart? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication switch Master to slave and back
I have successfully set up a replication master/slave scenario with my MySQL 5.0.51a Now in the event of a fail over to the slave (an identical appliance), I want the old master to become the slave for an eventual switch back the other way. Would it really take the same process to keep the old master up to date with the new one? Or is there a way to bring it up to date with the new machine without a mysqldump or copying data files? I have binary logging running on both machines in hopes that I could just tell the new slave how to catch up with the new master... Any assistance here? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Create function ignore deps
I want to check all my functions and procs into my svn as individual sql files. When I use these to create my db, the person doing this may not realize the correct order to run these files and not have dependency challenges... How can I have procs that depend on functions, or vice versa, get successfully created without regard to correct order? Thanks Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Index time columns?
I have a debate with someone about indexes on time columns. So I ask the community, should you put an index on a timestamp column of a table? Thanks...
Size limitation of user variable?
I am trying to put the result of a function that returns MEDIUMTEXT into a user variable in my procedure. I haven't attempted to push the limits of the MEDIUMTEXT size, but wonder if the user variable can even handle this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Function call reult in a WHERE-IN clause
I have a function that I built that returns a string that is really a comma separated list of values (below). I would like to use that returned value in an IN clause in sql. : select * from hosts where hostid in (getHosts(10014)); The function: CREATE FUNCTION getUserHosts(userID BIGINT(20) UNSIGNED) RETURNS varchar(4096) CHARSET latin1 BEGIN DECLARE hosts VARCHAR (4096); SELECT GROUP_CONCAT(DISTINCT h.hostid) INTO hosts FROM hosts h LEFT JOIN hosts_groups hg ON hg.hostid = h.hostid LEFT JOIN groups g ON g.groupid = hg.groupid LEFT JOIN rights r ON r.id = g.groupid AND r.type = 1 LEFT JOIN users_groups ug ON ug.usrgrpid = r.groupid LEFT JOIN nodes n ON getNodeFromID(h.hostid) = n.nodeid WHERE ug.userid = userID AND r.permission = 3 AND h.status <> 4 ORDER BY h.hostid; RETURN hosts; END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
create single resultset from multiple sets
I have a procedure (proc1) that calls another procedure (proc2) several times. I'd like to make proc1 return the proc2 results in a single resultset. How can this be done in mysql 5.0.51? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function returns null when running sql manually works
I have the solution, had to add a begin/end inside the repeat to protect
my original done var and declare another inside the loop.
I'm glad we had this little talk =P
l8r
DELIMITER $$
DROP FUNCTION IF EXISTS `firescope`.`reportWhereClause`$$
CREATE [EMAIL PROTECTED] FUNCTION `reportWhereClause`(RepID
BIGINT(20) UNSIGNED) RETURNS varchar(255) CHARSET latin1
BEGIN
DECLARE doneINT DEFAULT 0;
DECLARE filterSQL, filterTMP, colName VARCHAR (255);
DECLARE colID, rID BIGINT (20) UNSIGNED;
DECLARE cur1 CURSOR FOR SELECT
report_column_id, column_name
FROM
report_columns
WHERE
report_id = RepID
ORDER BY
report_column_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;
SELECT CONCAT(' ', rvf.filter_operator, ' ', colName, ' ', (CASE
rvf.filter_condition WHEN 'not_equal' THEN '<>' WHEN 'greater_than' THEN
'>' ELSE '=' END)
, ' ', '''', rvf.filter_value,'''', ' ')
INTO
filterTMP
FROM
report_filters rvf
WHERE
rvf.report_id = RepID
AND rvf.report_column_id = colID;
IF filterTMP IS NOT NULL THEN
IF filterSQL IS NULL THEN
SELECT
filterTMP
INTO
filterSQL;
ELSE
SELECT
CONCAT(filterSQL, filterTMP)
INTO
filterSQL;
END IF;
END IF;
SET filterTMP = NULL;
END;
END IF;
UNTIL done
END REPEAT;
CLOSE cur1;
RETURN filterSQL;
END$$
DELIMITER ;
-Original Message-
From: Cantwell, Bryan [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2008 5:13 PM
To: mysql@lists.mysql.com
Subject: RE: Function returns null when running sql manually works
OK, I know WHY it is returning null, just not WHAT to do about it.
In the inside sql, there is not always a result. So, done becomes 1 and
the repeat exits.
How can I keep from this happening? How could I make another 'done' like
variable that would not get also set to 1 if the inner sql doesn't
return a record in this pass?
thx
-Original Message-
From: Cantwell, Bryan [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2008 4:17 PM
To: mysql@lists.mysql.com
Subject: Function returns null when running sql manually works
Below I have a function with a cursor. I have tested the cursor sql
manually and it is fine, I have put the variables into the sql inside
the cursor loop and it returns data too, BUT, executing this function
returns null even though I know the correct info is available. Am I
missing something obvious here?
CREATE TABLE `report_columns` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`column_index` smallint(6) NOT NULL,
`column_name` varchar(128) NOT NULL,
`column_alias` varchar(128) NOT NULL,
`visible` char(1) NOT NULL,
`relationship_type_id` bigint(20) NOT NULL,
`relationship_ci` bigint(20) NOT NULL,
`relationship_index` bigint(20) NOT NULL,
`graph_value` tinyint(4) NOT NULL,
`graph_label` tinyint(4) NOT NULL,
PRIMARY KEY (`report_id`,`report_column_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `report_filters` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`report_filter_id` bigint(20) NOT NULL default '0',
`filter_condition` varchar(128) default NULL,
`filter_value` varchar(128) default NULL,
`filter_special` varchar(128) default NULL,
`filter_operator` varchar(10) default NULL,
PRIMARY KEY (`report_id`,`report_column_id`,`report_filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS firescope.testing;
CREATE FUNCTION firescope.`testing`(RepID BIGINT(20) unsigned) RETURNS
varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE filterSQL,filterTMP, colName varchar(255);
DECLARE colID, rID BIGINT(20) unsigned;
DECLARE cur1 CURSOR FOR SELECT report_column_id, column_name
FROM report_columns
WHERE report_id = RepID ORDER BY report_column_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
SELECT concat(' ' ,rvf.filter_operator,'
', colName,' ', (case rvf.filter_condition when 'not_equal' then '<>'
when 'greater_than' then '>' else '=' end)
,' ' ,rvf.filter
RE: Function returns null when running sql manually works
OK, I know WHY it is returning null, just not WHAT to do about it.
In the inside sql, there is not always a result. So, done becomes 1 and
the repeat exits.
How can I keep from this happening? How could I make another 'done' like
variable that would not get also set to 1 if the inner sql doesn't
return a record in this pass?
thx
-Original Message-
From: Cantwell, Bryan [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2008 4:17 PM
To: mysql@lists.mysql.com
Subject: Function returns null when running sql manually works
Below I have a function with a cursor. I have tested the cursor sql
manually and it is fine, I have put the variables into the sql inside
the cursor loop and it returns data too, BUT, executing this function
returns null even though I know the correct info is available. Am I
missing something obvious here?
CREATE TABLE `report_columns` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`column_index` smallint(6) NOT NULL,
`column_name` varchar(128) NOT NULL,
`column_alias` varchar(128) NOT NULL,
`visible` char(1) NOT NULL,
`relationship_type_id` bigint(20) NOT NULL,
`relationship_ci` bigint(20) NOT NULL,
`relationship_index` bigint(20) NOT NULL,
`graph_value` tinyint(4) NOT NULL,
`graph_label` tinyint(4) NOT NULL,
PRIMARY KEY (`report_id`,`report_column_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `report_filters` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`report_filter_id` bigint(20) NOT NULL default '0',
`filter_condition` varchar(128) default NULL,
`filter_value` varchar(128) default NULL,
`filter_special` varchar(128) default NULL,
`filter_operator` varchar(10) default NULL,
PRIMARY KEY (`report_id`,`report_column_id`,`report_filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS firescope.testing;
CREATE FUNCTION firescope.`testing`(RepID BIGINT(20) unsigned) RETURNS
varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE filterSQL,filterTMP, colName varchar(255);
DECLARE colID, rID BIGINT(20) unsigned;
DECLARE cur1 CURSOR FOR SELECT report_column_id, column_name
FROM report_columns
WHERE report_id = RepID ORDER BY report_column_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
SELECT concat(' ' ,rvf.filter_operator,'
', colName,' ', (case rvf.filter_condition when 'not_equal' then '<>'
when 'greater_than' then '>' else '=' end)
,' ' ,rvf.filter_value,'
') into filterTMP
FROM report_filters rvf
WHERE rvf.report_id = RepID
and rvf.report_column_id
= colID;
IF filterTMP is NOT null then
IF filterSQL is null
then
select
filterTMP into filterSQL;
ELSE
select
concat(filterSQL,filterTMP) into filterSQL;
END IF;
END IF;
SET filterTMP = null;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
return filterSQL;
END;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Function returns null when running sql manually works
Below I have a function with a cursor. I have tested the cursor sql
manually and it is fine, I have put the variables into the sql inside
the cursor loop and it returns data too, BUT, executing this function
returns null even though I know the correct info is available. Am I
missing something obvious here?
CREATE TABLE `report_columns` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`column_index` smallint(6) NOT NULL,
`column_name` varchar(128) NOT NULL,
`column_alias` varchar(128) NOT NULL,
`visible` char(1) NOT NULL,
`relationship_type_id` bigint(20) NOT NULL,
`relationship_ci` bigint(20) NOT NULL,
`relationship_index` bigint(20) NOT NULL,
`graph_value` tinyint(4) NOT NULL,
`graph_label` tinyint(4) NOT NULL,
PRIMARY KEY (`report_id`,`report_column_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `report_filters` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`report_filter_id` bigint(20) NOT NULL default '0',
`filter_condition` varchar(128) default NULL,
`filter_value` varchar(128) default NULL,
`filter_special` varchar(128) default NULL,
`filter_operator` varchar(10) default NULL,
PRIMARY KEY (`report_id`,`report_column_id`,`report_filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS firescope.testing;
CREATE FUNCTION firescope.`testing`(RepID BIGINT(20) unsigned) RETURNS
varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE filterSQL,filterTMP, colName varchar(255);
DECLARE colID, rID BIGINT(20) unsigned;
DECLARE cur1 CURSOR FOR SELECT report_column_id, column_name
FROM report_columns
WHERE report_id = RepID ORDER BY report_column_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
SELECT concat(' ' ,rvf.filter_operator,'
', colName,' ', (case rvf.filter_condition when 'not_equal' then '<>'
when 'greater_than' then '>' else '=' end)
,' ' ,rvf.filter_value,'
') into filterTMP
FROM report_filters rvf
WHERE rvf.report_id = RepID
and rvf.report_column_id
= colID;
IF filterTMP is NOT null then
IF filterSQL is null
then
select
filterTMP into filterSQL;
ELSE
select
concat(filterSQL,filterTMP) into filterSQL;
END IF;
END IF;
SET filterTMP = null;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
return filterSQL;
END;
