Databases

[:days4/projects/: Other SAGE Days 4 Projects]

Robert Miller, Emily Kirkman, Tom Boothby, David Joyner, Yi Qiang

GOAL: SAGE Enhancement Proposal.

• Database class
  • instance field
    • filename -- the database itself
    • connection -- a pysqlite connection to 'filename'
    • query string -- this would be a string representing the 'query state' of the database object.
      • allows for recursive searching quickly, since we can simply modify the string, and wait until we have to execute the query
    • dict of tables -- keyed by table name, entries are
      • dict of columns -- keyed by column name, keeps track of indices, primary key state
    • mutable -- boolean
  • functions
    • (mut'ble only) create/drop table
    • (mut'ble only) create/drop column( column name, col type, table, bool index=False, bool primary key=False )
      • if no table specified, raise an error and educate user about sql
    • (mut'ble only) create/drop index( column, table(s) )
    • (mut'ble only) create/drop primary key( column, table )
      • QUESTION: should we allow multiple tables? bounce this off someone who knows (i.e. is it possible to do this with sqlite?)
    • (mut'ble only) create/drop row( table, dict )
      • create accepts a dictionary keyed by column name

        • def foo(table=None, **kwds):
              if table is None:
                  raise KeyError('Table must be specified')
              print kwds
          
          sage: foo(table='table_name', shit='poo', dog='cat')
          # works
          sage: foo(shit='poo', dog='cat')
          Exception (click to the left for traceback):
          ...
          KeyError: 'Table must be specified'

    • (mut'ble only) add data from whatever (e.g. quickly via sql file): magic function to deal with other ways to add data? think about this more later.
    • copy
      • creates a new db and everything (completely deep copy)
      • preserves the query string?
      • executes the query string first?

      • is it possible to have multiple connections to the same .db file? [http://www.mail-archive.com/sqlite-users@sqlite.org/msg24725.html relevant]

    • save

      •  D.save('my.db')  should copy the class's database file to my.db.

      • should this execute queries first?
    • print
      • should probably just print a string with database name, number of tables...?
    • show
      • for extensions to the Database class, this can be anything, e.g. graph database prints nice table

      • for default database class, execute any queries and print the data- this isn't that much of an issue since everything will be int, real, bool or string...

      • notebook will print a nice html table
      • for command line, output returned by sqlite is a pretty good template
    • query
    • init
    • vacuum
    • clear queries

• exactly what should the following do?

  • D = Database('something.db')
    Q = D.query({'number_of_finches' : 7}, inplace=False) # hypothetical way to query

  • proposal: it should create a new Database object, with a separate connection to the same database, and add "number_of_finches = 7" to the query string

    D = Database('something.db')
    D.query({'number_of_finches' : 7}, inplace=True) # hypothetical way to query

  • proposal: it should add "number_of_finches = 7" to the query string
• init establishes connection to sqlite

  •  D = Database()  creates a new temp database and opens a connection to it

  •  D = Database('existing.db')  opens a connection to 'existing.db'

• create, remove and modify tables
• clear_queries

• Brainless database creation.
• Databases distributed with sage: Standard databases included with sage should be immutable
• Online databases
  • cgi web-accessible...
  • accessible from SAGE, perhaps hosted at sage.math...
  • SQLite servers?
• Automagic caching: keep track of computation results
  • keeping those results in portable objects, making these objects merge-able, etc...

• Licensing: ∃ DBGPL?

• Noticing identical database entries

• SAGE [http://www.initd.org/pub/software/pysqlite/doc/usage-guide.html interface] to sqlite [:sqlite-tutorial: creating your own database using sqlite in sage]