/ Gists

Gists

On gists

Novato - ranks

MySql tricks MySql - advanced AW

rank-model.php #


    public function triggerEvent(array $events = [], Nette\Database\Table\ActiveRow $section = null, $debug = false)
    {
        if($debug)
        {
            $logFile = INDEX_DIR . '/trigger-event.log';
            $logData = [];
            foreach ($events as $event)
            {
                $logData[] = sprintf('%s | %s | sectionId: %d', 
                                        date('Y-m-d H:i:s'), 
                                        $event, 
                                        ($section !== null) ? $section->id : $section
                                    ) 
                                    . PHP_EOL;
            }
            
            file_put_contents('nette.safe://' . $logFile, $logData, FILE_APPEND);
        }

        $sectionIndependentEvents = ['onAny'];

        $whereOrParams = $whereOr = [];
        foreach($events as $event)
        {
            $whereOr[] = 'FIND_IN_SET(?, event)';
            $whereOrParams[] = $event;
        }

        $presenters = $this->connection->table('catalogue_presenter_config')
            ->where(implode(' OR ', $whereOr), ...$whereOrParams);
        
        $pagesToInvalidate = [];                                       
        foreach ($presenters as $presenterRow)
        {
            $pages = $this->connection
                ->table('catalogue_page')
                ->where('catalogue_section.catalogue_id', $section->catalogue_id)
                ->where('presenter_id', $presenterRow->presenter_id);

            $invalidateIndependent = FALSE;
            foreach(explode(',', $presenterRow->event) as $event)
                if(in_array($event, $sectionIndependentEvents))
                    $invalidateIndependent = TRUE;

            if(!$invalidateIndependent)
                $page->where('catalogue_section_id', $section->id);
            
            foreach ($pages as $page)
                $pagesToInvalidate[$page->id] = $page;
        }   
        
        foreach ($pagesToInvalidate as $pageId => $page)
            $this->invalidPdf($page);
    }

    public function recountPageNumbersInCatalogue($catalogueId)
    {
        $this->connection->query('
            UPDATE 
            catalogue_page 
            JOIN (
                SELECT
                tmp2.*,
                @totalRank := @totalRank + tmp2.prev as lastPageNumberInCatalogue
                FROM 
                (SELECT @totalRank := 0) as tempTotalRank,
                (
                    SELECT
                    tmp.*,
                    @prev as prev,
                    @prev := tmp.pdf_pages_count
                    
                    FROM
                    (
                        SELECT 
                        @prev := 0,
                        t2.name AS sectionName,
                        t2.rank AS sectionRank,
                        t1.rank AS pageRank,
                        t1.id,
                        t1.pdf_pages_count
                        
                        FROM 
                    
                        catalogue_page t1
                        JOIN catalogue_section t2 ON t2.id = t1.catalogue_section_id
                        WHERE t2.catalogue_id = ?
                        ORDER BY sectionRank ASC, pageRank ASC
                    ) AS tmp
                    
                ) AS tmp2

            ) tmpJoin ON tmpJoin.id = catalogue_page.id
            SET page_number = lastPageNumberInCatalogue + 1
        ', $catalogueId);
    }
On gists

Tipliga - SQL - multiplevariables - ranks

MySql

tipliga.sql #

	INSERT INTO
		_tipliga_poradi
		(
			poradi,
			id_hrace,
			liga,
			rocnik,
			pz,
			v,
			r,
			p,
			nz,
			score,
			pts
		)
		
		SELECT
		rank,
		hrac,
		liga,
		rocnik,
		pz,
		v,
		r,
		p, 
		NETIPOVANO,
		SKORE,
		PTS
		FROM
		
		(
		
			SELECT
			*,
			IF(SKORE = @lastScore AND @lastPts = PTS, @currentRank := @currentRank, @currentRank := @i) as rank,
			@i := @i + 1,
			@lastScore := SKORE,
			@lastPts := PTS
			FROM
			(
			
			
			SELECT
							hrac, 
							h.prezdivka,
							SUM(PZ) as PZ,
							SUM(V) as V,
							SUM(R) as R,
							SUM(P) as P,
							SUM(NETIPOVANO) as NETIPOVANO,
							SUM(PTS) as SKORE,
							SUM(V)*3 + SUM(R)*1 + SUM(NETIPOVANO)*-1 as PTS,
							liga, 
							rocnik
			
							FROM
							(
										SELECT
										domaci as hrac,
										COUNT(*) as PZ,
										SUM(IF (domaci_body>host_body,1,0)) as V,
										SUM(IF (domaci_body=host_body AND domaci_tipoval = 1 AND host_tipoval = 1, 1,0)) as R,
										SUM(IF (domaci_body<host_body AND domaci_tipoval = 1, 1,0)) as P,
										SUM(IF (domaci_tipoval = 0, 1, 0)) as NETIPOVANO,
										SUM(domaci_body) as PTS,
										liga,
										rocnik
										
										FROM _tipliga_rozpisy
										WHERE 
										liga=%s
										AND rocnik=%s
										AND kolo <= %i
										GROUP BY hrac
								
										UNION ALL
										
										SELECT
										host as hrac,
										COUNT(*) as PZ,
										SUM(IF (domaci_body<host_body,1,0)) as V,
										SUM(IF (domaci_body=host_body AND domaci_tipoval = 1 AND host_tipoval = 1, 1,0)) as R,
										SUM(IF (domaci_body>host_body AND host_tipoval = 1, 1,0)) as P,
										SUM(IF (host_tipoval = 0, 1, 0)) as NETIPOVANO,
										SUM(host_body) as PTS,
										liga,
										rocnik
										
										FROM _tipliga_rozpisy
										WHERE 
										liga=%s
										AND rocnik=%s
										AND kolo <= %i
										GROUP BY hrac
							) as TMP
							
							JOIN hraci h ON h.id = TMP.hrac
							GROUP BY hrac
							ORDER BY PTS DESC, SKORE DESC, NETIPOVANO ASC, V DESC, h.prezdivka ASC
							
				) tmp,
				(SELECT @currentRank := 1, @i := 1, @lastPts:= -999, @lastScore := -999) as config
			) finalTable
On gists

sass mixin: Asset Helper Function

SCSS

mixin-asset-helper.scss #

/// Base path for assets (fonts, images...),
/// should not include trailing slash
/// @access public
/// @type String
$asset-base-path: '../assets' !default;

/// Asset URL builder
/// @access private
/// @param {String} $type - Asset type, matching folder name
/// @param {String} $file - Asset file name, including extension
/// @return {URL} - A `url()` function leading to the asset
@function asset($type, $file) {
  @return url($asset-base-path + '/' + $type + '/' + $file);
}

/// Image asset helper
/// @access public
/// @param {String} $file - Asset file name, including extension
/// @return {URL} - A `url()` function leading to the image
/// @require {function} asset
@function image($file) {
  @return asset('images', $file);
}

/// Font asset helper
/// @access public
/// @param {String} $file - Asset file name, including extension
/// @return {URL} - A `url()` function leading to the font
/// @require {function} asset
@function font($file) {
  @return asset('fonts', $file);
}

/// USAGE
/*
@font-face {
    font-family: 'Unicorn Font';
    src: font('unicorn.eot?') format('eot'),
         font('unicorn.otf') format('truetype'),
         font('unicorn.woff') format('woff'),
         font('unicorn.svg#unicorn') format('svg');
    font-weight: normal;
    font-style: normal;
  }
  
  .foo {
    background-image: image('kittens.png');
  }

  */
On gists

Nejvyssi zaznamy v kategoriích

MySql

example.sql #

select * from clanky where (kategorie, datum) in (select kategorie, max(datum) from clanky group by kategorie)





SELECT 
t1.id, 
t1.nadpis,
t2.datum,
t1.datum,
t2.kategorie
FROM clanky t1
JOIN  ( SELECT t2.kategorie, MAX(t2.datum) datum FROM clanky t2 GROUP BY t2.kategorie) t2
ON t1.kategorie = t2.kategorie
AND t1.datum = t2.datum







select * from clanky t1
where t1.id in (select max(t2.id) id from clanky t2 group by t2.kategorie)
On gists

SQL select only rows with max value on a column (2 ways) - universal

MySql MySql tricks MySql - advanced

solution.sql #

-- @link: https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1

-- Joining with simple group-identifier, max-value-in-group Sub-query
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev


-- Left Joining with self, tweaking join conditions and filters
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;
On gists

Dynamic SQL via prepared statements

•
MySql MySql tricks MySql - advanced

example.sql #

-- 1
SET @table = "pexeso";
SET @sql:= CONCAT('SELECT * FROM',' ', @table);
PREPARE stmt FROM @sql;
EXECUTE stmt;
-- DEALLOCATE PREPARE stmt;  # neni povinne


-- 1b, lze dosadit parametry pres ?
SET @id = 1;
PREPARE stmt FROM 'SELECT * FROM category WHERE id = ?';
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt; 




-- 2, Dynamically Building SQL based on Parameters
CREATE DEFINER=`root`@`localhost` 
  PROCEDURE `GetRecentActivity`(input VARCHAR(15))
BEGIN
SET @input = input;

if @input="asc" then
    SET @sort = " order by activity_log_key asc";
elseif @input = "desc" then
    SET @sort = " order by activity_log_key desc";
else
    SET @sort ="";
end if;

SET @query = CONCAT('select * from activitylog ',@sort,' limit 0, 5');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END


-- 3, more than one column
SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.`COLUMNS` C 
WHERE table_name = 'MyTb' AND COLUMN_NAME LIKE '%whatever%' INTO @COLUMNS;

SET @table = 'MyTb';
SET @s = CONCAT('SELECT ',@columns,' FROM ', @table);

PREPARE stmt FROM @s;
EXECUTE stmt;
On gists

Triggers

•
MySql MySql tricks MySql - advanced

clanky.sql #

CREATE TRIGGER diskuse_ai AFTER INSERT ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet + 1 WHERE id = NEW.clanek;
CREATE TRIGGER diskuse_ad AFTER DELETE ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet - 1 WHERE id = OLD.clanek;
CREATE TRIGGER diskuse_au AFTER UPDATE ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet + IF(id = NEW.clanek, 1, -1) WHERE id = OLD.clanek XOR id = NEW.clanek;
On gists

Recursive SCSS mixins and functions

SCSS

example.scss #

// 1)

@mixin test($val) {
  width: $val;
  height: $val;
  @if $val > 0 {
    @include test($val - 1);
  }
}

.x
{
  @include test(5);
}

/*

.x {
  width: 5;
  height: 5;
  width: 4;
  height: 4;
  width: 3;
  height: 3;
  width: 2;
  height: 2;
  width: 1;
  height: 1;
  width: 0;
  height: 0;
}

*/


// 2)
// @example: https://codepen.io/_lacus/pen/xwZvvx

@mixin opacityStair($fade: 0.9, $step: 0.1) {
	// Recursive sass example
    
	& + * {
		opacity: $fade;
		@if $fade <= $step {
		    // exit
		} @else {
			@include opacityStair($fade - $step, $step)
		}
	}
}

div {
	@include opacityStair();
}


div div {
  width: 100%;
  height: 33px;
  background-color: green;
}

/*
  
  div + * {
  opacity: 0.9;
}

div + * + * {
  opacity: 0.8;
}

div + * + * + * {
  opacity: 0.7;
}

div + * + * + * + * {
  opacity: 0.6;
}

div + * + * + * + * + * {
  opacity: 0.5;
}

div + * + * + * + * + * + * {
  opacity: 0.4;
}

div + * + * + * + * + * + * + * {
  opacity: 0.3;
}

div + * + * + * + * + * + * + * + * {
  opacity: 0.2;
}

div + * + * + * + * + * + * + * + * + * {
  opacity: 0.1;
}

div div {
  width: 100%;
  height: 33px;
  background-color: green;
}

  
*/


// 3) 
// @link: https://codepen.io/mirisuzanne/pen/bBdEXm

// define your colors
$colors: (
  'pink': #E2127A,
  'brand-primary': 'pink',
  'site-background': 'brand-primary',
);

// color function
@function color($color) {
  // our conditional statement
  @if map-get($colors, $color) {
    // follow the path one step
    $new-color: map-get($colors, $color);

    // recursion!
    $color: color($new-color);
  }
  
  @return $color;
}

// use your new function!
body {
  background: color('x');
}

// other styles...
body {  
  font-size: 8vmin;
  font-family: monospace;
  text-align: center;
  padding: 40vmin 2em;

  &::after {
    content: "‘site-background’ == #{color('site-background')}";
  }
}

/*


@charset "UTF-8";
body {
  background: "x";
}

body {
  font-size: 8vmin;
  font-family: monospace;
  text-align: center;
  padding: 40vmin 2em;
}

body::after {
  content: "‘site-background’ == #E2127A";
}
*/
On gists

Ref hell (yml docker container)

•
AW

DefaultSettings.php #

<?php

namespace Andweb\Yml;
use Nette;

class DefaultSettings
{
	use Nette\SmartObject;

	protected $arr;

	public function getDefaultSettings()
	{
		
		$this->arr['version'] = '3.6';
		$this->arr['ports']['mode'] = 'host';


		$this->arr['labels']['name'] = 'aw-awesome-hosting';
		$this->arr['labels']['value'] = 'yes';


		$this->arr['deploy']['replicas'] = 1;
		$this->arr['deploy']['restart_policy']['delay'] = "10s";
		$this->arr['deploy']['restart_policy']['window'] = "30s";
		$this->arr['deploy']['update_config'] = [
			'parallelism' => 1,
			'delay' => '30s',
			'order' => 'stop-first',
		];

		
		$this->arr['deploy']['resources']['reservations'] = [
			'cpus' => '0.25',
			'memory' => '1G',
		];

		$this->arr['networks']['external'] = "true"; 

		return $this->arr;
	}


}
On gists

Neon - konfigurace přes službu a metody

Nette Nette-Neon Nette-Tricks

config.neon #

services:
    - Config
reCaptcha:
    siteKey: @Config::getSiteKey()
    secretKey: @Config::getSecretKey()
    methodName: 'addReCaptcha' # optional