Skip to content

SQL Features

Jump to bottom
Dominique Debergue edited this page Dec 14, 2021 · 30 revisions

Gopher's SQL features will enable you to use all the database package's functionalities:

  • Account creation
  • Logging users in with their account name, password, and any custom checks
  • Auto-login with generated secure keys
  • Custom account information
  • Friending

If you do not wish to use the SQL features, you will need to implement your own mechanisms for the features listed above.

📘 Set-Up

Before you can use the SQL features, you will need a MySQL or similar database installed and running on your system. You can acquire the latest version of MySQL at: www.mysql.com

Once you've installed and ran SQL database, you will need to make a new user that Gopher will use to log into the database with. Make sure the user has the following privileges granted: SELECT, INSERT, UPDATE, DELETE, EXECUTE, CREATE, ALTER, REFERENCES, and DROP. You also will need to know the following information about your database for later: IP address, port number, and network protocol. If you haven't done so already, make a database where Gopher can create it's tables.

Now, with all this information you can set the required entries in gopher.ServerSettings to enable the SQL features:

package main
    
import (
	"github.com/hewiefreeman/GopherGameServer"
)

func main() {
	settings := gopher.ServerSettings{
		ServerName: "!s!",
		MaxConnections: 10000,

		HostName: "http://example.com",
		HostAlias: "http://www.example.com",
		IP: "192.168.1.1",
		Port: 8080,

		OriginOnly: true,

		// Using a TLS/SSL connection is highly recommended when using SQL features!
		TLS: true,
		CertFile: "C:/path/to/certificate.pem",
		PrivKeyFile: "C:/path/to/privkey.pem",

		// Enable SQL features
		SqlIP: "localhost",
		SqlPort: 3306,
		SqlProtocol: "tcp",
		SqlUser: "userName",
		SqlPassword: "password",
		SqlDatabase: "databaseName",
	}

	//
	gopher.Start(&settings)
}

⚠️ Warning: As stated in the above comment, it is highly recommended to use an encrypted TLS/SSL connection when using the SQL features, or sensitive account information can be compromised with network "snooping" (AKA "sniffing"). If you are not using an encrypted connection and do not know where to start, you can start by acquiring a free SSL certificate from Let's Encrypt and reading through their (or your web/cloud hosting provider's) documentation and help sections.

📘 Authenticating Clients

Gopher server and the client APIs will take care of most of the work from here. With the above entries in ServerSettings set, Gopher will now use the database to log clients in, enable clients to sign-up, and enable friending. A client will be required to sign-up before logging in unless they log in as a guest. It is highly recommended to not use the core.Login() method with the SQL features enabled. You should now rely entirely on the client API to send built-in commands, and let Gopher take care of the rest.

Check out your client API documentation to learn how to send the built-in (sign up, log in, log out, change password, etc) commands from there. All the authentication commands and their usages are explained in the Customize Authentication Features section.

📘 Custom Account Info

You can make a custom column in the accounts database with a database.AccountInfoColumn. An AccountInfoColumn registers a new column on the database in the table where User accounts are stored, and should only be used to store information that rarely change (like email, date of birth, verification codes, etc). You can make a new AccountInfoColumn with the database.NewAccountInfoColumn() method. This (like most set-up functions) can only be called before starting the server:

func main() {
	// Enable required settings
	settings := gopher.ServerSettings{
		ServerName: "!s!",
		// ...
	}

	// Register the new AccountInfoColumn "email"
	emailErr := database.NewAccountInfoColumn("email", database.DataTypeVarChar, 255, 0, true, true, false)
	if emailErr != nil {
		Println(emailErr)
		return
	}

	//
	gopher.Start(&settings)
}

The NewAccountInfoColumn() method's parameters are shown here as in the documentation:

func NewAccountInfoColumn(name string, dataType int, maxSize int, precision int, notNull bool, unique bool, encrypt bool) error

name is the name of the new info column, dataType should be one of the SQL data types defined in the documentation, maxSize is the maximum size of any data inserted to the column, precision is the decimal precision for numeric types, notNull prevents any new entries in that column from being null, unique makes sure no two of the same values are stored in that column, and encrypt will encrypt the column data before inserting.

The first time booting after registering a new AccountInfoColumn, Gopher will add a column of that name, data type, max size, etc to the User accounts table on the database. Of course, the server will not add a column to the database every boot when using NewAccountInfoColumn(). Instead, this now acts as a way for the server to remember the custom columns you've made. With a custom AccountInfoColumn registered, we can continue on to customize the authentication features.

📘 Customizing Authentication Features

There are many ways to customize the authentication features, so I think it's most logical to list them in order of relevance:

Sign-up

Using the "email" AccountInfoColumn we registered before, we can make it required for a client to supply an email as well as an account name and password when signing up by using the database.SetCustomSignupRequirements() method:

