# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3 (ipykernel)
#     language: python
#     name: python3
# ---

# ## N1QL 
# N1QL (pronounced “nickel”) is Couchbase’s next-generation query language. N1QL aims to meet the query needs of distributed document-oriented databases.
#
# The N1QL data model derives its name from the non-first normal form, which is a superset and generalization of the relational first normal form (1NF).
#
# N1QL is a JSON query language for executing industry-standard ANSI joins and querying, transforming, and manipulating JSON data – just like SQL. With native support for N1QL, Couchbase allows you to visualize and optimize complex query plans for large datasets, deliver the best performance at any scale, and meet the demands of millions of users.

# ## Running Queries
# N1QL queries can be executed in the following ways:
# - The Couchbase Query Workbench (in the Web Console)
# - The Command-Line based Query Shell (cbq)
# - Our REST API
# - Any of our Language SDKs, including Python (which we’ll focus on today).

# ## Notes
# - In Couchbase, you need indexes to run N1QL queries. In the case of `travel-sample` data, the indexes are created for you when you import the sample bucket.
# - The queries are executed lazily. Unless the Query Results are processed, the query might not have been executed. You can do that by iterating over the results. 
# - Another option to execute the queries is to use the `execute()` method on Query object.

# needed to support SQL++ (N1QL) query
from couchbase.cluster import (
    Cluster,
    ClusterOptions,
    QueryOptions,
    QueryScanConsistency,
)
from couchbase_core.cluster import PasswordAuthenticator

# get a reference to our cluster
cluster = Cluster(
    "couchbase://localhost",
    ClusterOptions(PasswordAuthenticator("<PASSWORD>", "Password")),
)

# get a reference to our bucket
bucket = cluster.bucket("travel-sample")

# +
import pprint

pp = pprint.PrettyPrinter(indent=4, depth=6)
# -

# ## cluster.query() Method
# The `query()` method in the Couchbase Cluster object can be used to run N1QL queries using the Python SDK. 
# It returns the data returned by the query or an error if the query is not successful.
#
# [`QueryOptions()`](https://docs.couchbase.com/python-sdk/current/howtos/n1ql-queries-with-sdk.html#query-options) can be used to specify options for the query like metrics, timeout, scan consistency, query parameters, query context, etc. 
#
# The results are returned as an iterator and can be processed as needed.

# +
# Select Statement
try:
    result = cluster.query(
        "SELECT * FROM `travel-sample`.inventory.airport LIMIT 10",
        QueryOptions(metrics=True),
    )

    for row in result.rows():
        pp.pprint(row)

    print(f"Query Execution time: {result.metadata().metrics().execution_time()}")

except CouchbaseException as ex:
    print(ex)
# -

# ## Queries and Placeholders
# Placeholders allow you to specify variable constraints for an otherwise constant query. There are two variants of placeholders: postional and named parameters. Positional parameters use an ordinal placeholder for substitution and named parameters use variables. A named or positional parameter is a placeholder for a value in the WHERE, LIMIT or OFFSET clause of a query.
#
# You can specify the query parameters either as a part of the query or as a `QueryOptions` object.
#
# The main difference between the positional parameters and the named parameters are in the way the parameters are mentioned in the query. Named parameters refer to the variables specified in the query while the positional parameters are always referred to by the order in which they are specified. The results do not change as the query is the same.

# ## Positional Parameters

# +
# The positional parameters are replaced in the order in which they are specified.
query = "SELECT a.airportname, a.city FROM `travel-sample`.inventory.airport a where country=$1 AND city=$2"
try:
    result = cluster.query(query, "United Kingdom", "London")
    # Each row is one document
    for row in result:
        print(f"Airport: {row['airportname']}, City: {row['city']}")

except Exception as e:
    print(e)

# +
query = "SELECT a.airportname, a.city FROM `travel-sample`.inventory.airport a where country=$1 AND city=$2"
try:
    result = cluster.query(
        query, QueryOptions(positional_parameters=["United Kingdom", "London"])
    )

    for row in result:
        print(f"Airport: {row['airportname']}, City: {row['city']}")

except Exception as e:
    print(e)
# -

# ## Named Parameters

# +
# The named parameters are replaced by the name specified in the query
query = "SELECT a.airportname, a.city FROM `travel-sample`.inventory.airport a where country=$country AND city=$city"
try:
    result = cluster.query(query, country="United Kingdom", city="London")

    for row in result:
        print(f"Airport: {row['airportname']}, City: {row['city']}")

