Documentation

CRM_Utils_SQL_TempTable
in package

Tags
copyright

CiviCRM LLC https://civicrm.org/licensing Table naming rules:

  • MySQL imposes a 64 char limit.
  • All temp tables start with "civicrm_tmp".
  • Durable temp tables: "civicrm_tmp_d_{12}_{32}"
  • Ephemeral temp tables: "civicrm_tmp_e_{12}_{32}"

To use TempTable:

  • Begin by calling CRM_Utils_SQL_TempTable::build().
  • Optionally, describe the table with setDurable(), setCategory(), setId().
  • Finally, call getName() or createWithQuery() or createWithColumns().

Example 1: Just create a table name. You'll be responsible for CREATE/DROP actions.

$name = CRM_Utils_SQL_TempTable::build()->getName(); $name = CRM_Utils_SQL_TempTable::build()->setDurable()->getName(); $name = CRM_Utils_SQL_TempTable::build()->setCategory('contactstats')->setId($contact['id'])->getName();

Example 2: Create a temp table using the results of a SELECT query.

$tmpTbl = CRM_Utils_SQL_TempTable::build()->createWithQuery('SELECT id, display_name FROM civicrm_contact'); $tmpTbl = CRM_Utils_SQL_TempTable::build()->createWithQuery(CRM_Utils_SQL_Select::from('civicrm_contact')->select('display_name'));

Example 3: Create an empty temp table with list of columns.

$tmpTbl = CRM_Utils_SQL_TempTable::build()->setDurable()->createWithColumns('id int(10, name varchar(64)');

Example 4: Drop a table that you previously created.

$tmpTbl->drop();

Example 5: Auto-drop a temp table when $tmpTbl falls out of scope

$tmpTbl->setAutodrop();

Table of Contents

Constants

CATEGORY_LENGTH  = 12
CATEGORY_REGEXP  = ';^[a-zA-Z0-9]+$;'
ID_LENGTH  = 37
ID_REGEXP  = ';^[a-zA-Z0-9_]+$;'
INNODB  = 'ENGINE=InnoDB'
MEMORY  = 'ENGINE=MEMORY'
UTF8  = 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci'

Properties

$autodrop  : mixed
$category  : mixed
$createSql  : mixed
$durable  : bool
$id  : mixed
$memory  : mixed
$utf8  : bool

Methods

__destruct()  : mixed
build()  : CRM_Utils_SQL_TempTable
createWithColumns()  : CRM_Utils_SQL_TempTable
Create the empty table.
createWithQuery()  : CRM_Utils_SQL_TempTable
Create the table using results from a SELECT query.
drop()  : CRM_Utils_SQL_TempTable
Drop the table.
getCategory()  : string|null
getCreateSql()  : string|null
getId()  : string|null
getName()  : string
Determine the full table name.
getUtf8String()  : string
Get the utf8 string for the table.
isAutodrop()  : bool
isDurable()  : bool
isMemory()  : bool
isUtf8()  : bool
setAutodrop()  : CRM_Utils_SQL_TempTable
setCategory()  : CRM_Utils_SQL_TempTable
setDurable()  : CRM_Utils_SQL_TempTable
Set whether the table should be durable.
setId()  : CRM_Utils_SQL_TempTable
Setter for id
setMemory()  : $this
Set table engine to MEMORY.
setUtf8()  : $this
Set table collation to UTF8.
toSQL()  : string

Constants

CATEGORY_LENGTH

public mixed CATEGORY_LENGTH = 12

CATEGORY_REGEXP

public mixed CATEGORY_REGEXP = ';^[a-zA-Z0-9]+$;'

ID_REGEXP

public mixed ID_REGEXP = ';^[a-zA-Z0-9_]+$;'

INNODB

public mixed INNODB = 'ENGINE=InnoDB'

MEMORY

public mixed MEMORY = 'ENGINE=MEMORY'

UTF8

The system will attempt to use the same as your other tables, and if you really need something else then use createWithColumns and specify it per-column there.

public mixed UTF8 = 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci'

Properties

Methods

getCategory()

public getCategory() : string|null
Return values
string|null

getCreateSql()

public getCreateSql() : string|null
Return values
string|null

getId()

public getId() : string|null
Return values
string|null

getName()

Determine the full table name.

public getName() : string
Return values
string —

Ex: 'civicrm_tmp_d_foo_abcd1234abcd1234'

getUtf8String()

Get the utf8 string for the table.

public getUtf8String() : string

Our tables are either utf8_unicode_ci OR utf8mb4_unicode_ci - check the contact table to see which & use the matching one. Or early adopters may have switched switched to other collations e.g. utf8mb4_0900_ai_ci (the default in mysql 8).

Return values
string

isAutodrop()

public isAutodrop() : bool
Return values
bool

isDurable()

public isDurable() : bool
Return values
bool

isMemory()

public isMemory() : bool
Return values
bool

isUtf8()

public isUtf8() : bool
Return values
bool

setMemory()

Set table engine to MEMORY.

public setMemory([bool $value = TRUE ]) : $this
Parameters
$value : bool = TRUE
Return values
$this

setUtf8()

Set table collation to UTF8.

public setUtf8([bool $value = TRUE ]) : $this

This method is deprecated as tables should be assumed to have UTF-8 as the default character set and collation; some other character set or collation may be specified in the column definition.

Parameters
$value : bool = TRUE
Return values
$this

toSQL()

private toSQL(string $action[, string|null $ifne = NULL ]) : string
Parameters
$action : string

Ex: 'CREATE', 'DROP'

$ifne : string|null = NULL

Ex: 'IF EXISTS', 'IF NOT EXISTS'.

Return values
string —

Ex: 'CREATE TEMPORARY TABLE civicrm_tmp_e_foo_abcd1234' Ex: 'CREATE TABLE IF NOT EXISTS civicrm_tmp_d_foo_abcd1234'


        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results