Recently, we released version 1.0 of myPrayerJournal, a minimalistic prayer journaling application. This series aims to provide a tour of the code, with several stops along the way:

• Part 0: Introduction (this post)
• Part 1: The Front End - Vue components and routing
• Part 2: State in the Browser - Vuex and getting information from an API
• Part 3: The API - Giraffe and JSON web endpoints
• Part 4: Authentication - Auth0, using information in both app and API
• Part 5: The Data Store - EF Core backed by PostgreSQL, with the DbContext defined in F#
• Part 6: Documentation - GitHub Pages generated on each commit
• Part 7: Conclusion - Lessons learned and opinions based on the development experience

From a technical perspective, this application was going to be a learning experience. We knew we wanted to use a Single Page Application (SPA) framework with an API; we'd built APIs before, but had yet to build a SPA. For front-end frameworks, we started with Angular, went through Aurelia and Elm, then decided on Vue. For the back-end API, we started with Suave, then went live on Node.js with Koa; later, we moved it to Go, and after .NET Core 2.1 was released, landed on Giraffe. The “learning experience” part was a success; through all these attempts, we utilized 5 different languages and 3 different database access techniques.

To understand the requirements, a short explanation of the process will help. “Prayer journaling” is a discipline where a person will write down the things for which they are praying; this provides a defined list to help guide their prayer, and helps them not forget things. Then, as the situation changes, they can record updates, through to the resolution of the situation (also called the request being “answered”). This discipline not only helps to focus efforts, it also provides a record of requests and answers. Although people have successfully used a notebook, or something similar, for a long time, that approach does have some downsides:

• For long term requests, you can run out of room for updates.
• A physical journal can only be in one place at one time.
• Answered requests coexist with unanswered requests, so you have to flip pages past them.
• Books can end up under stacks of other things, falling victim to “out of sight, out of mind.”

Looking to address some of those, the initial requirements started as the first three bullets below. The remaining requirements emerged through using the application as it was being developed.

• List unanswered requests, in a way that they can be marked as prayed or answered, and be updated
• List answered requests, and allow full requests (and their history) to be viewed
• Do the above in a way that will not be distracting
• Allow notes to be recorded for a request; not every update on a situation requires a change in the verbiage of the request
• Allow requests to be “snoozed” (removed from the journal, with a specified date when they will reappear), and list snoozed requests so that the snooze can be expired (returning the request to the journal immediately)
• Allow requests to be prioritized (this became the request recurrence feature)

Armed with these requirements, we will pick up next time with a look at the Vue front end.

Oracle has released version 3.2 (.20.09) of their SQL Developer tool. They're still releasing RPMs, so developers on Debian-based systems need to use alien to install it on their machines. We have done that, and have made this available for others to use as well. What makes this particular release of SQL Developer so great is that it now runs reliably under Java 1.7 - no more keeping a 1.6 JDK floating around just for SQL Developer!

The .deb package can be downloaded here, or you can browse current and previously posted packages in the “SQL Developer” directory of the Bit Badger Solutions Software Repository.

When we began developing C# web applications, we found ourselves in the position of determining what the best way of accessing the database is. We evaluated several technologies…

• NHibernate - May be very good, but it was overkill for what we were trying to do.
• LINQ to SQL - This brings C#'s LINQ (Language-Integrated Query) to SQL databases. You create database-aware classes and use LINQ to select from collections, which LINQ to SQL converts to database access. This is a good abstraction, but it relies on SQL Server; as we typically deploy to PostgreSQL, this didn't work. (We also couldn't get DBLinq, a database-agnostic implementation, to work.)
• ADO.NET - This is the tried-and-true database access methodology, released as part of the initial release of the .NET framework. The downside to this is that it encourages SQL in the code at the point of data retrieval; it does not provide a clean separation of data access from data processing.
• EF Code First - This didn't exist; it's also very SQL Server-centric. Not faulting Microsoft for that, especially since they release a free version now; but, as we deploy on Linux, until they release a Linux version, SQL Server is not an option.

With our PHP applications, we had written a database service that read queries from XML files. Then, queries were accessed by name, with parameters passed via arrays. The one thing that ADO.NET has that was useful was the fact that it is based on interfaces. This means that if we wrote something that exposed, manipulated, and depended on IDataConnection (instead of SqlConnection, the SQL Server implementation of that interface), we could support any implementation of database. The SqlDataReader implements IDataReader as well. Our solution was becoming apparent.

Over time, we developed what is now the Database Abstraction project hosted on CodePlex (UPDATE: migrated project to GitHub). On Thursday, we released the first public release (although the DLLs are in the repository, and are usually current at every commit). If you are looking for a way to separate your data access from the rest of your code, or want a solution that's database-agnostic, check it out. It supports SQL Server, MySQL, PostgreSQL, SQLite, and ODBC connections *, using the data provider name to derive the proper connection to implement. There is also a Mock implementation to support unit tests; this mock can provide data, providing a useful way to test methods. Finally, there is a membership and role provider based on Database Abstraction; simply configure the connection string, create the database tables, and away you go! **

A pre-release version is already in production use in our PrayerTracker application, and others are being built around it. If this sounds like something that could help your project, certainly feel free to check it out!

* Oracle is omitted from this list, as their DLL had redistribution restrictions; this meant that the source code repository, upon check-out, would have build errors. There may be an Oracle implementation in the future (it would be trivial), but there is not one now.

** The membership and role providers are untested; they will be tested and tweaked by version 0.9.

It had been a while since I had updated SQL Developer. It turns out that version 2.1 was released March 1st of this year. I've downloaded it and created a Debian package. It can be downloaded from the Bit Badger Solutions Linux Software Repository.

