0
# Tables and Schema Management
1

2
Database schema representation with multi-level namespacing, table creation with column definitions, and field access patterns. PyPika provides comprehensive support for database schemas, tables, and columns with intuitive attribute-based field access.
3

4
## Capabilities
5

6
### Table Creation and Management
7

8
Core table representation supporting schema namespacing, aliasing, and field access with dynamic attribute resolution.
9

10
```python { .api }
11
class Table:
12
    def __init__(self, name: str, schema: Optional[Union[Schema, str]] = None, 
13
                 alias: Optional[str] = None, query_cls: Optional[Type[Query]] = None):
14
        """
15
        Create a table reference.
16
        
17
        Parameters:
18
        - name: Table name
19
        - schema: Schema name or Schema instance
20
        - alias: Table alias
21
        - query_cls: Query class to use for operations
22
        """
23
        
24
    def select(self, *terms) -> QueryBuilder:
25
        """Start SELECT query from this table."""
26
        
27
    def update(self) -> QueryBuilder:
28
        """Start UPDATE query on this table."""
29
        
30
    def insert(self, *terms) -> QueryBuilder:
31
        """Start INSERT query into this table."""
32
        
33
    def field(self, name: str) -> Field:
34
        """Get field reference by name."""
35
        
36
    def as_(self, alias: str) -> Table:
37
        """Set table alias."""
38
        
39
    def for_(self, temporal_criterion: Criterion) -> Table:
40
        """Add temporal FOR clause (for temporal tables)."""
41
        
42
    def for_portion(self, period_criterion: PeriodCriterion) -> Table:
43
        """Add period FOR PORTION clause (for temporal tables)."""
44
        
45
    def get_table_name(self) -> str:
46
        """Get effective table name (alias or name)."""
47
        
48
    def get_sql(self, **kwargs) -> str:
49
        """Generate SQL representation."""
50
        
51
    @property
52
    def star(self) -> Star:
53
        """Get * field reference for this table."""
54
        
55
    # Dynamic field access
56
    def __getattr__(self, name: str) -> Field:
57
        """Access fields as attributes: table.field_name"""
58
        
59
    def __getitem__(self, name: str) -> Field:
60
        """Access fields with brackets: table['field_name']"""
61
```
62

63
**Usage Examples:**
64

65
```python
66
from pypika import Table, Schema, Database, Query
67

68
# Simple table
69
users = Table('users')
70
print(users.get_sql())  # "users"
71

72
# Table with alias
73
users_alias = Table('users', alias='u')
74
print(users_alias.get_sql())  # "users" "u"
75

76
# Table with schema
77
users_schema = Table('users', schema='public')
78
print(users_schema.get_sql())  # "public"."users"
79

80
# Field access patterns
81
users = Table('users')
82

83
# Attribute access
84
name_field = users.name
85
email_field = users.email
86

87
# Bracket access  
88
name_field = users['name']
89
email_field = users['email']
90

91
# Method access
92
name_field = users.field('name')
93

94
# Star field
95
all_fields = users.star  # Represents users.*
96

97
# Query operations from table
98
query = users.select(users.name, users.email)
99
query = users.update().set(users.email, 'new@email.com')
100
query = users.insert('John', 'john@email.com')
101
```
102

103
### Schema Management
104

105
Multi-level schema namespacing supporting nested schemas and database hierarchies.
106

107
```python { .api }
108
class Schema:
109
    def __init__(self, name: str, parent: Optional[Schema] = None):
110
        """
111
        Create schema reference.
112
        
113
        Parameters:
114
        - name: Schema name
115
        - parent: Parent schema for nested schemas
116
        """
117
        
118
    def get_sql(self, quote_char: Optional[str] = None, **kwargs) -> str:
119
        """Generate SQL representation."""
120
        
121
    def __getattr__(self, item: str) -> Table:
122
        """Access tables as attributes: schema.table_name"""
123

124
class Database(Schema):
125
    def __getattr__(self, item: str) -> Schema:
126
        """Access schemas as attributes: database.schema_name"""
127
```
128

129
**Usage Examples:**
130

131
```python
132
from pypika import Schema, Database, Table
133

134
# Simple schema
135
public = Schema('public')
136
users = Table('users', schema=public)
137
# Or equivalently:
138
users = public.users
139

140
# Nested schemas
141
company = Schema('company')
142
hr = Schema('hr', parent=company)
143
users = Table('users', schema=hr)
144
print(users.get_sql())  # "company"."hr"."users"
145

146
# Database with schemas
147
db = Database('mydb')
148
public_schema = db.public
149
users = public_schema.users
150
# Or chained:
151
users = db.public.users
152

153
# Multiple schema levels
154
db = Database('corp')
155
division = db.sales
156
region = Schema('west', parent=division)
157
users = Table('users', schema=region)
158
print(users.get_sql())  # "corp"."sales"."west"."users"
159
```
160

161
### Column Definitions
162

163
Column specifications for CREATE TABLE operations with type, nullability, and default value support.
164

165
```python { .api }
166
class Column:
167
    def __init__(self, column_name: str, column_type: Optional[str] = None,
168
                 nullable: Optional[bool] = None, default: Optional[Any] = None):
169
        """
170
        Define table column.
171
        
172
        Parameters:
173
        - column_name: Column name
174
        - column_type: SQL data type
175
        - nullable: Whether column accepts NULL (True/False/None for unspecified)
176
        - default: Default value or expression
177
        """
178
        
179
    def get_name_sql(self, **kwargs) -> str:
180
        """Get column name SQL."""
181
        
182
    def get_sql(self, **kwargs) -> str:
183
        """Get full column definition SQL."""
184
```
185

186
**Usage Examples:**
187

188
```python
189
from pypika import Column, Query, Table
190
from pypika.functions import Now
191

192
# Basic column definitions
193
columns = [
194
    Column('id', 'INTEGER', nullable=False),
195
    Column('name', 'VARCHAR(100)', nullable=False),
196
    Column('email', 'VARCHAR(255)', nullable=False),
197
    Column('age', 'INTEGER', nullable=True),
198
    Column('created_at', 'TIMESTAMP', default='CURRENT_TIMESTAMP'),
199
    Column('updated_at', 'TIMESTAMP', default=Now()),
200
    Column('status', 'VARCHAR(20)', default='active')
201
]
202

203
# CREATE TABLE with columns
204
users = Table('users')
205
query = (Query.create_table(users)
206
         .columns(*columns)
207
         .primary_key('id'))
208

209
print(query.get_sql())
210
# CREATE TABLE "users" (
211
#   "id" INTEGER NOT NULL,
212
#   "name" VARCHAR(100) NOT NULL,
213
#   "email" VARCHAR(255) NOT NULL,
214
#   "age" INTEGER NULL,
215
#   "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
216
#   "updated_at" TIMESTAMP DEFAULT NOW(),
217
#   "status" VARCHAR(20) DEFAULT 'active'
218
# )
219
```
220

221
### Table and Column Factory Functions
222

223
Utility functions for creating multiple tables and columns efficiently.
224

225
```python { .api }
226
def Tables(*names: Union[Tuple[str, str], str], **kwargs) -> List[Table]:
227
    """
228
    Create multiple tables.
229
    
230
    Parameters:
231
    - names: Table names or (name, alias) tuples
232
    - kwargs: Common table options (schema, query_cls)
233
    
234
    Returns:
235
    List of Table instances
236
    """
237

238
def Columns(*names: str) -> List[Column]:
239
    """
240
    Create multiple basic columns.
241
    
242
    Parameters:
243
    - names: Column names
244
    
245
    Returns:
246
    List of Column instances
247
    """
248
```
249

250
**Usage Examples:**
251

252
```python
253
from pypika import Tables, Columns, Schema
254

255
# Create multiple tables
256
users, orders, products = Tables('users', 'orders', 'products')
257

258
# Create tables with aliases
259
users, orders = Tables(('users', 'u'), ('orders', 'o'))
260

261
# Create tables with common schema
262
schema = Schema('public')
263
users, orders, products = Tables('users', 'orders', 'products', schema=schema)
264

265
# Create multiple basic columns
266
columns = Columns('id', 'name', 'email', 'created_at')
267

268
# More complex example
269
from pypika import MySQLQuery
270

271
# Tables using specific query class
272
user_table, order_table = Tables('users', 'orders', query_cls=MySQLQuery)
273

274
# Now these tables will use MySQL-specific features
275
query = user_table.select('*')  # Uses MySQLQuery internally
276
```
277

278
### Temporal Table Support
279

280
Support for SQL temporal tables with FOR SYSTEM_TIME and FOR PORTION clauses.
281

282
```python { .api }
283
class Table:
284
    def for_(self, temporal_criterion: Criterion) -> Table:
285
        """Add temporal FOR clause."""
286
        
287
    def for_portion(self, period_criterion: PeriodCriterion) -> Table:
288
        """Add FOR PORTION clause."""
289
```
290

291
**Usage Examples:**
292

293
```python
294
from pypika import Table, SYSTEM_TIME
295
from pypika.functions import Now
296
from datetime import datetime
297

298
# Temporal table queries
299
users = Table('users')
300

301
# Query historical data
302
historical_users = users.for_(SYSTEM_TIME.between('2023-01-01', '2023-12-31'))
303
query = Query.from_(historical_users).select('*')
304

305
# Query as of specific time
306
as_of_users = users.for_(SYSTEM_TIME.as_of('2023-06-01'))
307
query = Query.from_(as_of_users).select('*')
308

309
# Period-based temporal queries
310
# (Requires period criterion implementation)
311
# users.for_portion(period_criterion)
312
```
313

314
### Advanced Table Features
315

316
Additional table functionality including custom query class assignment and complex schema hierarchies.
317

318
**Usage Examples:**
319

320
```python
321
from pypika import Table, Query, PostgreSQLQuery, MySQLQuery
322

323
# Table with custom query class
324
pg_users = Table('users', query_cls=PostgreSQLQuery)
325
mysql_users = Table('users', query_cls=MySQLQuery)
326

327
# PostgreSQL-specific features available
328
pg_query = pg_users.select('*')  # Can use PostgreSQL-specific methods
329

330
# Complex schema with different query classes
331
from pypika import Database, Schema
332

333
# Database with mixed query classes
334
postgres_db = Database('postgres_db')
335
mysql_db = Database('mysql_db')
336

337
# Tables inherit query capabilities based on their configuration
338
pg_users = Table('users', schema=postgres_db.public, query_cls=PostgreSQLQuery)
339
mysql_users = Table('users', schema=mysql_db.main, query_cls=MySQLQuery)
340

341
# Each table uses appropriate SQL dialect
342
pg_query = pg_users.select('*').limit(10)  # PostgreSQL LIMIT syntax
343
mysql_query = mysql_users.select('*').limit(10)  # MySQL LIMIT syntax
344
```