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;
}

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

本书共88小节:

评论 (写第一个评论)