闲来无事，写个数据库吧（5）

已于 2022-04-23 20:09:35 修改
阅读量657 收藏
点赞数
分类专栏： sql 文章标签： 数据库 数据库开发
于 2022-04-23 16:33:37 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_51275728/article/details/124361699
版权

项目地址 How Does a Database Work? | Let’s Build a Simple Database (cstack.github.io)

我们接下来用到了rspec语言，官网RSpec: Behaviour Driven Development for Ruby

但安装我不太会，这里借鉴简单数据库实现——Part4 - 初次测试_RadiumYang的博客-CSDN博客

我是用kali安装的，首先得退出root账号，不然报错。

然后输入

sudo apt install bundler

可以用bundler -v检测是否安装。

然后创建Gemfile

nano Gemfile

写入内容

source "https://gems.ruby-china.com"
gem 'rspec', '~> 3.0'

ctrl+x然后按y退出，接着初始化

bundle install --binstubs
bin/rspec --init
cat .rspec

我将定义一个简短的帮助程序，将命令列表发送到我们的数据库程序，然后对输出进行断言，

新建一个文件夹spec

mkdir spec

输入我们的断言程序并保存。注意文件名以_spec.rb结尾


describe 'database' do
  def run_script(commands)
    raw_output = nil
    IO.popen("./db", "r+") do |pipe|
      commands.each do |command|
        pipe.puts command
      end

      pipe.close_write

      # Read entire output
      raw_output = pipe.gets(nil)
    end
    raw_output.split("\n")
  end


  it 'inserts and retrieves a row' do
    result = run_script([
      "insert 1 user1 person1@example.com",
      "select",
      ".exit",
    ])
    expect(result).to match_array([
      "sqlite> Executed.",
      "sqlite> (1, user1, person1@example.com)",
      "Executed.",
      "sqlite> ",
    ])
  end
 end

这个断言程序的意思是打开db文件，输入

insert 1 user1 person1@example.com
select
.exit

期望结果会是

sqlite> Executed.
sqlite> (1, user1, person1@example.com)
Executed.
sqlite>

注意db文件在spec的上一层。输入

bundle exec rspec

我们来看看结果

插1条正确还不够，我们要开始测试大量插入数据。理论上我们只能插入1400数据，原理是这样的：每一行的长度是291bytes（row_size）,故每一页可以装14行（4096/291=14）,一共100页，故能装1400行。但我的和他不一样，最多能插1529条数据(这里插1529会满，但插1528条不会，故能插原来的1条+1528条=1529条)。我也不知道为什么我能多装一点。(注意这段语句输入在ruby文件的最后一个end的上面。)

it 'prints error message when table is full' do
  script = (1..1529).map do |i|
    "insert #{i} user#{i} person#{i}@example.com"
  end
  script << ".exit"
  result = run_script(script)
  expect(result[-2]).to eq('sqlite> Error: Table full.')
end

以下是运行情况

既然存储空间能满，那么我们能否把username存储32个字节，把email存储255个字节呢？于是我们进行测试。

it 'allows inserting strings that are the maximum length' do
  long_username = "a"*32
  long_email = "a"*255
  script = [
    "insert 1 #{long_username} #{long_email}",
    "select",
    ".exit",
  ]
  result = run_script(script)
  expect(result).to match_array([
    "sqlite> Executed.",
    "sqlite> (1, #{long_username}, #{long_email})",
    "Executed.",
    "sqlite> ",
  ])
end

报错了!并且看报错可能还看不出来，感觉预期和实际一模一样，怎么错的呢？它找茬是吧？

 分析原因：我们会为用户名分配 32 个字节，为电子邮件分配 255 个字节。但是 C 字符串应该以空字符结尾，我们没有为其分配空间。解决方案是分配一个额外的字节：

 这样就行了。

那我们在判断输入的地方是不是也得做些检查呢？还有，我把prepare_statement写的那么臃肿，判断select写得那么差，现在我们来改一改。保证参数个数正确，且不会出现缓冲区溢出。

PrepareResult prepare_insert(InputBuffer* input_buffer, Statement* statement) {
  statement->type = STATEMENT_INSERT;

  char* keyword = strtok(input_buffer->buffer, " ");
  char* id_string = strtok(NULL, " ");
  char* username = strtok(NULL, " ");
  char* email = strtok(NULL, " ");
  char* check=strtok(NULL," ");
  if (id_string == NULL || username == NULL || email == NULL||check!=NULL) {
    return PREPARE_SYNTAX_ERROR;
  }

  int id = atoi(id_string);
  if (strlen(username) > COLUMN_USERNAME_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }
  if (strlen(email) > COLUMN_EMAIL_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }

  statement->row_to_insert.id = id;
  strcpy(statement->row_to_insert.username, username);
  strcpy(statement->row_to_insert.email, email);

  return PREPARE_SUCCESS;
}

PrepareResult prepare_select(InputBuffer* input_buffer,Statement* statement){
	statement->type=STATEMENT_SELECT;
	
	char *keyword =strtok(input_buffer->buffer," ");
	char * args=strtok(NULL," ");
	char* check=strtok(NULL," ");
	if(check!=NULL){
		return PREPARE_SYNTAX_ERROR;
	}
	if(args==NULL){
		statement->row_to_select=ALL;
		return PREPARE_SUCCESS;
	}
	if(strcmp(args,"id")==0){
		statement->row_to_select=ID;
	}
	else if(strcmp(args,"username")==0){
		statement->row_to_select=USERNAME;
	}
	else if(strcmp(args,"email")==0){
		statement->row_to_select=EMAIL;
	}
	else return PREPARE_SYNTAX_ERROR;
	
	return PREPARE_SUCCESS;
}

