Dgraph - Schema for University PhD Students and Research Projects

Aim

Create Dgraph schema for managing university PhD students and their research projects. Click here to view complete problem statement.

Graph

Schema

mutation{
  schema{
    type: string @index .
    
    ssn: string @index .
    name: string @index .
    dob: date @index .
    rank: int @index .
    researchSpeciality: string @index .
    
    projectNumber: string @index .
    startDate: date @index .
    endDate: date @index .
    budget: float @index .
    
    degreeProgram: string @index .
    
    deptNumber: string @index .
    officeAddress: string .
    
    startTime: date @index .
    endTime: date @index .
    
    principal_investigator: uid @reverse .
    co_investigator: uid @reverse .
    supervisor: uid @reverse .
    sponsor: uid @reverse .
    research_assistant: uid @reverse .
    
    student_advisor: uid @reverse .
    major_department: uid @reverse .
    
    chairman: uid @reverse .
    
    has_department: uid @reverse .
    has_professor: uid @reverse .
  }
}

*** END ***

Dgraph - Schema for Music Company

Aim

Create a dgraph schema for a Music Company. Click here to view complete problem statement. 

Graph

Schema

mutation{
  schema{
    type: string @index .
    
    name: string @index .
    ssn: string .
    
    title: string @index .
    copyrightDate: date @index .
    format: string @index .
    albumIdentifier: string @index .
    
    addressLine: string .
    city: string @index .
    telephoneNumber: string .
    
    musicalKey: string .
    
    recorded_at: uid @reverse .
    produced_by: uid @reverse .
    
    belongs_to: uid @reverse .
    uses: uid @reverse .
    writer: uid @reverse .
    performer: uid @reverse .
    
    plays: uid @reverse .
    stays: uid @reverse .
  }
}

*** END ***

Python Snippets

Integer Division with Ceil:

Description: Without using math module, getting the ceil functionality. The condition when evaluated to True, gets converted into 1 (in terms of integer) and added to result. When it is evaluated to False, gets converted to 0. Click here for reference thread.

def num_buses(n):
    """ (int) -> int

    Precondition: n >= 0

    Return the minimum number of buses required to transport n people.
    Each bus can hold 50 people.

    >>> num_buses(75)
    2
    """
    assert n>0
    return (n//50)+(n%50>0)

Swapping Array Portions:

Description: Rather than using any loop, make use of array slicing.

def swap_k(L, k):
    """ (list, int) -> NoneType

    Precondtion: 0 <= k <= len(L) // 2

    Swap the first k items of L with the last k items of L.

    >>> nums = [1, 2, 3, 4, 5, 6]
    >>> swap_k(nums, 2)
    >>> nums
    [5, 6, 3, 4, 1, 2]
    """
    assert 0 <= k <= len(L) // 2
    start_block = L[:k]
    end_block = L[-k:]
    L[:k] = end_block
    L[-k:] = start_block

Liquibase - Introduction

What is Liquibase

<<TODO>>

Why Liquibase

<<TODO>>

Liquibase Setup

1] To experiment with liquibase, you need a db instance running. Configure it using AWS - (click here for reference link)

2] Download Liquibase and add to PATH 

export PATH=$PATH:/path/to/extracted/liquibase/dir

3] Create a directory to contain liquibase scripts.

Add liquibase.properties file.

driver=org.postgresql.Driver
classpath=/home/sibtain/work/liquibase/postgresql-9.4.1212.jre6.jar 
url=jdbc:postgresql://lqbase.cfm21jixd6ei.us-east-1.rds.amazonaws.com:5432/my_db
username=postgres_user
password=postgres_pswd
# default changelog to use, relative to classpath
changeLogFile=release_1.changelog.xml

Add release_1.changelog.xml

<databasechangelog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xsi:schemalocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

 <changeset author="lqbase_sibtain" id="1">
        <createtable tablename="subject">
            <column name="id" type="int">
                <constraints nullable="false" primarykey="true">
            </constraints></column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false">
            </constraints></column>
            <column defaultvalueboolean="true" name="active" type="boolean">
        </column></createtable>
    </changeset>

</databasechangelog>

In case you have not created aws db instance, then you may need to download postgresql driver from here.

4] Execute Command

$ liquibase migrate

5] Check your database to find subject table created.

                List of relations
 Schema |         Name          | Type  |  Owner 
--------+-----------------------+-------+---------
 public | databasechangelog     | table | sibtain
 public | databasechangeloglock | table | sibtain
 public | subject               | table | sibtain

Examples

Create Employee table and add few records to it.

main.changelog.xml

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <include file="create_table_employee.changelog.xml" 
        relativeToChangelogFile="true"/>
    <include file="insert_data_employee.changelog.xml" 
        relativeToChangelogFile="true"/>
</databaseChangeLog>

create_table_employee.changelog.xml

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <changeSet id="create_employee_table" author="sibtain">
        <createTable tableName="Employee">
            <column name="EmployeeId" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="FirstName" type="VARCHAR(50)">
                <constraints nullable="true"/>
            </column>
            <column name="LastName" type="varchar(50)">
                <constraints nullable="true"/>
            </column>
            <column name="Phone" type="varchar(50)">
                <constraints nullable="true"/>
            </column>
            <column name="JoiningDate" type="Date">
                <constraints nullable="true"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet id="add_pk_for_employee" author="sibtain">
    <addPrimaryKey columnNames="EmployeeId"
                  constraintName="pk_employee"
                  tableName="Employee" />
    </changeSet>
</databaseChangeLog>

insert_data_employee.changelog.xml

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">


<changeSet id="add_employee_data" author="sibtain">
    <insert tableName="Employee">
        <column name="EmployeeId" type="BIGINT" valueNumeric="101"/>
        <column name="FirstName">John</column>
        <column name="LastName">Legault</column>
        <column name="Phone">+91 12398283</column>
        <column name="JoiningDate" valueDate="2014-03-10"/>
    </insert>
    <insert tableName="Employee">
        <column name="EmployeeId" type="BIGINT" valueNumeric="102"/>
        <column name="FirstName">Pete</column>
        <column name="LastName">Coyle</column>
        <column name="Phone">+44 12398283</column>
        <column name="JoiningDate" valueDate="2015-12-21"/>
    </insert>
    <insert tableName="Employee">
        <column name="EmployeeId" type="BIGINT" valueNumeric="103"/>
        <column name="FirstName">Brian</column>
        <column name="LastName">Apostle</column>
        <column name="Phone">+20 12398283</column>
        <column name="JoiningDate" valueDate="2016-09-20"/>
    </insert>
    <rollback>
        <sql>
            delete from Employee
            where EmployeeId in (101, 102, 103)
        </sql>
    </rollback>
</changeSet>
</databaseChangeLog>

After doing liquibase migrate, perform select to view results in database.

my_db=> SELECT * FROM "Employee";
 EmployeeId | FirstName | LastName |    Phone     | JoiningDate
------------+-----------+----------+--------------+-------------
        101 | John      | Legault  | +91 12398283 | 2014-03-10
        102 | Pete      | Coyle    | +44 12398283 | 2015-12-21
        103 | Brian     | Apostle  | +20 12398283 | 2016-09-20
(3 rows)