<jupyter_start><jupyter_text>Datajoint Quick Introduction The goal of this notebook is to quickly go over some the most commonly used datajoint features in relationship to typical project.  Why use database?For small projects, just writing stuff to a file or something along that manner would be sufficient, however when it comes to collobration between large amount of people or when there is a need to store everything in a single location where it can be access by other people as well as being backed up constantly, a database is the way to go.A database, without going too technical, is just a system that store tables of information and their relationship between each other. There is a whole process that goes into how to design these tables and relationship to be express in the database, but we will go over that later, or at least the basic of it.  Why use datajoint?Datajoint is a useful python libaray that allows anyone who knows basic python to interact with a traditional relational database, which in this case is MYSQL.Without datajoint, to interact with a database, the user must use SQL statements like below which isn't always pretty nor easy to learn without some background knowledge on how database works.Datajoint is meant to bridge this gap and make the knowledge barrier more tolerable for people don't have time to spend half a year studying about database design and etc.<jupyter_code>SELECT column_list
FROM table_1
INNER JOIN table_2 ON join_condition;<jupyter_output><empty_output><jupyter_text>Part 1: Installing DatajointRun:pip3 install datajoint  Part 2: Setting up datajoint login<jupyter_code>import datajoint as dj

# To get your datajoint login, please contact <NAME> on slack, he will be able to create your database credential.
# Datajoint will then use those credentials to login into the database and allow you to use it.

# This is the basic setup of the login information for datajoint. This only needs to be run once per computer.
# In Docker this is done by setting the enviorment variables which datajoint will use (more details on K8 stuff)

dj.config['database.host'] = 'at-database1.ad.bcm.edu' # This the database server, yes you must vpn into Baylor
dj.config['database.username'] = 'kijung' # Provided when you setup an account with Daniel
dj.config['database.password'] = '<PASSWORD>' # Provided when you setup an account with Daniel

dj.config.save_global() # Save the information above on your machine so you don't have to do this again.<jupyter_output><empty_output><jupyter_text>Part 3: dj.Manual Tables<jupyter_code>import datajoint as dj
# There 3 types of tables that are mainly used in datajoint, dj.Manual, dj.Compute, and dj.Lookup.
# We will focus on dj.Manual for this part.

# Every table needs to be created in a schema, which is where all the tables we will create will go under
# By default, you can only create schema with your username as a prefix, i.e. yourusername_exampleschema

# This is how you set the schema
# schema = dj.schema('kijung_datajoint_tutorial')
# schema = dj.schema('kijung_test_II')
schema = dj.schema('kijung-gnn')
# schema = dj.schema('kijung-gnn-02')
# schema = dj.schema('kijung-gnn-03')

# This is requried during table creation, as you will decreate the table class with this schema so datajoint will know
# where to create the table
@schema
class Sample(dj.Manual):
    definition = """
    sample_id : int unsigned
    """
schema.jobs.delete_quick()
# temp = dj.create_virtual_module('kijung_datajoint_tutorial', 'kijung_datajoint_tutorial')
# temp = dj.create_virtual_module('kijung_test_II', 'kijung_test_II')
temp = dj.create_virtual_module('kijung-gnn', 'kijung-gnn')
# temp = dj.create_virtual_module('kijung-gnn-02', 'kijung-gnn-02')
# temp = dj.create_virtual_module('kijung-gnn-03', 'kijung-gnn-03')
temp.Sample().delete_quick()
temp.SampleComputed.delete_quick()
import numpy as np

# Sid = np.array([1201, 1202, 1204, 1205, 1207, 1208, 1210, 1213, 1215, 1217, 1220,
#        1221, 1222, 1223, 1226, 1227, 1229, 1230, 1231, 1235, 1238, 1239,
#        1240, 1241, 1242, 1244, 1247, 1257, 1259, 1261, 1267, 1269, 1270,
#        1272, 1273, 1276, 1282, 1283, 1284, 1285, 1291, 1292, 1294, 1295,
#        1296, 1297, 1299, 1300])

# for i in Sid:
#     Sample.insert1(dict(sample_id=i))

for i in range(1,37):
    Sample.insert1(dict(sample_id=i))
Sample()
schema.jobs
@schema
class SampleComputed(dj.Computed):
    definition = """
    -> Sample
    """
    
    def make(self, key):
        sample_id = key['sample_id']
        
        # Call main function to start computation
        main(sample_id)
        
        self.insert1(key) # Just to mark it as completed
SampleComputed()
StudentScore().populate(reserve_jobs=True, suppress_errors=True)
(schema.jobs & dict(status = 'error')).delete_quick()
# Datajoint tables are created via classes with a definition field like this

# dj.Manual creation

@schema
class Student(dj.Manual):
    definition = """
    student_id : int unsigned
    ---
    first_name : varchar(64)
    last_name : varchar(64)
    """
    
# So a quick run down what all of this syntax means
# Looking at the definiton field, anything values --- is considered to the primary key of the table, or in simpler terms
# the column of values that uniquely identify each row of a table, in this case it is student_id

