1

from pvl.web import html

     2

from pvl.verkko import web, db

     3

     4

import math

     5

     6

import logging; log = logging.getLogger('pvl.verkko.table')

     7

     8

class Column (object) :

     9

    """

    10

        web.Table column spec, representing a property of an SQLAlchemy ORM model class.

    11

    """

    12

    13

    def __init__ (self, attr, title, column, rowhtml=None, sort=True, filter=True, colcss=True, rowfilter=False, rowtitle=None, rowcss=None) :

    14

        """

    15

            attr        - name of column value, used for http query arg, html css class, model getattr()

    16

            title       - title of column

    17

            column      - the model column property, used to build queries

    18

            rowhtml     - function returning column value as HTML for model

    19

            sort        - allow sorting by column

    20

            filter      - allow filtering by column

    21

            colcss      - apply css class for column; True -> attr

    22

            rowfilter   - allow filtering by row value

    23

            rowtitle    - function returning column title for model

    24

            rowcss      - function returning column class for model

    25

        """

    26

    27

        if colcss is True :

    28

            colcss = attr

    29

    30

        self.attr = attr

    31

        self.title = title

    32

        self.column = column

    33

    34

        # column attrs

    35

        self.sort = sort

    36

        self.filter = filter

    37

        self.colcss = colcss

    38

    39

        # row attrs

    40

        self.rowhtml = rowhtml

    41

        self.rowfilter = rowfilter

    42

        self.rowtitle = rowtitle

    43

        self.rowcss = rowcss

    44

    45

    def render_header (self, sorturl) :

    46

        """

    47

            Render <th> for <thead> in given Table.

    48

        """

    49

    50

        header = self.title

    51

    52

        if self.sort :

    53

            header = html.a(href=sorturl)(header)

    54

    55

        return html.th(header)

    56

    57

    def render_filter_input (self, filters) :

    58

        """

    59

            Render filter <input> in given Table.

    60

        """

    61

    62

        value = filters.get(self.attr)

    63

    64

        if value :

    65

            # XXX: multi-valued filters?

    66

            value = value[0]

    67

        else :

    68

            value = None

    69

    70

        return html.input(type='text', name=self.attr, value=value)

    71

    72

    def render_header_filter (self, filters) :

    73

        """

    74

            Render <td><input> for <thead> in given Table.

    75

        """

    76

    77

        if self.filter :

    78

            input = self.render_filter_input(filters)

    79

        else  :

    80

            input = None

    81

    82

        return html.td(class_=self.colcss)(input)

    83

    84

    def cell_value (self, item) :

    85

        """

    86

            Return value for cell.

    87

        """

    88

    89

        # XXX: this is sometimes broken, figure out how to index by column

    90

        return getattr(item, self.attr)

    91

    92

    def cell_html (self, item, value) :

    93

        """

    94

            Render contents for <td>.

    95

        """

    96

    97

        if self.rowhtml :

    98

            return self.rowhtml(item)

    99

        else :

   100

            return value

   101

   102

    def cell_css (self, item, value=None, hilight=None) :

   103

        """

   104

            Return CSS classes for <td>.

   105

        """

   106

   107

        if self.colcss :

   108

            yield self.colcss

   109

   110

        if self.rowcss :

   111

            yield self.rowcss(item)

   112

   113

        if hilight :

   114

            # lookup attr/value

   115

            hilight = self.attr in hilight and value in hilight[self.attr]

   116

   117

        if hilight :

   118

            yield 'hilight'

   119

   120

    def cell_title (self, item, value=None) :

   121

        """

   122

            Return title= for <td>

   123

        """

   124

   125

        if self.rowtitle :

   126

            return self.rowtitle(item)

   127

   128

    def render_cell (self, item, table, filters=None, hilight=None) :

   129

        """

   130

            Render <td> for item in <tbody> in given Table.

   131

   132

                hilight     - optionally higlight given { attr: value }'s using CSS.

   133

        """

   134

   135

        value = self.cell_value(item)

   136

   137

        if self.rowfilter and filters is not None :

   138

            # filter-link by row-value

   139

            filters = { self.attr: value }

   140

        else :

   141

            filters = None

   142

   143

        yield table.render_cell(self.cell_html(item, value),

   144

                css         = tuple(self.cell_css(item, value, hilight=hilight)),

   145

                filters     = filters,

   146

                title       = self.cell_title(item),

   147

        )

   148

   149

class Table (object) :

   150

    """

   151

        Render <table> with Columns from SQLAlchemy ORM model class.

   152

    """

   153

   154

    COLUMNS = ()

   155

    ITEMS = None

   156

   157

    # attr -> column

   158

    ATTRS = dict((col.attr, col) for col in COLUMNS)

   159

   160

    # items per page

   161

    PAGE = 10

   162

   163

    def __init__ (self, url, columns=None, table_url=None, item_url=None, caption=None, page=None) :

   164

        """

   165

                url         - pvl.web.Handler.url()

   166

                table_url   - ListHandler, or self?

   167

                item_url    - ItemHandler, or self#id

   168

                columns     - sequence of Columns

   169

                caption     - optional <caption>

   170

                page        - items per page

   171

        """

   172

   173

        self.url = url

   174

   175

        self.columns = columns or self.COLUMNS

   176

        self.table_url = table_url

   177

        self.item_url = item_url

   178

   179

        self.caption = caption

   180

        self.page = page or self.PAGE

   181

   182

    def tableurl (self, filters=None, **opts) :

   183

        """

   184

            URL for table with given opts, keeping our sorting/filtering unless overriden.

   185

        """

   186

   187

        args = dict()

   188

   189

        # apply

   190

        if filters :

   191

            args.update(filters)

   192

   193

        if opts :

   194

            args.update(opts)

   195

   196

        return self.url(self.table_url, **args)

   197

   198

    def sorturl (self, attr, sort=None, **opts) :

   199

        """

   200

            URL for table sorted by given column, reversing direction if already sorting by given column.

   201

        """

   202

   203

        if not sort :

   204

            sort = attr

   205

        elif sort.lstrip('+-') != attr :

   206

            sort = attr

   207

        elif sort.startswith('-') :

   208

            sort = "+" + attr

   209

        else :

   210

            sort = "-" + attr

   211

   212

        return self.tableurl(sort=sort, **opts)

   213

   214

    def itemurl (self, item) :

   215

        """

   216

            URL for given item, by id.

   217

        """

   218

   219

        if self.item_url :

   220

            # separate page

   221

            return self.url(self.item_url, id=item.id)

   222

        else :

   223

            # to our table

   224

            return '#{id}'.format(id=item.id)

   225

   226

    def render_head (self, filters=None, sort=None) :

   227

        """

   228

            Yield header columns in table header.

   229

        """

   230

   231

        # id

   232

        yield html.th('#')

   233

   234

        for column in self.columns :

   235

            yield column.render_header(sorturl=self.sorturl(column.attr, sort=sort, filters=filters))

   236

   237

    def render_head_filters (self, filters=None) :

   238

        """

   239

            Yield filter columns in table header.

   240

        """

   241

   242

        # id

   243

        yield html.td(html.input(type='submit', value=u'\u00BF'))

   244

   245

        for column in self.columns :

   246

            yield column.render_header_filter(filters)

   247

   248

    def render_cell (self, rowhtml, css=(), filters=None, title=None) :

   249

        """

   250

            Render a single cell.

   251

   252

                htmlvalue   - rendered value

   253

                css         - css classes to apply

   254

                filters     - render filter link for filter-values?

   255

                title       - mouseover title for cell

   256

        """

   257

   258

        if filters :

   259

            cell = html.a(href=self.tableurl(**filters))(rowhtml)

   260

        else :

   261

            cell = rowhtml

   262

   263

        css = ' '.join(cls for cls in css if cls)

   264

   265

        return html.td(class_=css, title=title)(cell)

   266

   267

    def render_row (self, item, **opts) :

   268

        """

   269

            Yield columns for row.

   270

        """

   271

   272

        yield html.th(

   273

            html.a(href=self.itemurl(item))("#")

   274

        ),

   275

   276

        for column in self.columns :

   277

            yield column.render_cell(item, self, **opts)

   278

   279

    def render_body (self, rows, **opts) :

   280

        """

   281

            Yield body rows.

   282

        """

   283

   284

        for i, item in enumerate(rows) :

   285

            yield html.tr(class_=('alternate' if i % 2 else None), id=item.id)(

   286

                self.render_row(item, **opts)

   287

            )

   288

   289

    def render_pagination (self, page, count=None, **opts) :

   290

        """

   291

            Render pagination links.

   292

        """

   293

   294

        if count is not None :

   295

            pages = int(math.ceil(count / self.page))

   296

        else :

   297

            pages = None

   298

   299

        if page > 0 :

   300

            yield html.a(href=self.tableurl(page=0, **opts))(html("«« First"))

   301

            yield html.a(href=self.tableurl(page=(page - 1), **opts))(html("« Prev"))

   302

   303

        if pages :

   304

            yield html.span("Page {page} of {pages}".format(page=(page + 1), pages=pages))

   305

        else :

   306

            yield html.span("Page {page}".format(page=(page + 1)))

   307

   308

        yield html.a(href=self.tableurl(page=(page + 1), **opts))(html("» Next"))

   309

   310

    def render_foot (self, query, page, **opts) :

   311

        """

   312

            Render pagination/host count in footer.

   313

        """

   314

   315

        # XXX: does separate SELECT count()

   316

        count = query.count()

   317

   318

        if page is None :

   319

            return "{count} {items}".format(count=count, items=self.ITEMS)

   320

        else :

   321

            # XXX: count is just the count we got..

   322

            return self.render_pagination(page, count=None, **opts)

   323

   324

    def render (self, query, filters=None, sort=None, page=None, hilight=None) :

   325

        """

   326

            Return <table> element. Wrapped in <form> if filters.

   327

                query   - filter()'d sort()'d SELECT query()

   328

                filters - None for no filtering ui, dict of filters otherwise.

   329

                sort    - None for no sorting ui, sort-attr otherwise.

   330

                page    - display pagination for given page

   331

                hilight - { attr: value } cells to hilight

   332

        """

   333

   334

        # render table

   335

        table = html.table(

   336

            html.caption(self.caption) if self.caption else None,

   337

            html.thead(

   338

                html.tr(

   339

                    self.render_head(filters=filters, sort=sort)

   340

                ),

   341

                (

   342

                    html.tr(class_='filter')(self.render_head_filters(filters=filters))

   343

                ) if filters is not None else None,

   344

            ),

   345

            html.tbody(

   346

                self.render_body(query,

   347

                    filters = filters,

   348

                    hilight = hilight,

   349

                )

   350

            ),

   351

            html.tfoot(

   352

                html.tr(

   353

                    html.td(colspan=(1 + len(self.columns)))(

   354

                        self.render_foot(query, page, filters=filters, sort=sort)

   355

                    )

   356

                )

   357

            )

   358

        )

   359

   360

        # filters form?

   361

        if filters is None :

   362

            return table

   363

        else :

   364

            return html.form(method='get', action=self.tableurl())(

   365

                html.input(type='hidden', name='sort', value=sort),

   366

                table,

   367

            )

   368

   369

    def json (self, item) :

   370

        """

   371

            Yield JSON params for given item.

   372

        """

   373

   374

        yield 'id', item.id

   375

   376

        for column in self.columns :

   377

            value = column.cell_value(item)

   378

   379

            yield column.attr, dict(

   380

                    html    = unicode(html(column.cell_html(item, value))),

   381

                    css     = tuple(column.cell_css(item, value)),

   382

                    title   = column.cell_title(item, value),

   383

            )

   384

   385

