AUTO_INCREMENT

[KyGost]

It would be quite nice to have an AUTO_INCREMENT feature for INTEGER columns.

There's a few approaches to how this could work though:

SQLite automatically acts on INTEGER PRIMARY KEY columns, backfilling where values are removed while the keyword specifies that backfill should not occur.
https://sqlite.org/autoinc.html

MySQL acts based on the current largest value in the column when the keyword is used.
https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

MSSQL stores a value and uses that. The value can be altered.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15

Anyone have any preference?

I'm not sure that I like how SQLite increments by default.

I kinda like the MySQL method; MSSQL sounds a bit messy and I worry for the potential issues of backfilling (SQLite).

[panarch]

I'm also familiar with MySQL's AUTO_INCREMENT syntax.

There can be something to consider about the implementation plan.
I'm not sure that current Store trait methods are enough to support AUTO_INCREMENT.
Then, we might need to add another optional store trait for this?

[KyGost]

Hmm
PostGreSQL does:
https://www.postgresql.org/docs/13/ddl-generated-columns.html

[KyGost]

I tried a number of ways writing it how I might expect it to be writable and with the current sqlparser, id INTEGER CONSTRAINT AUTO_INCREMENT NOT NULL seems to be the only reasonable way.

(With the NOT NULL always required)

Thoughts on AUTO_INCREMENT vs AUTOINCREMENT?

[KyGost]

nb:

id INTEGER CONSTRAINT AUTO_INCREMENT NOT NULL

Gives us:

[ColumnDef { name: Ident { value: "id", quote_style: None }, data_type: Int, collation: None, options: [ColumnOptionDef { name: Some(Ident { value: "AUTO_INCREMENT", quote_style: None }), option: NotNull }] }]

[KyGost]

CONSTRAINT AUTO_INCREMENT UNIQUE & CONSTRAINT AUTO_INCREMENT PRIMARY KEY also works

[KyGost]

I was trying to implement this using a validation check but it seems this would actually be quite simple by using the existing row/default code.

The question just becomes how to decide the value to use.

Doing a scan seems it would be rather expensive but once indexes are in (esp considering AUTO_INCREMENT will probably be very often PK) it shouldn't be too bad.

[KyGost]

Hmmmmm
That wouldn't work for multi row nor simultaneous inserts.
I suppose maybe the value will need to be stored in a schema.

If this is the case it would probably be most efficient to just use the MSSQL method of just storing and reseeding as needed.

If we wanted it to work otherwise it would probably need to check the value each update/insert (specified or not) and update it when that value is bigger &| maybe when values are deleted.

[KyGost]

@panarch do you think it would be better to add a generated parameter on schema or leave it to the store and add a new prefix for sled?

[panarch]

@KyGost
In the case of MySQL syntax, we might be able to use CREATE TABLE Test (id INTEGER AUTO_INCREMENT NOT NULL) without using CONSTRAINT keyword

..
ColumnOptionDef {
    name: None,
    option: DialectSpecific([
        Word(Word { value: "AUTO_INCREMENT", quote_style: None, keyword: AUTO_INCREMENT })
    ]) 
..

This above is supported by the latest version of sqlparser.

However, GlueSQL is currently using sqlparser@v0.6.1, the latest is v0.8.0. I'll migrate to newer ASAP.

The reason you needed to use CONSTRAINT keyword is because of sqlparser?

[KyGost]

@panarch
Ah awesome!

Because of splparser?

Yup.

[panarch]

And the design I briefly thought was something like this.

Adding optional store trait

pub trait AutoIncrement<T: Debug>  {
    fn generate_id() -> T;
}

and implementation idea for SledStorage

1. fetching the biggest item
2. add 1, and return

However, without index this above implementation plan can be quite inefficient, so it would be good to do something more on this, like you suggested in the thread.
(This would be ok with bulk insert INSERT INTO Table VALUES ("item1"), ("item2"), ...; but if someone tries to split insert into independent INSERT queries, then it can become O(n^2) hell.)

In anyway, the point would be... having store trait api as simple as possible

[panarch]

@KyGost 🚀 #160 🚀