示例#1
0
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
}
示例#2
0
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
}
示例#3
0
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
}
示例#4
0
// 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})
}
示例#5
0
// 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"})
}
示例#6
0
// 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)
	}
}
示例#7
0
// 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()
}