index=True parameter silently ignored with SQLite backend

[irossett]

Description

When using index=True parameter in entity attribute definitions with SQLite backend, the index is not created and no warning is issued. This creates inconsistent behavior between database backends and can lead to performance issues in production.

Environment

• PonyORM version: 0.7.19
• Database: SQLite
• Python version: 3.13.5
• Operating System: Endeavour OS

Expected Behavior

When defining an entity attribute with index=True, an index should be created on the corresponding database column, regardless of the database backend used.

Actual Behavior

With SQLite backend, the index=True parameter is silently ignored and no index is created.

Code to Reproduce

from pony.orm import *

db = Database()

class Document(db.Entity):
    id = PrimaryKey(int, auto=True)
    path = Required(str, index=True)  # This should create an index

db.bind('sqlite', 'test.db')
db.generate_mapping(create_tables=True)

# Verify indexes in SQLite
import sqlite3
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute("PRAGMA index_list('Document')")
print("Indexes found:", cursor.fetchall())  # Only shows automatic primary key indexes

Verification

The issue can be verified by:

1. Creating a table with index=True parameter
2. Checking the SQLite database with PRAGMA index_list('table_name')
3. Using "DB Browser for SQLite" and checking the "Indexes" section - only automatic primary key indexes are shown
4. Observing that no custom index is created for the index=True attribute

I have verified this using multiple approaches:

• Command line SQLite: PRAGMA index_list('Document') returns only automatic primary key indexes
• DB Browser for SQLite: Only automatic primary key indexes appear in the "Indexes" section, no custom indexes for the path field
• Python sqlite3 module: Programmatic verification confirms no custom indexes exist for attributes marked with index=True

Only when using manual SQL creation with db.execute("CREATE INDEX IF NOT EXISTS idx_document_path ON Document(path)") do the custom indexes appear in all verification methods.

Workaround

Currently, indexes must be created manually using SQL:

@db_session
def create_indexes():
    db.execute("CREATE INDEX IF NOT EXISTS idx_document_path ON Document(path)")

db.generate_mapping(create_tables=True)
create_indexes()

Impact

This issue can cause:

• Performance degradation in production when developers expect indexes to be created automatically
• Inconsistent behavior between different database backends
• Silent failures as no warning is issued when the parameter is ignored
• Debugging difficulties when trying to understand why queries are slow

Suggested Solutions

1. Fix the implementation to create indexes with SQLite when index=True is specified
2. Add a warning when index=True is used with SQLite if the feature is not supported
3. Update documentation to clearly state which database backends support automatic index creation
4. Add database-agnostic index creation that works consistently across all supported backends

Additional Context

This behavior is particularly problematic because:

• It works silently (no error or warning)
• Other database backends may handle this differently
• It's not documented in the main attribute documentation
• Developers migrating from other ORMs expect this basic functionality to work

The issue affects query performance significantly when working with larger datasets, as demonstrated by the performance difference before and after manually creating the index with SQL.