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

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

本书共71小节:

评论 (写第一个评论)