SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    239
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Need help with creating a portable application with database.

    I'm planning a portable logbook for employees to replace the current unstable Filemaker Pro one but I've got a few problems from the start.

    I don't know which database system to use.

    The project has to meet specific criteria: It is a logbook that stores information in a database and which can be used to produce reports detailing each employee's activity.

    It must also be:

    1. Portable on a flash disk.
    2. Easy to set up. Unzipping into a folder and being ready to go would be ideal.
    3. Possible to create a backup tool that saves backups of the database from within the application. The frequency of the backups should be controllable and restoring should be easy.
    4. Be able to import and export .csv files for integration/migration to and from the existing application.

    I do JavaScript, PHP, mySQL and general relational database design. I did know some ASP and VB some years ago. (I'm keen on CSS too, but that's icing).

    Options I've considered:

    a. MS Access. This would be easy to use since any machine with Access could be used and I could create the database, forms and reporting tools in one package. Cons are it is unstable, dependant on Access version on the machine in question and I don't know how to program it at the moment (I can learn). Even creating linked combo-boxes is hard compared to how easy it is in JavaScript/HTML.

    b. Portable Apache + mySQL and PHP/Javascript pages. This looks like it has a tough learning curve for me and it doesn't look like it will be easy for the users to setup and use.

    c. XAMPP and PHP/Javascript pages. You still have the setup difficulties for non-techy users and you have to start and stop services. This will be too much of a headache for the users - they really need something which is double-click and go.

    d. Online. Users would need net access, which they don't always have. This also means that the host (me) would have responsibility for the data held on my server. The users having responsibility for their own data is far preferable.


    I'm stumped at the moment. Access seems the easiest option atm but the instability of it is a big problem. Even small projects I've done recently have been prone to crashes (using Access 2007).

    Has anyone any ideas?

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Take a look at sqlite.
    http://www.sqlite.org

    It's a full database engine, pretty small in resources that works on a binary file for the storage.
    I know it's used in a lot of application for handheld devices (firefox 3 has started using it for the bookmarks and history).

    It has binding to php, c, c++, c#, vb, python, java, ruby and surely many more...

    If I where you, I would be looking in python + wxPython for the interface.
    It's easily maintainable, simple to develop, and not a big deal to install support on the client posts (python + wxpython install can be automated).
    It allows you to create a platform independent program too, which could be nice.

    I "migrated" from PHP to python in about 3 weeks, and started wxPython right after.
    I really found it easy and straightforward.
    You can see the result at www.webalis.com/gootrans

    For the user point of view, it's just a link to click to launch the application.
    No services, no maintenance...
    You could put the sources in a subversion or git repository, and have a batch updating the application automatically, as there are no compilation (well, yes, but python handles it auto-magically at run time)

  3. #3
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    239
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I really don't want to learn Python just for this. If I can do it, learn Python later and then migrate it that would be okay but I don't have the time or the inclination to learn a lot of new stuff at the moment.

    I don't need to learn it to use SQLite, do I? SQLite seems to be what I'm looking for but I need to read more about it. Thanks for the input.

  4. #4
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    XAMMP has a lot of batch files in root folder (setup_xampp.bat, apache_start.bat ...), so I think you can run them from autorun, can't you?

  5. #5
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    239
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Can I use it to create an app that can be run from a memstick with a single double-clickable icon though?

    The users need to be able to put the stick in, navigate to the right folder and double-click to start.

  6. #6
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SubEffect View Post
    I really don't want to learn Python just for this. If I can do it, learn Python later and then migrate it that would be okay but I don't have the time or the inclination to learn a lot of new stuff at the moment.
    As you wish, it's your call here.

    Quote Originally Posted by SubEffect View Post
    I don't need to learn it to use SQLite, do I? SQLite seems to be what I'm looking for but I need to read more about it. Thanks for the input.
    PHP5 have native binding to sqlite.
    http://www.php.net/manual/en/book.sqlite.php
    Depending of what path you follow, you might need to install connector to access the db, but it's all.
    For the rest, it's an database that implement the SQL language.
    You know hot to do select/insert/update/delete in mysql, then you know how to do them on any SQL database.
    There might be some tweak (ie, sqlite don't implement a date/time format, you will need to use a numeric value for it) but most of the stuff is straightforward.

    Quote Originally Posted by SubEffect View Post
    Can I use it to create an app that can be run from a memstick with a single double-clickable icon though?
    The users need to be able to put the stick in, navigate to the right folder and double-click to start.
    Of that, I'm less sure...
    You might sucessfuly start the web+db server when the key is inserted (with a noticeable timeout, to let everything get up, as a usb stick can be slow), but if the user take off the key without stopping the services, and I'm thinking about the DB files being not commited to disk, there is no way to know how this can end.
    Maybe ok, but maybe mysql will end up with corrupted tables, and need to manually repair them.
    Sqlite would be less subject for this if you close the db connection after each use, but not safe neither.

    Beside, don't forget that a flash memory have a limited number of write before the end of it's lifetime.
    Use something that frequently do write accesses, and you could toast an usb key in less than a year, and again loose a significant amount of datas.
    http://en.wikipedia.org/wiki/Flash_memory
    Memory wear

    Another limitation is that flash memory has a finite number of erase-write cycles. Most commercially available flash products are guaranteed to withstand around 100,000 write-erase-cycles, before the wear begins to deteriorate the integrity of the storage.[citation needed] The guaranteed cycle count may apply only to block zero (as is the case with TSOP NAND parts), or to all blocks (as in NOR). This effect is partially offset in some chip firmware or file system drivers by counting the writes and dynamically remapping blocks in order to spread write operations between sectors; this technique is called wear levelling. Another approach is to perform write verification and remapping to spare sectors in case of write failure, a technique called bad block management (BBM). For portable consumer devices, these wearout management techniques typically extend the life of the flash memory beyond the life of the device itself, and some data loss may be acceptable in these applications. For high reliability data storage, however, it is not advisable to use flash memory that has been through a large number of programming cycles. This limitation does not apply to 'read-only' applications such as thin clients and routers, which are only programmed once or at most a few times during their lifetime.

  7. #7
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Personally, I would run with MS Access for this one. If these folks have the licensing (ie--Office) it is a very, very cost-effective solution. Now, I do agree that programming Access can be quite painful. OTOH, one really need not do much programming unless one is getting very, very fancy. Stability-wise, it gets a bad rap because many organically grown solutions get miscast as departmental databases; if you are just hitting it locally then you should have no difficulties on that front. User experience-wise, desktop apps still beat the web hands down and there is alot less that can go wrong in the field than if you were relying upon a customized XAMPP stack.

    One other thing to think about is security and auditing--depending on what these logbooks are covering, it might make alot of sense to make this a server-only application and hand out mobile wireless cards.

  8. #8
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    239
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This doesn't need fancy requirements, no. And one advantage to Access is it can add new data into combo boxes without the need for a specific table.

    I'm using Access 2007 but most machines at work have Access 2003. I'm perfectly happy creating the application in compatibility mode .

    The plan is to have the following structure:

    Code:
    Logbook_folder
    -- Logbook_database.mdb
    -- Logbook_backups_folder
         -- Backup_A.mdb
         -- Backup_B.mdb
         -- Backup_etc.mdb
    Users can then just open the Logbook folder, double-click and go.

    I guess if I had a VB design platform I could actually create this as a stand alone application couldn't I? I seem to remember doing this years ago on an Information Systems course I did. That would be nice if I could do it that way.
    I don't own Visual Studio at the moment though.

    Edit: I just had a look and VB Express is free and can be used to create applications that interact with an Access database. I wish I didn't have to use MS stuff, but it's sadly looking like this is the way forwards for this project.

    If only I could do this with a portable mySQL database, PHP and JavaScript (I'd probably lean AJAX whilst I did it)
    Last edited by SubEffect; Jan 26, 2009 at 17:41. Reason: found VB Express


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •