NoSQL and SQL Databases for Mobile Applications. Case Study: MongoDB versus PostgreSQL

Compared with “classical” web, multi-tier applications, mobile applications have common and specific requirements concerning data persistence and processing. In mobile apps, data-base features can be distinctly analyzed for the client (minimalistic, isolated, memory-only) and the server (data rich, centralized, distributed, synchronized and disk-based) layers. Currently, a few lite relational database products reign the persistence for client platforms of mobile applications. There are two main objectives of this paper. First is to investigate storage options for major mobile platforms. Second is to point out some major differences be-tween SQL and NoSQL datastores in terms of deployment, data model, schema design, data definition and manipulation. As NoSQL movement lacks standardization, from NoSQL products family MongoDB was chosen as reference, due to its strengths and popularity among developers. PostgreSQL serves the position of SQL DBMSs representative due to its popularity and conformity with SQL standards.


Introduction
"Mobile world" is one of the most dynamic areas of Information Technology today.Smartphones and tablets explosion have created a huge market for mobile applications.Consequently there is an increasing demand for mobile application developers so that, even when taking into account just the case of Romania, many books [1] [2] [3] and articles (e.g.[4], [5], [6], etc.) have been published.Also many Business Information Systems/Business Informatics undergraduate and master programs introduced in their curriculum courses related to mobile devices and applications (e.g.http://dice.ase.ro/?page_id=31, http://ism.ase.ro/curriculum_2012_2014.jsp, http://www.feaa.uaic.ro/ro/main/page/335,http://www.econ.ubbcluj.ro/planuri_invatamant/master/planuri_master_2012_2013/E-Business.pdf).Almost all of the mobile applications require a persistent data layer, including options for queries.So the interest of database professionals, academics and researchers for mobile technologies is increasing.Mahmoud et al. [7] even suggest including mobile storage topics, devices and applications into database courses.This paper presents technologies and tools for deployment of the data layer in mobile applications on some major platform focusing on a NoSQL datastore, MongoDB.Section 2 deals with some specific requirements of mobile software in terms of storage and databases.Section 3 deepens the discussion of data storage features for major mobile platforms.The arguments for a NoSQL solution, including a basic description of mobile cloud solution implemented in MongoDB are subjects of section 4. Sections 5, 6 and 7 analyse some of the main differences in terms of database schema, Data Definition Language (DDL), Data Manipulation Language (DML) and database query between SQL (PostgreSQL dialect) and MongoDB.

Common Issues of Mobile Applications Storage Layer
Mobile applications share many features of "classical" client/server or web multi-layered applications architecture.Figure 1 depicts a classical six-layered (Security layer covers all the other five layers) framework [8] for enterprise mobile application development.[8] Persistence layer have similar requirements in terms of storage, DDL, DML and database query.Also the data accessibility mechanisms from application layers are similar to other web applications.The most striking difference between mobile and web application databases concerns is that while server persistence layer is similar, the client platforms of mobile apps (running mainly on smartphones and tablets) have a smaller scale of available resources (memory, processing power).Many mobile applications require replication and synchronization mechanisms for data persistent on smartphone with a centralized large scaled database available on a server [9] [10].Data storage for client must be processed without hard disk techniques [11] [12].Nori [13] identifies 11 characteristics that have to be provided, partially or fully, by the mobile and embedded DBMSs: a. Embeddable in applicationssometimes requiring no administration.b.Small footprintin order to be downloadable in broader range of mobile devices c.Run on mobile devices and operates in conditions of small amount of processor power, RAM and permanent memory.d.Componentized DBMSsupports from all DBMS functions only the ones required by the specific application.e. Self-managed DBMS, with no hope for the user to be a DBA who can ne able to restore a crashed database.f.In-memory DBMSrequiring special query processing and indexing technics which are optimized for main memory usage.g.Portable databases with very simple deployment.h.No code in the database that protects against viruses and malware.i. Synchronize with back-end data sources.j.Remote managementespecially in the case of enterprise-wide applications.k.Custom programming interfaces for specialized data-centric applications.with a combined market share over 87% in 2012.When customer decide, not only the features provided by the operation system are significant, but also hardware characteristics, applications available on the market, social status, durability and so on.Most of the applications developed for mobile devices need storage options for user's data, session data, life-cycle timestamps (especially for states la pause or suspend).Storage requirements can go from user specific options (simple values for dimensions, user accounts, etc.) to significant amounts of data gather by sensor, received from internet or internet by the user himself.When using Android, there are several options for saving data.Among them, shared preferences are based on key-value pairs mechanism, only usable for primitive data types and limited by the current user session.A developer can use the SDK to retrieve, add, delete and modify preferences either related to the current activity or shared across multiple activities for the same user session.For both variants, a public interface is available, android.con-tent.SharedPreferences that contains two other nested classes, one as an editor for the current key-value pairs and another which acts as a listener for changing preferences and related callbacks.Other pub-lic methods are related to obtaining a map of names and values (different data types) along with getters and setters for primitive data types.Apart from the shared preferences, any application with correct permissions grated, can access the file system from the internal storage.By default, Android limits the files access to the application which creates them (MODE_PRIVATE), but this behaviour can be modified to: MODE_WORLD_READABLE in order to allow any other application or user to read created or modified files (deprecated since API level 17), MODE_WORLD_WRITEABLE for open write access to others (also deprecated starting with API level 17) and finally MODE_APPEND which limits future users to add data at the end of the file.Classes for write and read are available in the API, as FileOutputStream and FileInputStream, associated to public methods as openFileOutput() and openFileInput().Read, write and close methods are also available.In addition, the developer can make use of built-in functionality for getting the path to a certain file, manage directories, delete files and retrieve a list of files from a specific folder.Also, methods exist for raw resources (added at design time usable at compile time) and for saving cache files (not recommended for sensitive data).An alternative for managing persistent data is the usage of external storage, fixed (internal non-removable partition) or removable (SD, microSD cards, devices connected through micro-USB ports).An essential feature of the external storage is the possibility of all application to access it.Also, special precautions are recommended, as the external device can be disconnected by the user at any moment (the developer has to check media availability through specific methods provided by the SDK).Almost the same file and directory related functions are used, to which dedicated locations as Music, Podcasts, Ringtones, Alarms, Pictures and others are pre-defined by the system, since DOI: 10.12948/issn14531305/17.2.2013.04API level 8 (all contained by the public class Environment -android.os.Environment).For the internal storage, the system maintains a policy of deleting all related files for an application when the application is uninstalled.The policy doesn't apply for the external storage unless the files are created by the application for private usage, by invoking getExternalFilesDir() method.Of course, all presented above can be used for structured data, as pair of values or even locally stored XML.Problems occur in means or querying data and obtaining aggregated reports.Among other data layer options, the most popular SQLite, advertised by its producers as a software library that implements a selfcontained, serverless, zero-configuration, transactional SQL database engine.In fact, SQLite is present on a variety of devices and operation systems, precompiled packages can be downloaded for Linux, Mac OS, Windows, Windows Phone 8, Windows .Net platform [7], [5] [3].Its popularity can be explained by the following features [3]:  easy to install and configure  simplicity  does not require a server (can run in client-only mode)  compactness of database (all database resides in a single file for each application)  it is an open source product.Being so compact, SQLite can be ported to almost any device, the source code being available for the community.Android offers full support libraries for SQLite.As each database resides in its own file on the disk and it is directly managed by the application (no server process involved, all classes are able to use one database if the name is known), it cannot be shared directly between multiple applications.The small footprint library can handle both DML and DDL statements, offers cursors access to clients, indexes, multiple data types, primary and foreign keys and a lot more features, which makes it very easy to use and a good candidate for being used as a persistence layer.Applications written for iOS devices such as iPhone or iPad have several storage options, some of them being similar to the above pre-sented for Android, and others specific to the host OS.Among these, one can find files (either as text, comma separated values, XML, JSON), property lists, core data, user preferences, SQLite.As almost the same features are available for SQLite on both mobile OS, and the files are managed by a similar mechanism, we'll only briefly describe the other options for the Apple devices.Property lists are used by applications on both iOS and MacOS in order to storing simple hierarchies of either primitive data types or containers as arrays or dictionaries.These data types can be nested in multiple levels, as needed by the developer.As mentioned in the official documentation by Apple, property lists are recommended for small amounts of data.Data stored in property lists can be serialized for future usage in either binary or XML formats.Eckermann [14] finds these lists easy to use, especially due to a welldefined Objective-C API, but on the other hand, states that they are not suitable for any type of relational data, can't handle large sets of objects, use an inefficient memory loading mechanism, and let the developer handle too much of the responsibilities for data integrity and manipulation.In order to help the developers with the usage of mode view controller pattern, the Cocoa framework help the iOS developers to define a data model easier through a feature called Core Data.By using this mechanism, the developer has access to a data management solution able to handle the definition and integration of data models.Core Data offers a visual interface, intuitive and easy to use.The data model communicates with the controller of the application and hides details as storage and SQL queries to the developer (although this leads to a lack of control over the data).This storage framework can serialize data on XML files, binary or directly to a SQLite database.Internally, it organizes data in an object graph and provides features for declaring relationships between different collections of data.One of the main advantages is the fact that it was specially built solution for iOS and it integrates well with other components or tools in the Apple ecosystem, reducing the code volume needed for persistence.The "user default" storage environment can be easily used for saving user settings and options related to applications or the system.It is not built to help the developers store large amount of data or data with any kind of structured organization.The SQLite for iOS can be directly used as a C library or by having an Objective-C wrapper as FMDB, ArchDBObjects, SQLiteManager or SQLite persistent objects.In a similar way with Android, the developer has to handle the single file needed for each SQLite database, used as a seed.Along with the option for locally storing data enumerated above for both Android and iOS, there are several other options as accessing database servers across the network with dedicated drivers, most of them created by the community.In the last couple of years, cloud databases had gained increase popularity among the developers and they seem to be important options for mobile devices.These kinds of services are offered by important players as Amazon with its S3 or Relational Services, Microsoft with Azure, Salesforce with Database.com,and the examples can continue.Related to the subject of the next section, there are a few cloud database providers specialized in NoSQL services, having MongoDB as an underlying service.The most popular are MongoLab, MongoHQ, Rackspace Cloud or dotCloud.These kinds of services are first of all characterized by the lack of involving the client in phases like installing or configuring the software.The customer pays for all these services, for data access, storage space, network traffic, monitoring programs and specialized interventions when needed.The provider has to be complaint with Service Level Agreement regarding infrastructure maintaining, quality if the service, response time, etc.Most of the database services in the cloud expose polyglot interfaces for the client, offering at the same time SQL and NoSQL data storage engines.For mobile applications developers, using database services in the cloud can be a big challenge on one hand, but can come with significant advantages on the other.It is worth mentioned that the device (with limited computing resources) can allocate more processing power to the application, which is more appreciated by the end user, replication problems are avoided, the need for expensive hardware (we have devices in 2013 running Android with 8 processor cores see Samsung S4 and other new devices).Of course, issues like constant need for network connection (through Wi-Fi or data plans), battery consumption or prices for consumed database services arise.One can think about other advantages as the usage of the same persistence technology for both desktop and mobile apps, backup and restore plans and procedures already defined by the service provider, ability to scale up or down storage and access capacity based on the business needs, and others.

NoSQL Datastores for Mobile Applications. The Case of MongoDB
There has been huge interest about NoSQL data stores for the last three years.A good amount of literature has been dedicated to NoSQL movement, which varies from full excitement [15] to criticism [16][17] and conciliation with relational technologies [18] [19].For a brief discussion about the context of NoSQL emergence, NoSQL basic features see [20], [21], [22], [23].In advocating NoSQL versus Relational Databases for Mobile Applications Asay [24] points out two weaknesses of relational technologiesthe rigidity of schema and impossibility of handling all of the different use cases mobile applications call for.Due to their scalability and speed Selvadurai [25] recommends using NoSQL datastores when mobile applications manage huge amount of data on a central server.In this paper MongoDB was chosen as a NoSQL camp representative due to its features and popularity [21] [26].Next we will describe the usage of an external cloud database service (MongoDB) for Android.Connection requires a Java driver and the mongo.jarlibrary added to the classpath.The driver was developed by the community and released for Android ( According with MongoDB architecture, multiple entry points can be used, all being part of replica sets (database instances forming a cluster in order to easily replicate data).As stated in the official documentation, the java MongoDB driver is thread safe and it is recommended to create a single object which can be accessed by multiple threads.Internally, this object creates a pool of connections, and for each operation it is able to find an available connection, use it and release the resources after it is done.The developer can enforce a certain consistent behaviour (usage of the same socket by the client) by calling the two specially designed functions, db.requestStart() and db.RequestDone().The driver also allows the developer to use authentication by username and password.After the connection sequence is performed, collections (all, or a specific one) can be retrieved, documents can be inserted, simple find operations can be performed by calling findOne() method.An important functionality is the usage of cursors by assigning the result of a collection.find()call to a DBCursor object.Sequential operations can be performed using the obtained cursor object.In addition to the operations enumerated above, some administrative actions can be per-formed as creating indexes, dropping them and obtaining a list for the collections in the current database.Some limitations might appear when connecting to a database service in the cloud.A very important functionality covered by the Java MongoDB driver is the ability to access the aggregation framework (see section 6).The wrapper around this functionality provided by the driver is DBCollection.aggregate().
Generic DBObject instances can be used by the programmer to create the pipeline of operations needed in aggregation processes.Finally, and AggregationOutput object can be obtained after calling the aggregate method over a certain collection.In order to better illustrate the above features, a popular service for MongoDB database in the cloud will be used as persistence layer for a demonstrative Android application.The service is provided by MongoLab and it offers physical storage in different data centers like Amazon, Joyent or Microsoft Azure.At the time of writing of this paper, the available storage options for shared plans are: 0.5, 1, 2 and 4 GB.The corresponding prices are between 10 to 40 USD each month (except the 0.5 GB instance which is free).The customer can also buy dedicated plans (details and prices varies from one storage provider to another.For Amazon hosted MongoDB databases in the cloud, available RAM resources start from 1.7 GB and go up to 68 GB, available processors core are between 1 and 8 and the user can choose one or more dedicated nodes.Default storage for each unit type varies from 40 to 160 GB and can be easily extended or moved to SSD disks.Just as a plan example, for a 34 GB of RAM, 2 dedicated nodes, 4 processor cores, 80 GB storage capacity (on SSD disks) costs about $3000 each month.Of course, the storage capacity doesn't come cheap, but includes additional services as MongoDB monitoring service activated, real-time access to created log files, 24/7 DBA assistance from 10Gen (creator of MongoDB), replica sets and dedicated virtual machines.DOI: 10.12948/issn14531305/17.2.2013.04 In order to create a database, some information needs to be provided: database name, data center provider (we choose Amazon, an EU Ireland instance based on proximity criteria), the instance type (depending on the storage capacity needed), a new username and password for connections.Also, MongoLab allows the users to clone any already created database using its interface or any other accessible in the cloud.After creating the database, some additional information is provided to the user: the connection string for MongoDB shell (in our case, # mongo ds061807.mongolab.com:61807/testdb-u <dbuser> -p <dbpassword>), the standard URI for client connection (for our database: mongodb://<dbuser>:<dbpassword>@ds06 1807.mongolab.com:61807/testdb).Connections are available using database drivers, including the java driver for Android.Moreover, the web interface allows users to add collections, manage the existing ones, adding and removing users and privileges, perform back-ups or back-up scheduled plans, import and export collections in binary, comma separated value and JSON formats, or even the entire database (binary only).The same web interface allows consulting statistics about the database as number of collections, number of objects, indexes, total storage size, average object dimensions, files size for the database.
In the remaining of this section we will exemplify some basic operations in a mobile application running on Android or iOS.The persistence layer is assured by the previously described provider, MongoLab, and all three components connect directly to the database without the need for middleware software.The application is designed to be used by didactic personnel in a faculty in order to remotely register grades obtained by students during exams.The following operations are performed using the mobile application:  the user connects to the cloud database, across the network;  the user retrieves all students from a collection, based on their group number;  adding a new grade (for a student);  getting the average grade for each group of students.Connection to the cloud based MongoDB instance using the Android SDK requires the provided standard URI, username ( profes-sor1) and password (education): MongoClient mongoAndroid = new MongoClient( "mongodb://professor1:education>@ds061807.mongolab.com:61807/testdb"); Assuming that each group of students has the grades stored in a specific collection, for the group InfoEc2, the mechanism of retrieving the entire collection is: For inserting a document, multiple options are available, e.g using document builder classes, generic database objects, parsing strings in JSON formats.The next three solutions insert a grade for a student: Assuming a collection containing all the students and the groups they belong to, here is the solution for getting all from group "1204": In the next section we will focus on some of the MongoDB-SQL differences in terms of basic data definition and manipulation.

Basic Differences between MongoDB and SQL in Terms of Data Definition and Manipulation
Compared with the data solution in previous section for the remaining of this paper MongoDB database is installed following a classical replica set which is required for the server side of a distributed mobile application.The relational database schema which serves as case study is depicted in figure 2. Invoices is the main table which stores -along with invoices_items and products -data about sales.The buyers (customers) have one or more contacts -people with important posi-tions (CIOs, financial and procurement managers, etc.).Postal addresses refer to present Romanian administrative organization -address, postal/zip code, location (city or village), and county -but can easily be understood by non-Romanians (also by Romanians!).Invoice payments are stored in receipts and receipts_invs tables.The data model is rather different in MongoDB.In SQL databases the above (sub)schema is composed by tables (views, stored procedures, etc.), each table having a common structure for all of its rows.Equivalent to tables, MongoDB databases have collections.Each collection have is composed by documents which can have a completely different structure.As the data model differs, there are some SQL option with no MongoDB equivalence and vice versa.For example in a typical relational (SQL) database server, the user (or application) is connected to a database (sub)schema.Depending on the user rights, from current subschema, user can access objects placed in other subschemas.In MongoDB the user can choose the current database (schema) using a command which reminds us of dBase or FoxPro (xBases):

use local
The storage objects in SQL databases -tables -can be displayed, as with every type of database object, by querying the data dictionary.In PostgreSQL the syntax is: One of the main differences between SQL and NoSQL datastores concerns data objects creation and population.In SQL there is a clear distinction between DDL (Data Definition) commands, such as:

INSERT INTO counties VALUES ('IS', 'Iasi', 'Moldova');
On the contrary, this distinction does not exist in MongoDB and other NoSQL DBMSs.
Collections are created on the fly, when a fist document is inserted using functions like insert, save or update.All documents in a collection have an object id which can be generated automatically by the system oras in above save statementspecified by the user.
As previously pointed out, another major difference is related to the record structure.In SQL database tables, every row has a similar (tabular) structure.When inserting a row without declaring values for all attributes, unspecified attributes will get by default null values: An alternate key can be declared in SQL/PostgreSQL as follows:

ALTER TABLE counties ADD UNIQUE (countyName);
In MongoDB there is no such thing as alternate key, but the similar result can be achieved creating another index using UNIQUE option:

6
Queries in MongoDB and SQL/PostgresSQL The core SQL strength is its almighty SE-LECT command used for expressing queries with various degree of complexity.In Mongo, there are many techniques for database query, some of them being shown below.For basic queries, the best equivalent of SQL SELECT is MongoDB find function.Whereas SELECT * FROM counties ORDER BY countyName extracts all rows (ordered) in a SQL table, in MongoDB all documents (ordered) in a collection can be extract as follows:

db.counties.find().sort({ countyName : 1 } )
First argument -which lacks in above queryis, by default, predicate for filtering docu-ments.To order the documents in the result, sort clause is needed:

db.counties.find( { countyRegion : "Moldova" } ).sort({ countyName : 1 })
SQL language is set oriented.Current row, first row which fulfills a predicate do not exists in SQL base vocabulary of SELECT statement, but only in procedural extensions of SQL (cursors).So, for example, in order to extract first county in Moldova region, ranking options are needed (e.g.TOP, LIMIT, RANK, DENSE_RANK):

SELECT * FROM counties WHERE countyRegion = 'Moldova' ORDER BY countyName LIMIT 1;
On the contrary, in MongoDB findOne function returns and displays just first record from all the records which fulfills a predicate (again, older database guys will remember LOCATE command in dBase or FoxPro):

db.counties.findOne ( { countyRegion : "Moldova" } )
Both SQL and MongoDB have options for nesting queries.Taking the problem of extracting last two records matching a predicate -let's say, display the last two counties in Moldova region.In SQL the above solution with and additional query nesting in FROM clause is functioning:

SELECT * FROM (SELECT * FROM counties WHERE countyRegion = 'Moldova' ORDER BY countyName DESC LIMIT 2) t ORDER BY countyName
In MongoDB we can use a JavaScript variable (see next section), but also we can nest a count function in find statement:

db.counties.find( { countyRegion : { $exists : false } } ). sort({ countyName : 1 })
In SQL there is no equivalent query, since all the table rows share the same structure.So all records will have countyRegion attribute, but in some of the rows its value are NULL:

SELECT * FROM counties WHERE countyRegion IS NULL ORDER BY countyName ;
Mongo is not able to make the difference between "not declared" attributes and "declared but null values" ones.Both criteria will be met by the following query:

db.counties.find( { countyRegion : { $exists : true }, countyRegion : null } )
In both SQL and MongoDB NULL value can appear in a list.Taking the following example: extract counties with no region (countyRegion attribute) plus counties from Moldova region.In SQL the query that answers the problem is:

db.counties.find( { countyRegion : { $in : [ "Moldova", null] } }). sort({ countyName : 1 })
Things get slightly more complicated when interested in extracting the distinct values of an attribute.Whereas in SQL there is a simple DISTINCT clause used in SELECT command:

SELECT DISTINCT countyRegion FROM counties ORDER BY countyRegion ;
in MongoDB extracting distinct values of an attribute (countyRegion in collection "coun-ties") can be achieved with (at least) three types of queries: db.runCommand({"distinct" : "counties", "key" : "countyRegion"}) //or: db.counties.distinct('countyRegion') //or using aggregation framework: db.counties.aggregate( [ { $project : {countyRegion : "$countyRegion", _id:0 } }, { $group : {_id : "$countyRegion" }} , { $sort : { _id : 1} } ] ) or using another aggregation framework solution, based on $addToSet option: Remember that actually MongoDB shell is a JavaScript shell.The above solution uses countMoldova JavaScript variable that stores the number of counties in Modova region.This is the argument for the second command, which displays the last two counties in that region.The main source of the need for variables is the lack of joins in MongoDB.When a collection is queried based on the documents extracted from another collection, generally it is necessary to split the solution into many queries and to store intermediate results in variables.As pointed out in section 4, there are no constraints to be declared in MongoDB.There are no foreign keys and the database is not normalized [26].There is a special datatype -DBRef -(http://docs.mongodb.org/manual/applications/database-references/) but even the mongo official documentation does not recommend using it.Collection postalCodes has a foreign key-like -countyCodebut its values will be handled "manually": In MongoDB there are no subqueries, but the rows selected by a first query (sub-query equivalent) can be transferred to an array searched with $in operator: For customer Client 2 SA two contacts were introduced, Vasile Ion who is Director general (general manager) and Popovici Ioana who is Sef aprovizionare (procurement management).In terms on MongoDB the document that describes the above customer contains a number of key-values pairs, from which the values of the attribute contacts is an array containing two elements.Each element is, at its turn, a (sub) document composed of (sub) document person and attribute (and its values) position.We do not dive into details above the strengths and weaknesses of this schema, but develop some differences in terms of data definition, manipulation and query that occur.What in SQL is the operations of updating and querying rows in tables can be translated in MongoDB not only in update/query documents in a collection, but also, update/query a subdocument or an array (included into another array…) in a document.Taking the simple example of updating an attribute value on a record, i.e. update phone number for customer 'Client 1 SRL'.In SQL that is possible by an UPDATE command:

UPDATE customers SET phone = '0232217001' WHERE custName = 'Client 1 SRL'
In MongoDB the query is pretty similar: Sometimes operations that are simple in SQL raise serious problems in MongoDB.For example, setting an attribute value from another attribute value in the same (sub)record.We discover that the mobilePhone number of Iurea Simion (personal code CNP5) is actually his officePhone number.Two updates (that could be fusioned into a single one) solve the problem in SQL:

UPDATE people SET officePhone = mobilePhone WHERE persCode = 'CNP5' ; UPDATE people SET mobilePhone = NULL WHERE persCode = 'CNP5' ;
In MongoDB this type of updates is not yet possible, so setting an attribute value from another attribute in the same document requires some basic programming/scripting.The idea is to store mobilePhone value into a variable and then update the office phone: Since the document structure in contacts collection is denormalized, Popa Ioanid appears in three documents, as contact for three different customers.This is an opportunity to point out another difference SQL-MongoDB.Whereas in SQL the default scope of update command is all the set of rows that satisfy the predicate in WHERE clause, in MongoDB the default scope is the only first document satisfying the predicate.So in this case update function must presents a third parameter multithat sets the scope to all documents that meet the criteria: guage.Even versions of the standard have been published every now and then (1989,1992,1999,2003,2008,2011), the core of the language remains unchanged since its inceptions.On the contrary, in MongoDB, DDL, DML and query options improve at a rapid pace with every new version, even the product seems to have reached a certain level of maturity.If, for example, attribute address must be renamed into postAdress, in SQL that has been possible through ALTER As already exemplified, due to different data structure, the some problem translates into different operations in SQL and MongoDB.Continuing with a correction.In both schemas, officePhone was placed in person table or sub-document.This is wrong, since officePhone is related simultaneously to both person and customer.So we need to move officePhone attribute (along with its values) from person to contact (mobilePhone is personal, but officePhone belongs to employer).In SQL (PostgreSQL syntax) this id done by following succession:

Data Layer Options on Some Major Mobile Platforms. From Local to the Cloud Microsoft
and Blackberry are struggling to get more users, by renewing their operations systems with Windows Phone 8 and Blackberry OS 10.But Android and iOS, produced by Google and Apple, are still ahead of the pack Mahmoud et al. [7] point out the most common software options for storage in mobile applications:  HTML5 (localStorage API which stores objects as key-value pairs and IndexDB which implements relational technology); DOI: 10.12948/issn14531305/17.2.2013.04

SELECT countyRegion, COUNT(*) FROM counties GROUP BY countyRegion ORDER BY countyRegion
To exemplify how to filter groups of records, we'll try to answer the problem of finding how many regions have more than three counties.In SQL GROUP BY option must be combined with HAVING: DOI: 10.12948/issn14531305/17.2.2013.04

7 More Advanced Options for Data Defini- tion, Manipulation and Query
We start this section with a problem from the previous one: display the last two counties in Moldova region.The next MongoDB solution shows another difference from SQLthe extensive use of variables in some queries:

custID = contacts.custID INNER JOIN people ON contacts.persCode = people.persCode WHERE contacts.position = 'Sef aprovizionare'
DOI: 10.12948/issn14531305/17.2.2013.04But in MongoDB due to the schema of records in contact collection this problem requires querying defining a predicate that involves an attribute within an array:In MongoDB the problem requires inserting an element into array customers which is possible either by $push or $addToSet:

TABLE people DROP COLUMN officePhone ;
TABLE for adding contact.officePhoneCurrently, in MongoDB the problem required renaming an array field which is possible only by the following code: Upsert (update combined in a single statement with insert) is among operations that are easier to perform in Mongo than in SQL/ PostgreSQL.In SQL standard and some dialects there is a special command -MERGE.Unfortunately, it is not yet implemented in PostgreSQL so the following MongoDB upsert has not an equivalent: db.

products.update ( {_id : 1}, {_id: 1, prodName : 'Produs 1', mu : 'buc', prodCateg : 'Cosmetice', percVAT : .24 }, {upsert : true} ) ;
If in collection products there is already a document with _id = 1, then the above statement is an update.Otherwise, it is an insert.As usual, things get easier with deletions, but, as with inserts and updates, sometimes a SQL DELETE operation translates into MongoDB into deleting documents, or subdocuments or array elements.But we will start with deletion of a data storage objecttable or collectionwhich is quite similar: Also in many situations deleting rows in a table translates simply in deleting documents in a collection: