gb.mysql

Public Procedure CreateDatabase()

  Dim $hConnection As New Connection

  With $hConnection
    .Type = "mysql"
    .Port = "3306"
    .Host = "localhost"
    .User = "root"
    .Password = "mypass"
    .Name = "Gambas"
    .Open()
  End With

  $hConnection.MySQL.Charset = "utf8" 'Sets the charset, default: utf8
  $hConnection.MySQL.Collation = "utf8_general_ci" 'Sets the collation, default: utf8_general_ci
  $hConnection.MySQL.Engine = "InnoDB" 'Sets the engine, default: InnoDB
  $hConnection.MySQL.DataBase.Delete(Gambas, True) 'Deletes a database
  $hConnection.MySQL.DataBase.Add(Gambas, $hConnection.MySQL.Charset, $hConnection.MySQL.Collation) 'Adds a database
  $hConnection.MySQL.DataBase.Modify(Gambas, $hConnection.MySQL.Charset, $hConnection.MySQL.Collation)

  'Shows the database information
  Message(("Information for: ") & $hConnection.Name & GB.NewLine & $hConnection.MySQL.DataBase.Info(txtDatabase.Text))

  'Lets create some tables

  'Table actor
  $hConnection.MySQL.Field.Add("actor_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
  $hConnection.MySQL.Field.Add("first_name", $hConnection.MySQL.DataTypes.VarChar(45), False)
  $hConnection.MySQL.Field.Add("last_name", $hConnection.MySQL.DataTypes.VarChar(45), False)
  $hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
  $hConnection.MySQL.Field.PrimaryKey(["actor_id"])
  $hConnection.MySQL.Field.Index("idx_actor_last_name", "last_name")
  $hConnection.MySQL.Table.Add("actor", "InnoDB", $hConnection.MySQL.Charset)
  'Statement executed
  ' Create TABLE `actor` (
  '   `actor_id` smallint(5) unsigned NOT NULL auto_increment,
  '   `first_name` varchar(45) NOT NULL,
  '   `last_name` varchar(45) NOT NULL,
  '   `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  '   PRIMARY KEY(`actor_id`),
  '   KEY `idx_actor_last_name` (`last_name`)
  ' )ENGINE = InnoDB Default CHARSET = utf8

  'Table country
  $hConnection.MySQL.Field.Add("country_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
  $hConnection.MySQL.Field.Add("country", $hConnection.MySQL.DataTypes.VarChar(50), False)
  $hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
  $hConnection.MySQL.Field.PrimaryKey(["country_id"])
  $hConnection.MySQL.Table.Add("country", "InnoDB", $hConnection.MySQL.Charset)
  'Statement executed
  ' Create TABLE `country` (
  '   `country_id` smallint(5) unsigned NOT NULL auto_increment,
  '   `country` varchar(50) NOT NULL,
  '   `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  '   PRIMARY KEY(`country_id`)
  ' )ENGINE = InnoDB Default CHARSET = utf8

  'Table city
  $hConnection.MySQL.Field.Add("city_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
  $hConnection.MySQL.Field.Add("city", $hConnection.MySQL.DataTypes.VarChar(50), False)
  $hConnection.MySQL.Field.Add("country_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False)
  $hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
  $hConnection.MySQL.Field.PrimaryKey(["city_id"])
  $hConnection.MySQL.Field.Index("idx_fk_country_id", "country_id")
  $hConnection.MySQL.Field.ForeignKey("country_id", "country", "country_id", "RESTRICT", "CASCADE")
  $hConnection.MySQL.Table.Add("city", "InnoDB", $hConnection.MySQL.Charset)
  'Statement executed
  ' Create TABLE `city` (
  '   `city_id` smallint(5) unsigned NOT NULL auto_increment,
  '   `city` varchar(50) NOT NULL,
  '   `country_id` smallint(5) unsigned NOT NULL,
  '   `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  '   PRIMARY KEY(`city_id`),
  '   KEY `idx_fk_country_id` (`country_id`),
  '   CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
  ' )ENGINE = InnoDB Default CHARSET = utf8

  'Table address
  $hConnection.MySQL.Field.Add("address_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False,, True)
  $hConnection.MySQL.Field.Add("address", $hConnection.MySQL.DataTypes.VarChar(50), False)
  $hConnection.MySQL.Field.Add("address2", $hConnection.MySQL.DataTypes.VarChar(50), True, $hConnection.MySQL.DataTypes.Null)
  $hConnection.MySQL.Field.Add("district", $hConnection.MySQL.DataTypes.VarChar(20), False)
  $hConnection.MySQL.Field.Add("city_id", $hConnection.MySQL.DataTypes.UnsignedSmallInt, False)
  $hConnection.MySQL.Field.Add("postal_code", $hConnection.MySQL.DataTypes.VarChar(10), True, $hConnection.MySQL.DataTypes.Null)
  $hConnection.MySQL.Field.Add("phone", $hConnection.MySQL.DataTypes.VarChar(20), False)
  $hConnection.MySQL.Field.Add("last_update", $hConnection.MySQL.DataTypes.TimeStamp, False, "CURRENT_TIMESTAMP",, "CURRENT_TIMESTAMP")
  $hConnection.MySQL.Field.PrimaryKey(["address_id"])
  $hConnection.MySQL.Field.Index("idx_fk_city_id", "city_id")
  $hConnection.MySQL.Field.ForeignKey("city_id", "city", "city_id", "RESTRICT", "CASCADE")
  $hConnection.MySQL.Table.Add("address", "InnoDB", $hConnection.MySQL.Charset)
  'Statement executed
  ' Create TABLE `address` (
  '   `address_id` smallint(5) unsigned NOT NULL auto_increment,
  '   `address` varchar(50) NOT NULL,
  '   `address2` varchar(50) default 'NULL',
  '   `district` varchar(20) NOT NULL,
  '   `city_id` smallint(5) unsigned NOT NULL,
  '   `postal_code` varchar(10) default 'NULL',
  '   `phone` varchar(20) NOT NULL,
  '   `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  '   PRIMARY KEY(`address_id`),
  '   KEY `idx_fk_city_id` (`city_id`),
  '   CONSTRAINT `address_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
  ' )ENGINE = InnoDB Default CHARSET = utf8

End