GQL queries in SQL, part 3

I did not have a lot of time to play with my sql connector this week, so there is only a brief update today -- hopefully, there will be a little bit more next week.

I started looking into expressing the sort order in sql statements and ran into a little issue: without any concrete type information in a query (remember, the properties in our model are a couple of layers further above), how would I know which column in our SQL table to sort by? I needed to map each possible value in an entity to one or more rows. For this reason, I added the following method to my prm-helper:

  def getSchema(self, connection, table_name):
    """For a given table name, return all property names defined.
    
       Args:
         connection: the sql connection that should be used to gather
                     any metadata
         table_name: the name of the table/model to inspect
    
       Returns:
         a dictionary with the property names as keys and a list
         of column names (used to store them) as values
    """
    result = {}
    for column in connection.cursor().execute(
        "PRAGMA TABLE_INFO(%s)" % table_name).fetchall():
      key = column[1]
      i = key.find('_')
      if i < 1:
        continue
      p1 = key[0:i]
      if p1 == 'pk':
        continue
      p2 = key[i+1:]
      if not p2 in result:
        result[p2] = []
      result[p2].append(key)
    return result

This little method iterates through a table's metadata and extracts the information I need, using the same naming conventions that are in place for the other mapping-related logic. The following unit test makes sure that the method works:

  def testGetSchema(self):
    helpers.create_tables([TestModel()], self.connection)
    prm = datastore_sqlite_stub.PRMHelper()
    schema = prm.getSchema(self.connection, 'TestModel')
    assert schema
    self.assertEquals(['text', 'number'], schema.keys())
    self.assertEquals({
        'text': ['string_text'],
        'number': ['int64_number']}, schema)

With this helper in place, I should be able to express sort order conditions in SQL during the next iteration.