except Exception as e:
    print(e)

# +
query = "SELECT a.airportname, a.city FROM `travel-sample`.inventory.airport a where country=$country AND city=$city"
try:
    result = cluster.query(
        query,
        QueryOptions(named_parameters={"country": "United Kingdom", "city": "London"}),
    )

    for row in result:
        print(f"Airport: {row['airportname']}, City: {row['city']}")

except Exception as e:
    print(e)
# -

# ## Query Metrics
# The performance & metadata about a query can be measured using the optional `metrics` parameter in the QueryOptions

query = "SELECT a.airportname, a.city FROM `travel-sample`.inventory.airport a where country=$country AND city=$city LIMIT 4"
try:
    result = cluster.query(
        query,
        QueryOptions(named_parameters={"country": "United Kingdom", "city": "London"}),
        metrics=True,
    )
    print("Results")
    print("------")
    for row in result:
        print(f"Airport: {row['airportname']}, City: {row['city']}")
    print("------")
    print(f"Query Metrics: {result.metrics}")
    print("------")
except Exception as e:
    print(e)

# ## Scan Consistency
# By default, the query engine will return whatever is currently in the index at the time of query (this mode is also called `QueryScanConsistency.NOT_BOUNDED`). If you need to include everything that has just been written, a different scan consistency must be chosen. If `QueryScanConsistency.REQUEST_PLUS` is chosen, it will likely take a bit longer to return the results but the query engine will make sure that it is as up-to-date as possible.

result = cluster.query(
    "SELECT ts.* FROM `travel-sample`.inventory.airline ts LIMIT 10",
    QueryOptions(scan_consistency=QueryScanConsistency.REQUEST_PLUS, metrics=True),
)
for row in result:
    pp.pprint(row)
print("Query Metrics:", result.metrics)

# ## Create, Read, Update, Delete (CRUD) Operations
# The most common operations in applications using Database systems are the CRUD operations. Most of the web applications are composed of these fundamental CRUD operations. 
#
# These statements are similar to SQL.

# ## Insert
# Use the INSERT statement to insert one or more new documents into an existing keyspace. Each INSERT statement requires a unique document key and well-formed JSON as values. In Couchbase, documents in a single keyspace must have a unique key.
#
# The key represents the ID of the document to be inserted. It cannot be Missing or Null & must be Unique across all the documents in the collection.

insert_statement = 'INSERT INTO `travel-sample`.inventory.hotel (KEY, VALUE) VALUES ("key1", { "type" : "hotel", "name" : "new hotel" })'
try:
    result = cluster.query(insert_statement).execute()
except Exception as e:
    print(e)

# Fetch the inserted document
result = cluster.query(
    "SELECT * from `travel-sample`.inventory.hotel where name='new hotel'"
)
try:
    for row in result:
        print(row)
except Exception as e:
    print(e)

# ## Upsert
# The UPSERT statement is used if you want to overwrite a document with the same key, in case it already exists. In case the document does not exist, a new document is created with the specified key.

upsert_statement = 'UPSERT INTO `travel-sample`.inventory.hotel (KEY, VALUE) VALUES ("key1", { "type" : "hotel", "name" : "new hotel", "city":"Manchester"})'
try:
    result = cluster.query(upsert_statement).execute()
    for row in result:
        print(row)
except Exception as e:
    print(e)

# ## Exercise 4.1
# - Fetch the upserted document

# +
# Solution
# -

# ## Update
# UPDATE replaces a document that already exists with updated values.
#
# You can use the RETURNING clause to return specific information as part of the query.

update_statement = "UPDATE `travel-sample`.inventory.hotel USE KEYS 'key1' UNSET city RETURNING hotel.name"
try:
    result = cluster.query(update_statement)
    for row in result:
        print(row)
except Exception as e:
    print(e)

# ## Exercise 4.2
# 1. Fetch the updated document
# 2. Update the hotel name to "New Hotel International"

# +
# Fetch the updated document
# -

# Update the hotel name to "New Hotel International"
update_statement = "UPDATE `travel-sample`.inventory.hotel USE KEYS 'key1' SET name = 'New Hotel International' RETURNING hotel.name"
try:
    result = cluster.query(update_statement)
    for row in result:
        print(row)
except Exception as e:
    print(e)

# ## Delete
# DELETE immediately removes the specified document from your keyspace.