class TableHandler (object) :

   386

    """

   387

        Mixin for handling Table args/rendering.

   388

    """

   389

   390

    CSS = (

   391

        "/static/dhcp/table.css", 

   392

    )

   393

   394

    TABLE = None

   395

    DB_TABLE = None

   396

   397

    # target Handlers for table links

   398

    TABLE_URL = None

   399

    TABLE_ITEM_URL = None

   400

   401

    def init (self) :

   402

        """

   403

            Bind self.table

   404

        """

   405

   406

        super(TableHandler, self).init()

   407

   408

        self.table = self.TABLE(self.url,

   409

                table_url   = self.TABLE_URL,

   410

                item_url    = self.TABLE_ITEM_URL,

   411

        )

   412

   413

    def query (self) :

   414

        """

   415

            Database SELECT query.

   416

        """

   417

   418

        if self.DB_CLASS is None :

   419

            raise NotImplementedError()

   420

   421

        return self.db.query(self.DB_CLASS)

   422

   423

    def sort (self, query) :

   424

        """

   425

            Apply ?sort= from requset args to query.

   426

   427

            Return { attr: sort }, query

   428

        """

   429

   430

        sort = self.request.args.get('sort')

   431

   432

        if sort :

   433

            name = sort.lstrip('+-')

   434

        else :

   435

            name = None

   436

   437

        if name :

   438

            order_by = self.TABLE.ATTRS[name].column

   439

        else :

   440

            order_by = self.TABLE.SORT # default

   441

   442

        # prefix -> ordering

   443

        if not sort :

   444

            pass

   445

        elif sort.startswith('+') :

   446

            order_by = order_by.asc()

   447

        elif sort.startswith('-') :

   448

            order_by = order_by.desc()

   449

        else :

   450

            pass

   451

   452

        # apply

   453

        log.debug("sort: %s", order_by)

   454

   455

        query = query.order_by(order_by)

   456

   457

        return sort, query

   458

   459

    def filter_attr (self, attr, value) :

   460

        """

   461

            Return filter expression for given attr == value

   462

        """

   463

   464

        # preprocess

   465

        like = False

   466

   467

        if value.endswith('*') :

   468

            like = value.replace('*', '%')

   469

   470

        # filter

   471

        column = self.TABLE.ATTRS[attr].column

   472

   473

        if like :

   474

            return (column.like(like))

   475

        else :

   476

            return (column == value)

   477

   478

    def _filter (self, attr, values) :

   479

        """

   480

            Apply filters for given attr -> (value, expression)

   481

        """

   482

   483

        for value in values :

   484

            value = value.strip()

   485

   486

            # ignore empty fields

   487

            if not value :

   488

                continue

   489

   490

            # lookup attr-specific filter

   491

            filter = getattr(self, 'filter_{attr}'.format(attr=attr), None)

   492

   493

            if filter :

   494

                filter = filter(value)

   495

            else :

   496

                # use generic

   497

                filter = self.filter_attr(attr, value)

   498

   499

            log.debug("%s: %s: %s", attr, value, filter)

   500

   501

            yield value, filter

   502

   503

    def filter (self, query) :

   504

        """

   505

            Apply filters from request.args against given hosts.

   506

   507

            Returns (filters, hosts).

   508

        """

   509

   510

        # filter?

   511

        filters = {}

   512

   513

        for attr in self.TABLE.ATTRS :

   514

            # from request args

   515

            values = self.request.args.getlist(attr)

   516

   517

            # lookup attr filters as expressions

   518

            value_filters = list(self._filter(attr, values))

   519