func (tr *Transaction) Update(t *table.Table, rowNumber int, row map[string]string) int { // Execute "before update" triggers. beforeTable, status := tr.DB.Get("~before") if status != st.OK { return status } original, status := t.Read(rowNumber) if status != st.OK { return status } triggerRA := ra.New() _, status = triggerRA.Load(beforeTable) if status != st.OK { return status } _, status = triggerRA.Select("TABLE", filter.Eq{}, t.Name) if status != st.OK { return status } status = trigger.ExecuteTrigger(tr.DB, t, triggerRA, "UP", row, original) if status != st.OK { return status } // Update the row. status = t.Update(rowNumber, row) if status != st.OK { return status } // Execute "after update" triggers. afterTable, status := tr.DB.Get("~after") if status != st.OK { return status } triggerRA = ra.New() _, status = triggerRA.Load(afterTable) if status != st.OK { return status } _, status = triggerRA.Select("TABLE", filter.Eq{}, t.Name) if status != st.OK { return status } status = trigger.ExecuteTrigger(tr.DB, t, triggerRA, "UP", row, original) if status != st.OK { return status } // Log the updated row. tr.Log(&UndoUpdate{t, rowNumber, original}) return st.OK }
func (tr *Transaction) Delete(t *table.Table, rowNumber int) int { // Execute "before delete" triggers. beforeTable, status := tr.DB.Get("~before") if status != st.OK { return status } row, status := t.Read(rowNumber) if status != st.OK { return status } triggerRA := ra.New() _, status = triggerRA.Load(beforeTable) if status != st.OK { return status } _, status = triggerRA.Select("TABLE", filter.Eq{}, t.Name) if status != st.OK { return status } status = trigger.ExecuteTrigger(tr.DB, t, triggerRA, "DE", row, nil) if status != st.OK { return status } // Update the row. status = t.Delete(rowNumber) if status != st.OK { return status } // Execute "after delete" triggers. afterTable, status := tr.DB.Get("~after") if status != st.OK { return status } triggerRA = ra.New() _, status = triggerRA.Load(afterTable) if status != st.OK { return status } _, status = triggerRA.Select("TABLE", filter.Eq{}, t.Name) if status != st.OK { return status } status = trigger.ExecuteTrigger(tr.DB, t, triggerRA, "DE", row, nil) if status != st.OK { return status } // Log the deleted row. tr.Log(&UndoDelete{t, rowNumber}) return st.OK }
func (tr *Transaction) Insert(t *table.Table, row map[string]string) int { // Execute "before insert" triggers. beforeTable, status := tr.DB.Get("~before") if status != st.OK { return status } triggerRA := ra.New() _, status = triggerRA.Load(beforeTable) if status != st.OK { return status } _, status = triggerRA.Select("TABLE", filter.Eq{}, t.Name) if status != st.OK { return status } status = trigger.ExecuteTrigger(tr.DB, t, triggerRA, "IN", row, nil) if status != st.OK { return status } // Insert the new row to table. numberOfRows, status := t.NumberOfRows() if status != st.OK { return status } status = t.Insert(row) if status != st.OK { return status } // Execute "after insert" triggers. afterTable, status := tr.DB.Get("~after") if status != st.OK { return status } triggerRA = ra.New() _, status = triggerRA.Load(afterTable) if status != st.OK { return status } _, status = triggerRA.Select("TABLE", filter.Eq{}, t.Name) if status != st.OK { return status } status = trigger.ExecuteTrigger(tr.DB, t, triggerRA, "IN", row, nil) if status != st.OK { return status } // Log the inserted row. tr.Log(&UndoInsert{t, numberOfRows}) return st.OK }
// Removes foreign key constraint from a column, together with delete/update restricted triggers. func RemoveFK(db *database.Database, fkTable *table.Table, fkColumn string, pkTable *table.Table, pkColumn string) int { beforeTable, status := db.Get("~before") if status != st.OK { return status } query := ra.New() query.Load(beforeTable) // Remove FK constraint on FK column. status = findAndDelete(beforeTable, query, ra.Condition{Alias: "TABLE", Filter: filter.Eq{}, Parameter: fkTable.Name}, ra.Condition{Alias: "COLUMN", Filter: filter.Eq{}, Parameter: fkColumn}, ra.Condition{Alias: "FUNC", Filter: filter.Eq{}, Parameter: "FK"}, ra.Condition{Alias: "PARAM", Filter: filter.Eq{}, Parameter: pkTable.Name + ";" + pkColumn}) if status != st.OK { return status } // Remove delete restricted trigger. status = findAndDelete(beforeTable, query, ra.Condition{Alias: "TABLE", Filter: filter.Eq{}, Parameter: pkTable.Name}, ra.Condition{Alias: "COLUMN", Filter: filter.Eq{}, Parameter: pkColumn}, ra.Condition{Alias: "FUNC", Filter: filter.Eq{}, Parameter: "DR"}, ra.Condition{Alias: "PARAM", Filter: filter.Eq{}, Parameter: fkTable.Name + ";" + fkColumn}) if status != st.OK { return status } // Remove update restricted trigger. return findAndDelete(beforeTable, query, ra.Condition{Alias: "TABLE", Filter: filter.Eq{}, Parameter: pkTable.Name}, ra.Condition{Alias: "COLUMN", Filter: filter.Eq{}, Parameter: pkColumn}, ra.Condition{Alias: "FUNC", Filter: filter.Eq{}, Parameter: "UR"}, ra.Condition{Alias: "PARAM", Filter: filter.Eq{}, Parameter: fkTable.Name + ";" + fkColumn}) }
// Removes primary key constraint from a column. func RemovePK(db *database.Database, t *table.Table, name string) int { beforeTable, status := db.Get("~before") if status != st.OK { return status } query := ra.New() query.Load(beforeTable) return findAndDelete(beforeTable, query, ra.Condition{Alias: "TABLE", Filter: filter.Eq{}, Parameter: t.Name}, ra.Condition{Alias: "COLUMN", Filter: filter.Eq{}, Parameter: name}, ra.Condition{Alias: "FUNC", Filter: filter.Eq{}, Parameter: "PK"}) }
// Handle UPDATE/DELETE statements. func Eg8() { /* CREATE TABLE PERSON ( NAME CHAR(20), AGE CHAR(2) ); CREATE TABLE CONTACT ( NAME CHAR(20), SITE CHAR(20), USERNAME CHAR(40) ); INSERT INTO PERSON VALUES('BUZZ', '18'); INSERT INTO PERSON VALUES('CHRISTINA', '16'); INSERT INTO PERSON VALUES('JOSHUA', '21'); INSERT INTO PERSON VALUES('NIKKI', '16'); INSERT INTO CONTACT VALUES('BUZZ', 'TWITTER', 'BUZZ01'); INSERT INTO CONTACT VALUES('CHRISTINA', 'FACEBOOK', 'CG'); INSERT INTO CONTACT VALUES('CHRISTINA', 'SKYPE', 'CGG'); INSERT INTO CONTACT VALUES('JOSHUA', 'TWITTER', 'JAMD') INSERT INTO CONTACT VALUES('NIKKI', 'MYB', 'NH'); COMMIT; DELETE FROM CONTACT WHERE NAME IN (SELECT NAME FROM PERSON WHERE NAME = CONTACT.NAME AND AGE > 18); UPDATE CONTACT SET SITE = "FB" WHERE SITE = "FACEBOOK"; COMMIT; SELECT * FROM CONTACT; -- NOTE THAT THE FOLLOWING 'TRANSLATION' DOES NOT CHECK FOR ERRORS. */ db, _ := database.Open(DBPath) PERSON, _ := db.Create("PERSON") PERSON.Add("NAME", 20) PERSON.Add("AGE", 2) CONTACT, _ := db.Create("CONTACT") CONTACT.Add("NAME", 20) CONTACT.Add("SITE", 20) CONTACT.Add("USERNAME", 40) tr := transaction.New(db) tr.Insert(PERSON, map[string]string{"NAME": "BUZZ", "AGE": "18"}) tr.Insert(PERSON, map[string]string{"NAME": "CHRISTINA", "AGE": "16"}) tr.Insert(PERSON, map[string]string{"NAME": "JOSHUA", "AGE": "21"}) tr.Insert(PERSON, map[string]string{"NAME": "NIKKI", "AGE": "16"}) tr.Insert(CONTACT, map[string]string{"NAME": "JOSHUA", "SITE": "TWITTER", "USERNAME": "******"}) tr.Insert(CONTACT, map[string]string{"NAME": "NIKKI", "SITE": "MYB", "USERNAME": "******"}) tr.Insert(CONTACT, map[string]string{"NAME": "BUZZ", "SITE": "TWITTER", "USERNAME": "******"}) tr.Insert(CONTACT, map[string]string{"NAME": "CHRISTINA", "SITE": "FACEBOOK", "USERNAME": "******"}) tr.Insert(CONTACT, map[string]string{"NAME": "CHRISTINA", "SITE": "SKYPE", "USERNAME": "******"}) tr.Commit() tr.ELock(PERSON) tr.ELock(CONTACT) query := ra.New() query.Load(PERSON) query.NLJoin("NAME", CONTACT, "NAME") query.Select("AGE", filter.Gt{}, 18) query.Project("SITE", "USERNAME") contactResult, _ := query.Table("CONTACT") for _, i := range contactResult.RowNumbers { tr.Delete(CONTACT, i) } rows, _ := CONTACT.SelectAll() for _, row := range rows { fmt.Println(row) } query2 := ra.New() query2.Load(CONTACT) query2.Select("SITE", filter.Eq{}, "FACEBOOK") contactResult, _ = query2.Table("CONTACT") for _, i := range contactResult.RowNumbers { fmt.Println("Updating", i) tr.Update(CONTACT, i, map[string]string{"SITE": "FB"}) } tr.Commit() rows, _ = CONTACT.SelectAll() for _, row := range rows { fmt.Println(row) } }
// Handle query. func Eg7() { /* CREATE TABLE PERSON ( NAME CHAR(20), AGE CHAR(2) ); CREATE TABLE CONTACT ( NAME CHAR(20), SITE CHAR(20), USERNAME CHAR(40) ); INSERT INTO PERSON VALUES('BUZZ', '18'); INSERT INTO PERSON VALUES('CHRISTINA', '16'); INSERT INTO PERSON VALUES('JOSHUA', '21'); INSERT INTO PERSON VALUES('NIKKI', '16'); INSERT INTO CONTACT VALUES('BUZZ', 'TWITTER', 'BUZZ01'); INSERT INTO CONTACT VALUES('CHRISTINA', 'FACEBOOK', 'CG'); INSERT INTO CONTACT VALUES('CHRISTINA', 'SKYPE', 'CGG'); INSERT INTO CONTACT VALUES('JOSHUA', 'TWITTER', 'JAMD') INSERT INTO CONTACT VALUES('NIKKI', 'MYB', 'NH'); COMMIT; SELECT SITE, USERNAME FROM CONTACT, PERSON WHERE PERSON.NAME = CONTACT.NAME AND PERSON.AGE > 17; -- NOTE THAT THE FOLLOWING 'TRANSLATION' DOES NOT CHECK FOR ERRORS. */ db, _ := database.Open(DBPath) PERSON, _ := db.Create("PERSON") PERSON.Add("NAME", 20) PERSON.Add("AGE", 2) CONTACT, _ := db.Create("CONTACT") CONTACT.Add("NAME", 20) CONTACT.Add("SITE", 20) CONTACT.Add("USERNAME", 40) tr := transaction.New(db) tr.Insert(PERSON, map[string]string{"NAME": "BUZZ", "AGE": "18"}) tr.Insert(PERSON, map[string]string{"NAME": "CHRISTINA", "AGE": "16"}) tr.Insert(PERSON, map[string]string{"NAME": "JOSHUA", "AGE": "21"}) tr.Insert(PERSON, map[string]string{"NAME": "NIKKI", "AGE": "16"}) tr.Insert(CONTACT, map[string]string{"NAME": "BUZZ", "SITE": "TWITTER", "USERNAME": "******"}) tr.Insert(CONTACT, map[string]string{"NAME": "CHRISTINA", "SITE": "FACEBOOK", "USERNAME": "******"}) tr.Insert(CONTACT, map[string]string{"NAME": "CHRISTINA", "SITE": "SKYPE", "USERNAME": "******"}) tr.Insert(CONTACT, map[string]string{"NAME": "JOSHUA", "SITE": "TWITTER", "USERNAME": "******"}) tr.Insert(CONTACT, map[string]string{"NAME": "NIKKI", "SITE": "MYB", "USERNAME": "******"}) tr.Commit() tr.SLock(PERSON) tr.SLock(CONTACT) query := ra.New() query.Load(PERSON) query.NLJoin("NAME", CONTACT, "NAME") query.Select("AGE", filter.Gt{}, 17) query.Project("SITE", "USERNAME") for i := 0; i < query.NumberOfRows(); i++ { row, status := query.Read(i) fmt.Println(row, status) } tr.Commit() }