Re: [libreoffice-users] Connection to SQL database

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
> >
> > On 01/15/2014 11:55 AM, Carl Paulsen wrote:
> > > OK, thanks everyone.  My system:
> > > Mac:  OS 10.6.8 and LO 4.0.4.2 (I was thinking of upgrading to LO 
> > > 4.1.4)
> > > PC:  Win7 and OOo 3.2 (I could install any version of LO on the PC)
> > >
> > >
> > > I'm pretty far over my head here based on how little I understand of 
> > > the replies, but I'm sure I can do it if I persist.  Here's what I 
> > > think I'm hearing so far:
> > >
> > > 1. Lots of talk about the "backend."  I thought the file I had was 
> > > the data itself which I'd hoped I could somehow directly access, but 
> > > even if it were the data, it sounds like at best I'd have to serve 
> > > that file/data using MySQL or something like that and a server like 
> > > XAMP, then connect via Base.  Right? In which case I might be able 
> > > to extract the data directly from the server?
> > >
> > > 2.  Ryan may be right, I think what I have is a so-called backup 
> > > from a commercial fundraising database used by a non-profit I'm 
> > > working with.  Perhaps it's just scripts that connect to a hosted 
> > > database?  How do I "execute" my .sql file on a MySQL server (which 
> > > I've set up on my Win7 machine)?  I am assuming I can get XAMP to 
> > > serve up MySQL on the Win7 machine...  Ryan, can I contact you 
> > > off-list if I figure out how to set up the MySQL server?
> > >
> > > 3.  I assume I'll need a password/login credentials, but can get 
> > > them if I need them.  I'm sure the data is in schema of some sort.
> > >
> > > 4.  Thanks for the reference Fernand.
> > >
> > > Cheers,
> > > Carl

--
Jay Lozier
jslozier@gmail.com


--
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