CREATE EVENT IN MYSQL AND SOME ADVANCED MYSQL COMMANDS

Dear Readers, this blog will be talking about how to create even in MySQL and some advanced MySQL commands. To create event in MySQL, we have to follow a Pattern which is written below..

1. Declare Delimiter

2. Define Name of the event

3. Define when to schedule

4. Start with “DO”

5. Then “Begin”

6. Define Business logic

(Like variable declaration, job which you want to schedule through your Event, any condition…So mainly it is the body of your Event)

7. Then Declare end of your event like “END <delimiter>”

8. Change Delimiter to normal Delimiter.

Example:- Here I tried to call a stored procedure in this event which is scheduled after every 4-Hour, and passed the parameter for that stored procedure by taking two date parameter, and also with some additional parameters, I tried to use loop and if-else condition also in this event…     (Tested and executed Event)

delimiter $$

CREATE EVENT ue_schedule_test

ON SCHEDULE

EVERY 4 HOUR

DO

BEGIN

DECLARE to_temp TEXT(25);

DECLARE from_temp TEXT(25);

DECLARE pv_temp TEXT(20);

DECLARE done INT DEFAULT FALSE;

DECLARE curs1 CURSOR FOR SELECT name FROM data ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN curs1;

read_loop: LOOP

FETCH curs1 INTO pv_temp;

SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),'SYSTEM','+00:00'),INTERVAL -4 HOUR),'%Y-%m-%d %H:%i:%S') INTO from_temp;

SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),'SYSTEM','+00:00'),INTERVAL 4 HOUR),'%Y-%m-%d %H:%i:%S') INTO to_temp;

DELETE FROM availibility WHERE Date=from_temp;

CALL usp_availability_test(from_temp,to_temp,pv_temp,'Total','+00:00','custom');

IF done THEN

LEAVE read_loop;

END IF;

END LOOP;

CLOSE curs1;

END $$

delimiter ;

Some Other Important My-Sql Commands
1. To clear console — \! clear
2. To delete procedure — drop prcedure
3. Show all stored procedure — show procedure status
4. To get 2nd highest salary —SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 1,1
5. To get 3rd highest salary —SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 2,1
6. To convert string into datetime type —SELECT STR_TO_DATE(yourdatefield, ‘%m/%d/%Y’) FROM
7. To check event scheduler is ON/OFF — select @@event_scheduler
8. To start event-scheduler — set GLOBAL event_scheduler=ON
9. To delete duplicate records from table
— delete from table1 USING table1, table1 as vtable
WHERE table1.ID<vtable.ID AND table1.field_name=vtable.field_name;
So, These are the some Advanced My-SQL features , which may help you…

Have a Good Day………!!!
PUSHPRAJ KUMAR
Helical IT Solutions

D3 Bubble Chart Integration with Jaspersoft

In this blog we will be discussing about D3 Bubble Chart Integration with Jaspersoft using HTML method of integration.

All the reports are develop using ireport 5.5 professional and jasper server 5.5

As html component of jasper server does not load any scripts in the html component, we loaded the script in one of the decorator page(jsp page). The page is located at the location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\WEB-INF\decorators\decorator.jsp

In the page we included the scripts which we want to load. We added the following code in the jsp page at line no 46:

<script type="text/javascript" language="JavaScript"
src="${pageContext.request.contextPath}/scripts/d3.v3.min.js"></script>

The script to be added should be kept at location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\scripts

Bubble Chart

Bubble Charts Integration with Jaspersoft

Bubble Charts Integration with Jaspersoft

    Bubble Chart:-


For this chart we need to include one more js script file in the decorator page as described in the start of the document.
The js file is sankey.js and can be downloaded from

http://dimplejs.org/dist/dimple.v1.1.5.min.js

Sample Code is shown below:

