[Technology Background] API and DB

### :school: TEEP 2024_RT LAB_ORAN DPDK #### :book: Technology Background :::success List the essential information of this chapter. 1. Networking Programming - API and DB ::: --- ## 1. Networking Programming - API and DB ### 1.1 API (Application Programming Interface) ![image](https://hackmd.io/_uploads/Hk-1awvF6.png) #### What Are API's? APIs, or application programming interfaces, are sets of tools, definitions, and protocols that enable the integration of application software and services. They allow products and services to communicate with each other without the need for constant development of new connectivity infrastructure. APIs can be private, partnered, or public, with benefits such as creating new revenue streams, expanding brand reach, and fostering innovation through external development. Two significant types of APIs are SOAP (Simple Object Access Protocol) and REST (Representational State Transfer). SOAP standardizes message formats and requests, serving as a protocol specification for communication between applications in different environments and programming languages. On the other hand, REST is an architectural style, relying on six guiding principles to offer a simpler and more flexible approach. Due to their simplicity and flexibility, RESTful APIs are gaining popularity and are often preferred over SOAP in the growing landscape of web APIs. ![image](https://hackmd.io/_uploads/Sy_bE_SYp.png) #### Why We Need API ? APIs play a crucial role in facilitating the integration of data, applications, and devices within an IT organization, enabling seamless communication and collaboration among various technologies. The inefficiencies and financial losses incurred due to incompatible technologies can be mitigated through effective API usage, especially when combined with distributed integration and containers. Agile integration, as an architectural approach to integration platforms, prioritizes a compact IT footprint, high scalability, and availability, as well as clearly defined, reusable, and well-managed endpoints. In essence, the future of interconnected systems should not only support collaboration between teams and technologies but actively encourage it. With the increasing pace of technological change, agile integration is seen as the optimal strategy to facilitate business transformation. ![image](https://hackmd.io/_uploads/rklXK-dHYp.png) #### API for Telco Telecom APIs, also known as network APIs for telcos, serve as standardized interfaces connecting user-facing applications with complex 4G or 5G networks. These APIs enable developers to access and control network resources, facilitating the creation of advanced applications and services with features like low-latency connectivity and quality of service. They act as protocols and tools, bridging the digital world with telecommunication infrastructure for seamless integration and enhanced functionalities. The Telecom API market has experienced significant growth, driven by factors such as digital transformation, increased mobile penetration, the IoT revolution, and the desire to enhance customer experience. Key players in the market include Twilio, Nexmo (Vonage), Plivo, and Tropo (Cisco). Trends shaping the future of the Telecom API market include 5G integration, the rise of edge computing, a focus on security and privacy, and the increasing role of artificial intelligence in communication systems. #### API Monetize Networks Network APIs play a crucial role in developing applications that generate revenue on networks. Examples include: 1. **mMTC/IoT Applications:** The use of device-specific Network APIs is essential for developing applications that interact with sensors and machines in Internet of Things (IoT) environments. 2. **Low-latency Applications:** Developers can use network APIs like Quality On Demand and device-specific APIs to create applications with minimal latency, particularly beneficial in scenarios involving edge computing. 3. **Network Slicing:** Network APIs enable the creation and management of network slices, allowing specific applications and services to operate within virtual network segments. #### Elements in an URI APIs * Connection Protocol: This denotes the communication protocol (e.g., HTTP, HTTPS, or TCP) that dictates how data will be transmitted between the client and server. Common protocols include HTTP, HTTPS, and FTP. * Host Name: This section contains the name of the hosting server, such as Google or Facebook, identifying the API's location. The domain name specifies the server hosting the resource (e.g., www.example.com in https://www.example.com/index.html). * Endpoint Type: It is useful to specify whether the endpoint is an API endpoint or another type, aiding in categorization. * Version: Incorporating a version in the API design is crucial for seamless updates. For example, transitioning from V1 to V2 ensures uninterrupted services reliant on the API during changes. * Base Path: Positioned after the version, the base path indicates the service's name the client intends to access, creating a structured hierarchy. This path outlines the resource's location on the server (e.g., /index.html in https://www.example.com/index.html). * Query: Following the base path, queries are added using "?" instead of "/", allowing the inclusion of parameters for the endpoint. This permits dynamic customization of requests, enhancing API flexibility. Query parameters, like q=example and limit=10 in https://www.example.com/search?q=example&limit=10, convey additional information to the server. * Fragment Identifier: The fragment identifier, introduced by a # character, specifies a particular section of the resource. For instance, in https://www.example.com/index.html#sectionX, the fragment identifier is sectionX. #### Test a REST API from command line with curl In this study note, I will demonstrate the execution of various HTTP requests, including GET, POST, PUT, HEAD, and DELETE, against a REST API. Specifically, I will be utilizing the REST API that corresponds to www.codever.dev. The API's documentation is based on OpenAPI and can be accessed for testing directly through the browser at https://www.codever.dev/api/docs/. **what is curl ?** Curl is a versatile command-line tool and library designed for data transfer using URL syntax. It facilitates communication with various protocols such as DICT, FILE, FTP, FTPS, Gopher, HTTP, HTTPS, IMAP, IMAPS, LDAP, LDAPS, POP3, POP3S, RTMP, RTSP, SCP, SFTP, SMTP, SMTPS, Telnet, and TFTP. Curl supports SSL certificates, enables HTTP POST, HTTP PUT, FTP uploading, and supports features like HTTP form-based upload, proxies, HTTP/2, cookies, and various authentication methods (Basic, Digest, NTLM, Negotiate, Kerberos). It also includes capabilities for file transfer resumption, proxy tunneling, and more. In the context of REST API interaction, curl can be employed to emulate HEAD, GET, POST, PUT, and DELETE requests. **install curl** ```= sudo apt install curl ``` **HEAD Request** If you want to find out information about something on the internet, like what kind of things it can do or what information it has, without actually getting all the details, you can ask the computer with a special request called a "HEAD request." It's like asking, "Hey, what can you tell me about yourself without telling me everything?" For example, if you want to know what's in the latest public bookmarks, you can use a HEAD request with a tool called curl. It's like sending a special message to the computer, saying, "What would I get if I asked for the latest bookmarks?" Head request: ```= curl -i -X HEAD https://www.codever.dev/api/public/bookmarks ``` curl options: * `-I` or `--head` - fetch the headers only * `-i, --include` - include the HTTP response headers in the output * `-X, --request` - specify a custom request method to use when communicating with the HTTP server (GET, PUT, DELETE&) Head Response: ![image](https://hackmd.io/_uploads/HJl77ust6.png) **GET Request** Executing curl with no parameters on a URL (resource) will execute a GET. GET Request: ```= curl -X GET "https://www.codever.dev/api/version" -H "accept: application/json" ``` GET Response: ![image](https://hackmd.io/_uploads/HkoBNuoFT.png) Curl options * `-H, --header` : customer header to pass to the server If you want to have it displayed prettier I suggest you use a tool like jq: ```= curl https://www.codever.dev/api/version | jq . ``` ![image](https://hackmd.io/_uploads/rJxNHOjKp.png) **Curl request with multiple headers** All the responses from the api of bookmarks.dev are gzipped. We could ask for the gzipped variant by issuing the following request: Request: ```= curl -v -H "Accept:application/json" -H "Accept-encoding:gzip" https://www.codever.dev/api/version ``` Curl options: * `-v, --verbose `: the opposite of --silent, make the operation more talkative To achieve that you need to simply add another -H option with the corresponding value. In this case you would get some unreadable characters in the content, if you do not redirect the response to a file: ``` > GET /api/version HTTP/1.1 > Host: www.codever.dev > User-Agent: curl/7.54.0 > Accept:application/json > Accept-encoding:gzip > < HTTP/1.1 200 OK < Server: nginx/1.12.0 < Date: Mon, 22 Jan 2024 14:45:39 GMT < Content-Type: application/json; charset=utf-8 < Transfer-Encoding: chunked < Connection: keep-alive < X-Powered-By: Express < Access-Control-Allow-Origin: * < Access-Control-Allow-Methods: POST, GET, PUT, PATCH, DELETE, OPTIONS < Access-Control-Allow-Headers: Content-Type, Authorization, Location < Access-Control-Expose-Headers: Content-Type, Authorization, Location < ETag: W/"48-3r0bLwzjWi409jIuXDFQE7IoNtI" < Strict-Transport-Security: max-age=63072000; includeSubdomains < X-Content-Type-Options: nosniff < Content-Encoding: gzip < * Connection #0 to host www.codever.dev left intact �V*K-*��S�R2�3�3P�QJ�, �H4��&��%��X&Z$[X&�Z��&�� ``` --- **CRUD Operations on Bookmarks.dev API** Those were some basic curl HTTP calls with a few options. Now we will combine them and show examples against a production ready API. For the examples I will use the API running on localhost. It is really easy to setup with Docker-compose if you follow the instructions from the Readme file. The API is protected with Keycloak and bearer token. A way to obtain a bearer token in Keycloak is to enable Direct Access Grants for the client - this corresponds to the Resource Owner Password Credentials in the OAuth2 Specification. Thus the user’s credentials are sent within form parameters. Of course we can do that with curl too: Request: ``` curl \ -d 'client_id=bookmarks' \ -d 'username=mock' \ -d "password=mock" \ -d 'grant_type=password' \ 'http://localhost:8480/auth/realms/bookmarks/protocol/openid-connect/token' \ | jq . ``` response: ``` { "access_token": "eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICJMNHV6eUFYbUlTSDJPRi00c2VZZ2Z3UWtJT204QTR3cnBDV0JHSVdOU2c4In0.eyJqdGkiOiJlY2I3YjE0Yi02ZTZhLTQyMzEtYWI5NS04ZDAwZmI5YjNiN2MiLCJleHAiOjE1ODM1MTg0OTUsIm5iZiI6MCwiaWF0IjoxNTgzNTE0ODk1LCJpc3MiOiJodHRwOi8vbG9jYWxob3N0Ojg0ODAvYXV0aC9yZWFsbXMvYm9va21hcmtzIiwiYXVkIjoiYWNjb3VudCIsInN1YiI6IjRjNjE3ZjJiLTJiYWQtNDk4Yi1hOWM2LTRlOWE4YzMwMzc5OCIsInR5cCI6IkJlYXJlciIsImF6cCI6ImJvb2ttYXJrcyIsImF1dGhfdGltZSI6MCwic2Vzc2lvbl9zdGF0ZSI6ImE5NGVjNGI3LTNhY2YtNGMzOS1iOTBlLTAzOWVlNzZjY2EwYiIsImFjciI6IjEiLCJhbGxvd2VkLW9yaWdpbnMiOlsiKiJdLCJyZWFsbV9hY2Nlc3MiOnsicm9sZXMiOlsiUk9MRV9VU0VSIiwib2ZmbGluZV9hY2Nlc3MiLCJ1bWFfYXV0aG9yaXphdGlvbiJdfSwicmVzb3VyY2VfYWNjZXNzIjp7ImFjY291bnQiOnsicm9sZXMiOlsibWFuYWdlLWFjY291bnQiLCJtYW5hZ2UtYWNjb3VudC1saW5rcyIsInZpZXctcHJvZmlsZSJdfX0sInNjb3BlIjoiZW1haWwgcHJvZmlsZSIsImVtYWlsX3ZlcmlmaWVkIjp0cnVlLCJuYW1lIjoiQWRyaWFuIE1hdGVpIiwicHJlZmVycmVkX3VzZXJuYW1lIjoiYW1hIiwiZ2l2ZW5fbmFtZSI6IkFkcmlhbiIsImZhbWlseV9uYW1lIjoiTWF0ZWkiLCJlbWFpbCI6ImFkcmlhbm1hdGVpQGdtYWlsLmNvbSJ9.cymI5LzrUFje4vZNYMzvS0yhdVx4V8u_XVDUxi4sUk4tKpI2xcFQqWEiN_hxCLHpCDjNCqjw2JQUxQTvbQe_Wf8TGWz1f3nXhKg5CEw29ArCV3lFZL7_QmUOod53KnQ-9umSe2EISv2EbD0__idaivyCIerfV4M0wfgG31iyLPJ6_Pl_nJiw5RgifdqNljpKL9znpt5l3PiU7x2ACGv9V_GPvwAnU-9VxIuEqfErgc6IfhQxg9vuI_kHprXu-ClATA_Zg_xNEw53TD3qHJV_5sCu58MORhPv8fddcAZeLHxsr9sVyhFlmKMz1ZGWH0q5QZwLzKlGaaVr72Y2KSEPyA", "expires_in": 3600, "refresh_expires_in": 36000, "refresh_token": "eyJhbGciOiJIUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICJiNDEwODA3My0yNjZiLTQ4YzAtOGJmYi04ZGJjZmE2NjYxMmEifQ.eyJqdGkiOiI0N2VhYzc3NS1mODdjLTRiYjMtODQxZi0wYTViZGZkMzIxZjYiLCJleHAiOjE1ODM1NTA4OTUsIm5iZiI6MCwiaWF0IjoxNTgzNTE0ODk1LCJpc3MiOiJodHRwOi8vbG9jYWxob3N0Ojg0ODAvYXV0aC9yZWFsbXMvYm9va21hcmtzIiwiYXVkIjoiaHR0cDovL2xvY2FsaG9zdDo4NDgwL2F1dGgvcmVhbG1zL2Jvb2ttYXJrcyIsInN1YiI6IjRjNjE3ZjJiLTJiYWQtNDk4Yi1hOWM2LTRlOWE4YzMwMzc5OCIsInR5cCI6IlJlZnJlc2giLCJhenAiOiJib29rbWFya3MiLCJhdXRoX3RpbWUiOjAsInNlc3Npb25fc3RhdGUiOiJhOTRlYzRiNy0zYWNmLTRjMzktYjkwZS0wMzllZTc2Y2NhMGIiLCJyZWFsbV9hY2Nlc3MiOnsicm9sZXMiOlsiUk9MRV9VU0VSIiwib2ZmbGluZV9hY2Nlc3MiLCJ1bWFfYXV0aG9yaXphdGlvbiJdfSwicmVzb3VyY2VfYWNjZXNzIjp7ImFjY291bnQiOnsicm9sZXMiOlsibWFuYWdlLWFjY291bnQiLCJtYW5hZ2UtYWNjb3VudC1saW5rcyIsInZpZXctcHJvZmlsZSJdfX0sInNjb3BlIjoiZW1haWwgcHJvZmlsZSJ9.MNxMEtq5zcVTjxCnws5EotnfuUH7uY_kbKDwRRzGcko", "token_type": "bearer", "not-before-policy": 0, "session_state": "a94ec4b7-3acf-4c39-b90e-039ee76cca0b", "scope": "email profile" } ``` **Create bookmark - POST** Request: ``` curl -i -X POST "http://localhost:3000/api/personal/users/4c617f2b-2bad-498b-a9c6-4e9a8c303798/bookmarks" \ -H "accept: */*" -H "Authorization: Bearer eyJhbGciOiJ...." \ -H "Content-Type: application/json" -d "{\"name\":\"How to test a REST api from command line with curl – CodepediaOrg\",\"location\":\"https://www.codepedia.org/ama/how-to-test-a-rest-api-from-command-line-with-curl/\",\"language\":\"en\",\"tags\":[\"rest\",\"curl\",\"api\",\"testing\"],\"publishedOn\":\"2024-01-22\",\"sourceCodeURL\":\"https://github.com/CodeverDotDev/codever\",\"description\":\" In this post I will present how to execute GET, POST, PUT, HEAD, DELETE HTTP Requests against a REST API. For the purpose of this blog post I will be using the REST api that supports [www.codever.dev](https://www.codever.dev)\",\"descriptionHtml\":\"In this post I will present how to execute GET, POST, PUT, HEAD, DELETE HTTP Requests against a REST API. For the purpose of this blog post I will be using the REST api that supports <a href=\\\"https://www.codever.dev\\\">www.codever.dev</a>\",\"userId\":\"4c617f2b-2bad-498b-a9c6-4e9a8c303798\",\"public\":true,\"lastAccessedAt\":\"2024-01-22T20:11:28.101Z\",\"likeCount\":0}" ``` Response: ``` HTTP/1.1 201 Created X-Powered-By: Express Access-Control-Allow-Origin: * Access-Control-Allow-Methods: POST, GET, PUT, PATCH, DELETE, OPTIONS Access-Control-Allow-Headers: Content-Type, Authorization, Location Access-Control-Expose-Headers: Content-Type, Authorization, Location Location: http://localhost:3000/api/personal/users/4c617f2b-2bad-498b-a9c6-4e9a8c303798/bookmarks/5e62b18b59770b5487a4c741 Content-Type: application/json; charset=utf-8 Content-Length: 79 ETag: W/"4f-26GcBfsvgN8d+T+zqql3Y5R+Rl8" Date: Mon, 22 Jan 2024 14:11:44 GMT Connection: keep-alive {"response":"Bookmark created for userId 4c617f2b-2bad-498b-a9c6-4e9a8c303798"} ``` **Read created resource - GET** We will read the previously created bookmark by issuing an GET request on the url from the `location` header Request: ``` curl -s -X GET "http://localhost:3000/api/personal/users/4c617f2b-2bad-498b-a9c6-4e9a8c303798/bookmarks/5e62b18b59770b5487a4c741" \ -H "accept: application/json" \ -H "Authorization: Bearer eyJhbGciOiJSUzI1NiIsInR5cCIgOi..." | jq . ``` Response: ``` { "tags": [ "rest", "curl", "api", "testing" ], "_id": "5e62b18b59770b5487a4c741", "name": "How to test a REST api from command line with curl – CodepediaOrg", "location": "https://www.codepedia.org/ama/how-to-test-a-rest-api-from-command-line-with-curl/", "language": "en", "description": " In this post I will present how to execute GET, POST, PUT, HEAD, DELETE HTTP Requests against a REST API. For the purpose of this blog post I will be using the REST api that supports [www.codever.dev](https://www.codever.dev)", "descriptionHtml": "In this post I will present how to execute GET, POST, PUT, HEAD, DELETE HTTP Requests against a REST API. For the purpose of this blog post I will be using the REST api that supports <a href=\"https://www.codever.dev\">www.codever.dev</a>", "publishedOn": "2020-03-05T00:00:00.000Z", "sourceCodeURL": "https://github.com/CodeverDotDev/codever", "userId": "4c617f2b-2bad-498b-a9c6-4e9a8c303798", "public": true, "likeCount": 0, "youtubeVideoId": null, "stackoverflowQuestionId": null, "createdAt": "2024-01-22T14:11:44.998Z", "updatedAt": "2024-01-22T14:16:45.998Z" } ``` **Update created resource - PUT** Request: ``` curl -s -X PUT "http://localhost:3000/api/personal/users/4c617f2b-2bad-498b-a9c6-4e9a8c303798/bookmarks/5e62b18b59770b5487a4c741" \ -H "accept: application/json" -H "Authorization: Bearer eyJhbGciOiJSUzI1NiI..." \ -H "Content-Type: application/json" -d "{\"name\":\"How to test a REST api from command line with curl – CodepediaOrg\",\"location\":\"https://www.codepedia.org/ama/how-to-test-a-rest-api-from-command-line-with-curl/\",\"tags\":[\"rest\",\"curl\",\"api\",\"testing\"],\"sourceCodeURL\":\"https://github.com/CodeverDotDev/codever\",\"description\":\"In this post I will present how to execute GET, POST, PUT, HEAD, DELETE HTTP requests against a REST API. For the purpose of this blog post I will be using the REST api that supports [www.codever.dev](https://www.codever.dev)\",\"public\":true,\"readLater\":false,\"language\":\"en\",\"youtubeVideoId\":null,\"stackoverflowQuestionId\":null,\"descriptionHtml\":\"In this post I will present how to execute GET, POST, PUT, HEAD, DELETE HTTP requests against a REST API. For the purpose of this blog post I will be using the REST api that supports <a href=\\\"https://www.codever.dev\\\">www.codever.dev</a>\",\"userId\":\"4c617f2b-2bad-498b-a9c6-4e9a8c303798\",\"_id\":\"5e62b18b59770b5487a4c741\"}" | jq . ``` Response: ``` { "tags": [ "rest", "curl", "api", "testing" ], "_id": "5e62b18b59770b5487a4c741", "name": "How to test a REST api from command line with curl – CodepediaOrg", "location": "https://www.codepedia.org/ama/how-to-test-a-rest-api-from-command-line-with-curl/", "language": "en", "description": "In this post I will present how to execute GET, POST, PUT, HEAD, DELETE HTTP requests against a REST API. For the purpose of this blog post I will be using the REST api that supports [www.codever.dev](https://www.codever.dev)", "descriptionHtml": "In this post I will present how to execute GET, POST, PUT, HEAD, DELETE HTTP requests against a REST API. For the purpose of this blog post I will be using the REST api that supports <a href=\"https://www.codever.dev\">www.codever.dev</a>", "sourceCodeURL": "https://github.com/CodeverDotDev/codever", "userId": "4c617f2b-2bad-498b-a9c6-4e9a8c303798", "public": true, "likeCount": 0, "youtubeVideoId": null, "stackoverflowQuestionId": null, } ``` **Delete created resource - DELETE** Request: ``` curl -i -X DELETE "http://localhost:3000/api/personal/users/4c617f2b-2bad-498b-a9c6-4e9a8c303798/bookmarks/5e62b18b59770b5487a4c741" -H "accept: */*" -H "Authorization: Bearer eyJhbGciOiJS...." ``` Response: ``` HTTP/1.1 204 No Content X-Powered-By: Express Access-Control-Allow-Origin: * Access-Control-Allow-Methods: POST, GET, PUT, PATCH, DELETE, OPTIONS Access-Control-Allow-Headers: Content-Type, Authorization, Location Access-Control-Expose-Headers: Content-Type, Authorization, Location Date: Mon, 22 Jan 2024 15:53:37 GMT Connection: keep-alive } ``` --- ### 1.2 DB (Database) #### Database define A database is a structured collection of information, or data, systematically organized and electronically stored in a computer system. Usually overseen by a database management system (DBMS), the combination of data, DBMS, and associated applications forms a database system. This term is often shortened to "database." In contemporary database types, data is typically arranged in tables with rows and columns, optimizing the processing and querying of information. This tabular structure facilitates easy access, modification, control, and organization of stored data. The widely used Structured Query Language (SQL) is employed in most databases for writing and querying data, providing a standardized and efficient method of interacting with the database. ![image](https://hackmd.io/_uploads/HJeCUurta.png) #### Types of databases There are many different types of databases. The best database for a specific organization depends on how the organization intends to use the data. * Relational databases Relational databases became dominant in the 1980s. Items in a relational database are organized as a set of tables with columns and rows. Relational database technology provides the most efficient and flexible way to access structured information. * Object-oriented databases Information in an object-oriented database is represented in the form of objects, as in object-oriented programming. * Distributed databases A distributed database consists of two or more files located in different sites. The database may be stored on multiple computers, located in the same physical location, or scattered over different networks. * Data warehouses A central repository for data, a data warehouse is a type of database specifically designed for fast query and analysis. * NoSQL databases A NoSQL, or nonrelational database, allows unstructured and semistructured data to be stored and manipulated (in contrast to a relational database, which defines how all data inserted into the database must be composed). NoSQL databases grew popular as web applications became more common and more complex. * Graph databases A graph database stores data in terms of entities and the relationships between entities. These are only a few of the several dozen types of databases in use today. Other, less common databases are tailored to very specific scientific, financial, or other functions. In addition to the different database types, changes in technology development approaches and dramatic advances such as the cloud and automation are propelling databases in entirely new directions. Some of the latest databases include * Open source databases An open source database system is one whose source code is open source; such databases could be SQL or NoSQL databases. * Cloud databases A cloud database is a collection of data, either structured or unstructured, that resides on a private, public, or hybrid cloud computing platform. There are two types of cloud database models: traditional and database as a service (DBaaS). With DBaaS, administrative tasks and maintenance are performed by a service provider. * Multimodel database Multimodel databases combine different types of database models into a single, integrated back end. This means they can accommodate various data types. * Document/JSON database Designed for storing, retrieving, and managing document-oriented information, document databases are a modern way to store data in JSON format rather than rows and columns. * Self-driving databases The newest and most groundbreaking type of database, self-driving databases (also known as autonomous databases) are cloud-based and use machine learning to automate database tuning, security, backups, updates, and other routine management tasks traditionally performed by database administrators. #### Why we need database? Databases are essential for several reasons, playing a crucial role in managing and organizing data efficiently. Here are some key reasons why databases are necessary: * **Organized Storage**: Databases offer a structured framework for efficiently organizing large datasets, using tables, rows, and columns. * **Data Accuracy**: They ensure data integrity by enforcing rules and constraints, preventing errors and maintaining consistency. * **Efficient Retrieval**: Databases enable users to retrieve specific information through queries, facilitating easy access and filtering based on requirements. * **Concurrency Management**: In multi-user settings, databases implement concurrency control, allowing simultaneous data access without compromising integrity. * **Security Measures**: Databases incorporate security features such as access control, encryption, and authentication to protect sensitive data. * **Scalability**: They provide scalability to handle increasing data volumes, supporting organizational growth without compromising performance. * **Consistent Performance**: Databases are designed for consistent performance, ensuring responsiveness despite growing data and user numbers. * **Redundancy and Backup**: They support redundancy and backup mechanisms to safeguard against data loss or corruption, crucial for disaster recovery. #### Connection between API and DB ![image](https://hackmd.io/_uploads/ry6U5_BtT.png) APIs and databases are integral components of contemporary software systems. APIs, or Application Programming Interfaces, facilitate communication between different software elements, while databases store and manage data. APIs often act as intermediaries to enable the retrieval and manipulation of data stored in databases by various applications. When a client application sends a request to an API, the API server processes the request and retrieves the necessary data from the database. The API then sends the data back to the client application in a format easily consumable by the application. This collaborative process ensures a seamless experience for end users. For example, in a mobile application for e-commerce, a user's search query prompts the mobile app to send a request to the API server, which retrieves relevant data from the database and presents it to the user. In the realm of APIs and databases, functions like CRUD (Create, Read, Update, Delete) and HTTP functions play a crucial role in interacting with data. CRUD functions are fundamental for database applications, representing the key operations of creating, reading, updating, and deleting data. In web-based REST APIs, these CRUD functions align with HTTP methods, such as GET, POST, DELETE, PUT, and PATCH. Each CRUD function and its corresponding HTTP method have specific roles in managing and manipulating data within the database. Here is a brief description of each CRUD function and its corresponding HTTP method: ![image](https://hackmd.io/_uploads/rkdpXuwFT.png) ![image](https://hackmd.io/_uploads/SyuFcdSYa.png) Network programming is a specialized field focused on developing code or programs to facilitate communication between computers or external devices through a network, like the internet. It is vital for enabling seamless communication among diverse software components such as client applications, APIs, and databases. APIs serve as intermediaries, streamlining the exchange of data between different software components. When a client application makes a request to an API, the API server processes the request, retrieves necessary data from a database, and returns the data to the client application in a easily consumable format. Two significant protocols in network programming are TCP/IP, a connection-oriented protocol ensuring reliable data delivery, and UDP/IP, a connectionless protocol providing less reliable data delivery. Other widely used protocols include HTTP, HTTPS, and MQTT. Two common programming interfaces in network programming are TLI, offering a high-level interface to network protocols with a more abstracted approach, and Socket API, a low-level interface providing direct access to underlying network protocols, offering more control but increased complexity. In summary, network programming is crucial for constructing modern software systems that heavily rely on APIs and databases. It facilitates smooth communication between software components over a network, supporting collaboration and integration. Moreover, it enables applications to efficiently retrieve and manipulate data in databases, enhancing the overall functionality and effectiveness of contemporary software systems. #### How to install DB ? You must first download mySQL server on your ubuntu, with command: ```= sudo apt update sudo apt install mysql-server ``` The following picture is the successfully installation on ubuntu ![image](https://hackmd.io/_uploads/ryb17-9ta.png) --- ### 1.3 Database best practice. #### Create a database **Step 1** check the mysql version ```= mysql --version ``` ![image](https://hackmd.io/_uploads/rkCIc2sYa.png) **Step 2** Enter MySQL Shell from Ubuntu 20.04 Terminal: ```= sudo mysql ``` When you will run the above-mentioned command, you will immediately enter MySQL shell as shown in the image below: ![image](https://hackmd.io/_uploads/Syanqnitp.png) **Step 3** Now when we are inside the MySQL shell, the first thing that we need to do is to create a database so that we can create tables inside it to perform different operations. A database in MySQL can be created with the following command: ```= mysql> create database DBName; ``` Here, you have to replace DBName with any name that you want for your database. We have named it MyDB. ![image](https://hackmd.io/_uploads/BkxBoniYp.png) **Step 4** When a database with your desired name has been created, you need to switch to that database so that when you will create tables in MySQL, they will be created inside this database. Without selecting a specific database, you are not allowed to create tables in MySQL. For switching to our newly created database, we will run the following command: ```= mysql> use MyDB ``` ![image](https://hackmd.io/_uploads/H1b0inoK6.png) #### Create a table We can create a table in MySQL by running the following command: ```= mysql> CREATE TABLE TableName(Col1 Datatype, Col2 Datatype, ….); ``` We have named it Student. Col1, Col2, etc. represent the names of the columns of your database whereas Datatype refers to the data types of each of these columns respectively. You can create as many columns or attributes for your table as you want. We created three columns named StudentID, StudetName, and StudentAge with the data types int, varchar(255), and int respectively. ![image](https://hackmd.io/_uploads/SkTCfs3K6.png) If this command is executed successfully, you will receive the message shown in the image below on the MySQL shell. ![image](https://hackmd.io/_uploads/S1e_7s3Fp.png) #### Do the insert,update,delete in the table **Insert** Once a table has been created in the MySQL database, we can insert records to this table by executing the following command: ```= mysql> INSERT INTO Student VALUES (1, ‘Sucipto’, 24); ``` You can change these values according to your own choice. ![image](https://hackmd.io/_uploads/Byvw4shYT.png) When our new record will be successfully added to our table, we will receive the message shown in the image below on the MySQL shell. ![image](https://hackmd.io/_uploads/HJYONi2t6.png) In the very same manner, we have inserted another record as shown in the images that follow: ![image](https://hackmd.io/_uploads/H179VshY6.png) **Display** When we have added a few records to our table, it is the right time to view these records. The SELECT statement is used for this purpose. If you want to display all the records or entries of your table in MySQL at once, then you can execute the following command: ```= mysql> SELECT * From Student; ``` ![image](https://hackmd.io/_uploads/HJjlBonYT.png) Executing this command will display all the records of your table in a nice tabular form as shown in the image below: ![image](https://hackmd.io/_uploads/ByGGrs2t6.png) In the very same manner, if you want to display the values of a specific column or multiple columns, then you can replace “*” in the command above with the name of that specific column. **Update** The UPDATE command in MySQL is used to modify the values of any specific record. For example, if you want to change the values of any record of your table, then you have to execute the following command: ```= mysql> UPDATE Student SET StudentName='Saad', StudentAge=24 WHERE StudentID=2; ``` Here, you can replace the value of StudentID with the ID of the record that you want to change. ![image](https://hackmd.io/_uploads/S19hHi2Fp.png) After executing this command, you will receive the message shown in the image below in the MySQL shell. ![image](https://hackmd.io/_uploads/ryHpHo3Y6.png) We can verify if the said changes have taken place or not by using the same SELECT statement once again to view the records of our table. You can see from the following image that our specified record has been updated successfully. ![image](https://hackmd.io/_uploads/S1NRHi2Ya.png) **Alter** The ALTER command in MySQL is used to Add or Drop a specified column from a table. For example, if we want to delete any specific column from our table, then we will execute the following command: ```= mysql> ALTER TABLE Student Drop COLUMN StudentAge; ``` Here, you can replace StudentAge with any column of your choice that you want to drop. ![image](https://hackmd.io/_uploads/BkQSLs3K6.png) After the successful execution of this operation, you will receive the message shown in the image below on the MySQL shell. ![image](https://hackmd.io/_uploads/BJl88ohYp.png) To check if the specified column has been dropped or not, we will run the SELECT command once again. The result is shown in the following image which confirms that the said column has been dropped successfully. ![image](https://hackmd.io/_uploads/B1yPIinFp.png) In the same manner, you can even add a new column to your table by replacing the DROP keyword with ADD and StudentAge with the name of your new column to be added. **Delete a Specific Record** The ALTER statement in MySQL is used to Add or Drop columns. However, if you want to delete a specific record from a table in MySQL, then you have to execute the following command: ```= mysql> DELETE FROM Student WHERE StudentID=1; ``` ![image](https://hackmd.io/_uploads/H1Vn8i3ta.png) If the specified record is deleted successfully, you will receive the message shown in the image below on the MySQL shell. ![image](https://hackmd.io/_uploads/SkDT8snta.png) ![image](https://hackmd.io/_uploads/H1iA8j2Fp.png) **Delete all record** Now, if you want to get rid of all the records of your table in MySQL at once, then you can execute the following command: ```= mysql> DELETE FROM Student; ``` ![image](https://hackmd.io/_uploads/Sk0QDj3KT.png) When this command will execute successfully, you will receive the message shown in the image below on the MySQL shell. ![image](https://hackmd.io/_uploads/SJ9Nvo2Ya.png) Ideally, this command should clear all the entries of your table. This can be verified by running the SELECT command yet again as shown in the following image. The receipt of an Empty Set message indicates that all the records from our specified table have been deleted successfully. ![image](https://hackmd.io/_uploads/S14HPsnKp.png) --- ### 1.4 Experiment (use the api to store data in database.) on progress~~ please wait patiently for further progress :construction_worker: :construction: