forked from ServiceStack/ServiceStack.OrmLite
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Sql.cs
173 lines (138 loc) · 9.7 KB
/
Sql.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
using System;
using System.Collections;
using System.Linq;
using System.Collections.Generic;
namespace ServiceStack.OrmLite
{
public static partial class Sql
{
public static string VARCHAR = nameof(VARCHAR);
public static List<object> Flatten(IEnumerable list)
{
var ret = new List<object>();
if (list == null) return ret;
foreach (var item in list)
{
if (item == null) continue;
if (item is IEnumerable arr && !(item is string))
{
ret.AddRange(arr.Cast<object>());
}
else
{
ret.Add(item);
}
}
return ret;
}
public static bool In<T, TItem>(T value, params TItem[] list) => value != null && Flatten(list).Any(obj => obj.ToString() == value.ToString());
public static bool In<T, TItem>(T value, SqlExpression<TItem> query) => value != null && query != null;
public static string Desc<T>(T value) => value == null ? "" : value + " DESC";
public static string As<T>(T value, object asValue) => value == null ? "" : $"{value} AS {asValue}";
public static T Sum<T>(T value) => value;
public static string Sum(string value) => $"SUM({value})";
public static T Count<T>(T value) => value;
public static T CountDistinct<T>(T value) => value;
public static string Count(string value) => $"COUNT({value})";
public static T Min<T>(T value) => value;
public static string Min(string value) => $"MIN({value})";
public static T Max<T>(T value) => value;
public static string Max(string value) => $"MAX({value})";
public static T Avg<T>(T value) => value;
public static string Avg(string value) => $"AVG({value})";
public static T AllFields<T>(T item) => item;
public static string JoinAlias(string property, string tableAlias) => tableAlias;
public static T JoinAlias<T>(T property, string tableAlias) => default(T);
public static string Custom(string customSql) => customSql;
public static T Custom<T>(string customSql) => default(T);
public static string Cast(object value, string castAs) => $"CAST({value} AS {castAs})";
public const string EOT= "0 EOT";
}
/// <summary>
/// SQL Server 2016 specific features
/// </summary>
public static partial class Sql
{
/// <summary>Tests whether a string contains valid JSON.</summary>
/// <param name="expression">The string to test.</param>
/// <returns>Returns True if the string contains valid JSON; otherwise, returns False. Returns null if expression is null.</returns>
/// <remarks>ISJSON does not check the uniqueness of keys at the same level.</remarks>
/// <see cref="https://docs.microsoft.com/en-us/sql/t-sql/functions/isjson-transact-sql"/>
public static bool? IsJson(string expression) => null;
/// <summary>Extracts a scalar value from a JSON string.</summary>
/// <param name="expression">
/// An expression. Typically the name of a variable or a column that contains JSON text.<br/><br/>
/// If <b>JSON_VALUE</b> finds JSON that is not valid in expression before it finds the value identified by <i>path</i>, the function returns an error. If <b>JSON_VALUE</b> doesn't find the value identified by <i>path</i>, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in <i>expression</i>.
/// </param>
/// <param name="path">
/// A JSON path that specifies the property to extract. For more info, see <see cref="https://docs.microsoft.com/en-us/sql/relational-databases/json/json-path-expressions-sql-server">JSON Path Expressions (SQL Server)</see>.<br/><br/>
/// In SQL Server 2017 and in Azure SQL Database, you can provide a variable as the value of <i>path</i>.<br/><br/>
/// If the format of path isn't valid, <b>JSON_VALUE</b> returns an error.<br/><br/>
/// </param>
/// <returns>
/// Returns a single text value of type nvarchar(4000). The collation of the returned value is the same as the collation of the input expression.
/// If the value is greater than 4000 characters: <br/><br/>
/// <ul>
/// <li>In lax mode, <b>JSON_VALUE</b> returns null.</li>
/// <li>In strict mode, <b>JSON_VALUE</b> returns an error.</li>
/// </ul>
/// <br/>
/// If you have to return scalar values greater than 4000 characters, use <b>OPENJSON</b> instead of <b>JSON_VALUE</b>. For more info, see <see cref="https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql">OPENJSON (Transact-SQL)</see>.
/// </returns>
/// <see cref="https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql"/>
public static T JsonValue<T>(string expression, string path) => default(T);
/// <summary>Extracts a scalar value from a JSON string.</summary>
/// <param name="expression">
/// An expression. Typically the name of a variable or a column that contains JSON text.<br/><br/>
/// If <b>JSON_VALUE</b> finds JSON that is not valid in expression before it finds the value identified by <i>path</i>, the function returns an error. If <b>JSON_VALUE</b> doesn't find the value identified by <i>path</i>, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in <i>expression</i>.
/// </param>
/// <param name="path">
/// A JSON path that specifies the property to extract. For more info, see <see cref="https://docs.microsoft.com/en-us/sql/relational-databases/json/json-path-expressions-sql-server">JSON Path Expressions (SQL Server)</see>.<br/><br/>
/// In SQL Server 2017 and in Azure SQL Database, you can provide a variable as the value of <i>path</i>.<br/><br/>
/// If the format of path isn't valid, <b>JSON_VALUE</b> returns an error.<br/><br/>
/// </param>
/// <returns>
/// Returns a single text value of type nvarchar(4000). The collation of the returned value is the same as the collation of the input expression.
/// If the value is greater than 4000 characters: <br/><br/>
/// <ul>
/// <li>In lax mode, <b>JSON_VALUE</b> returns null.</li>
/// <li>In strict mode, <b>JSON_VALUE</b> returns an error.</li>
/// </ul>
/// <br/>
/// If you have to return scalar values greater than 4000 characters, use <b>OPENJSON</b> instead of <b>JSON_VALUE</b>. For more info, see <see cref="https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql">OPENJSON (Transact-SQL)</see>.
/// </returns>
/// <see cref="https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql"/>
public static string JsonValue(string expression, string path) => string.Empty;
/// <summary>
/// Extracts an object or an array from a JSON string.<br/><br/>
/// To extract a scalar value from a JSON string instead of an object or an array, see <see cref="https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql">JSON_VALUE(Transact-SQL)</see>.
/// For info about the differences between <b>JSON_VALUE</b> and <b>JSON_QUERY</b>, see <see cref="https://docs.microsoft.com/en-us/sql/relational-databases/json/validate-query-and-change-json-data-with-built-in-functions-sql-server#JSONCompare">Compare JSON_VALUE and JSON_QUERY</see>.
/// </summary>
/// <typeparam name="T">Type of objects returned</typeparam>
/// <param name="expression">
/// An expression. Typically the name of a variable or a column that contains JSON text.<br/><br/>
/// If <b>JSON_QUERY</b> finds JSON that is not valid in <i>expression</i> before it finds the value identified by <i>path</i>, the function returns an error. If <b>JSON_QUERY</b> doesn't find the value identified by <i>path</i>, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in <i>expression</i>.
/// </param>
/// <param name="path">
/// A JSON path that specifies the object or the array to extract.<br/><br/>
/// In SQL Server 2017 and in Azure SQL Database, you can provide a variable as the value of <i>path</i>.<br/><br/>
/// The JSON path can specify lax or strict mode for parsing.If you don't specify the parsing mode, lax mode is the default. For more info, see <see cref="https://docs.microsoft.com/en-us/sql/relational-databases/json/json-path-expressions-sql-server">JSON Path Expressions (SQL Server)</see>.<br/><br/>
/// The default value for path is '$'. As a result, if you don't provide a value for path, <b>JSON_QUERY</b> returns the input <i>expression</i>.<br/><br/>
/// If the format of <i>path</i> isn't valid, <b>JSON_QUERY</b> returns an error.
/// </param>
/// <returns>
/// Returns a JSON fragment of type T. The collation of the returned value is the same as the collation of the input expression.<br/><br/>
/// If the value is not an object or an array:
/// <ul>
/// <li>In lax mode, <b>JSON_QUERY</b> returns null.</li>
/// <li>In strict mode, <b>JSON_QUERY</b> returns an error.</li>
/// </ul>
/// </returns>
public static string JsonQuery(string expression) => string.Empty;
public static T JsonQuery<T>(string expression) => default(T);
// SQL Server 2017+
public static string JsonQuery(string expression, string path) => string.Empty;
// SQL Server 2017+
public static T JsonQuery<T>(string expression, string path) => default(T);
}
}