var svg = dimple.newSvg("#chartContainer", 1090, 500);
var data = [
{
"Date":"1/1/2011",
"Analyst":"Cidalina Rivera",
"Category":"Application",
"Total Minutes":2114200,
"Minutes Per Item":1100,
"% SLA":80
},
{
"Date":"1/1/2011",
"Analyst":"Kiran Parvathala",
"Category":"Application",
"Total Minutes":391800,
"Minutes Per Item":1959,
"% SLA":21
},
{
"Date":"1/1/2011",
"Analyst":"KrishnaReddy Mavuru",
"Category":"Application",
"Total Minutes":1056125,
"Minutes Per Item":1207,
"% SLA":26
},
{
"Date":"1/1/2011",
"Analyst":"Narasimha Dara",
"Category":"Application",
"Total Minutes":386740,
"Minutes Per Item":610,
"% SLA":30
},
{
"Date":"1/1/2011",
"Analyst":"AslamJavid Shaik",
"Category":"Application",
"Total Minutes":1573856,
"Minutes Per Item":1096,
"% SLA":94
},
{
"Date":"1/1/2011",
"Analyst":"Harini Vemulapalli",
"Category":"Application",
"Total Minutes":2846340,
"Minutes Per Item":1890,
"% SLA":100
},
{
"Date":"1/1/2011",
"Analyst":"William Nelson",
"Category":"Application",
"Total Minutes":1205502,
"Minutes Per Item":662,
"% SLA":40
},
{
"Date":"1/1/2011",
"Analyst":"Janaki Govindarajan",
"Category":"Batch Job",
"Total Minutes":72684,
"Minutes Per Item":673,
"% SLA":79
},
{
"Date":"1/1/2011",
"Analyst":"Chiranjeevi Krishna Karne",
"Category":"Education",
"Total Minutes":542348,
"Minutes Per Item":3307,
"% SLA":52
},
{
"Date":"1/1/2011",
"Analyst":"Masaru Hirata",
"Category":"Fileshare",
"Total Minutes":129090,
"Minutes Per Item":662,
"% SLA":12
},
{
"Date":"1/1/2011",
"Analyst":"Naveen Kodali",
"Category":"Phone",
"Total Minutes":75076,
"Minutes Per Item":548,
"% SLA":84
},
{
"Date":"1/1/2011",
"Analyst":"JoshyPeter Joseph",
"Category":"Phone",
"Total Minutes":574860,
"Minutes Per Item":2948,
"% SLA":27
},
{
"Date":"1/1/2011",
"Analyst":"Maheshwar Malkapuram",
"Category":"Phone",
"Total Minutes":19845,
"Minutes Per Item":105,
"% SLA":92
},
{
"Date":"1/1/2011",
"Analyst":"Sunil Bhalerao",
"Category":"Procurement",
"Total Minutes":353000,
"Minutes Per Item":1765,
"% SLA":20
},
{
"Date":"1/1/2011",
"Analyst":"Tina Chan-Browne",
"Category":"Report",
"Total Minutes":51121,
"Minutes Per Item":469,
"% SLA":95
},
{
"Date":"1/1/2011",
"Analyst":"Sirajuddin Mohammad",
"Category":"Report",
"Total Minutes":323363,
"Minutes Per Item":1693,
"% SLA":47
},
{
"Date":"1/1/2011",
"Analyst":"Nishanth Nadam",
"Category":"Enterprise",
"Total Minutes":620000,
"Minutes Per Item":10000,
"% SLA":22
},
{
"Date":"1/1/2011",
"Analyst":"Santoshkumar Shinde",
"Category":"Enterprise",
"Total Minutes":715000,
"Minutes Per Item":11000,
"% SLA":90
},
{
"Date":"1/1/2011",
"Analyst":"Keith Moller",
"Category":"Enterprise",
"Total Minutes":948000,
"Minutes Per Item":12000,
"% SLA":100
},
{
"Date":"1/1/2011",
"Analyst":"AshokKumar Sangeetham",
"Category":"Server",
"Total Minutes":1166550,
"Minutes Per Item":7070,
"% SLA":88
}
];
data = dimple.filterData(data, "Date", "1/1/2011");
var myChart = new dimple.chart(svg, data);
myChart.setBounds(400, 60, 500, 330);
myChart.addMeasureAxis("x", "Minutes Per Item");
myChart.addMeasureAxis("y", "Total Minutes");
myChart.addMeasureAxis("z", "% SLA");
myChart.addSeries(["Analyst","Category"], dimple.plot.bubble);
myChart.addLegend(600, 10, 360, 30, "right");
myChart.draw();

    Integration with JasperServer:


