• Comparing tables within same database
• Comparing tables from different databases
• Comparing tables with different range of data within same/different database
• Comparing table and CSV file
• Comparing unordered CSV file and database

in these steps, the bold and italic sentences are the commands for terminal

1. install brew

   sudo xcodebuild -license
   /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
   

2. install mysql via brew

   brew install mysql
   

3. start mysql

   brew services start mysql
   

4. install rabbitmq 

   brew install rabbitmq
   

5. start rabbitmq

   /usr/local/sbin/rabbitmq-server -detached
   

6. pull the source code

   cd ~
   git clone https://github.com/analyticsMD/datadiff.git
   

7. create virtual environment p2env for qdiff

   cd datadiff
   python -m venv p2env
   

8. activate the virtual environment 

   source p2env/bin/activate
   

9. install dependency for qdiff

   pip install -r requirements.txt
   

10. create database 

    mysql -uroot -p
    CREATE DATABASE qdiff;
    exit;
    

11. init tables in the database

    python manage.py makemigrations
    python manage.py migrate
    

12. start Celery workers

    celery -A qdiff worker -l info --detach
    

13. do a test for sanity check

    python manage.py test
    

14. run the server for demo!!

    python manage.py runserver
    

15. check URL http://127.0.0.1:8000/

After you restart your device, you only need to do the following to launch Qdiff

1. start rabbitmq server

   /usr/local/sbin/rabbitmq-server -detached
   

2. activate virtual environment

   cd ~/datadiff
   source p2env/bin/activate
   

3. start Celery workers

   celery -A qdiff worker -l info --detach
   

4. run the django server

   python manage.py runserver
   

in these steps, the bold and italic sentences are the commands for terminal

1. install brew

   sudo xcodebuild -license
   /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
   

2. install mysql via brew

   brew install mysql
   

3. start mysql

   brew services start mysql
   

4. install rabbitmq 

   brew install rabbitmq
   

5. start rabbitmq

   /usr/local/sbin/rabbitmq-server -detached
   

6. install python36

   brew install python
   

7. pull the source code

   cd ~
   git clone https://github.com/analyticsMD/datadiff.git
   

8. create virtual environment p3env for qdiff

   cd datadiff
   python3 -m venv p3env
   

9. activate the virtual environment 

   source p3env/bin/activate
   

10. install dependency for qdiff

    pip install -r requirements.txt
    

11. create database 

    mysql -uroot -p
    CREATE DATABASE qdiff;
    exit;
    

12. init tables in the database

    python manage.py makemigrations
    python manage.py migrate
    

13. start Celery workers

    celery -A qdiff worker -l info --detach
    

14. do a test for sanity check

    python manage.py test
    

15. run the server for demo!!

    python manage.py runserver
    

16. check URL http://127.0.0.1:8000/

After you restart your device, you only need to do the following to launch Qdiff

1. start rabbitmq server

   /usr/local/sbin/rabbitmq-server -detached
   

2. activate virtual environment

   cd ~/datadiff
   source p3env/bin/activate
   

3. start Celery workers

   celery -A qdiff worker -l info --detach
   

4. run the django server

   python manage.py runserver
   

• Using Django DB cursor to read the data
• Supporting multiple databases and file sources

• Support CSV excel

A wrapper for package tableschema

• It will query setting.settings.SCHEMA_INFER_LIMIT number records and infer the schema from them.
• The setting.settings.SCHEMA_INFER_CONFIDENCE permits the minor occurance of abnomral, default is 1.00.
• The setting.settings.SCHEMA_CSV_MISSING_VALUES and SCHEMA_DATABASE_MISSING_VALUES is used to configurate what should be consider as missing.

1. Variable definitions

   data1, data2: the input data, can be queryset, list, dictionary

   item1, item2: the elements from data1 and data2

   dict1, dict2: the list for saving unmatch records

2. steps

   1. Sort the data1 and data2
   2. Iterate over data1 and data2 at same time, item1 comes from data1 and item2 comes from data2
      1. If the item1 is identical to item2, iterate next item pair
      2. Else if the item1 in dict2, save the all elements in dict2 except item1 as conflicted results, iterate next item1
      3. Else if the item2 in dict1, save the all elements in dict1 except item2 as conflicted results, iterate next item2
      4. Else, the item1 is different from the item2, put item1 in dict1 and item2 in dict2, iterate next item pair

3. Complexity.

   given m,n = len(data1),len(data2)

   Time complexity:

    O(m+n)
   

   Space complexity:

    O(m+n)
   

4. pseudo code in python

   qDiff(data1, data2):
       iter1 = iter(sorted(data1))
       iter2 = iter(sorted(data2))
       temp_dict1 ={}
       temp_dict2 ={}
       item1 = None
       item2 = None
       try:
           while True:
               item1 = next(iter1)
               item2 = next(iter2)
               h1 = hash(item1)
               h2 = hash(item2)
               if h1==h2:
                   item1 = None
                   item2 = None
                   continue
               elif h1 in temp_dict2 or h2 in temp_dict1:
                   if h1 in temp_dict2:
                       temp_dict2.pop(h1)
                       saveToConflictedResult(temp_dict2.values())
                   if h2 in temp_dict1:
                       temp_dict1.pop(h2) 
                       saveToConflictedResult(temp_dict1.values())
               else:
                   temp_dict1[h1]=item1
                   temp_dict2[h2]=item2
               item1 = None
               item2 = None
       except StopIteration as e:
           if not item1:
               saveToConflictedResult(list(iter2))
           else:
               saveToConflictedResult([item1] + list(iter1))

• Providing the comparison result
• GUI for accepting rules for resolving ((Not implemented yet))

• Parsing the input rules and save as rule set for reuse

• Rules:

    Where to write the resolved result
    Left join, right join, inner join, and outer join
    Condition based rule, (E.X. when field1 == 0 and field2 > 3)
  

• Filtering the conflicted results basing on the input rules

1. Task

   • Information about the data source
   • Uploaded file path
   • Database information (This will not contain password)
   • Datetime, Recording the start time and end time for performance evaluation
   • Owner (Not implemented yet)

2. Conflict record

   • Raw data
   • What source it belongs to
   • The name of raw table

3. Raw Table

   • Fields here are dynamics, this table schema is depending on the schema of given datasources

4. Report

   • Which generator will process the data
   • Generated file path
   • Parameters for the generator, in JSON format

5. Rule set (Not implemented yet)

   • Name
   • Description
   • Rule, formatted rules in json format