# ArangoDB hands-on session 2 with example solutions to exercises

The previous hands-on session's model solutions: https://hackmd.io/@valterUo/ByPof7cMO

## Exercises 2

The documentation is useful: https://www.arangodb.com/docs/stable/index.html.

1. Write a query that filters based on SHA1 and SHA256 hashes. Then create a persistent index that is based on both of these attributes. Compare how much faster the query is with or without the index. How does it affect that the index is combined of two attributes? Is there a difference if your FILTER clause contains `AND` or `OR`. (1 point)

    FOR package IN packages 
        FILTER package.SHA1 == "dbb1343a3d24f60e5038994e3528dd7486e40943" 
        AND package.SHA256 == "c1fad54e790d69b83f32f2612963baba3ea8091ff3ca72c960c7312096223e3a" 
        RETURN package
        
    db.packages.ensureIndex({ type: "persistent", fields: [ "SHA1", "SHA256" ], unique: false })

2. Create a full-text index on the attribute `Description`. Query those packages whose descriptions contain 'Python'. Group the result by architecture and return the count of packages for each architecture type. The result can look something like `[{"architecture": x, "number_of_packages": y}, ...]` (2 points)

    db.packages.ensureIndex({ type: "fulltext", fields: [ "Description" ], minLength: 3 })
    
    FOR package IN FULLTEXT(packages, "Description", "Python")
        COLLECT architecture = package.Architecture INTO package_group
        RETURN { "architecture": architecture, "number_of_packages": count(package_group) }

3. Create an index on the attribute `Size`. Query packages whose sizes are between x and y and sort the result to ascending order (the smaller first). (1 point)

    db.packages.ensureIndex({ type: "persistent", fields: [ "Size" ], unique: false })
    
    FOR package IN packages 
        FILTER TO_NUMBER(package.Size) < 1000 
        AND TO_NUMBER(package.Size) > 50
        SORT package.Size ASC
        RETURN package   
    

5. Write a transaction that inserts the following data into `packages` collection if it does not already exist. In the case it exists, throw a proper error. (1 point)

    {
        "_id": "packages/elinks-data",
        "_rev": "_cDljlvG---",
        "_key": "elinks-data",
        "Architecture": "all",
        "Description": "advanced text-mode WWW browser - data files",
        "Filename": "pool/main/e/elinks/elinks-data_0.12~pre6-5_all.deb",
        "Maintainer": "Moritz Muehlenhoff <[e-mail]>",
        "Package": "elinks-data",
        "Priority": "optional",
        "SHA1": "4ec9dbb32065b80ae5bc9d3f6d53edd499caf74a",
        "SHA256": "9dbeaaadb9d69bb1bceb183bfda4f8f87e4d4a2f10dd2e2da95827c1f7c4a55c",
        "Section": "web",
        "Size": "389458",
        "Source": "elinks",
        "Tag": "role::app-data",
        "Version": "0.12~pre6-5",
        "InstalledSize": 1733
    }

It is questionable if it is good to overwrite ArangoDB errors like this. Anyway, it is good to keep in mind that you can throw your own errors at any point. Also, remember that you can use Javascript here (try-catch).

    db._executeTransaction({
      collections: {
        write: "packages"
      },
      action: function () {
        var db = require("@arangodb").db
        let new_data = {
        "_id": "packages/elinks-data",
        "_rev": "_cDljlvG---",
        "_key": "elinks-data",
        "Architecture": "all",
        "Description": "advanced text-mode WWW browser - data files",
        "Filename": "pool/main/e/elinks/elinks-data_0.12~pre6-5_all.deb",
        "Maintainer": "Moritz Muehlenhoff <[e-mail]>",
        "Package": "elinks-data",
        "Priority": "optional",
        "SHA1": "4ec9dbb32065b80ae5bc9d3f6d53edd499caf74a",
        "SHA256": "9dbeaaadb9d69bb1bceb183bfda4f8f87e4d4a2f10dd2e2da95827c1f7c4a55c",
        "Section": "web",
        "Size": "389458",
        "Source": "elinks",
        "Tag": "role::app-data",
        "Version": "0.12~pre6-5",
        "InstalledSize": 1733
        }
        try {
            db.packages.save(new_data)
        } catch(err) {
            if(err.error_num = 1210) {
                throw "doh! Document already exists!"
            } else {
               throw err
            }
        }
      }
    })

7. Query some packages returning their `Installed-Size` attributes using the recommended `package.Installed-Size` notation. What happens? In this case, you can use the notation `package["Installed-Size"]` but of course, the attribute `Installed-Size` is not good. Write a query that updates all `Installed-Size` attributes to `InstalledSize` attributes and removes `Installed-Size` key-value pairs from the documents. Because you want to be sure that no data are lost during the update and the output is correct, perform this update using transactions. Besides update query, write a query or queries that check the correctness of the update. If your update fails for some reason, you can abort the transaction and roll-back. Hint: You benefit from https://www.arangodb.com/docs/stable/aql/operations-update.html. (2 points)

    db._executeTransaction({
      collections: {
        read: "packages",
	    write: "packages"
      },
      action: function () {
        var db = require("@arangodb").db
        
        // Query amount of documents with Installed-Size attribute
        
        const c = db._query("FOR doc in packages FILTER HAS(doc, 'Installed-Size') == true COLLECT WITH COUNT INTO length RETURN length")
	    const count_before_update = c._documents[0]
        
        // Perform the update
        
	    db._query("FOR package IN packages UPDATE package WITH { 'InstalledSize': package['Installed-Size'], 'Installed-Size': null } IN packages OPTIONS { keepNull: false }")
        
        // Count updated documents
        
	    const e = db._query("FOR doc in packages FILTER HAS(doc, 'InstalledSize') == true COLLECT WITH COUNT INTO length RETURN length")
	    const count_after_update = e._documents[0]
	
        // If the numbers of updated documents do not match, throw exception and roll-back
        
        if (count_before_update != count_after_update) {
            throw "Fail in update"
        } else {
            return "OK"
        }
      }
    })