Check-in [0c1020da14]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Implemented morglib.database.add_item().
Timelines: family | ancestors | descendants | both | dev
Files: files | file ages | folders
SHA1:0c1020da146322f8a76cb6a3d10f476ebb99edc3
User & Date: mvnathan 2014-09-20 00:15:34
Context
2014-09-20
07:51
Implemented command-line parsing for the new command. check-in: a855b05bad user: mvnathan tags: dev
00:15
Implemented morglib.database.add_item(). check-in: 0c1020da14 user: mvnathan tags: dev
2014-09-19
08:11
Added week, status, and priority to list of predefined properties. check-in: 08ee0f3c5e user: mvnathan tags: dev
Changes

Changes to py/morglib/database.py.

169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
...
346
347
348
349
350
351
352

353
354
355
356
357
358





























































359
360
361
362
363
364
365
...
424
425
426
427
428
429
430







































431
432
433
434
435
436
437
...
738
739
740
741
742
743
744




















































745
746
747
748
749
750
751
    SCHEMA_VERSION = 1

    # Hard-coded string to help identify whether an SQLite file is a Morg
    # database or not.
    MAGIC_PHRASE = ('This is a Morg database.\n' +
                    'Alter it manually at your own peril.')

    def __init__(self, sqlite_file, properties):
        '''Connect to Morg database.

        @param sqlite_file (string) Name of SQLite file containing items.
        @param properties (dict) Initial set of item properties.

        This method opens the SQLite file passed to it. If the file
        doesn't already exist, it will be created (which could involve
................................................................................
        rows = self.execute('select name, type, range from property')
        property_table = {}
        for n, t, r in rows:
            logger.debug('property name: {}, type: {}, range: {}'.
                         format (n, t, r))
            property_table[n] = (t, r)


        logger.info('adding new properties as required')
        for n, (t, r) in properties.iteritems():
            if (n in property_table):
                logger.debug('property {} already exists'.format(n))
                continue
            self.add_property(n, t, r)






























































    def add_property(self, n, t, r = None):
        '''Add a property and create its table.

        @param n (string) Property name.
        @param t (string) Property type.
        @param r (string) Property range.
................................................................................
            create  = ('''create table if not exists property_{}(
                              id integer primary key autoincrement,
                              item_id integer not null,
                              value {} not null {})'''.
                       format(i, t, range_constraint('value', t, r)))
            self.execute(create)








































    def is_empty(self):
        '''Check if database has anything in it.

        @return True if database is empty, False otherwise.

        Once a connection to the Morg SQLite database has been
        established, clients can use this function to see if that
................................................................................
        fmt = lambda v: '{}'.format(float(v)) # convert strings to float
    try:
        return ('check ({} in ({}))'.
                format(column_name,
                       ','.join(map(fmt, range_spec.split(',')))))
    except Exception, e:
        raise property_error(column_name, column_type, range_spec, e)





















































#------------------------------------------------------------------------

##############################################
# Editor config:                             #
##############################################
# Local Variables:                           #







|







 







>
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
...
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
...
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
...
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
    SCHEMA_VERSION = 1

    # Hard-coded string to help identify whether an SQLite file is a Morg
    # database or not.
    MAGIC_PHRASE = ('This is a Morg database.\n' +
                    'Alter it manually at your own peril.')

    def __init__(self, sqlite_file, properties = None):
        '''Connect to Morg database.

        @param sqlite_file (string) Name of SQLite file containing items.
        @param properties (dict) Initial set of item properties.

        This method opens the SQLite file passed to it. If the file
        doesn't already exist, it will be created (which could involve
