Top Level Namespace

Defined Under Namespace

Modules: Cetusql Classes: Database

Constant Summary collapse

KEY_PGDN = 
"[6~"
KEY_PGUP = 
"[5~"
KEY_HOME =

in Vim Home comes as ^[OH whereas on the command line it is correct as ^[[H

''
KEY_END = 
""
KEY_F1 = 
"OP"
KEY_UP = 
""
KEY_DOWN = 
""
KEY_LEFT = 
''
KEY_RIGHT = 
''
KEY_F5 = 
'[15~'
KEY_F6 = 
'[17~'
KEY_F7 = 
'[18~'
KEY_F8 = 
'[19~'
KEY_F9 = 
'[20~'
KEY_F10 = 
'[21~'
KEY_S_F1 = 
''
GMARK =

CONSTANTS

'*'
CURMARK = 
'>'
SPACE = 
" "
CLEAR = 
"\e[0m"
BOLD = 
"\e[1m"
BOLD_OFF = 
"\e[22m"
RED = 
"\e[31m"
ON_RED = 
"\e[41m"
GREEN = 
"\e[32m"
YELLOW = 
"\e[33m"
BLUE = 
"\e[1;34m"
ON_BLUE = 
"\e[44m"
REVERSE = 
"\e[7m"
UNDERLINE = 
"\e[4m"
CURSOR_COLOR =

CURSOR_COLOR = ON_BLUE

CLEAR

Instance Method Summary collapse

Instance Method Details

#agree(prompt = "") ⇒ Object Also known as: confirm 



313
314
315
316
317
 
# File 'lib/cetusql/cli_utils.rb', line 313

def agree(prompt="")
  x = input(prompt)
  return true if x.upcase == "Y"
  false
end

#calculate_column_width(content, col, maxrows = 99) ⇒ Object 



200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/cetusql/cli_sqlite.rb', line 200

def calculate_column_width content, col, maxrows=99
  ret = 1
  ctr = 0
  content.each_with_index { |r, i| 
    break if ctr > maxrows
    ctr += 1
    c = r[col]
    x = c.to_s.length
    ret = x if x > ret
  }
  ret
end

#calculate_column_widths(content, maxrows = 99) ⇒ Object 



195
196
197
198
199
 
# File 'lib/cetusql/cli_sqlite.rb', line 195

def calculate_column_widths content, maxrows=99
  widths = []
  content.first.each_with_index {|r,i| widths << calculate_column_width(content, i, maxrows) }
  return widths
end

#columnate(ary, sz) ⇒ Object

sz - lines in one column



325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
 
# File 'lib/cetusql/cli_utils.rb', line 325

def columnate ary, sz
  buff=Array.new
  return buff if ary.nil? || ary.size == 0
  
  # determine width based on number of files to show
  # if less than sz then 1 col and full width
  #
  wid = 30
  ars = ary.size
  ars = [$pagesize, ary.size].min
  d = 0
  if ars <= sz
    wid = $gcols - d
  else
    tmp = (ars * 1.000/ sz).ceil
    wid = $gcols / tmp - d
  end
  #elsif ars < sz * 2
    #wid = $gcols/2 - d
  #elsif ars < sz * 3
    #wid = $gcols/3 - d
  #else
    #wid = $gcols/$gviscols - d
  #end

  # ix refers to the index in the complete file list, wherease we only show 60 at a time
  ix=0
  while true
    ## ctr refers to the index in the column
    ctr=0
    while ctr < sz

      f = ary[ix]
      fsz = f.size
      if fsz > wid
        f = f[0, wid-2]+"$ "
        ## we do the coloring after trunc so ANSI escpe seq does not get get
        if ix + $sta == $cursor
          f = "#{CURSOR_COLOR}#{f}#{CLEAR}"
        end
      else
        ## we do the coloring before padding so the entire line does not get padded, only file name
        if ix + $sta == $cursor
          f = "#{CURSOR_COLOR}#{f}#{CLEAR}"
        end
        #f = f.ljust(wid)
        f << " " * (wid-fsz)
      end

      if buff[ctr]
        buff[ctr] += f
      else
        buff[ctr] = f
      end

      ctr+=1
      ix+=1
      break if ix >= ary.size
    end
    break if ix >= ary.size
  end
  return buff
end

#connect(dbname = nil) ⇒ Object

connect to given database, and if no name supplied then allow user to choose



27
28
29
30
31
32
33
34
 
# File 'lib/cetusql/cli_sqlite.rb', line 27

def connect dbname=nil
  dbname ||= getdbname
  return nil unless dbname
  #$log.debug "XXX:  CONNECT got #{dbname} "
  $current_db = dbname
  $db = SQLite3::Database.new(dbname) if dbname
  return $db
end

#ctrlp(arr) ⇒ Object

allows user to select from list, returning string if user pressed ENTER

Aborts if user presses Q or C-c or ESCAPE



218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
 
# File 'lib/cetusql/cli_utils.rb', line 218

def ctrlp arr
  patt = nil
  curr = 0
  while true
    # clear required otherwise will keep drawing itself
    system("clear")
    if patt and patt != ""
      # need fuzzy match here
      view = arr.grep(/^#{patt}/)
      view = view | arr.grep(/#{patt}/)
      fuzzypatt = patt.split("").join(".*")
      view = view | arr.grep(/#{fuzzypatt}/)
    else
      view = arr
    end
    curr = [view.size-1, curr].min
    # if empty then curr becomes -1
    curr = 0 if curr < 0
    view.each_with_index do |a, i|
      mark = " "
      mark = ">" if curr == i
      print "#{mark}  #{a} \n"
    end
    #puts " "
    print "\r#{patt} >"
    ch = get_char
    if  ch =~ /^[a-z]$/
      patt ||= ""
      patt << ch
    elsif ch == "BACKSPACE"
      if patt && patt.size > 0
        patt = patt[0..-2]
      end
    elsif ch == "Q" or ch == "C-c" or ch == "ESCAPE"
      break
    elsif ch == "UP"
      curr -= 1
      curr = 0 if curr < 0
    elsif ch == "DOWN"
      curr += 1
      curr = [view.size-1, curr].min
      # if empty then curr becomes -1
      curr = 0 if curr < 0
    elsif ch == "ENTER"
      return view[curr]
    else
      # do right and left arrow

      # get arrow keys here
    end

  end
end

#editline(array) ⇒ Object 



206
207
208
209
210
211
212
213
214
 
# File 'lib/cetusql/cli_utils.rb', line 206

def editline array
  Readline::HISTORY.push(*array) 
  begin
    command = Readline::readline('>', true)
  rescue Exception => e
    return nil
  end
  return command
end

#get_char ⇒ Object

Need to take complex keys and matc against a hash.



70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
 
# File 'lib/cetusql/cli_utils.rb', line 70

def get_char
  begin
    system("stty raw -echo 2>/dev/null") # turn raw input on
    c = nil
    #if $stdin.ready?
      c = $stdin.getc
      cn=c.ord
      return "ENTER" if cn == 10 || cn == 13
      return "BACKSPACE" if cn == 127
      return "C-SPACE" if cn == 0
      return "SPACE" if cn == 32
      # next does not seem to work, you need to bind C-i
      return "TAB" if cn == 8
      if cn >= 0 && cn < 27
        x= cn + 96
        return "C-#{x.chr}"
      end
      if c == ''
        buff=c.chr
        while true
          k = nil
          if $stdin.ready?
            k = $stdin.getc
            #puts "got #{k}"
            buff += k.chr
          else
            x=$kh[buff]
            return x if x
            #puts "returning with  #{buff}"
            if buff.size == 2
              ## possibly a meta/alt char
              k = buff[-1]
              return "M-#{k.chr}"
            end
            return buff
          end
        end
      end
    #end
    return c.chr if c
  ensure
    #system "stty -raw echo" # turn raw input off
    system("stty -raw echo 2>/dev/null") # turn raw input on
  end
end

#get_column_names(tbname) ⇒ Object 



23
24
25
 
# File 'lib/cetusql/cli_sqlite.rb', line 23

def get_column_names tbname
  get_metadata tbname
end

#get_data(db, sql) ⇒ Object 



35
36
37
38
39
40
41
42
43
 
# File 'lib/cetusql/cli_sqlite.rb', line 35

def get_data db, sql
  #$log.debug "SQL: #{sql} "
  $columns, *rows = db.execute2(sql)
  #$log.debug "XXX COLUMNS #{sql}, #{rows.count}  "
  content = rows
  return nil if content.nil? or content[0].nil?
  $datatypes = content[0].types #if @datatypes.nil?
  return content
end

#get_metadata(table) ⇒ Object 



44
45
46
47
 
# File 'lib/cetusql/cli_sqlite.rb', line 44

def get_metadata table
  get_data "select * from #{table} limit 1"
  return $columns
end

#get_table_names(db) ⇒ Object 



16
17
18
19
20
21
22
 
# File 'lib/cetusql/cli_sqlite.rb', line 16

def get_table_names db
  #raise "No database file selected." unless $current_db

  tables = get_data "select name from sqlite_master where type='table'"
  tables.collect!{|x| x[0] }  ## 1.9 hack, but will it run on 1.8 ??
  tables
end

#input(prompt = "", newline = false) ⇒ Object

readline version of gets



303
304
305
306
307
308
309
310
311
312
 
# File 'lib/cetusql/cli_utils.rb', line 303

def input(prompt="", newline=false)
  prompt += "\n" if newline
  ret = nil
  begin
    ret = Readline.readline(prompt, true).squeeze(" ").strip
  rescue Exception => e
    return nil
  end
  return ret
end

#menu(title, h) ⇒ Object

What if we only want to allow the given keys and ignore others. In menu maybe ENTER and other such keys should be ignored, or atleast option should be there, so i don’t accidentally hit enter. print in columns, but take into account size so we don’t exceed COLS (Some entries like paths can be long)



36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'lib/cetusql/cli_utils.rb', line 36

def menu title, h
  return unless h

  pbold "#{title}"
  ctr = 0
  #h.each_pair { |k, v| puts " #{k}: #{v}" }
  h.each_pair { |k, v| 
    print " #{k}: %-20s" % [v]
    if ctr > 1
      print "\n"
      ctr = 0
    else
      ctr += 1
    end
  }
  print "\n >"
  #print "\r >"
  ch = get_char
  puts ch
  #system("clear") # ???
  binding = h[ch]
  binding = h[ch.to_sym] unless binding
  if binding
    if respond_to?(binding, true)
      # 2017-03-19 - we can't send return values from a method ??
      send(binding)
    end
  end
  return ch, binding
end

#mktemp ⇒ Object 



139
140
141
142
143
144
145
146
147
148
 
# File 'lib/cetusql/cli_sqlite.rb', line 139

def mktemp
  require 'tempfile'
  tmpfile = Tempfile.new('SQL.XXXXXX')
  filename = tmpfile.path
  filename = Shellwords.escape(filename)
  #puts "Writing to #{filename}"
  #tmpfile.write(data.join("\n"))
  #tmpfile.close # need to flush, otherwise write is buffered
  return tmpfile
end

#multi_select(title, array) ⇒ Object

multiselect from an array using fzf



196
197
198
199
 
# File 'lib/cetusql/cli_utils.rb', line 196

def multi_select title, array
  arr = %x[ echo "#{array.join("\n")}" | fzf --multi --reverse --prompt="#{title} >"]
  return arr.split("\n")
end

#OLDview_data(db, sql, options) ⇒ Object

TODO use temp file, format it there and append to given file only after termtable



53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
 
# File 'lib/cetusql/cli_sqlite.rb', line 53

def OLDview_data db, sql, options
  outputfile = options[:output_to]
  formatting = options[:formatting]
  headers = options[:headers]
  #str = db.get_data sql
  rs = db.execute_query sql
  str = rs.content
  columns = rs.columns
  #puts "SQL: #{sql}.\nstr: #{str.size}"
  data = []
  if headers
    data << columns.join("\t")
  end
  str.each {|line| data << line.join("\t");  }
  #puts "Rows: #{data.size}"
  require 'tempfile'
  tmpfile = Tempfile.new('SQL.XXXXXX')
  filename = tmpfile.path
  filename = Shellwords.escape(filename)
  #puts "Writing to #{filename}"
  tmpfile.write(data.join("\n"))
  tmpfile.close # need to flush, otherwise write is buffered
  headerstr=nil
  if formatting
    headerstr = "-H" unless headers
    # sometimes this can be slow, and it can fault on UTF-8 chars
    system("cat #{filename} | term-table.rb #{headerstr} | sponge #{filename}")
  end
  if outputfile
    #puts "comes here"
    system("cp #{filename} #{outputfile}")
    filename = outputfile
  end
  system "wc -l #{filename}" if $opt_debug
  
  #system "$EDITOR #{filename}"
  system "vim -c ':set nowrap' #{filename}"
  tmpfile.close
  tmpfile.unlink
end

#pause(text = " Press a key ...") ⇒ Object 



175
176
177
178
 
# File 'lib/cetusql/cli_utils.rb', line 175

def pause text=" Press a key ..."
  print text
  get_char
end

#pbold(text) ⇒ Object 



162
163
164
 
# File 'lib/cetusql/cli_utils.rb', line 162

def pbold text
  puts "#{BOLD}#{text}#{BOLD_OFF}"
end

#perror(text) ⇒ Object 



168
169
170
171
 
# File 'lib/cetusql/cli_utils.rb', line 168

def perror text
  pred text
  get_char
end

#pgreen(text) ⇒ Object 



165
166
167
 
# File 'lib/cetusql/cli_utils.rb', line 165

def pgreen text
  puts "#{GREEN}#{text}#{CLEAR}"
end

#pred(text) ⇒ Object 



172
173
174
 
# File 'lib/cetusql/cli_utils.rb', line 172

def pred text
  puts "#{RED}#{text}#{CLEAR}"
end

#print_in_cols(a, noc = nil) ⇒ Object

columns if possible, and te width should be caclulated too



391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
 
# File 'lib/cetusql/cli_utils.rb', line 391

def print_in_cols a, noc=nil
  unless noc
    noc = 3
    if a.size < 7
      noc = 1
    elsif a.size < 15
      noc = 2
    end
  end

  x = noc - 1
  cols = a.each_slice((a.size+x)/noc).to_a
  # todo width should be determined based on COLS of screen, and width of data
  cols.first.zip( *cols[1..-1] ).each{|row| puts row.map{|e| e ? '%-30s' % e : '     '}.join("  ") }
end

#screen_settings ⇒ Object

— end constants check screen size and accordingly adjust some variables



293
294
295
296
297
298
299
300
 
# File 'lib/cetusql/cli_utils.rb', line 293

def screen_settings
  $glines=%x(tput lines).to_i
  $gcols=%x(tput cols).to_i
  $grows = $glines - 3
  $pagesize = 60
  #$gviscols = 3
  $pagesize = $grows * $gviscols
end

#select_from(title, array) ⇒ Object

How do we communicate to caller, that user pressed C-c



184
185
186
187
188
189
190
191
192
193
194
 
# File 'lib/cetusql/cli_utils.rb', line 184

def select_from title, array
  h = {}
  array.each_with_index {|e,ix| ix += 1; h[ix.to_s] = e }
  ch, text = menu title, h
  unless text
    if ch == "ENTER"
      return array.first
    end
  end
  return text
end

#single_select(title, array) ⇒ Object

CAUTION: this messes with single and double quotes, so don’t pass a query in



202
203
204
205
 
# File 'lib/cetusql/cli_utils.rb', line 202

def single_select title, array
  str = %x[ echo "#{array.join("\n")}" | fzf --reverse --prompt="#{title} >" -1 -0 ]
  return str
end

#tabulate(content, options) ⇒ Object

given content returned by get_data, formats and returns in a file



150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
 
# File 'lib/cetusql/cli_sqlite.rb', line 150

def tabulate content, options
  data = []
  content.each {|line| data << line.join("\t");  }
  puts "Rows: #{data.size}" if $opt_verbose
  require 'tempfile'
  tmpfile = Tempfile.new('SQL.XXXXXX')
  filename = tmpfile.path
  #filename = Shellwords.escape(filename)
  #puts "Writing to #{filename}"
  tmpfile.write(data.join("\n"))
  tmpfile.close # need to flush, otherwise write is buffered
  if options[:formatting]
    system("term-table.rb < #{filename} | sponge #{filename}")
  end
  return filename
end

#tabulate2(content, options) ⇒ Object

TODO check for headings true



170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
 
# File 'lib/cetusql/cli_sqlite.rb', line 170

def tabulate2 content, options
  widths = calculate_column_widths(content, 99)
  str = "| "
  sep = "+"
  widths.each do |w|
    str << "%-#{w}s | "
    sep << ("-"*(w+2)) + "+"
  end
  data = []
  data << sep 
  content.each_with_index {|line, ix| 
    data << str % line 
    data << sep if ix == 0
  }
  data << sep
  require 'tempfile'
  tmpfile = Tempfile.new('SQL.XXXXXX')
  filename = tmpfile.path
  #filename = Shellwords.escape(filename)
  #puts "Writing to #{filename}"
  tmpfile.write(data.join("\n"))
  tmpfile.close # need to flush, otherwise write is buffered
  return filename
end

#vared(var, prompt = ">") ⇒ Object

edit a variable inline like zsh’s vared



15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
# File 'lib/cetusql/cli_utils.rb', line 15

def vared var, prompt=">"
  Readline.pre_input_hook = -> do
    Readline.insert_text var
    Readline.redisplay
    # Remove the hook right away.
    Readline.pre_input_hook = nil
  end
  begin 
    input = Readline.readline(prompt, false)
  rescue Exception => e
    return nil
  end
  input
end

#view_data(db, sql, options) ⇒ Object 



93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
 
# File 'lib/cetusql/cli_sqlite.rb', line 93

def view_data db, sql, options
  outputfile = options[:output_to]
  formatting = options[:formatting]
  headers = options[:headers]
  #str = db.get_data sql
  rs = db.execute_query sql
  str = rs.content
  columns = rs.columns
  #puts "SQL: #{sql}.\nstr: #{str.size}"
  #data = []
  #if headers
    #data << columns.join("\t")
  #end
  #str.each {|line| data << line.join("\t");  }
  #puts "Rows: #{data.size}"
  headerstr=nil
  tmpfile = nil
  if formatting
    if headers
      str.unshift(columns)
    end
    filename = tabulate2 str, options
  else
    data = []
    if headers
      data << columns.join("\t")
    end
    str.each {|line| data << line.join("\t");  }
    tmpfile = mktemp()
    tmpfile.write(data.join("\n"))
    tmpfile.close # need to flush, otherwise write is buffered
    filename = tmpfile.path
  end
  if outputfile
    #puts "comes here"
    system("cp #{filename} #{outputfile}")
    filename = outputfile
  end
  
  #system "$EDITOR #{filename}"
  system "vim -c ':set nowrap' #{filename}"
  if tmpfile
    tmpfile.close
    tmpfile.unlink
  end
end