[libreoffice-users] Base - using external database tables
Hi OK, let me go back to square 1. First of all I am not talking about a situation in which LibreOffice base needs to access data tables in separate files that use different database structures, e.g. one table is in MySQL and another is in PostgreSQL. This may be something that others need and I seem to recall that M$ access could do this but I do not need to. Next, terminology because database can mean a number of things. Please correct me if I am wrong here. The data are contained in tables. These can be simple tables such as CSV tables or more complex tables constructed according to the rules of a particular database application. These tables can be in separate files or they may be embedded along with other information. Then there are such things as queries, forms, reports and configurations which could, I guess, be each in separate files but are normally saved embedded together or with, say a data table. The results of running these, if saved, will be data tables or reports in separate files. Finally there is the database application which may be in one or many files. Now, to my situation. First of all I am using Debian GNU/Linux testing distribution. I use LibreOffice and for my database needs I use base. I use the built in HSQLDB database application and have my data tables embedded in the .odb file. This has worked adequately (clunkily I would say, but I will keep other difficulties out of this discussion). However, base has recently become unusably slow in response to something that has changed in openjdk (I have asked about this elsewhere). This has prompted me to revisit the question of whether to separate out my data tables. I am wondering whether it might be a workaround for the slowness problem. Also there may be advantages in backing up data separately and having it less vulnerable to anything going wrong in LibreOffice. My data consist of a list of names, addresses etc. in one table and then some other tables that have additional information that only applies to (overlapping) subsets of the people in the names etc. table. Way back in the past I used M$ access to handle these data. At that time my data tables were each in a separate file and everything worked very well. When I moved over to OpenOffice.org I found that this was no longer possible. In order to run queries using data from different tables (all using the same database structure) I found I needed to embed all the tables into the same .odb files. So my question is, is this still the same in LibreOffice? More specifically, is it possible only using base, i.e. without running a separate database application, to access, run queries across and manipulate the data in data tables contained in separate files? I am assuming the these would probably be HSQLDB data tables. If not, would it be possible if the data tables were embedded in the same file? If either of these are possible then some pointers to how to separate out the data tables would be helpful. Kind regards John -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Base - using external database tables
I think the simple answer to your question (Is this the same in LO) is yes. But, as others have suggested, it is because you haven't quite got the right way of looking at it. Using a database rather than a spreadsheet allows you to group things according to the content of the data rather than where it is. With a database you can pull out any of subset of your data by selecting a group according to one of it's unique factors, for example select all phone numbers from people in the advanced group (for example) because you have a field in your data for advanced group, not because all those people are in a separate file. I've worked on large databases written by so-called professionals which used tables to separate data, and it is almost always just slower and more complicated than arranging the data better. I'd guess from some of the things you've said in your last mesage that you've read Mariano Casanova's Step by Step guide to Base. It has a very good section on organising data that I think will make your life much easier. In general it looks like Base (either LO or OOO) only accesses one database at a time. You probably want to stop thinking so much about files since the concept doesn't help very much in most of the context of what you want to do. As a last note, I'm using a MySQL backend under Fedora and no, it's not fast. I'll look into why, but I wonder if it's the JSQL interface too. Regards Mark Stanton One small step for mankind... -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Base - using external database tables
John On Thu, 2011-10-13 at 07:14 +0100, John Talbut wrote: Hi OK, let me go back to square 1. First of all I am not talking about a situation in which LibreOffice base needs to access data tables in separate files that use different database structures, e.g. one table is in MySQL and another is in PostgreSQL. This may be something that others need and I seem to recall that M$ access could do this but I do not need to. Next, terminology because database can mean a number of things. Please correct me if I am wrong here. The data are contained in tables. These can be simple tables such as CSV tables or more complex tables constructed according to the rules of a particular database application. These tables can be in separate files or they may be embedded along with other information. CSV files are normally used for importing and exporting data into or out of a database. Databases do not use csv or txt files otherwise. Then there are such things as queries, forms, reports and configurations which could, I guess, be each in separate files but are normally saved embedded together or with, say a data table. The results of running these, if saved, will be data tables or reports in separate files. Finally there is the database application which may be in one or many files. Now, to my situation. First of all I am using Debian GNU/Linux testing distribution. I use LibreOffice and for my database needs I use base. I use the built in HSQLDB database application and have my data tables embedded in the .odb file. This has worked adequately (clunkily I would say, but I will keep other difficulties out of this discussion). However, base has recently become unusably slow in response to something that has changed in openjdk (I have asked about this elsewhere). There have been problems with various recent Java versions causing performance problems. The work around to is use an earlier version of Java. This has prompted me to revisit the question of whether to separate out my data tables. I am wondering whether it might be a workaround for the slowness problem. Also there may be advantages in backing up data separately and having it less vulnerable to anything going wrong in LibreOffice. Backing up the data in a format that allows to reconstruct the database at worst or just open the backup is always good practice. This can be extended to backing up all data so one can restart if there is a hardware failure. My data consist of a list of names, addresses etc. in one table and then some other tables that have additional information that only applies to (overlapping) subsets of the people in the names etc. table. I am not sure about your design. There is procedure used with databases called normalization. The idea is to group similar data into separate tables with use of primary and foreign keys to allow linking. For example, one would have a table of vendor addresses, another of vendor contacts, another of vendor payment information, etc. Each table would have its own primary key (often an integer) and relationships with the other tables use a foreign key that is same as the primary key in the other table. Appropriate normalization tries to isolate different types of data into logical groups that allows easier data maintenance, access, and understanding of the relationships between the data. One can study data modeling and database design independent of the database application, the normalization procedure is not dependent on the applicaton. A good design for a specific situation will be implemented similarly in any database application. Way back in the past I used M$ access to handle these data. At that time my data tables were each in a separate file and everything worked very well. When I moved over to OpenOffice.org I found that this was no longer possible. In order to run queries using data from different tables (all using the same database structure) I found I needed to embed all the tables into the same .odb files. So my question is, is this still the same in LibreOffice? More specifically, is it possible only using base, i.e. without running a separate database application, to access, run queries across and manipulate the data in data tables contained in separate files? I am assuming the these would probably be HSQLDB data tables. If not, would it be possible if the data tables were embedded in the same file? If either of these are possible then some pointers to how to separate out the data tables would be helpful. Kind regards John To clarify a few points, in most database applications the database consists of a group of tables with related data with other constructs such as views, functions, etc that are used with the database. Within the database, data in different tables can be accessed by the use of SQL joins, unions, and subqueries. For example a query between
Re: [libreoffice-users] Base - using external database tables
On Thu, 2011-10-13 at 09:39 +0100, Mark Stanton wrote: I think the simple answer to your question (Is this the same in LO) is yes. But, as others have suggested, it is because you haven't quite got the right way of looking at it. Using a database rather than a spreadsheet allows you to group things according to the content of the data rather than where it is. With a database you can pull out any of subset of your data by selecting a group according to one of it's unique factors, for example select all phone numbers from people in the advanced group (for example) because you have a field in your data for advanced group, not because all those people are in a separate file. I've worked on large databases written by so-called professionals which used tables to separate data, and it is almost always just slower and more complicated than arranging the data better. I'd guess from some of the things you've said in your last mesage that you've read Mariano Casanova's Step by Step guide to Base. It has a very good section on organising data that I think will make your life much easier. In general it looks like Base (either LO or OOO) only accesses one database at a time. You probably want to stop thinking so much about files since the concept doesn't help very much in most of the context of what you want to do. As a last note, I'm using a MySQL backend under Fedora and no, it's not fast. I'll look into why, but I wonder if it's the JSQL interface too. When I use Base with MySQL/MariaDB I use the direct connection option. I forget which package is needed for this, I know I installed a MySQL to LO connection from the Ubuntu repositories. The connection seems reasonably quick and I can access all the databases/schemas on my box. Regards Mark Stanton One small step for mankind... -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
