Git Product home page Git Product logo

manticore-projects / jsqlformatter Goto Github PK

View Code? Open in Web Editor NEW
20.0 2.0 5.0 5.8 MB

Java SQL Formatter, Beautifier and Pretty Printer

Home Page: https://manticore-projects.com/JSQLFormatter/index.html

License: Other

Java 24.91% PLSQL 13.00% Python 0.24% ANTLR 3.07% HTML 0.47% CSS 24.92% JavaScript 1.46% XSLT 0.27% TSQL 0.78% PLpgSQL 30.87%
beautify formatter java mssql mysql oracle postgres pretty-print sql

jsqlformatter's People

Contributors

manticore-projects avatar

Stargazers

avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar

Watchers

avatar avatar

Forkers

lybigdata sinlang marlboro029 beset-sk bennylwf

jsqlformatter's Issues

missing "limit" keyword after format

I tried to format below SQL query.

2021-05-15_13-15

Formatter beautified the query without any error or exception.

2021-05-15_13-16

But formatted SQL didn't contain LIMIT 200000 part of the string.

In order to reproduce case easily, I'm pasting raw SQL query as a text below:

SELECT p.* 
FROM product p 
LEFT JOIN product_description pd ON (p.product_id = pd.product_id) 
LEFT JOIN product_type_google_category ptgc ON (p.product_type_id = ptgc.product_type_id) 
LEFT JOIN product_google_custom_label pgcl ON (p.product_id = pgcl.product_id) 
WHERE p.status=1 AND pd.language_id = 2 
AND p.product_id IN (SELECT product_id FROM cj_googleshopping_products) 
 ORDER BY date_available DESC, p.purchased DESC LIMIT 200000

Note: Forgot to mention that I'm using MySQL and MySQL supports the LIMIT clause to select a limited number of records. See here for other keywords used by various databases to provide the same functionality.

How to be compatible with sqls containing keywords

It's actually not a problem but a question.
I notice that on the Online DEMO, the following sql is parsable.

SELECT operator.c1 AS c1, apply.c2 as c2 from t_test1 operator left join t_test2 apply on operator.c3=apply.c3

But when I try it on original JSQLParser, the upper SQL is not parsable, complaining about syntax error.
I would like to ask if you have ever done some optimization on JSQLParser.
Thank you.

ParseException: Encountered unexpected token:<EOF>

When I add (create) a new .sql file in Netbeans,

2021-05-15_13-08

.. it makes a file with default comment:

2021-05-15_13-08_1

IMHO it also triggers auto formatting on new file which generates below exception:

  • net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: at line 9, column 3

Full exception here 👉 parseException.txt

TOP clause missing 

select top 10 qtysold, sellerid
from sales
order by qtysold desc, sellerid;

TOP 10 won't be formatted.

group_concat section in select remains unformatted

Hi, below is a working unformatted MySQL query written in two lines:

SELECT GROUP_CONCAT(DISTINCT TRIM(Concat(m.title, ' ', m.firstname, ' ', m.lastname)) ORDER BY p2m.manufacturer_type_id ASC SEPARATOR ' ') as manufacturer_name
FROM product_to_manufacturer p2m LEFT JOIN manufacturer m ON m.manufacturer_id = p2m.manufacturer_id WHERE p2m.product_id = 574768;

When I format the query, group_concat section in select remains unformatted with all its keywords:

select GROUP_CONCAT(DISTINCT TRIM(Concat(m.title, ' ', m.firstname, ' ', m.lastname)) ORDER BY p2m.manufacturer_type_id ASC SEPARATOR ' ') as manufacturer_name
from product_to_manufacturer p2m
  left join manufacturer m
    on m.manufacturer_id = p2m.manufacturer_id
where p2m.product_id = 574768
;

Here is my formatter settings:
2021-05-15_21-55

Here is an example formatted query from an online formatter service: 😉
2021-05-15_22-01

`WINDOW` clause missing 

WITH finishers AS (
        SELECT  'Sophia Liu' AS name
                , TIMESTAMP '2016-10-18 2:51:45' AS finish_time
                , 'F30-34' AS division
        UNION ALL
        SELECT  'Lisa Stelzner'
                , TIMESTAMP '2016-10-18 2:54:11'
                , 'F35-39'
        UNION ALL
        SELECT  'Nikki Leith'
                , TIMESTAMP '2016-10-18 2:59:01'
                , 'F30-34'
        UNION ALL
        SELECT  'Lauren Matthews'
                , TIMESTAMP '2016-10-18 3:01:17'
                , 'F35-39'
        UNION ALL
        SELECT  'Desiree Berry'
                , TIMESTAMP '2016-10-18 3:05:42'
                , 'F35-39'
        UNION ALL
        SELECT  'Suzy Slane'
                , TIMESTAMP '2016-10-18 3:06:24'
                , 'F35-39'
        UNION ALL
        SELECT  'Jen Edwards'
                , TIMESTAMP '2016-10-18 3:06:36'
                , 'F30-34'
        UNION ALL
        SELECT  'Meghan Lederer'
                , TIMESTAMP '2016-10-18 3:07:41'
                , 'F30-34'
        UNION ALL
        SELECT  'Carly Forte'
                , TIMESTAMP '2016-10-18 3:08:58'
                , 'F25-29'
        UNION ALL
        SELECT  'Lauren Reasoner'
                , TIMESTAMP '2016-10-18 3:10:14'
                , 'F30-34' )
