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) 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) 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()
}