1

from pvl.web import html

     2

from pvl.verkko import web, db

     3

     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 render_cell (self, item, table, hilight=None) :

    85

        """

    86

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

    87

    88

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

    89

        """

    90

    91

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

    92

        value = getattr(item, self.attr)

    93

    94

        if self.rowhtml :

    95

            rowhtml = self.rowhtml(item)

    96

        else :

    97

            rowhtml = value

    98

    99

        if self.rowcss :

   100

            rowcss = self.rowcss(item)

   101

        else :

   102

            rowcss = None

   103

   104

        if hilight :

   105

            # lookup attr/value

   106

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

   107

   108

        if self.rowfilter :

   109

            # filter-link by row-value

   110

            filters = { self.attr: value }

   111

        else :

   112

            filters = None

   113

   114

        # css classes to apply, or None's

   115

        css = (

   116

                self.colcss, 

   117

                rowcss, 

   118

                'hilight' if hilight else None

   119

        )

   120

   121

        if self.rowtitle :

   122

            rowtitle = self.rowtitle(item)

   123

        else :

   124

            rowtitle = None

   125

   126

        yield table.render_cell(rowhtml,

   127

                css         = css,

   128

                filters     = filters,

   129

                rowtitle    = rowtitle,

   130

        )

   131

   132

class Table (object) :

   133

    """

   134

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

   135

    """

   136

   137

    COLUMNS = ()

   139

   140

    # attr -> column

   141

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

   142

   143

    # items per page

   144

    PAGE = 10

   145

   146

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

   147

        """

   148

                url         - pvl.web.Handler.url()

   149

                table_url   - ListHandler, or self?

   150

                item_url    - ItemHandler, or self#id

   151

                columns     - sequence of Columns

   152

                caption     - optional <caption>

   153

                page        - items per page

   154

        """

   155

   156

        self.url = url

   157

   158

        self.columns = columns or self.COLUMNS

   159

        self.table_url = table_url

   160

        self.item_url = item_url

   161

   162

        self.caption = caption

   163

        self.page = page or self.PAGE

   164

   165

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

   166

        """

   167

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

   168

        """

   169

   170

        args = dict()

   171

   172

        # apply

   173

        if filters :

   174

            args.update(filters)

   175

   176

        if opts :

   177

            args.update(opts)

   178

   179

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

   180

   181

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

   182

        """

   183

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

   184

        """

   185

   186

        if not sort :

   187

            sort = attr

   188

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

   189

            sort = attr

   190

        elif sort.startswith('-') :

   191

            sort = "+" + attr

   192

        else :

   193

            sort = "-" + attr

   194

   195

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

   196

   197

    def itemurl (self, item) :

   198

        """

   199

            URL for given item, by id.

   200

        """

   201

   202

        if self.item_url :

   203

            # separate page

   204

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

   205

        else :

   206

            # to our table

   207

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

   208

   209

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

   210

        """

   211

            Yield header columns in table header.

   212

        """

   213

   214

        # id

   215

        yield html.th('#')

   216

   217

        for column in self.columns :

   218

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

   219

   220

    def render_head_filters (self, filters=None) :

   221

        """

   222

            Yield filter columns in table header.

   223

        """

   224

   225

        # id

   226

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

   227

   228

        for column in self.columns :

   229

            yield column.render_header_filter(filters)

   230

   231

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

   232

        """

   233

            Render a single cell.

   234

   235

                htmlvalue   - rendered value

   236

                css         - css classes to apply

   237

                filters     - render filter link for filter-values?

   238

                title       - mouseover title for cell

   239

        """

   240

   241

        if filters :

   242

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

   243

        else :

   244

            cell = rowhtml

   245

   246

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

   247

   248

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

   249

   250

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

   251

        """

   252

            Yield columns for row.

   253

        """

   254

   255

        for column in self.columns :

   256

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

   257

   258

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

   259

        """

   260

            Yield body rows.

   261

        """

   262

   263

        for i, item in enumerate(rows) :

   264

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

   265

                html.th(

   266

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

   267

                ),

   268

   269

                self.render_row(item, **opts)

   270

            )

   271

   273

        """

   274

            Render pagination links.

   275

        """

   276

   277

        if count is not None :

   278

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

   279

        else :

   280

            pages = None

   281

   282

        if page > 0 :

   285

   290

   292

   294

        """

   295

            Render pagination/host count in footer.

   296

        """

   297

   298

        # XXX: does separate SELECT count()

   299

        count = query.count()

   300

   303

        else :

   306

   307

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

   308

        """

   309

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

   310

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

   311

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

   312

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

   313

                page    - display pagination for given page

   314

                hilight - { attr: value } cells to hilight

   315

        """

   316

   317

        # render table

   318

        table = html.table(

   319

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

   320

            html.thead(

   321

                html.tr(

   322

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

   323

                ),

   324

                (

   325

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

   326

                ) if filters is not None else None,

   327

            ),

   328

            html.tbody(

   329

                self.render_body(query,

   330

                    hilight = hilight,

   331

                )

   332

            ),

   333

            html.tfoot(

   334

                html.tr(

   335

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

   337

                    )

   338

                )

   339

            )

   340

        )

   341

   342

        # filters form?

   343

        if filters is None :

   344

            return table

   345

        else :

   346

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

   347

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

   348

                table,

   349

            )

   350

   351

class TableHandler (object) :

   352

    """

   353

        Mixin for handling Table args/rendering.

   354

    """

   355

   356

    CSS = (

   357

        "/static/dhcp/table.css", 

   358

    )

   359

   360

    TABLE = None

   361

   362

    # target Handlers for table links

   363

    TABLE_URL = None

   364

    TABLE_ITEM_URL = None

   365

   366

    def query (self) :

   367

        """

   368

            Database SELECT query.

   369

        """

   370

   371

        raise NotImplementedError()

   372

   373

    def sort (self, query) :

   374

        """

   375

            Apply ?sort= from requset args to query.

   376

   377

            Return { attr: sort }, query

   378

        """

   379

   380

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

   381

   382

        if sort :

   383

            name = sort.lstrip('+-')

   384

        else :

   385

            name = None

   386

   387

        if name :

   388

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

   389

        else :

   390

            order_by = self.TABLE.SORT # default

   391

   392

        # prefix -> ordering

   393

        if not sort :

   394

            pass

   395

        elif sort.startswith('+') :

   396

            order_by = order_by.asc()

   397

        elif sort.startswith('-') :

   398

            order_by = order_by.desc()

   399

        else :

   400

            pass

   401

   402

        # apply

   403

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

   404

   405

        query = query.order_by(order_by)

   406

   407

        return sort, query

   408

   409

    def filter_attr (self, attr, value) :

   410

        """

   411

            Return filter expression for given attr == value

   412

        """

   413

   414

        # preprocess

   415

        like = False

   416

   417

        if value.endswith('*') :

   418

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

   419

   420

        # filter

   421

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

   422

   423

        if like :

   424

            return (column.like(like))

   425

        else :

   426

            return (column == value)

   427

   428

    def _filter (self, attr, values) :

   429

        """

   430

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

   431

        """

   432

   433

        for value in values :

   434

            value = value.strip()

   435

   436

            # ignore empty fields

   437

            if not value :

   438

                continue

   439

   440

            # lookup attr-specific filter

   441

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

   442

   443

            if filter :

   444

                filter = filter(value)

   445

            else :

   446

                # use generic

   447

                filter = self.filter_attr(attr, value)

   448

   449

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

   450

   451

            yield value, filter

   452

   453

    def filter (self, query) :

   454

        """

   455

            Apply filters from request.args against given hosts.

   456

   457

            Returns (filters, hosts).

   458

        """

   459

   460

        # filter?

   461

        filters = {}

   462

   463

        for attr in self.TABLE.ATTRS :

   464

            # from request args

   465

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

   466

   467

            # lookup attr filters as expressions

   468

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

   469

   470

            # ignore empty fields

   471

            if not value_filters :

   472

                continue

   473

   474

            # filtering values, and filter expressions

   475

            values, expressions = zip(*value_filters)

   476

   477

            # apply

   478

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

   479

            filters[attr] = values

   480

   481

        return filters, query

   482

   483

    def filters_title (self) :

   484

        """

   485

            Return a string representing the applied filters.

   486

        """

   487

   488

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

   489

   490

    def process (self) :

   491

        """

   492

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

   493

        """

   494

   495

        query = self.query()

   496

   497

        # filter

   498

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

   499

   500

        # sort

   501

        # TODO: sort per filter column by default?

   502

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

   503

   504

        # page?

   505

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

   506

   507

        if self.page :

   508

            self.page = int(self.page)

   509

   510

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

   511

   512

        self.query = query

   513

   514

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

   515

        """

   516

            Render table

   517

                query       - SELECT query for rows

   518

                filters     - applied filters

   519

                sort        - applied sort

   520

                page        - applied page

   521

                hilight     - hilight given { attr: value } cells

   522

        """

   523

   524

        return self.TABLE(self.url,

   525

                table_url   = self.TABLE_URL,

   526

                item_url    = self.TABLE_ITEM_URL,

   527

        ).render(query,