[Tutorial] Read-Only in Base

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Tutorial] Read-Only in Base

Post by Villeroy »

0. All input forms in Base indicate "read-only" in the title bar, but this does not matter at all.
Form controls are attached to ordinary Writer documents. The title bar of such form document indicates: "database.odb Form Name (read-only)".
Window title of a database form with database name, form name, indicating Writer's read.only status
Window title of a database form with database name, form name, indicating Writer's read.only status
DBFormWinTitle.png (6.95 KiB) Viewed 22376 times
The read-only refers to the Writer document only. In fact you can not modify anything in the surrounding Writer document. You can use the form controls as they have been designed to be used.
You edit the database through form controls attached to a read-only document, which is perfectly OK.
 Edit: SInce version 4 the "(read only)" hint is no longer in the title bar. 
1. The type of database matters.
After creating a new database from scratch, the status bar of that database's main window indicates "embedded HSQLDB" which is the type of the actually used database (Base is not a database program!).
Image
HSQLDB is a more or less full featured database, contrary to pseudo-database connections to csv files, spreadsheets and address books from e-mail applications. Base can not edit the latter types. You have to edit these in their respective application.
"True databases" are editable and may also implement a system of privileges with read and write permissions for logged-in users.
Status bar of a database document connected to a HSQLDB server on a local network with a logged-in user named "Röntgen"
Status bar of a database document connected to a HSQLDB server on a local network with a logged-in user named "Röntgen"
DB_Statusbar.png (3.62 KiB) Viewed 22376 times
2. The existence of a primary key.
Tools>Relations... indicates which tables have relations and that all the relations use primary keys (yellow key icons). Primary keys provide unique pointers to each single row of a table (some ID number). A primary key makes tables editable in Base.
The only exception to this rule is a connection to a directory of dBase (*.dbf) files which is editable even without primary key.
Status bar of a database document that has been connected to the "Bibliography" example database which is a directory of dBase files.
Status bar of a database document that has been connected to the "Bibliography" example database which is a directory of dBase files.
dBaseStatusBar.png (3.29 KiB) Viewed 22376 times
In table design you right-click some field(s) and set option "Primary key". For a composite PK you can Ctrl+Click to select more than one field.
Setting a primary key in the table editor (in German screenshot: "Primärschlüssel")
Setting a primary key in the table editor (in German screenshot: "Primärschlüssel")
Linking primary keys(1) and foreign keys(n) in the relations window
Linking primary keys(1) and foreign keys(n) in the relations window
relations.png (10.4 KiB) Viewed 25850 times
3. Naked data (no calculation results) from one or more tables including the primary key(s).
When a form is connected to some calculated row set, the values can not be edited as a matter of course. You can not edit a sum. Same with other row sets when they join more than one table.
You can edit tables as well as queries from a table if the table's primary key is included.
 Edit: I forgot: All views are read-only. All queries in direct SQL mode are read-only. 
 Edit: Important improvement in v3.3: An editable row set may include more than one table with the full set of all primary keys included. Before 3.3 row sets had to come from a single table in order to be editable. 
Base queries with a primary key plus calculated fields are editable except for the calculations.
SELECT * , "A" * "B" AS "Product" FROM "TABLE" is editable except for the calculated "Product"
All aggregations with "GROUP BY", sum, average, min, max and so on are not editable since each row in the result set represents more than one table row, so it is impossible to fetch a particular row for editing.

Base forms serve one main purpose: They let you edit relations between tables. The most important tools to edit relations are subforms and list boxes. A pair of form and subform lets you edit two related row sets while both forms refer to separated row sets of a single table. When your parent form is linked to an editable row set (single table with primary key) and the subforms are linked to other editable row sets, then all your form data will be editable.
Within the (sub-)forms list boxes are the key elements to edit relations: A list box lets you pick a mnemonic name and select another table's primary key into the current record''s foreign key. See [Example] Relations reflected by list boxes in forms

4. Locking on form level.
Finally, the creator of a form may restrict an input form to not allow editing the underlying table although the row set may be editable otherwise. Designing differently structured forms with different permissions for different data entry jobs can be very useful and efficient.
Last edited by Villeroy on Mon Feb 19, 2018 10:04 pm, edited 15 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: [Tutorial] Read-Only in Base

Post by r4zoli »

Addition to the section: 0. All input forms in Base indicate "read-only" in the title bar.

From OOo/LibO 3.4, the forms title bar the "read-only" text was removed.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Post Reply