sqfentity 2.0.0-nullsafety.0 
sqfentity: ^2.0.0-nullsafety.0 copied to clipboard
huseyintokpinar.comSqfEntity ORM for Flutter/Dart lets you build and execute SQL commands easily and quickly with the help of fluent methods similar to .Net Entity Framework.
import 'package:flutter/material.dart';
import 'package:flutter/services.dart';
// STEP 1: import sqfentity package.
import 'package:sqfentity_gen/sqfentity_gen.dart';
import 'package:sqfentity/sqfentity.dart';
import 'model/model.dart'; // you do not need to import this file. Just follow next steps
// STEP 2: define your tables as shown in the example Classes below.
// Define the 'TableCategory' constant as SqfEntityTable.
const SqfEntityTable tableCategory = SqfEntityTable(
tableName: 'category',
primaryKeyName: 'id',
primaryKeyType: PrimaryKeyType.integer_auto_incremental,
useSoftDeleting: true,
// when useSoftDeleting is true, creates a field named 'isDeleted' on the table, and set to '1' this field when item deleted (does not hard delete)
modelName:
null, // SqfEntity will set it to TableName automatically when the modelName (class name) is null
// declare fields
fields: [
SqfEntityField('name', DbType.text),
SqfEntityField('isActive', DbType.bool, defaultValue: true),
]);
// Define the 'TableProduct' constant as SqfEntityTable.
const SqfEntityTable tableProduct = SqfEntityTable(
tableName: 'product',
primaryKeyName: 'id',
primaryKeyType: PrimaryKeyType.integer_auto_incremental,
useSoftDeleting: true,
fields: [
SqfEntityField('name', DbType.text),
SqfEntityField('description', DbType.text),
SqfEntityField('price', DbType.real, defaultValue: 0),
SqfEntityField('isActive', DbType.bool, defaultValue: true),
SqfEntityFieldRelationship(
parentTable: tableCategory,
deleteRule: DeleteRule.CASCADE,
defaultValue: '0'), // Relationship column for CategoryId of Product
SqfEntityField('rownum', DbType.integer,
sequencedBy:
seqIdentity /*Example of linking a column to a sequence */),
SqfEntityField('imageUrl', DbType.text)
]);
// Define the 'Todo' constant as SqfEntityTable.
const SqfEntityTable tableTodo = SqfEntityTable(
tableName: 'todos',
primaryKeyName: 'id',
useSoftDeleting:
false, // when useSoftDeleting is true, creates a field named 'isDeleted' on the table, and set to '1' this field when item deleted (does not hard delete)
primaryKeyType: PrimaryKeyType.integer_unique,
defaultJsonUrl:
'https://jsonplaceholder.typicode.com/todos', // optional: to synchronize your table with json data from webUrl
// declare fields
fields: [
SqfEntityField('userId', DbType.integer),
SqfEntityField('title', DbType.text),
SqfEntityField('completed', DbType.bool, defaultValue: false)
]);
// Define the 'identity' constant as SqfEntitySequence.
const SqfEntitySequence seqIdentity = SqfEntitySequence(
sequenceName: 'identity',
maxValue: 10000, /* optional. default is max int (9.223.372.036.854.775.807) */
//modelName: 'SQEidentity',
/* optional. SqfEntity will set it to sequenceName automatically when the modelName is null*/
//cycle : false, /* optional. default is false; */
//minValue = 0; /* optional. default is 0 */
//incrementBy = 1; /* optional. default is 1 */
// startWith = 0; /* optional. default is 0 */
);
// STEP 3: Create your Database Model constant instanced from SqfEntityModel
// Note: SqfEntity provides support for the use of multiple databases. So you can create many Database Models and use them in the application.
@SqfEntityBuilder(myDbModel)
const SqfEntityModel myDbModel = SqfEntityModel(
modelName: null,
databaseName: 'sampleORM.db',
// put defined tables into the tables list.
databaseTables: [tableCategory, tableProduct, tableTodo],
// You can define tables to generate add/edit view forms if you want to use Form Generator property
formTables: [tableProduct, tableCategory, tableTodo],
// put defined sequences into the sequences list.
sequences: [seqIdentity],
bundledDatabasePath:
null // 'assets/sample.db' // This value is optional. When bundledDatabasePath is empty then EntityBase creats a new database when initializing the database
);
/* STEP 4: That's All..
--> Go Terminal Window and run command below
flutter pub run build_runner build --delete-conflicting-outputs
Note: After running the command Please check lib/model/model.g.dart
Enjoy.. Huseyin TOKPINAR
*/
void main(List<String> args) async {
WidgetsFlutterBinding.ensureInitialized();
// ATTENTION! when the software/app is started, database will initialized.
// If the database is not initialized, something went wrong. Check DEBUG CONSOLE for alerts
await runSamples();
}
Future<bool> runSamples() async {
// add some products
await addSomeProducts();
// Print all categories
await printCategories(false);
// SELECT AND ORDER PRODUCTS BY FIELDS
await samples1();
// FILTERS: SOME FILTERS ON PRODUCTS
await samples2();
// LIMITATIONS: PAGING, TOP X ROW
await samples3();
// DISTINCT, GROUP BY with SQL AGGREGATE FUNCTIONS,
await samples4();
// UPDATE BATCH, UPDATE OBJECT
await samples5();
// DELETE BATCH, DELETE OBJECT, RECOVERY
await samples6();
// ORM (Object Relational Mapping) SAMPLE
await samples7();
// fill List from the web (JSON)
await samples8();
// run custom sql query on database
await samples9();
// SEQUENCE samples
await samples10();
// toJson samples
await samples11();
// create model from existing database sample
await createModelFromDatabaseSample();
return true;
}
Future<void> printListDynamic(SqfEntityProvider model, String pSql) async {
final list = await model.execDataTable(pSql);
printList(list);
}
void printList(List<dynamic> list) {
for (final o in list) {
print(o.toString());
}
}
Future<void> printCategories(bool getIsDeleted) async {
final categoryList = await Category().select().toList();
print('LISTING CATEGORIES -> Category().select().toList()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${categoryList.length} matches found:');
for (int i = 0; i < categoryList.length; i++) {
print(categoryList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
}
class BundledDbModel extends SqfEntityModelProvider {}
Future<String> createModelFromDatabaseSample() async {
/* STEP 1
// Copy your database in /assets folder (in this sample we copied chinook.sqlite database)
// and define your asset database in pubspec.yaml as below
flutter:
assets:
- assets/chinook.sqlite
*/
// STEP 2
// Run this script with this parameters.
// databaseName: Specify a name for your database to use for the database connection
// bundledDatabasePath: File path of your copied database
final bundledDbModel = await convertDatabaseToModelBase(BundledDbModel()
..databaseName = 'chinook.db'
..bundledDatabasePath = 'assets/chinook.sqlite');
// STEP 3
// Run this function to convert the model to annotation
final String modelConstString =
SqfEntityConverter(bundledDbModel).createConstDatabase();
// That's all. Set clipboard to paste codes
await Clipboard.setData(ClipboardData(text: modelConstString));
/*
Model were created succesfuly and set to the Clipboard.
STEP 1:
Open model.dart file in lib/model folder and paste models after following line
part 'model.g.dart';
STEP 2:
Go Terminal Window and run command below
flutter pub run build_runner build --delete-conflicting-outputs
Your Entity models will be created in lib/model/model.g.dart
*/
print('''Your ${bundledDbModel.databaseName}
were created succesfuly and set to the Clipboard.
STEP 1:
Open model.dart file in lib/model folder and paste models after following line
part 'model.g.dart';
STEP 2:
Go Terminal Window and run command below
flutter pub run build_runner build --delete-conflicting-outputs
Your Entity models will be created in lib/model/model.g.dart''');
return modelConstString;
}
Future<String> createSqfEntityModelString() async {
// To get the class from the clipboard, run it separately for each object
// Create Entity Model String of model from file at '/lib/model/model.dart'
// and set the Clipboard (After debugging, press Ctrl+V to paste the model from the Clipboard)
final model = SqfEntityModelConverter(myDbModel).toModelBase();
final strModel = StringBuffer()
..writeln('''import 'dart:convert';
import 'dart:ui';
import 'package:flutter/material.dart';
import 'package:http/http.dart' as http;
import 'package:sqfentity/sqfentity.dart';
import 'package:sqfentity_base/sqfentity_base.dart';''')
..writeln(SqfEntityConverter(model).createModelDatabase())
..writeln(SqfEntityConverter(model).createEntites());
await Clipboard.setData(ClipboardData(text: strModel.toString()));
return strModel.toString();
// also you can get Model String from TextField in App (on the Emulator only!)
/* also you can generate model.g.dart as following:
--> Go Terminal Window and run command below
flutter pub run build_runner build --delete-conflicting-outputs
Note: After running the command Please check lib/model/model.g.dart
*/
// Notice: Keyboard shortcuts are not working on the emulator.
// To copy for your model, click on the cursor in the TextField than open tooltip menu in the emulator.
// When the menu opens, you can click 'SELECT ALL' and then click 'COPY'.
}
Future<void> printProducts() async {
final productList = await Product().select().toList();
print(
'EXAMPLE 1.1: SELECT ALL ROWS WITHOUT FILTER ex: SELECT * FROM PRODUCTS \n -> Product().select().toList()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
}
Future<void> samples1() async {
// EXAMPLE 1.1: SELECT * FROM PRODUCTS
await printProducts();
// EXAMPLE 1.2: ORDER BY FIELDS -> ex: SELECT * FROM PRODUCTS ORDER BY name, price DESC, id
var productList = await Product()
.select()
.orderBy('name')
.orderByDesc('price')
.orderBy('productId')
.toList();
print(
'EXAMPLE 1.2: ORDER BY FIELDS ex: SELECT * FROM PRODUCTS ORDER BY name, price DESC, id \n-> Product().select().orderBy(\'name\').orderByDesc(\'price\').orderBy(\'productId\').toList()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 1.3: SELECT SPECIFIC FIELDS -> ex: SELECT name,price FROM PRODUCTS ORDER BY price DESC
print(
'EXAMPLE 1.3: SELECT SPECIFIC FIELDS ex: SELECT name,price FROM PRODUCTS ORDER BY price DESC \n-> Product().select(columnsToSelect: [\'name\',\'price\']).orderByDesc(\'price\').toList()');
productList = await Product()
.select(columnsToSelect: ['name', 'price'])
.orderByDesc('price')
.toList();
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------');
}
Future<void> samples2() async {
// EXAMPLE 1.4: SELECT * FROM PRODUCTS WHERE isActive=1
var productList = await Product().select().isActive.equals(true).toList();
print(
'EXAMPLE 1.4: EQUALS ex: SELECT * FROM PRODUCTS WHERE isActive=1 \n-> Product().select().isActive.equals(true).toList()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 1.5: SELECT * FROM PRODUCTS WHERE ID IN (3,6,9)
productList = await Product().select().productId.inValues([3, 6, 9]).toList();
print(
'EXAMPLE 1.5: WHERE field IN (VALUES) ex: SELECT * FROM PRODUCTS WHERE ID IN (3,6,9) \n -> Product().select().productId.inValues([3,6,9]).toList()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
// Brackets in query, Contains, Endswith, Startswith SAMPLES
// EXAMPLE 1.6: SELECT TOP 1 * FROM PRODUCTS WHERE price>10000 AND (description LIKE '%256%' OR description LIKE '512%')
final singleProduct = await Product()
.select()
.price
.greaterThan(10000)
.and
.startBlock
.description
.contains('256')
.or
.description
.startsWith('512')
.endBlock
.toSingle();
print(
'EXAMPLE 1.6: BRACKETS ex: SELECT TOP 1 * FROM PRODUCTS WHERE price>10000 AND (description LIKE \'%256%\' OR description LIKE \'512%\') \n -> Product().select().price.greaterThan(10000).and.startBlock.description.contains(\'256\').or.description.startsWith(\'512").endBlock.toSingle((product){ // TO DO })');
print('Toplam ${(singleProduct != null ? '1' : '0')} sonuç listeleniyor:');
if (singleProduct != null) {
print(singleProduct.toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 1.7: SELECT name,price FROM PRODUCTS WHERE price <=10000 AND (description LIKE '%128%' OR description LIKE '%GB')
productList = await Product()
.select()
.price
.lessThanOrEquals(10000)
.and
.startBlock
.description
.contains('128')
.or
.description
.endsWith('GB')
.or
.description
.startsWith('128')
.endBlock
.toList();
print(
'EXAMPLE 1.7: BRACKETS 2 ex: SELECT name,price FROM PRODUCTS WHERE price <=10000 AND (description LIKE \'%128%\' OR description LIKE \'%GB\') \n -> Product().select(columnsToSelect:[\'name\',\'price\']).price.lessThanOrEquals(10000).and.startBlock.description.contains(\'128\').or.description.endsWith(\'GB\').endBlock.toList();');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 1.8: NOT EQUALS
productList = await Product().select().productId.not.equals(11).toList();
print(
'EXAMPLE 1.8: NOT EQUALS ex: SELECT * FROM PRODUCTS WHERE ID <> 11 \n -> Product().select().productId.not.equals(11).toList();');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 1.9: GREATERTHEN OR EQUALS, LESSTHAN OR EQUALS
productList = await Product()
.select()
.price
.greaterThanOrEquals(10000)
.and
.price
.lessThanOrEquals(13000)
.toList();
print(
'EXAMPLE 1.9: GREATERTHEN OR EQUALS, LESSTHAN OR EQUALS ex: SELECT * FROM PRODUCTS WHERE price>=10000 AND price<=13000 \n -> Product().select().price.greaterThanOrEquals(10000).and.price.lessThanOrEquals(13000).toList();');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 1.10: BETWEEN KEYWORD
productList = await Product()
.select()
.price
.between(8000, 14000)
.orderBy('price')
.toList();
print(
'EXAMPLE 1.10: BETWEEN ex: SELECT * FROM PRODUCTS WHERE price BETWEEN 8000 AND 14000 \n -> Product().select().price.between(8000,14000).orderBy(\'price\').toList();');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 1.11: 'NOT' KEYWORD
productList = await Product().select().productId.not.greaterThan(5).toList();
print(
'EXAMPLE 1.11: \'NOT\' KEYWORD ex: SELECT * FROM PRODUCTS WHERE NOT id>5 \n -> Product().select().productId.not.greaterThan(5).toList();');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 1.12: WRITING CUSTOM FILTER IN WHERE CLAUSE
productList = await Product()
.select()
.where('productId IN (3,6,9) OR price>8000')
.toList();
print(
'EXAMPLE 1.12: WRITING CUSTOM FILTER IN WHERE CLAUSE ex: SELECT * FROM PRODUCTS WHERE id IN (3,6,9) OR price>8000 \n -> Product().select().where(\'id IN (3,6,9) OR price>8000\').toList()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 1.13: Build filter and query from values from the form
// assume that the values come from the form by defining several variables:
int minPrice;
int maxPrice;
String nameContains;
String descriptionContains;
// setting values
minPrice =
8000; // if minPrice is null then -> The between method runs LessThanOrEquals Method
maxPrice =
10000; // if maxPrice is null then -> The between method runs GreaterThanOrEquals Method
nameContains =
'13'; // if all of the values any method's is null then -> this method will be extracted
descriptionContains = 'SSD';
productList = await Product()
.select()
.price
.between(minPrice, maxPrice)
.and
.name
.contains(nameContains)
.and
.description
.contains(descriptionContains)
.toList();
print(
'EXAMPLE 1.13: Product().select().price.between($minPrice, $maxPrice).and.name.contains(\'$nameContains\').and.description.contains(\'$descriptionContains\').toList()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (var prod in productList) {
print(prod.toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 1.14: Select products with deleted items (only softdelete was activated on Model)
productList = await Product().select(getIsDeleted: true).toList();
print(
'EXAMPLE 1.14: EXAMPLE 1.13: Select products with deleted items\n -> Product().select(getIsDeleted: true).toList()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (var prod in productList) {
print(prod.toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 1.15: Select products only deleted items (only softdelete was activated on Model)
productList = await Product()
.select(getIsDeleted: true)
.isDeleted
.equals(true)
.toList();
print(
'EXAMPLE 1.15: Select products only deleted items \n -> Product().select(getIsDeleted: true).isDeleted.equals(true).toList()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (var prod in productList) {
print(prod.toMap());
}
print('---------------------------------------------------------------\n\n');
}
Future<void> samples3() async {
// EXAMPLE 3.1: SELECT TOP 3 * FROM PRODUCTS ORDER BY price DESC
var productList =
await Product().select().orderByDesc('price').top(3).toList();
print(
'EXAMPLE 3.1: LIMITATION ex: SELECT TOP 3 * FROM PRODUCTS ORDER BY price DESC \n -> Product().select().orderByDesc(\'price\').top(3).toList()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 3.2: SAMPLE PAGING -> PRODUCTS in 3. page (5 items per page)
productList = await Product().select().page(3, 5).toList();
print(
'EXAMPLE 3.2: SAMPLE PAGING ex: PRODUCTS in 3. page (5 items per page) \n -> Product().select().page(3,5).toList()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
}
Future<void> samples4() async {
// EXAMPLE 4.1: SELECT DISTINCT name FROM PRODUCTS WHERE price > 3000
final productList = await Product()
.distinct(columnsToSelect: ['name'])
.price
.greaterThan(3000)
.toList();
print(
'EXAMPLE 4.1: DISTINCT ex: SELECT DISTINCT name FROM PRODUCTS WHERE price > 3000 \n -> Product().distinct(columnsToSelect:[\'name\').price.greaterThan(3000).toList();');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 4.2: GROUP BY with SCALAR OR AGGREGATE FUNCTIONS ex: SELECT COUNT(id) AS Count, MIN(price) AS minPrice, MAX(price) AS maxPrice, AVG(price) AS avgPrice FROM PRODUCTS GROUP BY name
// /* count(),avg(),max(),min() when empty returns columnname as default, count('aliasname') is returns alias columnname */
final objectList = await Product()
.select(columnsToSelect: [
ProductFields.name.toString(),
ProductFields.productId.count('Count'),
ProductFields.price.min('minPrice'),
ProductFields.price.max('maxPrice'),
ProductFields.price.avg('avgPrice'),
ProductFields.price.sum('sumPrice'),
])
.groupBy(ProductFields.name
.toString() /*also you can use this .groupBy('name')*/)
.toListObject();
print(
'EXAMPLE 4.2: GROUP BY WITH SCALAR OR AGGREGATE FUNCTIONS ex: SELECT name, COUNT(id) AS Count, MIN(price) AS minPrice, MAX(price) AS maxPrice, AVG(price) AS avgPrice,ProductFields.price.sum(\'sumPrice\') FROM PRODUCTS GROUP BY name \n-> Product().select(columnsToSelect: [ProductFields.name.toString(), ProductFields.id.count(\'Count\'), ProductFields.price.min(\'minPrice\'), ProductFields.price.max(\'maxPrice\'), ProductFields.price.avg(\'avgPrice\')).groupBy(ProductFields.name.toString()).toListObject()');
// PRINT RESULTS TO DEBUG CONSOLE
print('${objectList.length} matches found:');
for (int i = 0; i < objectList.length; i++) {
print(objectList[i].toString());
}
print('---------------------------------------------------------------');
}
Future<void> samples5() async {
// EXAMPLE 5.1: Update multiple records with query
var result = await Product()
.select()
.productId
.greaterThan(10)
.update({'isActive': 0});
print(
'EXAMPLE 5.1: Update multiple records with query \n -> Product().select().productId.greaterThan(10).update({\'isActive\': 0});');
print(result.toString());
print('---------------------------------------------------------------\n\n');
// UPDATE imageUrl field by CategoryId
await Product().select().categoryId.equals(1).update({
'imageUrl':
'https://raw.githubusercontent.com/hhtokpinar/sqfEntity/master/assets/notebook.png'
});
await Product().select().categoryId.equals(2).update({
'imageUrl':
'https://raw.githubusercontent.com/hhtokpinar/sqfEntity/master/assets/ultrabook.png'
});
// EXAMPLE 5.2: Update multiple records with query
result = await Product()
.select()
.productId
.lessThanOrEquals(10)
.update({'isActive': 1});
print(
'EXAMPLE 5.2: uUpdate multiple records with query \n -> Product().select().productId.lessThanOrEquals(10).update({\'isActive\': 1});');
print(result.toString());
print('---------------------------------------------------------------\n\n');
// EXAMPLE 5.3: select product by id and update
final product2 = await Product().getById(15);
// TO DO
// update product object if exist
if (product2 != null) {
product2.description = '512GB SSD i7 (updated)';
await product2.save();
print(
'EXAMPLE 5.3: id=15 Product item updated: ${product2.toMap().toString()}');
} else {
print('EXAMPLE 5.3: id=15 => product not found');
}
print('---------------------------------------------------------------\n\n');
// EXAMPLE 5.4: update some filtered products with saveAll method -> Product().saveAll(productList){});
var productList = await Product().select().price.lessThan(1000).toList();
double i = 0;
for (var product in productList) {
i = i + 10;
product.price = i;
}
final results = await Product().saveAll(productList);
productList = await Product().select().toList();
print(
'EXAMPLE 5.4: update some filtered products with saveAll method \n -> Product().saveAll(productList){});');
print(' List<BoolResult> result of saveAll method is following:');
for (var result in results) {
print(result.toString());
}
print('---------------------------------------------------------------');
print(
'EXAMPLE 5.4: listing saved products (set price=i) with saveAll method;');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------');
}
Future<void> samples6() async {
// EXAMPLE 6.1 : Delete all products.
// Uncomment following section for delete all products
/*
Product().select().delete().then((result) {
if (result.success)
print('${result.successMessage}');
else
print('${result.errorMessage}');
});
*/
// EXAMPLE 6.2: get product with query id and delete
var result = await Product().select().productId.equals(16).delete();
print(
'EXAMPLE 6.2: delete product by query filder \n -> Product().select().productId.equals(16).delete();');
print(result.toString());
print('---------------------------------------------------------------\n\n');
// EXAMPLE 6.3: Get product by id and then delete
final product = await Product().getById(17);
// TO DO
// delete product object if exist
if (product != null) {
result = await product.delete();
print(
'EXAMPLE 6.3: delete product if exist \n -> if (product != null) Product.delete();');
if (result.success) {
print('${result.successMessage}');
} else {
print('${result.errorMessage}');
}
print(
'---------------------------------------------------------------\n\n');
} else {
print('id=15 => product not found');
print(
'---------------------------------------------------------------\n\n');
}
// EXAMPLE 6.4: Delete many products by filter
result = await Product().select().productId.greaterThan(17).delete();
print(
'EXAMPLE 6.4: Delete many products by filter \n -> Product().select().productId.greaterThan(17).delete()');
if (result.success) {
print('${result.successMessage}');
} else {
print('${result.errorMessage}');
}
print('---------------------------------------------------------------\n\n');
/*/ EXAMPLE 6.5: Get product by id and then recover
Product().getById(17, (product) {
// TO DO
// delete product object if exist
if (product != null) {
product.delete().then((result) {
print(
'EXAMPLE 6.5: recover product if exist \n -> if (product != null) Product.recover();');
if (result.success)
print('${result.successMessage}');
else
print('${result.errorMessage}');
print(
'---------------------------------------------------------------\n\n');
});
} else {
print('id=15 => product not found');
print(
'---------------------------------------------------------------\n\n');
}
return;
});
*/
// EXAMPLE 6.6: Recover many products by filter
result = await Product().select().productId.greaterThan(17).recover();
print(
'EXAMPLE 6.6: Recover many products by filter \n -> Product().select().productId.greaterThan(17).recover()');
if (result.success) {
print('${result.successMessage}');
} else {
print('${result.errorMessage}');
}
print('---------------------------------------------------------------\n\n');
}
Future<void> samples7() async {
// EXAMPLE 7.1: goto Category Object from Product \n-> Product.category((_category) {});
final product = await Product().getById(3);
if (product != null) {
final category = await product.getCategory();
print(
'EXAMPLE 7.1: goto Category Object from Product \n-> Product.getCategory(); ');
print(
'The category of \'${product.name}\' is: ${category == null ? 'null' : category.toMap()}');
}
// EXAMPLE 7.2: list Products of Categories \n-> Product.category((_category) {});
final categoryList = await Category().select().toList();
for (var category in categoryList) {
final productList = await category.getProducts().toList();
print(
'EXAMPLE 7.2.${category.categoryId}: Products of \'${category.name}\' listing \n-> category.getProducts((productList) {}); ');
// PRINT RESULTS TO DEBUG CONSOLE
print('${productList.length} matches found:');
for (int i = 0; i < productList.length; i++) {
print(productList[i].toMap());
}
print('---------------------------------------------------------------');
return;
}
return;
}
Future<void> samples8() async {
List<Todo> todosList = await Todo.fromWeb();
if (todosList != null) {
await Todo().upsertAll(todosList);
todosList = await Todo().select().top(10).toList();
print(
'EXAMPLE 8.1: Fill List from web (JSON data) and upsertAll \n -> Todo.fromWeb((todosList) {}');
print('${todosList.length.toString()} matches found\n');
for (var todo in todosList) {
print(todo.toMap());
}
print(
'---------------------------------------------------------------\n\n');
}
todosList =
await Todo.fromWebUrl('https://jsonplaceholder.typicode.com/todos');
if (todosList != null) {
final results = await Todo().upsertAll(todosList);
print(
'EXAMPLE 8.2: upsertAll result \n -> final results = await Todo().upsertAll(todosList);');
// print upsert Results
for (var res in results.commitResult) {
res = res; // dummy line for analysis_options (unused_local_variable)
//print(res.toString()); // uncomment this line for print save results
}
}
todosList = await Todo().select().top(10).toList();
print(
'EXAMPLE 8.2: Fill List from web with Url (JSON data) and upsertAll \n -> Todo.fromWebUrl(\'https://jsonplaceholder.typicode.com/todos\', (todosList) {}');
print('${todosList.length.toString()} matches found\n');
for (var todo in todosList) {
print(todo.toMap());
}
print('---------------------------------------------------------------\n\n');
}
Future<void> samples9() async {
// EX.9.1 Execute custom SQL command on database
final sql_91 = 'UPDATE product set isActive=1 where isActive=1';
final result_91 = await MyDbModel().execSQL(sql_91);
print(
'EX.9.1 Execute custom SQL command on database\n -> final sql=\'$sql_91\';\n -> MyDbModel().execSQL(sql) \n -> print result = ${result_91.toString()}');
// EX.9.2 Execute custom SQL command List on database
final sqlList = <String>[]
..add('UPDATE product set isActive=1 where isActive=1')
..add('UPDATE product set isActive=0 where isActive=0');
final result_92 = await MyDbModel().execSQLList(sqlList);
print(
'EX.9.2 Execute custom SQL command List on database\n -> final sqlList=List<String>();\n -> MyDbModel().execSQLList(sqlList); \n -> print result = ${result_92.toString()}');
// EX.9.3 Execute custom SQL Query and get datatable -> returns List<Map<String,dynamic>>
final sql_93 = 'SELECT name, price FROM product order by price desc LIMIT 5';
final result_93 = await MyDbModel().execDataTable(sql_93);
print(
'EX.9.3 Execute custom SQL Query and get datatable -> returns List<Map<String,dynamic>> \n -> MyDbModel().execDataTable(\'$sql_93\');\n -> print result:');
for (var item in result_93) {
print(item.toString());
}
/// EX.9.4 Execute custom SQL Query and get first col of first row
final sql_94 = 'SELECT name FROM product order by price desc';
final result_94 = await MyDbModel().execScalar(sql_94);
print(
'EX.9.4 Execute custom SQL Query and get first col of first row -> returns dynamic \n -> MyDbModel().execScalar(\'$sql_94\');\n -> print result:');
print(result_94.toString());
}
Future<void> samples10() async {
print('EXAMPLE 10 SqfEntity Sequence SAMPLES-----------');
final int currentVal = await IdentitySequence().currentVal();
final int nextVal = await IdentitySequence().nextVal();
final int nextVal2 = await IdentitySequence().nextVal();
final int currentVal2 = await IdentitySequence().currentVal();
print('Sample Code:\n');
print('''
final currentVal= await IdentitySequence().currentVal();
result: currentVal = $currentVal
final nextVal = await IdentitySequence().nextVal();
result: nextVal = $nextVal
final int nextVal2 = await IdentitySequence().nextVal();
result: nextVal2 = $nextVal2
final int currentVal2 = await IdentitySequence().currentVal();
result: currentVal2 = $currentVal2
''');
}
/// toJson samples
Future<void> samples11() async {
// EXAMPLE 11.1 single object to Json
final product = await Product().select().toSingle();
if (product == null) {
return;
}
final jsonString = product.toJson();
print(
'EXAMPLE 11.1 single object to Json\n product jsonString is: $jsonString');
//EXAMPLE 11.2 object list with nested objects to Json
final jsonStringWithChilds =
await Category().select().toJsonWithChilds(); // all categories selected
print(
'EXAMPLE 11.2 object list with nested objects to Json\n categories jsonStringWithChilds is: $jsonStringWithChilds');
}
/// add new categories if not any Category
Future<void> addSomeProducts() async {
await addCategories();
// add new products if not any Product..
final product = await Product().select().toSingle();
if (product == null) {
await addProducts();
} else {
print(
'There is already products in the database.. addProduct will not run');
}
return;
}
Future<void> addCategories() async {
final category = await Category().select().toSingle();
if (category == null) {
await Category(name: 'Notebooks', isActive: true).save();
await Category(name: 'Ultrabooks', isActive: true).save();
} else {
print(
'There is already categories in the database.. addCategories will not run');
}
}
Future<bool> addProducts() async {
final productList = await Product().select(getIsDeleted: true).toList();
if (productList.length < 15) {
// some dummy rows for select (id:1- to 15)
await Product(
name: 'Notebook 12"',
description: '128 GB SSD i7',
price: 6899,
categoryId: 1)
.save();
await Product(
name: 'Notebook 12"',
description: '256 GB SSD i7',
price: 8244,
categoryId: 1)
.save();
await Product(
name: 'Notebook 12"',
description: '512 GB SSD i7',
price: 9214,
categoryId: 1)
.save();
await Product(
name: 'Notebook 13"',
description: '128 GB SSD',
price: 8500,
categoryId: 1)
.save();
await Product(
name: 'Notebook 13"',
description: '256 GB SSD',
price: 9900,
categoryId: 1)
.save();
await Product(
name: 'Notebook 13"',
description: '512 GB SSD',
price: 11000,
categoryId: 1)
.save();
await Product(
name: 'Notebook 15"',
description: '128 GB SSD',
price: 8999,
categoryId: 1)
.save();
await Product(
name: 'Notebook 15"',
description: '256 GB SSD',
price: 10499,
categoryId: 1)
.save();
await Product(
name: 'Notebook 15"',
description: '512 GB SSD',
price: 11999,
categoryId: 1)
.save();
await Product(
name: 'Ultrabook 13"',
description: '128 GB SSD i5',
price: 9954,
categoryId: 2)
.save();
await Product(
name: 'Ultrabook 13"',
description: '256 GB SSD i5',
price: 11154,
categoryId: 2)
.save();
await Product(
name: 'Ultrabook 13"',
description: '512 GB SSD i5',
price: 13000,
categoryId: 2)
.save();
await Product(
name: 'Ultrabook 15"',
description: '128 GB SSD i7',
price: 11000,
categoryId: 2)
.save();
await Product(
name: 'Ultrabook 15"',
description: '256 GB SSD i7',
price: 12000,
categoryId: 2)
.save();
await Product(
name: 'Ultrabook 15"',
description: '512 GB SSD i7',
price: 14000,
categoryId: 2)
.save();
print('added 15 new products');
// add a few dummy products for delete (id:from 16 to 20)
await Product(name: 'Product 1').save();
await Product(name: 'Product 2').save();
await Product(name: 'Product 3').save();
await Product(name: 'Product 4').save();
await Product(name: 'Product 5').save();
print('added 5 dummy products');
}
return true;
}
void sampleModelConvert() {
final seq = SqfEntitySequenceBase()
..sequenceName = 'sample'
..init();
final model = MyDb()
..databaseName = 'sampleORM.db'
..sequences = [seq]
..databaseTables = [
SqfEntityTableBase()
..dbModel = 'myyymodelll'
..defaultJsonUrl = 'none'
..fields = [
SqfEntityFieldBase('name', DbType.text)..defaultValue = 'test',
SqfEntityFieldBase('identity', DbType.text, sequencedBy: seq)
..defaultValue = 'test'
]
..primaryKeyName = 'id'
..primaryKeyType = PrimaryKeyType.integer_auto_incremental
..tableName = 'category'
..useSoftDeleting = true
..init()
];
final modelStr = SqfEntityConverter(model).createEntites();
print(modelStr);
}
class MyDb extends SqfEntityModelBase {}Metadata
270
likes
0
pub points
90%
popularity
Publisher
Metadata
SqfEntity ORM for Flutter/Dart lets you build and execute SQL commands easily and quickly with the help of fluent methods similar to .Net Entity Framework.
Repository (GitHub)
View/report issues
License
unknown (LICENSE)
Dependencies
flutter, path, sqfentity_gen, sqflite_common, sqflite_common_ffi, sqflite_sqlcipher, synchronized
