• Useful SQL queries for integrating GitLab in Metabase

    Recently I started playing with Metabase to create interesting visualizations and have the teams quickly check how we are working.

    One of the main tools we use is GitLab, so I needed the ability to query it. To be on the safe side, I configured the backup job to create a read-only mirror of the DB, and have Metabase access it.

    However, as soon as I started trying to query it, I found that the table structure used in GitLab was difficult to use for quick visualizations: while the unit our users know is their project, projects reside in a namespace, and namespaces can be nested. Also, since GitLab supports forks, you may end up with several projects that you want to aggregate stats of in order to reflect accurate numbers.

    To solve this, I created a few helper queries using PostgreSQL CTEs1.

    Here’s a dump of the queries I found useful, and some brief explanation of what they do.

    -- flat_namespaces - for a leaf_id, obtain the full name of the namespace where it is
    WITH RECURSIVE flat_namespaces AS (
        SELECT CONCAT(name) AS names, id, parent_id, id AS leaf_id
        FROM namespaces
        WHERE parent_id IS NULL
        SELECT CONCAT(flat_namespaces.names, ' / ', namespaces.name) AS names, namespaces.id, namespaces.parent_id, flat_namespaces.leaf_id
        FROM namespaces
        INNER JOIN flat_namespaces ON namespaces.id = flat_namespaces.parent_id
    -- root_projects - searches for the root project based on a candidate fork id
    , root_projects(root_project_id, fork_project_id) AS (
        ( -- project with no forks
            SELECT id AS root_project_id, id AS fork_project_id
            FROM projects
            WHERE NOT EXISTS (
                SELECT 1
                FROM fork_network_members
                WHERE (forked_from_project_id = projects.id OR project_id = projects.id)
        ( -- project that has forks (parent)
            SELECT DISTINCT forked_from_project_id as root_project_id, forked_from_project_id as fork_project_id
            FROM fork_network_members
        ( -- project that is a fork
            SELECT DISTINCT forked_from_project_id AS root_project_id, project_id AS fork_project_id
            FROM fork_network_members
            WHERE forked_from_project_id IS NOT NULL
    project_with_full_namespace(full_name, project_id) AS (
        SELECT CONCAT(flat_namespaces.names, ' / ', projects.name), root_projects.fork_project_id AS project_id
        FROM root_projects
        INNER JOIN projects ON projects.id = root_projects.root_project_id
        INNER JOIN flat_namespaces ON projects.namespace_id = flat_namespaces.leaf_id
    FROM project_with_full_namespace

    Let’s start from the top:

    flat_namespaces will return a table containing the concatenated names, the parent namespace id, and the leaf namespace id.

    So, if you have a namespace at /a/b/c, where the IDs are 1, 2, and 3 respectively, and you query it with a WHERE clause of leaf_id = 3, you’ll get a/b/c as the names value.

    root_projects will return a pair of root_project_id and fork_project_id. This takes into account the three different possibilities that might exists:

    • Projects with no forks.
    • Projects with forks.
    • Forks of other projects.

    Basically, with a fork_project_id you can get the corresponding root_project_id. If you couple this with flat_namespaces, for any given project ID on your GitLab installation, you can grab the root_project_id with the “full path” of namespaces as a string.

    Finally, project_with_full_namespace uses the previously defined CTEs to create the “full name” of the project and all its parent namespaces, similar to what you see in GitLab’s URLs. It’s important to note that this query will ignore forks, as it uses root_projects to navigate the fork tree, but this is what we needed for our dashboards.

    1. Common Table Structure 

  • HomeKit Accessory Simulator for iOS 10

    If you are developing for HomeKit, you will probably want to use the HomeKit Accessory Simulator.

    To download it, we are instructed to search for “Hardware IO Tools for Xcode”. However, in Xcode 8, this package changed its name to “Additional Tools for Xcode”. If you are looking for the latest supported HomeKit accesory types, search for the latest version.

  • Shopster 1.2 Released

    After reviving it, Shopster 1.2 is now available on the App Store. Go get it for free!

  • Reviving Shopster - In Review

    I’m trying to revive Shopster, an app we launched 2013, and I’m writing througout the process. If this is the first post you see, you can start here.

    After some time, I finally submitted Shopster 1.2 for App Store review. As I write this, it’s currently “In Review”, and I expect it to be available soon.

    I wanted to wrap up this series with a summary of the changes and what I expect to do in the future.

    Final Metrics

    I took some baseline metrics before I started. Here’s the final summary:

    github.com/AlDanial/cloc v 1.70  T=1.10 s (101.4 files/s, 6675.3 lines/s)
    Language                     files          blank        comment           code
    Objective C                     46           1176            494           3638
    C/C++ Header                    53            295            368            477
    Swift                            7            122             65            350
    HTML                             1             19              0            159
    JSON                             2              0              0            140
    Markdown                         1              3              0             33
    XML                              1             11              0             12
    Bourne Shell                     1              2              4              5
    SUM:                           112           1628            931           4814

    Worth noting:

    • I started using Swift. All new classes were written in Swift, and one small refactor (code I moved out from the AppDelegate) was rewritten in Swift as well.
    • There are some shell scripts, JSON files, etc, that were not present before. I integrated fastlane.


    In the process, I also removed some third party dependencies we used. The app is now much tighter and less dependent on third party code that I don’t manage.


    I solved all the deprecation warnings except one: I’m still using ABCreateStringWithAddressDictionary. As far as I can tell, the suggested replacement (CNPostalAddressFormatter) doesn’t quite cover my use case. I’ll review it in the future, and maybe rewrite that method, as it’s not that important for the app.

    UI and AutoLayout

    All the screens now use Auto Layout. Where there were animations that changed the frame, I changed to update the constraints and animate that change instead.

    Short Term Work

    Here’s a short list of things I’d like to tackle in the near future (wihtout counting major architecture changes such as using CloudKit):

    • Start using the default system font (San Francisco) instead of the custom font we are using.
    • Adopt Dynamic Type across the app.
    • Remove some graphics I think make the app look dated, like the logo on the Navigation Bar.
    • Localise the app in Spanish1. It’s currently English only.
    • Fix the missing deprecation warning.
    • Use the new location based notification trigger. Currently, we observe significant location changes, and when they match the areas we want, we trigger a Local Notification immediately. The new UserNotifications framework, allows you to set a location as a notification trigger, which would help eliminate part of this code. Since this is core to the app, I didn’t want to take on it now, as it will require more testing.
    • Add a new onboarding screen. As soon as you start the app, you need to allow location services and notifications. We simply throw the system dialogs to the user. We can do better.
    • Add @3x assets. I didn’t have them, and wanted to ship soon. They’ll come at a later update.
    • Make the app free, and show ads only to new users (the ones who got the app for free, basically).

    Long Term Work

    This list is shorter in items, but way harder, so I don’t know if or when I’ll do it:

    • Take on major refactors for the app. Adopt MVVM.
    • Start using CloudKit for sync.
    • Adopt a freemium model, where a one time payment unlocks some functionality.

    Closing Thoughts

    • Don’t let your apps rotten! Either pull them from the App Store gracefully, or spend the required time to keep them up to date at the very least through major OS versoins.
    • It’s hard giving up on an app you put effort on. I couldn’t, that’s why this version is on review. This is probably not the best example to follow.
    1. My native language! Shameful. 

  • Updating the Frame of a UITableView in iOS 10

    In Shopster, when you edit an element in the main UITableView, we show a “ruler” to allow for selecting the item quantity. When the ruler is shown, we make the table view narrower, so the text is still visible to the user.

    In iOS 10, however, Apple slightly changed the behaviour of UITableView, and the animation started failing as follows:

    In the animation closure, we are simply changing the UITableView’s frame. As you can see on the video, this makes the animation “jump”. The disclosure arrows move before the animation even start.

    The solution, luckily, is simple: just wrap the frame update in a beginUpdates / endUpdates pair as follows:

    // assumes currentFrame is set previously.
    self.tableView.frame = currentFrame

    If you want to play around with the example, here’s an interactive playground you can download to reproduce the issue or see the workaround.

  • 1
  • 2