4.10. Let’s create micro-blog#

Let’s create micro-blog with full text search by Groonga. Micro-blog is one of the broadcast medium in the forms of blog. It is mainly used to post small messages like a Twitter.

4.10.1. Create a table#

Let’s create table.

table_create --name Users --flags TABLE_HASH_KEY --key_type ShortText
table_create --name Comments --flags TABLE_HASH_KEY --key_type ShortText
table_create --name HashTags --flags TABLE_HASH_KEY --key_type ShortText
table_create --name Bigram --flags TABLE_PAT_KEY --key_type ShortText --default_tokenizer TokenBigram --normalizer NormalizerAuto
table_create --name GeoIndex --flags TABLE_PAT_KEY --key_type WGS84GeoPoint

column_create --table Users --name name --flags COLUMN_SCALAR --type ShortText
column_create --table Users --name follower --flags COLUMN_VECTOR --type Users
column_create --table Users --name favorites --flags COLUMN_VECTOR --type Comments
column_create --table Users --name location --flags COLUMN_SCALAR --type WGS84GeoPoint
column_create --table Users --name location_str --flags COLUMN_SCALAR --type ShortText
column_create --table Users --name description --flags COLUMN_SCALAR --type ShortText
column_create --table Users --name followee --flags COLUMN_INDEX --type Users --source follower

column_create --table Comments --name comment --flags COLUMN_SCALAR --type ShortText
column_create --table Comments --name last_modified --flags COLUMN_SCALAR --type Time
column_create --table Comments --name replied_to --flags COLUMN_SCALAR --type Comments
column_create --table Comments --name replied_users --flags COLUMN_VECTOR --type Users
column_create --table Comments --name hash_tags --flags COLUMN_VECTOR --type HashTags
column_create --table Comments --name location --flags COLUMN_SCALAR --type WGS84GeoPoint
column_create --table Comments --name posted_by --flags COLUMN_SCALAR --type Users
column_create --table Comments --name favorited_by --flags COLUMN_INDEX --type Users --source favorites

column_create --table HashTags --name hash_index --flags COLUMN_INDEX --type Comments --source hash_tags

column_create --table Bigram --name users_index --flags COLUMN_INDEX|WITH_POSITION|WITH_SECTION --type Users --source name,location_str,description
column_create --table Bigram --name comment_index --flags COLUMN_INDEX|WITH_POSITION --type Comments --source comment

column_create --table GeoIndex --name users_location --type Users --flags COLUMN_INDEX --source location
column_create --table GeoIndex --name comments_location --type Comments --flags COLUMN_INDEX --source location

4.10.1.1. Users table#

This is the table which stores user information. It stores name of user, profile, list of follower and so on.

_key

User ID

name

User name

follower

List of following users

favorites

List of favorite comments

location

Current location of user (geolocation)

location_str

Current location of user (string)

description

User profile

followee

Indexes for follower column in Users table. With this indexes, you can search users who follows the person.

4.10.1.2. Comments table#

This is the table which stores comments and its metadata. It stores content of comment, posted date, comment which reply to, and so on.

_key

Comment ID

comment

Content of comment

last_modified

Posted date

replied_to

Comment which you reply to someone

replied_users

List of users who you reply to

hash_tags

List of hash tags about comment

location

Posted place (for geolocation)

posted_by

Person who write comment

favorited_by

Indexes for favorites column in Users table. With this indexes, you can search the person who mark comment as favorite one.

4.10.1.3. HashTags table#

This is the table which stores hash tags for comments.

_key

Hash tag

hash_index

Indexes for Comments.hash_tags. With this indexes, you can search list of comments with specified hash tags.

4.10.1.4. Bigram table#

This is the table which stores indexes for full text search by user information or comments.

_key

Word

users_index

Indexes of user information. This column contains indexes of user name (Users.name), current location (Users.location_str), profile (Users.description).

comment_index

Indexes about content of comments (Comments.comment).

4.10.1.5. GeoIndex table#

This is the table which stores indexes of location column to search geo location effectively.

users_location

Indexes of location column for Users table

comments_location

Indexes of location column for Comments table

4.10.2. Loading data#

Then, load example data.

load --table Users
[
  {
    "_key": "alice",
    "name": "Alice",
    "follower": ["bob"],
    "favorites": [],
    "location": "152489000x-255829000",
    "location_str": "Boston, Massachusetts",
    "description": "Groonga developer"
  },
  {
    "_key": "bob",
    "name": "Bob",
    "follower": ["alice","charlie"],
    "favorites": ["alice:1","charlie:1"],
    "location": "146249000x-266228000",
    "location_str": "Brooklyn, New York City",
    "description": ""
  },
  {
    "_key": "charlie",
    "name": "Charlie",
    "follower": ["alice","bob"],
    "favorites": ["alice:1","bob:1"],
    "location": "146607190x-267021260",
    "location_str": "Newark, New Jersey",
    "description": "Hmm,Hmm"
  }
]

load --table Comments
[
  {
    "_key": "alice:1",
    "comment": "I've created micro-blog!",
    "last_modified": "2010/03/17 12:05:00",
    "posted_by": "alice",
  },
  {
    "_key": "bob:1",
    "comment": "First post. test,test...",
    "last_modified": "2010/03/17 12:00:00",
    "posted_by": "bob",
  },
  {
    "_key": "alice:2",
    "comment": "@bob Welcome!!!",
    "last_modified": "2010/03/17 12:05:00",
    "replied_to": "bob:1",
    "replied_users": ["bob"],
    "posted_by": "alice",
  },
  {
    "_key": "bob:2",
    "comment": "@alice Thanks!",
    "last_modified": "2010/03/17 13:00:00",
    "replied_to": "alice:2",
    "replied_users": ["alice"],
    "posted_by": "bob",
  },
  {
    "_key": "bob:3",
    "comment": "I've just used 'Try-Groonga' now! #groonga",
    "last_modified": "2010/03/17 14:00:00",
    "hash_tags": ["groonga"],
    "location": "146566000x-266422000",
    "posted_by": "bob",
  },
  {
    "_key": "bob:4",
    "comment": "I'm come at city of New York for development camp! #groonga #travel",
    "last_modified": "2010/03/17 14:05:00",
    "hash_tags": ["groonga", "travel"],
    "location": "146566000x-266422000",
    "posted_by": "bob",
  },
  {
    "_key": "charlie:1",
    "comment": "@alice @bob I've tried to register!",
    "last_modified": "2010/03/17 15:00:00",
    "replied_users": ["alice", "bob"],
    "location": "146607190x-267021260",
    "posted_by": "charlie",
  }
  {
    "_key": "charlie:2",
    "comment": "I'm at the Museum of Modern Art in NY now!",
    "last_modified": "2010/03/17 15:05:00",
    "location": "146741340x-266319590",
    "posted_by": "charlie",
  }
]

follower column and favorites column in Users table and replied_users column in Comments table are vector column, so specify the value as an array.

location column in Users table, location column in Comments table use GeoPoint type. This type accepts “[latitude]x[longitude]”.

last_modified column in Comments table use Time type.

There are two way to specify the value. First, specify epoch (seconds since Jan, 1, 1970 AM 00:00:00) directly. In this case, you can specify micro seconds as fractional part. The value is converted from factional part to the time which is micro seconds based one when data is loaded. The second, specify the timestamp as string in following format: “(YEAR)/(MONTH)/(DAY) (HOUR):(MINUTE):(SECOND)”. In this way, the string is casted to proper micro seconds when data is loaded.