Changes in StoneDB_5.7_v1.0.2 (2023-01-14, Release Candidate)

Big features:

• support User-Defined Functions(UDFs) to handle the dynamic SQL:

create function function_name();
create function function_name(args...);
create function function_name(args...) returns varchar(50)
begin
  SET @sql= select * from where id= args;
  PREPARE s1 FROM @sql;
  EXECUTE s1;
  DEALLOCATE PREPARE s1;
end $$

• Support ESCAPE Keyword;

% :
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '\\';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE '';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n';
_ :
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\_' ESCAPE '\\';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan_' ESCAPE '';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n_' ESCAPE '\n';

• Add syntax support for constraints primary key and index: Add primary key

ALTER  TABLE t2 ADD id INT AUTO_INCREMENT PRIMARYKEY;
CREATE TABLE `table_name` (`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主表id',`name` string NOT NULL COMMENT '名称') ;

Drop primary key

DROP INDEX PRIMARYON t;
ALTER TABLE  tbl_name  DROP {INDEX|KEY} index_name

Add index

ALTER TABLE  tbl_name  ADD {FULLTEXT|SPATIAL} [INDEX|KEY][index_name]
CREATE TABLE t1 (age INT, INDEX USING BTREE (age)) ENGINE = Tianmu;

Drop index

DROP  INDEX  index_name  ON  tbl_name;
ALTER TABLE  tbl_name  DROP {INDEX|KEY} index_name

Rename index

  ALTER TABLE  tbl_name   RENAME {INDEX|KEY} old_index_nameTOnew_index_name

• Support changing the character set of tables/fields by using the alter table syntax；

Change character set of table:

ALTER TABLE tablename convert to character set utf8;

Change character set of table field:

ALTER TABLE tablename MODIFY latin1_text_col TEXT CHARACTERSET utf8;
ALTER TABLE tablename MODIFY latin1_varchar_col VARCHAR(M)  CHARACTERSET utf8;
ALTER TABLE tablename MODIFY latin1_text_col CHAR CHARACTERSET utf8;
ALTER TABLE tablename CHANGE colname colname TEXT CHARACT
ER SET utf8;
ALTER TABLE tablename CHANGE colname colname VARCHAR(M) CACTER SET utf8;
ALTER TABLE tablename CHANGE colname colname CHAR CHARACTER SET utf8;

• Support BIT Data Type:

CREATE TABLE tablename(colname BIT(8));
ALTER TABLE tablename add colname BIT(8) comment '';
ALTER TABLE tablename modify column colname BIT(4);
ALTER TABLE tablename modify column colname varchar(20) ;

• Support replace into SQL Syntax；

REPLACE INTO tablename VALUES(1,'new','2013-07-22 10:30:00');
REPLACE INTO tablename VALUES(1,'Old','2014-08-20 18:47:00');

• Syntactically support unsigned and zerofill:

CREATE  TABLE tablename (colname int(4) unsigned zerofill);
ALTER TABLE tablename change column colname colname int(4) unsigned zerofill;
ALTER TABLE tablename change column colname colname int(4);

• The sql_mode adds the mandatory attribute to make the tianmu as the default: Notes: sql_mode ='MANDATORY_TIANMU' > default_storage_engine Global

set global sql_mode='MANDATORY_TIANMU';

session

set sql_mode='MANDATORY_TIANMU';

my.cnf

[mysqld] 
sql_mode ='MANDATORY_TIANMU'

startup script

./mysqld --sql-mode='MANDATORY_TIANMU'

• Complete mtr test.

Accessibility Notes

• Automatic detection of installation package and and identification ability.
• Rapid deployment as a secondary database for MySQL.

Docs: The manual has been updated as the code was modified. ( #doc)

• Bug fix: #938 #925 #829 #781 #818 #819 #273 #271 #984 #538 #558 #909 #964 #988 #1034 #581 #944 #763 #880 #965 #1075 #767 #995 #975 #656 #850 #852 #760