KDb/TODOs/User data

Design page for task: Support for user data storage.

Status: DONE
Started: Jstaniek (talk) 11:01, 13 August 2012 (BST)
Wish #305074

Rationale

Among others, this feature is needed by the Remember table column widths feature.

Requirements

Backward compatibility
- Older Kexi versions should just ignore the data
Per user-separation
- How: based on user name, this implies that updates are needed when user name changes
- How: in separate table kexi__userdata
Backend independence - should work with the SQLite driver as well the server backends (any table-based backends)
- How: achieved using standard db table, related to objects
- How: all users should have access to the settings
Portability to KDb and 100% schema-compatible with KDb
- How: API will change but table schema will stay the same
Lifetime
- Data should be created on demand, e.g. on first change of setting made by the user
- kexi__userdata table should be created on project creation, after kexi__objects is created
- For existing projects created with older KexiDB, kexi__userdata is missing. It should be created silently after opening (if there are permissions).
No-break behaviour for read-only connections
- In case of no write access (e.g. because of the read-only connection mode), it can logged to the debug output as a warning
Behaviour for User Mode connections
- User Mode should not limit the feature

kexi__userdata schema

kexi__userdata
  d_user Text NOT NULL             -- user name, NOT NULL but can be empty string "" 
                                   --  (useful for single user backend such as SQLite)
  o_id Integer UNSIGNED NOT NULL   -- n-to-1 relationship to kexi__objects.o_id
  d_sub_id Text NOT NULL NOT EMPTY -- sub-identifier of the data
                                   --  (not i18n'd, dependent on object type,
                                   --  should not be too long)
  d_data LongText                  -- actual data serialized to text

Notes:

Cascade update and delete of kexi__userdata records should happen based on kexi__objects
No primary key but (d_user, o_id, d_name) should be unique, so having a key would be useful
Data of global scope can be stored by adding a new 'data' object type, adding single record to kexi__objects, then relating to it in kexi__userdata

Extensions

Secrity for server databases:
- The problem: Currently, while Kexi does not fetch or modify other user's data, if the database is connected using administrative tools, with the same user's credentials as those used with Kexi, user can access or modify other user's data
- Solution: On creation, define database VIEW that gives access only to give user's data.
Alternative storage
- The problem: storing data in the project's main database may be not possible (read-only connection) or desired
- Solution: Create separate database for user data needs. Do not set this up by default.
  - The separate database can be kept in remotely-accessible area (e.g. on the server) or locally
  - Local separate database would improve experience in offline mode
  - Disadvantages of the local separate database:
    - data may be not accessible from all client computers
    - data may exists in different instances/copies if more than one client computer is, synchronization problems arise
  - The separate database, especially the local one, can be used for other purposes too, such as replication/caching