コード例 #1
0
ファイル: extended.py プロジェクト: henkelund/memobox
    def _install(cls):
        """Install attribute meta data tables"""

        return (
            lambda: (
                DBHelper().query("""
                    CREATE TABLE "attribute" (
                        "_id"    INTEGER PRIMARY KEY AUTOINCREMENT,
                        "code"   TEXT NOT NULL DEFAULT "",
                        "label"  TEXT NOT NULL DEFAULT "",
                        "parent" TEXT NOT NULL DEFAULT "",
                        "type"   TEXT NOT NULL DEFAULT ""
                    )
                """),
                DBHelper().query("""
                    CREATE TABLE "attribute_group" (
                        "_id"   INTEGER PRIMARY KEY AUTOINCREMENT,
                        "code"  TEXT NOT NULL DEFAULT "",
                        "label" TEXT NOT NULL DEFAULT "",
                        "type"  TEXT NOT NULL DEFAULT ""
                    )
                """),
                DBHelper().query("""
                    CREATE TABLE "attribute_group_attribute" (
                        "_id"       INTEGER PRIMARY KEY AUTOINCREMENT,
                        "group"     TEXT NOT NULL DEFAULT "",
                        "attribute" TEXT NOT NULL DEFAULT "",
                        FOREIGN KEY ("group") REFERENCES "attribute_group"("_id")
                            ON DELETE CASCADE ON UPDATE CASCADE,
                        FOREIGN KEY ("attribute") REFERENCES "attribute"("_id")
                            ON DELETE CASCADE ON UPDATE CASCADE
                    )
                """)
            ),
        )
コード例 #2
0
    def install():
        """Install dependencies and tables"""

        FileModel.install()
        q = DBHelper.quote_identifier
        thumb_table = q(ImageHelper._thumbnail_table)
        file_table = q(FileModel._table)
        file_table_pk = q(FileModel._pk)
        InstallHelper.install('image', (
            lambda: (
                DBHelper().query("""
                    CREATE TABLE %s (
                        "_id"       INTEGER PRIMARY KEY AUTOINCREMENT,
                        "file"      INTEGER NOT NULL DEFAULT 0,
                        "width"     INTEGER,
                        "height"    INTEGER,
                        "thumbnail" TEXT,
                        FOREIGN KEY ("file") REFERENCES %s(%s)
                            ON DELETE CASCADE ON UPDATE CASCADE
                    )
                """ % (thumb_table, file_table, file_table_pk)),
                DBHelper().query("""
                    CREATE UNIQUE INDEX "UNQ_FILE_THUMB_SIZE"
                        ON %s("file", "width", "height")
                """ % thumb_table),
                DBHelper().query("""
                    CREATE INDEX "IDX_FILE_THUMB_WIDTH" ON %s("width")
                """ % thumb_table),
                DBHelper().query("""
                    CREATE INDEX "IDX_FILE_THUMB_HEIGHT" ON %s("height")
                """ % thumb_table)
            ),
        ))
コード例 #3
0
    def test_install(self):
        """Test InstallHelper.install"""

        if os.path.isfile('/tmp/box.db'):
            os.unlink('/tmp/box.db')
        DBHelper().set_db('/tmp/box.db')
        InstallHelper.reset()
        module = 'sample_module'
        q = DBHelper.quote_identifier
        install_routines = ["""
            CREATE TABLE IF NOT EXISTS %s (
                id INTEGER
            )
        """ % q(module)]
        InstallHelper.install(module, install_routines)
        self.assertIn('id', DBHelper().describe_table(module))
        self.assertEqual(InstallHelper.version(module), 1)
        install_routines.append(
            lambda: DBHelper().insert(module, [{'id': 1}, {'id': 2}]))
        InstallHelper.install(module, install_routines)
        self.assertEqual(
            DBSelect(module, {'c': 'COUNT(*)'}).query().fetchone()['c'],
            2
        )
        self.assertEqual(InstallHelper.version(module), 2)
        install_routines.append(
            'ALTER TABLE %s ADD COLUMN %s TEXT' % (q(module), q('col')))
        InstallHelper.install(module, install_routines)
        self.assertIn('col', DBHelper().describe_table(module))
        self.assertEqual(InstallHelper.version(module), 3)
        InstallHelper.reset()
        DBHelper().set_db(None)
        os.unlink('/tmp/box.db')
コード例 #4
0
ファイル: file.py プロジェクト: henkelund/memobox
    def _install(cls):
        """Install base table and attributes"""

        DeviceModel.install()
        ExtendedModel.install()
        table = DBHelper.quote_identifier(cls._table)
        pk = DBHelper.quote_identifier(cls._pk)

        return (
            lambda: (
                DBHelper().query("""
                    CREATE TABLE %s (
                        %s           INTEGER PRIMARY KEY AUTOINCREMENT,
                        "name"       TEXT    NOT NULL DEFAULT '',
                        "abspath"    TEXT    NOT NULL DEFAULT '',
                        "extension"  TEXT,
                        "type"       TEXT    NOT NULL DEFAULT '',
                        "subtype"    TEXT    NOT NULL DEFAULT '',
                        "charset"    TEXT,
                        "checksum"   TEXT    NOT NULL DEFAULT '',
                        "size"       INTEGER,
                        "created_at" INTEGER,
                        "indexed_at" INTEGER,
                        "is_hidden"  INTEGER NOT NULL DEFAULT 0,
                        "rating"     INTEGER NOT NULL DEFAULT 0,
                        "device"     INTEGER,
                        "devpath"    TEXT    NOT NULL DEFAULT '',
                        FOREIGN KEY ("device") REFERENCES "device"("_id")
                            ON DELETE SET NULL ON UPDATE SET NULL
                    )
                """ % (table, pk)),
                DBHelper().query("""
                    CREATE UNIQUE INDEX "UNQ_FILE_DUPLICATE"
                        ON %s ("name", "devpath", "checksum")
                """ % table),
                cls._create_attribute_tables(),
                cls._create_attribute(
                    'width', 'Width', 'integer', 'Image'),
                cls._create_attribute(
                    'height', 'Height', 'integer', 'Image'),
                cls._create_attribute(
                    'latitude', 'Latitude', 'real', 'Image'),
                cls._create_attribute(
                    'longitude', 'Longitude', 'real', 'Image'),
                cls._create_attribute(
                    'timestamp', 'Timestamp', 'integer', 'Image'),
                cls._create_attribute(
                    'orientation', 'Orientation', 'integer', 'Image')
            ),
        )
コード例 #5
0
ファイル: extended.py プロジェクト: henkelund/memobox
    def _create_attribute(cls, code, label, datatype, group):
        """Create a new attribute for this model"""

        ids = DBHelper().insert('attribute', {
            'code': code,
            'label': label,
            'parent': cls._table,
            'type': datatype
        })
        group_id = cls._get_attribute_group(group, True)
        for new_id in ids:
            DBHelper().insert('attribute_group_attribute', {
                'group': group_id,
                'attribute': new_id
            })
コード例 #6
0
    def tearDown(self):
        """Clean up after test"""

        InstallHelper.reset()
        DBHelper().set_db(None)
        os.unlink('/tmp/box.db')
        FileModel._group_index = {}  # clear cached attr groups
コード例 #7
0
ファイル: test_log.py プロジェクト: henkelund/memobox
    def tearDown(self):
        """Clean up after test"""

        InstallHelper.reset()
        DBHelper().set_db(None)
        os.unlink('/tmp/box.db')
        LogHelper._model_installed = False
コード例 #8
0
ファイル: base.py プロジェクト: henkelund/memobox
    def _db_select(self, key=None):
        """Return a DBSelect querying for this model"""

        if not key:
            key = self.__class__._pk
        where = "%s = ?" % (DBHelper.quote_identifier(key),)
        return DBSelect(self.get_table()).where(where, self.get_data(key)).limit(1)
コード例 #9
0
    def install():
        """Install filter tables"""

        FileModel.install()
        q = DBHelper.quote_identifier
        filter_table = q(FilterHelper._filter_table)
        option_table = q(FilterHelper._filter_option_table)
        value_table = q(FilterHelper._filter_value_table)
        file_table = q(FileModel._table)
        file_table_pk = q(FileModel._pk)

        InstallHelper.install('filter', (lambda: (DBHelper().query("""
                    CREATE TABLE %s (
                        "_id"   INTEGER PRIMARY KEY AUTOINCREMENT,
                        "code"  TEXT NOT NULL DEFAULT '',
                        "label" TEXT NOT NULL DEFAULT ''
                    )
                """ % filter_table), DBHelper().query("""
                    CREATE UNIQUE INDEX "UNQ_FILE_FILTER_CODE"
                        ON %s ("code")
                """ % filter_table), DBHelper().query("""
                    CREATE TABLE %s (
                        "filter" INTEGER NOT NULL DEFAULT 0,
                        "value"  INTEGER NOT NULL DEFAULT 0,
                        "label"  TEXT NOT NULL DEFAULT '',
                        FOREIGN KEY ("filter") REFERENCES %s("_id")
                            ON DELETE CASCADE ON UPDATE CASCADE
                    )
                """ % (option_table, filter_table)), DBHelper().query("""
                    CREATE UNIQUE INDEX "UNQ_FILE_FILTER_OPTION_FILTER_VALUE"
                        ON %s("filter", "value")
                """ % option_table), DBHelper().query("""
                    CREATE TABLE %s (
                        "file"   INTEGER NOT NULL DEFAULT 0,
                        "filter" INTEGER NOT NULL DEFAULT 0,
                        "value"  INTEGER NOT NULL DEFAULT 0,
                        FOREIGN KEY ("file") REFERENCES %s(%s)
                            ON DELETE CASCADE ON UPDATE CASCADE,
                        FOREIGN KEY ("filter") REFERENCES %s("_id")
                            ON DELETE CASCADE ON UPDATE CASCADE
                    )
                """ % (value_table, file_table, file_table_pk, filter_table)),
                                                  DBHelper().query("""
                    CREATE UNIQUE INDEX "UNQ_FILE_FILTER_VALUE"
                        ON %s("file", "filter", "value")
                """ % value_table), DBHelper().query("""
                    CREATE INDEX "IDX_FILE_FILTER_VALUE_FILE" ON %s("file")
                """ % value_table), DBHelper().query("""
                    CREATE INDEX "IDX_FILE_FILTER_VALUE_FILTER" ON %s("filter")
                """ % value_table), DBHelper().query("""
                    CREATE INDEX "IDX_FILE_FILTER_VALUE_VALUE" ON %s("value")
                """ % value_table)), ))
コード例 #10
0
    def setUp(self):
        """LogModel test set up"""

        if os.path.isfile('/tmp/box.db'):
            os.unlink('/tmp/box.db')
        DBHelper().set_db('/tmp/box.db')
        InstallHelper.reset()
        LogModel.install()
コード例 #11
0
ファイル: install.py プロジェクト: henkelund/memobox
    def _initialize(cls, force=False):
        """Load version info from db"""

        if cls._state and not force:
            return

        if not DBHelper().describe_table(cls._table):
            DBHelper().query("""
                CREATE TABLE %s (
                    "module" TEXT PRIMARY KEY,
                    "version" INTEGER NOT NULL DEFAULT 0
                )
            """ % DBHelper.quote_identifier(cls._table))

        cls._state = {}
        for module in DBSelect(cls._table).query().fetchall():
            cls._state[module['module']] = module['version']
コード例 #12
0
ファイル: base.py プロジェクト: henkelund/memobox
    def _db_select(self, key=None):
        """Return a DBSelect querying for this model"""

        if not key:
            key = self.__class__._pk
        where = '%s = ?' % (DBHelper.quote_identifier(key), )
        return DBSelect(self.get_table()).where(where,
                                                self.get_data(key)).limit(1)
コード例 #13
0
ファイル: test_log.py プロジェクト: henkelund/memobox
    def setUp(self):
        """LogHelper test set up"""

        if os.path.isfile('/tmp/box.db'):
            os.unlink('/tmp/box.db')
        DBHelper().set_db('/tmp/box.db')
        InstallHelper.reset()
        LogHelper._model_installed = False
コード例 #14
0
    def add_file_thumbnail(cls, file_id, width, height, thumbnail):
        """"""

        DBHelper().insert(cls._thumbnail_table, {
            'file': file_id,
            'width': width,
            'height': height,
            'thumbnail': thumbnail
        }, True)
コード例 #15
0
ファイル: device.py プロジェクト: henkelund/memobox
    def _install(cls):
        """Define install routines for this model"""

        table = DBHelper.quote_identifier(cls._table)
        return (lambda: (DBHelper().query("""
                        CREATE TABLE %s (
                            "_id"          INTEGER PRIMARY KEY AUTOINCREMENT,
                            "serial"       TEXT NOT NULL DEFAULT '',
                            "product_id"   TEXT NOT NULL DEFAULT '',
                            "product_name" TEXT NOT NULL DEFAULT '',
                            "model"        TEXT NOT NULL DEFAULT '',
                            "vendor_id"    TEXT NOT NULL DEFAULT '',
                            "vendor_name"  TEXT NOT NULL DEFAULT '',
                            "manufacturer" TEXT NOT NULL DEFAULT ''
                        )
                    """ % table), DBHelper().query(
            'CREATE UNIQUE INDEX "UNQ_DEVICE_SERIAL" ON %s ("serial")' % table)
                         ), )
コード例 #16
0
ファイル: test_db.py プロジェクト: henkelund/memobox
    def setUp(self):
        """Initiate DBHelper singleton"""

        if os.path.isfile('/tmp/box.db'):
            os.unlink('/tmp/box.db')
        DBHelper().set_db('/tmp/box.db')
        self._helper = DBHelper()
        self._helper.query("""
            CREATE TABLE "test_table" (
                "col_a" INTEGER PRIMARY KEY AUTOINCREMENT,
                "col_b" INTEGER
            )
        """)
        self._num_rows = randint(128, 255)
        for i in range(self._num_rows):
            self._helper.query(
                'INSERT INTO "test_table" ("col_b") VALUES (?)',
                [randint(0, 99)]
            )
