1

from pvl.web import html

     2

from pvl.verkko import web, db

     3

     4

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

     5

     6

class Column (object) :

     7

    """

     8

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

     9

    """

    10

    11

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

    12

        """

    13

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

    14

            title       - title of column

    15

            column      - the model column property, used to build queries

    16

            rowhtml     - function returning column value as HTML for model

    17

            sort        - allow sorting by column

    18

            filter      - allow filtering by column

    19

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

    20

            rowfilter   - allow filtering by row value

    21

            rowtitle    - function returning column title for model

    22

            rowcss      - function returning column class for model

    23

        """

    24

    25

        if colcss is True :

    26

            colcss = attr

    27

    28

        self.attr = attr

    29

        self.title = title

    30

        self.column = column

    31

    32

        # column attrs

    33

        self.sort = sort

    34

        self.filter = filter

    35

        self.colcss = colcss

    36

    37

        # row attrs

    38

        self.rowhtml = rowhtml

    39

        self.rowfilter = rowfilter

    40

        self.rowtitle = rowtitle

    41

        self.rowcss = rowcss

    42

    43

    def render_header (self, sorturl) :

    44

        """

    45

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

    46

        """

    47

    48

        header = self.title

    49

    50

        if self.sort :

    51

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

    52

    53

        return html.th(header)

    54

    55

    def render_filter_input (self, filters) :

    56

        """

    57

            Render filter <input> in given Table.

    58

        """

    59

    60

        value = filters.get(self.attr)

    61

    62

        if value :

    63

            # XXX: multi-valued filters?

    64

            value = value[0]

    65

        else :

    66

            value = None

    67

    68

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

    69

    70

    def render_header_filter (self, filters) :

    71

        """

    72

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

    73

        """

    74

    75

        if self.filter :

    76

            input = self.render_filter_input(filters)

    77

        else  :

    78

            input = None

    79

    80

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

    81

    82

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

    83

        """

    84

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

    85

    86

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

    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

            rowhtml = self.rowhtml(item)

    94

        else :

    95

            rowhtml = value

    96

    97

        if self.rowcss :

    98

            rowcss = self.rowcss(item)

    99

        else :

   100

            rowcss = None

   101

   102

        if hilight :

   103

            # lookup attr/value

   104

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

   105

   106

        if self.rowfilter :

   107

            # filter-link by row-value

   108

            filters = { self.attr: value }

   109

        else :

   110

            filters = None

   111

   112

        # css classes to apply, or None's

   113

        css = (

   114

                self.colcss, 

   115

                rowcss, 

   116

                'hilight' if hilight else None

   117

        )

   118

   119

        if self.rowtitle :

   120

            rowtitle = self.rowtitle(item)

   121

        else :

   122

            rowtitle = None

   123

   124

        yield table.render_cell(rowhtml,

   125

                css         = css,

   126

                filters     = filters,

   127

                rowtitle    = rowtitle,

   128

        )

   129

   130

class Table (object) :

   131

    """

   132

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

   133

    """

   134

   135

    COLUMNS = ()

   136

   137

    # attr -> column

   138

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

   139

   140

    # items per page

   141

    PAGE = 10

   142

   143

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

   144

        """

   145

                url         - pvl.web.Handler.url()

   146

                table_url   - ListHandler, or self?

   147

                item_url    - ItemHandler, or self#id

   148

                columns     - sequence of Columns

   149

                caption     - optional <caption>

   150

                page        - items per page

   151

        """

   152

   153

        self.url = url

   154

   155

        self.columns = columns or self.COLUMNS

   156

        self.table_url = table_url

   157

        self.item_url = item_url

   158

   159

        self.caption = caption

   160

        self.page = page or self.PAGE

   161

   162

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

   163

        """

   164

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

   165

        """

   166

   167

        args = dict()

   168

   169

        # apply

   170

        if filters :

   171

            args.update(filters)

   172

   173

        if opts :

   174

            args.update(opts)

   175

   176

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

   177

   178

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

   179

        """

   180

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

   181

        """

   182

   183

        if not sort :

   184

            sort = attr

   185

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

   186

            sort = attr

   187

        elif sort.startswith('-') :

   188

            sort = "+" + attr

   189

        else :

   190

            sort = "-" + attr

   191

   192

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

   193

   194

    def itemurl (self, item) :

   195

        """

   196

            URL for given item, by id.

   197

        """

   198

   199

        if self.item_url :

   200

            # separate page

   201

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

   202

        else :

   203

            # to our table

   204

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

   205

   206

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

   207

        """

   208

            Yield header columns in table header.

   209

        """

   210

   211

        # id

   212

        yield html.th('#')

   213

   214

        for column in self.columns :

   215

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

   216

   217

    def render_head_filters (self, filters=None) :

   218

        """

   219

            Yield filter columns in table header.

   220

        """

   221

   222

        # id

   223

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

   224

   225

        for column in self.columns :

   226

            yield column.render_header_filter(filters)

   227

   228

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

   229

        """

   230

            Render a single cell.

   231

   232

                htmlvalue   - rendered value

   233

                css         - css classes to apply

   234

                filters     - render filter link for filter-values?

   235

                title       - mouseover title for cell

   236

        """

   237

   238

        if filters :

   239

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

   240

        else :

   241

            cell = rowhtml

   242

   243

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

   244

   245

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

   246

   247

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

   248

        """

   249

            Yield columns for row.

   250

        """

   251

   252

        for column in self.columns :

   253

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

   254

   255

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

   256

        """

   257

            Yield body rows.

   258

        """

   259

   260

        for i, item in enumerate(rows) :

   261

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

   262

                html.th(

   263

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

   264

                ),

   265

   266

                self.render_row(item, **opts)

   267

            )

   268

   269

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

   270

        """

   271

            Render pagination links.

   272

        """

   273

   274

        if count is not None :

   275

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

   276

        else :

   277

            pages = None

   278

   279

        if page > 0 :

   280

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

   281

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

   282

   283

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

   284

   285

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

   286

   287

    def render_foot (self, query, page) :

   288

        """

   289

            Render pagination/host count in footer.

   290

        """

   291

   292

        # XXX: does separate SELECT count()

   293

        count = query.count()

   294

   295

        if page :

   296

            return self.render_pagination(page, count)

   297

        else :

   298

            return "{count} hosts".format(count=count)

   299

   300

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

   301

        """

   302

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

   303

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

   304

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

   305

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

   306

                page    - display pagination for given page

   307

                hilight - { attr: value } cells to hilight

   308

        """

   309

   310

        # render table

   311

        table = html.table(

   312

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

   313

            html.thead(

   314

                html.tr(

   315

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

   316

                ),

   317

                (

   318

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

   319

                ) if filters is not None else None,

   320

            ),

   321

            html.tbody(

   322

                self.render_body(query,

   323

                    hilight = hilight,

   324

                )

   325

            ),

   326

            html.tfoot(

   327

                html.tr(

   328

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

   329

                        self.render_foot(query, page)

   330

                    )

   331

                )

   332

            )

   333

        )

   334

   335

        # filters form?

   336

        if filters is None :

   337

            return table

   338

        else :

   339

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

   340

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

   341

                table,

   342

            )

   343

   344

class TableHandler (object) :

   345

    """

   346

        Mixin for handling Table args/rendering.

   347

    """

   348

   349

    CSS = (

   350

        "/static/dhcp/table.css", 

   351

    )

   352

   353

    TABLE = None

   354

   355

    # target Handlers for table links

   356

    TABLE_URL = None

   357

    TABLE_ITEM_URL = None

   358

   359

    def query (self) :

   360

        """

   361

            Database SELECT query.

   362

        """

   363

   364

        raise NotImplementedError()

   365

   366

    def sort (self, query) :

   367

        """

   368

            Apply ?sort= from requset args to query.

   369

   370

            Return { attr: sort }, query

   371

        """

   372

   373

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

   374

   375

        if sort :

   376

            name = sort.lstrip('+-')

   377

        else :

   378

            name = None

   379

   380

        if name :

   381

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

   382

        else :

   383

            order_by = self.TABLE.SORT # default

   384

   385

        # prefix -> ordering

   386

        if not sort :

   387

            pass

   388

        elif sort.startswith('+') :

   389

            order_by = order_by.asc()

   390

        elif sort.startswith('-') :

   391

            order_by = order_by.desc()

   392

        else :

   393

            pass

   394

   395

        # apply

   396

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

   397

   398

        query = query.order_by(order_by)

   399

   400

        return sort, query

   401

   402

    def filter_attr (self, attr, value) :

   403

        """

   404

            Return filter expression for given attr == value

   405

        """

   406

   407

        # preprocess

   408

        like = False

   409

   410

        if value.endswith('*') :

   411

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

   412

   413

        # filter

   414

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

   415

   416

        if like :

   417

            return (column.like(like))

   418

        else :

   419

            return (column == value)

   420

   421

    def _filter (self, attr, values) :

   422

        """

   423

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

   424

        """

   425

   426

        for value in values :

   427

            value = value.strip()

   428

   429

            # ignore empty fields

   430

            if not value :

   431

                continue

   432

   433

            # lookup attr-specific filter

   434

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

   435

   436

            if filter :

   437

                filter = filter(value)

   438

            else :

   439

                # use generic

   440

                filter = self.filter_attr(attr, value)

   441

   442

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

   443

   444

            yield value, filter

   445

   446

    def filter (self, query) :

   447

        """

   448

            Apply filters from request.args against given hosts.

   449

   450

            Returns (filters, hosts).

   451

        """

   452

   453

        # filter?

   454

        filters = {}

   455

   456

        for attr in self.TABLE.ATTRS :

   457

            # from request args

   458

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

   459

   460

            # lookup attr filters as expressions

   461

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

   462

   463

            # ignore empty fields

   464

            if not value_filters :

   465

                continue

   466

   467

            # filtering values, and filter expressions

   468

            values, expressions = zip(*value_filters)

   469

   470

            # apply

   471

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

   472

            filters[attr] = values

   473

   474

        return filters, query

   475

   476

    def filters_title (self) :

   477

        """

   478

            Return a string representing the applied filters.

   479

        """

   480

   481

        return ', '.join(value for values in self.filters.itervalues() for value in values)

   482

   483

    def process (self) :

   484

        """

   485

            Process request args -> self.filters, self.sorts, self.page, self.query

   486

        """

   487

   488

        query = self.query()

   489

   490

        # filter

   491

        self.filters, query = self.filter(query)

   492

   493

        # sort

   494

        # TODO: sort per filter column by default?

   495

        self.sorts, query = self.sort(query)

   496

   497

        # page?

   498

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

   499

   500

        if self.page :

   501

            self.page = int(self.page)

   502

   503

            query = query.offset(self.page * self.TABLE.PAGE).limit(self.TABLE.PAGE)

   504

   505

        self.query = query

   506

   507

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

   508

        """

   509

            Render table

   510

                query       - SELECT query for rows

   511

                filters     - applied filters

   512

                sort        - applied sort

   513

                page        - applied page

   514

                hilight     - hilight given { attr: value } cells

   515

        """

   516

   517

        return self.TABLE(self.url,

   518

                table_url   = self.TABLE_URL,

   519

                item_url    = self.TABLE_ITEM_URL,

   520

        ).render(query,

   521

                filters = filters,

   522

                sort    = sort,

   523

                page    = page,

   524

                hilight = hilight,

   525

        )

   526