[Feature Request] SQL-like language for map manipulation

[PROPHESSOR]

The request is to add SQL-like query language for map editing (may be called SLQL — SLade Query Language)

Some examples of its usage:

• SELECT things WHERE type=3001 // Select all Imps on the map
• DESELECT lines WHERE texturemiddle="-" // Deselect the walls that don't have middle textures
• UPDATE sectors SET heightceiling=512 WHERE textureceiling="F_SKY" // Set height to 512 where sector texture is F_SKY
• DELETE things WHERE type=2012 // Delete all Medikits from the map
• COUNT vertexes WHERE x>=32 AND y>=32 // Count some vertexes

Virtual "databases" you can make requests to:

• things
• lines (sidedefs, operate with textures, actions and args)
• sectors
• vertexes

Supported operations:

• SELECT (change selection mode and select these elements)
• DESELECT (clear selection from these elements, don't change selection mode if it doesn't match)
• UPDATE (set some built-in or UDMF property)
• DELETE (delete these elements)
• COUNT (print amount of these elements)

Possible future improvements:

• Some high-level JOIN-like LOOKUP operations, for example:
  • SELECT things LOOKUP sectors WHERE thing.type=3 AND sector.floortex="FLAT1"
• Provide autocomplete and interactive input:
  • for texture names (open texture picker window)
  • thing types (open things picker window)
  • coordinates (draw some bounding rect)
  • UDMF properties (suggest autocompletion and scrollable suggestion list with short possible description)
  • etc...

Milestones

1. [Easy] Create SLQL input box
2. [Easy] Implement simple hardcoded statements:

• SELECT object
• DESELECT object
• COUNT object
• DELETE object // things only for now

3. [Easy] Implement simple WHERE statement

• no logical operations yet
• support for these types of comparison: =, >, <, >=, <=, !=

4. [Medium] Implement simple UPDATE statement for things and textures

• no logical operations
• only one change per query
• only for objects that don't require nodes rebuild (things, lines/sectors textures, heights, light levels, etc.)

5. [Hard] Implement logical operations for WHERE statement:

• AND
• OR
• brackets

6. Implement several SET operations at once, For example:

• UPDATE sectors SET lightlevel=255, textureceiling="F_SKY", heightceiling=512 WHERE texturefloor="GRASS1"

[Gaerzi]

There's already a Lua implementation. It's not SQL, and arguably more involved, but there's little reason to have two different scripting languages. https://slade.readthedocs.io/en/latest/

You can use it to update stuff from a map, see these examples: https://slade.readthedocs.io/en/latest/md/Examples/ChangeTextures.html https://slade.readthedocs.io/en/latest/md/Examples/ArchvileMadness.html

[sirjuddington]

I'll leave this open in case someone else wants to have a go at implementing something like it, but as @Gaerzi said we already have lua scripting that can do the same thing (and more)

[PROPHESSOR]

I've written a simple parser concept in TypeScript

export const QUERY_ACTIONS = ['select', 'deselect', 'update', 'delete', 'count'] as const;
export type QueryAction = typeof QUERY_ACTIONS[number];
export type QueryConditions = Record<string, string | number>; // TODO: Add typings
export const QUERY_TYPES = ['things', 'lines', 'sectors', 'vertexes'] as const;
export type QueryType = typeof QUERY_TYPES[number];

class Query {
    query: string;
    action: QueryAction;
    type: QueryType;
    conditions: QueryConditions | null;

    constructor(query: string) {
        this.query = query;
        this.action = this.parseQueryAction(query);
        this.type = this.parseQueryType(query);
        this.conditions = this.parseWhereSection(query);
    }

    private parseQueryAction(query: string): QueryAction {
        const tokens = query.toLowerCase().split(' ');

        if (QUERY_ACTIONS.includes(tokens[0] as QueryAction)) return (tokens[0] as QueryAction);

        throw new Error('Unknown query action ' + tokens[0]);
    }

    private parseQueryType(query: string): QueryType {
        const tokens = query.toLowerCase().split(' ');

        if (QUERY_TYPES.includes(tokens[1] as QueryType)) return (tokens[1] as QueryType);

        throw new Error('Unknown query type' + tokens[1]);
    }

    private parseWhereSection(query: string): QueryConditions | null {
        const queryLower = query.toLowerCase();
        if (!queryLower.includes('where')) return null;

        const tokensLower = queryLower.split(' ');
        const tokens = query.replaceAll(',', '').split(' ');
        const whereTokens = tokens.slice(tokensLower.indexOf('where') + 1);

        const conditions: QueryConditions = {};

        for (const condition of whereTokens) {
            if (!condition.includes('=')) continue;

            const [key, value] = condition.split('=');

            conditions[key] = Number(value) ?? value;
        }

        return conditions;
    }
}

class SLQL {
    query: Query;

    constructor(query: string) {
        console.log('Executing the query: ' + query);

        this.query = new Query(query);
    }

    public exec() {
        switch (this.query.action) {
            case 'select': return this.execSelectQuery();
            default:
                throw new Error('Query action not implemented yet ' + this.query.action)
        }
    }

    private execSelectQuery() {
        console.log(`Change selection type to ${this.query.type}`);
        console.log(`Go through ${this.query.type} and filter for such conditions: ${this.query.conditions && Object.entries(this.query.conditions)}`)
        console.log(`${this.query.action} filtered ${this.query.type}`);
    }
}

const query = new SLQL('SELECT things WHERE type=3');
query.exec();

As dasho suggested, I've used TypeScript-to-Lua transpiler to check it. The transpiled code looks like it should work, but I don't know how to use SLADE API from it: The playground link with Lua code (wait a few seconds to transpile)

If a way is found to conveniently use the SLADE API, I can write a full SLQL handler in TypeScript and it can be added to SLADE as a Lua script. But in that case, it would still need to be made convenient to use it. For example, when you press F1, a popup window opens where you can enter the query text with some autosuggestions and examples. This will require changes on the C++ side.

[eevee]

i can definitely see a gui being useful for arbitrary find/replace (and perhaps meshing with #1618 in some ways), but i'm not sure how SQL would be an improvement over that. also your parser doesn't seem to understand type = 3?

[PROPHESSOR]

your parser doesn't seem to understand type = 3?

Yeah, it's a quick concept, not ready-to-use parser