GlueSQL AUTO_INCREMENT default implementation

[KyGost]

Re: #186 (comment)

Proposed feature: AUTO_INCREMENT

Type: Column Option

Constraints: Allows column options:

Option	Compatibility	Notes
Data Type	INT	INT only
_ NULL	✔️	Allows NULL or NOT NULL though under the default implementation NULL value will unlikely be stored.
DEFAULT	❌
_ KEY	✔️
UNIQUE	✔️

Any combination of allowed options is supported.

Example call:

CREATE TABLE test (
  id INT AUTO_INCREMENT,
  name TEXT
)

Example function:

INSERT INTO test (
  name
) VALUES (
  "John"
)
-- > (1, "John")

INSERT INTO test (
  name
) VALUES (
  "Mary"
),(
  "Jane"
)
-- > (1, "John"), (2, "Mary"), (3, "Jane")

INSERT INTO test (
  id,
  name
) VALUES (
  NULL,
  "Greg"
),(
  73,
  "April"
)
-- > (1, "John"), (2, "Mary"), (3, "Jane"), (4, "Greg"), (73, "April")

INSERT INTO test (
  name
) VALUES (
  "Robert"
)
-- > (1, "John"), (2, "Mary"), (3, "Jane"), (4, "Greg"), (73, "April"), (5, "Robert")

RESEED(test, 10)

INSERT INTO test (
  name
) VALUES (
  "Alfred"
)
-- > (1, "John"), (2, "Mary"), (3, "Jane"), (4, "Greg"), (73, "April"), (5, "Robert"), (10, "Alfred")

Influence: Transact-SQL (t-sql)/MSSQL's IDENTITY

Function: Declaring AUTO_INCREMENT on a column will make it default to a generated value each time the column is inserted as:

1. NULL
2. Empty

The generated value will always be stored value plus the index of the position in an insert query (0 initial).
The stored value will always be, in order of priority:

1. Last reseed value, if more recent than 2
2. Last inserted generated value plus 1
3. 1

Notes: AUTO_INCREMENT should behave quite similarly to DEFAULT.

[panarch]

Let's make a full spec document, I'll leave comments and you can update the proposal.
Currently, we don't support PRIMARY KEY, so what can be with AUTO_INCREMENT?
Does it only allow AUTO_INCREMENT with UNIQUE NOT NULL AUTO_INCREMENT (without DEFAULT)?
or other combinations can be also allowed NOT NULL AUTO_INCREMENT or UNIQUE NULL AUTO_INCREMENT?

[panarch]

NULL: Any - Allows NULL or NOT NULL though NULL value should never be stored.
NULL is not allowed then we might allow only NOT NULL..?

If it only allows NOT NULL then all possible combinations are these two

• INT NOT NULL AUTO_INCREMENT
• INT NOT NULL UNIQUE AUTO_INCREMENT

[KyGost]

It allows NULL, no reason not to. It just won't be used.
This allows us to use defaults and such.
Perhaps we will have optional implementations in which NULL doesn't trigger generator too (in which case you could insert a NULL).

[panarch]

Let's exclude the optional one for now.

Based on the meaning of column option,
NOT NULL simply does not allow to have NULL value and NULL allows to have NULL value.

So.. NULL value should never be stored -> exact case to have NOT NULL constraint.

If you consider to add NULL specific behavior later, then how about beginning with only allowing NOT NULL and expand to NULL?

[KyGost]

I'll change the wording from

NULL value should never be stored

to

Under the default implementation NULL value will unlikely be stored

[KyGost]

Sorted now?

[panarch]

btw, if we consider using RESEED, then current sqlparser does not support RESEED keyword. It shows ParseError.
Prior to implement the functionality, it is required to contribute sqlparser to support RESEED.

[KyGost]

RESEED actually isn't the MSSQL keyword ;-p
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql

[KyGost]

But nonetheless, I think RESEED would be ideal and as such will give it a go.

[KyGost]

Thoughts?

[panarch]

This sounds like a too big deal to add RESEED syntax to sqlparser which is not supported by any other sql database.

Let's.. get back to the beginning.
Now I see that your design of auto increment is quite different from any other ones.
There are lots of existing references.

• MySQL - AUTO_INCREMENT
• SQLite - AUTOINCREMENT
• MSSQL - IDENTITY
• PostgreSQL - SERIAL

I read all above documentation pages briefly and.. I thought that they all look good.
I wonder that what is the motivation to make new custom rather than picking one of existings.
For the simplicity, IDENTITY can be a good choice.
It is little bit hard for me to understand why we need a new system.

[KyGost]

With my proposed implementation we get the following advantages:

• Fast(er) (Auto Increment Columns #186 (comment))
• Closer to all standards
  • Expected name
  • Expected result (to an extent)
    • Increments by 1 integer value for each row, IDENTITY gives options and allows the starting value to be anyware

At the cost of:

• Not the same as one of the many existing standards

As for RESEED I was thinking it would be nice to have the keyword but we could also do

ALTER TABLE tablename AUTO_INCREMENT = value

https://arstechnica.com/civis/viewtopic.php?t=123689
Which is frankly a bit more intuitive in a way, I think it works well.