Tag: SQLite

CHAR and VARCHAR Data Types in Different Database Engines

Storage engines can surprise you. For example, take the CHAR data type. It expects an exact number of characters and by definition stores afixed amountof information. However, you don’t have to fill all the available CHAR space – a shorter value will work. This is so similar to VARCHAR that I decided to explore the differences between these two types.Before diving into the details, let’s start with some basic information. CHAR and VARCHAR are SQL data types dedicated to storing character values. They are available in almost every database engine. Due to database and encoding particulars, the storage of character values in CHAR and VARCHAR columns differs.

SQLite Tools for Objective-C

The most common way to implement database functionalities in iOS applications is definitely Apple’s Core Data. However, its architecture is really Apple-like: it doesn’t let you see anything happening inside and the structure is hidden from you. That’s why some people dislike Core Data and prefer to stick with an SQLite database – even though SQLite is not supported natively.Fortunately, you are not forced to use core SQLite API in pure C. The iOS developers’ community has already delivered tools to make communication with an SQLite database easier.

A Comparison of Android ORMs

Although mobile apps do not rely on databases as much as, say, web applications, mobile app developers should still be interested in databases. There are better database solutions for Android apps than a native SQLite library; we’ll nominate ORMs (Object Relational Mapping) as one of them.ORMs make developers’ lives easier. They let us avoid struggling with building queries by concatenating strings or manually handling the connection with database. Typos are less of threat to your queries. And, with ORMs taking care of security, you don’t have to worry about resistance to injection attacks. You can focus on your application functionalities and let the database do its job.

Time Zones in Databases

Anyone who had to schedule an intercontinental phone call knows that there is no such thing as a simpletimecallednow. What you should rather think about is a time comprised ofhere and now.The Earth rotates around its own axis. When it’ssolar noon(the sun is at its highest position) in one place, it’s already past noon in places to the east and it’s still before noon in places to the west.To make communication easier, at the end of the 19th century, the Earth was divided into 24 hour-wide

S.Q.L or Sequel: How to Pronounce SQL?

SQL has been around for decades and supports a many billion dollar market. However, many people still struggle with just how to pronounce the term SQL. Is it “S.Q.L” [ˈɛs kjuː ˈɛl] or is it “sequel” [ˈsiːkwəl]?SQL… Where it all startedLet’s start at the beginning.Relational databases came into existence with E.F. Codd’s 1970 publication “A Relational Model of Data for Large Shared Data Banks.” While Codd’s ideas were remarkable for the time, in San Jose, California, two colleagues named

Database engine usage by Vertabelo users

The Vertabelo journey continues … We now have almost10,000users and the number of Vertabelo advocates keeps growing strong.Vertabelo users come from over100countries and speak various languages. What unites them?The relational database.Let’s see what relational databases they use:We wanted to determine the most popular database engine among Vertabelo users based on one of three widely-used operating systems:Windows,Linux,Mac OS.Guess what we find out?Among all supported popular databases (PostgreSQL, MySQL, Microsoft SQL Server, SQlite, Oracle, IBM DB2, HSQLDB),

Using an SQLite Database on Android Platform A Short FAQ

When developing an application with a SQLite database as persistent storage, it’s worth it (or necessary, in fact) to know some low-level details like: where the data is storedphysicallyand how we can determine if its structure is really the same as what we expect it to be. Being familiar with these things makes it easier and faster to develop, as well as find and fix bugs. I’ll try to explain some of the most frequently asked questions regarding SQLite databases.

Handling Database Structure Changes

In the previous article we wrote a simple Android app allowing the user to manage his ToDo list. He could add new tasks, mark them as done and delete them. That article showed how to create an SQLite DB in an automated way and how to do some simple CRUD operations on it.Let’s say that the first version of an application is released, we distribute it (i.e., it shows up in Google Play), people download and use it. After some time we decide to improve the app. Our goal is to add the ability to prioritize the tasks.

Top-N and Pagination Queries

Generally, we don’t limit query results. However, when we only care about the first few rows or to implement table pagination, limiting query results is just what we need. Database vendors provide us with such functionality; most of them in their own distinct way.ExampleLet’s take a look at the 2014 Sochi Olympics Men’s Normal Hill Individual ski jumping results in theskijump_resultstable. There is no index on theskijump_resultstable. The following queries are examples of a Top-N and pagination query, which I will use in the following parts of the article.

Using an SQLite Database on Android Platform – Introduction

According to the report of the International Data Corporation , Android operating system reached more than 80% market share during the 3rd quarter of 2013. Together with iOS, Android dominates in the mobile devices’ world.This made me think that it may be worth writing a few words about how to create mobile applications for these two mobile platforms. But not the kind of “hello world” applications – there are plenty of tutorials about that. I’d like to focus on the use of local

ON DELETE RESTRICT vs NO ACTION

When you create a foreign key in your database, you can specify what happens upon delete of the parent row. There are usually four possibilities:ON DELETE SET NULLON DELETE CASCADEON DELETE NO ACTIONON DELETE RESTRICTToday we’ll investigate the subtle difference between the last two options.In Some Databases There Is No Difference at AllIn Oracle, there is no RESTRICT keyword . The only option is NO ACTION. In MySQL, there is

MySQL’s group_concat Equivalents in PostgreSQL, Oracle, DB2, HSQLDB, and SQLite

group_concat in MySQLMySQL has a very handy function which concatenates strings from a group into one string. For example, let’s take a look at thechildrentable with data about parents’ and children’s names.To get the names of children of each person as a comma-separated string, you use thegroup_concatfunctions as follows:The result:To make sure the names of the children in each string arealphabetically orderedand to use semicolon „;” as aseparator, use this query: