SQLite and singular data



  • Let's say we are storing a table of scores on a test of some group of students. StudentID, Score, simple table.

    In the same SQLite database file I'd like to store a single property of the whole database, like the year when the test took place or a description of what student group was that.

    My first idea is to add a table "properties" or so with key/value columns. But maybe there's a better way in SQLite?

    TIA



  • Surely the year of the test is not a database property, rather a property of the test.  Unless you have one database per test (a real WTF) then your table needs a FK to a TEST table containing the description, date etc.  In the nicest possible sense, it sounds like you're a few tables short of a schema :)

    But to answer your question, many databases have the kind of table you describe for various configuration items etc.  I prefer one column per item so you get strong typing but others prefer key/value.  Some XML fetishists prefer using an external file for that stuff - it all depends.



  • @LoztInSpace said:

    Unless you have one database per test (a real WTF)
     

    In fact, there is one database per test, because it's not a database-driven webapp, but a desktop application which uses SQLite to encode files manipulated by user in a nice database format.

    Thanks for the on-topic reply :) I guess I'd stick to key/value table then, unless someone points out something better.



  •  I think I'd just use a spreadsheet. (Waits for hate mail...)



  • @dstozek said:

    In fact, there is one database per test, because it's not a database-driven webapp, but a desktop application which uses SQLite to encode files manipulated by user in a nice database format.
     

    Sounds like it shouldn't make a difference.

    Do you perform multiple tests in time with 1 installation of the app? In that case (test = DB) => wtf.

    If the installation and usage of the app are a one-time affair, then it's ok.



  • @LoztInSpace said:

    I prefer one column per item so you get strong typing but others prefer
    key/value.

    And SQLite uses dynamic typing anyway, so key/value is more fitting.



  • @dstozek said:

    @LoztInSpace said:

    Unless you have one database per test (a real WTF)
     

    In fact, there is one database per test, because it's not a database-driven webapp, but a desktop application which uses SQLite to encode files manipulated by user in a nice database format.

    I think you are perilously close to a WTF.  If it really is that simple I agree with the poster who suggested a spreadsheet.  Users 'ge't spreadsheets. Let them work stuff out for themselves and don't complicate things with a half baked 'database' solution.

    As part of my new years resolution is to avoid spending time/energy on things that I cannot control, I merely wish you good luck in whatever it is you are trying to do.

    Happy new year.



  • My desktop app operates on test results (up to several thousand in one file) as documents. Users open, save and create tables with test results. I am using SQLite as a format to save those test results. Therefore, 1 file = 1 database = 1 table with student scores. I could've used XML, CSV, spreadsheets or whatnot, but I thought using a light SQL engine would be the best solution. I try not to think about SQLite as a database solution, but a data storage method a little better than spreadsheets.

     Would you explain to me why it's near a wtf? Either me or you are certainly missing something.



  • The WTF lies here, although it's just a symptom, and if you've used DBs for (larger) apps before, the following certainly is a huge wtf:

    @dstozek said:

    1 file = 1 database = 1 table

    If more than one test is managed with the same app installation, you would do well to have several tables listing tests and their results.

    Same if multiple users are going to use the same app installation on the same machine -- you'll probably need a proper method  to separate out the data, which adds  a table of users.

    1 app = 1 DB is good, though.

    A relational schema in SQLite would be the equivalent to your alternative of several XML/CSV/etc files. Possibly a superior equivalent, but that depends on the application and the usage of the files afterwords.



  • @dstozek said:

    My desktop app operates on test results (up to several thousand in one file) as documents. Users open, save and create tables with test results. I am using SQLite as a format to save those test results. Therefore, 1 file = 1 database = 1 table with student scores. I could've used XML, CSV, spreadsheets or whatnot, but I thought using a light SQL engine would be the best solution. I try not to think about SQLite as a database solution, but a data storage method a little better than spreadsheets.

     Would you explain to me why it's near a wtf? Either me or you are certainly missing something.

    XML would have been easier...

    <Test Name="Test ..." Date="..." ...>
      <Result Student="ID1" Score="0" />
      ...
    </Test>
    


  • There can't be more than one test/table in one file, because the user directly operates on the files (like sending it by email or copying to a pendrive, or anything). Sure, if this was a webapp or an internal storage, then obviously I'd have a relational database with several tables. But this is a regular desktop application using SQLite as a way to save user's documents.

     @XIU said:

    XML would have been easier...

    <Test Name="Test ..." Date="..." ...>
    <Result Student="ID1" Score="0" />
    ...
    </Test>
     
    Although it's an obvoius solution, there are several disadvantages of XML in my case:

    - there's a quite big overhead for noting just a test result. To note just a single result, you need around 35 characters, where in SQLite db format it'd take only a few.

    - I can't use SQL aggregation functions which help me a lot in statistical functions with the code. Also filtering by columns is easier.

    - There is no significant difference between ease of use of XML and SQLite (I'm talking about .NET)

    So, I still fail to see the WTF. Is using SQLite in this way a WTF? why?


  • @dstozek said:

    So, I still fail to see the WTF. Is using SQLite in this way a WTF? why?
     

    Well, then it's not so much an SQL WTF as it is a choice of format (non-)issue. The things you mention certainly make a DB look like the best solution for user files.



  • TRWTF is that many people don't understand the whole point of using SQLite: it's not designed for huge server apps (like SQL Server or Oracle or w/e), it's for simple files. You wouldn't call Excel a wtf for storing each spreadsheet in a separate *.xls file instead of a gigantic database, and the OP is using SQLite the same way. It just happens that SQlite files are accessed using SQL.



  • @HypocriteWorld said:

    TRWTF is that many people don't understand the whole point of using SQLite: it's not designed for huge server apps (like SQL Server or Oracle or w/e), it's for simple files. You wouldn't call Excel a wtf for storing each spreadsheet in a separate *.xls file instead of a gigantic database, and the OP is using SQLite the same way. It just happens that SQlite files are accessed using SQL.
     

    The docs even say that it is not designed to replace Oracle; it is designed to replace fopen().



  • @HypocriteWorld said:

    TRWTF is that many people don't understand the whole point of using SQLite: it's not designed for huge server apps (like SQL Server or Oracle or w/e), it's for simple files. You wouldn't call Excel a wtf for storing each spreadsheet in a separate *.xls file instead of a gigantic database, and the OP is using SQLite the same way. It just happens that SQlite files are accessed using SQL.

     Yeh I’ve run into that issue a few times, amount of arguments I’ve had... 'why not use an xml file instead? we don't need a huge database running on a server', have to keep explaining that SqlLite is not a DBMS server, and is much lighter weight than a huge bloated xml file and offers all the benefits of having a Sql language. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>I've found a good use for SqlLite though for ever day use, we use Fluent nHibernate against Oracle. So to speed up unit tests we simply swap the Oracle configuration for a SqlLite configuration (with some funky additional magic to handle a couple of mapping differences). <o:p></o:p>

     



  •  Sorry about thread-digging, but it's OP speaking here.

    In case that interests anyone, the application we based on this solution is almost finished and currently in field testing. The described SQLite solution works like a wonder, was easy to code, hopefully it will be easy to maintain. SQLite itself is great, especially compared to MS SQL Compact.

    OP


  • :belt_onion:

    @dstozek said:

    SQLite itself is great, especially compared to MS SQL Compact.
    Could you elaborate a bit on this? I'm curious to know what problems you had because I'm considering using MS SQL Compact Edition. In what scenario is SQLite the better choice and why?



  • @dstozek said:

     Sorry about thread-digging, but it's OP speaking here.

    In case that interests anyone, the application we based on this solution is almost finished and currently in field testing. The described SQLite solution works like a wonder, was easy to code, hopefully it will be easy to maintain. SQLite itself is great, especially compared to MS SQL Compact.

    OP

    For what it's worth, Apple's application building framework (Cocoa) also offers you to store the contents of a document in sqlite format. That's exactly your setup: one app = one database scheme, one document = one sqlite database. It also allows you to replace it by XML, which encodes exactly the same relations, but (obviously) more readable and disk-space hungry. In Cocoa, your properties would constitute an extra table, one with one row where all properties are columns; the variant with key/value pairs in multiple rows integrates less well in Cocoa's data binding system. 


Log in to reply