gui for data entry

classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

gui for data entry

Graham Smith-4
There are several GUI editors for SQLite, but is there one that allows
creation of simple forms to allow data entry.  Although some allow
adding data to a single table, none that I have looked at seem to
allow a new record to be added when it involves more than one table.

It needs to be cross platform (Linux, Widows and Mac) and ideally
free, but a commercial product would still be considered.

I realise that the emphasis with SQLite is on it being the backend to
a database application, but I don't really need an application just
something a bit more feature rich than the GUIs I've looked at, to
replace Excel as a data storage tool.

Thanks,

Graham
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

oliver1804
Graham Smith <myotisone@...> writes:

>
> There are several GUI editors for SQLite, but is there one that allows
> creation of simple forms to allow data entry.  Although some allow
> adding data to a single table, none that I have looked at seem to
> allow a new record to be added when it involves more than one table.
>
> It needs to be cross platform (Linux, Widows and Mac) and ideally
> free, but a commercial product would still be considered.


[...]


OpenOffice Base

you need an ODBC driver too (http://www.ch-werner.de/sqliteodbc/)

Oliver

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

Graham Smith-4
Oliver

> OpenOffice Base
>
> you need an ODBC driver too (http://www.ch-werner.de/sqliteodbc/)

Thanks, I did consider this, but the last time I tried, it was very
clunky,  and crashed a lot, but I will have another look since you
have suggested it.

Graham
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

oliver1804
Graham Smith <myotisone@...> writes:

[...]
>
> Thanks, I did consider this, but the last time I tried, it was very
> clunky,  and crashed a lot, but I will have another look since you
> have suggested it.

[...]

works fine now but take the latest odbc driver from here:

http://www.ch-werner.de/sqliteodbc/sqliteodbc-088pre2.exe

because the 0.87 has a problem under special circumstances (makros) - something
you will use if you like to automate some functions in your forms or make them
more userfriendly

Oliver


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

Graham Smith-4
Oliver

> works fine now but take the latest odbc driver from here:
>
> http://www.ch-werner.de/sqliteodbc/sqliteodbc-088pre2.exe
>
> because the 0.87 has a problem under special circumstances (makros) - something
> you will use if you like to automate some functions in your forms or make them
> more userfriendly

Thanks again,

Graham
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

oliver1804
In reply to this post by oliver1804
ahh I forgot:

the best is to take OOo 330 m9

that is a milestone without all former illnesses - believe me I suffered a lot

D o n' t use 3.2.1!!!

Oliver

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

David Bicking-2
In reply to this post by Graham Smith-4
Take a look at Kexi. (kexi-project.org) It is part of the Koffice suite, but can be used standalone. I had tested it on Windows and had no trouble installing it.

It uses sqlite as its back end storage and allows you to create forms, etc. Its aim is to be "like" MS Access.

David


--- On Tue, 10/12/10, Graham Smith <[hidden email]> wrote:

> From: Graham Smith <[hidden email]>
> Subject: Re: [sqlite] gui for data entry
> To: "General Discussion of SQLite Database" <[hidden email]>
> Date: Tuesday, October 12, 2010, 12:09 PM
> Oliver
>
> > OpenOffice Base
> >
> > you need an ODBC driver too (http://www.ch-werner.de/sqliteodbc/)
>
> Thanks, I did consider this, but the last time I tried, it
> was very
> clunky,  and crashed a lot, but I will have another
> look since you
> have suggested it.
>
> Graham
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

Vivien Malerba
In reply to this post by Graham Smith-4
On 12 October 2010 17:51, Graham Smith <[hidden email]> wrote:
> There are several GUI editors for SQLite, but is there one that allows
> creation of simple forms to allow data entry.  Although some allow
> adding data to a single table, none that I have looked at seem to
> allow a new record to be added when it involves more than one table.
>
> It needs to be cross platform (Linux, Widows and Mac) and ideally
> free, but a commercial product would still be considered.
>

Libgda: http://www.gnome-db.org

Regards,
Vivien
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

Graham Smith-4
In reply to this post by oliver1804
> the best is to take OOo 330 m9
>
> that is a milestone without all former illnesses - believe me I suffered a lot
>
> D o n' t use 3.2.1!!!

Mmmm, still stuck on 3.2.1 with current release of Ubuntu (the OS I
use most of the time, followed by my Mac), so interesting to hear your
views on 3.2.1.

Graham
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

Graham Smith-4
In reply to this post by David Bicking-2
David

> Take a look at Kexi. (kexi-project.org) It is part of the Koffice suite, but can be used >standalone. I had tested it on Windows and had no trouble installing it.

I have also used this before (on Ubuntu) as well  as the OOo option, I
suppose I find the SQLite GUIs that much nimbler than the OOo or Kexi
options, that I was hoping for an alternative.

However, its looking like I will be looking at OOo and Kexi again,
which is fine, I was just looking for suggestions from people with
more experience.

Thanks,

Graham
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

oliver1804
In reply to this post by Graham Smith-4
Graham Smith <myotisone@...> writes:

[...]

> Mmmm, still stuck on 3.2.1 with current release of Ubuntu (the OS I
> use most of the time, followed by my Mac), so interesting to hear your
> views on 3.2.1.
>

I use OOo under WinXP and had a problem related to 3.2.1 in connection with
UNIQUE CONSTRAINTS

read this:
http://thread.gmane.org/gmane.comp.db.sqlite.general/57052/focus=57078
(no problem with this in 3.2.0)

and there were 1 or 2 other things that disappeared when I started using 330 m9
(connected with macros)

Oliver

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

Graham Smith-4
In reply to this post by Vivien Malerba
Vivien,

> Libgda: http://www.gnome-db.org

This is certainly a new one for me, I will give it a look.

Thanks,

Graham
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

Graham Smith-4
In reply to this post by oliver1804
Oliver

> I use OOo under WinXP and had a problem related to 3.2.1 in connection with
> UNIQUE CONSTRAINTS

Not sure I understand everything in the thread, but worth knowing as I learn.

Thanks,

Graham
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

Mihai Militaru-2
In reply to this post by Graham Smith-4
I use the Mozilla add-on, it's very handy and it gets updated.
https://addons.mozilla.org/en-US/firefox/addon/5817/

It currently uses SQLite version 3.6.22.

--
Mihai Militaru <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

Graham Smith-4
Mihai

> I use the Mozilla add-on, it's very handy and it gets updated.
> https://addons.mozilla.org/en-US/firefox/addon/5817/

That is what I am using, but unless I am missing something, it doesn't
let me create a form for data entry.

Thanks,

Graham
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

Mihai Militaru-2
In reply to this post by Mihai Militaru-2
On Wed, 13 Oct 2010 18:15:54 +0300
Mihai Militaru <[hidden email]> wrote:

> That is what I am using, but unless I am missing something, it doesn't
> let me create a form for data entry.

Please pardon my distraction, Graham, I didn't read your post
carefully. My recommendation was general-purpose.

But I got this idea: what if you create such "forms" yourself, using the
flexibility given by SQL? I don't know whether it would satisfy all
your requirements, but at least for duplication of data you can easily
use temporary "tool" tables with triggers, eg. you create a table using
the required fields (both named purposefully), and then triggers
attached to it can update different things on different target tables.
You export (or write manually) this easy setup to an SQL file and import
it every time you work, editing its content any time you need more
features. The manager appears to support user defined functions in a
language it doesn't specify and I don't recognize it (but I assume it's
Javascript) so the possibilities seem to be unlimited.

--
Mihai Militaru <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: gui for data entry

Graham Smith-4
Mihal

> But I got this idea: what if you create such "forms" yourself, using the
> flexibility given by SQL? I don't know whether it would satisfy all
> your requirements, but at least for duplication of data you can easily
> use temporary "tool" tables with triggers,

Thanks for this suggestion, at the moment this looks a bit beyond me,
but I will look at it in more detail.  From what I have now read, it
does seem a possibility.

Graham
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: GUI for data entry

BareFeetWare
In reply to this post by Mihai Militaru-2
On Wed, 13 Oct 2010 18:15:54 +0300
Mihai Militaru <[hidden email]> wrote:

> But I got this idea: what if you create such "forms" yourself, using the flexibility given by SQL? I don't know whether it would satisfy all your requirements, but at least for duplication of data you can easily use temporary "tool" tables with triggers, eg. you create a table using the required fields (both named purposefully), and then triggers attached to it can update different things on different target tables.


You don't need to create special tables for data entry. You can create views instead, coupled with "instead of" triggers.

This is one of the most undersold features of SQL and SQLite in general, I think.

When you design a database, you should properly structure and normalize it so that, for instance, there is no redundancy in data entry, a column that has an occasional entry is moved to its own table (rather than populating the main table with nulls), a multiple choice column contains an integer that links to a related table of values and so on. Dates should probably be entered as a julian day real (rather than text) and money should probably be entered as an integer (in cents) rather than a real, to avoid float calculation errors.

All that works well from a data integrity point of view. It's tight, uses minimal space and avoids redundancy and inaccuracies. But if you give such a properly normalized database in its raw form to a use for data entry (or even to browse data), it's horrible. They could go crazy trying to cross reference tables, mentally convert dates and money, switch to a separate table for occasional column info etc.

The answer is to create a view. This is pretty common place. In a view, you can join tables, show just some rows, show calculated totals, convert date reals to human readable text, integer amounts to currency etc.

But what few database designers seem to exploit is the fact that views can also be used to accept user input and convert that into meaningful entry into the underlying related tables. And, to answer the original poster's query, a view can

>> allow a new record to be added when it involves more than one table.


To make a view facilitate updating of the underlying tables, you need to add "instead of" triggers, which tell the database exactly what do do to tables instead of modifying the view.

I think this is a superior approach to handling all the data redirection in your application code or by creating temporary tables etc. Using views and triggers makes the solution portable between applications and consistent within the schema itself.

I'll see if I can put together some SQL with a few examples of how a view updates several related tables.

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: GUI for data entry

Graham Smith-4
Tom

> You don't need to create special tables for data entry. You can create views instead, >coupled with "instead of" triggers.

> I'll see if I can put together some SQL with a few examples of how a view updates several >related tables.

As the OP, I would be very interested in this. I did try using Views,
as there is no forms option in SQLiteManager, but the "add" button is
greyed out when using a View.

> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml

This link is currently giving an internal server error.

Thanks.

Graham
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: GUI for data entry

BareFeetWare
Hi Graham,

>> You don't need to create special tables for data entry. You can create views instead, coupled with "instead of" triggers.
>
>> I'll see if I can put together some SQL with a few examples of how a view updates several >related tables.
>
> As the OP, I would be very interested in this.

OK, I'll put something together and post it here. Do you have a schema you've been working on? I might be able to adapt it for you.

> I did try using Views, as there is no forms option in SQLiteManager

Try clicking the pencil button to show "Record Editor". I could only get it to work sometimes for views.

> but the "add" button is
> greyed out when using a View.

SQLiteManager seems to support updates (changing of existing rows) but not inserts (adding a new row) in views.

>> Comparison of SQLite GUI tools:
>> http://www.barefeetware.com/sqlite/compare/?ml
>
> This link is currently giving an internal server error.

Ah, thanks for the heads up. I've been moving my website to another hosting service. They don't seem to like some permissions on the PHP files. Incidentally, this page is actually a PHP file that grabs the data from an SQLite database. This database provides a view for the PHP to read and for a data entry operator (well, that's most often me) to read when using a GUI. That same view has "instead of" triggers so I can enter changes back into that same view, which propagates back to the underlying tables.

One of the features compared on the SQLite GUI comparison web page (which is now publicly viewable again), is "Data Entry -> Entry in views (with "instead of" triggers)". This will tell you what SQLite GUI editors I found that support editing of data in views.

Until today, I hadn't updated this comparison matrix for over a year, and now realise that some apps have even disappeared. It needs updating. If anyone is familiar with another SQLite GUI editor, please provide values for the comparison table and I will add it to the web page. Or let me know of any changes needed to existing data, or extra features you've compared.

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
12