コード例 #17
0
ファイル: test_base.py プロジェクト: henkelund/memobox
    def _install(cls):
        return (
            # "Version #1" routine
            """
            CREATE TABLE %s (
                "_id" INTEGER PRIMARY KEY AUTOINCREMENT,
                "some_attribute" INTEGER,
                "another_attribute" TEXT
            )
            """ % DBHelper.quote_identifier(cls._table),

            # "Version #2" routine
            lambda: DBHelper().insert(SampleModel._table, (
                {'_id': 1, 'some_attribute': 10, 'another_attribute': 'ten'},
                {'_id': 2, 'some_attribute': 20, 'another_attribute': 'twenty'},
                {'_id': 3, 'some_attribute': 30, 'another_attribute': 'thirty'}
            )
        )
)
コード例 #18
0
ファイル: file.py プロジェクト: henkelund/memobox
    def _install(cls):
        """Install base table and attributes"""

        DeviceModel.install()
        ExtendedModel.install()
        table = DBHelper.quote_identifier(cls._table)
        pk = DBHelper.quote_identifier(cls._pk)

        return (
            lambda:
            (DBHelper().query("""
                    CREATE TABLE %s (
                        %s           INTEGER PRIMARY KEY AUTOINCREMENT,
                        "name"       TEXT    NOT NULL DEFAULT '',
                        "abspath"    TEXT    NOT NULL DEFAULT '',
                        "extension"  TEXT,
                        "type"       TEXT    NOT NULL DEFAULT '',
                        "subtype"    TEXT    NOT NULL DEFAULT '',
                        "charset"    TEXT,
                        "checksum"   TEXT    NOT NULL DEFAULT '',
                        "size"       INTEGER,
                        "created_at" INTEGER,
                        "indexed_at" INTEGER,
                        "is_hidden"  INTEGER NOT NULL DEFAULT 0,
                        "rating"     INTEGER NOT NULL DEFAULT 0,
                        "device"     INTEGER,
                        "devpath"    TEXT    NOT NULL DEFAULT '',
                        FOREIGN KEY ("device") REFERENCES "device"("_id")
                            ON DELETE SET NULL ON UPDATE SET NULL
                    )
                """ % (table, pk)), DBHelper().query("""
                    CREATE UNIQUE INDEX "UNQ_FILE_DUPLICATE"
                        ON %s ("name", "devpath", "checksum")
                """ % table), cls._create_attribute_tables(),
             cls._create_attribute('width', 'Width', 'integer', 'Image'),
             cls._create_attribute('height', 'Height', 'integer', 'Image'),
             cls._create_attribute('latitude', 'Latitude', 'real', 'Image'),
             cls._create_attribute('longitude', 'Longitude', 'real', 'Image'),
             cls._create_attribute('timestamp', 'Timestamp', 'integer', 'Image'
                                   ),
             cls._create_attribute('orientation', 'Orientation', 'integer',
                                   'Image')), )
コード例 #19
0
ファイル: base.py プロジェクト: henkelund/memobox
    def save(self):
        """Store this models data"""

        data = self.clean_data(self._data)
        if self.id():
            self._db_select().query_update(data)
        else:
            ids = DBHelper().insert(self.get_table(), data)
            self.set_data(self.__class__._pk, ids[0])

        return self
コード例 #20
0
ファイル: test_base.py プロジェクト: henkelund/memobox
    def setUp(self):
        """BaseModel test set up"""

        if os.path.isfile('/tmp/box.db'):
            os.unlink('/tmp/box.db')
        DBHelper().set_db('/tmp/box.db')
        InstallHelper.reset()
        cache = FileSystemCache('/tmp/werkzeug')
        cache.clear()
        BaseModel.set_cache(cache)
        SampleModel.install()
コード例 #21
0
    def _install(cls):
        """Install base table and attributes"""

        ExtendedModel.install()
        table = DBHelper.quote_identifier(cls._table)

        return (
            lambda:
            (DBHelper().query("""
                    CREATE TABLE %s (
                        "_id"    INTEGER PRIMARY KEY AUTOINCREMENT,
                        "static" TEXT NOT NULL DEFAULT ''
                    )
                """ % table), SampleExtModel._create_attribute_tables(),
             SampleExtModel._create_attribute(
                 'sample_attr_1', 'Sample Attribute #1', 'integer', 'Default'),
             SampleExtModel._create_attribute(
                 'sample_attr_2', 'Sample Attribute #2', 'text', 'Default'),
             SampleExtModel._create_attribute(
                 'sample_attr_3', 'Sample Attribute #3', 'real', 'Not Default')
             ), )
コード例 #22
0
ファイル: log.py プロジェクト: henkelund/memobox
    def _install(cls):
        """Define install routines for this model"""

        table = DBHelper.quote_identifier(cls._table)
        pk = DBHelper.quote_identifier(cls._pk)
        return (
            lambda: (
                DBHelper().query(
                    """
                    CREATE TABLE %s (
                        %s           INTEGER PRIMARY KEY AUTOINCREMENT,
                        "level"      INTEGER NOT NULL DEFAULT %d,
                        "message"    TEXT NOT NULL DEFAULT '',
                        "created_at" INTEGER NOT NULL DEFAULT (STRFTIME('%%s', 'now'))
                    )"""
                    % (table, pk, cls.DEBUG)
                ),
                DBHelper().query('CREATE INDEX "IDX_LOG_LEVEL" ON %s ("level")' % table),
                DBHelper().query('CREATE INDEX "IDX_LOG_CREATED_AT" ON %s ("created_at")' % table),
            ),
        )
コード例 #23
0
    def join_filter_values(cls, select, filter_id, file_field, columns=()):
        """Support method for indexers needed since
        sqlite does not implement right join
        """

        filter_id = cls.get_filter_id(filter_id)
        file_field = DBHelper.quote_identifier(file_field)
        select.left_join(
            (cls._filter_value_table, 'fv'),
            'fv.filter = %d AND %s = fv.file' % (filter_id, file_field),
            columns)
        return select
