MoonPoint Support Logo

 


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals



Advanced Search
August
Sun Mon Tue Wed Thu Fri Sat
   
24 25 26
27 28 29 30 31    
2017
Months
AugSep
Oct Nov Dec


Fri, Aug 11, 2017 8:42 pm

Using SQL Inner Join

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:

NameType
CRQText
DeviceText
ProjectText
NotesText

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:

NameType
DescriptionText Unique
ManufacturerText
ModelText
SiteText
BuildingText
RoomText
NotesText

[ More Info ]

[/software/database/sql] permanent link

Tue, Jul 18, 2017 10:53 pm

DB Browser for SQLite upgrade to version 3.9.1 on Mac OS X

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

Thu, Jun 29, 2017 10:48 pm

Selecting unique column entries with SQL

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

Sun, May 28, 2017 10:10 pm

Querying an SQLite Database With PHP

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

Fri, Mar 24, 2017 10:31 pm

Redirecting SQLite output to a file

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

Wed, Feb 08, 2017 8:41 pm

Running SQL commands from a text file using sqlite3

Every week I need to determine the number of tasks I reviewed and approved in the prior week. I track the tasks in a database on my MacBook Pro laptop using the SQLite utility that is part of the OS X operating system - see Using SQLite. I have a database named "CRQ" that contains a table named "Tasks". The schema for the table is shown below; approval dates are stored as integers; SQLite does not have a storage class specifically for dates, instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values1.

$ 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

Tue, Jun 14, 2016 10:12 pm

Count Records with SQL

I needed to count the number of records in a table I use for tracking tasks within an SQLite database. The table is named "Tasks" and, in addition to other columns has a Received column, where I record when I received a task and an Approved column, where I record the date I approved the task. If I only want to see the task numbers and the dates on which I received and approved tasks and I want the records ordered by approval date, I can use the following Structured Query Language (SQL) command:
sqlite> select Task, Received, Approved from Tasks ORDER BY Approved;
TAS000000204813|2016-04-20|
TAS000000160855|2016-06-09|
TAS000000302389|2016-06-08|
TAS000000289579|2016-05-04|2016-05-06
TAS000000297385|2016-05-11|2016-05-11
TAS000000294896|2016-05-04|2016-05-12
TAS000000294136|2016-05-04|2016-05-12
TAS000000291991|2016-05-04|2016-05-13
TAS000000289882|2016-05-10|2016-05-13
TAS000000293978|2016-05-04|2016-05-16
TAS000000262109|2016-05-18|2016-05-19
TAS000000293289|2016-05-24|2016-05-24
TAS000000300934|2016-05-18|2016-05-24
TAS000000192984|2016-05-20|2016-05-24
TAS000000268274|2016-05-19|2016-05-24
TAS000000280549|2016-05-24|2016-05-24
TAS000000289548|2016-05-18|2016-05-24
TAS000000291959|2016-05-24|2016-05-24
TAS000000293970|2016-05-26|2016-05-26
TAS000000280569|2016-05-31|2016-05-31
TAS000000297347|2016-05-31|2016-05-31
TAS000000283262|2016-06-01|2016-06-07
TAS000000304021|2016-06-01|2016-06-07
TAS000000311853|2016-06-01|2016-06-07
TAS000000311293|2016-06-01|2016-06-07
TAS000000312411|2016-06-08|2016-06-08
TAS000000309299|2016-06-09|2016-06-09
TAS000000270128|2016-06-09|2016-06-09
TAS000000255397|2016-06-13|2016-06-13
TAS000000295302|2016-06-09|2016-06-14
TAS000000299922|2016-06-08|2016-06-14
TAS000000298570|2016-06-14|2016-06-14
TAS000000315505|2016-06-08|2016-06-14
sqlite>

I can count all of the records in the table with the SQL command below:

sqlite> select COUNT(*) FROM Tasks;
33
sqlite>

Within the table, I have a field named "Approved" that I use to track the approval date for tasks; I store the date as a text field, since SQLite doesn't have a storage class specifically for dates. If I want to know how many tasks I've approved since a specific date, I can use a command like the one below to determine the number approved since the specified date.

sqlite> select COUNT(*) FROM Tasks WHERE Approved > "2016-06-07";
8
sqlite>

[ More Info ]

[/software/database/sqlite] permanent link

Wed, May 04, 2016 9:45 pm

DB Browser for SQLite on OS X

If you would like a graphical user interface (GUI) to view and manage SQLite databases, you can use DB Browser for SQLite, which was originally developed by Mauricio Piacentini of Tabuleiro Producoes as the Arca Database Browser. The program was later modified to be compatible with SQLite 2.x databases and rleased into the public domain. In 2014, the project was renamed to "Database Browser for SQLite" at the request of Richard Hipp , the creator of SQLite. The software is available for Microsoft Windows, Apple OS X, and Linux systems. You can use it to view the records in tables in existing databases, add new records to tables, etc.

[ More Info ]

[/software/database/sqlite] permanent link

Fri, Apr 08, 2016 10:26 pm

Using SQLite

An easy way to create and maintain Structured Query Language (SQL) databases at no cost is to use the free SQLite software, which is available for Linux, OS X, and Microsoft Windows systems and also as C source code.

SQLite is provided by Apple with the current version of OS X; I don't know when it was first included with OS X, but I know it has been present from at least OS X 10.8.5 (Mountain Lion). If you open the Terminal application, which you can find in /Applications/Utilities, and type which sqlite3, you should see the program.

[ More Info ]

[/software/database/sqlite] permanent link

Once You Know, You Newegg AliExpress by Alibaba.com

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo