Class: LiveJournal::Database

Inherits:

Object

• Object
• LiveJournal::Database

show all

Defined in:

lib/livejournal/database.rb

Overview

Defined Under Namespace

Classes: Error

Constant Summary

EXPECTED_DATABASE_VERSION =

"3"

SCHEMA =

%q{
CREATE TABLE meta (
  key TEXT PRIMARY KEY,
  value TEXT
);
CREATE TABLE entry (
  itemid INTEGER PRIMARY KEY,
  anum INTEGER,
  subject TEXT,
  event TEXT,
  moodid INTEGER, mood TEXT, music TEXT, location TEXT, taglist TEXT,
  pickeyword TEXT, preformatted INTEGER, backdated INTEGER,
  comments INTEGER, year INTEGER, month INTEGER, day INTEGER,
  timestamp INTEGER, security INTEGER
);
CREATE INDEX dateindex ON entry (year, month, day);
CREATE INDEX timeindex ON entry (timestamp);
CREATE TABLE comment (
  commentid INTEGER PRIMARY KEY,
  posterid INTEGER,
  itemid INTEGER,
  parentid INTEGER,
  state TEXT,  -- screened/deleted/frozen/active
  subject TEXT,
  body TEXT,
  timestamp INTEGER  -- unix timestamp
);
CREATE INDEX commententry ON comment (itemid);
CREATE TABLE users (
  userid INTEGER PRIMARY KEY,
  username TEXT
);
CREATE TABLE commentprop (
  commentid INTEGER,  -- not primary key 'cause non-unique
  key TEXT,
  value TEXT
);
}.gsub(/^      /, '')

Instance Attribute Summary (collapse)

• - (Object) db readonly

  The underlying SQLite3 database.

Class Method Summary (collapse)

• + (Object) db_value(name, sym)

  :nodoc:.

• + (Object) optional_to_i(x)

  :nodoc:.

Instance Method Summary (collapse)

• - (Object) close

  Close the underlying database.

• - (Object) each_entry(where = nil, &block)

  Yield a set of entries, ordered by ascending itemid (first to last).

• - (Object) get_entry(itemid)

  Fetch a specific itemid.

• - (Object) get_meta(key)

  :nodoc:.

• - (Database) initialize(filename, create_if_necessary = false) constructor

  A new instance of Database.

• - (Object) journal

  The the actual journal stored by this Database.

• - (Object) last_comment_full

  Used for Sync::Comments.

• - (Object) last_comment_meta

  Used for Sync::Comments.

• - (Object) query_entries(sql, *sqlargs)

  Given SQL that selects some entries, yield each Entry.

• - (Object) query_entry(sql, *sqlargs)

  Given SQL that selects an entry, return that Entry.

• - (Object) set_meta(key, value)

  :nodoc:.

• - (Object) store_comments_full(comments)

  Used for Sync::Comments.

• - (Object) store_comments_meta(comments)

  Used for Sync::Comments.

• - (Object) store_entry(entry)

  Store an Entry.

• - (Object) store_usermap(usermap)

  Used for Sync::Comments.

• - (Object) total_entry_count

  Return the total number of entries.

• - (Object) trace!

  Turn tracing on.

• - (Object) transaction

  Run a block within a single database transaction.

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

# File 'lib/livejournal/database.rb', line 82

def initialize(filename, create_if_necessary=false)
  exists = FileTest::exists? filename
  raise Errno::ENOENT if not create_if_necessary and not exists
  @db = SQLite3::Database.new(filename)

  # We'd like to use type translation, but it unfortunately fails on MAX()
  # queries.
  # @db.type_translation = true

  if exists
    # Existing database!
    version = self.version
    unless version == EXPECTED_DATABASE_VERSION
      raise Error, "Database version mismatch -- db has #{version.inspect}, expected #{EXPECTED_DATABASE_VERSION.inspect}"
    end
  end

  if create_if_necessary and not exists
    # New database!  Initialize it.
    transaction do
      @db.execute_batch(SCHEMA)
    end
    self.version = EXPECTED_DATABASE_VERSION
  end
end

80
81
82

# File 'lib/livejournal/database.rb', line 80

def db
  @db
end

125
126
127
128

# File 'lib/livejournal/database.rb', line 125

def self.db_value(name, sym)  # :nodoc:
  class_eval %{def #{sym}; get_meta(#{name.inspect}); end}
  class_eval %{def #{sym}=(v); set_meta(#{name.inspect}, v); end}
end

74
75
76
77

# File 'lib/livejournal/database.rb', line 74

def self.optional_to_i(x) # :nodoc:
  return nil if x.nil?
  return x.to_i
end

115
116
117

# File 'lib/livejournal/database.rb', line 115

def close
  @db.close
end

167
168
169
170
171
172

# File 'lib/livejournal/database.rb', line 167

def each_entry(where=nil, &block)
  sql = 'SELECT * FROM entry'
  sql += " WHERE #{where}" if where
  sql += ' ORDER BY itemid ASC'
  query_entries sql, &block
end

149
150
151

# File 'lib/livejournal/database.rb', line 149

def get_entry(itemid)
  query_entry("select * from entry where itemid=?", itemid)
end

119
120
121

# File 'lib/livejournal/database.rb', line 119

def get_meta key # :nodoc:
  return @db.get_first_value('SELECT value FROM meta WHERE key=?', key)
end

137
138
139

# File 'lib/livejournal/database.rb', line 137

def journal
  usejournal || username
end

191
192
193
194
195

# File 'lib/livejournal/database.rb', line 191

def last_comment_full
  Database::optional_to_i(
      @db.get_first_value('SELECT MAX(commentid) FROM comment ' +
                          'WHERE body IS NOT NULL'))
end

186
187
188
189

# File 'lib/livejournal/database.rb', line 186

def last_comment_meta
  Database::optional_to_i(
      @db.get_first_value('SELECT MAX(commentid) FROM comment'))
end

160
161
162
163
164

# File 'lib/livejournal/database.rb', line 160

def query_entries(sql, *sqlargs) # :yields: entry
  @db.execute(sql, *sqlargs) do |row|
    yield Entry.new.load_from_database_row(row)
  end
end

154
155
156
157

# File 'lib/livejournal/database.rb', line 154

def query_entry(sql, *sqlargs)
  row = @db.get_first_row(sql, *sqlargs)
  return Entry.new.load_from_database_row(row)
end

122
123
124

# File 'lib/livejournal/database.rb', line 122

def set_meta key, value  # :nodoc:
  @db.execute('INSERT OR REPLACE INTO meta VALUES (?, ?)', key, value)
end

202
203
204

# File 'lib/livejournal/database.rb', line 202

def store_comments_full(comments)
  store_comments(comments, false)
end

198
199
200

# File 'lib/livejournal/database.rb', line 198

def store_comments_meta(comments)
  store_comments(comments, true)
end

180
181
182
183

# File 'lib/livejournal/database.rb', line 180

def store_entry entry
  sql = 'INSERT OR REPLACE INTO entry VALUES (' + ("?, " * 17) + '?)'
  @db.execute(sql, *entry.to_database_row)
end

207
208
209
210
211
212
213
214
215
216

# File 'lib/livejournal/database.rb', line 207

def store_usermap(usermap)
  transaction do
    sql = "INSERT OR REPLACE INTO users VALUES (?, ?)"
    @db.prepare(sql) do |stmt|
      usermap.each do |id, user|
        stmt.execute(id, user)
      end
    end
  end
end

175
176
177

# File 'lib/livejournal/database.rb', line 175

def total_entry_count
  @db.get_first_value('SELECT COUNT(*) FROM entry').to_i
end

142
143
144
145
146

# File 'lib/livejournal/database.rb', line 142

def trace!
  @db.trace() do |data, sql|
    puts "SQL> #{sql.inspect}"
  end
end

110
111
112

# File 'lib/livejournal/database.rb', line 110

def transaction
  @db.transaction { yield }
end