Class: Doraemon::PGSQLDatabase

Inherits:
Object
  • Object
show all
Includes:
Singleton
Defined in:
lib/doraemon/pgsql_database.rb

Instance Method Summary collapse

Constructor Details

#initialize ⇒ PGSQLDatabase

Returns a new instance of PGSQLDatabase.



19
20
21
 
# File 'lib/doraemon/pgsql_database.rb', line 19

def initialize
  self.connect
end

Instance Method Details

#active_scene(uid, scene_id) ⇒ Object 



105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/doraemon/pgsql_database.rb', line 105

def active_scene(uid, scene_id)
  @mutex.lock
  @conn.exec("SELECT * FROM scenes WHERE is_actived = true AND uid = #{uid}") do |result|
    if result.count > 0
      scene = result.first
      @conn.exec("UPDATE scenes SET is_actived = false WHERE id = #{scene['id']}") do |_|
        @conn.exec("UPDATE scenes SET is_actived = true WHERE id = #{scene_id} AND uid = #{uid}") do |result|
          @mutex.unlock
          yield(true) if block_given?
        end
      end
    else
      @conn.exec("UPDATE scenes SET is_actived = true WHERE id = #{scene_id} AND uid = #{uid}") do |result|
        @mutex.unlock
        yield(true) if block_given?
      end
    end
  end
end

#add_scene(uid, name) ⇒ Object

给指定 uid 增加场景



80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/doraemon/pgsql_database.rb', line 80

def add_scene(uid, name)
  @mutex.lock
  @conn.exec("INSERT INTO scenes (name, uid) VALUES ('#{name}', #{uid})") do |_|
    @conn.exec("SELECT CURRVAL(pg_get_serial_sequence('scenes', 'id'))") do |result|
      @mutex.unlock
      id = result.first['currval']
      scene = {id: id, name: name, isActived: false}
      yield(scene) if block_given?
    end
  end
end

#connect ⇒ Object 



14
15
16
17
 
# File 'lib/doraemon/pgsql_database.rb', line 14

def connect
  @conn = PG.connect(dbname: 'doraemon')
  @mutex = Mutex.new
end

#connected? ⇒ Boolean

Returns:

  • (Boolean) 


23
24
25
 
# File 'lib/doraemon/pgsql_database.rb', line 23

def connected?
  @conn != nil
end

#create_api(scene_id, path) ⇒ Object

创建 scene_id 场景下名为 path 的 api



158
159
160
161
162
163
164
165
166
167
168
169
 
# File 'lib/doraemon/pgsql_database.rb', line 158

def create_api(scene_id, path)
  path = path[1, path.length] if path.start_with?('/')
  @mutex.lock
  @conn.exec("INSERT INTO apis (scene_id, path) VALUES (#{scene_id}, '#{path}')") do |_|
    @conn.exec("SELECT CURRVAL(pg_get_serial_sequence('apis', 'id'))") do |result|
      @mutex.unlock
      id = result.first['currval']
      api = {id: id, path: path, enabled: true, contents: ''}
      yield(api) if block_given?
    end
  end
end

#delete_api(id) ⇒ Object

删除 api



172
173
174
175
176
177
178
 
# File 'lib/doraemon/pgsql_database.rb', line 172

def delete_api(id)
  @mutex.lock
  @conn.exec("DELETE FROM apis WHERE id = #{id}") do |result|
    @mutex.unlock
    yield(true) if block_given?
  end
end

#modify_api(id, enabled, path) ⇒ Object

修改 api



181
182
183
184
185
186
187
 
# File 'lib/doraemon/pgsql_database.rb', line 181

def modify_api(id, enabled, path)
  @mutex.lock
  @conn.exec("UPDATE apis SET enabled = #{enabled}, path = '#{path}' WHERE id = #{id}") do
    @mutex.unlock
    yield(true) if block_given?
  end
end

#query_actived_scene(uid) ⇒ Object

查询用户当前激活的场景



204
205
206
207
208
209
210
211
212
 
# File 'lib/doraemon/pgsql_database.rb', line 204

def query_actived_scene(uid)
  @mutex.lock
  @conn.exec("SELECT * FROM scenes WHERE uid = #{uid} AND is_actived = true") do |result|
    @mutex.unlock
    scene = result.first
    scene_id = scene.nil? ? -1 : scene['id'].to_i
    yield(scene_id) if block_given?
  end
end

#query_api_contents(scene_id, path) ⇒ Object

查询 scene_id 场景下指定路径的 api



190
191
192
193
194
195
196
197
198
199
200
201
 
