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

Re: [sqlite] C++ compiler

[Igor Korot]

Hi,

On Tue, Jan 2, 2018 at 3:34 PM,   wrote:
> Hi:
>
> Not only it can, but that is probably the use in the 99.00% of C++ 
> applications that uses SQLite.

That number should probably be 99.999(9)%... ;-)

Thank you.

>
> The only caveat is that you can get some warnings depending on the compiler 
> you use.
>
> Cheers
>
> --
> Adolfo J. Millan
>
>>
>>  Mensaje original 
>> De: eli 
>> Para:  sqlite-users@mailinglists.sqlite.org
>> Fecha:  Sat, 30 Dec 2017 13:35:35 +0200
>> Asunto:  [sqlite] C++ compiler
>>
>> Hello,
>
> It would be awesome if SQLite could compile as a part of bigger C++ project.
> Right now there is a bunch of pointer casting errors, that can be fixed in
> a matter of hour IMHO.
>
> Cheers,
> ___
> 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++ compiler

[ajm]

Hi: 

Not only it can, but that is probably the use in the 99.00% of C++ applications 
that uses SQLite.

The only caveat is that you can get some warnings depending on the compiler you 
use.

Cheers

--
Adolfo J. Millan

>
>  Mensaje original 
> De: eli 
> Para:  sqlite-users@mailinglists.sqlite.org
> Fecha:  Sat, 30 Dec 2017 13:35:35 +0200
> Asunto:  [sqlite] C++ compiler
>
> Hello,

It would be awesome if SQLite could compile as a part of bigger C++ project.
Right now there is a bunch of pointer casting errors, that can be fixed in
a matter of hour IMHO.

Cheers,
___
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++ compiler

[Deon Brewis]

Ditto. Large C++ project - no problem using SQLite.

Are you trying to compile sqlite.c as a C++ file? That won't work obviously, 
you have to compile as C and link it in.

The sqlite3.h header however can be pulled into any C++ file.

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Tuesday, January 2, 2018 9:50 AM
To: SQLite mailing list 
Subject: Re: [sqlite] C++ compiler

Eli Sent: Saturday, December 30, 2017 6:36 AM

>It would be awesome if SQLite could compile as a part of bigger C++ project.
>Right now there is a bunch of pointer casting errors, that can be fixed 
>in a matter of hour IMHO.

I don't have any trouble using it as part of a larger C++ project.

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.bankofamerica.com%2Femaildisclaimer&data=02%7C01%7C%7C8d0be66235844225ba0508d552094785%7C84df9e7fe9f640afb435%7C1%7C0%7C636505122160488357&sdata=O5qNYHgSsFYXspXcltee5v9RKF4SKqIru%2BPY%2FRWyHrY%3D&reserved=0.
   If you are not the intended recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7C%7C8d0be66235844225ba0508d552094785%7C84df9e7fe9f640afb435%7C1%7C0%7C636505122160488357&sdata=xij9z7ZYvd5j7T5L1Ygo63BNeQPGYPbcx3eLCate8LI%3D&reserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ compiler

[Simon Slavin]

On 30 Dec 2017, at 11:35am, eli  wrote:

> It would be awesome if SQLite could compile as a part of bigger C++ project.

It can.  It should work fine.  This is the main way SQLite is intended to be 
used.  Download the "amalgamation" source code (one .h and one .c file) and 
include them in your project.

The only problem is making sure your compiler understands that a file ending in 
".c" is C source code, not C++ source code.

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

Re: [sqlite] C++ compiler

[Igor Korot]

Hi,

On Sat, Dec 30, 2017 at 5:35 AM, eli  wrote:
> Hello,
>
> It would be awesome if SQLite could compile as a part of bigger C++ project.
> Right now there is a bunch of pointer casting errors, that can be fixed in
> a matter of hour IMHO.

Which OS/compiler are you trying?
What is the exact error message you are receiving?
Did you modify t code in any way?

Thank you.

>
> Cheers,
> ___
> 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++ compiler

[Nelson, Erik - 2]

Eli Sent: Saturday, December 30, 2017 6:36 AM

>It would be awesome if SQLite could compile as a part of bigger C++ project.
>Right now there is a bunch of pointer casting errors, that can be fixed in
>a matter of hour IMHO.

I don't have any trouble using it as part of a larger C++ project.

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ compiler

[John McKown]

On Sat, Dec 30, 2017 at 5:35 AM, eli  wrote:

> Hello,
>
> It would be awesome if SQLite could compile as a part of bigger C++
> project.
> Right now there is a bunch of pointer casting errors, that can be fixed in
> a matter of hour IMHO.
>

​I'm not a very knowledgeable C++ programmer, but wouldn't a simple:

extern C {
...
... SQLite definitions
...
}

be a way to do it?

Or maybe I'm simplifying your question too much (wouldn't be the first time
for me).​



>
> Cheers,
>


-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C++ compiler

[eli]

Hello,

It would be awesome if SQLite could compile as a part of bigger C++ project.
Right now there is a bunch of pointer casting errors, that can be fixed in
a matter of hour IMHO.

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

Re: [sqlite] C# pragma integrity_check call throwing exception

[Roberts, Barry (FINTL)]

Simon,


> Your text makes it look like you think that that kind of corruption affects 
> only existing rows.  This is not the case.  If you continue to write to a 
> database which shows this problem, you can lose more of the existing rows 
> and/or the new data you're trying to write.  The proper reaction to any such 
> errors is to revert to an uncorrupted backup or, at minimum, to do VACUUM or 
> REINDEX then check that the data is still consistent and plausible.

I am aware that other issues may exist. If the integrity_check did not return 
"ok" we would then try and fix the issues using the command line tooling. 
Generally if/when there is an issue it is reasonably minor, and a VACUUM or 
REINDEX fixes it.  My concern was that the newer C# driver is showing different 
behaviour to the old driver when tested against the exact same sqlite file (I 
have unit tests covering this scenario).  I have to justify changing our source 
code because of it.

Keith,


> Have you run "PRAGMA integrity_check;" from the command line shell against 
> the same database and does it return rows or just throw the same exception?


Thanks for the suggestion, I have just run a test database that is "corrupted" 
using the latest sqlite3 download

SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open entities-corrupt.db3
sqlite> pragma integrity_check;
Error: database disk image is malformed
sqlite> pragma quick_check;
ok
sqlite> REINDEX;
sqlite> pragma quick_check;
ok
sqlite> pragma integrity_check;
ok
sqlite>

So it looks like the actual engine is doing this now. This helps me justify 
altering our code base, to work with the revised behaviour.

Kind Regards,
Fugro Intersite.

Barry Roberts.
b.robe...@fugro.com | www.fugro.com

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

Re: [sqlite] C# pragma integrity_check call throwing exception

[Keith Medcalf]

Have you run "PRAGMA integrity_check;" from the command line shell against the 
same database and does it return rows or just throw the same exception?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Roberts, Barry (FINTL)
>Sent: Tuesday, 24 October, 2017 09:04
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] C# pragma integrity_check call throwing exception
>
>Hi,
>
>I asked the following question a few weeks ago, but did not get any
>responses, hoping someone may have an idea.
>
>We are currently running System.Data.SQLite.dll 1.0.80.0 and would
>like to upgrade to a newer version. However there are a variety of
>issues we have run into, most of which I have now resolved, however
>one remains. Assume I have a database which is "malformed" due to
>rowid xxx missing from a table index. Using the 1.0.80.0 driver the
>following code would return the results from the pragma
>integrity_check call.
>
>private static IEnumerable
>IntegrityCheck(SQLiteConnection connection)
>{
>using (var command = new SQLiteCommand(connection))
>{
>command.CommandTimeout = 0;
>command.CommandText = "pragma integrity_check";
>
>using (var reader = command.ExecuteReader())
>{
>while (reader.Read())
>{
>yield return reader.GetString(0);
>}
>}
>}
>}
>
>Our system would get a list of the rowid problems allowing it to log
>them and inform the user. I am currently testing using the 1.0.105.1
>driver, however the ExecuteReader() call (above) throws an exception
>saying the database is malformed. That is not very helpful and
>effectively stops the system obtaining integrity check information.
>If I replace the integrity_check with quick_check it works and
>returns ok, so access to the file is ok, just some internal indexes
>are messed up. Is there any way to get the above working in the later
>drivers, or can someone tell me which driver update changed this
>behaviour?
>
>Kind Regards,
>Fugro Intersite.
>
>Barry Roberts.
>b.robe...@fugro.com<mailto:b.robe...@fugro.com> | www.fugro.com
>
>___
>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# pragma integrity_check call throwing exception

[Simon Slavin]

On 24 Oct 2017, at 4:03pm, Roberts, Barry (FINTL)  wrote:

> Our system would get a list of the rowid problems allowing it to log them and 
> inform the user. I am currently testing using the 1.0.105.1 driver, however 
> the ExecuteReader() call (above) throws an exception saying the database is 
> malformed.

You originally wrote

> In the System.Data.SQLite.dll 1.0.80.0 C# driver the following code would 
> return the reason, I would get a list of the rowid problems allowing me to 
> log them.

The output of "PRAGMA integrity_check" just shows what SQLite found using a 
simple method of finding faults.  SQLite does not exhaustively investigate 
every byte of the file trying to figure out what it might mean.  The PRAGMA is 
just to give you an idea how much corruption there is: whether it looks like a 
crash occurred during the writing of one row [1] or whether a corrupt database 
has been in use for some time and or had multiple sectors overwritten.

Please also note that by default "PRAGMA integrity_check" stops after the first 
100 errors.  If it finds 100 faults in one index it won’t show you any problems 
with another table.

You are attempting to use standard SQLite API calls to investigate a corrupt 
database.  There is nothing we can do to help with this.  There is no 
requirement for consistency in handling corrupt databases in SQLite: if a 
SQLite call returns SQLITE_CORRUPT, or if "PRAGMA integrity_check" returns any 
faults at all, SQLite has done its job.  It’s a "yes or no" thing.

If you want software which can forensically investigate SQLite database 
corruption and tell you every little thing it finds, there are such programs on 
the market and the writer of at least one of them read this mailing list.  But 
it can’t be done with just SQLite API calls.

> If I replace the integrity_check with quick_check it works and returns ok, so 
> access to the file is ok, just some internal indexes are messed up.

