# project-4

**Repository Path**: yujieloong/project-4

## Basic Information

- **Project Name**: project-4
- **Description**: No description available
- **Primary Language**: Python
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 10
- **Created**: 2022-06-10
- **Last Updated**: 2022-06-17

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 面向对象课程-期末作业（项目4：数据库）

21215090 龙宇杰
<br>

## 1. 封装增、删、改三个功能接口

### 1.1 在数据表中插入数据
接口：
```python  
def run_app():
	with db.transaction(db_id='2018年各校招生计划', 
						user_secrets='12345678') as ctx:
		学校 = ctx.学校
		省份 = ctx.省份
		查询结果 = ctx.SQL(
			select=[学校.名称,学校.类型],
			where=db.AND(
				学校.是否985 == True,
				省份.列_211高校数量 > 1,
			)
		)
		ctx.INSERT(
			into = "学校",
			columns = ["id", "名称", "所在省份", "类型", "是否985"],
			values = [["item_enterprise_2_16","中山大学","广东","综合",True],
					["item_enterprise_2_17","暨南大学","广东","综合",False]]
		)
		ctx._commit_transaction()
```  
接口实现：
```python  
    def INSERT(self, into=None, columns = None, values = None):
		assert hasattr(self,into), '数据库里没有该表格！'
		table = getattr(self, into)
		table_content = self._read_table(into)
		if len(table_content["primary_keys"]) != 0:
			p_key = table_content["primary_keys"][0]
			assert p_key in columns, '没有主键对应的值！'
		for value in values:
			full_value = { c.column_name:None for c in table.columns }
			for c_name,v in zip(columns,value):
				if c_name == p_key:
					c = getattr(table, p_key)
					assert v not in c.column_data, '主键值重复！'
				assert c_name in full_value, '没有对应列！'
				full_value[c_name] = v
			new_value = [ v for _,v in full_value.items()]
			table_content["rows"].append(new_value)
		# print(table_content)
		self._write_table(into,table_content)
		return True
```  
结果截图：
![insert_result](insert.png)


### 1.2 在数据表中修改条目
接口：
```python  
def run_app():
	with db.transaction(db_id='2018年各校招生计划', 
						user_secrets='12345678') as ctx:
		学校 = ctx.学校
		省份 = ctx.省份
		查询结果 = ctx.SQL(
			select=[学校.名称,学校.类型],
			where=db.AND(
				学校.是否985 == True,
				省份.列_211高校数量 > 1,
			)
		)
		ctx.INSERT(
			into = "学校",
			columns = ["id", "名称", "所在省份", "类型", "是否985"],
			values = [["item_enterprise_2_16","中山大学","广东","综合",True],
					["item_enterprise_2_17","暨南大学","广东","综合",False]]
		)
		ctx.UPDATE(
			table_name = "学校",
			set_data = {"是否211":True},
			where=db.AND(
				学校.所在省份 == "广东",
			)
		)
		ctx._commit_transaction()
```  
接口实现：
```python  
    def UPDATE(self, table_name="", set_data=None, where=None):
		assert hasattr(self,table_name), '数据库里没有该表格！'
		if where is not None:
			assert isinstance(where, (AND,)), 'where类型错误!'
			for op in where.ops:
				if isinstance(op, (EQ,GT)):
					assert isinstance(op.left, Column) ,'算子左值类型错误！'
		table = getattr(self, table_name)
		table._read()
		table_content = self._read_table(table_name)
		if where is None:
			w = [True for i in range(len(table))]
		else:
			w = where.eval(table)
		if len(table_content["primary_keys"]) != 0:
			p_key = table_content["primary_keys"][0]
		else: p_key = None
		for i in range(len(w)):
			if w[i]:
				for k, v in set_data.items():
					assert hasattr(table, k), '没有对应列！'
					if k == p_key:
						c = getattr(table, p_key)
						if v != c.column_data[i]:
							assert v not in c.column_data, '主键不能重复！'
					for j in range(len(table.columns)):
						if k == table.columns[j].column_name:
							index = j
					table_content["rows"][i][index] = v
		print(table_content)
		self._write_table(table_name,table_content)
		return True
```  
结果截图：
![update_result](update.png)

### 1.3 删除对应条目
接口：
```python  
def run_app():
	with db.transaction(db_id='2018年各校招生计划', 
						user_secrets='12345678') as ctx:
		学校 = ctx.学校
		省份 = ctx.省份
		查询结果 = ctx.SQL(
			select=[学校.名称,学校.类型],
			where=db.AND(
				学校.是否985 == True,
				省份.列_211高校数量 > 1,
			)
		)
		ctx.INSERT(
			into = "学校",
			columns = ["id", "名称", "所在省份", "类型", "是否985"],
			values = [["item_enterprise_2_16","中山大学","广东","综合",True],
					["item_enterprise_2_17","暨南大学","广东","综合",False]]
		)
		ctx.UPDATE(
			table_name = "学校",
			set_data = {"是否211":True},
			where=db.AND(
				学校.所在省份 == "广东",
			)
		)
		ctx.DELETE(
			table_name="学校",
			where=db.AND(
		 		学校.名称 == "暨南大学",
			)
		)
		ctx._commit_transaction()
```  
接口实现：
```python  
    def DELETE(self, table_name="", where=None):
		assert hasattr(self,table_name), '数据库里没有该表格！'
		if where is not None:
			assert isinstance(where, (AND,)), 'where类型错误!'
			for op in where.ops:
				if isinstance(op, (EQ,GT)):
					assert isinstance(op.left, Column) ,'算子左值类型错误！'
		table = getattr(self, table_name)
		table._read()
		table_content = self._read_table(table_name)
		if where is None:
			w = [True for i in range(len(table))]
		else:
			w = where.eval(table)
		for i in range(len(w)):
			if w[i]:
				table_content["rows"].pop(i)
		print(table_content)
		self._write_table(table_name,table_content)
		return True
```  
结果截图：
![delete_result](delete.png)

