1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Suggestion for a web stack for an app

Discussion in 'Programming' started by Mydev, Sep 11, 2022.

0
  1. #1
    Hi friends.
    I'm pretty new to web development and mainly developing in C/C++/Python some general-purpose programs.

    I have to implement a web application that displays data on a table.
    There is an `SQLite` database file in some pre-determined path in the backend, and I want to display some of its columns on the table.

    **I need this app to enable some features**:
    1. Filter the data according to a period (last week/last month/last year) - each record in the DB has an appropriate date value
    2. export the table into: `CSV`/`PDF`/`XSL`
    3. Enable sorting of columns
    4. Enable filtering of columns according to various parameters
    5. Enable pagination - show 10/50/100 rows
    6. The database file can be like 4GB+, so the processing has to be somewhat "fast"
    7. Search box (across the entire table's data)

    **My question**:
    What would be the best web technologies to use for this purpose?
    Which backend technology (if needed at all)? Which frontend technology? Which library?

    Thank you, friends.
     
    Mydev, Sep 11, 2022 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    If it's that simple I'd just create a plain HTML page, add jquery, add a datatable, populate it using a json feed, put in buttons. You call it a web app but it's really just a fancy way of displaying a single data set, right?

    upload_2022-9-12_12-58-51.png

    In this example, I have buttons that are part of the data table and trigger a fetch. The API does the heavy lifting, the front end only has 10, 25, 50, or 100 records loaded so it's fast. The data displayed is actually calling on 5 different tables but within a single database and always returning the same type of info. If you look closely you can see that some columns allow sorting, some don't. There's all sorts of fancy controls.

    ref:
     
    sarahk, Sep 11, 2022 IP
  3. Mydev

    Mydev Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    Thanks for the answer!

    What about filtering the data according to time (last week/month/year)?
    Also, what about the possibility of the DB is very large (like 4GB+)? I think I need some pagination that processes some of the data on-demand and not at once.
    And, which backend technology is preferred?
     
    Mydev, Sep 12, 2022 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #4 
    individuals/json/all/?draw=2&columns%5B0%5D%5Bdata%5D=0&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=1&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=2&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=3&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=false&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=4&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=false&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=5&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=false&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=s&search%5Bregex%5D=false&_=1662944285126
    Code (markup):
    Filtering is fine. The query sends down a ton of info and from that you build your query and return the json feed for just the records that will be displayed. When the user clicks next page then the request is sent again but the start and length get updated, you plug that into your query. If your database is optimised and you have the right indexes you should be able to query it very quickly.

    start=0&length=10
    Code (markup):
    Backend: You can use fairly vanilla PHP to do it, gets more complicated as you build in security etc but PHP will do the trick.
     
    sarahk, Sep 12, 2022 IP
  5. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #5
    By app you mean phone app, right?
    You cannot send a 4GB sqlite file with your app, and you also cannot let all apps connect to sqlite file on your server.

    Import the sqlite file to mysql.
    From your app, send an encrypted query to webserver which has mysql db.
    Some scriptting language, like PHP, or python etc can receive the encrypted query, decode it, and connect to db to get required data.
    The language returns a json or csv file with only small number of results.

    After that you can load the json file in the app, using what sarahk suggested above.
     
    JEET, Sep 13, 2022 IP
  6. orionace

    orionace Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #6
    your questions aren't quite clear.
    is it a web app or a mobile app?

    if it is a mobile app then I'd recommend you better get your hands dirty with some java or kotlin.

    but if it's a web app then you should use Django (and of course python) as it would be better in terms and security and provide fewer complications as Django's default DB is SQLite
     
    orionace, Sep 15, 2022 IP