delete_statement = (
    "DELETE FROM `travel-sample`.inventory.hotel h USE KEYS 'key1' RETURNING h"
)
try:
    result = cluster.query(delete_statement)
    for row in result:
        print(f"Deleted Row: {row}")
except Exception as e:
    print(e)

# ## Exercise 4.3
# - Check if the deleted record exists

# +
# Solution
# -

# ## Select
# The SELECT statement takes a set of JSON documents from keyspaces as its input, manipulates it and returns a set of JSON documents in the result array. Since the schema for JSON documents is flexible, JSON documents in the result set have flexible schema as well.
#
# A simple query in N1QL consists of three parts:
#
# - SELECT: specifies the projection, which is the part of the document that is to be returned.
#
# - FROM: specifies the keyspaces(bucket, scope, collection) to work with.
#
# - WHERE: specifies the query criteria (filters or predicates) that the results must satisfy.
#
# To query on a keyspace, you must either specify the document keys or use an index on the keyspace.
#

# +
# Select All Airlines in the Database with Country "United Kingdom"
uk_airlines = (
    "SELECT * from `travel-sample`.inventory.airline where country='United Kingdom'"
)

try:
    result = cluster.query(uk_airlines)
    for row in result:
        pp.pprint(row)
except Exception as e:
    print(e)
# -

# Select Just Airline Name & ICAO Codes for Airlines
uk_airlines = "SELECT a.name, a.icao from `travel-sample`.inventory.airline a where country='United Kingdom'"
try:
    result = cluster.query(uk_airlines)
    for row in result:
        pp.pprint(row)
except Exception as e:
    print(e)

# ## Limit Results using OFFSET & LIMIT
# The LIMIT clause specifies the maximum number of documents to be returned in a resultset by a SELECT statement.
#
# When you don’t need the entire resultset, use the LIMIT clause to specify the maximum number of documents to be returned in a resultset by a SELECT query.
#
# The OFFSET clause specifies the number of resultset objects to skip in a SELECT query.
#
# When you want the resultset to skip over the first few resulting objects, use the OFFSET clause to specify that number of objects to ignore.
#
# The LIMIT and OFFSET clauses are evaluated after the ORDER BY clause.
#
# If a LIMIT clause is also present, the OFFSET is applied prior to the LIMIT; that is, the specified number of objects is omitted from the result set before enforcing a specified LIMIT.

# +
# Select Airline Name & ICAO Codes for 10 Airlines by ICAO Code
uk_airlines = "SELECT a.name, a.icao from `travel-sample`.inventory.airline a where country='United Kingdom' ORDER BY icao LIMIT 5"
try:
    result = cluster.query(uk_airlines)
    print("Initial 5 Records")
    for row in result:
        pp.pprint(row)
except Exception as e:
    print(e)

uk_airlines = "SELECT a.name, a.icao from `travel-sample`.inventory.airline a where country='United Kingdom' ORDER BY icao LIMIT 5 OFFSET 5"
try:
    result = cluster.query(uk_airlines)
    print("Next 5 Records")
    for row in result:
        pp.pprint(row)
except Exception as e:
    print(e)
# -

# ## Aggregate Functions
# Aggregate functions take multiple values from documents, perform calculations, and return a single value as the result. The function names are case insensitive.
#
# You can only use aggregate functions in SELECT, LETTING, HAVING, and ORDER BY clauses. When using an aggregate function in a query, the query operates as an aggregate query.
#

# +
# Get the Count of Airlines per Country
airline_counts = "SELECT COUNT(DISTINCT a.icao) AS airline_count, a.country \
    FROM `travel-sample`.inventory.airline a \
    GROUP BY a.country"

try:
    result = cluster.query(airline_counts)
    for row in result:
        print(row)
except Exception as e:
    print(e)

# +
# Get the Cities with more than 150 Landmarks
city_landmarks = "SELECT city City, COUNT(DISTINCT name) LandmarkCount \
    FROM `travel-sample`.inventory.landmark \
    GROUP BY city \
    HAVING COUNT(DISTINCT name) > 150"

try:
    result = cluster.query(city_landmarks)
    for row in result:
        print(row)
except Exception as e:
    print(e)
# -

# ## Exercise 4.4
# - Get the Count of Airports per Country sorted in descending order of Airports

# +
# Solution
# -

# ## Joins
# N1QL provides joins, which allow you to assemble new objects by combining two or more source objects.
#

