How Do You Create Two Tables with a Shared Column between Them (e.g., Have a Foreign Key Constraint)?

Problem scenario
You want to create two tables in a Postgresql database.  You want the tables to share a column to relate to one another.  You want to implement a foreign key constraint to achieve this goal.  Is there an example of SQL statements to do this?

Solution
Run these two SQL commands that start with the words "CREATE TABLE":

CREATE TABLE table1 (id integer,
    name    varchar(40),
    PRIMARY KEY(id)
);

CREATE TABLE table2 (id int REFERENCES table1,
    name    varchar(40)
);

The second DDL command (i.e., "CREATE TABLE table2") works because the datatype of id is int.  The datatype has to be the same as the primary key's datatype of table1 the subject of the references keyword.  You'll notice that the keywords "CONSTRAINT" and "FOREIGN KEY" are absent from the commands above.

What Do You Do when It Is Too Loud at a Hotel?

Problem scenario
You are at a hotel, and you want to go to sleep.  The freeway, train, or other guest room noise is too loud.  The hotel cannot or will not do anything about the problem.  What do you do?

Possible solutions
You may want to try ear plugs.  But sleeping in them can be uncomfortable.  They can fall out at night. The best solution may be to turn on the heater or A/C. Alternatively you could try turning on the bathroom fan.  This white noise can drown out the problem.  Some turn off when a temperature in the room has been reached.  You can try to handle whichever temperature will likely be unattainable.  It may be uncomfortable, but it is an option.  Some A/C units have a fan option that will remain on independent of the temperature in the room.  You may want to travel with a compact and light-weight white noise machine. There are iPhone and Android applications for your phone to create white noise.

You may also want to install a free decibel meter on your smart phone or tablet computer.  This can be used to prove the noise you are hearing.  You may want to get a baseline in the hallway and move toward the room where the noise is coming from.  Some hotels can be strict about cancelling non-refundable reservations.  A microphone can improve the accuracy of the dB results.  If you have a laptop, you could try installing a free application like this.  If you are old fashioned, you could buy a physical decibel meter.  Sometimes you are not sure if the sound is too loud or not.  Some hotels will listen to polite requests for refunds.  For some hotels, you may need to read about how small claims lawsuits work with a book like this.  Good luck with falling to sleep!

List of Flask (Web-Framework) Books

Beginning Web Development with Python: from prototype to production with flask, tornado and nginx by Andrei Dan
Building Web Applications with Flask by Italo Maia
Data Visualization with Python and JavaScript: Scrape, Clean, Explore & Transform Your Data by Kyran Dale  
Flask Blueprints by Joel Perras
Flask By Example by Gareth Dwyer
Flask Framework Cookbook by Shalabh Aggarwal
Flask Web Development: Developing Web Applications with Python by Miguel Grinberg
Instant Flask Web Development by Ron DuPlain
Learning Flask Framework by Matt Copperwaite and Charles Leifer
Mastering Flask by Jack Stouffer
Python in 24 Hours, Sams Teach Yourself (2nd Edition) by Katie Cunningham 

How Do You Troubleshoot a Syntax Error When Trying to Create a Table with a Primary Key in SQL?

Problem scenario
You are trying to create a table using SQL.  You use the keywords "CONSTRAINT" and "PRIMARY KEY."  For example, your query (or SQL DML command) looks like this:

create table contint ( specialcode char(6) CONSTRAINT PRIMARY KEY, title varchar(40)) ;

When you run the SQL command you get the message '...syntax error at or near "PRIMARY" LINE...'  What should you do to fix this?

Solution
Make sure you give the constraint itself a name.  The syntax is for the name of the constraint to follow the word "CONSTRAINT" and appear before the "PRIMARY KEY" words.

create table contint ( specialcode char(6) CONSTRAINT initialkey PRIMARY KEY, title varchar(50)) ;

Identifying constraints by names, while mandatory, helps future SQL querying.  The error message that is thrown may include the potentially descriptive name of the CONSTRAINT that was violated.  A constraint's name can be modified later. 

A List of Redis Books

