DbShell

OpenSource database toolkit using XAML for describing jobs

User Tools

Site Tools


Sidebar

DbShell

DbMouse

DbMouse is database GUI, which has support for DbShell. It could be used as DbShell console.

filter_expressions

Filter expressions

DbShell contains powerful implementation of column filters (used eg. in DbMouse). Filters depends on data type of column, which is filtered.

Filter features, common to all datatypes

  • keywords are typed with CAPITAL letters
  • SPACE is used as AND operator
  • COMMA (”,”) is used as OR operator
  • AND has bigger precedence than OR as usual, parentheses are not supported
  • Common expressions:
    • NULL - value is NULL
    • NOT NULL - value is not NULL
  • You can use single or double quotes for text with spaces

Text operators

  • TEXT (without mark) - test whether given text is contained in searched data
  • +TEXT - test whether given text is contained in searched data
  • -TEXT - test whether given text is not contained in searched data
  • <TEXT - test whether given text is alphabetically before searched data
  • >TEXT - test whether given text is alphabetically after searched data
  • ⇐TEXT - test whether given text is alphabetically before or equal to searched data
  • >=TEXT - test whether given text is alphabetically after or equal to searched data
  • =TEXT - test whether given text is equal to searched data
  • !=TEXT or <>TEXT - test whether given text is not equal to searched data
  • ^TEXT - test whether searched data starts with given text
  • !^TEXT - test whether searched data doesn't start with given text
  • $TEXT - test whether searched data ends with given text
  • !$TEXT - test whether searched data doesn't end with given text
  • EMPTY - test whether searched data is empty (contains only whitespaces)
  • NOT EMPTY - test whether searched data is not empty (contains any non-whitespace characters)

Examples

  • ^with $success - value starts with “with” and ends with “success”
  • word1 word2 - value contains “word1” and “word2”
  • “word1 word2” - value contains “word1 word2”
  • EMPTY, =0 - value is empty or is equal to text “0”

Number operators

  • NUMBER (without mark) - test whether searched number is equal to given number
  • <NUMBER - test whether searched number smaller than given number
  • >NUMBER - test whether searched number greater than given number
  • ⇐NUMBER - test whether searched number smaller than or equal to given number
  • >=NUMBER - test whether searched number greater than or equal to given number
  • =NUMBER - test whether given text is equal to searched data
  • !=NUMBER or <>NUMBER - test whether given text is not equal to searched data

Examples

  • 230 - value is equal to 230
  • 10,20,30,40 - value is equal to one of value 10,20,30,40
  • >10 <20 - value is greater than 10 and smaller than 20
  • <10, >20 - value is smaller than 10 or greater than 20

Date and time operators

Date and time syntax is a bit more complex. There are date and time literals, which should be combined using AND, OR operators (or using in one expression with relation operators)

Date syntax

You can use any of following formats:

  • yyyy-MM-dd (eg. 2012-10-30) - ISO format
  • dd.MM.yyyy (eg. 30.10.2012) - central europe format
  • MM/dd/yyyy (eg. 10/30/2012) - US format

Time syntax

Time should be given in one of form:

  • Full - HH:mm:ss.fff (fff are milliseconds) or HH:mm:ss.f (f are number of tenths of second)
  • Seconds resolution - HH:mm:ss
  • Minute resolution - HH:mm

Relational operators

  • DATE - test whether searched date and time is the same day as given date
  • =DATE - test whether searched date and time is the same day as given date
  • !=DATE or <>DATE - test whether searched date and time is not the same day as given date
  • ⇐DATE - test whether searched date and time is before or equal to given date
  • <DATE - test whether searched date and time is before given date
  • >=DATE - test whether searched date and time is after or equal to given date
  • >DATE - test whether searched date and time is after given date
  • ⇐DATE TIME - test whether searched date and time is before or equal to given date and time
  • <DATE TIME - test whether searched date and time is before given date and time
  • >=DATE TIME - test whether searched date and time is after or equal to given date and time
  • >DATE TIME - test whether searched date and time is after given date and time

Partial date or time specifications

  • 4-digit year number (eg. 2012) - whole year
  • number. (eg 2.) - day number - day in current month
  • number/ (eg 2/) - month number - month in current year
  • HH:* (eg 2:*) - test, whether hour part is HH (other parts are ignored)
  • yyyy-MM - year in given month
  • 3-letter uppercase month abbrevation (eg. JAN, FEB, MAR…) - test whether month part is given month (in any year)
  • 3-letter uppercase day abbrevation (eg. MON, TUE, WED) - test whether day part is given day (in any motnh/year)

Relative intervals

Self-explanative. Must be written with capital letters.

  • YESTERDAY
  • TODAY
  • TOMORROW
  • LAST WEEK
  • THIS WEEK
  • NEXT WEEK
  • LAST MONTH
  • THIS MONTH
  • NEXT MONTH
  • LAST YEAR
  • THIS YEAR
  • NEXT YEAR

Examples

  • YESTERDAY, TODAY - all values today and yesterday
  • APR 2010 - all values in april 2010
  • TODAY 12:* - all values today between 12:00 and 13:00
  • >=2012-10-30 14:40 ⇐2012-11-05 14:40 - values between given bounds
  • 15. - 15 th day in current month
  • MON THIS MONTH, NULL - all values, which are mondays this month or which are NULL

ANTLR Grammars

Grammars for parses are defined using ANTLR toolkit, and are part of open-source DbShell toolkit. There are separate grammars for each data type filter. If you are familiar with ANTLR or grammars, your could use it as additional documentation.

filter_expressions.txt · Last modified: 2013/10/07 08:54 by admin