# +
# Join the Airport Object with Destination Airport in Routes from SFO
join_example = "SELECT * \
    FROM `travel-sample`.inventory.route AS rte \
        JOIN `travel-sample`.inventory.airport AS apt ON rte.destinationairport = apt.faa \
    WHERE rte.sourceairport='SFO' \
    LIMIT 5"

try:
    result = cluster.query(join_example)
    for row in result:
        pp.pprint(row)
except Exception as e:
    print(e)

# +
# Join Airlines with the Routes using the Airline ID
join_example2 = 'SELECT * \
    FROM `travel-sample`.inventory.route \
    JOIN `travel-sample`.inventory.airline \
    ON route.airlineid = META(airline).id \
    WHERE airline.country = "France" \
    LIMIT 3'

try:
    result = cluster.query(join_example2)
    for row in result:
        pp.pprint(row)
except Exception as e:
    print(e)

# +
# Find the destination airport of all routes whose source airport is in San Francisco
# Join using sub query
join_example3 = 'SELECT DISTINCT subquery.destinationairport \
    FROM `travel-sample`.inventory.airport \
    JOIN ( \
      SELECT destinationairport, sourceairport \
      FROM `travel-sample`.inventory.route \
    ) AS subquery \
    ON airport.faa = subquery.sourceairport \
    WHERE airport.city = "San Francisco"\
    LIMIT 10'

try:
    result = cluster.query(join_example3)

    for row in result:
        print(row)

except Exception as e:
    print(e)
# -

# ## Exercise 4.5
# 1. Select the Airline Names & ICAO Codes for Airlines operating from France
# 2. Get the Count of Landmarks By Country
# 3. Find the source airport of all routes whose destination airport is in San Francisco
#

# +
# Solution 1

# +
# Solution 2

# +
# Solution 3
# -

# ## Array Operations
# Couchbase supports arrays as part of the documents and also provides a rich set of operations to work with arrays. 

# ## NEST
# NEST performs a join across two buckets. But instead of producing an object for each combination of left and right hand inputs, NEST produces a single object for each left hand input, while the corresponding right hand inputs are collected into an array and nested as a single array-valued field in the result object.
#

# +
# Nesting landmarks with the airport & routes
nest_query = "SELECT * \
    FROM `travel-sample`.inventory.route AS rte \
    JOIN `travel-sample`.inventory.airport AS apt \
      ON rte.destinationairport = apt.faa \
    NEST `travel-sample`.inventory.landmark AS lmk \
      ON apt.city = lmk.city \
    LIMIT 2"

try:
    result = cluster.query(nest_query)

    for row in result:
        pp.pprint(row)

except Exception as e:
    print(e)
# -

# ## UNNEST
# UNNEST allow you to take the contents of nested arrays and join them with their parent object.

# +
# Iterate over the reviews array and collects the author names of the reviewers who rated the rooms less than a 2
unnest_example = "SELECT RAW r.author \
    FROM `travel-sample`.inventory.hotel \
    UNNEST reviews AS r \
    WHERE r.ratings.Rooms < 2 \
    LIMIT 4"

try:
    result = cluster.query(unnest_example)
    for row in result:
        print(row)
except Exception as e:
    print(e)
# -

# ## Transactions
# A transaction is an atomic unit of work that contains one or more operations. It is a group of operations that are either committed to the database together or they are all undone from the database.
#
# Couchbase Supports Distributed ACID Transactions using N1QL. It is currently available for use with the Java, .NET & C++ SDK. It will be added to the Python SDK in the future.
#
# More details about Transactions in Couchbase including samples in Java, you can refer to the [documentation](https://docs.couchbase.com/server/current/learn/data/transactions.html).

# ## References
# - [N1QL Reference](https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/index.html)
# - [N1QL Tutorial](https://query-tutorial.couchbase.com/tutorial/#1)
# - [N1QL Queries from Python SDK](https://docs.couchbase.com/python-sdk/current/howtos/n1ql-queries-with-sdk.html)
# - [JOINs in N1QL](https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/join.html)
# - [N1QL Cheatsheet](https://docs.couchbase.com/files/Couchbase-N1QL-CheatSheet.pdf)
# - [Transactions in Java](https://docs.couchbase.com/java-sdk/current/howtos/distributed-acid-transactions-from-the-sdk.html)

StarcoderdataJupyterScriptsDedupFiltered