Yii2 and Postgresql

This is a quick note about my favorite PHP framework (Yii2) which I used recently.

Postgresql File Upload

  1.  // upload handling
  2.  // retrieve upload file if is a post
  3.  if($model->load(...))
  4.  {
  5.    $uploadFile = UploadedFile::getInstance($model, 'some_file');
  6.  }
  7.   
  8.  if(validateSuccess)
  9.  {
  10.    // retrieve tmp file content and convert to hex to store into bytea
  11.    $model->byteaColumn = bin2hex( file_get_contents($uploadFile->tempName) ); 
  12.    ...
  13.    $model->save();
  14.  }

Postgresql File Download Script

  1.  $response = \Yii::$app->response;
  2.  $response->format = $response::FORMAT_RAW;
  3.  $response->setDownloadHeaders($attachmentFileName, 'application/octet-stream' , false, $file->size);
  4.  // must convert back
  5.  $response->content = hex2bin(stream_get_contents($file-> byteaColumn) );
  6.   
  7.  $response->send();

UUID Extension for Postgresql

# in psql, need to enable extension for schema 
$ CREATE EXTENSION "uuid-ossp";

// thereafter, can set a column PK default to uuid_generate_v4() to generate UUID

Postgresql Trigger

CREATE OR REPLACE FUNCTION "public"."fn_next_id"()
RETURNS Trigger
 AS $function$
 DECLARE BEGIN
  IF NEW.id IS NULL OR NEW.id = ''
  THEN
   NEW.id := 'prefix' || '-' || to_char(now(), 'YYYYMMDD' ) || '-' || lpad(nextval('someseq')::text , 6, '0');
  END IF;
  RETURN NEW;
 END;   $function$
LANGUAGE plpgsql;
comments powered by Disqus