PostgreSQL Adapter

PostgreSQL Adapter

Quick Start:

• Step 1: insert data into MySQL

  ~/> cd ~/cloudberry/examples/twittermap
  ~/twittermap> ./script/dockerRunPostgreSqlDB.sh
  

• Step 2: setup config file in Cloudberry —— change copy the twittermap/web/cloudberry-postgresqldb.conf's content into cloudberry/neo/conf/application.conf and cover the original codes.

• Step 3: run cloudberry

  ~/> cd ~/cloudberry/cloudberry
  ~/cloudberry> sbt "project neo" "run"
  

• Step 4: change config file in Twittermap

  • change # sqlDB = PostgreSQL into sqlDB = PostgreSQL;
  • change sql = MySQL into # sql = MySQL.

• Step 5: run Twittermap

  ~/> cd ~/cloudberry/examples/twittermap
  ~/twittermap> sbt "project web" "run 9001"
  

• Step 6: PostgreSqlMigration file would register schemas by sending them to cloudberry, the services-postgres.js would send send request to cloudberry.

• Step 7: PostgreSqlGenerator would translate the request query into PostgreSQL query, and send it to PostgresConn, the result would be send back to Twittermap. The website can be visited on http://localhost:9001

The Schema:

Since PostgreSQL has Array type, we added hashtags and user_mentions back to psql's schema.

Cloudberry Middleware:

PostgreSqlGenerator’s diff from MySqlGenerator:

• quote: ` -> "
• full text search syntax (differ in parseTextRelation)
• insert & replace syntax (differ in parseCreate, parseUpsertMeta, parseAppend)
• SqlDataType (differ in fieldType2MySQLType):
  • tinyint -> smallint,
  • datetime -> timestamp
• support Bag type (i.e. support hashtags as array format)
  • added parseUnnest
  • added case “Bag" in fieldType2MySQLType
• different geo format (differ in parseGeoCell):
  • can return as an array, complicated syntax in MySQL like st_asText, st_fromGeoText can be removed.

Connector:

• only differs in url

Backend TwitterDataStore's schema:

• added hashtags & user_mentions

Twittermap:

Front-end common/service-postgresql.js:

• added byHashTagRequest

Frontend schema in MySqlMigration:

• added hashtags & user_mentions

Configuration:

• Same change as MySQL.

Docker Containers:

• postgres-container: ./script/dockerRunPostgreSqlDB.sh in twittermap would create postgres-container from postgres image.
• postgres-sample-tweet: ./script/dockerRunPostgreSqlDB.sh would create postgres-sample-tweet from spittet/php-postgres image.