代写CSE 180、代写c++编程设计

日期：2022-11-26 04:20

Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021

1. Preliminaries

Under Resources→Lab4 on Piazza, there are some files that are discussed in this document. Two

of the files are lab4_ create.sql script and lab4_data_loading.sql. The lab4_ create.sql script

creates all tables within the schema Lab4. The schema is similar to the one in our create.sql

solution to Lab1 (although it has two NOT NULL constraints on the Battles table, which are

described below). We included all the constraints that were in our Lab2 solution. Lab3’s new

General constraints and revised Referential Integrity constraints are not in this schema, but the

original Referential Integrity constraints are included.

lab4_data_loading.sql loads data into those tables, just as similar files did for previous Lab

Assignments. Alter your search path so that you can work with the tables without qualifying them

with the schema name:

ALTER ROLE <username> SET SEARCH_PATH TO Lab4;

You must log out and log back in for this to take effect. To verify your search path, use:

SHOW SEARCH_PATH;

Note: It is important that you do not change the names of the tables. Otherwise, your application

may not pass our tests, and you will not get any points for this assignment.

Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021

2. Instructions for database access from C

An important file under Resources→Lab4 is runAdventureApplication.c. That file is not compilable

as is. You will have to complete it to make it compilable, including writing three C functions that

are described in Section 4 of this document. You will also have to write a Stored Function that is

used by one of those C functions; that Stored Function is described in Section 5 of this document.

As announced at the beginning of the quarter, we assume that CSE 180 students are familiar with C.

However, you will not have to use a Make file, since runAdventureApplication.c is the only file in

your C program.

Assuming that runAdventureApplication.c is in your current directory, you can compile it with the

following command (where the “>” character represents the Unix prompt):

> gcc -L/usr/include -lpq -o runAdventureApplication runAdventureApplication.c

When you execute runAdventureApplication, its arguments will be your userid and your password

for our PostgreSQL database. So after you have successfully compiled your

runAdventureApplication.c (and separately successfully created your Stored Function in the

database), then you’ll be able to execute your program by saying:

> runAdventureApplication <your_userid> <your_password>

[Do not put your userid or your password in your program code, and do not include the < and >

symbols, just the userid and password. We will run your program as ourselves, not as you.]

How you develop your program is up to you, but we will run (and grade) your program using these

commands on unix.ucsc.edu. So you must ensure that your program works in that environment.

Don’t try to change your grade by telling us that your program failed in our environment, but

worked in your own environment; if you do, we’ll point you to this Lab4 comment.

Because Lab4 is mainly a database application programming assignment, rather than a C language

assignment, please don’t use a makefile or a header (.h) file in Lab4.

Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021

3. Goal

The fourth lab project puts the database you have created to practical use. You will implement part

of an application front-end to the database. As good programming practice, your functions should

catch erroneous parameters (and impossible situations). We describe erroneous parameters and

required handling below.

4. Description of the three C functions in the runAdventureApplication file that

interact with the database

The runAdventureApplication.c that you’ve been given contains skeletons for three C functions that

interact with the database using libpq.

These three C functions are described below. The first argument for all of these C function is your

connection to the database.

• printNumberOfThingsInRoom: The Things table has an attribute initialRoomID which

identifies the room where that thing is located when Adventure begins. However, a thing

could be owned by a character. If a thing is owned by a character, then the room where

that thing is currently located is the room that the character is in, not the thing’s

initialRoomID.

Besides the database connection, the printNumberOfThingsInRoom function has one

parameter, an integer, theRoomID.

o If there is no room in the Rooms table whose roomID is theRoomID, then

printNumberOfThingsInRoom shouldn’t print anything, and it should return the

value -1.

o If there is a room in Rooms table whose roomID is theRoomID, then

printNumberOfThingsInRoom should print the roomID, the description of that

room, and the number of number of things which are in that room, and it should

return the value 0. The format of the output should be:

Room <roomID>, <description>, has <number of things> in it.

(Don’t worry about the exact number of spaces in your output.)

Big Hint: C functions can have more than one SQL statement in them. You’ll want to

use more than one SQL statement in your code for printNumberOfThingsInRoom. And

you’ll want those statement to be in a Serializable transaction.

Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021

• updateWasDefeated: The Lab4 create_lab4.sql file CREATE Battles statement specifies

NOT NULL for the characterBattlePoints nor monsterBattlePoints attributes.

A character lost a battle if their characterBattlePoints in that battle is less than the

monsterBattlePoints of the monster in that battle. And a monster lost a battle if their

