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_html (self, item) :

    85

        """

    86

            Render contents for <td>.

    87

        """

    88

    89

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

    90

        value = getattr(item, self.attr)

    91

    92

        if self.rowhtml :

    93

            return self.rowhtml(item)

    94

        else :

    95

            return value

    96

    97

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

    98

        """

    99

            Return CSS classes for <td>.

   100

        """

   101

   102

        if self.colcss :

   103

            yield self.colcss

   104

   105

        if self.rowcss :

   106

            yield self.rowcss(item)

   107

   108

        if hilight :

   109

            # lookup attr/value

   110

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

   111

   112

        if hilight :

   113

            yield 'hilight'

   114

   115

    def cell_title (self, item) :

   116

        """

   117

            Return title= for <td>

   118

        """

   119

   120

        if self.rowtitle :

   121

            return self.rowtitle(item)

   122

   123

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

   124

        """

   125

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

   126

   127

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

   128

        """

   129

   130

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

   131

        value = getattr(item, self.attr)

   132

   133

        if self.rowfilter and filters is not None :

   134

            # filter-link by row-value

   135

            filters = { self.attr: value }

   136

        else :

   137

            filters = None

   138

   139

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

   140

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

   141

                filters     = filters,

   142

                title       = self.cell_title(item),

   143

        )

   144

   145

class Table (object) :

   146

    """

   147

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

   148

    """

   149

   150

    COLUMNS = ()

   151

    ITEMS = None

   152

   153

    # attr -> column

   154

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

   155

   156

    # items per page

   157

    PAGE = 10

   158

   159

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

   160

        """

   161

                url         - pvl.web.Handler.url()

   162

                table_url   - ListHandler, or self?

   163

                item_url    - ItemHandler, or self#id

   164

                columns     - sequence of Columns

   165

                caption     - optional <caption>

   166

                page        - items per page

   167

        """

   168

   169

        self.url = url

   170

   171

        self.columns = columns or self.COLUMNS

   172

        self.table_url = table_url

   173

        self.item_url = item_url

   174

   175

        self.caption = caption

   176

        self.page = page or self.PAGE

   177

   178

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

   179

        """

   180

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

   181

        """

   182

   183

        args = dict()

   184

   185

        # apply

   186

        if filters :

   187

            args.update(filters)

   188

   189

        if opts :

   190

            args.update(opts)

   191

   192

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

   193

   194

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

   195

        """

   196

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

   197

        """

   198

   199

        if not sort :

   200

            sort = attr

   201

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

   202

            sort = attr

   203

        elif sort.startswith('-') :

   204

            sort = "+" + attr

   205

        else :

   206

            sort = "-" + attr

   207

   208

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

   209

   210

    def itemurl (self, item) :

   211

        """

   212

            URL for given item, by id.

   213

        """

   214

   215

        if self.item_url :

   216

            # separate page

   217

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

   218

        else :

   219

            # to our table

   220

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

   221

   222

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

   223

        """

   224

            Yield header columns in table header.

   225

        """

   226

   227

        # id

   228

        yield html.th('#')

   229

   230

        for column in self.columns :

   231

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

   232

   233

    def render_head_filters (self, filters=None) :

   234

        """

   235

            Yield filter columns in table header.

   236

        """

   237

   238

        # id

   239

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

   240

   241

        for column in self.columns :

   242

            yield column.render_header_filter(filters)

   243

   244

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

   245

        """

   246

            Render a single cell.

   247

   248

                htmlvalue   - rendered value

   249

                css         - css classes to apply

   250

                filters     - render filter link for filter-values?

   251

                title       - mouseover title for cell

   252

        """

   253

   254

        if filters :

   255

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

   256

        else :

   257

            cell = rowhtml

   258

   259

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

   260

   261

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

   262

   263

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

   264

        """

   265

            Yield columns for row.

   266

        """

   267

   268

        yield html.th(

   269

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

   270

        ),

   271

   272

        for column in self.columns :

   273

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

   274

   275

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

   276

        """

   277

            Yield body rows.

   278

        """

   279

   280

        for i, item in enumerate(rows) :

   281

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

   282

                self.render_row(item, **opts)

   283

            )

   284

   285

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

   286

        """

   287

            Render pagination links.

   288

        """

   289

   290

        if count is not None :

   291

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

   292

        else :

   293

            pages = None

   294

   295

        if page > 0 :

   296

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

   297

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

   298

   299

        if pages :

   300

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

   301

        else :

   302

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

   303

   304

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

   305

   306

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

   307

        """

   308

            Render pagination/host count in footer.

   309

        """

   310

   311

        # XXX: does separate SELECT count()

   312

        count = query.count()

   313

   314

        if page is None :

   315

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

   316

        else :

   317

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

   318

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

   319

   320

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

   321

        """

   322

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

   323

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

   324

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

   325

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

   326

                page    - display pagination for given page

   327

                hilight - { attr: value } cells to hilight

   328

        """

   329

   330

        # render table

   331

        table = html.table(

   332

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

   333

            html.thead(

   334

                html.tr(

   335

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

   336

                ),

   337

                (

   338

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

   339

                ) if filters is not None else None,

   340

            ),

   341

            html.tbody(

   342

                self.render_body(query,

   343

                    filters = filters,

   344

                    hilight = hilight,

   345

                )

   346

            ),

   347

            html.tfoot(

   348

                html.tr(

   349

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

   350

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

   351

                    )

   352

                )

   353

            )

   354

        )

   355

   356

        # filters form?

   357

        if filters is None :

   358

            return table

   359

        else :

   360

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

   361

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

   362

                table,

   363

            )

   364

   365

    def json (self, item) :

   366

        """

   367

            Yield JSON params for given item.

   368

        """

   369

   370

        yield 'id', item.id

   371

   372

        for column in self.columns :

   373

            yield column.attr, dict(

   374

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

   375

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

   376

                    title   = column.cell_title(item),

   377

            )

   378

   379

class TableHandler (object) :

   380

    """

   381

        Mixin for handling Table args/rendering.

   382

    """

   383

   384

    CSS = (

   385

        "/static/dhcp/table.css", 

   386

    )

   387

   388

    TABLE = None

   389

   390

    # target Handlers for table links

   391

    TABLE_URL = None

   392

    TABLE_ITEM_URL = None

   393

   394

    def init (self) :

   395

        """

   396

            Bind self.table

   397

        """

   398

   399

        super(TableHandler, self).init()

   400

   401

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

   402

                table_url   = self.TABLE_URL,

   403

                item_url    = self.TABLE_ITEM_URL,

   404

        )

   405

   406

    def query (self) :

   407

        """

   408

            Database SELECT query.

   409

        """

   410

   411

        raise NotImplementedError()

   412

   413

    def sort (self, query) :

   414

        """

   415

            Apply ?sort= from requset args to query.

   416

   417

            Return { attr: sort }, query

   418

        """

   419

   420

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

   421

   422

        if sort :

   423

            name = sort.lstrip('+-')

   424

        else :

   425

            name = None

   426

   427

        if name :

   428

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

   429

        else :

   430

            order_by = self.TABLE.SORT # default

   431

   432

        # prefix -> ordering

   433

        if not sort :

   434

            pass

   435

        elif sort.startswith('+') :

   436

            order_by = order_by.asc()

   437

        elif sort.startswith('-') :

   438

            order_by = order_by.desc()

   439

        else :

   440

            pass

   441

   442

        # apply

   443

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

   444

   445

        query = query.order_by(order_by)

   446

   447

        return sort, query

   448

   449

    def filter_attr (self, attr, value) :

   450

        """

   451

            Return filter expression for given attr == value

   452

        """

   453

   454

        # preprocess

   455

        like = False

   456

   457

        if value.endswith('*') :

   458

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

   459

   460

        # filter

   461

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

   462

   463

        if like :

   464

            return (column.like(like))

   465

        else :

   466

            return (column == value)

   467

   468

    def _filter (self, attr, values) :

   469

        """

   470

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

   471

        """

   472

   473

        for value in values :

   474

            value = value.strip()

   475

   476

            # ignore empty fields

   477

            if not value :

   478

                continue

   479

   480

            # lookup attr-specific filter

   481

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

   482

   483

            if filter :

   484

                filter = filter(value)

   485

            else :

   486

                # use generic

   487

                filter = self.filter_attr(attr, value)

   488

   489

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

   490

   491

            yield value, filter

   492

   493

    def filter (self, query) :

   494

        """

   495

            Apply filters from request.args against given hosts.

   496

   497

            Returns (filters, hosts).

   498

        """

   499

   500

        # filter?

   501

        filters = {}

   502

   503

        for attr in self.TABLE.ATTRS :

   504

            # from request args

   505

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

   506

   507

            # lookup attr filters as expressions

   508

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

   509

   510

            # ignore empty fields

   511

            if not value_filters :

   512

                continue

   513

   514

            # filtering values, and filter expressions

   515

            values, expressions = zip(*value_filters)

   516

   517

            # apply

   518

            query = query.filter(db.or_(*expressions))

   519

            filters[attr] = values

   520