vitess.io/vitess@v0.16.2/go/mysql/schema.go (about)

     1  /*
     2  Copyright 2019 The Vitess Authors.
     3  
     4  Licensed under the Apache License, Version 2.0 (the "License");
     5  you may not use this file except in compliance with the License.
     6  You may obtain a copy of the License at
     7  
     8      http://www.apache.org/licenses/LICENSE-2.0
     9  
    10  Unless required by applicable law or agreed to in writing, software
    11  distributed under the License is distributed on an "AS IS" BASIS,
    12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    13  See the License for the specific language governing permissions and
    14  limitations under the License.
    15  */
    16  
    17  package mysql
    18  
    19  import (
    20  	"vitess.io/vitess/go/mysql/collations"
    21  	"vitess.io/vitess/go/sqltypes"
    22  
    23  	querypb "vitess.io/vitess/go/vt/proto/query"
    24  )
    25  
    26  // This file contains the mysql queries used by different parts of the code.
    27  
    28  const (
    29  	// BaseShowPrimary is the base query for fetching primary key info.
    30  	BaseShowPrimary = `
    31  		SELECT TABLE_NAME as table_name, COLUMN_NAME as column_name
    32  		FROM information_schema.STATISTICS
    33  		WHERE TABLE_SCHEMA = DATABASE() AND LOWER(INDEX_NAME) = 'primary'
    34  		ORDER BY table_name, SEQ_IN_INDEX`
    35  	// ShowRowsRead is the query used to find the number of rows read.
    36  	ShowRowsRead = "show status like 'Innodb_rows_read'"
    37  
    38  	// DetectSchemaChange query detects if there is any schema change from previous copy.
    39  	DetectSchemaChange = `
    40  SELECT DISTINCT table_name
    41  FROM (
    42  	SELECT table_name, column_name, ordinal_position, character_set_name, collation_name, data_type, column_key
    43  	FROM information_schema.columns
    44  	WHERE table_schema = database()
    45  
    46  	UNION ALL
    47  
    48  	SELECT table_name, column_name, ordinal_position, character_set_name, collation_name, data_type, column_key
    49  	FROM _vt.schemacopy
    50  	WHERE table_schema = database()
    51  ) _inner
    52  GROUP BY table_name, column_name, ordinal_position, character_set_name, collation_name, data_type, column_key
    53  HAVING COUNT(*) = 1
    54  `
    55  
    56  	// DetectSchemaChangeOnlyBaseTable query detects if there is any schema change from previous copy excluding view tables.
    57  	DetectSchemaChangeOnlyBaseTable = `
    58  SELECT DISTINCT table_name
    59  FROM (
    60  	SELECT table_name, column_name, ordinal_position, character_set_name, collation_name, data_type, column_key
    61  	FROM information_schema.columns
    62  	WHERE table_schema = database() and table_name in (select table_name from information_schema.tables where table_schema = database() and table_type = 'BASE TABLE')
    63  
    64  	UNION ALL
    65  
    66  	SELECT table_name, column_name, ordinal_position, character_set_name, collation_name, data_type, column_key
    67  	FROM _vt.schemacopy
    68  	WHERE table_schema = database()
    69  ) _inner
    70  GROUP BY table_name, column_name, ordinal_position, character_set_name, collation_name, data_type, column_key
    71  HAVING COUNT(*) = 1
    72  `
    73  
    74  	// ClearSchemaCopy query clears the schemacopy table.
    75  	ClearSchemaCopy = `delete from _vt.schemacopy where table_schema = database()`
    76  
    77  	// InsertIntoSchemaCopy query copies over the schema information from information_schema.columns table.
    78  	InsertIntoSchemaCopy = `insert _vt.schemacopy
    79  select table_schema, table_name, column_name, ordinal_position, character_set_name, collation_name, data_type, column_key
    80  from information_schema.columns
    81  where table_schema = database()`
    82  
    83  	// fetchColumns are the columns we fetch
    84  	fetchColumns = "table_name, column_name, data_type, collation_name"
    85  
    86  	// FetchUpdatedTables queries fetches all information about updated tables
    87  	FetchUpdatedTables = `select  ` + fetchColumns + `
    88  from _vt.schemacopy
    89  where table_schema = database() and
    90  	table_name in ::tableNames
    91  order by table_name, ordinal_position`
    92  
    93  	// FetchTables queries fetches all information about tables
    94  	FetchTables = `select ` + fetchColumns + `
    95  from _vt.schemacopy
    96  where table_schema = database()
    97  order by table_name, ordinal_position`
    98  
    99  	// GetColumnNamesQueryPatternForTable is used for mocking queries in unit tests
   100  	GetColumnNamesQueryPatternForTable = `SELECT COLUMN_NAME.*TABLE_NAME.*%s.*`
   101  
   102  	// Views
   103  	InsertIntoViewsTable = `insert into _vt.views (
   104      table_schema,
   105  	table_name,
   106  	create_statement) values (database(), :table_name, :create_statement)`
   107  
   108  	ReplaceIntoViewsTable = `replace into _vt.views (
   109  	table_schema,
   110  	table_name,
   111  	create_statement) values (database(), :table_name, :create_statement)`
   112  
   113  	UpdateViewsTable = `update _vt.views 
   114  	set create_statement = :create_statement 
   115  	where table_schema = database() and table_name = :table_name`
   116  
   117  	DeleteFromViewsTable = `delete from _vt.views where table_schema = database() and table_name in ::table_name`
   118  
   119  	SelectFromViewsTable = `select table_name from _vt.views where table_schema = database() and table_name in ::table_name`
   120  
   121  	SelectAllViews = `select table_name, updated_at from _vt.views where table_schema = database()`
   122  
   123  	// FetchUpdatedViews queries fetches information about updated views
   124  	FetchUpdatedViews = `select table_name, create_statement from _vt.views where table_schema = database() and table_name in ::viewnames`
   125  
   126  	// FetchViews queries fetches all views
   127  	FetchViews = `select table_name, create_statement from _vt.views where table_schema = database()`
   128  )
   129  
   130  // BaseShowTablesFields contains the fields returned by a BaseShowTables or a BaseShowTablesForTable command.
   131  // They are validated by the
   132  // testBaseShowTables test.
   133  var BaseShowTablesFields = []*querypb.Field{{
   134  	Name:         "t.table_name",
   135  	Type:         querypb.Type_VARCHAR,
   136  	Table:        "tables",
   137  	OrgTable:     "TABLES",
   138  	Database:     "information_schema",
   139  	OrgName:      "TABLE_NAME",
   140  	ColumnLength: 192,
   141  	Charset:      collations.CollationUtf8ID,
   142  	Flags:        uint32(querypb.MySqlFlag_NOT_NULL_FLAG),
   143  }, {
   144  	Name:         "t.table_type",
   145  	Type:         querypb.Type_VARCHAR,
   146  	Table:        "tables",
   147  	OrgTable:     "TABLES",
   148  	Database:     "information_schema",
   149  	OrgName:      "TABLE_TYPE",
   150  	ColumnLength: 192,
   151  	Charset:      collations.CollationUtf8ID,
   152  	Flags:        uint32(querypb.MySqlFlag_NOT_NULL_FLAG),
   153  }, {
   154  	Name:         "unix_timestamp(t.create_time)",
   155  	Type:         querypb.Type_INT64,
   156  	ColumnLength: 11,
   157  	Charset:      collations.CollationBinaryID,
   158  	Flags:        uint32(querypb.MySqlFlag_BINARY_FLAG | querypb.MySqlFlag_NUM_FLAG),
   159  }, {
   160  	Name:         "t.table_comment",
   161  	Type:         querypb.Type_VARCHAR,
   162  	Table:        "tables",
   163  	OrgTable:     "TABLES",
   164  	Database:     "information_schema",
   165  	OrgName:      "TABLE_COMMENT",
   166  	ColumnLength: 6144,
   167  	Charset:      collations.CollationUtf8ID,
   168  	Flags:        uint32(querypb.MySqlFlag_NOT_NULL_FLAG),
   169  }, {
   170  	Name:         "i.file_size",
   171  	Type:         querypb.Type_INT64,
   172  	ColumnLength: 11,
   173  	Charset:      collations.CollationBinaryID,
   174  	Flags:        uint32(querypb.MySqlFlag_BINARY_FLAG | querypb.MySqlFlag_NUM_FLAG),
   175  }, {
   176  	Name:         "i.allocated_size",
   177  	Type:         querypb.Type_INT64,
   178  	ColumnLength: 11,
   179  	Charset:      collations.CollationBinaryID,
   180  	Flags:        uint32(querypb.MySqlFlag_BINARY_FLAG | querypb.MySqlFlag_NUM_FLAG),
   181  }}
   182  
   183  // BaseShowTablesRow returns the fields from a BaseShowTables or
   184  // BaseShowTablesForTable command.
   185  func BaseShowTablesRow(tableName string, isView bool, comment string) []sqltypes.Value {
   186  	tableType := "BASE TABLE"
   187  	if isView {
   188  		tableType = "VIEW"
   189  	}
   190  	return []sqltypes.Value{
   191  		sqltypes.MakeTrusted(sqltypes.VarChar, []byte(tableName)),
   192  		sqltypes.MakeTrusted(sqltypes.VarChar, []byte(tableType)),
   193  		sqltypes.MakeTrusted(sqltypes.Int64, []byte("1427325875")), // unix_timestamp(create_time)
   194  		sqltypes.MakeTrusted(sqltypes.VarChar, []byte(comment)),
   195  		sqltypes.MakeTrusted(sqltypes.Int64, []byte("100")), // file_size
   196  		sqltypes.MakeTrusted(sqltypes.Int64, []byte("150")), // allocated_size
   197  	}
   198  }
   199  
   200  // ShowPrimaryFields contains the fields for a BaseShowPrimary.
   201  var ShowPrimaryFields = []*querypb.Field{{
   202  	Name: "table_name",
   203  	Type: sqltypes.VarChar,
   204  }, {
   205  	Name: "column_name",
   206  	Type: sqltypes.VarChar,
   207  }}
   208  
   209  // ShowPrimaryRow returns a row for a primary key column.
   210  func ShowPrimaryRow(tableName, colName string) []sqltypes.Value {
   211  	return []sqltypes.Value{
   212  		sqltypes.MakeTrusted(sqltypes.VarChar, []byte(tableName)),
   213  		sqltypes.MakeTrusted(sqltypes.VarChar, []byte(colName)),
   214  	}
   215  }