Re: [sqlite] Single large table vs. several smaller tables

2014-10-09 Thread Don V Nielsen
I suggest you group columns into a structures that you are comfortable
with.  I have a name, own a home, and have one car.  Everything is singular
to me, an individual.  So if I have a table Individuals, do I want 25
columns that encompass name, address, year, make, and model of my car, type
of home, how many windows it has?  At some point, even if the information
is 1 to 1, it makes sense to segregate related data into a table and join
it.

I don't really understand your particular data.  But if you can rationalize
that these pieces of data are best written on a 3x5 note card by itself,
then create a table and join them.  It's not all about efficiency.

just my opinion

On Thu, Oct 9, 2014 at 11:03 AM, Drago, William @ MWG - NARDAEAST <
william.dr...@l-3com.com> wrote:

> Clemens,
>
> That's 24 columns per unit, not rows. There's no duplicate information.
>
> Avoiding joins is something I considered. Thank you for confirming what I
> was thinking.
>
> -Bill
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Clemens Ladisch
> > Sent: Thursday, October 09, 2014 10:46 AM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Single large table vs. several smaller tables
> >
> > Drago, William @ MWG - NARDAEAST wrote:
> > > An automatic test system that I designed generates 25 data elements
> > > for each unit tested.  [...] should I lump everything together in one
> > > table just like the .csv file or should I create several smaller
> > > tables that group similar parameters?
> > > I'm not sure what would normally be done. I think the database is
> > > normalized properly in either case.
> >
> > When you have 24 rows for each unit, this sounds as if the unit
> > information is duplicated.  You have to decide if it would make sense
> > to have a separate table for units.
> >
> > Splitting up for "similar" parameters makes sense only when this
> > similarity has an effect on your queries, i.e., if it would be easier
> > to write "SELECT * FROM LFCal".  That's unlikey if you also have to do
> > a join with UTT.
> >
> > It might make sense to do the split as an optimization, but only if the
> > amount of data in the combined table were large enough to overwhelm
> > your computer.  This does not appear to be the case.
> >
> >
> > > CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and
> > ...
> >
> > This e-mail contains public information intended for any subscriber of
> > this mailing list and for anybody else who bothers to read it; it will
> > be copied, disclosed and distributed to the public.  If you think you
> > are not the intended recipient, please commit suicide immediately.
> > These terms apply also to any e-mails quoted in, referenced from, or
> > answering this e-mail, and supersede any disclaimers in those e-mails.
> > Additionally, disclaimers in those e-mails will incur legal processing
> > fees of $42 per line; you have agreed to this by reading this
> > disclaimer.
> >
> >
> > Regards,
> > Clemens
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
> attachments are solely for the use of the addressee and may contain
> information that is privileged or confidential. Any disclosure, use or
> distribution of the information contained herein is prohibited. In the
> event this e-mail contains technical data within the definition of the
> International Traffic in Arms Regulations or Export Administration
> Regulations, it is subject to the export control laws of the
> U.S.Government. The recipient should check this e-mail and any attachments
> for the presence of viruses as L-3 does not accept any liability associated
> with the transmission of this e-mail. If you have received this
> communication in error, please notify the sender by reply e-mail and
> immediately delete this message and any attachments.
> ___
> 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] Single large table vs. several smaller tables

2014-10-09 Thread Don V Nielsen
> concerned with organizational clarity and correctness than efficiency

>From my personal experience, Sqlite is so bloody fast I simply side table
efficiency until it needs to be looked at.  I can load 1.5 million name
address records (500 bytes each), a second table of 3 million records (same
size), then pick and choose from the latter and put into the former to
increase postal discounts, and do that in 30 minutes or under.  I have no
complaints.

Kudos to Sqlite and the freedom they have given me.
dvn

On Thu, Oct 9, 2014 at 11:17 AM, Drago, William @ MWG - NARDAEAST <
william.dr...@l-3com.com> wrote:

>
> > > The question I have is, should I lump everything together in one
> > table just like the .csv file or should I create several smaller tables
> > that group similar parameters? I'm not sure what would normally be
> > done. I think the database is normalized properly in either case.
> >
> > For SQLite, except in exceptional cases such as huge (multi terabyte)
> > databases or slow media, it is more efficient to have one big table
> > rather than several smaller tables.
>
> Good to know. Since this is a small, low volume database I'm more
> concerned with organizational clarity and correctness than efficiency.
>
> >
> > At a first glance, when I see two tables with identical column
> > definitions, I tend to feel that they should be merged into one table
> > with one additional column.
>
> That was my gut feeling. I could combine even further by using just 4
> columns, but I thought the code might be less complicated by keeping them
> separate:
>
> ID
> Frequency   (either HF or LF)
> VName   (Offset, v10...V200)
> MeasuredVoltage (actual recorded value)
>
> Thanks for your reply, Simon.
>
> -Bill
> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
> attachments are solely for the use of the addressee and may contain
> information that is privileged or confidential. Any disclosure, use or
> distribution of the information contained herein is prohibited. In the
> event this e-mail contains technical data within the definition of the
> International Traffic in Arms Regulations or Export Administration
> Regulations, it is subject to the export control laws of the
> U.S.Government. The recipient should check this e-mail and any attachments
> for the presence of viruses as L-3 does not accept any liability associated
> with the transmission of this e-mail. If you have received this
> communication in error, please notify the sender by reply e-mail and
> immediately delete this message and any attachments.
> ___
> 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] SQLite as a meta database

2014-11-05 Thread Don V Nielsen
Wow!  Please keep this discussion up-to-date because it is absolutely
fascinating what all of you are doing.  Thanks, dvn

On Wed, Nov 5, 2014 at 8:36 AM, Hugo Mercier 
wrote:

> Hi,
>
> Le 05/11/2014 14:16, Hick Gunter a écrit :
> > Hi,
> >
> > we have extensive experience with respect to the use of virtual tables
> in SQLite. In fact, the only native SQLite tables we use are in a
> configuration checking tool.
> >
> > We have "providers" from in-memory indexed tables, CTree (r) files,
> Oracle tables (read only), structured disk files, in-memory structures,
> binary records, etc.
> >
> > The trick is to be able to formulate your queries solely via comparison
> operators. This type of constraint gets passed to your xBestIndex function
> and can be processed there.
> >
> > e.g. provide 2 virtual fields _function and _frame
> >
> > SELECT * from VA, VB where VA._function='Intersect' and
> VA._frame=VB.geom;
> >
> > When called for VA or VB with the constraints (_function,=) and
> (frame,=) your xBestIndex function should return
> > - a value proportional to the effort of locating a record via the
> internal index as "estimated cost"
> > - a number that signifies "use internal index"
> > - set the "omit" flag fort he contraints
> > - set the "argvIndex" values for the constraints
> >
> > When called for VA or VB without constraints, your xBestIndex function
> should return
> > - a value proportional to the effort of a full table scan as "estimated
> cost"
> > - a number that signifies "full table scan"
> >
> > This will make SQLite read VB via full table scan, and look up VA via
> the internal index.
> > For each row retrieved from VB, your xFilter function will be called
> with the parameter values "Intersect" and "VB.geom".
> > SQLite will expect to retrieve exactly those rows of VA the "Intersect"
> with "VB.geom".
> >
> > Assuming that all _functions are commutative e.g. F(a,b) = F(b,a) you
> could provide a symmetrical solution:
> >
> > SELECT * from VA, VB where VA._function='Intersect' and
> VB._function='Intersect' and VA._frame=VB.geom and VB._frame=VA.geom;
> >
> > SQLite would then choose the smaller product of full table scan * lookup.
> >
> > I think it should be possible to have SQLite omit all the checks; if
> not, _frame needs to return geom (best guess...).
> >
>
> This 'virtual field' trick is very clever, thanks !
> I still have to figure out the details, but it could allow us to use
> internal spatial indices, without copying them locally.
>
> It confirms however the query would not be very natural to write for the
> end user and that we would have to assist him or automate the query
> generation.
>
> ___
> 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] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-10 Thread Don V Nielsen
Isn't this the result of the results cache?  The two queries are identical.

On Mon, Nov 10, 2014 at 9:26 AM, Clemens Ladisch  wrote:

> RP McMurphy wrote:
> > Is there a way we can make the w index work with both queries and not
> > have to run external loops to flatten all the WHERE clauses?
>
> 
>
> sqlite> .timer on
> sqlite> select count(*) from v wherez = 0 and
>...> (   y between 100 and 1001000
>...> or  y between 200 and 2001000
>...> or  y between 300 and 3001000
>...> or  y between 400 and 4001000);
> 1334
> Run Time: real 1.100 user 1.092007 sys 0.00
> sqlite> analyze;
> sqlite> select count(*) from v wherez = 0 and
>...> (   y between 100 and 1001000
>...> or  y between 200 and 2001000
>...> or  y between 300 and 3001000
>...> or  y between 400 and 4001000);
> 1334
> Run Time: real 0.002 user 0.00 sys 0.00
>
>
> Regards,
> Clemens
> ___
> 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] website documentation wording

2015-12-04 Thread Don V Nielsen
Tangeant off this note, kind of a history question.  "an SQLite".  I
personally would write "a SQL" because it is "sequel" to me.  When did
SQL--sequel become SQL--ess queue ell?  I always remember it as being
sequel, and it rolls off the tongue easier.  And as sequel, it would be "a
SQLite".

Happy Holidays, all.
dvn

On Thu, Dec 3, 2015 at 3:41 PM, Bernardo Sulzbach  wrote:

> Good catch, Dirk
>
> On Thu, Dec 3, 2015 at 4:47 PM, Dirk Jagdmann  wrote:
> > I'm currently looking at https://www.sqlite.org/autoinc.html
> >
> > I suggest you change "You can access the ROWID of an SQLite table using
> > one {of} the special column names..." and insert the word "of".
> >
> > --
> > ---> Dirk Jagdmann
> > > http://cubic.org/~doj
> > -> http://llg.cubic.org
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Bernardo Sulzbach
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] CSV excel import

2015-07-31 Thread Don V Nielsen
I use the Ruby scripting language and its abundance of libraries to read
excel files then write the output to sqlite.  Those libraries, whether they
r/w csv or excel files are robust, sometime robust beyond belief.  They are
likely more robust than what applications developers write into their
apps.  The libraries are maintained, and can be enhanced by the open source
community.  Lots of good stuff out there.

Sqlite is a db is awesome.  Let's leave the sqlite guys do what the sqlite
guys do best.


On Thu, Jul 30, 2015 at 2:47 PM, John McKown 
wrote:

> On Thu, Jul 30, 2015 at 12:58 PM, Sylvain Pointeau <
> sylvain.pointeau at gmail.com> wrote:
>
> > I understood from the mailing list, that CSV is not a defined format,
> then
> > let's propose another format, well defined, the Excel one (which is in my
> > experience a format to is good every time I had to exchange CSV files).
> >
> > Then why don't you propose an import of CSV from Excel (or similar)?
> > csv(excel)
> >
> > is it possible? in a lot of cases, I cannot use sqlite (executable)
> because
> > of the lack of a good CSV import. It would really great if this could be
> > addressed.
> >
> > Best regards,
> > Sylvain
> >
>
> ?I am replying to your original message rather that later ones because I'm
> curious about the CSV file which is giving you a problem. Using the sqlite3
> command on Linux Fedora 22 (64 bit), I get the following (transcript):
>
> $sqlite3
> SQLite version 3.8.10.2 2015-05-20 18:17:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table test(name1 text, name2 text, name3 text);
> sqlite> .mode csv
> sqlite> .import ./test.csv test
> sqlite> .mode lines
> sqlite> select * from test;
> name1 = name1
> name2 = name2
> name3 = name3
>
> name1 = line1a
> name2 = line1b
> name3 = line1c
>
> name1 = line2"a
> name2 = line2b
> name3 = 'line2b'
> sqlite> .quit
> joarmc at mckown5 2015-07-30T14:43:21 ~/junk
> $cat test.csv
> name1,name2,name3
> "line1a",line1b,line1c
> "line2""a",line2b,'line2b'
> joarmc at mckown5 2015-07-30T14:43:25 ~/junk
> ?
>
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] User-defined types

2015-06-05 Thread Don V Nielsen
>  How can you defend SQL syntax other than on grounds of history or
standardization?

Short answer:  QWERTY.

Long answer:  IBM mainframe DOS -> Z/OS.  A 1960's o/s that is still
supported by the inner workings of its most modern o/s.

There's is nothing wrong with supporting the past.  Sometimes things we
don't like have long histories that we may not like.  But like it, or not,
those histories created a standard and got us to where we are today.  Why
forget the past?

We can enhance and embrace at the same time.  I do all kinds of --stuff--
using Ruby and PHP.  And the --stuff-- gets translated to SQL and sent to
my favorite db, Sqlite.  Why?  Because SQL works, and so much understands
it.  It has a legacy and an understanding and it is documented and it is
well vetted and and and and.

My 1 cent.



On Fri, Jun 5, 2015 at 8:48 AM, Etienne Charland 
wrote:

> What you're looking for seems similar to LINQ to SQLite
> (System.Data.SQLite). When programming in C#, I don't code any SQL. I use a
> strongly-typed interface that then generates SQL queries in the background.
>
> Besides LINQ, you could create another interface that suits your needs,
> and that can then communicate with any database since all databases
> recognize SQL. Nothing needs to change on SQLite's side.
>
> My 2 cents.
>
>
> Etienne
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] FW: Very poor SQLite performance when using Win8.1 + Intel RAID1

2015-03-26 Thread Don V Nielsen
This discussion, and you guys, are amazing.  I am learning so much!

On Wed, Mar 25, 2015 at 8:56 PM, Keith Medcalf  wrote:

>
> On Wednesday, 25 March, 2015 10:42, Simon Slavin 
> said:
>
> >On 25 Mar 2015, at 1:47pm, Rob van der Stel <
> RvanderStel at benelux.tokheim.com> wrote:
>
> >> One open issue for me remains however. Can I assume that the
> >> FlushFileBuffers API that SQLite uses still causes cache to be
> >> written to disk even though the 'automatic write-cache flushing'
> >> of Windows is turned off ?
>
> This depends on the device itself.  Basically, one setting enables use of
> the
>
> >I can't answer your question because Windows isn't my field of expertise,
> >but you could read the section 'Write-Cache Buffer Flushing' in
>
> >
> http://www.samsung.com/global/business/semiconductor/minisite/SSD/us/html/whitepaper/whitepaper12.html
>
> Well, this is chock full of errors:
>
> Virtual Memory: In order to address any potential lack of memory capacity,
> the Windows OS automatically generates a block of virtual memory (stored in
> a hidden pagefile.sys file) on the "C:" drive. In the past, before PC
> Memory (DRAM) was available in high volume, PCs needed to utilize some HDD
> space to address any memory shortcomings. The Windows OS automatically
> reserves storage space for Virtual Memory equal to 100% of physical DRAM
> capacity (e.g. Windows will reserve 4GB of Virtual Memory for a system with
> 4GB of DRAM or 32GB of capacity for systems using a 64-bit OS and featuring
> 32GB of DRAM).
>
> is completely bogus.  A machine with 4GB of RAM and a 4GB swapfile has 8GB
> of total virtual memory.  If you change the 4GB of RAM to 8GB of RAM and
> set no swap then you will have 8GB of virtual memory.  In both cases you
> have the same amount of virtual memory available.  Turning off the swap
> file reduces the number of levels of hardware lookasides and management
> overhead that need to be done to perform V:R mapping increasing performance
> significantly.  In addition, if your pagefile size is fixed, then the
> number of pagetable entries is fixed and the amount of management overhead
> of dynamic VM size is eliminated resulting in performance about half-way
> between operating in "magical" mode (the default, where the VM size is not
> fixed) and the optimal (where there is no swapfile at all).  This applies
> mutatis mutandis no matter what size of virtual ram you are talking about.
> If you have 256 GB of RAM then there is no way that you need to have a 256
> GB swapfile (or any swapfile at all).
>  These myths started in the mid-80's when the price of RAM chips went over
> $100 per 256 kilobit and you needed to be, shall we say, rather well-to-do
> in order to have anything beyond the bare necessity of RAM.  In the modern
> era, you should simply buy sufficient RAM and do away with the archaic
> concept of a swapfile.
>
> Prefetch/Superfetch:  Windows stores common device drivers and frequently
> used applications in main memory (DRAM) in order to improve performance. By
> disabling these features, the OS can reduce system memory (DRAM) use.
> Furthermore, since SSDs have very fast data access times, these features
> are no longer really necessary. Thus, they are disabled for all profiles.
>
> is also somewhat misleading.  Superfetch moves commonly used stuff from
> "spinning disk" to Flash Storage based on the premise that "spinning disk"
> has crappy random access characteristics and Flash (even USB) is better in
> this regard, and that spinning disk has better sequential performance
> compared to that same Flash (which it does).  If the OS detects that the
> system volume is located on a volume with decent random access performace
> characteristics and decent sequential performance, then it will disable
> Superfetch for you all by itself, and ignore your attempts to re-enable it.
>
> Prefetch is somewhat different.  Prefetch is a list of dependancy and
> relocation data to allow modules to be loaded without having the loader do
> all sorts of extra work resolving linkage references and finding and
> loading dependent modules, in addition to pre-loading and relocating
> dependencies before they are requested.  It provides an advantage only if
> you have a really really really old and very very very slow computer with a
> really super slow I/O system.  The advantage declines quite rapidly and
> goes negative long before it become negligible again -- but on any modern
> computing equipment it does nothing of any significance (nor hinderance).
>
> Be careful of things that claim they "magically optimize" for you.  Often
> they don't.
>
> >Also, I like this post:
>
> >http://blogs.msdn.com/b/oldnewthing/archive/2013/04/16/10411267.aspx
>
> This guy is a Balmerite and should be shot on site (or at least put out to
> pasture like Ballmer where he can do no more harm).  Maybe you want to hide
> such things from certain classes of people.  Perhaps when Windows is first
> installed it should ask 

Re: [sqlite] SQLite Expert

2011-10-31 Thread Don V Nielsen
I use the free version of sqlite expert.  I use sqlite for my IO handling.
 Having the power of sql available to test values, generate counts, etc...
is indispensable.

On Sun, Oct 30, 2011 at 2:50 PM, Abair Heart  wrote:

> Hi all,
>
> Searching the archives implies, that "sqlite expert" hasn't been asked
> about.
>
> Has anyone tried it yet?  Is the program safe? time-saving?
>
>
> Thanks for any input.
>
> Abair
> ___
> 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] UPDATE

2011-10-31 Thread Don V Nielsen
I need help with a complex UPDATE.  I want to update each row in a table,
calculating an average, and then apply that value back into a column of the
same row.  Is this possible with Sqlite?  Below is code that should work
with SqlServer; its UPDATE supports a FROM statement.

UPDATE m SET rtwgt = avgrowid
FROM seg_02_matches as m
JOIN (
  SELECT pr3.zip, pr3.crrt, avg(ap.[rowid]) AS avgrowid
  FROM (
   SELECT pr1.zip, pr1.crrt, pr1.prty, 'WI' AS 'id' FROM pool_WI AS pr1
   UNION
   SELECT pr2.zip, pr2.crrt, pr2.prty, 'NY' AS 'id' FROM pool_NY AS pr2
  ) AS pr3
  INNER JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid =
pr3.id
  GROUP BY pr3.zip, pr3.crrt
) as sub ON m.zip = sub.zip AND m.route = sub.crrt

I don't think I can use WHERE IN because I need multiple elements returned
by the sub-select.

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


Re: [sqlite] UPDATE

2011-11-01 Thread Don V Nielsen
Thanks, Igor.  My original macro did replicate the where in the sub-select.
 I temporarily got rid of them in my testing because it was so messy and I
didn't know how to get the update working.

I will give this a shot, and thanks for time and effort.

On Tue, Nov 1, 2011 at 7:45 AM, Igor Tandetnik <itandet...@mvps.org> wrote:

> Don V Nielsen <donvniel...@gmail.com> wrote:
> > I need help with a complex UPDATE.  I want to update each row in a table,
> > calculating an average, and then apply that value back into a column of
> the
> > same row.  Is this possible with Sqlite?  Below is code that should work
> > with SqlServer; its UPDATE supports a FROM statement.
> >
> > UPDATE m SET rtwgt = avgrowid
> > FROM seg_02_matches as m
> > JOIN (
> >  SELECT pr3.zip, pr3.crrt, avg(ap.[rowid]) AS avgrowid
> >  FROM (
> >   SELECT pr1.zip, pr1.crrt, pr1.prty, 'WI' AS 'id' FROM pool_WI AS pr1
> >   UNION
> >   SELECT pr2.zip, pr2.crrt, pr2.prty, 'NY' AS 'id' FROM pool_NY AS pr2
> >  ) AS pr3
> >  INNER JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid =
> > pr3.id
> >  GROUP BY pr3.zip, pr3.crrt
> > ) as sub ON m.zip = sub.zip AND m.route = sub.crrt
>
> UPDATE seg_02_matches SET rtwgt = (
>  SELECT avg(ap.rowid)
>  FROM (
>SELECT zip, crrt, prty, 'WI' AS id FROM pool_WI AS pr1
>UNION ALL
>SELECT zip, crrt, prty, 'NY' AS id FROM pool_NY AS pr2
>  ) AS pr3
>   JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid = pr3.id
>   WHERE seg_02_matches.zip = pr3.zip AND seg_02_matches.route = pr3.crrt
> );
>
> Might work faster if WHERE clause were duplicated inside the subselect.
> Try it both ways:
>
> UPDATE seg_02_matches SET rtwgt = (
>  SELECT avg(ap.rowid)
>  FROM (
>SELECT prty, 'WI' AS id FROM pool_WI AS pr1
>WHERE seg_02_matches.zip = pr1.zip AND seg_02_matches.route = pr1.crrt
>UNION ALL
>SELECT prty, 'NY' AS id FROM pool_NY AS pr2
>WHERE seg_02_matches.zip = pr2.zip AND seg_02_matches.route = pr2.crrt
>  ) AS pr3
>   JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid = pr3.id
> );
>
> Might be better still if you had a single "pool" table with the explicit
> id column, rather than having to manufacture it on the fly with UNION ALL.
> --
> Igor Tandetnik
>
> ___
> 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] Slow INDEX

2011-11-02 Thread Don V Nielsen
I would like to stick my neck out over the chopping block and agree.  My
experience is the opposite, but appears to support Puneet's assertion.
 With me, it takes my C# application 12 seconds to pass 103,00 records and
insert 98,000 rows into the db from it.  The next time I run the
application (which starts with a fresh db,) it takes 7 seconds or less.
 This leads me to be believe the O/S still has the original file cached, so
it's i/o performance is much improved.

dvn

On Wed, Nov 2, 2011 at 11:27 AM, Mr. Puneet Kishor wrote:

>
> On Nov 2, 2011, at 11:24 AM, Fabian wrote:
>
> > Now if I re-open the database, I can add an additional 10.000 rows very
> > fast (<1 sec). But if I reboot the (Windows) PC, and insert an additional
> > 10.000 rows, it takes at least 30 secs, which seems very slow, if I can
> add
> > the first 1 million in under 10 seconds.
>
>
> Others will have better answers, but methinks that when you reboot the
> computer, the operating system's caches are flushed out, which slows the
> operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.)
> and notice if the speed increases again to what you expect.
>
>
>
> --
> Puneet Kishor
> ___
> 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] Using "sub-select" to return limit

2011-11-03 Thread Don V Nielsen
Given the following, I get an error that f.zip does not exist.  Obviously,
I am mentally missing something contextually, but I'm not getting it.
 Would someone work through the scope of things in this select.

select p.*,pr.rowid from pool_wi as p
inner join add_priorities as pr on pr.prty = p.prty
where p.zip = '53005' and p.crrt = 'C022'
order by pr.rowid
limit (
  select f.need from seg_02_final_view as f where f.zip = p.zip and f.crrt
= p.crrt
)

What I ultimately want to do is iterate through a table that contains the
limit and use that value to select all matching values from another table,
limiting the number of records selected from the group to that defined by
limit.  For example, a table would hold two rows of zip, route and limit,
[53005,C020,1] & [53005,C022,2].  I want to use the zip and route to select
all matching records from another table, but limit the result of the
sub-select to the qty of records as defined by limit.

Thanks for your time and consideration.
dvn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] sql server management studio like tool for SQLite

2011-11-03 Thread Don V Nielsen
If you are looking to manage a database file, such as view the contents of
tables, write and test scripts for processing, etc...there are a number of
management packages.  I like using Sqlite Expert.

If you are looking for something that is going to do DTS, bulk imports,
etc...I don't know what exists.  I don't think there is anything.

On Thu, Nov 3, 2011 at 6:41 PM, David Hubbard  wrote:

> Is there any tool for SQLite like sql server management studio? We are
> looking at using SQLite and I have no
> expireince with it but would like an easy to use tool to use with
> SQLite that can perform the same functions as SSMS.
> Or can you connect SQLite to SSMS?
>
> Thanks any help received will be appreciated.
> ___
> 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] Using "sub-select" to return limit

2011-11-04 Thread Don V Nielsen
Thanks.  I was afraid of that.  I got out my old Data Base Systems books
and was trying to figure out what I forgot.  Turns out I didn't forget it.
 It's just not possible.

Thanks again.  Off to coding.

On Thu, Nov 3, 2011 at 9:08 PM, Pavel Ivanov <paiva...@gmail.com> wrote:

> > What I ultimately want to do is iterate through a table that contains
> the> limit and use that value to select all matching values from another
> table,> limiting the number of records selected from the group to that
> defined by> limit.
> There's no way to do such thing using only SQL. You have to select
> data from your table of limits into your programming language, iterate
> through results and for each row issue separate select statement with
> appropriate limit (which will be a constant not a nested select
> query).
>
>
> Pavel
>
>
> On Thu, Nov 3, 2011 at 5:23 PM, Don V Nielsen <donvniel...@gmail.com>
> wrote:
> > Given the following, I get an error that f.zip does not exist.
>  Obviously,
> > I am mentally missing something contextually, but I'm not getting it.
> >  Would someone work through the scope of things in this select.
> >
> > select p.*,pr.rowid from pool_wi as p
> > inner join add_priorities as pr on pr.prty = p.prty
> > where p.zip = '53005' and p.crrt = 'C022'
> > order by pr.rowid
> > limit (
> >  select f.need from seg_02_final_view as f where f.zip = p.zip and f.crrt
> > = p.crrt
> > )
> >
> > What I ultimately want to do is iterate through a table that contains the
> > limit and use that value to select all matching values from another
> table,
> > limiting the number of records selected from the group to that defined by
> > limit.  For example, a table would hold two rows of zip, route and limit,
> > [53005,C020,1] & [53005,C022,2].  I want to use the zip and route to
> select
> > all matching records from another table, but limit the result of the
> > sub-select to the qty of records as defined by limit.
> >
> > Thanks for your time and consideration.
> > dvn
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Proper way to change temp directory

2011-11-21 Thread Don V Nielsen
In Ruby (just for giggles), its

Dir.chdir('dir text') {|dir|
  # do your sort stuff here
}  # previous directory restored when logic block is completed




On Sun, Nov 20, 2011 at 3:50 AM, Yang Zhang  wrote:

> Cool beans, perhaps this should be added to the docs!
>
> On Sun, Nov 20, 2011 at 1:36 AM, Dan Kennedy 
> wrote:
> > On 11/20/2011 04:00 PM, Yang Zhang wrote:
> >>
> >> Out of curiosity, what's the proper way to change the temp directory
> >> (say, to avoid "Error: database or disk full" errors on vacuum, which
> >> I ran into)?  temp_store_directory has been working for me but it's
> >> deprecated and may be elided from builds.  Is the only option to
> >> recompile sqlite?  Thanks.
> >
> > On unix setting the TMPDIR environment variable works.
> >
> > On windows SQLite uses GetTempPath(). So perhaps setting
> > TMP or TEMP works there.
> >
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Yang Zhang
> http://yz.mit.edu/
> ___
> 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] Confused by DELETE statement error

2011-11-21 Thread Don V Nielsen
I'm confused by the error message, "near 'order' : syntax error", with the
following statement.  The same statement, beginning with "select *" in
place of "delete", will function fine.

delete from seg_ny_adds
where needid = 90
order by prty desc
limit (select count() from seg_ny_adds where needid = 90) - 2

What I am trying to accomplish is to remove from each group (needid
identifies the group) all but the top 2 rows.
The plan is to loop through the table, identify each group that has more
than two rows, order them by priority descending,
and then delete the top records leaving the bottom two.  The bottom two
will be the highest priority rows.

Thanks for time and consideration,
dvn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Looping select

2011-11-29 Thread Don V Nielsen
This one is weird.  And I don't know exactly what I have the ability to
provide you for information.  Below is the C# code I use to execute a
query.  And below that is what is ultimately translated and executed.  The
c# application appears to get hung up and loops.  It burns cpu, but never
stops.  But the same (translated) code will run in seconds if I cut and
paste it into Sqlite Expert.  I'll keep poking around and see what else I
might be doing.

All comments about my syntax are welcome.
Have a good one!

=== c# query =

SQLiteCommand upd_cmd = DbWork.CreateCommand();
//upd_cmd.Parameters.Add(SegmentController.SqlprmDepth);
upd_cmd.CommandText = String.Format(
@"update {0} set {11} =
(
  select avg(rowid) from
  (
select pr.rowid
from {1} as m
inner join {2} as p on p.{5} = m.{5} and p.{6} = m.{6}
inner join {3} as pr on pr.{7} = p.{7} and pr.{8} = p.{9}
where {4} = {0}.{4}
order by pr.rowid
limit {12}
  )
)
where {4} in
(
  select {4} from {1} where {10} = {12}
);
",
seg.TblNameMatches,
seg.ViewNameMatches,
seg.ViewNamePools,
AddPoolController.POOL_PRIORITIES_TABLE_NAME,
SegmentController.SEG_COL_NEEDID,
TblZipRoute.ZR_COL_ZIP,
TblZipRoute.ZR_COL_CRRT,
TblZipRoute.ZR_COL_PRTY,
AddPoolController.POOL_COL_POOLID,
TblZipRoute.ZR_COL_ID,
lvl,
SegmentController.SEG_COL_ROUTE_WEIGHT,
i
);

//SegmentController.SqlprmDepth.Value = i;
upd_cmd.ExecuteNonQuery();
}

=== c# query translated ===

update seg_WI_matches set rtwgt =
(
  select avg(rowid) from
  (
select pr.rowid
from seg_WI_matches_view as m
inner join seg_WI_pool_view as p on p.zip = m.zip and p.crrt = m.crrt
inner join add_priorities as pr on pr.prty = p.prty and pr.poolid =
p.fileid
where needid = seg_WI_matches.needid
order by pr.rowid
limit 1
  )
)
where needid in
(
  select needid from seg_WI_matches_view where CR = 1
);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Looping select

2011-11-30 Thread Don V Nielsen
There is too much ambiguity with regards to needid; poor coding|edit
checking on my part.  When I label everything properly, then the query runs
successfully (darn fast, too).  I also changed the replacement tag @depth
to @dpth.  When I was examining everything with notepad++, it highlighted
@depth as a keyword.  I thought maybe I should change that.

Anywho, the system was not throwing an error.  It just cycled and cycled
and cycled.

dvn

=

update seg_02_matches set rtwgt =
(
  select avg(rowid) from
  (
select pr.rowid
from seg_02_matches as m1
inner join seg_02_matches_view as n1 on n1.rowid = m1.needid
inner join seg_02_pool_view as p on p.zip = n1.zip and p.crrt = n1.crrt
inner join add_priorities as pr on pr.prty = p.prty and pr.poolid =
p.fileid
where m1.needid = seg_02_matches.needid
order by pr.rowid
limit @dpth
  )
)
where needid in
(
  select m2.needid from seg_02_matches as m2
  inner join seg_02_matches_view as n2 on n2.rowid = m2.needid
  where n2.CR = @dpth
);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite with Vis. Studio C# Entity Framework 4.1

2011-12-14 Thread Don V Nielsen
While I love working with C# and Sqlite, I'm quite an amateur at it even
though I'm doing some sophisticated programming for my employer.  And even
less at exploiting the capabilities of Visual Studio in helping me.  Below
is a very typical routine for me.  I use string.Format a lot to assemble
the sql commands necessary to get the job done, as in the following
example.  DbWork is a property that contains the sqlite connection passed
to the class.  Its joining multiple tables and views (which are unions of
tables) in order to calculate a wgt'd average and apply the results back to
the input table.

private static void calculate_rtwgt(Segment seg, string lvl, Int32
depth)
{
DbTransaction trans = DbWork.BeginTransaction();

// TODO - I need to be sensitive to pool rank in the sequencing
// for each depth, calculate rte weights
for (int i = 1; i <= depth; i++)
{
using (SQLiteCommand cmd = DbWork.CreateCommand())
{
cmd.Parameters.Add(SegmentController.SqlprmDepth);
cmd.CommandText = String.Format(
@"update {0} set {11} =
(
  select avg(rowid) from
  (
select pr.rowid
from {1} as m1
inner join {2} as p on p.{5} = m1.{5} and p.{6} = m1.{6}
inner join {3} as pr on pr.{7} = p.{7} and pr.{8} = p.{9}
where m1.{4} = {0}.{4}
order by pr.rowid
limit {12}
  )
)
where {0}.{4} in
(
  select m2.{4} from {1} as m2 where m2.{10} = {12}
);
",
seg.TblNameMatches,
seg.ViewNameMatches,
seg.ViewNamePools,
AddPoolController.POOL_PRIORITIES_TABLE_NAME,
SegmentController.SEG_COL_NEEDID,
TblZipRoute.ZR_COL_ZIP,
TblZipRoute.ZR_COL_CRRT,
TblZipRoute.ZR_COL_PRTY,
AddPoolController.POOL_COL_POOLID,
TblZipRoute.ZR_COL_ID,
lvl,
SegmentController.SEG_COL_ROUTE_WEIGHT,
SegmentController.SqlprmDepth.ParameterName
);

SegmentController.SqlprmDepth.Value = i;
cmd.ExecuteNonQuery();

}
}

trans.Commit();
trans.Dispose();
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite with Vis. Studio C#

2011-12-16 Thread Don V Nielsen
My application never uses a DataSet.  I push all db responsibility to
sqlite.  It returns DbDataReaders when I need to recurse through results.



On Thu, Dec 15, 2011 at 1:40 PM, Jeff Matthews  wrote:

> Regarding my earlier question as to .NET SQlite syntax, I was wondering if
> I
> had to use DataAdapters and create and fill objects (DataSets), or not.
>
> Here is some sample code dealing with SQLite in C#:
> http://www.codeproject.com/KB/cs/SQLiteCSharp.aspx
>
> It looks to me like he is loading the database into a DataSet and then,
> doing his thing on the DataSet, rather than on the database directly.
>
> See how he is building his query as a string (surrounded by quotes) and
> then, calling a function to conduct it?
>
> Is this necessary - i.e., is this the only way to query and manipulate
> SQLite data from C#?
>
> I was thinking that we could just use sql statements literally, without
> having to send them as a string to a function.
>
> Does my question make sense?
>
> Can it not be done just using the sql literally and skipping all the
> DataAdapter, DataSet stuff?
>
>
>
>
>
> ___
> 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] FW: Which version of SQLite?

2011-12-16 Thread Don V Nielsen
Ruby is awesome, especially when working with Sqlite...using ActiveRecord.
 But its I/O is really slow, so propagating a database is not its thing.

dvn

On Wed, Dec 14, 2011 at 8:32 PM, Nataraj S Narayan wrote:

> Hi
>
> I am also an ex-clipper. I miss the old 'code blocks' days. I think
> Ruby comes closest to Clipper with its own code blocks.
>
> regards
>
> Nataraj
>
> On Thu, Dec 15, 2011 at 7:17 AM, Jeff Matthews  wrote:
> >
> >
> > I want to use SQLite in a C# app I am developing.
> >
> >
> >
> > My database knowledge drops off around 1996, when I gave up Clipper
> > programming.   I have since learned a little about some of the new
> methods
> > used by database gurus.  But I remember Clipper like it was yesterday
> since
> > I did so much of it.
> >
> >
> >
> > Here is a short snippet of the logic I recall using back when:
> >
> >
> >
> > Select (0)  //  Provides allocation for a new file handle for opening a
> > database
> >
> > Use Customers   //  Will open Customers dbf, where in those days, each
> table
> > was its own file, and thus, we had multiple dbf files in an app to use
> > relational data.
> >
> > Set Index to Phone, LastName  //  The indexes were also stored in
> separate
> > files.
> >
> > Set Order to 2  //  This would mean that our seeks would use the LastName
> > index's sort order
> >
> >
> >
> > seek "MATTHEWS"  //   Try to find the first instance of search string in
> the
> > index and move record pointer to the row, or if not found, eof()
> >
> >
> >
> > numrecs=0
> >
> > if found();
> >
> >do while trim(upper(LastName))="MATTHEWS" .and. !eof()
> //
> > cycle through the records and stop if eof() is hit
> >
> >delete   // delete the entire row
> >
> >numrecs=numrecs+1
> >
> >skip  // go to next record
> >
> >enddo
> >
> > endif
> >
> > showMessage(ltrim(str(numrecs,0))+" records deleted.")
> >
> >
> >
> > That's it.
> >
> >
> >
> > I am not wanting to have to deal with excitingly new ways to do all this,
> > such as DataSets or Entities, unless someone says, "But, you must!" or
> > "You're crazy not to."
> >
> >
> >
> > So, which is the best SQLite download for me to use in C# using the
> closest
> > syntax and logic flow as set forth above?  I am ready to download and
> start
> > hacking.
> >
> >
> >
> > Thanks.
> >
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Parameters are not allowed in views

2011-12-22 Thread Don V Nielsen
Where can I learn more about "restrict it".  I'm not familiar with the
syntax for using the question mark.  Is there a specific part of the
documentation that explains it and how it works?

Thanks,
dvn

On Wed, Dec 21, 2011 at 5:00 PM, Petite Abeille wrote:

>
> On Dec 21, 2011, at 11:40 PM, Chris Mets wrote:
>
> > Is this truly a limitation of SQLite or am I doing something wrong?
>
> The later. Simply create your view. Then restrict it.
>
> In other words:
>
> create view foo as select bar from baz
>
> select * from foo where bar = ?
>
> ___
> 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] Optimizing a query with range comparison.

2011-12-27 Thread Don V Nielsen
I do something similar, where the ranges are zip codes.  However, my tableb
is arranged vertically with one key (zip code) and one value (geographic
zone).  I would then join the two tables using the zip code, rather than
trying to identify the zip code within a range of zip codes in tableb.
Matching 3 million tablea rows to 49,000 tableb rows takes 90 seconds, I
think.


create tablea (id int, pos int);
create tableb (pos int, ?? int);  /* not sure what is represented by index
position of range */

create table ids as
select ?? from tableb b join tablea a on b.pos = a.pos;


On Tue, Dec 27, 2011 at 9:39 AM, Bo Peng  wrote:

> Dear Sqlite experts,
>
> The schema of my test tables is as follows:
>
> CREATE TABLE tableA (id INT, pos INT);
> CREATE TABLE tableB (start INT, end INT);
>
> CREATE INDEX tableA_idx on tableA (pos asc);
> CREATE INDEX tableB_idx on tableB (start asc, end asc);
>
> CREATE TABLE ids (id INT);
>
> tableA saves position of about 8 million objects, and table B saves
> about 40 thousand ranges. I need to find out all ids in tableA that
> falls into one of the ranges in tableB, and insert the results into
> table ids. I am using a query
>
> INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND
> end;
>
> with indexes on tableA.pos and tableB.start, tableB.end (combined),
> this query takes hours to execute. Is there anyway to optimize this
> query? My understanding is that, if the query takes each pos and
> compare it to all ranges, it will be slow. If it takes each range and
> get all pos fall into the range, the query will be much faster. I have
> tried to 'EXPLAIN' the query but I do not understand the output
> because it looks different from what is described in
> http://www.sqlite.org/eqp.html. I will appreciate it if someone can
> tell me what sqlite is doing for this query.
>
> > explain select id from tableA, tableB where pos between start and end;
> 0|Trace|0|0|0||00|
> 1|Goto|0|26|0||00|
> 2|OpenRead|1|2|0|2|00|
> 3|OpenRead|0|1446|0|2|00|
> 4|OpenRead|2|133259|0|keyinfo(1,BINARY)|00|
> 5|Rewind|1|22|0||00|
> 6|Column|1|0|1||00|
> 7|IsNull|1|21|0||00|
> 8|Affinity|1|1|0|d|00|
> 9|SeekGe|2|21|1|1|00|
> 10|Column|1|1|1||00|
> 11|IsNull|1|21|0||00|
> 12|Affinity|1|1|0|d|00|
> 13|IdxGE|2|21|1|1|01|
> 14|Column|2|0|2||00|
> 15|IsNull|2|20|0||00|
> 16|IdxRowid|2|2|0||00|
> 17|Seek|0|2|0||00|
> 18|Column|0|0|3||00|
> 19|ResultRow|3|1|0||00|
> 20|Next|2|13|0||00|
> 21|Next|1|6|0||01|
> 22|Close|1|0|0||00|
> 23|Close|0|0|0||00|
> 24|Close|2|0|0||00|
> 25|Halt|0|0|0||00|
> 26|Transaction|0|0|0||00|
> 27|VerifyCookie|0|6|0||00|
> 28|TableLock|0|2|0|tableB|00|
> 29|TableLock|0|1446|0|tableA|00|
> 30|Goto|0|2|0||00|
>
> Many thanks in advance,
> Bo
> ___
> 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] Using CTE with INSERT

2015-10-13 Thread Don V Nielsen
I'm struggling to implement an INSERT statement that get's is values from a
CTE.  What I want to do is identify zip/crrt combinations at meet minimum
qty specifications, and then insert into a table the recid values of those
record destined to the identify zip/crrts.  While the syntax below is
incorrect, hopefully it conveys what I am trying to accomplish.

Can someone assist?  Your time and consideration is much appreciated.
don v nielsen

Note: CTAS would work in this scenario.  But I cannot figure out that
syntax, as well.

