Re: [libreoffice-users] Connection to SQL database

Thanks again, Jay.  Because of my long-time familiarity with Filemaker, 
Access, Salesforce (and Base soon I hope), I'm well aware of the 
structure of a relational database.  And I'm pretty sophisticated user 
and even developer so long as the backend is hidden from me (LOL).

Now I have this file I need to "open" and examine, and then move into 
separate tables/csv files to eventually move into Salesforce. The file 
came from a proprietary online database system (I'm not sure what) as a 
backup.  And you are correct, the structure is almost certainly similar 
to what you outline (e.g. name/contact table, donations table, perhaps a 
volunteer actions table, etc. all with various keys to connect).

But...I don't know just what tables are there, what field/columns are 
there, whether there's any consistency in data entry, and the like.  All 
of this needs to be mapped into appropriate fields in the Salesforce 
system.  I need to "see" the data and edit it (adding fields, splitting 
data up, etc.) in order to plan and prepare for the move, and then 
extract it into a csv file (the only file type that Salesforce will 
accept as a source).

Thanks to Alex's suggestion, I've learned that the file contains table 
structure info (table name, column names and types, Key field 
assignments, etc.) with the actual data for each table included (comma 
separated I think).  I suspect the file is sufficient to construct a SQL 
database that will contain all of the organization's data, either in 
MySQL or in Base or Access.  But I need to figure out how to do that in 
such a way that I can assess what data is there and export into a csv file.

Thanks again.  Any more help is always appreciated.
Carl


On 1/15/14 2:43 PM, Jay Lozier wrote:
> Carl
>
> SQL is the query language used by relational databases and there is 
> ANSI/ISO standard specification for the SQL. So, in principle, any 
> relational database should understand vanilla SQL. Generally, this is 
> true. HSQL (Base backend), Jet (Access), Oracle, MySQL/MariaDB, 
> PostgreSQL, and SQL Server are all relational databases and they all 
> use SQL as their query language. The only warning is all the database 
> providers have added extensions to the SQL language. The GUIs used by 
> Base and Access tend to hide the SQL query from the user. Some of the 
> other tools such as PgAdmin (PostgreSQL) or MySQL Workbench generally 
> require you to write the SQL query in an editor. But these tools allow 
> to use a GUI to access most of the database functions.
>
> The database is program for storing and accessing data that has some 
> sort of structure. In a relational database, the data is organized in 
> tables that consist of rows (specific data) and columns (data types). 
> To keep the data manageable, the data is usually broken up across a 
> series of related tables with defined relations between the tables 
> defined (the data in a specific row in Table A is related to the data 
> in specific row in Table B). In relational database each column has 
> specific data type (number, string, date, etc.).
>
> Your raw data, say for a donor, would consist of name, address, 
> contact information, pledge amount, payments, etc. You would probably 
> split the data into two or more tables. One table would have the donor 
> name, address, and contact information. Another table might have a 
> record of contacts including type, date, result. A third table might 
> have a detail record of payments/donations with date and amounts. To 
> relate each table, each table would likely have a primary key assigned 
> for each entry with appropriate foreign keys (keys that refer to an 
> entry in another table) included as well as the data specific to the 
> table. A query would use these keys to get the data and combine it 
> into a result set. The data design is based on the principle of entry 
> data only once into the database (called "normalization"). So you 
> would only have one table with the donor's name entered while the 
> other tables that would refer to the entry would was the entry's 
> primary key to refer to it. Often integers are used as primary keys 
> because they are easy to deal with and increment nicely. The only 
> requirement for a primary key is that is unique to the table.
>
> If you are using MySQL or MariaDB (MariaDB is a MySQL fork) a pretty 
> good book specific to that family is MariaDB: A Crash Course by Ben 
> Forta. It covers basic SQL, basic database design, etc.
>
> Jay
>
>
> On 01/15/2014 01:34 PM, Carl Paulsen wrote:
> > Thanks, Jay, that is VERY helpful and clarifies a lot.  I wondered if 
> > I needed the server setup but lots of things I read yesterday 
> > suggested I did need that.  I do understand that things like Base and 
> > Access are just GUIs, but I guess I really just don't understand what 
> > a database is and, in particular, what SQL is.  I know there needs to 
> > be raw data and a file/table structure and I assume a way to interact 
> > with it, but I'm still missing a lot.
> >
> > Is there a basic online reference to help me understand what a SQL 
> > database is and what MySQL and the rest are relative to that?
> >
> > Thx,
> > Carl
> >
> >
> > On 1/15/14 12:11 PM, Jay Lozier wrote:
> > > Carl
> > >
> > > Backend is a database term that refers to the database itself such 
> > > as MySQL, JET (MS Access), SQL Server, etc. Most non DBA's refer to 
> > > the GUI tools (BASE, Access) as the database when in fact they are 
> > > used to connect various backends. Base and Access both can connect 
> > > to the variety of backends not just the default they are shipped with.
> > >
> > > I do not think you need XAMP just the database connector and 
> > > database. XAMP refers to X = OS, A = Apache server, M = MySQL, and P 
> > > = Perl/Python/PHP. It is normally used by developers and the reason 
> > > for the Apache server is to provide test for web connectivity. PHP 
> > > is a very common server scripting language used by many websites. 
> > > For Windows is sometimes called WAMP, Mac - MAMP, and Linux - LAMP.
> > >
> > > I use MySQL for database development and do not use LAMP (Linux 
> > > user) at all because I only need the database.
> > >
> > > Jay

--

Carl Paulsen

Dover, NH 03820


--
To unsubscribe e-mail to: users+unsubscribe@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