How to make new function (mysql GROUP_CONCAT)

[Antusqus]

Hi there,

This project looks great for my purposes. I'm looking into creating ASTs from VIEW queries.
My goal is to eventually parse the queries and transpile it into another language (such as SQL Server)
However, Views can get quite complex and functions can differ alot between two languages. That is why I wish to start by creating an AST.

I have been trying to customize the sourcecode and run tests but it quickly becomes very complex.
The main problem seems to be the SEPARATOR call that attaches to GROUP_CONCAT (Native to MYSQL, but is similar to string aggregation).

Could you give me some pointers on where to start customizing? Or do you have any documentation on workflow for example?
So far I have:
in expressions.py
added
class Group_Concat(AggFunc): arg_types = {"this": True, "expressions": True}

in tokens.py
added GROUP_CONCAT = auto()
and SEPARATOR = auto()

into mysql.py
added (under) **Parser.FUNCTIONS "GROUP_CONCAT" : _group_concat

• _group_concat + _group_concat_sql << don't do anything yet

I am trying to learn by copying first and running/printing bits and pieces.
But in all honesty, it shows that I have no clue what I am doing so far.

Would you advise me to stop tinkering with this and look elsewhere, or do you think it is a viable approach?
Thank you for your time!

Below is the query that I am trying to parse as a testcase, in case you were curious. Straight from the mysql_sakila sample db.
`from sqlglot import parse_one

mySQL_statement: str =
"""
CREATE VIEW nicer_but_slower_film_list AS
SELECT
film.film_id AS FID,
film.title AS title,
film.description AS description,
category.name AS category,
film.rental_rate AS price,
film.length AS length,
film.rating AS rating,
GROUP_CONCAT(CONCAT(CONCAT(UPPER(SUBSTR(actor.first_name, 1, 1)),
LOWER(SUBSTR(actor.first_name,
2,
LENGTH(actor.first_name))),
_utf8mb4 ' ',
CONCAT(UPPER(SUBSTR(actor.last_name, 1, 1)),
LOWER(SUBSTR(actor.last_name,
2,
LENGTH(actor.last_name))))))
SEPARATOR ', ') AS actors
FROM
((((category
LEFT JOIN film_category ON ((category.category_id = film_category.category_id)))
LEFT JOIN film ON ((film_category.film_id = film.film_id)))
JOIN film_actor ON ((film.film_id = film_actor.film_id)))
JOIN actor ON ((film_actor.actor_id = actor.actor_id)))
GROUP BY film.film_id , category.name
"""

print(repr(parse_one(mySQL_statement,"mysql")))
`

[tobymao]

this was a bit tricky to do properly, i've implemented it in this commit

49a4099

[Antusqus]

That's really cool. Thank you so much!