github.com/hasnat/dolt/go@v0.0.0-20210628190320-9eb5d843fbb7/libraries/doltcore/sqle/procedures_table.go (about) 1 // Copyright 2021 Dolthub, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package sqle 16 17 import ( 18 "fmt" 19 "io" 20 "strings" 21 "time" 22 23 "github.com/dolthub/go-mysql-server/sql" 24 25 "github.com/dolthub/dolt/go/libraries/doltcore/doltdb" 26 "github.com/dolthub/dolt/go/libraries/doltcore/schema" 27 "github.com/dolthub/dolt/go/libraries/doltcore/sqle/sqlutil" 28 "github.com/dolthub/dolt/go/store/types" 29 ) 30 31 const ( 32 // ProceduresTableName is the name of the dolt stored procedures table. 33 ProceduresTableName = "dolt_procedures" 34 // ProceduresTableNameCol is the name of the stored procedure. Using CREATE PROCEDURE, will always be lowercase. 35 ProceduresTableNameCol = "name" 36 // ProceduresTableCreateStmtCol is the CREATE PROCEDURE statement for this stored procedure. 37 ProceduresTableCreateStmtCol = "create_stmt" 38 // ProceduresTableCreatedAtCol is the time that the stored procedure was created at, in UTC. 39 ProceduresTableCreatedAtCol = "created_at" 40 // ProceduresTableModifiedAtCol is the time that the stored procedure was last modified, in UTC. 41 ProceduresTableModifiedAtCol = "modified_at" 42 ) 43 44 // The fixed SQL schema for the `dolt_procedures` table. 45 func ProceduresTableSqlSchema() sql.Schema { 46 sqlSchema, err := sqlutil.FromDoltSchema(doltdb.ProceduresTableName, ProceduresTableSchema()) 47 if err != nil { 48 panic(err) // should never happen 49 } 50 return sqlSchema 51 } 52 53 // The fixed dolt schema for the `dolt_procedures` table. 54 func ProceduresTableSchema() schema.Schema { 55 colColl := schema.NewColCollection( 56 schema.NewColumn(doltdb.ProceduresTableNameCol, schema.DoltProceduresNameTag, types.StringKind, true, schema.NotNullConstraint{}), 57 schema.NewColumn(doltdb.ProceduresTableCreateStmtCol, schema.DoltProceduresCreateStmtTag, types.StringKind, false), 58 schema.NewColumn(doltdb.ProceduresTableCreatedAtCol, schema.DoltProceduresCreatedAtTag, types.TimestampKind, false), 59 schema.NewColumn(doltdb.ProceduresTableModifiedAtCol, schema.DoltProceduresModifiedAtTag, types.TimestampKind, false), 60 ) 61 return schema.MustSchemaFromCols(colColl) 62 } 63 64 // DoltProceduresGetTable returns the `dolt_procedures` table from the given db, creating it if it does not already exist. 65 func DoltProceduresGetTable(ctx *sql.Context, db Database) (*WritableDoltTable, error) { 66 root, err := db.GetRoot(ctx) 67 if err != nil { 68 return nil, err 69 } 70 tbl, found, err := db.GetTableInsensitiveWithRoot(ctx, root, doltdb.ProceduresTableName) 71 if err != nil { 72 return nil, err 73 } 74 if found { 75 return tbl.(*WritableDoltTable), nil 76 } 77 78 err = db.createDoltTable(ctx, doltdb.ProceduresTableName, root, ProceduresTableSchema()) 79 if err != nil { 80 return nil, err 81 } 82 root, err = db.GetRoot(ctx) 83 if err != nil { 84 return nil, err 85 } 86 tbl, found, err = db.GetTableInsensitiveWithRoot(ctx, root, doltdb.ProceduresTableName) 87 if err != nil { 88 return nil, err 89 } 90 // Verify it was created successfully 91 if !found { 92 return nil, sql.ErrTableNotFound.New(ProceduresTableName) 93 } 94 return tbl.(*WritableDoltTable), nil 95 } 96 97 // DoltProceduresAddProcedure adds the stored procedure to the `dolt_procedures` table in the given db, creating it if 98 // it does not exist. 99 func DoltProceduresAddProcedure(ctx *sql.Context, db Database, spd sql.StoredProcedureDetails) (retErr error) { 100 tbl, err := DoltProceduresGetTable(ctx, db) 101 if err != nil { 102 return err 103 } 104 _, ok, err := DoltProceduresGetDetails(ctx, tbl, spd.Name) 105 if err != nil { 106 return err 107 } 108 if ok { 109 return sql.ErrStoredProcedureAlreadyExists.New(spd.Name) 110 } 111 inserter := tbl.Inserter(ctx) 112 defer func() { 113 err := inserter.Close(ctx) 114 if retErr == nil { 115 retErr = err 116 } 117 }() 118 return inserter.Insert(ctx, sql.Row{ 119 strings.ToLower(spd.Name), 120 spd.CreateStatement, 121 spd.CreatedAt.UTC(), 122 spd.ModifiedAt.UTC(), 123 }) 124 } 125 126 // DoltProceduresDropProcedure removes the stored procedure from the `dolt_procedures` table. 127 func DoltProceduresDropProcedure(ctx *sql.Context, db Database, name string) (retErr error) { 128 strings.ToLower(name) 129 tbl, err := DoltProceduresGetTable(ctx, db) 130 if err != nil { 131 return err 132 } 133 _, ok, err := DoltProceduresGetDetails(ctx, tbl, name) 134 if err != nil { 135 return err 136 } 137 if !ok { 138 return sql.ErrStoredProcedureDoesNotExist.New(name) 139 } 140 deleter := tbl.Deleter(ctx) 141 defer func() { 142 err := deleter.Close(ctx) 143 if retErr == nil { 144 retErr = err 145 } 146 }() 147 return deleter.Delete(ctx, sql.Row{name}) 148 } 149 150 // DoltProceduresGetDetails returns the stored procedure with the given name from `dolt_procedures` if it exists. 151 func DoltProceduresGetDetails(ctx *sql.Context, tbl *WritableDoltTable, name string) (sql.StoredProcedureDetails, bool, error) { 152 name = strings.ToLower(name) 153 indexes, err := tbl.GetIndexes(ctx) 154 if err != nil { 155 return sql.StoredProcedureDetails{}, false, err 156 } 157 var fragNameIndex sql.Index 158 for _, index := range indexes { 159 if index.ID() == "PRIMARY" { 160 fragNameIndex = index 161 break 162 } 163 } 164 if fragNameIndex == nil { 165 return sql.StoredProcedureDetails{}, false, fmt.Errorf("could not find primary key index on system table `%s`", 166 doltdb.SchemasTableName) 167 } 168 169 indexLookup, err := fragNameIndex.Get(name) 170 if err != nil { 171 return sql.StoredProcedureDetails{}, false, err 172 } 173 dil := indexLookup.(*doltIndexLookup) 174 rowIter, err := dil.RowIter(ctx, dil.IndexRowData(), nil) 175 if err != nil { 176 return sql.StoredProcedureDetails{}, false, err 177 } 178 defer rowIter.Close(ctx) 179 sqlRow, err := rowIter.Next() 180 if err == nil { 181 if len(sqlRow) != 4 { 182 return sql.StoredProcedureDetails{}, false, fmt.Errorf("unexpected row in dolt_procedures:\n%v", sqlRow) 183 } 184 return sql.StoredProcedureDetails{ 185 Name: sqlRow[0].(string), 186 CreateStatement: sqlRow[1].(string), 187 CreatedAt: sqlRow[2].(time.Time), 188 ModifiedAt: sqlRow[3].(time.Time), 189 }, true, nil 190 } else if err == io.EOF { 191 return sql.StoredProcedureDetails{}, false, nil 192 } else { 193 return sql.StoredProcedureDetails{}, false, err 194 } 195 }