You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "FlyingZC (via GitHub)" <gi...@apache.org> on 2023/08/02 10:01:18 UTC
[GitHub] [shardingsphere] FlyingZC opened a new issue, #27806: Support parsing openGauss alter sql
FlyingZC opened a new issue, #27806:
URL: https://github.com/apache/shardingsphere/issues/27806
# Background
Hi community,
ShardingSphere parser engine helps users parse a SQL to get the AST (Abstract Syntax Tree) and visit this tree to get SQLStatement (Java Object). Currently, we are planning to enhance the support for openGauss SQL parsing in ShardingSphere.
More details:
https://shardingsphere.apache.org/document/current/en/reference/sharding/parse/
# Task
This issue is to support more openGauss sql parse, as follows:
```sql
alter server server_test options(user 'my_user', password 'mypassword');
WARNING: USER MAPPING for current user to server server_test created.
ALTER SERVER
-- 当前用户到给定server的用户映射已存在时
```
```sql
//DBtest.java
//演示基于JDBC开发的主要步骤，会涉及创建数据库、创建表、插入数据等。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.Types;
public class DBTest {
//创建数据库连接。
public static Connection GetConnection(String username, String passwd) {
String driver = "org.opengauss.Driver";
String sourceURL = "jdbc:opengauss://localhost:8000/postgres";
Connection conn = null;
try {
//加载数据库驱动。
Class.forName(driver).newInstance();
} catch (Exception e) {
e.printStackTrace();
return null;
}
try {
//创建数据库连接。
conn = DriverManager.getConnection(sourceURL, username, passwd);
System.out.println("Connection succeed!");
} catch (Exception e) {
e.printStackTrace();
return null;
}
return conn;
};
//执行普通SQL语句，创建customer_t1表。
public static void CreateTable(Connection conn) {
Statement stmt = null;
try {
stmt = conn.createStatement();
//执行普通SQL语句。
int rc = stmt
.executeUpdate("CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));");
stmt.close();
} catch (SQLException e) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
}
}
//执行预处理语句，批量插入数据。
public static void BatchInsertData(Connection conn) {
PreparedStatement pst = null;
try {
//生成预处理语句。
pst = conn.prepareStatement("INSERT INTO customer_t1 VALUES (?,?)");
for (int i = 0; i < 3; i++) {
//添加参数。
pst.setInt(1, i);
pst.setString(2, "data " + i);
pst.addBatch();
}
//执行批处理。
pst.executeBatch();
pst.close();
} catch (SQLException e) {
if (pst != null) {
try {
pst.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
}
}
//执行预编译语句，更新数据。
public static void ExecPreparedSQL(Connection conn) {
PreparedStatement pstmt = null;
try {
pstmt = conn
.prepareStatement("UPDATE customer_t1 SET c_customer_name = ? WHERE c_customer_sk = 1");
pstmt.setString(1, "new Data");
int rowcount = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
}
}
//执行存储过程。
public static void ExecCallableSQL(Connection conn) {
CallableStatement cstmt = null;
try {
// 存储过程TESTPROC需提前创建。
cstmt=conn.prepareCall("{? = CALL TESTPROC(?,?,?)}");
cstmt.setInt(2, 50);
cstmt.setInt(1, 20);
cstmt.setInt(3, 90);
cstmt.registerOutParameter(4, Types.INTEGER); //注册out类型的参数，类型为整型。
cstmt.execute();
int out = cstmt.getInt(4); //获取out参数
System.out.println("The CallableStatment TESTPROC returns:"+out);
cstmt.close();
} catch (SQLException e) {
if (cstmt != null) {
try {
cstmt.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
}
}
/**
* 主程序，逐步调用各静态方法。
* @param args
*/
public static void main(String[] args) {
//创建数据库连接。
Connection conn = GetConnection("tester", "Password1234");
//创建表。
CreateTable(conn);
//批插数据。
BatchInsertData(conn);
//执行预编译语句，更新数据。
ExecPreparedSQL(conn);
//执行存储过程。
ExecCallableSQL(conn);
//关闭数据库连接。
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
```
```sql
// 关闭掉自动提交
conn.setAutoCommit(false);
Statement st = conn.createStatement();
// 打开游标，每次获取50行数据
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next())
{
System.out.print("a row was returned.");
}
conn.commit();
rs.close();
// 关闭服务器游标。
st.setFetchSize(0);
rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next())
{
System.out.print("many rows were returned.");
}
conn.commit();
rs.close();
// Close the statement.
st.close();
conn.close();
```
```sql
conn.setAutoCommit(true);
```
```sql
//bit类型使用示例，注意此处bit类型取值范围[0,1]
Statement st = conn.createStatement();
String sqlstr = "create or replace function fun_1()\n" +
"returns bit AS $$\n" +
"select col_bit from t_bit limit 1;\n" +
"$$\n" +
"LANGUAGE SQL;";
st.execute(sqlstr);
CallableStatement c = conn.prepareCall("{ ? = call fun_1() }");
//注册输出类型，位串类型
c.registerOutParameter(1, Types.BIT);
c.execute();
//使用Boolean类型获取结果
System.out.println(c.getBoolean(1));
// money类型使用示例
// 表结构中包含money类型列的使用示例。
st.execute("create table t_money(col1 money)");
PreparedStatement pstm = conn.prepareStatement("insert into t_money values(?)");
// 使用PGobject赋值，取值范围[-92233720368547758.08，92233720368547758.07]
PGobject minMoney = new PGobject();
minMoney.setType("money");
minMoney.setValue("-92233720368547758.08");
pstm.setObject(1, minMoney);
pstm.execute();
// 使用PGMoney赋值,取值范围[-9999999.99,9999999.99]
pstm.setObject(1,new PGmoney(9999999.99));
pstm.execute();
// 函数返回值为money的使用示例。
st.execute("create or replace function func_money() " +
"return money " +
"as declare " +
"var1 money; " +
"begin " +
" select col1 into var1 from t_money limit 1; " +
" return var1; " +
"end;");
CallableStatement cs = conn.prepareCall("{? = call func_money()}");
cs.registerOutParameter(1,Types.DOUBLE);
cs.execute();
cs.getObject(1);
```
# Process
1. First confirm that this is a correct openGauss sql syntax, if not please leave a message under the issue and ignore it;
2. Compare SQL definitions in Official SQL Doc and ShardingSphere SQL Doc;
3. If there is any difference in ShardingSphere SQL Doc, please correct them by referring to the Official SQL Doc;
4. Run mvn install the current_file_module;
5. Check whether there are any exceptions. If indeed, please fix them. (Especially xxxVisitor.class);
6. Add new corresponding SQL case in SQL Cases and expected parsed result in Expected Statement XML;
7. Run SQLParserParameterizedTest to make sure no exceptions.
# Relevant Skills
1. Master JAVA language
2. Have a basic understanding of Antlr `g4` file
3. Be familiar with openGauss SQLs
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support parsing openGauss alter sql [shardingsphere]
Posted by "FlyingZC (via GitHub)" <gi...@apache.org>.
FlyingZC closed issue #27806: Support parsing openGauss alter sql
URL: https://github.com/apache/shardingsphere/issues/27806
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] commented on issue #27806: Support parsing openGauss alter sql
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27806:
URL: https://github.com/apache/shardingsphere/issues/27806#issuecomment-1703266836
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support parsing openGauss alter sql [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27806:
URL: https://github.com/apache/shardingsphere/issues/27806#issuecomment-1783911467
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org