Dear students, this is lecture number 38 of the database management system course. The course code is CS403. In the previous lecture, we were discussing about the indexes. What is the purpose of indexes? The purpose of indexes is efficient retrieval of data from the database because otherwise we will have to access the data sequentially. Because we know that the data is stored in the order of the primary key generally. And when we need to access the data on the basis of value of an attribute other than the key, in that situation if we do not have the indexes then we will have to access the data sequentially and that will take time and that would create inefficiency. So index files or the indexes are basically used to provide the efficient access of the data. Or the indexes that you define on a specific table through the create index command or as a result an index file is created. At the end of the previous lecture, we were discussing different implementation techniques for the index files. So we will start our today's discussion from this point. So let's start today's lecture. One of the approach of implementing the index files or indexes is the inverted file. The attribute on which we want direct access inverted file is created on that field. File is said to be inverted on that field. More than one inversions or inverted files can be created even on all attributes. So what is an inverted file? What does it contain? First of all, you should know that inverted file is basically a file that is used to implement an index and you can say that inverted file is sort of an index file. But what is the objective to create an index on any attribute? What is it? And how does it work?  And if you look at it, you will see the different records in front of you. Note that the records are stored on the basis of the primary key values. And you can see this in that state. Let's say we want to create an index on the basis of name. So if this index is created through the inverted file or through an inversion, then what will happen? Look, inversion on student name. There is a format or data in front of you that will be in an inverted file. If you look at it, you can see two columns. In the left column, you have got the names of all the records, the value of the name attribute from all the records in the table. At this time, we have an example table. There are eight records. If there are a thousand, ten thousand, or a million records more than this, this is your inverted file. If the index is created through the inverted file, then you create an index on the basis of the attribute. For example, here we are creating on the student name. So the value of that attribute from all the records, it will be stored in the order. Whether it is ascending or descending, it depends on what option you have given. While creating the index file. If you call it ascending, then the names will be stored in the ascending order. If you call it descending, then these names will be stored in the descending order. After that, look at the second column. It contains the relative record number for this particular value of the name. The student name attribute in the table. If you look at the first column, you see that all the names are in the alphabetical order and in the ascending order. For example, Amjad, Amjad, Arif, Zia, Muhammad Ali, Sadia, all of them. After that, the record in which you have this value of the student name attribute, the value which is listed here in the order, the relative record number of that record. The relative record number means that from where the file is starting, if you call it the first record, then the second, etc. So the record number that is stored in the second column is stored like this. For example, look at Amjad, we have two. By entering the record in this order, both Amjad and Amjad have become the first record. It is not necessary that it is the first, but the agreement has become like this. So in this, you are told that the first record is Amjad and it is in the first position. After that, the second record, which is the basis of the name, is also Amjad from the agreement. It is the second position. Again, this is just by chance. Then the next record on the basis of the name is Arif Zia. See, the actual position of the record having the value in the student name attribute is fourth one. But if you look at it in terms of the name, the position of the name which becomes the order that is on the third place because after Amjad, it becomes Arif Zia in the alphabetical order. So it is written that the record is on the fourth place in the table. Similarly, when we see the names in the alphabetical order, then it comes as Muhammad Ali. The position of this file in this table is sixth. Likewise, the record of Zia Zia in the original table is seventh, then it comes as Suhail Dar. The record of the Suhail Dar is at position five in the table. Similarly, the last record, because we have the same record, is of Tahira Ijaz. And the location of the record having this value in the student name attribute is three. Now see, if you pay attention to this, that the thing you have stored in the inverted file is only the value of that attribute on which you have created the index. And along with that value, the relative record number of that record which is in the position of the file is written. The advantage of this has been understood immediately that rather than duplicating the entire file, there could be 10, 12, 25, 30, 40, 50 attributes in the file. So, we are not duplicating the entire data. As we discussed in the last lecture, we are not duplicating the entire data. Rather, we are just storing the value of the attribute on which we are creating the index. So, only its value is stored. And along with that, as I told you, the value of that attribute is the number of that record which is formed with it. Now, you can see that the storage was efficient that you have only stored one attribute. The second thing is that you have to do the first thing if you want to access the basis of the name. First, you said that the select star from the student where st name is equal to Sadia. Now, the record is stored on the table and the record is stored on the student ID base. The command that you have given, it wants to restrict, it wants to select certain rows based on the value of student name. Now, since you have a student ID, the only option in the absence of index file is to search all records sequentially. Because Sadia's record can be stored on the student ID base and it can be stored on the student ID base. You can never know. So, the only option in the absence of index file is to search all the records sequentially. But now you have got the index file. So, rather than going directly to the table, to the data, you will first consult the index file. You will find the name of Sadia there.  But the thing is that from the index file or in that particular entry, it can be very, very efficient. For example, as we have an inverted file here, there are many approaches that you can have to make it efficient. For example, one option can be that the name, the first letter letter from Sadia or S, you can apply hashing on it. So, you can go directly to the place where the entry from S to S can start. The second option is that we also have a binary search. You can use it. Even if you want to do a sequential, let's say worst come worst, if we want to do a sequential too, then you have this, because the record size is small, the sequential search will also be very efficient in this. Although the sequential search will not be there. Even then, if you want to do a sequential too, in the worst case, even then, the sequential search will be more efficient in the table. There is another very important benefit, very important advantage of this index file that let's say you are trying to find out Sadia, record of Sadia. And you have, because in the index file, you have contained all the names in the sorted order. Now, you were first searching for it. Let's say sequential. And after Sadia, you reached to S. And you encountered the name Semal, which is spelling S-W-E-M-A-L. Now Sadia spells S-A-D-I-A, and Semal is S-W-E. Now S-A is smaller, and S-E is larger, bigger. So as soon as you read S-E, or you didn't find S-A, now it is confirmed that there is no record of Sadia in the database table. Why? Because if it was, then the record of Sadia should have been before Semal, because it should have been before that in its alphabetical order. This means that the advantage of index files is that if you want to find a record, then the trace of it, its entry in the index file, should be in a specific location, in a specific order. If it is not there, then it is not here. For example, if you have records, let's say 1 lakh records, and you have the first record, that it is Amjad. First, you try to find a record named Amir, which is A-A-M-I-R, Amir. So Amir is A-A-M-I-R, and Amjad is A-M. The record is A-M, and you are looking for Amir there. So whatever you reach on Am, you will know that Am is Amir, and Amir did not get it. This means that there is no record of Amir in your table. So the problem of sequential search, where there is a possibility, why do you look at the full table in sequential search? Because what happens is that whatever your desired condition is, the record of that record can be anywhere in the entire relation. In an index file, when you sort it on your name, only the names, then after that, when you cross a name, and you get that name, then it is confirmed that you will not get it in the table. Another thing to keep in mind is that you can see the advantage, that fine, this is the advantage. But there is definitely some overhead involved as well. What is that? The first thing is that storage-wise, this is your inverted file. This is a separate file. So this will take your place, apart from the data that you are storing in the database table. Apart from that, your index file is also stored. The first thing will be this. The second thing is that now the maintenance of the index file is also involved. That means that you add your record in your table, in your relation. So the entry that has to appear in the index file at the appropriate place. For example, if you enter the Rehana record and the Rehana record becomes your last record based on your ID, then you will also write the Rehana record in the table. But at the same time, you will make an entry in the index file with the name Rehana and also place its relative position in the table. Because this is the last record, so in this case, it will become a bloodstain. So this will make your mind that nothing comes for free. If you are giving an index file as a facility, along with it, it is introducing some overhead. Now here is another example of an inverted file, that is inversion on the student address. On this, the first record we have in inverted file is of NULL. Because this is the smallest value considered by this reference. So NULL is on the 7th position because we have not entered the address in the 7th record. So it came first on the number. After that, because in alphabetical order, the digits come first from the alphabet. So our first record was A8ST Lahore. Now this record is placed at the one position, the relative position of it. Then after that, A j road, you see the alphabetical order is placed at the same position. And like that. Now you can see that if you have created an inversion, an index file through an inverted file, then the more attributes you have, you will create the indexes. If the indexes are being implemented through inverted files, then the inverted files they will be created for every index. So what is involved in this, you should understand that in this case, let's say you have created two indexes on two different attributes. One of them is the primary key base which you defined as primary key. So that was created automatically by the DBMS. You have created it explicitly. See, whenever you make any change in the records, you change someone's data. First of all, if you change the address of a student, then its address will change in the table. But with that new address, the position of this record is relative. With reference to the index file, it will be adjusted. And the previous entry will be removed or replaced in a new place. So what I talked to you about having indexes is fine. They provide efficient access. But you also have to keep in mind the overhead which is involved. Inverted files contains the values of the attribute the sorted order and the relative address of the record fine. DBMS searches appropriate record number and hands over to the access method to retrieve that desired record. See, here we have referenced one thing as the access method. As far as the access method is concerned, what we mean by that is our file organization which we have studied in which we have studied the sequential file, the index sequential file and the hashed file organization. In this, what is your index file that is being basically maintained by the DBMS database management system and your access method as your file organization that is jointly managed by the operating system and the DBMS. So in this, what we have said is that the one who will refer your index file will do the DBMS himself. And in that, the one who was looking for your record that was your index file let's take an inverted file from that DBMS and searches and I confirm that it is not accessed sequentially. There are very efficient methods like you gave an example that can be hashed that can be binary searched and there are other ways through which even in the index file your DBMS finds its required value efficiently. That is not sequential. So sequential in the index file find that value that is its relative record number that would be handed over to the access method or the file organization system that will hand over to you. And then your file organization system will map it with the reference of its arrangement and records that will then access it. Whether it is applying hashing whether it is creating indexes or indexes or whatever it is doing then it is the responsibility of the access method the file organization to go to that particular record and access it and return it. That was all about the inverted files that we have to study and I hope that you have realized that how do they make the access efficient. First you search for the value that you need to find in your index file and then you go to that record directly. So that was how the inverted files work. And I have said to you that you can have multiple inverted files for the same table once. And this possibility is that you have multiple indexes one inverted file is zero and zero is implemented one sort of case and like that this is also possible. Now we are going to discuss the second approach of implementing the index files. And as I told you that we are not going to discuss the link list themselves we are just going to discuss how they are used to implement the index. Link lists are also known as a pointer chain. One thing needs an extra field in the physical record to store the link. Now the idea basically is that as you know in the link list every node points towards the next node. So this idea basically to implement this idea we need a pointer and as in the link list you use your data with an extra attribute to store the link. The same idea is being implemented here that now the physical record whatever you store in the physical record but you will store one field to store the link or pointer chain. So through that you will link the records together. Relative addresses are used and then you will link them together through relative addresses. For example we have the same data we saw on the slide last time here you are seeing at the end of the slide rather the last attribute of this slide is pointer. Now this is a pointer that is being used to store the order of the records on the basis of name value. You need a true name attribute value on that value this is the order of the records which is stored. For example if you see our first record because the first record we have to store it separately. Like in the link list you store the head the first record will store a different attribute. First we said Amjad was our first record. So we have stored it in a different attribute in a different place. Now the first record will reach Now this record contains the address of the next record on the basis of the order of the names. So what will happen that the first record is written as 2 which is the record number 2. When the record number 2 is the record of Amjad what is written in its pointer 4 which means that on the basis of name order wise the next record becomes record number 4. Go to 4 there is 6 there is 7 there is 7 there is 8 and when the record is written as 5 it is written as 3 and 3 is written as 0 0 means this is the last record of this list. In this order that is the last record. This means when you have to search on the basis of name so if you are sequencing then you will follow this chain then you will get the record of the sequence. Again see what is the reason why I am searching on the basis of name so if you follow this chain then whatever you cross you reach on the basis of name now the basis starts with S and you are looking for M A if you reach you don't find M U then you will find the name of the sequence then you will find the search and also I told you that if you feel that we have sequential search involved whether it is in order or sequential then I told you that it can be for example that the first record of any letter or on the basis of it in the tree or save it in the form of inverted file then the first record as we have Muhammad Ali then if this first record starts with M then you can store this in an inverted file that record number 6 is the first record and the other chain can be then this possibility can be so means the drawbacks that you see in the situation it means it is not necessary that you take this option in that situation you take another option because I have told you that during this course you are studying different options for different things now let us take a particular option that is your decision and you analyze your own environment so this is how the pointers are used to implement the index head pointer needs to be stored pointers for multiple fields can be created as we added a pointer field when we created the index on the address we created the inverted file likewise we can create another link list on the address so in this way the two pointers will be added in this way we can include multiple pointers in the physical record or practical implementation of the file in this way we can also implement the concept of circular link list because previously we saw the last record that contains zero or null it means this is the last record of this chain or the name chain is the last record record number 3 we had zero in record number 3 we can also place one this way it becomes a circular link list and you have read in data structures that the benefit of circular link list is that if you are standing at any point then you can further move that you can follow the chain otherwise you will have to start so through the implementation of index you can make it circular and at the same time the other option we are going to discuss is that you can do a double link list double link list is that on every node you have a next pointer and previous you know if you are standing at any point then you can forward and backward so you can implement it yes double link list now we are going to discuss the tree index tree index means we implement index through trees this concept we have discussed to some extent in the indexed talks we talked about a tree but I said that we will discuss these things in detail when we discuss data storage and indexed concepts now this is the time and again we are not going to discuss the trees themselves in depth but these are the typical arrangements of nodes each node stores key values and pointers nodes are connected through links as you saw in the inverted file it is a structure which attributes value and its relative order this condition We have a tree node, which is stored in the tree, and the key value that you want to search for is the key value that you want to search for. Along with this, we have pointers that point to the cores. This is a general idea. When we are going to look at its details, a general idea is that you have a record structure, that the top is your root, and then its branches, its children, and then it is linked to it. And every node has key values, along with the different pointers. So this is a basic idea of tree index. You will see its implementation further. Well, yes, this is common with almost all trees, that there is a special node, the root node. Each node has a parent node, but the root node does not have a parent node. And has one or more children, except the leaf nodes. There is no child node of leaf nodes. Different types of trees exist. There are different types of trees. You have definitely read that. There are binary trees, balanced trees, and different types of trees. The particular type of tree that we are going to use in the implementation of index files is B plus trees. And its characteristics are that the number of pointers in a node is one more than the number of keys. This means that as we have already discussed that there will be some pointers in a node, and some keys will be present in it. So the number of pointers will be one more than the number of keys. If there is so much space in the node that there can be three keys, then there will be four pointers in it. Similarly, if there are ten keys in it, then there will be eleven pointers in it. The number of pointers in a node is called the order of tree. What is the order of this tree? The special thing about it is that the distance of all leaf nodes from the root is the same. This is something that is specific with these B trees or B plus trees. The distance you make in terms of links between the nodes. So all your leaf nodes will be at the same distance from the root node. And this is the special property of these B plus trees. As I have told you that nodes have keys and pointers in them. So the link between these nodes is that the leftmost pointer will point those nodes which have less keys than the first key. And the rightmost will be the one with less keys than the rightmost. And the rightmost will be the one with less keys than the rightmost. Let us look at this example. What are the index entries? The search key value, for example, we said that if there is a key value on the name, we have key value first, we have amjad and comma page id. That is an example of the particular record on the id of that page. So on that page, you search the entries on the leaves and you can access your required record. The example we are going to show you is that the node you have has two keys. When there are two keys, that means there will be three pointers. Now this is an example of the tree index. In this, you can see that the nodes we have, the values are two stored and the pointers are three stored. Another thing is that the leftmost pointer will point to the node that contains the smaller values than the first value. And the second pointer says that the bigger values are equal to this value. And the rightmost pointer will do that the second key value store is equal to this value. So this is a general idea of how a tree is constructed and what its structure is. First of all, you have to search for a value. For example, you want to search a record with the value of 35. If you want to search for 35, then you will always start from the root. You will start from the root. Now you will compare your value with the first value in the root. The first value stored in the root is 40. Now you will see that the required value of 35 is smaller than 35. Now 35 is less than 40. So what you will do is that you will follow the left link. If you follow that link, then you will come to that node where 20 and 33 are stored. Again, you will compare your value with the first 20. If you compare it with 20, then what is the difference? Since your 35 is bigger than 20, then you will not follow the left link. Then you will see that you will compare it with 33. If you compare it with 33, then 35 is also bigger than that. Now you will follow the right link of 33. And you will come to that node where you have got 33 and 37. Now in this node, which is your leaf node, where you can see values with the star, this is basically your leaf node. You suppose that there are practically records here. So where you will search the records sequentially. And it is sure that the leaf nodes in your records are sorted in order. Now you have compared 35 with 33. You have compared it with 37. Now 37 is greater than your required value, that is 35. When you reached a big value and you didn't find the 35, your record record, this means that the record with the 35 value doesn't exist here. Now look at the sequential search ratio. During very few comparisons, you realized or you came to know or you found out that your required record doesn't exist. Let's say you want to find out the record with the value 46. You have compared it with 46. Now 46 is larger than 40. You will follow the right link of the right link since there is no second link. You have followed the right link. You have reached the first value, which is 51. Now your 46 is your required record and it is smaller than 51. Since it is smaller, you will follow the left link of the first link. Now you will come to that leaf node where 40 and 46 are lying. Now you will compare 40 with 46. You will come to the next one. When you reach the particular part of the node where 46 is lying, when you compare, they are the same. So this is your required record. Now look, if you first find out that this is your record, which you can see on the screen right now. If you find out this is your record, then you will find out that 46 is the eighth record. So after finding out the eighth record, you will come to know which is your record. First you have read the 41 node, then the 51 node, and then you have directly reached that point where your record was. So since this is a small scale, which is a small example, there are less values in it. On this small scale, you can see that after a little excess, you reach your desired record. So this is a general structure that you have, how your tree index is. Now I will quickly go through the other two or three slides in which you will have a little idea that when different keys will enter in it, then how will your tree's face change at that time? For example, look, in this slide, we said that we first enter this file. We enter a record in which the value of this key is 25. So what will be the center of the record? What will be the shape of the tree? This will be. Now look at this, you have another node at the bottom of the root, and here you have adjusted 25. Now look at this, you have inserted it on the tree, and this happens. Now a stage has come, if you look at it, now your tree has increased its level. You see that the first level you had, the first tree was only 2. Now here level is 3 because now the root node has gone up a step. This means that the leaf nodes will remain the same distance and your tree will continue to grow on the top side, and this way your tree will continue to be popular and your index will be maintained. Now the question arises that the leaf nodes that we had said that we had placed values and stars there. The question arises that what can be read in the leaf nodes or what can be there? With that you will be able to see more clearly how the index files or tree index are used to search for a required record. What is actually stored in the leaves of the index for key value K? There are three alternatives. The first option is that actual data records with key value K, i.e. the leaf nodes that you have there are practically your records stored there. As we had shown that we have two values that can come to us in a leaf node. These are basically the records in the leaf node. The key value of the leaf node is the store because it is the part of the record. So not only the key value is stored, but the entire record is stored there. If you have reached the value of the leaf node in that particular key value, that means you have reached that record only. But obviously what you see in this is that what you see in this is that when you have a unique value against which you have only one record. The second possibility is that K, i.e. the value of the index attribute on which we have created the index. And along with it is the record ID of matching data record. The record in which this value is of that attribute, its ID. As we had talked about, in relative record number, in inverted files and pointers, it is at this point that your key value store along with a pointer which is pointing to that location where that record is stored, it can be an address and it can be a relative record number as we have used before. The second possibility is that K is the key value and the list of record IDs of matching data records. Especially when we have talked about the list, you must have understood that in the case of the secondary key, it is more suitable or possible because we have only one value in the case of the secondary key, it can have multiple values. So either you have with multiple values the records that you have, you can have multiple entries in the leaf node. And in that case, when you get a record, you will have to search sequentially for how many more records. The second possibility is that you can store all of their IDs. There will be a list of record IDs in which there will be a place like Amjad, where we have two records. So when we store the record IDs, we will have to store the list of record IDs that is Amjad and along with those records' IDs, i.e. 1, 2, that Amjad is also with Amjad and we also have Amjad's record on 2. So all the records which have the same value of that attribute will have IDs. If we look at our alternative 1, then the actual data record is stored along with the key. If this is used, the indexed structure is a file organization for data records. In the same file, or in the sorted file, or in the indexed sequence, it is the same situation that it is your file organization itself. Because your leaf node is storing the key's value of the record too. So the indexed file is also your file organization. Yes, it is obvious that the alternative 1 that you can adopt only for one attribute. Because it is obvious that if you are doing multiple attributes, then basically you are duplicating the data. For example, in the student case, if the student ID is unique, for example, if the national ID card is also stored, then it is unique. So if we apply these tree indexes and store the data directly on the leaf nodes, like the alternative 1 means that we are storing the records at two places. Once, with the student ID's reference, once with the national ID's card's reference, then that would be inefficient. The redundancy is involved in that. So it is obvious that the alternative 1 is used for one index. And normally, as we have discussed again and again, it is only on the primary key that your tree index is stored. And the leaf nodes they contain practically physically the records. Otherwise, if you have to apply tree index, index create and tree index then preferably you will use alternative 2 or alternative 3. This alternative saves pointer lookups, but can be expensive to maintain with insertions and deletions. For example, in the leaf node, you do not have the key value but the entire record is stored. So the advantage of a straight web is that you do not have to record the key value, also the record at the same place. But the negative side is that it can only be one attribute and normality is on the primary key. The second thing is that if your record insertion that you are doing in the database, if it contains a lot of disorders, disorders means that you do not enter the key value in sequence but you are giving the gap and you are giving the values of the gap again. For example, you gave record number 1, then 10, then 15, then 20, then 25, then 30, 32, 33, 34, 48, 49, like this. You gave the gap, now your file is getting popular, your nodes are getting formed and you can have one node that record number 1 and 10. You can record it.  You have to maintain the order. According to the order, it should be the first node, then the disturbance will be created. And due to this file's size will grow and if you increase the disorder, there will be a lot of gaps and you will have to shift the record. You will have to move it and there will be changes to the higher levels. So, keep in mind that if you cannot force it, when there is a red entry, you always enter the data in the order of the key. You can advise them that this is better. But you cannot force it. You should know that if they give a lot of gaps and record again, you should understand as a designer, as a DBA that there is a problem that is not being efficiently accessed. So, you can do one way that when your file is popular, you can create it in a new way with a new series. Now, as many records are available in the file, it will become a little more efficient. But then it will be that in a new structure, in the new file, it will be in order. So, this is your alternative one in which you have stored the key in the leaf nodes. It is efficient that it does not have to follow the pointer, but if that insertion division is not in order, it can be a problem. Alternative two and three are that in the leaf node key value and their pointers. Whether it is a single pointer or a list of pointers. As I said, record ID of matching data record. Three is list of record IDs. Easier to maintain than alternative one. So, when you want to do an insertion division out of order, rather than moving the records, you simply have to shift the key. So, it is easy to manage it. If more than one index is required on a given file, at most one index can use alternative one. Obviously, we have already talked about it. Rest must use alternatives two or three. Obviously, the physical records are on alternative one which is your key. The other indexes will ask two or three questions. If it is unique, it can go for two. But if it is not unique, then obviously it has to be three. Alternative three is more compact than alternative two. But leads to variable size data entries even if search keys are of fixed length. Do you understand that you have a flexibility. You have left the option of storing the list of record IDs. If it is unique, then you have only one element and it can still work. But if you have the secondary key, you have got the multiple entries, multiple records with the same value, then you have to store the list of record IDs. It is flexible but the problem is the size of a node that you cannot fix that has to be variable somehow. The implementation of this because obviously you cannot fix that how many entries are there in a node with a specific value. For example, the list of record IDs can be one record, two, ten, more than this. This means that the entries you have have to have a value and the list of record IDs have to be variable somehow. There are many ways of implementation that we are not going to discuss but the advantage is that it is flexible, it is compact but the overhead and the negative side is that the entries cannot determine the size that has to be variable. Whatever you are searching that is fixed size. Even then your entries can be multiple, they can be variable because you cannot determine the number of entries for a particular value. Even words for large record IDs list the data entry would have to span multiple blocks. When you have many possibilities that you have one country that has multiple values the third alternative is quite difficult to handle but the thing is that if you do not have any other choice then definitely you have to do that. Dear students, in today's lecture we have discussed different implementation of index files and as I mentioned at the start of the lecture the advantage of reading these implementation techniques is that as a database person as a database expert you should not simply use their benefits their advantages compare them with their cost. You should know that if I am going to use these features then its underlying cost is cost in terms of storage cost in terms of processing cost in terms of time what is that? So that you can use your resources and your options so in today's lecture we have studied the inverted files we have studied the link list or the pointer change and we have studied the tree index. We can implement indexes in different ways but these three are your major ways we generally implement indexes and every basic idea the advantages and disadvantages we have discussed. We will conclude this topic and we will start discussions on views in the next lecture I will leave now Allah Hafiz


Hi. This is this. That's me. Now, this is a bit of a sneaky title. Only slightly though. It's advertised as have a build and automate it with FING. But it might as well just be called have a build and automate. We'll definitely be looking at FING, but it's not and some details particularly as it applies to Drupal, but it's not a deep dive into FING. That would be even more boring than a presentation on automated build processes. If that's what you're after, then I'm sorry. There is a really good manual online though. That's what they're there for. I've built a lot of Drupal sites. I'm a Drupal developer in Sydney. I worked for Previous Next. I've been doing Drupal since about 2006 in some way, shape or form. I've kind of gone through the fairly common evolution of working by myself, working on larger projects that need teams and that sort of stuff. Over the years I've become more interested in things like how we scale Drupal teams and some of the problems that come up. Drupal's obviously not designed or doesn't work the same way as software frameworks. There's some sort of problems you run into fairly early as soon as you need to work with someone, which I'm sure many of you are aware of. We do a bit of everything as well. A lot of site building, a lot of module development, front-end stuff. We do with accessibility and cross-brows of stuff, performance. The beauty of working on Drupal projects is often you get to dip your toes in everywhere. And integration problems across that sort of stuff. This is in the DevOps track. I'm interested in this sort of stuff from a developer's perspective. Interested in DevOps as applying agile practices throughout the organization. From dev to delivery. Really focusing on shipping. We'll get into some of that. This is fairly applicable to a wide group. I don't know what sort of people would come to talk like this. Hopefully you have some involvement in actually delivering sites. That could be developing. It could be helping developers ship or testing or whatnot. This is a topic that kind of applies across the board. We'll go through it this way. We'll have a look at some of the problems that emerge as you scale teams up. This is definitely, I'm trying to sell you on build processes in general. Then we'll get into some specifics. We're starting at the beginning. Keep that in mind. Feel free to walk out if you've already got your build automated and your CI working. You can stay for the thing if you want to see that. It may not be that useful for you. If you're curious about build processes and you haven't really done that or you're looking to improve, then hang around. We'll cover some of that motivation and then look at some of the how. Some Drupal examples which involve thing. Then I'll give you some advice that we've kind of gleaned from making a mess of some of our build scripts and fixing them. Also some patterns we picked up from the community. Quick story. There is no one workflow. This is what we're dealing with with the build processes. We're trying to tighten up our workflow to automate it. This is what the DevOps is about. There's definitely no particular golden workflow for Drupal. Drupal is used in all sorts of ways. Scaled all the way up and all the way down. Single person shops to big teams and big hard problems. Even though there's no one workflow and the processes and tools are going to change depending on your situation having a process and having a workflow is still very important. Although I will give a caveat that a lot of this stuff is going to be less useful if you're working by yourself but if you have goals to expand then pay attention. Who here is familiar with this though? The very first bit and it says we are uncovering better ways of developing software by doing it and helping others to do it. Through this work we've come to value blah blah and the first one is individuals and interactions over processes and tools. This is all about tools and processes but it's important to as you're nerding out on this stuff keep your eyes on the prize. All about delivering value and if anyone saw the B-hat talk I think it was in this room that had a very good summary of the motivation. Keeping your eyes on the prize that we're delivering value and not get lost in the weeds of all this automation stuff. Let it serve our purposes. I used to work by myself if anyone else did. Life was easier then didn't really need things like configuration management maybe used version control. I was probably more interested in using version control to deploy which is not a great idea either but that's all fine and then you start to work on projects we need to work with other people and this is where the problems start. You do automation before. This evolution is not in one particular order but these are the sort of things that I kind of noticed as I worked on bigger projects. As you work on teams you start to use things like version control to communicate with people not just to deploy you of course use exportables and that's a source of automation opportunity. This sort of stuff becomes a reality. Different tools so as you work on different projects bigger projects you start to need things like solar and of course server stuff. Memcasty and all sorts of things that smaller projects are less of a problem and not only do you need them in production you need them in different environments and you need to track them and so this is the guts. This is where it starts to really become a problem because doing this stuff just by yourself is not that bad you have a production server, you put solar on that, you need solar on your laptop, what's the problem? The problem is when you have different versions on different laptops and different operating systems and that sort of stuff so is this familiar kind of territory? And then development ramp up of course is probably the other thing which I hear a bit of pain about every now and then and that just gets compounded as we get more and more complex technology stacks. So this is related to it so of course we start scripting the stuff away at various times to set up new sites, we have a script to deploy we have scripts, sync environments, we have scripts, manage our exportables maybe we type it in, maybe we have a script a bit ad hoc perhaps at the beginning but obviously we recognize that these are boring parts that are getting in our way so we often do our best to try to automate them. Then we think well maybe we can I've heard of this CI thing, maybe we can do some of this stuff on the server so we copy scripts to the server and we run them there so now we can maybe even automate it on checking, things like that and maybe some of you have even gotten this far which is you start checking scripts into your source code repository so that you can kind of share them, that's kind of the next stage and this is all sort of heading in one direction, I think you kind of play with this stuff for a while until you start to think maybe I need a build system, it has tools now. So some of the build systems around are things like make, rake is there a jake, lots of aches maven and ant and they build stuff build build build build build so what am I talking about it's kind of a hard thing to summarize like what a build is, it's not a script, what is it it's kind of a job, some people use it to just mean a task any ideas, it's the waste it's the non-development tasks that we do so anything that we have to do to get the code running that's not writing the code so it's, you know, we have a word for this in sort of agile in stuff and it's waste and it's not, you know, it's a necessary evil so it's not a terrible word but it's something that we need to be aware of, we need to always know how much waste we've got so that we can look at it regularly as part of our retrospectives and speed it up, automate it, make it smaller if we can sometimes we can't because it includes things like deploying which I mean you have to do that but it does need to be automated so boring, repetitive, automatable yes, it's a build, that's what it is so just in case you're confused it's sometimes hear it in context of compiling code and the reason is because for compiled systems it's not so much about compiling is not the problem that they're solving with the build tool it's compiling lots of things and then compiling things that take a long time and then managing all that together so dependencies between different things that need to get compiled and orders and then really taking care of the time problem there so we're starting to have these problems even on non-compiled projects so we do repetitive tasks and some of them are dependent on others and we want to shorten the time between making the solution and deploying it so that's what builds are about and a good build should have a few properties it should be shippable again, we're focusing on delivering value so things like auto deployment and continuous delivery this is on the road to that continuous integration this is really the glue that gets us there so if we have a good automated build process that we're maintaining and keeping healthy then the rest of this stuff kind of falls out and we're managing our waste not creating it so while we do automate lots of development stuff focusing on the pipeline to production is a sign of a good build process should be repeatable this is an important one so it should be a tool to control our complexity not add to it so it's a fine line with this stuff it can run out of control but it should be repeatable you should be able to take the same source code out of version control into different environments run the build and get the same output it's a simple goal but often goes haywire it's one of those things you had environment parity problems so you introduced an automated build process and now you've got two problems just a quick word on the environment differences they're inevitable right I mean staging is never going to be exactly like production developments definitely not laptops definitely not but we make them as close as we can we use the build process to try to identify the differences and manage that complexity so it's easier to reason about so when we do have environment differences we know where they are and this is the sort of thing you'll get out of an automated build process testable so just by extension of focusing on shipping to production it's going to be inspectable executable we can run it we can performance test it we can look at the final code and lint it and do all the things we may need to do and of course run all our tests which for us I don't know about you but that's a huge suite of functional testing mainly so these are slow tests and we want to run these before production so it's no use if we can only test it live and last but not least it produces artifacts things you can inspect and things you can deploy so artifacts is everything from test reports to the actual executable product so it takes us to what our builds made of get into the pieces other builds that's right they are sometimes made of other builds so I'm going to use some thing language here you'll hear words like script action stuff like that in build literature but just to keep it simple I'll break things down into the thing components so we've got our tasks we want to run a script we want to execute a shell command we want to copy some files you know just boring stuff that we need to do say we're installing dribble we need to change file permissions or it can even be things it can even be deploy type things set up a database in a development environment but it varies a lot this stuff is obviously going to change depending on your setup but that's the beauty of it so then we group those into targets these are the things that we actually run generally and they'll actually take us to our desired state so there'll be a few different tasks run the script copy the files check the code so that sort of stuff and we've got our environment so this is going to change different sets of external files, different servers and different properties and we'll get briefly into that too about what we call jobs so this is outside the build process generally but this is just running a target in a given environment so we do this with Jenkins we tend to do it using Fing so anyone here using Jenkins at the moment? Anyone using Fing at the moment? You're in the wrong room so Fing is not going to make so who's using Fing locally like in local development like a lot only a couple and using it on the build server only about half half interesting both so Fing for those who don't know is a it's called Fing is not going to make it's a build tool it's basically a clone of a patchy ant which is a Java build tool Java in the sense that it's written in Java it's extended in Java so Fing is to ant as say the hat is to cucumber very similar almost exactly the same and the build scripts XML sort of little DSL thingies so we'll see a few of them it's probably the worst part of Fing is that you're dealing with XML and it's fairly difficult to try to make a XML heavy presentation exciting in any way but there's a pass we can expand it with PHP so this is a big win and this is really why we went with it something like rake there's a lot of great build tools out there but the way we use it we run a lot locally we want to get the same build targets local and on the server so we want our developers to be actually using it to be automating their tasks and they know PHP so it's a simple matter of friction and this is stuff that works best from the ground up so your developers should be able to sort of stuff they would be doing in shell scripts perhaps if they know a bit of that they should be able to do it in this and then put in the version control and share with everyone so that's sort of the goal that's kind of the takeaway of why Fing and not something else but anything everyone's comfortable with is really what you should be using because these build jobs should go into source control and everyone can collaborate on them so no Java runs everywhere obviously you're going to run on your server stack so that's all good so I'll give some summary of just broad overview of the big pieces of Fing it's pretty straightforward it matches up with the pieces of a build so we have projects sorry if that's a bit small better so it's XML very exciting has hierarchy so that's the main feature so you got a lot of nesting going on so the project is the root node that's really all you need to know you give it a name and you stick the file in the root of your project and now you're good to go and you can put all your jobs in there and like we said before we have tasks grouped into targets so the targets go inside the project the tasks are executed in the target easy each target can depend on other targets so here we've got a build target that needs some sort of initialization target this is pretty standard stuff and you can specify default for the whole project so generally you'll find that what you're aiming for is like one good kind of ready to go download the source code maybe install some dependencies with Composer like maybe install Fing with Composer and then run the Fing default build and that should run everywhere so you probably want to keep your CI specific stuff in a different build and you have some development tasks in other builds but there should be anything that you absolutely positively need to get going you put in a default build and you just call it like that and you're done so tasks are a little bit more interesting this is where we start to kind of make our little DSL for our build processes so here's one that someone made for Drush so rather than just going back to shell script again it's a bit easier to manage kind of see where we're parameterizing and kind of injecting different environment setups so if you're not doing this I mean I know it's natural to get started with this and jump straight to just exacting out and running shell stuff but this is really where you want to be because that stuff gets unwieldy very quickly and the way you do all that injection stuff is with properties so we use properties to pass the context so we'll have different contexts when we're running on Jenkins different when we're running on our laptop often the same build tasks so here's a very exciting hello world type one I haven't tried these by the way so if you're typing them in I can't promise they'll work they're about right there so properties can come in from various places it's very robust you can pull them in from command line options directly which is great for CI in all sorts of contexts properties files of course is the main one so we ship with sort of a build properties file often to get people going it's kind of you know most people are on similar setups but if they're a special snowflake on their laptop they can just tweak that and run the same build jobs and you can also get properties in interactively through the command prompt so you can use it as an interactive tool as well so that's FIM it's pretty straight forward any questions on that so far easy, installing is very straight forward we use the composer method now which I think the website still lists the pair one but the composer one is definitely solid so you can even go down to locking in the version for the project I'm a vendering fan so I don't mind that at all and using it's straight forward so tasks can log stuff to the console if you haven't seen a build tool before like a regimented shell script and you can choose when to log stuff to the console or not and then there's obviously fail and success status and it bubbles up so the task can fail makes the target fail, makes the build fail, gets picked up by the CI server and they all talk the same language and there's a easy way to get through that so the output and any artifacts are there at the end and they can be used by the CI server also for reporting so there are two ways to get results there so is everyone doing this sort of stuff how are we integrating FIM in CI land these days, are you just looking at the console output or anything else console output or just pass fail so what are you doing with it so we use both, once we have a failing build we'll have some artifacts to inspect generally so that brings us to what we put in so these are the sort of things that we automate and we take a bit of a different approach so I think it's good to go bottom up with this sort of stuff so something that a developer would do by hand they might want to automate it, they put it in the build eventually it'll kind of sneak in upstream rather than putting all your logic into your Jenkins server that should be fairly dumb but we do a lot of the standard Drupally stuff we want to automate everything that isn't real development work, so I said it before but this is absolutely everything so if you find yourself doing something by hand then stick it in here so we sync databases, we run tests we do some managing of our exportables and front-end tasks and then that default build of course to kind of get people bootstrapped so I'll show you some examples I tried to tease out some of our specifics and kind of give you an idea of the sort of thing that might be generally useful but a lot of this stuff is environment specific so we sync environments with Drush anyone else, environments with Drush hand, dumping the database so you start doing it with Drush and then you get sick of typing Drush SQL sync over and over and over and over again so you make a shell script and then you get sick of that and you start to manage it this way so this is pretty cool so we can run the same sync on the CI because we've parameterized the source and destinations and we're dropping it first so this is often important so we're capturing those little details so that's what a real task looks like it has its own name and its own set of properties etc so they're all defined in PHP and I don't have a slide on that but if you're interested I can pull up a simple enough example, they're quite brain dead the task definitions are quite simple we run our tests so might look something like this again we're not calling out to the shell directly we're kind of capturing what's important so where are our BAHAT tests stored, where's the executable that's like the main one, this is the sort of thing that lets us run it in different environments because that's just another property where do we put our output, same build job different environments and parameterized and even stuff that's really dripply like dealing with features for instance I think I worked out that at one point the only surefire way to revert features cleanly is to clear the case twice list the features, revert the features, clear the case again and then do like a little dance but this works I think so you can see here we're calling out other thing targets so build it up into little pieces and keep it clean this is actually not how our current scripts look but they will soon, some refactoring required okay this is another interesting one actually that one of our developers came up with he got sick, we use sort of a feature branch based workflow so he got sick of going to another branch, checking the branch out, syncing the database for like a peer review kind of setup so what's this branch going to do on the current test instance that everyone's working against so you know there's obviously some repetitive stuff around that so he wrapped it up in a switch branch task and then made a branch target so now whenever he wants to test the branch or any now he checked it into the project now everyone's got the task and he just tells everyone about it all they have to do is type in Fing branch branch name or actually in this version it's interactive so it says what do you want to call the branch X if the branch exists like you're checking someone else's work then it will switch to that branch, sync the database, revert the features all in one go so type one thing and now you're looking at exactly what they were looking at so makes reviews a lot smoother this is the sort of little stuff that productivity improvements that are important boring, boring jobs solved, front end tasks even so this is becoming more common, is anyone here using SAS a lot yeah so you need some way to manage this stuff so probably the interesting one here is that we're using Bundler to lock down the versions of the SAS stuff so this is the complex annoying world we live in for instance if you wanted source maps perfectly required feature for dealing with some of these post processed star sheets you needed bleeding eds version check out of this compass thing and that or it wouldn't work one developer might battle with that and solve it they check it into the source repository in the lock file and then everyone else just keeps using the same task it'll depend on a generally we have a prep and that'll make sure that Bundler install or update I don't remember which it's the opposite to Composer I don't have to remember because really she's and they magically get the right version and everyone's on the same page so seemingly simple stuff that can go annoyingly wrong but you only have to solve it once and then we generally again put this stuff all together in a big default build pretty good there really I'm just going to go quickly over some patterns you've probably noticed some of them before a lot of them are common sense but they're kind of the important kind of takeaways if you want to have a healthy build script so we've got this is a favorite of mine so it's called a working skeleton in this book but it's an approach that I've often taken with this sort of stuff if you deal with things like Drushmake and things that can kind of get a bit complicated you don't do the whole you know to a large project or you don't do the whole thing in one go and we focus on deploying to production so it can be very difficult to retrofit a lot of this stuff and get it working smoothly so the way to do it is obviously start at the beginning if you can and don't just start at the beginning to take that thin slice approach and get it working local on the CI server auto deploying nothing auto deploy an empty site and do that first and then you'll kind of you'll know when it's broken and it'll be easy to maintain so it's a simple tip but I kind of see it done wrong and it's kind of the agile way. Location agnostic is a pretty straightforward one make sure it has access to all the files you need use relative paths that sort of stuff and version the script with the source so don't put the build XML on the Jenkins server put it in the project so that's sort of the way to go and that's you know it's like dependency injection script injection so keep the CI scripts on the CI server so you will have CI configuration or your job configuration and maybe some other stuff that's specific to those environments but don't we don't manage them in the source control that you might manage them however you're managing your server configs for instance but keep the app build scripts with the app one for each project you have a starter one but let it diverge project to project if you work on lots of different projects so it's fine and you know it's got to be something that you're free to improve and add to and that sort of stuff and then we just get into sort of some optimization stuff so you'll once you get into this you'll notice that the build times get bigger and bigger and slower and slower and they pile up so look into breaking out targets this is sort of stuff that we're just getting pain points on now and looking at ways of fixing so that's probably more a continuous integration topic but you can start at the build and kind of get your targets to obviously not be rerunning the same target when you have dependencies and get a bit more of a flow going if there's a slow target like find a way to reuse that you can put some logic in and you can kind of check for results and there's little hacks and tricks you can do to not run the same build over and over again if it's been successful move it forward that's this classic CI pipeline kind of thing that you're kind of going for and it's really about just the build jobs should actually get faster and faster the further down the pipeline you get and you do that by getting jobs that trigger other jobs and so that's called cumulative builds and those were actually taken out of a great book on build processes so it's not a CI talk but the beauty of this sort of stuff is that if you follow this approach of starting at the bottom I actually recommend people don't start with a continuous integration server like right at the beginning or take that working skeleton approach and have it very simple don't try to pile heaps of logic in there and this is a Travis file and that's a CI configuration I mean all the real work is done in the, sorry it's cut off it's done right at the bottom, thing build so that's about I think we've got maybe three minutes, two minutes for questions, sorry if I ran a little long there's some must read books if you haven't seen a red continuous delivery it's like the best book ever and that's the book that I've got the patterns out of so thank you, questions? It is so there's a statement, a comment that Ant is language agnostic for extensions, right, right, right I have a preference, again it was the fact that I can write a custom task totally from scratch with very basic PHP knowledge I mean it's dead simple, I don't need a java compiler or anything so if you're saying that I don't need that for Ant then I would guess that they're exactly the same and I'm confused but as far as I know the config syntax is, I mean I've seen like build configs and they look very similar, I mean it's XML Do you have somewhere work or something like this where you are published something like template for the built XML or something like this I'll probably blog this stuff but if you don't want to wait go to, I think there's something from a long time ago, Drupal project template maybe, but I'll put that in the blog as well, but yeah look if you're curious, I think that works, right, so question is is there an extension to use non-XML build language no, not as far as I know, that's really the problem with thing, but all of this stuff is applicable to any build tool, so pick one you like and run with it, but again I would lean towards things that your developers can actually use to extend and not make it in operations only or only one guy in the organization knows Ruby or whatever but other than that it doesn't matter, so go with what's frictionless I think is the way okay, there are many I guess I kind of brushed over that so standardize execution, so you've got one command to run, that's a big one, you could write your own meta shell script, but that's painful, what else the reporting, so often these build tools kind of use a reporting format or support a reporting format, or not so much build tools they plug into the CI servers straight away, so you don't have to do any extra work so that's a big plus, there's plugins for fiend, maven, ant, rake, etc, for all the CI servers, so it just glues together and easy, but I'd say that the maintenance of shell scripts gets out of hand for me, I mean, writing XML scripts is ugly, so you should try to make them less scripty and more declarative and smaller, and there's kind of a subtle advantage of using such a horrible language like XML, in that it makes you keep them tighter, whereas with shell I find my shell scripts can get out of hand quite quickly, because it's easier to get stuff done in it, so there's lots of reasons but let's have the main ones I really should have mentioned that yeah, that was a big, thank you very much, that was a big oversight on my part, there are hundreds of pre-done tasks for copying files, changing permissions, deploying stuff, dealing with, there's even like a Drupal update system like database migration system what else does it do, everything, I mean you don't have to write too many of these things, but they're easy to write as well, so that's also the big win for Fing and I'm assuming that Ant has a similar library, but Fing has an absolutely wonderful library of hundreds of ready to go tasks that have just work, so I think we're out of time, thank you oh, and two quick things, the survey thing, and the code sprint, code sprint don't forget the code sprint