................................................................................
        rows = self.execute('select name, type, range from property')
        property_table = {}
        for n, t, r in rows:
            logger.debug('property name: {}, type: {}, range: {}'.
                         format (n, t, r))
            property_table[n] = (t, r)

        if (properties):
            logger.info('adding new properties as required')
            for n, (t, r) in properties.iteritems():
                if (n in property_table):
                    logger.debug('property {} already exists'.format(n))
                    continue
                self.add_property(n, t, r)

    def add_item(self, properties):
        '''Add an item to the Morg database, assigning it the given properties.

        @param properties (dict) The new item's various properties.

        The properties dictionary maps property names (strings) to
        property values, which may be either integers, floats, or
        strings.

        Property names may be abbreviated to the shortest, unique prefix.
        This function will search the master property table for a match
        and substitute the abbreviation with the full name.

        Text properties with a defined range may also have their values
        abbreviated.

        This function performs all its database operations in a
        transaction. Thus, any failures will roll back the Morg database
        to its previous state.

        '''
        logger.info('adding new item')
        for p, v in properties.iteritems():
            logger.debug('property {} = {}'.format(p, v))

        with self._db:
            self.execute('insert into item default values')
            item_id = self._db.last_insert_rowid()
            logger.debug('inserted item {}'.format(item_id))

            logger.debug('creating table item_{} for new item'.format(item_id))
            create = '''create table if not exists item_{} (
                            property_id integer not null,
                            value_id integer not null)'''.format(item_id)
            self.execute(create)

            logger.debug('recording properties for new item {}'.format(item_id))
            for p, v in properties.iteritems():
                prop = self.find_property(p)
                val  = cast_property_value(v, prop['name'],
                                              prop['type'], prop['range'])

                prop_id = prop['id']
                msg = ('inserting value ({}) for item {} into property_{}'.
                       format(val, item_id, prop_id))
                logger.debug(msg)
                insert = '''insert into property_{} (item_id, value)
                                values (?,?)'''.format(prop_id)
                self.execute(insert, (item_id, val))
                val_id = self._db.last_insert_rowid()
                logger.debug('inserted row {} into property_{}'.
                             format(val_id, prop_id))

                logger.debug('recording property ID {} value ID {} in item_{}'.
                             format(prop_id, val_id, item_id))
                insert = '''insert into item_{} (property_id, value_id)
                                values (?,?)'''.format(item_id)
                self.execute(insert, (prop_id, val_id))

        logger.info('successfully added new item {}'.format(item_id))

    def add_property(self, n, t, r = None):
        '''Add a property and create its table.

        @param n (string) Property name.
        @param t (string) Property type.
        @param r (string) Property range.
................................................................................
            create  = ('''create table if not exists property_{}(
                              id integer primary key autoincrement,
                              item_id integer not null,
                              value {} not null {})'''.
                       format(i, t, range_constraint('value', t, r)))
            self.execute(create)

    def find_property(self, prop_name):
        '''Search property table for a matching property name.

        @param  prop_name (string) Property name to match.
        @return Dict containing matching property.

        This method searches the <tt>property</tt> table for a property
        whose name matches <tt>prop_name</tt>, which can be an
        abbreviation. Failure to find a match, unique or otherwise, will result
        in a <tt>property_error</tt>.

        On success, this method will return a dictionary containing the
        matching row from the <tt>property</tt> table. The keys and value
        types of the returned dictionary are as follows:

        @li <tt> id </tt>: int
        @li <tt> name </tt>: str
        @li <tt> type </tt>: str
        @li <tt> range </tt>: str or None

        '''
        logger.debug('looking for property matching "{}"'.format(prop_name))
        select = ("select * from property where (name like '{}%')".
                  format(prop_name))
        rows = self.execute(select)

        n = len(rows)
        if (n < 1):
            raise property_error(prop_name, None, None, 'no such property')
        if (n > 1):
            msg = ('prefix "{}" can match properties: {}'.
                   format(prop_name,
                          ', '.join(map(lambda r: r[1], rows))))
            raise property_error(prop_name, None, None, msg)

        logger.debug('found property ({}) matching "{}"'.
                     format(', '.join(map(str, rows[0])), prop_name))
        return dict(zip(('id', 'name', 'type', 'range' ), rows[0]))

    def is_empty(self):
        '''Check if database has anything in it.

        @return True if database is empty, False otherwise.

        Once a connection to the Morg SQLite database has been
        established, clients can use this function to see if that
