Difference between revisions of "NL/Documentation/How Tos/Using SQLite With OpenOffice.org"
(→is SQLite?) |
(→Why use SQLite with OpenOffice?) |
||
Line 17: | Line 17: | ||
Deze gids beperkt zich tot ODBC wat {{AOo}} gebruikt om te verbinden met databases en in het bijzonder SQLite. | Deze gids beperkt zich tot ODBC wat {{AOo}} gebruikt om te verbinden met databases en in het bijzonder SQLite. | ||
− | == | + | ==Waarom SQLite te gebruiken met OpenOffice?== |
− | + | Het gebruiken van SQLite heeft vele voordelen: | |
− | * | + | * De database is slechts in één bestand, in tegenstelling tot dBASE dat een bestand per tabel maakt, wat het delen van databases tussen verschillende gebruikers eenvoudig maakt. |
− | * Java JRE | + | * Java JRE of SDK is niet vereist voor ondersteuning van SQLite. |
− | * | + | * Er is geen server nodig (lokaal of op afstand) voor toegang tot een database. |
− | * ODBC | + | * Stuurprogramma's voor ODBC bestaan voor *NIX en voor Windows, wat gebruikers van beide omgevingen in staat stelt toegang te krijgen tot de gegevens en ze te delen. |
− | * | + | * Een [https://www.openoffice.org/dba/drivers/sqlite/index.html nieuw stuurprogramma voor SDBC] (eigen voor {{AOo}}) is beschikbaar om mee te experimenteren. |
− | * | + | * Uitgebreide grafische interfaces, zoals die van MS Access, kunnen gemakkelijk worden ontwikkeld met de formulieren, query's, statussen en macro's van {{AOo}}. Deze interfaces worden afzonderlijk opgeslagen in .odb-bestanden van OpenOffice en kunnen onafhankelijk van de gegevens worden gedeeld met gebruikers onder UNIX of Windows. |
− | + | en enkele nadelen: | |
− | * | + | * Aanpassen van de structuur van een tabel van SQLite, nadat die is gemaakt, is niet direct mogelijk. Dit betekent dat u niet gemakkelijk velden kunt toevoegen, noch hun eigenschappen kunt aanpassen als de tabel eenmaal is gemaakt. Daarom moet u de structuur van uw tabellen voorbereiden voordat u ze maakt. |
* OpenOffice.org requires the use of the ODBC driver for accessing a SQLite database thus making this operation somewhat complicated. First it must be declared in ODBC and then in OpenOffice. The native SDBC driver should correct this situation when it is production ready. | * OpenOffice.org requires the use of the ODBC driver for accessing a SQLite database thus making this operation somewhat complicated. First it must be declared in ODBC and then in OpenOffice. The native SDBC driver should correct this situation when it is production ready. | ||
* The initial database file cannot be created with OpenOffice but by using other tools. | * The initial database file cannot be created with OpenOffice but by using other tools. | ||
Line 37: | Line 37: | ||
{{Note|SQLite appeals to anyone frustrated with using MDB files under Linux, or who has had difficulty installing MySQL or other RDBMS server on their workstation.}} | {{Note|SQLite appeals to anyone frustrated with using MDB files under Linux, or who has had difficulty installing MySQL or other RDBMS server on their workstation.}} | ||
− | |||
=Installation= | =Installation= |
Revision as of 09:58, 29 June 2023
Contents
Introductie
OpenOffice Base verschaft functies om met andere databases te erbinden dan de standaard HSQL database. Deze handleiding zou u door de stappen moeten leiden om Apache OpenOffice te verbinden met een SQLite databasebestand.
Wat is SQLite?
SQLite is een basis databaseprogramma dat de meeste mogelijkheden van SQL92 implementeert. Anders dan PostgreSQL en MySQL, slaat SQLite een hele database met al zijn tabellen op in één enkel bestand. Andere voordelen zijn: toegang tot de database vereist geen database-server, databasebestanden kunnen vrijelijk worden gedeeld tussen machines met verschillende volgorde van bytes en databases mogen tot 2 terabytes (241 bytes) in grootte zijn. En daarnaast is het heel snel (twee keer zo snel als PostgreSQL en MySQL voor de meeste bewerkingen) en heeft een kleine voetafdruk voor het geheugen.
Beheren van de gegevens kan worden gedaan op de volgende manieren:
- Via een Linux-bibliotheek voor C/C++ of Windows DLL.
- Via een in-regelig programma (sqlite: beschikbaar onder Linux en Windows) dat het mogelijk maakt de bestanden van de database te maken of te beheren.
- Via de SQLite PHP-module of, als u dat heeft, PHP versie 5 intern naar een database van SQLite.
- Via ODBC (Linux en Windows) wat een toepassing die deze standaard ondersteunt, toestaat een database van SQLite te bereiken.
- Gebruiken van het experimentele stuurprogramma voor SDBC SQLite
Deze gids beperkt zich tot ODBC wat Apache OpenOffice gebruikt om te verbinden met databases en in het bijzonder SQLite.
Waarom SQLite te gebruiken met OpenOffice?
Het gebruiken van SQLite heeft vele voordelen:
- De database is slechts in één bestand, in tegenstelling tot dBASE dat een bestand per tabel maakt, wat het delen van databases tussen verschillende gebruikers eenvoudig maakt.
- Java JRE of SDK is niet vereist voor ondersteuning van SQLite.
- Er is geen server nodig (lokaal of op afstand) voor toegang tot een database.
- Stuurprogramma's voor ODBC bestaan voor *NIX en voor Windows, wat gebruikers van beide omgevingen in staat stelt toegang te krijgen tot de gegevens en ze te delen.
- Een nieuw stuurprogramma voor SDBC (eigen voor Apache OpenOffice) is beschikbaar om mee te experimenteren.
- Uitgebreide grafische interfaces, zoals die van MS Access, kunnen gemakkelijk worden ontwikkeld met de formulieren, query's, statussen en macro's van Apache OpenOffice. Deze interfaces worden afzonderlijk opgeslagen in .odb-bestanden van OpenOffice en kunnen onafhankelijk van de gegevens worden gedeeld met gebruikers onder UNIX of Windows.
en enkele nadelen:
- Aanpassen van de structuur van een tabel van SQLite, nadat die is gemaakt, is niet direct mogelijk. Dit betekent dat u niet gemakkelijk velden kunt toevoegen, noch hun eigenschappen kunt aanpassen als de tabel eenmaal is gemaakt. Daarom moet u de structuur van uw tabellen voorbereiden voordat u ze maakt.
- OpenOffice.org requires the use of the ODBC driver for accessing a SQLite database thus making this operation somewhat complicated. First it must be declared in ODBC and then in OpenOffice. The native SDBC driver should correct this situation when it is production ready.
- The initial database file cannot be created with OpenOffice but by using other tools.
In short, the principal advantages of the use of SQLite with OpenOffice are the format of data storage and the portability from one environment to another. In the list of disadvantages, the two last are not specific to SQLite, but are requirements of using ODBC. However, these are not as awkward as they do not take place during the initial creation of the database.
SQLite appeals to anyone frustrated with using MDB files under Linux, or who has had difficulty installing MySQL or other RDBMS server on their workstation. |
Installation
Unix ODBC Platform
Connection to a SQLite database will be established through UnixODBC. ODBC is an open specification to provide applications with a consistent interface to a variety of data sources. UnixODBC is an implementation of this specification for non-Windows platforms.
You can install unixodbc with your distribution's package manager, or download the sources from the UnixODBC website. The unixodbc-bin package provides a GUI application for configuration, which will be used below. Under Ubuntu or similar Linux distributions, you can use the command:
sudo apt-get install unixodbc unixodbc-bin
SQLite
Obviously you will need to install SQLite if it is not installed already as this will be the database you want to connect to.
You can install SQLite with your distributions package manager. The package should be called sqlite or sqlite3. Under Ubuntu:
sudo apt-get install sqlite3
If you prefer, you can always download and install the latest version from source.
SQLite ODBC Driver
For SQLite to work through the ODBC standards you will also need to install and configure the SQLite ODBC Driver. This driver is available in Ubuntu in the libsqliteodbc package.
If you prefer, you can download the source and compile it yourself. You will need the unixodbc-dev and libsqlite3-dev packages installed for it to build successfully.
To complete the installation the driver has to be integrated into unixODBC. To do this, launch the ODBCConfig utility as root in an terminal window:
sudo ODBCConfig
If ODBCConfig is not found check that you also installed the unixODBC-bin package while installing the ODBC drivers. |
Now a GUI to manage the configuration shows up.
Click the Drivers tab and choose Add… Then fill the fields as follows:
Name: SQLite Description: SQLite for ODBC Driver: /usr/lib/libsqlite3odbc.so Driver64: /usr/lib64/libsqlite3odbc.so Setup: /usr/lib/libsqlite3odbc.so Setup64: /usr/lib64/libsqlite3odbc.so
If you are still using a 2.8.x version of libsqlite the driver names will be different. Try browsing the folder by clicking on the arrow at the right and select one that seems suitable. |
Then confirm using the checkmark icon on the left in top of the window. Your new driver will now appear in the Drivers tab:
The driver installation under Linux is now finished and you can exit ODBCConfig.
For those who do not have ODBCConfig or who prefer to carry out this configuration manually, it is necessary to edit the file /etc/odbcinst.ini as root and add the following lines:
[SQLite] Description= ODBC for SQLite Driver= /usr/lib/libsqliteODBC.so Driver64= /usr/lib64/libsqliteODBC.so Setup= /usr/lib/libsqliteODBC.so Setup64= /usr/lib64/libsqliteODBC.so FileUsage= 1 CPTimeout= CPReuse=
Under Windows
Under Windows the installation and configuration is much easier. Simply download the sqliteODBC.exe, the installer will automatically setup the ODBC drivers including the SQLite configurations for it and also provides you with the sqlite command line tool to create and modify SQLite databases. |
Setup of SQLite database
Creating a SQLite database file
An initial database file cannot be created directly in Apache OpenOffice but rather this is accomplished by using either the utility “sqlite” on the command line or by using a graphical management tool, a list of GUIs is available at SQLite.org
The utility "sqlite" should be already installed if you use Linux and have installed SQLite from rpm. For other *NIX systems, you must build SQLite from source which you can find at www.sqlite.org/download
Performance and usage under Linux and Windows is identical.
Using sqlite
Open a terminal window and change to the directory where you want to create your database. Run sqlite with the filename of your database as argument (e.g. $ sqlite mydatabase |
Run sqlite from the start menu or where it was installed. A standard file open dialog box will open and ask you to select a database file. |
If this database file does not exist yet, it will be created automatically. Using sqlite you can also create tables and modify your database with standard SQL-commands:
$ sqlite <filename of database> SQLite version 2.8.12 Enter ".help" for instructions sqlite> CREATE TABLE mytable (id INT, text VARCHAR(100)); sqlite> .exit
Using sqlite on the command line program makes it possible to create tables and indices, to enter and post data and to make queries provided you know the SQL language - however, it is more practical for beginners to carry out these operations later on using OpenOffice.
Using SQLite Database Browser
There is also a graphical utility for creating and manipulating SQLite databases called sqlitebrowser. You can install this using your distribution's package manager.
After unpacking the file, launch sqlitebrowser and select File/New Database or the corresponding icon:
Choose the directory in which to place the file and then enter the name of the database file and click on Save.
Using this program, you can also create the tables, the fields, and the indices, to access data and to visualize them. While sqlitebrowswer is suitable for these actions, there is the disadvantage that it does not show the full list of field types during field creation. In this case, it may be preferable to do it later using Apache OpenOffice.
ODBC-setup of new database
After creating a new SQLite database above you now have to configure this database in ODBC so you can connect to it in OpenOffice later on.
Under Windows:
You will reach the configuration through the OpenOffice dialog in the next step, just read on. |
Connecting OpenOffice to a SQLite database
You had to spend a lot of time installing and configuring other things but finally we can move on to OpenOffice. Only a few more configuration windows and you will be done.
Start OpenOffice Base and in the first step 1. Select database chose the third option, connect to an existing database using ODBC.
Connect to an existing database ODBC
In the second step 2. Set up ODBC connection you can select your SQLite database. Clicking on Browse you should get a list of all databases you have configured in ODBC.
If nothing is showing up here you have a problem. Check that you actually set up the database in ODBC and that the drivers are properly configured as described.
Under Windows:
Before you will find your database listed here you need to register it with ODBC as mentioned above. Click on Organize... in the bottom right of the window.
|
Working on the database with OpenOffice
The rest is OpenOffice Base just as you know it.
Only note a few things: Creating new tables using OpenOffice is possible. Those tables - along with all data you enter in them - are saved in the external SQLite database file to which you connected your OpenOffice .odb file.
Fields defined as type "integer" are auto-increment, that is they automatically increment the value of the field when adding a new record in this table.
Once you have saved your table, you cannot modify it! I.e. you will not be able to modify the properties of the fields any more, nor add or remove! |
This rather awkward constraint is specific to SQLite, which does not handle the SQL command ALTER COUNTS, and not the ODBC driver. This limitation is designed in by the authors in order to make the basic database engine as light as possible. However, this limitation may be eliminated in a future version. Therefore you are advised to check your table structure very carefully before recording it.
If you must modify a table, the only solution is to create a new table and save it under different name. By doing so, you can transfer the data from the original table to the new table.
Creating and using Forms, Queries and Reports are all independent of the datasource you use. They will function just as with the default integrated HSQL-database and will still be saved in your odb-file. For more information on these topics refer to the other documentation.
If you want to use subforms you need to activate "parameter substitution" first.
Select "Edit/Database/Advanced Settings..." in the main menu of your OpenOffice Base window. There, in the "Special Settings" tab check the option "Replace named parameters with '?'". You might need to restart OpenOffice but then you should be able to use subforms without problems. |
Exchanging databases between users
Thanks to the availability of driver sqliteODBC driver for both Linux and Windows (just as for OpenOffice, of course!), a SQLite database can be easily exchanged between users of the two environments. It is necessary that each has an installed ODBC driver that is aware of the target SQLite file and which is defined as a data source in OpenOffice. Likewise, OpenOffice Base files (.odb) containing forms and macros can also be exchanged between users of the two environments.
If forms are to function correctly for each user, it is absolutely necessary that each user applies exactly the same name to the data source during its creation. |
Content on this page is licensed under the Public Documentation License (PDL). |