Hannes Pries - Web/Shopware/JavaScript-Developer Blog
Weil es immer eine simple Lösung gibt!

nano /opt/fb_trace_all.conf

<database>
enabled true
log_connections true
log_statement_prepare true
log_statement_start true
log_statement_finish true
print_plan true
print_perf true
time_threshold 0
max_sql_length 65000
max_arg_length 240
max_arg_count 100
</database>

./fbtracemgr -se localhost:service_mgr -user SYSDBA -pass masterkey -start -name allsql -conf /opt/fb_trace_all.conf >> /tmp/lognamen.log

tail -f /tmp/lognamen.log

SELECT x.url, 'single' type, count(x.id)
FROM (
select d.url, p.id, p.parent_id, count(p2.id) children from sales_channel_domain d
join sales_channel sc on d.sales_channel_id = sc.id
join product_visibility pv on pv.sales_channel_id = sc.id
join product p on p.id = pv.product_id
left outer join product p2 on p.id = p2.parent_id
where sc.active = 1
group by d.url, p.id, p.parent_id
)x
WHERE x.parent_id is null and x.children < 1
group by x.url

union all

SELECT x.url, 'main' type, count(x.id)
FROM (
select d.url, p.id, p.parent_id, count(p2.id) children from sales_channel_domain d
join sales_channel sc on d.sales_channel_id = sc.id
join product_visibility pv on pv.sales_channel_id = sc.id
join product p on p.id = pv.product_id
left outer join product p2 on p.id = p2.parent_id
where sc.active = 1
group by d.url, p.id, p.parent_id
)x
WHERE x.parent_id is null and x.children > 0
group by x.url

union all

SELECT x.url, 'variant' type, count(x.id)
FROM (
select d.url, p2.id, p2.parent_id, 0 children from sales_channel_domain d
join sales_channel sc on d.sales_channel_id = sc.id
join product_visibility pv on pv.sales_channel_id = sc.id
join product p on p.id = pv.product_id
left outer join product p2 on p.id = p2.parent_id
where sc.active = 1
)x
group by x.url

DELIMITER \\
DROP PROCEDURE IF EXISTS exampleProcedure\\
CREATE PROCEDURE exampleProcedure()
BEGIN
DECLARE checkval INT;
SET expectedval = 0;

START TRANSACTION;

-- implement your logic here

IF checkval = expectedval THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END \\
DELIMITER ;

CALL exampleProcedure();

DROP PROCEDURE IF EXISTS exampleProcedure;

/**
* https://www.geodatasource.com/developers/php
*
* @param $lat1
* @param $lon1
* @param $lat2
* @param $lon2
* @param string $unit
* @return float
*/
public function calcDistance($lat1, $lon1, $lat2, $lon2, $unit = 'K'): float
{
$theta = $lon1 - $lon2;
$dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
$dist = acos($dist);
$dist = rad2deg($dist);
$miles = $dist * 60 * 1.1515;
$unit = strtoupper($unit);

if ($unit == 'K') {
return ($miles * 1.609344);
} else if ($unit == 'N') {
return ($miles * 0.8684);
} else {
return $miles;
}
}

if(ldt1.isAfter(ldt2)){
days = Duration.between(ldt1.toLocalDate().atStartOfDay(), ldt2.toLocalDate().atStartOfDay()).toDays();
days = Math.abs(days);
}

long days = TimeUnit.DAYS.convert(date.getTime() - date2.getTime(), TimeUnit.MILLISECONDS);

$interval = date_diff($datetime1, $datetime2);
$days = (int) $interval->format('%a');

select datediff('2017-12-24', current_date) waitingforxmas;

SELECT TO_DATE('2017-12-24', 'YYYY-MM-DD') - CURRENT_DATE WaitingForXmas
FROM dual

DateTimeFormatter formatter = DateTimeFormatter.ofPattern(pattern);
LocalDateTime ldt = LocalDateTime.parse(value.trim(), formatter);

//Calendar
GregorianCalendar gc = GregorianCalendar.from(ldt.atZone(ZoneId.systemDefault()));

//SQL-Timestamp
preparedStatement.setTimestamp(1, Timestamp.from(ldt.atZone(ZoneId.systemDefault()).toInstant()));

CREATE DATABASE example;
CREATE USER 'exam'@'localhost' IDENTIFIED BY '3x4m';
GRANT ALL ON example.* TO 'exam'@'localhost';

CREATE TABLE TEST_ITEMS(
ITEM_ID INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
ITEM_VALUE VARCHAR(255) NOT NULL,
PRIMARY KEY(ITEM_ID)
);

INSERT INTO TEST_ITEMS (ITEM_VALUE) VALUES('TEST_01');
INSERT INTO TEST_ITEMS (ITEM_VALUE) VALUES('TEST_02');
INSERT INTO TEST_ITEMS (ITEM_VALUE) VALUES('TEST_03');
INSERT INTO TEST_ITEMS (ITEM_VALUE) VALUES('TEST_04');
INSERT INTO TEST_ITEMS (ITEM_VALUE) VALUES('TEST_05');
INSERT INTO TEST_ITEMS (ITEM_VALUE) VALUES('TEST_06');
INSERT INTO TEST_ITEMS (ITEM_VALUE) VALUES('TEST_07');
INSERT INTO TEST_ITEMS (ITEM_VALUE) VALUES('TEST_08');
INSERT INTO TEST_ITEMS (ITEM_VALUE) VALUES('TEST_09');

<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxTotal="100" maxIdle="30" maxWaitMillis="10000"
username="root" password="" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/blog_test"/>

