modules/query.rb

   1 ## modules/query
   2 require 'modules/sql'
   3
   4 ## all the sql queries are supposed to be here
   5 ## TODO: THIS NEEDS A LOT WORK
   6 module Query
   7         include SQL
   8         extend Query
   9
  10         COLUMNS = %w(prio tags key value descr onchg ctime mtime info1 info2 info3)
  11         TYPES = %w(cmd map opt tag txt)
  12
  13         CREATE = "INSERT INTO nyu (type, key, value) VALUES (?, ?, ?)"
  14         CREATE_OPT = "INSERT INTO nyu (type, key, value, info2) VALUES ('opt', ?, ?, ?)"
  15         DELETE_ALL_CONFIG_DATA = "DELETE FROM nyu WHERE NOT (type='txt' or type='tag')"
  16         SET = {}
  17         TIME_LEFT = "SELECT info1 FROM nyu WHERE type='txt' AND (NOT info1='') AND info1>? AND tags GLOB '*/i/*' ORDER BY info1 LIMIT 1;"
  18         GET_ALL_CONFIG_DATA = "SELECT * FROM nyu WHERE NOT (type='txt' OR type='tag')"
  19         INSERT_WITHOUT_ROWID = "INSERT INTO nyu (key,type,ctime,mtime,prio,tags,value,descr,onchg,info1,info2,info3) VALUES (?,?,?,?,?, ?,?,?,?,?, ?,?)"
  20         UPDATE_CONFIG_DATA = "UPDATE nyu SET key=?, type=?, ctime=?, mtime=?, prio=?, tags=?, value=?, descr=?, onchg=?, info1=?, info2=?, info3=? WHERE type=? AND key=?"
  21
  22         TABLE_CREATION = <<SQL
  23 CREATE TABLE nyu(
  24         rowid INTEGER PRIMARY KEY ASC,
  25         key TEXT,
  26         type TEXT,
  27         ctime INTEGER,
  28         mtime INTEGER,
  29         prio REAL,
  30         tags TEXT,
  31         value TEXT,
  32         descr TEXT,
  33         onchg TEXT,
  34         info1 TEXT,
  35         info2 TEXT,
  36         info3 TEXT
  37 );
  38 SQL
  39
  40
  41         INSERT = "INSERT INTO nyu"
  42         INSERT2 = "#{INSERT} (key, type, value) VALUES"
  43
  44         SELECT = "SELECT * FROM nyu"
  45         SELECT_VALUE = "SELECT value FROM nyu"
  46
  47         GET_NYURONS = "#{SELECT}"
  48
  49         for column in COLUMNS
  50                 SET[column] = "UPDATE nyu SET #{column}=? WHERE rowid=?"
  51         end
  52
  53         def create_table()
  54                 sql do
  55                         sql TABLE_CREATION
  56                         sql_each_line DEFAULTS
  57                 end
  58         end
  59
  60         def get_time_left()
  61                 x = sql_first(TIME_LEFT, Time.now.to_i)
  62 #               x = @@time_left.execute(Time.now.to_i).first
  63                 if x
  64                         return x.first.to_i
  65                 else
  66                         return nil
  67                 end
  68         end
  69
  70         def export_options( fname )
  71                 fname = File.expand_path(fname)
  72                 assert_neq fname, File.expand_path(Info.dbfile),
  73                         "Cannot export to current database!"
  74
  75                 db = SQLite3::Database.new( fname )
  76                 db.execute TABLE_CREATION
  77                 db.transaction
  78                 sql GET_ALL_CONFIG_DATA do |row|
  79                         row.shift
  80                         db.execute(INSERT_WITHOUT_ROWID, *row)
  81                 end
  82                 db.commit
  83                 db.close
  84         end
  85
  86         def import_options( fname, loop_detected = false )
  87                 fname = File.expand_path(fname)
  88                 assert_neq fname, File.expand_path(Info.dbfile),
  89                         "Cannot import from current database!"
  90
  91                 bk = "/tmp/nyu_backup_#{rand 100000}"
  92                 export_options( bk )
  93
  94                 db = SQLite3::Database.new( fname )
  95                 sql_begin
  96                 sql DELETE_ALL_CONFIG_DATA
  97                 db.execute GET_ALL_CONFIG_DATA do |row|
  98                         row.shift
  99                         sql(INSERT_WITHOUT_ROWID, *row)
 100                 end
 101
 102                 begin
 103                         sql_end
 104                 rescue
 105                         Console.error "import failed"
 106                         unless loop_detected
 107                                 import_options(bk, true)
 108                         end
 109                 ensure
 110                         db.close
 111                         File.delete(bk)
 112                 end
 113         end
 114
 115         def merge_options( fname )
 116                 fname = File.expand_path(fname)
 117                 assert_neq fname, File.expand_path(Info.dbfile),
 118                         "Cannot merge from current database!"
 119
 120                 db = nil
 121                 sql_begin
 122                 begin
 123                         db = SQLite3::Database.new( fname )
 124                         db.execute GET_ALL_CONFIG_DATA do |row|
 125                                 row.shift
 126                                 othertype = row[1]
 127                                 otherkey  = row[0]
 128                                 if mtime = Query.get_mtime_from_type_key(othertype, otherkey)
 129                                         mtime = mtime.first
 130                                         othertime = row[3]
 131
 132                                         if mtime.nil? or (othertime and mtime < othertime)
 133                                                 sql UPDATE_CONFIG_DATA, *(row + [othertype, otherkey])
 134                                         end
 135                                 else
 136                                         sql INSERT_WITHOUT_ROWID, *row
 137                                 end
 138                         end
 139                 ensure
 140                         db.close if db
 141                         sql_end
 142                 end
 143         end
 144
 145         def exists?(type, key=nil)
 146                 sql_first("SELECT value FROM nyu WHERE type=? AND key=?", type, key)
 147         end
 148
 149         def get_neurons(filter)
 150                 list = []
 151                 addnyurons = proc do |row|
 152                         list << Neuron.new(row)
 153                 end
 154
 155                 query = GET_NYURONS
 156                 query += " WHERE #{filter}" unless filter.strip.empty?
 157                 begin
 158                         sql(query, &addnyurons)
 159                 rescue SQLite3::SQLException
 160                         begin
 161                                 list = []
 162                                 sql(query, &addnyurons)
 163                         rescue Exception
 164                                 Console.error
 165                         end
 166                 rescue Exception
 167                         Console.error
 168                 end
 169                 return list
 170         end
 171
 172
 173         def set(rowid, key, val)
 174                 if Neuron === rowid
 175 #                       nyuron = rowid
 176                         rowid = rowid.rowid
 177 #                       option = nyuron.option? if key == 'value'
 178                 elsif SQLite3::ResultSet::ArrayWithTypesAndFields === rowid
 179                         rowid = rowid.first.to_i
 180                 end
 181
 182                 assert rowid, Numeric
 183
 184 #               if option
 185 #                       set_option(nyuron.key, val) ## this will also eval the code of some options
 186 #               else
 187 #                       sql("UPDATE nyu SET #{key}=\"#{val.sql}\" WHERE rowid=#{rowid}")
 188                         if SET.has_key?(key)
 189                                 sql SET[key], val, rowid
 190 #                               @@set[key].execute(val, rowid)
 191                         else
 192                                 forbid
 193                         end
 194 #                       sql("UPDATE nyu SET #{key}=? WHERE rowid=?", val, rowid)
 195 #                       trace
 196 #               end
 197         end
 198
 199         def create(type, key=nil, val=nil)
 200                 sql CREATE, type, key, val
 201 #               @@create.execute(type, key, val)
 202                 neuron = Neuron.from_rowid(sql_last_row)
 203                 Cache << neuron
 204                 return neuron
 205         end
 206         alias create_and_return create
 207
 208         def create_opt(key=nil, val=nil, info2=nil)
 209                 sql CREATE_OPT, key, nil, info2
 210                 opt = Neuron.from_rowid(sql_last_row)
 211                 opt.value = val
 212                 Cache << opt
 213                 return opt
 214         end
 215         alias create_opt_and_return create_opt
 216
 217         def get(rowid, key)
 218                 rowid = rowid.rowid if Neuron === rowid
 219                 assert rowid, Numeric
 220
 221                 sql_first("SELECT #{key} FROM nyu WHERE rowid=#{rowid}")
 222         end
 223
 224         def get_mtime_from_type_key(type, key)
 225                 sql_first("SELECT mtime FROM nyu WHERE type=? AND key=?", type, key)
 226         end
 227
 228         def get_all_where(str)
 229                 sql("SELECT * FROM nyu WHERE " + str)
 230         end
 231
 232         def get_all_tagged_rows()
 233                 sql("SELECT tags FROM nyu WHERE tags IS NOT NULL")
 234         end
 235
 236
 237         def get_all_tags()
 238                 sql("SELECT key FROM nyu WHERE type='tag'").map!{|x| x.first}
 239         end
 240
 241         def get_key_val_of_type(type, &block)
 242                 sql("SELECT key, value FROM nyu WHERE type=?", type, &block)
 243         end
 244
 245         def get_where(str, *args)
 246                 sql_first("SELECT * FROM nyu WHERE " + str, *args)
 247         end
 248
 249         def get_mapping(keybuffer)
 250                 result = sql("#{SELECT_VALUE} WHERE type='map' AND key='#{keybuffer}' LIMIT 1").first
 251                 return result ? result.first : nil
 252         end
 253
 254         def get_command(command)
 255                 result = sql("#{SELECT_VALUE} WHERE type='cmd' AND key='#{command}' LIMIT 1").first
 256                 return result ? result.first : nil
 257         end
 258
 259         def get_tabcomp(cmd)
 260                 sql_first("SELECT info1 FROM nyu WHERE type='cmd' AND key='#{cmd}'")
 261         end
 262
 263         def get_option_info(key)
 264                 sql_first("SELECT value, info2, info3 FROM nyu WHERE type='opt' AND key='#{key}'")
 265         end
 266
 267         def all_reoccuring_notes(&block)
 268                 sql("SELECT * FROM nyu WHERE type='txt' AND NOT info2='' AND tags GLOB '*/r/*'", &block)
 269         end
 270
 271         def db_size()
 272                 sql_first("SELECT count(*) FROM nyu").first.to_i
 273         end
 274
 275         def delete(what)
 276                 if Neuron === what
 277                         Cache.delete(what)
 278                         what = what.rowid
 279                 end
 280                 assert what, Numeric
 281                 sql("DELETE FROM nyu WHERE rowid=#{what}")
 282         end
 283
 284         def set_option(key, val)
 285                 result = Query.get_option_info(key)
 286                 oldval, types, code = result
 287
 288                 catch :break do
 289                         if code
 290                                 if code.strip =~ /^alias (\w+)$/
 291                                         code = Query.get_option_code($1)
 292                                         throw :break unless String === code
 293                                 end
 294                                 env = Environment.new
 295                                 env.option = key
 296                                 env.value = val
 297                                 env.types = types
 298                                 env.oldvalue = oldval
 299
 300                                 result = env.run(code)
 301                                 val = result unless result.nil?
 302                         end
 303                 end
 304
 305                 Cache.opt[key.to_sym] = Convert.option(val,types)
 306                 sql("UPDATE nyu SET value=? WHERE type='opt' AND key=?", val, key)
 307         end
 308
 309         def manage_tag(name, number = nil, rowid = nil)
 310                 return unless String === name and not name.empty?
 311                 assert number, Integer, NilClass
 312                 if number.nil?
 313                         number = sql("SELECT count(*) FROM nyu WHERE tags glob '*#{Convert::TAG_SPLIT_STRING}#{name}#{Convert::TAG_SPLIT_STRING}*'")
 314                         number ||= 0
 315                 end
 316                 if rowid.nil?
 317                         rowid = sql_first("SELECT rowid FROM nyu WHERE type='tag' AND key=?", name)
 318                 end
 319                 if rowid
 320                         set(rowid, "value", number)
 321                 else
 322                         create('tag', name, number)
 323                         API.refilter
 324                 end
 325         end
 326 end