Your text makes it look like you think that that kind of corruption affects 
only existing rows.  This is not the case.  If you continue to write to a 
database which shows this problem, you can lose more of the existing rows 
and/or the new data you’re trying to write.  The proper reaction to any such 
errors is to revert to an uncorrupted backup or, at minimum, to do VACUUM or 
REINDEX then check that the data is still consistent and plausible.

Simon.

[1] Under normal operation, if you did not intentionally turn off safety 
measures using PRAGMAs, even power failure should not cause corruption to a 
SQLite database once it has been reopened.  However, many people use PRAGMAs to 
turn off safety measures hoping for greater speed.  And a faulty storage module 
can also cause this kind of corruption.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C# pragma integrity_check call throwing exception

[Roberts, Barry (FINTL)]

Hi,

I asked the following question a few weeks ago, but did not get any responses, 
hoping someone may have an idea.

We are currently running System.Data.SQLite.dll 1.0.80.0 and would like to 
upgrade to a newer version. However there are a variety of issues we have run 
into, most of which I have now resolved, however one remains. Assume I have a 
database which is "malformed" due to rowid xxx missing from a table index. 
Using the 1.0.80.0 driver the following code would return the results from the 
pragma integrity_check call.

private static IEnumerable IntegrityCheck(SQLiteConnection 
connection)
{
using (var command = new SQLiteCommand(connection))
{
command.CommandTimeout = 0;
command.CommandText = "pragma integrity_check";

using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return reader.GetString(0);
}
}
}
}

Our system would get a list of the rowid problems allowing it to log them and 
inform the user. I am currently testing using the 1.0.105.1 driver, however the 
ExecuteReader() call (above) throws an exception saying the database is 
malformed. That is not very helpful and effectively stops the system obtaining 
integrity check information. If I replace the integrity_check with quick_check 
it works and returns ok, so access to the file is ok, just some internal 
indexes are messed up. Is there any way to get the above working in the later 
drivers, or can someone tell me which driver update changed this behaviour?

Kind Regards,
Fugro Intersite.

Barry Roberts.
b.robe...@fugro.com | www.fugro.com

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

Re: [sqlite] C++ sqlite_version()

[Jens Alfke]

> On Aug 25, 2017, at 7:56 PM, Papa  wrote:
> 
> const std::string& SQLite3_RDB::getSQLiteVersion(){
> tmp = sqlite_version();
> }

sqlite_version() is not a function in SQLite itself; is it from some wrapper 
library you're using? Most likely the crash is in that function. Look at the 
crash backtrace.

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

Re: [sqlite] C++ sqlite_version()

[Keith Medcalf]

https://sqlite.org/c3ref/libversion.html


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Papa
>Sent: Friday, 25 August, 2017 20:56
>To: SQLite mailing list
>Subject: [sqlite] C++ sqlite_version()
>
>In my C++ program, I'd like to display the SQLite3 version. To do so,
>this is what I have done.
>
>class SQLite3_RDB {
>     private:
>     sqlite3* db; //!< Data Base
>         std::string tmp;
>    public:
>     SQLite3_RDB(){}
>     ~SQLite3_RDB(){sqlite3_close(db); }
>     const std::string& getSQLiteVersion();
>
>};
>
>const std::string& SQLite3_RDB::getSQLiteVersion(){
>     tmp = sqlite_version();
>}
>
>However, as soon as I call the getSQLiteVersion() class member
>function,
>I get a "program failure". The gdb reports 'Signal received: SIGSEGV
>(Segmentation fault)'
>What am I doing wrong?
>
>--
>ArbolOne.ca
>Using Fire Fox and Thunderbird.
>ArbolOne is composed of students and volunteers dedicated to
>providing free services to charitable organizations.
>ArbolOne on Java Development in progress [ í ]
>
>___
>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++ sqlite_version()

[Keith Medcalf]

sqlite3_version is not a function.  it is the "location" of the version string.

The definition of sqlite3_version is:

char sqlite3_version[];
equivalent to
char *sqlite3_version;

not a function that returns a pointer.  

It is a pointer to a zero terminated string (or rather the address of the first 
character of the zero-terminated version string)

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Papa
>Sent: Friday, 25 August, 2017 20:56
>To: SQLite mailing list
>Subject: [sqlite] C++ sqlite_version()
>
>In my C++ program, I'd like to display the SQLite3 version. To do so,
>this is what I have done.
>
>class SQLite3_RDB {
>     private:
>     sqlite3* db; //!< Data Base
>         std::string tmp;
>    public:
>     SQLite3_RDB(){}
>     ~SQLite3_RDB(){sqlite3_close(db); }
>     const std::string& getSQLiteVersion();
>
>};
>
>const std::string& SQLite3_RDB::getSQLiteVersion(){
>     tmp = sqlite_version();
>}
>
>However, as soon as I call the getSQLiteVersion() class member
>function,
>I get a "program failure". The gdb reports 'Signal received: SIGSEGV
>(Segmentation fault)'
>What am I doing wrong?
>
>--
>ArbolOne.ca
>Using Fire Fox and Thunderbird.
>ArbolOne is composed of students and volunteers dedicated to
>providing free services to charitable organizations.
>ArbolOne on Java Development in progress [ í ]
>
>___
>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++ sqlite_version()

[Papa]

In my C++ program, I'd like to display the SQLite3 version. To do so, 
this is what I have done.


class SQLite3_RDB {
    private:
    sqlite3* db; //!< Data Base
        std::string tmp;
   public:
    SQLite3_RDB(){}
    ~SQLite3_RDB(){sqlite3_close(db); }
    const std::string& getSQLiteVersion();

};

const std::string& SQLite3_RDB::getSQLiteVersion(){
    tmp = sqlite_version();
}

However, as soon as I call the getSQLiteVersion() class member function, 
I get a "program failure". The gdb reports 'Signal received: SIGSEGV 
(Segmentation fault)'

What am I doing wrong?

--
ArbolOne.ca
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to providing free 
services to charitable organizations.
ArbolOne on Java Development in progress [ í ]

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

Re: [sqlite] C API: which calls have the biggest chance of latency?

[Jens Alfke]

> On May 25, 2017, at 10:00 PM, Wout Mertens  wrote:
> 
> I would like to make it partially asynchronous, still doing most of the
> work on the main thread, but waiting in a helper thread. I was thinking
> that the longest delays will be from disk access, so sqlite_step().

SQLite has a cache, so accessing recently-used pages of the database doesn’t 
hit the disk at all. (Unless the OS is low on memory and parts of the cache 
have been paged out…)
And the OS has a disk cache, so even if SQLite has to hit the filesystem, that 
request might be nearly instantaneous if the page is cached by the OS.
If actual disk access is required, the time can vary wildly — it depends on on 
how many other I/O requests by other processes are in the queue, what type of 
disk it is, how far the head has to seek if it’s a hard disk…

What I’m saying is that it’s pretty hard to predict. In the worst case I’ve 
seen queries that normally take a millisecond last for several seconds, when 
the OS is heavily bogged down with I/O.

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

Re: [sqlite] C API: which calls have the biggest chance of latency?

[Richard Hipp]

On 5/26/17, Wout Mertens  wrote:
>
> Are the above assumptions correct? Any other calls (besides opening the db)
> that can take a long time?

Most of the work associated with opening the database connection
(which is to say, parsing the schema) is deferred until the first time
you call sqlite3_prepare_v2().  Note also that if another process
modifies the schema (for example by running CREATE TABLE or CREATE
INDEX) then SQLite will automatically reparse the whole schema the
next time you run sqlite3_step() for previously prepared statements.

Note that parsing the schema involves calling sqlite3_exec() to run an
SQL statement that reads the schema - something that is possible
because SQLite is reentrant.  See
https://www.sqlite.org/src/artifact/b1140c3d0?ln=298 for the recursive
call to sqlite3_exec() and
https://www.sqlite.org/src/artifact/b1140c3d0?ln=289-291 for the
SELECT statement it uses for this.  Then for each CREATE statement in
the schema, SQLite recurses yet one more time to parse that statement
as well.  See https://www.sqlite.org/src/artifact/b1140c3d0?ln=84 for
the second level recursion.  Prior to the second recursion, SQLite
sets flags that tell the parser not to actually create the tables and
indexes, but just build up its internal symbol table.  Hence, the
statement that gets prepared does not actually get stepped.  The
side-effect of building the symbol tables, which is what we want,
happens during the preparation.

All of this is to say there is a lot going on under the hood, and all
that mechanism is deferred until the last possible moment, which means
it can happen just about anytime.  It is not as simple as saying "all
the hard work is done during sqlite3_step()".

Normally a schema parse takes microseconds - SQLite's parser will
normally churn though 100K or more SQL statements per second -  but it
can be longer depending on how big the schema is.

-- 
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 API: which calls have the biggest chance of latency?

[R Smith]

On 2017/05/26 7:33 AM, Simon Slavin wrote:

On 26 May 2017, at 6:00am, Wout Mertens  wrote:


Ideally there'd be some way to know if a _step() call will be served from
buffer…

There are (simplified) three possibilities: quick quick, slow slow, and slow 
quick.

A) SQLite finds a good index for the search/sort and can easily locate all the 
rows it needs to execute the call.  In this case, both the initial _step() and 
all the others will execute quickly.

B) SQLite can’t find an ideal index for the query but finds one which will 
allow it to execute the query acceptably, just skipping down the table 
identifying which rows should be processed.  In this case, both the initial 
_step() and all the others will execute slowly.  But if the table is short, or 
if your command needs to execute a large proportion of the rows in the table 
that might not be very slowly.

C) SQLite can’t find any helpful indexes and decides that the most efficient 
way to execute the command involves making a temporary index.  In this case, 
the initial _step() can take a long time but subsequent _step()s can be fast.


Just to add, there could be a great amount of processing involved in 
even starting the query, such as using WITH clauses or SELECTing from a 
sub-query or perhaps using an IN operator on another query and the like. 
In these cases, my experience is (on all DB systems I use) that the 
initial STEP can take several magnitudes more time than the subsequent 
ones. It's hard to pin down a rule, it is not a consistent thing.




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

Re: [sqlite] C API: which calls have the biggest chance of latency?

[Wout Mertens]

On Fri, May 26, 2017 at 7:33 AM Simon Slavin  wrote:

>
> On 26 May 2017, at 6:00am, Wout Mertens  wrote:
>
> > Ideally there'd be some way to know if a _step() call will be served from
> > buffer…
>
> There are (simplified) three possibilities: quick quick, slow slow, and
> slow quick.
>
[...]
Aha, good to know, thanks!

I can’t speak to how useful it will be to handle _step() synchronously in
> real life applications.  Most of my applications are web-facing systems and
> handle their SQLite databases asynchronously because that’s how server
> access works in JS.  They make HTTP calls to a PHP application running on a
> server.  But for SELECT commands there’s really little they can do until
> they’ve got the first line back and know whether there are any rows.  Maybe
> prepare a few DOM structures for the results, but that doesn’t take long.
>

The reason better-sqlite3 calls sqlite in the main thread is that there is
a substantial cost to running the DB query in a different thread, involving
double copying and mutexes. For small databases, that overhead can really
add up, especially if the whole db fits in cache.

In my case however, my app runs web services as well as biggish database
access in a single process, so I can't block for potentially hundreds of
ms. I do like the simplicity of the library, so I'd like it to be minimally
asynchronous.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API: which calls have the biggest chance of latency?

[Simon Slavin]

On 26 May 2017, at 6:00am, Wout Mertens  wrote:

> Ideally there'd be some way to know if a _step() call will be served from
> buffer…

There are (simplified) three possibilities: quick quick, slow slow, and slow 
quick.

A) SQLite finds a good index for the search/sort and can easily locate all the 
rows it needs to execute the call.  In this case, both the initial _step() and 
all the others will execute quickly.

B) SQLite can’t find an ideal index for the query but finds one which will 
allow it to execute the query acceptably, just skipping down the table 
identifying which rows should be processed.  In this case, both the initial 
_step() and all the others will execute slowly.  But if the table is short, or 
if your command needs to execute a large proportion of the rows in the table 
that might not be very slowly.

C) SQLite can’t find any helpful indexes and decides that the most efficient 
way to execute the command involves making a temporary index.  In this case, 
the initial _step() can take a long time but subsequent _step()s can be fast.

Here’s the thing.  An experienced SQLite programmer creates indexes suited to 
all WHERE and ORDER BY clauses they use, and then executes ANALYZE.  If you do 
this, SQLite can always do (A), and because everything executes quickly there’s 
little advantage to asynchronous queries.  The only time you really need to 
worry about (B) and (C) is when you provide facilities to your user allowing 
them to make up their own selection or sort order.  Of course, your particular 
needs may include this.

I can’t speak to how useful it will be to handle _step() synchronously in real 
life applications.  Most of my applications are web-facing systems and handle 
their SQLite databases asynchronously because that’s how server access works in 
JS.  They make HTTP calls to a PHP application running on a server.  But for 
SELECT commands there’s really little they can do until they’ve got the first 
line back and know whether there are any rows.  Maybe prepare a few DOM 
structures for the results, but that doesn’t take long.

Hope this helps.

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

[sqlite] C API: which calls have the biggest chance of latency?

[Wout Mertens]

I am liking the simplicity of the better-sqlite3 Nodejs library, but it is
synchronous (for some good reasons), so it will hang the main thread until
sqlite is done.

I would like to make it partially asynchronous, still doing most of the
work on the main thread, but waiting in a helper thread. I was thinking
that the longest delays will be from disk access, so sqlite_step().

The idea is to call sqlite_step() in a thread and then wait for the main
thread to copy the result into JS values (JS memory is not threadsafe),
then wait again and so on.

I wonder, is it only the first _step() that takes a really long time, doing
the query planning and index reads etc, and the rest is reading mostly from
buffered data? That would allow reading all the values in the main thread,
after the initial wait, or maybe per 10 rows or so.

Ideally there'd be some way to know if a _step() call will be served from
buffer…

Are the above assumptions correct? Any other calls (besides opening the db)
that can take a long time?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[James K. Lowden]

On Tue, 9 Aug 2016 17:09:39 -0300
Paulo Roberto  wrote:

> I would like something like this:
> 
> "BEGIN EXCLUSIVE TRANSACTION;"
>  "SELECT counter FROM mytable WHERE counterid = ?;"
>  "UPDATE mytable SET counter=? WHERE counterid = ?;"
>   "COMMIT TRANSACTION;"

begin transaction;

UPDATE mytable SET counter = (
select 1 + max(counter)
from mytable where counterid = ? )
WHERE counterid = ?;

select counter - 1 as counter 
from mytable where counterid = ?;

commit transaction;

Standard SQL.  Doesn't rely on BEGIN EXCLUSIVE.  Should be just as
fast.  

--jkl

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

Re: [sqlite] C API - Parameterized Atomic Transactions

[Dominique Devienne]

On Thu, Aug 11, 2016 at 4:34 AM, Paulo Roberto 
wrote:

> Thank you very much, it worked!


Just remember that exposing a SQL function that de-references a
"user"-supplied integer value as a pointer is inherently unsafe.
Anyone can select remember(val, 0) or select remember(val, 101) and crash
(at best) your app, or worse silently corrupt it in some way.

A safer (but slower) approach would be to add a level of indirection, and
not use the int as a "pointer" directly, but as a *key* in a map
(a global) used to lookup the memory address of the counter, so you *can*
error out (instead of crash/corrupt) on a bad input/key. My $0.02.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Paulo Roberto]

Thank you very much, it worked!

On Tue, Aug 9, 2016 at 11:49 PM, Richard Hipp  wrote:

> On 8/9/16, Paulo Roberto  wrote:
> >
> > I found your solution pretty elegant and I tried to implement it.
> > But after solving a lot of building issues with the sqlite3ext header
>
> It does not have to be implemented as a loadable extension.  Just copy
> the lines https://www.sqlite.org/src/artifact/8440f8d0b4?ln=41-53 into
> your application, then invoke
> https://www.sqlite.org/src/artifact/8440f8d0b4?ln=65-66 on the
> database connection right after you get it back from sqlite3_open().
>
> --
> 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 API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Paulo Roberto  wrote:
>
> I found your solution pretty elegant and I tried to implement it.
> But after solving a lot of building issues with the sqlite3ext header

It does not have to be implemented as a loadable extension.  Just copy
the lines https://www.sqlite.org/src/artifact/8440f8d0b4?ln=41-53 into
your application, then invoke
https://www.sqlite.org/src/artifact/8440f8d0b4?ln=65-66 on the
database connection right after you get it back from sqlite3_open().

-- 
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 API - Parameterized Atomic Transactions

[Paulo Roberto]

Thank you for all the answers.

Clemens,
The counterid in my case is a text field and not an integer. That's why I
need to sanitize.

Clemens and Keith,
As each of my process has its own connection to the database, I tried your
solution using BEGIN IMMEDIATE and it worked successfully.
Thank you.


Richard,

I found your solution pretty elegant and I tried to implement it.
But after solving a lot of building issues with the sqlite3ext header I was
not able to figure out how to use the function sqlite3_remember_init even
reading the MACROS definition.

My problem is with the parameter :

const sqlite3_api_routines *pApi

No matter what value I pass I keep receiving the signal SIGSEV
i.e. Segmentation fault.

Do you have another reference to give to me?

Thanks for your help.

Regards.

Paulo Roberto.


On Tue, Aug 9, 2016 at 9:07 PM, Keith Medcalf  wrote:

>
> > "BEGIN EXCLUSIVE TRANSACTION;"
> >  "SELECT counter FROM mytable WHERE counterid = ?;"
> >  "UPDATE mytable SET counter=? WHERE counterid = ?;"
> >   "COMMIT TRANSACTION;"
>
> > I have a counter that I need to increment and get its previous value in
> one
> > operation.
> > To access this counter I must pass as a parameter to the query a specific
> > WHERE condition.
>
> To paraphrase:
>
> I want to return the current value of a counter identified by an id and
> then increment it.  This operation must be atomic (have repeatable read
> isolation).
>
> BEGIN IMMEDIATE;
> SELECT counter FROM mytable WHERE counterid = ?;
> UPDATE mytable SET counter = counter + 1 WHERE counterid = ?;
> COMMIT;
>
> Of course, the 1 in the increment does not need to be a constant but can
> be a ? if you are incrementing by some arbitrary value.
>
> You need to prepare and execute the statements one after each.
>
> And yes, the select and update, performed inside the same transaction, on
> a connection not being simultaneously used for "other purposes" is executed
> with repeatable read isolation.
>
>
>
>
> ___
> 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 API - Parameterized Atomic Transactions

[Keith Medcalf]

> "BEGIN EXCLUSIVE TRANSACTION;"
>  "SELECT counter FROM mytable WHERE counterid = ?;"
>  "UPDATE mytable SET counter=? WHERE counterid = ?;"
>   "COMMIT TRANSACTION;"

> I have a counter that I need to increment and get its previous value in one
> operation.
> To access this counter I must pass as a parameter to the query a specific
> WHERE condition.

To paraphrase:

I want to return the current value of a counter identified by an id and then 
increment it.  This operation must be atomic (have repeatable read isolation).

BEGIN IMMEDIATE;
SELECT counter FROM mytable WHERE counterid = ?;
UPDATE mytable SET counter = counter + 1 WHERE counterid = ?;
COMMIT;

Of course, the 1 in the increment does not need to be a constant but can be a ? 
if you are incrementing by some arbitrary value.

You need to prepare and execute the statements one after each.

And yes, the select and update, performed inside the same transaction, on a 
connection not being simultaneously used for "other purposes" is executed with 
repeatable read isolation.




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

Re: [sqlite] C API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Richard Hipp  wrote:

> Or, you could make remember() a two argument function:
>
>UPDATE mytable SET counter=remember(counter, $ptr)+1 WHERE counterid=$id
>

A sample implementation for this function can now been seen at
https://www.sqlite.org/src/artifact/8440f8d0b452c5cd
-- 
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 API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Richard Hipp  wrote:
>
> UPDATE mytable SET counter=remember(counter)+1 WHERE counterid=?
>

Or, you could make remember() a two argument function:

   UPDATE mytable SET counter=remember(counter, $ptr)+1 WHERE counterid=$id

Then bind $ptr to the address of the variable in which you want to
store the original value (and also bind $id to the particular counter
you want to look up, of course).  That way, the same remember()
function could be reused with multiple variables for storing the
result - just rebind the $ptr value each time.

This requires casting a pointer into a 64-bit integer, which is not
guaranteed to work according to various C/C++ standards but which does
in fact work on all modern architectures.
-- 
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 API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Paulo Roberto  wrote:
>
> I need some help to ... increment a counter and get its
> former value.

> My question is: Preparing 4 statements, binding then and calling
> *sqlite3_step
> *for each one of then in order, would have the expected atomic operation
> behavior or not? If not, how could I achieve this?
>

Yes.

Maybe another technique would be to create an application-defined
function named "remember()" that takes a single integer argument and
returns the same value, but remembers the value in a variable in your
application.  Then run:

UPDATE mytable SET counter=remember(counter)+1 WHERE counterid=?

That way you would only have a single prepared statement to deal with.
On the other hand, the results get written into a single variable, so
it wouldn't work to use this from multiple threads, unless each thread
had its own remember() function.
-- 
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 API - Parameterized Atomic Transactions

[Simon Slavin]

On 9 Aug 2016, at 9:09pm, Paulo Roberto  wrote:

> My question is: Preparing 4 statements, binding then and calling *sqlite3_step
> *for each one of then in order, would have the expected atomic operation
> behavior or not?

You might be happier with BEGIN IMMEDIATE.

No other connections can make changes between your BEGIN IMMEDIATE and your 
COMMIT.

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

Re: [sqlite] C API - Parameterized Atomic Transactions

[Clemens Ladisch]

Paulo Roberto wrote:
> I need some help to do a simple operation, increment a counter and get its
> former value.
> I could have some race condition, so the transaction must be atomic.
>
> I also would like to use prepared statements to accomplish that, so I have
> less effort sanitizing inputs.

Integer numbers would not need to be sanitized.

> "BEGIN EXCLUSIVE TRANSACTION;"
> "SELECT counter FROM mytable WHERE counterid = ?;"
> "UPDATE mytable SET counter=? WHERE counterid = ?;"
> "COMMIT TRANSACTION;"

> My question is: Preparing 4 statements, binding then and calling *sqlite3_step
> *for each one of then in order, would have the expected atomic operation
> behavior?

Yes; that is what transactions are for.
(You have to ensure that errors in the middle statements are handled correctly.)


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

[sqlite] C API - Parameterized Atomic Transactions

[Paulo Roberto]

Hello,

I need some help to do a simple operation, increment a counter and get its
former value.
I could have some race condition, so the transaction must be atomic.

I also would like to use prepared statements to accomplish that, so I have
less effort sanitizing inputs.

My problem:

I have a counter that I need to increment and get its previous value in one
operation.
To access this counter I must pass as a parameter to the query a specific
WHERE condition.

I would like something like this:

"BEGIN EXCLUSIVE TRANSACTION;"
 "SELECT counter FROM mytable WHERE counterid = ?;"
 "UPDATE mytable SET counter=? WHERE counterid = ?;"
  "COMMIT TRANSACTION;"

The *sqlite3_exec* doesn't allow me to pass parameters.
And the documentation of *sqlite3_prepare_v2 *says: "*These routines only
compile the first statement in zSql, so *pzTail is left pointing to what
remains uncompiled*"

My question is: Preparing 4 statements, binding then and calling *sqlite3_step
*for each one of then in order, would have the expected atomic operation
behavior or not? If not, how could I achieve this?

Thank you in advance.

Regards.

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

[sqlite] C API reference manpages

[Kristaps Dzonsons]

> On Thu, 31 Mar 2016 10:21:53 -0400
> Richard Hipp  wrote:
> 
>> On 3/31/16, Kristaps Dzonsons  wrote:
>>>
>>> Is there any interest in integrating this tool to have manpages in
>>> the doc distribution without downstream bits?
>>>
>>
>> I think that would be cool.  Integrating your tool into the source
>> tree would mean that as the comment formats evolve, your tool would
>> evolve in lock-step.  
> 
> +1.  I'm another who's wanted SQLite man pages for a long time.  I hope
> you can see your way to integrating Kristaps's tool.  

Ping?

Any word on what can be done/I can do (CC0, whatever) to facilitate
getting our favourite database some mdoc(7) manpages?