func main() {
	// Enable required settings
	settings := gopher.ServerSettings{
		ServerName: "!s!",
		// ...
	}

	// Register the new AccountInfoColumn "email"
	database.NewAccountInfoColumn("email", database.DataTypeVarChar, 255, 0, true, true, false)

	// Require the "email" AccountInfoColumn when signing up
	custSignupErr := database.SetCustomSignupRequirements([]string{"email"})
	if custSignupErr != nil {
		Println(custSignupErr)
		return
	}

	//
	gopher.Start(&settings)
}

📝 Note: You can assign multiple custom AccountInfoColumns to be requirements by adding their names to the []string passed to SetCustomSignupRequirements(), or any other custom requirements setter method.

Now, we can capture the input from the client API by setting the sign up callback with the gopher.SetSignupCallback() method and, for instance, send an email to the provided address:

func main() {
	// Enable required settings
	settings := gopher.ServerSettings{
		ServerName: "!s!",
		// ...
	}

	// Register the new AccountInfoColumn "email"
	database.NewAccountInfoColumn("email", database.DataTypeVarChar, 255, 0, true, true, false)

	// Require the "email" AccountInfoColumn when signing up
	database.SetCustomSignupRequirements([]string{"email"})

	// Set sign up callback
	cbErr := gopher.SetSignupCallback(clientSignUp)
	if cbErr != nil {
		fmt.Println(cbErr)
		return
	}

	//
	gopher.Start(&settings)
}

func clientSignUp(userName string, clientColumns map[string]interface{}) bool {
	if email, ok = clientColumns["email"].(string); ok {
		// Check if valid address format and send an email...
		// You should return false if address is not email format
		
	} else {
		// email input is not a string
		return false
	}

	// Allow sign up
	return true
}

This callback takes two parameters: userName (string), and clientColumns (map[string]interface{}). userName is the account/User name the client wants, and the clientColumns are the client's input for your defined (and in this case required) AccountInfoColumns.

The sign up callback runs before a client can sign up, so you can either have your callback return true to allow the sign up, or false to prevent it. This is useful for verifying correct and/or formatted input.

📝 Note: You can still pass clientColumns from the client API without using SetCustomSignupRequirements() or any other custom database action requirement setters. The difference is that the requirement setters will send an error back to the client if the clientColumns do not match the required ones. For example, if your custom requirements for changing a password include email and securityAnswer, the clientColumns sent from the client API for a password change request must only contain email and securityAnswer. Otherwise, an error will be sent back to the client API, and the sign up will be prevented.

Logging in

Similar to the sign up requirements, we can make the "email" column required for logging in as well as their account name and password by using the database.SetCustomLoginRequirements() method:

// Require the "email" AccountInfoColumn when logging in
database.SetCustomLoginRequirements([]string{"email"})

We can capture a login request from the client API with the gopher.SetLoginCallback() method:

func main() {
	// ........

	// Require the "email" AccountInfoColumn when logging in
	database.SetCustomLoginRequirements([]string{"email"})

	// Set log in callback
	cbErr := gopher.SetLoginCallback(clientLogIn)
	if cbErr != nil {
		fmt.Println(cbErr)
		return
	}

	//
	gopher.Start(&settings)
}

func clientLogIn(userName string, databaseID int, receivedColumns map[string]interface{}, clientColumns map[string]interface{}) bool {
	if clientColumns["email"].(string) != receivedColumns["email"].(string) {
		// email provided does not match the email for the user on database
		return false
		
	} else {
		// email input is not a string
		return false
	}

	// Allow log in
	return true
}

This callback takes four parameters: userName (string), databaseID (int), receivedColumns (map[string]interface{}), and clientColumns (map[string]interface{}). userName is the account/User name, databaseID is the index of the User account on the database, receivedColumns are the AccountInfoColumns received from the database, and clientColumns are the client's input for the AccountInfoColumns.

The receivedColumns are only retrieved from the database if the client provided some clientColumns, and only those columns are retrieved. This is how you compare input from the client to data stored on the User's account.

The log in callback runs before a client can log in, so you can either have your callback return true to allow the log in, or false to prevent it. This is useful for verifying correct and/or formatted input.

Logging in with an AccountInfoColumn

You can replace the User name requirement for logging in with any defined AccountInfoColumn with the CustomLoginColumn entry in gopher.ServerSettings:

func main() {
	settings := gopher.ServerSettings{
		// ...

		// Enable SQL features
		SqlIP: "localhost",
		SqlPort: 3306,
		SqlProtocol: "tcp",
		SqlUser: "userName",
		SqlPassword: "password",
		SqlDatabase: "databaseName",

		// Set custom login column
		CustomLoginColumn: "email",
	}

	//
	gopher.Start(&settings)
}

Now when a client sends a log in request, the server will search for an entry in the "email" AccountInfoColumn that matches the login input from the client. So, instead of using an account's name and password, clients now use the "email" AccountInfoColumn and password to log in (of course including any other custom requirements).

Changing AccountInfoColumn data

