Re: [sqlite] SQLite template files

[Jean-Denis Muys]

On 4/6/10 18:50 , "BareFeet"  wrote:

> I understand that in many cases, the SQLite database developed are
> intrinsically tied to and designed for the application framework in which they
> reside. However, even those SQLite database files can be opened and
> manipulated by a SQLite GUI tool or the sqlite3 command line tool. In fact,
> the developer of those probably highly specialized database files no doubt
> created the databases using a GUI or command line tool.

In theory it's possible. In practice, it's often of limited value. Often,
much of the domain-specific behaviour is encoded in the real application
rather than in the SQLite file. The app itself is often a lot more than a
shallow GUI on top of a database.

In fact, when wanting to transfer much of that domain-specific behaviour, I
found that when it was possible, it was also cumbersome and error prone. For
me SQLite is most of the time a high-level document file format. The fact
that behind the curtain it's also a SQL database is nice but not essential.

To be more specific, my applications usually use Core Data as the data
persistence layer. My data models are designed using Xcode's graphical
entity-relationship design tool which generates specific design documents (
xxx.xcdatamodel) and the corresponding SQLite databases automatically.

Out of curiosity, I sometimes opened the resulting SQLite files with
general-purpose SQLite tools, and this might occasionally be useful for
debugging purposes, but certainly not for educational purposes. I suspect
this would generally be the case, and the reason why no sharing of SQLite
structure has emerged.

For sharing purposes, the Xcode entity-relationship document (or some such)
would be more interesting.

Jean-Denis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite template files

[Simon Slavin]

On 6 Apr 2010, at 5:50pm, BareFeet wrote:

> If the logic is built into the SQL itself, then the other development 
> language is not an issue. I can open my SQLite database files in a GUI tool 
> written in Objective-C or the command line tool written in C, or a PHP front 
> end to a web page, etc.

In SQLite there's a certain amount of logic which can be carried out or 
enforced by TRIGGERs and FOREIGN KEYs.  I can imagine a competent hacker 
setting up a database with a lot of these and using the resulting database 
without any other programming language support, possibly even from the sqlite3 
command-line tool.  If you add a SQLite GUI database manager to that you might 
even have a usable environment for someone who is fairly techie but not a 
programmer.

So I just spent a couple of minutes imagining what that would be like.  I think 
it still won't result in anything a non-techie can use.  Error messages from 
FOREIGN KEY constraints, for sure, wouldn't be much of an explanation to a 
non-techie user as to what they did wrong.  ("No you can't save this list of 
ingredients because I don't know what 'linguini' is.")

So you'd have to do the whole thing with TRIGGERs, because they can have 
specified error text.  Which is not normal (mostly constraints are imposed by 
the programming around the SQL logic, done in whatever programming language the 
programmer uses).  Which means you'll want schema specially designed for your 
GUI environment.  Which is why you won't be using any existing SQLite template 
files.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite template files

[BareFeet]

> I believe that by 'template files' you mean some example databases for common 
> applications, for instance invoicing, a list of the albums you have, a recipe 
> database, etc..

Yes, that is correct.

> That's because what you seek does not exist.

Well, it does exist if people share their SQLite database files ;-)

> User interfaces are separate from the rdbms back end.


Well, yes, I guess that's true. But an SQLite database file is a standalone 
database file that can be opened by any SQLite browser/management tool, such as 
the sqlite3 command line tool or any of the GUI tools listed here:
http://www.tandb.com.au/sqlite/compare/

It is possible to design, say, a general invoicing database using the sqlite3 
command line tool or a SQLite GUI tool and then open it in a completely 
different GUI tool. If the database designer has imbedded all the necessary 
logic in the SQL itself (such as through triggers and views), then the database 
file is a portable repository for information.

I understand that in many cases, the SQLite database developed are 
intrinsically tied to and designed for the application framework in which they 
reside. However, even those SQLite database files can be opened and manipulated 
by a SQLite GUI tool or the sqlite3 command line tool. In fact, the developer 
of those probably highly specialized database files no doubt created the 
databases using a GUI or command line tool.

> The UI depends on the application (accounting, addressbook, spatial analyses)


In its basic form, the UI is just data viewing and entry into columns in rows. 
A generic GUI tool does that.

> the language used for development (python, ruby, perl, C, Common LISP, object 
> COBOL), and the intended user base.


If the logic is built into the SQL itself, then the other development language 
is not an issue. I can open my SQLite database files in a GUI tool written in 
Objective-C or the command line tool written in C, or a PHP front end to a web 
page, etc.

> You also need to write the middleware that validates data entry, translates 
> between the human-readable UI widgets and the desired dbms action (INSERT, 
> DELETE, UPDATE, SELECT) using embedded SQL (a set-based language, not a 
> procedural one).


That depends on the tool used. At the most basic level data validation should 
be enforced at the SQL level and the GUI tool should show any SQl generated 
errors. At a basic minimum, the GUI tool only has to facilitate basic SQL 
statement execution. If it has some nice GUI elements for update, insert etc, 
all the better.

Thanks for the input,
Tom
BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite template files

[Rich Shepard]

On Tue, 6 Apr 2010, BareFeet wrote:

> Thanks to those who replied on this topic, but no-one offered any
> repository of SQLite template files. Does that mean there aren't any
> available?

Tom,

   That's because what you seek does not exist. User interfaces are separate
from the rdbms back end. The UI depends on the application (accounting,
addressbook, spatial analyses), the language used for development (python,
ruby, perl, C, Common LISP, object COBOL), and the intended user base. You
also need to write the middleware that validates data entry, translates
between the human-readable UI widgets and the desired dbms action (INSERT,
DELETE, UPDATE, SELECT) using embedded SQL (a set-based language, not a
procedural one).

   Asking for a SQLite template is analoguos to deciding that you'll use an
International Harvester 6.9l diesel engine and asking for pre-existing
vehicle designs to wrap around it without specifying your intended use.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite template files

[Simon Slavin]

On 6 Apr 2010, at 10:49am, BareFeet wrote:

> Thanks to those who replied on this topic, but no-one offered any repository 
> of SQLite template files. Does that mean there aren't any available?

I believe that by 'template files' you mean some example databases for common 
applications, for instance invoicing, a list of the albums you have, a recipe 
database, etc..

The answer is that I've never come across any such collection of schema, nor 
would I expect to.  SQLite itself is just an engine and needs a programming 
language around it to be of any use to someone.  And anyone who can write code 
to call SQLite is probably going to want to design their own schema to suit 
their own particular invoicing structure or music filing system.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite template files

[BareFeet]

Thanks to those who replied on this topic, but no-one offered any repository of 
SQLite template files. Does that mean there aren't any available?

Thanks,
Tom
BareFeet


From: BareFeet <list@tandb.com.au>
Date: 27 March 2010 5:58:32 PM AEDT
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: [sqlite] SQLite template files
Hi all,

I'm on the verge of releasing a new GUI app for SQLite. I would like to include 
some SQLite template files other than my own. These templates will act as a 
starting point for the user to enter data and/or modify the schema as needed.

One feature of this software is its ability to facilitate data entry in views 
(ie not just tables). So I am especially interested in SQLite files that make 
use of views, triggers, relationships etc. Some simple, some advanced.

Can you direct me to were I might find some useful templates?

Thanks,
Tom
BareFeet

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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite template files

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

BareFeet wrote:
> I'm on the verge of releasing a new GUI app for SQLite. I would like to 
> include some SQLite template files other than my own.

I'd suggest having them on a web site wiki style so that people can
update and download them that way.  It is also better not tying the
templates to application releases (eg if you release a new version of
the application every six months then you don't want users to have to
wait 6 months for new/updated templates.)

Here is an example of how you could do it.  The Acire project lists
general Python snippets:

  http://aciresnippets.wordpress.com/

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEUEARECAAYFAkuu48cACgkQmOOfHg372QS6qACYz5wMincyNTM5FQH4/seu9kkZ
TgCfY6nPKXzNgb8wT0H84hgH9p//D+4=
=LYdj
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite template files

[Jean-Christophe Deschamps]

Hi Tom,

>BTW- if you haven't done so already; it may be of use to the user to add
>extensions: VirtualText and Jean-Christophe Deschamps has an extension
>for fuzzy search for example.

There is no problem.  Alexey put it on his website under the extension 
for Unicode folder.  Please drop me a mail if you're interested, as the 
code as it is currently compiles for Windows only, but I'm willing to 
work to port to whatever system.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite template files

[Gary_Gabriel]

Hi Tom,
> One feature of this software is its ability to facilitate data entry in views 
> (ie not just tables). So I am especially interested in SQLite files that make 
> use of views, triggers, relationships etc. Some simple, some advanced.
>
> Can you direct me to were I might find some useful templates?
>   
I am completing an SQLite application that maybe of interest to you. It
builds temporary tables for user entry of Internet Messaging information
resources and assets. Views (will) play an useful role for user to
enable him to gain different perspectives of his stored information
resources and assets for use in project management and threading
information. These perspectives begin to put the resources into
relationships that provide potential to approach project management issues.

If you are interested, I don't feel comfortable discussing details on
this busy mailing list; send me an email with any questions or remarks
you may have. We can clarify the details and come back to the list later.

Let me know what you think- Gary Gabriel

BTW- if you haven't done so already; it may be of use to the user to add
extensions: VirtualText and Jean-Christophe Deschamps has an extension
for fuzzy search for example.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite template files

[BareFeet]

Hi all,

I'm on the verge of releasing a new GUI app for SQLite. I would like to include 
some SQLite template files other than my own. These templates will act as a 
starting point for the user to enter data and/or modify the schema as needed.

One feature of this software is its ability to facilitate data entry in views 
(ie not just tables). So I am especially interested in SQLite files that make 
use of views, triggers, relationships etc. Some simple, some advanced.

Can you direct me to were I might find some useful templates?

Thanks,
Tom
BareFeet

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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users