[More Info]

[/software/database/sqlite/db_browser] permanent link

I have an SQLite database that I use to track approval of tasks. Every Monday, I need to generate a count of the number of tasks approved from the prior Tuesday through the Monday on which I'm creating the report. The approval dates are stored in the database as an integer and I enter them in the form 2018-05-14. I use the following SQL command in a Python script to determine the number I've approved in the last week:

sql = 'SELECT COUNT(*) FROM Tasks WHERE Approved >= date(CURRENT_DATE,"-6 day")'

But I noticed that the count didn't always include the tasks I approved on the prior Tuesday. E.g., when I ran the script tonight, May 14, the number reported was 5, yet I expected the result to be 7.

[ More Info ]

[/software/database/sqlite] permanent link

I have an SQLite database stored on my MacBook Pro laptop that I use to track work requests. The database file is named CRQ.db, since it tracks work done under a Change Request (CRQ). Within that database are several tables two of which are "Equipment" and "Device". For every CRQ that I need to deal with, I store records with the CRQ number in a "CRQ" column and an identifier for each piece of equipment affected by work done under that CRQ in a "Device" column. information on equipment that is affected by the CRQ in the Equipment table. I have another table named "Device" that holds details for each device, including the manufacturer and model number as well as the physical location of the device. The information in the "Description" column in the "Device" table matches the "Device" name in the "Equipment" table. The two tables have the following structure:

Equipment.Device = Device.Description

[ More Info ]

[/software/database/sql] permanent link

[/software/database/sql] permanent link

I have an SQLite database stored on my MacBook Pro laptop that I use to track work requests. The database file is named CRQ.db, since it tracks work done under a Change Request (CRQ). Within that database are several tables two of which are "Equipment" and "Device". For every CRQ that I need to deal with, I store information on equipment that is affected by the CRQ. The equipment table has the following structure:

The device field holds a unique name assigned to the particular device. A device may be affected by more than one CRQ, so it can, potentially, occur multiple times in the table. A CRQ can affect multiple pieces of equipment.

The device table has the following structure:

[ More Info ]

[/software/database/sql] permanent link

I upgraded the version of DB Browser for SQLite on my MacBook Pro today to the latest version, 3.9.1v2. Before upgrading, I had version 3.8.0 on the system. When I checked the version from a command line interface, i.e., a Terminal window, using the system_profiler command, I didn't find any references to the program when I searched for "SQLite:", but I saw the version number listed for "sqlitebrowser."

$ system_profiler SPApplicationsDataType | grep -i "SQLite:" -A 2
$ system_profiler SPApplicationsDataType | grep -i "Browser:" -A 2
    sqlitebrowser:

      Version: 3.8.0
$ system_profiler SPApplicationsDataType | grep -i "sqlitebrowser" -A 2
    sqlitebrowser:

      Version: 3.8.0
--
      Location: /Applications/sqlitebrowser.app
      Get Info String: DB Browser for SQLite

$

[ More Info ]

[/software/database/sqlite/db_browser] permanent link

If you have a MariaDB, MySQL, SQLite, etc. database that may have more than one occurrence of a value for a particular column in a table, but want to display only unique values, you can fillter out the repeated values using SELECT DISTINCT. E.g., I have a table in a database where each record, aka row, in the table holds two fields: one holds an account id (Acct) and the other a project name. An account can be associated with more than one project and any project may have many account IDs associated with it.

To produce a list of the projects in the table without listing any project more than once and to list the projects in alphabetical order, I can use the SQL statement below:

SELECT DISTINCT Project FROM Acct_Projects ORDER BY Project;

Each record, aka row, in the table holds two fields: one holds an account id (Acct) and the other a project name. For the PHP code I use to query the SQLite database that holds the data to display a list of all the projects in the table, I use the following code:

<?php

$filename   = "/Users/jasmith1/Documents/www/SGRS/SGRS.db";

$query_string = "SELECT DISTINCT Project FROM Acct_Projects ORDER BY Project";

$db_handle  = new SQLite3($filename);

$results     = $db_handle->query($query_string);

echo "<table>\n";
echo "<tr><td><b>Project</b></td></tr>\n";
while ($row = $results->fetchArray()) {
    echo "<tr><td><a href=\"project_acctids.php?project=" . 
          $row['Project']  . "\">" .  $row['Project'] . "</a></td></tr>\n";
}
echo "</table>\n";

?>

I make the project displayed on each row of the HTML table a clickable link that will open another page, project_acctids.php that will, for the project that is clicked on, display all of the account ids associated with that project.

[/software/database/sql] permanent link

If you wish to query an SQLite database using PHP, you can do so by placing commands similar to those shown below within the code for a webpage:

<?php

$filename = "/Users/jasmith1/Documents/www/test/presidents.db";

$db = new SQLite3($filename);

$results = $db->query('SELECT * FROM Terms');
while ($row = $results->fetchArray()) {
    var_dump($row);
    echo "<br>\n";
}
?>

In the example above, the database location and file name is stored in the variable $filename. The file must be in a location accessible by the webserver and the file permissions must allow access to the file.

[ More Info ]

[/software/database/sqlite] permanent link

If you need to redirect the output of SQL commands to a file while using SQLite, you can do so using the .output command. E.g., I have an SQLite database on my MacBook Pro laptop running OS X that contains a table named Equipment. Within that table is a column named Device that is a description for the particular piece of equipment in the table entry. I can view just that field for all records with the SQLite command SELECT Device FROM Equipment. To direct the output of the command to a text file named device.txt, I can use the command .output device.txt. After executing the command to select the Device field from all records, I can then issue the .output command without any arguments to it to return to having the output of commands displayed on the console rather than going to the file.

$ sqlite3 ~/Documents/Work/CRQ/CRQ.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .output device.txt
sqlite> SELECT Device FROM Equipment;
sqlite> .output
sqlite>

[/software/database/sqlite] permanent link

$ sqlite3 ~/Documents/Work/CRQ/CRQ.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .schema Tasks
CREATE TABLE "Tasks" (
`CRQ`	TEXT NOT NULL,
`Task`	TEXT NOT NULL,
`URL`	TEXT,
`Received`	INTEGER,
`Reviewed`	INTEGER,
`Approved`	INTEGER,
`Notes`	TEXT,
`Summary`	TEXT
);
sqlite> .exit
$

I can open the database and manually type the command to count the records with an approval date on or after a date I specify using an SQL command like the one below:

$ sqlite3 ~/Documents/Work/CRQ/CRQ.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> SELECT COUNT(*) FROM Tasks WHERE Approved >= '2017-02-01';
11
sqlite>

[ More Info ]

[/software/database/sqlite] permanent link