(For those who weren't here: https://github.com/kristapsdz/sqlite2mdoc)

Best,

Kristaps

[sqlite] C API reference manpages

[Kristaps Dzonsons]

> How about the CC0 license? I think it's designed for these sorts of
> things (you want to make something public domain even if you're not
> allowed to) - https://creativecommons.org/about/cc0/

Jonathon,

I think the problem is that LV is similar to Norway[1] in this regard,
so something like CC0 doesn't work as one would expect it to.  However,
the manpages generated by sqlite2mdoc are in whatever license (or
no-license) you want, so at the end of the day, it doesn't affect the
library or its included documentation.  (After all, tcl isn't PD, and
that's required as it is!)

Licensing aside, if anybody has any comments or issues on the tool or
its mdoc(7) output, let me know!

Best,

Kristaps

[1]
http://lists.ibiblio.org/pipermail/cc-community/2014-October/thread.html#8863
(``Open Definition 2.0 released'')


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 842 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] C API reference manpages

[Jonathan Moules]

How about the CC0 license?
I think it's designed for these sorts of things (you want to make something 
public domain even if you're not allowed to) - 
https://creativecommons.org/about/cc0/

 On Fri, 01 Apr 2016 00:05:30 +0100 Kristaps Dzonsons  wrote  

>> As for public domain, I'm happy to put the sources under a similar 
>> license. I can't speak for the voodoo of the public domain and the EU 
>> (or is it country-by-country?), however. 
> 
> From an English translation I found of the Latvian law includes moral 
> rights and is closer to the droit d'auteur than copyright. So it seems 
> difficult to place your work in the public domain. 
> 
> I would advise you to contact the FSF, FSFE Legal or SFLC in this matter 
> if you want a general advice without legal fees. This entire topic as 
> been discussed in context of Wikimedia's projects, especially Wikipedia 
> and Wikimedia Commons, and CC0, so you might find information about 
> Latvia there. 

Matthias-Christian, 

I'm afraid I can't say anything useful about licenses, but in searching 
around, it appears that you're correct[1]. 

[sqlite] C API reference manpages

[James K. Lowden]

On Thu, 31 Mar 2016 10:21:53 -0400
Richard Hipp  wrote:

> On 3/31/16, Kristaps Dzonsons  wrote:
> >
> > Is there any interest in integrating this tool to have manpages in
> > the doc distribution without downstream bits?
> >
> 
> I think that would be cool.  Integrating your tool into the source
> tree would mean that as the comment formats evolve, your tool would
> evolve in lock-step.  

+1.  I'm another who's wanted SQLite man pages for a long time.  I hope
you can see your way to integrating Kristaps's tool.  

--jkl

[sqlite] C API reference manpages

[Kristaps Dzonsons]

>> As for public domain, I'm happy to put the sources under a similar
>> license.  I can't speak for the voodoo of the public domain and the EU
>> (or is it country-by-country?), however.
> 
> From an English translation I found of the Latvian law includes moral
> rights and is closer to the droit d'auteur than copyright. So it seems
> difficult to place your work in the public domain.
> 
> I would advise you to contact the FSF, FSFE Legal or SFLC in this matter
> if you want a general advice without legal fees. This entire topic as
> been discussed in context of Wikimedia's projects, especially Wikipedia
> and Wikimedia Commons, and CC0, so you might find information about
> Latvia there.

Matthias-Christian,

I'm afraid I can't say anything useful about licenses, but in searching
around, it appears that you're correct[1].

Assuming, arguendo, that this is true, I'm happy to continue keeping the
source where it is and tracking the header file--something I'd need to
do anyway, of course, regardless of where it sits.  Or if, as Richard
intimated, an ISC licensed file is acceptable in docsrc, it may be kept
there.  I'm open to suggestions!

Regarding the system itself, are there any desired features or
invocations that I can implement for ease of use and/or utility?  It
emits pretty standard mdoc(7) markup except for the IMPLEMENTATION
NOTES, which is non-canonical (but documented), where I stash the actual
declarations themselves.  In short, the manpages have everything in them
that I would need as a user.

Best,

Kristaps

[1] http://www.satori.lv/raksts/4336/Autortiesibasunbriva_kultura
(Link contents in Latvian.)

[sqlite] C API reference manpages

[Matthias-Christian Ott]

On 31/03/16 14:39, Kristaps Dzonsons wrote:
>>> Is there any interest in integrating this tool to have manpages in the
>>> doc distribution without downstream bits?
>>
>> I think that would be cool.  Integrating your tool into the source
>> tree would mean that as the comment formats evolve, your tool would
>> evolve in lock-step.  If your tool is kept separate, then we might
>> change the documentation comment format without telling you, and break
>> things.
>>
>> One issue is the license.  SQLite is public domain, not BSD (or ISC).
>> Is it even possible for citizens of Latvia to release something to the
>> public domain?  In any event, some paperwork would have to flow
>> between us to make that happen.  Though, maybe that is not a huge
>> concern as we are not nearly as persnickety with licensing for the
>> documentation as we are with the code, and this tool would go in the
>> separate documentation repository
>> (https://www.sqlite.org/docsrc/timeline), not in the main code
>> repository.
>>
>> Would an even better solution be to enhance the capi3ref TCL script to
>> spit out mdoc files in parallel to the HTML documentation it currently
>> produces?
> 
> Richard,
> 
> As for public domain, I'm happy to put the sources under a similar
> license.  I can't speak for the voodoo of the public domain and the EU
> (or is it country-by-country?), however.

>From an English translation I found of the Latvian law includes moral
rights and is closer to the droit d'auteur than copyright. So it seems
difficult to place your work in the public domain.

I would advise you to contact the FSF, FSFE Legal or SFLC in this matter
if you want a general advice without legal fees. This entire topic as
been discussed in context of Wikimedia's projects, especially Wikipedia
and Wikimedia Commons, and CC0, so you might find information about
Latvia there.

- Matthias-Christian

[sqlite] C API reference manpages

[Kristaps Dzonsons]

>> Is there any interest in integrating this tool to have manpages in the
>> doc distribution without downstream bits?
> 
> I think that would be cool.  Integrating your tool into the source
> tree would mean that as the comment formats evolve, your tool would
> evolve in lock-step.  If your tool is kept separate, then we might
> change the documentation comment format without telling you, and break
> things.
> 
> One issue is the license.  SQLite is public domain, not BSD (or ISC).
> Is it even possible for citizens of Latvia to release something to the
> public domain?  In any event, some paperwork would have to flow
> between us to make that happen.  Though, maybe that is not a huge
> concern as we are not nearly as persnickety with licensing for the
> documentation as we are with the code, and this tool would go in the
> separate documentation repository
> (https://www.sqlite.org/docsrc/timeline), not in the main code
> repository.
> 
> Would an even better solution be to enhance the capi3ref TCL script to
> spit out mdoc files in parallel to the HTML documentation it currently
> produces?

Richard,

As for public domain, I'm happy to put the sources under a similar
license.  I can't speak for the voodoo of the public domain and the EU
(or is it country-by-country?), however.

As for Tcl--I don't grok it myself or I'd have started modifying there.
 (I used capi3ref as a starting point.)  Be it C or Tcl, there's a lot
of string fiddling to get everything formatted for mdoc(7) (and to
collect refs), so it won't be pretty (or brief) in any language.

I'm happy maintaining the tool in or out of the tree--whatever's most
convenient.  I figured I'd just track changes to sqlite.h.in and update
the scanner if it was required.  (E.g. and in the most likely case: more
HTML tags.)  At the end of the day, I just want to use apropos and man
instead of the browser!

Best,

Kristaps

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 842 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] C API reference manpages

[Kristaps Dzonsons]

Hello,

I couldn't find any way to convert the SQLite docs (via sqlite.h) to
UNIX manpages, so I wrote a tool that does so:

 https://github.com/kristapsdz/sqlite2mdoc

This generates one manpage per API reference with the proper SEE ALSO
(from collected references) and IMPLEMENTATION NOTES (the raw
declarations and preprocessor statements).  Its deficiencies are in
handling HTML tables (prints verbatim--not bad except for sqlite3_open,
where the table is quite wide), the mdoc(7) isn't picture-perfect, and
in handling some un-closed markup seen here and there.

It follows the logic of the Tcl code in capi3ref[2], but does so in C
w/o dependencies unless running Linux.

Is there any interest in integrating this tool to have manpages in the
doc distribution without downstream bits?

I've been using this for a while as part of the manpage corpus on my
OpenBSD, Mac OS X, and Linux machines.

Best,

Kristaps

[1] http://man.openbsd.org/OpenBSD-current/man7/mdoc.7
[2] https://www.sqlite.org/cgi/docsrc/finfo?name=pages/capi3ref.in

[sqlite] C API reference manpages

[Richard Hipp]

On 3/31/16, Kristaps Dzonsons  wrote:
>
> Is there any interest in integrating this tool to have manpages in the
> doc distribution without downstream bits?
>

I think that would be cool.  Integrating your tool into the source
tree would mean that as the comment formats evolve, your tool would
evolve in lock-step.  If your tool is kept separate, then we might
change the documentation comment format without telling you, and break
things.

One issue is the license.  SQLite is public domain, not BSD (or ISC).
Is it even possible for citizens of Latvia to release something to the
public domain?  In any event, some paperwork would have to flow
between us to make that happen.  Though, maybe that is not a huge
concern as we are not nearly as persnickety with licensing for the
documentation as we are with the code, and this tool would go in the
separate documentation repository
(https://www.sqlite.org/docsrc/timeline), not in the main code
repository.

Would an even better solution be to enhance the capi3ref TCL script to
spit out mdoc files in parallel to the HTML documentation it currently
produces?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[R Smith]

That's not an SQLitespeed feature but indeed a backwards-compatible 
SQLite feature. (I had this wrong too at some point)

You probably already know, but to be clear: In SQL standard, 
double-quotes indicate identifiers and single quotes indicate string values.
While the single quotes are used more or less universally, identifiers 
often get quoted differently.
In Postgres and SQLite the standard is adhered to with Double quotes.
MySQL uses a back-tick like this ` (which, by the way, will also work in 
SQLite due the same backward compatibility)
MSSQL likes the square brackets [ and ] around identifiers (which, 
again, also works in SQLite for the same reason)

Further to this, in earlier MySQL and in SQLite you may also use double 
quotes to denote strings, and it will regard a double-quoted value to be 
a string if A - it isn't an identifier, or B - used in a place where you 
can't use an identifier. (You can just imagine the bugs in your SQL that 
can arise from this!)

While all of the above works, you are strongly encouraged to simply do 
it correctly and use double-quotes for identifiers and single quotes for 
strings.

We have been lobbying for a strict-mode in SQLite where none of these 
shenanigans are allowed - but that is far easier said than done.


On 2016/02/09 5:10 PM, Chris Prakoso wrote:
> Actually I've just done it now, in SQLiteSpeed, and it allowed me to use
> double-quote as delimiter successfully.
>
> Regards,
> Chris
>
> On Tue, Feb 9, 2016 at 3:03 PM, Simon Slavin  wrote:
>
>> On 9 Feb 2016, at 12:10pm, Chris Prakoso  wrote:
>>
>>> *insert into test (field1,field2) values (1,"two"),(2,"three")*
>> As well as the comments about your software being out of date, you need to
>> know that the text delimiter in SQLite is the non-directional single quote
>> character normally seen as an apostrophe.  You cannot successfully use the
>> double quote character or any directional quotes.
>>
>> Should be
>>
>>  insert into test (field1,field2) values (1,'two'),(2,'three')
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Chris Prakoso]

Yes. Nothing complicated, fortunately.

On Tue, Feb 9, 2016 at 4:09 PM, Steven M. McNeese <
steven.mcneese at freedomparkdfw.com> wrote:

> So are you saying you are just reading data from your SQLite db using C#
> and
> just need to insert using the SQLite command?
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Chris
> Prakoso
> Sent: Tuesday, February 9, 2016 7:56 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] C# + SQLite - Update/Insert using Transaction is
> slower than without.
>
> Steven,
>
> I don't use any back-end, the code I pasted here IS my back-end.  I opted
> for direct SQLite connection.  So I don't use EF6 nor Linq.
>
> Chris
>
> On Tue, Feb 9, 2016 at 1:47 PM, Steven M. McNeese <
> steven.mcneese at freedomparkdfw.com> wrote:
>
> > Chris,
> >
> > What are you using in c# for SQLite back end?  Ado.net? Linq.  Let me
> > know and I can help you with bulk inserts.
> >
> > Sent from my iPhone
> >
> > > On Feb 9, 2016, at 6:13 AM, Chris Prakoso  wrote:
> > >
> > > Ok. Got it.
> > > Now, if only I can get that multiple rows update working on my code,
> > > it would be perfect.
> > >
> > > Thanks a lot,
> > > Chris
> > >
> > >> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch
> > >> 
> > wrote:
> > >>
> > >> Chris Prakoso wrote:
> > >>>public bool UpdateData(string tableName,
> > >> Dictionary fields, List whereKeys)
> > >>>{
> > >>>...
> > >>>using (SQLiteTransaction transaction =
> > >> conn.BeginTransaction())
> > >>>{
> > >>>...
> > >>>rowsUpdated = cmd.ExecuteNonQuery();
> > >>>transaction.Commit();
> > >>
> > >> Using one transaction for each statement is slow.
> > >> (Re-opening the database doesn't help either.)
> > >>
> > >> You should use a single transaction around all update statements.
> > >>
> > >>
> > >> Regards,
> > >> Clemens
> > >> ___
> > >> sqlite-users mailing list
> > >> sqlite-users at mailinglists.sqlite.org
> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-user
> > >> s
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ---
> This email has been checked for viruses by Avast antivirus software.
> https://www.avast.com/antivirus
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Chris Prakoso]

Thanks for the detailed explanation.

Regards,
Chris

On Tue, Feb 9, 2016 at 4:05 PM, R Smith  wrote:

> That's not an SQLitespeed feature but indeed a backwards-compatible SQLite
> feature. (I had this wrong too at some point)
>
> You probably already know, but to be clear: In SQL standard, double-quotes
> indicate identifiers and single quotes indicate string values.
> While the single quotes are used more or less universally, identifiers
> often get quoted differently.
> In Postgres and SQLite the standard is adhered to with Double quotes.
> MySQL uses a back-tick like this ` (which, by the way, will also work in
> SQLite due the same backward compatibility)
> MSSQL likes the square brackets [ and ] around identifiers (which, again,
> also works in SQLite for the same reason)
>
> Further to this, in earlier MySQL and in SQLite you may also use double
> quotes to denote strings, and it will regard a double-quoted value to be a
> string if A - it isn't an identifier, or B - used in a place where you
> can't use an identifier. (You can just imagine the bugs in your SQL that
> can arise from this!)
>
> While all of the above works, you are strongly encouraged to simply do it
> correctly and use double-quotes for identifiers and single quotes for
> strings.
>
> We have been lobbying for a strict-mode in SQLite where none of these
> shenanigans are allowed - but that is far easier said than done.
>
>
>
> On 2016/02/09 5:10 PM, Chris Prakoso wrote:
>
>> Actually I've just done it now, in SQLiteSpeed, and it allowed me to use
>> double-quote as delimiter successfully.
>>
>> Regards,
>> Chris
>>
>> On Tue, Feb 9, 2016 at 3:03 PM, Simon Slavin 
>> wrote:
>>
>> On 9 Feb 2016, at 12:10pm, Chris Prakoso  wrote:
>>>
>>> *insert into test (field1,field2) values (1,"two"),(2,"three")*

>>> As well as the comments about your software being out of date, you need
>>> to
>>> know that the text delimiter in SQLite is the non-directional single
>>> quote
>>> character normally seen as an apostrophe.  You cannot successfully use
>>> the
>>> double quote character or any directional quotes.
>>>
>>> Should be
>>>
>>>  insert into test (field1,field2) values (1,'two'),(2,'three')
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Salih Yücel]

Hi,
I have windowns phone project but one problem . Sqli te select result data type 
text column turkish characters encoding problem
But Android operation system no problem turkish characters


Salih Y?cel
Mobil Grup Lideri /Mobile Group Leader

Univera Bilgisayar Sistemleri San. ve Tic. A.?.


Bu elektronik posta ve onunla iletilen b?t?n dosyalar sadece g?ndericisi 
taraf?ndan almas? ama?lanan yetkili ger?ek ya da t?zel ki?inin kullan?m? 
i?indir.E?er s?z konusu yetkili al?c? de?ilseniz bu elektronik postan?n 
i?eri?ini a??klaman?z, kopyalaman?z, y?nlendirmeniz ve kullanman?z kesinlikle 
yasakt?r ve bu elektronik postay? derhal silmeniz gerekmektedir. UN?VERA A.?. 
bu mesaj?n i?erdi?i bilgilerin do?rulu?u veya eksiksiz oldu?u konusunda 
herhangi bir garanti vermemektedir. Bu nedenle bu bilgilerin ne ?ekilde olursa 
olsun i?eri?inden, iletilmesinden, al?nmasndan ve saklanmas?ndan sorumlu 
de?ildir. Bu mesajdaki g?r??ler yalnzca g?nderen ki?iye aittir ve UN?VERA 
A.?.'nin g?r??lerini yans?tmayabilir. Bu e-posta bilinen b?t?n bilgisayar 
vir?slerine kar?? taranm??t?r.

This e-mail and any files transmitted with it are confidential and intended 
solely for the use of the individual or entity to whom they are addressed. If 
you are not the intended recipient you are hereby notified that any 
dissemination, forwarding, copying or use of any of the information is strictly 
prohibited, and the e-mail should immediately be deleted. UNIVERA A.S. makes no 
warranty as to the accuracy or completeness of any information contained in 
this message and hereby excludes any liability of any kind for the information 
contained therein or for the information transmission, reception, storage or 
use of such in any way whatsoever. The opinions expressed in this message 
belong to sender alone and may not necessarily reflect the opinions of UNIVERA 
A.S. This e-mail has been scanned for all known computer viruses.



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Chris Prakoso
Sent: Tuesday, February 9, 2016 5:09 PM
To: SQLite mailing list 
Subject: Re: [sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

Yes thank you.  My SQLite is the latest, it's the front-end that is outdated, 
which I have just swiftly corrected.

Regards,
Chris

On Tue, Feb 9, 2016 at 2:52 PM, Richard Hipp  wrote:

> On 2/9/16, Clemens Ladisch  wrote:
> > Chris Prakoso wrote:
> >> insert into test (field1,field2) values (1,"two"),(2,"three")
> >>
> >> SQL Error: near ",": syntax error
> >
> > You might want to update to a tool that is not years out of date.
> >
>
> What Clemens means by this is that prior to SQLite 3.7.11 (2012-03-20)
> the INSERT statement would only take a single row in the VALUES
> clause.  He is suggesting that you are using a version of SQLite that
> is 4 year old or older and therefore lacks this feature.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Clemens Ladisch]

Chris Prakoso wrote:
> insert into test (field1,field2) values (1,"two"),(2,"three")
>
> SQL Error: near ",": syntax error

You might want to update to a tool that is not years out of date.


Regards,
Clemens

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Chris Prakoso]

Ok. Thanks for the reminder.

Regards,
Chris

On Tue, Feb 9, 2016 at 3:18 PM, Richard Hipp  wrote:

> On 2/9/16, Chris Prakoso  wrote:
> > Actually I've just done it now, in SQLiteSpeed, and it allowed me to use
> > double-quote as delimiter successfully.
> >
>
> That is supported for backwards compatibility.  I originally put in
> support for double-quoted string literals to be compatible with MySQL
> 3.5.  I have long since regretted that decision.  You are encouraged
> to use single-quotes for string literals, as double-quotes have a very
> different meaning that can lead to subtle bugs.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Chris Prakoso]

Actually I've just done it now, in SQLiteSpeed, and it allowed me to use
double-quote as delimiter successfully.

Regards,
Chris

On Tue, Feb 9, 2016 at 3:03 PM, Simon Slavin  wrote:

>
> On 9 Feb 2016, at 12:10pm, Chris Prakoso  wrote:
>
> > *insert into test (field1,field2) values (1,"two"),(2,"three")*
>
> As well as the comments about your software being out of date, you need to
> know that the text delimiter in SQLite is the non-directional single quote
> character normally seen as an apostrophe.  You cannot successfully use the
> double quote character or any directional quotes.
>
> Should be
>
> insert into test (field1,field2) values (1,'two'),(2,'three')
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Chris Prakoso]

Yes thank you.  My SQLite is the latest, it's the front-end that is
outdated, which I have just swiftly corrected.

Regards,
Chris

On Tue, Feb 9, 2016 at 2:52 PM, Richard Hipp  wrote:

> On 2/9/16, Clemens Ladisch  wrote:
> > Chris Prakoso wrote:
> >> insert into test (field1,field2) values (1,"two"),(2,"three")
> >>
> >> SQL Error: near ",": syntax error
> >
> > You might want to update to a tool that is not years out of date.
> >
>
> What Clemens means by this is that prior to SQLite 3.7.11 (2012-03-20)
> the INSERT statement would only take a single row in the VALUES
> clause.  He is suggesting that you are using a version of SQLite that
> is 4 year old or older and therefore lacks this feature.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Simon Slavin]

On 9 Feb 2016, at 12:10pm, Chris Prakoso  wrote:

> *insert into test (field1,field2) values (1,"two"),(2,"three")*

As well as the comments about your software being out of date, you need to know 
that the text delimiter in SQLite is the non-directional single quote character 
normally seen as an apostrophe.  You cannot successfully use the double quote 
character or any directional quotes.

Should be

insert into test (field1,field2) values (1,'two'),(2,'three')

Simon.

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[R Smith]

On 2016/02/09 1:30 PM, Chris Prakoso wrote:
> Hi Clemens,
>
> Thanks for your reply.  I've tried to use raw SQL but it didn't work
> either.  Do you have any SQLite front-end that you use?

If I may suggest, try SQLitespeed (http://sqlc.rifin.co.za/) and add 
your DB file, open it and then use the buttons on the far right (such as 
"Insert", "Update", "Delete", etc.) to produce the SQL that will insert 
to or update the selected table. You can then see the correct syntax and 
expand on it to add your own values etc.

You could also right-click on the results list and select from the menu 
"Row Actions"-->"Show UPDATE SQL for changing this row" or "Show INSERT 
SQL for copying this row" etc.
There are a myriad more auto-SQL things in there, perhaps a good option 
if you are learning SQL still.

You could also right-click in a query and then select "Copy as 
Code"-->"Java"/"C#"/ whatever you like, or add your own codify settings 
for your preferred language to take the pain out of adapting queries to 
code.

The INSERT and UPDATE functions you described should really work in SQL, 
so perhaps it's a small syntax error or such preventing success.


(PS: This list is not a support group for SQLitespeed or any other gui, 
you may use its own bug/query reporter from the menu to get help there)

Cheers,
Ryan

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Chris Prakoso]

Steven,

I don't use any back-end, the code I pasted here IS my back-end.  I opted
for direct SQLite connection.  So I don't use EF6 nor Linq.

Chris

On Tue, Feb 9, 2016 at 1:47 PM, Steven M. McNeese <
steven.mcneese at freedomparkdfw.com> wrote:

> Chris,
>
> What are you using in c# for SQLite back end?  Ado.net? Linq.  Let me know
> and I can help you with bulk inserts.
>
> Sent from my iPhone
>
> > On Feb 9, 2016, at 6:13 AM, Chris Prakoso  wrote:
> >
> > Ok. Got it.
> > Now, if only I can get that multiple rows update working on my code, it
> > would be perfect.
> >
> > Thanks a lot,
> > Chris
> >
> >> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch 
> wrote:
> >>
> >> Chris Prakoso wrote:
> >>>public bool UpdateData(string tableName,
> >> Dictionary fields, List whereKeys)
> >>>{
> >>>...
> >>>using (SQLiteTransaction transaction =
> >> conn.BeginTransaction())
> >>>{
> >>>...
> >>>rowsUpdated = cmd.ExecuteNonQuery();
> >>>transaction.Commit();
> >>
> >> Using one transaction for each statement is slow.
> >> (Re-opening the database doesn't help either.)
> >>
> >> You should use a single transaction around all update statements.
> >>
> >>
> >> Regards,
> >> Clemens
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Chris Prakoso]

Thanks Ryan,

I will definitely try it.  I'm ok with raw SQL, just not familiar with the
odd ones like this multiple rows update.

Thanks a lot,
Chris

On Tue, Feb 9, 2016 at 12:42 PM, R Smith  wrote:

>
>
> On 2016/02/09 1:30 PM, Chris Prakoso wrote:
>
>> Hi Clemens,
>>
>> Thanks for your reply.  I've tried to use raw SQL but it didn't work
>> either.  Do you have any SQLite front-end that you use?
>>
>
> If I may suggest, try SQLitespeed (http://sqlc.rifin.co.za/) and add your
> DB file, open it and then use the buttons on the far right (such as
> "Insert", "Update", "Delete", etc.) to produce the SQL that will insert to
> or update the selected table. You can then see the correct syntax and
> expand on it to add your own values etc.
>
> You could also right-click on the results list and select from the menu
> "Row Actions"-->"Show UPDATE SQL for changing this row" or "Show INSERT SQL
> for copying this row" etc.
> There are a myriad more auto-SQL things in there, perhaps a good option if
> you are learning SQL still.
>
> You could also right-click in a query and then select "Copy as
> Code"-->"Java"/"C#"/ whatever you like, or add your own codify settings for
> your preferred language to take the pain out of adapting queries to code.
>
> The INSERT and UPDATE functions you described should really work in SQL,
> so perhaps it's a small syntax error or such preventing success.
>
>
> (PS: This list is not a support group for SQLitespeed or any other gui,
> you may use its own bug/query reporter from the menu to get help there)
>
> Cheers,
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Clemens Ladisch]

Chris Prakoso wrote:
> public bool UpdateData(string tableName, Dictionary 
> fields, List whereKeys)
> {
> ...
> using (SQLiteTransaction transaction = 
> conn.BeginTransaction())
> {
> ...
> rowsUpdated = cmd.ExecuteNonQuery();
> transaction.Commit();

Using one transaction for each statement is slow.
(Re-opening the database doesn't help either.)

You should use a single transaction around all update statements.


Regards,
Clemens

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Chris Prakoso]

Ok. Got it.
Now, if only I can get that multiple rows update working on my code, it
would be perfect.

Thanks a lot,
Chris

On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch  wrote:

> Chris Prakoso wrote:
> > public bool UpdateData(string tableName,
> Dictionary fields, List whereKeys)
> > {
> > ...
> > using (SQLiteTransaction transaction =
> conn.BeginTransaction())
> > {
> > ...
> > rowsUpdated = cmd.ExecuteNonQuery();
> > transaction.Commit();
>
> Using one transaction for each statement is slow.
> (Re-opening the database doesn't help either.)
>
> You should use a single transaction around all update statements.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Clemens Ladisch]

Chris Prakoso wrote:
> I've been testing the performance of my Insert/Update using
> Transaction and without, and I found that it is quicker when I don't
> use it.

Show the code.


Regards,
Clemens

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Clemens Ladisch]

Chris Prakoso wrote:
> My question is whether anybody had successfully implemented multiple
> rows Insert/Update.

This is possible in SQL:

INSERT INTO MyTable(ID, Value)
VALUES (1, 'hello'),
   (2, 'world');

UPDATE MyTable
SET Value = 'the same value'
WHERE ID IN (1, 2);

-- rather verbose; better use two simple UPDATEs:
UPDATE MyTable
SET Value = CASE ID
WHEN 1 THEN 'new A'
WHEN 2 THEN 'new B'
END
WHERE ID IN (1, 2);


Regards,
Clemens

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Chris Prakoso]

I did a test using simple table, and entering the sql directly using SQLite
Administrator:

*insert into test (field1,field2) values (1,"two"),(2,"three")*


The error I got from the SQLite Administrator is:

*2/9/2016 11:29:40 AM: SQL Error: near ",": syntax error  *

Thanks,
Chris


On Tue, Feb 9, 2016 at 11:55 AM, Richard Hipp  wrote:

> On 2/9/16, Chris Prakoso  wrote:
> > Hi Clemens,
> >
> > Thanks for your reply.  I've tried to use raw SQL but it didn't work
> > either.
>
> Please show us the SQL that you did you.
>
> > Do you have any SQLite front-end that you use?
> >
>
> The only supported "front-end" (if I correctly understand your
> meaning) is the command-line tool "sqlite3.exe" available from the
> https://www.sqlite.org/download.html page.  There are many other
> third-party tools, most of which are GUI-based.  You can use them if
> you want, and they usually work quite well, but occasionally have
> bugs.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Chris Prakoso]

Here they are:

public bool UpdateData(string tableName, Dictionary
fields, List whereKeys)
{
bool result = false;
string sql = "";
List fieldList = new List();
List whereKeyList = new List();
int rowsUpdated = 0;

using (SQLiteConnection conn = new
SQLiteConnection(this.ConnectionString))
{
try
{
conn.Open();

SQLiteCommand cmd = new SQLiteCommand(conn);

using (SQLiteTransaction transaction =
conn.BeginTransaction())
{

// Build a list of fields need to be updated
if (fields.Count > 0)
{
foreach (KeyValuePair kvp in
fields)
{
cmd.Parameters.AddWithValue(kvp.Key,
kvp.Value);
fieldList.Add(kvp.Key);
}
}

sql = "update " + tableName + " set " +
this.BuildUpdateFieldList(fieldList) +
" where " + this.BuildWhereClause(whereKeys);

cmd.CommandText = sql;

rowsUpdated = cmd.ExecuteNonQuery();

transaction.Commit();
}


if (rowsUpdated > 0)
result = true;

}
catch (System.Exception ex)
{
this.UpdateStatusMessage(ex.Message);
}
}

return result;
}

On Tue, Feb 9, 2016 at 11:13 AM, Clemens Ladisch  wrote:

> Chris Prakoso wrote:
> > I've been testing the performance of my Insert/Update using
> > Transaction and without, and I found that it is quicker when I don't
> > use it.
>
> Show the code.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Chris Prakoso]

Hi Clemens,

Thanks for your reply.  I've tried to use raw SQL but it didn't work
either.  Do you have any SQLite front-end that you use?


Regards,
Chris

On Tue, Feb 9, 2016 at 11:12 AM, Clemens Ladisch  wrote:
> Chris Prakoso wrote:
>> My question is whether anybody had successfully implemented multiple
>> rows Insert/Update.
>
> This is possible in SQL:
>
> INSERT INTO MyTable(ID, Value)
> VALUES (1, 'hello'),
>(2, 'world');
>
> UPDATE MyTable
> SET Value = 'the same value'
> WHERE ID IN (1, 2);
>
> -- rather verbose; better use two simple UPDATEs:
> UPDATE MyTable
> SET Value = CASE ID
> WHEN 1 THEN 'new A'
> WHEN 2 THEN 'new B'
> END
> WHERE ID IN (1, 2);
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Chris Prakoso]

Hi all,

I've been testing the performance of my Insert/Update using
Transaction and without, and I found that it is quicker when I don't
use it.

Anybody has an insight on this?

Thanks a lot,
Chris

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Chris Prakoso]

Hi all,

I just joined the Mailing List yesterday, so apologise for any mistake
I am doing.

I'm a .NET (C#) Developer, and at the moment I'm coding a small app
with SQLite as the backend database.

My question is whether anybody had successfully implemented multiple
rows Insert/Update.

I've tried to use INSERT OR REPLACE and passing multiple VALUES, but
it didn't seem to work.

Any pointers would be appreciated.


Thanks very much,
Chris

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Richard Hipp]

On 2/9/16, Chris Prakoso  wrote:
> Actually I've just done it now, in SQLiteSpeed, and it allowed me to use
> double-quote as delimiter successfully.
>

That is supported for backwards compatibility.  I originally put in
support for double-quoted string literals to be compatible with MySQL
3.5.  I have long since regretted that decision.  You are encouraged
to use single-quotes for string literals, as double-quotes have a very
different meaning that can lead to subtle bugs.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Steven M. McNeese]

So are you saying you are just reading data from your SQLite db using C# and
just need to insert using the SQLite command?  

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Chris
Prakoso
Sent: Tuesday, February 9, 2016 7:56 AM
To: SQLite mailing list
Subject: Re: [sqlite] C# + SQLite - Update/Insert using Transaction is
slower than without.

Steven,

I don't use any back-end, the code I pasted here IS my back-end.  I opted
for direct SQLite connection.  So I don't use EF6 nor Linq.

Chris

On Tue, Feb 9, 2016 at 1:47 PM, Steven M. McNeese <
steven.mcneese at freedomparkdfw.com> wrote:

> Chris,
>
> What are you using in c# for SQLite back end?  Ado.net? Linq.  Let me 
> know and I can help you with bulk inserts.
>
> Sent from my iPhone
>
> > On Feb 9, 2016, at 6:13 AM, Chris Prakoso  wrote:
> >
> > Ok. Got it.
> > Now, if only I can get that multiple rows update working on my code, 
> > it would be perfect.
> >
> > Thanks a lot,
> > Chris
> >
> >> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch 
> >> 
> wrote:
> >>
> >> Chris Prakoso wrote:
> >>>public bool UpdateData(string tableName,
> >> Dictionary fields, List whereKeys)
> >>>{
> >>>...
> >>>using (SQLiteTransaction transaction =
> >> conn.BeginTransaction())
> >>>{
> >>>...
> >>>rowsUpdated = cmd.ExecuteNonQuery();
> >>>transaction.Commit();
> >>
> >> Using one transaction for each statement is slow.
> >> (Re-opening the database doesn't help either.)
> >>
> >> You should use a single transaction around all update statements.
> >>
> >>
> >> Regards,
> >> Clemens
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-user
> >> s
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Richard Hipp]

On 2/9/16, Clemens Ladisch  wrote:
> Chris Prakoso wrote:
>> insert into test (field1,field2) values (1,"two"),(2,"three")
>>
>> SQL Error: near ",": syntax error
>
> You might want to update to a tool that is not years out of date.
>

What Clemens means by this is that prior to SQLite 3.7.11 (2012-03-20)
the INSERT statement would only take a single row in the VALUES
clause.  He is suggesting that you are using a version of SQLite that
is 4 year old or older and therefore lacks this feature.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Steven M. McNeese]

Chris,

What are you using in c# for SQLite back end?  Ado.net? Linq.  Let me know and 
I can help you with bulk inserts. 

Sent from my iPhone

> On Feb 9, 2016, at 6:13 AM, Chris Prakoso  wrote:
> 
> Ok. Got it.
> Now, if only I can get that multiple rows update working on my code, it
> would be perfect.
> 
> Thanks a lot,
> Chris
> 
>> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch  
>> wrote:
>> 
>> Chris Prakoso wrote:
>>>public bool UpdateData(string tableName,
>> Dictionary fields, List whereKeys)
>>>{
>>>...
>>>using (SQLiteTransaction transaction =
>> conn.BeginTransaction())
>>>{
>>>...
>>>rowsUpdated = cmd.ExecuteNonQuery();
>>>transaction.Commit();
>> 
>> Using one transaction for each statement is slow.
>> (Re-opening the database doesn't help either.)
>> 
>> You should use a single transaction around all update statements.
>> 
>> 
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Richard Hipp]

On 2/9/16, Chris Prakoso  wrote:
> Hi Clemens,
>
> Thanks for your reply.  I've tried to use raw SQL but it didn't work
> either.

Please show us the SQL that you did you.

> Do you have any SQLite front-end that you use?
>

The only supported "front-end" (if I correctly understand your
meaning) is the command-line tool "sqlite3.exe" available from the
https://www.sqlite.org/download.html page.  There are many other
third-party tools, most of which are GUI-based.  You can use them if
you want, and they usually work quite well, but occasionally have
bugs.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] C# Microsoft.data.sqlite SqliteCommand::ExecuteReader encounters a doubly freed pointer after 935 invocations

[Frank Chang]

We encountered  a doubly freed "C" pointer after calling sqlite3_exec
in my custom version of the Microsoft.Data.Sqlite C# method
SqliteCommand::ExecuteAnReader which does not call the parent C# class
method DbCommand::ExecuteReader which does not use a C# NativeMethod. The
C# NativeMethod class has a method for handling garbage collection which I
bypasses by calling public virtual void
SqliteCommand::ExecuteAnReader().
After using sqlite3_exec to insert 935 rows into the Sqlite3 Vendors
table, we got the following error message:  *** Error in
`/opt/monodevelop/bin/mono': free(): invalid pointer: 0x07b2e018 ***
Stacktrace:

  at  <0x>
  at (wrapper managed-to-native) object.__icall_wrapper_mono_marshal_free
(intptr) 
  at (wrapper managed-to-native)
Microsoft.Data.Sqlite.SqliteCommand.sqlite3_exec
(Microsoft.Data.Sqlite.Interop.Sqlite3Handle,string,Microsoft.Data.Sqlite.SqliteCommand/FooCallbackType,intptr,string&)

  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteAnReader () 
  at XMLConverter.Program.InsertTable (string,string,int) 
  at XMLConverter.Program.Main (string[]) 
  at (wrapper runtime-invoke) .runtime_invoke_void_object
(object,intptr,intptr,intptr)  How might we workaround
this problem in C# or alternatively should we investigate the C language
function how to add additional C language code around sqlite3_exec()? Any
help is greatlly appreciated.

[sqlite] SQLite C# Windows Store Apps

[Martin Křížek]

Hi,
can any one write some suggestion (I mean nuget package(s)) for creating
Windows Store App in C# with SQLite? In desktop apps I use simply
"System.Data.SQLite (x86/x64)" which I love because it allows me to write
SQL statements directly. Are there any good (ideally with examples or
documentation) packages that really works with Windows Store Apps posted to
Store?
Thanks in advance.

Best regards

Martin Krizek

[sqlite] what is typical pattern for test double of sqlite c api

[Adam Devita]

Good day,

I'm about to implement TDD for an existing c project that uses sqlite,
using CPPUnit.  Sqlite would be a dependency from the point of view of
the routines making calls to it.

Is is typical to just write a link time stub to substitute commonly
used parts of the interface (exec, open, prepare, bind, step, reset,
finalize, close)  or is it easy to do something in the spirit of
1) include sqlite3Ext.h  in the TDD sources
2) #define SQLITE_CORE
3) and use run time substitution on the sqlite3_api_routines to point
at my test doubles?

regards,
Adam D.





-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] C++ ORM

[Alejandro Santos]

On Mon, Mar 9, 2015 at 10:38 PM, Scott Robison  
wrote:
> A co-worker who is working on a project is interested in finding out if
> there is an effective ORM for C++ / SQLite. I've not used one so I'm
> turning to the list to see if anyone has a recommendation.
>
> Note: He's okay using SQLite more or less directly if he needs to, as he
> recognizes the lack of reflection in C++ might lead to a less manageable
> ORM. Still, never hurts to ask.
>

I've been using SQLite from my C++ project with the sqlite3pp library.
It is not an ORM and while it works OK for my use case, the
transaction managements objects needs some work. I have only one
transacion in my application so this is not an issue for me.

https://github.com/iwongu/sqlite3pp


-- 
Alejandro Santos

[sqlite] C++ ORM

[Darren Spruell]

On Mon, Mar 9, 2015 at 3:25 PM, Simon Slavin  wrote:
>
> On 9 Mar 2015, at 9:38pm, Scott Robison  wrote:
>
>> A co-worker who is working on a project is interested in finding out if
>> there is an effective ORM for C++ / SQLite. I've not used one so I'm
>> turning to the list to see if anyone has a recommendation.
>
> For those playing along at home, ORM == Object Relational Mapping.  In other 
> words you do Object-oriented programming in C++ and the objects are stored in 
> a SQLite database.
>
> I'm not aware that this problem has been solved well in any language or with 
> any database engine.  It might seem like a terrific oppotunity to write a 
> demonstration library, but differences in how languages do OO and how 
> databases store data seem to make this a difficult problem to crack.  I'd be 
> interested in any solutions that use SQLite with any popular OO language.

You did qualify it with "solved well", so I can only comment that
depending on your requirements various ORMs may work well enough for
the job. I've done projects in Python with Django's ORM (MVC web
apps), SQLAlchemy, and am finding peewee [1] to work well for a small
project I'm working on right now.

Most OO language ORMs work about like this:

Model Definition:

Model classes, fields and model instances all map to database concepts:

Thing :: Corresponds to...
Model class :: Database table
Field instance :: Column on a table
Model instance :: Row in a database table

All that said, I'm not familiar with C++ options myself.

[1] http://peewee.readthedocs.org/en/latest/index.html

-- 
Darren Spruell
phatbuckett at gmail.com

[sqlite] C++ ORM

[Boris Kolpackov]

Hi Scott,

Scott Robison writes:

> A co-worker who is working on a project is interested in finding out if
> there is an effective ORM for C++ / SQLite. I've not used one so I'm
> turning to the list to see if anyone has a recommendation.

Check out ODB:

http://codesynthesis.com/products/odb/

Quite a few people are using it with SQLite. Since I am involved with
the project, I am not going to tell you how wonderful it is but rather
leave you to decide for yourself.


> Note: He's okay using SQLite more or less directly if he needs to, as he
> recognizes the lack of reflection in C++ might lead to a less manageable
> ORM.

Who needs reflection when you can have a C++ compiler (frontend)? ;-)

Boris

[sqlite] C++ ORM

[QxOrm contact]

Hello,

I'm the main developer of QxOrm library and QxEntityEditor application :
http://www.qxorm.com/

Quickly : QxOrm library provides ORM feature to C++/Qt developers, and
QxEntityEditor provides a graphic way to design and manage the data model.
FYI, QxOrm provides also a set of other features like serialization,
introspection, client-server module, etc...
More details on the QxOrm and QxEntityEditor website : http://www.qxorm.com/

2015-03-10 1:25 GMT+01:00 Darren Duncan :

> On 2015-03-09 3:25 PM, Simon Slavin wrote:
>
>> On 9 Mar 2015, at 9:38pm, Scott Robison  wrote:
>>
>>  A co-worker who is working on a project is interested in finding out if
>>> there is an effective ORM for C++ / SQLite. I've not used one so I'm
>>> turning to the list to see if anyone has a recommendation.
>>>
>>
>> For those playing along at home, ORM == Object Relational Mapping.  In
>> other words you do Object-oriented programming in C++ and the objects are
>> stored in a SQLite database.
>>
>> I'm not aware that this problem has been solved well in any language or
>> with any database engine.  It might seem like a terrific oppotunity to
>> write a demonstration library, but differences in how languages do OO and
>> how databases store data seem to make this a difficult problem to crack.
>> I'd be interested in any solutions that use SQLite with any popular OO
>> language.
>>
>
> I am developing a project right now that aims to out-do ORM at its own
> game, by changing the paradigm so the relational database and application
> programming realms are unified into one environment, where relations and
> tuples are first-class types you can use in applications, and the same
> arbitrary user-defined types you can use in applications can be used
> directly in databases, no "mapping" required.  I hope to have a first
> version executing in about 2 months. -- Darren Duncan
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C++ ORM

[Simon Slavin]

On 9 Mar 2015, at 9:38pm, Scott Robison  wrote:

> A co-worker who is working on a project is interested in finding out if
> there is an effective ORM for C++ / SQLite. I've not used one so I'm
> turning to the list to see if anyone has a recommendation.

For those playing along at home, ORM == Object Relational Mapping.  In other 
words you do Object-oriented programming in C++ and the objects are stored in a 
SQLite database.

I'm not aware that this problem has been solved well in any language or with 
any database engine.  It might seem like a terrific oppotunity to write a 
demonstration library, but differences in how languages do OO and how databases 
store data seem to make this a difficult problem to crack.  I'd be interested 
in any solutions that use SQLite with any popular OO language.

Simon.

[sqlite] C++ ORM

[Darren Duncan]

On 2015-03-09 3:25 PM, Simon Slavin wrote:
> On 9 Mar 2015, at 9:38pm, Scott Robison  wrote:
>
>> A co-worker who is working on a project is interested in finding out if
>> there is an effective ORM for C++ / SQLite. I've not used one so I'm
>> turning to the list to see if anyone has a recommendation.
>
> For those playing along at home, ORM == Object Relational Mapping.  In other 
> words you do Object-oriented programming in C++ and the objects are stored in 
> a SQLite database.
>
> I'm not aware that this problem has been solved well in any language or with 
> any database engine.  It might seem like a terrific oppotunity to write a 
> demonstration library, but differences in how languages do OO and how 
> databases store data seem to make this a difficult problem to crack.  I'd be 
> interested in any solutions that use SQLite with any popular OO language.

I am developing a project right now that aims to out-do ORM at its own game, by 
changing the paradigm so the relational database and application programming 
realms are unified into one environment, where relations and tuples are 
first-class types you can use in applications, and the same arbitrary 
user-defined types you can use in applications can be used directly in 
databases, no "mapping" required.  I hope to have a first version executing in 
about 2 months. -- Darren Duncan

[sqlite] C++ ORM

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/2015 02:38 PM, Scott Robison wrote:
> Note: He's okay using SQLite more or less directly if he needs to,
> as he recognizes the lack of reflection in C++ might lead to a less
> manageable ORM. Still, never hurts to ask.

Don't use an ORM unless the ongoing benefits outweigh the drawbacks.

http://blogs.tedneward.com/2006/06/26/The%2BVietnam%2BOf%2BComputer%2BScience.aspx

http://stackoverflow.com/questions/404083/is-orm-still-the-vietnam-of-computer-science

A nice quote from the latter:

  Object-oriented is still object-oriented and Relational is
  still Set-oriented.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlT+NmIACgkQmOOfHg372QQ7CQCgqZcwZ0xQK0fJoG39t6OKDHBF
oIEAoInFomPfmw77XSMbNWWZndb9IKQc
=u2/6
-END PGP SIGNATURE-

[sqlite] C++ ORM

[Scott Robison]

A co-worker who is working on a project is interested in finding out if
there is an effective ORM for C++ / SQLite. I've not used one so I'm
turning to the list to see if anyone has a recommendation.

Note: He's okay using SQLite more or less directly if he needs to, as he
recognizes the lack of reflection in C++ might lead to a less manageable
ORM. Still, never hurts to ask.

-- 
Scott Robison