コード例 #24
0
ファイル: extended.py プロジェクト: henkelund/memobox
    def _create_attribute_tables(cls):
        """Install attribute tables for this model"""

        q = DBHelper.quote_identifier
        for datatype in ('text', 'integer', 'real', 'blob'):
            table_name = cls._get_attribute_table(datatype)
            idx = '%s_ATTR_%s' % (cls._table.upper(), datatype.upper())            

            DBHelper().query("""
                CREATE TABLE %s (
                    "_id"       INTEGER PRIMARY KEY AUTOINCREMENT,
                    "attribute" INTEGER,
                    "parent"    INTEGER,
                    "value"     %s,
                    FOREIGN KEY ("attribute") REFERENCES "attribute"("_id")
                        ON DELETE CASCADE ON UPDATE CASCADE,
                    FOREIGN KEY ("parent") REFERENCES %s(%s)
                        ON DELETE CASCADE ON UPDATE CASCADE
                );
                """ % (q(table_name), datatype.upper(),
                            q(cls._table), q(cls._pk))
            )
            DBHelper().query("""
                CREATE UNIQUE INDEX "UNQ_%s_ATTR_PARENT"
                    ON %s ("attribute", "parent");
                """ % (idx, q(table_name))
            )
            DBHelper().query("""
                CREATE INDEX "IDX_%s_ATTR" ON %s ("attribute");
                """ % (idx, q(table_name))
            )
            DBHelper().query("""
                CREATE INDEX "IDX_%s_PARENT" ON %s ("parent");
                """ % (idx, q(table_name))
            )
            DBHelper().query("""
                CREATE INDEX "IDX_%s_VALUE" ON %s ("value");
                """ % (idx, q(table_name))
            )
コード例 #25
0
ファイル: log.py プロジェクト: henkelund/memobox
    def _install(cls):
        """Define install routines for this model"""

        table = DBHelper.quote_identifier(cls._table)
        pk = DBHelper.quote_identifier(cls._pk)
        return (
            lambda: (
                DBHelper().query("""
                    CREATE TABLE %s (
                        %s           INTEGER PRIMARY KEY AUTOINCREMENT,
                        "level"      INTEGER NOT NULL DEFAULT %d,
                        "message"    TEXT NOT NULL DEFAULT '',
                        "created_at" INTEGER NOT NULL DEFAULT (STRFTIME('%%s', 'now'))
                    )""" % (table, pk, cls.DEBUG)),
                DBHelper().query(
                    'CREATE INDEX "IDX_LOG_LEVEL" ON %s ("level")'
                        % table),
                DBHelper().query(
                    'CREATE INDEX "IDX_LOG_CREATED_AT" ON %s ("created_at")'
                        % table)
            ),
        )
コード例 #26
0
ファイル: filter.py プロジェクト: henkelund/memobox
    def join_filter_values(cls, select, filter_id, file_field, columns=()):
        """Support method for indexers needed since
        sqlite does not implement right join
        """

        filter_id = cls.get_filter_id(filter_id)
        file_field = DBHelper.quote_identifier(file_field)
        select.left_join(
            (cls._filter_value_table, 'fv'),
            'fv.filter = %d AND %s = fv.file' % (filter_id, file_field),
            columns
        )
        return select
コード例 #27
0
ファイル: install.py プロジェクト: henkelund/memobox
    def _update_version(cls, module, version):
        """Update module version number"""

        cls._initialize()
        if module in cls._state:
            DBSelect(cls._table
                ).where('module = ?', module
                ).query_update({'version': version})
        else:
            DBHelper().insert(cls._table, {
                'module': module,
                'version': version
            })
        cls._initialize(True)
コード例 #28
0
    def add_filter_option(cls, filter_id, value, label=None):
        """Add an option to the given filter"""

        if cls.filter_has_option(filter_id, value):
            return

        filter_id = cls.get_filter_id(filter_id)
        if not label:
            label = str(value)

        DBHelper().insert(cls._filter_option_table, {
            'filter': filter_id,
            'value': value,
            'label': label
        })
コード例 #29
0
    def create_filter(cls, code, label=None):
        """Create a new filter"""

        if not label:
            label = code.capitalize()

        ids = DBHelper().insert(
            cls._filter_table,
            {
                'code': code,
                'label': label
            },
            True  # ignore
        )

        return ids[0] if len(ids) > 0 else None
コード例 #30
0
ファイル: install.py プロジェクト: henkelund/memobox
    def install(cls, module, routines):
        """Run module install/upgrade routines"""

        if not routines:
            return

        version = cls.version(module)
        if version >= len(routines):
            return

        while version < len(routines):
            if isroutine(routines[version]):
                routines[version]()
            else:
                DBHelper().query(routines[version])
            version = version + 1

        cls._update_version(module, version)
コード例 #31
0
ファイル: base.py プロジェクト: henkelund/memobox
    def clean_data(self, data):
        """Clean the given dict from non-savable data"""

        cache_key = 'DESCRIBE_%s' % self._table
        desc = self.__class__._cache.get(cache_key)
        if type(desc) is not dict:
            desc = DBHelper().describe_table(self._table)
            self.__class__._cache.set(cache_key, desc)

        clean_data = {}
        for key in data.keys():
            if key == self._pk:
                continue
            elif key in desc:
                #TODO: type cast?
                clean_data[key] = data[key]

        return clean_data
コード例 #32
0
ファイル: test_base.py プロジェクト: henkelund/memobox
 def _install(cls):
     return (
         # "Version #1" routine
         """
         CREATE TABLE %s (
             "_id" INTEGER PRIMARY KEY AUTOINCREMENT,
             "some_attribute" INTEGER,
             "another_attribute" TEXT
         )
         """
         % DBHelper.quote_identifier(cls._table),
         # "Version #2" routine
         lambda: DBHelper().insert(
             SampleModel._table,
             (
                 {"_id": 1, "some_attribute": 10, "another_attribute": "ten"},
                 {"_id": 2, "some_attribute": 20, "another_attribute": "twenty"},
                 {"_id": 3, "some_attribute": 30, "another_attribute": "thirty"},
             ),
         ),
     )