insert into vo_crrt_pieces (recid)
values (
  with
  -- controls from carrier route processing
  pkg_controls AS (
SELECT * FROM d_pkg WHERE pkg_level = 'CRD'
  )
  -- select pieces that meeting pkg and pkg_level minimums
  , min_pkgs_met AS (
SELECT
  a.zip
  , a.crrt
  , count(1) pkg_pieces
  , sum(CASE WHEN c.selective THEN 1 ELSE 0 END) sel_pieces
  , sum(CASE WHEN NOT c.selective THEN 1 ELSE 0 END) non_pieces
FROM
  addresses [a]
  , pkg_controls [b]
JOIN versions [c] ON c.ver_id = a.version_id
WHERE trim(crrt) <> ''
GROUP BY
  a.zip,
  a.crrt
HAVING
  count(1) > b.min_pkg
  AND sum(CASE WHEN NOT c.selective THEN 1 ELSE 0 END) > b.min_pieces
  )
  SELECT recid
  FROM addresses [c]
  JOIN min_pkgs_met [a] ON a.zip = c.zip AND a.crrt = a.crrt
)


[sqlite] Using CTE with INSERT

2015-10-13 Thread Don V Nielsen
The syntax "WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;" throws
the same exception: "[2015-10-13 08:20:22] [1] [SQLITE_ERROR] SQL error or
missing database (near ")": syntax error)"



On Tue, Oct 13, 2015 at 8:17 AM, Clemens Ladisch  wrote:

> Don V Nielsen wrote:
> > I'm struggling to implement an INSERT statement that get's is values
> from a
> > CTE.
> >
> > insert into vo_crrt_pieces (recid)
> > values (
> >   with
> >   pkg_controls AS (
> > SELECT * FROM d_pkg WHERE pkg_level = 'CRD'
> >   )
> >   -- select pieces that meeting pkg and pkg_level minimums
> >   , min_pkgs_met AS (
> >   ...
> >   )
> >   SELECT recid
> >   FROM addresses [c]
> >   JOIN min_pkgs_met [a] ON a.zip = c.zip AND a.crrt = a.crrt
> > )
>
> Without the database schema, this is impossible to test.
>
> A CTE is allowed where a SELECT is allowed, so you have to use the
> SELECT form of the INSERT statement:
>
>  CREATE TABLE t(x);
>  INSERT INTO t(x) SELECT 1;
>
>  INSERT INTO t(x) WITH p AS (SELECT 1) SELECT * FROM p; -- or:
>  WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] Using CTE with INSERT

2015-10-13 Thread Don V Nielsen
This is an IDE issue.  If I run my queries from the command line, all is
good.  Sorry for the confusion.  It is when I run my queries through my
Rubymine IDE that I am getting exceptions thrown.  I need to investigate
what version of Sqlite is being implemented.

Something learned is always a good experience,
dvn

On Tue, Oct 13, 2015 at 8:28 AM, Don V Nielsen 
wrote:

> The syntax "WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;" throws
> the same exception: "[2015-10-13 08:20:22] [1] [SQLITE_ERROR] SQL error or
> missing database (near ")": syntax error)"
>
>
>
> On Tue, Oct 13, 2015 at 8:17 AM, Clemens Ladisch 
> wrote:
>
>> Don V Nielsen wrote:
>> > I'm struggling to implement an INSERT statement that get's is values
>> from a
>> > CTE.
>> >
>> > insert into vo_crrt_pieces (recid)
>> > values (
>> >   with
>> >   pkg_controls AS (
>> > SELECT * FROM d_pkg WHERE pkg_level = 'CRD'
>> >   )
>> >   -- select pieces that meeting pkg and pkg_level minimums
>> >   , min_pkgs_met AS (
>> >   ...
>> >   )
>> >   SELECT recid
>> >   FROM addresses [c]
>> >   JOIN min_pkgs_met [a] ON a.zip = c.zip AND a.crrt = a.crrt
>> > )
>>
>> Without the database schema, this is impossible to test.
>>
>> A CTE is allowed where a SELECT is allowed, so you have to use the
>> SELECT form of the INSERT statement:
>>
>>  CREATE TABLE t(x);
>>  INSERT INTO t(x) SELECT 1;
>>
>>  INSERT INTO t(x) WITH p AS (SELECT 1) SELECT * FROM p; -- or:
>>  WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;
>>
>>
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>


[sqlite] sqlite window functions extension?

2015-10-14 Thread Don V Nielsen
Is there an extension to sqlite the provide window functions such as
row_number over and partition?  There are plenty of googles seeking said
functionality.

What am I looking to do?  Using a table X, I've built a summary table,
calculating a value called net_non_pieces.  net_not_pieces is a qty of rows
from a group rows that needs to be recoded to a different value.  So what I
want to do is for each group in X, look up the calculated value in
crrt_net_non, then grab the first net_non_pieces (sequenced, of course) and
apply a code to those rows, leaving the remaining rows alone.

I'm not sure how to accomplish such a thing

CREATE TABLE crrt_net_non -- developed from table X
(
zip TEXT,   --composite key
crrt TEXT,  --composite key
pkg_pieces integer,  --total row in group
sel_pieces integer,
non_pieces integer,
net_non_pieces integer  --qty of this group to recode
);

Its a pretty innocent thing to do with window functions.  It is not trivial
to do it without.  I can't even figure out how.

Why am I not dumping Sqlite and going with prostgres or the like?  Because
sqlite is awesome.  It is a portable file, easily created as I need for
each project, that can be moved around or deleted, making it super easy to
maintenance in my production environment.  Love the thing.  But this
problem has me flummuxed.

Thanks for your time and consideration,
dvn


[sqlite] sqlite window functions extension?

2015-10-14 Thread Don V Nielsen
X has columns zip & crrt, just like crrt_net_non.  These form a composite
key identifying groups within x.  A value "53001.R501" would be an
example...53001 being the zip code and R501 being the carrier route.  There
are 52 rows in X that have the key 53001.R501.  A calculation determined
that I need 42 rows from that key and saved the result in crrt_net_non, the
row looking like "53001.R501.52.6.46.42".  What I need is a sql function
that can iterate over crrt_net_non, then grab the rows from X, "53001.R501"
being first key, sort them into an internal group sequence, then update a
code of the first 42 rows of that sorted group, and then doing this until
crrt_net_non is exhausted.

On Wed, Oct 14, 2015 at 4:03 PM, Igor Tandetnik  wrote:

> On 10/14/2015 4:49 PM, Don V Nielsen wrote:
>
>> What am I looking to do?  Using a table X, I've built a summary table,
>> calculating a value called net_non_pieces.  net_not_pieces is a qty of
>> rows
>> from a group rows that needs to be recoded to a different value.  So what
>> I
>> want to do is for each group in X, look up the calculated value in
>> crrt_net_non, then grab the first net_non_pieces (sequenced, of course)
>> and
>> apply a code to those rows, leaving the remaining rows alone.
>>
>
> It might help if you show the definition of X, a sample of data in it, and
> the desired result of the query over that data. I, for one, have difficulty
> following your description.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] sqlite window functions extension?

2015-10-14 Thread Don V Nielsen
Table crrt_net_non_pieces:

zip crrt   pkg_pieces   sel_pieces   non_pieces   net_non_pieces
53001   R501   52   646   42
53001   R502   34   727   24


Addresses (as I have been referring to as X) is going to have too many rows
(86 for this example, 52 rows for 53001.R501 and 34 rows for 53001.R502.
The calculation result says I need 42 of the 52 X rows from 53001.R501 to
be recoded and 24 of the 34 X rows from 53001.R502.

Addresses table:

zip crrt   segment   version_id
53001   R501   060060
53001   R501   060060
53001   R501   060060
53001   R501   060060
53001   R501   060060

In sql server, I would be doing something like this:

  select x.* ,'xx' as segment from ( select -- count(1) a.zip ,a.crrt
,a.version_id ,ROW_NUMBER() OVER ( PARTITION BY a.zip,a.crrt ORDER BY CASE WHEN
a.version_id = '0060' THEN 0 WHEN a.version_id = '0064' THEN 2 WHEN
a.version_id = '0061' THEN 3 ELSE 99 END ASC ) AS NUM from
dbwork..addresses a )x join dbwork..crrt_net_non y on y.zip = x.zip and
y.crrt = x.crrt where x.num <= y.net_non_pieces ;
And the results would come out something like the following:  I truncate
the rows with num values 1..whatever.  Notice NUM is the sequential value
applied to the input (1..52 in the case of R501 and 1..34 in the case of
R502).  I then only kept the number of rows as identified by the
corresponding y.net_non_pieces value.

zip crrt   version_id   NUM   segment
[truncated num 1..38]
53001   R501   0060 39xx
53001   R501   0060 40xx
53001   R501   0060 41xx
53001   R501   0060 42xx
[truncated num 1..24]
53001   R502   0060 21xx
53001   R502   0060 22xx
53001   R502   0060 23xx
53001   R502   0060 24xx


Is this better?  Sorry that I imagined/assumed that you guys just picture
this stuff in your head.  I get that impression reading this list.

dvn


These window functions seem to be some sort of Holy Grail that I just can't
imagine duplicating without it.


On Wed, Oct 14, 2015 at 4:27 PM, R.Smith  wrote:

>
>
> On 2015-10-14 11:20 PM, Don V Nielsen wrote:
>
>> X has columns zip & crrt, just like crrt_net_non.  These form a composite
>> key identifying groups within x.  A value "53001.R501" would be an
>> example...53001 being the zip code and R501 being the carrier route.
>> There
>> are 52 rows in X that have the key 53001.R501.  A calculation determined
>> that I need 42 rows from that key and saved the result in crrt_net_non,
>> the
>> row looking like "53001.R501.52.6.46.42".  What I need is a sql function
>> that can iterate over crrt_net_non, then grab the rows from X,
>> "53001.R501"
>> being first key, sort them into an internal group sequence, then update a
>> code of the first 42 rows of that sorted group, and then doing this until
>> crrt_net_non is exhausted.
>>
>
> Hi Don, usually a CTE can be pressed into service to make things work.
> Maybe others know exactly what you mean here but it's a bit Greek to me.
>
> As Igor suggested - perhaps some data to accompany this schema and an
> example result-set (or resulting updated table) from it will make it clear
> what you want to achieve with the query. Feel free to simplify so the
> essence of what is needed remain, but be sure to give an example that
> cannot be achieved by other arbitrary means.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] sqlite window functions extension?

2015-10-14 Thread Don V Nielsen
The only reason NUM exists in the result set is so I could use it to limit
the outer most select.

But I am having problems with the LIMIT statement.  It throws an exception
no matter what table alias is used: X or x2.  It says "no such column".

Thanks for your time, by the way.

On Wed, Oct 14, 2015 at 6:08 PM, Igor Tandetnik  wrote:

> On 10/14/2015 6:24 PM, Don V Nielsen wrote:
>
>> zip crrt   version_id   NUM   segment
>> [truncated num 1..38]
>> 53001   R501   0060 39xx
>> 53001   R501   0060 40xx
>> 53001   R501   0060 41xx
>> 53001   R501   0060 42xx
>> [truncated num 1..24]
>> 53001   R502   0060 21xx
>> 53001   R502   0060 22xx
>> 53001   R502   0060 23xx
>> 53001   R502   0060 24xx
>>
>
> select * from addresses X where rowid in (
>   select x2.rowid from addresses x2 where X.zip=x2.zip and X.crrt=x2.crrt
>   order by (CASE ...)
>   limit ifnull( (
>  select net_non_pieces from crrt_net_non net
>  where X.zip=net.zip and X.crrt=net.crrt
>   ), 0)
> );
>
> Do you actually need NUM column in the resultset? That one would be tricky
> to pull off.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] sqlite window functions extension?

2015-10-15 Thread Don V Nielsen
The correlated query was not accepted in the select statement.  However, I
modified your original UPDATE query, as follows, and it did execute, and
passed the proper quantity of rows for the first zip/crrt combination,
only.  The rows were not updated.  It counted correctly, but did not change
the values.

sql> UPDATE tmp_addresses
  set segment='xx'
  where rowid in (
select x2.rowid from tmp_addresses x2 where x2.zip=zip and x2.crrt=crrt
order by
  CASE
  WHEN x2.version_id = '0060' THEN 0
  WHEN x2.version_id = '0064' THEN 2
  WHEN x2.version_id = '0061' THEN 3
  ELSE 99
  END ASC
limit ifnull( (
  select net_non_pieces from crrt_net_non net
  where net.zip=zip and net.crrt=crrt
), 0)
  )
[2015-10-15 06:54:18] *42 row(s)* affected in 189ms

I have not yet attempted your most recent suggestion.  I will get to that
some time today.



On Wed, Oct 14, 2015 at 9:21 PM, Igor Tandetnik  wrote:

> On 10/14/2015 9:29 PM, Don V Nielsen wrote:
>
>> But I am having problems with the LIMIT statement.  It throws an exception
>> no matter what table alias is used: X or x2.  It says "no such column".
>>
>
> Ah, interesting. LIMIT clause doesn't appear to allow correlated
> subqueries; only self-contained expressions. Here goes that idea.
>
> Something like this should work:
>
> select X.*
> from addresses X join crrt_net_non_pieces using (zip, crrt)
> where net_non_pieces > (
>   select count(*) from addresses x2
>   where X.zip=x2.zip and X.crrt=x2.crrt and
>   (x2.version_id < X.version_id or (x2.version_id = X.version_id and
> x2.rowid < X.rowid))
> );
>
> I took the liberty to simplify the ordering expression, for purposes of
> exposition (it's very long and would need to be repeated four times).
> Replace all occurrences of T.version_id with your CASE clause, calculated
> against table T. Also, I'm breaking ties by rowid; your original problem
> statement is underspecified unless there's a total order on Addresses.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] sqlite window functions extension?

2015-10-15 Thread Don V Nielsen
I'm surprised that and extension for this type of functionality has not
been been developed by someone with the c/c++.  It's seems like a natural
fit.  I wish I had the kind of ability & smarts to do it.

On Thu, Oct 15, 2015 at 9:04 AM, Igor Tandetnik  wrote:

> On 10/15/2015 9:36 AM, Don V Nielsen wrote:
>
>>  limit ifnull( (
>>select net_non_pieces from crrt_net_non net
>>where net.zip=zip and net.crrt=crrt
>>
>
> I suspect this WHERE clause is equivalent to "where net.zip=net.zip and
> net.crrt=net.crrt" - that is, always true. The actual limit value comes
> from whichever row accidentally happens to be first.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] sqlite window functions extension?

2015-10-16 Thread Don V Nielsen
The final solution at this point is Ruby.  I really want to push everything
I can into Sqlite because *it is so freakin fast!*  Unfortunately. I I am
just not getting it.  Also unfortunately, iterating, getting data, and
updating data in scripting languages is not efficient.  I've implemented a
transaction for each crrt_net_non iteration, but that is still painfully
slow.  I will have to be more creative in my data handling (move to dbi
versus activerecord) and partitioning of transactions (committing after
every 100,000 updates, for example.)

NetNon.all.each {|nn|
  ActiveRecord::Base.transaction {
addrs = Address.
where(zip:nn.zip,crrt:nn.crrt,version_id:non_sel_ver.ver_id).
select(:id,:segment).
take(nn.net_non_pieces)
addrs.each {|addr| addr.update(segment:'xx') }
  }
  pb.inc
}

In English..get all rows from crrt_net_non: it has the control quantity
net_non_pieces.  Select from addresses all rows with the same zip and crrt
values, and matching the version to be updated.  I only need columns id and
segment, and take only the number of records as calculated in
net_non_pieces.  Update the segment code.  Loop.

On Thu, Oct 15, 2015 at 9:47 AM, Don V Nielsen 
wrote:

> I'm surprised that and extension for this type of functionality has not
> been been developed by someone with the c/c++.  It's seems like a natural
> fit.  I wish I had the kind of ability & smarts to do it.
>
> On Thu, Oct 15, 2015 at 9:04 AM, Igor Tandetnik 
> wrote:
>
>> On 10/15/2015 9:36 AM, Don V Nielsen wrote:
>>
>>>  limit ifnull( (
>>>select net_non_pieces from crrt_net_non net
>>>where net.zip=zip and net.crrt=crrt
>>>
>>
>> I suspect this WHERE clause is equivalent to "where net.zip=net.zip and
>> net.crrt=net.crrt" - that is, always true. The actual limit value comes
>> from whichever row accidentally happens to be first.
>>
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>


[sqlite] Puzzled about table alias and where they are accessible

2015-10-16 Thread Don V Nielsen
Given the following:

select * from tmp_addresses [x]
where
  rowid in (
select rowid
from tmp_addresses [a]
where a.version_id = '0060'
  and [a].zip = [x].zip and [a].crrt = [x].crrt
limit (
  SELECT net_non_pieces
  FROM crrt_net_non [b]
  WHERE [b].zip = [x].zip AND [b].crrt = [x].crrt
)
  )

The above sql fails parsing because of the alias [x] usage in the limit
clause.  If I eliminate the [x] alias from the limit, i.e. "WHERE [b].zip =
zip AND [b].crrt = crrt", then the sql parses and executes.  Note the
performance: [2015-10-16 13:49:37] completed in 2m 30s 730ms.

I'm curious as to why the alias is not accessible from the limit sub-query?


[sqlite] sqlite window functions extension?

2015-10-16 Thread Don V Nielsen
Wrapping up this thread.

What I am going to go with is the following.  I'm using the initial WITH to
reduce as much as possible the number of records to be repeatedly searched:
maybe 25/30%.  The addresses table currently has 4.4mm rows; it will go up
to 11.5mm when this goes to prod.  I'm testing performance right now.
Might not be too good.

begin transaction;
with ver_addresses AS (
  SELECT rowid as id,zip,crrt
  from addresses
  where version_id = '0060'
)
update addresses
set segment = '60'
where
  rowid in (
select id
from ver_addresses [a]
where [a].zip = [addresses].zip and [a].crrt = [addresses].crrt
limit (
  SELECT net_non_pieces
  FROM crrt_net_non [b]
  WHERE [b].zip = zip AND [b].crrt = crrt
)
  );
commit;


On Fri, Oct 16, 2015 at 9:24 AM, Don V Nielsen 
wrote:

> The final solution at this point is Ruby.  I really want to push
> everything I can into Sqlite because *it is so freakin fast!*
> Unfortunately. I I am just not getting it.  Also unfortunately, iterating,
> getting data, and updating data in scripting languages is not efficient.
> I've implemented a transaction for each crrt_net_non iteration, but that is
> still painfully slow.  I will have to be more creative in my data handling
> (move to dbi versus activerecord) and partitioning of transactions
> (committing after every 100,000 updates, for example.)
>
> NetNon.all.each {|nn|
>   ActiveRecord::Base.transaction {
> addrs = Address.
> where(zip:nn.zip,crrt:nn.crrt,version_id:non_sel_ver.ver_id).
> select(:id,:segment).
> take(nn.net_non_pieces)
> addrs.each {|addr| addr.update(segment:'xx') }
>   }
>   pb.inc
> }
>
> In English..get all rows from crrt_net_non: it has the control quantity
> net_non_pieces.  Select from addresses all rows with the same zip and crrt
> values, and matching the version to be updated.  I only need columns id and
> segment, and take only the number of records as calculated in
> net_non_pieces.  Update the segment code.  Loop.
>
> On Thu, Oct 15, 2015 at 9:47 AM, Don V Nielsen 
> wrote:
>
>> I'm surprised that and extension for this type of functionality has not
>> been been developed by someone with the c/c++.  It's seems like a natural
>> fit.  I wish I had the kind of ability & smarts to do it.
>>
>> On Thu, Oct 15, 2015 at 9:04 AM, Igor Tandetnik 
>> wrote:
>>
>>> On 10/15/2015 9:36 AM, Don V Nielsen wrote:
>>>
>>>>  limit ifnull( (
>>>>select net_non_pieces from crrt_net_non net
>>>>where net.zip=zip and net.crrt=crrt
>>>>
>>>
>>> I suspect this WHERE clause is equivalent to "where net.zip=net.zip and
>>> net.crrt=net.crrt" - that is, always true. The actual limit value comes
>>> from whichever row accidentally happens to be first.
>>>
>>> --
>>> Igor Tandetnik
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>


[sqlite] sqlite window functions extension?

2015-10-16 Thread Don V Nielsen
I'm disappointed.  I killed it after 1.5hrs, and suspected everything you
explained in the other thread.  It is always true.

Que Sera Sera

dvn

On Fri, Oct 16, 2015 at 2:33 PM, Igor Tandetnik  wrote:

> On 10/16/2015 3:23 PM, Don V Nielsen wrote:
>
>>  limit (
>>SELECT net_non_pieces
>>FROM crrt_net_non [b]
>>WHERE [b].zip = zip AND [b].crrt = crrt
>>
>
> Again, I don't think this does what you think it does. Test with at least
> two rows in crrt_net_non, with different values for net_non_pieces, and
> confirm that as many rows are being updated as you've hoped. I suspect you
> may be disappointed.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] Parsing the contents of a field

2016-01-13 Thread Don V Nielsen
You will thank yourself by using a scripting language such Ruby, php, or
python.

Is there a reg ex library for sqlite that could be employed?

On Tue, Jan 12, 2016 at 11:42 PM, audio muze  wrote:

> I have a table of roughly 500k records with a number of fields
> containing delimited text that needs to be parsed and written to
> separate tables as a master lists.  In order to do this I need to
> parse the field contents, however, I don't see any functions within
> SQLite to enable that.  The number of delimited entries embedded in a
> field can vary from none to as man as 20/30.  Is there an addin I can
> compile with SQLite that provides the ability to parse a string?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] SQLite Pronunciation

2016-03-17 Thread Don V Nielsen
WHAT A GREAT VIDEO.  Thanks for sharing.

Everyone I work with...or worked with...that sees me work with SQLite
(sequel lite to me) asks...what is that?  why are using that?  I love it.
It is the best configuration manager.

On Wed, Mar 16, 2016 at 2:09 PM, Jay Kreibich  wrote:

>
>
> IIRC, the SQL ISO standard defines the pronunciation of the term ?SQL? as
> "ess-cue-ell?, although if you trace SQL back to the IBM days of SEQUEL,
> there is a strong argument that the term ?sequel? makes more sense.
>
> I know when the SQLite development team speaks about it, they tend to use
> the term ?ess cue ell lite? (technically doubling the ?L?).
>
> https://www.youtube.com/watch?v=Jib2AmRb_rk
>
> When writing the book ?Using SQLite,? I actually had a rather long
> discussion with my editor about this, since the pronunciation affects the
> choice of words when talking about ?an ess-cue-ell lite database? vs ?a
> sequel-lite database? (?an? vs ?a?).  Knowing the development team tends to
> pronounce the letters, I went with ?an.?
>
>   -j
>
>
>
> On Mar 16, 2016, at 1:38 PM, danap at dandymadeproductions.com wrote:
>
> > Hello,
> >
> > Please grant me some leeway here since as someone who has
> > not been in an academic school for years and is mainly self
> > taught. I have Mainly deriving information from reading
> > manuals and occasionally viewing some videos.
> >
> > Maybe I'm wrong, but according to Wikepedia SQLite appears
> > to be pronounced the same has it is spelled,
> > (sikwl.lat).
> > Maybe not a long A there perhaps.
> >
> > Where as I first heard Microsoft's MSSQL pronounce (sequent),
> > which I have also heard in academic videos by professors.
> > Following that logic, SQLite, (sequent.light)?
> >
> > Dana Proctor
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] SQLite Pronunciation

2016-03-17 Thread Don V Nielsen
> appear to know slightly what I was talking about

O, I'm confident that I'm not the only one that relates to that
assertion

On Thu, Mar 17, 2016 at 12:25 PM,  wrote:

> > On 3/16/16, Daniel Telvock  wrote:
> >
> >> I was at the Investigative Reporters and Editors Conference last week
> >> and the presenter for SQLite courses 1 and 3 said that it is actually
> >> pronounced SQ Lite. Even he thought that was odd considering SQL is a
> >> term or acronym.
> >
> > I wrote SQLite, and I think it should be pronounced "S-Q-L-ite".  Like
> > a mineral.  But I'm cool with y'all pronouncing it any way you want. :-)
> >
> >
> > --
> > D. Richard Hipp
> > drh at sqlite.org
>
>
> Thank you all for the responses, video reference, and apologizes for
> the origin post of pasting directly from the Wikepedia webpage, they
> have (eskju:el'lait) or (si:kwel.lait).
>
> I'm making some tutorial videos for my project and just wished to
> appear to know slightly what I was talking about when using SQLite,
> pronouncing, for the examples database connection for demonstration.
>
> Dana Proctor
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] sqlite fixed data loading extension

2016-03-25 Thread Don V Nielsen
I have a need for something that can parse and load into sqlite tables
fixed length data. I know sqlite has a csv import, so I would like to
duplicate that kind of functionality handling fixed columnar data. I
thought an extension would be perfect so I could specify something as
simple as "sqlite3 -fx parameter.dat" on the command line and it would
import the data.

Has anyone written a data loading extension already that would be willing
to share the source code with me? I have not written an sqlite extension
before, I know C# not C/C++, and leveraging someone else's effort would
help me a lot in learning the language and the extension. I've already
written this type of sqlite data loading logic into a specific application
using C#, but I would like it to create something more generic and
generally usable by anyone.

I've seen a lot of traffic on the mailing list about sqlite's csv import
abilities and wondered if someone has improved them with their own
extension. (I guess this begets the question..."Is sqlite's csv import an
extension?")

All the applications I have had experience with in the past (Sqlite Expert,
Navicat, and some others) require the user to run an application and setup
the functionality each and every time you used it. They do not give the
ability to save the import setup into a meaningful parameters file that can
be edited an rerun from the command line. So these types of applications
are ruled out. I would think I could do this in some fashion with
Informatica or like professional product, but at the expense of $$$ and
more $.

Any observations, comment, or suggestions? Is there a different mail list I
should hit up?

Thanks for your time and consideration
dvn


"My dad said to me as a child, 'Why be taught what you can learn on your
own. That's why he would just throw me in the lake...so he could learn
CPR." - Anthony Jeselnik.


[sqlite] sqlite fixed data loading extension

2016-03-27 Thread Don V Nielsen
Well, like I mentioned, I do have the basic functionality that I want in an
existing C# application. Unfortunately it works in the opposite direction
that I was hoping...meaning it implements Sqlite and not Sqlite
implementing it. The functionality would be more generally usable if Sqlite
could implement that logic as an extension.

And maybe I could leverage that experience into developing a window
extension...row_number over( partition by/order by ).  Couple years ago I
was seeking said functionality from Sqlite but none existed.  And now that
I think about it, I probably can't avoid said C# application because of the
lack of windowing. I needed at that time some kind of compiled language
because I could not get the i/o performance I need from a scripting
language.

[it's been a day since I drafted the above email. but now these thoughts
have crossed my mind]

Has anyone else attempted writing a window extension for Sqlite? With
enough people requesting it, what difficulties are involved such that it is
low on a development list? What negatives does a window extension impose
that make it undesirable. What am I in for and what advice can you provide?

I probably can get away with a scripting language controlling my process. A
lot of the effort can be handed off to Sqlite in batch, handling the
queries of 20mm rows.  The final result set that needs to be windowed is
typically 5/10m rows, which should be pretty manageable...performance wise.

dvn - just the ramblings of a simple mind.



On Fri, Mar 25, 2016 at 4:48 PM, James K. Lowden 
wrote:

> On Fri, 25 Mar 2016 06:49:22 -0500
> Don V Nielsen  wrote:
>
> > I have a need for something that can parse and load into sqlite tables
> > fixed length data.
>
> Insert your own separators.
>
> $ cat input
> 12345678910111213141516171819202122232425
>
> Print two 5-byte ranges separated by ", ".
>
> $ awk '{ OFS=", "; print substr($0, 1, 5), substr($0, 6, 5); }'  \
> input
> 12345, 67891
>
> --jkl
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] Help needed for COPY Command.

2016-03-28 Thread Don V Nielsen
You need to handle the load yourself.  Since you are talking bulk load, a
scripting language is probably out of the question because they are too
slow.  I used C# with the System.Data.Sqlite binary.

The db handling you need to worry about is the following:

connection to db
prepare an insert statement
read your input
while not eof
  populate prepared insert arguments
  execute the insert
  read your input
end
close connection

It's about that simple.  Depending on how many millions your are loading,
you might want to setup a transaction cycle and commit every so often.  I
think I committed every 1mm records.  It is a very fast load.

With what I was doing at the time, I started using Ruby because it was
quick to implement (less than 5 minutes).  But the execution time of the
load was going to be 24hrs or in that range.  Using a compiled language
(C#) the load happened in about 15 minutes (~750m a minute).  So the time
it took to write in C# was worth it.

If this is one time load, I have used small sqlite managers to do the bulk
load (Sqlite Expert to be exact.)  The bulk load is very fast.  But you
must go through the interactive wizard every time to do a load.  You cannot
save the setup and repeat the action from the command line, unfortunately.

dvn

On Mon, Mar 28, 2016 at 2:45 AM, Simon Slavin  wrote:

>
> On 28 Mar 2016, at 6:49am, Mahi Gurram  wrote:
>
> > I have tried .import and its working for command line interface. But i
> need
> > to do a bulk import (copy data from file into table) from c interface.
>
> Sorry, but SQLite has no facilities for handling text files.  It reads and
> writes to its own database files only.  You will have to write your own
> routines to read text from your text files.
>
> You might instead like to use system calls to script the command line
> shell:
>
> https://www.sqlite.org/cli.html
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] Unhandled Exception: System.TypeInitializationException

2013-04-05 Thread Don V Nielsen
Thanks for reading.  I am getting the following error moving a 32 bit
application from Windows Server 2003 to WS2008.  This is a straight copy
from one computer to another.  It runs fine on WS2003 and not so good on
WS2008.  The application was compiled with VS2005.  I do not have a
development environment on the WS2008.  I have not cleared licensing for
that, yet.

I'm not a developer savant.  I can code in C#, assemble, and run things.
 I've been googling this issue, but do not really understand what I am
reading.  All of you rank pretty high on my rankings, so I thought I would
ask you all for help.

Thanks for your time and consideration,
dvn

Unhandled Exception: System.TypeInitializationException: The type
initializer for 'CDG.AddAName.AAN' threw an exception. --->
System.BadImageFormatException: Could not load file or assembly
'System.Data.SQLite, Version=1.0.82.0, Culture=neutral,
PublicKeyToken=db937bc2d44ff139' or one of its dependencies. An attempt was
made to load a program with an incorrect format.
File name: 'System.Data.SQLite, Version=1.0.82.0, Culture=neutral,
PublicKeyToken=db937bc2d44ff139'
   at CDG.AddAName.AAN..cctor()

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value
[HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind
failure logging.
To turn this feature off, remove the registry value
[HKLM\Software\Microsoft\Fusion!EnableLog].

   --- End of inner exception stack trace ---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Unhandled Exception: System.TypeInitializationException

2013-04-06 Thread Don V Nielsen
TY sir.  I'm going to be a much smarter developer when this is all said and
done.  I have lots to learn.  Is everything cool with sqlite and VS2012?
 I'm in the process of installing VS2012/Express on my 64bit laptop in an
effort to get my education started.


On Sat, Apr 6, 2013 at 4:18 AM, Bernd <be...@web.de> wrote:

> Am 05.04.2013 22:19, schrieb Don V Nielsen:
>
>  Thanks for reading.  I am getting the following error moving a 32 bit
>> application from Windows Server 2003 to WS2008.  This is a straight copy
>> from one computer to another.  It runs fine on WS2003 and not so good on
>> WS2008.  The application was compiled with VS2005.  I do not have a
>> development environment on the WS2008.  I have not cleared licensing for
>> that, yet.
>>
>> I'm not a developer savant.  I can code in C#, assemble, and run things.
>>   I've been googling this issue, but do not really understand what I am
>> reading.  All of you rank pretty high on my rankings, so I thought I would
>> ask you all for help.
>>
>> Thanks for your time and consideration,
>> dvn
>>
>> Unhandled Exception: System.**TypeInitializationException: The type
>> initializer for 'CDG.AddAName.AAN' threw an exception. --->
>> System.**BadImageFormatException: Could not load file or assembly
>> 'System.Data.SQLite, Version=1.0.82.0, Culture=neutral,
>> PublicKeyToken=**db937bc2d44ff139' or one of its dependencies. An
>> attempt was
>> made to load a program with an incorrect format.
>> File name: 'System.Data.SQLite, Version=1.0.82.0, Culture=neutral,
>> PublicKeyToken=**db937bc2d44ff139'
>> at CDG.AddAName.AAN..cctor()
>>
>
> That looks a lot like your two systems do not have the same bitness. Could
> it be that your WS2008 is 64 bit? Have a look at
> https://system.data.sqlite.**org/index.html/doc/trunk/www/**downloads.wiki<https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki>
> to resolve this issue.
>
> Bernd
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] C# amalgamation hand holding requested

2012-01-20 Thread Don V Nielsen
I need help using the amalgamation in my projects.  I simply do not know
what steps to take in VS to implement it.

I have some fairly elaborate projects that I have been redeveloping to use
sqlite instead of .net structure and memory functions.  One project is
complete, but it uses the sqlite dll.  Recent conversations by users has
convinced me that I would be better off using the amalgamation file
instead.  However, I don't know what steps in VS to do to specify the
source and header files so that it compiles into my application.

Can someone walk through steps necessary for specifying properties,
references, and source code.  Again, this is C# development.  I've really
become self-aware just how little I know about VS in the terms of
application development.  I've been flying blind for quite some time.  I'm
just like a newbie.

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


Re: [sqlite] C# amalgamation hand holding requested

2012-01-24 Thread Don V Nielsen
Ok.  So I am doing things correctly.  I am already doing things correctly.
 I am using the provided .NET dll.  I copy it to my local project.  And
when that project moves to production, it will be in the project's .bin
folder.  I should not have to worry about the dll hell associated with HP
and Quicken and what not.

Thanks.  I was getting confused.

dvn


On Mon, Jan 23, 2012 at 2:03 PM, Simon Slavin  wrote:

>
> On 23 Jan 2012, at 7:39pm, Roosevelt Anderson wrote:
>
> > The C# compiler does not compile C. C# and VB.NET get compiled down to
> bytecode.
> >
> > On Mon, Jan 23, 2012 at 12:56 PM, Simon Slavin 
> wrote:
> >>
> >> On 23 Jan 2012, at 4:39pm, Roosevelt Anderson wrote:
> >>
> >>> You can't use the amalgamation directly in C# as the SQLite
> >>> amalgamation is in C.
> >>
> >> Can you not simply tell your compiler that '.c' files are C and not C#
> ?  That's what you do with Objective-C.  Or do C# compilers not compile C ?
>
> Ah.  So C# is not a superset of C.  That explains things.  Thanks.
>
> Simon.
> ___
> 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] C# amalgamation hand holding requested

2012-01-25 Thread Don V Nielsen
Yeah.  I've been stung by the GAC in the past.  When I first learned of it,
I thought it was the bee's knees and life would be rosy after that.
 Instead, it turned out to be a wolf in sheep's clothing.  I avoid it with
all my applications, now.

On Tue, Jan 24, 2012 at 10:22 AM, Roosevelt Anderson <
roosevelt.ander...@gmail.com> wrote:

> This is .NET development not regular Windows development. The .NET
> application most of the time will use the version of
> System.Data.SQLite.dll that is in the same directory as the executing
> application. The only time it will not use this version is if you load
> System.Data.SQLite.dll into the Global Assembly Cache which I do not
> recommend.
>
> On Tue, Jan 24, 2012 at 10:49 AM, Black, Michael (IS)
> <michael.bla...@ngc.com> wrote:
> > Yes --  you do need to worry.
> >
> > Plusyou apparently should rename the DLL so it doesn't collide with
> any others from what I can discern from this:
> >
> >
> http://msdn.microsoft.com/en-us/library/windows/desktop/ms682586(v=vs.85).aspx
> >
> >
> >
> > Michael D. Black
> >
> > Senior Scientist
> >
> > Advanced Analytics Directorate
> >
> > Advanced GEOINT Solutions Operating Unit
> >
> > Northrop Grumman Information Systems
> >
> > 
> > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Don V Nielsen [donvniel...@gmail.com]
> > Sent: Tuesday, January 24, 2012 9:39 AM
> > To: General Discussion of SQLite Database
> > Subject: EXT :Re: [sqlite] C# amalgamation hand holding requested
> >
> > Ok.  So I am doing things correctly.  I am already doing things
> correctly.
> >  I am using the provided .NET dll.  I copy it to my local project.  And
> > when that project moves to production, it will be in the project's .bin
> > folder.  I should not have to worry about the dll hell associated with HP
> > and Quicken and what not.
> >
> > Thanks.  I was getting confused.
> >
> > dvn
> >
> >
> > On Mon, Jan 23, 2012 at 2:03 PM, Simon Slavin <slav...@bigfraud.org>
> wrote:
> >
> >>
> >> On 23 Jan 2012, at 7:39pm, Roosevelt Anderson wrote:
> >>
> >> > The C# compiler does not compile C. C# and VB.NET get compiled down
> to
> >> bytecode.
> >> >
> >> > On Mon, Jan 23, 2012 at 12:56 PM, Simon Slavin <slav...@bigfraud.org>
> >> wrote:
> >> >>
> >> >> On 23 Jan 2012, at 4:39pm, Roosevelt Anderson wrote:
> >> >>
> >> >>> You can't use the amalgamation directly in C# as the SQLite
> >> >>> amalgamation is in C.
> >> >>
> >> >> Can you not simply tell your compiler that '.c' files are C and not
> C#
> >> ?  That's what you do with Objective-C.  Or do C# compilers not compile
> C ?
> >>
> >> Ah.  So C# is not a superset of C.  That explains things.  Thanks.
> >>
> >> Simon.
> >> ___
> >> 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-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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] support of %y in strftime()

2012-02-03 Thread Don V Nielsen
But if we don't start worrying about the year 10,000 now, then we'll end of
having another disaster like Y2k.  Oh yeah, that amounted to big fat thud.
 Never mind.

:)

On Fri, Feb 3, 2012 at 8:39 AM, Black, Michael (IS)
wrote:

> Non-standard ISO formatalready doable like this:
>
>
>
> sqlite> select substr(strftime("%Y",'now'),3,2);
> 12
>
>
>
> This will work until the year 10,000 :-)
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of LacaK [la...@zoznam.sk]
> Sent: Friday, February 03, 2012 3:37 AM
> To: General Discussion of SQLite Database
> Subject: EXT :[sqlite] support of %y in strftime()
>
> Hi *,
> there is date-time formating function strftime(), which supports some
> (not all) string formating parameters (like %d, %m, %Y)
> There is %Y for 4-digit year.
> Is possible add also %y for 2-digit year ? (like in strftime() in
> standard C library)
> If it is not a big problem (I hope, that isn't), I would be very happy
> if it will be implemented.
> (can I post somewhere bug report, feature request)
> TIA
> -Laco.
> ___
> 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] Inserts get slower and slower

2012-02-09 Thread Don V Nielsen
I've noticed a similar thing happening.  The first 1/3rd loads quickly; the
remain 2/3rds stagnates.  It appears that there is some kind of bottleneck
happening.  I thought it was the SAN.

My application begins a transaction, does all its inserts, and then
commits.  There could be millions in the transaction.  Would it be better
processing to commit in batches, say 250m or 500m?

Now's the time for me to make these changes, as the application is being
prep'd for production.

dvn

On Wed, Feb 8, 2012 at 4:29 PM, Simon Slavin  wrote:

>
> On 8 Feb 2012, at 10:22pm, Oliver Peters wrote:
>
> > It's the Primary Key that you're using cause for every INSERT it is
> checked if unix_time is already present in a record.
> >
> > So the question is if you really need unix_time as a PK
>
> If you're batching your INSERTs up into transactions, try doing a VACUUM
> after each COMMIT.
>
> Simon.
> ___
> 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] SQLite shell dot commands

2012-02-24 Thread Don V Nielsen
Please, I don't mean this to be offensive.  I'm not.  It was suggested that
the syntax "[Ben's table]" is cumbersome.  What is really cumbersome, in my
opinion, is the table name itself.  The table name includes an white space
(space) and a delimiting character (apostrophe.)  The simple table name has
lots of junk in it that will throw a left hook to many language parsers.

To simplify everything, just name the table BensTable.  Camel case the
words, drop the spaces.  Everything is still very readable and makes sense.

dvn

On Thu, Feb 23, 2012 at 4:17 AM, Benoit Mortgat  wrote:

> I have a database with a table name containing a quote and a space.
> Let's say it's called “Ben's table”
>
> I have created it with:
>
> CREATE TABLE "Ben's table" ([column_spec]);
>
> I tried the following in the SQLite shell:
>
>.import 'file_name.txt' "Ben's table"
>
> But that does not work. So I had to dig a bit into shell.c to
> understand, and I found the following:
>
>  * inside the do_meta_command function, the arguments to dot-commands
>   are tokenized with the following rules:
>
>   * The tokenizer skips all whitespace character when finding the
> start for the next token.
>
>   * When ' or " is found where a token should start, the token will be
> the portion of text between that delimiter and its next occurrence.
> There is no possibility to escape the delimiter.
> The two delimiters are discarded.
>
>   * If any other character is found where a token should start, the
> token ends at whitespace.
>
>   * Backslashes get special processing except for tokens that were
> delimited by the single quote.
>
>  * inside the same do_meta_command method, when processing the import
>   command, the name of the table is appended to some queries this way:
>
>   sqlite3_snprintf(nByte+20, zSql, "INSERT INTO %s VALUES(?", zTable);
>
> So, my .import command was tokenized that way:
>
>Token 1: file_name.txt
>Token 2: Ben's table  <--- note: double quotes gone.
>
> And the SQL query that was forged was incorrect:
>
>INSERT INTO Ben's table VALUES(?...
>
> The only way I found in order to have my import succeed was:
>
>.import 'file_name.txt' "[Ben's table]"
>
> which I find cumbersome.
>
> Are there any plans to improve the tokenizer to enable escaping the
> delimiter (doubling the single quote or backslash-escaping the double
> quote), and to escape the table name in the dot-commands?
>
> Potential problem: how to use .backup or .restore with a file name that
> contains both ' " and space?
>
> Thanks.
>
> --
> Benoit Mortgat
> ___
> 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] Inserts get slower and slower

2012-02-24 Thread Don V Nielsen
Follow-up on this issue.

This morning I used a JetBrains product called dotTrace to analyze my
application's resource usage. I loaded a 7 million rows into a table.  When
I simply loaded the table, it took 12 minutes.  Pretty impressive.  When I
added 'Unique' to one of the fields definitions, the load time jumped to 90
minutes.

Naturally, most all the cpu consumption was cause by the various stream and
data readers in the application.  It's easy to understand, also, that
applying unique is going to cause addition hits against the database
(ensuring the unique field value does not already exist.)  The attached
image is the dotTrace output for this run.

What I did next was employ an internal hash in my application.  I attempt
to insert the unique value into the hash and catch the exception if it
exists.  The run time is now 14 minutes for the 10 million records.


On Thu, Feb 9, 2012 at 8:46 AM, Black, Michael (IS)
<michael.bla...@ngc.com>wrote:

> I think you may find you're running into buffer cache limits (not sqlite
> but OS limits).
>
>
>
> So the 1st third all fits into buffer cache.  Once it starts committing to
> disk things slow down a LOT.
>
>
>
> Since you're not showing an real times it's hard to say you are any slower
> than anybody else.
>
>
>
> I saw similar behavior on a project I was doing and it all boiled down to
> disk write speed once things started going to disk.
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> ____________
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Don V Nielsen [donvniel...@gmail.com]
> Sent: Thursday, February 09, 2012 8:14 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Inserts get slower and slower
>
> I've noticed a similar thing happening.  The first 1/3rd loads quickly; the
> remain 2/3rds stagnates.  It appears that there is some kind of bottleneck
> happening.  I thought it was the SAN.
>
> My application begins a transaction, does all its inserts, and then
> commits.  There could be millions in the transaction.  Would it be better
> processing to commit in batches, say 250m or 500m?
>
> Now's the time for me to make these changes, as the application is being
> prep'd for production.
>
> dvn
>
> On Wed, Feb 8, 2012 at 4:29 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> >
> > On 8 Feb 2012, at 10:22pm, Oliver Peters wrote:
> >
> > > It's the Primary Key that you're using cause for every INSERT it is
> > checked if unix_time is already present in a record.
> > >
> > > So the question is if you really need unix_time as a PK
> >
> > If you're batching your INSERTs up into transactions, try doing a VACUUM
> > after each COMMIT.
> >
> > Simon.
> > ___
> > 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-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] Limit on the Compound Select Statements

2012-02-24 Thread Don V Nielsen
Would it make more sense to put the values into a text file and import the
text file?  It separates the data from the application, and simplifies
making future changes to the list.

On Thu, Feb 23, 2012 at 1:52 PM, Abhinav Upadhyay <
er.abhinav.upadh...@gmail.com> wrote:

> On Thu, Feb 23, 2012 at 6:50 PM, Simon Slavin 
> wrote:
> >
> > On 23 Feb 2012, at 1:16pm, Abhinav Upadhyay <
> er.abhinav.upadh...@gmail.com> wrote:
> >
> >> I do not remember the
> >> exact error message but it close to this. As per the documentation on
> >> the compound select statements
> >> (http://www.sqlite.org/lang_select.html) on Sqlite website, there is
> >> no mention of an explicit limit. I would like to know the exact limit
> >> on this, so that I could my code to work within this limit
> >
> > 
> >
> > especially item 3, but also others.
> >
> > However, I question the advantage of doing one long INSERT rather than
> doing many inside a transaction.  Are you binding parameters ?
> >
>
> It was already inside a bigger transaction, I was trying out something
> naive and turns out it is not worth it. Thanks for the pointer  :)
>
> --
> Abhinav
> ___
> 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] Inserts get slower and slower

2012-02-24 Thread Don V Nielsen
Follow-up on this issue. (Re-Post of previous which included large .jpg.  I
converted .jpg to text [see below] to make message smaller, and then
deleted previous post.)

This morning I used a JetBrains product called dotTrace to analyze my
application's resource usage. I loaded a 7 million rows into a table.  When
I simply loaded the table, it took 12 minutes.  Pretty impressive.  When I
added 'Unique' to one of the fields definitions, the load time jumped to 90
minutes.

Naturally, most all the cpu consumption was cause by the various stream and
data readers in the application.  It's easy to understand, also, that
applying unique is going to cause addition hits against the database
(ensuring the unique field value does not already exist.)  The attached
image is the dotTrace output for this run.

What I did next was employ an internal hash in my application.  I attempt
to insert the unique value into the hash and catch the exception if it
exists.  The run time is now 14 minutes for the 10 million records.



4 -- Thread #1 • 30,628 ms
  99.38 % Main • 30,437 ms • CDGAddAName.AAN.Main(Sing[])
4 99.38 % InitializePools • 30,437 ms •
CDGAddName.AddPoolController.InitializePools
4 99.38 % LoadPool • 30,437 ms + CDGAddAName.AddPool.LoadPool
4 55.32 % save_rec_to_db • 16,45 ms •
CDGddAName.TblZipRoute.save_rec_to_db(Hashtable, String)
4 55.01 % ExecuteNonQuery • 16,850 ms •
System.Data.SQLite.SQLitecommand.ExecuteNonQuery
4 54.81 % ExecuteReader • 16,786 ms •
System.Data.SQLite.SQLitecommand.ExecuteReader(CommandBehavior)
4 54.75 % NextResuIt • 16,770 ms •
System.DataSQLite.SQLiteDataReader.NextResult
  52.42 % Step • 16,056 ms •
System.Data.SQLite.SqlLite3.Step(SQLiteStatement)
   2.17 % GetStatement • 665 ms •
System.Data.SQLite.SQLitecommand.GetStatement(1nt32)
   0.05 % Reset • 16 ms • System.Data.SQLite.SQLite3.Reset(SQLtteStatement)
   0.05 % CheckClosed • 16 ms •
System.Data.SQLIte.SQLiteDataReader.CheckClosed
   0.05 % InitializeForReader • 16 ms •
System.Data.SQLite.SQLitecommand.InitializeForReader
   0.10 % Dispose • 32 ms • System.Data.Common.DbDataReader.Dispose
   0.10 % Nexesult • 32 ms • System.Data.SQLiteSQLiteDataReader.NextResult
   0.26 % getltern • 79 ms • System.Collections.Hashtable.getItem(Object)
   0.05 % Eligible • 16 ms • CDG.AddAName.Global.Eligible(Hashtable)
4 43.95 % get_parsed_record • 13,451 ms
CDGAddAName.AddPool.get_parsed_record
4 43.95% GearsedRecord • 13,461 ms •
CDG.Util.FL_FirstLogic.FL_InputFile.GearsedRecord
4 42.87% ReadRecord • 13,129 ms •
CDG.UtiI.FL_Firsltogic.FL_Inputlile.ReadRecord
  42.07 % ReadLine • 13,129 ms • System.IO.StreamReader.ReadLine
   1.08 % Parse_Record • 332 ms •
CDG.UtiI.FL_FirstLogic.Parse_FmtDef.Parse(RecordString)
   0.62 % [Native or optirnized code] 191 ms
4 Thread #2 • 30,628 ms
 100.00 % [Native or optimized code] 30,628 ms


On Thu, Feb 23, 2012 at 4:02 PM, Don V Nielsen <donvniel...@gmail.com>wrote:

> Follow-up on this issue.
>
> This morning I used a JetBrains product called dotTrace to analyze my
> application's resource usage. I loaded a 7 million rows into a table.  When
> I simply loaded the table, it took 12 minutes.  Pretty impressive.  When I
> added 'Unique' to one of the fields definitions, the load time jumped to 90
> minutes.
>
> Naturally, most all the cpu consumption was cause by the various stream
> and data readers in the application.  It's easy to understand, also, that
> applying unique is going to cause addition hits against the database
> (ensuring the unique field value does not already exist.)  The attached
> image is the dotTrace output for this run.
>
> What I did next was employ an internal hash in my application.  I attempt
> to insert the unique value into the hash and catch the exception if it
> exists.  The run time is now 14 minutes for the 10 million records.
>
>
> On Thu, Feb 9, 2012 at 8:46 AM, Black, Michael (IS) <
> michael.bla...@ngc.com> wrote:
>
>> I think you may find you're running into buffer cache limits (not sqlite
>> but OS limits).
>>
>>
>>
>> So the 1st third all fits into buffer cache.  Once it starts committing
>> to disk things slow down a LOT.
>>
>>
>>
>> Since you're not showing an real times it's hard to say you are any
>> slower than anybody else.
>>
>>
>>
>> I saw similar behavior on a project I was doing and it all boiled down to
>> disk write speed once things started going to disk.
>>
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> Advanced Analytics Directorate
>>
>> Advanced GEOINT Solutions Operating Unit
>>
>> Northrop Grumman Information Systems
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on behalf of Don V Nielsen [donvniel

Re: [sqlite] read each line in sqlite database

2012-03-01 Thread Don V Nielsen
I think Simon's solution is in error, and Igor's is correct.  In Simon's
case, Slevel will be set to 1 if price1 is greater than 30.  However, the
original c function would set Slevel to 2 because price1 is greater than 12
and it is greater than 30.  Two increments of i are executed in that
scenario.  Igor's accumulating the boolean results gets you where you want
to be; it's just a little cryptic.

The original function was less than explicit.  I'm assuming some kind of
weighted value is being generated.  My solution is probably the most wordy
and least efficient performance wise, it is explicit in function.  It also
allows you to set the weight of each price1/price2 level.

UPDATE bb SET Slevel =
(
  (Case when price1>12 then 1
when price1>20 then 2
else 0
   End) +
  (Case when price2>20 then 1
when price2>30 then 2
when price2>80 then 3
else 0
   End)
);


On Thu, Mar 1, 2012 at 7:21 AM, Igor Tandetnik  wrote:

> YAN HONG YE  wrote:
> > I have a sqlite database named bb:
> >
> >> NamePrice1Price2Slevel
> >> A123 231  NULL
> >> A22212 NULL
> >> A3   21223   NULL
> >
> > My question is:
> > I want to update culumn Slevel by function myfunc():
> >
> > int myfunc():
> > {int i=0;
> > if (price1 >12)
> > i++;
> > if (price1>30)
> > i++;
> > if (price2>20)
> > i++;
> > if (price2>30)
> > i++;
> > if (price2>80)
> > i++;
> > return i;
> > }
>
> Just run this query:
>
> update bb set Slevel = (price1>12) + (price1>30) + (price2>20) +
> (price2>30) + (price2>80);
>
> --
> Igor Tandetnik
>
> ___
> 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] read each line in sqlite database

2012-03-01 Thread Don V Nielsen
Ya know, if you ever wanted to deviate from the SQL standard, a handy
upgrade to the CASE statement would permit split conditions on the case and
when statement, as in:

Case price1
   when > 12 then 1
   when > 30 then 2
   else 0
end

dvn

On Thu, Mar 1, 2012 at 12:43 PM, Don V Nielsen <donvniel...@gmail.com>wrote:

> I think Simon's solution is in error, and Igor's is correct.  In Simon's
> case, Slevel will be set to 1 if price1 is greater than 30.  However, the
> original c function would set Slevel to 2 because price1 is greater than 12
> and it is greater than 30.  Two increments of i are executed in that
> scenario.  Igor's accumulating the boolean results gets you where you want
> to be; it's just a little cryptic.
>
> The original function was less than explicit.  I'm assuming some kind of
> weighted value is being generated.  My solution is probably the most wordy
> and least efficient performance wise, it is explicit in function.  It also
> allows you to set the weight of each price1/price2 level.
>
> UPDATE bb SET Slevel =
> (
>   (Case when price1>12 then 1
> when price1>20 then 2
> else 0
>End) +
>   (Case when price2>20 then 1
> when price2>30 then 2
> when price2>80 then 3
> else 0
>End)
> );
>
>
> On Thu, Mar 1, 2012 at 7:21 AM, Igor Tandetnik <itandet...@mvps.org>wrote:
>
>> YAN HONG YE <yanhong...@mpsa.com> wrote:
>> > I have a sqlite database named bb:
>> >
>> >> NamePrice1Price2Slevel
>> >> A123 231  NULL
>> >> A22212 NULL
>> >> A3   21223   NULL
>> >
>> > My question is:
>> > I want to update culumn Slevel by function myfunc():
>> >
>> > int myfunc():
>> > {int i=0;
>> > if (price1 >12)
>> > i++;
>> > if (price1>30)
>> > i++;
>> > if (price2>20)
>> > i++;
>> > if (price2>30)
>> > i++;
>> > if (price2>80)
>> > i++;
>> > return i;
>> > }
>>
>> Just run this query:
>>
>> update bb set Slevel = (price1>12) + (price1>30) + (price2>20) +
>> (price2>30) + (price2>80);
>>
>> --
>> Igor Tandetnik
>>
>> ___
>> 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] What do people think of SQLite Root?

2012-03-07 Thread Don V Nielsen
Thumbs up on SqliteExpert.  I use it too.  Very nice.

dvn

On Tue, Mar 6, 2012 at 7:20 PM, Stephen Chrzanowski wrote:

> I use SQLite Expert.  There is a fee for the professional version, but it
> is one time, all updates are free.
>
> http://www.sqliteexpert.com/
>
>
> On Tue, Mar 6, 2012 at 1:43 PM, Fabio Spadaro  >wrote:
>
> > Hi.
> >
> > Il giorno 06 marzo 2012 17:31, Kit  ha scritto:
> >
> > > > Announcement of the release Sqlite Root 
> > >  now available for Linux.
> > > > Any feedback is appreciated.
> > > > Fabio Spadaro
> > >
> > > Two big problems:
> > > - license
> > > - size
> > >
> > > This software is unusable for me.
> > > --
> > > Kit
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> > What do you mean by "size".
> >
> > --
> > Fabio Spadaro
> >
> > Try Sqlite Root a GUI Admin Tools for manage Sqlite Database:
> > www.sqliteroot.com
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] why one row's results isn't the same between program and command shell

2012-03-21 Thread Don V Nielsen
Doh!

Know the feeling.  Well!

On Wed, Mar 21, 2012 at 2:38 PM, Simon Slavin  wrote:

>
> On 21 Mar 2012, at 7:35pm, Adam DeVita  wrote:
>
> > Sorry for false alarm.  Please disregard this thread:
> >
> > Solution: make sure you quit everything and isolate the code.  There is a
> > subsequent write to the new record that made it appear as a problem, when
> > it wasn't.
>
> We've all done it.  Glad you sorted it out.
>
> Simon.
> ___
> 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] 64-bit Windows Command Shell

2012-03-22 Thread Don V Nielsen
There is a natural 5th extrapolation:

5) Could sqlite3 take advantage of multiple cpu's by parsing a single task
into one thread per cpu and segment data to be worked by each thread?  Big
league stuff.  But I don't think sqlite3 is meant to compete in that
market.  It already exceeds expectations in its current market.

dvn

On Wed, Mar 21, 2012 at 6:57 PM, Udi Karni  wrote:

> Frankly I don't know if a 64-bit version and Big RAM would make a
> difference and if so - up to what point. With SQLite being a single process
> - assigned for the most part to a single CPU - even if everything was done
> in RAM - there is a limit to what 1 CPU can do.
>
> I am just noticing anecdotally that SQlite uses cache and dealing with
> tables of a few hundred MB or less doesn't seem to generate IO. Also - when
> there is IO - it often comes from the swap file (under Windows 7).
>
> So the questions are -
>
> (1) how much RAM is the point of diminishing returns on 32-bit
> (2) is there value to going 64-bit
> (3) if there was a 64-bit version - would it use more RAM more effectively?
> (4) as a fallback - let's say the 32-bit version and 4GB are as good as you
> can pretty much expect. Would getting a server with 4 CPUs and 16GB (a
> high-end home-version PC) - reasonably enable me to run 3-4 SQLite jobs
> concurrently? In other words - no great speed improvement per job - but in
> aggregate more work could get done?
>
> Thanks !
>
> On Wed, Mar 21, 2012 at 12:26 PM, Roger Binns 
> wrote:
>
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On 21/03/12 11:09, Black, Michael (IS) wrote:
> > > Cache is the primary (and obvious) thing I can think of.
> >
> > With a 32 bit compilation you'll be able to bump it up to about 2GB.
> > However by that point you will long have passed diminishing returns and
> > can just let the OS do its own caching.
> >
> > Roger
> > -BEGIN PGP SIGNATURE-
> > Version: GnuPG v1.4.11 (GNU/Linux)
> >
> > iEYEARECAAYFAk9qK2IACgkQmOOfHg372QQVdwCfbJTAzhCPR4ARPxhYHewLvvcT
> > 4lYAoI4QFXFfxILtsQGxVWm8BRM/mbIX
> > =e0aW
> >  -END PGP SIGNATURE-
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] User input checking

2012-03-23 Thread Don V Nielsen
Ruby on Rails -- ActiveRecord.  ActiveRecord prevents sql injections, I
think.  Using ERB in the html would give you that kind of functionality.
 Correct?

dvn

On Fri, Mar 23, 2012 at 2:49 AM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> I also forgot to mention doing all that stuff on a second connection.
> Open that one read-only (SQLITE_OPEN_READONLY).
>
> You can also double check a statement makes no changes:
>
>  http://www.sqlite.org/c3ref/stmt_readonly.htmla
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk9sKu4ACgkQmOOfHg372QT/WgCeMahjkI40RyhgvltXMfpE/cax
> SPQAniSVzBeLj+KRIr33DyyOs/+RA0aa
> =opYb
> -END PGP SIGNATURE-
> ___
> 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] Downgrade support for OS/2?

2012-04-03 Thread Don V Nielsen
Well.  I know some banks and some transportation systems still use OS/2.
 Do they use sqlite?  If yes, I'm sure their apps are stable, meaning their
sqlite implementation is stable.  Does sqlite need to continue for OS/2?
 Most likely not.

Death of a friend.  I always liked OS/2.  It's still better than Windows
with regard to file properties.

dvn

On Tue, Apr 3, 2012 at 12:44 PM, Michael Steiger wrote:

> On 03.04.2012 19:38 Richard Hipp said the following:
>
>  We propose to remove the VFS module for OS/2 from the SQLite amalgamation
>> in the next release, reducing the size of the amalgamation source file by
>> 1924 lines (or about 1.4%).
>>
>> If this change will cause you any serious hardship, please speak up and we
>> will reconsider.
>>
>>
> And this one day after the 25th anniversary of the announcement of OS/2.
> RIP OS/2 and RIP OS/2 support in sqlite.
>
>
> __**_
> 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] Parameterized SQL

2012-06-15 Thread Don V Nielsen
I use Ruby.  In the following example I use a replacement tag "#{lvl}" in
the scripts.  For each of the levels (indv,hhld,resi), I do a substitution
against the script and pass the script to sqlite.

dbname =
File.join('d:','cloveretl','projects','test_ii','data-out-97','test.db')
db = SQLite3::Database.new(dbname)
script_folder = File.join(prj.ps_project_folder,'scripts')

scripts_to_run = [
  '02_build_relationships.sql',
  '05_cdi_perspective.sql',
  '05_itrack_perspective.sql',
  '10_cdi_select',
  '10_itrack_select'
]

['indv','hhld','resi'].each {|lvl|
  puts "Executing level: #{lvl}"
  scripts_to_run.each {|sql|
puts "  script: #{sql}"
script = File.open(File.join(script_folder,sql)).readlines(sep=nil)[0]
db.execute_batch(script.gsub('#{lvl}',lvl))
  }
}


On Fri, Jun 15, 2012 at 5:01 AM, Niall O'Reilly wrote:

>
> On 15 Jun 2012, at 10:45, Udi Karni wrote:
>
> > Niall - thanks. If I understand correctly - you use bash to do the
> > preprocessing of the substitutions and submit the prepared statements to
> > Sqlite.
>
> Well, 'prepared' is not the term I would use, as it has a specific
>meaning in the context of SQLite (or other SQL implementations).
>
>Bash does make substitutions in the 'pre-scripted' (for want of a
>better term) block delimited by '<the modified text to sqlite3 as input.  I understand that other
>shells can do likewise, but bash is the one I'm familiar with.
>
>So, yes and no ... 8-)
>
>Good luck!
> Niall O'Reilly
>
> ___
> 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] Fossil documentation edit

2017-02-15 Thread don v nielsen
On page http://fossil-scm.org/index.html/doc/trunk/www/quickstart.wiki 
it reads:


" To merge two branches back together, firstupdate 
to the branch you want to 
merge into.Then do amerge 
another branch that you 
want to incorporate the changes from."


Is it more appropriate for the second sentence to read: "Then do amerge 
[of] another branch..." or 
"Then merge another branch..."?


dvn

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


Re: [sqlite] Strange problem with using random with a table

2016-11-11 Thread Don V Nielsen
Does the same thing happen if you select from Psalms, or Thessalonians,
instead of Proverbs?  Sorry. Little religion humor. Too early in the
morning and I haven't had my coffee, yet.

On Fri, Nov 11, 2016 at 4:34 AM, Cecil Westerhof 
wrote:

> 2016-11-11 11:22 GMT+01:00 Clemens Ladisch :
> > Cecil Westerhof wrote:
> >> When I execute the following query:
> >> SELECT *, randomiser * 1000
> >> FROM (SELECT abs(random()) / 1 AS randomiser);
> >>
> >> I get normal results:
> >> 61|61000
> >>
> >> But when I change it to:
> >> SELECT *, randomiser * 1000
> >> FROM (SELECT abs(random()) / 1 AS randomiser
> >>   FROM   proverbs)
> >> LIMIT 5;
> >>
> >> I get very strange results:
> >> 382|606000
> >> 172|148000
> >> 144|592000
> >> 181|136000
> >> 123|469000
> >>
> >> What could be happening here?
> >
> > In the first query, the subquery is computed with a temporary table
> > (actually, a coroutine) before the values of the outer query are
> > computed.
> >
> > The second query is flattened, so it ends up like this:
> >
> >   SELECT abs(random()) / 1,
> >  abs(random()) / 1 * 1000
> >   FROM proverbs
> >   LIMIT 5;
> >
> > "randomiser" is not a table column, it is just an _alias_ for the
> > expression, so this is an allowed transformation.
> >
> > To prevent this, violate one of the rules that the optimizer currently
> > uses to determine whether flattening is possible:
> > 
> > For example, put a LIMIT clause also into the subquery.
>
> I need all the records, so I use:
> SELECT *
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> ,  randomiser * 1000
> FROM (
> SELECT abs(random()) / 1 AS randomiser
> FROM   proverbs
> LIMIT  (SELECT COUNT(*) FROM proverbs)
> )
> LIMIT 5;
>
> Not very clean, but it works.
>
> Maybe a good idea to have an option to disable flattening?
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] .mode column .width .separator

2016-10-14 Thread Don V Nielsen
Not that I don't enjoy this conversation, but this should be end of this
thread. Thanks for telling me to look at shell.c. I did. Interesting read.
I can see that simply changing "  " to "" in the printf call, recompiling,
and tada, I have what I want.  Thanks David.

- HOWEVER -

New logic is required to make an appropriate update to shell.c such that
the current user experience is not changed. One cannot simply replace "  "
with p->colSeparator. That is because in column mode, the default .mode
column separator is hardcoded as "  ". However, the default colSeparator
value is a pipe '|' (which affects other modes.) The following
considerations have to be resolved:

a) .separator allows one to override colSeparator
b) specifying .separator and .column are not dependent on sequence in the
current implementation

So to maintain the current behavior:

c) some indicator must be in place to identify when colSeparator was changed
d) when mode is changed to .column, colSeparator would be internally set to
"  " only when colSeparator was not already changed by .separator
e) .separator would have to set the indicator when it was implemented

Not following c) thru e) would change the behaviors in a way that would
require the user to know:
f) changing modes will change internal default values, and to know what
those values are
g) .separator must be specified after .mode column to change the
colSeparator value from internal default

The above f) & g) are not currently required of the user. It's messy when
one thinks about it.

dvn

On Fri, Oct 14, 2016 at 2:27 PM, Don V Nielsen <donvniel...@gmail.com>
wrote:

> Thanks, David. I can read C, but I don't know how to compile it. :(  Just
> not a language I have learned.
>
> On Fri, Oct 14, 2016 at 10:22 AM, David Raymond <david.raym...@tomtom.com>
> wrote:
>
>> If you're using the CLI exclusively you can go into shell.c and get rid
>> of the double spaces. In the copy I'm looking at that's bundled with the
>> 3.14.2 amalgamation it looks like there're 5 lines to alter and you'll be
>> good.
>>
>> They're in
>> static int shell_callback(
>> ...
>> case MODE_Column: {
>> ...
>> Lines 1007, 1010, 1026 look like they deal with displaying the header line
>> Lines 1050 and 1054 deal with each row.
>>
>> All of those lines are the end of a utf8_printf( call and have...
>>
>> i==nArg-1 ? rowSep : "  ");
>>
>> I tried turning the 2 spaces there into "" recompiled it, and it seems to
>> display the way you're wanting it to.
>>
>> Of course since I don't know C, by doing that I have no clue what else I
>> might just have broken which relied on the old format, but hey :)
>>
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Don V Nielsen
>> Sent: Friday, October 14, 2016 7:39 AM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] .mode column .width .separator
>>
>> Thank you, Gunter. I will investigate virtual tables; it's a bit over my
>> head at the moment.
>>
>> My final implementation was to route the select via .output, employ a CTE
>> to prepare the data, use printf to setup the record format, and then
>> select
>> the data from the CTE. Again, I do not enjoy the redundancy of the two
>> selects, but it gets the job done. A script will handle the statement in
>> the future.
>>
>>
>> with pre_process as (
>>   select
>>   ... columns blah blah ...
>> case when piecerate in ('AF','RF') and version_id = '81' then '81'
>> else
>> segment end as segment,
>>   ... columns blah blah ...
>>   from address_txt
>> )
>> select
>> printf(
>>
>> '%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10
>> s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%
>> -50s%-25s%-2s%-8s%-3s%-6s%-9s',
>>   ... columns, blah blah ...
>> )
>> from pre_process
>> ;
>>
>>
>> Works like a champ.
>> Have a good one.
>>
>> On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:
>>
>> > You can eliminate separators by using
>> > .mode list
>> > .sepa ""
>> >
>> > But then you need to format your values to the correct widths for the
>> > record description. Text processing is not the primary domain of SQLite
>> and
>> > is best left to the presentation layer.
>> >
>> > Alternatively you may consider writing a virtual table module to do the
>> > processing for you. Semantics could be s

Re: [sqlite] .mode column .width .separator

2016-10-14 Thread Don V Nielsen
Thank you, Gunter. I will investigate virtual tables; it's a bit over my
head at the moment.

My final implementation was to route the select via .output, employ a CTE
to prepare the data, use printf to setup the record format, and then select
the data from the CTE. Again, I do not enjoy the redundancy of the two
selects, but it gets the job done. A script will handle the statement in
the future.


with pre_process as (
  select
  ... columns blah blah ...
case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  ... columns blah blah ...
  from address_txt
)
select
printf(

'%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%-50s%-25s%-2s%-8s%-3s%-6s%-9s',
  ... columns, blah blah ...
)
from pre_process
;


Works like a champ.
Have a good one.

On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:

> You can eliminate separators by using
> .mode list
> .sepa ""
>
> But then you need to format your values to the correct widths for the
> record description. Text processing is not the primary domain of SQLite and
> is best left to the presentation layer.
>
> Alternatively you may consider writing a virtual table module to do the
> processing for you. Semantics could be similar to:
>
> CREATE VIRTUAL TABLE mainframe_file USING mainframe ( '',
> '', []); --> open filename, prepare to write in
> specified format, write header
> INSERT INTO mainframe_file SELECT ...; -> stuff in data, write record
> DROP TABLE mainframe_file; -> close the file, write trailer record if
> required
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Don V Nielsen
> Gesendet: Donnerstag, 13. Oktober 2016 20:51
> An: General Discussion of SQLite Database <sqlite-users@mailinglists.
> sqlite.org>
> Betreff: [sqlite] .mode column .width .separator
>
> I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
> with mainframe data in a fixed format.
>
> I would like to use .mode column to create my output text file in a fixed
> layout. I set all my column widths using .width. I then output my data to a
> file. Unfortunately, there are two blanks separating each column, space I
> don't want to be there. The .separator command does not provide any
> mechanism for turning it off. Is there a way?
>
> I realize there is a printf function available. However, it appears that
> output values must come from a table column, where as below, I could use
> case statements in the sql select of the data.
>
> Any suggestions? I think I am overlooking a .separator option that says
> "don't put spaces between output columns". I assume that John McKown has
> faced this already, given his mainframe pedigree.
>
> Thanks for your time,
> dvn
>
>
>
> Sample output:
> "H  0NZOX0001687395  83501  5827  "...
>
> .mode column
> .width 1 15 5 4 2 1 4 4 1 5 1 10 10 10 1 20 20 15 1 10 20 20 50 50 50 50 50
> 25 2 8 003 006 009
> .output vo_pwprep.txt
> select
>   recid,
>   z_num,
>   zip,
>   zip4,
>   dpbc,
>   ckdig,
>   cart,
>   lot,
>   lot_order,
>   walk_seq,
>   walk_seq_bic,
>   case when piecerate in ('AF','RF') and version_id = '81' then '81' else
> segment end as segment,
>   version_id,
>   message,
>   seed,
>   seed_id,
>   seed_key,
>   planet,
>   ocr_acr,
>   priority,
>   keycode,
>   custno,
>   name,
>   title,
>   firm_id,
>   addr1,
>   addr2,
>   city,
>   state,
>   seq_number,
>   srvc_type,
>   imb_mid,
>   imb_serial
> from address_txt
> ;
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Import 5000 xml files in a sqlite database file

2016-10-25 Thread Don V Nielsen
Ruby scripting language, using Nokogiri library to parse the html.

On Sun, Oct 23, 2016 at 11:12 AM, Preguntón Cojonero Cabrón <
preguntoncojon...@gmail.com> wrote:

> Scripting powershell? C#?
>
> El 23/10/2016 18:04, "Sylvain Pointeau" 
> escribió:
>
> > hello,
> >
> > I am not sure if Oxygen or another XML specialized software could do it,
> > however it would be easy done using C or C++ or Java. Advantage is that
> it
> > is then easy to automatize in a batch mode. I can propose you my services
> > if you are interested.
> >
> > Best regards,
> > Sylvain
> >
> > Le samedi 22 octobre 2016,  a écrit :
> >
> > > Hi,
> > >
> > > I have more than 5000 xml files. All files have the same xml-structure.
> > >
> > > Each file has different values (timestamps, numbers and strings). I
> would
> > > like to put all these values in a sqlite database tabke, all in one
> > table.
> > > => Import the data values into a sqlite database table.
> > >
> > > Can you please tell me a software program, that can do this quickly?
> > >
> > > Thank you for your answers.
> > >
> > > Best regards
> > >
> > > Bob
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org 
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Development environment reccomendation

2016-10-25 Thread Don V Nielsen
Ruby to script out and automate the process. Could use Sinatra as a
lightweight web interface, or go Rails for a full blown web service.

On Mon, Oct 24, 2016 at 5:02 AM, Simon Slavin  wrote:

>
> On 24 Oct 2016, at 6:26am, Philip Rhoades  wrote:
>
> > What development environment would people suggest for building the
> sqlite app?
>
> If I understand correctly, 'mmssms.db' is itself a SQLite database file.
> So your choice comes down to whatever programming language/environment
> you're familiar with, as long as it supports the SQLite API or has a
> library which does.
>
> Ruby is fine, since you mention that:
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Table name syntax

2016-11-21 Thread Don V Nielsen
> And since the "*" forms are considered bad style

I have done this, not knowing it is bad style. Can you provide some reasons
why it is bad? I can assume, "Applications are supposed to be controlled
environments, and using tbl.* introduces uncertainty outside the
applications control." But are there more specific reasons?

Thanks,dvn


On Mon, Nov 21, 2016 at 3:09 PM, Richard Hipp  wrote:

> On 11/21/16, David Raymond  wrote:
> >
> > Following the nice SQL diagrams it looks like in a select you can only
> have
> > * or table-name.*, whereas in other places you can have
> > schema-name.table-name. Granted, the second version can be made prettier
> and
> > more readable, but I would have assumed the first version would be ok. Is
> > this par for all SQL versions?
>
> I don't know what other database engines do, but you are correct that
> SCHEMA.TABLE.* is not allowed in SQLite.  And since the "*" forms are
> considered bad style (to be used only interactively, and not in
> applications) we are not motivated to change it, lest developers be
> tempted to use "*" in their applications.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Autoincrement sequence not updated by UPDATE

2016-10-31 Thread Don V Nielsen
> It guess it comes down to what one wants from "INTEGER PRIMARY KEY
AUTOINCREMENT"

What I would want, ...expect, is that a primary key autoincrement column
would be left completely alone. And if was altered, it was altered on
accident. I always thought "integer primary key" was synonymous with
__rowid__.  What application would want to mess with that?



On Mon, Oct 31, 2016 at 12:56 PM, Richard Hipp  wrote:

> On 10/27/16, Adam Goldman  wrote:
> > I expected the test case below to print 5679, but it prints 1235
> > instead. I tested under a few versions including 3.15.0. It's a bit of a
> > corner case and I worked around it in my application, but I guess it's a
> > bug.
> >
> > CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
> > INSERT INTO foo (bar) VALUES(1234);
> > UPDATE foo SET bar=5678;
> > DELETE FROM foo;
> > INSERT INTO foo DEFAULT VALUES;
> > SELECT * FROM foo;
>
> Yes, this is a discrepancy between the implementation and the
> documentation.  But the implementation of AUTOINCREMENT has *never*
> before modified the content of the sqlite_sequence table on an UPDATE,
> since the AUTOINCREMENT feature was first introduced in 2002 (SQLite
> version 2.5.2). For 14 years, AUTOINCREMENT has always worked as it
> does in 3.15.0.  If we "fix" the implementation now, we run a risk of
> breaking some of the millions of applications that use AUTOINCREMENT.
> For that reason, we have tentatively decided to update the
> documentation rather than the code.
>
> Note that the implied purpose of AUTOINCREMENT is to generate a unique
> and immutable identifier.  Running an UPDATE on an immutable
> identifier breaks the contract.  Even though this contract was not
> previously stated in the documentation, apparently most people
> understood it because this issue has never come up before in 14 years
> of heavy use.
>
> Thanks for pointing out the problem.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] .mode column .width .separator

2016-10-13 Thread Don V Nielsen
These are simply blanks, 0x20, use to create separation of the output
columns. I'm assuming this is an inherent behavior for readability. If the
output was not being directed to the output file, it would be directed to
the display.

I'm trying to avoid pre processing (creating a table or view of the
preprocessed data) and post processing (having to pass 10g of text data to
removed blanks -- which is dangerous on its own.)

dvn

On Thu, Oct 13, 2016 at 1:56 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 13 Oct 2016, at 7:51pm, Don V Nielsen <donvniel...@gmail.com> wrote:
>
> > Unfortunately, there are two blanks separating each column
>
> Can you tell what characters these are ?  Perhaps use a hexdump facility.
>
> My guess at this point is that you should continue with the file you have
> already developed and then post-process it to remove the blank characters.
> If those characters are used only in the blanks you don't want, it should
> be possible to use a simple find/replace utility to do it.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] .mode column .width .separator

2016-10-13 Thread Don V Nielsen
I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
with mainframe data in a fixed format.

I would like to use .mode column to create my output text file in a fixed
layout. I set all my column widths using .width. I then output my data to a
file. Unfortunately, there are two blanks separating each column, space I
don't want to be there. The .separator command does not provide any
mechanism for turning it off. Is there a way?

I realize there is a printf function available. However, it appears that
output values must come from a table column, where as below, I could use
case statements in the sql select of the data.

Any suggestions? I think I am overlooking a .separator option that says
"don't put spaces between output columns". I assume that John McKown has
faced this already, given his mainframe pedigree.

Thanks for your time,
dvn



Sample output:
"H  0NZOX0001687395  83501  5827  "...

.mode column
.width 1 15 5 4 2 1 4 4 1 5 1 10 10 10 1 20 20 15 1 10 20 20 50 50 50 50 50
25 2 8 003 006 009
.output vo_pwprep.txt
select
  recid,
  z_num,
  zip,
  zip4,
  dpbc,
  ckdig,
  cart,
  lot,
  lot_order,
  walk_seq,
  walk_seq_bic,
  case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  version_id,
  message,
  seed,
  seed_id,
  seed_key,
  planet,
  ocr_acr,
  priority,
  keycode,
  custno,
  name,
  title,
  firm_id,
  addr1,
  addr2,
  city,
  state,
  seq_number,
  srvc_type,
  imb_mid,
  imb_serial
from address_txt
;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] .mode column .width .separator

2016-10-13 Thread Don V Nielsen
I can do this, which works. But the redundancy bothers me, and is prone to
finger-check errors.

with pre_process as (
  select
recid,
z_num,
zip,
zip4,
dpbc,
case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  ... blah blah ...
  from address_txt
)
select
printf(
'%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%-50s%-25s%-2s%-8s%-003s%-006s%-009s',
recid,
z_num,
zip,
zip4,
dpbc,
segment
  ... blah blah ...
)
from pre_process
limit 10
;


On Thu, Oct 13, 2016 at 2:02 PM, Don V Nielsen <donvniel...@gmail.com>
wrote:

> These are simply blanks, 0x20, use to create separation of the output
> columns. I'm assuming this is an inherent behavior for readability. If the
> output was not being directed to the output file, it would be directed to
> the display.
>
> I'm trying to avoid pre processing (creating a table or view of the
> preprocessed data) and post processing (having to pass 10g of text data to
> removed blanks -- which is dangerous on its own.)
>
> dvn
>
> On Thu, Oct 13, 2016 at 1:56 PM, Simon Slavin <slav...@bigfraud.org>
> wrote:
>
>>
>> On 13 Oct 2016, at 7:51pm, Don V Nielsen <donvniel...@gmail.com> wrote:
>>
>> > Unfortunately, there are two blanks separating each column
>>
>> Can you tell what characters these are ?  Perhaps use a hexdump facility.
>>
>> My guess at this point is that you should continue with the file you have
>> already developed and then post-process it to remove the blank characters.
>> If those characters are used only in the blanks you don't want, it should
>> be possible to use a simple find/replace utility to do it.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] .mode column .width .separator

2016-10-13 Thread Don V Nielsen
Thanks, but it appears that ".mode column" triggers it, and .separator does
not appear to have any influence in that mode.

Out of curiosity, it appears that the row separator (in windows) is a
single character. Do you know to specify as the row separator?  Everything
I attempt is taken as literal characters.  I've attempted: 0x0D0x0A,
0x0D0A, 0Dx0Ax. Nothing appears to work.

dvn

On Thu, Oct 13, 2016 at 2:11 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 13 Oct 2016, at 7:51pm, Don V Nielsen <donvniel...@gmail.com> wrote:
>
> > The .separator command does not provide any
> > mechanism for turning it off. Is there a way?
>
> Can't try it now but does
>
> .separator ""
>
> do what you want ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] .mode column .width .separator

2016-10-13 Thread Don V Nielsen
Thanks John. I had a feeling you would have encountered this sort of stuff.

And thanks for your time, Simon.

All is appreciated.

And thanks in advance, Dr. Hipp, if you act on this...allowing a \0 row
separator in column mode.  It would simplify the life of us mainframers.

dvn

On Thu, Oct 13, 2016 at 3:31 PM, John McKown <john.archie.mck...@gmail.com>
wrote:

> On Thu, Oct 13, 2016 at 2:42 PM, Don V Nielsen <donvniel...@gmail.com>
> wrote:
>
> > Thanks, but it appears that ".mode column" triggers it, and .separator
> does
> > not appear to have any influence in that mode.
> >
> > Out of curiosity, it appears that the row separator (in windows) is a
> > single character. Do you know to specify as the row separator?
> Everything
> > I attempt is taken as literal characters.  I've attempted: 0x0D0x0A,
> > 0x0D0A, 0Dx0Ax. Nothing appears to work.
> >
>
> ​The row separator is specified using the 2nd parameter of the .separator
> command. Example transcript:
>
>
> SQLite version 3.11.0 2016-02-15 17:29:24
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .mode column
> sqlite> .separator | -
> sqlite> create table a(one text,two text);
> sqlite> .width 10 10
> sqlite> insert into a values('1a','2a');
> sqlite> insert into a values('1b','2b');
> sqlite> select * from a;
> 1a  2a-1b  2b-sqlite>
>
> Note that the specified column separator is ignored in .mode column, but
> the row separator is not. Also, for fun, note what happens with negative
> widths
>
> sqlite> .width -10 -10
> sqlite> select * from a;
> 1a  2a-1b  2b-sqlite>
>
> Also, I have looked at the current sqlite3.c source code. In .mode column,
> the space separator character is "hard coded" and so cannot be set to any
> other character.​
>
> Lastly, you can specify a "control" character by using a C language escape.
> E.g. (continuing from above examples)
>
> sqlite> .separator - \n
> sqlite> select * from a;
> 1a  2a
> 1b  2b
>
> To address your desire, it would be necessary for the column separator
> character to be honored in .mode column mode and the separator be made a
> 0x00, or \0. If Dr. Hipp were to do this, this would eliminate the column
> separator entirely because \0 would result in "no" character between the
> columns. This appears, to me, to be a rather simple change in shell.c.
>
>
> > dvn
> >
> >
>
> --
> Heisenberg may have been here.
>
> Unicode: http://xkcd.com/1726/
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] .mode column .width .separator

2016-10-14 Thread Don V Nielsen
Thanks, David. I can read C, but I don't know how to compile it. :(  Just
not a language I have learned.

On Fri, Oct 14, 2016 at 10:22 AM, David Raymond <david.raym...@tomtom.com>
wrote:

> If you're using the CLI exclusively you can go into shell.c and get rid of
> the double spaces. In the copy I'm looking at that's bundled with the
> 3.14.2 amalgamation it looks like there're 5 lines to alter and you'll be
> good.
>
> They're in
> static int shell_callback(
> ...
> case MODE_Column: {
> ...
> Lines 1007, 1010, 1026 look like they deal with displaying the header line
> Lines 1050 and 1054 deal with each row.
>
> All of those lines are the end of a utf8_printf( call and have...
>
> i==nArg-1 ? rowSep : "  ");
>
> I tried turning the 2 spaces there into "" recompiled it, and it seems to
> display the way you're wanting it to.
>
> Of course since I don't know C, by doing that I have no clue what else I
> might just have broken which relied on the old format, but hey :)
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Don V Nielsen
> Sent: Friday, October 14, 2016 7:39 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] .mode column .width .separator
>
> Thank you, Gunter. I will investigate virtual tables; it's a bit over my
> head at the moment.
>
> My final implementation was to route the select via .output, employ a CTE
> to prepare the data, use printf to setup the record format, and then select
> the data from the CTE. Again, I do not enjoy the redundancy of the two
> selects, but it gets the job done. A script will handle the statement in
> the future.
>
>
> with pre_process as (
>   select
>   ... columns blah blah ...
> case when piecerate in ('AF','RF') and version_id = '81' then '81' else
> segment end as segment,
>   ... columns blah blah ...
>   from address_txt
> )
> select
> printf(
>
> '%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-
> 10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-
> 50s%-50s%-25s%-2s%-8s%-3s%-6s%-9s',
>   ... columns, blah blah ...
> )
> from pre_process
> ;
>
>
> Works like a champ.
> Have a good one.
>
> On Fri, Oct 14, 2016 at 2:58 AM, Hick Gunter <h...@scigames.at> wrote:
>
> > You can eliminate separators by using
> > .mode list
> > .sepa ""
> >
> > But then you need to format your values to the correct widths for the
> > record description. Text processing is not the primary domain of SQLite
> and
> > is best left to the presentation layer.
> >
> > Alternatively you may consider writing a virtual table module to do the
> > processing for you. Semantics could be similar to:
> >
> > CREATE VIRTUAL TABLE mainframe_file USING mainframe ( '',
> > '', []); --> open filename, prepare to write in
> > specified format, write header
> > INSERT INTO mainframe_file SELECT ...; -> stuff in data, write record
> > DROP TABLE mainframe_file; -> close the file, write trailer record if
> > required
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > Im Auftrag von Don V Nielsen
> > Gesendet: Donnerstag, 13. Oktober 2016 20:51
> > An: General Discussion of SQLite Database <sqlite-users@mailinglists.
> > sqlite.org>
> > Betreff: [sqlite] .mode column .width .separator
> >
> > I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
> > with mainframe data in a fixed format.
> >
> > I would like to use .mode column to create my output text file in a fixed
> > layout. I set all my column widths using .width. I then output my data
> to a
> > file. Unfortunately, there are two blanks separating each column, space I
> > don't want to be there. The .separator command does not provide any
> > mechanism for turning it off. Is there a way?
> >
> > I realize there is a printf function available. However, it appears that
> > output values must come from a table column, where as below, I could use
> > case statements in the sql select of the data.
> >
> > Any suggestions? I think I am overlooking a .separator option that says
> > "don't put spaces between output columns". I assume that John McKown has
> > faced this already, given his mainframe pedigree.
> >
> > Thanks for your time,
> > dvn
> >
> >
> >
> > Sample output:
> > "H  0NZOX0001687395  83501  5827  "...
> >
> > .mode column
> > .width 1 15 5 4 2 1 4 4 1 5 1 10 1

Re: [sqlite] using sqlite extensions within Ruby

2016-12-08 Thread Don V Nielsen
Like you, I like ruby and working with sqlite via sqlite3 gem. So you have
recompiled sqlite3 with the sessions extension. Correct? And this modified
sqlite3 is the nearest sqlite3 available in your execution path. Correct?
The sqlite_ruby library is an interface to sqlite3...the base model.
Granted, I have no knowledge of the sessions extension, nor any extension
for that matter. But I do not know how sqlite_ruby would have any language
to the extended functionality; it is simply unaware of the bindings. If the
functionality is available via function calls in the sql itself, have you
tried executing the sql directly, i.e.:

  conn =
ActiveRecord::Base.establish_connection(adapter:'sqlite3',database:DB_NAME)
  conn.connection.execute(pragma_this_or_that)
  conn.connection.execute(your_sql_with_functions)

Without you augmenting the sqlite3 gem and teaching it how to interact with
the new functions, I don't know how your would accomplish what you are
suggesting.

dvn

On Wed, Dec 7, 2016 at 12:07 PM, Will Parsons 
wrote:

> I don't usually see questions about using SQLite3 from Ruby here, so I
> hope this is not out of place.
>
> Ruby is my normal programming language of choice nowadays, and I've
> been very happy with the sqlite3 ruby gem to use SQLite3 within my
> programs.  I'd now like to make use of the SQLite3 session extension
> under Ruby using the sqlite3 gem, but can't figure out how to do that.
>
> I'm currently using FreeBSD and have compiled sqlite3 with the
> SESSION extension.
>
> I previously asked this question on stackoverflow:
>
> http://stackoverflow.com/questions/40875967/how-to-use-
> sqlite3-session-extension-with-ruby
>
> and got the reply that no such support was available.  I'm hoping that
> someone here may have helpful advice.
>
> --
> Will
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] using sqlite extensions within Ruby

2016-12-08 Thread Don V Nielsen
Documentation update to https://sqlite.org/sessionintro.html:

"The session extension provide a mechanism". Should that read, "The session
extension provides a mechanism"?

dvn

On Thu, Dec 8, 2016 at 9:23 AM, Don V Nielsen <donvniel...@gmail.com> wrote:

> Like you, I like ruby and working with sqlite via sqlite3 gem. So you have
> recompiled sqlite3 with the sessions extension. Correct? And this modified
> sqlite3 is the nearest sqlite3 available in your execution path. Correct?
> The sqlite_ruby library is an interface to sqlite3...the base model.
> Granted, I have no knowledge of the sessions extension, nor any extension
> for that matter. But I do not know how sqlite_ruby would have any language
> to the extended functionality; it is simply unaware of the bindings. If the
> functionality is available via function calls in the sql itself, have you
> tried executing the sql directly, i.e.:
>
>   conn = ActiveRecord::Base.establish_connection(adapter:'sqlite3',
> database:DB_NAME)
>   conn.connection.execute(pragma_this_or_that)
>   conn.connection.execute(your_sql_with_functions)
>
> Without you augmenting the sqlite3 gem and teaching it how to interact
> with the new functions, I don't know how your would accomplish what you are
> suggesting.
>
> dvn
>
> On Wed, Dec 7, 2016 at 12:07 PM, Will Parsons <varro@nodomain.invalid>
> wrote:
>
>> I don't usually see questions about using SQLite3 from Ruby here, so I
>> hope this is not out of place.
>>
>> Ruby is my normal programming language of choice nowadays, and I've
>> been very happy with the sqlite3 ruby gem to use SQLite3 within my
>> programs.  I'd now like to make use of the SQLite3 session extension
>> under Ruby using the sqlite3 gem, but can't figure out how to do that.
>>
>> I'm currently using FreeBSD and have compiled sqlite3 with the
>> SESSION extension.
>>
>> I previously asked this question on stackoverflow:
>>
>> http://stackoverflow.com/questions/40875967/how-to-use-sqlit
>> e3-session-extension-with-ruby
>>
>> and got the reply that no such support was available.  I'm hoping that
>> someone here may have helpful advice.
>>
>> --
>> Will
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Speaking of Ruby & Sqlite...a weekend oddity for someone

2016-12-09 Thread Don V Nielsen
I have an odd problem. It involves Linux & Windows, and it involves
the ruby gem sqlite3. I've already contacted that author/maintainer of
the gem, but I have not heard back in several weeks.

I have a sql file that contains a ctas statement that employs cte with
statements. On Windows, the sql executes fine using the sqlite3
command line tool "sqlite3 aan.db -init cte_with.sql". And it runs
fine calling sqlite3 via the sqlite3 gem:

db = SQLite3::Database.new File.join(FOLDER,'aan.db')
db.execute 'drop table if exists fullfilled;'
sql = File.readlines( File.join(FOLDER,'cte_with.sql') ).collect {|ln| ln.chomp}
db.execute sql.join(' ')


Now with linux, the exact same sql code and ruby files are used. The
sql exec completes successfully using the command line tool. However,
it fails using the sqlite3 gem. The specific exception is "in
'initialize': near "with": syntax error (Sqlite3::SQLException)". It
is failing in the preparation of the statement. From all appearances,
sqlite3 is throwing the exception.

The gem compiles sqlite, creating sqlite_native.so, by downloading the
latest source file, as
"http://sqlite.org#{URL_PATH}/sqlite-autoconf-#{URL_VERSION}.tar.gz;
and compiling that. I have been comparing the gem code between the o/s
environments, and the C code (to this point) is identical.

I have dug into this deeper than my brain can figure out. Is it
possible that there is some kind of #ifdef or #ifndef being triggered,
based on o/s environment, that could effect the parsing or execution
of a cte?

I have a package that contains the sql, the ruby code, and a test db
(1.4k zipped) if anyone would like to give this a shot in their linux
environment and see if you get the same result. Just email me back and
I will send it to you.

* If I change the sql to create temporary tables instead of using the
cte with statements, then everything runs fine regardless of the o/s
environment.

I'm scratching my head on this, one. As always, thanks for your time
and consideration.
dvn

final note: if you take this one, a successful run will create an
empty table. it is good that the table is created. the problem is the
exception, not an empty table.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Speaking of Ruby & Sqlite...a weekend oddity for someone

2016-12-10 Thread Don V Nielsen
You are correct, sir. I discovered in the rake tasks the following
little bit of code. It directs which version of sqlite to download and
compile.

# BINARY_VERSION = "3.8.11.1"
# URL_VERSION= "3081101"
# URL_PATH   = "/2015"

Now I just need to figure out how to reinitiate the rake compile task
after making the following update, then test.

BINARY_VERSION = "3.15.2.0"
URL_VERSION= "3150200"
URL_PATH   = "/2016"

On Fri, Dec 9, 2016 at 5:27 PM, Roger Binns <rog...@rogerbinns.com> wrote:
> On 09/12/16 14:09, Don V Nielsen wrote:
>> However,
>> it fails using the sqlite3 gem. The specific exception is "in
>> 'initialize': near "with": syntax error
>
> That will be a SQLite version issue - older SQLite's didn't support
> with.  While you made some effort around versions, whatever is happening
> there is an older library is actually used.
>
> You can use this to find out exactly what library version is used:
>
>   SELECT sqlite_version(), sqlite_source_id();
>
> Also this will give a list of compilation options, to verify:
>
>   PRAGMA compile_options;
>
> Roger
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Problem with CASE in WHERE clause

2016-12-04 Thread Don V Nielsen
Sorry, but the whole scenario is messy, at best. The column is declared
bool, and then a string '1' is assigned to it. The case lacks an else, so
it resulting in one of two types: a string when true and an integer when
false. Correct? And then on top of that, as Simon pointed out, the column
affinity is bool, so a string is being interpreted as a bool (technically
integer) and so the first one is resulting in true when it appears that the
second one should do so. Please agree that there is way more happening that
what should be.

My recommendation is this. Make [posted] a varchar(1) with only two valid
values: 'y' and 'n'. Then rewrite your logic to work with 'y' and 'n' and
see if that works across every database. It is much more explicit, cleaner,
and does not rely on any underlying interpretations.

Just my opinion. Merry Christmas.
dvn

On Sun, Dec 4, 2016 at 2:46 AM, Frank Millman  wrote:

>
> From: Simon Slavin
> Sent: Sunday, December 04, 2016 10:26 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Problem with CASE in WHERE clause
>
>
> > On 4 Dec 2016, at 6:55am, Frank Millman  wrote:
> >
> > > If a column contains a ‘1’, I would expect sqlite3 to return true when
> testing for ‘1’, but in my example it returns false.
> >
> > I think I’ve found the problem ...
> >
>
> Thank you very much for your explanation, Simon.
>
> My live situation is a bit more complex than my example, so I will have to
> experiment to find the ideal solution.
>
> But you have given me the information I need to move forward – much
> appreciated.
>
> Frank
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] like operator

2016-12-05 Thread Don V Nielsen
Igor, I'm not sure if you gain anything from"length(lower(name))". Just
"length(name)" would suffice.

On Mon, Dec 5, 2016 at 10:11 AM, Dominique Devienne 
wrote:

> On Mon, Dec 5, 2016 at 4:24 PM, Igor Tandetnik  wrote:
>
> > On 12/5/2016 10:19 AM, Igor Tandetnik wrote:
> >
> >> On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote:
> >>
> >>> select name from employee table where name like '%Araya%' or name like
> >>> '%Amul%' or name like '%Aj%';
> >>>
> >>> Table - Employee
> >>>
> >>> Id | Name | age |
> >>> 1  | Arayan Kuma | 29  |
> >>> 2  | Amul Kanth  | 30  |
> >>> 3  | Ajay Kumar | 45  |
> >>>
> >>> I dont like to use may or conditions for pattern matching using like
> >>> operator.
> >>> Is there any other way I can workaround without using or condition in
> >>> like operator in sqlite.
> >>>
> >>
> >> WHERE length(replace(replace(replace(name, 'Araya', ''), 'Amul', ''),
> >> 'Aj', '')) != length(name)
> >>
> >
> > Actually, this is not quite the same: it's case-sensitive, whereas LIKE
> is
> > case-insensitive by default. To be equivalent, make it
> >
> > WHERE length(replace(replace(replace(lower(name), 'araya', ''), 'amul',
> > ''), 'aj', '')) != length(lower(name))
>
>
> Or use the pragma [1].
> Thanks, didn't realize/know LIKE was case-insensitive (for ASCII chars
> only) by default, in SQLite.
> Also made me double-check whether Oracle is case-sensitive or not (it is)
> [2]
>
> BTW, Igor: wow :)
> Not that I'd use that ever, but still, very clever! --DD
>
> [1] https://www.sqlite.org/pragma.html#pragma_case_sensitive_like
> [2]
> http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-
> oracle
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] date-2.2c tests fail for sqlite-3.16.1 on Fedora / i686

2017-01-05 Thread Don V Nielsen
Graham, you may be on to something: "I don't know what the tests are
doing, but could it be connected with the fact that a leap-second was
added as we changed from 2016 to 2017". I noticed a whole bunch of
rspec tests (my projects completely unrelated to sqlite) failing with
microsecond differences. My issue is likely the result of the leap_sec
shift. Thanks!

On Thu, Jan 5, 2017 at 4:08 AM, Graham Holden  wrote:
> I don't know what the tests are doing, but could it be connected with the 
> fact that a leap-second was added as we changed from 2016 to 2017 and one of 
> expected/got is taking this into account and the other isn't?
> Graham
>  Original message From: Richard Hipp  Date: 
> 05/01/2017  08:12  (GMT+00:00) To: SQLite mailing list 
>  Subject: Re: [sqlite] date-2.2c tests 
> fail for sqlite-3.16.1 on Fedora / i686
> On 1/4/17, Jakub Dorňák  wrote:
>> Example output:
>>
>> ...
>> ! date-2.2c-1 expected: [06:28:00.001]
>> ! date-2.2c-1 got:  [06:28:00.000]
>> ! date-2.2c-4 expected: [06:28:00.004]
>> ! date-2.2c-4 got:  [06:28:00.003]
>> ! date-2.2c-7 expected: [06:28:00.007]
>> ! date-2.2c-7 got:  [06:28:00.006]
>> ! date-2.2c-8 expected: [06:28:00.008]
>> ! date-2.2c-8 got:  [06:28:00.007]
>> ...
>
> This is probably a function of the underlying floating-point hardware.
> What CPU is this running on?
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

2017-01-06 Thread Don V Nielsen
In the below view, what is "a"? A FROM is not defined.

--
-- Ideally I want to write the next query using the previous view
--
CREATE VIEW if not exists "despesas_master_list_view_tidy" AS
SELECT
a.*,
b.name as modalidade_licitacao,
c.name as credor,
d.*
LEFT JOIN licitacao_modalidades AS b ON a.modalidade_lic=b.id
LEFT JOIN credores AS c ON a.credor_id=c.id
LEFT JOIN empenhos_list_view AS d ON a.empenho_id=d.id;


On Fri, Jan 6, 2017 at 3:27 AM, Domingo Alvarez Duarte 
wrote:

> Hello Richard !
>
> The simple example I've sent is the minimal to show the problem, the real
> database schema where I found this problem has a lot of small tables joined
> and I was using the views to simplify (not duplicate) code, so on that case
> it'll result in bloat and repetition. see bellow:
>
> ===
>
> create table if not exists municipios(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists municipios_orgaos(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists municipios_poder(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists credores(
> id integer primary key,
> cnpj_cpf varchar collate nocase_slna,
> name varchar not null collate nocase_slna,
> unique(cnpj_cpf, name)
> );
>
> create table if not exists tipos_despesa(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists funcoes_governo(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists subfuncoes_governo(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists programas_governo(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists acoes_governo(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists fontes_recursos(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists aplicacoes_fixo(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists aplicacoes_variavel(
> id integer primary key,
> code integer,
> name varchar not null collate nocase_slna,
> unique(code, name)
> );
>
> create table if not exists licitacao_modalidades(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> create table if not exists elementos_despesa(
> id integer primary key,
> name varchar not null unique collate nocase_slna
> );
>
> --
> -- the table bellow has 6M records
> --
> CREATE TABLE if not exists empenhos(
> id integer primary key,
> ano_exercicio integer not null,
> nr_empenho varchar not null,
> valor decimal,
> municipio_id integer not null,
> orgao_id integer not null,
> funcao_governo_id integer,
> subfuncao_governo_id integer,
> cd_programa integer,
> cd_acao integer,
> fonte_recurso_id integer,
> cd_aplicacao_fixo integer,
> elemento_id integer,
> unique(municipio_id, nr_empenho)
> );
>
>
> --
> -- the table bellow has 6M records
> --
> CREATE TABLE if not exists despesas_master(
> id integer primary key,
> empenho_id integer not null,
> modalidade_lic integer not null,
> credor_id integer not null,
> historico_despesa varchar collate nocase_slna,
> unique(credor_id, empenho_id)
> );
>
> --
> -- the table bellow has 24M records
> --
> CREATE TABLE if not exists despesas_detalhe(
> id integer primary key,
> id_despesa_detalhe integer not null,
> despesa_id integer not null,
> mes_referencia integer not null,
> tp_despesa_id integer not null,
> dt_emissao_despesa date not null,
> vl_despesa decimal
> );
>
> CREATE VIEW if not exists "empenhos_list_view" AS
> SELECT
> a."id",
> a."ano_exercicio",
> a."nr_empenho",
> a."valor",
> d.name as municipio,
> e.name as orgao,
> f.name as funcao,
> g.name as subfuncao,
> h.name as programa,
> i.name as acao,
> k.name as fonte_recurso,
> l.name as aplicacao_fixo,
> n.name as elemento,
> 

Re: [sqlite] Commit ad601c7962 degrade performance in a client program

2017-01-05 Thread Don V Nielsen
I loved this remark posted in the bugzilla chain:   "(... Fossil?
where do people find these version control systems?)"

On Thu, Jan 5, 2017 at 2:09 AM, Richard Hipp  wrote:
> On 1/3/17, Jianxun Zhang  wrote:
>> I am working in Yocto project. We have a fake-root program “pseudo” that
>> uses sqlite3 at a step when building Yocto images. We found a 2% increase of
>> the whole build time, and my bisecting shows the ad601c7962 in sqlite3 is
>> the root cause.
>
> That change was backed out by
> https://www.sqlite.org/src/timeline?c=9675518b33e8d407 and so your
> code should return to its old speed, or be faster.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Using CSV.c

2017-01-10 Thread Don V Nielsen
I would recommend using a script or batch file to automate the process
using the command line tool. It would be easier to maintain and remove a
level or two of complexity.


On Tue, Jan 10, 2017 at 4:03 AM, tbuck...@frontier.com <
tbuck...@frontier.com> wrote:

> Hello,I am having a little trouble implementing the csv.c program for
> SQLite.  Are their any programming examples available?
> All I want to do is import a csv file to a database, sort the file and
> output the results to another csv file all in c code.  I can manually do it
> with the dot (.) tools, but I want to automate this by executing one exe
> file.Tom...
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Possible bug with union and join.

2016-12-27 Thread Don V Nielsen
Theory related question. I'm being argumentative, I know. But this
issue is in the same category as one discussed weeks ago.

SQLite is, in a sense, typeless. All data is stored as text (ignore
blob). Correct? It is when one casts a column to something other than
text that triggers SQLite to treat the text differently.

Disregarding auto-incremented key values, why have an integer key.
Even if the key value will only be numeric digits like 1, 255, 1024,
etc..., are they "truly" integers? If the value is not used in a
mathematical formula, why think of it as an integer? It is still just
text...a string of ascii digits... but still text. Is there something
behind the scenes of how text data comprised of numeric digits is
stored?

Like the previous issue I suggested keeping the keys between tables
the same data type. The issue resolves itself. The same would be true,
here. One table has text which could be '1,10'. But in the other
table, it is integer 1 & 10. It could be text '1' & '10'. No type
conversion problems.

I don't know. I would like to hear what others have to say.
dvn

On Sun, Dec 25, 2016 at 2:43 PM, Adrian Stachlewski
 wrote:
> Fortunately names of columns are much more transparent and documented in
> our internal specification. 'Id' was created only for example, but thanks
> for advice :)
>
> Adrian
>
> 2016-12-25 13:44 GMT+01:00 Simon Slavin :
>
>>
>> On 23 Dec 2016, at 4:55pm, Adrian Stachlewski 
>> wrote:
>>
>> > Id field in one table is defined as TEXT, because there are stored
>> > identifiers which can be numeric or text mostly like in the example ("4",
>> > "4,5", "10-1") (to be precise this map is created on the fly by
>> > concatenating some ids and names from another tables).  In second table
>> > there are stored identifiers which are integer only. This ids means
>> > something entirely different, but there is one case, when table with date
>> > keeps ids from both tables. Unfortunately I cannot change input data - it
>> > is taken from some APIs using csv files.
>>
>> Okay.  You’re wedded to a data format created by someone else.  That
>> explains the problem.
>>
>> If you have the opportunity to rename your columns when you import from
>> the CSV files, I might recommend that you do not call the TEXT field 'id'.
>> The convention for 'id' is for an INTEGER PRIMARY KEY and it might confuse
>> other people who see your database.
>>
>> Good luck with problem you posted about.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Possible bug with union and join.

2016-12-27 Thread Don V Nielsen
Thanks for the correction/information! Merry Christmas and Happy New
Year to you.

On Tue, Dec 27, 2016 at 9:48 AM, Richard Hipp <d...@sqlite.org> wrote:
> On 12/27/16, Don V Nielsen <donvniel...@gmail.com> wrote:
>> Theory related question. I'm being argumentative, I know. But this
>> issue is in the same category as one discussed weeks ago.
>>
>> SQLite is, in a sense, typeless. All data is stored as text (ignore
>> blob). Correct? It is when one casts a column to something other than
>> text that triggers SQLite to treat the text differently.
>
> Incorrect.  SQLite stores content in memory and on disk in multiple
> formats, including 2's-complement integers, IEEE 754 floating point
> numbers, text formatted as UTF8, UTF16be, or UTF16le, and binary
> blobs.  See, for example,
> https://www.sqlite.org/fileformat2.html#serialtype
>
>>
>> Disregarding auto-incremented key values, why have an integer key.
>
> Special optimizations apply to tables with an INTEGER PRIMARY KEY that
> make such tables particularly fast.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] UNION

2017-03-03 Thread don v nielsen

Might I suggest: https://www.w3schools.com/sql/

dvn

On 03/01/2017 09:02 AM, jose isaias cabrera wrote:

Ryan,

I just want to thank you for your kindness and display of goodwill to
mankind.  This is a great response.  I even learned something from this
post.  Thanks so much for your responses.  There are others like us in
this group that love to learn and your posts always are well received.
Thanks.  In Spanish we say, "muchas gracias."

josé

On 2017-03-01 09:51, R Smith wrote:


On 2017/03/01 3:40 AM, do...@mail.com wrote:


# SELECT * FROM processors UNION SELECT * FROM storage;
Error: SELECTs to the left and right do not have the same number of
result columns.

All tables that I created in my database have differing column names,
values, and amounts of columns with the noted exception of the one
column which is common (board). I've no idea what to do now.

Why is this an error?

I think that perhaps you are new to SQL and other replies assumed you simply 
wanted what you wrote. I could be wrong, but just in case, here are my 
suggestions:

Perhaps what you would rather like to do is JOIN these tables and not UNION 
them?

Do you wish to match /every/ processor with /every/ board?
In this case, the statement should read: (Warning: this could produced 
excessively long listings)
SELECT * FROM processors, storage;

Do you wish to match only processors and storage that fit on the same boards?
In this case the statement might read something like:
SELECT *
FROM processors
JOIN storage USING board
;

Do you wish to list /all/ processors and add the storage options for the same 
board /if/ there are any?
In this case the statement might read something like:
SELECT *
FROM processors
LEFT JOIN storage ON storage.board = processors.board
;

As you can see, lots of different things can be achieved. A quick course in SQL 
via perhaps W3Schools will teach all these in a few hours.

Cheers,
Ryan

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

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


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


Re: [sqlite] sqlite3 feature or regression

2017-03-08 Thread don v nielsen
Vermes, I'm late to the party but would still like to comment. The 
problem is the ruby code, not sqlite. The following is what you coded in 
the Ruby:


db.execute("select szamla,megnevezes from proba") do |row|

In some shape or fashion, the result set is getting mangled by the 
update. When I debug it, the code is getting deeper than I have the 
ability to understand, but the execute is being reevaluated. That is 
probably getting more complicated due to the open (non-committed 
transaction). Again, this is deeper than my ability to understand.


What you need is a static collection and then iterate over the results:

(db.execute("select szamla,megnevezes from proba")).each {|row|

- or -

rows = db.execute("select szamla,megnevezes from proba")
rows.each {|row|

The above will execute the select, returning a collection of the ten 
rows, then iterate for each member of the collection. The collection is 
not influenced by the update.


dvn


On 03/07/2017 09:00 AM, Vermes Mátyás wrote:

On Mon, 6 Mar 2017 18:34:40 -0500
Richard Hipp  wrote:


For the benefit of those of us who do not do Ruby, perhaps you could
explain in words what you think it is that SQLite is doing
incorrectly?


I am not a Ruby programmer either nor a real SQLite user. I am interested in writing  SQL interfaces 
to http://github.com/mrev11/ccc3;>CCC to various databases.  Ruby 
was chosen only because it can be run everywhere.   Just run the script: A select of ten rows turns 
into an endless loop.

Consider my post as a bug report. I do not need any workaround, and do know how 
to use WAL or duplicate database connections.



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


Re: [sqlite] Poll: Include the recent sqlite3_column_name() fix in the upcoming 3.20.0 release?

2017-07-31 Thread Don V Nielsen
Merge it now.

As stated in documentation: "The SQLite documentation clearly states that,
if there is no AS clause, the names of output columns are indeterminate,
arbitrary, and subject to change." It would be my hope that legacy
applications exploiting the returned column name given said documentation
would be compiling a specific version of the code, or providing a specific
dll to accompany the app. The legacy apps should not have a problem, in
those scenarios.

Moving forward, wanting to implement the latest SQLite, the change has been
clearly documented. And so, testing will be required to upgrade.

IMO, dvn

On Mon, Jul 31, 2017 at 10:30 AM, Neville Dastur 
wrote:

> IMHO the inconsistency is a bug of sorts and so would “vote” for a merge
> now.
> In a sense applications that risk breaking are due to hacking around what
> is documented. And if the change is going to happen it might as well be now
> along with the major versions change.
>
> --
> L: http://uk.linkedin.com/in/nevilledastur  nevilledastur>
> T: @nevilledastur 
> Clinical Software Solutions: http://www.clinsoftsolutions.com <
> http://www.clinsoftsolutions.com/>
> hospify.com  - secure healthcare communication
>
>
>
> > On 31 Jul 2017, at 16:21, Richard Hipp  wrote:
> >
> > Ticket https://sqlite.org/src/info/de3403bf5ae5f72ed describes a
> > problem with column naming, and a proposed solution.  Today's
> > question:  Should the proposed solution be merged into the 3.20.0
> > release?
> >
> > Pros:  (1)  The change makes column names more consistent.  (2) The
> > change fixes some breakage caused by a query planner enhancement
> > introduced in 3.19.0.  (3) The change makes column naming in SQLite
> > work (more) like it does in PostgreSQL, MySQL, and SQLServer.  (4) The
> > change will likely be in 3.21.0 even it it isn't in 3.20.0.  Better to
> > go ahead and get over the pain of any breakage that results now,
> > rather than putting it off until later.
> >
> > Cons: (5) The change might cause breakage for legacy applications that
> > depend on the older (arguably buggy) behavior.  (6) This seems like a
> > big change to receive so little beta exposure prior to the official
> > release.  (7) Making the merge will (or should) delay the release by a
> > day or so.  The release was going to happen tomorrow (2017-08-01) but
> > if we do the merge, I think the release should be postponed until
> > 2017-08-02 or 2017-08-03.
> >
> > Let me know your thoughts.   Replies to the mailing list are
> > preferred, but private email directly to me is also accepted.
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Version 3.20.0 coming soon...

2017-07-14 Thread Don V Nielsen
> perhaps the devs can invent some other namespace

Seconded. I would like to think the devs, themselves, would like to create
some sort of namespace or isolation for the structures created by the
extensions. A little more gusto in the name, rather than STMT, would always
be appreciated. imo

On Fri, Jul 14, 2017 at 5:31 AM, Simon Slavin  wrote:

>
>
> On 14 Jul 2017, at 8:50am, Clemens Ladisch  wrote:
>
> > There already is an "sqlite_" namespace, and it is reserved for internal
> > objects of SQLite itself.
> >
> > The STMT virtual table is an extension that must be explicitly enabled.
> > (The sqlite3 command-line shell does so by default, but it already did
> > with other extensions like fileio, and it does not actually have the
> > same API stability guarantees as the SQLite library.)
>
> Then perhaps the devs can invent some other namespace for this new
> category of thing.  I don’t mind what the name is, I would just like the
> entity to be clearly marked as not one of mine.  The ability to filter out
> names including underline characters is very useful.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] A Descending Auto Increment Key

2017-07-20 Thread Don V Nielsen
On the heel of this is a grammatical error: "declared type "INTEGER"
includes an "PRIMARY KEY DESC" clause". It should read "includes a
"primary...".

On Wed, Jul 19, 2017 at 11:03 PM, Keith Medcalf  wrote:

>
> AUTOINCREMENT can only be used with INTEGER PRIMAY KEY columns in a rowid
> table definition to declare an alias for the rowid.  Such columns must not
> have the DESC ordering (the rowid is ascending).  If it does, then "PRIMARY
> KEY" is syntactic sugar for "UNIQUE" (ie, creating a unique index) and the
> AUTOINCREMENT keyword cannot be applied.
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of sanhua.zh
> Sent: Wednesday, 19 July, 2017 20:07
> To: sqlite-users
> Subject: [sqlite] A Descending Auto Increment Key
>
> When I call `CREATE TABLE test(i INTEGER PRIMARY KEY DESC AUTO
> INCREMENT)`, SQLite comes to an error "AUTOINCREMENT is only allowed on an
> INTEGER PRIMARY KEY".
>
>
> But as the document http://www.sqlite.org/lang_createtable.htmlsays, in
> the column-constraint, it shows that `PRIMARY DESC AUTOINCREMENT` is a
> valid syntax.
>
>
> Why the original SQL failed? Is that really not supported?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Tutorials, books, video about SQLite

2017-08-09 Thread Don V Nielsen
Safari Books Online. I believe there are a number of SQLite title there.

On Wed, Aug 9, 2017 at 2:06 PM, Lars Frederiksen  wrote:

> Hi
>
>
>
> I would appreciate very much  if you clever people out there have some
> booktitles or links to tutorials (websites, video etc) about SQLite.
>
>
>
> Regards
>
> Lars
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

2017-06-08 Thread Don V Nielsen
"CREATE TABLE meta_comments"

Simon, isn't your approach the most logical solution rather than
incorporating a comment column into the master? Once incorporated, wouldn't
you be opening yourself up to a litany of "Not the way we work", "We need
feature x to be useful", "What if we want a null comment?" and user stuff
like that? Your framework gives something to build off of, can be adapted
at will, and is not part of the core.

Personally, I would find it *helpful* to have a comment column, but it is
certainly not critical to the operation of the database. It's not a must
have feature for this db.

Just my two cents, or less

On Wed, Jun 7, 2017 at 12:57 PM, Simon Slavin  wrote:

>
>
> On 6 Jun 2017, at 2:17pm, PICCORO McKAY Lenz 
> wrote:
>
> > how its the status of this work?
>
> The work of parsing comments in the CREATE TABLE command ?  I don’t think
> anyone else thinks this is worth working on.  Discussion in this list has
> come up with many reasons why it’s a poor way to store comments, including
>
> * Difficulty of parsing text which may have CR, LT, tab, comma, etc..
> * Impossible to update the comments without DROPping and reCREATEing the
> table because SQLite implements only a few ALTER TABLE commands.
> * Documentation restricted to one language.
>
> Here’s a simple version of the best system I ever came up with from
> working in multi-programmer projects, where clear comments were important
> to letting one developer know what another intended.  Comments for a
> database can be stored in the following table in that database:
>
> CREATE TABLE meta_comments (
> entityType TEXT COLLATE NOCASE NOT NULL,
> theTable TEXT COLLATE NOCASE NOT NULL,
> theName TEXT COLLATE NOCASE NOT NULL,
> theComment TEXT COLLATE NOCASE NOT NULL,
> PRIMARY KEY (entityType, theTable, theName));
>
> Values for "entityType" can be ’schema’,'table','index','trigger','view',
> and anything else you want to document.
>
> If you need multilanguage documentation (required for some countries which
> work to protect a language) add a "language TEXT COLLATE NOCASE NOT NULL"
> field and include it in the primary key.  Ih one use of an early version of
> this we also used a field called "theVersion" to document changes in each
> entity, though I don’t know how sensible that is for most uses.  We also
> used to use a table like this to store commands, though if I was designing
> that system from scratch now I’d use a different table.
>
> I came up with the above structure myself, warrant that it is not
> encumbered by any intellectual property, and dedicate it to the public
> domain.  Anyone can use it for anything they want.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Please remove me from the mailing list

2017-09-13 Thread Don V Nielsen
SWEET MOTHER OF MOSES. I saw "Please remove me from your list" with Simon
Slavin's name next to it. I nearly had a heart attack.

On Wed, Sep 13, 2017 at 9:39 AM, Simon Slavin  wrote:

>
>
> On 13 Sep 2017, at 2:44pm, Dan K McCormick  wrote:
>
> > Thanking you in advance Dan
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> You can use the link at the end of every post, including this one, to
> remove yourself.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Sharing data between desktop and Android

2017-09-06 Thread Don V Nielsen
I use an app called AirDroid: https://www.airdroid.com/

It goes beyond copying files to/from phone and pc, but it is one of its
functionalities.


On Wed, Sep 6, 2017 at 6:08 AM, Cecil Westerhof 
wrote:

> 2017-09-06 12:54 GMT+02:00 Andy Ling :
>
> > > Cecil Westerhof wrote:
> > > > I am thinking about writing some Android applications. I would like
> to
> > > > share data between the phone (or tablet) and de desktop. What is the
> > best
> > > > way to do this? In a way that would also be convenient for other
> > people.
> > >
> > > I use an Android app that does this. It has a companion PC app that
> lets
> > > you backup and modify the underlying sqlite database. It transfers the
> > > database between Android and PC using a web link. There is a "sync
> > > to PC" menu on the app that opens the connection.
> > >
> >
> > ​> Can you share information about it?
> >
> > It's not my app, I just use it. It's called MobileSheetsPro. There's more
> > information
> > here
> >
> > http://www.zubersoft.com/mobilesheets/
> >
> > Mike, the guy that wrote it, is extremely helpful. So if you contact him
> > I'm sure
> > he'll help you out.
> >
>
> ​OK, I will do that. Thanks.
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] bug: failure to write journal reported as "disk I/O error"

2017-09-27 Thread Don V Nielsen
I'm sorry gentlemen, but the argument has gotten thick and petulant.

Every complaint and response is resolving down to a mainframe line of
thought (thank God), which few today are willing to accept. That is, the
SQLite software is kept compatible with its root. How many System 370 Cobol
programs can run on to today's hyper-tech mainframes? All of them. Sqlite
was inspired by a need and built at a time when PC's and O/S's were more
primitive. It has some flaws from then that are still with us today. Why?
Because of compatibility. It is more important for this product to be
compatible with its origin because people and machines are dependent on it
being that way.

The error system is what it is because it worked back then. Efforts have
been made to improve things as far as giving the developer more information
to work with and figure things out. The developer knows their version of
SQLite and their operating system(s). It's the developer's responsibility
to match what SQLite provides given the values available in the environment
that it exists in. If the developer's application is going to run atop of
Linux, and Windows, and Android, it is the developers job to create their
application in a way that is sensitive to them.

SQLite is capable of running anywhere. It is not its responsibility for
knowing exactly where it is being run. It doesn't function at that layer.
If the codes are not enough, the amalgamation is out there. Get a copy and
build into it a new layer of error interpretation logic and have it return
what is needed by the O/S that is specific to the application's needs and
wants.

If I'm wrong, I'm sorry. But I got the feeling the original post (the very
first) was a tantrum, and no matter what anyone does to sooth the situation
is working. It is only getting worse.

Again, I apologize for losing my control.

On Wed, Sep 27, 2017 at 12:12 PM, Guy Harris  wrote:

> On Sep 27, 2017, at 10:00 AM, Keith Medcalf  wrote:
>
> > On Wednesday, 27 September, 2017 10:39, Guy Harris 
> wrote:
> >
> >> On Sep 27, 2017, at 6:58 AM, Keith Medcalf  wrote:
> >
> >>> Well, the terminology is correct.  These *ARE* I/O Errors.  The
> >>> system attempted I/O.  It failed.  Hence the term I/O Error.
> >
> >> Just don't call it a "disk I/O error".
> >
> > Well, maybe.  However the I/O that had the error was associated with a
> disk operation (as opposed to a "Video I/O Error", or a "Cardpunch I/O
> Error", "Printer I/O Error", etc.).
>
> Actually, if it had occurred on my machine, it wouldn't have been
> associated with a disk operation; should the application check where the
> data is stored and say "flash memory I/O" error if appropriate? :-)
>
> The point is that the *disk* isn't particularly relevant to some possible
> errors - the problem isn't with the *disk*, which reported no error, the
> problem is with something in the *file system*, such as the amount of space
> available, the permissions on files, etc..
>
> >>> It is irrelevant whether the error was caused because the heads on
> >>> the tape drive need cleaning, access was denied to spool storage, the
> >>> disk was full, someone yanked the cable out of the disk drive, or the
> >>> card reader got jammed up.
> >
> >> I.e., SQLITE_IOERR is equivalent to -1 as a return from various UN*X
> >> system calls, so that, when a program sees it, it needs to get
> >> further error information, such as an errno value, to deal with the
> >> error and, if necessary, to report it.
> >
> > Yes.  An I/O operation of some sort was attempted.  That I/O operation
> involved some sort of "disk" access.  That operation failed with an error.
>
> ...and the next step is to determine what the exact error was.
>
> >> So it *is* relevant to what to do next.
> >
> > Well, in the same sort of way as the message from attempting to send
> Snail mail "Mail Undeliverable" is relevant to what to do next.  You know
> that the error was related to the delivery of the postal item just as the
> "Disk I/O Error" indicates that an I/O operation that involved a disk
> operation failed with an error.
> >
> > In both cases you need to query for the underlying error condition in
> order to determine what to do.
>
> Well, in the first case, the postal service may well say more than just
> "Mail undeliverable", such as "no such person at that address", "no such
> address", etc..
>
> > So in that sense it is relevant to what to do next -- you need to query
> for more particulars.  This is opposed to say a "Syntax Error" in which it
> is pretty clear that the error is a mis-formed statement.
>
> Yes, but even in *that* case, it's often possible to say, for example,
> "there's no operator between the operands "foo" and "bar"" rather than just
> "syntax error".
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> 

Re: [sqlite] My 1st C++ SQLite3 program

2017-08-28 Thread Don V Nielsen
database_name is never assigned a value? In SQLite3_RDB::SQLite3_RDB() it
is spelled databese_name?

On Sat, Aug 26, 2017 at 10:28 PM, Simon Slavin  wrote:

>
>
> On 27 Aug 2017, at 2:34am, Papa  wrote:
>
> > Why do I get this error message?
>
> Try having the program using file calls to open a simple text file in the
> same directory.  Does that succeed ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] GROUP_CONCAT separator and DISTINCT

2017-08-25 Thread Don V Nielsen
I think David Blake is suggesting that GROUP_CONCAT intuitively suggests it
should take the separator argument, regardless of DISTINCT being present.
It is logical that it should, given GROUP_CONCAT takes two arguments, not
one. The second argument defaults to a comma when omitted. The presence of
DISTINCT is really a lexical issue; an attribute of the first argument and
not an argument itself.

Using replace() or a subquery are workarounds, which is how they look and
feel. Using replace() is the most logical workaround...unless...the column
already contains a comma. In that case, then an awkward subquery is
required. This eventually leads one to think "what is this guy trying to
accomplish" when one reads it.

Just my two cents

On Fri, Aug 25, 2017 at 3:01 AM, Clemens Ladisch  wrote:

> Dave Blake wrote:
> > It seems that it is not possible to specify the concatenation separator
> > when using GROUP_CONCAT with DISTINCT.
>
> The documentation  says:
> | In any aggregate function that takes a single argument, that argument
> | can be preceded by the keyword DISTINCT.
>
> > Is there another way I can specify the separator when using DISTINCT?
>
> If your values do not contain commas, you can use replace() afterwards.
>
> Otherwise, use a subquery with DISTINCT first, and then run the
> group_concat() over that.
>
>
> Regars,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


  1   2   >