CREATE TABLE [patient_info](
	[serial_no] INTEGER PRIMARY KEY AUTOINCREMENT,
	[id] CHAR(64) NOT NULL ON CONFLICT ROLLBACK,
	[name] CHAR(64),
	[patient_guid] CHAR(64) NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK);

CREATE TABLE [exam_info](
	[serial_no] INTEGER PRIMARY KEY AUTOINCREMENT,
	[patient_guid] CHAR(64) REFERENCES [patient_info]([patient_guid]) ON DELETE RESTRICT ON UPDATE CASCADE,
	[exam_datetime] DATETIME,
	[file_guid] CHAR(64) NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK);

CREATE TABLE [operation_log](
	[serial_no] INTEGER PRIMARY KEY AUTOINCREMENT,
	[file_guid] CHAR(64) REFERENCES [exam_info]([file_guid]) ON DELETE RESTRICT ON UPDATE CASCADE,
	[operate_datetime] DATETIME NOT NULL ON CONFLICT ROLLBACK,
	[operation_type] INT NOT NULL ON CONFLICT ROLLBACK,
	[field_type] INT,
	[field_value] CHAR(200));
	
CREATE TABLE [other_info](
	[serial_no] INTEGER PRIMARY KEY AUTOINCREMENT,
	[version] INT NOT NULL ON CONFLICT ROLLBACK);