### 1.4 bug
可能是js环境下python库不支持__exit__这个钩子函数的功能。不手动commit的话，在with...as...的上下文环境中，无法触发后台的commit函数。
![bug](bug.png)
<br>

使用异常机制也照样无法执行else（即finally）部分的语句。

```python  
def run_app():
	try:
		ctx = db.transaction(db_id='2018年各校招生计划', user_secrets='12345678')
		学校 = ctx.学校
		省份 = ctx.省份
		查询结果 = ctx.SQL(
			select=[学校.名称,学校.类型],
			where=db.AND(
				学校.是否985 == True,
				省份.列_211高校数量 > 1,
			)
		)
		ctx.INSERT(
			into = "学校",
			columns = ["id", "名称", "所在省份", "类型", "是否985"],
			values = [["item_enterprise_2_16","中山大学","广东","综合",True],
					["item_enterprise_2_17","暨南大学","广东","综合",False]]
		)
	except:
		ctx._abort_transaction()
		print("transaction execute ERROR!")
	else:
		print("commit tansaction!")
		print("ctx:",ctx)
		if not (ctx.transaction_id is None):
			ctx._commit_transaction()
    ......

class transaction:
	def __init__(self, db_id, user_secrets, table_users=None):
		self.transaction_id = ajax.rpc.create_transaction(user_secrets, db_id, table_users)
		self.db_id = db_id
		self.user_secrets = user_secrets
		self.table_users = table_users
		self.tables = []
		# 使用test_run()使用异常机制时，需要在这里执行__enter__()函数
		self.__enter__() 
    
    ......
```  

## 2. 乐观事务处理

老师实现的原始版本乐观事务处理是有bug的。每次写的结果都保存到_transaction_writes这个数据结构里面。而每次读都是从本地文件进行读。事务可能有很多各种各样的SQL语句组成，它们有时是前后因果依赖的。因此同一事务中，必须读到最新写入的值。所以每个事务，需要对它操作的表维护一个内存中的副本_transaction_cache。

代码改动如下:

```python  
_transaction_cache = {}

def read_table(user_secrets, transaction_id, db_id, table_name):
	assert user_secrets==_user_secrets
	_transaction_global_lock.acquire()
	table_writes = _transaction_table_writes.get((db_id, table_name), [])
	timestamp = 0 if len(table_writes)==0 else table_writes[-1]
	reads = _transaction_reads.get(transaction_id, [])
	reads.append((db_id, table_name, timestamp))
	_transaction_reads[transaction_id] = reads
	# read
	total_cache = _transaction_cache.get(transaction_id,{})
	table_cache = total_cache.get((db_id, table_name), {})
	if table_cache is None:
		with open(os.path.join('db_files', db_id, f'{table_name}.json'), encoding="utf-8") as fp:
			data = json.load(fp)
			total_cache[(db_id, table_name)] = data
			_transaction_cache[transaction_id] = total_cache
	else:
		data = table_cache
	_transaction_global_lock.release()
	return data

def write_table(user_secrets, transaction_id, db_id, table_name, table_data):
	print("Enter write_table()")
	print("db_id:",db_id,", table_name",table_name,", table_data:\n",table_data)
	assert user_secrets==_user_secrets
	_transaction_global_lock.acquire()
	# 保存时间戳就好，更改保存到_transaction_cache里
	table_writes = _transaction_table_writes.get((db_id, table_name), [])
	timestamp = 0 if len(table_writes)==0 else table_writes[-1]
	writes = _transaction_writes.get(transaction_id, [])
	writes.append((db_id, table_name, timestamp))
	_transaction_writes[transaction_id] = writes
	# 更新保存到缓存里
	total_cache = _transaction_cache.get(transaction_id,{})
	total_cache[(db_id, table_name)] = table_data
	_transaction_cache[transaction_id] = total_cache
	_transaction_global_lock.release()

def commit_transaction(user_secrets, transaction_id):
	print("Enter commit_transaction()")
	assert user_secrets==_user_secrets
	succ = True
	_transaction_global_lock.acquire()
	reads = _transaction_reads.get(transaction_id, [])
	for (db_id, table_name, timestamp) in reads:
		table_writes = _transaction_table_writes.get((db_id, table_name), [])
		if len(table_writes)>0 and table_writes[-1]>timestamp:
			succ = False
	if succ:
		for (db_id, table_name, timestamp) in _transaction_writes[transaction_id]:
			table_writes = _transaction_table_writes.get((db_id, table_name), [])
			print("table_writes for ",(db_id, table_name)," is:",table_writes)
			if len(table_writes)>0 and table_writes[-1]>timestamp:
				succ = False
				_transaction_global_lock.release()
				return succ
			new_timestamp = 1 if len(table_writes)==0 else table_writes[-1] + 1
			table_writes.append(new_timestamp)
			print("After update,table_writes is :",table_writes)
			# 更新写时间戳
			_transaction_table_writes[(db_id, table_name)] = table_writes
		# 永久保存到本地
		total_cache = _transaction_cache.get(transaction_id,{})
		for k, table_data in total_cache.items():
			with open(os.path.join('db_files', k[0], f'{k[1]}.json'), 'w', encoding="utf-8") as fp:
				json.dump(table_data, fp, indent=4, sort_keys=True, ensure_ascii=False)
	_transaction_global_lock.release()
	return succ
```