Masterguide Intro

Masterguide DB Version, Tables, & Permissions

Welcome to PostgreSQL version 9.3. Among other advanced features, this database allows for native JSON querying.

Read more about the database here: http://www.postgresql.org/about/news/1481/

Tables

04/30/2014 - Initial Release


Series Tables, used to calculate the dashboard figures, and will allow us to eventually navigate to and from months in the Masterguide UI

  • business_series
  • group_series
  • location_series
  • report_series
  • shift_series
  • tour_series
  • user_series


Data Tables, extracted from Inteliguide/Patrolguide production data, transformed into a friendly state, and loaded into Masterguide

  • franchises
  • businesses (also contains Franchises because they can serve as Businesses)
  • dedicatedworkorders
  • patrolworkorders
  • locations
  • groups
  • group_locations (lookup table for Group Location members)
  • roles
  • shifts
  • time_punches
  • tours
  • users

You can open the tables to reveal their data and design. Foreign keys for joining tables are self-evident and named appropriately.

For example: 'franchise_number' joins on the 'number' column in the franchises table. You'll see that a lot.


Permissions

  • Masterguide Tables (all except schema_migrations) are Read Only
  • Cannot Create Users or Grant Rights
  • Can create tables, including temp tables
  • Can create views and functions
  • Cannot create indexes

If you need new indexes created, or new users created, please submit requests to us here through Zendesk.


Query Browser

We recommend using Navicat Essentials for PostgreSQL.

http://www.navicat.com/products/navicat-for-postgresql

You can also use PostgreSQL Admin for free.

http://www.pgadmin.org/


Navicat Screenshot Example (in Mac version)


Navicat Query Options Example (in Mac version)

Build complex queries and save your work for later revision and reuse.


Navicat Export Options Example (in Mac version)

Export whatever dataset you're looking at with the click of a button.


Basic Examples using Navicat

All Active User Roles by Franchise

Joins between the 'users' and 'roles' and 'franchises' tables because any one user can have one to many roles, but only one role per franchise. This will mean that the same users will appear multiple times in the results set if they're authorized for more than one franchise market.


Just gimme all active users and their given roles per franchise:

SELECT u.first, u.last, r.role, f.number, f.name 
FROM users as u
INNER JOIN roles as r on u.id = r.user_id
INNER JOIN franchises as f on f.number = r.franchise_number
WHERE r.archived is null
ORDER BY f.number, u.last DESC --not specifying the direction defaults to 'ASC'

Returns 7125 records at the time of this example.


Ok, now let me see the total roles between active users and archived users:

SELECT 'active' as status, COUNT(*) 
FROM users as u
INNER JOIN roles as r on u.id = r.user_id
INNER JOIN franchises as f on f.number = r.franchise_number
WHERE r.archived is null
UNION
SELECT 'inactive' as status, COUNT(*) 
FROM users as u
INNER JOIN roles as r on u.id = r.user_id
INNER JOIN franchises as f on f.number = r.franchise_number
WHERE r.archived is not null

Returns 2 results displaying the active and inactive totals


Same thing a bit fancier written in one statement:

SELECT CASE WHEN r.archived is null THEN 'active' ELSE 'inactive' END as result, COUNT(r.id)
FROM users as u
INNER JOIN roles as r on u.id = r.user_id
INNER JOIN franchises as f on f.number = r.franchise_number
GROUP BY result

Returns 2 results displaying the active and inactive totals


Getting adventurous, let me see the totals as percentages:

SELECT COUNT(r.id) as total_authorized_roles, ROUND(SUM(CASE WHEN r.archived is not null THEN 0 ELSE 1 END)*100.00/COUNT(r.id), 2) as active_percentage,
ROUND(SUM(CASE WHEN r.archived is null THEN 0 ELSE 1 END)*100.00/COUNT(r.id), 2) as inactive_percentage
FROM users as u
INNER JOIN roles as r on u.id = r.user_id
INNER JOIN franchises as f on f.number = r.franchise_number

Returns 1 result showing the total amount of authorizations (roles) breaking out the percentage of active and inactive. This is one way of accomplishing it.


Feel free to run any of these examples to see the output and/or tinker with the variables. Just copy/paste the queries into your query browser of choice after you establish a connection.


Data Export Options using Navicat (an example)

Let's just dump all the data we want on any tour for any franchise ending in 4/29/2014 UTC. Here's the basic query to get at the data:

SELECT * FROM tours WHERE ended BETWEEN '2014-04-29 00:00:00' AND '2014-04-30 00:00:00'

Then we can easily use the Navicat export option to choose what type of file we want, step through the options, and save the results set to whatever local resource we want.


A Word About Dates/Times

All Masterguide Dates/Times are in UTC. This is because there needs to be a unified and standard way of storing all dates in the system across all timezones. Using SQL Date/Time functions, you can easily convert whatever Date/Timestamp you want to whatever local offset you desire. We recommend http://www.worldtimebuddy.com/ to get the offset from GMT that you need (GMT and UTC are synonymous for our purposes).

By way of example, let's modify the above tours export example and narrow our results to just those records that ended before midnight in CDT (considering we're in daylight savings time) on the 30th of April, 2014… for the Omaha market:

Since we know CDT is - 5 from UTC, we want to subtract that amount of hours from the 'ended' column in the 'tours' table before making the comparison… We also want to narrow the results to the Omaha market, which is 'franchise_number' 0.

SELECT * FROM tours WHERE (ended - INTERVAL '5 hours') BETWEEN '2014-04-29 00:00:00' AND '2014-04-30 00:00:00'
AND franchise_number = 0 ORDER BY ended DESC


You can see that by adjusting the ended value before the comparison, we achieve the desired result. Sure enough, as in the example below, we see that the last tour was Completed a mere 35 seconds before midnight on the 30th when adjusted for CDT.


Documentation & Google is your friend!

You can learn all about any type of manipulation, operators, and functions, from your most basic to more advanced features, in the PostgreSQL 9.3.4 Documentation. Google is also a great resource if you're looking to do something fancy or complicated. There are great communities like https://stackoverflow.com out there that will help answer questions, and many similar questions to most things that you'd want to do with the data already exist for your instant edification.

Here's a link to the PostgreSQL 9.3.4 Documentation: http://www.postgresql.org/docs/9.3/static/index.html

And, as always, we're here to help. Start a conversation in the comments below, or submit a ticket!




Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk