|
|
| Line 1: |
Line 1: |
| − | =Project=
| |
| − | ==Updating: [[Documentation/How_Tos/Using_SQLite_With_OpenOffice.org]]==
| |
| | | | |
| − | OpenOffice Base provides functions to connect to other databases than the default HSQL database. This Tutorial should guide you through the steps to connect OpenOffice to a [http://www.sqlite.org/ SQLite] database file.
| |
| − |
| |
| − |
| |
| − | ==Installation==
| |
| − |
| |
| − | {{Documentation/Linux|Linux}}
| |
| − |
| |
| − | ===ODBC drivers===
| |
| − | Connection to a SQLite database will be established through ODBC drivers. ODBC drivers for SQLite are independent of the SQLite project.
| |
| − |
| |
| − |
| |
| − | You can install <tt>unixodbc</tt> with your packetmanager - or download the sources from the [http://www.unixodbc.org/ unixODBC] website. The <tt>unixodbc-dev</tt> and <tt>unixodbc-bin</tt> packages will also be required to complete the configuration for OpenOffice.
| |
| − |
| |
| − | sudo apt-get unixodbc unixodbc-dev unixodbc-bin
| |
| − |
| |
| − |
| |
| − | ===SQLite===
| |
| − | Obviously you will need to install [http://www.sqlite.org SQLite] if it is not installed already as this will be the database you want to connect to.
| |
| − |
| |
| − |
| |
| − | There will likely be a version in the repositories although it might be dated. You can always [http://www.sqlite.org/download.html download] and install the latest version from source.
| |
| − |
| |
| − |
| |
| − | ===sqliteODBC===
| |
| − | For SQLite to work through the ODBC standards you will also need to install and configure a special set of drivers.
| |
| − |
| |
| − |
| |
| − | There are no precompiled packages so you will need to [http://ch-werner.de/sqliteodbc/ download the source] and compile it yourself:
| |
| − |
| |
| − | tar xvzf sqliteodbc-0.''XX''.tar.gz
| |
| − | ./configure –prefix=/usr
| |
| − | make
| |
| − | sudo make install
| |
| − |
| |
| − | <tt>--prefix=/usr</tt> is optional, but is especially useful with Mandrake distributions, to install the driver in <tt>/usr/lib</tt> rather than in the default location, <tt>/usr/local/lib</tt>.
| |
| − |
| |
| − | {{Documentation/Note|If you get an error message claiming ''"ODBC header files and/or libraries not found"'' check that you have installed the <tt>unixODBC-dev</tt> package while installing the [[#ODBC_drivers|ODBC drivers]].}}
| |
| − |
| |
| − |
| |
| − | 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
| |
| − |
| |
| − | {{Documentation/Note|If ODBCConfig is not found check that you also installed the <tt>unixODBC-bin</tt> package while installing the [[#ODBC_drivers|ODBC drivers]].}}
| |
| − |
| |
| − | Now the a GUI to manage the configuration shows up.
| |
| − | [[Image:doc_howto_sqlite_odbcadmin.png|390px]]
| |
| − |
| |
| − | Click the <tt>Drivers</tt> tab and choose <tt>Add...</tt> Then fill the fields as follows:
| |
| − |
| |
| − | Name: SQLite
| |
| − | Description: SQLite for ODBC
| |
| − | Driver: /usr/lib/libsqlite3odbc.so
| |
| − | Driver64:
| |
| − | Setup: /usr/lib/libsqlite3odbc.so
| |
| − | Setup64:
| |
| − |
| |
| − | {{Documentation/Note|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 <tt>Drivers</tt> tab:
| |
| − | [[Image:doc_howto_sqlite_odbcadmin-2.png|450px]]
| |
| − |
| |
| − | The driver installation under Linux is now finished and you can exit <tt>ODBCConfig</tt>.
| |
| − |
| |
| − |
| |
| − | For those who do not have <tt>ODBCConfig</tt> or who prefer to carry out this configuration manually, it is necessary to edit the file <tt>/etc/odbcinst.ini</tt> as root and add the following lines:
| |
| − | <nowiki>[SQLite]</nowiki>''
| |
| − | Description<nowiki>= ODBC for SQLite</nowiki>''
| |
| − | Driver<nowiki>= /usr/lib/libsqliteODBC.so</nowiki>''
| |
| − | Setup<nowiki>= /usr/lib/libsqliteODBC.so</nowiki>''
| |
| − | FileUsage<nowiki>= 1</nowiki>''
| |
| − | CPTimeout<nowiki>= </nowiki>''
| |
| − | CPReuse<nowiki>= </nowiki>''
| |
| − |
| |
| − |
| |
| − | ===Under Windows===
| |
| − | {{Documentation/Windows|Under Windows the installation and configuration is much easier. Simply [http://ch-werner.de/sqliteodbc/ 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 OpenOffice.org but rather this is accomplished by using either the utility “<tt>sqlite</tt>” on the command line or by using a graphical management tool, a list of GUIs is available at [http://www.sqlite.org/cvstrac/wiki?p=ManagementTools SQLite.org]
| |
| − |
| |
| − | The utility "<tt>sqlite</tt>" 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 [http://www.sqlite.org/download.html]
| |
| − |
| |
| − | Performance and usage under Linux and Windows is identical.
| |
| − |
| |
| − | ====Using sqlite====
| |
| − | {{Documentation/Linux|Open a terminal window and change to the directory where you want to create your database. Run <tt>sqlite</tt> with the filename of your database as argument (e.g. ''$ sqlite mydatabase''}}
| |
| − | {{Documentation/Windows|Run <tt>sqlite</tt> 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 <tt>sqlite</tt> you can also create tables and modify your database with standard SQL-commands:
| |
| − |
| |
| − | <nowiki>$ sqlite <filename of database></nowiki>
| |
| − | SQLite version 2.8.12
| |
| − | Enter ".help" for instructions
| |
| − | sqlite> CREATE TABLE mytable (id INT, text VARCHAR(100));
| |
| − | sqlite> .exit
| |
| − |
| |
| − | {{Documentation/Note|You can then check that the file was, indeed, created in your filesystem. The name of the file does not require a file extension, but you can give it the extension <tt>db</tt> so that is easy to locate as a database.}}
| |
| − |
| |
| − | Using <tt>sqlite</tt> 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====
| |
| − | {{Documentation/Caution|This information might be outdated.}}
| |
| − |
| |
| − | After unpacking the file, launch <tt>sqlitebrowser</tt> and select <tt>File/New Database</tt> or the corresponding icon:
| |
| − |
| |
| − | [[Image:doc_howto_sqlite_dbbrowser.png|410px]]
| |
| − |
| |
| − | Choose the directory in which to place file and then enter name of the database file and click on <tt>Save</tt>.
| |
| − |
| |
| − | Using this program, you can also create the tables, the fields and the indices, to access data and to visualize them. While
| |
| − | <tt>sqlitebrowswer</tt> 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 OpenOffice.org.
| |
| − |
| |
| − |
| |
| − | ===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.
| |
| − |
| |
| − |
| |
| − | {{Documentation/Linux|'''Under Linux:'''
| |
| − | Launch <tt>ODBCConfig</tt> again and go to the tab <tt>User DSN</tt>. Here add a new entry. A window appears in which you must choose
| |
| − | the <tt>SQLite</tt> driver you have configured earlier. In the next window, as shown below, you enter a name for this connection (for example the name of your database) and the complete access path to the database file which you created previously.
| |
| − |
| |
| − | [[Image:doc_howto_sqlite_driverproperties-2.png|380px]]
| |
| − |
| |
| − | Validate the data on the screen, click the checkmark and you should see your new data source in the tab <tt>User DSN</tt>.
| |
| − |
| |
| − |
| |
| − | If you do not have <tt>ODBCConfig</tt> or simply prefer to configure manually, launch your favorite editor, open (or create)
| |
| − | the file <tt>odbc.ini</tt> in your home directory, and add the following lines to it:
| |
| − |
| |
| − | <nowiki>[My Base]</nowiki>
| |
| − | Description<nowiki>= My test database</nowiki>
| |
| − | Driver<nowiki>= SQLite</nowiki>
| |
| − | Database<nowiki>= /home/<user>/basename.db</nowiki>
| |
| − | Timeout<nowiki>= 1000</nowiki>
| |
| − | StepAPI<nowiki>= No</nowiki>
| |
| − |
| |
| − | {{Documentation/Note|If this data source must by other users on the system, launch <tt>ODBCConfig</tt> as root and add this source in the tab <tt>System DSN</tt> or <tt>data Source system</tt>, or create a <tt>/etc/odbc.ini</tt> file.}}
| |
| − |
| |
| − | You are now ready to access your data base with OpenOffice.org!}}
| |
| − |
| |
| − | {{Documentation/Windows|'''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 [[#ODBC-setup of new database|configured]] in ODBC.
| |
| − |
| |
| − | If nothing is showing up here you have a problem. Check that you actually [[#ODBC-setup of new database|set up the database in ODBC]] and that the drivers are properly configured as described.
| |
| − |
| |
| − | {{Documentation/Windows|'''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.
| |
| − |
| |
| − | [[Image:doc_howto_sqlite_odbcdriver-windows-1.png]]
| |
| − |
| |
| − |
| |
| − | In the ''User DSN'' (or the ''System DSN'') tab click on ''Add...'' to setup your database file.
| |
| − |
| |
| − | [[Image:doc_howto_sqlite_odbcdriver-windows-2.png]]
| |
| − |
| |
| − |
| |
| − | Give your database a meaningful name as ''Data Source Name'' - this will be the name you find your database listed as. For ''Database Name'' browse to the SQLite database file you have created earlier. Leave the rest on its default values and press ok.
| |
| − |
| |
| − | [[Image:doc_howto_sqlite_odbcdriver-windows-3.png]]
| |
| − |
| |
| − |
| |
| − | The database will now be listed in the ''ODBC Data Source Administrator'' and leaving this window you should now also be able to select this database in the ''OpenOffice Base'' dialog.
| |
| − | }}
| |
| − |
| |
| − |
| |
| − |
| |
| − |
| |
| − | ==Working on 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.
| |
| − |
| |
| − | {{Documentation/Caution|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 do, 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.
| |
