Re: [sqlite] SQLite OLEDB provider for Linked Server

[CityDev]

It looks like you get an activation code from their site, which can either be
temporary or you can pay for a proper one.
-- 
View this message in context: 
http://www.nabble.com/SQLite-OLEDB-provider-for-Linked-Server-tp24635046p24640083.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLite: Porting to another Operating system.

[CityDev]

I'm new to SQLite. I would assume you would dump the tables to an external
format and then load them into the new database. I can't however see where
the documentation is for this kind of database management function. Anyone
know where I should look, or do you have to download the SQLite3 application
to see it?
-- 
View this message in context: 
http://www.nabble.com/SQLite%3A-Porting-to-another-Operating-system.-tp24640206p24643787.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLite: Porting to another Operating system.

[CityDev]

  All you have to do is copy  

That's handy - I didn't realise that. However I suggest it's good practice
to dump and reload in these kinds of situations. I don't yet know how SQLite
works but I suspect a reload will get the physical data into a better shape
and clear out deleted items etc. Do you know where's there's documentation
on this?

-- 
View this message in context: 
http://www.nabble.com/SQLite%3A-Porting-to-another-Operating-system.-tp24640206p24644244.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLite: Porting to another Operating system.

[CityDev]

Thanks Donald. I can't see how you get to that page off the documentation
menu but now I can go direct.

I'm only familiar with DB2, Access Jet and Focus. In each case I would
expect to reorganise the physical database on a regular basis - maybe daily
or weekly. What's the best way of doing that with SQLite?
-- 
View this message in context: 
http://www.nabble.com/SQLite%3A-Porting-to-another-Operating-system.-tp24640206p24648140.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] What is a Relation?

[CityDev]

Just to kill time over coffee - what do you take the word to mean?

I've just been reading a 1991 James Martin book on Object Orientation and he
was using it to talk about links between entities. Chris Date was very
specific that a relation was essentially a table. Mainly however, people
seem to use the word to describe the connections you can make by performing
joins between tables. What do you think is 'correct'? How did the other
meaning gain currency?

I see James Martin owns an island in Bermuda and has been handing out
millions all over the place - a good incentive to start writing those books
you've been thinking about.
-- 
View this message in context: 
http://www.nabble.com/What-is-a-Relation--tp24674278p24674278.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] What is a Relation?

[CityDev]

It's true that Codd and Date used the term 'relational' (They championed the
N-ary Relational Model - others were around at the same time) but it's not
easy to track the origin of the term in mathematics. Certainly the word
implies joining things together. I guess the joining refers to fields
(domains) within each row (n-tuple).

If you look at other forums you often see novices (and others) using the
word 'relate' as if it is some special way of joining information between
two tables. They also clearly feel that you need to declare foreign keys in
order to have a logical connection between tables. As you are no doubt
aware, one of the guiding principles of the relational model is there is
nothing that is not a data value so you are totally free to join anything to
anything as long as you feel it might make sense. Relationships between
tables are contingent - they can be there one day and gone the next. Any
persistent relationship information (eg foreign keys) is optional and there
for other purposes (eg documentation or referential integrity enforcement).

Another common conceptual misunderstanding I've seen over the years is that
the database is just a fancy filestore. There are still people who just want
a cursor to a chunk of data which they pull in and iterate over rather than
use SQL's power to manage data a set-at-a-time. Call me old fashioned but
object-relational mappers worry me on this score.

-- 
View this message in context: 
http://www.nabble.com/What-is-a-Relation--tp24674278p24681797.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Denormalisation

[CityDev]

I did a Computer Science MSc 30 years ago specialising in databases (the
relational model was only in prototypes). Of course normalisation was well
known, but what people would say is normalising is the easy part; the skill
comes in 'collapsing'. More recently the term 'denormalise' has been used
instead. This is where you repeat foreign data in a table to avoid the
overhead of joins at runtime. 

Over the intervening years I can't ever remember denormalising data (even
when dealing with eg 13 million insurance customers in a table). Is it OK
nowadays to say always aim to be fully normalised - modern RDBMSs are
usually powerful enough to cope with most anything?
-- 
View this message in context: 
http://www.nabble.com/Denormalisation-tp24688494p24688494.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] totally OT: debunking debunking SQL

[CityDev]

Codd had his 'extended relational model' and I think Chris Date has got the
Third Manifesto. Unfortunately people can't be satisfied they've invented
something really, really simple and just feel proud, they want to become
professors and write impenetrably clever papers that only their colleagues
can follow. 
-- 
View this message in context: 
http://www.nabble.com/totally-OT%3A-debunking-debunking-SQL-tp24687789p24688728.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Closure

[CityDev]

As an aside, a principle of the relational model is that operations on
relations should produce a relation. This caused a bit of a problem early on
as if you perform a Project operation ie cut down the number of fields, you
can finish up with 'duplicate' rows ie rows that can't be distinguished.
RDBMs in practice all use tables instead of relations. Even base tables can
contain 'duplicate' records although that would be bad practice. 


 
-- 
View this message in context: 
http://www.nabble.com/Statements-that-return-a-result-set--tp24735659p24737576.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Insert multiple entries in a single INSERT statement

[CityDev]

Kees Nuyt wrote:
> 
> 
> Insert one row at a time.
> 
> 
> 
 Presumably you can do this kind of thing:

INSERT INTO Table2 ( [FieldX] )
SELECT FieldY
FROM Table1;
-- 
View this message in context: 
http://www.nabble.com/Insert-multiple-entries-in-a-single-INSERT-statement-tp24705205p24737715.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Question on converting objects into relational db

[CityDev]

Bear in mind a relational database is designed around relations. It's not
obvious what your 'data model' is but there is a suggestion it contains
variable types of things. If you want to store variable things then many of
the features of a relational database don't work.

Often an object maps reasonably to an entity, and an entity shares its
structure with other entities of the same entity type. In this case you can
easily map object attributes to fields in a table. 

Maybe you are storing something more esoteric. You may have to serialize the
object rather than store it as fields.

-- 
View this message in context: 
http://www.nabble.com/Question-on-converting-objects-into-relational-db-tp25385539p25432919.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Question on converting objects into relational db

[CityDev]

Simon Slavin-2 wrote:
> 
> You can use property lists.  
> 

It's an interesting question as to what that gives you. It's clearly not a
relation if you know how it was formed but - just thinking about it briefly
- it may still behave as a relation as far as SQL is concerned. But it looks
dodgy to me.

-- 
View this message in context: 
http://www.nabble.com/Question-on-converting-objects-into-relational-db-tp25385539p25443743.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] SQLite Suitability for Shopping Cart

[CityDev]

I am building a shopping cart application that calls a web service which
provides lists of documents and the documents themselves. The user purchases
document images. The application will load lists into a database, then build
pages from the stored lists. The database will also keep track of
application state and store the retrieved documents for a month. It must be
capable of supporting 100 simultaneous users. The maximum list size is 8000
documents which takes about 5 minutes to load from the web service (in 5
second transactions). There will be more than one front end server.

>From this description, do you think SQLite is a valid choice as the database
in this scenario?
-- 
View this message in context: 
http://www.nabble.com/SQLite-Suitability-for-Shopping-Cart-tp25530589p25530589.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLite Suitability for Shopping Cart

[CityDev]

Thanks for that Sebastian. 

It does however surprise me. I believe Access Jet handles simultaneous
activities. Indeed I've got a feeling it does row-locking rather than
page-locking. It's odd that SQLite has such a limitation. Anyway, you've
saved me a lot of heartache.
-- 
View this message in context: 
http://www.nabble.com/SQLite-Suitability-for-Shopping-Cart-tp25530589p25562183.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLite Suitability for Shopping Cart

[CityDev]

Fred Williams-5 wrote:
> 
>  no further need to ask and answer, "Will SQLite support
> multi users? 
> 
Maybe it should be covered a bit more specifically on the SQLite site,
bearing in mind that new people would naturally have the mindset that
databases are for shared use normally. SQLite does have sharing capabilities
so it would be helpful to profile these for novices. 
-- 
View this message in context: 
http://www.nabble.com/SQLite-Suitability-for-Shopping-Cart-tp25530589p25615724.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] normalization example(s)

[CityDev]

You seem to be asking about four separate issues - normalisation,  table
creation, table loading, and SQLite syntax. The thing is I've just looked at
your book's index and I can't imagine a better source of answers to your
questions.

Maybe you would like to post some specific cases here?
-- 
View this message in context: 
http://www.nabble.com/normalization-example%28s%29-tp25630323p25641261.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Sqlite reading all column data on selects.

[CityDev]

In the relational model, the unit of access is a tuple so you would expect a
DBMS to process all columns. Typically they also access in units of a page
(although a tuple of course might extend across more than one page)

The discussion seems to have moved onto selecting pages. Surely if you need
to do this, stick an index on the column concerned. The index's purpose is
to take you directly to the target page. 
-- 
View this message in context: 
http://www.nabble.com/Sqlite-reading-all-column-data-on-selects.-tp25594620p25641328.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] normalization example(s)

[CityDev]

Don't get carried away with keys, triggers and stuff like that at this point.

For a start off your example is not normalised. You would have a person
table and a food table but then you will need a person-food table as the
relationship between person and food is many-to-many so you have to break
that up using an 'intersection entity'. Think what you want to store (if
anything) in a row like  - it could be things like last-eaten.