The client API can send a command that changes the data in an AccountInfoColumn for their account. You can set custom requirements for this command, just like the rest of the requirement setters with the database.SetCustomAccountInfoChangeRequirements() method:

// Require the "email" AccountInfoColumn when changing an AccountInfoColumn's data
database.SetCustomAccountInfoChangeRequirements([]string{"email"})

📝 Note: A client must be logged in to change an AccountInfoColumn's data for their account, but they still need to at least supply the correct password for the account (not including your custom requirements).

We can capture an AccountInfoColumn change request from the client API with the gopher.SetAccountInfoChangeCallback() method:

func main() {
	// ........

	// Require the "email" AccountInfoColumn when changing an AccountInfoColumn's data
	database.SetCustomLoginRequirements([]string{"email"})

	// Set account info change callback
	cbErr := gopher.SetAccountInfoChangeCallback(clientAccountInfoChange)
	if cbErr != nil {
		fmt.Println(cbErr)
		return
	}

	//
	gopher.Start(&settings)
}

func clientAccountInfoChange(userName string, databaseID int, receivedColumns map[string]interface{}, clientColumns map[string]interface{}) bool {
	if clientColumns["email"].(string) != receivedColumns["email"].(string) {
		// email provided does not match the email for the user on database
		return false
		
	} else {
		// email input is not a string
		return false
	}

	// Allow info change
	return true
}

The info change callback's parameters are exactly the same as the log in callback's, and returns a bool that, when false, prevents the password change (like in the example above).

Changing an account's password

The client API can send a command that changes their account password. You can set custom requirements for this command, just like the rest of the requirement setters with the database.SetCustomPasswordChangeRequirements() method:

// Require the "email" AccountInfoColumn when changing a password
database.SetCustomPasswordChangeRequirements([]string{"email"})

📝 Note: A client must be logged in to change their password, but they still need to at least supply the correct old password for the account (not including your custom requirements).

We can capture a password change request from the client API with the gopher.SetPasswordChangeCallback() method:

func main() {
	// ........

	// Require the "email" AccountInfoColumn when changing a password
	database.SetCustomPasswordChangeRequirements([]string{"email"})

	// Set password change callback
	cbErr := gopher.SetPasswordChangeCallback(clientPasswordChange)
	if cbErr != nil {
		fmt.Println(cbErr)
		return
	}

	//
	gopher.Start(&settings)
}

func clientPasswordChange(userName string, databaseID int, receivedColumns map[string]interface{}, clientColumns map[string]interface{}) bool {
	if clientColumns["email"].(string) != receivedColumns["email"].(string) {
		// email provided does not match the email for the user on database
		return false
		
	} else {
		// email input is not a string
		return false
	}

	// Allow password change
	return true
}

The password change callback's parameters are exactly the same as the log in callback's, and returns a bool that, when false, prevents the password change (like in the example above).

Deleting an account

The client API can send a command deletes their account. You can set custom requirements for this command, just like the rest of the requirement setters with the database.SetCustomDeleteAccountRequirements() method:

// Require the "email" AccountInfoColumn when deleting an account
database.SetCustomDeleteAccountRequirements([]string{"email"})

📝 Note: A client must be logged out to delete their account, but they still need to at least supply the correct password for the account (not including your custom requirements).

We can capture a delete account request from the client API with the gopher.SetDeleteAccountCallback() method:

func main() {
	// ........

	// Require the "email" AccountInfoColumn when deleting an account
	database.SetCustomDeleteAccountRequirements([]string{"email"})

	// Set delete account callback
	cbErr := gopher.SetDeleteAccountCallback(clientDeleteAccount)
	if cbErr != nil {
		fmt.Println(cbErr)
		return
	}

	//
	gopher.Start(&settings)
}

func clientDeleteAccount(userName string, databaseID int, receivedColumns map[string]interface{}, clientColumns map[string]interface{}) bool {
	if clientColumns["email"].(string) != receivedColumns["email"].(string) {
		// email provided does not match the email for the user on database
		return false

	} else {
		// email input is not a string
		return false

	}

	// Allow account delete request
	return true
}

The delete account callback's parameters are exactly the same as the log in callback's, and returns a bool that, when false, prevents the account from being deleted (like in the example above).

📘 Auto-Login (Remember Me)

The auto-login feature, like all the other SQL features, is taken care of by the client API and Gopher. The only thing you need to do to set it up on the server side is enable RememberMe in gopher.ServerSettings:

settings := gopher.ServerSettings{
	// .....
	
	RememberMe: true,
}

Now, a client can send a login request with a remember me boolean that when true will save a key pair in the database. The key pair will now be used to log the user in automatically the next time they connect to the server, and will make a new key for the client every time they auto-login.

📘 Friending

Friending is done entirely from the client side. Read the client API documentation to learn how to work with friending.

If you notice there is lacking information, missing features, or bad explanations, please open an issue. All requests are acceptable and will be taken into consideration.

Clone this wiki locally