Base documentation

11 replies [Last post]
oweng
oweng's picture
Offline
Last seen: 5 days 10 hours ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 3281
For the interest of Base users (new and experienced) I am starting this thread to keep track of documentation related to this aspect of LO. Documentation for this component has been sadly lacking, so hopefully this thread will go some way towards rectifying this situation. If anyone notices a thread or page, either on this forum or elsewhere, that they feel offers a particularly detailed and helpful account of some aspect of Base, please feel free to comment below and I will add the link to the list. Thanks.

2014-03-29, major update of information in this post, to be clearer, more inclusive, and hopefully more helpful.
2014-04-04, update to include a few additional links: Tutorials videos by TheFrugalComputerGuy (YouTube); Before you begin links to two LOF threads; create separate Database engines links section.
2014-04-12, minor update to include Apache OO club membership example under Tutorials; new Examples sub-section with a few threads from this forum; getting around the 256 output field limit in a query under Miscellaneous.
2014-06-29, minor update to include Database Programming with OpenOffice.org Base & Basic under Primary sources.
2014-07-19, added MariaDB entry under Database engines.
2014-07-20, added PoorSQL entry under Primary sources.
2014-10-12, added MySQL/MariaDB connector extension link against corresponding entries under Database engines.
2014-11-29, added two forum thread links under Split-mode section: reconnecting to a database; using the Switchboard extension.
2016-02-01, added SecretaryBird forum thread under Examples.
2016-02-04, added Access2Base link under Primary sources and How to store Impress and Writer files in Base forum thread under Miscellaneous.
2016-02-21, added AOF Database examples sub-forum link under Examples.

Primary sources

Database engines

  • Firebird (effort currently underway to make Firebird v2.5 the default database engine used in Base; Firebird v3.0 will likely be implemented as the default engine at a later point; reference manuals available via the documentation tab).
  • H2 (a.k.a. H2DB; Java-dependent; reference material available on main page).
  • HyperSQL (a.k.a. HSQL database / HSQLDB; Java-dependent; currently v1.8 is the default engine used in Base; v1.8 User Guide in PDF or HTML ; v2.3 User Guide and Utilities Guide).
  • MariaDB (is a “drop-in replacement for MySQL” of which it is a fork; reference manuals available via the Knowledge Base ). Native connector for Linux available here.
  • MySQL (free version is known as “Community edition”; reference manuals available via the documentation tab). Native connector for Linux available here.
  • PostgreSQL (reference manuals available via the documentation menu).

Tutorials

Forum threads / comments / links of importance

Key: AOF = Apache OpenOffice Forum, LOF = LibreOffice Forum (i.e., here).

Before you begin

Split-mode databases

Choosing a back-end database

Access methods

  • AOF, Running HSQLDB in server mode (for concurrent, read/write data access among multiple users on a local LAN).
  • AOF, Running HSQLDB in servlet mode (on a web server for internet access from Base).
  • LOF, Base on a website Note: The idea of “running” Base (the ODF front-end document) on a website, is somewhat misleading. What is usually implied is an “online web form builder / generator” as a web service to expose data from a back-end database.

Data recovery

Miscellaneous

Examples

frofa
frofa's picture
Offline
Last seen: 13 hours 17 min ago
Title: ★★★★★
Joined: 24 Feb 2012
Posts: 804
Mariano Casanova’s Base TUTORIAL is helpful
Mariano Casanova’s TUTORIAL called ‘Base Tutorial: From Newbie to Advocate in a one, two… three!’ is also very helpful.
oweng
oweng's picture
Offline
Last seen: 5 days 10 hours ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 3281
Robin Beaumont tutorial
I am not sure how helpful this tutorial is, but it does include example databases as well as PDFs. Originally linked in this post, but the domain has changed.
darkcity
darkcity's picture
Offline
Last seen: 10 weeks 6 days ago
Title: ★★★★★
Joined: 14 May 2013
Posts: 101
frofa wrote:Mariano
frofa wrote:
Mariano Casanova’s TUTORIAL called ‘From Newbie to Advocate in a one, two… three!’ is also very helpful.

