Simple Databases

apatheticyogurtSoftware and s/w Development

Dec 13, 2013 (3 years and 6 months ago)

95 views

Simple Databases

Setting Up SQLite in Eclipse


Use SQLite


already installed on the
linux

machines


Download one of the following two SQLite
JDBC drivers


sqlitejdbc
-
v056.jar


sqlite
-
jdbc
-
3.7.2.jar


Store it wherever you like

At Least Two Methods to Get it
Working


Both basically put the jar you just downloaded
in the build path for your project.


Technique 1:Right click on your project icon in
the Package Explorer. In the menu select
Build
Path

and then
Add External Archives
. Use the
folder explorer that appears to find the jar file
you downloaded and select “open” and it will
be made part of your program’s build path.



At Least Two Methods to Get it
Working


Technique 2:


Select
Run

at the top of the page.


Select
Run Configurations…
about 5 lines down.


Select the
Classpath

tab in the row of tabs underneath the
name of your main routine.


In the
Classpath

window select
User Entries


Select Add External Jars… from the right column


Now navigate to the folder where you stored your
sqlite

jdbc

jar file


Select the jar file


Hit the
Open

button


Then select
Apply

button




Installing SQLite3 on Linux


Linux


Download the source file from (usually the second
file listed)
http://www.sqlite.org/download.html


t
ar

xzvf

the downloaded file


cd to the new folder


./configure


make


make install

Installing SQLite3 on a Mac


On a recent OS you don’t have to, it is already
there

Installing SQLite3 on Windows


Download the first two zip files from the
section labeled
Precompiled Binaries for
Windows
.


Unzip them and place the three resulting files
in C:
\
WINDOWS
\
system32 (or any directory
on you PATH.


Alternative: I created a new directory called SQLite
in C:
\
Program Files (x86) and placed the three files
in that location. I then extended the PATH
variable to search that location

Adding the SQLite Manager to
FireFox


You can manage an SQLite database using the command
line and text
-
based SQLite commands, but, it is easier to
the SQLite Manager extension you can get for Firefox.


First, start Firefox


Then go to


https://addons.mozilla.org/en
-
US/firefox/addon/sqlite
-
manager/


and hit the green “Add to Firefox” button and install

the extension.


After it is installed you can click on the “SQLite Manager”
under the Tools tab at the very top.



Using SQLite Manager

Creating/Deleting/Closing/

Connecting To a Database


Creating a Database


Database Tab >> New Database


Enter name (.
sqlite

will be appended to end)


Navigate to desired folder and click “Select Folder”


Close a Database


Database >> Close Database


Connect to Database


Database >> Connect Database


Navigate to desired folder, select
sqlite

file, hit Open


Delete Database


From command line , Windows Explorer, or Finder navigate
to appropriate folder and remove file containing database




Using SQLite Manager

Creating a Table


Adding a simple Table


Table >> Create Table


Fill in Table Name text box with the new table name


For each column (attribute)


Fill in Column Name text box with column name unique to this table


Select Data Type

»
Only real types are Text, Numeric, Integer, Real, None (Blob)


Select/Enter Constraint: Primary Key,
Autoinc
, Allow Null, Unique, Default Value


Enter attributes or columns


Give it a name


Give it a data type


Identify it as having


Enter a Column Name (e.g. Id)


Select a Data Type (e.g. Integer)


Select the appropriate check boxes

»
Primary Key

»
Autoinc

»
Allow Null (Not NULL)

»
Unique

»
Default: Enter Value (rarely use one of possible selections)


Hit OK Button



Using SQLite Manager

Viewing Database


Select a Table


Select
Structure

tab


Shows the corresponding Create statement


Operations (also in the
Structure

tab)


Drop


Empty


Rename





Using SQLite Manager

Adding Data


Select Table on Left >> Select
Browse and Search

tab


Add a tuple


Hit
Add

Button, Fill in values (default shown), hit OK
button


Notice SQL statement


Update a tuple (Edit)


Select Row, Hit
Edit

Button, Modify Values, hit OK


Delete a tuple


Select Row, Hit
Delete

Button (you better be sure)





Using SQLite Manager

Searching Database


Select a Table


Select
Browse & Search

tab


Hit
Search

button


Enter operations and values for all attributes in
query


Hit
OK

Button




SQL Statements by Example


Will be used in JDBC


Execute SQL
button


All previous operations and much more can be
done with SQL Statements


SQL Manger used primarily for viewing



Create Table


CREATE TABLE Person (


PersonId

Integer Primary Key
AutoIncrement

,



/* It must always be non
-
null.





Autoincrement

only allowed on Integer Primary Key



*/


PersonName

Text,
--

Notice it can be null


Birthdate Text NOT NULL)


CREATE TABLE Marriage (


MarriageId

Integer Primary Key,


Husband Integer REFERENCES Person(
PersonId
),
--

one way to specify foreign keys


Wife Integer,


FOREIGN KEY (Wife) REFERENCES Person(
PersonId
))


another way to specify foreign keys


There is a Default Primary Key if none specified


As if there were the attribute: ROWID Primary Key


Turning on and off foreign key checking


PRAGMA
foreign_keys

= ON;


PRAGMA
foreign_keys

= OFF;




DROP, EMPTY, or RENAME Table


DROP TABLE Person;


DELETE FROM Person;


ALTER TABLE Person RENAME TO Student;
--

not used much




Adding Tuples To A Table


INSERT INTO Person VALUES(1, “William”, “Male”, “222 West Elm”, 27);


--

The order of values is important




INSERT INTO Person (Id, Name, Gender, Address, Age)


VALUES (2, “William”, “Male”, “222 West Elm”, 27);


INSERT INTO Person (Age, Address, Gender, Name, Id)


VALUES (27, “222 West Elm”, “Male”, “William”, 3);


INSERT INTO Person (Id, Name)


VALUES (4, “Bill”);
--

other values become NULL








Modifying a Tuple


UPDATE Person


SET Name = “Bill”, Phone = “801
-
555
-
5555”


WHERE Name = “William”;

Simple Search


SELECT * FROM
GradeBook


WHERE Grade = “A”;


SELECT * FROM
GradeBook


WHERE Grade = “A” AND Section = 1;


SELECT * FROM
GradeBook


WHERE MidTerm1 IS NULL;

Deleting a Tuple


DELETE FROM Class List


WHERE Section = 2;

236 Review



or
Project,


or Select,


or Rename




or
Project


Select
Execute SQL
Tab


Sample SQL statements


SELECT
x,z

FROM Person;


SELECT * FROM Person;


Semi colon not needed in SQL Manger but is needed in JDBC




or Select


Select
Execute SQL
Tab


Sample SQL statements


SELECT * FROM T WHERE x = y;




or Rename


renames column in resulting table


Used primarily to control Join


Select
Execute SQL
Tab


SELECT X AS A, Y, Z FROM T;


Multi
-
word names should be quoted



Queries


Union



SELECT Name, Address


FROM
ClassList


UNION


SELECT Name, Address


FROM
FacultyList
;


Doesn’t have to be Union compatible


The names from the second table are “renamed”
to the names of the first table


Queries


Difference



SELECT Name, Address


FROM Staff


EXCEPT


SELECT Name,
Addr


FROM Students


Queries


Intersection



SELECT Name, Address


FROM Staff


INTERSECT


SELECT Name,
Addr


FROM Students


Queries

Cartesian Product


SELECT * FROM Person CROSS JOIN Spouse;



Queries

Natural Join


SELECT * FROM Person NATURAL JOIN Spouse;


To rename before natural joins use nested queries or
sub queries


CREATE TABLE Person (Id Integer Primary Key, Name TEXT);


CREATE TABLE Spouse(Id Integer Primary Key, Spouse Integer);


SELECT Spouse1, Name AS Spouse2


FROM Person NATURAL JOIN



(SELECT Spouse AS Id, Name AS Spouse1




FROM Person NATURAL JOIN Spouse



);


Any place you can put a Table name in a query you can put:
(SELECT …)



Queries

Inner Join


SELECT



Customers.AcctNumber
,

Customers.

Custname


FROM




Customers

INNER JOIN

cust_invoice



ON

Customers.AcctNumber

=

cust_invoice.AcctNumber
;


SELECT



Customers.AcctNumber
,

Customers.

Custname


FROM




Customers

JOIN

cust_invoice



ON

Customers.AcctNumber

=

cust_invoice.AcctNumber
;



Join controlled by “ON” clause


Queries

Left Outer Join


SELECT



Customers.AcctNumber
,

Customers.

Custname


FROM




Customers

LEFT OUTER JOIN

cust_invoice



ON

Customers.AcctNumber

=

cust_invoice.AcctNumber
;


SELECT



Customers.AcctNumber
,

Customers.

Custname


FROM




Customers

LEFT JOIN

cust_invoice



ON

Customers.AcctNumber

=

cust_invoice.AcctNumber
;



All tuples in Left table of the join that do not match tuples from the right
table according to the “ON” clause are preserved by appending a so
-
called generated
“Default”
tuple from the right table.


Queries

Right Out Joins



There is NO Right Outer Join in SQLite


If you wanted


Customers.AcctNumber
,

Customers.

Custname


FROM




Customers

RIGHT OUTER JOIN

cust_invoice



ON

Customers.AcctNumber

=

cust_invoice.AcctNumber
;


You would use


Customers.AcctNumber
,

Customers.

Custname



FROM




cust_invoice

LEFT OUTER JOIN

Customers




ON

Customers.AcctNumber

=

cust_invoice.AcctNumber
;


Queries

Outer Joins



There is NO Outer Join in SQLite


If you wanted


Select
Customers.AcctNumber
,

Customers.

Custname


FROM




Customers

OUTER JOIN

cust_invoice



ON

Customers.AcctNumber

=

cust_invoice.AcctNumber
;


You would use


Select
Customers.AcctNumber
,

Customers.

Custname



FROM




cust_invoice

LEFT OUTER JOIN

Customers




ON

Customers.AcctNumber

=

cust_invoice.AcctNumber


UNION


Customers.AcctNumber
,

Customers.

Custname



FROM




Customers


LEFT OUTER JOIN

cust_invoice



ON

Customers.AcctNumber

=

cust_invoice.AcctNumber
;



Views
--

Giving a Query a Name



CREATE VIEW
TableName

AS SELECT …



CREATE
VIEW
ParentsChildren

AS



SELECT
PersonName

AS
ParentName
,
ChildName





FROM
Person JOIN






(
SELECT
ParentId
,
PersonName

AS
ChildName








FROM
Person JOIN Children







WHERE
Person.PersonId

=
Children.ChildId






)


WHERE
Person.PersonId

=
ParentId
;



You cannot edit (delete, insert, or update) a view


Also an example of nested queries




Using The Command Line For

Creating/Deleting/Closing/

Connecting To a Database


Creating a Database


s
qlite3
newDBName.sqlite


If the file
newDBName.sqlite

does not exist it will create a new
database with that name for you


Close a Database


While in sqlite3 type .quit at the prompt


Connect to Database


Assuming there is an existing “
DB.sqlite
” database

Sqlite3
DB.sqlite


Delete Database from the command line


In Windows type: del
DB.sqlite


In Unix or a Mac type :
rm

DB.sqlite




Transactions


Treat sequence of 2 or more SQL statements
as if it were a single statement.


Constraints not checked until the end of the
transaction


ACID
: Atomic Consistent, Isolated, and
Durable


Transactions

Autocommit

or Atomic Transactions


Autocommit

or atomic transaction

UPDATE authors


SET
au_fname

= 'John‘


WHERE
au_id

= '172
-
32
-
1176'

Transactions

Multi
-
Statement Transactions


BEGIN TRANSACTION


UPDATE authors



SET
au_fname

= 'John'



WHERE
au_id

= '172
-
32
-
1176‘;


UPDATE authors



SET
au_fname

= '
Marg




WHERE
au_id

= '213
-
46
-
8915'



COMMIT TRANSACTION;


The word “COMMIT” may be replace by “END”


SavePoints


Rollbacks


IF statements

IF (…) BEGIN


ROLLBACK TRANSACTION [TO SAVEPOINT
savepointName
]

END ELSE BEGIN


COMMIT TRANSACTION

END

Using JDBC


Sample.java


Sample2.java



Shows drop table and insert


Empty Table and Rename Table


Update (Modify)


Simple Select


Deleting a Tuple


Select, Project, Rename


Union, Intersection, Difference


Joins


Transactions


Views


Support Classes:





SetUp.java

SetUp4.java

SetUp2.java

Table.java

SetUp3.java

QueryResult.java