PrepareResult prepare_delete(InputBuffer* input_buffer,Statement* statement){
	statement->type = STATEMENT_DELETE;
	
	char* keyword = strtok(input_buffer->buffer, " ");
    char* id_string = strtok(NULL, " ");
    char* username = strtok(NULL, " ");
    char* email = strtok(NULL, " ");
    char* check=strtok(NULL," ");
	if(check!=NULL){
		return PREPARE_SYNTAX_ERROR;
	}
    if (id_string == NULL || username == NULL || email == NULL) {
    return PREPARE_SYNTAX_ERROR;
    }

    int id = atoi(id_string);
    if (strlen(username) > COLUMN_USERNAME_SIZE) {
      return PREPARE_STRING_TOO_LONG;
    }
    if (strlen(email) > COLUMN_EMAIL_SIZE) {
      return PREPARE_STRING_TOO_LONG;
    }

    statement->row_to_delete.id = id;
    strcpy(statement->row_to_delete.username, username);
    strcpy(statement->row_to_delete.email, email);

    return PREPARE_SUCCESS;
}

PrepareResult prepare_update(InputBuffer* input_buffer,Statement* statement){
    statement->type = STATEMENT_UPDATE;
    char* keyword = strtok(input_buffer->buffer, " ");
    char* id_string_old = strtok(NULL, " ");
    char* username_old = strtok(NULL, " ");
    char* email_old = strtok(NULL, " ");
    char* id_string_new = strtok(NULL, " ");
    char* username_new = strtok(NULL, " ");
    char* email_new = strtok(NULL, " ");
    char* check=strtok(NULL," ");
	if(check!=NULL){
		return PREPARE_SYNTAX_ERROR;
	}
    if (id_string_old == NULL || username_old == NULL || email_old == NULL) {
    return PREPARE_SYNTAX_ERROR;
    }
    
    if (id_string_new == NULL || username_new == NULL || email_new == NULL) {
    return PREPARE_SYNTAX_ERROR;
    }
    
    int id_old = atoi(id_string_old);
    int id_new = atoi(id_string_new);
    if (strlen(username_old) > COLUMN_USERNAME_SIZE) {
      return PREPARE_STRING_TOO_LONG;
    }
    if (strlen(email_old) > COLUMN_EMAIL_SIZE) {
      return PREPARE_STRING_TOO_LONG;
    }
    if (strlen(username_new) > COLUMN_USERNAME_SIZE) {
      return PREPARE_STRING_TOO_LONG;
    }
    if (strlen(email_new) > COLUMN_EMAIL_SIZE) {
      return PREPARE_STRING_TOO_LONG;
    }

    statement->row_to_update_old.id = id_old;
    strcpy(statement->row_to_update_old.username, username_old);
    strcpy(statement->row_to_update_old.email, email_old);
    statement->row_to_update_new.id = id_new;
    strcpy(statement->row_to_update_new.username, username_new);
    strcpy(statement->row_to_update_new.email, email_new);
    return PREPARE_SUCCESS;
}

PrepareResult prepare_statement(InputBuffer* input_buffer,Statement* statement) {
   if (strncmp(input_buffer->buffer, "insert",6) == 0) {
    return prepare_insert(input_buffer, statement);
   }
   else if(strncmp(input_buffer->buffer,"select",6)==0){
    return prepare_select(input_buffer, statement);
   }
   else if(strncmp(input_buffer->buffer,"delete",6)==0){
   	 return prepare_delete(input_buffer, statement);  	   
   }  
   else if(strncmp(input_buffer->buffer,"update",6)==0){
   	 return prepare_update(input_buffer, statement);
   }
   else return  PREPARE_UNRECOGNIZED_STATEMENT;
}

我们刚才判断字符过长时用到了PREPARE_STRING_TOO_LONG,在定义及实现的地方补上说明。

typedef enum { 
PREPARE_SUCCESS, 
PREPARE_SYNTAX_ERROR,
PREPARE_STRING_TOO_LONG,
PREPARE_UNRECOGNIZED_STATEMENT
 } PrepareResult;
Statement statement;
    switch (prepare_statement(input_buffer, &statement)) {
      case (PREPARE_SUCCESS):
        break;
      case (PREPARE_STRING_TOO_LONG):
        printf("Error: String is too long.\n");
        continue;
      case (PREPARE_SYNTAX_ERROR):
      	printf("Error: Syntax error.\n");
		continue; 
      case (PREPARE_UNRECOGNIZED_STATEMENT):
        printf("Error: Unrecognized keyword at start of '%s'.\n",input_buffer->buffer);
        continue;
    }

 我们再加入一点内容：id必须大于零且不能重复。id大于零可以在预处理时判断出来，是否重复得在执行部分遍历所有行才能判断。先处理id大于零吧。先补定义。

typedef enum { 
PREPARE_SUCCESS, 
PREPARE_NEGATIVE_ID,
PREPARE_SYNTAX_ERROR,
PREPARE_STRING_TOO_LONG,
PREPARE_UNRECOGNIZED_STATEMENT
 } PrepareResult;

再补实现

PrepareResult prepare_insert(InputBuffer* input_buffer, Statement* statement) {
  statement->type = STATEMENT_INSERT;

  char* keyword = strtok(input_buffer->buffer, " ");
  char* id_string = strtok(NULL, " ");
  char* username = strtok(NULL, " ");
  char* email = strtok(NULL, " ");
  char* check=strtok(NULL," ");
  if (id_string == NULL || username == NULL || email == NULL||check!=NULL) {
    return PREPARE_SYNTAX_ERROR;
  }

  int id = atoi(id_string);
  if (id < 0) {
    return PREPARE_NEGATIVE_ID;
  }
  if (strlen(username) > COLUMN_USERNAME_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }
  if (strlen(email) > COLUMN_EMAIL_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }

  statement->row_to_insert.id = id;
  strcpy(statement->row_to_insert.username, username);
  strcpy(statement->row_to_insert.email, email);

  return PREPARE_SUCCESS;
}

PrepareResult prepare_update(InputBuffer* input_buffer,Statement* statement){
    statement->type = STATEMENT_UPDATE;
    char* keyword = strtok(input_buffer->buffer, " ");
    char* id_string_old = strtok(NULL, " ");
    char* username_old = strtok(NULL, " ");
    char* email_old = strtok(NULL, " ");
    char* id_string_new = strtok(NULL, " ");
    char* username_new = strtok(NULL, " ");
    char* email_new = strtok(NULL, " ");
    char* check=strtok(NULL," ");
	if(check!=NULL){
		return PREPARE_SYNTAX_ERROR;
	}
    if (id_string_old == NULL || username_old == NULL || email_old == NULL) {
    return PREPARE_SYNTAX_ERROR;
    }
    
    if (id_string_new == NULL || username_new == NULL || email_new == NULL) {
    return PREPARE_SYNTAX_ERROR;
    }
    
    int id_old = atoi(id_string_old);
    int id_new = atoi(id_string_new);
    if (id_new < 0) {
    return PREPARE_NEGATIVE_ID;
    }
    if (strlen(username_old) > COLUMN_USERNAME_SIZE) {
      return PREPARE_STRING_TOO_LONG;
    }
    if (strlen(email_old) > COLUMN_EMAIL_SIZE) {
      return PREPARE_STRING_TOO_LONG;
    }
    if (strlen(username_new) > COLUMN_USERNAME_SIZE) {
      return PREPARE_STRING_TOO_LONG;
    }
    if (strlen(email_new) > COLUMN_EMAIL_SIZE) {
      return PREPARE_STRING_TOO_LONG;
    }

    statement->row_to_update_old.id = id_old;
    strcpy(statement->row_to_update_old.username, username_old);
    strcpy(statement->row_to_update_old.email, email_old);
    statement->row_to_update_new.id = id_new;
    strcpy(statement->row_to_update_new.username, username_new);
    strcpy(statement->row_to_update_new.email, email_new);
    return PREPARE_SUCCESS;
}

再补输出

    switch (prepare_statement(input_buffer, &statement)) {
      case (PREPARE_SUCCESS):
        break;
      case (PREPARE_STRING_TOO_LONG):
        printf("Error: String is too long.\n");
        continue;
      case (PREPARE_NEGATIVE_ID):
        printf("Error: ID must be positive.\n");
        continue;
      case (PREPARE_SYNTAX_ERROR):
      	printf("Error: Syntax error.\n");
		continue; 
      case (PREPARE_UNRECOGNIZED_STATEMENT):
        printf("Error: Unrecognized keyword at start of '%s'.\n",input_buffer->buffer);
        continue;
    }

完成了！那我们继续这个过程，先补重复定义。

typedef enum { 
 EXECUTE_SUCCESS,
 EXECUTE_TABLE_FULL,
 EXECUTE_PRIMARY_KEY_DUPLICATION,
 EXECUTE_NOT_FOUND
} ExecuteResult;

再补实现

ExecuteResult execute_insert(Statement* statement, Table* table) {
  Row row;
  if (table->num_rows >= TABLE_MAX_ROWS) {
    return EXECUTE_TABLE_FULL;
  }
  for (uint32_t i = 0; i < table->num_rows; i++) {
    deserialize_row(row_slot(table, i), &row);
    if(row.id==statement->row_to_insert.id){
     return EXECUTE_PRIMARY_KEY_DUPLICATION;	
	}
  }
 
  Row* row_to_insert = &(statement->row_to_insert);

  serialize_row(row_to_insert, row_slot(table, table->num_rows));
  table->num_rows += 1;

  return EXECUTE_SUCCESS;
}

ExecuteResult execute_update(Statement* statement, Table* table) {
    Row row;
    bool flag=false; 
    for(uint32_t i=0;i<table->num_rows;i++){
    	deserialize_row(row_slot(table, i), &row);
    	if(statement->row_to_update_new.id==row.id&&statement->row_to_update_old.id!=row.id){
    		return EXECUTE_PRIMARY_KEY_DUPLICATION;
		}
	}
    for(uint32_t i=0;i<table->num_rows;i++){
		deserialize_row(row_slot(table, i), &row);
		if(statement->row_to_update_old.id==row.id && strcmp(statement->row_to_update_old.username,row.username )==0&&strcmp(statement->row_to_update_old.email,row.email)==0)
		{ flag=true;
		   row.id=statement->row_to_update_new.id;
		   strcpy(row.username,statement->row_to_update_new.username);	
           strcpy(row.email,statement->row_to_update_new.email);
        }
        serialize_row(&row,row_slot(table,i));
    }
    if(flag==false){
    	return EXECUTE_NOT_FOUND;
	}
    return EXECUTE_SUCCESS;
}

