Introduce

websql-orm framework，support typescriptangularcordovachrome sqlite database.

中文文档

Usage

websql-orm use TypeScript language, needs to be in before using tsconfig.json add a decorator configuration items to enable the decorator features.

{
"compilerOptions": {
"experimentalDecorators": true
}
}

Installation

npm install websql-orm@latest

Tip: do not install a version prior to 2.1.0. The version prior to 2.1.0 is the debug version and cannot be used.

cordova plugin add cordova-sqlite-storage

Tip: In the cordova project, you need to install the cordova plugin.

import{EnvConfig}from'websql-orm';EnvConfig.useCordovaSqliteStorage=true;

Enable Debug log

import{EnvConfig}from'websql-orm';EnvConfig.enableDebugLog=true;

Other Config

import{EnvConfig}from'websql-orm';// �时间移除毫秒EnvConfig.dateFormatRemoveMillisecond=true;

Define table

How do I define a table using an entity class?

import{database,column,ColumnType,Table}from'websql-orm';
@database("student_db","student")exportclassstudentextendsTable{
@column(ColumnType.STRING|ColumnType.PRIMARY)id: string;
@column(ColumnType.STRING)user_name: string;}

1. Decorator @database definition student table, the name of the class student is the name of the table, student_db is the database name.
2. Decorator @column definition column, ColumnType.STRING define text type.
3. Each table must have a PRIMARY key field, ColumnType.PRIMARY define the PRIMARY key field.
4. Each entity class must inherit Table.

Decorator description

Decorator name	Description	Sample
@database	Define table	@database("db_name","table_name")
@column	Define column	@column(ColumnType.STRING)
@reference	Define foreign key references	@reference('class_info','id')

Define foreign key reference see advanced section

Field type enumeration

Table field enumeration values are consistent with TypeScript primitive types.

Field type enumeration	Description
ColumnType.PRIMARY	Primary key
ColumnType.STRING | ColumnType.PRIMARY	Primary key (string)
ColumnType.NUMBER | ColumnType.PRIMARY	integer PRIMARY KEY autoincrement
ColumnType.BOOLEAN	boolean
ColumnType.NUMBER	number
ColumnType.STRING	string
ColumnType.ARRAY	Array
ColumnType.DATE	Date
ColumnType.ANY	any

How to use

Take the student table defined above as an example

websql-orm Methods list

sqlite.fromSqlQuery the table record and return a list of records

varlist=awaitsqlite.fromSql(newstudent(),'select * from student where user_name=? and id=? ',['Tom','guid']);

sqlite.fromSqlFirstQuery the first table record and return the first record

varinfo=awaitsqlite.fromSqlFirst(newstudent(),'select * from student where user_name=? ',['Tom']);

sqlite.existQuerying for the existence of a record returns true or false

varresult=awaitsqlite.exist(newstudent(),'b4ce6b51-0bd6-46ee-a5c7-d1d5a93bdee9');

sqlite.insertInserts a record, returning the number of rows affected

varstu=newstudent();stu.id=uid;stu.user_name='Tom';varresult=awaitsqlite.insert(stu);

sqlite.updateModify the record to return the number of rows affected

varinfo=awaitsqlite.fromSqlFirst(newstudent(),'select * from student where user_name=? ',['Tom']);info.user_name='Sam';varresult=awaitinfo.save();// var result = await sqlite.update(info)

sqlite.queryQuery records and return a list of records

varlist=awaitsqlite.query(newstudent(),{user_name:'Tom'});

sqlite.queryFirstQuery the first record

varinfo=awaitsqlite.queryFirst(newstudent(),{user_name:'Tom'});

sqlite.execSqlExecute the SQL statement and return the number of affected rows

varresult=awaitsqlite.execSql(newstudent(),'insert into (id,user_name) values (?,?)',['b4ce6b51-0bd6-46ee-a5c7-d1d5a93bdee9','Tom']);

sqlite.deleteDelete records

vardelResult=awaitsqlite.delete(newstudent(),'291d853d-021b-4a66-9322-9d32eb27eb27');

sqlite.fromSqlByJsQuery records (not tracking entities)

vardata:any=awaitsqlite.fromSqlByJs(dbName,'select * from student where user_name=? ',['Tom']);

sqlite.fromSqlFirstByJsQuery the first record (not track entity)

vardata:any=awaitsqlite.fromSqlFirstByJs(dbName,'select * from student where user_name=? ',['Tom']);

Use websql-orm in javascript

示例

Javascript, with no decorator, currently provides only three methods.

varsqliteJs=newSqliteJs('db_name');sqliteJs.fromSql("select * from hero where id = ?",[id]).then(function(result){console.log(result);});sqliteJs.fromSqlFirst("select * from hero where id = ?",[id]).then(function(result){console.log('successful');});sqliteJs.execSql("insert into hero (id,name) values (?,?)",[id,name]).then(function(result){if(result>0){console.log('successful');}});

Advanced

Define the reference

How do I define a foreign key reference?

@database('hero_db','hero')exportclassheroextendsTable{
@column(ColumnType.STRING|ColumnType.PRIMARY)id: string;
@column(ColumnType.STRING)full_name: string;/* ... Omit other field definitions ... */
@reference('id',newskill(),'hero_id')skills: Array<skill>;}

The sample

Entities defined