monsterBattlePoints in that battle is less than the characterBattlePoints of the character in

that battle. If the character and monster in a battle had the same number of battlePoints in

that battle, then neither of them lost.

wasDefeated is an attribute in both the Characters and the Monsters table (which could be

NULL). If a character (or monster) lost a battle, then its wasDefeated attribute value

should be TRUE. And if a character (or monster) didn’t lose a battle, then its wasDefeated

attribute value should be FALSE. Some values of wasDefeated may be correct, but some

values of wasDefeated may be incorrect; the updateWasDefeated function will fix the

wasDefeated values (for either characters or monsters) that are incorrect in a Serializable

transaction.

Besides the database connection, updateWasDefeated has another parameter

doCharactersOrMonsters, which should have the value ‘C’ or ‘M’. updateWasDefeated

should fix incorrect values of wasDefeated for either characters or monster, depending on

the value of doCharactersOrMonsters). In either case, updateWasDefeated returns the

number of incorrect wasDefeated values that had to be fixed because they were incorrect.

o If doCharactersOrMonsters has the value ‘C’, updateWasDefeated should fix the

incorrect wasDefeated values for characters, and return the number of incorrect

wasDefeated values in Characters that had to be fixed.

o If doCharactersOrMonsters has the value ‘M’, updateWasDefeated should fix the

incorrect wasDefeated values for monsters, and return the number of incorrect

wasDefeated values in Monsters that had to be fixed.

If doCharactersOrMonsters has any value other than ‘C’ or ‘M’, that’s an error, and

updateWasDefeated should return the value -1. But if there are no characters (or monsters)

whose wasDefeated is incorrect, that’s not an error; in that case, updateWasDefeated

should return 0, since no wasDefeated values were updated.

Note that there are two reasons that the value of wasDefeated could be incorrect for a

character (or monster):

a) if wasDefeated is FALSE, but the character (or monster) lost at least one battle, or

b) if wasDefeated is TRUE, but the character (or monster) didn’t lose at least one

battle.

updateWasDefeated needs to fix both of these incorrect values for characters (or monsters).

• increaseSomeThingCosts: Besides the database connection, this function has one integer

parameter, maxTotalIncrease. increaseSomeThingCosts invokes a Stored Function,

increaseSomeThingCostsFunction, that you will need to implement and store in the

database according to the description in Section 5. The Stored Function

increaseSomeThingCostsFunction should have the same maxTotalIncrease parameter that

was supplied to increaseSomeThingCosts (but the Stored Function does not have the

Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021

database connection as a parameter).

The Things table has an attribute cost, indicating the cost of a thing.

increaseSomeThingCostsFunction will change the cost for some (but not necessarily all)

things in the Things table. Section 5 explains which things should have their cost values

changed, and also tells you how much you should increase these costs. The

increaseSomeThingCosts function should return the same integer result that the

increaseSomeThingCostsFunction Stored Function returns.

increaseSomeThingCostsFunction may return a negative value, signifying an erroneous

argument. increaseSomeThingCosts should just return that value to its invoker, which will

deal with that value as described in the Testing section below in Section 6.

The increaseSomeThingCosts function must only invoke the Stored Function

increaseSomeThingCostsFunction, which does all of the work for this part of the

assignment; increaseSomeThingCosts must not do the work itself.

Each of these three functions is annotated in the runAdventureApplication.c file we’ve given you,

with comments providing a description of what it is supposed to do (repeating just part of the above

descriptions). Your task is to implement functions that match those descriptions.

Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021

The following helpful libpq-related links appear in Lecture 11.

• Chapter 33. libpq - C Library in PostgreSQL docs, particularly:

• 31.1. Database Connection Control Functions

• 31.3. Command Execution Functions

• 33.21. libpq Example Programs

• PostgreSQL C tutorial from Zetcode

5. Stored Function

As Section 4 mentioned, you should write a Stored Function (not a Stored Procedure) called

increaseSomeThingCostsFunction which has one integer parameter, maxTotalIncrease. If

maxTotalIncrease is not positive, then increaseSomeThingCostsFunction should return -1,

signifying an error.

The attribute thingKind in the Things table distinguishes among different kinds of things, such as

scrolls (‘sc’), maps (‘ma’), statues (‘st’), swords (‘sw’) and shields (‘sh’). [Don’t worry about the

full words for these things; our Adventure database instances store two symbol abbreviations, but

it’s convenient to talk about scrolls, rather than things whose thingKind is ‘sc’.]

If a thing in Things has ownerMemberID and ownerRole that are NULL, then that thing is not

