func (pgDbc PostgresDbConnection) UserExists(userName string, dbConn *baseDbConnection.DbConnection) (bool, error) {
queryResults, err := dbConn.Query("SELECT 1 FROM pg_roles WHERE rolname = '" + userName + "'")
if err != nil {
return false, err
}
users := baseDbConnection.ParseResults(queryResults)
for _ = range users {
return true, nil
}
return false, nil
}
func (pgDbc PostgresDbConnection) DbExists(databaseName string, dbConn *baseDbConnection.DbConnection) (bool, error) {
queryResults, err := dbConn.Query("SELECT 1 FROM pg_database WHERE datname = '" + databaseName + "'")
if err != nil {
return false, err
}
databases := baseDbConnection.ParseResults(queryResults)
for _ = range databases {
return true, nil
}
return false, nil
}
func (pgDbc PostgresDbConnection) GetTableDefinition(tableName string, dbConn *baseDbConnection.DbConnection) (baseDbConnection.TableDefinition, error) {
// In PostgreSQL, we query the system tables in information_schema to get the list
// of tables and their columns.
sqlGetTable := `
SELECT c.table_name, c.column_name, c.ordinal_position,
c.data_type, CASE WHEN primary_key = 1 THEN TRUE ELSE FALSE END AS primary_key,
c.column_default
FROM information_schema.columns c LEFT OUTER JOIN
(SELECT kcu.table_name, kcu.column_name, 1 AS primary_key
FROM information_schema.key_column_usage kcu INNER JOIN
information_schema.table_constraints tc ON
tc.table_catalog = kcu.table_catalog AND
tc.table_schema = kcu.table_schema AND
tc.table_name = kcu.table_name
WHERE tc.constraint_type = 'PRIMARY KEY') pk
ON pk.table_name = c.table_name AND
pk.column_name = c.column_name
WHERE c.table_catalog = '` + dbConn.DBName + `' AND
c.table_schema = '` + dbConn.Schema + `' AND c.table_name = '` + tableName + `'
ORDER BY c.table_name, c.ordinal_position, c.column_name`
tableDefinition := baseDbConnection.TableDefinition{}
tableDefinition.Columns = make([]baseDbConnection.ColumnDefinition, 0)
queryResults, err := dbConn.Query(sqlGetTable)
tableColumns := baseDbConnection.ParseResults(queryResults)
if err != nil {
return baseDbConnection.TableDefinition{}, err
}
for _, tableColumn := range tableColumns {
tableDefinition.Name = tableColumn["table_name"].(string)
newColumn :=
baseDbConnection.ColumnDefinition{
tableColumn["column_name"].(string),
tableColumn["data_type"].(string),
false,
tableColumn["column_default"].(string),
}
if tableColumn["primary_key"].(bool) {
newColumn.PrimaryKey = true
}
tableDefinition.Columns = append(tableDefinition.Columns, newColumn)
}
return tableDefinition, nil
}
func (pgDbc PostgresDbConnection) CreateTable(table baseDbConnection.TableDefinition, dbConn *baseDbConnection.DbConnection) error {
existingTable, err := dbConn.GetTableDefinition(table.Name)
if err != nil {
return err
}
if existingTable.Name != table.Name {
sqlCreateTable := "CREATE TABLE IF NOT EXISTS " + dbConn.Schema + "." + table.Name + " (\n"
fields := make([]string, 0)
primaryKey := make([]string, 0)
for _, tableColumn := range table.Columns {
fieldDef := "\"" + tableColumn.Name + "\" " + tableColumn.Type
if tableColumn.Default != "" {
fieldDef += " DEFAULT " + tableColumn.Default
}
fields = append(fields, fieldDef)
if tableColumn.PrimaryKey {
primaryKey = append(primaryKey, tableColumn.Name)
}
}
fields = append(fields, "CONSTRAINT pk_"+existingTable.Name+" PRIMARY KEY ("+strings.Join(primaryKey, ",")+")")
sqlCreateTable = sqlCreateTable + strings.Join(fields, "\n,")
sqlCreateTable = sqlCreateTable + ") WITH (OIDS = FALSE);\n"
//TODO: Default Table Grants
//TODO: Indexes
//TODO: Partitioning
_, err := dbConn.Exec(sqlCreateTable)
return err
} else {
return errors.New("Table already exists.")
}
}
func (pgDbc PostgresDbConnection) CreateUser(userName string, password string, dbConn *baseDbConnection.DbConnection) error {
_, err := dbConn.Exec("CREATE USER " + userName + " WITH PASSWORD '" + password + "'")
if err == nil {
_, err = dbConn.Exec("GRANT ALL PRIVILEGES ON DATABASE " + dbConn.DBName + " TO " + userName)
}
if err == nil {
_, err = dbConn.Exec("GRANT ALL PRIVILEGES ON SCHEMA public TO " + userName)
}
return err
}
func (pgDbc PostgresDbConnection) DropUser(userName string, dbConn *baseDbConnection.DbConnection) error {
_, err := dbConn.Exec("REVOKE ALL PRIVILEGES ON SCHEMA public FROM " + userName)
if err == nil {
_, err = dbConn.Exec("REVOKE ALL PRIVILEGES ON DATABASE " + dbConn.DBName + " FROM " + userName)
}
if err == nil {
//RISK ITEM: If the user owns any objects, this will fail.
//HOW DO WE WANT TO HANDLE THIS?
_, err = dbConn.Exec("DROP USER IF EXISTS " + userName)
}
return err
}
func (pgDbc PostgresDbConnection) DropDatabase(databaseName string, dbConn *baseDbConnection.DbConnection) error {
_, err := dbConn.Exec("DROP DATABASE " + databaseName)
return err
}
func (pgDbc PostgresDbConnection) CreateDatabase(databaseName string, dbConn *baseDbConnection.DbConnection) error {
_, err := dbConn.Exec("CREATE DATABASE " + databaseName)
return err
}