# For every attribute (column, but in Database desgin we call it attribute) of the table the follwing syntax is a lowercase
# seperated by underscore attirubte name, i.e student_id follow by a colon sepeartor : with a space before and after
# and then the variable type. Datajoint support a couple of variable types, you can look them up here:
# https://github.com/datajoint/wiki/wiki/Data-types
# After creation the table my defining it, you can then access like this:
Student()

# Which of course will be empty since there is nothing in it
# To insert values into the table, you can do them via dicts like this

# Note that the key values of the dict must match the attribute names in the table


# Querying the stduent table again, we should see one value now
Student()
# To restrict by a condition
Student() & 'student_id < 5'
# To restrict by a specific match
Student() & 'student_id = 1'
# or
Student() & dict(student_id = 1)
# fetching a specific value as dict
(Student() & dict(student_id = 1)).fetch1()
# fetching only the primary key of the tuple
(Student() & dict(student_id = 1)).fetch1('KEY')
# If there are buip
# to delete a specific value
(Student() & dict(student_id = 1)).delete_quick()

Student()
# To wipe the table from the DB we run drop_quick()
# Note that this is not recoverabe, same thing for Stduent().delete_quick(), the differnce is that one drop dumps
# the entire table out of the database allowing you to redefine the defintion, and delete just delete the rows only
Student().drop_quick()

# This basically covers the basic of datajoint tables<jupyter_output><empty_output><jupyter_text>Part 4 dj.Computed Tables<jupyter_code>'''
dj.Computed tables are very similar to dj.Manual execpt that instead of you inserting each entry manually, the entry 
will be instead computed from a pre-define function from the user, hence the computed parts

The other advantage of dj.Computed tables is that it can serve as a job queue for multiple instances of your application via
the populate function which is called to start the computation of each row. This is particularly useful in cases with
kubernetes cluster deployment where you can deploy like a 1000 instances of the application and datajoint will handle
in an atomic fashion where each instance is populating 1 unique tuple.

Let begin with creating with recreating the student table
''' 

@schema
class Student(dj.Manual):
    definition = """
    student_id : int unsigned
    ---
    first_name : varchar(64)
    last_name : varchar(64)
    """

for i in range(10):
    dict_to_insert = dict(
    student_id = i,
    first_name = 'Daniel' + str(i),
    last_name = 'Sitonic' + str(i))

    Student.insert1(dict_to_insert)
# Creating the compute table that refernces the student table
@schema
class StudentScore(dj.Computed):
    definition = """
    -> Student
    ---
    test_score : float
    """
    
    def make(self, key):
        '''
        This function is what is called during populate, and is where the user put his or her computation code and details
        on what to store once the computation is done.
        '''
        
        # Key here is a dictionary containing the primary key of Student which is student_id
        # If you want to get the attribute then you will need to use fetch1 like the exmaple I put above
        key['test_score'] = key['student_id'] * 100
        
        self.insert1(key)
    
'''
The -> Student is what we call a forgien key refernce as in it reference the primary key of another table which in this
case is Student. This means for each key in Student, there will be a computation tuple in StudentScore that corresponds
with that tuple. In general the dj.Computed table is like your computation code / result storage table.
'''
# Querying the StudentScore table, which should be empty
StudentScore()
'''
Populating the StudentScore table with reserved_jobs=True

reserve_jobs, basically tells the database that whatever tuple it is working on to reserve it and not let other instances
of populate work on that tuple. This is what allow coredenation among mutiple instances.

In general the work flow of the populate function works like this:
Get a forigen key that has not been reserved or computed already -> send to make function -> make function insert into 
the computed table -> repeat

suppress_errors=True means not to throw an exception if there is a problem and just continue the loop above
'''

StudentScore().populate(reserve_jobs=True, suppress_errors=True)
# Querying the table again, we shall see that there are computed entires now
StudentScore()
# To make get useful data there is a join command that can be done between the Student and StudentScore table
Student * StudentScore

# This will combine both tables where the student_id matches, after this you can use fetch() to fetch all the tuples all
# at once which will be a list of dicts, then dump it in pandas or something to make format it and plot it 
'''
In order to see that status of your currently running jobs when let say you deploy to the cluster. Datajoint records all
reserved jobs and failed jobs along with there error message in schema.jobs

If we query it is now, it will be empty since there is nothing in there, however if there is an error job or 
one of the reserved_job instances was stop arrupptly without SIGTERM signal, then it stay in that table until deleted.

The problem is that until it is deleted no other instances of populate can fetch that key to populate thus you must 
delete it so the database knows it is clear to allow computation of that key again
'''

schema.jobs<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.06588406588406588, 'fraction_numerical': 0.02265902265902266, 'mean_word_length': 4.152367557430849, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 25, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '506438', 'license': 'MIT', 'n_tokens_mistral': 3230, 'n_tokens_neox': 3006, 'n_words': 1504, 'path': 'DatajointQuickIntroduction.ipynb', 'repo_name': 'Hyunmok-Park/GNN_hyunmok', 'size': '10993'}