12. 数据库Schema及创建数据表

龶龵龴龳龲龯龭龫龨龦龤 Schema 龕龔龑龏龎龴龳龲龊龴龳龉龊龆龅龂齿齻齷齵齱齯齭齩齦齦齥齤齣齡齝齚龫龨齖齒齐齍齌齋齇龴齆齅龔龑齄齃齀鼿齱龴龳龉

鼻鼹齱 Schema 鼶鼵鼲鼱鼮鼬 https://www.drupal.org/node/146843

鼻鼹 API 鼶鼵鼬 https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database…

黢黡齭 \core\lib\Drupal\Core\Database\database.api.php 鼶黛黙黖黓黏黍齱黋黊黉

本主题是《云客Drupal8源码分析之数据库系统及其使用》的补充,便于查询,所以独立成一个主题。

数据表定义

黆黃黁齅龔龑龴龳龉齤麾麺麷黁麳麰鼿齐齍麬麫龴龳龉齱麨麧齤麦麣龳黢龔龑齤齭 drupal 黙麡齍麨麧鼮齐齍麞麛龴齆齤齌齋龫麚麖麕麓麐鼬

麎麍龵黊麌齍麈齱麄麁鼬

'description': 齐齍鹻鹷鹳龆鹱鹭齤齀鹬鹨鹦龉齱鹢齱 . 鹡齀鼿鹞鹛龉鹚齤鹞鹛龉鹙齀 {} 鹒鹐齤龫 " 鹉齯鹷鹈龴龳鹇龉 {node}."

'fields': 齐齍麬鹃龴齆 (' 龆龅鹙 ' => 鸹鸷鹨鹦 ) 齷鹨鹦鸴龉齱鸱齤鸹鸷鹨鹦黖鼮齐齍龴齆 . 齣麎鸹鸷鸭龴鸬麨麧 :

'description': 齐齍鹻鹷鹳龆鹱鹭齤齀鹬鹨鹦龆龅齱鹢齱 . 鹡齀鼿鹞鹛龉鹚齤鹞鹛龉鹙齀 {} 鹒鹐齤龫 " 鹉齯鹷鹈龴龳鹇龉 {node}."

'type': 鸪齀龴龳鸨鸧 : 'char', 'varchar', 'text', 'blob', 'int', 'float', 'numeric', or 'serial'. 鸝鸙龴鸨鸧鸗鸓鼿鸐鸏龴龳龲鹡鸌鸈麨齱鸨鸧 . 'serial' 鸇龉齄鸆龆龅 . 鸃麫 MySQL 鹬鷹鼮 'INT auto_increment'. 鷰鷬鸨鸧 'varchar_ascii' 鷫齀鹬鷪鷧龆龅鷤鷢鹙鹇 US ASCII 龆鹱鷚 .

'mysql_type', 'pgsql_type', 'sqlite_type' 齦齦 : 龫鷒鷐黁齱鸨鸧齭鷍麍鷊黓齤鷐鷫齣鹇鷰鷬龴龳龲鸈麨鸨鸧齤麡鹚鷫齣鷆鷄鷍麍齱鸨鸧鸭龴齤鷀麡鶿鷫麣黓鶽鶼齤齖鸪鷫齣鶻齀 "text" 鹬鸇鶸

'serialize': 鶳鶯鶬齤鸈麨龆龅鼮鶩鸏鶧齯鹉黃鸱齇龴龳

'size': 龴龳鸝鶣 : 'tiny', 'small', 'medium', 'normal', 'big'. 麡鼮齐齍鷫鹉齯鶢鸝鶬齱鶞鶜齤鸈鶙鹉齯鹡鸌鶻齀龨鶖鸨鸧 ( 鶔龫齭 MySQL 黙黓 TINYINT INT BIGINT). 鶋鷆鶬鼮 'normal'( MySQL 黙鼮 INT, VARCHAR, BLOB,). 鸃麫麌鶖龴龳鸨鸧鵽鵻齤鵷鼮鵶黓鸝鶣鵳鷫齀齤鷫麣齱齆鵰鵭 DatabaseSchema::getFieldTypeMap()

'not null': 鶳鶯鶬齤 true 龉鶜 no NULL . 鵩鵨鹇 false.

'default': 龆龅齱鵩鵨鶬齤黋麄龴龳鸨鸧齤齭 php '', '0', 0 鼮鵷鵢齱 . 'int' 龆龅鸈麨 '0' 鵞鵷鵚龤龕黋麄鹡鵖齥齤鵒鹇 '0' 鼮龆鹱鹭齤鵷鼮鵏龴 0

'length': 鸨鸧 'char', 'varchar' or 'text' 龆龅齱鶢鸝鵋鵈 . 鸃麫鹞鹛龆龅鸨鸧鵞鵇鷄黢鶬

'unsigned': 鶳鶯鶬齤鸈鵅 'int', 'float' and 'numeric' 鸨鸧黓鵁鹱鵖龕鴿鴾齥 . 鵩鵨鹇 FALSE. 鸃麫鹞鹛龆龅鸨鸧鵞鵇鷄黢鶬

'precision', 'scale': 鸃麫 'numeric' 龆龅鵽鵻鼮鴺鴹齱齤 precision 鸈鵅鴷鵈 ( 黓鴴龴龆齱鴲龴 ) scale ( 鶣龴鴰鴰龴 ). 鹞鹛鸨鸧鵞鵇鷄

'binary': 鶳鶯鶬齤鸈鵅 MySQL 鸏鶧鴭鷧 'char', 'varchar' or 'text' 龆龅鸨鸧鶻齀鸝鶣鴪鴨鴦齱鴣鴠鷧鴟鸃 . 鸃麫鵩鵨鴝鼮鸝鴨鴦齱鸨鸧齤齷鷊黓鴛鴘

龆龅麨麧黙鴕 'type' 齣黡齱鴑鴐鸭龴鼮鷫鴎齱齤鷀黓鴌鴋黡鼬 'numeric' 鴺鴹鸈麨 'precision' 'scale', 'varchar' 鴺鴹鸈麨 'length'

'primary key': 齋鴁鳾麈齱鸙齍鸱龴齆齤齐齍龉黙鳺麣黓齐齍鳾麈齤鷀齐齍鳾麈鷫齣鹒鳷鸙齍鸱齤鳶鹇鹃鵰鳾麈黉

'unique keys': unique 麈齱麬鹃龴齆 ('keyname' => specification). specification 鼮齐齍齋鴁 unique 齱鸱龴齆

'foreign keys': 齐齍麬鹃龴齆 ('my_relation' => specification). 鳵齍麨麧鼮齐齍鹒鳷鸬鹡齀龉鹙齡鸱鸗鸓齱龴齆齤鸱鸗鸓鹇 (' 鳴鸱 ' => ' 鹡齀鸱 ') 齤黡麈鵷齭龴龳龲黙龔龑齤 drupal 鵷鴭鷧麡齍

'indexes': 鳳鹡齱麬鹃龴齆 ('indexname' => specification).

$schema = array(
  'description' => 'The base table for nodes.',
  'fields' => array(
    'nid'       => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
    'vid'       => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE,'default' => 0),
    'type'      => array('type' => 'varchar','length' => 32,'not null' => TRUE, 'default' => ''),
    'language'  => array('type' => 'varchar','length' => 12,'not null' => TRUE,'default' => ''),
    'title'     => array('type' => 'varchar','length' => 255,'not null' => TRUE, 'default' => ''),
    'uid'       => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'status'    => array('type' => 'int', 'not null' => TRUE, 'default' => 1),
    'created'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'changed'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'comment'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'promote'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'moderate'  => array('type' => 'int', 'not null' => TRUE,'default' => 0),
    'sticky'    => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'translate' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
  ),
  'indexes' => array(
    'node_changed'        => array('changed'),
    'node_created'        => array('created'),
    'node_moderate'       => array('moderate'),
    'node_frontpage'      => array('promote', 'status', 'sticky', 'created'),
    'node_status_type'    => array('status', 'type', 'nid'),
    'node_title_type'     => array('title', array('type', 4)),
    'node_type'           => array(array('type', 4)),
    'uid'                 => array('uid'),
    'translate'           => array('translate'),
  ),
  'unique keys' => array(
    'vid' => array('vid'),
  ),
  // For documentation purposes only; foreign keys are not created in the
  // database.
  'foreign keys' => array(
    'node_revision' => array(
      'table' => 'node_field_revision',
      'columns' => array('vid' => 'vid'),
     ),
    'node_author' => array(
      'table' => 'users',
      'columns' => array('uid' => 'uid'),
     ),
   ),
  'primary key' => array('nid'),
);

鷍麍齱麨麧黓鴌鳱黡齱鷹鵅鼬

primary key 鼬鳾麈齤齐齍龉黙鳺麣黓齐齍鳾麈鳭鳩齤鷐鷫齣鳧鹙麡齍鳾麈鳭鳩齤鳥鸈麨齷鹒鳷黓鳤鴌鸱齤鶔龫齐齍鳣鳡龉齤鷫齣鸈麨鳾麈鳭鳩鹒鳷鳟鳛鴉鳣鵖齤麾麺齭鳙黙鳟鳛鷫齣鸐鵢齤鳣鵖黖鷫齣鸐鵢齤鷀鳟鳛鴉鳣鵖鵷麣鵢鹚鸐鵢齤麡鴝鼮鹃鵰鳾麈齱龤齀黉

unique keys 鼬鳘齐齩齤鸃龆龅龴龳鴠鳙鳘齐齩鳭鳩齤鷫齣鵢鹚鸈麨鸙齍龆龅齤鸬鸈麨齱麡鴌龆龅齤齷齵麌齄齱龴龳鵷鷫齣鳔鳐齤麌鳙龴龳齭麡齍龆龅鴺鴹鼮鳘齐齱黉

foreign keys 鼬黡麈齤麡鸈鵅鳏龆龅鹡齀鼿鹞鹛龉齱鳋齍龆龅齤齭鳏龆龅鵶鹉齯齱鶬鴺鴹鼮鸬鹡齀鼿齱麾齍龆龅鹉齯齱鶬鳈齐齤龫鷒鳄鳂鶬鹚齤鵷齭鸬鹡齀龆龅麕麓黙齤麾麺鵞鳁鳡鳀鲼齤鶔龫鲹鷪龉黁鹉齯齀鲶 ID 齤麾麺麡齍龆龅鴝鸏鶧鸈麨黡麈鼿齀鲶龉齱 id 龆龅黉

indexes 鼬鳳鹡齤鳾黁齀麫鲴鲰龴龳鲮鲬齤鲫鲩齩麣齤鹒鳷鸙齍鸱齱鳳鹡鲦鲤鳐鵰鳳鹡鲠鲟鸙鸱鳳鹡齤鲞黛鲛鲗鲓齡鼿鳳鹡鸈麨齱鸱鹚齤齭龴龳龲麕鴐麡鴌鳳鹡鸬齀鼿齤鲒龉鲐鸝鹚麣鸝鸝鲫鲩鲏鳳鲰鵈齤龉鲋鶣鲇鳳鹡鲆鲄鵷鲂鲁鱾齤鳐鵰鳳鹡鸈麨鸱鹚齤鱺黃鵷鼮鱹麄齱齤麬麫麡鼹麍鱶鱲鱮鱫鼬 http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

齭鷍麍齱麨麧黙鼬鳳鹡鱠黓齍麨麧鼮 'node_type' => array(array('type', 4)), 鹇鱞麺鵷鼮 'node_type' => array('type',), 鱚鱙麡齍鸭龴 4 鼮鱞麺麄麁鱙麡鼮鸈齭 type 麡齍龆龅黙鹉齯齱龴龳鳺黓鱗 4 龆鱕鸬齀麫鳳鹡麕麓齤鲴鱑鲰鵈齤龫鷒齯鹉鹡鸌鵷鱐鱌麡鶿齱鱋麣齤麾鸭龴 4 鵞鸬鵇鷄齤麡鶿齱齀鱇鵢鶿鱆齀麫鳾麈黉

黓鸴麡鶿齱龉麨麧鴝鷫齣龔龑龉鸴齤鼹鱇龫麎鼬

$con=\Drupal\Core\Database\Database::getConnection($target, $key);

$con->schema()->createTable($table_name, $schema);

$con->schema() 鰿鰼齱鼮齐齍 schema 鸃鰹齤鰵鰲龦龤 schema 齤鰱麍鲫鰭鸴鰪鸙鼹鱇

鳴鰩齭 \core\lib\Drupal\Core\Database\Driver\ 黙齤齷鼮 \core\lib\Drupal\Core\Database\Schema.php 齱鲛鸨齤

鰥鸨鲫鰭齍龴龳龲齖齀鱋麣齤鲛鸨齀齣龵鰤龴龳龲鳈鰢齱鵷鵢龕龴龳龲鼹鵻齥

Schema鸃鰹齱鸪齀鼹鱇龫麎

