Difference between revisions of "SQL Syntax Highlighting"

From Apache OpenOffice Wiki
Jump to: navigation, search
 
(41 intermediate revisions by 5 users not shown)
Line 1: Line 1:
−
__TOC__
+
{| align="right"
 +
| __TOC__
 +
|}
  
  
 
==Introduction==
 
==Introduction==
  
−
The SQL view of the query designer, which displays, and allows to edit, raw SQL statements, currently is a mere text input field. It would be helpful especially to the unexperienced user to have syntax highlighting here: SQL keywords should be notable, literals should be distinguished, and the like.
 
  
−
{{issue|67961}}
+
[[Image:SQL SyntaxHighlighting Logo.png|center]]
−
{{cws|sqlsyntaxhighlighting}}
+
 
 +
 
 +
The SQL view of the query designer, which displays, and allows to edit, raw SQL statements, currently is a mere text input field. It would be helpful especially to the unexperienced user to support the users by various means and make it more convinient.
 +
 
 +
The cws for the finished implementation was {{CWS|sqlsyntaxhighlighting}}
  
 
==Detailed Information==
 
==Detailed Information==
 
===Feature Description===
 
===Feature Description===
  
 +
Five new features will be implemented:
 +
 +
* Syntax highlighting for the SQL view will be added: SQL keywords, literals and the like will be notable {{Bug|67961}}
 +
* Syntax highlighting for the {{menu|Tools|SQL…}} dialog {{Bug|95321}}
 +
* configurable colors in {{menu|Tools|Options|General|Appearance}} {{Bug|95166}}
 +
* The SQL view will respect the settings from {{menu|Tools|Options|OpenOffice|Fonts}}, where you can set a font name as well as a font size {{Bug|91687}}
 +
* To support users writing complex queries including bracket terms, matching characters will be shortly highlighted when pressing },)," or ' {{Bug|95165}}
 +
 +
===Technical details===
 +
 +
Instead of duplicating neccessary code for EditView/EditEngine/TextWindow again, a new control MultiLineEditSyntaxHighlight will be created, which will be derived from a MultiLineEdit, which actually already does all the EditView/EditEngine stuff already as a nice usable control. So this new control will be the same as a MultiLineEdit, but will be able to do the new stuff as well. As the current Base SQL view is using a MultiLineEdit already, it will be extremly easy to use the new control.
 +
 +
 +
 +
===SQL strings===
 +
 +
"all",
 +
"and",
 +
"any",
 +
"as",
 +
"asc",
 +
"avg",
 +
"between",
 +
"by",
 +
"cast",
 +
"corresponding",
 +
"count",
 +
"create",
 +
"cross",
 +
"delete",
 +
"desc",
 +
"distinct",
 +
"drop",
 +
"escape",
 +
"except",
 +
"exists",
 +
"false",
 +
"from",
 +
"full",
 +
"global",
 +
"group",
 +
"having",
 +
"in",
 +
"inner",
 +
"insert",
 +
"intersect",
 +
"into",
 +
"is",
 +
"join",
 +
"left",
 +
"like",
 +
"local",
 +
"match",
 +
"max",
 +
"min",
 +
"natural",
 +
"not",
 +
"null",
 +
"on",
 +
"or",
 +
"order",
 +
"outer",
 +
"right",
 +
"select",
 +
"set",
 +
"some",
 +
"sum",
 +
"table",
 +
"temporary",
 +
"true",
 +
"union",
 +
"unique",
 +
"unknown",
 +
"update",
 +
"using",
 +
"values",
 +
"where"
  
−
The SQL view will respect the settings from Tools-Options-Fonts, where you can set a font name as well as a font size
 
  
  
Line 37: Line 118:
 
|}
 
|}
  
−
===Roadmap===
+
 
−
* it is currently planned to have this feature ready for [[OOoRelease31]]
+
===Colors===
 +
 
 +
<span style="color:#009900">SQLIDENTIFIER</span>: 009900
 +
 
 +
<span style="color:#000000">SQLNUMBER</span>: 000000
 +
 
 +
<span style="color:#CE7B00">SQLSTRING</span>: CE7B00
 +
 
 +
<span style="color:#000000">SQLOPERATOR</span>: 000000
 +
 
 +
<span style="color:#0000E6">SQLKEYWORD</span>: 0000E6
 +
 
 +
<span style="color:#259D9D">SQLPARAMETER</span>: 259D9D
 +
 
 +
<span style="color:#969696">SQLCOMMENT</span>: 969696
  
 
===People involved===
 
===People involved===
Line 58: Line 153:
  
 
! developer
 
! developer
 +
|-
 +
 +
! Frank Schönheit
 +
 +
! fs
 +
 +
! code reviewer / nitpicker
 +
|-
 +
 +
! Bettina Haberer
 +
 +
! bh
 +
 +
! UX
 +
 +
|-
 +
 +
! Mechtilde Stehmann
 +
 +
! mechtilde
 +
 +
! QA
 +
|-
 +
 +
! André Schnabel
 +
 +
! Thalion72
 +
 +
! QA
 
|}
 
|}
  
−
==stuff not to forget==
+
== File Format Changes ==
 +
 
 +
No changes to the file format is neccessary
 +
 
 +
 
 +
== Migration ==
  
−
* color settings in "Tools" - "Options" – "Appearance"
+
Old documents will work flawlessly with the new user interface.
 +
 
 +
== Configuration ==
 +
 
 +
SQL colors can be configured in {{menu|Tools|Options|Appearance}}
  
 
==Future work==
 
==Future work==
Line 68: Line 201:
  
 
===end user===
 
===end user===
−
* insert SQL code snippets
+
To further improve the usability, it would be nice to have the possibility to
 +
 
 +
* insert various SQL and user code snippets
 +
 
 
* code completion including SQL keywords, table and field names
 
* code completion including SQL keywords, table and field names
 +
 +
* FS:
 +
How much effort would it be to allow an additional separate "class" (aka color) whose elements can be configured at runtime?
 +
 +
That is, at the moment both functions and SQL keywords use the same color, though they are different. The list of supported functions depends on the underlying database, and could be fed by the query designer into the SQL editor.
  
  
 
===code base===
 
===code base===
−
 
+
If the BASIC IDE as well as HTML source view only need regular MultiLineEdit functionality, the new code could be used for these two cases as well. This would get rid of a lot of code and would make it more maintainable.
  
 
[[Category:Database]]
 
[[Category:Database]]

Latest revision as of 10:14, 12 August 2022


Introduction

SQL SyntaxHighlighting Logo.png


The SQL view of the query designer, which displays, and allows to edit, raw SQL statements, currently is a mere text input field. It would be helpful especially to the unexperienced user to support the users by various means and make it more convinient.

The cws for the finished implementation was CWS sqlsyntaxhighlighting  

Detailed Information

Feature Description

Five new features will be implemented:

  • Syntax highlighting for the SQL view will be added: SQL keywords, literals and the like will be notable Issue 67961
  • Syntax highlighting for the Tools → SQL… dialog Issue 95321
  • configurable colors in Tools → Options → General → Appearance Issue 95166
  • The SQL view will respect the settings from Tools → Options → OpenOffice → Fonts, where you can set a font name as well as a font size Issue 91687
  • To support users writing complex queries including bracket terms, matching characters will be shortly highlighted when pressing },)," or ' Issue 95165

Technical details

Instead of duplicating neccessary code for EditView/EditEngine/TextWindow again, a new control MultiLineEditSyntaxHighlight will be created, which will be derived from a MultiLineEdit, which actually already does all the EditView/EditEngine stuff already as a nice usable control. So this new control will be the same as a MultiLineEdit, but will be able to do the new stuff as well. As the current Base SQL view is using a MultiLineEdit already, it will be extremly easy to use the new control.


SQL strings

"all", "and", "any", "as", "asc", "avg", "between", "by", "cast", "corresponding", "count", "create", "cross", "delete", "desc", "distinct", "drop", "escape", "except", "exists", "false", "from", "full", "global", "group", "having", "in", "inner", "insert", "intersect", "into", "is", "join", "left", "like", "local", "match", "max", "min", "natural", "not", "null", "on", "or", "order", "outer", "right", "select", "set", "some", "sum", "table", "temporary", "true", "union", "unique", "unknown", "update", "using", "values", "where"


String additions and changes

resource Old String New String
fontsubs.src FL_SOURCEVIEW "Font settings for HTML and Basic sources" ; "Font settings for HTML, Basic and SQL sources" ;


Colors

SQLIDENTIFIER: 009900

SQLNUMBER: 000000

SQLSTRING: CE7B00

SQLOPERATOR: 000000

SQLKEYWORD: 0000E6

SQLPARAMETER: 259D9D

SQLCOMMENT: 969696

People involved

Name OOo Nickname Role
Max Odendahl mod developer
Frank Schönheit fs code reviewer / nitpicker
Bettina Haberer bh UX
Mechtilde Stehmann mechtilde QA
André Schnabel Thalion72 QA

File Format Changes

No changes to the file format is neccessary


Migration

Old documents will work flawlessly with the new user interface.

Configuration

SQL colors can be configured in Tools → Options → Appearance

Future work

end user

To further improve the usability, it would be nice to have the possibility to

  • insert various SQL and user code snippets
  • code completion including SQL keywords, table and field names
  • FS:

How much effort would it be to allow an additional separate "class" (aka color) whose elements can be configured at runtime?

That is, at the moment both functions and SQL keywords use the same color, though they are different. The list of supported functions depends on the underlying database, and could be fed by the query designer into the SQL editor.


code base

If the BASIC IDE as well as HTML source view only need regular MultiLineEdit functionality, the new code could be used for these two cases as well. This would get rid of a lot of code and would make it more maintainable.

Retrieved from "https://wiki.openoffice.org/w/index.php?title=SQL_Syntax_Highlighting&oldid=250273"
Personal tools