Flutter - sqflite: How to change the order (index) of the persisted data?

Question

I have searched for this question everywhere. Maybe I didn’t use the correct search terms. If it’s been asked already, my apologies. I’ll quickly delete this if it turns out to be a duplicate.

I’m a rather inexperienced coder, so this code will definitely not be the best looking one out there. Hope it’s understandable though.

The Question:

• I have a list of custom ListTiles and its data is persisted via the sqflite package.
• Everything you do to it (change info on it) is already being persisted
• The problem is, I have implemented the reorderables 0.3.2 package so that the list could be reorderable via drag and drop
• How can I make the order changes apply to the database? How can I change the order of the bar tiles in the database?
• The way it works right now, you add a bar and it goes to the DB. That’s the order forever it never changes.
• How can I make the index changes from dragging and dropping a bar to a different position/index apply to my sqflite database?

I will leave the code from where the list is rendered, the fetchAndSetBars method, as well as the DB methods for creating and updating the DB. If I’m missing any info that anyone might need let me know. I will put it up here as soon as possible

Image of the list ordered by DB

Image of altered list order. If you restart app it goes back to image 1

This is the code for the list that is the problem:

import 'package:flutter/material.dart';
import 'package:provider/provider.dart';
import 'package:budget_mentor/models/bar_tile_data.dart';
import 'package:reorderables/reorderables.dart';

class BarTilesList extends StatelessWidget {
 @override
 Widget build(BuildContext context) {
   return FutureBuilder(
     future:
         Provider.of<BarTileData>(context, listen: false).fetchAndSetBars(),
     builder: (ctx, snapshot) => snapshot.connectionState ==
             ConnectionState.waiting
         ? Center(
             child: CircularProgressIndicator(),
           )
         : Consumer<BarTileData>(
             builder: (context, barTileData, child) {
               ScrollController _scrollController =
                   PrimaryScrollController.of(context) ?? ScrollController();

               void _onReorder(int oldIndex, int newIndex) {
                 Widget row = barTileData.barTiles.removeAt(oldIndex);
                 barTileData.barTiles.insert(newIndex, row);
               }

               return Column(
                 children: <Widget>[
                   Expanded(
                     child: CustomScrollView(
                       controller: _scrollController,
                       slivers: <Widget>[
                         ReorderableSliverList(
                           delegate: ReorderableSliverChildBuilderDelegate(
                               (BuildContext context, int index) =>
                                   barTileData.barTiles[index],
                               childCount: barTileData.barTiles.length),
                           onReorder: _onReorder,
                         )
                       ],
                     ),
                   ),
                   Text('Placeholder'),
                 ],
               );
             },
           ),
   );
 }
}

FetchAndSetBars method:

Future<void> fetchAndSetBars() async {
 final dataList = await DBHelper.getBarTileDBData('user_bars');
 barTiles = dataList.map((bar) {
   final int colorHex = int.tryParse(bar['barColor']);
   final String valueKey = bar['barKey'];
   return BarTile(
     barTitle: bar['barTitle'],
     barColor: Color(colorHex),
     barWidth: bar['barWidth'],
     id: bar['barID'],
     key: ValueKey(valueKey),
   );
 }).toList();

 notifyListeners();
}

DB Creation Code:

    static Future<Database> barTilesDatabase() async {
 final dbPath = await sql.getDatabasesPath();
 return sql.openDatabase(path.join(dbPath, 'bars_tile_list.db'),
     onCreate: (db, version) {
   return db.execute(
       'CREATE TABLE user_bars($barTitle TEXT PRIMARY KEY, $barColor TEXT, $barWidth REAL, $barID TEXT, $barKey TEXT)');
 }, version: 5);
}

(Codes for insert and delete are standard) I‘m guessing the only other relevant DB piece of code to add would be my updateDB method:

static updateUserBarsDB(
   String barTitle,
   Color barColor,
   double barWidth,
   String barID,
   String barKey) async {

 Database db = await DBHelper.barTilesDatabase();
 final String colorValueAsString = barColor.value.toString();

 Map<String, dynamic> updatedBarTile = {
   DBHelper.barTitle: barTitle,
   DBHelper.barColor: colorValueAsString,
   DBHelper.barWidth: barWidth,
   DBHelper.barID: barID,
   DBHelper.barKey: barKey,
 };

 String id = barID;
 await db.update('user_bars', updatedBarTile,
     where: '${DBHelper.barID} = ?', whereArgs: [id]);
 print(await db.query('user_bars'));
}

Solution

Okay, I am trying to build the same thing in my application, and here is a suggestion on how to handle this. I am not a database expert so can't really say if this is cost-efficient or optimized in terms of DB operations.

Let's suppose you have the following elements in a list [0, 1, 2, 3]. I have chosen these numbers so that they are indicative of their indices.

Now consider the following operations.

1. Moving the first element from start to the last position. After performing this operation, the list will become this -> [1, 2, 3, 0]. From this, we can understand that when an element is moved from a lesser position value to a greater position value (0 -> 4), then we all the elements in between the two positions need to get a -1 in their position value. So position 1 becomes 0, 2 becomes 1, and so on and so forth.

2. Moving the last element from last to the start position. After performing this operation, the list will become this -> [3, 0, 1, 2]. From this, we can understand that when an element is moved from a greater position value to a lesser position value (3 -> 0), we need to perform the reverse of the above operation and we add plus 1 to all the positions that are present in between the two indices. So when the 4th element(3) becomes 1st element(0), all elements above the 4th element and below the 1st position (including the 1st position) need to increment their positions by one.

---

Coming to the SQL part of the problem. You can have an index or list_postion column in your table which corresponds to the position of the element. when the reordering operation takes place, you can update the elements inside the database with the index column.

Read the Stream of the tasks using an order-by operation from the table based on their index value in ascending order. An example query for this will be

SELECT * FROM tasks_table ORDER BY list_index ASC

Hope this helps out, and I am not sure of the performance over large lists for this approach and I am currently looking at some other ways and algorithms for this doing this in a faster way. But for now, it works for me.