再补输出

switch (execute_statement(&statement, table)) {
      case (EXECUTE_SUCCESS):
       printf("Executed.\n");
        break;
      case (EXECUTE_PRIMARY_KEY_DUPLICATION):
      	printf("Error: Primary key duplication.\n");
      	break;
      case (EXECUTE_NOT_FOUND):
	    printf("Error: Not found.\n");
		break;   
      case (EXECUTE_TABLE_FULL):
        printf("Error: Table full.\n");
        break;
      }

我们再修改execute_select,execute_delete。这两个之前写的太差了。

ExecuteResult execute_select(Statement* statement, Table* table) {
  Row row;
  for (uint32_t i = 0; i < table->num_rows; i++) {
    deserialize_row(row_slot(table, i), &row);
    if(statement->row_to_select==ID){
    printf("(%d)\n", row.id);
    } 
    else if(statement->row_to_select==USERNAME){
    printf("(%s)\n", row.username);
    }
    else if(statement->row_to_select==EMAIL){
    printf("(%s)\n", row.email);
    }
    else if(statement->row_to_select==ALL){
    printf("(%d, %s, %s)\n", row.id, row.username, row.email);
    }
  } 
  
  return EXECUTE_SUCCESS;
}

ExecuteResult execute_delete(Statement* statement, Table* table) {
	Row row;
	bool flag=false; 
	Row* row_to_delete = &(statement->row_to_delete);
	
	for(uint32_t i=0,j=0;i<table->num_rows;i++){
      deserialize_row(row_slot(table, i), &row);
	  if(statement->row_to_delete.id==row.id && strcmp(statement->row_to_delete.username,row.username )==0&&strcmp(statement->row_to_delete.email,row.email)==0){   
	    flag=true;			
	  }
	  else {
		serialize_row(&row,row_slot(table,j));
		j++;
      }
    }	
	
	if(flag==true){
	  table->num_rows -= 1;
	  return EXECUTE_SUCCESS;
    } 
	return EXECUTE_NOT_FOUND;
}

好了，以下是全部代码

#include <stdbool.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <stdint.h>
#define COLUMN_USERNAME_SIZE 32
#define COLUMN_EMAIL_SIZE 255
#define size_of_attribute(Struct, Attribute) sizeof(((Struct*)0)->Attribute)
#define TABLE_MAX_PAGES 100


typedef enum {
  META_COMMAND_SUCCESS,
  META_COMMAND_UNRECOGNIZED_COMMAND
} MetaCommandResult;

typedef enum { 
  PREPARE_SUCCESS, 
  PREPARE_NEGATIVE_ID,
  PREPARE_SYNTAX_ERROR,
  PREPARE_STRING_TOO_LONG,
  PREPARE_UNRECOGNIZED_STATEMENT
} PrepareResult;

typedef enum { 
  EXECUTE_SUCCESS,
  EXECUTE_TABLE_FULL,
  EXECUTE_PRIMARY_KEY_DUPLICATION,
  EXECUTE_NOT_FOUND
} ExecuteResult;


typedef enum { 
  STATEMENT_INSERT, 
  STATEMENT_SELECT,
  STATEMENT_DELETE,
  STATEMENT_UPDATE
} StatementType;

typedef enum {
  ID,	
  USERNAME,
  EMAIL,
  ALL	
} SelectResult;

typedef struct {
  uint32_t num_rows;
  void* pages[TABLE_MAX_PAGES];
} Table;

typedef struct {
  uint32_t id;
  char username[COLUMN_USERNAME_SIZE+1];
  char email[COLUMN_EMAIL_SIZE+1];
} Row;

typedef struct{
  char* buffer;
  size_t buffer_length;
  ssize_t input_length;
} InputBuffer;

typedef struct {
  StatementType type;
  Row row_to_insert;
  SelectResult row_to_select; 
  Row row_to_delete;
  Row row_to_update_old;
  Row row_to_update_new;
} Statement;

const uint32_t ID_OFFSET = 0;
const uint32_t PAGE_SIZE = 4096;
const uint32_t ID_SIZE = size_of_attribute(Row, id);
const uint32_t USERNAME_SIZE = size_of_attribute(Row, username);
const uint32_t EMAIL_SIZE = size_of_attribute(Row, email);
const uint32_t USERNAME_OFFSET = ID_OFFSET + ID_SIZE;
const uint32_t EMAIL_OFFSET = USERNAME_OFFSET + USERNAME_SIZE;
const uint32_t ROW_SIZE = ID_SIZE + USERNAME_SIZE + EMAIL_SIZE;
const uint32_t ROWS_PER_PAGE = PAGE_SIZE / ROW_SIZE;
const uint32_t TABLE_MAX_ROWS = ROWS_PER_PAGE * TABLE_MAX_PAGES;

void free_table(Table* table) ;
void close_input_buffer(InputBuffer* input_buffer);

MetaCommandResult do_meta_command(InputBuffer* input_buffer,Table* table) {
  if (strcmp(input_buffer->buffer, ".exit") == 0) {
  	close_input_buffer(input_buffer);
	free_table(table);
    exit(EXIT_SUCCESS);
  } else {
    return META_COMMAND_UNRECOGNIZED_COMMAND;
  }
}

InputBuffer* new_input_buffer(){
  InputBuffer* input_buffer=(InputBuffer*)malloc(sizeof(InputBuffer));
  input_buffer->buffer=NULL;
  input_buffer->buffer_length=0;
  input_buffer->input_length=0;
  return input_buffer;
}


void* row_slot(Table* table, uint32_t row_num) {
  uint32_t page_num = row_num / ROWS_PER_PAGE;
  void* page = table->pages[page_num];
  if (page == NULL) {
    // Allocate memory only when we try to access page
    page = table->pages[page_num] = malloc(PAGE_SIZE);
  }
  uint32_t row_offset = row_num % ROWS_PER_PAGE;
  uint32_t byte_offset = row_offset * ROW_SIZE;
  return page + byte_offset;
  }

void serialize_row(Row* source, void* destination) {
  memcpy(destination + ID_OFFSET, &(source->id), ID_SIZE);
  memcpy(destination + USERNAME_OFFSET, &(source->username), USERNAME_SIZE);
  memcpy(destination + EMAIL_OFFSET, &(source->email), EMAIL_SIZE);
}

void deserialize_row(void* source, Row* destination) {
  memcpy(&(destination->id), source + ID_OFFSET, ID_SIZE);
  memcpy(&(destination->username), source + USERNAME_OFFSET, USERNAME_SIZE);
  memcpy(&(destination->email), source + EMAIL_OFFSET, EMAIL_SIZE);
}

PrepareResult prepare_insert(InputBuffer* input_buffer, Statement* statement) {
  statement->type = STATEMENT_INSERT;

  char* keyword = strtok(input_buffer->buffer, " ");
  char* id_string = strtok(NULL, " ");
  char* username = strtok(NULL, " ");
  char* email = strtok(NULL, " ");
  char* check=strtok(NULL," ");
  if (id_string == NULL || username == NULL || email == NULL||check!=NULL) {
    return PREPARE_SYNTAX_ERROR;
  }

  int id = atoi(id_string);
  if (id < 0) {
    return PREPARE_NEGATIVE_ID;
  }
  if (strlen(username) > COLUMN_USERNAME_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }
  if (strlen(email) > COLUMN_EMAIL_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }

  statement->row_to_insert.id = id;
  strcpy(statement->row_to_insert.username, username);
  strcpy(statement->row_to_insert.email, email);

  return PREPARE_SUCCESS;
}

PrepareResult prepare_select(InputBuffer* input_buffer,Statement* statement){
  statement->type=STATEMENT_SELECT;
	
  char *keyword =strtok(input_buffer->buffer," ");
  char * args=strtok(NULL," ");
  char* check=strtok(NULL," ");
  if(check!=NULL){
	return PREPARE_SYNTAX_ERROR;
  }
  if(args==NULL){
	statement->row_to_select=ALL;
	return PREPARE_SUCCESS;
  }
  if(strcmp(args,"id")==0){
	statement->row_to_select=ID;
  }
  else if(strcmp(args,"username")==0){
	statement->row_to_select=USERNAME;
  }
  else if(strcmp(args,"email")==0){
	statement->row_to_select=EMAIL;
  }
  else return PREPARE_SYNTAX_ERROR;
	
  return PREPARE_SUCCESS;
}

PrepareResult prepare_delete(InputBuffer* input_buffer,Statement* statement){
  statement->type = STATEMENT_DELETE;
  char* keyword = strtok(input_buffer->buffer, " ");
  char* id_string = strtok(NULL, " ");
  char* username = strtok(NULL, " ");
  char* email = strtok(NULL, " ");
  char* check=strtok(NULL," ");
  if(check!=NULL){
	return PREPARE_SYNTAX_ERROR;
  }
  if (id_string == NULL || username == NULL || email == NULL) {
    return PREPARE_SYNTAX_ERROR;
  }

  int id = atoi(id_string);
  if (strlen(username) > COLUMN_USERNAME_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }
  if (strlen(email) > COLUMN_EMAIL_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }

  statement->row_to_delete.id = id;
  strcpy(statement->row_to_delete.username, username);
  strcpy(statement->row_to_delete.email, email);

  return PREPARE_SUCCESS;
}

