[sqlite] Appropriate Uses For SQLite

[Adam Kennedy]

But the great thing about SQLite is you don't have to go logical form in
light weight apps.

http://search.cpan.org/dist/ORLite/lib/ORLite.pm

ORLite does a half-and-half approach that generates the easy parts of the
SQL with very little code, but avoids the code weight needed to generate
all of it.

my @users = DB::User->select( 'where name = ? order by name', $name );

It's great to have the flexibility to go all logical form, partly, or none,
as best suits the nature of the database you are embedding in your larger
application.

Adam

On 18 February 2015 at 15:44, Darko Volaric  wrote:

> Right now, roughly speaking, I'm doing:  logical form -> SQL -> execution
> of logical form, and SQL seems to me to just be an arbitrary hoop that I
> have to jump through, complicating things along the way.
>

[sqlite] DBD::SQLite 1.35, and speeding up databases created before 3.7.8

[Adam Kennedy]

Firstly, I have just released a new Perl DBD::SQLite module, which
fixes a few perl-side bugs and moves us from 3.7.7 to 3.7.9.

Secondly, for a collection of arbitrary SQLite database files with
unknown schemas that were created before 3.7.8, is it possible to do
bulk-processing of the databases to gain the speed improvements of the
new merge-sort indexing in 3.7.8?

My reading of the release notes is that connecting to each database
file and issuing REINDEX might be enough, plus maybe a VACUUM and
ANALYZE afterwards for good measure.

But I can't see anything saying explicitly this is enough, or if I
need to drop all of the indexes entirely and create them again.

The latter is obviously a more problematic situation when the schemas
are arbitrary.

Thanks for your advice

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

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

[Adam Kennedy]

Foreign key information is highly useful in many different ORM systems
in many different languages.

My personal example, the "An ORM for any SQLite database in one line
of code" ORLite module in Perl.

http://search.cpan.org/perldoc?ORLite

It is expected to generate entire class trees for any arbitrary
database unassisted with just

use ORLite 'mydatabase.sqlite';

It uses introspection of the foreign key information to generate code
which allows something like the following to return a different object
instead of just the id.

$child->father

Right now I'm doing some extremely fragile SQL parsing but was about
to switch over to the more reliable pragma. I'd dearly love to
continue to be able to.

That said, I don't specifically need it to be a pragma, just as long
as there's some place to get the information instead of writing my own
SQL parser. Somewhere in a sqlite__columns table would be totally fine
too.

In summary, the enforcement of foreign key constraints is not the only
use case for the pragma. While the enforcement use case is now
removed, the rest still remain.

Adam K

On 9 December 2010 06:44, Richard Hipp  wrote:
> On Wed, Dec 8, 2010 at 2:35 PM, Petite Abeille 
> wrote:
>
>> Hello,
>>
>> The pragma foreign_key_list appears to be deprecated in 3.7.4:
>>
>> http://www.sqlite.org/pragma.html#pragma_foreign_key_list
>>
>> Any reason for such deprecation?
>>
>
> Now that foreign key constraints are enforced natively, why would you want
> to have a list of them?  Why should the foreign_key_list pragma continue to
> consume code space and developer maintenance time?
>
>
>
>>
>> What's the alternative to that pragma to achieve the same effect?
>>
>> Thanks in advance.
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [DBD-SQLite] Re: SQLite bug ticket - build fails on sun4-solaris-64int 2.10

[Adam Kennedy]

Apologies, that should be...

http://svn.ali.as/cpan/trunk/DBD-SQLite/Makefile.PL

2010/1/3 Adam Kennedy <adamkennedybac...@gmail.com>:
> The build code we're using is run across all operating systems.
>
> You can see the actual build script here.
>
> http://ali.as/cpan/trunk/DBD-SQLite/Makefile.PL
>
> Unfortunately, we neither have the ability to run configure (as we
> don't have reliable access to /bin/sh or any of the other stuff it
> needs) or the ability to use a pregenerated static configuration
> across all platforms.
>
> But your other comments are welcome.
>
> I've removed the useless pointer size flag, and commented out the core
> flag until we can determine why it was there in the first place
> (there's a new team looking after DBD::SQLite since about March last
> year, and some stuff is definitely grandfathered in from long ago).
>
> Adam K
>
>>> The other flags seem to be guessed.  There is no need to tell a 64 bit
>>> system that file offets are 64 bits.  The only 'have' is HAVE_USLEEP but
>>> the system likely has LOCALTIME_R and GMTIME_R too as well as several
>>> other header files.
>>>
>>> If you do not want to build SQLite using its build system then the
>>> approach I take is to run SQLite's configure, grab the DEFS = line out
>>> of the resulting Makefile and generate a .h file with the relevant -D
>>> turned into #defines.  If you define _HAVE_SQLITE_CONFIG_H then SQLite
>>> will #include any config.h so you can dump your #defines in there.
>>>
>>> Roger
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [DBD-SQLite] Re: SQLite bug ticket - build fails on sun4-solaris-64int 2.10