I've used it with Sun's Java 6 Update 18; I have not tested it with OpenJDK. If you have problems getting it to work, you may want to check the previous post on this topic.

Oracle SQL Developer is a Java-based tool that provides a graphical interface to a database. While it's main focus is Oracle (of course), it can be hooked up, via JDBC, to many other databases, such as MySQL, PostgreSQL, and SQL Server. It's similar to Toad, but is provided by Oracle at no cost.

Oracle provides SQL Developer in either an RPM, or a generic binary install. I like the ability to manage packages, but I've never had much luck at getting RPM to run on Ubuntu. I downloaded the RPM file, and, using alien, I converted the package to a .deb package (Debian package format) and installed it. It worked like a charm!

I haven't tested it with gcj, but using Sun's Java 6 update 7 from the Ubuntu repositories, it ran just fine. After you install the package, do a directory list on /usr/lib/jvm. You're looking for the Sun JDK - if it's installed, you'll have a symlink java-6-sun that points to java-6-sun-1.6.0.07. Once you've determined the location of the JDK, run “sqldeveloper” from the command line - the program will prompt you for the path to your JDK. Enter it (probably /usr/lib/jvm/java-6-sun) and you're good to go. (You have to install the package as root - but, for the rest of these steps, use your normal user, not root, as this puts settings in a .sqldeveloper directory off your home directory.) The package installs an icon in the “Programming” or “Development” group. Once you've told it where the JDK is, you can use this to launch it.

Download SQL Developer 1.5.1 Debian Package

I found a script over on the Lunarpages Forums about using PHP to back up your site. I have taken it, modified it a little, beefed up the documentation a lot, and am now posting it here. You can copy and paste it from below to customize it for your own use.

<?php
/**
 * Generic Backup Script.
 *
 * To configure this script for your purposes, just edit the parameters below.
 * Once you have the parameters set properly, when the script executes, it will
 * create an archive file, gzip it, and e-mail it to the address specified.  It
 * can be executed through cron with the command
 *
 * php -q [name of script]
 *
 * You are free to use this, modify it, copy it, etc.  However, neither DJS
 * Consulting nor Daniel J. Summers assume any responsibility for good or bad
 * things that happen when modifications of this script are run.
 *
 * @author Daniel J. Summers <daniel@djs-consulting.com>
 */

// --- SCRIPT PARAMETERS ---

/*  -- File Name --
	This is the name of the file that you're backing up, and should contain no
	slashes.  For example, if you're backing up a database, this might look
	something like...
$sFilename = "backup-my_database_name-" . date("Y-m-d") . ".sql"; */
$sFilename = "backup-[whatever-it-is]-" . date("Y-m-d") . ".[extension]";

/*  -- E-mail Address --
	This is the e-mail address to which the message will be sent. */
$sEmailAddress = "[your e-mail address]";

/*  -- E-mail Subject --
	This is the subject that will be on the e-mail you receive. */
$sEmailSubject = "[something meaningful]";

/*  -- E-mail Message --
	This is the text of the message that will be sent. */
$sMessage = "Compressed database backup file $sFilename.gz attached.";

/*  -- Backup Command --
	This is the command that does the work.

  A note on the database commands - your setup likely requires a password
	for these commands, and they each allow you to pass a password on the
	command line.  However, this is very insecure, as anyone who runs "ps" can
	see your password!  For MySQL, you can create a ~/.my.cnf file - it is
	detailed at //dev.mysql.com/doc/refman/4.1/en/password-security.html .
	For PostgreSQL, the file is ~/.pgpass, and it is detailed at
	//www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html .  Both of
	these files should be chmod-ded to 600, so that they can only be viewed by
	you, the creator.

  That being said, some common commands are...

  - Backing Up a MySQL Database
$sBackupCommand = "mysqldump -u [user_name] [db_name] > $sFilename";

  - Backing Up a PostgreSQL Database
$sBackupCommand = "pg_dump [db_name] -h localhost -U [user_name] -d -O > $sFilename";

  - Backing Up a set of files (tar and gzip)
$sBackupCommand = "tar cvf $sFilename [directory]

  Whatever command you use, this script appends .gz to the filename after the command is executed.  */
$sBackupCommand = "[a backup command]";

// --- END OF SCRIPT PARAMETERS ---
//
// Edit below at your own risk.  :)

// Do the backup.
$sResult = passthru($sBackupCommand . "; gzip $sFilename");
$sFilename .= ".gz";

// Create the message.
$sMessage = "Compressed database backup file $sFilename attached.";
$sMimeBoundary = "<<<:" . md5(time());
$sData = chunk_split(base64_encode(implode("", file($sFilename))));

$sHeaders = "From: $sEmailAddress\r\n"
		. "MIME-Version: 1.0\r\n"
		. "Content-type: multipart/mixed;\r\n"
		. " boundary=\"$sMimeBoundary\"\r\n";

$sContent = "This is a multi-part message in MIME format.\r\n\r\n"
		. "--$sMimeBoundary\r\n"
		. "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n"
		. "Content-Transfer-Encoding: 7bit\r\n\r\n"
		. $sMessage."\r\n"
		. "--$sMimeBoundary\r\n"
		. "Content-Disposition: attachment;\r\n"
		. "Content-Type: Application/Octet-Stream; name=\"$sFilename\"\r\n"
		. "Content-Transfer-Encoding: base64\r\n\r\n"
		. $sData."\r\n"
		. "--$sMimeBoundary\r\n";

// Send the message.
mail($sEmailAddress, $sEmailSubject, $sContent, $sHeaders);

// Delete the file - we don't need it any more.
unlink($sFilename);