Presentation Notes by Deid Reimer
About SQLite
- SQLite is a relational database management system contained in a C programming library.
- The database is contained in a file.
- As compared to other DB management systems, SQLite is not client–server. It is embedded into the end program.
Create Data
Since one needs data to have a database, and I have recently been in
Arizona I figured I would make a DB of some of the minerals of Arizona. Thanks to the web site
http://www.arizonaedventures.com/arizona/blog/articles/arizona/arizona-rocks-and-minerals/
- Then the data needs to be put into a form that can be entered into the DB. This could be done manually, entry by entry.
- I scraped the data off the above web page and put it into a .csv file.
- myMinerals.txt
1|Agate|agate.jpeg|A striped, banded, or variegated semiprecious form of quartz. Petrified wood is usually an agatized wood. 2|Amethyst|amethyst.jpeg|A crystalline type of quartz colored purple by manganese. 3|Calcite|calcite.jpeg|Calcite is a common mineral made of calcium. Pure calcite is white but crystals may be clear. It often forms around springs and in caves. 4|Chalcedony|chalcedony.jpeg|A smooth frosty white or gray translucent form of quartz that fills cracks, lines cavities, and forms crusts. 5|Chert|chert.jpeg|A brittle form of quartz that has sharp edges, used for making arrowheads. Opaque brown, yellow, or gray in color. Black chert is called flint. 6|Copper|copper.png|May be found in its native metallic form or in greenish ores such as chrysocolla, malachite and azurite. 7|Feldspar|feldspar.jpeg|A pale pink mineral commonly found in granite. 8|Fluorite|flourite.jpeg|Calcium fluoride occuring as glassy purple or yellow chunks or crystals, either cube-shaped or eight-sided. 9|Galena|galena.jpeg|A mineral that contains lead and forms cube-shaped crystals. 10|Gold|gold.jpg|A precious metal, often associated with veins of quartz. 11|Gypsum|gypsum.jpg|Calcium sulfate occurs naturally in soft white layers, or in rounded shapes called “desert roses.” 12|Halite|halite.jpg|Naturally occurring sodium chloride (rock salt). 13|Hematite|hematite.jpg|Iron ore, dense and colored dark red or dark gray. 14|Jasper|jasper.jpg|A pretty, opaque quartz usually colored red or reddish brown. 15|Magnetite|magnetite.jpg|A grayish black iron ore that is naturally magnetic and can be picked up easily with a magnet. Lodestone is a form of magnetite. 16|Manganese|manganese.jpg|Dark purple or steel gray, may have fern-like patterns (dendrites), gives amethyst its purple color. 17|Mica|mica.jpg|A colorless or black silicate mineral that comes in thin, flexible sheets. Found in granite and other igneous rocks, it also forms in metamorphic rocks. 18|Pyrite|pyrite.jpg|A metallic mineral made of iron and sulfur, brassy yellow in color and forming cubic crystals, also known as fool’s gold. 19|Olivine|olivine.jpg|An olive green mineral common in igneous rocks. 20|Quartz|quartz.jpg|The most common mineral, a form of silica. It is clear or colored and may or may not have crystals. Quartz can be milky, smoky, or rosy in appearance. Other types of quartz include: amethyst, tiger eye, agate, jasper, flint, chert, chalcedony. 21|Silver|silver.jpg|A precious metal, often associated with ores of lead and zinc. 22|Tiger Eye|tigereye.jpg|A type of quartz that has mineral fibers trapped inside. 23|Turquoise|turquoise.jpg|A semiprecious gem made of copper and aluminum phosphate.
- The other choice I made was to just put the name of the mineral image files into the DB rather than the actual data. Then reference the files in the created html. You could put the binary image data into the DB and then pull that out as an image. For another day.
Create Database
- Since this is php and sqlite I created a php program to create the database. With Assistance from https://www.tutorialspoint.com/sqlite/sqlite_php.htm
- makeDB.php
<?php
// Create a new database
// Thanks to:
// https://www.tutorialspoint.com/sqlite/sqlite_php.htm
// for a starting point
// Class to open or create a DB file. The constructor takes a db name argument
// This new class extends(uses) the standard SQLite3 libraries.
class MyDB extends SQLite3 {
function __construct($dbName) {
$this->open($dbName);
}
}
// Check if a filename argument was passed to us. If so do the work.
// Otherwise politely complain.
if (isset($argv[1])) {
// Get the filename
$name = $argv[1];
// Create a new instance of the MyDB class. Which will open/create the new DB file
$db = new MyDB($name);
// Check if it works by seeing if we got a database object. No complain.
if(!$db) {
// It did not open. Display the error.
echo "Could not open: " . $db->lastErrorMsg() . "\n";
// It worked. Tell the user.
} else {
echo "Opened/created the database successfully.\n";
}
// A filename was not supplied. Complain.
} else {
echo "Please enter a DB name to create.\n";
}
?>
Create a Table in the Database
- Again a php program
-
makeTable.php
<?php
class MyDB extends SQLite3 {
function __construct($dbname) {
$this->open($dbname);
}
}
// Get the DB filename
if (isset($argv[1])) {
$name = $argv[1];
// Create an instance of this DB thus opening it.
$db = new MyDB($name);
// Error if it did not open OK
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
// A query to create the specific table for minerals
$sql =<<<EOF
CREATE TABLE Minerals
(ID INT PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
Image TEXT NOT NULL,
Description TEXT
);
EOF;
// Execute the query
$ret = $db->exec($sql);
// Error if it was not created OK.
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo "Table created successfully\n";
}
$db->close();
// Error if DB file not supplied.
} else {
echo "Please enter a DB name to use.\n";
}
?>
Populate Database
- And populate the DB with the data in my .csv file. Again
a php program.
populateTable.php
<?php
// Populate a DB table previously created.
// Thanks to:
// https://www.tutorialspoint.com/sqlite/sqlite_php.htm
// for a starting point
// Class to open or create a DB file. The constructor takes a db name argument
// This new class extends(uses) the standard SQLite3 libraries.
class MyDB extends SQLite3 {
function __construct($dbName) {
$this->open($dbName);
}
}
// Check if a filename argument was passed to us. If so do the work.
// Otherwise politely complain.
if (isset($argv[1]) and isset($argv[2])) {
// Get the DB filename and the text filename
$DBname = $argv[1];
$textName = $argv[2];
// Create a new instance of the MyDB class. Which will open/create the new DB file
$db = new MyDB($DBname);
// Check if it works by seeing if we got a database object. No complain.
if(!$db) {
// It did not open. Display the error.
echo "Could not open: " . $db->lastErrorMsg() . "\n";
// It worked. Tell the user.
} else {
echo "Opened/created the database successfully.\n";
}
// Open the .csv file
$inFile = fopen ($textName, 'r');
// Continue if it opened OK.
if ($inFile) {
// Read lines of the file until end of file.
while (! feof($inFile)) {
$line = fgets($inFile);
// If the line has data continue. The !feof only works after reading past the end.
if ($line){
// Break the line up into its component parts. Create the SQL and insert the line into the DB
list($id, $name, $image, $description) = explode('|', $line);
$sql = "insert into Minerals (ID, Name, Image, Description)
Values ($id, '$name', '$image', '$description')";
$stat = $db->exec($sql);
// If this worked continue.
if(!$stat) {
echo $db->lastErrorMsg();
} else {
echo "Record $id added\n";
}
}
}
}
// A DB or text filename was not supplied. Complain.
} else {
echo "Please enter a DB name and text file name to use.\n";
}
?>