コード例 #33
0
    def set_filter_values(cls, file_id, filter_id, values=None):
        """Set the values for the given file and filter"""

        if isinstance(file_id, FileModel):
            file_id = file_id.id()

        filter_id = cls.get_filter_id(filter_id)

        DBSelect(cls._filter_value_table).where('file = ?', file_id).where(
            'filter = ?', filter_id).query_delete()

        if values is None:
            return
        elif type(values) not in (list, tuple):
            values = (values, )

        for value in values:
            DBHelper().insert(cls._filter_value_table, {
                'file': file_id,
                'filter': filter_id,
                'value': value
            })
コード例 #34
0
ファイル: test_extended.py プロジェクト: henkelund/memobox
    def _install(cls):
        """Install base table and attributes"""

        ExtendedModel.install()
        table = DBHelper.quote_identifier(cls._table)

        return (
            lambda: (
                DBHelper().query("""
                    CREATE TABLE %s (
                        "_id"    INTEGER PRIMARY KEY AUTOINCREMENT,
                        "static" TEXT NOT NULL DEFAULT ''
                    )
                """ % table),
                SampleExtModel._create_attribute_tables(),
                SampleExtModel._create_attribute(
                    'sample_attr_1', 'Sample Attribute #1', 'integer', 'Default'),
                SampleExtModel._create_attribute(
                    'sample_attr_2', 'Sample Attribute #2', 'text', 'Default'),
                SampleExtModel._create_attribute(
                    'sample_attr_3', 'Sample Attribute #3', 'real', 'Not Default')
            ),
        )
コード例 #35
0
ファイル: extended.py プロジェクト: henkelund/memobox
    def _get_attribute_group(cls, group_id, create=False):
        """Retrieve an attribute group id"""

        if (cls._table in cls._group_index
                and group_id in cls._group_index[cls._table]):
            return cls._group_index[cls._table][group_id]

        group_desc = {
            '_id': group_id,
            'code': str(group_id).replace(' ', '_'),
            'label': str(group_id.replace('_', ' ')).capitalize()
        }

        cls._group_index[cls._table] = {}
        for field in ('_id', 'code', 'label'):
            select = DBSelect('attribute_group').where(
                            '%s = ?' % field, group_desc[field]).where(
                            'type = ?', cls._table).limit(1)
            group = select.query().fetchone()
            if type(group) is dict:
                cls._group_index[cls._table][group_id] = group['_id']
                return cls._group_index[cls._table][group_id]

        if not create:
            return None

        ids = DBHelper().insert('attribute_group', {
            'code': group_desc['code'],
            'label': group_desc['label'],
            'type': cls._table
        })

        if len(ids) > 0:
            cls._group_index[cls._table][group_id] = ids[0]
            return cls._group_index[cls._table][group_id]

        return None
コード例 #36
0
ファイル: device.py プロジェクト: henkelund/memobox
    def _install(cls):
        """Define install routines for this model"""

        table = DBHelper.quote_identifier(cls._table)
        return (
            lambda: (
                DBHelper().query(
                    """
                        CREATE TABLE %s (
                            "_id"          INTEGER PRIMARY KEY AUTOINCREMENT,
                            "serial"       TEXT NOT NULL DEFAULT '',
                            "product_id"   TEXT NOT NULL DEFAULT '',
                            "product_name" TEXT NOT NULL DEFAULT '',
                            "model"        TEXT NOT NULL DEFAULT '',
                            "vendor_id"    TEXT NOT NULL DEFAULT '',
                            "vendor_name"  TEXT NOT NULL DEFAULT '',
                            "manufacturer" TEXT NOT NULL DEFAULT ''
                        )
                    """ % table),
                DBHelper().query(
                    'CREATE UNIQUE INDEX "UNQ_DEVICE_SERIAL" ON %s ("serial")'
                         % table)
            ),
        )
コード例 #37
0
ファイル: install.py プロジェクト: henkelund/memobox
    def reset(cls):
        """Reset all versions"""

        cls._state = None
        DBHelper().query('DROP TABLE IF EXISTS %s'
            % DBHelper.quote_identifier(cls._table))