$con = \Drupal\Core\Database\Database::getConnection($target, $key);
$schema = $con->schema();
$schema->createTable($table_name, $schema); //创建表
$schema->tableExists($table); //判断表是否存在
$schema->renameTable($table, $new_name);//重命名表
$schema->fieldExists($table, $column); //判断字段是否存在
$schema->fieldNames($fields);//从一个column specifiers返回字段名
$schema->changeField($table, $field, $field_new, $spec, $keys_new = array());//修改字段
$schema->addField($table, $field, $spec, $keys_new = array());//添加字段
$schema->dropField($table, $field);//删除字段
$schema->fieldSetDefault($table, $field, $default);//为字段设置默认值
$schema->fieldSetNoDefault($table, $field); //取消默认值
$schema->indexExists($table, $name); //判断是否存在索引
$schema->addIndex($table, $name, $fields, $spec = array());//添加索引
$schema->dropIndex($table, $name);//删除索引
$schema->addPrimaryKey($table, $fields); //给联合主键添加字段
$schema->dropPrimaryKey($table);//删除主键
$schema->addUniqueKey($table, $name, $fields);//添加唯一性约束
$schema->dropUniqueKey($table, $name);//销毁唯一性约束
$schema->prepareComment($comment, $length = null); //预处理注释
$schema->getComment($table, $column = null); //获取字段注释

这些方法的说明请看注释:

\core\lib\Drupal\Core\Database\Schema.php

鸪齀龴龳龲Mysql龴龳龲齱鸨鸧鸗鸓龫麎

$map = array(
        'varchar_ascii:normal' => 'VARCHAR',

        'varchar:normal' => 'VARCHAR',
        'char:normal' => 'CHAR',

        'text:tiny' => 'TINYTEXT',
        'text:small' => 'TINYTEXT',
        'text:medium' => 'MEDIUMTEXT',
        'text:big' => 'LONGTEXT',
        'text:normal' => 'TEXT',

        'serial:tiny' => 'TINYINT',
        'serial:small' => 'SMALLINT',
        'serial:medium' => 'MEDIUMINT',
        'serial:big' => 'BIGINT',
        'serial:normal' => 'INT',

        'int:tiny' => 'TINYINT',
        'int:small' => 'SMALLINT',
        'int:medium' => 'MEDIUMINT',
        'int:big' => 'BIGINT',
        'int:normal' => 'INT',

        'float:tiny' => 'FLOAT',
        'float:small' => 'FLOAT',
        'float:medium' => 'FLOAT',
        'float:big' => 'DOUBLE',
        'float:normal' => 'FLOAT',

        'numeric:normal' => 'DECIMAL',

        'blob:big' => 'LONGBLOB',
        'blob:normal' => 'BLOB',
        );

齭齝齚鰁鯽齱鹚鯺鯷麺龔龑龴龳龉鱚鱙

齭齝齚鼶黛鯳麎龔龑齐齍鼶黛齤鳧鹙鹇鼬 module_name.install

module_name 鼮齝齚鹙齤鯯黢鼶黛鲒鯮 php 鼶黛鱫鯪齤齭鰱麍麨麧鯧龴 hook_schema().

鹞黙 hook 鸏鶧鶸鯦鹇齝齚鹙齤鶔龫齝齚鹙鲤 yunke, 鶧鯧龴鹙鸏鶧鹇 yunke_schema()

齭鯧龴黙鰿鰼齐齍麬鹃龴齆齤麈鹙龉鶜龴龳龲龉鹙齤麈鶬鹇龴龳龉麨麧龴齆龕鵭鷍齥齤鸷鯥龫麚麖麕麓麐鼬

table_name1 table_name2 鹇黁龔龑齱龉鹙齤鹛齵齱鶬鹇鷍麍麨麧齱龉麨麧龴齆黉

齭齝齚鰁鯽齱鹚鯺鵞鲏鳳 module_name.install 鼶黛齤鯡鳙鰱麍齱 hook_schema() 鯧龴齤 齖齒鰿鰼鶬齄鯟龑鯞麡鯝齍龴龳龉齤齌齋鹇麨麧龴齆鯜鷧齤齭齝齚鯚鯗齱鹚鯺齄鯟鰋鰊麡鯝齍龉黉

function yunke_schema() {
  $table['table_name1'] = $schema;
  $table['table_name2'] = $schema2;
  return $table;
}

模块升级可能会涉及到数据表的更新、变更等升级操作,这应该是一个独立的主题(它包含表定义、配置、数据处理等等),此处不做介绍,在模块相关主题中讲解。

本书共63小节:

评论 (写第一个评论)