Warning: Can't synchronize with repository "(default)" (Unsupported version control system "svn": No module named svn). Look in the Trac log for more information.

Changes between Version 7 and Version 8 of ViewsInSqlObject


Ignore:
Timestamp:
03/04/08 19:16:35 (11 years ago)
Author:
Chris Arndt
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • ViewsInSqlObject

    v7 v8  
    1 This is not a real example, my use-case for a view had left-joins on 6 tables, not just one, but it's a good template to work from. Note that you always have to have an 'id' column come back, and it has to be a valid primary key for the view, otherwise SQLObjet magic blows up. 
     1{{{ 
    22 
    3 This will only work on databases that support views, obviously. You should be able to put this straight into model.py, modify to actually work with your SQL database, and then do tg-admin sql create. After that, you can treat it like a normal SQLObject table. 
     3#!rst 
    44 
    5 {{{ 
    6 #!python 
    7 class MyView(SQLObject): 
    8     name_one = StringCol() 
    9     name_two = StringCol() 
    10     somebool = BoolCol() 
    11  
    12     @classmethod 
    13     def createTable(cls, ifNotExists=False, createJoinTables=True, 
    14                     createIndexes=True, applyConstraints=True, 
    15                     connection=None): 
    16         conn = connection or cls._connection 
    17         if ifNotExists and conn.tableExists(cls.sqlmeta.table): 
    18             return 
    19  
    20         sql, constraints = cls.createTableSQL() 
    21  
    22         # Treat the view like a constraint, only create it 
    23         # after all the other tables have been created. 
    24         return [sql] 
    25  
    26     @classmethod 
    27     def createTableSQL(cls, createJoinTables=True, createIndexes=True, 
    28                        connection=None): 
    29         return """CREATE VIEW %s as ( 
    30         SELECT table_one.id as id, 
    31                table_one.some_name as name_one, 
    32                table_two.some_name as name_two, 
    33                SOMEFUNC(table_one.foo + table_two.bar) as somebool 
    34         FROM  
    35             table_one  
    36             LEFT JOIN table_two ON (table_one.foo_id = table_one.id) 
    37         ) """ % (cls.sqlmeta.table,), [] 
    38  
     5.. note:: This page has been migrated to http://docs.turbogears.org/1.0/ViewsInSqlObject 
    396}}} 
    407 
    41 ------ 
    42  
    43 This example also doesn't cover the case where you don't have an ID or you'd have to fake one.  The best solution I've found is concatenating IDs from tables involved and then using this result as the id for the view. 
    44  
    45 {{{ 
    46 #!python 
    47 class MyView(SQLObject): 
    48     class sqlmeta: 
    49         idType = 'str' 
    50         idName = 'concatenated_ids' 
    51  
    52     name_one = StringCol() 
    53     name_two = StringCol() 
    54     somebool = BoolCol() 
    55 }}} 
    56  
    57 For !PostgreSQL, creating this should be something like: 
    58  
    59 {{{ 
    60 CREATE VIEW myView AS  
    61        SELECT table1.id::text || '.'::text || table2.id::text || '.'::text || table3.id::text as concatenated_ids,  
    62               table1.col1, table2.col2, table3.col3  
    63        FROM table1, table2, table3  
    64        WHERE table1.something  = table2.otherthing AND table1.anotherthing = table3.yetanotherthing; 
    65 }}} 
    66  
    67 Since all tables have an id column that is unique, we grant that the resulting concatenation will also be unique and be the best choice for a complex view. 
    68  
    69 ''- Jorge Godoy'' 
    70  
    71 ------ 
    72  
    73 We had some trouble with the above method, but we are trying to do something a little different. 
    74  
    75 We already have database schema defined and wanted to use the views we already had defined.  The view was created by: 
    76 {{{ 
    77 CREATE VIEW collection_metrics AS         
    78     SELECT  
    79         collection_id             AS id, 
    80         SUM(duration)             AS duration, 
    81         SUM(size)                 AS size, 
    82         COUNT(collection_id)      AS files, 
    83         COUNT(DISTINCT item_id)   AS items 
    84     FROM  
    85         files 
    86     GROUP BY 
    87         collection_id 
    88     ORDER BY 
    89         collection_id; 
    90 }}} 
    91  
    92  
    93 At first I tried this SQLObject: 
    94 {{{ 
    95 #!python 
    96 class collectionMetrics(SQLObject): 
    97     class sqlmeta: 
    98         fromDatabase=True 
    99 }}} 
    100  
    101 Which would fail because SQLObject couldn't find a PRIMARY KEY from the database.  The solution is fairly simple, just manually define the columns, and tell SQLObject which table to use. 
    102  
    103 {{{ 
    104 #!python 
    105 class collectionMetrics(SQLObject): 
    106     duration = FloatCol() 
    107     size = FloatCol() 
    108     files = IntCol() 
    109     items = IntCol() 
    110     class sqlmeta: 
    111         table = 'collection_metrics' 
    112 }}} 
    113  
    114 Hope that helps! 
    115  
    116 Paul Warren. 2006-03-14