Материал из for iRidium developers
Перейти к: навигация, поиск
Эта страница является переводом страницы DB API. Перевод выполнен на 100%.

Other languages:
English • ‎русский

iRidium API. Databases

The system and user SQL databases store information about changes of server variables in iRidium Server. On the basis of this information you can create graphs and logs

SQLite version: 3.8.7

To create requests to databases get acquainted with the SQL language.


System Database

It is available for reading only. It is filled with data from tags of the server project in which the data saving in the database is set up.

IR.GetDatabase

Get access to the system database as to an SQL object to read data from it.

the constructor of SQL objects. It creates SQL objects for work with the SQLite database

Syntax

IR.GetDatabase();

input value description

-

output
Object [object SQLPrototype] type: Object
объект - the system database


Sample

var systembase = IR.GetDatabase();
IR.Log(systembase) // [object SQLPrototype]

the system database is available for reading only


User Databases

They are available for reading, writing, editing and deleting. Create your own database with the help of the constructor and fill it in with the help of SQL object methods.

new SQL()

the constructor of the SQL for creating user SQLite databases

Syntax

new SQL();

input value description

-

output
Object [object SQLPrototype] type: Object
the object - the user database


Sample

var mybase = new SQL(); 
IR.Log(mybase) // [object SQLPrototype]

to refer to the object write it in the variable


RecordSet Object

The RecordSet object - it is selection of records from the SQL database. We receive it after by request to the database:

var RecordSetObj = sqlobject.Request('SELECT <request>');

Example:

// to get all records from the Info table where the Value field < 100
var RecordSetObj = database.Request('SELECT * FROM Info WHERE Value<100');
IR.Log(RecordSetObj) // [object RecordSetPrototype]

The RecordSet object has unique properties. You can apply methods specific only for the RecordSet object.


Methods of SQL Objects

To form a user database, create an SQL object and write it in the variable (name it database). You can apply the following methods to the object:


.Open

to open the database file

Syntax

sqlobject.Open(file)

input value description
sqlobject type: object
the object - the user or system database
file "MyBase.db" type: string
the name of the database file
output

-


Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
   mybase.Open('MyBase.db');
   mybase.Execute("BEGIN");
   mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
   mybase.Execute("COMMIT");  
   mybase.Close();
});

If there is no such file, it will be created in the folder of project documents, for example C:\Users\User\Documents\iRidium pro documents beta\Server\Documents\<Project Name>\MyBase.db


.Execute

to perform actions with the database. Available actions are described in the SQL language

Syntax

sqlobject.Execute(action)

input value description
sqlobject type: object
the object - the user or system database
action "BEGIN" type: string
the action with the database
output

-


Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
   mybase.Open('MyBase.db');
   mybase.Execute("BEGIN");
   mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
   mybase.Execute("COMMIT");  
   mybase.Close();
});

Use logic commands to write in the database fast and correctly:

mybase.Execute("BEGIN");   // to start work with the database
...
// actions with the database 
...
mybase.Execute("COMMIT");  // to finish work with the database




.Request

requests to the database allow you to indicate conditions for data selection from the database. The rules for forming conditions are described in the SQL language

Syntax

sqlobject.Request(conditions)

input value description
sqlobject type: object
the object - the user or system database
conditions 'SELECT * FROM Info WHERE Value<100' type: string
the body of the request to the database
output
RecordSet Object [object RecordSetPrototype] type: object
the RecordSet object, it contains the request result


Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN");  
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');  
      IR.Log(RecordSetObj.GetColumnName(0)+'\t'+RecordSetObj.GetColumnName(1)+'\t'+RecordSetObj.GetColumnName(2));
      mybase.Execute("COMMIT");
      mybase.Close();
});

in response to the request we get the RecordSet object which contains the request result


.Close

to finish work with the database

Syntax

sqlobject.Close()

input value description
sqlobject type: object
the object - the user or system database
output

-


Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Close();
});

Before finishing work with the database, save all changes with the command mybase.Execute("COMMIT")



Methods of RecordSet Objects

RecordSet - it is a selection of records in the SQL database which you receive by request to the database. You can apply methods of work with database rows and columns to the RecordSet object. You can request object properties.


.GetColumnName

the column name (the column index: 0...n)

Syntax

RecordSetObj.GetColumnName(index)

input value description
RecordSetObj type: object
the RecordSet object, it contains data selection received by request to the database
index 0 type: number
the column index, 0...n
output
column name ID type: string
the column name


Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN");
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');     
      IR.Log(RecordSetObj.GetColumnName(0)+ '\t'+ RecordSetObj.GetColumnName(1)+ '\t'+ RecordSetObj.GetColumnName(2));
      mybase.Execute("COMMIT");
      mybase.Close();
});





.GetColumnType

the column type: int (1), float (2), string (3). The column index: 0...n

Синтаксис

RecordSetObj.GetColumnType(index)

на входе значение описание
RecordSetObj type: object
the RecordSet object, it contains data selection received by request to the database
index 0 type: number
the column index, 0...n
на выходе
type 1 type: number
the data type:
  • 1 - int
  • 2 - float
  • 3 - string


Пример

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN");
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');  
      IR.Log(RecordSetObj.GetColumnType(0)+ '\t'+ RecordSetObj.GetColumnType(1)+ '\t'+ RecordSetObj.GetColumnType(2));
      mybase.Execute("COMMIT");
      mybase.Close();
});





.GetColumnIndex

the number of rows in the column (the column index: 0...n)
the function does not work

Syntax

RecordSetObj.GetColumnIndex(index)

input value description
RecordSetObj type: object
the RecordSet object, it contains data selection received by request to the database
index 0 type: number
the column index, 0...n
output
rows 1 type: number
the number of rows in the column


Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN");
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');  
      IR.Log(RecordSetObj.GetColumnIndex(0)+ '\t'+ RecordSetObj.GetColumnIndex(1)+ '\t'+ RecordSetObj.GetColumnIndex(2));
      mybase.Execute("COMMIT");
      mybase.Close();
});





.GetRowValue

the value in the table cell selected by the indexes of the column and row (indexes: 0...n)

Syntax

RecordSetObj.GetRowValue(column, row)

input value description
RecordSetObj type: object
the RecordSet object, it contains data selection received by request to the database
column 0 type: number
the column index, 0...n
row 0 type: number
the row index, 0...n
output
data 1 type: number, string
the value in the cell


Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN"); 
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');  
      IR.Log(RecordSetObj.GetRowValue(0,0)+ '\t'+ RecordSetObj.GetRowValue(1,0)+ '\t'+ RecordSetObj.GetRowValue(2,0));
      mybase.Execute("COMMIT");
      mybase.Close();
});





Object Properties

RecordSet

RecordSetObj.property

Rows 4 type: Number
the number of rows in the table
Columns 12 type: Number
the number of columns in the table