Bug #93763 Contribution of Sequence Engine
Submitted: 29 Dec 2018 8:59 Modified: 3 Jan 7:09
Reporter: Zhao Jianwei Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Storage Engines Severity:S4 (Feature request)
Version:8.0.13 OS:Linux
Assigned to: CPU Architecture:Any
Tags: contributions
Triage: Needs Triage: D5 (Feature request)

[29 Dec 2018 8:59] Zhao Jianwei
Description:
Hi  guys

We have been getting great convenience from MySQL open source project,  thanks for all your guys's great work.
I also faced some diffculty when we want to generate unique ID, so I developed a new storage engine named Sequence Engine, I also want to contribute to MySQL project.

Detailed information about it:

      [Feature] SEQUENCE ENGINE

      Description:
      ------------
      SEQUENCE engine as an embedded logical engine, it mainly used to generate unique number,
      it is the middle layer engine that use InnoDB or other storage engine as the based table engine,
      All query on sequence table will be changed into the operation on based table.
      the cache or other sequence value management is decided by SEQUENCE engine handler.

      According to the setting which is defined by 'CREATE SEQUENCE ... ' or 'CREATE TABLE...Engine=Sequence + INSERT VALUES'.
      user can query the nextval or currval from sequence object.
      In order to distinguish the normal SELECT statement, we supply two functions;
      
      'SELECT NEXTVAL FROM SEQUENCE' will return the based table record directly.
      'SELECT NEXTVAL(), CURRVAL()' will return the iterated record.

      Syntax:
      -------
      CREATE SEQUENCE SYNTAX:

      CREATE SEQUENCE [IF NOT EXISTS] schema.sequence_name
         [START WITH <constant>]
         [MINVALUE <constant>]
         [MAXVALUE <constant>]
         [INCREMENT BY <constant>]
         [CACHE <constant> | NOCACHE]
         [CYCLE | NOCYCLE]
        ;

      OR:
        CREATE TABLE schema.sequence_name (
        `currval` bigint(21) NOT NULL COMMENT 'current value',
        `nextval` bigint(21) NOT NULL COMMENT 'next value',
        `minvalue` bigint(21) NOT NULL COMMENT 'min value',
        `maxvalue` bigint(21) NOT NULL COMMENT 'max value',
        `start` bigint(21) NOT NULL COMMENT 'start value',
        `increment` bigint(21) NOT NULL COMMENT 'increment value',
        `cache` bigint(21) NOT NULL COMMENT 'cache size',
        `cycle` bigint(21) NOT NULL COMMENT 'cycle state',
        `round` bigint(21) NOT NULL COMMENT 'already how many round'
      ) ENGINE=Sequence DEFAULT CHARSET=latin1

      INSERT INTO schema.sequence_name VALUES(0,0,1,9223372036854775807,1,1,10000,1,0);
      COMMIT;

      Strongly recommend the first CREATE SEQUENCE syntax.

      SHOW SYNTAX:
        SHOW CREATE TABLE schema.sequence_name;

      QUERY SYNTAX:
        SELECT [nextval | currval | *] FROM schema.sequence_name;
        SELECT nextval(schema.sequence_name);
        SELECT currval(schema.sequence_name);

      Usage:
      ------
      FOR EXAMPLE:
       CREATE SEQUENCE s;
       INSERT INTO t VALUES(NEXTVAL(s));

How to repeat:
see Description;
[30 Dec 2018 6:21] Umesh Shastry
Hello Zhao,

Thank you for report and providing contribution.
Please ensure to re-send the patch via that tab. Otherwise we would not be able to accept it.

regards,
Umesh
[2 Jan 2:03] Zhao Jianwei
The patch diff file

Attachment: sequence.diff (application/octet-stream, text), 193.60 KiB.

[2 Jan 2:07] Zhao Jianwei
Hi,  Umesh

Thank you for your reply,  I have upload the patch file again,  pls check it.

Regards
Zhao Jianwei
[3 Jan 7:09] Zhao Jianwei
Hi

I have created a pull request on github repository,  please ignore the patch file.

https://github.com/mysql/mysql-server/pull/238

thanks again.

regards

Zhao jianwei
[9 Jan 20:36] Omer Barnir
Also see bug#93866