Search Your Question

Showing posts with label Sql. Show all posts
Showing posts with label Sql. Show all posts

Delete record from Core-data

Ans: 

Deleting a record from a persistent store involves three steps:

  1. Fetch the record that needs to be deleted
  2. Mark the record for deletion
  3. Save the changes
Code : 


let fetchRequest: NSFetchRequest<Profile> = Profile.fetchRequest()
fetchRequest.predicate = Predicate.init(format: "profileID==\(withID)")
let objects = try! context.fetch(fetchRequest) // Step 1
for obj in objects {
    context.delete(obj)  // Step 2
}

do {
    try context.save() //  <- remember to save data // Step 3
} catch {
    // Do something... 
}

Difference between Core Data and Sqlite

Ans : Core-data is not database that we understand. Sqlite is Database. 

Lets have a look over difference between Core-data and Sqlite (or Database) :

Core Data:


  1. Primary function is graph management (although reading and writing to disk is an important supporting feature).
  2. Operates on objects stored in memory (although they can be lazily loaded from disk).
  3. Works with fully-fledged objects that self-manage a lot of their behavior and can be sub classed and customized for further behaviors.
  4. Non-transactional, single threaded, single user (unless you create an entire abstraction around Core Data which provides these things).
  5. Only operates in memory.
  6. Requires a save process.
  7. Can create millions of new objects in-memory very quickly (although saving these objects will be slow).
  8. Leaves data constraints to the business logic side of the program.


Database or SQLite:


  1. Primary function is storing and fetching data.
  2. Operates on data stored on disk (or minimally and incrementally loaded).
  3. Stores "dumb" data.
  4. Can be transactional, thread-safe, multi-user.
  5. Can drop tables and edit data without loading into memory.
  6. Perpetually saved to disk (and often crash resilient).
  7. Can be slow to create millions of new rows.
  8. Offers data constraints like "unique" keys.
Credit : http://www.cocoawithlove.com/2010/02/differences-between-core-data-and.html


difference between coredata and sqlite

Advantage of Core-data : 

  • Much better memory management. With a plist you must load the entire thing into memory; with Core Data only the objects you're currently using need to be loaded. Also, once objects are loaded, they're normally placeholder "fault" objects whose property data doesn't load until you need it.
  • Related to the above, when you have changes, you can save only the changed objects, not the entire data set.
  • You can read/write your model objects directly instead of converting them to/from something like an Dictionary.
  • Built-in sorting of objects when you fetch them from the data store.
  • Rich system of predicates for searching your data set for objects of interest.
  • Relationships between entities are handled directly, as properties on the related objects. With a plist you would need to do something like store an object ID for a relationship, and then look up the related object.
  • Optional automatic validation of property values.
More about Core-data : 

  • CoreData isn't a Database. It's an object persistence layer. There is no concept of primary keys or foreign keys in CoreData.
  • If you want to establish a relationship between two entities. You'll define a relationship, CoreData takes care of how that relationship is stored.
  • Select an entity, use plus button at the bottom of the entities attributes list, select add relationship, select the destination entity from the dropdown.
  • Select the destination entity and define an inverse relationship in the same way.

What are join in sql? Explain Types of Join

Ans :

Join :
When we need data from more than 1 table, then we can fetch data from those table using joining those table. It will return only matching data from two tables.

Select * from table1 t1 inner join table2 t2 on t1.column1 = t2.column1

Types of Join :

1. Inner Join :
If we use inner join between two table, then only data exists in both table, are returned.

2. Outer Join :
    Left Outer Join : It returns all data from left table(table1) and only matching data from both table.
    Right Outer Join : It returns all data from right table(table2) and only matching data from both table.

3. Cross Join : It is like cartesian join. It returns all records from both table and it returns table1.count * table2.count records in returns. Suppose table1 has 4 records and table2 has 3 records and returns 4*3 records.

4. Self Join : If some column's reference has in same table then self join is used. It is same like inner join but here both left and right table are same. Its like if you table and Columns are such as MainID, Name, ParentID then you can make query like
Select * from table t1 join table t2 on t1.MainID = t2.ParentID

Add column in SQLite

Ans : 
If we have uploaded our app ver 1.0 and we want to add columns in sqlite database, then following thing can done.

If we have sqlite database which table structure can be changed after release. Then we need to maintain database version. We can update database version by excecuting query like PRAGMA user_version = version_num; (For swift : PRAGMA table_info(tblTest))

In second release If we need to add columns, then we can check database version, If database version is old version then we can execute following query :

ALTER TABLE {tableName} ADD COLUMN COLNew {type};

Query for last inserted row in SQL database

Ans. 

If column is primary key and integer, then ,

Sqlite : SELECT * FROM table ORDER BY column DESC LIMIT 1;

or

SELECT * FROM table WHERE ID = SELECT LAST_INSERT_ROWID()

FMDB : 

Last inserted id is : [fmdb lastInsertRowId];

Sqlite3 all functions : 

sqlite3_open: This function is used to create and open a database file. It accepts two parameters, where the first one is the database file name, and the second a handler to the database. If the file does not exist, then it creates it first and then it opens it, otherwise it just opens it.
sqlite3_prepare_v2: The purpose of this function is to get a SQL statement (a query) in string format, and convert it to an executable format recognisable by SQLite3.
sqlite3_step: This function actually executes a SQL statement (query) prepared with the previous function. It can be called just once for executable queries (insert, update, delete), or multiple times when retrieving data. It’s important to have in mind that it can’t be called prior to the sqlite3_preprare_v2 function.
sqlite3_column_count: This method’s name it makes it easy to understand what is about. It returns the total number of columns (fields) a contained in a table.
sqlite3_column_text: This method returns the contents of a column in text format, actually a C string (char *) value. It accepts two parameters: The first one is the query converted (compiled) to a SQLite statement, and the second one is the index of the column.
sqlite3_column_name: It returns the name of a column, and its parameters are the same to the previous function’s.
sqlite3_changes: It actually returns the number of the affected rows, after the execution of a query.
sqlite3_last_insert_rowid: It returns the last inserted row’s ID.
sqlite3_errmsg: It returns the description of a SQLite error.
sqlite3_finalize: It deletes a prepared statement from memory.

sqlite3_close: It closes an open database connection. It should be called after having finished any data exchange with the database, as it releases any reserved system resources.

Difference between Sqlite and CoreData

Ans  : Sqlite is database and Coredata is Object Relational Model which is layer between UI and Database. Coredata is memory efficient and Querying to Sqlite is not so efficient.
So we can't compare Sqlite and Coredata. Both are different thing.