Building Scalable Apps with Redis and Node.js by Joshua Johanan
Instant Redis Optimization How-to by Arun Chinnachamy
Instant Redis Persistence by Matt Palmer
Learning Redis by Vinoo Das
Mastering Redis by Jeremy Nelson
Microservices, IoT and Azure: Leveraging DevOps and Microservice Architecture to deliver SaaS Solutions by Bob Familiar
Node Patterns - Databases: Volume I - LevelDB, Redis and CouchDB by Pedro Teixeira
Redis Applied Design Patterns by Arun Chinnachamy
Redis Cookbook: Practical Techniques for Fast Data Manipulation by Tiago Macedo and Fred Oliveira
Redis Essentials by Maxwell Dayvson Da Silva and Hugo Lopes Tavares
Redis in Action by Josiah L. Carlson
Redis PREDICTIVE ANALYTICS REPORT by Gerard Blokdijk
Redis Programming by Example by Agus Kurniawan
Redis: Questions and Answers by George Duckett

How Do You Fix The Ansible Error “Failed to connect to the host via ssh: OpenSSH_6.6.1, OpenSSL 1.0.1e-fips 11 Feb 2013”?

Problem scenario
You are using Ansible, and you receive an error like this:

coolserver.com | UNREACHABLE! => {
    "changed": false,
    "msg": "Failed to connect to the host via ssh: OpenSSH_6.6.1, OpenSSL 1.0.1e-fips 11 Feb 2013\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 56: Applying options for *\r\npercent_expand: unknown key %C\r\n",
    "unreachable": true

How do you fix this?

Solution
On the Ansible control server, find the ansible.cfg file that is governing the Ansible actions.  Back it up.  Eliminate the "control_path" stanza underneath the ssh_connection section.

The second line of these two would need to be deleted:

[ssh_connection]
control_path = ~/.ssh/ansible-ssh-%%C

How Do You Deploy LAPP with Frontend User Authentication?

Problem scenario
You want a web page that asks for a username and password (e.g., to log in).  The users will not know if the authentication is done via Apache or Postgres.  You happen want the authentication to be challenged against a Postgres database.  This way if you have backend access to the Postgres database, you can use "CREATE ROLE" or "CREATE ROLE jane PASSWORD 'veryfun' LOGIN" to create users.  You will be able to provide the users with the credentials and web URL.  They will gain access to another page other than the landing page if authentication is successful.  This second page may or may not invoke SQL commands.  The users will not need to have knowledge of SQL.  They will not need special software installed on their computers.

How do you deploy LAPP (Linux, Apache, Postgres, and PHP) with a web page frontend to challenge credentials?

Solution
Make sure you understand the security risks of this before implementing it.  This example solution assumes you have installed Apache web server, PHP, php-pgsql, and Postgres on a Linux server (i.e., you have deployed LAPP and need to install php-pgsql).  To install LAPP see this link.  To install php-pgsql, see this link.  This solution also assumes the default location for the web files is /var/www/html/.  Standard deployments of Apache (without special configuration) on Debian/Ubuntu distributions of Linux use this location.

#1  Put this file (<html> tag to </html> tag below) in /var/www/html/ under the name gooda.php:

<html>
<body>

<form action="goodb.php" method="post">
UserName: <input type="text" name="username"><br>
Password: <input type="password" name="password"><br>
<input type="submit">
</form>

</body>
</html>

#2  Put this file  (<html> tag to "end of file")  in /var/www/html/ under the name goodb.php:

<html>
<body>

Username entered: <?php echo $_POST["username"]; ?><br>

<?php $part1 = $_POST["username"]; ?>
<?php $part2 = $_POST["password"]; ?>
<?php $comp = 'user='.$part1.' password='.$part2 ; ?>

</body>
</html>

<?php // echo "Hello world!";
   $host        = "host=127.0.0.1"; 
   $port        = "port=5432";
   $dbname      = "dbname=cooldb";
   $credentials = $comp;

   $db = pg_connect( "$host $port $dbname $credentials"  );
   pg_query("CREATE DATABASE niftydb");

?> //end of file

3.  Change the "cooldb" to the name of an existing database.  Change the "niftydb" to the name of the database you want the SQL DDL command to create.  You may want to change the pg_query command altogether.  It is up to you as this is just an example.

4.  Know that this is a proof-of-concept for educational purposes. For sensitive passwords over the internet, you may want other authentication and security in place.  These PHP files would be appropriate behind a firewall in an enterprise's development or QA environment.  If you browse to the gooda.php page from a web browser (assuming the postgres and Apache web services are running on the Linux server itself), you will be able to type in a username and password for Postgres.  The pg_query command will run if the authentication was successful. 

A List of Git Books

19 Git Tips for Everyday Use by Alex Kras
The Beginner's Guide to GitHub by Thomas Mailund
Building Tools with GitHub: Customize Your Workflow by Chris Dawson and Ben Straub
Distributed Version Control with Git: Mastering the Git Command Line - Third Edition (vogella Series) by Lars Vogel and Alex Blewitt
Dive into GIT by James Ware
Eclipse 4.5 Git Course for Java Eclipse Developers by John R. Hines
Get GIT in a Flash: Just What You Need to Know to Start by Cheryl Mason
Git: Best Practices Guide by Eric Pidoux
Git Distilled by Peter Bell
Git: Distributed Version Control—Fundamentals and Workflows by René Preißel and Bjørn Stachmann
Git Essentials by Ferdinando Santacroce
GIT for IT Students by Dorothy Andrews
Git for Teams: A User-Centered Approach to Creating Efficient Workflows in Git by Emma Jane Hogbin Westby
GIT for Techies by Rhea Kitchen
GIT for Universities by Robert Charles
Git in Practice: Includes 66 Techniques by Mike McQuaid
Git Magic by Ben Lynn
Git Memento by Jie Deng
Git Overview: With Command-Line Incantantations by J Adrian Zimmer
Git Pocket Guide: A Working Introduction by Richard E. Silverman
Git Recipes: A Problem-Solution Approach by Wlodzimierz Gajda
Git Version Control Cookbook - 90 Recipes to Transform your Development Workflow and Boost Productivity by Aske Olsson and Rasmus Voss
Git: Version Control for Everyone by Ravishankar Somasundaram
Git Version Control Guide: Step -by-step tutorial for beginners by Gregory Blake
Introduction to Git-flow: A Git Workflow and Development Model by Chris Weed
Jump Start Git: Take Control of Your Code and Assets by Shaumik Daityari
Learn Enough Git to Be Dangerous: An introduction to version control with Git (Learn Enough Developer Fundamentals Book 3) by Michael Hartl
Learning the Git Bash Shell: Become a Windows Command Line Commando by Michael Hanna
Learn Git in a Month of Lunches by Rick Umali
Learn Version Control with Git: A step-by-step course for the complete beginner by Tobias Günther
Mastering Git by Jakub Narebski
Pragmatic Guide to Git (Pragmatic Guides) by Travis Swicegood
Professional Git by Brent Laster
Pro Git by Scott Chacon
Pro Git by Scott Chacon and Ben Straub
Version Control with Git: Powerful tools and techniques for collaborative software development by Jon Loeliger and Matthew McCullough
What is Git ?! by Seyed Hossein Ahmadpanah

How Do You Create a Table in a Specific Database Using a SQL Front-End GUI Application?

Problem scenario
You are using a SQL front end to query a Postgres database.  (To view directions for installing a free front-end GUI application, click here.) You have run various commands to prove the connection to the database server works.  

Running this query

select version();

shows

"PostgreSQL 9.5.6 on x86_64-pc-linux-gnu, compiled by ..."

You have used CREATE DATABASE successfully.  When you try "create schema epsilon" you get "Error: permission denied for database postgres."  You have a database called "theta."

You have tried using the "connect" word in your SQL queries.  You have tried expanding "public" to find the "theta" database using the GUI portion of my SQL front end.  But you cannot seem to create database-specific tables or schemas.

How do you create a table in a specific database from a SQL front end (GUI application)?

Solution
This assumes the SQL front end GUI application has connected to the Postgres database.  On the left-hand side, right click the database's name (under "public").    Go to CREATE NEW -> TABLE.  In the "Name" field type the desired name of the new table.  Use the "Add" button to add tables.  You can set the Datatype as desired.  The column names can be modified according to your desires.