github.com/adecaro/fabric-ca@v2.0.0-alpha+incompatible/lib/server/db/mysql/mysql.go (about)

     1  /*
     2  Copyright IBM Corp. All Rights Reserved.
     3  
     4  SPDX-License-Identifier: Apache-2.0
     5  */
     6  
     7  package mysql
     8  
     9  import (
    10  	"context"
    11  	"regexp"
    12  	"strings"
    13  
    14  	"github.com/cloudflare/cfssl/log"
    15  	"github.com/go-sql-driver/mysql"
    16  	"github.com/hyperledger/fabric-ca/lib/server/db"
    17  	"github.com/hyperledger/fabric-ca/lib/server/db/util"
    18  	"github.com/hyperledger/fabric-ca/lib/tls"
    19  	"github.com/hyperledger/fabric/bccsp"
    20  	"github.com/hyperledger/fabric/common/metrics"
    21  	"github.com/jmoiron/sqlx"
    22  	"github.com/pkg/errors"
    23  )
    24  
    25  var (
    26  	re = regexp.MustCompile(`\/([0-9,a-z,A-Z$_]+)`)
    27  )
    28  
    29  // Mysql defines MySQL database
    30  type Mysql struct {
    31  	SqlxDB          db.FabricCADB
    32  	TLS             *tls.ClientTLSConfig
    33  	CSP             bccsp.BCCSP
    34  	CAName          string
    35  	MetricsProvider metrics.Provider
    36  
    37  	datasource string
    38  	dbName     string
    39  }
    40  
    41  // NewDB create a MySQL database
    42  func NewDB(datasource, caName string, clientTLSConfig *tls.ClientTLSConfig, csp bccsp.BCCSP, metricsProvider metrics.Provider) *Mysql {
    43  	log.Debugf("Using MySQL database, connecting to database...")
    44  	return &Mysql{
    45  		TLS:             clientTLSConfig,
    46  		CSP:             csp,
    47  		datasource:      datasource,
    48  		CAName:          caName,
    49  		MetricsProvider: metricsProvider,
    50  	}
    51  }
    52  
    53  // Connect connects to a MySQL server
    54  func (m *Mysql) Connect() error {
    55  	datasource := m.datasource
    56  	clientTLSConfig := m.TLS
    57  
    58  	m.dbName = util.GetDBName(datasource)
    59  	log.Debugf("Database Name: %s", m.dbName)
    60  
    61  	connStr := re.ReplaceAllString(datasource, "/")
    62  
    63  	if clientTLSConfig.Enabled {
    64  		tlsConfig, err := tls.GetClientTLSConfig(clientTLSConfig, m.CSP)
    65  		if err != nil {
    66  			return errors.WithMessage(err, "Failed to get client TLS for MySQL")
    67  		}
    68  
    69  		mysql.RegisterTLSConfig("custom", tlsConfig)
    70  	}
    71  
    72  	log.Debugf("Connecting to MySQL server, using connection string: %s", util.MaskDBCred(connStr))
    73  	sqlxdb, err := sqlx.Connect("mysql", connStr)
    74  	if err != nil {
    75  		return errors.Wrap(err, "Failed to connect to MySQL database")
    76  	}
    77  
    78  	m.SqlxDB = db.New(sqlxdb, m.CAName, m.MetricsProvider)
    79  	return nil
    80  }
    81  
    82  // PingContext pings the database
    83  func (m *Mysql) PingContext(ctx context.Context) error {
    84  	err := m.SqlxDB.PingContext(ctx)
    85  	if err != nil {
    86  		return errors.Wrap(err, "Failed to ping to MySQL database")
    87  	}
    88  	return nil
    89  }
    90  
    91  // Create creates database and tables
    92  func (m *Mysql) Create() (*db.DB, error) {
    93  	db, err := m.CreateDatabase()
    94  	if err != nil {
    95  		return nil, err
    96  	}
    97  	err = m.CreateTables()
    98  	if err != nil {
    99  		return nil, err
   100  	}
   101  	return db, nil
   102  }
   103  
   104  // CreateDatabase creates database
   105  func (m *Mysql) CreateDatabase() (*db.DB, error) {
   106  	datasource := m.datasource
   107  	dbName := m.dbName
   108  	err := m.createDatabase()
   109  	if err != nil {
   110  		return nil, errors.Wrap(err, "Failed to create MySQL database")
   111  	}
   112  
   113  	log.Debugf("Connecting to database '%s', using connection string: '%s'", dbName, util.MaskDBCred(datasource))
   114  	sqlxdb, err := sqlx.Open("mysql", datasource)
   115  	if err != nil {
   116  		return nil, errors.Wrapf(err, "Failed to open database (%s) in MySQL server", dbName)
   117  	}
   118  
   119  	m.SqlxDB = db.New(sqlxdb, m.CAName, m.MetricsProvider)
   120  
   121  	return m.SqlxDB.(*db.DB), nil
   122  }
   123  
   124  // CreateTables creates table
   125  func (m *Mysql) CreateTables() error {
   126  	err := m.createTables()
   127  	if err != nil {
   128  		return errors.Wrap(err, "Failed to create MySQL tables")
   129  	}
   130  	return nil
   131  }
   132  
   133  func (m *Mysql) createDatabase() error {
   134  	dbName := m.dbName
   135  	log.Debugf("Creating MySQL Database (%s) if it does not exist...", dbName)
   136  
   137  	_, err := m.SqlxDB.Exec("CreateDatabase", "CREATE DATABASE IF NOT EXISTS "+dbName)
   138  	if err != nil {
   139  		return errors.Wrap(err, "Failed to execute create database query")
   140  	}
   141  
   142  	return nil
   143  }
   144  
   145  func (m *Mysql) createTables() error {
   146  	db := m.SqlxDB
   147  	log.Debug("Creating users table if it doesn't exist")
   148  	if _, err := db.Exec("CreateUsersTable", "CREATE TABLE IF NOT EXISTS users (id VARCHAR(255) NOT NULL, token blob, type VARCHAR(256), affiliation VARCHAR(1024), attributes TEXT, state INTEGER, max_enrollments INTEGER, level INTEGER DEFAULT 0, incorrect_password_attempts INTEGER DEFAULT 0, PRIMARY KEY (id)) DEFAULT CHARSET=utf8 COLLATE utf8_bin"); err != nil {
   149  		return errors.Wrap(err, "Error creating users table")
   150  	}
   151  	log.Debug("Creating affiliations table if it doesn't exist")
   152  	if _, err := db.Exec("CreateAffiliationsTable", "CREATE TABLE IF NOT EXISTS affiliations (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(1024) NOT NULL, prekey VARCHAR(1024), level INTEGER DEFAULT 0, PRIMARY KEY (id)) DEFAULT CHARSET=utf8 COLLATE utf8_bin"); err != nil {
   153  		return errors.Wrap(err, "Error creating affiliations table")
   154  	}
   155  	log.Debug("Creating index on 'name' in the affiliations table")
   156  	if _, err := db.Exec("CreateAffiliationsIndex", "CREATE INDEX name_index on affiliations (name)"); err != nil {
   157  		if !strings.Contains(err.Error(), "Error 1061") { // Error 1061: Duplicate key name, index already exists
   158  			return errors.Wrap(err, "Error creating index on affiliations table")
   159  		}
   160  	}
   161  	log.Debug("Creating certificates table if it doesn't exist")
   162  	if _, err := db.Exec("CreateCertificatesTable", "CREATE TABLE IF NOT EXISTS certificates (id VARCHAR(255), serial_number varbinary(128) NOT NULL, authority_key_identifier varbinary(128) NOT NULL, ca_label varbinary(128), status varbinary(128) NOT NULL, reason int, expiry timestamp DEFAULT 0, revoked_at timestamp DEFAULT 0, pem varbinary(4096) NOT NULL, level INTEGER DEFAULT 0, PRIMARY KEY(serial_number, authority_key_identifier)) DEFAULT CHARSET=utf8 COLLATE utf8_bin"); err != nil {
   163  		return errors.Wrap(err, "Error creating certificates table")
   164  	}
   165  	log.Debug("Creating credentials table if it doesn't exist")
   166  	if _, err := db.Exec("CreateCredentialsTable", "CREATE TABLE IF NOT EXISTS credentials (id VARCHAR(255), revocation_handle varbinary(128) NOT NULL, cred varbinary(4096) NOT NULL, ca_label varbinary(128), status varbinary(128) NOT NULL, reason int, expiry timestamp DEFAULT 0, revoked_at timestamp DEFAULT 0, level INTEGER DEFAULT 0, PRIMARY KEY(revocation_handle)) DEFAULT CHARSET=utf8 COLLATE utf8_bin"); err != nil {
   167  		return errors.Wrap(err, "Error creating credentials table")
   168  	}
   169  	log.Debug("Creating revocation_authority_info table if it does not exist")
   170  	if _, err := db.Exec("CreateRevocationAuthorityTable", "CREATE TABLE IF NOT EXISTS revocation_authority_info (epoch INTEGER, next_handle INTEGER, lasthandle_in_pool INTEGER, level INTEGER DEFAULT 0, PRIMARY KEY (epoch)) DEFAULT CHARSET=utf8 COLLATE utf8_bin"); err != nil {
   171  		return errors.Wrap(err, "Error creating revocation_authority_info table")
   172  	}
   173  	log.Debug("Creating nonces table if it does not exist")
   174  	if _, err := db.Exec("CreateNoncesTable", "CREATE TABLE IF NOT EXISTS nonces (val VARCHAR(255) NOT NULL, expiry timestamp, level INTEGER DEFAULT 0, PRIMARY KEY (val)) DEFAULT CHARSET=utf8 COLLATE utf8_bin"); err != nil {
   175  		return errors.Wrap(err, "Error creating nonces table")
   176  	}
   177  	log.Debug("Creating properties table if it does not exist")
   178  	if _, err := db.Exec("CreatePropertiesTable", "CREATE TABLE IF NOT EXISTS properties (property VARCHAR(255), value VARCHAR(256), PRIMARY KEY(property)) DEFAULT CHARSET=utf8 COLLATE utf8_bin"); err != nil {
   179  		return errors.Wrap(err, "Error creating properties table")
   180  	}
   181  	_, err := db.Exec("CreatePropertiesTable", db.Rebind("INSERT INTO properties (property, value) VALUES ('identity.level', '0'), ('affiliation.level', '0'), ('certificate.level', '0'), ('credential.level', '0'), ('rcinfo.level', '0'), ('nonce.level', '0')"))
   182  	if err != nil {
   183  		if !strings.Contains(err.Error(), "1062") { // MySQL error code for duplicate entry
   184  			return err
   185  		}
   186  	}
   187  	return nil
   188  }