Yii2 AR SQL Verbs; Y-U-SO-WEIRD?

Yii2 being an active record style DB abstraction AND needing to support a wide range of database technologies facilitated creating the standard insert / select / update / delete functionality inside the Active Record model layer. While most of implementation makes sense some of it is not intuitive. Herein is a TL;DR of the 4 major commands and the *All() version if applicable.

 

select / selectAll (in Yii2 this is termed ‘find’)

\Class::find([{array of criteria}])->one(); OR \Class::findOne([{array of criteria}]);

\Class::find([{array of criteria}])->all(); OR \Class::findAll([{array of criteria}]);

 

insert / insertAll (termed ‘save’)

$model = new \Class({column values as array});

$model->save();

No saveAll() implementation by default

 

update / updateAll

$model = \Class::find({column values as array});

$model->setAttribute(s)({string or array of SQL SET keyed values})

$model->save();

 

Class::updateAll([{array of criteria}], {string to Key = Value pairs for SQL SET})

 

delete / deleteAll

\Class::delete({string of criteria})

\Class::deleteAll({string of criteria})

 

Bonus: Yii2 also has the ability to create database commands and bypass the Active Record abstraction altogether: Yii::app()->db->createCommand({string  of SQL command}).

 

Now, lets look at MySQL / Maria / MsSQL / Postgra default immplimentation of the same actions:

select/all

Select {string of keyed values} FROM {string of source} WHERE {string of keyed values}

 

insert/All

Insert Into {string of source} VALUES {string of keyed values}

 

update/All

Update {string of source} Set {string of keyed values}

 

delete/All

Delete From {string of source} Where  {string of keyed values}

 

See a pattern there? string source, key/value data sets. And minus Select all start with the data source followed by the command, the the key/value criteria.  And even select makes sense when you treat the field names as a string substitution for `select {needle(s)} from {source|`.

 

It is not that AR is bad, nor that Yii2 had a lack of effort. Trying to support the feature set of multiple database technologies, active record, createQuery, best practices, security, and community requests is daunting, hands down. Maybe I’ll write a package to normalize the base 4 verbs for MySQl/MariaDB…

 

 

Shameless Self Promotion: Presenting during Tampa Bay PHP’s May meetup!

Using Codeception for Acceptance testing

Tuesday, May 30, 2017, 6:30 PM

Sourcetoad’s new location
2901 W Busch Blvd #1018 Tampa, FL

9 Members Went

David Eddy will present “Using Codeception for Acceptance testing: the crash course.”

Check out this Meetup →

 

Update: Video is up on youtube at https://youtu.be/QXSP0bEpF4Y .

Refactoring array index magic number with class constant.

During a code review my peer Michal Mazur turned me onto the follow example; I have to say I am really digging it: Array Index as constant .

In my specific case here is the usage:

if (!empty($paramData[19]) && is_string($paramData[19])) {
    $paramData = $paramData[19];
} elseif (empty($paramData[19]) && !empty(isset($paramData[17])) && isset($paramData[17])) {
    $paramData = $paramData[17];
}

…and the refactored logic:

if (!empty($rowData[$this::FULL_SOURCE]) && is_string($rowData[$this::FULL_SOURCE])) {
    $imageSource = $rowData[$this::FULL_SOURCE];
} elseif (empty($rowData[$this::FULL_SOURCE]) && !empty(isset($rowData[$this::SQUARE_SOURCE])) &&
    isset($rowData[$this::SQUARE_SOURCE])
) {
    $imageSource = $rowData[$this::SQUARE_SOURCE];
}

While a bit more verbose the logic is much easier to read using the contextually named constants.

PHP and large CSV’s…

After looking around a bit I have yet to find a way to read a specified line from a file without doing one of the following:

  1. fopen()
  2. looping every line until reaching the desired line

Desired functionality:

  1. fopen($file)
  2. imaginary function call `fgetcsvline($lineNumber)` would returns the contents of line $lineNumber

 

Anyone know a solution for this?

 

Update: Derp, how could I forget about ‘The League’? http://csv.thephpleague.com/

PHPStorm 2017.1 released…

I’m sure many of you know this already by JetBrains has released PHPStorm version 2017.1. The improvement list looks nise and Im eager to try ’em out.
Get he full details over at the office release page: https://blog.jetbrains.com/phpstorm/2017/03/phpstorm-2017-1-is-now-released/

Of special interest is the Codecetpion and PHPUnit 6 as I am a big personal supporter of automated testing process.

Quick little thing.

Was sad to see Yii2’s getOldAttributes() did not have to ability to limit based on a provided array; whereas getAttributes() does take an array to limit the returned attributes. So I whipped this up right quick:

/**
 * @param array $array
 *
 * @return array
 */
public function getOldAttributes($array = [])
{
    $returnData = parent::getOldAttributes();

    if (!empty($array)) {
        $returnData = array_intersect($array, $returnData);
    }

    return $returnData;
}

Simple little thing but super handy.

emoji as variable names…

…during a short discussion today with a peer we found this little tool: https://mothereff.in/js-variables . I was all like AHA! I have an idea! Sadly though it turns out `(╯°□°)╯︵ ┻━┻` is not of the Unicode 8 character set.
:sad-face:. Then we got on the it would cool conversation of having emoji as variable names.

Imagine opening PHPStorm, etc and see in birthday cake var names! So I mocked something up real quick to pass around the office, enjoy!

PHP str_word_count’s and validating base64 strings…

Recently I had to validate a string that is was indeed a base64 string and it was indeed an encoded allowable image type. Between str_word_count’s second parameter (returning the string as an array based on the words) and a quick Stack Overflow search this is neat little piece of logic IMO:

/**
 * Validate a base64 string is indeed of an allowable MIME type
 * @author David J Eddy <me@davidjeddy.com>>
 * @version 0.8.0
 * @param $paramData
 *
 * @return bool
 */
public function validate($paramData)
{
    $returnData = true;
    // explode the string into an array ignoring everything but words
    $wordArray = str_word_count(substr($paramData, 0 , 23), 1);

    // is the string a valid base64 object?
    // @source http://stackoverflow.com/questions/4278106/how-to-check-if-a-string-is-base64-valid-in-php
    if (base64_encode(base64_decode($paramData, true)) !== $paramData) {
        $returnData = false;
    }

    // does the string contain 'data', 'image', 'base'?
    if (
        in_array('data', $wordArray) === false ||
        in_array('image', $wordArray) === false ||
        in_array('base', $wordArray) === false ||
        $returnData === false
    ) {
        $returnData = false;
    }

    // does the string contain an allowed MIME type?
    if (in_array('data', $this->allowableMimeType) === false || $returnData === false) {
        $returnData = false;
    }

    return $returnData;
}

What do you think? Have an often forgotten php function or parameter that helps you greatly?