gistfile1.js

Schema.add({
    name: 'Content',
    fields: [
        { name: 'contentId', type: 'int', autoIncrement: true, defaultValue: 0 },
        { name: 'seoFriendly', type: 'varchar', size: 255 },
        { name: 'title', type: 'varchar', size: 255, defaultValue: 'Untitled' },
        { name: 'description', type: 'varchar', size: 255, defaultValue: 'No description' },
        { name: 'body', type: 'longtext' }
    ],
    primaryKey: 'contentId',
    indexes: [
        'seoFriendly'
    ]
});

Schema.add({
    name: 'Churches',
    fields: [
        { name: 'churchId', type: 'int', autoIncrement: true, defaultValue: 0 },
        { name: 'churchName', type: 'varchar', size: 255, defaultValue: 'Unknown' },
    ],
    primaryKey: 'churchId'
});

Schema.add({
    name: 'ChurchContentRelationships',
    fields: [
        { name: 'churchId', type: 'int' },
        { name: 'contentId', type: 'int' }
    ],
    primaryKey: 'churchId, contentId'
});

// get all content related to a specific church
SQL.getDataRows([
    'SELECT',
    '   *',
    'FROM',
    '   Content,ChurchContentRelationships',
    'WHERE',
    '   ChurchContentRelationships.churchId = ' req.session.churchId,
    '   AND Content.contentId=ChurchContentRelationships.contentId'
]);