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

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

本书共78小节:

评论 (写第一个评论)