That Coding Guy

Tech Tips and Tales, and Lessons Learned

Data Conversion made Easier with Scala Scripts

By

Scala scripts are often used to quickly test Scala code or try out new ideas, but they also work well for handling small, one-time tasks or data conversions. For example, I often need to convert data from one format to another such as converting formatted text from one system’s extract file into a differently-formatted database file for another system or for a vendor. Most of these tasks will only ever be done once, and while I could use tools such as awk or Bash scripting or even a small C# or Java program, in a Scala-focused development group, creating these scripts as small Scala scripts lets us remain focused on just one language rather than requiring a multitude of languages, and keeping it as a script lets me rapidly build and tweak the script until it is ready.
 
For a simple example, assume you need to populate a mapping table to map names of famous artwork from a vendor’s system to a two different internal systems, one for the catalog database and the other for the analytics database. The painting name in the source file may or may not include a trailing space; if it does, you need to append a “#” to the name for the first system and a “$” for the second. Your task is to convert the source text file from the vendor’s PostgreSQL database that contains names of the artists and paintings into a SQL script that will insert the mappings, modified as necessary, into a SQL Server database. (This was actually a real, very typical, example…) A simple Scala script makes this task trivial.
 
 
Extract of Source File – Note the trailing spaces on line 2 and 3:
 
'Michelangelo_-_Creation_of_Adam'
'Edgar Degas - La famille Bellelli '
'  Claude Monet, Impression, soleil levant '
'Renoir:7505c319-a5ee-4285-a045-f783d6f7e975'
 
 
Scala script:
 
// run as a Scala script, as follows:
//       cat artwork_list.txt | scala CreateMappingInserts.scala > artwork_mapping_inserts.sql

io.Source.stdin.getLines.foreach {
  line => 
    val artworkId = line.trim.replace("'", "")
    val artworkIdForCatalog = (if (artworkId.endsWith(" ")) artworkId + "#" else artworkId)
    val artworkIdForAnalytics = (if (artworkId.endsWith(" ")) artworkId + "$" else artworkId)
    println("INSERT INTO artwork_mapping_table " + 
        "(artwork_id, artwork_id_catalog_db, artwork_id_analytics_db) " + 
        "VALUES ('" + artworkId + "', '" + artworkIdForCatalog + "', '" + artworkIdForAnalytics + "')\nGO")
}
 
 
Generated SQL Script:
 
INSERT INTO artwork_mapping_table (artwork_id, artwork_id_catalog_db, artwork_id_analytics_db)
VALUES ('Michelangelo_-_Creation_of_Adam', 'Michelangelo_-_Creation_of_Adam', 'Michelangelo_-_Creation_of_Adam')
GO
INSERT INTO artwork_mapping_table (artwork_id, artwork_id_catalog_db, artwork_id_analytics_db)
VALUES ('Edgar Degas - La famille Bellelli ', 'Edgar Degas - La famille Bellelli #', 'Edgar Degas - La famille Bellelli $')
GO
INSERT INTO artwork_mapping_table (artwork_id, artwork_id_catalog_db, artwork_id_analytics_db)
VALUES ('  Claude Monet, Impression, soleil levant ', '  Claude Monet, Impression, soleil levant #', '  Claude Monet, Impression, soleil levant $')
GO
INSERT INTO artwork_mapping_table (artwork_id, artwork_id_catalog_db, artwork_id_analytics_db)
VALUES ('Renoir:7505c319-a5ee-4285-a045-f783d6f7e975', 'Renoir:7505c319-a5ee-4285-a045-f783d6f7e975', 'Renoir:7505c319-a5ee-4285-a045-f783d6f7e975')
GO
 
Although this example is trivial, the concept scales equally well into much larger and more complex problems. More importantly, it avoids the need to introduce awk or another language when Scala can do the job quite nicely.
 

Parse.com Cloud Code is like JavaScript Pseudo-Triggers on MongoDB

By

Using Parse.com allows me tremendous simplicity when writing mobile apps: it lets me focus on just writing the mobile code and the database, but lets me use an N-tier deployment model and a remote NoSQL database. However, there are times when I really want to run a tiny piece of code inside the database — the same way I would use stored procedures or triggers in a relational database — or inside the REST server as I would normally do with server-side business logic.

