Developers Club geek daily blog

Use of the LSM engine from SQLite 4 as separate NoSQL of the database with use of Python

2 years, 10 months ago

Today two of my most favourite subjects — SQLite and key-value of the database. And this time I write about both at once: this post is devoted to a Python-wrapper for the storage used in SQLite 4 key-value on the basis of LSM.

I not too attentively trace SQLite releases, but version 3.8.11 drew my attention as in its description serious increase in performance in comparison with 3.8.0 is declared. In accompanying information I came across mentioning of new experimental expansion for full-text search (about which wrote once) and therefore it became interesting to me what develops a situation with SQLite 4.

Having studied available information, I paid attention that one of tasks of developers was to provide in new versions the interface for the connected engines of databases. At the time of writing of this post in SQLite 4 there were already two built-in backends, one of which — key-value storage on the basis of LSM. Few months I happened to be played the last with Cython while I wrote a Python-wrapper for the built-in k-v of UnQLite and Vedis storages. And I thought that it would be quite good to apply Cython to creation of the interface of the DB engine on the basis of LSM used in SQLite 4.

Having dealt with the source code SQLite 4 and the tiny heading LSM file, I wrote python-lsm-db (documentation).

Read more »

How to make friends Linq-to-Entities and Regex

2 years, 11 months ago
Entity Framework strongly facilitates development of systems, using databases. Let's not argue on merits and demerits of this framework now (which, of course, much), and we will consider one of practical tasks which I had to solve when developing such system.

Let's assume, we have a SQLite database with quite large number of records, and this base is used in our .NET the application through System.Data.SQLite and Entity Framework 6.0. And here the customer comes and reports that he needs new function of search of records in base and such that it was possible to look for with use of standard regular expressions.

In this article I will tell how I achieved that processing of the regular expression set in Linq-request happens on server side that allows to accelerate its processing and not to allow senseless inflating of memory of the client application because of preliminary downloading of all data.

Read more »

Work with the SQLite database by means of SQLitePCL wrapper

3 years, 2 months ago

SQLite is cross-platform (Windows, iOS, Android, Python, Mono, etc.) the database engine open source. It supports multiple tables, indexes, triggers and representations.
Transaction ACID (Atomicity/Atomicity, Consistency/coordination, Isolation/isolation, Durability/reliability) are supported.
SQLitePCL is a Portable Class Library library open source, available to the address / which allows to work with the SQLite bases common both in .Net applications and in WP, Windows Store, UAP, and also Android/iOS (by means of Xamarin). In other words, it is wrapper/wrapper of library C which simplifies development and saves time. The wrapper is quite new. Earlier for the .Net and Windows Store applications it was possible to use sqlite-net wrapper.

Read more »

Effective work with SQLite on the example of ICQ

3 years, 4 months ago
As well as in many other applications, we should store a lot of information in mobile ICQ: messages, contacts and so forth. When the amount of requests to these data reaches some critical value, application starts braking. Long start, slow opening of chat, slow sending messages, constant spinner? all this terribly strains. Most often unsuccessful work with data is the reason of brakes. I want to share our experience of refactoring of data structure, query tuning and some convenient receptions for migration in article.

Some words about initial task. The main entity at us? the ICQ profile which has contact list, and those have messages. Our application exists many years, was developed by different people with different approaches, the version number of the main DB surely came nearer to 30. Besides, the quantity of features in product cannot be predicted in advance, it too has affected architecture. Generally, the data model initially was approximately such:

Read more »

Generation of LINQ to SQL of a code for SQLite in .NET (C#)

3 years, 4 months ago
On C# I write seldom, both generally all our applications and services are connected to data source, using MSSQL the server or services of databases. And here time to write the application came, using not the server, but a local DB. It is a little pogugliv, I suddenly selected SQLite.


All our customers had requirements to a code:
  • the set naming of variables;
  • grouping of a code (designers, variables, methods, events...);
  • taba and formatting (it was punished by the death penalty);
  • for samopisny SQL requests the developer left from command.

Proceeding from the above-mentioned, for databases of course the classes LINQ to SQL connected with relational objects were used.

Read more »

Custom SQLite Android functions or the LOWER_FNC ()

3 years, 8 months ago
SELECT * WHERE LOWER_FNC(name) like of' %" + filterText +" %'"

When developing Android of the application faced a problem in request of SQLite of the filter with the Russian letters. For the English localization there are no problems. For other international layouts capital letters in request were incorrectly processed.
Having a little understood I came across the following description:

(18) Case-insensitive matching of Unicode characters does not work.

The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive comparisons and case conversions requires tables and logic that would nearly double the size of the SQLite library. The SQLite developers reason that any application that needs full Unicode case support probably already has the necessary tables and functions and so SQLite should not take up space to duplicate this ability.

Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines.

Possibly current implementation of SQLite Android also is
only supports case-insensitive comparisons of ASCII characters

Saw a solution through CursorWrapper but decided to collect after all the SQLite version and to use addCustomFunction

What from this turned out read under a cat

Read more »

Acquaintance to FireUI

3 years, 8 months ago
Recently we have published series of articles devoted to applications programming in FireMonkey. Then we have described the key moments of application programming, including creation of database, connection of data with the help of the LiveBinding technology, application deployment on mobile platform. However, in details we did not begin to consider nuances of creation of mobile applications. In many respects it is connected with that process of mobile development in Delphi evolves from the version to the version. In particular, in the last XE7 today of the Delphi version, the new designer of the FireUI Multi-Device Designer forms has been provided. In this article by means of small example we will consider that is FireUI and how with its emergence the development methodology has changed.

Read more »

Lenovo phone unblocking by deep methods

4 years, 4 months ago
I have bought somehow Lenovo A706? and I was happy with it, and is happy very. There was, however, pair of the screen of glyuchok connected with independence, though in general? quite suitable device. But once, in one dark-dark night at dacha the trouble has happened to it. It lay to itself(himself) and was loaded. Before it light vanished, and I specially for the night have disconnected data transmission to preserve the battery if light again goes out. Also I left it to lie muzzle down...

Read more »

We treat SQLite in Monotouch or practical advantage of reflection

4 years, 4 months ago
Work with child of Xamarin is interesting and full of surprises, both in good word sense, and in the bad. One problems are solved by means of Google and StackOverflow, others demand non-standard approach. In this article I want to tell story how it is possible by means of source codes, reflection and three cups of tea to solve one very unpleasant problem.

Read more »

ACID in SQLite

4 years, 6 months ago
In this post the system of blocking and maintenance of atomicity, coherence, isolation and reliability (ACID) in SQLite, and also algorithms of record and reading from the file of base is described.

Read more »