Use SQL comment line for parameter hint to retain .sql usability

[bkmdev]

Enhancement Request: Currently, to make existing .sql files usable by pugSQL, one needs to "corrupt" them by injecting :myVar references into the SQL queries, thus invalidating them as usable .sql files.

I feel like this is a missed opportunity, as it would have been nicer / more elegant, if one simply could have just annotated them via the comment line (--) providing hints as to the variable to use (ie: parse for in the subsequent line(s)) to maintain the usability of the original .sql file.

[Yeganloo]

Can you give an example of what you propose?
What the query will be look like?
If : is all your problem, using a database tool like "DBeaver" will solve your problems and all of your queries are still usable!

[bkmdev]

I was thinking something like:

-- :name find_user :var "42" :one
select * from users where user_id = "42"

(basically some kind of space delimited token in the comment line keyed by the :var reserved word to indicate what in the next line is the variable to parse for & replace)

Re. DBeaver: Are you referring to https://dbeaver.io/ ? If so, I tried searching the site for PugSQL but didn't get any hits... Can you clarify? (I'm not familiar with it and normally just use MySQL Workbench)

[Yeganloo]

Yes. That is DBeaver.
It is a SQL tool, there is no pugsql in it.
Did you use PG-Admin4 or SQL-MyAdmin? it is something like them, but it can almost connect all kind of SQL-Engines.
Your problem is that queries you write for Pugsql are not usable in your db too (pgAdmin for example). Right?
If you run those kind of query in DBeaver, it prompts before running query and ask for : marked parameters.
Like this:

So i think it's not Pugsql problem and it's about the query tool you use!

About your proposal, what is fantastic in Pugsql is that queries inside app are exactly what you use outside of code!
The main problem with this proposal is, it cause Pugsql to regenerate a new query, before execution!
So all the benefits of debugging are gone from Pugsql!
Query in sql file is not exectly the query that run in your DBMS!

[doekman]

I've tackled this issue by giving SQL files with parameters the extension .psql instead of .sql.

I've used this with other project, and it can also be used with PugSQL, since globbing is done via *sql (without a dot before sql).

[bkmdev]

Hi, Apologies for the delayed response. I thought I had replied but this was still sitting as a draft :) @Yeganloo, I think you may have interpreted my request backwards, I'm trying to preserve the SQL code so it can simply be enhanced with just PugSQL specific comment hints VS rely on specific tools to be able to parse PugSQL syntax "SQL". The great idea of PugSQL is that it has the ability to (almost) directly import in straight SQL while maintaining the usability on the database tools side. I say "almost" because one needs to break the SQL code in order to use it in PugSQL. What would be wonderful & better is if PugSQL could seamlessly point at existing `.SQL` files that only need to be enhanced with comments that have PugSQL hints. This would allow the original SQL code to still function, irregardless of what SQL client/studio/dev tool you choose to use (& there are many!). Another benefit is the SQL code repo could be shared w/o having to create an edited clone of the original files _just to adopt PugSQL usage_. Re. DBeaver: it seems like a very powerful tool & I will look into it but I think you are limiting the adoption of PugSQL by depending on everyone having to use DBeaver to maintain compatibility of their SQL files with existing DBs. Anyway, that's my 2￠ :) hope that explains the idea better. Don't get me wrong because I really think the idea & philosophy of PugSQL is great! (it just needs tweaking). Regards,

…

On Sep 12, 2020, at 2:16 PM, Hosein Yeganloo ***@***.***> wrote: Yes. That is DBeaver. It is a SQL tool, there is no pugsql in it. Did you use PG-Admin4 or SQL-MyAdmin? it is something like them, but it can almost connect all kind of SQL-Engines. Your problem is that queries you write for Pugsql are not usable in your db too (pgAdmin for example). Right? If you run those kind of query in DBeaver, it prompts before running query and ask for : marked parameters. So i think it's not Pugsql problem and it's about the query tool you use! About your proposal, what is fantastic in Pugsql is that queries inside app are exactly what you use outside of code! The main problem with this proposal is, it cause Pugsql to regenerate a new query, before execution! So all the benefits of debugging are gone from Pugsql! Query in sql file is not exectly the query that run in your DBMS! ― You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.