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 09:25:16 UTC
[GitHub] [shardingsphere] FlyingZC opened a new issue, #27759: Support parsing openGauss DROP TEXT sql
FlyingZC opened a new issue, #27759:
URL: https://github.com/apache/shardingsphere/issues/27759
# 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
DROP TEXT SEARCH CONFIGURATION [ IF EXISTS ] name [ CASCADE | RESTRICT ];
```
```sql
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.opengauss.copy.CopyManager;
import org.opengauss.core.BaseConnection;
public class Migration{
public static void main(String[] args) {
String url = new String("jdbc:opengauss://localhost:8000/postgres"); //数据库URL
String user = new String("username"); //openGauss数据库用户名
String pass = new String("passwd"); //openGauss数据库密码
String tablename = new String("migration_table_1"); //定义表信息
String delimiter = new String("|"); //定义分隔符
String encoding = new String("UTF8"); //定义字符集
String driver = "org.opengauss.Driver";
StringBuffer buffer = new StringBuffer(); //定义存放格式化数据的缓存
try {
//获取源数据库查询结果集
ResultSet rs = getDataSet();
//遍历结果集，逐行获取记录
//将每条记录中各字段值，按指定分隔符分割，由换行符结束，拼成一个字符串
//把拼成的字符串，添加到缓存buffer
while (rs.next()) {
buffer.append(rs.getString(1) + delimiter
+ rs.getString(2) + delimiter
+ rs.getString(3) + delimiter
+ rs.getString(4)
+ "\n");
}
rs.close();
try {
//建立目标数据库连接
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, pass);
BaseConnection baseConn = (BaseConnection) conn;
baseConn.setAutoCommit(false);
//初始化表信息
String sql = "Copy " + tablename + " from STDIN with (DELIMITER " + "'" + delimiter + "'" +","+ " ENCODING " + "'" + encoding + "')";
//提交缓存buffer中的数据
CopyManager cp = new CopyManager(baseConn);
StringReader reader = new StringReader(buffer.toString());
cp.copyIn(sql, reader);
baseConn.commit();
reader.close();
baseConn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace(System.out);
} catch (SQLException e) {
e.printStackTrace(System.out);
}
} catch (Exception e) {
e.printStackTrace();
}
}
//********************************
// 从源数据库返回查询结果集
//*********************************
private static ResultSet getDataSet() {
ResultSet rs = null;
try {
Class.forName("com.MY.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:MY://10.119.179.227:3306/jack?useSSL=false&allowPublicKeyRetrieval=true", "jack", "Gauss@123");
Statement stmt = conn.createStatement();
rs = stmt.executeQuery("select * from migration_table");
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return rs;
```
```sql
// 此示例演示如何通过ODBC方式获取openGauss中的数据。
// DBtest.c (compile with: libodbc.so)
#include <stdlib.h>
#include <stdio.h>
#include <sqlext.h>
#ifdef WIN32
#include <windows.h>
#endif
SQLHENV V_OD_Env; // Handle ODBC environment
SQLHSTMT V_OD_hstmt; // Handle statement
SQLHDBC V_OD_hdbc; // Handle connection
char typename[100];
SQLINTEGER value = 100;
SQLINTEGER V_OD_erg,V_OD_buffer,V_OD_err,V_OD_id;
int main(int argc,char *argv[])
{
// 1. 申请环境句柄
V_OD_erg = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env);
if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
{
printf("Error AllocHandle\n");
exit(0);
}
// 2. 设置环境属性（版本信息）
SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
// 3. 申请连接句柄
V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc);
if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
{
SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
exit(0);
}
// 4. 设置连接属性
SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_ON, 0);
// 5. 连接数据源，这里的“userName”与“password”分别表示连接数据库的用户名和用户密码，请根据实际情况修改。
// 如果odbc.ini文件中已经配置了用户名密码，那么这里可以留空（""）；但是不建议这么做，因为一旦odbc.ini权限管理不善，将导致数据库用户密码泄露。
V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "gaussdb", SQL_NTS,
(SQLCHAR*) "userName", SQL_NTS, (SQLCHAR*) "password", SQL_NTS);
if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
{
printf("Error SQLConnect %d\n",V_OD_erg);
SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
exit(0);
}
printf("Connected !\n");
// 6. 设置语句属性
SQLSetStmtAttr(V_OD_hstmt,SQL_ATTR_QUERY_TIMEOUT,(SQLPOINTER *)3,0);
// 7. 申请语句句柄
SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt);
// 8. 直接执行SQL语句。
SQLExecDirect(V_OD_hstmt,"drop table IF EXISTS customer_t1",SQL_NTS);
SQLExecDirect(V_OD_hstmt,"CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));",SQL_NTS);
SQLExecDirect(V_OD_hstmt,"insert into customer_t1 values(25,li)",SQL_NTS);
// 9. 准备执行
SQLPrepare(V_OD_hstmt,"insert into customer_t1 values(?)",SQL_NTS);
// 10. 绑定参数
SQLBindParameter(V_OD_hstmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,
&value,0,NULL);
// 11. 执行准备好的语句
SQLExecute(V_OD_hstmt);
SQLExecDirect(V_OD_hstmt,"select id from testtable",SQL_NTS);
// 12. 获取结果集某一列的属性
SQLColAttribute(V_OD_hstmt,1,SQL_DESC_TYPE,typename,100,NULL,NULL);
printf("SQLColAtrribute %s\n",typename);
// 13. 绑定结果集
SQLBindCol(V_OD_hstmt,1,SQL_C_SLONG, (SQLPOINTER)&V_OD_buffer,150,
(SQLLEN *)&V_OD_err);
// 14. 通过SQLFetch取结果集中数据
V_OD_erg=SQLFetch(V_OD_hstmt);
// 15. 通过SQLGetData获取并返回数据。
while(V_OD_erg != SQL_NO_DATA)
{
SQLGetData(V_OD_hstmt,1,SQL_C_SLONG,(SQLPOINTER)&V_OD_id,0,NULL);
printf("SQLGetData ----ID = %d\n",V_OD_id);
V_OD_erg=SQLFetch(V_OD_hstmt);
};
printf("Done !\n");
// 16. 断开数据源连接并释放句柄资源
SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
SQLDisconnect(V_OD_hdbc);
SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
return(0);
```
```sql
/**********************************************************************
* 请在数据源中打开UseBatchProtocol，同时指定数据库中参数support_batch_bind
* 为on
* CHECK_ERROR的作用是检查并打印错误信息。
* 此示例将与用户交互式获取DSN、模拟的数据量，忽略的数据量，并将最终数据入库到test_odbc_batch_insert中
***********************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
void Exec(SQLHDBC hdbc, SQLCHAR* sql)
{
SQLRETURN retcode; // Return status
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLCHAR loginfo[2048];
// Allocate Statement Handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Prepare Statement
retcode = SQLPrepare(hstmt, (SQLCHAR*) sql, SQL_NTS);
sprintf((char*)loginfo, "SQLPrepare log: %s", (char*)sql);
CHECK_ERROR(retcode, loginfo, hstmt, SQL_HANDLE_STMT);
// Execute Statement
retcode = SQLExecute(hstmt);
sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql);
CHECK_ERROR(retcode, loginfo, hstmt, SQL_HANDLE_STMT);
// Free Handle
retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
sprintf((char*)loginfo, "SQLFreeHandle stmt log: %s", (char*)sql);
CHECK_ERROR(retcode, loginfo, hstmt, SQL_HANDLE_STMT);
}
int main ()
{
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
int batchCount = 1000;
SQLLEN rowsCount = 0;
int ignoreCount = 0;
SQLRETURN retcode;
SQLCHAR dsn[1024] = {'\0'};
SQLCHAR loginfo[2048];
// 交互获取数据源名称
getStr("Please input your DSN", (char*)dsn, sizeof(dsn), 'N');
// 交互获取批量绑定的数据量
getInt("batchCount", &batchCount, 'N', 1);
do
{
// 交互获取批量绑定的数据中，不要入库的数据量
getInt("ignoreCount", &ignoreCount, 'N', 1);
if (ignoreCount > batchCount)
{
printf("ignoreCount(%d) should be less than batchCount(%d)\n", ignoreCount, batchCount);
}
}while(ignoreCount > batchCount);
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Set ODBC Verion
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
// Allocate Connection
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
henv, SQL_HANDLE_DBC);
// Set Login Timeout
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
// Set Auto Commit
retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)(1), 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT)",
hdbc, SQL_HANDLE_DBC);
// Connect to DSN
sprintf(loginfo, "SQLConnect(DSN:%s)", dsn);
retcode = SQLConnect(hdbc, (SQLCHAR*) dsn, SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, loginfo, hdbc, SQL_HANDLE_DBC);
// init table info.
Exec(hdbc, "drop table if exists test_odbc_batch_insert");
Exec(hdbc, "create table test_odbc_batch_insert(id int primary key, col varchar2(50))");
// 下面的代码根据用户输入的数据量，构造出将要入库的数据:
{
SQLRETURN retcode;
SQLHSTMT hstmtinesrt = SQL_NULL_HSTMT;
int i;
SQLCHAR *sql = NULL;
SQLINTEGER *ids = NULL;
SQLCHAR *cols = NULL;
SQLLEN *bufLenIds = NULL;
SQLLEN *bufLenCols = NULL;
SQLUSMALLINT *operptr = NULL;
SQLUSMALLINT *statusptr = NULL;
SQLULEN process = 0;
// 这里是按列构造，每个字段的内存连续存放在一起。
ids = (SQLINTEGER*)malloc(sizeof(ids[0]) * batchCount);
cols = (SQLCHAR*)malloc(sizeof(cols[0]) * batchCount * 50);
// 这里是每个字段中，每一行数据的内存长度。
bufLenIds = (SQLLEN*)malloc(sizeof(bufLenIds[0]) * batchCount);
bufLenCols = (SQLLEN*)malloc(sizeof(bufLenCols[0]) * batchCount);
// 该行是否需要被处理，SQL_PARAM_IGNORE 或 SQL_PARAM_PROCEED
operptr = (SQLUSMALLINT*)malloc(sizeof(operptr[0]) * batchCount);
memset(operptr, 0, sizeof(operptr[0]) * batchCount);
// 该行的处理结果。
// 注：由于数据库中处理方式是同一语句隶属同一事务中，所以如果出错，那么待处理数据都将是出错的，并不会部分入库。
statusptr = (SQLUSMALLINT*)malloc(sizeof(statusptr[0]) * batchCount);
memset(statusptr, 88, sizeof(statusptr[0]) * batchCount);
if (NULL == ids || NULL == cols || NULL == bufLenCols || NULL == bufLenIds)
{
fprintf(stderr, "FAILED:\tmalloc data memory failed\n");
goto exit;
}
for (int i = 0; i < batchCount; i++)
{
ids[i] = i;
sprintf(cols + 50 * i, "column test value %d", i);
bufLenIds[i] = sizeof(ids[i]);
bufLenCols[i] = strlen(cols + 50 * i);
operptr[i] = (i < ignoreCount) ? SQL_PARAM_IGNORE : SQL_PARAM_PROCEED;
}
// Allocate Statement Handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtinesrt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmtinesrt, SQL_HANDLE_STMT);
// Prepare Statement
sql = (SQLCHAR*)"insert into test_odbc_batch_insert values(?, ?)";
retcode = SQLPrepare(hstmtinesrt, (SQLCHAR*) sql, SQL_NTS);
sprintf((char*)loginfo, "SQLPrepare log: %s", (char*)sql);
CHECK_ERROR(retcode, loginfo, hstmtinesrt, SQL_HANDLE_STMT);
retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)batchCount, sizeof(batchCount));
CHECK_ERROR(retcode, "SQLSetStmtAttr", hstmtinesrt, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmtinesrt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(ids[0]), 0,&(ids[0]), 0, bufLenIds);
CHECK_ERROR(retcode, "SQLBindParameter for id", hstmtinesrt, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmtinesrt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 50, 50, cols, 50, bufLenCols);
CHECK_ERROR(retcode, "SQLBindParameter for cols", hstmtinesrt, SQL_HANDLE_STMT);
retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAMS_PROCESSED_PTR, (SQLPOINTER)&process, sizeof(process));
CHECK_ERROR(retcode, "SQLSetStmtAttr for SQL_ATTR_PARAMS_PROCESSED_PTR", hstmtinesrt, SQL_HANDLE_STMT);
retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_STATUS_PTR, (SQLPOINTER)statusptr, sizeof(statusptr[0]) * batchCount);
CHECK_ERROR(retcode, "SQLSetStmtAttr for SQL_ATTR_PARAM_STATUS_PTR", hstmtinesrt, SQL_HANDLE_STMT);
retcode = SQLSetStmtAttr(hstmtinesrt, SQL_ATTR_PARAM_OPERATION_PTR, (SQLPOINTER)operptr, sizeof(operptr[0]) * batchCount);
CHECK_ERROR(retcode, "SQLSetStmtAttr for SQL_ATTR_PARAM_OPERATION_PTR", hstmtinesrt, SQL_HANDLE_STMT);
retcode = SQLExecute(hstmtinesrt);
sprintf((char*)loginfo, "SQLExecute stmt log: %s", (char*)sql);
CHECK_ERROR(retcode, loginfo, hstmtinesrt, SQL_HANDLE_STMT);
retcode = SQLRowCount(hstmtinesrt, &rowsCount);
CHECK_ERROR(retcode, "SQLRowCount execution", hstmtinesrt, SQL_HANDLE_STMT);
if (rowsCount != (batchCount - ignoreCount))
{
sprintf(loginfo, "(batchCount - ignoreCount)(%d) != rowsCount(%d)", (batchCount - ignoreCount), rowsCount);
CHECK_ERROR(SQL_ERROR, loginfo, NULL, SQL_HANDLE_STMT);
}
else
{
sprintf(loginfo, "(batchCount - ignoreCount)(%d) == rowsCount(%d)", (batchCount - ignoreCount), rowsCount);
CHECK_ERROR(SQL_SUCCESS, loginfo, NULL, SQL_HANDLE_STMT);
}
// check row number returned
if (rowsCount != process)
{
sprintf(loginfo, "process(%d) != rowsCount(%d)", process, rowsCount);
CHECK_ERROR(SQL_ERROR, loginfo, NULL, SQL_HANDLE_STMT);
}
else
{
sprintf(loginfo, "process(%d) == rowsCount(%d)", process, rowsCount);
CHECK_ERROR(SQL_SUCCESS, loginfo, NULL, SQL_HANDLE_STMT);
}
for (int i = 0; i < batchCount; i++)
{
if (i < ignoreCount)
{
if (statusptr[i] != SQL_PARAM_UNUSED)
{
sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_UNUSED", i, statusptr[i]);
CHECK_ERROR(SQL_ERROR, loginfo, NULL, SQL_HANDLE_STMT);
}
}
else if (statusptr[i] != SQL_PARAM_SUCCESS)
{
sprintf(loginfo, "statusptr[%d](%d) != SQL_PARAM_SUCCESS", i, statusptr[i]);
CHECK_ERROR(SQL_ERROR, loginfo, NULL, SQL_HANDLE_STMT);
}
}
retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmtinesrt);
sprintf((char*)loginfo, "SQLFreeHandle hstmtinesrt");
CHECK_ERROR(retcode, loginfo, hstmtinesrt, SQL_HANDLE_STMT);
}
exit:
printf ("\nComplete.\n");
// Connection
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
// Environment
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
```
```sql
cedric=# select * from pgfadvise_dontneed('pgbench_accounts');
```
# 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 DROP TEXT sql [shardingsphere]
Posted by "FlyingZC (via GitHub)" <gi...@apache.org>.
FlyingZC closed issue #27759: Support parsing openGauss DROP TEXT sql
URL: https://github.com/apache/shardingsphere/issues/27759
--
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 #27759: Support parsing openGauss DROP TEXT sql
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27759:
URL: https://github.com/apache/shardingsphere/issues/27759#issuecomment-1703268145
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 DROP TEXT sql [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27759:
URL: https://github.com/apache/shardingsphere/issues/27759#issuecomment-1783911732
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