This is the tutorial I’m using. Its well written starting with database theory, with the practical exercise covering the last third. It covers SQL in detail too.

When/if you get to page 86 it lists a link which is down-

http://documentation.openoffice.org/servlets/ProjectDocumentList?folderI...

Here is the code from the link-


    DROP TABLE "Patient Medication" IF EXISTS;
    DROP TABLE "Medication" IF EXISTS;
    DROP TABLE "Payment" IF EXISTS;
    DROP TABLE "Schedule" IF EXISTS;
    DROP TABLE "Assignment" IF EXISTS;
    DROP TABLE "Therapists Number" IF EXISTS;
    DROP TABLE "Phone Number" IF EXISTS;
    DROP TABLE "Patient" IF EXISTS;
    DROP TABLE "Psychiatrist" IF EXISTS;
    DROP TABLE "Medical Doctor" IF EXISTS;
    DROP TABLE "Therapist" IF EXISTS;
    CREATE TABLE "Psychiatrist" (
    "ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> NOT
    NULL PRIMARY KEY,
    "First Name" VARCHAR(25) NOT NULL,
    "Surname" VARCHAR(25) NOT NULL,
    "Gender" CHAR(6),
    "Street and number" VARCHAR(50),
    "City" VARCHAR(25),
    "Postal code" CHAR(5),
    "State" CHAR(2),
    "Phone Number" VARCHAR(10)
    );
    CREATE TABLE "Medical Doctor" (
    "ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> NOT
    NULL PRIMARY KEY,
    "First Name" VARCHAR(25) NOT NULL,
    "Surname" VARCHAR(25) NOT NULL,
    "Gender" CHAR(6),
    "Street and number" VARCHAR(50),
    "City" VARCHAR(25),
    "Postal code" VARCHAR(5),
    "State" CHAR(2),
    "Phone Number" VARCHAR(10)
    );
    CREATE TABLE "Patient" (
    "ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> NOT NULL PRIMARY KEY,
    "First Name" VARCHAR(25) NOT NULL,
    "Surname" VARCHAR(25) NOT NULL,
    "Gender" CHAR(6),
    "Date of Birth" DATE,
    "Street and number" VARCHAR(50),
    "City" VARCHAR(25),
    "Postal code" VARCHAR(5),
    "State" CHAR(2),
    "Diagnosis" VARCHAR(60),
    "Medical Doctor ID" INTEGER,
    "Psychiatrist ID" INTEGER,
    "Time of registry" TIMESTAMP,
    CONSTRAINT "CK_PAT_GNDR" CHECK( "Gender" in ( 'Male', 'Female' ) ),
    CONSTRAINT FK_PAT_PSY FOREIGN KEY ("Psychiatrist ID") REFERENCES
    "Psychiatrist" ("ID Number"),
    CONSTRAINT FK_PAT_DOC FOREIGN KEY ("Medical Doctor ID") REFERENCES
    "Medical Doctor" ("ID Number")
    );
    CREATE TABLE "Phone Number" (
    "Phone ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> NOT NULL
    PRIMARY KEY,
    "Patient ID" INTEGER NOT NULL,
    "Number" VARCHAR(10),
    "Description" VARCHAR(10),
    CONSTRAINT FK_PAT_PHN FOREIGN KEY ("Patient ID") REFERENCES "Patient" ("ID Number")
    );
    CREATE TABLE "Therapist" (
    "ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> NOT
    NULL PRIMARY KEY,
    "First Name" VARCHAR(25) NOT NULL,
    "Surname" VARCHAR(25) NOT NULL,
    "Gender" CHAR(6),
    "Street and number" VARCHAR(50),
    "City" VARCHAR(25),
    "Postal code" VARCHAR(5),
    "State" CHAR(2),
    "Tax number" VARCHAR(20),
    "Academic degree" VARCHAR(25),
    "License number" VARCHAR(15),
    "Hiring date" DATE NOT NULL,
    "Termination date" DATE,
    CONSTRAINT "CK_THP_GNDR" CHECK( "Gender" in ( 'Male', 'Female' ) ),
    CONSTRAINT "CK_TERM_DT" CHECK( "Termination date" > "Hiring date" )
    );
    CREATE TABLE "Therapists Number" (
    "Phone ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> NOT NULL
    PRIMARY KEY,
    "Therapist ID" INTEGER,
    "Number" VARCHAR(10),
    "Description" VARCHAR(10),
    CONSTRAINT FK_THP_PHN FOREIGN KEY ("Therapist ID") REFERENCES "Therapist"
    ("ID Number")
    );
    CREATE TABLE "Assignment" (
    "Assignment ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> NOT
    NULL PRIMARY KEY,
    "Patient ID" INTEGER NOT NULL,
    "Therapist ID" INTEGER NOT NULL,
    "Date assigned" DATE DEFAULT CURRENT_DATE NOT NULL,
    "Date case closed" DATE,
    CONSTRAINT FK_PAT_ASMT FOREIGN KEY ("Patient ID") REFERENCES "Patient"
    ("ID Number"),
    CONSTRAINT FK_THP_ASMT FOREIGN KEY ("Therapist ID") REFERENCES "Therapist"
    ("ID Number"),
    CONSTRAINT "CK_CLOSE_DT" CHECK( "Date case closed" >= "Date assigned" )
    );
    CREATE TABLE "Schedule" (
    "Schedule ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> NOT
    NULL PRIMARY KEY,
    "Assignment ID" INTEGER,
    "Slot date" DATE NOT NULL,
    "Slot hour" TIME NOT NULL,
    "Status of the session" VARCHAR(20),
    CONSTRAINT FK_SCH_ASMT FOREIGN KEY ("Assignment ID") REFERENCES
    "Assignment" ("Assignment ID")
    );
    CREATE TABLE "Payment" (
    "Payment ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> NOT
    NULL PRIMARY KEY,
    "Patient ID" INTEGER NOT NULL,
    "Date and time of Payment" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
    "Amount" DECIMAL (10, 2) NOT NULL,
    "Notes" VARCHAR(100),
    "Result" CHAR(6) NOT NULL,
    CONSTRAINT FK_PAT_PYMNT FOREIGN KEY ("Patient ID") REFERENCES "Patient"
    ("ID Number"),
    CONSTRAINT CK_DBT CHECK("Result" IN ('DEBIT', 'CREDIT'))
    );
    CREATE TABLE "Medication" (
    "Medication ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> NOT
    NULL PRIMARY KEY,
    "Name" VARCHAR(30) NOT NULL,
    "Description" VARCHAR(256)
    );
    CREATE TABLE "Patient Medication" (
    "Patient ID" INTEGER NOT NULL,
    "Medication ID" INTEGER NOT NULL,
    "Dosage" VARCHAR(50),
    "Start date" DATE DEFAULT CURRENT_DATE,
    "End date" DATE,
    CONSTRAINT PK_PAT_MED PRIMARY KEY ("Patient ID", "Medication ID" ),
    CONSTRAINT FK_MED_PAT FOREIGN KEY ("Medication ID") REFERENCES
    "Medication" ("Medication ID"),
    CONSTRAINT FK_PAT_MED FOREIGN KEY ("Patient ID") REFERENCES "Patient" ("ID Number"),
    CONSTRAINT CK_END_DT CHECK( "End date" >= "Start date" )
    );

