AUTOINCREMENT support

[newmanjeff]

Is there any way to specify that a table's primary key should be AUTOINCREMENT?

[warmwaffles]

@newmanjeff I think we can, there is overhead associated with it https://www.sqlite.org/autoinc.html

[warmwaffles]

I think the area to do that would be here

ecto_sqlite3/lib/ecto/adapters/sqlite3/connection.ex

Lines 1731 to 1750 in d4689ab

	defp composite_pk_definition(%Table{} = table, columns) do
	pks =
	Enum.reduce(columns, [], fn {_, name, _, opts}, pk_acc ->
	case Keyword.get(opts, :primary_key, false) do
	true -> [name | pk_acc]
	false -> pk_acc
	end
	end)
	if length(pks) > 1 do
	composite_pk_expr = pks |> Enum.reverse() |> Enum.map_join(", ", &quote_name/1)
	{
	%{table | primary_key: :composite},
	", PRIMARY KEY (" <> composite_pk_expr <> ")"
	}
	else
	{table, ""}
	end
	end

Just need to handle the case where two primary keys are provided and not to use autoincrement in that case.

[newmanjeff]

Thanks -- I'll take a stab at it in a bit.

[warmwaffles]

Perhaps it could also be an opt in using the opts?

[newmanjeff]

I added an MR that includes AUTOINCREMENT for :bigserial. :serial was already set as AUTOINCREMENT, so it seems inconsistent that :bigserial would not be marked that same way. The option to disable auto-increment exists by using a different column type (e.g. :id). I think this follows the principal of least-surprise on "what does the data type :bigserial mean for ecto_sqlite3."

My only hesitation here is backwards-compatibility. The resulting schema will be different if the database was created on an older version vs this version. :bigserial column types will be common since it's the default primary key for ecto. I could add an Application env option to control it if you think that would be better.

[warmwaffles]

A lot of the issues I am grappling with is trying to maintain some backwards compatibility with old the old ecto2 sqlite adapter and decisions made there and this one.

I think, let's just rip the band-aid off and force the issue. If we backwards compatibility is absolutely necessary, we can add in an option to turn off the new behavior.

[newmanjeff]

Sounds good to me.

[joeljuca]

I just stumbled upon this issue after searching about how ecto_sqlite3 deals with AUTOINCREMENT. Considering that it brings some known overhead to SQLite tables, and that INTEGER PRIMARY KEY columns are aliased to the SQLite-internal rowid column, why not make primary key definitions default to INTEGER PRIMARY KEY?

[tahirmurata]

I just stumbled upon this issue after searching about how ecto_sqlite3 deals with AUTOINCREMENT. Considering that it brings some known overhead to SQLite tables, and that INTEGER PRIMARY KEY columns are aliased to the SQLite-internal rowid column, why not make primary key definitions default to INTEGER PRIMARY KEY?

Any updates on this?

[warmwaffles]

No updates on this. I haven't explored this more. If you want to, you can take a stab at it 😄