Re: [sqlite] Looking for SQLite app builder

[Rich Shepard]

On Fri, 17 Aug 2018, Bob Sisson wrote:


I am looking for a cross platform app builder that will use SQLite to
build an inspection checklist that will build a "summary page" that takes
selected check boxes, radio buttons and pull downs and looks up their
expanded values and fills in a summary page to be printed.


Bob,

  Since SQLite is an embedded database back end to an application you need
both a UI and middleware. What you use depends on the platform on which you
run your computers and the language you prefer to use to build your tools.

  Without knowing any of this I will crawl out on a limb and suggest you use
Django .

HTH,

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

Re: [sqlite] Are you getting spam when you post to sqlite-users?

[Rich Shepard]

On Tue, 17 Apr 2018, Simon Slavin wrote:


Okay, that's enough. Thanks for the help, everyone.


Simon,

  FWIW, I haven't seen any spam from this mail list. I do run my own MTA
which aggressively rejects known spam. Over 20 years I doubt there have been
more than a handfull from all mail lists to which I have been subscribed.

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

Re: [sqlite] Mailinglist question

[Rich Shepard]

On Wed, 9 Aug 2017, Nico Williams wrote:


Another thing I've done in the past is: download list archives, run a
script to fix the From lines so the archive is then a proper mbox format,
then use mutt. :) It helps if the archives keep Message-ID headers.


Nico,

  I've not followed this thread so my comment might be way off-base.
However, ... I use alpine (and pine before that) which uses mbox for
messages, with a direct subscription to the mail list. I run postfix here
but need to now relay outboun messages through my ISP since Frontier
Communications no longer supports my static IP address and I'm forced to use
their DHCP server. Inbound messages, however, come directly in and are
stored in ~/mail/sqlite.

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

Re: [sqlite] Datatype for prices (1,500)

[Rich Shepard]

On Wed, 30 Nov 2016, Igor Tandetnik wrote:


Store it as an integer, in $.0001 units. So $1.500 would be represented
simply as an integer 1500.


  All you need to store in the database table is the number. How it is
formatted for viewing or printing is controlled by the user interface.

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

Re: [sqlite] Version 3.15.0 released

[Rich Shepard]

On Fri, 14 Oct 2016, D. Richard Hipp wrote:


SQLite version 3.15.0 is now available on the SQLite website:


  Thank you, Richard and all other devs, for an excellent product. Your
efforts are certainly appreicated even when not publicly expressed.

Carpe weekend,

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

[sqlite] Mailing List Request

[Rich Shepard]

On Fri, 11 Mar 2016, Rousselot, Richard A wrote:

> Any way we could have the mailing list strip tabs from the subject lines?
> I have been getting very odd subject formatting lately.

Richard,

   That's dependent upon the mail user agent you use, not the mail list. The
mail list management software passes on all messages as received.

Rich

[sqlite] Mozilla wiki 'avoid SQLite'

[Rich Shepard]

On Sun, 14 Jun 2015, Richard Hipp wrote:

> SQL (and I speak in general terms here, not just of SQLite) provides way
> more than syntactic sugar over b-trees. The syntactic sugar over b-trees
> part is the *least* of the many advantages of SQL. Other more important
> features include:

   Very well written, Richard.

> (3) Declarative Programming.  With SQL, the programmer asks the machine a
> question and lets the query planner figure out an appropriate algorithm. A
> few lines of query text replace hundreds or thousands of lines of
> procedural code needed to implement that query. If performance problems
> are encountered, they can usually be remedied by CREATE INDEX, and without
> changing a single line of code - the query procedures devised by the query
> planner shift automatically.

   Another stumbling block for some application developers is not recognizing
that SQL works in sets unlike earlier database formats that stored fields in
records. That's why a SELECT returns an entire set of table rows meeting the
selection criteria and the row order is not guaranteed.

Rich

[sqlite] Sample SQL code thats beyond me :(

[Rich Shepard]

On Sat, 23 May 2015, Rob Willett wrote:

> Thanks again and as its Saturday have a drink. If any of you are near York
> in England I?ll happily buy you a pint.

Rob,

   I'm in the upper left corner of the US so I'll have to pass on your kind
offer.

   Germane to your fundamental concern, over the years I've found that my
first pass at a database schema is usually sub-optimal. The first design is
based on initial assumptions, and further deep thinking can bring up issues
not recognized before.

   I'm sure you will evolve a schema that works well for your needs and
avoids hidden problems.

Rich

[sqlite] Sample SQL code thats beyond me :(

[Rich Shepard]

On Sat, 23 May 2015, Rob Willett wrote:

> What I want to do is join the table Users and Perimeter Notifications
> together but only if the value of Devices.Holiday_Mode is either non
> existent or if Devices.Holiday_Mode does exist and its 0. If
> Devices.Holiday_Mode is 1 it means the user is on holiday and don?t send
> them anything.

Rob,

   First, you can set holiday_mode to 0 by default rather than leaving it
NULL (unknown). As you wrote, unless the user explicitly sets the mode to 1
the assumption is that its value is 0. After all, it's gotta' be one or the
other, right?

   Second, select * from Devices where holiday_mode == 0. Use that as a
sub-query and join users to the results. Now you have a list of user email
addresses for only those with holiday_mode of zero.

HTH,

Rich

[sqlite] Appropriate Uses For SQLite

[Rich Shepard]

On Wed, 18 Feb 2015, Richard Hipp wrote:

> Please be my "focus group", and provide feedback, comments, suggestions,
> and/or criticism about the revised document. Send your remarks back to
> this mailing list, or directly to me at the email in the signature.

Richard,

   It is clear and well organized. The one difference between SQLite and
a client-server rdbms that could be explicit is single-user versus
multi-user applications and situations. That difference is implied in the
descriptions but likely not noticed by your target market audience.

Rich

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[Rich Shepard]

On Tue, 3 Feb 2015, Gerald Bauer wrote:


  I'm looking for a little tool that reads in an SQLite schema (e.g.
beer.db, football.db, etc.) and outputs (generates) documentation for
tables, fields etc. as a single HTML page or as HTML pages. Any insight
appreciated?


Gerald,

  While not in html format, try the .schema command from the command line.

  Invoke sqlite3, then type .help to see all options. The .sc option dumps
the entire schema to the display; if you specify a table name then the
schema for only that table is displayed.

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

Re: [sqlite] ORDER BY and LIMIT regression

[Rich Shepard]

On Mon, 19 Jan 2015, Richard Hipp wrote:


Thank you for reporting the problem.


  Certainly!


We always fix every problem that we are aware of in SQLite.  But this
problem had not been previously reported to us, and did not occur in any
of the 168 million test cases that we ran prior to releasing SQLite 3.8.8,
so it didn't get fixed.


  And while I've not upgraded that many times over the years, it's never
before been an issue. I've no idea how the timestamps could get so
different, but it's not likely a coding error.

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

Re: [sqlite] PK References One of Two Other Tables

[Rich Shepard]

On Sun, 11 Jan 2015, Simon Slavin wrote:


You can't do the latter.  Foreign keys can reference only one table.  You
could create yet another table, which just supplies primary keys, but it
would seem that this would just duplicate a function of your 'items'
table.


Simon,

  That's what I thought. Guess I need to restructure the tables so as to
avoid the situation.

Thanks,

Rich

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

[sqlite] PK References One of Two Other Tables

[Rich Shepard]

  Items to be raffled can be donated or purchased. The Donations and
Purchases tables each have the item ID as their PK.

  The Raffles table should have the PK as either the Donations or the
Purchases item ID. How do I write the DDL creating the Raffles table so that
the PK is either a donated or purchased item_id number? I've only had a
foreign key reference one specific table, not either of two tables and I've
not found a solution in the references available to me.

Rich

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

Re: [sqlite] SQLite-3.7.4 Amalgamation?

[Rich Shepard]

On Tue, 7 Dec 2010, Richard Hipp wrote:

> I changed to a more consistent naming scheme for all of the build products:
>
> sqlite-PRODUCT-OS-ARCH-VERSION.zip
>
> with the OS and ARCH being omitted for source-code products.  In your
> case, you probably are looking for

Richard,

   That's how it's been for a while.

>http://www.sqlite.org/sqlite-amalgamation-3070400.zip

   I will change the script so it unzips rather than untars, and it looks for
zeros rather than periods in the version number.

Thanks,

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

[sqlite] SQLite-3.7.4 Amalgamation?

[Rich Shepard]

   What happened to the souce tarball of the amalgamation? I'm wondering if
the change to the autoconf version will break the Slackbuild script I use.

   Was there something wrong with the tarballs of previous versions?

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

Re: [sqlite] Multiple databases vs. Multiple tables.

[Rich Shepard]

On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> Queries will be done across the servers to aggregate the content. I am
> concern about the locking mechanism, if I write to the single database and
> I represent the servers as tables I will have no access to read the other
> servers/tables, but if I distribute the servers across different DBs I can
> write into one and the other ones are open to read.

   How frequently are data written to the tables? How much data per write?

   It appears that you want to use separate databases for each server so you
might try that and see how well it works for you.

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

Re: [sqlite] Multiple databases vs. Multiple tables.

[Rich Shepard]

On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> Only one user (the browser). The DB is on a device the same place where
> the user (Browser) is. A process will collect the information about the
> servers and their contents and story it to the DB at the same time the
> user may ask for the contents to display on the screen.

   Then the next question is wether queries are restricted to each server. If
so, you could have a separate database for each. Alternatively, you can
define a server table and associate each one with the other data you collect
and retrieve. The latter approach is more flexible and allows easier changes
if/when your information needs change.

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

Re: [sqlite] Multiple databases vs. Multiple tables.

[Rich Shepard]

On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> in  your opinion is it better (performance, maintainability etc...) to
> have multiple databases or multiple tables. The problem I am facing is as
> follows. I have many media servers containing a large amount of images
> music and videos, let's assume 5. I would like to gather the information
> (metadata, thumbnails, location etc) and story it in the database. I will
> also have a GUI/Browser that will display that information. Now, the
> question is it better to use multiple databases, each server = one
> database or each server = one table.

Daiuusz,

   I suggest you're asking the wrong questions. How many simultaneous users
will access data in the database? Will the database be stored on one server
and accessed across the network?

   It might be that for your application SQLite is not the appropriate tool.

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

Re: [sqlite] Query to compare two sqlite databases

[Rich Shepard]

On Fri, 24 Sep 2010, luuk34 wrote:

> you mean something like:
> select  id1, id2 from callprog a where id1 not in (select b.id1 FROM
> callprog b where b.id1=a.id1 );

   Yeah; much better.

> But what is there is more than 1 column? it will grow in complexity when
> you have a lot of columns.

   My understanding of the original problem was that rows were about the
same, so finding a common column in db1 which was not in db2 was the need.
As long as there is one unique column common to both tables it makes no
difference how many other columns there are in each one.

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

Re: [sqlite] Query to compare two sqlite databases

[Rich Shepard]

On Fri, 24 Sep 2010, John Reed wrote:

> I compare an application every few days which has sqlite as it's client
> database. I look at the content and check whether documents have made it
> into the application after it has been built. I also check the metadata in
> the sqlite client database for changes. So, I am constantly comparing the
> last database with the newer database. Both databases have exactly the
> same tables, with only the data being changed in most of the 51 tables.The
> largest table has about 3,700,000 rows. Most other tables have much less
> rows in them. Could someone suggest an sql query to find the difference in
> the same table (ta) for both the last database (db1) and the newer
> database (db2)? I can use SQLiteSpy to connect and attach to the
> databases.

   You'll want to tune the syntax, but try something like:

   SELECT colA FROM db1 WHERE (NOT EXIST colA IN db2);

The idea is to match rows in each table and where the equivalent row in db1
is not in db2, add that to the results table.

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

Re: [sqlite] Import and export databases from/to SQL dump files

[Rich Shepard]

On Wed, 22 Sep 2010, Nimish Nayak wrote:

> I saw the shell.c file in src and figured out how exactly it exports
> databases to SQL dump files and implemented the same in C. But i could not
> find anything for importing the databases to SQL dump files.

   I've not looked at the source code, but the shell command for importing
SQL files is .read. I regularly .dump files, tweak the data, then re-import
using .read after dropping the original table.

HTH,

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

[sqlite] Syntax for Counting Rows By Multiple Groups

[Rich Shepard]

   I've a table with 15 columns, including industry number, industry
description, and state. I'm trying to formulate the proper SELECT statement
to return the count of rows for each industry number/description in each of
the 5 states. I've looked at the aggregate function chapter in Rick van der
Lans' "The SQL Guide to SQLite" without seeing a suitable example.

   The closest I've come so far is:
sqlite> select sic, sic_desc, state, count(*) from Companies group by 
sic;
The 'group by' phrase returns one row per group, which is what I want as
long as the group is a compound of industry number and state (the
description makes it easier to read and is fixed in association with each
number).

   What I'd like to see is a table grouped either by sic or state:

   sic  sic_descstate   total companies

or

   state   sic   sic_desc  total companies

   This shouldn't be that difficult but I'm just not thinking correctly.

TIA,

Rich

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

[sqlite] WHERE Clause Not Working On Database

[Rich Shepard]

   I cannot select rows from a table using the WHERE clause and cannot find
my error. Perhaps others will see what I miss.

   The table, Companies, has 1500+ rows. One column is defined as
   state CHAR(2),

but the select statement seeking all rows where state = 'OR' for example
returns nothing:

sqlite> select * from Companies where state = 'OR';
sqlite>

   If I specify a single column in the above the same lack of returned rows
is seen.

   A select * from Companies statement returns all rows with no error
messages; so does a count statement:

sqlite> select count(distinct state) from Companies;
5

   Also, it does not matter which column I specify in the WHERE clause, no
rows are returned.

   What simple, embarrassing error have I made in the syntax of the SELECT
statement when I add the WHERE clause?

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

Re: [sqlite] Importing csv to SQLite

[Rich Shepard]

On Mon, 13 Sep 2010, Mithun Nair wrote:

> When i try importing it into a SQLite table, i get some errors like
> "expected 2 columns of data but found 1".  Later i found that importing a
> comma separated file into SQlite wont work because a comma is considered
> as a delimiter by the the SQLite engine even within a "". Is this true? My
> csv file source is a webservice, so i have to try adjusting with the comma
> delimiter. They are not gonna change it for me :). Should i write some
> simple string parser of my own? Or is there any better, optimal method?

Mithun,

   I've gone through this a couple of times (because I did not save the
solution from the first time), most recently this past weekend. Here's what
you need to do to clean up your .csv file for import into SQLite.

   Use the text editor of your choice (emacs, vim, joe, whatever).

   First, you are correct that commas embedded in text strings are seen as
column delimiters. Therefore, convert all commas to the default SQLite
separator |. In my data I run this series:
   - replace all "," with "|"
   - replace all ,,, with |||
   - repeat above for all NULL columns in your source file with varying
numbers of commans.

   Second, replace all " (double qoutes) used to delimit text attributes with
' (single quotes). That's also a global search and replace. I've not had a
problem with apostrophes within a quoted text string as long as the column
separators were the vertical bars.

HTH,

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

Re: [sqlite] Issues .import(ing) a .csv file

[Rich Shepard]

On Sun, 12 Sep 2010, Simon Slavin wrote:

> That's one danger, depending on how you have set up your delimiters.  I
> think your earlier post about commas is more likely to solve your problem.

Simon,

   Yup. It did.

Thanks,

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

Re: [sqlite] New to SQLite and I have a question

[Rich Shepard]

On Sun, 12 Sep 2010, Bob Keeland wrote:

> Access may not be the best database for my programs, but then again my
> needs are not that great. I just need to search a database and then search
> the results of the first search, then search the results of the second
> search, etc. That sounds simple to me, but I'm an ecologist not a
> professional programmer.

Bob,

   I, too, am an ecologist, but I've been coding applications since 1972. :-)
FORTRAN, C, and now Python.

   I'll suggest a SQL language book as your first purchase. Your description
above suggests that nested sub-queries in the SELECT statement are what you
need to learn.

   Learn SQLite, too, and you won't go wrong as your needs grow.

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

Re: [sqlite] New to SQLite and I have a question

[Rich Shepard]

On Sat, 11 Sep 2010, Sam Carleton wrote:

> If you need to learn the basics of SQL, I would highly recommend the book SQL
> For 
> Smarties.
> It covers all the basics and a lot more.  I also have his book Trees and
> Hierarchies in SQL for
> Smarties,
> but this assumes you already know the basics.

   Anything written by Joe Celko is worth reading.

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

Re: [sqlite] New to SQLite and I have a question

[Rich Shepard]

On Sat, 11 Sep 2010, Bob Keeland wrote:

> While I greatly appreciate the help I've gotten on SQLite in general, I
> still wonder about the last part of my questions below. Can anyone
> recommend a good book for learning to use SQLite? What about these that I
> found on Amazon.com

> The Definitive Guide to SQLite by Mike Owens

   Outstanding for understanding SQLite and how it works.

> Using SQLite by Jay A. Kreibich

   I've not read Jay's book so I cannot comment on the focus.

> The SQL Guide to SQLite by Rick F. van der Lans

   Rick's book (and I reviewed/proof-read several chapters) focuses on the
SQL language as used in SQLite. It is a great complement to Mike Owens'
book. (As noted above I have no knowledge of Jay's book.)

   You might also want to read Rick's 'Introduction to SQL, 4th Ed.' which is
more general (and a lot longer) than the SQLite-specific book. He covers
time-based queries in the book which is rarely seen in SQL texts yet
commonly used in business databases.

> I only know a little about SQL in general and even less about SQLite, and
> I could probably use help. I learned how to program in Visual Studio.NET
> 2003 from a book Sam's Teach Yourself Microsoft Visual Basic.NET 2003. I'm
> now using Visual Basic 2010 Express (and have ordered a book on it).

   You work in Microsoft's world so I have nothing to offer for help. But, I
will tell you (having dealt with clients who try to use Access) that that is
a flat-field data base while SQLite is relational. Depending on your
application you'll almost certainly need the latter.

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

Re: [sqlite] Issues .import(ing) a .csv file

[Rich Shepard]

On Sun, 12 Sep 2010, Simon Slavin wrote:

> Any commas in it ?  Any single or double quotes ?

Simon,

   Probable. I need to look.

   There are apostrophe's in a long text column that I thought might be
interpreted as single quotation marks.

Thanks,

Rich

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

Re: [sqlite] Issues .import(ing) a .csv file

[Rich Shepard]

On Sat, 11 Sep 2010, Melton Low wrote:

> When you saved the spreadsheet as a csv file, you probably took the
> default delimiter which happens to be a comma.  You should check for a
> cell with data that has a comma in it. The embedded comma would be treated
> as a column separator which would result in the extra column.

Mel,

   Oh, Duh! That's probably it. I had similar issues months ago and thought
that I had saved the e-mail message with the solution, but grep'ing my mail
directory didn't find it.

   What I did then, and will do again now, is replace the field separators
with '|'. Emacs is very good with this.

Thanks very much,

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

[sqlite] Issues .import(ing) a .csv file

[Rich Shepard]

   I cannot see what's wrong with a line in a .csv file. SQLite tells me it
expected 14 columns of data, but found 15. No matter how many times I count
the columns (exported in .csv from an OpenOffice.org Calc spreadsheet with
14 columns), that's all I find. Nothing appears wrong with the following
line, either. And there's no extra space or char visible in emacs after the
final field's closing ".

   What should I look for that I haven't tried yet to find?

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

[sqlite] ADOdb with SQLite-3.7.2

[Rich Shepard]

   I've not before dealt with ADOdb, but a CMS I'm considering using to
change my company web site uses that. My Google search found one reference
(from 2009) suggesting that ADOdb does not play well with sqlite3. Is this
true (still)?

   If I can use it, I'll take a deeper look at the CMS.

Thanks,

Rich

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

Re: [sqlite] copy data from one db to another

[Rich Shepard]

On Wed, 9 Jun 2010, Vivien Malerba wrote:

>> I forgot to mention, the source is a PostgreSQL db, not SQLite, so
>> there's no source file to copy.  Though a backup might be
>> interesting ...

> You can use Libgda's gda-sql tool in which you can:
> * open a connection to the PostgreSQL db (for example named db1)
> * open a connection to the SQLite db (for example named db2)
> * bind those 2 connections into a 3rd one, and execute statements like
> "insert into db2.table_one_name select * from db1.table_one_name ;"

   Why not do a database dump from postgres, then read the .sql file into
SQlite? As long as you use standard SQL in the data development language
(DDL) you'll get ASCII SQL files for each table's schema with INSERT
statements for each row of each table.

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

Re: [sqlite] database development - correct way?

[Rich Shepard]

On Wed, 9 Jun 2010, Oliver Peters wrote:

> So I assume that it is not(!) a mistake not(!) to use a composite PK in my
> table "customer" (customernumber,customerorigin) and to refer to it from
> the table "order" where I had to use these fields as a composite FK?

Oliver,

   Too many negatives there for me to really follow what you're asking.

   Whenever possible, the primary key for a table should be a 'natural' value
that uniquely describes that entity. For example, a vehicle identification
number, license registration number, or a person's passport number. For a
customer table there is no natural identifier so you make one up: the ID
column. This could be a sequential number or a compisite based on the
customer's name. The customer table stands alone and can be used in various
applications so you want only the single ID attribute as the primary key.

   If it is possible for a specific named customer to have several origins,
then you would want a composite primary key. But, if each customer has only
a single origin then you should have a simple primary key, the customer ID.

   The order table should have its own ID column as a primary key. This way
you assign each new order a different primary key even if the same customer
places two or more orders on the same date. For example:

order_numbercustomer_id date
1   1   9 June 2010
2   3   9 June 2010
3   1   9 June 2010

This makes each order unique regardless of customer, date, or items ordered.

HTH,

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

Re: [sqlite] database development - correct way?

[Rich Shepard]

On Wed, 9 Jun 2010, Tim Romano wrote:

> Typically, Orders are divided into OrderHeader and OrderDetail tables:
>
> OrderHeader
> id integer primary key
> orderdate
> customerid
>
> OrderDetail
> id
> orderid  references OrderHeader(id)
> articleid references article(id)
> quantity int
>
> And you could then place a unique composite index on (orderid, articleid) in
> OrderDetail if you wanted to prevent the same article from appearing on more
> than one line-item of the order.

   To generalize this, when designing a database schema it is best to look at
the relationships involved. For example, the customer-order relationship is
1-to-many (each customer may have many separate orders). However, orders and
items are a many-to-many relationship (each order can have many items, and
each item can be on many orders).

   Usually, many-to-many relationships require an intermediate table that
uses the primary keys from both tables as a composite primary key.

   And, as Tim wrote, separating the order header from the line item details
makes both creation and maintenance much easier.

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

Re: [sqlite] SQLite template files

[Rich Shepard]

On Tue, 6 Apr 2010, BareFeet wrote:

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

Tom,

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

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

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

Re: [sqlite] SQLite version 3.6.23

[Rich Shepard]

On Tue, 9 Mar 2010, D. Richard Hipp wrote:

>> The download page no longer mentions any version of SQLite:

> Oops.  Does now.

   And I thought it was me! I got lost in the repository, started over, tried
a different route, and suddenly the familiar download page appeared. Whew!

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

Re: [sqlite] Newbie question - SQLite the best choice?

[Rich Shepard]

On Sat, 6 Mar 2010, Richard Cooke wrote:

> Our application could have up to 10,000 users via a public facing web
> site.  As a first stab at the schema, I thought I'd have one "Master User"
> database which will probably look like this:

Richard,

   If I recall correctly, SQLite does not do well with multiple, simultaneous
access. I suggest that you take a look here:
 

and look at the multiuser/network client-server tools built on top of
SQLite3.

   For our needs, we use SQLite as the embedded back end to single-user
applications and postgres or mysql for multiuser and Web-based apps.

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

Re: [sqlite] Aggregate From Two-Table SELECT

[Rich Shepard]

On Thu, 18 Feb 2010, P Kishor wrote:

> SELECT l.llid, l.name, SUM(s.endKM - s.beginKM) AS distance
> FROM lotic AS l JOIN streamlength AS s ON l.llid = s.llid
> WHERE l.llid = '1226038453652'
> GROUP BY l.llid, l.name

   Thank you. Now I know.

Much appreciated,

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

[sqlite] Aggregate From Two-Table SELECT

[Rich Shepard]

   I'd appreciate learning how to correctly write a SELECT statement that
reports the SUM of one returned column.

   I can select all relevant rows, but don't know where to put the
SUM(distance) phrase:

   SELECT l.llid, l.name, s.endKM - s.beginKM AS distance
FROM lotic AS l, streamlength AS s
WHERE l.llid = s.llid and l.llid = '1226038453652';

   I would like SQL to do the work of summing the derived column 'distance'
for me.

Rich

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

Re: [sqlite] SQLite Training

[Rich Shepard]

On Wed, 27 Jan 2010, Roger Binns wrote:

> The main drawback is that the book hasn't been revised since 2006 since
> when the virtual machine has changed, foreign key support is present, you
> can use threading freely, there is incremental Blob I/O, virtual tables
> are available, VFS is available, backup API is available etc so the book
> is no longer definitive!

Roger,

   That's very true, but it's still the best overall introduction to SQLite.

   Almost no book on a particular software application remains current. Too
often the book is based on a software version that has been replaced by the
time the book makes it out of the manufacturing process. The fundamentals
are usually still valid, however.

   I actually bought my copy in 2006 and found it answered many questions for
me. Still does.

Rich

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

Re: [sqlite] SQLite Training

[Rich Shepard]

On Thu, 28 Jan 2010, Simon Slavin wrote:

> ... but that requires that you understand at least a bit of relational
> databases theory.  We've recently had questions from people who don't
> understand an INDEX, or what you would want one, or how to make one which
> is useful for a particular query.  So I guess the lowest rung on learning
> SQLite is something like "What is a relational database ?".

> Sorry, a little hobby-horse of mine.

Simon,

   You'll find no disagreement here. I've seen too many "databases" that are
flat-field because the creators have no idea on how to build a relational 
database
by normalizing data. However, that's not specific to SQLite.

   There are many Web sites that can provide a good introduction. Google will
turn them up.

   I will make the same argument about statistical software, particularly GIS
for spatial analyses. Just because you teach someone how to use a word
processor does not make her a writer. Holds true for any technical tool: if
you don't know how to properly use the tool you can hurt yourself.

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

Re: [sqlite] SQLite Training

[Rich Shepard]

On Thu, 28 Jan 2010, nyetngoh wong wrote:

> I'm currently working on a project that uses SQLite and would like to know
> if there is any SQLite certification available in Singapore. Do you
> provide any forms of technical training or courses on using SQLite
> efficiently ?

   All you need is to know SQL well. I'll recommend two books.

   1) Michael Owens. 2006. "The Definitive Guide to SQLite." Apress.
An excellent reference to the specifics of SQLite.

   2) Rick F. van der Lans. 2009. "The SQL Guide to SQLite." Lulu.
An excellent tutorial and reference on SQL as implemented in SQLite.

   In addition, feel free to post specific questions here.

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

Re: [sqlite] SQLite Version 3.6.22

[Rich Shepard]

On Tue, 5 Jan 2010, D. Richard Hipp wrote:

> It has been our habit for the past several years to do at least one SQLite
> release per month. However, we are thinking of backing off from that
> schedule and releasing every other month. If we stick to this plan, it
> means the next release (3.6.23) will not occur until March.

Richard,

   You have my support for increasing the time between releases to 2-3
months.

   Thank you all for your outstanding efforts.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Rich Shepard]

On Fri, 11 Dec 2009, Florian Schricker wrote:

> Schema of DB (simplified):
> - Oper (string)
> - Product (string)
> - Category (string)
> - Name (string)
> - CreateTS (Timestamp)
> - Value (Double)

Florian,

   The schema refers to the set of tables, and the attributes within each
table. Is the above one table or a set of tables.

> Primary keys are Oper, Product, Category, Name and CreateTS

   There is only one primary key per table.

> Can I do that in SQL in one query?

   I urge you to learn about database design and SQL. You can find a lot of
information on the Web and there are many good books on each topic. Only one
of the above attributes is specified as 'Double', yet you write about
dumping data as doubles into each one. Step back and do some learning before
you dig a big hole and fall into it.

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

Re: [sqlite] It's done, except for this nit:

[Rich Shepard]

On Sun, 29 Nov 2009, Ted Rolle, Jr. wrote:

> I need for computed total price to display with leading and trailing
> zeros.
> E.g.
>  1 -> 1.00
> 2.5 -> 2.50
> .9 -> 0.90

Ted,

   It is most common to handle display issues in the UI component. _Most_
applications have three components: a database back end (SQLite here); some
middleware code in the language of your choice for business logic,
reporting, etc.; and the UI that allows interaction with the application's
user. Presentation of output, and formatting of user input is done with the
UI code. Such formatting is not part of SQL.

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

Re: [sqlite] Output in currency format

[Rich Shepard]

On Thu, 12 Nov 2009, Dan Bishop wrote:

> Microsoft Excel has a similar problem.  I ran into it back when I was
> working in a credit union and tried to import a CSV file containing credit
> card numbers.  Wouldn't have noticed except that credit card numbers are
> 16 digits long and double only has 15 digits of precision.

   Excel also has an incorrect formula for Net Present Value. Lotus 1-2-3 had
an incorrect formula for standard deviation (they used the population
formula rather than the sample formula).

   I don't use any M$ software, but when I need to import large numbers into
a spreadsheet (I use XessSE), it's always as text.

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

Re: [sqlite] Converting .dbf to SQLite

[Rich Shepard]

On Thu, 12 Nov 2009, Alex Mandel wrote:

> Using R might actually be a convenient way to do it all in essentially
> one step, and technically batch scriptable.

   I found a perl script that converts .dbf to .csv. It's then trivial to
import the .csv into SQLite.

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

Re: [sqlite] ogr2ogr

[Rich Shepard]

On Thu, 12 Nov 2009, stormtrooper wrote:

> There is a command line tool (ogr2ogr) that runs on linux or windows that
> converts dbf to sqlite, csv, xml, etc. It is primarily a converter for
> geographic data but supports tabular data as well. OGR is part of fwtools.

Keith,

   Thank you. I'm getting back to using GRASS for terrain analyese and
hydrological modeling after a number of years and have not used ogr2ogr.
It's installed here. I've read the man page and will now look for syntax
examples. It's just the tool I need.

Thank you,

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

Re: [sqlite] Converting .dbf to SQLite

[Rich Shepard]

On Thu, 12 Nov 2009, Jean-Christophe Deschamps wrote:

> I'm pretty sure OpenOffice can do a number of such conversions, free and
> portable.  About command-line tools for linux, I just don't know.

   Perhaps. I learned today that only the Winduhs version of OO.o can import
.mdb files; the linux version cannot.

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

Re: [sqlite] Converting .dbf to SQLite

[Rich Shepard]

On Thu, 12 Nov 2009, dave lilley wrote:

> Not trying to be silly here but why not write a wee program that reads in
> the dbf file and for each row read in write the data into an sql file?

   Because I'd have to research the format of the .dbf file and I'd probably
be re-inventing the wheel.

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

[sqlite] Converting .dbf to SQLite

[Rich Shepard]

   Now that I have a working tool to convert from Access .mdb to sqlitedb
files, I need one for dBASE .dbf files. Or, a conversion to .csv will work,
too. Needs to run on linux, of course.

   My Google searches turned up a bunch of tools for the Windows platforms,
supposedly free converters that had prices on them, but nothing like the
mdbtools or mdb-sqlite.

TIA,

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

Re: [sqlite] Converting .mdb Files

[Rich Shepard]

On Wed, 11 Nov 2009, Jan wrote:

> I am using this one:
> http://code.google.com/p/mdb-sqlite/
>
> Have not tried it on linux though.

Jan,

   It's a java app so it should run on anything. What I need to do now is
find the java ant tool on my system.

Thanks,

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

[sqlite] Converting .mdb Files

[Rich Shepard]

   I have a 12.1M .mdb file (soils data) that I want to convert to SQLite. I
downloaded, built, and installed mdbtools-0.5 but it segfaults when I try to
run mdb-schema and mdb-export on the soils data. The -0.6pre1 won't build
because the backend.c file is declared both static and dynamic. I don't know
that this tool is maintained any longer.

   Has anyone here needed to convert from M$ Access to SQLite? If so, how
have you done this on a linux system?

TIA,

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

Re: [sqlite] Output in currency format

[Rich Shepard]

On Tue, 10 Nov 2009, Peter Haworth wrote:

> Is there a way to do this or should I plan on handling it within the
> application?

Pete,

   The latter. Display formatting is not part of SQL.

   You might also consider using integer values for money because the math is
more accurate.

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

Re: [sqlite] Order of operations when using TRIGGERs

[Rich Shepard]

On Thu, 22 Oct 2009, Simon Slavin wrote:

> I'm using a TRIGGER that is triggered AFTER INSERT.  The INSERT commands
> do not know or set the value for the ROWID column.  Can I reliably fetch
> the value for this column from 'NEW.' ?  Or can I rely only on values
> which are explicitly set in the INSERT command ?

Simon,

   I cannot directly answer your question because I have always avoided using
the rowid for _anything_. There's no guarantee that a given row will always
have the same position in the set, so all the SQL experts I've read advise
against using it. Can you assign a unique identifier to each row as the
primary key? If so, use that in the trigger.

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

Re: [sqlite] .import on a .csv file

[Rich Shepard]

On Thu, 22 Oct 2009, Simon Davies wrote:

> One solution is to replace your existing separators  (,) with a character
> that does not exist in your data, specify that character as the separator
> to sqlite, and you should be good to go .import.

   I found the solution was to change all field separators from "," to "|"
which is the default SQLite field separator. This way, after more cleaning
and tuning, I could remove all double quotation marks and each column
remained delimited while embedded commas remained.

   I used emacs for this but any text editor will work.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem about write data into the DB

[Rich Shepard]

On Wed, 21 Oct 2009, ?? wrote:

>  I deployed a django app on my laptop, the whole environment is like this:
> the OS is UBUNTU904, the web server is Apache, and the database is
> sqlite3. The deployment is success, but when I try to write some data into
> the database, I get the HTTP 500 error. And I check the error log, it
> shows "*OperationalError: unable to open database file*". What does this
> error mean? If there are some operation permission need configure?

   I'd look at the django code to see where it opens the database and what
happens to inform the user if that attempt fails. I know nothing about
django so I cannot suggest where you should look.

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

Re: [sqlite] how to represent a tree in SQL

[Rich Shepard]

On Wed, 14 Oct 2009, Michael Chen wrote:

> I am developing a numerical application, where a single rooted dynamic
> tree is the main data structure. I intended to use SQLite for this purpose
> and also for other data as well. However I have no reference how to
> represent a tree using tables. I need these functionalities: (1) basic
> tree: single root, multi-levels, arbitrary number of branches, index each
> node, index each path (from root to a leaf), lookup parent, lookup
> descendants (2) dynamics: delete a path, add a path; maintain parent and
> descendants table; maintain history of tree; lookup history (3) each node
> has lots of matrix and vectors, which will be updated with dynamics, and
> should be tracked
>
> As you see, it is nontrivial to write a tree structure to support all
> these functions, while keep the code clean and neat. That's why I want to
> use SQLite to keep things straight. Is there a good reference on this?

Michael,

   I've not yet had a need to address trees and hierarchies in SQL, but I
will unconditionally recommend you run to borrow or buy a copy of Joe
Celko's "TREES & HIERARCHIES IN SQL", (Morgan-Kaufmann), 2004 ISBN
1-55860-920-2.

   I read his dbms columns in the 1980s and 1990s, read and use his "SQL
Programming Style" and "SQL for Smarties," and communicated with him for
advice on time-and-date based applications. If you still have questions
after reading this book, send him an e-mail message.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help SQL

[Rich Shepard]

On Sun, 11 Oct 2009, Igor Tandetnik wrote:

> I simply combine (month, day) pair into a single number - the same way you
> combine two digits of a decimal number by multiplying the first by 10 and
> adding the second. The multiplier doesn't have to be 100 - any number
> greater than 31 will do.

Igor,

   Would it not be easier to use the DATE column and STRFTIME()? Specifying
start and end dates as -MM-DD removes the need for such manipulations
and will work within a single year as well as over multi-year spans.

   I ask because that's the way I would approach the solution to the question
Rick asked. The MONTH and DAY columns seem repetitive to me and a potential
souce of loss of integrity. If those columns are filled by extracting the
appropriate portions of the DATE column, why not use the latter itself?

Curious minds want to know,

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

Re: [sqlite] DELETE FROM Not Working -- FIXED

[Rich Shepard]

On Thu, 8 Oct 2009, Rich Shepard wrote:

>   Oh, rats! I messed up the table when converting the dates. Guess I need
> to start the process over. Will report results when I'm done.

   Amazing! When the dates are correct, and not all the same, the delete
statement works as intended on the table.

Thanks, all!

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

Re: [sqlite] DELETE FROM Not Working

[Rich Shepard]

On Thu, 8 Oct 2009, Igor Tandetnik wrote:

> Well, do you actually have rows that you believe should satisfy the
> condition? Show one of those.

   Oh, rats! I messed up the table when converting the dates. Guess I need to
start the process over. Will report results when I'm done.

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

Re: [sqlite] DELETE FROM Not Working

[Rich Shepard]

On Thu, 8 Oct 2009, Igor Tandetnik wrote:

> Show what the data looks like now, show the statement you are running, and
> define "not working".

Igor,

   Here are two records:

sqlite> select * from Penalties limit 2;
2009-071|Water Quality, Storm Water|NWR|205 Auto Salvage,
Inc.|Portland|Belete|smith|CP|Failing To Collect Monitoring Data Required In
Schedule B of The Permit|2009-05-07|
2004-159|Water Quality, Storm Water|NWR|205 Auto Salvage,
Inc.|Portland|Jurries|Camilleri|CPDO|Any Violation Related To Water Quality
Which Is Not Otherwise Classified In These Rules.|2005-02-14|1335

   The statement I'm running, and the lack of response is shown by these
statements:

sqlite> select count(*) from Penalties;
1477
sqlite> delete from Penalties where DateIssued < '2005-01-01';
sqlite> select count(*) from Penalties;
1477

   There are the same number of records after I run the delete statement
because none were deleted. Trying a select using the same where clause
results in no rows returned.

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

Re: [sqlite] DELETE FROM Not Working

[Rich Shepard]

On Thu, 8 Oct 2009, Pavel Ivanov wrote:

> Your dates are compared as simple strings. Thus with your statement you're
> trying to delete all rows where DateIssued is January, 1 of any year
> earlier than 2005. Bottom line: change the way you store your dates if you
> really want to compare them in sql statements.

Pavel,

   I changed the data type in the schema to VARCHAR and the format is now
-MM-DD. However, it's still not working. What am I still doing
incorrectly now?

Thanks,

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

Re: [sqlite] DELETE FROM Not Working

[Rich Shepard]

On Thu, 8 Oct 2009, Igor Tandetnik wrote:

> Actually, columns with these declared types will have NUMERIC affinity.

   Thanks, Igor. I missed that.

> Realize that -MM-DD format works not because SQLite treats it somehow
> specially, but because for strings in this format, alphabetical order just
> happens to match calendar order or dates. But yes, if you store your dates
> as -MM-DD strings, most comparisons will just work.

   I converted those date formats in a spreadsheet. As soon as I finish
cleaning the exported .csv for SQLite I'll be able to delete those rows.

Thanks,

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

Re: [sqlite] DELETE FROM Not Working

[Rich Shepard]

On Thu, 8 Oct 2009, Pavel Ivanov wrote:

> And the main problem: SQLite doesn't have such type as date. All types
> it supports are listed here: http://www.sqlite.org/datatype3.html.

Pavel,

   We can use DATE, TIME, and DATETIME column types; they all have TEXT
storage class.

> Bottom line: change the way you store your dates if you really want to
> compare them in sql statements.

   A closer look tells me that the string format is incorrect for SQL. It
needs to be -MM-DD rather than D/M/. That incorrect format seems to
be the problem.

Thanks,

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

[sqlite] DELETE FROM Not Working

[Rich Shepard]

   This must be my error, but I am not seeing it. Your input is requested.

   I have a table named Penalties with a column named DateIssued and a
datatype of DATE. A select operation shows dates such as 4/6/1992 and
12/15/1993.

   To delete all rows with dates earlier than 1/1/2005 I used the statement:

DELETE FROM Penalties WHERE DateIssued < '1/1/2005';

but it did not delete the records. Doesn't matter if I use single quotes,
double quotes, or no quotes.

   Trying to figure out where my syntax is faulty I tried selecting those
records, but none are presented. I'm sure it's a simple user error in my
syntax, but I don't see what that error is and I don't find anything in Rick
van der Lans's or Mike Owen's books that's different from what I've tried.

   I'm sure it's a head-slapping, obvious error so please point it out to me.

Thanks,

Rich

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

Re: [sqlite] Comparing two tables?

[Rich Shepard]

On Wed, 30 Sep 2009, Joe Bennett wrote:

> Have two tables that have the same columns, but different number of rows
> (rows do not match but columns do. 86 matching columns in each table):

   That's to be expected; why would the rows match?

> Now, I'm looking to do this:
>
> Find the first row of data in TableB, take Column1 and Column2's data from
> row one and see if that data exists in TableA. Something like this: SELECT
> * FROM TableA WHERE Column1=Row1Data AND Column2=Row1Data.

   It appears that you want the INTERSECT operator; it finds the set of all
rows in two tables based on common columns. It removes duplicates.

   This is a standard SQL operation (after all, SQL is a language designed to
work with sets). See Section 15.4 (Combining With INTERSECT) in Rick van der
Lans's "The SQL Guide to SQLite" or the equivalent section in his
"Introduction to SQL, 4th Edition."

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TH3 license

[Rich Shepard]

On Fri, 25 Sep 2009, Clifford Hung wrote:

> How do I obtain a TH3 license?

   Perhaps go to the local DMV office and wait in line for several hours?

   I've no idea what a TH3 license is, and Google didn't help any.

   If you want to use SQLite for some purpose read

and you'll see that it's in the public domain (for all countries that
recognize that concept).

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

Re: [sqlite] Tedious CSV import question

[Rich Shepard]

On Thu, 24 Sep 2009, C. Mundi wrote:

> I just bit the bullet and did it.  Python has an excellent csv module,
> capable of handling just about any dialect you're likely to encounter.  I
> am so grateful I did not have to write a parser for CSV.  In just a few
> lines I can read the csv right into sqlite.  If anyone wants the code I
> will post it here if deemed appropriate.

Carlos,

   How about sending me a copy?

Thanks,

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange File Import Error

[Rich Shepard]

On Wed, 23 Sep 2009, Simon Davies wrote:

> You seem to have translated one of your data commas into a separator pipe:
> "Stormwater; NPDES Construction More Than 1 Acre Disturbed Ground, Issued
> By Agent"
> ->
> Stormwater; NPDES Construction More Than 1 Acre Disturbed Ground|Issued By 
> Agent

Simon,

   Thank you very much. No matter how often I read that line (and the rest of
them) I completely missed that.

Much appreciated!

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

Re: [sqlite] Strange File Import Error

[Rich Shepard]

On Tue, 22 Sep 2009, Simon Davies wrote:

>> 117172|Engineered Structures Inc.|Brockway Center|10875 SW Herman
>> Rd|Tualatin|97062-8033|Washington|NWR|45.3834|-122.7882|1542|Nonresidential
>> Construct NEC|Gen12c(Agent)|Stormwater; NPDES Construction More Than 1 Acre
>> Disturbed Ground|Issued By Agent|Minor|STM|Legal Contact|Engineered
>> Structures Inc.|Gary|Ross|15940 SW 72nd
>> Ave|Portland|OR|97224-7936|503-968-6639|
>
> But I count 26 pipe chars in your text above...

Simon,

   With the terminal one removed there are 25. That's how it was before I
tried adding a terminating pipe to see where SQLite is seeing the 26th
column.

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

Re: [sqlite] Strange File Import Error

[Rich Shepard]

On Tue, 22 Sep 2009, Simon Davies wrote:

> Remove the trailing pipe character

   Did that as soon as I learned it made no difference. Each line should be
clean and there are 25 columns defined in it. Quite frustrating.

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

Re: [sqlite] Strange File Import Error

[Rich Shepard]

On Tue, 22 Sep 2009, Simon Slavin wrote:

> Use a text editor on the file and change all occurrences of (including the
> quotes)
>
> ","
>
> to
>
> "|"
>
> then set .separator to the single character | before you import the
> file.

Simon,

   The default separator is '|' (as confirmed by the .show command) so I
changed all commas to the pipe. Makes no difference. Line 1 is still seen by
SQlite as 26 columns for the expected 25 columns. Yet, when I count them I
see only the 25.

   Just for the heck of it I added a pipe symbol to the end-of-line, but that
doesn't make a difference either.

   Can't see what's causing the problem for some reason. Here's line 1 again
(wrapped by alpine when pasted in):

117172|Engineered Structures Inc.|Brockway Center|10875 SW Herman
Rd|Tualatin|97062-8033|Washington|NWR|45.3834|-122.7882|1542|Nonresidential
Construct NEC|Gen12c(Agent)|Stormwater; NPDES Construction More Than 1 Acre
Disturbed Ground|Issued By Agent|Minor|STM|Legal Contact|Engineered
Structures Inc.|Gary|Ross|15940 SW 72nd
Ave|Portland|OR|97224-7936|503-968-6639|

   There are no longer any commas in the file.

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

Re: [sqlite] Strange File Import Error

[Rich Shepard]

On Tue, 22 Sep 2009, Pavel Ivanov wrote:

> AFAIK, you can do only the first - remove all quotes and make sure that no
> commas met in field values.

Pavel,

   I've just tried this and the command line processor still finds 26 columns
instead of the counted 25. Sigh.

Thanks,

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

Re: [sqlite] Strange File Import Error

[Rich Shepard]

On Tue, 22 Sep 2009, Pavel Ivanov wrote:

> No, the problem is that sqlite3 command line utility doesn't parse quotes
> in csv files. It gets line, splits it using comma as delimiter,
> disregarding any quotes and then inserts resulting strings into database.
> So you'll have to use something else for importing your database, maybe
> write your own importer.

Pavel,

   If I understand correctly, I can remove all quotes as long as the only
commas delineate columns. Or, I can use the pipe as a separator and remove
all quotes, too. Correct?

   I've not before dealt with importing an externally-created .csv so this is
new territory for me.

Thanks,

Rich

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

Re: [sqlite] Strange File Import Error

[Rich Shepard]

On Tue, 22 Sep 2009, Pavel Ivanov wrote:

> Could you show us this line 1?

Pavel,

   Sure. I think the issue is that the file uses double quotation marks
rather than single ones. But, in that case, how do I mark an embedded
apostrophe?

Line 1:

"117172","Engineered Structures, Inc.","Brockway Center","10875 SW Herman
Rd","Tualatin","97062-8033","Washington","NWR","45.3834","-122.7882","1542","Nonresidential
Construct, Nec","Gen12c(Agent)","Stormwater; NPDES Construction More Than 1
Acre Disturbed Ground, Issued By Agent","Minor","STM","Legal
Contact","Engineered Structures, Inc.","Gary","Ross","15940 SW 72nd
Ave","Portland","OR","97224-7936","503-968-6639"

   In both emacs and joe this displays as a single line.

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

[sqlite] Strange File Import Error

[Rich Shepard]

   I've downloaded data in .csv format from an agency's Web site and want to
put it in a sqlite database. I created the table as a .sql file and read
that into sqlite. It's fine.

   However, when I try to import the csv file sqlite balks:

sqlite> .import all-deq-contacts-fixed.csv Permits
all-deq-contacts-fixed.csv line 1: expected 25 columns of data but found 29

   There are only 25 columns, and I do not see any spurious spaces at the end
of the line. Each line in the data to be imported terminates with the last
string; all columns are strings because it's a PITA to change two to INTEGER
and two to REAL on a file 7400 lines long.

   I know the error is telling me something I've missed, but I just don't see
what that is.

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

Re: [sqlite] SQL language question

[Rich Shepard]

On Tue, 22 Sep 2009, D. Richard Hipp wrote:

> The question is this:  Should the no-op UPDATE statement (x=x) cause the
> ON UPDATE SET NULL foreign key constraint to set t2.y to NULL or not?
>
> PostgreSQL says "no" - the t2.y value is not nulled unless the t1.x
> value really does change values.

Richard,

   Given the choices, I would go with postgres. It is rational and logical
that if the t1.x value has not changed then the referential t2.x value
should not be changed either.

> And a related question: Does anybody really care about ON UPDATE SET
> NULL? Has anybody ever actually seen ON UPDATE SET NULL used in practice?

   Not I.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Importing data into SQLite - text files are not really portable

[Rich Shepard]

On Tue, 8 Sep 2009, Ribeiro, Glauber wrote:

> Unfortunately, the 3 main families of small computer operating systems
> have 3 different definitions of what a text file is...
>
> This causes no end of trouble when moving text files between these kinds
> of systems.

   I've never worked with a Mac so I have no knowledge of them. However, for
years I have used dos2unix (and, occasionally unix2dos) to do the requisite
line termination changes. It's no big deal to run the text file through the
filter.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Booleans in SQLite

[Rich Shepard]

On Mon, 7 Sep 2009, Jim Showalter wrote:

> Oracle doesn't have a native boolean type. You have to use INTEGER and
> interpret it.
>
> MySQL doesn't have a boolean type (it's just a synonym for TINYINT).
>
> SQL Server doesn't have a boolean type. You have to use BIT and
> interpret it.

   PostgreSQL has a boolean type. It can be 'true' or 'false'; unknown is
represented by the standard NULL type.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Rich Shepard]

On Sun, 23 Aug 2009, Itzchak Raiskin wrote:

> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height). I would like to query this
> database with start and end points of a line and get a vector with all
> heights point along this line. I can, of course create a query for each
> point along the line, but this will be very time consuming as I have
> hundreds of lines with hundreds of points. Any suggestions?

Itzik,

   Other responders have suggested how to query for specific lines, but that
will not fulfill the requirements of a GIS. You could create a
computer-aided drafting (CAD) application this way, but you need to
incorporate coodinate geometry if you want a spatial analytical tool. You
need to accommodate a single edge that defines adjacent polygons and store
the spatial relationship. SQLite is not the best tool for spatial
applications.

   I suggest that you look at PostGIS, OpenGIS, GRASS, and other
well-developed applications. Yes, they use PostgreSQL for the data storage
and can store geometric objects as data entities.

   You are, of course, welcome to reinvent the wheel, but I suggest that you
learn something about spatial databases and coordinate geometry if you want
an application that actually works.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite-3.6.17 for Slackware-12.2

[Rich Shepard]

   We had a thread not long ago about pre-built packages for SQLite. I just
downloaded the 3.6.17 source, built it, and upgraded from 3.6.16. Works
fine. So, if anyone wants a package for Slackware-12.2, ask and ye shall
receive.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A problem with versions of SQLite

[Rich Shepard]

On Mon, 10 Aug 2009, Rod Dav4is wrote:

> Perhaps the version 3.6 db manager will automatically convert my 2.1 to
> the new 3.6 format (or is that too much to hope for?).

   Knowing nothing about any version of Windows, I suggest that you make a
copy of the exiting file and try opening it in the latest version. Your
original is still present and you'll quickly learn whether you have an issue
to resolve.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite database "signature" ?

[Rich Shepard]

On Thu, 6 Aug 2009, luc.moulinier wrote:

> I'd like to know what is the best way to know if a file is a sqlite DB or
> not (without launching sqlite of course) ? For example, is the first line
> of the file unambiguously a signature of sqlite ? If so, what is its
> structure ? Many thanks in advance ! Luc

   The first 16 bytes are:
SQLite format 3

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data synchronization between two offices

[Rich Shepard]

On Tue, 4 Aug 2009, Rstat wrote:

> But today we sell more and more books and the addition of a database means
> we need to synchronize our data from the warehouse database to the main
> office one, mainly to be able to know what kind of stock we have. I think
> we will have to go with a batch syncing scheduled twice a day for now. We
> have been dealing with Talend open studio so far and enjoy the product.

   I've not used talend, but it may work as an interim solution. What you
might think about as a scalable long-term solution is building a Web-based
application. You set up httpd with SQLite in the office, build the
application with Python/Django or Ruby on Rails, and you're set to go.
Anyone with a computer, Web browser, and connection to the 'Net can access
the data.

   You can have multiple terminals in the warehouse and various users in the
offices all accessing the information at their convenience. By having
different user categories, each with different priviledges, you can meet
everyone's needs and increase data integrity. For example, senior management
might be able to view data and reports but not enter or edit anything.
Receiving clerks can enter new inventory but not change records once entered
(that's up to a foreman or supervisor). Sales are entered either directly
from your Web site or someone in the office and both a pick list and invoice
are generated.

   Not only does this approach add capabilities and flexibility, but this
kind of control can support the SEC enforcement of your company executive's
compliance with the Sarbanes-Oxley Act. They can demonstrate fiscal control
and probity quite easily.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Comparisons SQL

[Rich Shepard]

On Mon, 3 Aug 2009, Rick Ratchford wrote:

> It was set as String actually.

Rick,

   That's the storage class; well, TEXT is the storage class.

> I believe this is a WRAPPER thing though. I'm programming in VB6 and using
> Olaf's VB wrapper.

   Oh. I know nothing about Microsoft languages (or operating systems for
that matter), except that they're different. I do all my coding in C or
Python.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Comparisons SQL

[Rich Shepard]

On Mon, 3 Aug 2009, Rick Ratchford wrote:

> The native Date in a table without any additional expressions is
> '-mm-dd 00:00:00'.

Rick,

   That's a timestamp format. Did you specify the column as date or
timestamp?

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [OpenSQL Camp] OpenSQL Camp, November 2009 in Portland, OR (fwd)

[Rich Shepard]

   For those who are interested.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

-- Forwarded message --
Hello!

We're planning to have OpenSQL Camp in Portland. The event will be free, and
we're hoping to have 120 people there.  We've started a wiki page for adding
yourself to the attendee list, and for helping with planning!

http://opensqlcamp.org/Events/Portland2009/

We'll keep you posted as details evolve!  Feel free to pass this on to
groups you think might be interested.

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

Re: [sqlite] Subtotal SQL

[Rich Shepard]

On Wed, 29 Jul 2009, Adler, Eliedaat wrote:

> SQL/sqlite challenge  for all:

   No challenge for anyone who knows SQL.

> I need a running sum of size that works regardless of what order the objects 
> are in.
> User Function/Aggregates welcome!

   _All_ implementations of SQL include a suite of aggregate functions, and
SUM() is among those. Take a look at the SQLite Web site, Mike Owens's book,
Rick van der Lans's book, or any introduction to SQL.

   You will write, "SELECT filename, date, owner, size, SUM(size) AS total
FROM mytable;"

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Rich Shepard]

On Tue, 28 Jul 2009, Shaun Seckman (Firaxis) wrote:

>   Looking at the SQL syntax chart it doesn't seem like this is possible. 
> In other SQL servers I'm able to use the statement "insert into
> foo('col1', col2') values ('1', '1'), ('2', '2'), ('3', '3');".  Is this
> possible in SQLite or must I instead insert one at a time.

Shaun,

   I've only seen single values per row in the references and that's what
I've done both programmatically and in the shell. Programmatically, use a
loop for new values. In the shell, I write a .sql file that has the INSERT
INTO statements in a transaction.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME and storage type

[Rich Shepard]

On Mon, 27 Jul 2009, Rael Bauer wrote:

> If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table
> "notes" add column "last_modified" DATETIME default "2001-01-01";) will
> the declared default value be stored as a string or real value?

Rael,

   What you have above is data type DATE. DATETIME includes the TIME
component.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME and storage type

[Rich Shepard]

On Mon, 27 Jul 2009, Rael Bauer wrote:

> If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table
> "notes" add column "last_modified" DATETIME default "2001-01-01";) will
> the declared default value be stored as a string or real value?

Rael,

   String (the actual data storage type name is TEXT).

> Also, more generally, how can I find out what storage type field values
> have been stored in?

   Section 6.3 of Rick van der Lans's new "The SQL Guide to SQLite" covers
this topic. Use the 'typeof' command. Example:

SELECT typeof ('2009-07-27') as date;

   The returned result:

date

text

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Denormalisation

[Rich Shepard]

On Mon, 27 Jul 2009, CityDev wrote:

> Over the intervening years I can't ever remember denormalising data (even
> when dealing with eg 13 million insurance customers in a table). Is it OK
> nowadays to say always aim to be fully normalised - modern RDBMSs are
> usually powerful enough to cope with most anything?

   My opinion: _always_ go for 4th normal form. I believe that it was both
less robust RDBMSs and less powerful hardware that would justify
denormalizing for speed on rare occasions.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Rich Shepard]

On Mon, 27 Jul 2009, Paul Claessen wrote:

> So .. would anyone know a good book for seasoned programmers, who are new
> to databases, that addresses all these issues?

Paul,

   Any of Joe Celko's books. His "SQL Programming Style" is particularly good
for an overview. The amazon.com listing lets you examine the ToC, among
other extracts.

   Also, Rick van der Lans's "Introduction to SQL, 4th Ed."

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Rich Shepard]

On Mon, 27 Jul 2009, Beau Wilkinson wrote:

> I am dealing with such a project now. The schema consists of time stamp
> plus blob, where the blobs "map" directly to C++ structs. Of course, there
> are all sorts of useful data items in those blobs, and many of the
> capabilities of SQL are lost by reducing data to blobs. I think this is an
> extreme example of what you describe. The architectural excuse given was,
> I think, performance.

   I'm not a professional coder or DBA, but I've been writing code and
building database applications for about 3 decades. The problem, I believe,
is shifting paradigms and the difficulties people have in doing so.

   The original spaghetti-code model I used with FORTRAN in the early 1970s
gave way to structured programming with C. Most of us still effectively use
structured programming with procedural languages, either compiled or
interpreted. While OO is a nice concept, and does have use in the
appropriate applications, it produces bloated binaries because all classes
in the hierarchy need to be compiled and included, even if one function is
all that's needed. However, even within the huge tribe of procedural
languages, there are different paradigms that one needs to understand to
most efficiently use a language. The differences between C and Common LISP
immediately come to mind.

   Regardless, it is very common to see those new to SQL take whatever
knowledge and experience they have with procedural languages and try to
apply it to working with sets. Your project summarized above seems to be an
example of this. Trying to apply the flow control and other structures of a
procedural language to SQL tends to result in a mess. Too few books or other
resources stress these differences so newcomers are taught to look at the
whole database manipulation language (principally the SELECT statement) in a
different way.

   What is more unfortunate is when someone with greater knowledge takes over
a project but is prevented from re-doing it in a more efficient way because
someone else's ego will be bruised or the powers that be cannot appreciate
the need.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Rich Shepard]

On Mon, 27 Jul 2009, Darren Duncan wrote:

> Object orientation has nothing to do with all this per se, though objects
> can easily be mapped to tuples.

Darren,

   A related issue is that object orientation is almost always used in the
context of procedural languages (e.g., C++, Python, Ruby) while SQL is _not_
a procedural language. SQL is a language for working with sets (tables, or
relations).

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Rich Shepard]

On Fri, 24 Jul 2009, CityDev wrote:

> I'm only familiar with DB2, Access Jet and Focus. In each case I would
> expect to reorganise the physical database on a regular basis - maybe
> daily or weekly. What's the best way of doing that with SQLite?

   Vacuum.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Rich Shepard]

On Fri, 24 Jul 2009, Simon Slavin wrote:

> SQLite has enough fans that people are happy to build binaries for
> major platforms.

   I have a SlackBuild script that I've used since sqlite-3.3.1 on June 15,
2006. It builds a Slackware package (*.tgz) on my standard system (currently
-12.2). In the past I've sent the SQLite-*.tgz package to folks who've asked
for it, and I'd be happy to provide a copy to the application's Web site if
that would help. It uses the amalgamation packages with standard options and
standard Slackware filesystem usage (e.g., the executable is in
/usr/bin/).

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users