# File 'lib/doraemon/pgsql_database.rb', line 190

def query_api_contents(scene_id, path)
  path = path[1, path.length-1] if path.start_with?('/')
  @mutex.lock
  @conn.exec("SELECT * FROM apis WHERE scene_id = #{scene_id} AND path = '#{path}' AND enabled = true") do |result|
    @mutex.unlock
    api = result.first
    if !api.nil?
      api['contents'] = Base64.strict_decode64(api['contents']).force_encoding("utf-8")
    end
    yield(api) if block_given?
  end
end

#query_apis(scene_id) ⇒ Object

获取所有 api



126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
 
# File 'lib/doraemon/pgsql_database.rb', line 126

def query_apis(scene_id)
  @mutex.lock
  @conn.exec("SELECT * FROM scenes WHERE id = #{scene_id}") do |scene_result|
    name = scene_result.first['name']
    is_actived = scene_result.first['is_actived'] == 't'
    @conn.exec("SELECT * FROM apis WHERE scene_id = #{scene_id} ORDER BY id") do |result|
      @mutex.unlock
      apis = []
      result.each do |x|
        apis << {
          id: x['id'], 
          path: x['path'], 
          enabled: x['enabled'] == 't', 
          contents: x['contents']
        }
      end
      yield(name, is_actived, apis) if block_given?
    end
  end
end

#query_scenes(uid) ⇒ Object

获取对应 uid 的所有场景信息



93
94
95
96
97
98
99
100
101
102
103
 
# File 'lib/doraemon/pgsql_database.rb', line 93

def query_scenes(uid)
  @mutex.lock
  @conn.exec("SELECT * FROM scenes WHERE uid = #{uid} ORDER BY id") do |result|
    @mutex.unlock
    scenes = []
    result.each do |x|
      scenes << {id: x['id'], name: x['name'], isActived: x['is_actived'] == 't'}
    end
    yield(scenes) if block_given?
  end
end

#query_user(username) ⇒ Object

使用用户名查询端口



50
51
52
53
54
55
56
57
58
59
60
61
62
 
# File 'lib/doraemon/pgsql_database.rb', line 50

def query_user(username)
  @mutex.lock
  @conn.exec("SELECT * FROM users WHERE username = '#{username}'") do |result|
    @mutex.unlock
    if result.count > 0
      user = result.first
      yield({port: user['port'].to_i, username: username, uid: user['uid'].to_i}) if block_given?
    else
      puts "#{username} not register yet."
      yield if block_given?
    end
  end
end

#query_user_with_uid(uid) ⇒ Object

使用用户 id 查询用户信息



65
66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/doraemon/pgsql_database.rb', line 65

def query_user_with_uid(uid)
  @mutex.lock
  @conn.exec("SELECT * FROM users WHERE uid = #{uid}") do |result|
    @mutex.unlock
    if result.count > 0
      user = result.first
      yield({port: user['port'].to_i, username: user['username'], uid: user['uid'].to_i}) if block_given?
    else
      puts "UID [#{uid}] not register yet."
      yield if block_given?
    end
  end
end

#register_user(username) ⇒ Object

注册一个用户



28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
# File 'lib/doraemon/pgsql_database.rb', line 28

def register_user(username)
  @mutex.lock
  @conn.exec("SELECT uid FROM users WHERE username = '#{username}'") do |result|
    if result.count > 0
      @mutex.unlock
      yield(false) if block_given?
    else
      @conn.exec("SELECT next_port FROM configure WHERE id = 1") do |result|
        port = result.first['next_port'].to_i
        reg_tm = (Time.now.to_f * 1000).to_i
        @conn.exec("INSERT INTO users (username, port, reg_tm) VALUES ('#{username}', #{port}, #{reg_tm})") do |_|
          @conn.exec("UPDATE configure SET next_port = #{port+1} WHERE id = 1") do |_|
            @mutex.unlock
            yield(true) if block_given?
          end
        end
      end
    end
  end
end

#save_apis(scene_id, apis) {|true| ... } ⇒ Object

保存 scene_id 场景下的所有 api

Yields:

  • (true) 


148
149
150
151
152
153
154
155
 
# File 'lib/doraemon/pgsql_database.rb', line 148

def save_apis(scene_id, apis)
  apis.each do |api|
    contents = Base64.strict_encode64(api['contents'])
    id = api['id']
    @conn.sync_exec("UPDATE apis SET contents = '#{contents}' WHERE id = #{id}")
  end
  yield(true) if block_given?
end