[Adam Kennedy]

The build code we're using is run across all operating systems.

You can see the actual build script here.

http://ali.as/cpan/trunk/DBD-SQLite/Makefile.PL

Unfortunately, we neither have the ability to run configure (as we
don't have reliable access to /bin/sh or any of the other stuff it
needs) or the ability to use a pregenerated static configuration
across all platforms.

But your other comments are welcome.

I've removed the useless pointer size flag, and commented out the core
flag until we can determine why it was there in the first place
(there's a new team looking after DBD::SQLite since about March last
year, and some stuff is definitely grandfathered in from long ago).

Adam K

>> The other flags seem to be guessed.  There is no need to tell a 64 bit
>> system that file offets are 64 bits.  The only 'have' is HAVE_USLEEP but
>> the system likely has LOCALTIME_R and GMTIME_R too as well as several
>> other header files.
>>
>> If you do not want to build SQLite using its build system then the
>> approach I take is to run SQLite's configure, grab the DEFS = line out
>> of the resulting Makefile and generate a .h file with the relevant -D
>> turned into #defines.  If you define _HAVE_SQLITE_CONFIG_H then SQLite
>> will #include any config.h so you can dump your #defines in there.
>>
>> Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [DBD-SQLite] Re: SQLite bug ticket - build fails on sun4-solaris-64int 2.10

[Adam Kennedy]

Thanks for the comments on the build flags, we will investigate.

The threadsafe disabling may be a result of DBD::SQLite being itself
compiled on a Perl that has threading compiled out (which adds about
10-20% overall speed and is used in certain situations).

Adam K
DBD::SQLite Release Manager

2010/1/2 Darren Duncan :
> Roger, thank you for this quick response, and for your tips.  So from the
> looks of what you're saying, the problem is actually related to something
> that the DBD::SQLite build process is doing inadequately, so now I suspect
> the ball will go back there to be dealt with.  Thank you. -- Darren Duncan
>
> Roger Binns wrote [to sqlite-us...@sqlite.org]:
>>
>> Darren Duncan wrote:
>>>
>>> I would like to bring an apparent SQLite bug to the attention of the
>>> SQLite core developers as a ticket, where build fails on sun4-solaris-64int
>>> 2.10
>>
>> You'll find this is not a bug in SQLite.
>>
>>> cc: Fatal error in /opt/sunstudio12.1/prod/bin/cg
>>> cc: Status 139
>>> *** Error code 139
>>
>> That is the compiler crashing (signal 11, SIGSEGV).  This sort of thing
>> usually turns out to be an optimiser bug and likely won't happen if you
>> disable optimisation, or compile the files individually rather than
>> using the amalgamation.  Alternatively use a working compiler like gcc.
>>
>> Incidentally three of your defines are dodgy:
>>
>> -DSQLITE_CORE
>>
>> There is never any need to specify this - all that stuff is handled
>> internally.
>>
>> -DSQLITE_PTR_SZ=4
>>
>> That name is not used anywhere in the SQLite source I could find.  Even
>> if it was, implying 4 byte pointers on a 64 bit machine seems dangerous.
>>
>> -DTHREADSAFE=0
>>
>> Really?  What is wrong (and less likely to cause the unwary grief) than
>> the default of 1?
>>
>> The other flags seem to be guessed.  There is no need to tell a 64 bit
>> system that file offets are 64 bits.  The only 'have' is HAVE_USLEEP but
>> the system likely has LOCALTIME_R and GMTIME_R too as well as several
>> other header files.
>>
>> If you do not want to build SQLite using its build system then the
>> approach I take is to run SQLite's configure, grab the DEFS = line out
>> of the resulting Makefile and generate a .h file with the relevant -D
>> turned into #defines.  If you define _HAVE_SQLITE_CONFIG_H then SQLite
>> will #include any config.h so you can dump your #defines in there.
>>
>> Roger
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> ___
> DBD-SQLite mailing list
> dbd-sql...@lists.scsys.co.uk
> http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [DBD-SQLite] test DBD::SQLite 1.26_05 - foreign keys!

[Adam Kennedy]

Please note the following correction to the announcement.

Whining is also welcome. :)

Adam K

2009/10/15 Darren Duncan :
> Patches welcome.  Ideas welcome.  Testing welcome.  Whining to /dev/null.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users