Base documentation
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
- LO Documentation (includes links for the Base Handbook and the Getting Started Guide, chapter 8 of which provides a brief overview of how to use Base; the two example Base files mentioned in the Base Handbook can be obtained here: Media_Without_Macros.odb and Media_With_Macros.odb).
- TDF Wiki Documentation/Publications (includes older versions of the Base Handbook and Getting Started Guide).
- Database Programming with OpenOffice.org Base & Basic (352 page paperback book by Roberto Benitez, available via LuLu and other sources).
- Access2Base is a large library of macros to assist people with transitioning from Microsoft Access to LO Base.
- PoorSQL can be used to both format SQL (for posting complex examples on this forum) and validating SQL.
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
- TheFrugalComputerGuy video list (series of YouTube videos showing how to perform many basic tasks in LO Base).
- TheFrugalComputerGuy Base index (contains links to prior video as well as example ODB files related to each tutorial).
- A club membership database (Apache OO wiki simple example of using Base).
- Base Tutorial: From Newbie to Advocate in a one, two… three! (2nd edition, September 2010, relating to OpenOffice.org, by Mariano Casanova – refer this post below for missing code example from page 86 of this tutorial).
- Practical course chapters using Libreoffice Base (by Robin Beaumont, includes several PDFs and example databases).
Forum threads / comments / links of importance
Key: AOF = Apache OpenOffice Forum, LOF = LibreOffice Forum (i.e., here).
Before you begin
- AOF, Cross-platformness (this is a good summary that links to several of the following linked threads).
- AOF, Avoiding data loss by avoiding embedded databases (essential reading, including many links to related threads below in this post).
- LOF, ODF container portability (general discussion about the ODF container as it related to embedded databases, particular Firebird).
- LOF, Apache OO vs LO implementation differences (ongoing thread about any known differences between these two implementations; includes a link to the original AskLO thread).
Split-mode databases
- AOF, Portable split HSQL (a.k.a. macro-enhanced) database template
- LOF, Setting up a split database
- AOF, Splitting an embedded database
- LOF, Use the integral JDBC driver built-into hsqldb.jar
- LOF, How to publish / distribute a Base project? (the solution is dependent on the “Portable split HSQL (a.k.a. macro-enhanced) database template” linked above).
- LOF, How to reconnect to a split-mode HSQL database.
- LOF, Using the Switchboard extension.
Choosing a back-end database
- AOF, Database engines
- AOF, MySQL vs HSQLDB
- Ocelot Computer Services, MySQL vs Firebird (note that this is not an Apache OO / LO centric examination, but rather a fairly straight comparison of features).
- LOF, HSQLDB to be replaced by Firebird
- Don Parris, Connecting to PostgreSQL using the SDBC driver v0.8.1
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
- AOF, Recovering a damaged ODB file
- AOF, List of zip tools that ignore zip corruption
- LOF, Rescuing a corrupt database file
- AOF, Account of a recovery process
Miscellaneous
- AOF, Pictures in Base
- LOF, Apache OpenOffice vs LibreOffice Base implementational differences
- LOF, Is there a way to get around limit of 256 output fields?
- LOF, How to store Impress and Writer files in Base?
Examples
- AOF, Database examples sub-forum
- LOF, Club membership (example in post #1 – reference to Apache OO wiki example listed above under Tutorials).
- LOF, Simple archaeological (example files in post #7).
- Ask, SecretaryBird (large example). Site link here. Note that this example makes heavy use of the Access2Base library linked above.
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/
- HSQLDB v1.8.0 User Guide (PDF or HTML).
- HSQLDB v2.3.0 User Guide and Utilities Guide (PDF and HTML).
- Guide on connecting to PostgreSQL using the SDBC driver.
Also see the links following to download the 2 example Base files mentioned in the handbook.
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
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?