[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] sqlite fixed data loading extension

2016-03-25 Thread James K. Lowden
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] sqlite fixed data loading extension

2016-03-25 Thread Dominique Devienne
On Fri, Mar 25, 2016 at 12:49 PM, Don V Nielsen 
wrote:
>
> (I guess this begets the question..."Is sqlite's csv import an
extension?")


No, it is not an extension, it's part of SQLite's shell directly:
https://www.sqlite.org/cli.html#csv

Which also means it's not part of the SQLite library itself BTW.

Extensions are shared libraries with known (by convention) entry points
(i.e. functions)
which are loaded via https://www.sqlite.org/c3ref/load_extension.html,
which is exposed
by the shell as .load (see "Loading Extensions" in
https://www.sqlite.org/cli.html [1]).

As to your main question, I don't know of such an extension.

Thanks, --DD

PS: Richard, could we have anchors for all h3 titles in the HTML for
https://www.sqlite.org/cli.html, for direct linking please?


[sqlite] sqlite fixed data loading extension

2016-03-25 Thread Jim Callahan
>
> ??I have a need for something that can parse and load into sqlite tables
> fixed length data.


?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
> m
> ??
> ore $.?


The open source statistical language R can read fixed width files and write
out SQLite tables.

https://www.r-project.org/

Read a table of *f*ixed *w*idth *f*ormatted data into a data.frame
.
https://stat.ethz.ch/R-manual/R-devel/library/utils/html/read.fwf.html

Write a data frame to a SQLite table
http://www.rdocumentation.org/packages/RSQLite/functions/dbWriteTable


This blog post gives an overview of RSQLite
http://sandymuspratt.blogspot.com/2012/11/r-and-sqlite-part-1.html

Full documentation for the RSQLite package
https://cran.r-project.org/web/packages/RSQLite/RSQLite.pdf

BTW, since you are familiar with the Microsoft language C#;
Microsoft has purchased Revolution Analytics and is now
supporting a version of R.
https://mran.revolutionanalytics.com/open/

Jim Callahan
Orlando, FL

On Fri, Mar 25, 2016 at 7:49 AM, Don V Nielsen 
wrote:

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