owned; if ownerMemberID and ownerRole for a thing aren’t NULL, then that thing is owned. (It’s

not possible for one of those attributes to be NULL unless the other attribute is also NULL.) There

might be 10 owned things that are scrolls, 3 owned things that are swords, and 1 owned thing that’s

a map.

[Note that there is no separate table of thingKind values. You determine which thingKind values

there are and how many things have each thingKind by looking at the Things table.]

We decide to increase the cost of popular things so that our Adventure game makes more money.

(Paying for things is not part of this assignment.) The cost increase will be the same for all things

which have the same thingKind (e.g., scrolls), even though different scrolls may have different

costs.

• If a thingKind is owned 5 or more times, then its cost increase should be 5.

• If a thingKind is owned 4 times, then its cost increase should be 4.

• If a thingKind is owned 3 times, then its cost increase should be 2.

• Otherwise, no cost increase.

But increaseSomeThingCostsFunction shouldn’t apply these cost increases. The total of the cost

increases must less than or equal to the value of the parameter maxTotalIncrease.

increaseSomeThingCostsFunction will return the total of the cost increases, which might equal

maxTotalIncrease, but also might be less than maxTotalIncrease.

Here’s how to determine which things should have their cost values increased:

Iterate through thingKind values based on the number of owners of that thingKind, in decreasing

order, so that the most popular thingKind values are considered first. For each thingKind you

consider, increase the cost of that thingKind by the cost increase describe above. However, the

total of your cost increases must not be more than maxTotalIncrease.

Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021

Note: You can declare variables as NUMERIC (e.g., NUMERIC(5,2)) in PL/pgSQL functions.

And it’s okay to add an INTEGER to a value that’s declared as NUMERIC.

Here are some examples of how this works. Assume that scrolls are owned by 20 characters

(cost increase is 5), swords are owned by 4 characters (cost increase is 4), maps are owned by 3

characters (cost increase is 2) and shields are owned by 3 characters (cost increase is also 2).

• What happens if maxTotalIncrease is 62? Then the costs of 12 scrolls are increased by 5,

the costs of the swords stay the same, and the cost of 1 of the maps (or shields) is increased

by 2. The value that is returned is 62 (which is 12*5 + 0*4 + 1*2).

[Any 12 of the scrolls, and any 1 of the maps (or shields) could have their costs increased,

doesn’t matter which. thingKind values which have the same popularity level are treated

the same way.]

• What happens if the maxTotalIncrease is 63? Then the costs of 12 scrolls are increased by

5, the costs of the swords stay the same, and the cost of 1 of the maps (or shields) is

increased by 2. The value that is returned is once again 62 (which is 12*5 + 0*4 + 1*2),

not 63.

• What happens if the maxTotalIncrease is 111? Then the costs of the 20 scrolls are

increased by 5, the costs of 2 swords are increased by 4, and the cost of 1 of the maps (or

shields) is increased by 2. The value that is returned is 110 (which is 20*5 + 2*4 + 1*2).

• What happens if maxTotalIncrease is 200? Then the costs of the 20 scrolls are increased

by 5, the costs of the 4 swords are increased by 4, and the cost of the 3 maps is increased by

2 and the costs of the 3 shields is increased by 2. The value that is returned is 128 (which

is 20*5 + 4*4 + 6*2).

• What happens if the maxTotalIncrease is 3? Then the costs of the scrolls and swords

remain the same, and the cost of 1 of the maps in increased by 2. The value that is returned

is 2 (which is 0*5 + 0*4 + 1*2).

• Finally, what happens if the maxTotalIncrease is 1? All the costs remain the same. The

value that is returned is 0.

Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021

Write the code to create the Stored Function, and save it to a text file named

increaseSomeThingCostsFunction.pgsql. After you’ve saved that file, you can create the Stored

Function increaseSomeThingCostsFunction by issuing the psql command:

\i increaseSomeThingCostsFunction.pgsql

at the server prompt. If the creation goes through successfully, then the server should respond with

the message “CREATE FUNCTION”. You will need to call the Stored Function from the

increaseSomeThingCosts function in your C program, as described in the previous section, so

you’ll need to create the Stored Function before you run your program. You should include the

increaseSomeThingCostsFunction.pgsql source file in the zip file of your submission, together with

your versions of the C source files runAdventureApplication.c that was described in Section 4; see

Section 7 for detailed instructions.

A guide for defining Stored Functions for PostgreSQL can be found here on the PostgreSQL site.

PostgreSQL Stored Functions have some syntactic differences from the PSM stored

procedures/functions that were described in Lecture. For Lab4, you should write a Stored Function

that has only IN parameters; that’s legal in both PSM and PostgreSQL.

We’ve given you some more hints on Piazza about writing PostgreSQL Stored Functions,

including:

• StoredFunction_pgsql_Info.pdf, which describes some differences between PSM and

PL/pgSQL,

• fireSomePlayersFunction.pgsql, an example of a PostgreSQL Stored Function, and

• What_Does_fireSomePlayersFunction_Do.pdf, an explanation of what that Stored Function

does. But we won’t provide the tables and load data for running that Stored Function.

6. Testing

Within main for runAdventureApplication.c, you should write several tests of the C functions

described in Section 4. You might also want to write your own tests, but the following tests must

be included in the runAdventureApplication.c file that you submit in your Lab4 solution.

• Write four tests of the printNumberOfThingsInRoom function.

o The first test should be for the room whose roomID is 1.

o The second test should be for the room whose roomID is 2.

o The third test should be for the room whose roomID is 3.

o The fourth test should be for the room whose roomID is 7.

Printing occurs within printNumberOfThingsInRoom, which should return the value 0 or

the value -1. If it returns 0, no additional action is required. But if it returns -1, then print

out:

No room exists whose id is <roomID>

where roomID is the roomID that you supplied. Continue executing additional tests if this

occurs.

However, if printNumberOfThingsInRoom returns any other value besides 0 or -1, print out

an error message describing the bad value returned (format up to you, as long as it conveys

full information about the error) and exit using bad_exit.

Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021

• Write three tests for the updateWasDefeated function.

o The first test should be for doCharactersOrMonsters ‘C’.

o The second test should be for should be for doCharactersOrMonsters ‘M’.

o The third test should also be for doCharactersOrMonsters ‘C’. (What should the

value returned by that test always be?)

If updateWasDefeated returns a non-negative value, then print out:

<value returned> wasDefeated values were fixed for <doCharactersOrMonsters>

where <value returned> is the value returned and <doCharactersOrMonsters> is the

parameter that was sent to the function.

If updateWasDefeated returns -1 then print out:

Illegal value for doCharactersOrMonsters <doCharactersOrMonsters>

If updateWasDefeated returns any other value, then print out an error message describing

the bad value returned (format up to you, as long as it conveys full information about the

error) and exit using bad_exit.

• Also write four tests for the increaseSomeThingCosts function.

o The first test should have maxTotalIncrease value 12.

o The second test should have maxTotalIncrease value 500.

o The third test should have maxTotalIncrease value 39.

o The fourth test should have maxTotalIncrease value 1.

Run these tests and print their results from main in runAdventureApplication. If

increaseSomeThingCosts returns a non-negative value, print out that result in the following

format:

Total increase for maxTotalIncrease <maxTotalIncrease> is <value returned>

But increaseSomeThingCosts can return a negative value, signifying an error. For these

negative values, print an error message (format of error message is up to you) that

describes the error and the erroneous parameter that resulted in the error and exit (using

bad_exit).

You must run all of these tests for all three of these functions in the specified order, starting with

the database provided by our create and load scripts. Some of these functions modify the database,

so be sure to use the load data that we’ve provided, executing the functions in the specified order.

Reload the original load data before you start, but you do not have to reload the data multiple times

in Lab4.

Hmmm, do these tests affect each other? What do you think?

Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021

7. Submitting

1. Remember to add comments to your C code so that the intent is clear.

2. Place the C program runAdventureApplication.c and the stored procedure declaration code

increaseSomeThingCostsFunction.pgsql in your working directory at unix.ucsc.edu.

3. Zip the files to a single file with name Lab4_XXXXXXX.zip where XXXXXXX is your 7-

digit student ID, for example, if a student's ID is 1234567, then the file that this student

submits for Lab4 should be named Lab4_1234567.zip. To create the zip file, you can use the

Unix command:

zip Lab4_1234567 runAdventureApplication.c increaseSomeThingCostsFunction.pgsql

Please do not include any other files in your zip file, except perhaps for a view creation file

(described below) and an optional README file, if you want to include additional

information about your Lab4 submission.

4. Some students might want to use views to do Lab4. That’s not required. But if you do use

views, you must put the statements creating those views in a file called

createAdventureViews.sql which you include in your Lab4 zip file.

5. Lab4 is due on Canvas by 11:59pm on Tuesday, November 29, 2022. Late submissions will

not be accepted, and there will be no make-up Lab assignments.