Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #81483

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

Show key headers only | View raw

"Dennis Lee Bieber" <wlfraed@ix.netcom.com> wrote in message 
news:lrr67al6ppa852agu9rq2dstqtue17iiem@4ax.com...
> On Mon, 24 Nov 2014 10:05:11 +0200, "Frank Millman" <frank@chagford.com>
> declaimed the following:
>
>>
>>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
>

I also find it confusing. The same word is used by different RDBMS's to mean 
different things.

When using sqlite3 in interactive mode, you can enter '.tables' to list all 
tables in the database, and you can enter '.schema {table_name}' to view the 
original DDL statement used to create the table.

>From the PostgreSQL docs -

"""
A database contains one or more named schemas, which in turn contain tables. 
Schemas also contain other kinds of named objects, including data types, 
functions, and operators. The same object name can be used in different 
schemas without conflict; for example, both schema1 and myschema can contain 
tables named mytable. Unlike databases, schemas are not rigidly separated: a 
user can access objects in any of the schemas in the database he is 
connected to, if he has privileges to do so.
"""

>
> 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.
>

I find that schemas gives me the advantages of both without the 
disadvantages.

The disadvantage of using separate databases is that I want the flexibility 
to allow one company to access data from another company, under controlled 
conditions, specified by various parameters and permissions. It might be 
possible to accesss a remote database with some systems, I don't know, but 
schemas make it easy.

The disadvantage of using a single database, with a column specifying the 
company, is that I want the flexibility to manage different companies, with 
different data requirements, within the same system. A common database would 
result in a mish-mash of different tables, some of which only relate to one 
company or group of companies,  and other tables relate to others. Using 
schemas, I create one schema per company, which contains only the data 
relating to that company.

Frank

Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 18:11 -0800
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 13:22 +1100
    Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 19:54 -0800
      Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Michael Torrie <torriem@gmail.com> - 2014-11-22 21:58 -0700
      Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 16:41 +1100
        Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 22:08 -0800
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 17:32 +1100
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 22:46 -0800
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Christian Gollwitzer <auriocus@gmx.de> - 2014-11-23 10:55 +0100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-23 10:21 +0200
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-23 17:29 -0800
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 19:31 +1100
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Jussi Piitulainen <jpiitula@ling.helsinki.fi> - 2014-11-23 11:40 +0200
              Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 20:53 +1100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-23 13:01 -0500
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-24 10:05 +0200
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-24 13:44 -0500
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-25 10:17 +1100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-25 07:31 +0200
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-25 17:40 +1100
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-23 11:58 -0500
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-23 18:22 -0800
    Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-24 15:49 +1100

csiph-web