Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!eternal-september.org!feeder.eternal-september.org!newsfeed0.kamp.net!newsfeed.kamp.net!feeder1.cambriumusenet.nl!feed.tweaknews.nl!194.109.133.81.MISMATCH!newsfeed.xs4all.nl!newsfeed2.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject: Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert
Date: Mon, 24 Nov 2014 13:44:04 -0500
Organization: IISS Elusive Unicorn
References: <ca987fb8-157d-4612-bafa-5da2da2920e0@googlegroups.com> <mailman.16153.1416709340.18130.python-list@python.org> <5d474a87-e2a5-43d3-b07c-4a1194880b4d@googlegroups.com> <54716987.7040301@gmail.com> <mailman.16159.1416721303.18130.python-list@python.org> <ea4e3af8-f173-4706-ba4e-4eca9d701e40@googlegroups.com> <CAPTjJmp4Y5zowWn5yFtJutKO4H5jVtqLaNtWqEpA6B35xNdsNA@mail.gmail.com> <m4s5er$sg1$1@ger.gmane.org> <8d747a5biq4rc559tvgju088508bp0o2gv@4ax.com> <m4uork$6h5$1@ger.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.16255.1416854656.18130.python-list@python.org>
Lines: 72
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:81467

On Mon, 24 Nov 2014 10:05:11 +0200, "Frank Millman" <frank@chagford.com>
declaimed the following:

>I will explain why such a feature would have been useful for me.
>
>My accounting system handles multiple companies. I needed to figure out a 
>way to keep each one's data separate from the others.
>
>Originally I supported two databases - PostgreSQL and Sql Server. They both 
>have a concept called 'schemas', which handles my requirement elegantly - 
>each company's data is stored in its own schema.
>

	We must have a different impression of what a "schema" consists of. As
I learned it, the "schema" basically came down to the statements defining
the form of the data, the rules (triggers) on it, and the relationships
between data items. http://en.wikipedia.org/wiki/Database_schema

	By that definition, SQLite3 does support "schemas" (it's all the DDL
statements used to create the database less the data itself -- which are
DML). So does Firebird, MySQL, Access/JET

	(Based on the linked article, your usage appears to be the Oracle
definition... Interestingly -- out of the three books I have on PostgreSQL,
only one even has the word schema involved, and the description makes it
sound like just another layer; sort of a sub-database restricted by
username within the main database -- you still have to recreate the table
definitions within it)


Handbook of Relational Database Design (Fleming & von Halle, 1989
Addison-Wesley) page 18:
"""
In 1977, the American National Standards Institute ... developed a set of
requirements for effective DBMSs. These requirements were specified in
terms of a three-part framework for organizing data managed by a DBMS: (1)
the /external schema/, or data organization as viewed by the user or
programmer; (2) the /internal schema/, or data organization as viewed by
the DBMS's internal access logic; and (3) the /conceptual schema/, or
integrated view of all data employed within the business.
"""

	I've got another three or four theory books that use the English
"scheme" rather than the Greek "schema".



	For a client-server database system, with ability to restrict access
based upon user accounts/host address, there are a number of ways to
address the separation.

	Either each user account (company) gets its own database in the server
-- possibly using the same pre-packaged DML to define the tables/etc -- and
one uses the access control system to prevent an account from even knowing
there are other databases out there... Or... there is one set of tables
(one database) containing data from all the companies -- wherein each table
has a field identifying the company, and ALL accesses to the data is via
views that incorporate a select clause restricting the data to the current
logged in company.

	Since SQLite3 is a file-server database, with each user program
directly accessing the file, there is no user authorization phase (if you
have read access to the database file on the machine, you could copy it to
a machine with R/W privileges and dump all the data). No different than M$
Access/JET (MDB/ACCDB files). JET is also a file-server -- each copy of a
running application is fighting for access to the same file (via locks);
there is no master server arbitrating access requests.

-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/