[Dev] Generic table editors
In some cases it makes sense to create a specialized editor, e.g. smart_scripts internal structure (in the database) is a little bit compound (e.g. a way to link multiple actions), therefor SmartScripts has a special editor displaying events and actions.
However, in some cases there is no way to create anything but a generic view for table, columns and row. In this case WDE.DatabaseEditors project is used. Both the tables view on the left and the game_event editor is provided by this project.
It is fully generic, i.e. it loads the tables definition from json files. The json files are stored in WDE.DatabaseEditors/DbDefinitions folder.
You might ask: if this is generic, why do we even need a json definition, why not generate it automatically using database columns? The thing is, this is a generic view, but thanks to the definition, the editor can interpret the data in a specific way. I.e. TrinityCore's holiday_dates table has a column date_value which is a number - seconds since 1st of January 2000 (a unix epoch time with an offset). Thanks to the provided definition, the editor will display the date in a human readable form:
The generic table editor can work in three different modes, depending on a field record_mode in the json definition. Depending on the mode, the view is different and the generated query may vary.
Template mode can be used for tables that have a primary key consisting of one column, i.e. creature_template.
In this mode, the user has to pick the key that one wants to edit.
This view presents table "column" in a vertical form - as rows. This is mainly due to the fact that _template tables usually have a lot of columns and scrolling horizontally is extremely inconvenient while vertical view makes much more sense.
you can also use search option to quickly find a field by name.
The generated query will always be update, unless you insert a completely new key. This is due to the fact, that we shall never delete + insert from _template tables (for retail-like content). If however you are creating custom stuff, you can insert a new key and it will correctly generate insert query.
SingleRow mode is the most universal, it can be used for tables with any primary key, as long as the columns in the key are numbers only (so no strings in table key). The view is very similar to a generic SQL viewers, but it makes use of the provided definition and presents the fields in a special way. I.e. in the screenshot above, Start/End Time columns are unix timestamp in the database, here displayed in a human-friendly form, Holiday is an entry from a DBC, here the corresponding name is shown.
In this mode, the user don't select any key to display, this view always displays all the rows, with pagination (in the bottom right corner) and with an ability to write a custom SQL query to filter.
The user can delete rows (DELETE query will be generated), can insert new rows (INSERT query will be generated), can edit non-key columns (UPDATE query will be generated) and can edit key columns (DELETE + INSERT will be generated, because changing a key is treated as a deletion + insertion in the editor).
Single Record rows can be also displayed in a special 'single row' editing window, as seen above. This can be useful, i.e. this window is opened when you double click a spawn in the 3d world.
MultiRecord mode is the most specific one. It can be used for tables whose primary key contains at least two columns, yet one of them is usually the one the user want to edit it by. I.e. Trinity's creature_text has a primary key of (CreatureId, GroupId, Id). Creature id is a creature entry, while group id and id are just some consecutive numbers per entry. In other words: it doesn't make sense to edit (at once) 'all the creature texts, that has group id 1', yet it makes a lot of sense to edit (at once) 'all the creature texts, that belongs to creature id XYZ'
In this mode, the user has to select the key one wants to edit. And despite the primary key having multiple columns, the user selects just one column, e.g. in the creature_text example, the user has to select the creature entry whose texts we want to edit.
In the view there is no pagination, there is no filtering, because by design we already see the rows which matches our selected key.
Also, when it comes to the query generation, it never generates UPDATE query, it always generates DELETE + INSERT query by the selected key.
As I've mentioned before, the table json definitions need to be provided, because without one the editor doesn't know how to display the data, however, it is possible to generate the definitions automatically and then modify it according to our needs.
To access this feature, open the settings and select 'Database table editor' tab:
- You can generate a default definition for a table (and you can export all default definitions to a folder):
- You can check what are the differences between an existing definition and the table in your database that the definition refers to. I.e. the screenshot below shows what are the differences AzerothCore creature_template definition and currently selected Trinity Wrath database (you can't see the selected database on the screenshot, but that's what I had active while taking a screenshot):
As you can see, in my database I don't have following columns: trainer_type, trainer_spell, trainer_class, trainer_race, those columns are present in the definition (because that's what Azeroth has), yet they don't exist in Trinity. On the other hand, there is no column ExperienceModifier in this definition, because apparently Azeroth core doesn't have it.
This feature can be useful when adding support for a new database and instead of starting with a blank json, we can use existing one and modify it to our database.
- The last tab shows which tables have corresponding definitions:
It can be useful to see what's missing :)
The easiest way to learn the json structure is to see the existing definitions, however here I will quickly explain its fields:
| Field | Type | Required? | Modes | Description |
|---|---|---|---|---|
| id | string | Yes | all | Raw table name (for legacy reasons) |
| table_name | string | Yes | all | Raw table name (yes, both required) |
| record_mode | Enum: Template, SingleRow, MultiRecord
|
Yes | all | The type of this table (modes described in the section above) |
| primary_key | Array<string> | Yes | all | This is a list of all the primary key columns, no matter if the record_mode uses all the columns or not |
| compatibility | Array<string> | Yes | all | Array of 'core tags' denoting which emulators support this table (using this definition) |
| name | string | Yes | all | Human friendly name of this table (usually without underscores, with spaces) |
| single_solution_name | string | * | Template, MultiRecord | This can be used to change the document name to currently edited key in Template and MultiRecord mode. You can use special strings: {name} and {key} in this field which will get replaced by the key or name of currently edited thing. I.e. for creature_template in Template mode, you can set this field to Creature {name} template, so when editing creature id 3, this will change into 'Creature Flesh Eater (3) template'. Since in mode SingleRow we don't select any key, it doesn't make sense to use this field in this mode |
| multi_solution_name | string | * | Template, MultiRecord | Despite in Template and MultiRecord you have to select a key that you want to edit, you can also edit few keys at once. In this case it doesn't make sense to show all of those in the title of the document, therefore usually I set this field to Multi creature templates. So if you edit two or more creatures at once, this title will be used |
| description | string | No | all | This is a human friendly description of the table, shown when using 'New item' dialog |
| table_index_name | string | * | Template, MultiRecord | This is the name of the key column. In case of MultiRecord, this is the column that we want to group for. Not used in SingleRow at all |
| picker | string | * | Template, MultiRecord | This field describes a picker for a key that we will be editing, i.e. for creature_text where we edit texts by creature we want to use CreatureParameter which is a parameter with creature entries |
| groups | Array<Group> | Yes | all | Columns are grouped into arbitrary groups (for easier UX). For group definition, see below |
| icon_path | string | No | all | This is the path of this table icon. You can see existing icons here or feel free to design a new icon :) |
| only_conditions | bool | - | MultiRecord | @TODO |
| skip_quick_load | bool | No | all | By default, all the compatible tables (with definitions) will be displayed in the quick load panel. It is not a problem when only some of the tables have a definition, when there is more definitions, it makes more sense to hide by default some less used tables by setting this field to true. |
| group_name | string | No | all | This is a name of group that this table/definition belongs to, this is only used to group tables in New Item panel, so purely visual thing. Optional, but highly recommended |
| table_name_source_field | string | No | Template, MultiRecord | In order to replace {name} in single_solution_name to the real name, we need to provide info which column actually contains name (i.e. id in `creature_template) |
| auto_key | Enum (Creature / GameObject) or null |
No | SingleRow | This is a very specific field, designed only for creature and gameobject tables. The editor can generate guids automatically, if you configure the guid ranges in the settings. If we set this field to either Creature or Gameobject, then when inserting a new row, the editor will generate a guid automatically for it |
| commands | Array<Command> | No | all | Array of commands available in the toolbar for this table, see Command structure definition below |
| foreign_tables | Array<ForeignTable> | No | all | The editor can join two tables into one for easier data visualisation. I.e. we can merge creature_template_addon and creature_template tables into one view. This can be only used when there is 1 - 1 relation between those two |
| sort_by | Array<string> | No | SingleRow, MultiRecord | A list of columns to sort the INSERT output by. If null, then it will be sorted by primary key columns |
| conditions | Condition | No | SingleRow, MultiRecord | (TrinityCore based servers) If there is a condition source associated with this table, then we can provide all the required data to let the user edit conditions inline |
*) Required when using certain mode
Columns are grouped into human friendly groups, however not all modes make use of it. Fields are grouped in Template mode (see the picture), they are not grouped in SingleRow and MultiRecord mode, however, there is an option display an editor for one particular key in SingleRow mode, which then will be grouped (see below).
If the mode doesn't support groups, just put all the fields into one group with any name.
| Field | Type | Required? | Modes | Description |
|---|---|---|---|---|
| group_name | string | Yes | all | Human friendly name of the group |
| fields | Array<Field> | Yes | all | An array of all the columns |
| show_if | object | No | Template | A very specific use-case: "dataX" columns in gameobject_template change their meaning depending on type column. This show_if can be used to hide and show certain data depending on the type. Check existing gameobject_template to understand it |
| show_if > db_column_name | string | (Yes) | (Template) | A name of column that we want to test against (i.e. type in gameobject_template) |
| show_if > value | integer | (Yes) | (Template) | A value that db_column_name needs to have, in order to display this group |
| Field | Type | Required? | Modes | Description |
|---|---|---|---|---|
| name | string | Yes | all | Human friendly name for this column |
| db_column_name | string | Yes (*) | all | Raw column name in the database |
| value_type | string | Yes (*) | all | A type of this column. See below for possible values |
| preferred_width | number | No | SingleRow, MultiRecord | The default with of this column (used when an user opens the table for the first time, because once the user change the column width manually, it will use his custom preferred value) |
| help | string | No | all | Short description of what the column means |
| foreign_table | string | No | all | If this is a column from a foreign table, then we need to put its name here (and the table needs to be defined in the definition foreign tables array |
| default | number / string / bool | No | all | When inserting a new row, this will be used as the default value. Otherwise it will default to 0/false/empty string depending on the value_type
|
| autoincrement | string | No | MultiRecord | Used only in MutliRecord mode: if we provide a column name here, when an user adds a new row, it will set this column to max existing value + 1 (useful in creature_text - group_id
|
| read_only | boolean | No | Template, MultiRecord | If true, an user won't be able to edit this column (only view) |
| can_be_null | boolean | No | all | If true, the column can contain null value |
| zero_is_blank | boolean | No | SingleRow, MultiRecord | If true, the editor will hide 0 value. Useful to make the view more clear, when 0 means no value. I.e. 0 emote is emote NONE, so it is better to hide it completely rather than display NONE (0) text |
| is_condition | boolean | No | all | If we define conditions for the definition, then we need to add this special field denoting conditions editing button. When we set this field to true, we only need to provide name and set db_column_name to special value "conditions", even tho such column doesn't exist in the database |
| autogenerate_comment | string | No | SingleRow, MultiRecord | We can provide here an expression that will be automatically generated depending on the row values, as a comment in the database. Check existing definitions for examples |
| meta | string | No | all | Not all columns denote an actual database column, there can be meta columns. This field denotes what type of special column it is. See below for possible meta columns |
*) not required when is_condition is true or when meta is not empty!
Available meta columns
-
expression:EXPRESSION HERE- it can be used to compute some value and display it here. Checkcreature_templatedefinitions (only in TEMPLATE mode) -
table:TABLE NAME HERE;RAW SQL CONDITION HERE- it can be used to display a button, that will open another table editor with provided SQL condition. In the SQL condition you can use{db_column_name}which will get replace with the current value of this column. I.e
table:pool_members;`poolSpawnId` = {entry}`
This will display a button, that will open pool_members table editor, with a filter where poolSpawnId equals to the entry of currently selected row
-
invoke:COMMAND HERE- it will display a button, that invokes provided command on the server. Column names in curly brackets, i.e.{entry}gets replaced by the value of this column -
tableByKey:TABLE NAME HERE;A COLUMN FROM THE CURRENT DEFINITION- it will display a button, that will open another table editor with a key matching a column name provided, so it can be used to open MultiRecord table from another table. -
one2one:TABLE NAME HERE- it will display a button, that will open another table editor, which has a one to one relationship. You don't specify foregin tables for this, it will simply open another table with the same primary key value.
We provide foreign table data only if we want to display some columns from another table. Check creature_template.json definition, which shows also creature_template_addon columns (as a one view).
| Field | Type | Required? | Description |
|---|---|---|---|
| table_name | string | Yes | Foreign table name |
| foreign_key | Array<string> | Yes | List of this foreign table primary key columns |
| Field | Type | Required? | Description |
|---|---|---|---|
| source_type | integer | Yes | A condition_source_type for this table |
| source_group | string | Yes | A name of column used for source_group in conditions table |
| source_entry | string | Yes | A name of column used for source_group in conditions table |
| targets | Array<object> | Yes | A list of possible condition targets |
| targets > id | integer | Yes | An id of this condition target |
| targets > name | string | Yes | A human friendly name of this condition target |
This is a very specific feature. You can define custom commands in the code, that later can be executed in certain tables. I.e. check CreatureTextBroadcastId command implementation. It assigns BroadcastTextId automatically using provided Text.
| Field | Type | Required? | Description |
|---|---|---|---|
| command_id | string | Yes |
CommandId of IDatabaseTableCommand
|
| parameters | Array<string> | Yes | A list of column names, that will be provided to the IDatabaseTableCommand
|
value_type can be either a generic type: string, uint, int, long, ulong
or it can be one of possible Parameters which can have special values. To open the list of possible parameters, open settings and select Parameters browser tab:
you can also check existing definitions for possible parameters. In the code, parameters are defined in few different places (those are not all):