# N-SQL

**Repository Path**: tidex/N-SQL

## Basic Information

- **Project Name**: N-SQL
- **Description**: N-SQL is a SQL statement parser and generator.
- **Primary Language**: C#
- **License**: MIT
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2018-09-20
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# N-SQL

[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![Build Status](https://travis-ci.org/mokeyish/N-SQL.svg?branch=master)](https://travis-ci.org/mokeyish/N-SQL)
[![mokeyish MyGet Build Status](https://www.myget.org/BuildSource/Badge/mokeyish?identifier=c79af9bd-90b3-4fd1-9bdb-e7a20396a94c)](https://www.myget.org/)
[![NuGet](https://img.shields.io/nuget/v/NSqlParser.svg?style=flat-square&label=nuget)](https://www.nuget.org/packages/NSqlParser/)

N-SQL(NsqlParser) is a SQL statement parser and generator. It translates SQLs in a traversable hierarchy of CSharp classes. NSqlParser is not limited to one database but provides support for a lot of specials of Oracle, MySQL, PostgreSQL ... To name some, relational operators like != and so on.

## Support

If you need help using N-SQL feel free to file an issue or contact me.

## Exmaples

1. Sql Translation .

    ```csharp
    const string sourceSql = "select * from dual skip 1 limit 5";

    var selectStatement = SqlParser.ParseStatement(sourceSql);

    var targetSql = selectStatement.ToOracle();

    const string expectedOracleSql = "SELECT * FROM dual OFFSET 1 ROW FETCH FIRST 5 ROWS ONLY";

    Assert.Equal(expectedOracleSql, targetSql);
    ```
2. Sql Optimization.

    ```csharp
    var statement = Select(5 + Length(Column("name")) + 11).Build();

    Assert.Equal("Select 16 + Length(name)", statement.ToSql());
    Assert.Equal("SELECT 16 + LENGTH(name) FROM DUAL", statement.ToOracle());
    Assert.Equal("select 16 + length(name)", statement.ToNpgsql());
    Assert.Equal("select 16 + length(name)", statement.ToMySql());
    ```

## Roadmap

### Statement

- [x] Select Statement
- [ ] Data Diff
  - [x] Union Statement
  - [x] Minus/Except Statement(Oralce,Postgresql)
  - [x] Intersect Statement (oracle, Postgresql)

### Expression

#### Function Expression

- [ ] **String Function**
  - [x] **String Trimming**
    - [x] `trim(:string, trimText: string?)`
    - [x] `ltrim(:string, trimText: string?)`
    - [x] `rtrim(:string, trimText: string?)`
    - [x] *`Trim_Start(:string, trimText: string?)`*
    - [x] *`Trim_End(:string, trimText: string?)`*
  - [ ] **String Padding**
    - [x] `lpad(:string, length: int, paddingText: string?)`
    - [x] `rpad(:string, length: int, paddingText: string?)`
    - [x] *`Padding_Left(:string, length: int, paddingText: string?)`*
    - [x] *`Padding_Right(:string, length: int, padingText: string?)`*
  - [x] `substr(:string, position: int, length: int)`
  - [x] `concat(:string+)`
  - [x] left （MySQL/PostgreSQL）
  - [x] right （MySQL/PostgreSQL）
  - [x] `upper(:string)`
  - [x] `lower(:string)`
  - [x] `replace(:string, old: string, new: string)`
  - [x] `reverse(:string)`
  - [ ] regexp_replace (Oracle/PostgreSQL)
- [ ] **Numeric Function**
  - [x] `count(all|distinct [:number])`
  - [x] `sum(all|distinct [:number])`
  - [x] `max(all|distinct [:number])`
  - [x] `avg(all|distinct [:number])`
  - [x] `min(all|distinct [:number])`
  - [x] `stddev([:number])`
  - [x] `variance([:number])`
  - [ ] `median([:number])`
  - [ ] `Percent([:number], :number)`
  - [x] `round(:number)`
  - [x] `length(:string)`
  - [x] `sin(:number)`
  - [x] `cos(:number)`
  - [x] `tan(:number)`
  - [x] `sign(:number)`
  - [x] `abs(:number)`
  - [ ] `sqrt(:number)`
  - [ ] `pow(:number, :number)`
  - [ ] `log(:number, :number)`
- [ ] **Date Function**
  - [x] now / sysdate (Oracle)
  - [x] extract(Year | Month | Week | Day | Hour | Minute | Second MilliSecond FROM :datetime)
  - [x] *`Year(:datetime)`*
  - [x] *`Month(:datetime)`*
  - [x] *`Week(:datetime)`*
  - [x] *`Day(:datetime)`*
  - [x] *`Hour(:datetime)`*
  - [x] *`Minute(:datetime)`*
  - [x] *`Second(:datetime)`*
  - [ ] *`year_Diff(:datetime, :datetime)`*
  - [ ] *`Month_Diff(:datetime, :datetime)`*
  - [ ] *`Week_Diff(:datetime, :datetime)`*
  - [ ] *`Day_Diff(:datetime, :datetime)`*
  - [ ] *`Hour_Diff(:datetime, :datetime)`*
  - [ ] *`Minute_Diff(:datetime, :datetime)`*
  - [ ] *`Second_Diff(:datetime, :datetime)`*
  - [ ] *`year_Add(:datetime, :int)`*
  - [ ] *`Month_Add(:datetime, :int)`*
  - [ ] *`Week_Add(:datetime, :int)`*
  - [ ] *`Day_Add(:datetime, :int)`*
  - [ ] *`Hour_Add(:datetime, :int)`*
  - [ ] *`Minute_Add(:datetime, :int)`*
  - [ ] *`Second_Addd(:datetime, :int)`*
  - [ ] *`year_Sub(:datetime, :int)`*
  - [ ] *`Month_Sub(:datetime, :int)`*
  - [ ] *`Week_Sub(:datetime, :int)`*
  - [ ] *`Day_Sub(:datetime, :int)`*
  - [ ] *`Hour_Sub(:datetime, :int)`*
  - [ ] *`Minute_Sub(:datetime, :int)`*
  - [ ] *`Second_Sub(:datetime, :int)`*
- [ ] **Cast Function**
  - [x] cast
  - [x] to_char
  - [x] to_number
  - [ ] to_date
- [x] nvl（Oracle）/ coalesce (Postgresql、MySQL) / ifnull (MySQL)
- [ ] ifnull
- [ ] nullif

#### Value Expression

- [ ] **Constant Expression**
  - [x] string
  - [x] date
  - [x] number  
  - [x] boolean
- [ ] **Numeric Expression**
  - [ ] **Arithmetic Expression**
    - [x] plus: `+`
    - [x] minus: `-`
    - [x] multiply: `*`
    - [x] divide: `/`
- [x] case when
- [ ] decode （Oracle）

#### Logical Expression

- [x] **Comparison Expression**
  - [x] equal: `=`
  - [x] not eaual: `!=`、`<>`、`~=`、`^=`
  - [x] greater: `>`
  - [x] less: `<`
  - [x] greater or equal: `>=`
  - [x] less or equal: `<=`
- [x] and / or
- [x] in
- [x] like
- [ ] Start_With
- [ ] End_with
- [ ] Contains
- [x] not
- [x] null / not null
- [x] exist