<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/TestDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

<%@page import="java.sql.ResultSetMetaData"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="javax.sql.DataSource"%>
<%@page import="javax.naming.InitialContext"%>
<%@page import="javax.naming.Context"%>
<html>
<head>
<title>Blog Test DB</title>
<style type="text/css">
table{
border-collapse:collapse;
}

td, th{
border:1px solid #000000;
padding:4px;
}

th{
font-weight:bold;
}
</style>
</head>
<body>
<table>
<%
try{
//get DataSource
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/TestDB");

//prepare SQL
String sql="SELECT * FROM TEST_ITEMS";

//Query database
Connection conn=ds.getConnection();
Statement stmt=conn.createStatement();
ResultSet rset=stmt.executeQuery(sql);
ResultSetMetaData rsetM=rset.getMetaData();

//output
%>
<tr>
<%
for(int i=1; i<=rsetM.getColumnCount();i++){
%>
<th><%=rsetM.getColumnName(i) %></th>
<%
}
%>
</tr>
<%
while(rset.next()){
%>
<tr>
<%
for(int i=1; i<=rsetM.getColumnCount();i++){
%>
<td><%=rset.getString(i) %></td>
<%
}
%>
</tr>
<%
}

//close all resources
rset.close();
stmt.close();
conn.close();
}
catch(Exception e){
e.printStackTrace();
}
%>
</table>
</body>
</html>

CREATE TABLE tests (
test_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
test_name VARCHAR(255) NOT NULL,
PRIMARY KEY (test_id)
);

INSERT INTO tests(test_name) VALUES ('TEST_01');
INSERT INTO tests(test_name) VALUES ('TEST_02');
INSERT INTO tests(test_name) VALUES ('TEST_03');
INSERT INTO tests(test_name) VALUES ('TEST_04');
INSERT INTO tests(test_name) VALUES ('TEST_05');

$result=array();
foreach($data as $row){
$obj=new Test();
$obj->setId($row["test_id"]);
$obj->setName($row["test_name"]);
$result[]=$obj;
}

private function getColumnName($prop){
$result=null;
$doc=$prop->getDocComment();
if(preg_match("/@dbcolumn=[a-zA-Z0-9_]+/",$doc)){
$result=preg_replace("/^.+@dbcolumn=([a-zA-Z0-9_]+)\s.+$/Uis","$1",trim($doc));
}
return $result;
}

private function loadProperties($ref){
if(isset(self::$cache[$ref->getName()])){
return self::$cache[$ref->getName()];
}
else{
$props=$ref->getProperties();
$map=array();
foreach($props as $prop){
$prop->setAccessible(true);
$map[$this->getColumnName($prop)]=$prop;
}
self::$cache[$ref->getName()]=$map;
return self::$cache[$ref->getName()];
}
}

class Test{
/**
* @dbcolumn=test_id
*/
private $id=0;
/**
* @dbcolumn=test_name
*/
private $name="";

public function __construct(){

}

public function getId(){
return $this->id;
}
public function setId($id){
$this->id=$id;
}
public function getName(){
return $this->name;
}
public function setName($name){
$this->name=$name;
}
}

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="datasources.xsl"?>
<datasources>
<datasource name="ds_test" type="DBMySQLi">
<host>localhost</host>
<port>3306</port>
<sid>test</sid>
<username>root</username>
<userpassword>bitnami</userpassword>
</datasource>
</datasources>

include_once("system/PDBC/PDBCDBFactory.php");
PDBCDBFactory::init("system/PDBC/dbclasses/","system/PDBC/conffiles/");

$sql="SELECT test_id,test_name FROM tests ORDER BY test_name ASC";

private function fillObject($row,$ref){
$obj=$ref->newInstance();
$props=$this->loadProperties($ref);
foreach($row as $key => $value){
if(isset($props[$key])){
$props[$key]->setValue($obj,$value);
}
}
return $obj;
}

public function queryList($db,$sql,$className){
$db->executeQuery($sql);
$ref=new ReflectionClass($className);
$result=array();
for($i=0;$i<$db->getCount();$i++){
$result[count($result)]=$this->fillObject($db->getRow($i),$ref);
}
return $result;
}

$mapper=new PDBCObjectMapper();
$tests=$mapper->queryList($db, $sql, "Test");

foreach($tests as $test){
echo $test->getId()." - ".$test->getName()."<br>\n";
}

include_once("system/PDBC/PDBCDBFactory.php");
PDBCDBFactory::init("system/PDBC/dbclasses/","system/PDBC/conffiles/");

class Test{
/**
* @dbcolumn=test_id
*/
private $id=0;
/**
* @dbcolumn=test_name
*/
private $name="";

public function __construct(){

}

public function getId(){
return $this->id;
}

public function setId($id){
$this->id=$id;
}

public function getName(){
return $this->name;
}

public function setName($name){
$this->name=$name;
}
}

$db=PDBCCache::getInstance()->getDB("ds_test");
$sql="SELECT test_id,test_name FROM tests ORDER BY test_name ASC";
$mapper=new PDBCObjectMapper();
$tests=$mapper->queryList($db, $sql, "Test");
foreach($tests as $test){
echo $test->getId()." - ".$test->getName()."<br>\n";
}

select ROUND(disk_reads/decode(executions,0,1,executions)/300) EXTIME, SQL_TEXT
from v$sql
where disk_reads/decode(executions,0,1,executions)/300 >1
and executions>0
order by (disk_reads/decode(executions,0,1,executions)/300) DESC