SELECT  name
        , Strftime( finish_time, '%X' ) AS finish_time
        , division
        , Strftime( fastest_time, '%X' ) AS fastest_time
        , Strftime( second_fastest, '%X' ) AS second_fastest
FROM (  SELECT  name
                , finish_time
                , division
                , finishers
                , First(finish_time) OVER w1 AS fastest_time
                , NTH_VALUE(finish_time, 2) OVER w1 AS second_fastest
        FROM finishers
        WINDOW w1 AS (
    PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))
;

formatter doesn't lower some keywords (in, distinct, union, all, and)

Hi, some keywords aren't being lowered although "options > external formatter > spelling > keywords" is LOWER selected.

Here are examples to reproduce the case.

Example, raw query-1:

SELECT  ccp.product_id, COUNT( DISTINCT ccp.campaign_id )
FROM campaign_constraint_product ccp
LEFT JOIN campaign c ON c.campaign_id = ccp.campaign_id AND c.status = 1
WHERE product_id IN (414732,530729)
GROUP BY ccp.product_id ORDER BY 2 DESC
LIMIT 10;

Formatted:

select  ccp.product_id
        , Count( DISTINCT ccp.campaign_id )
from campaign_constraint_product ccp
  left join campaign c
    on c.campaign_id = ccp.campaign_id
      AND c.status = 1
where product_id IN ( 414732, 530729 )
group by ccp.product_id
order by 2 desc
limit 10
;

Example, raw query-2:

SELECT campaign_id FROM campaign_constraint_product ccp WHERE ccp.product_id = 580696
UNION ALL
SELECT campaign_id FROM campaign_action_product cap WHERE cap.product_id =580696;

Formatted:

select campaign_id
from campaign_constraint_product ccp
where ccp.product_id = 580696
UNION ALL
select campaign_id
from campaign_action_product cap
where cap.product_id = 580696
;

BadLocationException: Wrong (offset+length)

Hi, I've installed .nbm from here. 0.1.6 I've added a .sql file with a query:

select * from customer c where date_added >= '2021-04-26 00:00' order by date_added desc limit 3;

2021-05-01_17-03

When I try to format, I'm getting "BadLocationException: Wrong (offset+length)" exception without formatting query.

Screenshot_2021-05-01_17-06-20

Product Version: Apache NetBeans IDE 12.3
Java: 11.0.11; Java HotSpot(TM) 64-Bit Server VM 11.0.11+9-LTS-194
Runtime: Java(TM) SE Runtime Environment 11.0.11+9-LTS-194
System: Linux version 4.19.0-0.bpo.9-amd64 running on amd64; UTF-8; en_US (nb)

NullPointerException with a "select ... case when" query

Hi, below is a working MySQL query from my workspace:

-- failed to format start
select case when (
select ccp.campaign_id
from campaign_constraint_product ccp
  inner join campaign_free_shipping_products_visibility cfspv
    on cfspv.campaign_id = ccp.campaign_id
where ccp.product_id = 530729
  AND cfspv.status = 1
UNION
select cap.campaign_id
from campaign_action_product cap
  inner join campaign_free_shipping_products_visibility cfspv
    on cfspv.campaign_id = cap.campaign_id
where cap.product_id = 530729
  AND cfspv.status = 1
) is not null then 1 else 0 end as is_free_shipping
;
-- failed to format end

When I try to format query, JSQLFormatter throws NullPointerException from appendFromItem() function.

Exception details here:

java.lang.NullPointerException
	at com.manticore.jsqlformatter.JSQLFormatter.appendFromItem(JSQLFormatter.java:2653)
	at com.manticore.jsqlformatter.JSQLFormatter.appendSelectBody(JSQLFormatter.java:1669)
	at com.manticore.jsqlformatter.JSQLFormatter.appendSelect(JSQLFormatter.java:1609)
