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

mysqldump -u demouser -p demo_webshop > ./dump_for_65_update.sql
sed 's/`demo_webshop`.//g' dump_for_65_update.sql > dump_for_65_update_clean.sql
sed -i 's|/\*!50017 DEFINER=`demouser`@`localhost`\*/||g' dump_for_65_update_clean.sql
mysql --database=demo65_webshop --user=demouser65 -p < ./dump_for_65_update_clean.sql

SELECT
sum(round(((data_length + index_length) / 1024 / 1024), 2)) sum_size
FROM information_schema.TABLES;

SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

containerData/
apache/
Dockerfile
my_vhost.conf
setup.sh
db_vol/
sw/
...
docker-compose.yml

FROM php:7.2-apache

RUN apt-get update &&\
apt-get install --no-install-recommends --assume-yes --quiet ca-certificates \
curl \
git \
libxml2-dev \
libxslt-dev \
libfreetype6-dev \
libjpeg62-turbo-dev \
libpng-dev \
libcurl4-gnutls-dev \
zlib1g-dev \
&& docker-php-ext-install -j$(nproc) iconv \
&& docker-php-ext-configure gd --with-freetype-dir=/usr/include/ --with-jpeg-dir=/usr/include/ \
&& docker-php-ext-install -j$(nproc) gd

RUN apt-get install -y libc-client-dev libkrb5-dev \
&& docker-php-ext-configure imap --with-kerberos --with-imap-ssl \
&& docker-php-ext-install imap

RUN docker-php-ext-install pdo_mysql \
&& docker-php-ext-install json \
&& docker-php-ext-install dom \
&& docker-php-ext-install curl \
&& docker-php-ext-install zip \
&& docker-php-ext-install intl \
&& docker-php-ext-install xml \
&& docker-php-ext-install xsl
RUN rm -rf /var/lib/apt/lists/*

#RUN pecl install xdebug-2.8.0 && docker-php-ext-enable xdebug
#RUN echo 'zend_extension="/usr/local/lib/php/extensions/no-debug-non-zts-20151012/xdebug.so"' >> /usr/local/etc/php/php.ini
#RUN echo 'xdebug.remote_port=9000' >> /usr/local/etc/php/php.ini
#RUN echo 'xdebug.remote_enable=1' >> /usr/local/etc/php/php.ini
#RUN echo 'xdebug.remote_host=host.docker.internal' >> /usr/local/etc/php/php.ini

RUN echo 'memory_limit = 512M' >> /usr/local/etc/php/php.ini

RUN a2enmod rewrite

# copy conf-file to /etc/apache2/sites-enabled/000-default.conf
RUN mkdir /files;
COPY ./setup.sh /files/setup.sh
ENTRYPOINT ["sh", "/files/setup.sh"]

<VirtualHost *:80>
ServerName localhost
ServerAlias www.localhost

DocumentRoot "/var/www/html/public"

RewriteEngine On
RewriteMap lc int:tolower

<Directory "/var/www/html/public">
Options Indexes FollowSymLinks
AllowOverride All
Require all granted
DirectoryIndex index.php
</Directory>
</VirtualHost>

/usr/sbin/apache2ctl -D FOREGROUND

version: '3.3'

services:
sw_mysql:
image: bitnami/mysql:5.7
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: sw
MYSQL_USER: sw
MYSQL_PASSWORD: sw
volumes:
- ./db_vol/:/bitnami/mysql/data

sw_http:
build: ./containerData/apache
volumes:
- ./containerData/apache/my_vhost.conf:/etc/apache2/sites-enabled/000-default.conf:rw
- ./sw:/var/www/html:rw,delegated
ports:
- 80:80
- 443:443

mail:
image: mailhog/mailhog
ports:
- 8025:8025

adminer:
image: adminer
ports:
- 8082:8080
depends_on:
- sw_mysql

version: '3.3'

services:
http:
image: geerlingguy/php-apache:latest
ports:
- 8881:80
volumes:
- ./:/var/www/html:rw,delegated
command: ["/usr/sbin/apache2ctl", "-D", "FOREGROUND"]
restart: always

version: '3.3'

services:
mysql:
image: bitnami/mysql:latest
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: test
MYSQL_USER: app
MYSQL_PASSWORD: app
volumes:
- ./devops/sql:/docker-entrypoint-initdb.d
ports:
- 3336:3306

http:
image: geerlingguy/php-apache:latest
ports:
- 8881:80
volumes:
- ./:/var/www/html:rw,delegated
command: ["/usr/sbin/apache2ctl", "-D", "FOREGROUND"]
restart: always
depends_on:
- adminer

adminer:
image: adminer
restart: always
ports:
- 8882:8080
depends_on:
- mysql

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;

UPDATE wp_posts
SET post_content = REPLACE(post_content, 'https:', 'http:')
WHERE post_content like '%https:%';

UPDATE wp_posts
SET post_title = REPLACE(post_title, 'https:', 'http:')
WHERE post_title like '%https:%';

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

thread_stack = 768K

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

$initStatements = [
PDO::MYSQL_ATTR_INIT_COMMAND =>
"SET sql_mode=(SELECT REPLACE(REPLACE(REPLACE(@@sql_mode,'STRICT_TRANS_TABLES',''),'NO_ZERO_DATE',''),'ONLY_FULL_GROUP_BY',''));"
];
$pdo = new PDO("mysql:host=".$host.";dbname=".$dbname, $username, $password, $initStatements);

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

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 USERS(
USER_ID INT(11) UNSGINED NOT NULL,
USER_NAME VARCHAR(255) NOT NULL,
PRIMARY KEY (USER_ID)
);

CREATE TABLE MESSAGES(
MESSAGE_ID INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
MESSAGE_TITLE VARCHAR(255) NOT NULL,
FROM_ID INT(11) UNSIGNED NOT NULL,
TO_ID INT(11) UNSIGNED NOT NULL,
CC_ID INT(11) UNSIGNED NOT NULL,
PRIMARY KEY (MESSAGE_ID)
);

$sql="
SELECT 'user_id', 'user_name'
UNION
SELECT USER_ID,USERNAME
FROM USERS
INTO OUTFILE ".$exchangeFolder."/users.csv
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'
";

$cyp="USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///".$exchangeFolder."/messages.csv" AS row
MERGE (m:message{mid:row.msg_id,title:row.msg_title});";

function cleanFolder($folder){
$files=scandir($folder);
foreach($files as $file){
if(preg_match("/\.csv$/i", $file)){
unlink($folder."/".$file);
}
}
}

include_once("PDBC/PDBCDBFactory.php");
PDBCDBFactory::init("PDBC/dbclasses/","PDBC/conffiles/");
$db=PDBCCache::getInstance()->getDB("embdoop");

use Everyman\Neo4j\Client;
use Everyman\Neo4j\Cypher\Query;

$client = new Everyman\Neo4j\Client();
$client->getTransport()->setAuth("neo4j","blubb");

function executeCypher($query){
global $client;
$query=new Query($client, $query);
$query->getResultSet();
}

//clear for export (if a previous import failed)
cleanFolder($exchangeFolder);

//export nodes
echo "create users.csv\n";
$sql=" SELECT 'user_id', 'user_name' UNION
SELECT USER_ID,USER_NAME
FROM USERS
INTO OUTFILE '".$exchangeFolder."/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'";
$db->execute($sql);

echo "create messages.csv\n";
$sql=" SELECT 'msg_id', 'msg_title' UNION
SELECT MESSAGE_ID, MESSAGE_TITLE
FROM MESSAGES
INTO OUTFILE '".$exchangeFolder."/messages.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'";
$db->execute($sql);

//export relations
echo "create relations_etc.csv\n";
$sql=" SELECT 'user_id', 'msg_id', 'type' UNION
SELECT TO_ID, MESSAGE_ID, 'TO'
FROM MESSAGES
UNION
SELECT CC_ID, MESSAGE_ID, 'CC'
FROM MESSAGES

INTO OUTFILE '".$exchangeFolder."/relations_etc.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'";
$db->execute($sql);

echo "create relations_from.csv\n";
$sql=" SELECT 'user_id', 'msg_id', 'type' UNION
SELECT FROM_ID, MESSAGE_ID, 'FROM'
FROM MESSAGES

INTO OUTFILE '".$exchangeFolder."/relations_from.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'";
$db->execute($sql);

//create indexes for fast import
echo "create index's in neo4j\n";
$cyp="CREATE INDEX ON :user(uid);";
executeCypher($cyp);
$cyp="CREATE INDEX ON :message(mid);";
executeCypher($cyp);

//import nodes
echo "import users.csv\n";
$cyp="USING PERIODIC COMMIT 10000\n
LOAD CSV WITH HEADERS FROM "file:///".$exchangeFolder."/users.csv" AS row\n
MERGE (u:user{uid:row.user_id,name:row.user_name});";
executeCypher($cyp);

echo "import messages.csv\n";
$cyp="USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///".$exchangeFolder."/messages.csv" AS row
MERGE (m:message{mid:row.msg_id,title:row.msg_title});";
executeCypher($cyp);

//import relations
echo "import relations_from.csv\n";
$cyp="USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///".$exchangeFolder."/relations_from.csv" AS row
MATCH(u:user{uid:row.user_id})
MATCH(m:message{mid:row.msg_id})
MERGE (u)-[r:send]->(m);";
executeCypher($cyp);

echo "import relations_etc.csv\n";
$cyp="USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///".$exchangeFolder."/relations_etc.csv" AS row
MATCH(u:user{uid:row.user_id})
MATCH(m:message{mid:row.msg_id})
MERGE (m)-[r:receive]->(u)
SET r.type=row.type;";
executeCypher($cyp);

//clear after import
cleanFolder($exchangeFolder);

MATCH (n)
DETACH DELETE n

SELECT ID,NAME
FROM TEST
WHERE ID>5
INTO OUTFILE '/var/www/app/data/export/out_test.csv'
FIELDS TERMINATED BY ','

sudo aa-status

sudo /etc/init.d/apparmor reload

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";
}

$pattern="";
$pattern=substr($result,$from,$len);

$pattern=substr($result,$from,$len);