edit
IndependentProject.DatabaseStructure
Independent Project
.
Database Structure
## translations: should I support multiple matchtypes for a single rule? * rule_id - int * match - list of strings * matchtype - index - string * type - ("function" | "query" | "data") * content - long string database commands: * `CREATE TABLE rules (rule_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, type ENUM('function', 'query', 'data'), content VARCHAR(16384));` * `CREATE TABLE matches (matchtype VARCHAR(32), m VARCHAR(1024), function VARCHAR(16384), rule_id INT NOT NULL, KEY(rule_id));` * `CREATE INDEX matchtype_index ON matches (matchtype);` #### note: at some point, matches.function should probably be converted into an ID referencing the code in another table. This is not because functions will take up unnecessary space, but that this makes it far easier to implement a fast compiled caching system. This way the code can be stored by ID and looked up by ID which is very fast. The cache system would need some way to look them up and give them IDs anyway. Now it looks like it shouldn't be too slow to process from SQL memory anyway. select m where matchtype='match were looking for', loop through these, test them, if they match, follow it. It seems that at this point, it is assumed that this table will be read into memory by lua, where the organization of the tables fit nicely into a lookup on the matchtypes resulting in a list of rules whose rule_matches can then be tested. If the rule_matches match, then the rule data can be queried. At this point it seems to make more sense to have a table mapping matchtypes to matches and their rule_id rather than having two seperate matchtypes and rule_matches tables. --- ## Process * expr : string : varchar(16384) * file : string : varchar(4196) * stdin : large string : blob * args : string varchar(4196) `create table process (expr varchar(16384), file varchar(4196), stdin blob, args varchar(4196));` ## lua function * expr : string : varchar(16384) * lua_code : large string : text `create table lua (expr varchar(16384), lua_code text);` ## string * id : int primary not null auto increment * expr : string : varchar(16384) * result : string : varchar(16384) * context : string : varchar(16384) * exec : list of strings (id) + id : int + command : string : varchar(1024) `create table string (id int NOT NULL AUTO_INCREMENT, expr varchar(16384), result varchar(16384), context varchar(16384), exec integer, PRIMARY KEY(id));` `create table string_commands (id int, command varchar(1024));` ## matching shortcuts * name : string : char(255) * expr : string : varchar(16384) `create table match_env (name char(255), expr varchar(16384));` ## translations * expr : string : varchar(16384) * translation : string : varchar(1024) `create table translations (expr varchar(16384), translation varchar(1024));`