KDb/TODOs/User data: Difference between revisions

From KDE Community Wiki
< KDb‎ | TODOs
No edit summary
mNo edit summary
 
(12 intermediate revisions by the same user not shown)
Line 1: Line 1:
Design page for task: '''Support for user data storage'''.
Design page for task: '''Support for user data storage'''.


*Status: DONE
*Started: [[User:Jstaniek|Jstaniek]] ([[User talk:Jstaniek|talk]]) 11:01, 13 August 2012 (BST)
*Started: [[User:Jstaniek|Jstaniek]] ([[User talk:Jstaniek|talk]]) 11:01, 13 August 2012 (BST)
*{{Wish|??}}
*{{Wish|305074}}


__TOC__
__TOC__
Line 16: Line 17:
*Backend independence - should work with the SQLite driver as well the server backends (any table-based backends)
*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: achieved using standard db table, related to objects
*Portability to Predicate and 100% schema-compatible with Predicate
**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
**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 schema==
  kexi__userdata
  kexi__userdata
   d_user Text NOT NULL            -- user name
   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
   o_id Integer UNSIGNED NOT NULL  -- n-to-1 relationship to kexi__objects.o_id
   d_sub_id Text NOT NULL           -- sub-identifier of the data
   d_sub_id Text NOT NULL NOT EMPTY -- sub-identifier of the data
                                     -- (not i18n'd, dependent on object type, should not be tool long)
                                     -- (not i18n'd, dependent on object type,
                                    --  should not be too long)
   d_data LongText                  -- actual data serialized to text
   d_data LongText                  -- actual data serialized to text


Notes:
Notes:
*cascade update and delete of kexi__userdata records should happen based on kexi__objects
*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
*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
*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

Latest revision as of 18:43, 6 April 2023

Design page for task: Support for user data storage.

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