[catch] at com.manticore.jsqlformatter.JSQLFormatter.format(JSQLFormatter.java:956)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterWrapper.format(JSQLFormatterWrapper.java:45)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterJob.format(JSQLFormatterJob.java:79)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterService.format(JSQLFormatterService.java:60)
	at de.funfried.netbeans.plugins.external.formatter.FormatterServiceDelegate.format(FormatterServiceDelegate.java:94)
	at de.funfried.netbeans.plugins.external.formatter.ui.editor.ExternalFormatterReformatTaskFactory$2.reformat(ExternalFormatterReformatTaskFactory.java:95)
	at org.netbeans.modules.editor.indent.TaskHandler$MimeItem.runTask(TaskHandler.java:550)
	at org.netbeans.modules.editor.indent.TaskHandler.runTasks(TaskHandler.java:309)
	at org.netbeans.modules.editor.indent.IndentImpl.reformat(IndentImpl.java:349)
	at org.netbeans.modules.editor.indent.api.Reformat.reformat(Reformat.java:129)
	at org.netbeans.editor.ActionFactory$FormatAction$1$1.run(ActionFactory.java:1854)
	at org.netbeans.editor.GuardedDocument.runAtomicAsUser(GuardedDocument.java:333)
	at org.netbeans.editor.ActionFactory$FormatAction$1.run(ActionFactory.java:1833)
	at org.netbeans.modules.progress.ui.RunOffEDTImpl$1.run(RunOffEDTImpl.java:146)
	at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1418)
	at org.netbeans.modules.openide.util.GlobalLookup.execute(GlobalLookup.java:45)
	at org.openide.util.lookup.Lookups.executeWith(Lookups.java:278)
	at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:2033)

"Encountered unexpected token" when SQL has comment

Hi,

select customer_id from customer c where c.email='[email protected]'; --7313088

Query is formatted but plugin throws net.sf.jsqlparser.parser.ParseException exception because of comment section and splits it from SQL query.

select customer_id
from customer c
where c.email = '[email protected]'
;

-- failed to format start
 --7313088
-- failed to format end

Exception:

net.sf.jsqlparser.parser.ParseException: Encountered unexpected token:<EOF>
    at line 1, column 10.

Was expecting one of:

    "("
    "CALL"
    "COMMENT"
    "COMMIT"
    "DECLARE"
    "DELETE"
    "DESCRIBE"
    "DROP"
    "EXEC"
    "EXECUTE"
    "EXPLAIN"
    "GRANT"
    "INSERT"
    "MERGE"
    "SET"
    "SHOW"
    "TRUNCATE"
    "UPDATE"
    "UPSERT"
    "USE"
    "VALUES"
    "WITH"
    <K_SELECT>

	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:28839)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:28678)
	at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:269)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:81)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:181)
Caused: net.sf.jsqlparser.JSQLParserException: Encountered unexpected token:<EOF>
    at line 1, column 10.

Was expecting one of:

    "("
    "CALL"
    "COMMENT"
    "COMMIT"
    "DECLARE"
    "DELETE"
    "DESCRIBE"
    "DROP"
    "EXEC"
    "EXECUTE"
    "EXPLAIN"
    "GRANT"
    "INSERT"
    "MERGE"
    "SET"
    "SHOW"
    "TRUNCATE"
    "UPDATE"
    "UPSERT"
    "USE"
    "VALUES"
    "WITH"
    <K_SELECT>

	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:183)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:60)
[catch] at com.manticore.jsqlformatter.JSQLFormatter.format(JSQLFormatter.java:966)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterWrapper.format(JSQLFormatterWrapper.java:48)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterJob.format(JSQLFormatterJob.java:66)
	at de.funfried.netbeans.plugins.external.formatter.sql.jsqlformatter.JSQLFormatterService.format(JSQLFormatterService.java:64)
	at de.funfried.netbeans.plugins.external.formatter.FormatterServiceDelegate.format(FormatterServiceDelegate.java:94)
	at de.funfried.netbeans.plugins.external.formatter.ui.editor.ExternalFormatterReformatTaskFactory$2.reformat(ExternalFormatterReformatTaskFactory.java:93)
	at org.netbeans.modules.editor.indent.TaskHandler$MimeItem.runTask(TaskHandler.java:550)
	at org.netbeans.modules.editor.indent.TaskHandler.runTasks(TaskHandler.java:309)
	at org.netbeans.modules.editor.indent.IndentImpl.reformat(IndentImpl.java:349)
	at org.netbeans.modules.editor.indent.api.Reformat.reformat(Reformat.java:129)
	at org.netbeans.editor.ActionFactory$FormatAction$1$1.run(ActionFactory.java:1854)
	at org.netbeans.editor.GuardedDocument.runAtomicAsUser(GuardedDocument.java:333)
	at org.netbeans.editor.ActionFactory$FormatAction$1.run(ActionFactory.java:1833)
	at org.netbeans.modules.progress.ui.RunOffEDTImpl$1.run(RunOffEDTImpl.java:146)
	at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1418)
	at org.netbeans.modules.openide.util.GlobalLookup.execute(GlobalLookup.java:45)
	at org.openide.util.lookup.Lookups.executeWith(Lookups.java:278)
	at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:2033)

IMHO, ending comment section may be ignored and left as is after formatting.

I think there is a general problem with SQL comments. When I reformat file, (for example, adding more new SQL queries and format) every formatting action causes failed to format start section to duplicate like this;

select customer_id
from customer c
where c.email = '[email protected]'
;

-- failed to format start


-- failed to format start


-- failed to format start


-- failed to format start


-- failed to format start


-- 7313088
-- failed to format end
-- failed to format end
-- failed to format end
-- failed to format end
-- failed to format end

Comment sections should be ignored without touching them.

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.