コード例 #38
0
ファイル: test_db.py プロジェクト: henkelund/memobox
class TestDBHelper(unittest.TestCase):
    """DBHelper test case class"""

    def setUp(self):
        """Initiate DBHelper singleton"""

        if os.path.isfile('/tmp/box.db'):
            os.unlink('/tmp/box.db')
        DBHelper().set_db('/tmp/box.db')
        self._helper = DBHelper()
        self._helper.query("""
            CREATE TABLE "test_table" (
                "col_a" INTEGER PRIMARY KEY AUTOINCREMENT,
                "col_b" INTEGER
            )
        """)
        self._num_rows = randint(128, 255)
        for i in range(self._num_rows):
            self._helper.query(
                'INSERT INTO "test_table" ("col_b") VALUES (?)',
                [randint(0, 99)]
            )

    def tearDown(self):
        """Clean up after test"""

        DBHelper().set_db(None)
        os.unlink('/tmp/box.db')

    def test_set_db(self):
        """Test DBHelper.set_db"""

        self.assertIsInstance(self._helper.get_connection(), Connection)
        self._helper.set_db(None)
        self.assertIsNone(self._helper.get_connection())
        self.assertRaises(OperationalError, self._helper.set_db, '.')
        self._helper.set_db(':memory:')
        self.assertIsInstance(self._helper.get_connection(), Connection)

    def test_quote_identifier(self):
        """Test DBHelper identifier quoting"""

        quote = DBHelper.quote_identifier
        self.assertEqual(quote('id'), '"id"')
        self.assertEqual(quote('"id"'), '"""id"""')
        self.assertEqual(quote('tbl.col'), '"tbl"."col"')
        self.assertEqual(quote('tbl.col', 'tc'), '"tbl"."col" AS "tc"')

    def test_query(self):
        """Test query execution"""

        cursor = self._helper.query('SELECT DATE() AS "date"')
        self.assertIsInstance(cursor, Cursor)
        self.assertIn('date', cursor.fetchone().keys())
        self.assertRaises(OperationalError, self._helper.query, 'SYNTAX ERROR')

    def test_describe_table(self):
        """Test describing a table"""

        desc = self._helper.describe_table('test_table')
        self.assertIn('col_a', desc)
        self.assertIn('col_b', desc)
        self.assertNotIn('col_c', desc)
        self.assertEqual(desc['col_a']['pk'], 1)
        self.assertEqual(desc['col_b']['pk'], 0)
        self.assertEqual(desc['col_b']['type'], 'INTEGER')

    def test_insert(self):
        """Test helper insert method"""

        ids = self._helper.insert('test_table', (
            {'col_b': randint(0, 99)},
            {'col_b': randint(0, 99)},
            {'col_b': randint(0, 99)}
        ))
        self.assertEqual(len(ids), 3)
        self.assertEqual(ids[0] + 1, ids[1])
        self.assertEqual(ids[0] + 2, ids[2])
        last = ids[2]
        ids = self._helper.insert('test_table', {'col_b': randint(0, 99)})
        self.assertEqual(ids[0], last + 1)
        self.assertRaises(
            OperationalError,
            self._helper.insert,
            'missing_table',
            {'col_b': 0}
        )
        ids = self._helper.insert('test_table', {})
        self.assertEqual(len(ids), 0)

    def test_select_from(self):
        """Test the SELECT <columns> FROM part of DBSelect"""

        select = DBSelect('a')
        sql = re.sub(r'\s+', ' ', select.render())
        self.assertEqual(sql, 'SELECT "a".* FROM "a"')

        select.set_from('b', ())
        sql = re.sub(r'\s+', ' ', select.render())
        self.assertEqual(sql, 'SELECT "a".* FROM "b" INNER JOIN "a"')

        select.set_from('c', ('d', 'e'))
        sql = re.sub(r'\s+', ' ', select.render())
        self.assertEqual(
            sql,
            'SELECT "a".*, "c"."d", "c"."e" ' +
                'FROM "c" INNER JOIN "b" INNER JOIN "a"'
        )

        count = self._helper.query(
            DBSelect('test_table', {'count': 'COUNT(*)'}).render()
        ).fetchone()
        self.assertTrue(type(count['count']) == int)
        self.assertTrue(count['count'] >= 0)

    def test_select_columns(self):
        """Test DBSelect.columns"""

        select = DBSelect('a', {})
        select.columns({'c': 'b'})
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT "a"."b" AS "c" FROM "a"'
        )
        select.left_join('b', 'a.b = b.a', {})
        select.columns('*', 'b')
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT "a"."b" AS "c", "b".* FROM "a" LEFT JOIN "b" ON a.b = b.a'
        )

    def test_select_join(self):
        """Test the DBSelects' *_join mehtods"""

        self._helper.query('CREATE TABLE a (x INTEGER)')
        self._helper.query('CREATE TABLE b (y INTEGER)')
        self._helper.insert('a', ({'x': 1}, {'x': 2}, {'x': 3}))
        self._helper.insert('b', ({'y': 2}, {'y': 3}, {'y': 4}))
        sql = DBSelect('a', {'n': 'COUNT(*)'}).inner_join('b', 'a.x = b.y', ())
        self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 2)
        sql = DBSelect('a', {'n': 'COUNT(*)'}).left_join('b', 'a.x = b.y', ())
        self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 3)
        # Right and outer joins are currently not supported by SQLite
        #sql = DBSelect('a', {'n': 'COUNT(*)'}).right_join('b', 'a.x = b.y', ())
        #self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 3)
        #sql = DBSelect('a', {'n': 'COUNT(*)'}).full_join('b', 'a.x = b.y', ())
        #self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 4)

    def test_select_where(self):
        """Test the WHERE part of DBSelect"""

        select = DBSelect('test_table')
        select.where('"col_a" = ?', 1)
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT "test_table".* FROM "test_table" WHERE ("col_a" = ?)'
        )
        self.assertEqual(len(select.query().fetchall()), 1)
        select.or_where('"col_a" = ?', 2)
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT "test_table".* FROM "test_table" ' +
                'WHERE ("col_a" = ?) OR ("col_a" = ?)'
        )
        self.assertEqual(len(select.query().fetchall()), 2)
        select.where('"col_a" IN (?)', (3, 4, 5))
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT "test_table".* ' +
                'FROM "test_table" ' +
                    'WHERE ("col_a" = ?) ' +
                        'OR ("col_a" = ?) ' +
                        'AND ("col_a" IN (?, ?, ?))'
        )
        self.assertEqual(len(select.query().fetchall()), 1)

    def test_select_distinct(self):
        """Test the DISTINCT directive"""

        select = DBSelect('a')
        select.distinct()
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT DISTINCT "a".* FROM "a"'
        )
        select.distinct(False)
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT "a".* FROM "a"'
        )

    def test_select_limit(self):
        """Test LIMIT and OFFSET parts"""

        select = DBSelect('test_table')
        select.limit(10)
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT "test_table".* FROM "test_table" LIMIT 10'
        )
        self.assertEqual(len(select.query().fetchall()), 10)
        select.limit(10, self._num_rows - 5)
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT "test_table".* FROM "test_table" LIMIT %d, 10'
                % (self._num_rows - 5,)
        )
        self.assertEqual(len(select.query().fetchall()), 5)

    def test_select_order(self):
        """Test the ORDER BY part of DBSelect"""

        self._helper.query('CREATE TABLE a (x INTEGER, y INTEGER)')
        self._helper.insert('a', (
            {'x': 1, 'y': 1},
            {'x': 2, 'y': 2},
            {'x': 3, 'y': 2},
        ))
        select = DBSelect('a').order('x')
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT "a".* FROM "a" ORDER BY "x" ASC'
        )
        self.assertEqual(select.query().fetchone()['x'], 1)
        select = DBSelect('a').order('y', 'DESC').order('x')
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT "a".* FROM "a" ORDER BY "y" DESC, "x" ASC'
        )
        self.assertEqual(select.query().fetchone()['x'], 2)

    def test_select_unset(self):
        """Test unsetting parts of SELECT"""

        select = DBSelect('a', {'c': 'col'}
                    ).left_join('b', 'a.c = b.d', {'d': 'col'}
                    ).where('a.col = ?', 1
                    ).order('b.d', 'DESC'
                    ).limit(1, 2
                    ).distinct(True)
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT DISTINCT "a"."col" AS "c", "b"."col" AS "d" ' +
                'FROM "a" LEFT JOIN "b" ON a.c = b.d ' +
                'WHERE (a.col = ?) ' +
                'ORDER BY "b"."d" DESC ' +
                'LIMIT 2, 1'
        )
        select.unset(select.FROM | select.COLUMNS).set_from('x')
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT DISTINCT "x".* ' +
                'FROM "x" WHERE (a.col = ?) ORDER BY "b"."d" DESC LIMIT 2, 1'
        )
        select.unset(select.WHERE)
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT DISTINCT "x".* ' +
                'FROM "x" ORDER BY "b"."d" DESC LIMIT 2, 1'
        )
        select.unset(select.DISTINCT | select.ORDER | select.LIMIT)
        self.assertEqual(
            re.sub(r'\s+', ' ', str(select)),
            'SELECT "x".* FROM "x"'
        )

    def test_select_clone(self):
        """Test cloning a SELECT"""

        select1 = DBSelect('a', {'c': 'col'}
                    ).left_join('b', 'a.c = b.d', {'d': 'col'}
                    ).where('a.col = ?', 1
                    ).order('b.d', 'DESC'
                    ).limit(1, 2
                    ).distinct(True)
        select2 = select1.clone()
        self.assertEqual(str(select1), str(select2))
        select2.or_where('b.col = ?', 1)
        self.assertNotEqual(str(select1), str(select2))
        select1.unset(select1.WHERE)
        select2.unset(select2.WHERE)
        self.assertEqual(str(select1), str(select2))

    def test_select_update(self):
        """Test executing UPDATEs based on SELECTs"""

        select = DBSelect('test_table').where('col_a = ?', 1)
        self.assertEqual(select.query_update({'col_b': 512}), 1)
        select = DBSelect('test_table').order('col_a').limit(3, 1)
        self.assertEqual(select.query_update({'col_b': 512}), 3)
        select = DBSelect('test_table').where('col_b = ?', 512)
        self.assertEqual(select.query_update({'col_b': 1024}), 4)

    def test_select_delete(self):
        """Test executing DELETEs based on SELECTs"""

        select = (DBSelect('test_table')
                    .order('col_a', 'DESC')
                    .limit(self._num_rows - 10))
        self.assertEqual(select.query_delete(), self._num_rows - 10)
        select = DBSelect('test_table').where('col_a = ?', 1)
        self.assertEqual(select.query_delete(), 1)
        select = DBSelect('test_table').where('col_a IN (?)', (1, 2, 3, 4))
        self.assertEqual(select.query_delete(), 3)
コード例 #39
0
ファイル: base.py プロジェクト: henkelund/memobox
 def _prepare_filter_attribute(self, attribute):
     """Prepare attribute for use in WHERE"""
     # TODO: check attribute against describe table?
     return DBHelper.quote_identifier(attribute)