Re: [sqlite] Table within a table??

[mark m]

O.K.  I think I am starting to get the idea.  It is just so foreign for me
to organize things this way.
A master work history table for all cases almost seems confusing.  It will
just take a bit of adjustment
for me to "trust" the database way of doing things.  Text files organized in
the way I described has always
made it easy for me to figure things out when there was a problem.

I will give this a try.

Thanks very much for all the help.

On Fri, Oct 30, 2009 at 4:04 PM, Darren Duncan wrote:

> mark m wrote:
> > Thanks very much!!  It also occurred to me that I could have a Table
> named
> > "case1" and another
> > named "case1workhist".  The RDBMS wouldn't know they were related but my
> > application could be
> > set up to know this.
> >
> > Here is more detail on my current data organization:
> >
> > Open Cases
> >Case 1...
> >Case 2...
> >   field 1
> >   field 2
> >   work history
> >  item 1
> >  item 2
> > worker ID
> > duration
> > type
> > rate
> >  item 3
> >  ...
> >  ...
> >  item n
> >Case 3
> >...
> >...
> >Case n
> >
> > In my app, a certain case is chosen to be displayed.  All of the above
> > information is displayed in one form
> > or another within my app.  The user can change any given piece of info or
> > add new information.  So, I guess
> > I could just look for the tables "Casen" and "casenworkhist" to display
> my
> > info.  I do however need to go through
> > and calculate all the hours for all open cases and other calculations
> like
> > that.  I want to be sure I'm setting things
> > up in a way that will allow me to do this with minimal overhead.
> >
> > In your method I would only have 1 workhist table??  whereas in my method
> I
> > would have n workhist tables.  Is it
> > better to have only 1 and use a foriegn key like you describe to link
> work
> > history records with a given case??  Is the
> > rule generally to minimize the number of tables??
>
> A rule for relational database best practices is to minimize the number of
> tables that are mutually homogeneous in meaning and structure, and to allow
> or
> exploit multiple tables that are mutually heterogeneous.  If you have a
> conceptual reason for having multiple same-looking tables, then you encode
> that
> as an extra column in the 1 table.
>
> So for example, the work history details for *all* of your cases would go
> in *1*
> work_history table, not a separate table for each case, and you would have
> a
> field in work_history called case_number to identify which records of that
> table
> belong to each case.
>
> Given the hierarchy you mention, a table layout like this might work:
>
>   CREATE TABLE cases (
> case_number INTEGER PRIMARY KEY,
> ,
> 
>   )
>
>   CREATE TABLE workers (
>  worker_id INTEGER PRIMARY KEY,
>  ...
>   )
>
>   CREATE TABLE work_histories (
> work_history_item_number INTEGER PRIMARY KEY,
> case_number INTEGER,
> worker_id INTEGER,
> duration,
> type,
> rate,
> UNIQUE KEY (case_number, worker_id)  # or make this pk instead
> FOREIGN KEY (case_number) REFERENCES cases (case_number),
> FOREIGN KEY (worker_id) REFERENCES workers (worker_id)
>   )
>
> By the way, I name my tables in plural to describe what the table as a
> whole
> represents, (much like how one might name an array variable in a program,
> which
> is what a table is analogous to save for not being ordered), which is for
> example a collection of cases.  But some people will tell you tables should
> be
> named after what an individual record in it represents; I disagree with
> them (it
> is like naming an array after what one of its elements is) but it is
> commonly
> practiced, and its a style issue in the end, the DBMS doesn't care.
>
> -- Darren Duncan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature proposal - strong but dynamic typing

[Dan Bishop]

Darren Duncan wrote:
> Roger Binns wrote:
>   
>>> In fact this support might even be easier as it may only require 
>>> enhancements to 
>>> the SQL parser, which would generate VM opcodes like for a CHECK 
>>> constraint, 
>>> unless further work is done to optimize for the presented cases, or to 
>>> enhance 
>>> semantics.
>>>   
>> It has been stated in several places that the increasing items in the parser
>> will lead to various fields needing to go to the next size up which would
>> increase memory consumption.  There is also the whole backwards
>> compatibility angle - what would happen if the database was loaded into an
>> older version of SQLite which then ignored this whole UNIVERSAL thing
>> allowing "wrongly" typed data to be inserted?
>> 
>
> An added type name like UNIVERSAL would be completely backwards compatible 
> because, as far as I recall, if SQLite currently sees a type name it doesn't 
> recognize, then the column has no affinity and will accept any value, so same 
> behavior.  And so then, for older systems using that keyword would be 
> effectively a documentation convention.
>   
This is not correct. See http://www.sqlite.org/datatype3.html, section 2.1:

"""

The type affinity of a column is determined by the declared type of the 
column, according to the following rules:

   1.

  If the datatype contains the string "INT" then it is assigned
  INTEGER affinity.

   2.

  If the datatype of the column contains any of the strings "CHAR",
  "CLOB", or "TEXT" then that column has TEXT affinity. Notice that
  the type VARCHAR contains the string "CHAR" and is thus assigned
  TEXT affinity.

   3.

  If the datatype for a column contains the string "BLOB" or if no
  datatype is specified then the column has affinity NONE.

   4.

  If the datatype for a column contains any of the strings "REAL",
  "FLOA", or "DOUB" then the column has REAL affinity

   5.

  Otherwise, the affinity is NUMERIC.

If a table is created using a "CREATE TABLE  AS SELECT..." 
statement, then all columns have no datatype specified and they are 
given no affinity.

"""

#5 means that your UNIVERSAL type is not backwards-compatible, and #3 
means that it is not necessary.

Your proposal would break any databases that store strings or blobs in 
columns with unrecognized types. In particular, it would break all the 
tables I have that store timestamps as text in columns declared as 
TIMESTAMP. We could deal with this by adding a new affinity rule:

"""
If the datatype for a column contains either of the strings "DATE" or 
"TIME", then the column has DATETIME affinity.

A column with DATETIME affinity behaves in the same way as a column with 
NUMERIC affinity, except that in strict affinity mode TEXT and BLOB 
values are allowed only if they are valid time strings (as determined by 
the datetime() or julianday() function).
"""

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

Re: [sqlite] Data loss after vacuum

[Simon Slavin]

On 31 Oct 2009, at 3:22am, chen jia wrote:

> Before I ran vacuum, this table, firmsret11, has 2338120 rows.
>
> After I ran vacuum as follows,
> $ sqlite3 hq.db
> SQLite version 3.6.10
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> vacuum;
> sqlite> .exit
>
> and this table only had two rows,
> $ sqlite3 hq.db
> SQLite version 3.6.10
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select count(*) from firmsret11;
> 2
>
> Dose anyone know how this could happen and how I can do to check what
> is causing this problem?  Thanks.

Please enter this line in the same sqlite3 tool you are using for  
vacuum:

PRAGMA integrity_check;

It appears that you have a copy of the database from before the  
'vacuum' command.  If you can, take another copy and run the PRAGMA  
command both before and after the 'vacuum'.  For more details see



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

[sqlite] Data loss after vacuum

[chen jia]

Hi there,

I experienced data loss in one my tables after I execute vacuum
command from sqlite3.

Before I ran vacuum, this table, firmsret11, has 2338120 rows.

After I ran vacuum as follows,
$ sqlite3 hq.db
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> vacuum;
sqlite> .exit

and this table only had two rows,
$ sqlite3 hq.db
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select count(*) from firmsret11;
2

Dose anyone know how this could happen and how I can do to check what
is causing this problem?  Thanks.

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

Re: [sqlite] Another SELECT/JOIN Question.

[Igor Tandetnik]

Peter Haworth 
wrote: 
> Trying to implement the following situation involving 4 tables
> 
> Customers is the "master table" in that the results should end up with
> one row for each primary key value in it.
> 
> I need to sum the values of a column in the Sales table, which has a
> column that joins to the primary key of Customers and can have
> multiple rows for each Customers primary key value.
> 
> I also need to sum the values of a column in the Expenses table.  In
> order to get the Expenses entries for each primary key value in
> TableA, I first have to join to the Transactions table using the
> primary key of Table A,  and then link to Expenses using the primary
> key value of Transactions.  Transactions can have multiple rows for
> each Customers primaryKey value and Expenses can have multiple rows
> for each Transactions primary key value.
> 
> Here's the latest attempt.
> 
> SELECT Customers.CustID,sum(Sales.value) AS totalsales,
> Sum(Expenses.value) AS totalexpenses
> FROM Customers
> LEFT JOIN Sales ON Sales.CustID=Customers.CustID
> LEFT JOIN Transactions ON Transactions.CustID=Customers.CustID
> LEFT JOIN Expenses ON Expenses.TranID=Transactions.TranID
> GROUP BY Customers.CustID

select Customers.CustID,
(select sum(value) from Sales where Sales.CustID=Customers.CustID),
(select sum(value) from Transactions join Expenses on 
(Expenses.TranID=Transactions.TranID)
 where Transactions.CustID=Customers.CustID)
from Customers;

Igor Tandetnik

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

Re: [sqlite] BUG: datatypes conversion logic error

[Simon Slavin]

On 30 Oct 2009, at 9:47pm, Alexey Pechnikov wrote:

> Now SQLite think that 1 is equal to '1' in some causes and think
> different in other.

Just like every other language, once you get into it you have to learn  
how the language works to understand what's going on.  Your problem is  
not really with the comparison, it's with what happens to a value when  
it is stored in a table.  Strongly typed languages usually do one of  
two things:

A) not allow the comparison at all (you get a syntax error from the  
compiler)

B) say that two values of different types never equal one-another

I think either of those would suit you and fortunately you can  
implement each one in whatever programming language you're using,  
before the values get near SQLite.  You're a person who thinks along  
the lines of strong typing, so do what suits you.

> Do you know some language where 1='1' or 1!='1' randomly?