................................................................................
        fmt = lambda v: '{}'.format(float(v)) # convert strings to float
    try:
        return ('check ({} in ({}))'.
                format(column_name,
                       ','.join(map(fmt, range_spec.split(',')))))
    except Exception, e:
        raise property_error(column_name, column_type, range_spec, e)

def cast_property_value(v, n, t, r):
    '''Cast property value into proper type, possibly completing text prefixes.

    @param  v (string, int, float) The property value to be cast.
    @param  n (string) Property name.
    @param  t (string) Property type.
    @param  r (string or None) Property range.
    @return Property value as string, int, or float.

    This function casts v according to type t. If t is text and it has a
    range specified, v can be a prefix for one of the allowed values. In
    that case, we will search r for an unambiguous prefix that matches v.
    Failure to find a suitable match will result in a
    <tt>property_error</tt> exception.

    Numeric values that cannot be converted into their corresponding
    types will also yield a <tt>property_error</tt> exception.

    '''
    if (t == 'integer'):
        try:
            return int(v)
        except Exception, e:
            msg = ('unable to convert property value {} to int '.format(v) +
                   '(exception: {})'.format(e))
            raise property_error(n, t, r, msg)

    if (t == 'real'):
        try:
            return float(v)
        except Exception, e:
            msg = ('unable to convert property value {} to float '.format(v) +
                   '(exception: {})'.format(e))
            raise property_error(n, t, r, msg)

    # Property type must be text
    if (r is not None):
        logger.debug('looking for value matching "{}"'.format(v))
        completions = filter(lambda s: s.startswith(v), r.split(','))
        n = len(completions)
        if (n < 1):
            raise property_error(n, t, r,
                                 'no property value matching "{}"'.format(v))
        if (n > 1):
            msg = ('ambiguous property value prefix "{}"; can complete {}'.
                   format(v, ', '.join(completions)))
            raise property_error(n, t, r, msg)
        logger.debug('found property value "{}" matching "{}"'.
                     format(completions[0], v))
        return completions[0]
    return v

#------------------------------------------------------------------------

##############################################
# Editor config:                             #
##############################################
# Local Variables:                           #

Changes to wiki/todo.wiki.

21
22
23
24
25
26
27

28
29
30
31
32
33
34
35
  *  Update database constructor doc string.
  *  Use the BETWEEN operator in property range constraints.
  *  Implement a watermark class to ease verification and "Morgification."
  *  The watermark table should be created and populated in a transaction.
  *  Update doc strings to reflect recent changes about the watermark.
  *  Pass database to all commands' <tt>__call__</tt> method.
  *  Generalize Morg so it isn't necessarily task-centric.


<h2>PENDING</h2>

  *  Implement <tt>morglib.database.add_item()</tt>.
  *  Implement <tt>new</tt> command-line processing.
  *  Hook up <tt>new</tt> command to <tt>add_item()</tt>.
  *  Hook up <tt>new</tt> command to <tt>add_property()</tt>.
  *  Add some user documentation.







>



<




21
22
23
24
25
26
27
28
29
30
31

32
33
34
35
  *  Update database constructor doc string.
  *  Use the BETWEEN operator in property range constraints.
  *  Implement a watermark class to ease verification and "Morgification."
  *  The watermark table should be created and populated in a transaction.
  *  Update doc strings to reflect recent changes about the watermark.
  *  Pass database to all commands' <tt>__call__</tt> method.
  *  Generalize Morg so it isn't necessarily task-centric.
  *  Implement <tt>morglib.database.add_item()</tt>.

<h2>PENDING</h2>


  *  Implement <tt>new</tt> command-line processing.
  *  Hook up <tt>new</tt> command to <tt>add_item()</tt>.
  *  Hook up <tt>new</tt> command to <tt>add_property()</tt>.
  *  Add some user documentation.