< Kexi | KexiDB Design page for task: Support for user data storage. Started: Jstaniek (talk) 11:01, 13 August 2012 (BST) Wish #305074 Contents 1 Rationale 2 Requirements 3 kexi__userdata schema 4 Extensions 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 Retrieved from "https://community.kde.org/index.php?title=Kexi/KexiDB/User_data&oldid=43999" This page was last edited on 23 August 2015, at 13:09. Content is available under Creative Commons License SA 4.0 unless otherwise noted.