Not randomly, no.  But I know of quite a few languages where storing a  
value somewhere can modify it:

Start with value V.
Store it somewhere.
Get it back again.
Compare with the original and they do not match.

In C, try storing a 'double float' value in a 'single float', getting  
it back out into a 'double float' again, then comparing that with the  
original value.  (Make sure your compiler doesn't optimise out the  
operations.)  You can find plenty of initial values that this fails  
for.  So obviously, the operation of storing-then-retrieving the value  
makes it difficult to tell how it behaves in the future.

> The correct way is to convert numeric values to text before check
> of equality.

That is no more correct than to refuse to compare numeric and text  
values.  The thing you have spotted does really exist, but I don't  
think it's a bug.  If you want to prove it's a bug, I think this is  
where to look:



The key word seems to be 'comparable'.  It might be in the section on  
page 25 on 'coercibility rules for dyadic operators' or section 8.2 on  
page 207 on comparison operators.  I don't think the standard says  
definitely one way or another whether you can compare a string with a  
number, but I didn't read it thoroughly taking notes.

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

[sqlite] Another SELECT/JOIN Question.

[Peter Haworth]

Trying to implement the following situation involving 4 tables

Customers is the "master table" in that the results should end up with  
one row for each primary key value in it.

I need to sum the values of a column in the Sales table, which has a  
column that joins to the primary key of Customers and can have  
multiple rows for each Customers primary key value.

I also need to sum the values of a column in the Expenses table.  In  
order to get the Expenses entries for each primary key value in  
TableA, I first have to join to the Transactions table using the  
primary key of Table A,  and then link to Expenses using the primary  
key value of Transactions.  Transactions can have multiple rows for  
each Customers primaryKey value and Expenses can have multiple rows  
for each Transactions primary key value.

Here's the latest attempt.

SELECT Customers.CustID,sum(Sales.value) AS totalsales,  
Sum(Expenses.value) AS totalexpenses
FROM Customers
LEFT JOIN Sales ON Sales.CustID=Customers.CustID
LEFT JOIN Transactions ON Transactions.CustID=Customers.CustID
LEFT JOIN Expenses ON Expenses.TranID=Transactions.TranID
GROUP BY Customers.CustID

What I want to end up with is 1row for each CustID value containing  
CustID, totalsales, and totalexpenses.  I do get one row per CustID  
but totalsales and totalexpenses are almost always incorrect   For  
example, if there are 10 rows in TableB for a particular value of  
CustID, the sum of the TableB entries is 10 times what it should be.

Any ideas?

Pete






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

Re: [sqlite] How to input a double num?

[Kees Nuyt]

On Fri, 30 Oct 2009 02:47:37 -0700 (PDT), liubin liu
<7101...@sina.com> wrote:

> Thank you!
>
> I mayn't need the high precision like
> "212345678901234567890123456.988290112".


Indeed, you don't. In a previous message you said:

>> The project is on power-measurement. So there
>> are some big nums with high precision. 

I know of no power-measurement with such high precision.
6 or 7 significant digits is the best you can get in that
field. As in "2123456.0"

That a huge accuracy, think of one dollar on a few million.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: datatypes conversion logic error

[Alexey Pechnikov]

Hello!

On Friday 30 October 2009 22:16:27 Simon Slavin wrote:
> > I think the text '1' must
> > be equal to numeric 1 always like to standart de-facto for RDBMS.
> 
> 
> Personally I think that 1 and 1.0 are the same, and that '1' is never  
> the same as either.  Some people and some languages feel that 1 is  
> never the same as 1.0.  It's all a point of view.

Now SQLite think that 1 is equal to '1' in some causes and think
different in other.

$ sqlite3
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(a text);
sqlite> insert into test values(1);
sqlite> insert into test values('1');
sqlite> select * from test where a=1;
1
1
sqlite> select * from test where a='1';
1
1


$ sqlite3
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(a int);
sqlite> insert into test values(1);
sqlite> insert into test values('1');
sqlite> select * from test where a=1;
1
1
sqlite> select * from test where a='1';
1
1

As you can see above 1 is always equal to '1'. 
And integer is automatically converted to float:
select 1.0=1
1

But numeric is not converted to text before check:
sqlite> select 1='1';
0
sqlite> select 1.1='1.1';
0

Do you know some language where 1='1' or 1!='1' randomly?
The correct way is to convert numeric values to text before check
of equality.

SQLite datatyping is similar to Tcl by ideology. As example in Tcl:
$ tclsh8.5
tclsh8.5 [/tmp]expr {1==1.0?1:0}
1
tclsh8.5 [/tmp]expr {1=="1.0"?1:0}
1
tclsh8.5 [/tmp]expr {1=="1"?1:0}
1


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature proposal - strong but dynamic typing

[Nicolas Williams]

On Fri, Oct 30, 2009 at 03:59:11PM -0500, Jay A. Kreibich wrote:
> On Fri, Oct 30, 2009 at 03:19:59PM -0500, Nicolas Williams scratched on the 
> wall:
> > I should add that a pragma that cause CHECK constraints to be
> > automatically created for enforcing strong typing in subsequent CREATE
> > TABLE statements 
> 
>   That's tricky.  Values have TYPES.  Columns have AFFINITIES.  There
>   is not a clear or obvious one-to-one mapping between them in all
>   cases.  The CREATE TABLE column datatypes can be used to figure out
>   the column affinity, but that doesn't always make it clear what
>   value type should go in the CHECK expression.

Indeed.

>   Also, CHECK expressions need to go into the CREATE TABLE definition.
>   They can't be added after-the-fact, like key triggers.  There are
>   ways around this, of course, but they're kind of messy.

That's actually a _feature_.  The pragma should affect only subsequent
table creation.

Note: I am not asking for this.  I'm only asking that whatever is done,
if anything, be backwards compatible, and preferably result in DB files
that are compatible with older versions of SQLite3.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Jay A. Kreibich]

On Fri, Oct 30, 2009 at 03:19:59PM -0500, Nicolas Williams scratched on the 
wall:
> I should add that a pragma that cause CHECK constraints to be
> automatically created for enforcing strong typing in subsequent CREATE
> TABLE statements 

  That's tricky.  Values have TYPES.  Columns have AFFINITIES.  There
  is not a clear or obvious one-to-one mapping between them in all
  cases.  The CREATE TABLE column datatypes can be used to figure out
  the column affinity, but that doesn't always make it clear what
  value type should go in the CHECK expression.



  Also, CHECK expressions need to go into the CREATE TABLE definition.
  They can't be added after-the-fact, like key triggers.  There are
  ways around this, of course, but they're kind of messy.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to skip the first field on .import

[Ted Rolle]

On Fri, 30 Oct 2009 12:17:53 -0700
Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Ted Rolle wrote:
> > How do I let this start out at the default value and auto increment?
> > My column separator is '|'.
> 
> Create a temporary table and import into that.  Then copy those
> values into your permanent table using something like:
> 
>   insert into perm_table select null,* from temp_table;
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
> 
> iEYEARECAAYFAkrrO90ACgkQmOOfHg372QRNgQCgq/obmjL/Rw862bsqk9GIU4FE
> yQYAoLc3JEugW75ZSGPZADTuZNC5Ruww
> =CZTr
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

I keep baing amazed at SQLite!  The automagically-generated rowid is
what I needed all along.  It comes along for the ride.  I didn't have
to change a thing.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Darren Duncan]

I think that something several people had missed was that I specifically 
proposed the strong typing behavior to be activated by a new pragma, and unless 
people activate that pragma they would get the old behavior, so total backwards 
compatibility.  I see several people then proposed using the same pragma.

In any event, I am quite satisfied to drop the matter and no longer pursue this 
feature.

Let people write explicit CHECK constraints when they want strong typing, which 
also has the advantage of carrying that behavior backwards to older SQLite 
installations.

And also, CHECK constraints would be necessary to use anyway for the general 
case of constraints where the constraint isn't just "all values are of this 
generic type".

So, request withdrawn.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Nicolas Williams]

I should add that a pragma that cause CHECK constraints to be
automatically created for enforcing strong typing in subsequent CREATE
TABLE statements is rather like having FOREIGN KEY clauses automatically
generate triggers.  There's precedent, in other words, and it is a
simple way to implement strong typing.

Also, when you view the schema you'll see the CHECK clauses, and will
know not only that typing is enforced at INSERT/UPDATE time, but also
what type SQLite3 actually inferred from the declared type.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on a Windows Network

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Slavin wrote:
> Standard Windows drive sharing uses SMB, sometimes called SAMBA.

SMB stands for Server Message Block - the name of the protocol as originally
developed by IBM in 1982.  The protocol is extensible in that a dialect is
negotiated up front, there are numerous bit flags indicating features (eg
adding Unicode, changing bulk read/write handling) which were later added,
various requests have info-levels - a number indicating the data structure
expected.  There have been implementations of clients and servers in Dos,
Xenix, OS/2, Windows 3.x, Windows 9x, Windows NT line as well as on Unix
using ported Windows code (lookup ASU), native implementation on DEC
Pathworks etc.  There is not one protocol, but rather a huge hairy mess that
has grown over time.  (For example there are several different ways to open
a file, at least 5 different ways to write, numerous different IPC
mechanisms, different locking mechanisms and every new version of the
Windows NT/2K/XP etc line had the habit of directly marshalling internal
kernel data structures over the wire which changed with each release.)

In the mid-90s when all the Internet stuff was getting hot, Sun decided to
call NFS WebNFS.  Microsoft responded by calling the protocol CIFS (the I
stands for Internet) and doing some minor tweaks that made their
implementation less restrictive when setting up connections.

Samba is a free software (GPL) implementation of the protocol and by far the
most common non-Microsoft one.  However it has to translate the expected
semantics into what the platform it is running on has.  These expectations
change by client and server versions and in some cases even by what the
filesystem type is reported as (consequently Samba typically reports most
filesystems as ntfs even though they aren't).

There are maddening little implementation details, bugs becoming established
behaviour and worked around by clients and servers (which break if you fix
the bug) etc.  Sometimes applications have surreal behaviour (Excel is the
king of this).

In short there is nothing "standard" about it, and calling it "Samba" is
wrong :-)

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

iEYEARECAAYFAkrrSSgACgkQmOOfHg372QR9hwCfdBp8mpO4x94Uldks9n9r3i5W
dGcAmwbzfVrd3tiUJ8GYClJQDwJwd1b4
=xWbt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table within a table??

[Darren Duncan]

mark m wrote:
> Thanks very much!!  It also occurred to me that I could have a Table named
> "case1" and another
> named "case1workhist".  The RDBMS wouldn't know they were related but my
> application could be
> set up to know this.
> 
> Here is more detail on my current data organization:
> 
> Open Cases
>Case 1...
>Case 2...
>   field 1
>   field 2
>   work history
>  item 1
>  item 2
> worker ID
> duration
> type
> rate
>  item 3
>  ...
>  ...
>  item n
>Case 3
>...
>...
>Case n
> 
> In my app, a certain case is chosen to be displayed.  All of the above
> information is displayed in one form
> or another within my app.  The user can change any given piece of info or
> add new information.  So, I guess
> I could just look for the tables "Casen" and "casenworkhist" to display my
> info.  I do however need to go through
> and calculate all the hours for all open cases and other calculations like
> that.  I want to be sure I'm setting things
> up in a way that will allow me to do this with minimal overhead.
> 
> In your method I would only have 1 workhist table??  whereas in my method I
> would have n workhist tables.  Is it
> better to have only 1 and use a foriegn key like you describe to link work
> history records with a given case??  Is the
> rule generally to minimize the number of tables??

A rule for relational database best practices is to minimize the number of 
tables that are mutually homogeneous in meaning and structure, and to allow or 
exploit multiple tables that are mutually heterogeneous.  If you have a 
conceptual reason for having multiple same-looking tables, then you encode that 
as an extra column in the 1 table.

So for example, the work history details for *all* of your cases would go in 
*1* 
work_history table, not a separate table for each case, and you would have a 
field in work_history called case_number to identify which records of that 
table 
belong to each case.

Given the hierarchy you mention, a table layout like this might work:

   CREATE TABLE cases (
 case_number INTEGER PRIMARY KEY,
 ,
 
   )

   CREATE TABLE workers (
 worker_id INTEGER PRIMARY KEY,
 ...
   )

   CREATE TABLE work_histories (
 work_history_item_number INTEGER PRIMARY KEY,
 case_number INTEGER,
 worker_id INTEGER,
 duration,
 type,
 rate,
 UNIQUE KEY (case_number, worker_id)  # or make this pk instead
 FOREIGN KEY (case_number) REFERENCES cases (case_number),
 FOREIGN KEY (worker_id) REFERENCES workers (worker_id)
   )

By the way, I name my tables in plural to describe what the table as a whole 
represents, (much like how one might name an array variable in a program, which 
is what a table is analogous to save for not being ordered), which is for 
example a collection of cases.  But some people will tell you tables should be 
named after what an individual record in it represents; I disagree with them 
(it 
is like naming an array after what one of its elements is) but it is commonly 
practiced, and its a style issue in the end, the DBMS doesn't care.

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

Re: [sqlite] using EXCEPT with UNION

[mchulet]

Thank you sir


Igor Tandetnik wrote:
> 
> mchulet wrote:
>> Hi,
>>Please need your help in figuring out why this Oracle query does not
>> work in SQLite :
>> (select * from A minus select * from B) union all (select * from B minus
>> select * from B)
>> 
>> I tried the same in SQLite:
>> select * from A EXCEPT select * from B union all select * from B EXCEPT
>> select * from B
>> 
>> but it does not return the union, when I try to use parentheses it throws
>> an
>> error.
> 
> Try this:
> 
> select * from (select * from A EXCEPT select * from B)
> union all
> select * from (select * from B EXCEPT select * from A);
> 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/using-EXCEPT-with-UNION-tp26110941p26135714.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 decide which table is the outer table and which table is the inner table?

[Simon Slavin]

On 30 Oct 2009, at 4:07pm, Kristoffer Danielsson wrote:

> "Some database engines like SQL Server decide which table is the  
> outer table and which table is the inner table"
>
>
> How do I simulate that behavior in SQLite? A misformed SQL statement  
> from the user results in unacceptable lockups...

SQLite already makes that decision.  If you want to enforce which way  
it decides, you specify INNER JOIN or OUTER JOIN just as you do in  
most SQL engines:



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

Re: [sqlite] How to skip the first field on .import

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Ted Rolle wrote:
> How do I let this start out at the default value and auto increment?
> My column separator is '|'.

Create a temporary table and import into that.  Then copy those values into
your permanent table using something like:

  insert into perm_table select null,* from temp_table;

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

iEYEARECAAYFAkrrO90ACgkQmOOfHg372QRNgQCgq/obmjL/Rw862bsqk9GIU4FE
yQYAoLc3JEugW75ZSGPZADTuZNC5Ruww
=CZTr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: datatypes conversion logic error

[Simon Slavin]

On 30 Oct 2009, at 4:31pm, Alexey Pechnikov wrote:

> The datatype conversion logic is not correct and is not compatible  
> with
> other RDBMS.

It is impossible to be compatible with even the three most popular SQL  
DBMSs.  They are not compatible with one-another.  I can show you  
places where MSSQL truncates strings, and Postgres rejects them, and  
the specification isn't clear which on, if either, is doing the wrong  
thing.

However, it's possible to be compatible with the SQL specification,  
and SQLite is pretty good at doing that.  Before you ask, the ways in  
which SQLite fails SQL standard compatibility are not to do with typing.

> I think the text '1' must
> be equal to numeric 1 always like to standart de-facto for RDBMS.


Personally I think that 1 and 1.0 are the same, and that '1' is never  
the same as either.  Some people and some languages feel that 1 is  
never the same as 1.0.  It's all a point of view.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Nicolas Williams]

On Fri, Oct 30, 2009 at 01:30:31PM -0400, John Crenshaw wrote:

+1

I don't think this proposal can or will be accepted.

One reasonable idea, perhaps, would to have a pragma that causes
subsequent CREATE TABLE statements to get automatically generated CHECK
expressions that enforce typing.  Any CHECK expressions in the given
CREATE TABLE statements would have to be wrapped, but that seems simple
enough.

That way you get forwards- and backwards-compatibility for DB files,
schemas, and SQL statements, while while still having the options of
strong and dynamic typing and the ability to mix the two.

And you'd not need any ugly keywords like "STRONG" or "UNIVERSAL"
(which, because SQLite3 accepts any type names, would have compatibility
issues anyways).

You'd still pay for type checking at run-time, even when using strong
type checking.  I'm OK with that.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[John Crenshaw]

> I believe I understand Darren's point (whether or not I care for them
> is another story).

Yes, you've understood Darren for the most part, but clearly don't understand 
the objections.

> On Fri, Oct 30, 2009 at 2:22 AM, Roger Binns  wrote:
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > Darren Duncan wrote:
> >> But on a newer SQLite that implements the stronger typing support I 
> >> proposed,
> >> when that feature is active then columns with declared types like 
> >> INTEGER/etc
> >> would enforce that only values of that type are stored there,
> >
> > I might have misunderstood you.  Do you really mean that a new SQLite
> > version should enforce the types with 'UNIVERSAL' meaning any?  Do you
> > really expect everyone to have to upgrade their database schemas for this?
>
> No, (I think what) Darren is saying is that a column with type
> UNIVERSAL will behave as if that column had no CHECKs at all. It would
> not enforce any type, and behave, more or less, like any SQLite column
> except for INTEGER PRIMARY KEY currently behaves. That is, UNIVERSAL
> would allow storing anything in it.

You said no, then answered yes. This proposal would require many thousands of 
existing schemas to be updated. Anyone who doesn't want the strong typing would 
have to update their schema to use the "UNIVERSAL" keyword. This isn't going to 
be acceptable to ANYBODY except the "strong typing" clan. Additionally, this 
would be quite the shock to users not participating in this thread, who may, 
without prior warning, see new random errors when they update. A likely sore 
spot is the TIMESTAMP which, due to the current lack of documentation and 
supporting APIs, may likely be used to store data in ANY of the 4 types right 
now.

> >
> >> shorthand for an appropriate CHECK constraint,
> >
> > Now I am even more confused.  There is this alleged group of people out
> > there who need type enforcing but are somehow unable to put in CHECK
> > constraints (which also let you addition stuff like range checks and other
> > restrictions on values), so the rest of us would have to start littering our
> > schemas with 'UNIVERSAL' to cater for them?
>
> Any column not declared as UNIVERSAL, so, INTEGER, REAL, BLOB, TEXT,
> perhaps even a new type called DATETIME, would behave as if CHECK
> CONSTRAINT were defined on them, allowing only the declared type of
> data to be stored in them.

Fortunately, I don't think this is exactly what is being proposed. The proposal 
(as I read it) only does the strong type checking on column types it 
recognizes, and others are left to the current model. If strong typing were 
done on any column not declared as UNIVERSAL, this would wreck even more 
schemas, since there are certainly going to be countless schemas using data 
types other than those that would be implemented.

SQLite currently takes *ANYTHING* as the typename. This means, for example, 
someone might choose to give each column a type based on the C/C++ type/class 
that they will use to manipulate it. Not good SQL, but functional SQLite and 
plenty clean. I personally use TIMESTAMP for what you called DATETIME and there 
are who knows how many different variations on that, used by other SQL engines.

> I see no problem with the existing tools, but, on the other hand, I
> really see no problem with Darren's suggestion as well other than it
> might make SQLite less Lite and more Heavy.
>
> But, I certainly see no backward compatibility issues with Darren's
> suggestion. His suggestion allows those who care for strong typing,
> but are too lazy to do it themselves, will actually have it done for
> them, and those who don't care for strong typing can use UNIVERSAL.

This IS a backwards compatibility issue. People get the new version of the 
library, but it behaves substantially differently than the previous version. 
The behavioral difference may break their app, and worse, it will not break at 
compile time, but only at runtime. This is a backwards compatibility sort of 
the worst type.

Worse yet, this could affect users more transparently than you think. Take, for 
example, the web site written in PHP that uses SQLite. One day PHP is updated 
to use the new strongly typed SQLite, then Apache is updated to use the latest 
PHP, finally, one by one, web hosting providers throughout the world update to 
the latest Apache. And a whole rash of websites suddenly have problems. These 
sites changed NOTHING, but suddenly their sites don't work?

I see SERIOUS problems with this proposal, especially in terms of backwards 
compatibility.

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

Re: [sqlite] How to decide which table is the outer table and whichtable is the inner table?

[Griggs, Donald]

 
Re:   How to decide which table is the outer table and whichtable is the
inner table?

Possibly relevant:
http://www.sqlite.org/lang_analyze.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does has FTS3 external tokenizers ability now?

[Alexey Pechnikov]

Hello!

On Friday 30 October 2009 18:39:19 Dan Kennedy wrote:
> > The feature was planning some times ago. Is this released now?
> 
> Yes. See the README.tokenizers file in the full source (tar.gz)
> distribution for docs.

I don't find any about creating tokenizer on Tcl or other lang.
Is it possible?

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: datatypes conversion logic error

[Alexey Pechnikov]

Hello!

On Friday 30 October 2009 18:30:26 Pavel Ivanov wrote:
> 
> You think words "SQLite is backwards compatible" describe
> implementation in some way? If this sentence is removed from the
> documentation you will be happier and finally read the whole document
> to understand how SQLite's type system work in details?

The datatype conversion logic is not correct and is not compatible with
other RDBMS. So the declared tasks of the SQLite datatypes system is 
not performed and document is not consistent. I think the text '1' must
be equal to numeric 1 always like to standart de-facto for RDBMS.

> I agree that this statement can be a bit too much general and is not
> applicable in some cases. But all cases are explained in the rest of
> the document, so I think it's not quite right to extract just this one
> statement and based on it accuse the whole SQLite that it doesn't work
> correctly.

I did find some datatypes bugs in tclsqlite interface and fix it as
http://geomapx.blogspot.com/2009/10/tclsqlite.html
Note: the original code produce different results from tclsh shell and from 
tcl script file.

As you can see the datatyping is not ideal now.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to decide which table is the outer table and which table is the inner table?

[Pavel Ivanov]

Of course SQLite wasn't changed much in this part since November 2008
but the citation you gave is either wrong or the key words in it are
"something like" in phrase "SQlite does something like this". Because
SQLite is smart enough to choose smaller table as an outer one and
bigger table as an inner one. Although it can choose other way round
if you have index on Id in smaller table and don't have index on Id in
bigger table. And in this case there's no performance hit in such
decision, only benefit.

Pavel

On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson
 wrote:
>
> Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx
>
>
>
> SQLite uses only nested loops to implement joins. Given a query like the 
> following:
>
> SELECT ...
> FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id
>
>
>
> SQlite does something like this:
>
>
>
> for each row in OuterTable
>  Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
> end for each
>
>
>
> I assume SQLite has not improved on JOIN precedence since then, which means 
> that if OuterTable is HUGE, there will be an huge performance hit!
>
>
>
> "Some database engines like SQL Server decide which table is the outer table 
> and which table is the inner table"
>
>
> How do I simulate that behavior in SQLite? A misformed SQL statement from the 
> user results in unacceptable lockups...
>
> _
> Nya Windows 7 - Hitta en dator som passar dig! Mer information.
> http://windows.microsoft.com/shop
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to decide which table is the outer table and which table is the inner table?

[Kristoffer Danielsson]

Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx

 

SQLite uses only nested loops to implement joins. Given a query like the 
following:

SELECT ...
FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id

 

SQlite does something like this:

 

for each row in OuterTable
 Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
end for each

 

I assume SQLite has not improved on JOIN precedence since then, which means 
that if OuterTable is HUGE, there will be an huge performance hit!

 

"Some database engines like SQL Server decide which table is the outer table 
and which table is the inner table"


How do I simulate that behavior in SQLite? A misformed SQL statement from the 
user results in unacceptable lockups...
  
_
Nya Windows 7 - Hitta en dator som passar dig! Mer information.
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does has FTS3 external tokenizers ability now?

[Dan Kennedy]

On Oct 30, 2009, at 10:07 PM, Alexey Pechnikov wrote:

> Hello!
>
> The feature was planning some times ago. Is this released now?

Yes. See the README.tokenizers file in the full source (tar.gz)
distribution for docs.

Dan.

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

Re: [sqlite] BUG: datatypes conversion logic error

[Pavel Ivanov]

You think words "SQLite is backwards compatible" describe
implementation in some way? If this sentence is removed from the
documentation you will be happier and finally read the whole document
to understand how SQLite's type system work in details?

I agree that this statement can be a bit too much general and is not
applicable in some cases. But all cases are explained in the rest of
the document, so I think it's not quite right to extract just this one
statement and based on it accuse the whole SQLite that it doesn't work
correctly.

Pavel

On Fri, Oct 30, 2009 at 11:19 AM, Alexey Pechnikov
 wrote:
> Hello!
>
> On Friday 30 October 2009 18:03:29 Pavel Ivanov wrote:
>> Don't use word 'terrible' for things that you don't understand
>> completely. To understand it read once more about SQLite datatypes,
>> affinity and about cases when datatype is changed automatically in
>> expressions: http://www.sqlite.org/datatype3.html. It will explain to
>> you why 1 ='1' doesn't work but a = 1 and a = '1' work.
>
> Please see paragraph "Datatypes In SQLite Version 3" in the document.
> This speaking about "The dynamic type system of SQLite is  backwards
> compatible with the more common static type systems". But some tests
> show the serious compatible problem.
>
>> Although I can admit that your case with trigger and my own tests show
>> that for some reason affinity rules do not work in the "instead of"
>> trigger but work in simple selects and all other types of triggers
>> (I've tested "after insert" and "before insert").
>
> Yes, there are some differents. But documentation of SQLite datatypes is not
> right correspond to the realization. So documentation is wrong or realization
> is wrong.
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to skip the first field on .import

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ted Rolle
Sent: Friday, October 30, 2009 10:36 AM
To: sqlite-users
Subject: [sqlite] How to skip the first field on .import

The first field in my table is ID primary integer autoincrement.

=> Do you instead mean "INTEGER PRIMARY KEY AUTOINCREMENT" ?

I read that if it is set to NULL it defaults to the maximum value
possible.  Not a Good Thing(tm).

=> Can you post the url where you read this?   From page 
http://www.sqlite.org/autoinc.html
it says something quite different:

If no ROWID is specified on the insert, an appropriate ROWID is
created automatically. The usual algorithm is to give the newly created
row a ROWID that is one larger than the largest ROWID in the table prior
to the insert. If the table is initially empty, then a ROWID of 1 is
used. If the largest ROWID is equal to the largest possible integer
(9223372036854775807) then the database engine starts picking candidate
ROWIDs at random until it finds one that is not previously used. 



How do I let this start out at the default value and auto increment?
My column separator is '|'.

=>  I'm guessing you're using the command-line utility program.
Others may have better answers, but:
  -- If you're restoring a table you've dumped previously, then you
probably want the primary key values to be set explicitly to their
previous values -- i.e., you don't want them to take on new values.
  -- If this is an initial data import, one way to do this is to
".import" to a temporary table withOUT the ID key, then use an sql
command to transfer the data to the permanent table (which DOES use
INTEGER PRIMARY KEY AUTOINCREMENT), e.g.
INSERT INTO myPerm 
  SELECT NULL, * FROM myTemp

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

Re: [sqlite] BUG: datatypes conversion logic error

[Alexey Pechnikov]

Hello!

On Friday 30 October 2009 18:03:29 Pavel Ivanov wrote:
> Don't use word 'terrible' for things that you don't understand
> completely. To understand it read once more about SQLite datatypes,
> affinity and about cases when datatype is changed automatically in
> expressions: http://www.sqlite.org/datatype3.html. It will explain to
> you why 1 ='1' doesn't work but a = 1 and a = '1' work.

Please see paragraph "Datatypes In SQLite Version 3" in the document. 
This speaking about "The dynamic type system of SQLite is  backwards 
compatible with the more common static type systems". But some tests 
show the serious compatible problem.

> Although I can admit that your case with trigger and my own tests show
> that for some reason affinity rules do not work in the "instead of"
> trigger but work in simple selects and all other types of triggers
> (I've tested "after insert" and "before insert").

Yes, there are some differents. But documentation of SQLite datatypes is not 
right correspond to the realization. So documentation is wrong or realization
is wrong.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does has FTS3 external tokenizers ability now?

[Alexey Pechnikov]

Hello!

The feature was planning some times ago. Is this released now?

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: datatypes conversion logic error

[Pavel Ivanov]

> So 1 can be equal to '1' and can be not. It's terrible behaviour.

Don't use word 'terrible' for things that you don't understand
completely. To understand it read once more about SQLite datatypes,
affinity and about cases when datatype is changed automatically in
expressions: http://www.sqlite.org/datatype3.html. It will explain to
you why 1 ='1' doesn't work but a = 1 and a = '1' work.

Although I can admit that your case with trigger and my own tests show
that for some reason affinity rules do not work in the "instead of"
trigger but work in simple selects and all other types of triggers
(I've tested "after insert" and "before insert").


Pavel

On Fri, Oct 30, 2009 at 10:55 AM, Alexey Pechnikov
 wrote:
> Hello!
>
> $ sqlite3
> SQLite version 3.6.19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select 1='1';
> 0
> sqlite> create table test(a text);
> sqlite> insert into test values (1);
> sqlite> select * from test where a='1';
> 1
> sqlite> select * from test where a=1;
> 1
>
> So 1 can be equal to '1' and can be not. It's terrible behaviour.
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature proposal - strong but dynamic typing

[Simon Slavin]

On 30 Oct 2009, at 2:14pm, P Kishor wrote:

> Actually, there can be one bad effect of Darren's suggestion, now that
> I think of it, and that would be for those who don't care for strong
> typing. They will end up getting strong typing for all non-UNIVERSAL
> columns whether they like it or not, whether they expect it or not,
> unless there is a pragma as well to just disable strong typing
> completely. See, it is getting less Lite and more Heavy. At this
> point, those who are dissatisfied with SQLite should just move to
> PostGres of MySQL.

Yes.  The proper way to implement strong typing, for those who want  
it, is to introduce a keyword for strong typing, not change the  
meaning of existing keywords.  So for instance you could define

numberOrdered   INTEGER
numberSent  STRONG INTEGER

or something like that.  Without the word 'STRONG' column types would  
continue to mean what they've always meant.

But as others have said here, my opinion is that the enforcement of  
typing is not the job of a database engine, it's the job of the  
application, which can check types at the same time as it checks 'not- 
negative', and 'must-contain-only-printable-characters'.  It's a waste  
putting strong types into SQLite when late (or weak) typing is so much  
more powerful.

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

Re: [sqlite] BUG: datatypes conversion logic error

[Alexey Pechnikov]

Hello!

$ sqlite3
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select 1='1';
0
sqlite> create table test(a text);
sqlite> insert into test values (1);
sqlite> select * from test where a='1';
1
sqlite> select * from test where a=1;
1

So 1 can be equal to '1' and can be not. It's terrible behaviour.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to skip the first field on .import

[P Kishor]

On Fri, Oct 30, 2009 at 9:35 AM, Ted Rolle  wrote:
> The first field in my table is ID primary integer autoincrement.

First, change the above to

ID INTEGER PRIMARY KEY


> I read that if it is set to NULL it defaults to the maximum value
> possible.  Not a Good Thing(tm).

I have no idea where you read that, and what the above means, but look
at the following --

SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b TEXT);
sqlite> INSERT INTO foo (a, b) VALUES (1, 'blah');
sqlite> INSERT INTO foo (b) VALUES ('more blah');
sqlite> INSERT INTO foo (a, b) VALUES (NULL, 'even more blah');
sqlite> SELECT * FROM foo;
a   b
--  --
1   blah
2   more blah
3   even more
sqlite>


I didn't get any "maximum value possible"


>
> How do I let this start out at the default value and auto increment?
> My column separator is '|'.
>
> Ted
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite Qt problem

[Nataraj S Narayan]

Hi

I am using sqlite3 from Qt

have added a trigger to a table
i have setup an Abort  message in the trigger

My problem is that i am unable to get the message from a qt app, while
i am getting an exact message from Ruby language -

"db.execute( "insert into param_details (param_code,param_value)
values (?,?)",12,'786786')
SQLite3::SQLException: First Digit Should Be 9"


But in Qt

Getting "Error: library routine called out of sequence "

believe something wrong with at

sqlite3 *handle = *static_cast(v.data()); in


   1.
QString StrQuery = "insert into
PARAM_DETAILS(PARAM_CODE,PARAM_VALUE) values
   2.
  (12,'8443211326')";
   3.

   4.
  /* if(!(q5.exec(StrQuery)))
   5.
 {
   6.
   qDebug() << q5.lastError();
   7.
 }*/
   8.
if(!(q5.exec(StrQuery)))
   9.
  {
  10.
  QVariant v = q5.driver()->handle();
  11.

  12.
  if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*")==0)
  13.
  {
  14.
  // v.data() returns a pointer to the handle
  15.
  sqlite3 *handle = *static_cast(v.data());
  16.
  if (handle != 0)
  17.
  { // check that it is not NULL
  18.
  qDebug("Error: %s", sqlite3_errmsg(handle));
  19.
  QMessageBox::about(0,"Hello",sqlite3_errmsg(handle));
  20.
  }
  21.
  }
  22.
  }

please help

regards

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Scott Hess]

On Fri, Oct 30, 2009 at 7:14 AM, P Kishor  wrote:
> Actually, there can be one bad effect of Darren's suggestion, now that
> I think of it, and that would be for those who don't care for strong
> typing. They will end up getting strong typing for all non-UNIVERSAL
> columns whether they like it or not, whether they expect it or not,
> unless there is a pragma as well to just disable strong typing
> completely.

If someone were to implement this, the appropriate place is not in the
schema definition, the appropriate place is as a PRAGMA, perhaps
PRAGMA strong_typing.  When defined, schema would be transformed at
table creation time to add the appropriate checks.  With this
implementation you could also more easily compile the support out of
SQLite, because it's not a change to syntax, it's a change to how
tables are created, so when compiled out you get exactly the SQLite
you had before the code was added.  Another alternate might be "CREATE
PEDANTIC TABLE xxx", because then the only footprint is that
additional keyword, though I think PRAGMA is a lot more clear for
optional features.

That said, I agree with others that the feature is mis-guided.  If you
think that a column should be an integer, then when you store things
to that column use sqlite3_bin_int().  As an application developer,
you should NEVER let users define the type of data stored in your
database in the first place, you should ALWAYS explicitly validate
their input.  You could use this kind of type-checking to keep people
developing against your schema from doing bad things, but given the
nature of SQLite it's hard to see how useful that would be in practice
(versus a client-server system where many people may be sharing the
same production database instance).

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

[sqlite] How to skip the first field on .import

[Ted Rolle]

The first field in my table is ID primary integer autoincrement.
I read that if it is set to NULL it defaults to the maximum value
possible.  Not a Good Thing(tm).

How do I let this start out at the default value and auto increment?
My column separator is '|'.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Doug Currie]

On Oct 30, 2009, at 10:14 AM, P Kishor wrote:

> Actually, there can be one bad effect of Darren's suggestion, now that
> I think of it, and that would be for those who don't care for strong
> typing. They will end up getting strong typing for all non-UNIVERSAL
> columns whether they like it or not, whether they expect it or not,
> unless there is a pragma as well to just disable strong typing
> completely.

On Oct 29, 2009, at 5:33 PM, Darren Duncan wrote:

> Support for what I indicated could conceivably just be added like  
> how support
> for foreign keys was just added, and it could be turned on/off with  
> a pragma
> likewise to aid backwards compatibility, for people who wrote the  
> column types
> in their SQL but expected enforcement to be lax.

e

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

Re: [sqlite] feature proposal - strong but dynamic typing

[P Kishor]

I believe I understand Darren's point (whether or not I care for them
is another story).

On Fri, Oct 30, 2009 at 2:22 AM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Darren Duncan wrote:
>> But on a newer SQLite that implements the stronger typing support I proposed,
>> when that feature is active then columns with declared types like INTEGER/etc
>> would enforce that only values of that type are stored there,
>
> I might have misunderstood you.  Do you really mean that a new SQLite
> version should enforce the types with 'UNIVERSAL' meaning any?  Do you
> really expect everyone to have to upgrade their database schemas for this?

No, (I think what) Darren is saying is that a column with type
UNIVERSAL will behave as if that column had no CHECKs at all. It would
not enforce any type, and behave, more or less, like any SQLite column
except for INTEGER PRIMARY KEY currently behaves. That is, UNIVERSAL
would allow storing anything in it.



>
>> shorthand for an appropriate CHECK constraint,
>
> Now I am even more confused.  There is this alleged group of people out
> there who need type enforcing but are somehow unable to put in CHECK
> constraints (which also let you addition stuff like range checks and other
> restrictions on values), so the rest of us would have to start littering our
> schemas with 'UNIVERSAL' to cater for them?

Any column not declared as UNIVERSAL, so, INTEGER, REAL, BLOB, TEXT,
perhaps even a new type called DATETIME, would behave as if CHECK
CONSTRAINT were defined on them, allowing only the declared type of
data to be stored in them.


>
> I have yet to see a clear demonstration of two things:
>
> Why developers who want particular type/value constraints are unable to just
> go ahead and use constraints?

There is really no understandable reason for this other that perhaps
psychology and a bad kind of laziness.

>
> Why developers who want 'strong types' don't realise that modulo type
> affinity you get out what you put in so don't put in "wrong" types!
>
> In short what problem actually needs to be solved and what is wrong with the
> existing tools for those who have the problem?

I see no problem with the existing tools, but, on the other hand, I
really see no problem with Darren's suggestion as well other than it
might make SQLite less Lite and more Heavy.

But, I certainly see no backward compatibility issues with Darren's
suggestion. His suggestion allows those who care for strong typing,
but are too lazy to do it themselves, will actually have it done for
them, and those who don't care for strong typing can use UNIVERSAL.

Actually, there can be one bad effect of Darren's suggestion, now that
I think of it, and that would be for those who don't care for strong
typing. They will end up getting strong typing for all non-UNIVERSAL
columns whether they like it or not, whether they expect it or not,
unless there is a pragma as well to just disable strong typing
completely. See, it is getting less Lite and more Heavy. At this
point, those who are dissatisfied with SQLite should just move to
PostGres of MySQL.


>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkrqlEEACgkQmOOfHg372QTCPACgkdvchMq2NzAU7n4cSKXABUNF
> YGMAn3buLfY4gfVoEeyeTYGA2UC1I4dL
> =3FL+
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

[Sam Carleton]

I believe that is the one.  It is possible to run the XULRunner app stand
alone.  Go to the Firefox web site for details on exactly how to do that.
 What I did was start up Firefox, go to the add-ins, search for SQLite and
install it into Firefox.  Then in the tools menu there is a link to start
the application.

Sam

On Fri, Oct 30, 2009 at 8:44 AM, Ted Rolle  wrote:

> On Fri, 30 Oct 2009 13:25:50 +0100
> Gilles Ganault  wrote:
>
> > On Mon, 26 Oct 2009 15:07:38 -0400, Sam Carleton
> >  wrote:
> > >There is a WONDERFUL Firefox plug-in that I use, I love it!  I am
> > >guessing it works on all OS's but I use it on Windows Vista and
> > >Windows 7.
> >
> > Thanks. I assume it's the SQLite Manager plug-in:
> >
> > http://code.google.com/p/sqlite-manager/
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> Got it.  It's a file on my desktop.  How do I install it?
>
> Ted
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table within a table??

[P Kishor]

On Fri, Oct 30, 2009 at 2:41 AM, mark m  wrote:
> Thanks very much!!  It also occurred to me that I could have a Table named
> "case1" and another
> named "case1workhist".  The RDBMS wouldn't know they were related but my
> application could be
> set up to know this.

Expand Darren's suggestion with the following --

CREATE TABLE worker (
worker_id INTEGER PRIMARY KEY,
b TEXT
  );

  CREATE TABLE work_history (
worker_id INTEGER,
c INTEGER,
d TEXT,
CONSTRAINT FOREIGN KEY (worker_id) REFERENCING worker (worker_id)
case_id INTEGER
  );

CREATE TABLE cases (
case_id INTEGER PRIMARY KEY,
e TEXT
  );

So, above I have added a table for cases where you can store your case
history, and then, in your work_history table, added a case_id FK.


>
> Here is more detail on my current data organization:
>
> Open Cases
>   Case 1...
>   Case 2...
>      field 1
>      field 2
>      work history
>         item 1
>         item 2
>            worker ID
>            duration
>            type
>            rate
>         item 3
>         ...
>         ...
>         item n
>   Case 3
>   ...
>   ...
>   Case n
>
> In my app, a certain case is chosen to be displayed.  All of the above
> information is displayed in one form
> or another within my app.  The user can change any given piece of info or
> add new information.  So, I guess
> I could just look for the tables "Casen" and "casenworkhist" to display my
> info.  I do however need to go through
> and calculate all the hours for all open cases and other calculations like
> that.  I want to be sure I'm setting things
> up in a way that will allow me to do this with minimal overhead.
>
> In your method I would only have 1 workhist table??  whereas in my method I
> would have n workhist tables.  Is it
> better to have only 1 and use a foriegn key like you describe to link work
> history records with a given case??  Is the
> rule generally to minimize the number of tables??
>
> Mark
>
> On Fri, Oct 30, 2009 at 2:44 AM, Darren Duncan wrote:
>
>> mark m wrote:
>> > I'm very new to database programming so this question is pretty basic
>> >
>> > I have data that is currently organized as follows:
>> >
>> > Each case has several fields that contain only one value.  There are
>> several
>> > fields that have a pipe-delimited string
>> > that represents a work history.  Each work history item has its own
>> fields
>> > like data, worker ID etc.  So, as I convert
>> > my text files over to database format, I find myself wanting to have a
>> table
>> > within a table.  So, ideally the case table would
>> > have several singular fields and a field named "work history" that would
>> > contain a table that would have all of the work history
>> > in it.
>> >
>> > But, I haven't found a way to do this.  If this is not possible, what
>> would
>> > be the best way to organize data such as this in
>> > a database program.
>> >
>> > Thanks for the help.
>> >
>> > Mark
>>
>> What you are talking about is perfectly reasonable from a logical
>> standpoint,
>> and in the relational model the feature would be called "relation-valued
>> attributes" or "RVAs".  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.  Logically speaking, RVAs are what you
>> get as
>> the intermediate stage of a "GROUP BY", and are the input for aggregate
>> operators like SUM()/COUNT()/MIN()/MAX()/etc.
>>
>> However, SQLite and many other SQL DBMSs don't support RVAs, so you'll have
>> to
>> use a logically equivalent arrangement of 2 sibling tables that have a
>> parent/child (say, "worker"/"work_history") foreign-key relationship.
>>
>> That is, instead of this kind of schema (in pseudocode):
>>
>>   var worker : TABLE {
>>     worker_id : INT, b : TEXT, work_history : TABLE { c : INT, d : TEXT }
>>   }
>>
>> ... you have this kind of schema:
>>
>>   var worker : TABLE { worker_id : INT, b : TEXT }
>>   var work_history : TABLE { worker_id : INT, c : INT, d : TEXT }
>>
>> ... and work_history.worker_id has a foreign key constraint on
>> worker.worker_id
>> .  The SQL to do the latter is approximately:
>>
>>   CREATE TABLE worker (
>>     worker_id INTEGER PRIMARY KEY,
>>     b TEXT
>>   );
>>
>>   CREATE TABLE work_history (
>>     worker_id INTEGER,
>>     c INTEGER,
>>     d TEXT,
>>     CONSTRAINT FOREIGN KEY (worker_id) REFERENCING worker (worker_id)
>>   );
>>
>> You would have a record in "worker" for each distinct "worker_id" and that
>> record contains all the details that aren't part of the work history.  Then
>> you
>> have a record in "work_history" for each record that would have been in the
>> inner table of "worker" had it existed, and you have the added "worker_id"
>> field
>> in "work_history" for every history row that would refer to the same
>> worker.
>> Having the same "worker_id" 

Re: [sqlite] how to get the Trigger's Raise err in Application

[greensparker]

Jan, 
my system dont have any file named llibsqlite , in my filesystem
but i have
   /usr/lib/libsqlite.so
   /usr/lib/libsqlite.so.0
   /usr/lib/libsqlite.so.0.8.6
   /usr/lib/libsqlite3.so
   /usr/lib/libsqlite3.so.0.8.6

my pro file have

LIBS += /usr/lib/libsqlite.so
INCLUDE +=/usr/include
QT += sql
HEADERS += mainMenu.h
SOURCES += main.cpp \
mainMenu.cpp

And getting the following err
mainMenu.o: In function `mainMenu::createMainMenu()':
/home/bala/test1/mainMenu.cpp:69: undefined reference to `sqlite3_errmsg'
collect2: ld returned 1 exit status
make: *** [test] Error 1


jan-118 wrote:
> 
> afaik on unix you need this
> 
> LIBS += -L/usr/lib -llibsqlite
> 
> 
> 
> greensparker schrieb:
>> Im in Debian Linux.,
>> This is my pro file
>> 
>> QT += sql
>> LIBS +=/usr/lib/libsqlite.so
>> INCLUDE +=/usr/include
>> HEADERS += mainMenu.h
>> SOURCES += main.cpp \
>> mainMenu.cpp
>> 
>> headers in my mainMenu.cpp file
>> 
>> #include "mainMenu.h"
>> #include 
>> #include "DBConnection.h"
>> #include// path = usr/local/include/sqlite3.h
>> 
>> same err im getting 
>> :69: undefined reference to `sqlite3_errmsg'
>> collect2: ld returned 1 exit status
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/how-to-get-the-Trigger%27s-Raise-err-in-Application-tp26091341p26129984.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] BUG: datatypes conversion logic error

[Alexey Pechnikov]

Hello!

The documentation speak
"Datatypes In SQLite Version 3
The dynamic type system of SQLite is backwards compatible with the more common 
static type systems of other database engines in the sense that SQL statement 
that work on statically typed databases should would the same way in SQLite."

But is't wrong and SQLite type system is incompatible with other databases and 
produce logic errors.

SQLite is typeless database but

$ sqlite3 :memory:
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select 1='1';
0


PostgreSQL is strict typing database but

$ psql -h localhost --cluster 8.1/testing -U postgres template1

template1=> select 1='1';
 ?column?
--
 t
(1 row)


In SQLite selects, views and triggers on views there are a lot of problems as 
result of the bug.
As example the trigger is work incorrect for _numeric_ group_name because the 
group_name field is defined as text:

CREATE TRIGGER view_user_service_hw_insert instead of insert on 
view_user_service_hw
begin
select RAISE (ABORT,'Service does not found')
  WHERE (select count(id) from user_service where id=NEW.user_service_id 
and delete_date IS NULL)=0;

select RAISE (ABORT,'The hardware is used yet')
  WHERE (select count(id) from view_user_service_hw where 
user_service_id=NEW.user_service_id and group_name=NEW.group_name and 
delete_date IS NULL)>0;

insert into user_service_hw
  (user_service_id,hw_id)
select
  NEW.user_service_id, id from hw_telephony_number where 
group_name=NEW.group_name and 'telephony_number'=(select hw_name from 
view_user_service where id=NEW.user_service_id);
end;

And we must patch this like to

CREATE TRIGGER view_user_service_hw_insert instead of insert on 
view_user_service_hw
begin
select RAISE (ABORT,'Service does not found')
  WHERE (select count(id) from user_service where id=NEW.user_service_id 
and delete_date IS NULL)=0;

select RAISE (ABORT,'The hardware is used yet')
  WHERE (select count(id) from view_user_service_hw where 
user_service_id=NEW.user_service_id and group_name=cast(NEW.group_name as text) 
and delete_date IS NULL)>0;

insert into user_service_hw
  (user_service_id,hw_id)
select
  NEW.user_service_id, id from hw_telephony_number where 
group_name=cast(NEW.group_name as text) and 'telephony_number'=(select hw_name 
from view_user_service where id=NEW.user_service_id);
end;

There are no same problems in PostgreSQL and so SQLite has incompatible SQL.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get the Trigger's Raise err in Application

[Jan]

afaik on unix you need this

LIBS += -L/usr/lib -llibsqlite



greensparker schrieb:
> Im in Debian Linux.,
> This is my pro file
> 
> QT += sql
> LIBS +=/usr/lib/libsqlite.so
> INCLUDE +=/usr/include
> HEADERS += mainMenu.h
> SOURCES += main.cpp \
> mainMenu.cpp
> 
> headers in my mainMenu.cpp file
> 
> #include "mainMenu.h"
> #include 
> #include "DBConnection.h"
> #include// path = usr/local/include/sqlite3.h
> 
> same err im getting 
> :69: undefined reference to `sqlite3_errmsg'
> collect2: ld returned 1 exit status
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get the Trigger's Raise err in Application

[greensparker]

Im in Debian Linux.,
This is my pro file

QT += sql
LIBS +=/usr/lib/libsqlite.so
INCLUDE +=/usr/include
HEADERS += mainMenu.h
SOURCES += main.cpp \
mainMenu.cpp

headers in my mainMenu.cpp file

#include "mainMenu.h"
#include 
#include "DBConnection.h"
#include// path = usr/local/include/sqlite3.h

same err im getting 
:69: undefined reference to `sqlite3_errmsg'
collect2: ld returned 1 exit status

-- 
View this message in context: 
http://old.nabble.com/how-to-get-the-Trigger%27s-Raise-err-in-Application-tp26091341p26129536.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] [Windows] Good GUI alternative to sqlite.exe?

[Ted Rolle]

On Fri, 30 Oct 2009 13:25:50 +0100
Gilles Ganault  wrote:

> On Mon, 26 Oct 2009 15:07:38 -0400, Sam Carleton
>  wrote:
> >There is a WONDERFUL Firefox plug-in that I use, I love it!  I am
> >guessing it works on all OS's but I use it on Windows Vista and
> >Windows 7.
> 
> Thanks. I assume it's the SQLite Manager plug-in:
> 
> http://code.google.com/p/sqlite-manager/
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Got it.  It's a file on my desktop.  How do I install it?

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

Re: [sqlite] SQLite server for Windows/Linux?

[Gilles Ganault]

On Mon, 26 Oct 2009 15:10:05 -0400, Reid Thompson
 wrote:
>may be of interest  
>http://sqlrelay.sourceforge.net/

Thanks for the link. Unfortunately, it doesn't seem to support Windows
yet.

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

Re: [sqlite] SQLite on a Windows Network

[Gilles Ganault]

On Fri, 30 Oct 2009 01:47:33 -0400, mark m
 wrote:
>I have heard problems with SQLite and NFS but I have no idea if a standard
>Windows shared drive uses NFS or not.  Am I o.k. to use SQLite???

http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

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

Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

[Gilles Ganault]

On Mon, 26 Oct 2009 15:07:38 -0400, Sam Carleton
 wrote:
>There is a WONDERFUL Firefox plug-in that I use, I love it!  I am guessing
>it works on all OS's but I use it on Windows Vista and Windows 7.

Thanks. I assume it's the SQLite Manager plug-in:

http://code.google.com/p/sqlite-manager/

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

Re: [sqlite] how to get the Trigger's Raise err in Application

[Jan]

If your are on windows try this in your .pro file:

win32:LIBS += [yourpath]\libsqlite.lib
win32:INCLUDEPATH += [yourpath]\include

Check Qt docs on qmake for unix.

Jan

greensparker schrieb:
> jan Thnks fr ur support. I have included sqlite3.h.
> But getting the following err.
> 
> 69: undefined reference to`sqlite3_errmsg'
> 
> :-1: error: collect2: ld returned 1 exit status
> 
> How to resolve?
> 
> Thnks
> Bala
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on a Windows Network

[Simon Slavin]

On 30 Oct 2009, at 5:47am, mark m wrote:

> I have heard problems with SQLite and NFS but I have no idea if a  
> standard
> Windows shared drive uses NFS or not.  Am I o.k. to use SQLite???

Standard Windows drive sharing uses SMB, sometimes called SAMBA.  NFS  
is not involved.

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

Re: [sqlite] How to input a double num?

[liubin liu]

Thank you!

I mayn't need the high precision like
"212345678901234567890123456.988290112".



John Crenshaw-2 wrote:
> 
>> May I use sqlite3_bind_double() and sqlite3_prepare_v2() to solve the
>> problem.
> 
> That won't fix it. Your number is too large to fit in any native data
> type. Even the plain math inside your own program won't work right,
> because the precision of the number is limited at the C level, not the
> SQLite or printf level.
> 
> You'll need an arbitrary precision math library, sqlite3_bind_blob(),
> and sqlite3_prepare_v2(). You can't use sqlite3_bind_double() because
> your number is too big for a double.
> 
> FYI, those arbitrary precision libraries are brutes, so brace yourself.
> 
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/How-to-input-a-double-num--tp26105457p26127343.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] problem with inserting non NULL values into sqlite table

[TTTTT]

hey! Thank you very much.. 
i wanted to insert string and i have forgotten about a fact i need to use
these quotes  ' '.. 
that has solved the problem...
Thank you..



Nataraj, 
 
i am using sqlite3_exec function to get error... and yes, call back is one
of arguments passed to the function..
check this:   http://www.sqlite.org/c3ref/exec.html  



-- 
View this message in context: 
http://old.nabble.com/problem-with-inserting-non-NULL-values-into-sqlite-table-tp26114852p26127196.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] Some clarification needed about Unicode

[John Crenshaw]

> http://codesnipers.com/?q=utf-8-versus-windows-unicode
> 
> The author asset that .NET is the only platform that offer full UTF-16
> support in the Windows API.

The author is half mistaken, as was I. Michael Kaplan and Raymond Chen
(big MS names many will recognize) clarified this.  For Win2k, only
UCS-2 is supported. Starting with XP, the Win32 APIs accept full UTF-16.
On any version, MultiByteToWideChar() should return data in the proper
encoding for that system. Igor and the others helped hash this through
until the real answer could be found.

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

Re: [sqlite] Idea for improving page cache

[John Crenshaw]

> Just for the sake of discussion I've attached a performance
> graph for various C++ data structures plus the Unrolled LL.
> The tests where run on a dell vostro 1400 laptop. As you can
> see the graphs show the ULL to be quite efficient for
> insert/delete from the front/back of the list. I beleive this
> is mainly due to the fact that a new node is not allocated
> for the insert for each operation.

Yes, a stack would be a good use for ULL because front/back
insert/delete can be highly efficient, and you can afford 0 wasted
space.

I'd love to see the actual data you tried to attach, but I couldn't
because the attachment was blank except for a message footer.

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

Re: [sqlite] how to get the Trigger's Raise err in Application

[greensparker]

jan Thnks fr ur support. I have included sqlite3.h.
But getting the following err.

