从零开发数据库系统(四)——创建表结构

1. 数据表实现

上节实现了对字符串的操作,这是我们对表和字段进行操作的前提。接下来我们来完成一个数据表的创建。

在此之前我们需要做一些准备:

  • 为数据库增加一个操作函数uint16_t ca_database_index(char* name)来通过数据库名称获取数据库的ID,具体实现如下:
uint16_t ca_database_index(char* name)
{
    FILE* file;
	fopen_s(&file, DB_FILE_NAME, "r+");
	if (file == NULL)
		return 0;
	fseek(file, 0, SEEK_END);
	int fileSize = ftell(file);
	ca_database_data_t item;
	for (int i = 0; i < fileSize; i += CA_SIZE_DATABASE_DATA)
	{
		fseek(file, i, SEEK_SET);
		fread_s(&item, CA_SIZE_DATABASE_DATA, CA_SIZE_DATABASE_DATA, 1, file);
		if (!strcmp(item.db_name, name))
		{
			fclose(file);
			return item.db_id;
		}
	}

	fclose(file);
	return 0;
}
  • 为数据表增加一个操作函数uint16_t ca_table_index(char* name)来通过数据表名称获取数据表的ID,具体实现如下:
uint16_t ca_table_index(uint16_t db_id, char* name)
{
	FILE* file;
	fopen_s(&file, TB_FILE_NAME, "r+");
	if (file == NULL)
		return NULL;
	fseek(file, 0, SEEK_END);
	long fileSize = ftell(file);
	ca_table_data_t item;
	for (long i = 0; i < fileSize; i += CA_SIZE_TABLE_DATA)
	{
		fseek(file, i, SEEK_SET);
		fread(&item, CA_SIZE_TABLE_DATA, 1, file);
		if (!strcmp(item.tb_name, name) && item.db_id == db_id)
		{
			fclose(file);
			return item.tb_id;
		}
	}

	fclose(file);
	return 0;
}
  • 为数据表增加一个设置字段偏移量的操作函数int ca_table_set_column(uint16_t tb_id, uint16_t col_offset, uint16_t order),具体实现如下:
int ca_table_set_column(uint16_t tb_id, uint16_t col_offset, uint16_t order)
{
	FILE* file;
	fopen_s(&file, TB_FILE_NAME, "r+");
	if (file == NULL)
		return NULL;
	fseek(file, 0, SEEK_END);
	long fileSize = ftell(file);
	ca_table_data_t item;
	for (long i = 0; i < fileSize; i += CA_SIZE_TABLE_DATA)
	{
		fseek(file, i, SEEK_SET);
		fread(&item, CA_SIZE_TABLE_DATA, 1, file);
		if (item.tb_id == tb_id) {
			item.column_index[order] = col_offset;
			fseek(file, i, SEEK_SET);
			if (fwrite(&item, CA_SIZE_TABLE_DATA, 1, file))
			{
				fclose(file);
				return CA_OK;
			}
		}
	}

	fclose(file);
	return CA_ERROR;
}

1. 表的操作函数实现

  • 创建表

在上节的设计中创建表时需要传入字段,经过实践发现字段和表有一种相互依赖的关系,有些矛盾,所以做了一下修改,在创建表时不需要传入字段数据了。

另外增加了db_id参数,让该函数可以通过数据库名称或者数据库ID来进行表创建。

ca_table_data_t* ca_table_create(
	uint8_t* db_name, uint16_t db_id, uint8_t* tb_name, uint8_t* comment, uint16_t type, uint16_t encoder)
{
	FILE* file;
	fopen_s(&file, TB_FILE_NAME, "r+");
	if (file == NULL)
		fopen_s(&file, TB_FILE_NAME, "w+");
	if (file == NULL)
		return NULL;
	
	ca_table_data_t* catb = malloc(sizeof(ca_table_data_t));
	if (catb == NULL)
	{
		fclose(file);
		return NULL;
	}
	// 获取文件大小
	fseek(file, 0, SEEK_END);
	long fileSize = ftell(file);
	// 查找表是否已经存在
	ca_table_data_t item;
	for (int i = 0; i < fileSize; i += CA_SIZE_TABLE_DATA) {
		fseek(file, i, SEEK_SET);
		fread(&item, CA_SIZE_TABLE_DATA, 1, file);
		if (!strcmp(item.tb_name, tb_name))
		{
			memcpy(catb, &item, CA_SIZE_TABLE_DATA);
			// 表已存在
			fclose(file);
			return catb;
		}
	}
	uint16_t tb_index = fileSize / CA_SIZE_TABLE_DATA + 1;
	uint16_t db_index;
	if (db_id == 0)
		db_index = ca_database_index(db_name);
	else
		db_index = db_id;

	if (db_index == 0)
	{
		// 没有找到指定的数据库
		fclose(file);
		free(catb);
		return NULL;
	}
	catb->db_id = db_index;
	catb->tb_id = tb_index;
	catb->tb_type = type;
	catb->tb_encoder = encoder;
	ca_string_t* cs_comment = ca_string_create(comment, encoder);
	if (cs_comment == NULL)
	{
		fclose(file);
		free(catb);
		return NULL;
	}
	catb->tb_comment = cs_comment->header.offset;
	strcpy_s(catb->tb_name, L_TB_NAME, tb_name);
	memset(catb->column_index, UINT32_MAX, sizeof(catb->column_index));
	
	fseek(file, 0, SEEK_END);
	if (!fwrite(catb, CA_SIZE_TABLE_DATA, 1, file))
	{
		free(catb);
		fclose(file);
		return NULL;
	}
	fclose(file);
	return catb;
}
  • 删除表

因为在不同的数据库下可以出现名字相同的表,因此在这里做一下区分,后面的函数也都增加了uint16_t db_id参数。

int ca_table_drop(uint16_t db_id, uint8_t* tb_name)
{
	FILE* file;
	fopen_s(&file, TB_FILE_NAME, "r+");
	if (file == NULL)
		return NULL;
	uint8_t empty[CA_SIZE_TABLE_DATA] = { 0 };
	// 获取文件大小
	fseek(file, 0, SEEK_END);
	long fileSize = ftell(file);
	// 查找表
	ca_table_data_t item;
	for (int i = 0; i < fileSize; i += CA_SIZE_TABLE_DATA) {
		fseek(file, i, SEEK_SET);
		fread(&item, CA_SIZE_TABLE_DATA, 1, file);
		if (!strcmp(item.tb_name, tb_name) && item.db_id == db_id)
		{
			fseek(file, i, SEEK_SET);
			fwrite(empty, CA_SIZE_TABLE_DATA, 1, file);
			fclose(file);
			return CA_OK;
		}
	}

	fclose(file);
	return CA_OK;
}
  • 重命名表
int ca_table_rename(uint16_t db_id, uint8_t* tb_old_name, uint8_t* tb_new_name)
{
	FILE* file;
	fopen_s(&file, TB_FILE_NAME, "r+");
	if (file == NULL)
		return NULL;

	// 获取文件大小
	fseek(file, 0, SEEK_END);
	long fileSize = ftell(file);
	// 查找表是否已经存在
	ca_table_data_t item;
	for (int i = 0; i < fileSize; i += CA_SIZE_TABLE_DATA) {
		fseek(file, i, SEEK_SET);
		fread(&item, CA_SIZE_TABLE_DATA, 1, file);
		if (!strcmp(item.tb_name, tb_old_name) && item.db_id == db_id)
		{
			strcpy_s(item.tb_name, L_TB_NAME, tb_new_name);
			fwrite(&item, CA_SIZE_TABLE_DATA, 1, file);
			fclose(file);
			return CA_OK;
		}
	}

	fclose(file);
	return CA_OK;
}
  • 获取表字段数量
int ca_table_column_count(uint16_t db_id, uint8_t* tb_name)
{
	FILE* file;
	fopen_s(&file, TB_FILE_NAME, "r+");
	if (file == NULL)
		return NULL;
	fseek(file, 0, SEEK_END);
	long fileSize = ftell(file);
	ca_table_data_t item;
	for (long i = 0; i < fileSize; i += CA_SIZE_TABLE_DATA)
	{
		fseek(file, i, SEEK_SET);
		fread(&item, CA_SIZE_TABLE_DATA, 1, file);
		if (!strcmp(item.tb_name, tb_name) && item.db_id == db_id)
		{
			uint32_t c;
			for (c = 0; c < N_MAX_COL; c++)
				if (c == UINT32_MAX) break;
			fclose(file);
			return c;
		}
	}

	fclose(file);
	return 0;
}

2. 表字段操作函数实现

  • 创建字段
// 创建字段
ca_column_data_t* ca_column_create(
	uint8_t* tb_name, uint16_t tb_id, uint8_t* col_name, uint8_t* comment, uint8_t* default_value,
	uint16_t type, uint16_t check, uint16_t encoder,
	uint32_t length, uint16_t order)
{
	FILE* file;
	fopen_s(&file, COL_FILE_NAME, "r+");
	if (file == NULL)
		fopen_s(&file, COL_FILE_NAME, "w+");
	if (file == NULL)
		return NULL;

	fseek(file, 0, SEEK_END);
	long fileSize = ftell(file);
	// 检测重复并检测插入位置
	uint32_t col_offset = 0;
	if (tb_id == 0)
		tb_id = ca_table_index(tb_name);
	ca_column_data_t item;
	for (long i = 0; i < fileSize; i += CA_SIZE_COLUMN_DATA)
	{
		fseek(file, i, SEEK_SET);
		fread(&item, CA_SIZE_COLUMN_DATA, 1, file);
		if (item.tb_id == tb_id && !strcmp(item.col_name, col_name))
		{
			fclose(file);
			return NULL;
		}
		if (item.tb_id == 0 && item.col_id == 0 && col_offset == 0)
		{
			col_offset = i;
		}
	}

	ca_column_data_t* ca_col = malloc(CA_SIZE_COLUMN_DATA);
	if (ca_col == NULL)
	{
		fclose(file);
		return NULL;
	}

	ca_col->tb_id = tb_id;
	ca_col->col_id = fileSize / CA_SIZE_COLUMN_DATA + 1;
	ca_col->col_order = order;
	ca_col->col_type = type;
	ca_col->col_check = check;
	ca_col->col_length = length;

	ca_string_t* col_default = NULL;
	if (default_value)
	{
		ca_string_t* col_default = ca_string_create(default_value, encoder);
		if (col_default == NULL)
		{
			fclose(file);
			ca_string_destroy(col_default);
			return NULL;
		}
		ca_col->col_default = col_default->header.offset;
	}
	ca_string_t* col_comment = NULL;
	if (comment)
	{
		col_comment = ca_string_create(comment, encoder);
		if (col_comment == NULL)
		{
			fclose(file);
			ca_string_destroy(col_comment);
			return NULL;
		}
		ca_col->col_comment = col_comment->header.offset;
	}
	strcpy_s(ca_col->col_name, L_COL_NAME, col_name);
	// 插入数据
	if (col_offset > 0)
	{
		fseek(file, col_offset, SEEK_SET);
		ca_col->col_offset = col_offset;
	}
	else
	{
		fseek(file, 0, SEEK_END);
		ca_col->col_offset = ftell(file);
	}
	if (!fwrite(ca_col, CA_SIZE_COLUMN_DATA, 1, file))
	{
		if (col_default) ca_string_drop(col_default->header.offset);
		if (col_comment) ca_string_drop(col_comment->header.offset);
		fclose(file);
		return NULL;
	}
	// 更新表文件的字段数据
	return ca_table_set_column(tb_id, ca_col->col_offset, ca_col->col_order);
}
  • 删除字段
int ca_column_drop(uint16_t db_id, uint8_t* tb_name, uint16_t tb_id, uint8_t* col_name)
{
	FILE* file;
	fopen_s(&file, COL_FILE_NAME, "r+");
	if (file == NULL)
		return NULL;
	uint8_t empty[CA_SIZE_COLUMN_DATA] = { 0 };
	fseek(file, 0, SEEK_END);
	long fileSize = ftell(file);
	if (tb_id == 0)
		tb_id = ca_table_index(db_id, tb_name);
	if (tb_id == 0)
	{
		fclose(file);
		return CA_ERROR;
	}
	ca_column_data_t item;
	for (long i = 0; i < fileSize; i += CA_SIZE_COLUMN_DATA)
	{
		fseek(file, i, SEEK_SET);
		fread(&item, CA_SIZE_COLUMN_DATA, 1, file);
		if (item.tb_id == tb_id && !strcmp(item.col_name, col_name))
		{
			fseek(file, i, SEEK_SET);
			if (fwrite(empty, CA_SIZE_COLUMN_DATA, 1, file))
			{
				fclose(file);
				return CA_OK;
			}
			
		}
	}
	fclose(file);
	return CA_ERROR;
}
  • 重命名字段
// 重命名字段
int ca_column_rename(uint16_t db_id, uint8_t* tb_name, uint16_t tb_id, uint8_t* col_old_name, uint8_t* col_new_name)
{
	FILE* file;
	fopen_s(&file, COL_FILE_NAME, "r+");
	if (file == NULL)
		return NULL;
	fseek(file, 0, SEEK_END);
	long fileSize = ftell(file);
	if (tb_id == 0)
		tb_id = ca_table_index(db_id, tb_name);
	if (tb_id == 0)
	{
		fclose(file);
		return CA_ERROR;
	}
	ca_column_data_t item;
	for (long i = 0; i < fileSize; i += CA_SIZE_COLUMN_DATA)
	{
		fseek(file, i, SEEK_SET);
		fread(&item, CA_SIZE_COLUMN_DATA, 1, file);
		if (item.tb_id == tb_id && !strcmp(item.col_name, col_old_name))
		{
			fseek(file, i, SEEK_SET);
			strcpy_s(item.col_name, L_COL_NAME, col_new_name);
			if (fwrite(&item, CA_SIZE_COLUMN_DATA, 1, file))
			{
				fclose(file);
				return CA_OK;
			}
		}
	}
	fclose(file);
	return CA_ERROR;
}

2. 测试创建表

为了名字整齐,我把这些文件名字进行了调整

#define DB_FILE_NAME    "cadb.database"
#define STR_FILE_NAME    "cadb.string"
#define TB_FILE_NAME	"cadb.table"
#define COL_FILE_NAME	"cadb.column"

代码

int main()
{
	char db_name[] = "test2", tb_name[] = "test1";
	ca_table_data_t* ca_tb = ca_table_create("test2", "test1", "Test Table Comment", 0, CA_ENCODER_UTF8);
	ca_column_data_t* ca_column = ca_column_create(NULL, ca_tb->tb_id, "col1", "Test Create Column!", "Hello,World!",
		0, CA_CLCK_NUIQUE | CA_CLCK_NULL, CA_ENCODER_UTF8, 20, 0);
	return 0;
}

使用hexdump查看文件内容

  • cadb.string
000000  0d 00 00 00 00 00 00 00 48 65 6c 6c 6f 2c 57 6f  ........Hello,Wo
000010  72 6c 64 21 00 0e 00 00 00 15 00 00 00 48 65 6c  rld!.........Hel
000020  6c 6f 2c 20 57 6f 72 6c 64 21 00 1c 00 00 00 2b  lo, World!.....+
000030  00 00 00 54 65 73 74 20 44 61 74 61 62 61 73 65  ...Test Database
000040  20 53 74 72 69 6e 67 20 54 61 62 6c 65 21 00 13   String Table!..
000050  00 00 00 4f 00 00 00 54 65 73 74 20 54 61 62 6c  ...O...Test Tabl
000060  65 20 43 6f 6d 6d 65 6e 74 00 14 00 00 00 6a 00  e Comment.....j.
000070  00 00 54 65 73 74 20 43 72 65 61 74 65 20 43 6f  ..Test Create Co
000080  6c 75 6d 6e 21 00                                lumn!.
  • cadb.table
000000  02 00 01 00 00 00 00 00 4f 00 00 00 74 65 73 74  ........O...test
000010  31 00 fe fe fe fe fe fe fe fe fe fe fe fe fe fe  1...............
000020  fe fe fe fe fe fe fe fe fe fe fe fe fe fe fe fe  ................
000030  fe fe fe fe fe fe fe fe fe fe fe fe fe fe fe fe  ................
000040  fe fe fe fe fe fe fe fe fe fe fe fe 00 00 00 00  ................
000050  ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff  ................
......
0001f0  ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff  ................
  • cadb.column
000000  01 00 01 00 00 00 00 00 00 00 00 00 06 00 14 00  ................
000010  00 00 00 00 6a 00 00 00 63 6f 6c 31 00 fe fe fe  ....j...col1....
000020  fe fe fe fe fe fe fe fe fe fe fe fe fe fe fe fe  ................
000030  fe fe fe fe fe fe fe fe fe fe fe fe fe fe fe fe  ................
000040  fe fe fe fe fe fe fe fe fe fe fe fe fe fe fe fe  ................
000050  fe fe fe fe fe fe fe fe cd cd cd cd cd cd cd cd  ................

下一节,我们来实现通过数据库遍历表以及通过表遍历字段。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值