I notice there is no base specific user guide listed on the libreoffice page-
http://www.libreoffice.org/get-help/documentation/

oweng
oweng's picture
Offline
Last seen: 5 days 10 hours ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 3281
TDF wiki pages / HSQLDB guides / PostgreSQL+SDBC
Thanks for the code darkcity. The Base Handbook chapters that are translated are now listed on TDF Wiki Documentation / Publications page. You will generally find that working copies are available on this page before final copies appear on the official documentation page. There is also some information on TDF Wiki Documentation / Other page, which I will repeat here and add to:
darkcity
darkcity's picture
Offline
Last seen: 10 weeks 6 days ago
Title: ★★★★★
Joined: 14 May 2013
Posts: 101
Thanks, have downloaded draft
Thanks, have downloaded draft Base guide Smile
frofa
frofa's picture
Offline
Last seen: 13 hours 17 min ago
Title: ★★★★★
Joined: 24 Feb 2012
Posts: 804
LibreOffice Base version 4 Handbook
Here is a link to the LO version 4 Base HANDBOOK (PDF file).

Also see the links following to download the 2 example Base files mentioned in the handbook.

Media_Without_Macros.odb

Media_With_Macros.odb.

jonesypeter
Offline
Last seen: 2 years 6 weeks ago
Title:
Joined: 14 Apr 2014
Posts: 1
Development of Base
Hi,

