Tuesday, 26 March 2019

Resolve an issue to insert explicit value for identity column in SQL table asking for IDENTITY_INSERT to set ON/OFF in PHP (Laravel)

The following methods to control the `IDENTITY_INSERT` to ON/OFF for SQL table seeding process in PHP (Laravel) :
    
    function setIdentityInsert($table, $onStatus = true)
    {
        $status = $onStatus ? 'ON' : 'OFF';

        $this->sqlConnection->unprepared("SET IDENTITY_INSERT $table $status");
    }

    function insertTableData($table, $data)
    {
        return $this->sqlConnection->table($table)->insert($data);
    }

    function seedTable($table, $hasAutoIncrementPrimaryKey = false, $data = [])
    {
        if ($hasAutoIncrementPrimaryKey) {
            $this->setIdentityInsert($table);
            $response = $this->insertTableData($table, $data);
            $this->setIdentityInsert($table, false);

            return $response;
        }
        else {
            return $this->insertTableData($table, $data);
        }
    }

Note: Generally, the table requires to have an auto-increment primary key to set Identity Insert to `ON`, that's why I have the `$hasAutoIncrementPrimaryKey` flag. Otherwise, seeding may throw an error as:
    
    SQLSTATE[HY000]: General error: 544 Cannot insert explicit value for
    identity column in table 'test_table_name' when IDENTITY_INSERT is set to
    OFF. [544] (severity 16) [(null)]
Hope this helps!

Thursday, 21 March 2019

Get a list of columns and their data type by passing table name on MySQL and MSSQL databases.

To get a list of columns and their data type by passing table name on MySQL and MSSQL databases.

    
    /**
     * Returns the list of columns and their data type by the table name on MySQL database.
     * @param $sqlConnection MySQL Database Connection Link
     * @param $tableName
     * @param bool $fullType
     * @return array
     */
    private function getMySqlTableColumnAndTypeList($mysqlConnection, $tableName, $fullType = false)
    {
        $table = $mysqlConnection->select("DESCRIBE $tableName");

        $fieldAndTypeList = [];
        foreach ($table as $field){
            $type = ($fullType || !str_contains($field->Type, '('))? $field->Type: substr($field->Type, 0, strpos($field->Type, '('));
            $fieldAndTypeList[$field->Field] = $type;
        }
        return $fieldAndTypeList;
    }

    /**
     * Returns the list of columns and their data type and nullable by the table name on MSSQL database.
     * @param $sqlConnection SQL Database Connection Link
     * @param $tableName
     * @return array
     */
    private function getSqlTableColumnsWithDataTypes($sqlConnection, $tableName)
    {
        $tableInfo = $sqlConnection->select("SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '". $tableName . "'");

        $fieldAndTypeList = [];
        foreach ($tableInfo as $field){
            $isNullable = ($field->IS_NULLABLE == 'YES') ? true : false;
            $fieldAndTypeList[$field->COLUMN_NAME] = (object)['type' => $field->DATA_TYPE, 'nullable' => $field->IS_NULLABLE, 'isNullable' => $isNullable];
        }

        return $fieldAndTypeList;
    }

Raw Query in MSSQL:
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name';

Raw Query in MySQL:
DESCRIBE your_table_name;


Appreciated for any feedback!

Monday, 25 February 2019

Password protect to directory on the web server and Add a new user

Allow User to access the app 1. Create/Update `.htaccess` file in root directory:
    touch .htaccess
    vi .htaccess
Then, add following content:
    AuthUserFile /home/bitnami/htdocs/.htpasswd
    AuthGroupFile /dev/null
    AuthName "Application Authentication"
    AuthType Basic
    require user demo-user1
    require user test-user2
3. And, for passwords, create `.htpasswd` file in root directory.
    touch .htpasswd.
4. To add/edit password, run the following command:
    htpasswd -c .htpasswd demo-user1
    
And, follow instruction ahead.

Thursday, 21 February 2019

Laravel - PHPUnit - run single test class or method

