Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[Jean-Luc Hainaut]

True, "some" parts of "some" games can be implemented with DB 
technology, particularly matrix- and graph-based ones. Not only for fast 
storage and retrieval of game data, but, more interestingly, for 
implementing complex computation algorithms through SQL queries, that 
may prove faster than their expression in standard languages.


In a series of case studies I have developed to show that many problems 
can be elegantly and efficiently solved by a carefully designed DB 
schema + SQL queries, I have included three applications close to the 
game domain: text-based adventure games, Conway's cellular automata (aka 
"Game of life") and shortest path finding.


The text of these studies (all implemented in SQLite) are available on 
https://projects.info.unamur.be/~dbm/mediawiki/index.php/LIBD:Outils#SQLfast 
.


- text-based adventure games: download document "Case study: The book of 
which you are the hero"
- Conway's cellular automata: download document "Case study: Conway's 
Game of Life"
- shortest path problem:  download document "Case study: Path finders, 
rovers and Ariadne's thread".


Just my three cents!

J-L Hainaut


  Any practical realtime video game using SQLite is probably
doing so only to save and restore the game board between games.

and perhaps calculating the initial "maze" or other non time sensitive data
processing


Even a cursory look into production
quality video game development will tell you that a database is the wrong
technology to base a video game engine on.

Fully agree, I would use another library for that part.
___
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] c program which performs the same function as the SQLite shell command ".import"

[R Smith]

To add some thoughts to Peter's discussion...

In game design speed is definitely of the utmost importance since a 
visual game is basically a UI that is time-sensitive (unlike nearly any 
other type of software). It's usual to implement some slow data 
mechanism, typically an internet service DB for things like profile and 
history, then an internet comms module (multiplayer etc.), normal SQLite 
style DB for local data and on top of that a quick-lookup cache DB for 
anything that the UI will need rapidly.


The reason the cache needs to exist is that DB's are slow in game terms. 
They spend their time and 50% of their code to ensure your transaction 
is SAFE and ACCURATE rather than Quick (which is a MUST for any normal 
application, but we really don't care about much in games). A lookup 
list or pointer dictionary is a far better way to access your speedy 
info - but not the best way to access ALL your info, so the best system 
usually turns out to be some hybrid setup.


Sadly there is no off-the-shelf guaranteed best hybridization (that I 
know of), you will have to use the best way that is memory and space 
efficient, and where that falls short in retrieving timing, cache the 
items that are needed quicker. It's a process. We usually start out with 
a good DB engine (SQLite always in our case) and a good caching engine 
(which we route through the same dispatcher so that development of the 
downstream parts are oblivious and unaffected by how the data arrives), 
and then move things over between these systems as needed.


One accidental side-effect of using sqlite as much as possible is that 
over the last ~15 years games/apps using sqlite have gotten faster by no 
effort of ours, but by the mere virtue of sqlite code itself doubling in 
speed every 10 years or so. That's an amazing benefit.




On 2018/01/18 2:41 AM, petern wrote:

FYI. 2D/3D game usability is extremely sensitive to response time.  A stock
in-memory SQLite database with plenty of memory is still too slow for
tracking the state of an interactive graphical game especially on portable
grade cpus.  Any practical realtime video game using SQLite is probably
doing so only to save and restore the game board between games.   The
actual game time logic and player movements are entirely coordinated by a
custom engine which interfaces directly between highly optimized in-memory
data structures and the graphics library.   The equivalent loop in SQLite
would be a statement that has already been prepared and is receiving rows
from the database without interruption for the entire game duration.
Without heavy rework of the whole database concept, that simply can't work
because a statement's results are isolated from subsequent model changes
while result rows are being read.   Even a cursory look into production
quality video game development will tell you that a database is the wrong
technology to base a video game engine on.


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

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

>  Any practical realtime video game using SQLite is probably
> doing so only to save and restore the game board between games.


and perhaps calculating the initial "maze" or other non time sensitive data
processing


> Even a cursory look into production
> quality video game development will tell you that a database is the wrong
> technology to base a video game engine on.
>
>
Fully agree, I would use another library for that part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[petern]

FYI. 2D/3D game usability is extremely sensitive to response time.  A stock
in-memory SQLite database with plenty of memory is still too slow for
tracking the state of an interactive graphical game especially on portable
grade cpus.  Any practical realtime video game using SQLite is probably
doing so only to save and restore the game board between games.   The
actual game time logic and player movements are entirely coordinated by a
custom engine which interfaces directly between highly optimized in-memory
data structures and the graphics library.   The equivalent loop in SQLite
would be a statement that has already been prepared and is receiving rows
from the database without interruption for the entire game duration.
Without heavy rework of the whole database concept, that simply can't work
because a statement's results are isolated from subsequent model changes
while result rows are being read.   Even a cursory look into production
quality video game development will tell you that a database is the wrong
technology to base a video game engine on.







On Wed, Jan 17, 2018 at 2:43 PM, Shane Dev  wrote:

> In my day job, I am an SAP consultant - for over 20 years. Production
> quality code? Yes, but only within the companies where I have worked - tax,
> banking, inventory, procurement, sales, etc.
>
> My interest in SQLite is a personal hobby project at the moment. I have a
> couple of ideas for end user applications - a game (tentatively called
> "Canibal Ants") and a planning tool. Both of them would modeled with graphs
> (as in graph theory). Given the choice of complex core application code or
> a complex DB schema, I prefer the latter.
>
> At this stage, I am trying to understand the strategies used by experienced
> SQLite library users to solve common programming problems. I will
> investigate the shell_callback function.
>
> On 17 January 2018 at 19:21, petern  wrote:
>
> > Take a look at the function shell_callback for hints. See the MODE_Csv
> > case.
> > You could start by cribbing the functions MODE_Csv uses for your own row
> > handler and then see what you'll have to figure out yourself.
> >
> > Typically, if you are a serious product developer at a frontier in the
> > market, you will have to fairly expertly code and deploy your own
> > program(s) for every target platform on which you want your application
> to
> > get off the ground.  If what you're doing is worthwhile, then you will
> have
> > to somehow develop the code to make it happen.  That's the development
> > process.
> >
> > Let me ask some questions anybody reading your posts is definitely
> > wondering about.
> >
> > What is your background?  Have you done production quality software
> > development work before?
> >
> > Is your application worthwhile?  If you can say, what does your
> application
> > do for the end user that they couldn't do without it?
> >
> >
> >
> >
> >
> >
> >
> >
> > On Wed, Jan 17, 2018 at 2:54 AM, Shane Dev  wrote:
> >
> > > On 17 January 2018 at 08:45, petern 
> wrote:
> > >
> > > > Shane. Expect to do a lot of hacking on shell.c.  It's not intended
> as
> > a
> > > > library but as the main program of a console application.
> > >
> > >
> > > That's a shame. I try very hard not to reinvent the wheel especially
> when
> > > the wheel question (shell.c) is widely used, flexible and presumably
> > > thoroughly debugged.
> > >
> > > However, I can't be the only one trying to programmatically exchange
> data
> > > between SQLite and a delimited text file. For importing, the CSV
> virtual
> > > table works well for multi-column CSVs, thanks again for the tip. For
> > > exporting, I could retrieve the data using sqlite3_exec and build a
> > string
> > > from the 3rd and 4th parameters of the callback function. Then I would
> > need
> > > to code logic to insert the column and line separators and handle edge
> > > cases (fields containing separators or double quotes, single column
> > tables,
> > > etc) and finally write the string to a file.
> > >
> > > Is this most efficient approach?
> > > ___
> > > 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] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

In my day job, I am an SAP consultant - for over 20 years. Production
quality code? Yes, but only within the companies where I have worked - tax,
banking, inventory, procurement, sales, etc.

