Creating A Database

1. Creating A Database • Now that we have examined a variety of data structures in CL, let’s use what we’ve learned to create a database – how? • In Java or C, we might define a class or struct and then declare an array of them for our database • In CL, we have a wide variety of choices: • store a record as a list and store the DB as a list of lists • store a record as a structure and store the DB as a list of structures • store a record as a structure and store the DB as an array of structures • store a record as a dotted pair with the key being the car and the data of the record as the cdr, and store the DB as an array of dotted pairs • store a record as an a dotted pair with the key being the car and the data of the record being the cdr, and store the DB as an Association List • store a record as a structure with and store the DB in a hash table with each record’s key used to hash into the hash table

2. List of Lists • The simplest implementation is to store our DB as a list of lists • the Practical Common Lisp text covers this in chapter 3 (see Practical: A Simple Database) • adding a record is a matter of inputting the new record into a list and adding the list to the DB list (see next slide) • sorting can be done using (sort db compare-function :key #’access-function) • where compare-function might be #’> or #’string<, etc • and access-function would be #’car, #’cadr, #’caddr, etc • searching, updating and deleting will require a dolist statement and an access function (example follows in two slides) • storing or printing the database is a matter of iterating through the list and writing each sublist to a stream or standard output using print or format (format would be preferred)

3. Inputting A Record • For each of the DB record’s slots, we input them one at a time and add them to a single list • Now we append the list to our database • If the item input is a string, use read-line instead of read • We would call this using something like: • (setf db (input db)) (defun input (db) (let (temp) (format t “Enter value for record’s slot1: ”) (setf temp (append temp (list (read)))) (format t “Enter value for record’s slot2: ”) (setf temp (append temp (list (read)))) … (setf db (append db (list temp))) temp))

4. Implementing Search and Delete (defun search (db access-function target) (dolist (i db) (if (equal target (access-function i)) (print-record i))) (defun search> (db access-function target) (dolist (i db) (if (> target (access-function i)) (print-record i))) ;; use string> if field is a string, use char> if field is a char (search db #’cadr ’csc) (defun print-record (rec) (let ((slot1 (car rec)) (slot2 (cadr rec)) (slot3 (caddr rec)) …) (format t “…” slot1 slot2 slot3)) (defun db-delete (db target) (remove target db :key #’car)) ;; delete by first slot (defun db-delete-all (db access-function target) (remove target db :key access-function)) (defun db-delete< (db access-function target) ;; use #’string>, etc (remove target db :key access-function :test #’>)) ;; as necessary

5. Storing/Loading • We have two options for storing and loading our database to/from disk file • We can simply write/read the entire list • We can output/input each item one at a time • the first solution is the simplest and by no means inefficient as long as the items being stored can be input using one of the read functions • the second solution allows the file to be accessed by other readers aside form lisp • for instance, if you were to open the file created by the first solution in notepad, it would be readable but it would look very messy (defun output (lis) (defun input ( ) (let ((outfile (open “…” (let ((infile (open “…”)) temp) :direction :output))) (setf temp (read infile)) (print lis outfile) (close infile) (close outfile)) temp))

6. Analysis of the List of List Approach • Drawbacks • Clunky implementation for search, delete, etc • Does not take advantage of some of the built-in features of CL such as the hash table • Inefficient search (hash table might be better) • Possible to have corrupt records • If a datum is missing from a record, all of the rest of the fields may be thrown off leading to incorrect results when searching or printing • Offers no information hiding type of protection • In fact, we won’t find that in CL unless we turn to OOP, and even then it may not be what we expect • Advantages • Easy to implement because of all the list operations available in CL • So it would also be easy (relatively speaking) to debug • Easy to implement load and store operations • Sort being a built-in operation permits easy sorting of the DB • Is this the best approach? No

7. List/Array of Structures • The main problem with our previous approach was that by storing a record as an ordinary list, there was nothing that dictated that a specific value had to be placed in a specific position • If we instead define the format of each record as a structure, we can control this • Now we store our records in a list or an array, but each record is formatted to be a pre-defined structure • This not only helps ensure correctness, it also allows us to provide default values, default types, and the structure will automatically generate accessor functions when the structure is defined • Whether we use an array or a list is a matter of choice • Recall that both are sequences with many of the same operations available • The list will be more familiar to us and allows us to more easily change the size, but is less efficient of an implementation

8. (defstruct student name (major 'CSC) (gpa 0.0) (hours 0)) (defun addstudent (db) (let (n m g h) (print "Enter new student name:") (setf n (read)) (print "Enter new student major:") (setf m (read)) (print "Enter new student gpa:") (setf g (read)) (print "Enter new student hours:") (setf h (read)) (setf db (append db (list (make-student :name n :major m :gpa g :hours h)))) db)) (defun search-major (db target) (dolist (a db) (if (equal (student-major a) target) (print-student a))) (defun find-student (db target) (do ((a (car db) (car db))) ((or (equal (student-name a) target) (null db)) (if (equal (student-name a) target) (return-from find-student a) (setf db (cdr db)))))) PartialExampleUsing a List

9. (defun save2 (db) (let ((outfile (open “…” :if-does-not-exist :create :if-exists :supersede :direction :output))) (print (length db) outfile) (dolist (a db) (print (db-name a) outfile) (print (db-major a) outfile) …) (close outfile))) (defun load2 ( ) (let ((infile (open “…” :if-does-not-exist :error)) student db num) (setf num (read infile)) (dotimes (i num) (setf student (make-student)) (setf (student-name student) (read-line infile)) ;; read-line for string (setf (student-major student) (read infile)) ;; read for symbols, numbers … (setf db (append db (list student)))) (close infile) db)) Loading/Storing

10. Sorting • The built-in sort function can work for us here • Recall that sort operates on any type of sequence • If we are storing our database, we need to specify two additional parameters to the function • The function to compare each database entry • #’string<, #’string>, #’<, #’> • An accessor function for the structure’s slot that we want compared • #’student-name, #’student-major, etc • If our database is stored in db then we might use the following to sort in descending order by hours • (sort db #'< :key #'student-hours) • Or to sort in ascending order by name • (sort db #’string> :key #’student-name) • Recall that sort is destructive, so this alters db itself rather than returning a sorted item • Note: this will work whether db is a list or an array!

11. Primary Keys • So far our database is just a collection of stored items • A relational database has an additional property in which all records have a unique identifier called the primary key • This allows us to find a specific record in the database at any time by searching for that key • It must be the only copy of that value in the relation since it is unique • In our previous example, imagine that two students had the same name, this would lead to problems if I wanted to delete a record or update a record based on student name • We can solve this problem by adding a unique value to the list of lists or the structure • For instance, a social security number • Now, whenever I search, I can search for the entry that happens to store that unique value • However, we could instead use one of the CL data structures that use keys – association lists or hash tables

12. Association List Implementation • Several minor modifications will be made to our list of structure implementation • Upon inputting a new record • check to see if the key already exists by using assoc • if assoc returns an item, then the key is already in use • otherwise add the new record using acons • Searching for a specific record is done by inputting the key value and using assoc • we would probably want to return the cdr of what assoc returns since assoc returns (key . record) • Printing the entire database requires iterating through the association list and outputing the cdr of each record • Sorting requires that we change our access function by adding another cdr onto it (such as sorting by #’cadddr instead of #’caddr) • we could also sort by key if that is relevant

13. Example (defun find-record (target db) (let ((record (assoc target db))) (if record (cdr record) (format t "Record with key of ~A not found!" target)))) (defun input (db) (let (temp slot1 slot2 slot 3 …) (format t "Enter new record’s slot 1: ") (setf slot1 (read-line)) ;; assume slot1 is a name or identifier (format t "Enter new record’s slot 2: ") (setf slot2 (read)) ;; assume slot2 is the key … (setf temp (make-record :slot1 slot1 :slot2 slot2 …)) (if (assoc slot1 db) (format t “Cannot add ~A because ~A is a duplicate key!” slot1 slot2) (setf db (acons slot2 temp db))) db))

14. Example Continued (defun remove-record (target db) (let ((record-to-delete (assoc target db))) (if record-to-delete (setf db (remove record-to-delete db)) (format t “Record with key of ~A not found!” target)) db)) (defun print-records (db) (dolist (a db) (print-struct (cdr a)))) • NOTE: do not use an a-list if your records are stored as lists instead of structures • this would make acons cons together an atom (the key) to a list (the record) giving us a list with the key as the car of the list, negating the association-list strength of having a key!

15. Hash Table Implementation • The most substantial changes to our database arise if we want to implement it as a hash table • To add records, we use (setf (gethash key db) record) which adds key/record to the db • Unlike the list or array implementations, we lose the easy ability to search • previously, we used dolist or dotimes and aref • now, we have to write a function to be called by maphash for • printing, saving/loading the database, searching for all records that fit some criteria (e.g., all ENG majors, all GPAs >= 3.0) • The hash table implementation is useful only in that it finds the key entry for us without us having to search for it, but assoc did the same • additionally, gethash does not care if a key already exists in the hash table, so doing (setf (gethash key db) record) will replace the old record with the new record if a key is reused • we would have to make sure that a key does not already exist by first seeing if (gethash key db) returns a record or nil

16. Criteria Searching in the Hash Table • Consider our previously defined student structure • We want to search for a specific major • Here is how it would be implemented using an association list and using a hash table • The hash table version, while not significantly harder to write, requires using lambda functions (which we will cover in more detail in a week) (defun search-by-major (major db) (maphash #'(lambda (x y) (if (equal major (student-major y)) (print y))) db)) (defun search-by-major (major db) (dolist (a db) (if (equal major (student-major (cdr a))) (print a))))

17. Accessing the Entire Database • One drawback of the hash table approach is when it comes time to access the entire thing • Whether for criteria-based search or printing the entire thing • This is caused by the fact that the hash table is stored in an array larger in size than the number of element stored there • maphash will iterate through the array but only apply the given function to non-empty elements • a given hash table might store 100 elements in a table of size 1000 (defun print-db (db) (maphash #'(lambda (x y) (print-record y)) db)) This function will print the records out, not in the order they were entered, but a seemingly random order based on the numeric order that their keys map into the hash table

18. Advantages/Disadvantages