The following command runs the test on a single method: (e.g. we are testing the `testCreateUser()` method here)
vendor/bin/phpunit --filter testCreateUser UserControllerTest tests/feature/UserControllerTest.php
vendor/bin/phpunit --filter methodName ClassName path/to/file.php
Further, lets say you want to test a ClassName which exists into two location:
tests/unit/UserControllerTest.php // contains 3 tests
tests/feature/UserControllerTest.php // contains 2 tests
In this case, you simply run below command which will check for all the test on both location.
vendor/bin/phpunit --filter UserControllerTest

-----Output---------
Time: 2.56 seconds, Memory: 30.00MB

OK (5 tests, 22 assertions)
Please note, if you have phpunit available globally on your machine, you can simply run
phpunit --filter 

# instead of 
vendor/bin/phpunit --filter
Stackflow resource.

Thursday, 31 January 2019

Rename all existing files on AWS S3 Bucket using Laravel Storage library

Follow below steps to rename existing files on a selected directory on S3 Bucket.

1. Lets say your config/filesystems.php looks like this:
'disks' => [
  's3_test_bucket' => [
        'driver' => 's3',
        'key'    => env('AWS_KEY', 'your_aws_key_here'),
        'secret' => env('AWS_SECRET','your_aws_secret_here'),
        'region' =>  env('AWS_REGION', 'your_aws_region_here'),
        'version' => 'latest',
        'bucket'  => 'my-test-bucket',
  ],
];

2. Let's say, you have my-test-bucket on your AWS S3.

3. Lets say you have following files inside the my-test-bucket/test-directory directory.
i.e.
- test-files-1.csv
- test-files-2.csv
- test-files-3.csv

3. Call below function to rename existing files on a selected directory on S3 Bucket.
$directoryPath = 'test-directory';
$storage = new MyStorageRepository();
$storage->renameAnyExistingFilesOnImportDirectory('my-test-bucket', 'test-directory');

4. Output: files should be rename as below on my-test-bucket/test-directory directory:
- test-files-1--1548870936.csv
- test-files-2--1548870936.csv
- test-files-3--1548870936.csv

5. Include the below library class or methods on your class and you should be good.

use Illuminate\Support\Facades\App;
use Illuminate\Support\Facades\Storage;

class MyStorageRepository
{
    public function renameAnyExistingFilesOnImportDirectory($bucket, $directoryPath)
    {
        $directoryPath = App::environment() . '/' . $directoryPath;
        $storage = Storage::disk('s3_test_bucket');

        $suffix = '--' . time(); // File suffix to rename.

        if ($storage->exists($directoryPath)) {
            $this->renameStorageDirectoryFiles($directoryPath, $storage, $suffix);
        }
    }

    private function getNewFilename($filename, $suffix = null)
    {
        $file = (object) pathinfo($filename);

        if (!$suffix) {
            $suffix = '--' . time();
        }

        return $file->dirname . '/' . $file->filename . $suffix . '.' . $file->extension;
    }

    private function renameStorageDirectoryFiles($directoryPath, $storage = null, $suffix = null, $filesystemDriver = null)
    {
        if (!$storage) {
            $storage = Storage::disk($filesystemDriver);
        }

        // List all the existing files from the directory
        $files = $storage->files($directoryPath);

        if (count($files) < 1 ) return false;

        foreach($files as $file) {
            // Get new filename
            $newFilename = Helpers::getNewFilename($file, $suffix);

            // Renamed the files
            $storage->move($file, $newFilename);
        }
    }
}
  
Also, copy of ref here

Thursday, 24 January 2019

Laravel Method to check if the given record id exists on DB table or not.

public function isModelRecordExist($model, $recordId)
{
    if (!$recordId) return false;

    $count = $model->where(['id' => $recordId])->count();

    return $count ? true : false;
}

// To Test
$recordId = 5;
$status = $this->isModelRecordExist( (new MyTestModel()), $recordId);

// Outcome: true | false

Hope it helps!