The data which we use for developing the calendar view can be fetched from any database. The data fetched from database is stored in a variable and is then accessed in the html component using the same variable. Applying this of process makes the report dynamic instead of static. Few parameters can also be added in the report which can be used in query and/or html component.
Generally for these type of charts we pass a variable which contains required data containing date, hour and a value associated with that particular date and hour. The string is created according to JSON format, so that when accessed in script tag, can be easily converted to JSON object using eval function.
Any variable/parameter can be accessed as shown below:
” var arr =”+$V{variable1}+” ”
Parameter in query:
Select * from table_name
where date between $P{parameter1} and $P{parameter2}

The steps on how to integrate it with jasperserver was discussed in my previous blog(D3 Integrating with Jasperserver).

Introduction to XSD – XML Schema Definition

This blog will talk about Introduction to XSD – XML Schema Definition   XML Schema Definition:- XML Schema describes the structure of an XML document, what a given XML document can contain, in the same way that a database schema describes the data that can be contained in a database (i.e. table structure, data types, constraints etc.). The XML schema defines the shape, or structure, of an XML document, along with rules for data content. The purpose of an XML Schema is to define the legal building blocks of an XML document, just like a DTD. XML Schemas are richer and more powerful than DTDs. XSD (XML Schema Definition), a Recommendation of the World Wide Web Consortium (W3C)

  • defines elements that can appear in a document
  • defines attributes that can appear in a document
  • defines which elements are child elements
  • defines the order of child elements
  • defines the number of child elements
  • defines whether an element is empty or can include text
  • defines data types for elements and attributes
  • defines default and fixed values for elements and attributes
  • xml schema support datatype
  • XML Schemas are written in XML
  • XML Schemas support data types
  • XML Schemas support namespaces

NameSpace:-

  • XML Namespace is a mechanism to avoid name conflicts by differentiating elements or attributes within an XML document that may have identical names, but different definitions.
  • It solve the name conflict by using a prefix.

<code><h:table> <h:tr>         <h:td>IT</h:td>          <h:td>Management</td> </h:tr> </h:table> <f:table>                 <f:name>Playground</f:name>                     <f:width>100</f:width>                 <f:length>200</f:width> </f:table> </code>

namespace for the prefix must be defined. The namespace is defined by the xmlns attribute in the start tag of an element. The namespace declaration has the following syntax. xmlns:prefix=”URI“. <root> <h:table  xmlns:h=”http://www.w3.org/tr/html4”> <h:tr>           <h:td>IT</h:td>                           <h:td>Management</h:td> </h:tr> </h:table> </root>   Advantages of XML Schemas Created Using XSD:-

  • XSD provides control over the type of data that can be assigned to elements and attributes.
  • XSD enables you to create your own data types. This feature enhances the flexibility of defining the structure of the XML document.
  • XSD enables you to specify restriction on data. For example, you can ensure that the content of an element is a positive integer value.
  • The syntax for defining an XSD is the same as the syntax used for xml documents. Therefore, it is easier to learn the syntax of an XSD.
  • XML schema content models can be used to validate mixed content.

XML schema is extensible. This enables you to reuse parts of a schema in another schema and derive custom data types from existing data types.

How to Use LUA as Web Script

Lua is commonly described as a “multi-paradigm” language, providing a small set of general features that can be extended to fit different problem types, rather than providing a more complex and rigid specification to match a single paradigm. Lua, for instance, does not contain explicit support for inheritance, but allows it to be implemented with metatables. Similarly, Lua allows programmers to implement namespaces, classes, and other related features using its single table implementation; first-class functions allow the employment of many techniques from functional programming; and full lexical scoping allows fine-grained information hiding to enforce the principle of least privilege.

lua base language is light – the full reference interpreter is only about 180 kB compiled– and easily adaptable to a broad range of applications.

Lua is a dynamically typed language intended for use as an extension or scripting language, and is compact enough to fit on a variety of host platforms.

It supports only a small number of atomic data structures such as

– Boolean values,

– Numbers (double-precision floating point by default), and

– Strings.

Typical data structures such as

– Arrays,

– Sets,

– Lists, and

– Records

can be represented using Lua’s single native data structure,

  •  the table, which is essentially a heterogeneous associative array.
Ex. a_table = {} -- Creates a new, empty table

NOTES--Lua programs are not interpreted directly from the textual Lua file, but are compiled into byte-code which is then run on the Lua virtual machine. The compilation process is typically transparent to the user and is performed during run-time, but it can be done offline in order to increase loading performance or reduce the memory footprint of the host environment by leaving out the compiler.

How  to Add Buttons in LUA and Handling their  Events…

Button1 = exSection:option(Button, “_button_click_event “,translate(“Button Section”))      Note:- exSection  is defined in a map, and button is defined in that exSection-section

Button1.title = translate(“CLICK  HERE TO PERFORM BUTTON-ACTION”)

Button1.inputtitle=translate(” START “)

Button1.width=80

Function Button1.write(self, section, value)        –Defining  button-click event function

luci.sys.call(‘/etc/init.d/test_daemon  start’)        –Firing a system-command   to start a script

end

How  to call Templates from LUA and calling LUA-function from  template

testTemplate = exSection:option(Value, “_custom”, translate(“Template Testing… “))

testTemplate.template = “Template-Testing”

Template-Testing.htm

<%+cbi/valueheader%>

<%-  …… LUA code which you want to execute in your lua page…..  %>

<input type=”text ” name=”cbi.read.testintvl” id=” cbi.read.testintvl ” value=”<%-=h_testlval-%>”></input>                           ——Your HTML Code  here h_testlval   value comes from lua page.

<%+cbi/valuefooter%>

Note:- In LUA page we called any input-type(EX.Button,TextBox..)From their name.

Function Definitions in lua page

function testTemplate.lastmodifiedtime(self, section)

local mdtime=st.mtime(“/usr/lib/test.txt”)

return os.date(‘%c’, mdtime)

end

How  to create MAP and SECTION in lua page

local m, s

m=Map (“testing”, translate (“TEST-APPLICATION CONFIGURATION “))

s = m: section (TypedSection, “testagent”, translate (“TESTAGENT”))

s.anonymous = true

Predefined Function of lua when using map.

I.            m.on_init=function(self)

———–Things which you want to execute before your lua page load———

End

II.            m.on_before_commit = function(self)

——Before commit———-

End

III.            m.on_after_commit=function(self)

—–After Commit———-

End

LUA Uses by different Organization in their Application.

  1. Adobe Photoshop Lightroom uses LUA for its user interface.
  2. Apache HTTP Server can use LUA anywhere in the request process (since version 2.3, via the core mod_lua module).
  3. Cisco uses LUA to implement Dynamic Access Policies within the Adaptive Security Appliance.
  4. iClone, a 3D real-time animation studio to create animation movies uses Lua in the controls of its new physic simulation.
  5. MySQL Proxy uses LUA for its extensions
  6. VLC media player uses LUA to provide scripting support.

Wikipedia since March 2013 LUA is used as a new templating language, and its template library is growing.

 

Pushpraj Kumar,

Helical IT Solutions