Then if you think on, you probably will want to break it up again as you
will want to hold 'dishes', like peaches-and-cream or eggs-benedict.

Work with the data first before getting hung up a SQLite table declarations.

-- 
View this message in context: 
http://www.nabble.com/normalization-example%28s%29-tp25630323p25648562.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] feature request - field exclusion list in select list

[CityDev]

This is altogether bad practice.

You should always define what fields you want. Using Select * is just asking
for trouble. To limit the number of fields that are accessible to a
process/user/program, you should use a view. With a view you can of course
then use Select * but that's not the point.

Each program should have a defined external view/schema. This decouples
programs from the underlying data model (within the limitations of the
database view facilities - which are not that brilliant in SQLite).   
-- 
View this message in context: 
http://www.nabble.com/feature-request---field-exclusion-list-in-select-list-tp25639074p25696861.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] You can't

[CityDev]

SQLite is a relational database. Relations are unordered sets so SQL, which
SQLIte uses, doesn't have any operators to move backwards and forwards.

With a relational database you should number your records in some way and
then your application asks for the appropriate records by number, so if
you're looking at Question 23 and you want to page backwards, your
application must ask SQLite for Question 22.
-- 
View this message in context: 
http://www.nabble.com/how-to-move-backward-and-forward-in-sqlite--tp25702963p25710367.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLite DB Structure

[CityDev]

Firstly I'm not an SQLite expert but, now you know that, I would guess SQLite
marks deleted records waiting until some clean-up process removes them and
re-writes the active data. I would therefore look for some bit that is set.
Try setting up a new database, adding records - taking a copy, then deleting
the records and then compare the two images. That should point you to where
SQLIte is tagging records. Then you go back to your target database and
'unset' the deletion flags, now you know where SQL puts them. You should
then be able to see the deleted records again. 
-- 
View this message in context: 
http://www.nabble.com/SQLite-DB-Structure-tp25990484p26013692.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Table within a table??

[CityDev]

Darren Duncan wrote:
> 
> 
>   Or at least it is in the version of the relational model 
> that allows non-scalar attribute values, but that is the one that Chris
> Date et 
> al, as well as myself ascribe to.
> 
> 

I didn't read this through but I recall Chris Date defining a relational
database as formed from relations normalised to 1NF. First Normal Form
basically means no substructure ie each column is a single value from a
scalar domain. To say it would be nice to have structured data is ignoring
two things: i) we've got this for just fine without it, and ii) SQL is a
first-order language so it can't easily handle substructure. 
-- 
View this message in context: 
http://old.nabble.com/Table-within-a-table---tp26125451p26154639.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Table within a table??

[CityDev]

Darren Duncan wrote:
> 
>   Being that arrays *are* relations, you can use all the relational
> operators on them.  
> 

Just to be totally clear - an array is not a relation. An array has fixed
order of each dimension (eg columns and rows), and you address it by
position. A relation is unordered (although you can use a query language
like SQL to produce ordered tables), and you address it by data values.

The rationale was that a user of the information wouldn't have to dig around
to find out that Field 1 is Q1 sales, Field 2 is Q2 sales, Field 5 is Actual
Sales to Date and Field 6 is Last Year Total Sales etc. Q2 Sales would have
to have a name, although there's nothing to stop someone calling it
SXVAT_Q2_PreCV2. It's better than nothing.

Nothing's perfect and it seems no-one can say what a domain should contain
but that's no excuse to throw the baby out with the bath water. Stick as
close to relational thinking as you can, and your design will be that much
more effective.  
-- 
View this message in context: 
http://old.nabble.com/Table-within-a-table---tp26125451p26199506.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] How to deal with non 'normilize-able' tables

[CityDev]

What you are saying is you are holding information about items which have
different characteristics. To represent these as relations you would have a
product entity then you would have an attribute entity that would be like
(product_id,attribute_id,attribute_name,attribute_value) eg:

screwdriver1210, 1, handle, wood
screwdriver1210, 2, point, crossdrive
screwdriver1210, 3, weight, 180
hammer0899, 1,weight, 3.35
hammer0899, 2 head, steel
etc

You then join from product to this table and pick up the listed features.
You can of course use attribute_ids that are standardised eg 56 is always
weight etc

That's one way. Alternatively if there aren't too many different attributes
you can collapse ('denormalise') these into a bunch of fields in the product
table. I would recommend leaving your model normalised until you are forced
to compromise.


-- 
View this message in context: 
http://old.nabble.com/How-to-deal-with-non-%27normilize-able%27-tables-tp26495733p26516107.html
Sent from the SQLite mailing list archive at Nabble.com.

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