FactosCodeExample

From UbuntuBots

This are just general examples of how the database and factoids could be handled.
The code that handles db is split in two modules, one more general to using 
databases (databases.py) and the objects.py, which is a wrapper for databases.py 
and is more specific to factoids stuff.

databases.py

This is an example using the classes from databases.py

It has some classes for tables that are channel specific, like ChannelTable and ChannelSqliteDB, which I'll ignore for now.

First we would need to define a table object

from databases import Table, SqliteDB

tblStruct = (
   ('name',   'TEXT PRIMARY KEY'),
   ('number', 'INTEGER'),
   ('data',   'TEXT'),
   )

myTable = Table(tblStruct, name='mytable')

myTable would be needed for whenever we need to do any operation in that table. Is a dict-like object where we can get information about the table and get Row objects

>>> myTable['name']
'TEXT PRIMARY KEY'

>>> values = ('foo', 1, 'hello!')
>>> row = myTable.row(values)
>>> row['name']
'foo'
>>> row['data']
'hello!'

>>> row['name'] = 'bar'
>>> tuple(row)
('bar', 1, 'hello!')

Now for creating the table and inserting some data

>>> db = SqliteDB(':memory:')
>>> db.setDB() # we connect to the database here
<sqlite3.Connection object at 0x96bf9a0>
>>> db.setTable(myTable) # here setTable returns None,
                         # this means the table doesn't exist yet

>>> db.setTable(myTable, create=True) # this creates our table
<Table(mytable name TEXT PRIMARY KEY, number INTEGER, data TEXT)>

>>> db.insert(row)
>>> res = db.getOne("WHERE name='bar'")
>>> tuple(res)
(u'bar', 1, u'hello!')

>>> db.commit()

This could be done as well like this:

>>> db = SqliteDB(':memory:')
>>> db.insert(row, table=myTable)
>>> db.commit()

By using the argument table in db.insert it will connect to the database and creates the table if needed. This way you can define any tables needed and manipulate them easily.

objects.py

This is an example using the classes in objects.py, this is mostly a wrapper around databases for do stuff more specific to factoids

>>> from objects import Fact, User, SqliteDB
# NOTE: this SqliteDB object isn't the same as the one in databases!

>>> db = SqliteDB(':memory:')
>>> user = User('dude!user@home.com', db=db) # all User and Fact need a db instance

>>> user.pull() # this will fetch the user_id from db, if it doesn't exist, a new id is given
>>> user.user_id
0
>>> fact = Fact(name='foo', db=db)
>>> fact.pull()
Exception FactNotFound: Fact not found

# ok, create the fact
>>> fact.data = 'This is the reply of !foo'
>>> fact.create(user.user_id) # this inserts our new fact !foo
>>> user.push() # this would insert (or update) user into the db
>>> db.commit()

Now we can try to fetch a factoid:

>>> fact = Fact(name='foo', db=db)
>>> fact.pull()
>>> fact.data
u'This is the reply of !foo'
>>> fact.created_at
1273986814

>>> user = User(user_id=fact.created_by, db=db)
>>> user.pull()
>>> user.host
'dude!user@home.com'

And if we want to edit the fact

>>> editor = User('editor!user@home.com', db=db)
>>> editor.pull()
>>> editor.user_id
1
>>> fact.edit(editor.user_id, 'New reply for our !foo factoid')
>>> fact.push()
>>> editor.push()
>>> db.commit()

By using fact's edit method automatically creates a revision in the history, you can check with:

>>> row = db.getOne("WHERE name='foo'", table=db.tableHist)
>>> tuple(row)
(u'foo', 0, None, None, 0, u'This is the reply of !foo')

"This is the reply of !foo" was the value foo had before the edit.