CakpPHP – wgrywanie plików na serwer

Tabele MySQL

CREATE TABLE files ( 
  id           INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, 
  file_type_id INT UNSIGNED, 
  project_id   INT UNSIGNED, 
  orygname     VARCHAR(255) NOT NULL, 
  dir          VARCHAR(255), 
  filename     VARCHAR(255) NOT NULL, 
  ext          CHAR(9), 
  description  TEXT, 
  module       ENUM ('project')
  is_active    TINYINT(1) DEFAULT 1, 
  user_id      INT UNSIGNED NOT NULL, 
  created      DATETIME,   
  modified     DATETIME, 
);
CREATE TABLE file_types ( 
  id          INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, 
  name        VARCHAR(255) NOT NULL,
  description TEXT
);

CakePHP MySQL – procedury składowane

Utworzenie procedury składowanej w MySQL

DELIMITER $$
CREATE PROCEDURE low_amount()
BEGIN
SELECT ip.id, ip.product_id, ip.brand_id, ip.amount_alert, ip.is_ordered, SUM(ip.amount) AS suma, 
       p.name AS product, b.initials, b.name AS brand
FROM item_packs ip 
    INNER JOIN products p ON p.id=ip.product_id
    INNER JOIN brands   b ON b.id=ip.brand_id
GROUP BY ip.product_id, ip.brand_id
HAVING suma <= ip.amount_alert;
END $$
DELIMITER ;
SHOW PROCEDURE STATUS    -- wszystkie procedury

SHOW PROCEDURE STATUS [LIKE 'my_procedure' | WHERE search_condition]

SHOW CREATE PROCEDURE my_procedure;
DROP PROCEDURE [IF EXISTS] my_procedure;

SHOW PROCEDURE STATUS WHERE Db='myDababaseName' \G
DELIMITER $
CREATE PROCEDURE user_projects(p_user_id INT)
BEGIN
  SELECT p.id, p.status_id, COUNT(*) AS count,
        SUM(p.price) AS total, SUM(p.price * (ch.percent / 100)) AS weighted
  FROM projects p
   LEFT JOIN chances ch ON p.chance_id=ch.id
  WHERE p.manager_id = p_user_id
   AND p.status_id <> 3                        -- bez odrzuconych
  GROUP BY p.status_id ;
END $
DELIMITER ;

Wywołanie procedury w CakePHP 3

$connection = \Cake\Datasource\ConnectionManager::get('default');

$results = $connection->execute('CALL low_amount()')->fetchAll('assoc');

$this->set(compact('results'));

MySQL – foreign key – lista powiązanych kluczy

W jaki sposób sprawdzić które tabele mają zdefiniowane klucze obce do tabeli users.

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CONSTRAINT_NAME,   -- <<-- Nazwa potrzebna do skasowania 
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'users'\G
+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+      
| articles   | user_id     | user_key        | users                 | id                     |
| bookmarks  | user_id     | ub_key          | users                 | id                     |
+------------+-------------+-----------------+-----------------------+------------------------+

Druga wersja

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
  WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL; 
+--------------+---------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME | CONSTRAINT_NAME |
+--------------+---------------+-------------+-----------------+
| intranet     | articles      | user_id     | user_key        |
| intranet     | articles_tags | article_id  | article_key     |
| intranet     | articles_tags | tag_id      | tag_key         |
| intranet     | bookmarks     | user_id     | ub_key          |
+--------------+---------------+-------------+-----------------+

Nodejs – Yarn package manager

instalacja

# curl -sL https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add -
# echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list
# apt-get update && apt-get install yarn
# yarn
yarn install v1.19.1
info No lockfile found.
[1/4] Resolving packages...
[2/4] Fetching packages...
[3/4] Linking dependencies...
[4/4] Building fresh packages...
success Saved lockfile.
Done in 0.10s.

tinymce – pełne URL obrazków

Obrazki wstawiane są z względnymi ścieżkami do dokumentów. Jest to problem podczas generowania  PDF który pobierze obrazek tylko jeśli src zawiera pełny adres URL. W takim przypadku należy dodać linie:

relative_urls : false,
remove_script_host: false,
convert_urls : true,

pierwsza linia powinna wystarczyć.

To może być problemem w przypadku zmiany domeny (przenoszenia serwisu) – obrazki wciąż będą wskazywały na stary URL.