Any chance of adding AUTOINCREMENT? about activerecord-enhancedsqlite3-adapter HOT 4 CLOSED

Yes and no.

It's an experimental project. I'm playing with making a graph db implementation for the LiteStack.

I wanted to use a composite key instead of an index because it means the rows will be stored together. I realize the performance gains to read speeds would probably be tiny, even for a very big SQLite db—but I figured the performance hit on write speeds would be similarly negligible, and I had an inkling that it would be useful down the line.

Since it's proving difficult to implement, it's probably not worth the trouble. I'll most likely drop the idea and go back to using a unique index, as you suggest.

from activerecord-enhancedsqlite3-adapter.

Can you describe the shape of your composite primary keys?

from activerecord-enhancedsqlite3-adapter.

Here's the schema as it currently stands. I'd like nodes.id to increment.

ActiveRecord::Schema[7.1].define(version: 2023_09_28_231239) do
  create_table "links", primary_key: ["type", "source_id", "target_id", "metadata"], force: :cascade do |t|
    t.text "type", null: false
    t.integer "source_id", null: false
    t.integer "target_id", null: false
    t.json "metadata", default: {}
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["source_id"], name: "index_links_on_source_id"
    t.index ["target_id"], name: "index_links_on_target_id"
  end

  create_table "nodes", primary_key: ["type", "id"], force: :cascade do |t|
    t.text "type", null: false
    t.integer "id", null: false
    t.json "data", default: {}, null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  add_foreign_key "links", "nodes", column: "source_id"
  add_foreign_key "links", "nodes", column: "target_id"
end

from activerecord-enhancedsqlite3-adapter.

That's what I worried was the case. We can't add an auto_increment option to individual columns. SQLite only supports auto-increment on a integer primary key column:

AUTOINCREMENT is not allowed on any table column other than INTEGER PRIMARY KEY. Any attempt to use AUTOINCREMENT on a column other than the INTEGER PRIMARY KEY column results in an error.
— https://www.sqlite.org/autoinc.html

Are you certain you need a composite PRIMARY KEY? What about simply adding a UNIQUE INDEX on the set of columns (see: https://stackoverflow.com/a/6154876/2884386)?

from activerecord-enhancedsqlite3-adapter.