Naming standards for SQLite

Naming Conventions for SQLite
     Each database, table, column, index, trigger, or view has a name by which it is identified
and almost always the name is supplied by the developer.The rules governing how a
valid identifier is formed in SQLite are set out in the next few sections.

Valid Characters
     An identifier name must begin with a letter or the underscore character, which may be
followed by a number of alphanumeric characters or underscores. No other characters
may be present.

These identifier names are valid:

   mytable
   my_field
  xyz123
  a

However, the following are not valid identifiers:

   my table
   my-field
   123xyz

Name Length
     SQLite does not have a fixed upper limit on the length of an identifier name, so any
name that you find manageable to work with is suitable.
Reserved Keywords
Care must be taken when using SQLite keywords as identifier names. As a general rule
of thumb you should try to avoid using any keywords from the SQL language as identifiers,
although if you really want to do so, they can be used providing they are enclosed
in square brackets.
For instance the following statement will work just fine, but this should not be mimicked
on a real database for the sake of your own sanity.
sqlite> CREATE TABLE [TABLE] (
...> [SELECT],
...> [INTEGER] INTEGER,
...> [FROM],
...> [TABLE]
...> );

Case Sensitivity
     For the most part, case sensitivity in SQLite is off.Table names and column names can
be typed in uppercase, lowercase, or mixed case, and different capitalizations of the same
database object name can be used interchangeably.
SQL commands are always shown in this book with the keywords in uppercase for
clarity; however, this is not a requirement.

Note

The CREATE TABLE, CREATE VIEW, CREATE INDEX, and CREATE TRIGGER statements all store
the exact way in which they were typed to the database so that the command used to create a database
object can be retrieved by querying the sqlite_master table. Therefore it is always a good idea to format
your CREATE statements clearly, so they can be referred to easily in the future.

Read More

SQLite first setup

   SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.

Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasis scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

SQLite does not compete with client/server databases.


First configuration for SQLite
     You have to write three js files they are
1) database_startup.js
2) database_query.js
3) database_debug.js

     database_startup.js file helps you to initialize the database, create tables and if you want to insert any default values.

var CreateTb1 = "CREATE TABLE IF NOT EXISTS tbl1(ID INTEGER PRIMARY KEY AUTOINCREMENT, CreatedDate TEXT,LastModifiedDate TEXT, Name TEXT)";
var CreateTb2 = "CREATE TABLE IF NOT EXISTS tbl2(ID INTEGER PRIMARY KEY AUTOINCREMENT, CreatedDate TEXT,LastModifiedDate TEXT,Mark INTEGER)";

var DefaultInsert = "INSERT INTO tbl1(CreatedDate,Name) select '" + new Date() + "','Merbin Joe'  WHERE NOT EXISTS(select * from tbl1)";

var db = openDatabase("TestDB", "1.0", "Testing Purpose", 200000); // Open SQLite Database

$(window).load(function()
{
  initDatabase();
});

function createTable() // Function for Create Table in SQLite.
{

  db.transaction(function(tx)
  {
    tx.executeSql(CreateTb1, [], tblonsucc, tblonError);
    tx.executeSql(CreateTb2, [], tblonsucc, tblonError);

    insertquery(DefaultSettingInsert, defaultsuccess);

  }, tranonError, tranonSucc);
}

function initDatabase() // Function Call When Page is ready.
{
  try
  {
    if (!window.openDatabase) // Check browser is supported SQLite or not.
    {
      alert('Databases are not supported in your device');
    }
    else
    {
      createTable(); // If supported then call Function for create table in SQLite
    }
  }
  catch (e)
  {
    if (e == 2)
    {
      // Version number mismatch.
      console.log("Invalid database version.");
    }
    else
    {
      console.log("Unknown error " + e + ".");
    }
    return;
  }
}

     database_query.js this file is used to control all the insert, delete and update query's and finally it will call the given success function.

function insertquery(query, succ_fun)
{
  db.transaction(function(tx)
  {
    tx.executeSql(query, [], eval(succ_fun), insertonError);
  });
}

function deletedata(query, succ_fun)
{
  db.transaction(function(tx)
  {
    tx.executeSql(query, [], eval(succ_fun), deleteonError);
  });
}

function updatedata(query, succ_fun)
{
  db.transaction(function(tx)
  {
    tx.executeSql(query, [], eval(succ_fun), updateonError);
  });
}

function selectitems(query, succ_fun) // Function For Retrive data from Database Display records as list
{
  db.transaction(function(tx)
  {
    tx.executeSql(query, [], function(tx, result)
    {
      eval(succ_fun)(result.rows);
    });
  });
}

     database_debug.js if any error occur on the transaction or insert or delete or update time the error will through to this files

function tblonsucc()
{
  console.info("Your table created successfully");
}

function tblonError()
{
  console.error("Error while creating the tables");
}

function tranonError(err)
{
  console.error("Error processing SQL: " + err.code);
}

function tranonSucc()
{
  console.info("Transaction Success");
}

function insertonError()
{
  console.error("Error on Insert");
}

function deleteonError()
{
  console.error("Error on delete");
}

function defaultsuccess()
{
  console.info("Default Insert Success");
}

function updateonError()
{
  console.error("Error on update");
}

     You can use the pass the values by following methods.
Select from Table

var query="select Name,CreatedDate  from tbl1 where ID=1";  
selectitems(query,getval_success);
-----
-----
function getval_success(result)
{
  if(result.length>0)
 {
  for(var i = 0, item = null; i < result.length; i++)
  {
     item = result.item(i);
     alert(item['Name']);
  }
 }
}

Update values to Table

   var query="update Ex_tb_InExType set IsActive=0 where TypeID="+DelID;
   updatedata(query,select_function);

Insert Value to Table

   var query="insert into tbl1(CreatedDate,Name) values('"+new Date()+"','Joe')";
   insertquery(query,select_function);

Read More