My interest in SQLite is a personal hobby project at the moment. I have a
couple of ideas for end user applications - a game (tentatively called
"Canibal Ants") and a planning tool. Both of them would modeled with graphs
(as in graph theory). Given the choice of complex core application code or
a complex DB schema, I prefer the latter.

At this stage, I am trying to understand the strategies used by experienced
SQLite library users to solve common programming problems. I will
investigate the shell_callback function.

On 17 January 2018 at 19:21, petern  wrote:

> Take a look at the function shell_callback for hints. See the MODE_Csv
> case.
> You could start by cribbing the functions MODE_Csv uses for your own row
> handler and then see what you'll have to figure out yourself.
>
> Typically, if you are a serious product developer at a frontier in the
> market, you will have to fairly expertly code and deploy your own
> program(s) for every target platform on which you want your application to
> get off the ground.  If what you're doing is worthwhile, then you will have
> to somehow develop the code to make it happen.  That's the development
> process.
>
> Let me ask some questions anybody reading your posts is definitely
> wondering about.
>
> What is your background?  Have you done production quality software
> development work before?
>
> Is your application worthwhile?  If you can say, what does your application
> do for the end user that they couldn't do without it?
>
>
>
>
>
>
>
>
> On Wed, Jan 17, 2018 at 2:54 AM, Shane Dev  wrote:
>
> > On 17 January 2018 at 08:45, petern  wrote:
> >
> > > Shane. Expect to do a lot of hacking on shell.c.  It's not intended as
> a
> > > library but as the main program of a console application.
> >
> >
> > That's a shame. I try very hard not to reinvent the wheel especially when
> > the wheel question (shell.c) is widely used, flexible and presumably
> > thoroughly debugged.
> >
> > However, I can't be the only one trying to programmatically exchange data
> > between SQLite and a delimited text file. For importing, the CSV virtual
> > table works well for multi-column CSVs, thanks again for the tip. For
> > exporting, I could retrieve the data using sqlite3_exec and build a
> string
> > from the 3rd and 4th parameters of the callback function. Then I would
> need
> > to code logic to insert the column and line separators and handle edge
> > cases (fields containing separators or double quotes, single column
> tables,
> > etc) and finally write the string to a file.
> >
> > Is this most efficient approach?
> > ___
> > 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] c program which performs the same function as the SQLite shell command ".import"

[Richard Hipp]

On 1/17/18, petern  wrote:
>
> Richard, since you're responding to questions, let me ask again about 3.22
> INTROPECTION_PRAGMAS release.

No.  We are past pencils-down.  No new features at this point.

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

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[petern]

csv.c isn't a writer.  Shane expected to write the file by inserting rows
into the vtable.  He has no application whatsoever but for the shell.

Richard, since you're responding to questions, let me ask again about 3.22
INTROPECTION_PRAGMAS release.
Will function_list() be progressing at all toward outputing a composite key
for the function?  What about a module name column in 3.22?
Seeing which module currently controls the function name would be a helpful
diagnostic at least.

Peter




On Wed, Jan 17, 2018 at 10:43 AM, Richard Hipp  wrote:

> On 1/17/18, petern  wrote:
> > Take a look at the function shell_callback for hints.
>
> If the goal is to create a TSV reader/writer, it seems like the CVS
> reader/writer might be a better starting point, as it is unencumbered
> by lots of other unrelated features as is the shell.  You might be
> able to get cvs.c to work simply by changing a single instance of a
> ',' literal into '\t'.  Probably a little more work than that will be
> involved, but not too much more.
>
> --
> 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] c program which performs the same function as the SQLite shell command ".import"

[Richard Hipp]

On 1/17/18, petern  wrote:
> Take a look at the function shell_callback for hints.

If the goal is to create a TSV reader/writer, it seems like the CVS
reader/writer might be a better starting point, as it is unencumbered
by lots of other unrelated features as is the shell.  You might be
able to get cvs.c to work simply by changing a single instance of a
',' literal into '\t'.  Probably a little more work than that will be
involved, but not too much more.

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

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[petern]

Take a look at the function shell_callback for hints. See the MODE_Csv
case.
You could start by cribbing the functions MODE_Csv uses for your own row
handler and then see what you'll have to figure out yourself.

Typically, if you are a serious product developer at a frontier in the
market, you will have to fairly expertly code and deploy your own
program(s) for every target platform on which you want your application to
get off the ground.  If what you're doing is worthwhile, then you will have
to somehow develop the code to make it happen.  That's the development
process.

Let me ask some questions anybody reading your posts is definitely
wondering about.

What is your background?  Have you done production quality software
development work before?

Is your application worthwhile?  If you can say, what does your application
do for the end user that they couldn't do without it?








On Wed, Jan 17, 2018 at 2:54 AM, Shane Dev  wrote:

> On 17 January 2018 at 08:45, petern  wrote:
>
> > Shane. Expect to do a lot of hacking on shell.c.  It's not intended as a
> > library but as the main program of a console application.
>
>
> That's a shame. I try very hard not to reinvent the wheel especially when
> the wheel question (shell.c) is widely used, flexible and presumably
> thoroughly debugged.
>
> However, I can't be the only one trying to programmatically exchange data
> between SQLite and a delimited text file. For importing, the CSV virtual
> table works well for multi-column CSVs, thanks again for the tip. For
> exporting, I could retrieve the data using sqlite3_exec and build a string
> from the 3rd and 4th parameters of the callback function. Then I would need
> to code logic to insert the column and line separators and handle edge
> cases (fields containing separators or double quotes, single column tables,
> etc) and finally write the string to a file.
>
> Is this most efficient approach?
> ___
> 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] c program which performs the same function as the SQLite shell command ".import"

[Peter Da Silva]

On 1/17/18, 11:07 AM, "sqlite-users on behalf of Jens Alfke" 
 
wrote:
> If I were tackling this, I’d look for an open-source CSV parser/generator 
> library. Once you have that, the part that reads/writes the rows to the 
> database is pretty simple.

If they’re reading tab separated files, I wouldn’t use CSV code... there’s a 
lot of complexity in CSV readers that are unnecessary for TSV because it 
doesn’t support or require quoting of embedded separators.

Just something like

char *s, *cols[MAXLINE], line[MAXLINE];
int col;
while(fgets(line, MAXLINE, fp)) {
s = line;
col = 0;
while(cols[col] = s, s = strchr(s, ‘\t’)) {
*s++ = ‘\0’;
col++;
}
if(s = strchr(cols[col], ‘\n’)) *s = ‘\0’;
// bind cols[...] to prepared statement
// step prepared statement
}



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

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[Jens Alfke]

> On Jan 17, 2018, at 3:53 AM, Bart Smissaert  wrote:
> 
> You don't have to, just need a different wheel.

If I were tackling this, I’d look for an open-source CSV parser/generator 
library. Once you have that, the part that reads/writes the rows to the 
database is pretty simple.

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

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[Bart Smissaert]

> I try very hard not to reinvent the wheel

You don't have to, just need a different wheel. I did this recently both
for .csv and also for .html
and working very nicely and far more flexible than using the code in
shell.c.

RBS

On Wed, Jan 17, 2018 at 10:54 AM, Shane Dev  wrote:

> On 17 January 2018 at 08:45, petern  wrote:
>
> > Shane. Expect to do a lot of hacking on shell.c.  It's not intended as a
> > library but as the main program of a console application.
>
>
> That's a shame. I try very hard not to reinvent the wheel especially when
> the wheel question (shell.c) is widely used, flexible and presumably
> thoroughly debugged.
>
> However, I can't be the only one trying to programmatically exchange data
> between SQLite and a delimited text file. For importing, the CSV virtual
> table works well for multi-column CSVs, thanks again for the tip. For
> exporting, I could retrieve the data using sqlite3_exec and build a string
> from the 3rd and 4th parameters of the callback function. Then I would need
> to code logic to insert the column and line separators and handle edge
> cases (fields containing separators or double quotes, single column tables,
> etc) and finally write the string to a file.
>
> Is this most efficient approach?
> ___
> 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] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

