ijokarumawak

2015-10-06 18:00:31.874 TRACE 27028 --- [nio-8080-exec-1] c.couchbase.workshop.AirportController   :
SELECT airportname FROM `travel-sample`
  WHERE type = "airport" AND airportname
  LIKE "CENT%" ORDER BY airportname ASC

ijokarumawak

explain SELECT airportname FROM `travel-sample`
  WHERE type = "airport" AND airportname LIKE "CENT%" ORDER BY airportname ASC;

ijokarumawak

# (長いので改行していますが、一行のコマンドです)
$ /Applications/Couchbase\ Server.app/Contents/Resources/couchbase-core/bin/forestdb_dump \
~/Library/Application\ Support/Couchbase/var/lib/couchbase/data/@2i\
/travel-sample_def_airportname_4830792848150130043_0.index/data.fdb.0 |grep "Doc ID"

Doc ID:Centennial
Doc ID:Central Airport
Doc ID:Central Illinois Rgnl
Doc ID:Central Nebraska Regional Airport
Doc ID:Central Station

ijokarumawak

CREATE INDEX def_airportname
  ON travel-sample(`airportname`) USING GSI;

ijokarumawak

CREATE INDEX def_airportname_upper
  ON `travel-sample`(upper(airportname)) USING GSI;

ijokarumawak

N1qlQuery query = N1qlQuery.simple(
	select("airportname").from(i(bucket.name())).where(
		x("type").eq(s("airport"))
			.and(upper(x("airportname")).like(s(prefix.toUpperCase() + "%")))
	).orderBy(asc(x("airportname")))
);

ijokarumawak

@RequestMapping("/routes")
public ResponseEntity<List<String>> routesByAirline(@RequestParam String airline) {
  N1qlQuery query = N1qlQuery.simple(select(
    arrayLength("schedule").as("num_routes"),
    x("sourceairport").as("source"),
    x("destinationairport").as("dest"),
    x("airline")
  ).from(i(bucket.name())).where(
    x("type").eq(s("route"))
    .and(x("airline").eq(s(airline)))

ijokarumawak

{
  "id": 10005,
  "type": "route",
  "airline": "AF",
  "airlineid": "airline_137",
  "sourceairport": "TPE",
  "destinationairport": "MNL",
  "stops": 0,
  "equipment": "777",
  "schedule": [

ijokarumawak

@RequestMapping("/all")
public List<FlightPath> findAll(@RequestParam String from, @RequestParam String to) {
  N1qlQuery query = N1qlQuery.simple(select("a.name", "s.flight", "s.utc", "r.equipment")
    .from(i(bucket.name()).as("r"))
    .unnest("r.schedule").as("s")
    .join(i(bucket.name()).as("a").toString()).onKeys("r.airlineid")
    .where(x("r.sourceairport").eq(s(from)).and(x("r.destinationairport").eq(s(to))))
    .orderBy(asc("s.utc"))
  );

ijokarumawak

public static FlightPath create(JsonObject input, String from, String to) {
  return new FlightPath(
    input.getString("name"),
    input.getString("flight"),
    Arrays.asList(input.getString("equipment").split(" ")),
    input.getString("utc"),
    from,
    to
  );
}