Developers Club geek daily blog

The library helping to overcome a conceptual gap between OOP and a DB during testing when using ORM — LinqTestable

2 years, 5 months ago
It is known that between an object-oriented and relational model there is a conceptual gap which is not able to overcome even ORM. Generally this gap influences that when using relational database we are forced to work on sets, but not on specific objects. But there is also other factor: the behavior of NULL in a DB differs from behavior of NULL in object-oriented languages. It can become a problem when you use the same request in two situations: 1) at request to a DB 2) at a unit testing when instead of the table of a DB the array in random access memory is used. Moreover, it can become a problem if you address only a DB, but think about NULL in terms OOPs, but not a relational DB!

image

Read more »


Parallel processing of a big selekt in several sessions

2 years, 5 months ago
Provide: is selekt which returns records, each of which needs to be processed, and whether it is a lot of records, whether processing of each record takes a lot of time, and processing of one record does not depend on processes of other records.
Classical example to involve a multithreading or in case of databases to execute processing in several sessions. In Orakle hint / * + by parallel () */and pipelined functions is for this purpose used. It is healthy, but if you have Oracle standard edition (where parallel does not work) or you want to process not each record separately (for reasons that it is better to save up work, and then in bulk, with one blow, to execute), and to divide all output of a selekt to pieces and to process everyone separately?

Read more »


It is available to 0xDBE 1.0 Preview

2 years, 5 months ago
JetBrains approaches release of new IDE for work with SQL and databases. We started the EAP program a year ago and we thank all its participants. As always, your opinions and wishes made our product better.

We are glad to provide you 0xDBE 1.0 Preview. It is excellent chance to try our new IDE and to share impressions that will help us to make 0xDBE even better by the time of release.



We like the name 0xDBE, but it nevertheless working, and to release we will select another — more readable.

Still we want to know better our users and to understand how you work with databases therefore made small poll. We ask to pass it, on it 5-8 minutes will leave. Among those who took part in it we will play ten annual licenses on 0xDBE.

So, here what we added since EAP launch:

Fast and exact introspektion

First of all, we processed algorithm of receipt of a metainformation about the database and expanded standard JDBC интроспекцию for the majority of the supported DBMS. In addition to increase in speed it led to improvements in the graphic interface — now database objects are grouped in types:



Object management of a DB

By clicking Ctrl+F6 (Cmd-F6 for OS X) on a table name in a text editor or in the Database view window the Modify Table window in which it is possible to add and delete columns opens, to change their properties, to add and delete keys and indexes. In real time the DDL script which will be executed for these changes is generated:



Tekstoy search

Now text search in result of request is available. For example, you need to find data, and the column was forgotten. There is a support of regular expressions:


Read more »


It is a little about query tuning

2 years, 5 months ago
Want on a simple example to tell about how sometimes it is possible to optimize requests quite simple at first sight strongly. Let's take such code, for an example on PostgreSQL 9.3, but the principle approaches all DBMS at which there is hash join.

Task simple — to sdzhoynit two tables — one very big, another small — but dzhoin not simple, but gold with OR. (As a real case — dzhoin tables of postings on accounts to accounts, considering that in wiring two fields with the account — for the debit and the credit.)

Read more »


Regular expressions of Oracle. Dangerous range

2 years, 6 months ago


The Oracle developer who is often using regular expressions in code especially on bases with orthodox settings, can face sooner or later the phenomenon which, except as mysticism, you will not call in any way. Long searches of origins of problem can lead to loss of weight, appetite and to provoke different psychosomatic frustration — all this I now and I will try to prevent. And I will be helped with it by the regexp_replace function. It can have to 6 arguments:

REGEXP_REPLACE (
  1. iskhodnaya_stroka,
  2. template,
  3. zamenyayushchaya_stroka,
  4. position of the beginning of search of coincidence to template (by default 1),
  5. number of occurrence of template in initial line (by default 0 – all occurrences),
  6. modifier (meanwhile dark horse)
)
Returns the changed iskhodnuyu_stroka in which all occurrences of template are replaced with the value transferred in the parameter of zamenyayushchaya_strok. Often use the short version of function where 3 first arguments are set that happens enough for solution of many tasks. I too so will make. Let's say we need in line 'MASK: lower case' to disguise all lower case characters asterisks. The template of '[a-z]' has to be suitable for task of range of lower case characters. We check

select regexp_replace('MASK: lower case', '[a-z]', '*') as result from dual

Waiting
+------------------+
| RESULT           |
+------------------+
| MASK: ***** **** |
+------------------+

Reality
+------------------+
| RESULT           |
+------------------+
| *A**: ***** **** |
+------------------+

If on your base this phenomenon was not reproduced, means to you it was still lucky. But more often diggings in codings begin, convertings of lines from one symbol set in another and over time there comes approximately such status

Read more »


Oracle, typical problems of SQL. The guaranteed choice

2 years, 6 months ago


On nose elections, and it means that today we will select the governor. To be exact to assign barrier of passing of candidates for the following round. But at first it is necessary to decide on terms. What is the guaranteed choice in SQL? Let's say in request condition to the table comparison of any field with any variable is executed. Depending on value of this variable the request can return lines from the table, and can not return them at all. If such variable value drops out that lines from the table do not return, for this case it is necessary to generate specially predeterminated left result. That is anyway the general request has to with guarantee yes return something. The term is taken from here. However the task becomes complicated that (and can and vice versa, becomes simpler) that instead of one simple cell with value, is necessary for us is guaranteed to return full-fledged line.

I provide data of the Central Electoral Commission. The first round of vote has ended with such results
ID Name of the candidate Profession Number of votes
1 Incorruptible Amoral Chistorukovich prosecutor 9867
2 Effective Budget Osvoilovich businessman 8650
3 Truthful Chtodadut Napisaylovich editor of the newspaper 745
4 Comely Lucifer Feoktistovn  prior 234
5 Hrensgora Ktotakoy Niktoneznayevich teacher of rural school 3

Read more »


Oracle, typical problems of SQL. Transformation of the values listed in column in lines of the table

2 years, 6 months ago
Welcome to Hollywood. I represent you today's heroines

image
Angelina, Charlize and Penelope. Wake up, girls.

Today we are waited by society columns. At first we learn these three beauties had who in husbands. And then, by means of plain special effects, I will show you, in what order they brakosochetatsya with each other. So this article will be especially interesting to young maidens.

Let's create and will fill base table
create table hollywood 
as 
with t (id, actress, husbands) as (
  select 1, 'Анджелина Джоли', 'Джонни Ли Миллер, Билли Боб Торнтон, Брэд Питт' from dual union all
  select 2, 'Шарлиз Терон', null from dual union all
  select 3, 'Пенелопа Крус', 'Хавьер Бардем' from dual
)  
select * from t;

alter table hollywood add primary key (id);

Read more »


Oracle, typical problems of SQL. Reproduction of lines of the table depending on value of number in column

2 years, 6 months ago
image

On nose winter, frosts come nearer, and it means that today we will pickle bananas. For this purpose the following ingredients will be necessary for us:
ID INGREDIENT MEASURE QUANTITY
1 Banana Piece 3
2 Parsley Branch 2
3 Water Liter 3
4 Salt Spoon 1
5 Vinegar Spoon 2

Read more »


How to count everything on light as one SQL query. Window PostgreSQL functions

2 years, 6 months ago

I with surprise have found out that many developers, even long ago the using postgresql, do not understand window functions, considering as their some special magic for favorites. Well or at best "kopipastit" from expression StackOverflow like "row_number () OVER ()", without going into detail. And after all window functions — the most useful functionality of PostgreSQL.
I will try to explain simply how it is possible to use them.

Read more »


Adminer — the web interface for databases of one .php in size the file

2 years, 6 months ago


In the light of recent post about comparison of PostgreSQL and MySQL, in comments there was problem of choice of the convenient interface for work with the postgresy. I have faced such problem, having decided to look for alternatives to all to known phpMyAdmin/php*Admin which is considered the standard at webmasters.

Read more »