Hey traders, this is Tosh, I go by T Bradley 90 in the My Investing Club chat, a general reminder for those who do not know, MIC is having a one-year anniversary event where BOW is going to be trading live in front of our members. It's coming up August 17th, mark your calendars. As an added benefit for our members, the event is 100% and exclusively free for annual and lifetime members, while lifetime on top of that get extra coaching before the event and guaranteed front-row seating. While most charged for these events, we show our support by making it again free for annual and lifetime members of MIC. If you are interested in signing up and attending this event, DM T Bradley 90 in MIC Slack Chat and or email myself at Toshitmyinvestingclub.com. Now we have a very special video for you guys as one of our head moderators, Matt, who goes by Chicago trader in chat, who puts together his weekly Sunday video series has graced us with yet another video. This week, he does a weekly review to further analyze the fundamental side of what's been going on while today's video is just a preview of the full link video if you want to watch the full length or any of our exclusive content to become an MIC member. What's going on everyone, Chicago trader checking in for the weekly fundamental video here. Hope you guys are having a nice week as usual. The last few weeks, there's been a couple of names that have been kind of fun to do a deep dive into and unfortunately on the this week, the second week of June here, nothing really that I saw that was quite as intriguing as the last couple of videos with RWK, you know, workforce, those things and whatnot. But just a quick one, I'm going to go over a couple names. The first one, the big moving for the week was NBY Nova Bay. The thing was up from 40 cents and touched almost $5 over a thousand percent gain. Kind of silly on what my opinion, they said that they were going to sell a product on Amazon. It wasn't even a partnership with Amazon. So maybe somebody misread it, I don't know. But kind of looking into this, you know, of course, guys, before I go any further, I've got investment advice on my behalf or in my seat just for entertainment purposes. But so, you know, kind of going back to it, Nova Bay, pharmaceutical company, they're going to sell the product on Amazon. So I thought it was interesting, you know, kind of going through Amazon and looking at it. So they'd actually mentioned that this thing, Abenova, which was the product used to be $300 a bottle. And now if you look at it on Amazon, it's actually going for around $30 to $40. So you can obviously see that, you know, just to even get into agreements. So without the interest, they paid $182,000 of debt on, you know, a couple million dollars. So on a double digit in terms of percentage on the fee, you know, it's unregistered here. When you read this as a conversion price at $165,000, you come back here. So I thought it was interesting, too, is this move, let me zoom in here, on this day, which was June 10th, I thought it was kind of another interesting timing on it, because if you come down here, so you look at these two shelves, you know, unfortunately, they were a day late, right? They expire three years later. So 610 of 19, they definitely had to be the first day they can't use them. You scroll up to here, this S1 actually has no effect. So they can't use it. Maybe they amend it. This is why you can see, so at this point, they did a pipe, I'm not sure how much it is, doesn't really matter. And then, you know, on the first glance, I thought, hey, they got a new shelf. It's got the effect. Well, that's great. They can raise money eventually, right, if they want to. But coming on in, of course, you got to do a little bit more inspection. So they registered about 2.8 million shares here, so you can kind of come through the resale from, you know, it was a Triton funds. So again, it's pursuant to the SPA, the Stock Purchase Agreement.  Hey, traders, this is Tosh. I go by T Bradley 90 in the My Investing Club Chat. Just wanted to reach out and say, if you have any questions about MIC, joining MIC, maybe you're a member already, you have three ways to contact myself personally and through MIC. You can hit our social media. You can hit me through PMs in chat or you can contact us through my email at Tosh at MyInvestingClub.com, that's T-O-S-H at MyInvestingClub.com. I will get back to you in a timely manner and I'm saying this because I'm here to help and I don't want anybody to be afraid to reach out and ask any question that they have. We are here for you guys. All right, see you guys.