For example, one of my mobile apps (PatchTrader) enables Scouts to more easily trade patches at large Scouting events. In this app, whenever a Scout “checks-in” at a new location, any of their older check-ins should be marked as expired in the remote database. I could have the mobile app save the new check-in to the remote database, then retrieve any old check-ins for that user and reach back to the server to expire each one, but this is a terrible use of mobile bandwidth and battery life. It also very fragile. PatchTrader is used at campsites with bad cell service, so it is very possible that the app will be unable to successfully complete all that work, thus leaving old check-ins still showing as “active” in the database. In an ideal world, I’d want a database trigger to fire on the first update and mark the other rows as expired, or I’d have my “check-in” RESTful service handle this … but I didn’t build a REST server and I’m not on a relational database.

Enter Parse’s “cloud code” — it fills that need nicely, and once again saves me from having to write a full server application when I just need one little bit of server-side code.

Parse’s “cloud code” allows you to run custom JavaScript inside the Parse server rather than in your client app, but still without requiring you to build a real REST server. You can use that cloud code to do things such as providing custom server-side logic, doing more efficient database processing or specialized counts, sending push notifications, and most importantly in my “PatchTrader” case, it provides hooks for code that can be run before or after a database object is saved or deleted in the MongoDB database. Pseudo-triggers on MongoDB!

Admittedly these are not real triggers but instead merely server-side JavaScript that is called by Parse’s underlying Node.js engine, but they feel like database triggers so calling them triggers better fits my mental model. And while one could argue that database triggers are evil because they hide business logic inside the database rather than keeping it visible in the server-side code, my DBA hat would reply that there are valid cases for triggers. Then my developer hat would chime in and explain that in this case, PatchTrader is a free app with a very limited audience that I’m doing in my spare time and it is tracking nothing more serious than map check-ins while trading patches, and my time would be better spent enhancing the app rather than building a REST server for it, so the trade-off is acceptable.

To show a simple cloud code example, this is the “after save” code that gets fired off by the server whenever a new check-in is saved by a PatchTrader user:

Parse.Cloud.afterSave("Checkin", function(request) {

    // Skip the update if we are being updated by server-side code
    // (i.e.: if this afterSave call is being cascaded from a previous afterSave call)
    if (request.master) {
        console.log("Cascading afterSave ignored on ID: " + request.object.id);
        return;
    }

    // mark any other "active" checkins for this user as "inactive"
    query = new Parse.Query("Checkin")
        .equalTo("user", request.user)
        .notEqualTo("objectId", request.object.id)
        .notEqualTo("isActiveCheckin", false);
    query.find( {
        success: function(results) {
            console.log("Updating " + results.length + " items...");
            for (var i = 0; i < results.length; i++) {
                var checkin = results[i];
                console.log("   Updating ID: " + checkin.id);
                checkin.set("isActiveCheckin", false);
            }
            Parse.Object.saveAll(results, {useMasterKey: true});
        },
        error: function(error) {
            console.error("Error setting items to 'inactive'." +
                " Error: " + error.code + " : " + error.message);
        }
    });
});

 

As you can see, it’s very simple and it gets the job done without adding any extra burden to the mobile app or mobile bandwidth, and it prevents bad data from gathering in the database.

It’s very handy, when used appropriately. If you’re a Parse.com developer and you haven’t played with Cloud Code, I urge you to take a look!

How to Restore a Single iOS App from a Backup

By

As an iOS app developer, there are times when you need to restore an old version of an iOS app from a backup. Or perhaps the vendor of your favorite app released a particularly bad or buggy update, and you’d like to go back to the previous version until they fix their app.  This post gives the step-by-step instructions on how to restore the old app version.
 
Update: This technique only works up through iOS 8. As of iOS 9, iTunes no longer backs up the .IPA files to the local computer so for iOS 9 and later, this technique is no longer usable.
 
Preliminary Notes:
  • I am using iTunes 12.2 on Windows with iOS 8.4.1 for the directions below although this should work in iTunes on Mac OS X as well.
  • The directions assume you’ve made a backup onto your laptop or desktop computer. Backups to iCloud won’t work.
  • If successful, this will restore both the old version of the app plus any data that had been backed up for that app.
 