69: undefined reference to`sqlite3_errmsg'

:-1: error: collect2: ld returned 1 exit status

How to resolve?

Thnks
Bala
-- 
View this message in context: 
http://old.nabble.com/how-to-get-the-Trigger%27s-Raise-err-in-Application-tp26091341p26126588.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] Some clarification needed about Unicode

[A.J.Millan]

- Original Message - 
From: "John Crenshaw" 
To: "General Discussion of SQLite Database" 
Sent: Thursday, October 29, 2009 10:55 PM
Subject: Re: [sqlite] Some clarification needed about Unicode


>No, I mean which encoding. You can't give a UTF-16 string to an API that
>only knows how to handle UCS-2 encoded data, just like you can't use a
>UTF-8
>string when ASCII data is expected. When I tackle this nightmare the last
>time I was left with the understanding that the wide Win32 APIs expected
>data to be UCS-2 encoded. Now I'm no longer sure, and I can't find any
>reliable documentation on this either way. It would be good if the APIs
>accept UTF-16, because that would mean they also accept UCS-2, but I
>couldn't find anything reliable to support this idea. Some folks say yes.
>Some say no. The documentation says nothing.

John:

Perhaps this page do some clarification on the point. Anyway, the only clear
is that the matter is not absolutely clear. Of course that is a big
consolation those us who feel lost the first time who went to this oddity
matter of the internationalization.

http://codesnipers.com/?q=utf-8-versus-windows-unicode

The author asset that .NET is the only platform that offer full UTF-16
support in the Windows API.

A.J.Millan.

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

Re: [sqlite] How to input a double num?

[John Crenshaw]

> May I use sqlite3_bind_double() and sqlite3_prepare_v2() to solve the
> problem.

That won't fix it. Your number is too large to fit in any native data
type. Even the plain math inside your own program won't work right,
because the precision of the number is limited at the C level, not the
SQLite or printf level.

You'll need an arbitrary precision math library, sqlite3_bind_blob(),
and sqlite3_prepare_v2(). You can't use sqlite3_bind_double() because
your number is too big for a double.

FYI, those arbitrary precision libraries are brutes, so brace yourself.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[John Crenshaw]

Been watching this discussion go back and forth, and I'd like to weigh
in.

I'm generally a HUGE fan of strong typing, but this doesn't do it for
me. To me, strongly typed means a compiler catches my type mismatches
before the app goes out the door. In this case though, no matter what
you do, a mistake with your use of a type can never show up at compile
time. You'll only get the error at runtime.

Frankly, once the mistake is made, and the code deployed, I expect the
program to behave the best it possibly can. Making the application blow
up, rather than storing the data and moving on, is not a great plan in
my mind. The user doesn't *care* that a REAL was provided, but an
INTEGER was expected. The user cares that the app exploded on them and
didn't save the edit they just made.

If strong data typing *at the SQL level* is that important to you, this
is a job for constraints:
CHECK(typeof(product_id) = 'integer')

If you hate the idea of manually adding these constraints, you could
even write something to automatically add them for you. If you hate the
performance hit, you could add them only in your debug build.

As far as making the core universally reject data that doesn't match its
belief about the format that data should be in, I think this is a very
bad idea. It would break plenty of old code to provide a feature that is
already possible for which the benefit is questionable anyway. Since
SQLite can't catch my type errors at application compile time anyway, I
think I like the current behavior better.

Just my 2 cents.

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

Re: [sqlite] Table within a table??

[mark m]

Thanks very much!!  It also occurred to me that I could have a Table named
"case1" and another
named "case1workhist".  The RDBMS wouldn't know they were related but my
application could be
set up to know this.

Here is more detail on my current data organization:

Open Cases
   Case 1...
   Case 2...
  field 1
  field 2
  work history
 item 1
 item 2
worker ID
duration
type
rate
 item 3
 ...
 ...
 item n
   Case 3
   ...
   ...
   Case n

In my app, a certain case is chosen to be displayed.  All of the above
information is displayed in one form
or another within my app.  The user can change any given piece of info or
add new information.  So, I guess
I could just look for the tables "Casen" and "casenworkhist" to display my
info.  I do however need to go through
and calculate all the hours for all open cases and other calculations like
that.  I want to be sure I'm setting things
up in a way that will allow me to do this with minimal overhead.

In your method I would only have 1 workhist table??  whereas in my method I
would have n workhist tables.  Is it
better to have only 1 and use a foriegn key like you describe to link work
history records with a given case??  Is the
rule generally to minimize the number of tables??

Mark

On Fri, Oct 30, 2009 at 2:44 AM, Darren Duncan wrote:

> mark m wrote:
> > I'm very new to database programming so this question is pretty basic
> >
> > I have data that is currently organized as follows:
> >
> > Each case has several fields that contain only one value.  There are
> several
> > fields that have a pipe-delimited string
> > that represents a work history.  Each work history item has its own
> fields
> > like data, worker ID etc.  So, as I convert
> > my text files over to database format, I find myself wanting to have a
> table
> > within a table.  So, ideally the case table would
> > have several singular fields and a field named "work history" that would
> > contain a table that would have all of the work history
> > in it.
> >
> > But, I haven't found a way to do this.  If this is not possible, what
> would
> > be the best way to organize data such as this in
> > a database program.
> >
> > Thanks for the help.
> >
> > Mark
>
> What you are talking about is perfectly reasonable from a logical
> standpoint,
> and in the relational model the feature would be called "relation-valued
> attributes" or "RVAs".  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.  Logically speaking, RVAs are what you
> get as
> the intermediate stage of a "GROUP BY", and are the input for aggregate
> operators like SUM()/COUNT()/MIN()/MAX()/etc.
>
> However, SQLite and many other SQL DBMSs don't support RVAs, so you'll have
> to
> use a logically equivalent arrangement of 2 sibling tables that have a
> parent/child (say, "worker"/"work_history") foreign-key relationship.
>
> That is, instead of this kind of schema (in pseudocode):
>
>   var worker : TABLE {
> worker_id : INT, b : TEXT, work_history : TABLE { c : INT, d : TEXT }
>   }
>
> ... you have this kind of schema:
>
>   var worker : TABLE { worker_id : INT, b : TEXT }
>   var work_history : TABLE { worker_id : INT, c : INT, d : TEXT }
>
> ... and work_history.worker_id has a foreign key constraint on
> worker.worker_id
> .  The SQL to do the latter is approximately:
>
>   CREATE TABLE worker (
> worker_id INTEGER PRIMARY KEY,
> b TEXT
>   );
>
>   CREATE TABLE work_history (
> worker_id INTEGER,
> c INTEGER,
> d TEXT,
> CONSTRAINT FOREIGN KEY (worker_id) REFERENCING worker (worker_id)
>   );
>
> You would have a record in "worker" for each distinct "worker_id" and that
> record contains all the details that aren't part of the work history.  Then
> you
> have a record in "work_history" for each record that would have been in the
> inner table of "worker" had it existed, and you have the added "worker_id"
> field
> in "work_history" for every history row that would refer to the same
> worker.
> Having the same "worker_id" values in both sibling tables tells the RDBMS
> which
> rows in the 2 tables correspond to each other.
>
> -- Darren Duncan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature proposal - strong but dynamic typing

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Darren Duncan wrote:
> But on a newer SQLite that implements the stronger typing support I proposed, 
> when that feature is active then columns with declared types like INTEGER/etc 
> would enforce that only values of that type are stored there, 

I might have misunderstood you.  Do you really mean that a new SQLite
version should enforce the types with 'UNIVERSAL' meaning any?  Do you
really expect everyone to have to upgrade their database schemas for this?

> shorthand for an appropriate CHECK constraint, 

Now I am even more confused.  There is this alleged group of people out
there who need type enforcing but are somehow unable to put in CHECK
constraints (which also let you addition stuff like range checks and other
restrictions on values), so the rest of us would have to start littering our
schemas with 'UNIVERSAL' to cater for them?

I have yet to see a clear demonstration of two things:

Why developers who want particular type/value constraints are unable to just
go ahead and use constraints?

Why developers who want 'strong types' don't realise that modulo type
affinity you get out what you put in so don't put in "wrong" types!

In short what problem actually needs to be solved and what is wrong with the
existing tools for those who have the problem?

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

iEYEARECAAYFAkrqlEEACgkQmOOfHg372QTCPACgkdvchMq2NzAU7n4cSKXABUNF
YGMAn3buLfY4gfVoEeyeTYGA2UC1I4dL
=3FL+
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table within a table??

[Darren Duncan]

mark m wrote:
> I'm very new to database programming so this question is pretty basic
> 
> I have data that is currently organized as follows:
> 
> Each case has several fields that contain only one value.  There are several
> fields that have a pipe-delimited string
> that represents a work history.  Each work history item has its own fields
> like data, worker ID etc.  So, as I convert
> my text files over to database format, I find myself wanting to have a table
> within a table.  So, ideally the case table would
> have several singular fields and a field named "work history" that would
> contain a table that would have all of the work history
> in it.
> 
> But, I haven't found a way to do this.  If this is not possible, what would
> be the best way to organize data such as this in
> a database program.
> 
> Thanks for the help.
> 
> Mark

What you are talking about is perfectly reasonable from a logical standpoint, 
and in the relational model the feature would be called "relation-valued 
attributes" or "RVAs".  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.  Logically speaking, RVAs are what you get as 
the intermediate stage of a "GROUP BY", and are the input for aggregate 
operators like SUM()/COUNT()/MIN()/MAX()/etc.

However, SQLite and many other SQL DBMSs don't support RVAs, so you'll have to 
use a logically equivalent arrangement of 2 sibling tables that have a 
parent/child (say, "worker"/"work_history") foreign-key relationship.

That is, instead of this kind of schema (in pseudocode):

   var worker : TABLE {
 worker_id : INT, b : TEXT, work_history : TABLE { c : INT, d : TEXT }
   }

... you have this kind of schema:

   var worker : TABLE { worker_id : INT, b : TEXT }
   var work_history : TABLE { worker_id : INT, c : INT, d : TEXT }

... and work_history.worker_id has a foreign key constraint on worker.worker_id 
.  The SQL to do the latter is approximately:

   CREATE TABLE worker (
 worker_id INTEGER PRIMARY KEY,
 b TEXT
   );

   CREATE TABLE work_history (
 worker_id INTEGER,
 c INTEGER,
 d TEXT,
 CONSTRAINT FOREIGN KEY (worker_id) REFERENCING worker (worker_id)
   );

You would have a record in "worker" for each distinct "worker_id" and that 
record contains all the details that aren't part of the work history.  Then you 
have a record in "work_history" for each record that would have been in the 
inner table of "worker" had it existed, and you have the added "worker_id" 
field 
in "work_history" for every history row that would refer to the same worker. 
Having the same "worker_id" values in both sibling tables tells the RDBMS which 
rows in the 2 tables correspond to each other.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Darren Duncan]

Roger Binns wrote:
>> In fact this support might even be easier as it may only require 
>> enhancements to 
>> the SQL parser, which would generate VM opcodes like for a CHECK constraint, 
>> unless further work is done to optimize for the presented cases, or to 
>> enhance 
>> semantics.
> 
> It has been stated in several places that the increasing items in the parser
> will lead to various fields needing to go to the next size up which would
> increase memory consumption.  There is also the whole backwards
> compatibility angle - what would happen if the database was loaded into an
> older version of SQLite which then ignored this whole UNIVERSAL thing
> allowing "wrongly" typed data to be inserted?

An added type name like UNIVERSAL would be completely backwards compatible 
because, as far as I recall, if SQLite currently sees a type name it doesn't 
recognize, then the column has no affinity and will accept any value, so same 
behavior.  And so then, for older systems using that keyword would be 
effectively a documentation convention.

But on a newer SQLite that implements the stronger typing support I proposed, 
when that feature is active then columns with declared types like INTEGER/etc 
would enforce that only values of that type are stored there, like it is a 
shorthand for an appropriate CHECK constraint, and so marking a column 
UNIVERSAL 
in this situation is explicitly stating that, in the system where type 
constraints are enforced, that column may take any value.

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

[sqlite] Table within a table??

[mark m]

I'm very new to database programming so this question is pretty basic

I have data that is currently organized as follows:

Each case has several fields that contain only one value.  There are several
fields that have a pipe-delimited string
that represents a work history.  Each work history item has its own fields
like data, worker ID etc.  So, as I convert
my text files over to database format, I find myself wanting to have a table
within a table.  So, ideally the case table would
have several singular fields and a field named "work history" that would
contain a table that would have all of the work history
in it.

But, I haven't found a way to do this.  If this is not possible, what would
be the best way to organize data such as this in
a database program.

Thanks for the help.

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