PrepareResult prepare_update(InputBuffer* input_buffer,Statement* statement){
  statement->type = STATEMENT_UPDATE;
  char* keyword = strtok(input_buffer->buffer, " ");
  char* id_string_old = strtok(NULL, " ");
  char* username_old = strtok(NULL, " ");
  char* email_old = strtok(NULL, " ");
  char* id_string_new = strtok(NULL, " ");
  char* username_new = strtok(NULL, " ");
  char* email_new = strtok(NULL, " ");
  char* check=strtok(NULL," ");
  if(check!=NULL){
	return PREPARE_SYNTAX_ERROR;
  }
  if (id_string_old == NULL || username_old == NULL || email_old == NULL) {
    return PREPARE_SYNTAX_ERROR;
  }
    
  if (id_string_new == NULL || username_new == NULL || email_new == NULL) {
    return PREPARE_SYNTAX_ERROR;
  }
    
  int id_old = atoi(id_string_old);
  int id_new = atoi(id_string_new);
  if (id_new < 0) {
    return PREPARE_NEGATIVE_ID;
  }
  if (strlen(username_old) > COLUMN_USERNAME_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }
  if (strlen(email_old) > COLUMN_EMAIL_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }
  if (strlen(username_new) > COLUMN_USERNAME_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }
  if (strlen(email_new) > COLUMN_EMAIL_SIZE) {
    return PREPARE_STRING_TOO_LONG;
  }

  statement->row_to_update_old.id = id_old;
  strcpy(statement->row_to_update_old.username, username_old);
  strcpy(statement->row_to_update_old.email, email_old);
  statement->row_to_update_new.id = id_new;
  strcpy(statement->row_to_update_new.username, username_new);
  strcpy(statement->row_to_update_new.email, email_new);
  return PREPARE_SUCCESS;
}

PrepareResult prepare_statement(InputBuffer* input_buffer,Statement* statement) {
  if (strncmp(input_buffer->buffer, "insert",6) == 0) {
    return prepare_insert(input_buffer, statement);
  }
  else if(strncmp(input_buffer->buffer,"select",6)==0){
    return prepare_select(input_buffer, statement);
  }
  else if(strncmp(input_buffer->buffer,"delete",6)==0){
   	return prepare_delete(input_buffer, statement);  	   
  }  
  else if(strncmp(input_buffer->buffer,"update",6)==0){
   	return prepare_update(input_buffer, statement);
  }
  else return  PREPARE_UNRECOGNIZED_STATEMENT;
}


ExecuteResult execute_insert(Statement* statement, Table* table) {
  Row row;
  if (table->num_rows >= TABLE_MAX_ROWS) {
    return EXECUTE_TABLE_FULL;
  }
  for (uint32_t i = 0; i < table->num_rows; i++) {
    deserialize_row(row_slot(table, i), &row);
    if(row.id==statement->row_to_insert.id){
      return EXECUTE_PRIMARY_KEY_DUPLICATION;	
	}
  }
 
  Row* row_to_insert = &(statement->row_to_insert);

  serialize_row(row_to_insert, row_slot(table, table->num_rows));
  table->num_rows += 1;

  return EXECUTE_SUCCESS;
}

ExecuteResult execute_select(Statement* statement, Table* table) {
  Row row;
  for (uint32_t i = 0; i < table->num_rows; i++) {
    deserialize_row(row_slot(table, i), &row);
    if(statement->row_to_select==ID){
    printf("(%d)\n", row.id);
    } 
    else if(statement->row_to_select==USERNAME){
    printf("(%s)\n", row.username);
    }
    else if(statement->row_to_select==EMAIL){
    printf("(%s)\n", row.email);
    }
    else if(statement->row_to_select==ALL){
    printf("(%d, %s, %s)\n", row.id, row.username, row.email);
    }
  } 
  
  return EXECUTE_SUCCESS;
}

ExecuteResult execute_delete(Statement* statement, Table* table) {
  Row row;
  bool flag=false; 
  Row* row_to_delete = &(statement->row_to_delete);
  for(uint32_t i=0,j=0;i<table->num_rows;i++){
    deserialize_row(row_slot(table, i), &row);
	if(statement->row_to_delete.id==row.id && strcmp(statement->row_to_delete.username,row.username )==0&&strcmp(statement->row_to_delete.email,row.email)==0){   
	  flag=true;			
	}
	else {
	  serialize_row(&row,row_slot(table,j));
	  j++;
    }
  }	
  if(flag==true){
	table->num_rows -= 1;
	return EXECUTE_SUCCESS;
  } 
  else return EXECUTE_NOT_FOUND;
}

ExecuteResult execute_update(Statement* statement, Table* table) {
  Row row;
  bool flag=false; 
  for(uint32_t i=0;i<table->num_rows;i++){
    deserialize_row(row_slot(table, i), &row);
    if(statement->row_to_update_new.id==row.id&&statement->row_to_update_old.id!=row.id){
      return EXECUTE_PRIMARY_KEY_DUPLICATION;
	}
  }
    for(uint32_t i=0;i<table->num_rows;i++){
		deserialize_row(row_slot(table, i), &row);
		if(statement->row_to_update_old.id==row.id && strcmp(statement->row_to_update_old.username,row.username )==0&&strcmp(statement->row_to_update_old.email,row.email)==0)
		{ 
           flag=true;
		   row.id=statement->row_to_update_new.id;
		   strcpy(row.username,statement->row_to_update_new.username);	
           strcpy(row.email,statement->row_to_update_new.email);
        }
        serialize_row(&row,row_slot(table,i));
    }
    if(flag==false){
    	return EXECUTE_NOT_FOUND;
	}
    return EXECUTE_SUCCESS;
}

ExecuteResult execute_statement(Statement* statement, Table* table) {
  switch (statement->type) {
    case (STATEMENT_INSERT):
      return execute_insert(statement, table);
    case (STATEMENT_SELECT):
      return execute_select(statement, table);
    case (STATEMENT_DELETE):
	  return execute_delete(statement, table);  
	case (STATEMENT_UPDATE):
	  return execute_update(statement, table);
  }
 
}


Table* new_table() {
  Table* table = (Table*)malloc(sizeof(Table));
  table->num_rows = 0;
  for (uint32_t i = 0; i < TABLE_MAX_PAGES; i++) {
    table->pages[i] = NULL;
  }
  return table;
}

void free_table(Table* table) {
  for (int i = 0; table->pages[i]; i++) {
	free(table->pages[i]);
  }
  free(table);
}

void print_prompt() { 
  printf("sqlite> ");
}

void read_input(InputBuffer* input_buffer){

  ssize_t bytes_read=getline(&(input_buffer->buffer), &(input_buffer->buffer_length), stdin);
 
  if(bytes_read<=0){
    printf("Error: Wrong reading input.\n");
    exit(EXIT_FAILURE);
  }
  input_buffer->input_length=bytes_read-1;
  input_buffer->buffer[bytes_read -1 ]=0;
}

void close_input_buffer(InputBuffer* input_buffer){
  free(input_buffer->buffer);
  free(input_buffer);
}

int main(int argc, char* argv[]) {


   Table* table=new_table(); 
   InputBuffer* input_buffer = new_input_buffer();
   while (true) {
     print_prompt();
     read_input(input_buffer);

     if (input_buffer->buffer[0] == '.') {
       switch (do_meta_command(input_buffer,table)) {
         case (META_COMMAND_SUCCESS):
           continue;
         case (META_COMMAND_UNRECOGNIZED_COMMAND):
           printf("Error: Unrecognized command '%s'.\n", input_buffer->buffer);
           continue;
       }
     }

      Statement statement;
      switch (prepare_statement(input_buffer, &statement)) {
        case (PREPARE_SUCCESS):
          break;
        case (PREPARE_STRING_TOO_LONG):
          printf("Error: String is too long.\n");
          continue;
        case (PREPARE_NEGATIVE_ID):
          printf("Error: ID must be positive.\n");
          continue;
        case (PREPARE_SYNTAX_ERROR):
          printf("Error: Syntax error.\n");
	  	  continue; 
        case (PREPARE_UNRECOGNIZED_STATEMENT):
          printf("Error: Unrecognized keyword at start of '%s'.\n",input_buffer->buffer);
          continue;
      }

   
      switch (execute_statement(&statement, table)) {
        case (EXECUTE_SUCCESS):
          printf("Executed.\n");
          break;
        case (EXECUTE_PRIMARY_KEY_DUPLICATION):
       	  printf("Error: Primary key duplication.\n");
      	  break;
        case (EXECUTE_NOT_FOUND):
	      printf("Error: Not found.\n");
		  break;   
        case (EXECUTE_TABLE_FULL):
          printf("Error: Table full.\n");
          break;
      }
   
  }
}

我们在学习写代码的过程中，也在学习思路。大家看了这么多了，应该也有了不少收获吧!如果你有收获的话，可以点个赞哦。