Steps to Restore an Old iOS App Version:
 
  1. In iTunes, TURN OFF automatic syncing of devices.
    • In Windows, this is in “Edit -> Preferences -> Devices”
    • On Macintosh, this is in “iTunes -> Preferences -> Devices”
  2. Do NOT update your apps in iTunes.
  3. On the iPhone, delete the existing app from the phone.
    • If you don’t delete the existing app from your phone before doing the backup below, the backup will overwrite the old app version in your backup with the new version from the phone.
    • If you do accidentally do this, see “How to Import an Older App Version”  below for how to fix this.
  4. Connect your phone to your computer. Wait for it to appear in iTunes.
  5. Select your phone in the iTunes left panel under “Devices” or via the “Device” toolbar button.
  6. Back up the existing apps from your phone to your computer via “Transfer Purchases” in iTunes.
    • This is done either by right-clicking on the “Devices -> Apps” menu for your phone or selecting “File -> Devices -> Transfer purchases from [your phone name]“.
    • On Windows, you may need to press F10 to see the File menu.
  7. In iTunes, click the “Apps” button at the top of the Summary page for your phone.
  8. Scroll down through the list of your apps until you find the app you want to restore. The button next to the app should read “Install”.  Click this button.
  9. Click the “Apply” button down towards the bottom right of the iTunes window.  This will start the normal syncing process, and it will also reinstall the app onto your phone.
    • The status bar at the bottom of the iTunes window might make it look like all your apps will be removed. Don’t panic — they won’t be.
  10. The replacement app — the old version that you just restored — will appear in the first available icon position on the iPhone. If you previously had the app in a folder, you will need to manually put it back into the folder.
  11. If everything worked, cheer wildly.
 
How To Import an Older App Version into iTunes:
 
If you did accidentally overwrite the old app version in your backup with the new version, using iTunes 11.0 or later the following steps will let you replace the new version of an app in your iTunes library with an older version. Once you have “downgraded” the app in your iTunes library, you can resume the “Steps to Restore an Old App Version” process above.
 
  1. Open the recycling bin, or wherever you stored the old IPA files.
  2. Find the IPA file for the old version of the app.
  3. Open iTunes.
  4. Drag the .IPA file into the “Library” section of iTunes.
  5. Click “Replace” when iTunes warns you that you are about to replace a new version of that app with an old version.
  6. Resume the process above.

Parse.com – All the Joys of a RESTful Server without the Coding

By

Many times my mobile apps need a remote or a shared database, but often they need nothing more than only that: the mobile client and the remote database. I could write a new RESTful server for each of those apps but when the app needs only standard CRUD operations, writing a new REST server each time feels like overkill. (And connecting directly to a remote database over the Internet is a horrible evil idea!)

This is where Parse.com shines: it provides REST services, authentication, user management, security, and data storage, letting me focus on writing just the client code and the database design. It lets me deliver an N-tier app but build it as if it were just a simple 2-tier app, leveraging a NoSQL database that “feels” like it is connected directly to my mobile app. (Parse uses MongoDB and Node.js behind the scenes.) Parse also provides very nice SDKs, providing full support for features like async processing in C#/Xamarin (my mobile platform of choice!), JavaScript, and Objective-C among others. Parse also provides much more: the ability to run arbitrary JavaScript code, easy support for push notifications, hosting of static HTML pages, and more – but for simple apps, the basic data storage and REST services are all I need.

Moreover, Parse provides a very generous free tier so I can get started on an app or prototype an app for a client for no cost, then scale it up into the paid tiers when the app grows or traffic spikes.

I highly recommend it!

(Apparently I’m coming fairly late to the “Parse” party – it seems like many others already knew about this gem. Now that I’ve finally tried it, however, I’m kicking myself for not using it earlier so I’m doing my part to get the word out. And no, I have no connection whatsoever with Parse.com other than being a fan.)

Stored Procedure to Display Row Counts for all Tables in any SQL Server Database

By

Using the technique I discussed in this post, the following SQL Server stored procedure can be used to display the number of rows in each table and used/unused space for all tables in a specified database.  Put the stored procedure into whatever database you use for your Utilities database, then run the procedure from anywhere, passing in the name of the database for which to display row counts.

CREATE PROCEDURE p_show_row_counts_and_table_sizes(@dbname VARCHAR(255) = NULL)
AS
BEGIN
DECLARE @sql VARCHAR(MAX)

SET @sql = '
SELECT 
    COALESCE(t.NAME, ''~~GRAND TOTAL:'') AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
       INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
       INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
       INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
       LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE ''dt%'' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    GROUPING SETS
    (
        (t.Name, s.Name, p.Rows),
        ()
    )
ORDER BY 
    t.Name'

IF @dbname IS NOT NULL
BEGIN
    SET @sql = 'USE ' + @dbname + ' ' + @sql
END
EXEC (@sql)
END

 

Note: The contents of the “@sql” SQL Query used inside this stored procedure is from the StackOverflow.com answer by “marc_s” on Oct 25 ’11 (edited Oct 29 ’13) to the question “Get size of all tables in database“. My contribution was simply to turn that query into a stored procedure that could access any database and to add the “Grand Total” values.