c program which performs the same function as the SQLite shell command ".import"

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|

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-3210000 of shell.c )

Is this a reasonable approach? Is there a better way?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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-3210000 of shell.c )
>
> Is this a reasonable approach? Is there a better way?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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" <[hidden email] on behalf of [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Shane Dev
In reply to this post by petern
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 <[hidden email]> 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 <[hidden email]> 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-3210000 of shell.c )
> >
> > Is this a reasonable approach? Is there a better way?
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 1/16/18, Shane Dev <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

> On 1/16/18, Shane Dev <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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 <[hidden email]> wrote:
>
> > On 1/16/18, Shane Dev <[hidden email]> 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
> > [hidden email]
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Shane Dev
On 17 January 2018 at 08:45, petern <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

> On 17 January 2018 at 08:45, petern <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Jens Alfke-2


> On Jan 17, 2018, at 3:53 AM, Bart Smissaert <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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" <[hidden email] on behalf of [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

petern
In reply to this post by Shane Dev
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 <[hidden email]> wrote:

> On 17 January 2018 at 08:45, petern <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 1/17/18, petern <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

> On 1/17/18, petern <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 1/17/18, petern <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Shane Dev
In reply to this post by petern
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 <[hidden email]> 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 <[hidden email]> wrote:
>
> > On 17 January 2018 at 08:45, petern <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> wrote:
> >
> > > On 17 January 2018 at 08:45, petern <[hidden email]>
> 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
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

R Smith-2
In reply to this post by petern
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Jean-Luc Hainaut
In reply to this post by Shane Dev

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 
<https://projects.info.unamur.be/%7Edbm/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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users