Thanks very much for starting this ‘Base’ thread. The sample files are especially useful.

It always feels as though ‘Base’ is like the ugly sister in terms of development. Every release seems to mention loads about Write and Calc, and little about Base. Perhaps it is just me thinking that.

Anyway, are there any plans to include a hyperlink field type? Like in Access. I have asked this for a number of years on the official forums and just been told it is because of the underlying database engine. Anyway hopefully it is on its way?

Best wishes

Peter Jones

oweng
oweng's picture
Offline
Last seen: 5 days 10 hours ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 3281
no further info at this time
jonesypeter wrote:
Thanks very much for starting this ‘Base’ thread. The sample files are especially useful. It always feels as though ‘Base’ is like the ugly sister in terms of development. Every release seems to mention loads about Write and Calc, and little about Base. Perhaps it is just me thinking that.
You are welcome. It is not just you Peter. Base has been something of a poor forgotten cousin until 2013-2014. The development is picking up a bit in recent months / last year or so.

jonesypeter wrote:
Anyway, are there any plans to include a hyperlink field type? Like in Access. I have asked this for a number of years on the official forums and just been told it is because of the underlying database engine. Anyway hopefully it is on its way?
For the clarity of others (as you appear to have raised the original bug) this is enhancement request FDO#51203. I have no further information on the progress of this feature, other than what is indicated in the report. The related AskLO thread (also raised by yourself) is here.

oweng
oweng's picture
Offline
Last seen: 5 days 10 hours ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 3281
update
oweng wrote:
jonesypeter wrote:
Anyway, are there any plans to include a hyperlink field type? Like in Access. I have asked this for a number of years on the official forums and just been told it is because of the underlying database engine. Anyway hopefully it is on its way?
For the clarity of others (as you appear to have raised the original bug) this is enhancement request FDO#51203
I have provided an update (comment in bug report) on what would likely be required to get a Hyperlink data type implemented. I can’t find an open OASIS issue related to this matter, so I would suggest raising it on the OASIS office-comment mailing list to see this progressed further at this point.
darkcity
darkcity's picture
Offline
Last seen: 10 weeks 6 days ago
Title: ★★★★★
Joined: 14 May 2013
Posts: 101
There is a book out call
There is a book out call

Use LibreOffice Base Paperback by Mr Thomas Ecclestone

http://www.amazon.com/Use-LibreOffice-Base-Thomas-Ecclestone/dp/1502941708/

Has anyone read it, and if so is it any good?

SnowJersey
SnowJersey's picture
Offline
Last seen: 25 weeks 1 day ago
Title:
Joined: 22 Nov 2015
Posts: 6
Nice Video Series
This a nice video series on YouTube of a gentleman who explains Base pretty well. It’s a nice resource for beginners and it doesn’t assume much knowledge, has sample files, and you can watch step by step. “http://thefrugalcomputerguy.com/libreoffice-base/index.php”: http://thefrugalcomputerguy.com/libreoffice-base/index.php

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.