2021SC@SDUSC

#SQLConfig interface – configure SQL

The first is to divide all different databases to facilitate the matching of different databases

String DATABASE_MYSQL = "MYSQL";
	String DATABASE_POSTGRESQL = "POSTGRESQL";
	String DATABASE_SQLSERVER = "SQLSERVER";
	String DATABASE_ORACLE = "ORACLE";
	String DATABASE_DB2 = "DB2";
	String DATABASE_CLICKHOUSE = "CLICKHOUSE";

	String SCHEMA_INFORMATION = "information_schema";  //Mysql, PostgreSQL and SQL server all have system modes
	String SCHEMA_SYS = "sys";  //SQL Server system mode
	String TABLE_SCHEMA = "table_schema";
	String TABLE_NAME = "table_name";

Then the interface method is given:

	boolean isMySQL();
	boolean isPostgreSQL();
	boolean isSQLServer();
	boolean isOracle();
	boolean isDb2();
	boolean isClickHouse();

The function is to judge what type of database is currently used for subsequent operations

boolean limitSQLCount(); 

Here are some limitations:

The maximum single database size is unlimited
Maximum single table size 32 TB
Maximum 1.6 TB per record
1 GB maximum allowed for single field
The maximum number of records allowed in a single table is unlimited
Maximum number of fields in a single table 250 - 1600 (depending on field type)
The maximum number of indexes in a single table is unlimited

Of course, there is no real unlimited, but it is still restricted by the available disk space and available memory / swap area of the system. In fact, when these values become abnormally large, the system performance will also be greatly affected. Our limitSQLCount method is used to bypass Max by giving system attribute tables such as table and column_ SQL_ Count and other restrictions

Then there are some specific operations

String getDBVersion();

	String getDBUri();
	String getDBAccount();
	String getDBPassword();
	String getSQL(boolean prepared) throws Exception;

First, get the database version number. You can solve some compatibility problems of JDBC Driver connecting to the database by judging the version number, such as MYSQL: 8.0, 5.7, 5.6, etc; PostgreSQL: 11, 10, 9.6, etc
Then get the database address
Get the database account after
Get database password
Get SQL statement

Next, there are some get/set methods. These are some rules set by the author. When setting, the type is SQLConfig type, and when getting, they are their own attribute types

boolean isTest();
	SQLConfig setTest(boolean test);

	int getType();
	SQLConfig setType(int type);

	int getCount();
	SQLConfig setCount(int count);

	int getPage();
	SQLConfig setPage(int page);

	int getQuery();
	SQLConfig setQuery(int query);

	int getPosition();
	SQLConfig setPosition(int position);

	int getCache();
	SQLConfig setCache(int cache);

	boolean isExplain();
	SQLConfig setExplain(boolean explain);

    List<Join> getJoinList();

	SQLConfig setJoinList(List<Join> joinList);

For example, the function of setJoinList method:
In the analyzed AbstractSQLConfig class, there are its specific applications:

First, let's take a look at the specific data of the JOIN type

private String path;

	private String originKey;
	private String originValue;

	private String joinType; // "@" - APP, "<" - LEFT, ">" - RIGHT, "*" - CROSS, "&" - INNER, "|" - FULL, "!" - OUTER, "^" - SIDE, "(" - ANTI, ")" - FOREIGN
	private String relateType; // "" - one to one, "{}" - one to many, "< >" - many to one
	private JSONObject request; // { "id@":"/Moment/userId" }
	private String table; //User
	private String alias; //owner
	private String key; //id
	private String targetTable; // Moment
	private String targetAlias; //main
	private String targetKey; // userId

	private JSONObject outter;

	private SQLConfig joinConfig;
	private SQLConfig cacheConfig;
	private SQLConfig outterConfig;

This is the specific attributes of the JOIN type.
Then take a look at its specific:

	public static SQLConfig parseJoin(RequestMethod method, SQLConfig config, List<Join> joinList, Callback callback) throws Exception {
		boolean isQuery = RequestMethod.isQueryMethod(method);
		config.setKeyPrefix(isQuery && config.isMain() == false);

		//TODO parses SQLConfig and merges column, order, group, etc
		if (joinList == null || joinList.isEmpty() || RequestMethod.isQueryMethod(method) == false) {
			return config;
		}


		String table;
		String alias;
		for (Join j : joinList) {
			table = j.getTable();
			alias = j.getAlias();
			//The LIMIT cannot be set for the JOIN subquery, because the ON relationship is processed after the subquery, which will lead to incorrect results
			SQLConfig joinConfig = newSQLConfig(method, table, alias, j.getRequest(), null, false, callback);
			SQLConfig cacheConfig = j.canCacheViceTable() == false ? null : newSQLConfig(method, table, alias, j.getRequest(), null, false, callback).setCount(1);

			if (j.isAppJoin() == false) { //Except @ APP JOIN, all other are SQL joins. The secondary table should be configured in this way
				if (joinConfig.getDatabase() == null) {
					joinConfig.setDatabase(config.getDatabase()); //Resolve the inconsistency of quotation marks between the primary table and the secondary table
				}
				else if (joinConfig.getDatabase().equals(config.getDatabase()) == false) {
					throw new IllegalArgumentException("Main table " + config.getTable() + " of @database:" + config.getDatabase() + " And it SQL JOIN Secondary table of " + table + " of @database:" + joinConfig.getDatabase() + " atypism!");
				}
				if (joinConfig.getSchema() == null) {
					joinConfig.setSchema(config.getSchema()); //The primary table joins the secondary table, and the default schema is consistent
				}
				
				if (cacheConfig != null) {
					cacheConfig.setDatabase(joinConfig.getDatabase()).setSchema(joinConfig.getSchema()); //Resolve the inconsistency of quotation marks between the primary table and the secondary table
				}


				if (isQuery) {
					config.setKeyPrefix(true);
				}

				joinConfig.setMain(false).setKeyPrefix(true);

				if (j.isLeftOrRightJoin()) {
					SQLConfig outterConfig = newSQLConfig(method, table, alias, j.getOuter(), null, false, callback);
					outterConfig.setMain(false).setKeyPrefix(true).setDatabase(joinConfig.getDatabase()).setSchema(joinConfig.getSchema()); //Resolve the inconsistency of quotation marks between the primary table and the secondary table
					j.setOuterConfig(outterConfig);
				}
			}

			//Solve the problem of query: 1/2 query quantity times an error  
			/* SELECT  count(*)  AS count  FROM sys.Moment AS Moment  
			   LEFT JOIN ( SELECT count(*)  AS count FROM sys.Comment ) AS Comment ON Comment.momentId = Moment.id LIMIT 1 OFFSET 0 */
			if (RequestMethod.isHeadMethod(method, true)) {
				joinConfig.setMethod(GET); //Subquery cannot be SELECT count(*), but should be SELECT momentId
				joinConfig.setColumn(Arrays.asList(j.getKey())); //Optimize performance without taking unnecessary fields

				if (cacheConfig != null) {
					cacheConfig.setMethod(GET); //Subquery cannot be SELECT count(*), but should be SELECT momentId
					cacheConfig.setColumn(Arrays.asList(j.getKey())); //Optimize performance without taking unnecessary fields
				}
			}

			j.setJoinConfig(joinConfig);
			j.setCacheConfig(cacheConfig);
		}

		config.setJoinList(joinList);

		return config;
	}

Here, first judge the joinlist, then cycle through the joinlist, process each join separately, and then obtain the data in each join and add it to our SQLConfig. Then, set the joinlist of config to this joinlist, and then return to config