@database('hero_db','hero')exportclassheroextendsTable{
@column(ColumnType.STRING|ColumnType.PRIMARY)id: string;
@column(ColumnType.STRING)full_name: string;
@column(ColumnType.NUMBER)age: number;
@column(ColumnType.BOOLEAN)is_girl: boolean;
@column(ColumnType.DATE)join_time: Date;
@column(ColumnType.ANY)body_data: {stature: number,blood_type: BloodTypeEnum};
@reference('id',newskill(),'hero_id')skills: Array<skill>;}
@database('hero_db','skill')exportclassskillextendsTable{
@column(ColumnType.STRING|ColumnType.PRIMARY)id: string;
@column(ColumnType.STRING)name: string;
@column(ColumnType.STRING)descript: string;
@column(ColumnType.NUMBER)harm: number;
@column(ColumnType.STRING)hero_id:string;}exportenumBloodTypeEnum{A=1,B=2,AB=3,O=4,OTHER=5}

The sample

import{skill}from'./entities/skill';import{hero}from'./entities/hero';import{sqlite}from"websql-orm";import{BloodTypeEnum}from'./entities/BloodTypeEnum';exportclassDemo{privatelvbu_id="da93faef-bfff-49c6-92b9-8807ec196bab";privateliubei_id="ed609b25-166f-41de-9bbc-90eb8891b688";privateguanyu_id="7289f8cb-496c-4057-b054-3bb4a3af1d6c";constructor(){varthat=this;setTimeout(async()=>{awaitthat.addHero();awaitthat.queryHeros();awaitthat.queryHero(this.lvbu_id);awaitthat.updateHero(this.liubei_id);awaitthat.deleteHero(this.guanyu_id);},0);}asyncaddHero(){awaitthis.deleteHeros();letlvbu=newhero();lvbu.id=this.lvbu_id;lvbu.age=32;lvbu.full_name="吕布";lvbu.is_girl=false;lvbu.join_time=newDate("2000/01/01");lvbu.body_data={stature: 185,blood_type: BloodTypeEnum.A};letliubei=newhero();liubei.id=this.liubei_id;liubei.age=31;liubei.full_name="刘备";liubei.is_girl=false;liubei.join_time=newDate("2001/02/01");liubei.body_data={stature: 178,blood_type: BloodTypeEnum.B};letguanyu=newhero();guanyu.id=this.guanyu_id;guanyu.age=30;guanyu.full_name="关羽";guanyu.is_girl=false;guanyu.join_time=newDate("2001/02/01");guanyu.body_data={stature: 180,blood_type: BloodTypeEnum.AB};awaitsqlite.save(lvbu);awaitsqlite.save(liubei);awaitsqlite.save(guanyu);awaitthis.addSkills();}asyncaddSkills(){letlvbu_skill=newskill();lvbu_skill.id=this.uuid();lvbu_skill.name="方天画斩";lvbu_skill.descript="方天画斩是吕布第一伤害技能";lvbu_skill.harm=76;lvbu_skill.hero_id=this.lvbu_id;letliubei_skill=newskill();liubei_skill.id=this.uuid();liubei_skill.name="以德服人";liubei_skill.descript="刘备清除身上的控制效果，并获得护盾，护盾存在期间刘备免疫控制";liubei_skill.harm=50;liubei_skill.hero_id=this.liubei_id;letguanyu_skill=newskill();guanyu_skill.id=this.uuid();guanyu_skill.name="青龙偃月";guanyu_skill.descript="关羽这个英雄要持续不断的跑才能发挥本身的威力";guanyu_skill.harm=70;guanyu_skill.hero_id=this.guanyu_id;letresult=awaitsqlite.insert([lvbu_skill,liubei_skill,guanyu_skill]);if(result>0){console.log("添加技能成功");}else{console.log("添加技能失败")}}asyncdeleteHero(id: string){letdelResult=awaitsqlite.delete(newhero(),id);if(!delResult){console.warn(`删除英雄${id}失败`);}else{console.log(`删除英雄${id}成功`);}}asyncdeleteHeros(){varsuccess=true;varheros=awaitsqlite.fromSql(newhero(),"select * from hero",[]);if(heros!=null&&heros.length>0){for(letindex=0;index<heros.length;index++){constelement=heros[index];letdelResult=awaitsqlite.delete(newhero(),element.id);if(!delResult){success=false;}}}if(!success){console.warn("删除所有英雄失败");}else{console.log("删除所有英雄成功");}}asyncupdateHero(id){var_hero=awaitsqlite.queryFirst(newhero(),{id: id});_hero.join_time=newDate();await_hero.save();console.log("修改英雄");}asyncqueryHeros(){varheros=awaitsqlite.fromSql(newhero(),"select * from hero",[]);console.log("查询所有英雄:");console.log(heros);}asyncqueryHero(id: string){varhero_=awaitsqlite.fromSql(newhero(),"select * from hero where id = ?",[id]);console.log("查询英雄:");console.log(hero_);var_hero=awaitsqlite.query(newhero(),{id: id});console.log("查询英雄:");console.log(_hero);var__hero=awaitsqlite.queryFirst(newhero(),{id: id});console.log("查询英雄:");console.log(__hero);var___hero=awaitsqlite.fromSqlFirst(newhero(),"select * from hero where id = ?",[id]);console.log("查询英雄:");console.log(___hero);}publicuuid(): string{lets: any[]=[];lethexDigits="0123456789abcdef";for(leti=0;i<36;i++){s[i]=hexDigits.substr(Math.floor(Math.random()*0x10),1);}s[14]="4";s[19]=hexDigits.substr((s[19]&0x3)|0x8,1);s[8]=s[13]=s[18]=s[23]="-";letuuid=s.join("");returnuuid;}}newDemo();

License

Copyright (c) 2019, Sam Chen. (ISC License)