On 17 January 2018 at 08:45, petern  wrote:

> Shane. Expect to do a lot of hacking on shell.c.  It's not intended as a
> library but as the main program of a console application.


That's a shame. I try very hard not to reinvent the wheel especially when
the wheel question (shell.c) is widely used, flexible and presumably
thoroughly debugged.

However, I can't be the only one trying to programmatically exchange data
between SQLite and a delimited text file. For importing, the CSV virtual
table works well for multi-column CSVs, thanks again for the tip. For
exporting, I could retrieve the data using sqlite3_exec and build a string
from the 3rd and 4th parameters of the callback function. Then I would need
to code logic to insert the column and line separators and handle edge
cases (fields containing separators or double quotes, single column tables,
etc) and finally write the string to a file.

Is this most efficient approach?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[petern]

Shane. Expect to do a lot of hacking on shell.c.  It's not intended as a
library but as the main program of a console application.  Another way
involves controlling the IO handles of your process and sending strings but
that will probably run into portability problems that are even a bigger
headache.

Proceeding from the assumption the main program will be in a different
compilation unit, that function you want to call will have to be exportable
instead of static and you'll have to get a valid ShellState from
somewhere.  You could start your hacking by adding an exportable function
that takes only zLine and a forged ShellState to pass into the ultimate
do_meta_command() call.  Shell.c is interactive so you have to watch out
for it messing around with your program's IO handles.  If you plan to use
other shell.c functions and/or keep up with changes/fixes to the original
shell.c, try to isolate your changes to just the exported functions you add
and figure out some minimal #ifdef changes to disable the IO interactions.

There is no guarantee your additions and #ifdefs will continue to work if
shell.c has changes/fixes.  Expect merge headaches every time there's a new
release you want for your product.   If you only need the .import function,
cloning that code into your program could be easier to maintain but
probably more work up front.

Peter





On Tue, Jan 16, 2018 at 10:13 PM, Shane Dev  wrote:

> Apparently the CSV virtual table supports neither changes (INSERT, UPDATE,
> DELETE), nor reading single column csv files.
>
> What I really want is the functionality of .import and .output SQLite shell
> commands. Maybe a better strategy would be to compile shell.c with my c
> program and call the function do_meta_command(char *zLine, ShellState *p).
>
> To those familiar with shell.c, is this a reasonable approach?
>
> On 17 January 2018 at 00:15, Richard Hipp  wrote:
>
> > On 1/16/18, Shane Dev  wrote:
> > > I tried -
> > >
> > > sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='test.tsv');
> > >
> > > where test.tsv is a tab separated table. However
> > >
> > > select count(*) from t1;
> > >
> > > goes into an infinite loop. Do you how to specify a separator other
> than
> > > ","?
> >
> > The "C" in CSV stands for "Comma".  That is the only separator
> > supported.  But, you can probably edit the source code to do something
> > different.
> >
> > --
> > 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] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

Apparently the CSV virtual table supports neither changes (INSERT, UPDATE,
DELETE), nor reading single column csv files.

What I really want is the functionality of .import and .output SQLite shell
commands. Maybe a better strategy would be to compile shell.c with my c
program and call the function do_meta_command(char *zLine, ShellState *p).

To those familiar with shell.c, is this a reasonable approach?

On 17 January 2018 at 00:15, Richard Hipp  wrote:

> On 1/16/18, Shane Dev  wrote:
> > I tried -
> >
> > sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='test.tsv');
> >
> > where test.tsv is a tab separated table. However
> >
> > select count(*) from t1;
> >
> > goes into an infinite loop. Do you how to specify a separator other than
> > ","?
>
> The "C" in CSV stands for "Comma".  That is the only separator
> supported.  But, you can probably edit the source code to do something
> different.
>
> --
> 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] c program which performs the same function as the SQLite shell command ".import"

[Richard Hipp]

On 1/16/18, Shane Dev  wrote:
> I tried -
>
> sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='test.tsv');
>
> where test.tsv is a tab separated table. However
>
> select count(*) from t1;
>
> goes into an infinite loop. Do you how to specify a separator other than
> ","?

The "C" in CSV stands for "Comma".  That is the only separator
supported.  But, you can probably edit the source code to do something
different.

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

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

Thanks Peter,

That saved me hours of work.

According to the comments -

/* Read a single field of CSV text.  Compatible with rfc4180 and extended
** with the option of having a separator other than ",".

I tried -

sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='test.tsv');

where test.tsv is a tab separated table. However

select count(*) from t1;

goes into an infinite loop. Do you how to specify a separator other than
","?



On 16 January 2018 at 17:29, petern <peter.nichvolo...@gmail.com> wrote:

> FYI. csv.c is already a separate C program which imports CSV files without
> necessity of the SQLite shell:
>
> https://sqlite.org/csv.html
>
> On Tue, Jan 16, 2018 at 12:47 AM, Shane Dev <devshan...@gmail.com> wrote:
>
> > Hi,
> >
> > I am looking for an efficient way to write a c program which performs the
> > same function as the SQLite shell command ".import"
> >
> > My initial strategy is to include the sqlite library source files and
> copy
> > the control block from shell.c that begins after
> >
> > if( c=='i' && strncmp(azArg[0], "import", n)==0 ){
> >
> > up to and the line -
> >
> > if( needCommit ) sqlite3_exec(p->db, "COMMIT", 0, 0, 0);
> >
> > (i.e lines 5858-6052 in version sqlite-amalgamation-321 of shell.c )
> >
> > Is this a reasonable approach? Is there a better way?
> > ___
> > 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] c program which performs the same function as the SQLite shell command ".import"

[Peter Da Silva]

On 1/16/18, 10:29 AM, "sqlite-users on behalf of petern" 
 wrote:
> https://sqlite.org/csv.html

BTW typo on that page:

“The example above showed a single filename='th3file.csv' argument for the CSV 
virtual table.”

Should be:

“The example above showed a single filename='thefile.csv' argument for the CSV 
virtual table.”


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

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[petern]

FYI. csv.c is already a separate C program which imports CSV files without
necessity of the SQLite shell:

https://sqlite.org/csv.html

On Tue, Jan 16, 2018 at 12:47 AM, Shane Dev <devshan...@gmail.com> wrote:

> Hi,
>
> I am looking for an efficient way to write a c program which performs the
> same function as the SQLite shell command ".import"
>
> My initial strategy is to include the sqlite library source files and copy
> the control block from shell.c that begins after
>
> if( c=='i' && strncmp(azArg[0], "import", n)==0 ){
>
> up to and the line -
>
> if( needCommit ) sqlite3_exec(p->db, "COMMIT", 0, 0, 0);
>
> (i.e lines 5858-6052 in version sqlite-amalgamation-321 of shell.c )
>
> Is this a reasonable approach? Is there a better way?
> ___
> 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] c program which performs the same function as the SQLite shell command ".import"

[Shane Dev]

Hi,

I am looking for an efficient way to write a c program which performs the
same function as the SQLite shell command ".import"

My initial strategy is to include the sqlite library source files and copy
the control block from shell.c that begins after

if( c=='i' && strncmp(azArg[0], "import", n)==0 ){

up to and the line -

if( needCommit ) sqlite3_exec(p->db, "COMMIT", 0, 0, 0);

(i.e lines 5858-6052 in version sqlite-amalgamation-321 of shell.c )

Is this a reasonable approach? Is there a better way?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users