weixin_51275728
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
闲来湖南麻将客户端服务端数据库全部.rar
07-30
本压缩包包含的“闲来湖南麻将客户端服务端数据库全部.rar”提供了从客户端到服务端的全套数据资源，虽然无法逐一验证每个文件的可用性，但对深入理解游戏运行机制、学习开发技巧以及进行类似项目开发具有重要的参考...
SQlite源码分析-体系结构
diaoju3333的博客
04-10 766
体系结构 在内部，SQLite由以下几个组件组成：内核、SQL编译器、后端以及附件。SQLite通过利用虚拟机和虚拟数据库引擎（VDBE），使调试、修改和扩展SQLite的内核变得更加方便。所有SQL语句都被编译成易读的、可以在SQLite虚拟机中执行的程序集。SQLite支持大小高达2 TB的数据库，每个数据库完全存储在单个磁盘文件中。这些磁盘文件可以在不同字节顺序的计算机...
3-23 Rspec自动化测试（开始练习）
weixin_30755393的博客
03-22 107
闰年程序 leap_year_spec.rb require_relative './leap_year' describe "Leap Year" do it "2016 is leap year" do result = is_leap_year?(2016) # 把 2016 传进去 expect(result).to eq(true) # 检查结果应该要...
数据库基础、使用C语言构建一个数据库、SQL语言、MySQL_c语言数据库
最新发布
2401_84252820的博客
04-16 1394
数据库就是英文的“database”翻译来的，data + base，故名思义就是数据的根源，数据的基础。那么为什么要有数据库呢，数据库首先是个计算机软件，在所谓数据库诞生之前，常用方法可能是程序员自己写一个小程序来完成数据处理分析这样的工作。伴随着计算机的普及，越来越多的场景开始使用计算机，产生了越来越多的数据，也催生了越来越多的数据分析需求。
实现简单数据库
woodslay的博客
07-28 2469
众所周知，无论是前端、后端，最终系统数据都会通过数据库系统进行读取和写入等操作，如下图所示，注册数据经过后端服务器最终进入数据库，因此可以认为数据库是系统的核心，特别是一旦涉及到高并发时，数据库可能成为系统的瓶颈，因此数据库的底层有必要深入学习，这里简单总结下基础数据库的底层实现，为后续深入学习数据库打下基础...
闲来无事，写个数据库吧（8）
weixin_51275728的博客
04-30 679
写写数据库
自己实现一个简单的数据库
abc
07-20 6860
How do you build a database? (self.Database) How do you build a database? (self.Database) Its a great question, and deserves a long answer. Most database servers are built in C, and store data using B-tree type constructs. In the old days there was a produ
自己闲来无事，闲暇时间做的玩的电商，框架ssh,数据库采用MYSQL
12-10
【标题】中的“电商”项目是利用SSH框架和MySQL数据库构建的一个小型在线购物平台。SSH框架，全称为Struts2 + Spring + Hibernate，是Java Web开发中常用的三大框架集成，用于构建高效、可扩展的Web应用程序。 ...
Spider:网络爬虫 基于python2.7 闲来无事 用于练习
06-15
常见的存储方式包括文件（如CSV、JSON）、数据库（如SQLite、MySQL）以及NoSQL数据库（如MongoDB）。 8. **反爬机制与用户代理** 许多网站会设置反爬机制，如限制IP访问频率、检查User-Agent等。Python爬虫可通过...
校园闲置物品交易平台的设计与实现（ 论文 、 SqlServer数据库、程序、视频）.zip
03-15
系统架构可能涉及前端、后端、数据库三层结构，前端使用JSP进行动态网页展示，后端则通过Servlet或MVC框架处理业务逻辑，数据库部分则利用SQL Server来存储和管理用户信息、商品信息、交易记录等大量数据。...
【完整版本】全开源无加密运营版H5棋牌源码（源码+数据库+架设教程）.rar
12-10
【完整版本】全开源无加密运营版H5棋牌源码是一个综合性的开发资源包，它包含了一套完整的H5棋牌游戏的源代码、相关的数据库以及详细的架设教程。这个资源对于那些想要开发或运营H5棋牌游戏的开发者或者创业者来说，...
自己写一个简单的数据库
12-14
所有应用软件之中，数据库可能是复杂的。 　　MySQL的手册有3000多页，PostgreSQL的手册有2000多页，Oracle的手册更是比它们相加还要厚。 　　但是，自己写一个简单的数据库，做起来并不难。Reddit上面有一个帖子，只用了几百个字，把原理讲清楚了。下面是我根据这个帖子整理的内容。 　　一、数据以文本形式保存 　　第一步，是将所要保存的数据，写入文本文件。这个文本文件是你的数据库。 　　为了方便读取，数据必须分成记录，每一条记录的长度规定为等长。比如，假定每条记录的长度是800字节，那么第5条记录的开始位置在3200字节。 　　大多数时候，我们不知道某一条
10分钟教你写一个数据库
艾小仙的专栏
10-12 8808
今天教大家借助一款框架快速实现一个数据库，这个框架就是Calcite，下面会带大家通过两个例子快速教会大家怎么实现，一个是可以通过 SQL 语句的方式可以直接查询文件内容，第二个是模拟 Mysql 查询功能，以及最后告诉大家怎么实现 SQL 查询 Kafka 数据。CalciteCalcite 是一个用于优化异构数据源的查询处理的可插拔基础框架（他是一个框架），可以将任意数据（Any data, ...
如何编写一个分布式数据库
热门推荐
stark_summer的专栏
10-02 2万+
讲师: 刘奇(goroutine) 个人简介： PingCAP创始人兼CEO。分布式系统专家，擅长分布式数据库，分布式缓存。目前从事NewSQL方向的创业，通过开源方式重建google内部的F1和spanner。目前项目已经开源，https://github.com/pingcap/tidb大家好, 我是开源项目 分布式 NewSQL 数据库 TiDB 和 分布式缓存 Codis 的 创始人 刘
c++编写一个简单的数据库程序
东南风的专栏
12-30 6431
第一步：构建一个头文件（**.h）#include #include #include #include using namespace std; class CStudentRec { public: char chFlag;//标志，A表示正常，N表示空 char strName[20];//姓名 char strID[10];//学号 float fScore[3];//3门成绩
mysql导入sql脚本、增删改数据库、数据表，超详细图解
ayychiguoguo的博客
10-29 7020
1 创建数据库 mysql> create {database/schema} database_name; 例：mysql> create database date_test; 2 查看数据库 mysql> show database； 3 选择数据库 mysql>use database_name; 4 创建数据表 mysql> create table table_name(col_name 1 data_type1, col_name 2 da
作业系列 - 尝试编写SQL数据库（一）
moon_sui的博客
10-23 376
因为第一次尝试做数据库，感觉应该是一个令人蛋疼的经历，因此记录下来，供大家指正。 以下是项目下载地址，诚挚的希望大家互相学习，代码可能有点乱，如果有好的建议欢迎大家留言，谢谢大家。 参考课本知识，我把自己的数据库分为了这样四个部分： 一、应用层 本层次用于提交登录和处理SQL语句，按照数据库访问权限的限制，用于储存用户表账号密码的数据库的查询访问权限应当最低，可以在没有登录的情况下访问。但
社区闲置共享系统的数据库设计
05-20
社区闲置共享系统需要设计一个数据库来保存用户信息、商品信息、订单信息等数据。以下是一个简单的数据库设计方案： 1. 用户表（User）：保存用户信息，包括用户ID、用户名、密码、邮箱、电话等。 | 字段名称 | ...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

weixin_51275728

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值